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?
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.
(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.
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.
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)]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With