Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I improve the performance of pandas GroupBy filter operation?

This is my first time asking a question.

I'm working with a large CSV dataset (it contains over 15 million rows and is over 1.5 GB in size).

I'm loading the extracts into Pandas dataframes running in Jupyter Notebooks to derive an algorithm based on the dataset. I group the data by MAC address, which results in 1+ million groups.

Core to my algorithm development is running this operation:

pandas.core.groupby.DataFrameGroupBy.filter

Running this operation takes 3 to 5 minutes, depending on the data set. To develop this algorithm, I must execute this operation hundreds, perhaps thousands of times.

This operation appears to be CPU bound and only uses one of several cores available on my machine. I spent a few hours researching potential solutions online. I've tried to use both numba and dask to accelerate this operation and both attempts resulted in exceptions.

Numba provided a message to the effect of "this should not have happened, thank you for helping improve the product". Dask, it appears, may not implement the DataFrameGroupBy.filter operation. I could not determine how to re-write my code to use pool/map.

I'm looking for suggestions on how to accelerate this operation:

pandas.core.groupby.DataFrameGroupBy.filter

Here is an example of this operation in my code. There are other examples, all of which seem to have about the same execution time.

import pandas as pd

def import_data(_file, _columns):
    df = pd.read_csv(_file, low_memory = False)
    df[_columns] = df[_columns].apply(pd.to_numeric, errors='coerce')
    df = df.sort_values(by=['mac', 'time'])
    # The line below takes ~3 to 5 minutes to run
    df = df.groupby(['mac']).filter(lambda x: x['latency'].count() > 1)
    return df

How can I speed this up?

like image 744
Rob P Avatar asked Feb 09 '19 20:02

Rob P


People also ask

Is pandas GROUP BY efficient?

Groupby is a very popular function in Pandas. This is very good at summarising, transforming, filtering, and a few other very essential data analysis tasks.

What are the two essential functions that pandas GROUP BY method perform?

(1) Splitting the data into groups. (2). Applying a function to each group independently, (3) Combining the results into a data structure. Out of these, Pandas groupby() is widely used for the split step and it's the most straightforward.

Is pandas GROUP BY lazy?

Again, the Pandas GroupBy object is lazy. It delays almost any part of the split-apply-combine process until you call a method on it.


1 Answers

filter is generally known to be slow when used with GroupBy. If you are trying to filter a DataFrame based on a conditional inside a GroupBy, a better alternative is to use transform or map:

df[df.groupby('mac')['latency'].transform('count').gt(1)]

df[df['mac'].map(df.groupby('mac')['latency'].count()).gt(1)]
like image 96
cs95 Avatar answered Oct 25 '22 06:10

cs95