Adding a new calculated row to an existing pandas dataframe with calculations specific to each colum-CodePudding

I would like to add a new row to an existing dataframe that consists of values calculated from the pre existing rows. My input looks something like this:

name	value1	value2	value3	value4
john	10	30	40	50
mary	50	10	30	20
smith	40	20	40	10

Now I want to add a Grand total row at the end but the row must not only hold the sum value. Something like this:

name	value1	value2	value3	value4
Grand total	(sum)100	sum(60)	(value2.sum/value1.sum)0.6	(avg )

Thanks in advance

CodePudding user response：

Use DataFrame.agg and add another values to Series, last add new row in DataFrame.loc:

s = df.agg({'value1':'sum', 'value2':'sum', 'value4':'mean'})
s['name'] = 'Grand total'
s['value3'] = s['value2'] / s['value1']

df.loc[len(df)] = s

Or processing separately:

s = df[['value1','value2']].sum()
s['name'] = 'Grand total'
s['value3'] = s['value2'] / s['value1']
s['value4'] = df['value4'].mean()

df.loc[len(df)] = s
print (df)
          name  value1  value2  value3     value4
0         john      10      30    40.0  50.000000
1         mary      50      10    30.0  20.000000
2        smith      40      20    40.0  10.000000
3  Grand total     100      60     0.6  26.666667