TransWikia.com

create a new column to show the quintile in groupby

Stack Overflow Asked by Warrior on December 4, 2021

Edit: I got several answers below, but as I commented at each of the answers, they seem not to work together with a groupby function. I often got errors such as ‘TypeError: invalid type comparison

I want to create a new column that shows the quintile of the value columns. The sample data is shown below:

d = {'group1': ['A', 'A', 'A', 'A', 'A','B','B','B','B','B'], 'group2': ['a','b','a','b','a','b','a','b','a','b'],
     'value': [1,2,3,4,5,6,7,8,9,10]}
df = pd.DataFrame(d)
group1  group2  value
A      a       1
A      b       2
A      a       3
A      b       4
A      a       5
B      b       6
B      a       7
B      b       8
B      a       9
B      b      10

I want the result to be

group1  group2  value   quintile 
A      a       1       1
A      b       2       1
A      a       3       2
A      b       4       2
A      a       5       3
B      b       6       3
B      a       7       4
B      b       8       4
B      a       9       5
B      b       10      5

I tried several approaches, but they don’t work. For example, the following is what I tried:

df['quintile'] = df.groupby(['group1','group2'])['value'].quintile()

3 Answers

I believe panda.qcut is whatn you are looking for

df = pd.DataFrame(np.arange(10), columns=['value'])
df['quintile'] = pd.qcut(df['value'], 5, labels=False)
df

  value quintile
1   1   0
2   2   1
3   3   1
4   4   2
5   5   2
6   6   3
7   7   3
8   8   4
9   9   4
10  10   5

check this out for more about (pd.qcut)

Answered by linkonabe on December 4, 2021

You have a parenthesis missing after groupby. Also if you are looking for quantiles, the following works:

df.groupby(['group1','group2'])['value'].quantile()

Answered by ChairNTable on December 4, 2021

pd.cut(df['value'], bins=5, labels=[x for x in range(1,6)])

Answered by Chris on December 4, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP