Using pandas cut function with groupby and group-specific bins

Question

I have the following sample DataFrame

import pandas as pd
import numpy as np

df = pd.DataFrame({'Tag': ['A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C', 'C', 'C', 'C', 'C'],
                   'ID': [11, 12, 16, 19, 14, 9, 4, 13, 6, 18, 21, 1, 2], 
                   'Value': [1, 13, 11, 12, 2, 3, 4, 5, 6, 7, 8, 9, 10]})

to which I add the percentage of the Value using

df['Percent_value'] = df['Value'].rank(method='dense', pct=True)

and add the Order using pd.cut() with pre-defined percentage bins

percentage = np.array([10, 20, 50, 70, 100])/100

df['Order'] = pd.cut(df['Percent_value'], bins=np.insert(percentage, 0, 0), labels = [1,2,3,4,5])

which gives

    Tag  ID   Value Percent_value   Order
0    A   11     1   0.076923         1
1    A   12     13  1.000000         5
2    A   16     11  0.846154         5
3    B   19     12  0.923077         5
4    B   14     2   0.153846         2
5    B   9      3   0.230769         3
6    B   4      4   0.307692         3
7    C   13     5   0.384615         3
8    C   6      6   0.461538         3
9    C   18     7   0.538462         4
10   C   21     8   0.615385         4
11   C   1      9   0.692308         4
12   C   2      10  0.769231         5

My Question

Now, instead of having a single percentage array (bins) for all Tags (groups), I have a separate percentage array for each Tag group. i.e., A, B and C. How can I apply df.groupby('Tag') and then apply pd.cut() using different percentage bins for each group from the following dictionary? Is there some direct-way avoiding for loops as I do below?

percentages = {'A': np.array([10, 20, 50, 70, 100])/100,
               'B': np.array([20, 40, 60, 90, 100])/100,
               'C': np.array([30, 50, 60, 80, 100])/100}

Desired outcome (Note: Order is now computed for each Tag using different bins):

    Tag  ID   Value Percent_value   Order
0    A   11     1    0.076923        1          
1    A   12     13   1.000000        5
2    A   16     11   0.846154        5
3    B   19     12   0.923077        5
4    B   14     2    0.153846        1
5    B   9      3    0.230769        2
6    B   4      4    0.307692        2
7    C   13     5    0.384615        2
8    C   6      6    0.461538        2
9    C   18     7    0.538462        3
10   C   21     8    0.615385        4
11   C   1      9    0.692308        4
12   C   2      10   0.769231        4

My Attempt

orders = []

for k, g in df.groupby(['Tag']):
    percentage = percentages[k]
    g['Order'] = pd.cut(g['Percent_value'], bins=np.insert(percentage, 0, 0), labels = [1,2,3,4,5])
    orders.append(g)

df_final = pd.concat(orders, axis=0, join='outer')

user_6287308 · Answer

You can apply pd.cut within groupby,

df['Order'] = df.groupby('Tag').apply(lambda x: pd.cut(x['Percent_value'], bins=np.insert(percentages[x.name],0,0), labels=[1,2,3,4,5])).reset_index(drop = True)


    Tag ID  Value   Percent_value   Order
0   A   11  1         0.076923        1
1   A   12  13        1.000000        5
2   A   16  11        0.846154        5
3   B   19  12        0.923077        5
4   B   14  2         0.153846        1
5   B   9   3         0.230769        2
6   B   4   4         0.307692        2
7   C   13  5         0.384615        2
8   C   6   6         0.461538        2
9   C   18  7         0.538462        3
10  C   21  8         0.615385        4
11  C   1   9         0.692308        4
12  C   2   10        0.769231        4

Using pandas cut function with groupby and group-specific bins

1 Answers