I have below data frame:
Name Account Revenue 1 Revenue 2
John A Set-up 100.00 0.00
Peter K Slot 250.00 0.00
Michael S Set-up 0.00 25.00
I'm trying to use pandas pivot function so I can have Account
values as columns but to sum up Revenue 1
and Revenue 2
together.
df=df.pivot_table(data=df,index=['Name'],columns=['Account'])
But it is returning below data frame:
Revenue 1 Revenue 2
Name Set-up Slot Set-up
John 100.00 0.00 0.00
Peter K 0.00 250.00 0.00
Michael S 0.00 0.00 25.00
What I would like is to sum both revenue types and show up in same account type as below:
Name Set-up Slot
John 100.00 0.00
Peter K 0.00 250.00
Michael S 25.00 0.00
Any ideas guys?
CodePudding user response:
You can sum Revenue
s before pivoting:
df['Revenue'] = df.filter(like='Revenue').sum(axis=1)
df=df.pivot_table(index='Name',
columns='Account',
values='Revenue',
fill_value=0,
aggfunc='sum')
print (df)
Account Set-up Slot
Name
John A 100 0
Michael S 25 0
Peter K 0 250
Your solution and sum
after pivoting:
df=df.pivot_table(index='Name',columns='Account',aggfunc='sum').groupby(level=1,axis=1).sum()
print (df)
Account Set-up Slot
Name
John A 100.0 0.0
Michael S 25.0 0.0
Peter K 0.0 250.0