Skip rows when loading txt file

Question

I have roughly 8000 gzipped files that are basically .txt files. Each files contains something in the order of 5 million rows. Additionally, each row either has 7 or 10 columns. The number of columns is defined by one column which has the value -1 or 1 respectively.

The thing is I need to load all files, do some filtering (since I don't need everything in each file), and then concat everything in the end to do my final analysis.

What I do now is something like this:

gz = gzip.open(f'{path}/{filename}')
df = pd.read_csv(gz, delimiter="	")

This is by far the step of all the steps I am doing that takes the longest time. Reading a 5 million row gzipped file. This takes about 15s in this case for each file. However, I know in advance whether or not the things I need from files are either -1 or 1 in the particular column I talked about above. And to filter out all of those that are e.g. not -1 I can just do:

df = df[df["col1"] != 1]

But this happens AFTER I have loaded the entire file. And depending on whether it's -1 or 1 there is a reduction of either 25% or 75% in data, respectively.

Again, since the "bottleneck" are the read_csv part, I thought that by not loading either 25% or 75% of the data I could reduce the loading time. Because when everything is loaded df = df[df["col1"] != 1] only takes a ms or something.

So is there any way to filter out these particular rows while loading, or will that not give any noticable performance boost at all ? Or are there even smarter ways to load this in a faster way ?

user_8973620 · Answer

As the first option, you can read only the 'filter' column and determine which rows you want to keep:

df = pd.read_csv('path', usecols='col')

selector = df[df['col'] != 1].index
df = pd.read_csv('path', skiprows=selector)

As the second option, your can read data in chunks and filter each chunk:

lst = []
for chunk in pd.read_csv('path', chunksize=1000):
    chunk = chunk[df['col'] != 1]
    lst.append(chunk)

df = pd.concat(lst)

user_11971785 · Answer

Reading large data from csv for ad hoc analysis is not optimal in my oppinion. You might want consider to create a script that reads the csv files once and store them as a whole in a much better format for later, e.g. in an database or at least something like pickle.

8000*15sec = 120000sec = ~33.33h

You could therefore try the following options to read the data to speed up the initial transformation:

Option 1:

import csv
data = csv.DictReader(open('ur_file.csv'))

Option 2:

import dask
data = dask.dataframe.read_csv('ur_file.csv')

Try to avoid pandas, pandas csv read is extremly slow because csv does not store datatypes. This means it has to infer the correct type for each value, which is expensive. Pickle for example stores those information. But there are other types which might be better as well, e.g. feather etc. There are dedicated blog articles just for that

Skip rows when loading txt file

2 Answers