Different column aggregations in python dataframe

Question

How to get a row at last of dataframe which is column aggregations. But, it is sum for few and max for other?

import pandas as pd

data = [
    ["id_1",6,7,9],
    ["id_2",9,7,1],
    ["id_3",6,7,10],
    ["id_4",9,5,10]
]
df = pd.DataFrame(data, columns = ['Student Id', 'Math', 'Physical', 'Flag'])

     Student Id  Math  Physical  Flag
0       id_1     6         7          9
1       id_2     9         7          1
2       id_3     6         7         10
3       id_4     9         5         10

I want to get a row at last as total which gives me column sum for Math & Physical and max for Flag. Excluding student_id column

     Student Id  Math  Physical  Chemistry
0       id_1     6         7          9
1       id_2     9         7          1
2       id_3     6         7         10
3       id_4     9         5         10
       (Total)   30        26         10

I can do the sum of all by below but how to get different aggregations for different columns and also exclude NAN?

df.loc['(TOTAL)'] = df[['Math', 'Physical' ]].sum(axis = 0)

jezrael · Accepted Answer · 2021-03-19 11:21:34Z

2

Use DataFrame.agg for specify aggregations, for new total row is used DataFrame.set_index:

df = df.set_index('Student Id')
df.loc['(TOTAL)'] = df.agg({'Math':'sum', 'Physical':'sum', 'Flag':'max'})
df = df.reset_index()
print (df)
  Student Id  Math  Physical  Flag
0       id_1     6         7     9
1       id_2     9         7     1
2       id_3     6         7    10
3       id_4     9         5    10
4    (TOTAL)    30        26    10

Or replaced NaN only for specified column:

df.loc[len(df)] = df.agg({'Math':'sum', 'Physical':'sum', 'Flag':'max'})
df = df.fillna({'Student Id':'(TOTAL)'})

print (df)
  Student Id  Math  Physical  Flag
0       id_1   6.0       7.0   9.0
1       id_2   9.0       7.0   1.0
2       id_3   6.0       7.0  10.0
3       id_4   9.0       5.0  10.0
4    (TOTAL)  30.0      26.0  10.0

answered Mar 19, 2021 at 11:21

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Different column aggregations in python dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related