Q:

Identifying and computing statistics for top x objects in a dataframe

Hi I have a dataframe as follows:
    product ID  fees    % fees  quantity    % quantity  avg. price/item
0   ABB         4000    6%       651           5%            100
1   AXX         2500    5%       425           4%            110
2   ACC         2000    5%       538           4%            90
3   ADD         1500    4%       217           3%            80
4   AEE         1300    4%       192           3%            120

The dataframe is ordered by the fees for each product (highest to lowest) and I have 4000 products in the dataframe.
What I would like to do is create 3 buckets: for products 1-10, 11-1000, 1001+. For each bucket I would like to calculate total % fees, total % quantity and a quantity weighted mean of avg. price for that bucket.
I know how to do this using groupby if I had labelled the rows as to what bucket they belong to (then I could groupby the column containing the bucket label and compute the statistic).
Any ideas on how to best label each row with a bucket identifier? If there is a less pedestrian way of doing this without labelling rows please let me know!

A:

I took the liberty to change your data a bit to let it span across all possible ranges and labels.
df:
   product ID  fees % fees quantity % quantity avg. price/item label
0        ABB    40     6%      651         5%             100     2
1        AXX     2     5%      425         4%             110     1
2        ACC  2000     5%      538         4%              90     3
3        ADD   150     4%      217         3%              80     2
4        AEE  1300     4%      192         3%             120     3

To label the data, you need to use pandas.cut
df['label'] = pd.cut(df['fees'], [1, 10, 1000, np.inf], labels=[1,2,3])

Output:
   product ID  fees % fees quantity % quantity avg. price/item label
0        ABB    40     6%      651         5%             100     2
1        AXX     2     5%      425         4%             110     1
2        ACC  2000     5%      538         4%              90     3
3        ADD   150     4%      217         3%              80     2
4        AEE  1300     4%      192         3%             120     3

Then, as you have mentioned, you can simply groupby data with labels and perform the statistic with groupby.
Note that [1, 10, 1000, np.inf] defines the bins while, [1,2,3] are labels for the bins. 

