CODEWITHSUNDEEP
pythonpandas
Amit
Amit

Reputation: 91

Pandas Multi-Index aggregations

I have a multi indexed pandas dataframe like -

   a             b           c
c1   c2      c1    c2     c1   c2   
9   10.0     162  165     16  15.0

I want to create another column which would contain sum of c1 columns and c2 columns for that row like -

   a             b           c          total
c1   c2      c1    c2     c1   c2      c1   c2
9   10.0     162  165     16  15.0     187  190

How to I achieve the same considering there can be n number of level 1 columns (a,b,c,d...) and n number of level 2 columns (c1,c2,c3,c4...)

Thanks!

Upvotes: 0

Views: 61

Answers (2)

Shubham Sharma
Shubham Sharma

Reputation: 71689

Let us try stack to reshape then assign the total column by summing along the columns axis, then unstack to reshape back

s = df.stack()
s.assign(total=s.sum(1)).unstack()

     a          b          c        total       
    c1    c2   c1   c2    c1    c2     c1     c2
0  9.0  10.0  162  165  16.0  15.0  187.0  190.0

Upvotes: 2

Nk03
Nk03

Reputation: 14949

Here's one way:

  1. sum using level=1 and axis=1
  2. Convert the result to multiindex
  3. use pd.concat to join the result with your original df.
df1 = df.sum(level=1, axis=1)
df1.columns = pd.MultiIndex.from_product([['total'], df1.columns])
df = pd.concat([df, df1], 1)

OUTPUT:

   a          b        c        total       
  c1    c2   c1   c2  c1    c2     c1     c2
0  9  10.0  162  165  16  15.0  187.0  190.0

Upvotes: 3

Related Questions