Faster ways to extract stats by group from python dataframe

Question

I am extracting some stats (sd, avg, max, first, last) using groupby from a python data frame but my function is relatively slow and would take many hours on my actual data. I am sure there are other faster ways than the method I am using on a very small sample data below. Please let me know about more efficient and better practices. Thanks!

import pandas as pd
data = [['A', 'A1', 15, 537],['A', 'A1', 16, 40],['A', 'A1', 17, 664],['A', 'A1', 18, 30],['A', 'A1', 19, 673],['A', 'A1', 20, 126],['A', 'A1', 21, 372],['A', 'A1', 22, 278],['A', 'A1', 23, 26],['A', 'A1', 24, 501],['A', 'A2', 12, 667],['A', 'A2', 13, 225],['A', 'A2', 14, 102],['A', 'A2', 15, 890],['A', 'A2', 16, 723],['A', 'A2', 17, 970],['B', 'B1', 8, 68],['B', 'B1', 9, 80],['B', 'B1', 10, 98],['B', 'B1', 11, 103],['B', 'B1', 12, 103],['B', 'B1', 13, 100],['B', 'B2', 21, 86],['B', 'B2', 22, 84],['B', 'B2', 23, 100],['B', 'B2', 24, 19],['B', 'B2', 25, 22],['B', 'B2', 26, 10],['B', 'B2', 27, 40],['B', 'B2', 28, 39],['B', 'B2', 29, 36],['B', 'B2', 30, 71],['B', 'B3', 50, 106],['B', 'B3', 51, 96],['B', 'B3', 52, 35],['B', 'B3', 53, 84],['B', 'B3', 54, 97],['B', 'B3', 55, 50],['B', 'B3', 56, 47]]

df = pd.DataFrame(data, columns = ['Product', 'Model', 'DayId', 'Qty'])

def get_qty_stats(x, qty):    
# x already sorted by pt 
d = {}
d['sd_qty'] = x[qty].std()
d['avg_qty'] = x[qty].mean()
d['max_qty'] = x[qty].max()

open_rec = x.iloc[0] # 1st row
d['open_qty'] = open_rec[qty]

close_rec = x.iloc[-1] # last row
d['close_qty'] = close_rec[qty]

return pd.Series(d, index=['avg_qty', 'max_qty', 'sd_qty', 'open_qty', 'close_qty'])


%time
# call function to get stats for qty
df_out = df.groupby(['Product', 'Model']).apply(get_qty_stats, 'Qty').reset_index()
df_out.loc[df_out['sd_qty'].isna(), 'sd_qty'] = 0

user_7175713 · Answer

You can use named aggregation to get the data, it should be faster

grouper = ['Product', 'Model']
df.groupby(grouper).agg(avg_qty=('Qty', 'mean'),
                        max_qty=('Qty', 'max'), 
                        sd_qty=('Qty', 'std'), 
                        open_qty=('Qty', 'first'), 
                        close_qty=('Qty', 'last'))


                     avg_qty  max_qty      sd_qty  open_qty  close_qty
Product Model
A       A1     324.700000      673  261.111747       537        501
        A2     596.166667      970  354.727736       667        970
B       B1      92.000000      103   14.573949        68        100
        B2      50.700000      100   31.885385        86         71
        B3      73.571429      106   28.756780       106         47

user_6358894 · Answer

.apply is always a chokepoint (it's one of the slowest ways to perform operations on Pandas objects). Here, you can use .agg instead, and pass in the recognized string names for functions like mean instead of writing a custom single-use function:

%%timeit

df.groupby(['Product', 'Model']).agg({'Qty': ['mean', 'max', 'std', 'first', 'last']})

# 6.47 ms ± 520 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Compare:

%%timeit
df.groupby(['Product', 'Model']).apply(get_qty_stats, 'Qty').reset_index()

# 9.95 ms ± 55.7 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Faster ways to extract stats by group from python dataframe

2 Answers