I am using pandas with a dataframe like below:

I want to create buckets for the percent column such as 0-5, 6-15, >16. With these buckets I record both the count of percent column (which is effectively a histogram) but also record the average of Amount in that same bucket.

Using the above example:

How can I achieve this in python and pandas (or any other library)

CodePudding user response：

You need to use pandas.cut and groupby agg:

(df.assign(Bucket=pd.cut(df['percent '],
                         [0, 5, 15, float('inf')],
                         labels=['0-5', '6-15', '>15']))
   .groupby('Bucket').agg(**{'percent count': ('percent ', 'count'),
                            'Avg. Amount': ('Amount', 'mean')
                           })
   .fillna(0, downcast='infer')
   .reset_index()
)

output:

  Bucket  percent count  Avg. Amount
0    0-5              3           82
1   6-15              0            0
2    >15              1           20

CodePudding user response：

Use numpy.select, Series.between and Groupby.agg:

In [232]: import numpy as np

In [233]: conds = [df['percent'].between(0,5), df['percent'].between(6,15), df['percent'].gt(15)]

In [234]: choices = ['5', '15', '>15']

In [237]: df['Bucket'] = np.select(conds, choices)

In [245]: res = df.groupby('Bucket').agg({'percent': 'count', 'Amount': 'mean'}).reindex(choices).fillna(0).astype(int).reset_index()

In [246]: res
Out[246]: 
  Bucket  percent  Amount
0      5        3      82
1     15        0       0
2    >15        1      20

Timings:

@mozway's solution:

In [257]: def f2():
     ...:     (df.assign(Bucket=pd.cut(df['percent'],[0, 5, 15, float('inf')],labels=['0-5', '6-15', '>15'])).groupby('Bucket').agg(**{'percent count': ('percent', 'count'),'Avg. Amount': ('Amount', 'mean')}).fillna(0, dow
     ...: ncast='infer').reset_index())
     ...: 

In [258]: %timeit f2()
8.02 ms ± 587 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

My solution:

In [253]: def f1():
     ...:     conds = [df['percent'].between(0,5), df['percent'].between(6,15), df['percent'].gt(15)]
     ...:     choices = ['5', '15', '>15']
     ...:     df['Bucket'] = np.select(conds, choices)
     ...:     res = df.groupby('Bucket').agg({'percent': 'count', 'Amount': 'mean'}).reindex(choices).fillna(0).astype(int).reset_index()
     ...: 

In [254]: %timeit f1()
3.64 ms ± 371 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)