CODEWITHSUNDEEP
pythonpandas
marupav
marupav

Reputation: 335

How to get percentiles on groupby column in python?

I have a dataframe as below:

df = pd.DataFrame({'state': ['CA', 'WA', 'CO', 'AZ'] * 3,
               'office_id': list(range(1, 7)) * 2,
               'sales': [np.random.randint(100000, 999999) for _ in range(12)]})

To get percentiles of sales,state wise,I have written below code:

pct_list1 = []
pct_list2 = []
for i in df['state'].unique().tolist():
    pct_list1.append(i)
    for j in range(0,101,10):
        pct_list1.append(np.percentile(df[df['state'] == i]['sales'],j))
    pct_list2.append(pct_list1)
    pct_list1 = []

colnm_list1 = []
for k in range(0,101,10):
    colnm_list1.append('perct_'+str(k))

colnm_list2 = ['state'] + colnm_list1

df1 = pd.DataFrame(pct_list2)
df1.columns = colnm_list2
df1

Can we optimize this code?

I feel that,we can also use

df1 = df[['state','sales']].groupby('state').quantile(0.1).reset_index(level=0)
df1.columns = ['state','perct_0']
for i in range(10,101,10):
    df1.loc[:,('perct_'+str(i))] = df[['state','sales']].groupby('state').quantile(float(i/100.0)).reset_index(level=0)['sales']

If there are any other alternatives,please help.

Thanks.

Upvotes: -1

Views: 842

Answers (1)

Peter9192
Peter9192

Reputation: 3069

How about this?

quants = np.arange(.1,1,.1)
pd.concat([df.groupby('state')['sales'].quantile(x) for x in quants],axis=1,keys=[str(x) for x in quants])

Upvotes: 1

Related Questions