pandas frequency of a specific value per group

Question

Suppose I have data for 50K shoppers and the products they bought. I want to count the number of times each user purchased product "a". value_counts seems to be the fastest way to calculate these types of numbers for a grouped pandas data frame. However, I was surprised at how much slower it was to calculate the purchase frequency for just one specific product (e.g., "a") using agg or apply. I could select a specific column from a data frame created using value_counts but that could be rather inefficient on very large data sets with lots of products.

Below a simulated example where each customer purchases 10 times from a set of three products. At this size you already notice speed differences between apply and agg compared to value_counts. Is there a better/faster way to extract information like this from a grouped pandas data frame?

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "col1": [f'c{j}' for i in range(10) for j in range(50000)],
    "col2": np.random.choice(["a", "b", "c"], size=500000, replace=True)
})
dfg = df.groupby("col1")

# value_counts is fast
dfg["col2"].value_counts().unstack()

# apply and agg are (much) slower
dfg["col2"].apply(lambda x: (x == "a").sum())
dfg["col2"].agg(lambda x: (x == "a").sum())

# much faster to do
dfg["col2"].value_counts().unstack()["a"]

EDIT:

Two great responses to this question. Given the starting point of an already grouped data frame, it seems there may not be a better/faster way to count the number of occurrences of a single level in a categorical variable than using (1) apply or agg with a lambda function or (2) using value_counts to get the counts for all levels and then selecting the one you need.

The groupby/size approach is an excellent alternative to value_counts. With a minor edit to Cainã Max Couto-Silva's answer, this would give:

dfg = df.groupby(['col1', 'col2'])
dfg.size().unstack(fill_value=0)["a"]

I assume there would be a trade-off at some point where if you have many levels apply/agg or value_counts on an already grouped data frame may be faster than the groupby/size approach which requires creating a newly grouped data frame. I'll post back when I have some time to look into that.

Thanks for the comments and answers!

user_7964527 · Answer

Filter before value_counts

df.loc[df.col2=='a','col1'].value_counts()['c0']

Also I think crosstab is 'faster' than groupby + value_counts

pd.crosstab(df.col1, df.col2)

user_7076819 · Answer

This is still faster:

dfg = df.groupby(['col1','col2'])
dfg.size().unstack()

Tests:

%%timeit
pd.crosstab(df.col1, df.col2)
# > 712 ms ± 12.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
dfg = df.groupby("col1")
dfg["col2"].value_counts().unstack()
# > 165 ms ± 12.9 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

%%timeit
dfg = df.groupby(['col1','col2'])
dfg.size().unstack()
# > 131 ms ± 1.35 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

If we expand the dataframe to 5 million rows:

df = pd.concat([df for _ in range(10)])

print(f'df.shape = {df.shape}')
# > df.shape = (5000000, 2)

print(f'{df.shape[0]:,} rows.')
# > 5,000,000 rows.

%%timeit
pd.crosstab(df.col1, df.col2)
# > 1.58 s ± 33.3 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
dfg = df.groupby("col1")
dfg["col2"].value_counts().unstack()
# > 1.27 s ± 47.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%%timeit
dfg = df.groupby(['col1','col2'])
dfg.size().unstack()
# > 847 ms ± 53.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

pandas frequency of a specific value per group

2 Answers