Improve Pandas dataframe filtering speed

Question

I have a dataset with 19 columns and about 250k rows. I have worked with bigger datasets, but this time, Pandas decided to play with my nerves.

I tried to split the original dataset into 3 sub-dataframes based on some simple rules. However, it takes a long time to execute the code. About 15-20 seconds just for the filtering.

Any alternative way that will improve the performance of the code?

import pandas as pd

#read dataset
df = pd.read_csv('myData.csv')

#create a dataframe with col1 10 and col2 <= 15
df1 = df[(df.col1 == 10) & (df.col2 <= 15)]
df = df[~df.isin(df1)].dropna()

#create a dataframe with col3 7 and col4 >= 4
df2 = df[(df.col3 == 7) & (df.col4 >= 4)]
df = df[~df.isin(df2)].dropna()

In the end, I have the df1, df2, df dataframes with the filtered data.

Answer 1

The concept to understand is that the conditional is actually a vector. So, you can simply define the conditions, and then combine them logically, like:

condition1 = (df.col1 == 10) & (df.col2 <= 15)
condition2 = (df.col3 == 7) & (df.col4 >= 4)

# at this point, condition1 and condition2 are vectors of bools

df1 = df[condition1]
df2 = df[condition2 & ~condition1]
df = df[~ (condition1 | condition2)]

This will be considerable faster as it only evaluates the conditional once. Then it uses them to perform indexed lookup to create the new smaller dataframes.

Answer 2

Have you timed which line of your code is most time consuming? I suspect that the line df = df[~df.isin(df1)].dropna() would take a long time. Would it be faster if you simply use the negation of the condition you applied to obtain df1, when you want to filter away rows in df1 from df?

That is, use df = df[(df.col1 != 10) | (df.col2 > 15)].

Answer 3

In addition to the suggestions in the other answers, it helps if we add the search fields to the DataFrame index. Ref: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html

Pandas DataFrames support multi-index as well. Ref: https://pandas.pydata.org/pandas-docs/stable/user_guide/advanced.html

An example can be found here https://stackoverflow.com/a/58061800/2130670