CODEWITHSUNDEEP
pythonpandaspandas-groupbymulti-index
Eugen Vušak
Eugen Vušak

Reputation: 61

Python Pandas: MultiIndex groupby second level of columns

I'm trying to group rows by multiple columns. What I want to achieve can be illustrated by this small example:

import pandas as pd

col_index = pd.MultiIndex.from_arrays([['A','A','B','B'],['a','b','c','d']])

df = pd.DataFrame([ [1,2,3,3],
                    [4,2,2,2],
                    [6,4,2,2],
                    [1,2,4,4],
                    [3,8,4,4],
                    [1,2,3,3]], columns = col_index)

DataFrame created by this looks like this: 

   A     B   
   a  b  c  d
0  1  2  3  3
1  4  2  2  2
2  6  4  2  2
3  1  2  4  4
4  3  8  4  4
5  1  2  3  3

I would like to group by 'c' and 'd', actually whole 'B' This gives me "KeyError: 'c' "

#something like this
df.groupby(['c','d'], axis = 1, level = 1)
#or like this
df.groupby('B', axis = 1, level = 0)

I tried searching for answer but I can't seem to find any.

Can somebody tell me what I'm doing wrong?

Upvotes: 6

Views: 3843

Answers (2)

Tai
Tai

Reputation: 7994

You can also specify the 2-level multi-indices explicitly. 

df.groupby([("B","c"), ("B", "d")])

Upvotes: 2

Allen Qin
Allen Qin

Reputation: 19947

This is one way of doing it by resetting the columns first:

df.set_axis(df.columns.droplevel(0), axis=1,inplace=False).groupby(['c','d']).sum()
Out[531]: 
      a   b
c d        
2 2  10   6
3 3   2   4
4 4   4  10

Upvotes: 1

Related Questions