CODEWITHSUNDEEP
pandaspython-3.8
Zanam
Zanam

Reputation: 4807

Pivot table in pandas dataframe with multi level columns

I have a pandas dataframe df which looks as following:

node    date_   A1         A2   Month
bkt             B1         B2   
0   1/1/2015     0.98       1
1   1/2/2015     0.71    0.96   1
2   1/3/2015     0.98       1
3   2/4/2015     0.34       2
4   2/2/1900     0.96    1.57   2
5   2/6/2015     0.01    0.03   2
6   3/7/2015     0.30    0.25   3

I am trying to group by Month for each column to get the following mean:

Month   A1   A2
        B1   B2
1    0.89    0.96
2    0.44    0.80 
3    0.30    0.25

where 

0.89 = avreage(0.98, 0.71, 0.98) 
0.80 = average(1.57, 0.03)

where for each month I want to group by (A1, B1) and (A2, B2) to get average of values. I am not sure how to do this multi level grouping.

Edit:

df.columns
MultiIndex([( 'date_', ''),
            ( 'A1', 'B1'),
            ( 'A2',  'B2'),
            ( 'Month', '')],
           names=['node', 'bkt'])

Upvotes: 1

Views: 155

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150735

IIUC:

df['date_'] = pd.to_datetime(df['date_'])
df.drop(('Month',''), axis=1).groupby(df['date_'].dt.month).mean()

Output:

node         A1        A2
bkt          B1        B2
date_                    
1      0.890000  0.986667
2      0.436667  1.200000
3      0.300000  0.250000

Upvotes: 1

Related Questions