I am trying to decile a sales column in my dataframe but also partition by year. So each year should have different deciles.
df = ['year','name', 'sales']
I think I can use this function but want to partition by year
df['decile']=pd.qcut(df['sales'],10,labels=False)
I suppose I can use groupby but I am not able to figure out the syntax.
Would really appreciate any help?
You can try:
df['decile'] = df.groupby('year')[['sales'']].apply(lambda g: pd.qcut(g.rank(method='first'), 10, labels=False)+1)
Explain:
g.rank(method='first'): in case there are many sales with same values. I add this one because in my experiment, I encountered many case where you have same values. If there is low chance of duplicated values, then you can replace by g which is fine.10, labels=False)+1): you can leave option +1 if you want to label from 1 to 10. If not, it will label from 0 to 9