Sum specific columns in dataframe with multi index

Question

I have a dataframe (df) with a multi index

Class   A       B   
Sex    M    F   M   F
Group               
B1    81    34  55  92
B2    38     3  25  11
B3    73    71  69  79
B4    69    23  27  96
B5     5     1  33  28
B6    40    81  91  87

I am trying to create 2 sum columns (one for M and one for F) so my output would look like:

Class   A        B      Total   
Sex    M    F    M   F  Total M Total F
Group                       
B1    81    34  55  92    136       126
B2    38     3  25  11     63        14
B3    73    71  69  79    142       150
B4    69    23  27  96     96       119
B5     5     1  33  28     38        29
B6    40    81  91  87    131       168

I have tried :

df['Total M'] = df['M'].sum(axis=1)
df['Total F'] = df['F'].sum(axis=1)

without success

Ynjxsjmh · Accepted Answer · 2022-06-17 10:50:13Z

1

You can try

df[('Total', 'Total M')] = df.xs('M', level=1, axis=1).sum(axis=1)
df[('Total', 'Total F')] = df.xs('F', level=1, axis=1).sum(axis=1)

# or in a for loop

for col in ['M', 'F']:
    df[('Total', f'Total {col}')] = df.xs(col, level=1, axis=1).sum(axis=1)

print(df)

     A       B       Total
     M   F   M   F Total M Total F
B1  81  34  55  92     136     126
B2  38   3  25  11      63      14
B3  73  71  69  79     142     150
B4  69  23  27  96      96     119
B5   5   1  33  28      38      29
B6  40  81  91  87     131     168

answered Jun 17, 2022 at 10:50

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wwnde · Accepted Answer · 2022-06-17 10:51:43Z

0

use the loc accessor. Code below

df[('Total', 'Total M')] = df.loc(axis=1)[:, ['M']].sum(axis=1)
df[('Total', 'Total F')]= df.loc(axis=1)[:, ['F']].sum(axis=1)

answered Jun 17, 2022 at 10:51

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

Comments

Chris Adams · Accepted Answer · 2022-06-17 11:01:35Z

0

Here's an alternative approach using groupby. Probably overkill for only 2 level groups, but should scale well in scenarios where there are more.

totals = df.groupby(axis=1, level=1).sum()
totals.columns = pd.MultiIndex.from_product([['Total'], totals.columns])

df = df.join(totals)

[out]

Class   A       B     Total     
Sex     M   F   M   F     F    M
Group                           
B1     81  34  55  92   126  136
B2     38   3  25  11    14   63
B3     73  71  69  79   150  142
B4     69  23  27  96   119   96
B5      5   1  33  28    29   38
B6     40  81  91  87   168  131

answered Jun 17, 2022 at 11:01

Chris Adams

18.7k4 gold badges26 silver badges44 bronze badges

Collectives™ on Stack Overflow

Sum specific columns in dataframe with multi index

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related