Calculated quartile category of a column in pandas

user_8901845 0
Viewed 1174

I have a data frame as shown below

df:

product_x  year     total_price      total_sale
A          2016     50               200           
B          2016     200              100           
A          2017     250              250           
B          2017     1000             300           
A          2018     100              50           
B          2018     900              600
K          2016     20               300
D          2016     100              450

In above data frame I would like add new column called total_sale_Quartile.

Explanation to calculate total_sale_Quartile.

sort total_sale as shown below

50, 100, 200, 250, 300, 300, 450, 600

Q1 = 50 to 100
Q2 = 101 to 250
Q3 = 251 to 300
Q4 = 301 to 600

Expected output:

product_x  year     total_price      total_sale   total_sale_Quartile
A          2016     50               200          Q2
B          2016     200              100          Q1    
A          2017     250              250          Q2  
B          2017     1000             300          Q3   
A          2018     100              50           Q1    
B          2018     900              600          Q4
K          2016     20               300          Q3
D          2016     100              450          Q4
pandas dataframe python
1 Answers

Use, pd.cut with optional parameter include_lowest=True to categorise the values from total_sale into Quartiles:

df['total_sale_Quartile'] = (
    pd.cut(
        df['total_sale'], bins=[50, 100, 250, 300, 600],
        labels=['Q1', 'Q2', 'Q3', 'Q4'], include_lowest=True)
)

OR, use pd.qcut if you want to categorise the column just based on the quantile ranges:

df['total_sale_Quartile'] = (
    pd.qcut(df['total_sale'], 4, labels=['Q1', 'Q2', 'Q3', 'Q4'])
)

Result:

# print(df)
  product_x  year  total_price  total_sale total_sale_Quartile
0         A  2016           50         200                  Q2
1         B  2016          200         100                  Q1
2         A  2017          250         250                  Q2
3         B  2017         1000         300                  Q3
4         A  2018          100          50                  Q1
5         B  2018          900         600                  Q4
6         K  2016           20         300                  Q3
7         D  2016          100         450                  Q4
user_128331660
Related