Pandas pivot based on grouping columns as one

March 10, 2022

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?

>Solution :

You can sum Revenues 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