After reading Excel data for Dep, Name, Sales, and X Sales values as shown below,
Add the ratio of X Sales by Sales (X Sales/Sales*100) as 'rate' coulmn,
When pivot_table was applied based on Dep in the following way, the rate value was displayed as a simple sum.
pd.pivot_table(df, index='Dep', values=['Sales', 'X Sales', 'rate'], aggfunc=np.sum)
Finally, I would like to express the rate by each Dep (total of X Sales / total of Sales x 100) as below.
It hasn't been long since I started studying, so I think it's a basic question Please help me with how to solve it.
python pandas
How about adding a column separately after calculating the pivot table?
import pandas as pd
import numpy as np
dates = pd.date_range('20130101', periods=6)
d = {'Dep':['A','B','B','B','C','C'], "Sales":[10,0,5,3,4,1],"X Sales":[7,0,2,2,2,0]}
df = pd.DataFrame(data=d)
new_df=pd.pivot_table(df, index='Dep', values=['Sales', 'X Sales'])
new_df['rate']=new_df.apply(lambda row: row['X Sales']/row['Sales']*100, axis=1)
print(new_df)
© 2024 OneMinuteCode. All rights reserved.