CODEWITHSUNDEEP
pythonpandaspivot
NoIdeaHowToFixThis
NoIdeaHowToFixThis

Reputation: 4564

Pivot table with multilevel columns

Given the code below. 

import numpy as np
import pandas as pd

df = pd.DataFrame({
    'clients': pd.Series(['A', 'A', 'A', 'B', 'B']),
    'odd1': pd.Series([1, 1, 2, 1, 2]),
    'odd2': pd.Series([6, 7, 8, 9, 10])})

grpd = df.groupby(['clients', 'odd1']).agg({
    'odd2': [np.sum, np.average]
}).reset_index('clients').reset_index('odd1')

>> grpd
   odd1 clients  odd2         
                  sum  average
0     1       A    13      6.5
1     2       A     8      8.0
2     1       B     9      9.0
3     2       B    10     10.0

I would like to create a pivot table as below:

       | odd1    | odd1    | ...... | odd1    |
------------------------------------|---------|
clients| average | average | .....  | average |

The desired output is:

clients |   1       2      
--------|------------------
A       |   6.5     8.0    
B       |   9.0     10.0

This would work had we a column which is not multilevel:

grpd.pivot(index='clients', columns='odd1', values='odd2')

Not sure I understand how multilevel cols work.

Upvotes: 2

Views: 4828

Answers (1)

unutbu
unutbu

Reputation: 879191

import numpy as np
import pandas as pd

df = pd.DataFrame({
    'clients': pd.Series(['A', 'A', 'A', 'B', 'B']),
    'odd1': pd.Series([1, 1, 2, 1, 2]),
    'odd2': pd.Series([6, 7, 8, 9, 10])})

aggd = df.groupby(['clients', 'odd1']).agg({
    'odd2': [np.sum, np.average]})

print(aggd.unstack(['odd1']).loc[:, ('odd2','average')])

yields

odd1       1   2
clients         
A        6.5   8
B        9.0  10

Explanation: One of the intermediate steps in grpd is 

aggd = df.groupby(['clients', 'odd1']).agg({
    'odd2': [np.sum, np.average]})

which looks like this:

In [52]: aggd
Out[52]: 
             odd2        
              sum average
clients odd1             
A       1      13     6.5
        2       8     8.0
B       1       9     9.0
        2      10    10.0

Visual comparison between aggd and the desired result 

odd1       1   2
clients         
A        6.5   8
B        9.0  10

shows that the odd1 index needs to become a column index. That operation -- the moving of index labels to column labels -- is the job done by the unstack method. So it is natural to unstack aggd:

In [53]: aggd.unstack(['odd1'])
Out[53]: 
        odd2                
         sum     average    
odd1       1   2       1   2
clients                     
A         13   8     6.5   8
B          9  10     9.0  10

Now it is easy to see we just want to select the average columns. That can be done with loc:

In [54]: aggd.unstack(['odd1']).loc[:, ('odd2','average')]
Out[54]: 
odd1       1   2
clients         
A        6.5   8
B        9.0  10

Upvotes: 3

Related Questions