Create bins on groupby in pandas-CodePudding

my data frame got a column department and rank

name  dept  Rank
"A"  "ENG"  1
"A"  "MGMT"  1
"B"  "ENG"  2
"C"  "MGMT"  2
"D"  "MGMT"  3
"E"  "ENG"  3

I want to create bins in each dept.

name  dept  Rank  Comment
"A"  "ENG"  1  Good
"A"  "MGMT"  1  Good
"B"  "ENG"  2  Avg
"C"  "MGMT"  2  Avg
"D"  "MGMT"  3  Poor
"E"  "ENG"  3  Poor

I'm able to do this by below code

 df['Comment'] = pd.qcut(df[df['dept'] == "ENG"]['Rank'], q=[0.0, .25, .5, 1.0], labels=['Good', 'Avg', 'Poor'])
 df['Comment'] = pd.qcut(df[df['dept'] == "MGMT"]['Rank'], q=[0.0, .25, .5, 1.0], labels=['Good', 'Avg', 'Poor'])

I'm very sure, there must be some way using groupby, but can't figure it out.

CodePudding user response：

wouldn't it be simpler to create a dictionary and map the values? Since you would need to do it anyways while creating the labels and mapping to the bins

d={1:'Good',
   2:'Avg',
   3:'Poor'}

df['comments']=df['Rank'].map(d)
df

    name    dept    Rank    comments
0   "A"     "ENG"     1     Good
1   "A"     "MGMT"    1     Good
2   "B"     "ENG"     2     Avg
3   "C"     "MGMT"    2     Avg
4   "D"     "MGMT"    3     Poor
5   "E"     "ENG"     3     Poor

CodePudding user response：

You can use groupby.transform/groupby.apply:

df['Comment'] = (df
 .groupby('dept')['Rank']
 .transform(lambda g:
     pd.qcut(g,
             q=[0, 1/3, 2/3, 3/3],   # changed
             labels=['Good', 'Avg', 'Poor']))
 )

NB. quantiles are in the range [0, 1], I changed the example.

Output:

  name  dept  Rank Comment
0    A   ENG     1    Good
1    A  MGMT     1    Good
2    B   ENG     2     Avg
3    C  MGMT     2     Avg
4    D  MGMT     3    Poor
5    E   ENG     3    Poor