Add a Total row to a pandas dataframe

Simple tip to add a total row to a pandas dataframe. Here is a simple dummy data set. It's not unusual for me to summarize field test results of new prototypes in a format like this. To make the data ready to be pasted into presentations and whatnot I usually add a total row.

In [1]:
import pandas as pd
import numpy as np

# Create dummy data set
df = pd.DataFrame({
    'Region': ['North America', 'South America', 'Europe', 'Asia-Pacific'],
    'Positive': [46, 10, 30, 36],
    'Equal': [21, 12, 15, 11],
    'Negative': [15, 9, 11, 18],
    'Inconclusive': [4, 5, 3, 6],
    })

# We set Region as index, it will make sense when we insert the total row.
df = df.set_index('Region')

# Force datatype as integers, with this example in mind integers are the correct type, it just looks bloated as a float.
for col in df.columns:
    df[col] = df[col].astype('int32')

print(df)

               Positive  Equal  Negative  Inconclusive
Region                                                
North America        46     21        15             4
South America        10     12         9             5
Europe               30     15        11             3
Asia-Pacific         36     11        18             6

If all columns are numerical we can do it like this

In [2]:
# Add total row
df.loc[('Total')] = df.sum()

print(df)

               Positive  Equal  Negative  Inconclusive
Region                                                
North America        46     21        15             4
South America        10     12         9             5
Europe               30     15        11             3
Asia-Pacific         36     11        18             6
Total               122     59        53            18

Data set that also contains non-numericals

Applying the same method on a data set that contains other data types kind of works but has its drawbacks as you can see here.

In [3]:
# Create dummy data set
df_test2 = pd.DataFrame({
    'Region': ['North America', 'South America', 'Europe', 'Asia-Pacific'],
    'Positive': [46, 10, 30, 36],
    'Equal': [21, 12, 15, 11],
    'Negative': [15, 9, 11, 18],
    'Inconclusive': [4, 5, 3, 6],
    'non_numerical': ['A', 'BC', 'DEF', 'GHIJ']
    })

# We set Region as index, it will make sense when we insert the total row.
df_test2 = df_test2.set_index('Region')

# Force datatype as integers, with this example in mind integers are the correct type, it just looks bloated as a float.
for col in ['Positive', 'Equal', 'Negative', 'Inconclusive']:
    df_test2[col] = df_test2[col].astype('int32')

# Add a total row
df_test2.loc[('Total')] = df_test2.sum()

print(df_test2)

               Positive  Equal  Negative  Inconclusive non_numerical
Region                                                              
North America        46     21        15             4             A
South America        10     12         9             5            BC
Europe               30     15        11             3           DEF
Asia-Pacific         36     11        18             6          GHIJ
Total               122     59        53            18    ABCDEFGHIJ

The hardcoded way

We can insert a total row like this. Maybe not the best way since you have to know how many columns that are non-numerical.

In [4]:
# Create dummy data set
df_test3 = pd.DataFrame({
    'Region': ['North America', 'South America', 'Europe', 'Asia-Pacific'],
    'Positive': [46, 10, 30, 36],
    'Equal': [21, 12, 15, 11],
    'Negative': [15, 9, 11, 18],
    'Inconclusive': [4, 5, 3, 6],
    'non_numerical': ['A', 'BC', 'DEF', 'GHIJ']
    })

# We set Region as index, it will make sense when we insert the total row.
df_test3 = df_test3.set_index('Region')

# Add a total row
df_test3.loc[('Total'), :] = [df_test3['Positive'].sum(), df_test3['Equal'].sum(), df_test3['Negative'].sum(), df_test3['Inconclusive'].sum(), np.nan]

# Force datatype as integers, with this example in mind integers are the correct type, it just looks bloated as a float.
for col in ['Positive', 'Equal', 'Negative', 'Inconclusive']:
    df_test3[col] = df_test3[col].astype('int32')

print(df_test3)

               Positive  Equal  Negative  Inconclusive non_numerical
Region                                                              
North America        46     21        15             4             A
South America        10     12         9             5            BC
Europe               30     15        11             3           DEF
Asia-Pacific         36     11        18             6          GHIJ
Total               122     59        53            18           NaN

A more dynamic approach

Another method is to create a new dataframe and append a total row for columns containing numeric values, the other columns will get a NaN (Not a Number)

In [5]:
# Create dummy data set
df_test4 = pd.DataFrame({
    'Region': ['North America', 'South America', 'Europe', 'Asia-Pacific'],
    'Positive': [46, 10, 30, 36],
    'Equal': [21, 12, 15, 11],
    'Negative': [15, 9, 11, 18],
    'Inconclusive': [4, 5, 3, 6],
    'non_numerical': ['A', 'BC', 'DEF', 'GHIJ']
    })

# Create a new dataframe  and append a Total row for columns containing numeric values
df_test4 = pd.DataFrame([*df_test4.values, ['Total', *df_test4.sum(numeric_only=True).values]], columns=df_test4.columns)

print(df_test4)

          Region  Positive  Equal  Negative  Inconclusive non_numerical
0  North America        46     21        15             4             A
1  South America        10     12         9             5            BC
2         Europe        30     15        11             3           DEF
3   Asia-Pacific        36     11        18             6          GHIJ
4          Total       122     59        53            18           NaN