Odtnhj

How can I filter which lines of a CSV to be loaded into memory using pandas? This seems like an option that one should find in read_csv. Am I missing something?

Example: we've a CSV with a timestamp column and we'd like to load just the lines that with a timestamp greater than a given constant.

There isn't an option to filter the rows before the CSV file is loaded into a pandas object.

You can either load the file and then filter using df[df['field'] > constant], or if you have a very large file and you are worried about memory running out, then use an iterator and apply the filter as you concatenate chunks of your file e.g.:

import pandas as pd
iter_csv = pd.read_csv('file.csv', iterator=True, chunksize=1000)
df = pd.concat([chunk[chunk['field'] > constant] for chunk in iter_csv])

You can vary the chunksize to suit your available memory. See here for more details.

I didn't find a straight-forward way to do it within context of read_csv. However, read_csv returns a DataFrame, which can be filtered by selecting rows by boolean vector df[bool_vec]:

filtered = df[(df['timestamp'] > targettime)]

This is selecting all rows in df (assuming df is any DataFrame, such as the result of a read_csv call, that at least contains a datetime column timestamp) for which the values in the timestamp column are greater than the value of targettime. Similar question.

You can specify nrows parameter.


import pandas as pd
df = pd.read_csv('file.csv', nrows=100)


This code works well in version 0.20.3.

If you are on linux you can use grep.

# to import either on Python2 or Python3
import pandas as pd
from time import time # not needed just for timing
try:
 from StringIO import StringIO
except ImportError:
 from io import StringIO


def zgrep_data(f, string):
 '''grep multiple items f is filepath, string is what you are filtering for'''

 grep = 'grep' # change to zgrep for gzipped files
 print(' for from '.format(grep,string,f))
 start_time = time()
 if string == '':
 out = subprocess.check_output([grep, string, f])
 grep_data = StringIO(out)
 data = pd.read_csv(grep_data, sep=',', header=0)

 else:
 # read only the first row to get the columns. May need to change depending on 
 # how the data is stored
 columns = pd.read_csv(f, sep=',', nrows=1, header=None).values.tolist()[0] 

 out = subprocess.check_output([grep, string, f])
 grep_data = StringIO(out)

 data = pd.read_csv(grep_data, sep=',', names=columns, header=None)

 print(' finished for - seconds'.format(grep,f,time()-start_time))
 return data