Appending column totals to a Pandas DataFrame

user_3132783 0
Viewed 108291

I have a DataFrame with numerical values. What is the simplest way of appending a row (with a given index value) that represents the sum of each column?

pandas python
9 Answers

** Get Both Column Total and Row Total **

This gives total on both rows and columns:

import numpy as np
import pandas as pd


df = pd.DataFrame({'a': [10,20],'b':[100,200],'c': ['a','b']})

df.loc['Column_Total']= df.sum(numeric_only=True, axis=0)
df.loc[:,'Row_Total'] = df.sum(numeric_only=True, axis=1)

print(df)

                 a      b    c  Row_Total
0             10.0  100.0    a      110.0
1             20.0  200.0    b      220.0
Column_Total  30.0  300.0  NaN      330.0
user_52003290
  1. Calculate sum and convert result into list(axis=1:row wise sum, axis=0:column wise sum)
  2. Add result of step-1, to the existing dataFrame with new name
new_sum_col = list(df.sum(axis=1))
df['new_col_name'] = new_sum_col
user_63640600

I did not find the modern pandas approach! This solution is a bit dirty due to two chained transposition, I do not know how to use .assign on rows.

# Generate DataFrame
import pandas as pd
df = pd.DataFrame({'a': [10,20],'b':[100,200],'c': ['a','b']})

# Solution
df.T.assign(Total = lambda x: x.sum(axis=1)).T

output:

    a    b  c  Total
0  10  100  a    110
1  20  200  b    220
user_53043660

For those that have trouble because the result is 0 or NaN, check dtype first.

df.dtypes

Since sum can only process numeric try to change the type of your dataframe first. In this example, chang to int32 for integer.

df = df.astype('int32')
df.dtypes

Then, you should be able to sum across row and add new column (as the accepted answer, not the question).

df['sum']= df.sum(numeric_only=True,axis=1)

Bonus: Sort the sum column

df.sort_values(by=['sum'])
user_116717790
Related