Pandas Get Total | Sum of Column

To get the total or sum of a column use sum() method, and to add the result of the sum as a row to the DataFrame use loc[], at[], append() and pandas.Series() methods. In this article, I will explain how to get the total/sum for a given column with examples.

1. Quick Examples of Get Total of Column

If you are in a hurry, below are some quick examples of how to get the total of pandas DataFrame by a given or all column.


# Below are quick example

# Use DataFrame.sum() method
df2 = df['math'].sum()

# Using DataFrame.sum() method 
df2 = sum(df['math'])

# Use DataFrame.loc[] and pandas.Series() to get total of columns
df.loc['Total'] = pd.Series(df['math'].sum(), index = ['math'])

# Get total of columns using DataFrame.loc[] method
df.loc['Total'] = df["math"].sum()

# Use DataFrame.loc[] & DataFrame.sum() Method
df.loc["Total", "math"] = df.math.sum()

# Use DataFrame.at[] method to get total of columns
df.at['Total', "math"] = df["math"].sum()

# Use DataFrame.append() method
df2 = df.append(pd.DataFrame(df.math.sum(), index = ["Total"], columns=[ "math"]))
print(df2)

Now, let’s create a DataFrame with a few rows and columns, execute these examples and validate results. Our DataFrame contains column names studentnamemathscience and english.


import pandas as pd
studentdetails = {
       "studentname":["Ram","Sam","Scott","Ann","John"],
       "math" :[80,90,85,70,95],
       "science" :[85,95,80,90,75],
       "english" :[90,85,80,70,95]
              }
index_labels=['r1','r2','r3','r4','r5']
df = pd.DataFrame(studentdetails ,index=index_labels)
print(df)

Yields below output.


   studentname        math science  english
r1         Ram          80       85       90
r2         Sam          90       95       85
r3       Scott          85       80       80
r4         Ann          70       90       70
r5        John          95       75       95

2. Use DataFrame.sum() Method

Use DataFrame.sum() method to calculate the sum/total of a column. The below example gets the total sum of math column. Alternatively, you can also use the sum() method that takes the Series object as an argument.


# Use DataFrame.sum() method
math_sum = df['math'].sum()
print(math_sum)

# Using DataFrame.sum() method 
math_sum = sum(df['math'])
print(math_sum )

# Output
# 420

3. Use pandas.Series() to Get Total of Column

Use pandas.Series() to create a sum row at the end of the DataFrame. The index should be set as the same as the specific column you need to sum.


# Use pandas.Series() to to create new row with sum
df.loc['Total'] = pd.Series(df['math'].sum(), index = ['math'])
print(df)

Yields below output.


      studentname        math science  english
r1            Ram        80.0     85.0     90.0
r2            Sam        90.0     95.0     85.0
r3          Scott        85.0     80.0     80.0
r4            Ann        70.0     90.0     70.0
r5           John        95.0     75.0     95.0
Total         NaN       420.0      NaN      NaN

4. Get Total of Column Using Series.sum() Method

Series.sum() gets you the sum of a column. This is equivalent to the method numpy.sum. You can assign the sum of a column to a DataFrame to create a row. Note that in this way, it creates the same value for each column. The next example solves this issue.


# Get total of columns using sum method
df.loc['Total'] = df["math"].sum()
print(df)

Yields below output.


      studentname        math  science  english
r1            Ram          80       85       90
r2            Sam          90       95       85
r3          Scott          85       80       80
r4            Ann          70       90       70
r5           John          95       75       95
Total         420         420      420      420

5. Use DataFrame.loc[] & DataFrame.sum() Methods

You can use DataFrame.loc[] and DataFrame.sum() method to fix the above issue. In this only the column you are getting sum with have total value and other will have NaN value.


# Use DataFrame.loc[] & DataFrame.sum() Method
df.loc["Total", "math"] = df.math.sum()
print(df)

Yields below output.


      studentname        math  science  english
r1            Ram        80.0     85.0     90.0
r2            Sam        90.0     95.0     85.0
r3          Scott        85.0     80.0     80.0
r4            Ann        70.0     90.0     70.0
r5           John        95.0     75.0     95.0
Total         NaN       420.0      NaN      NaN

6. Use DataFrame.at[] Method to Get Total of Column

Alternatively, you can also use DataFrame.at[], This gives the same result as above.


# Use DataFrame.at[] method to get total of columns
df.at['Total', "math"] = df["math"].sum()
print(df)

Yields same output as above.

7. Use DataFrame.append() Method

You can also use DataFrame.append() method to get the total of pandas columns added to the DataFrame.


# Use DataFrame.append() method
df2 = df.append(pd.DataFrame(df.math.sum(), index = ["Total"], columns=[ "math"]))
print(df2)

Yields below output.


      studentname        math  science  english
r1            Ram          80     85.0     90.0
r2            Sam          90     95.0     85.0
r3          Scott          85     80.0     80.0
r4            Ann          70     90.0     70.0
r5           John          95     75.0     95.0
Total         NaN         420      NaN      NaN

8. Complete Example For Get Total of Column


import pandas as pd
import numpy as np
studentdetails = {
       "studentname":["Ram","Sam","Scott","Ann","John"],
       "mathantics" :[80,90,85,70,95],
       "science" :[85,95,80,90,75],
       "english" :[90,85,80,70,95]
              }
index_labels=['r1','r2','r3','r4','r5']
df = pd.DataFrame(studentdetails ,index=index_labels)

# Use DataFrame.sum() method
df2 = df['math'].sum()
print(df2)

# Using DataFrame.sum() method 
df2 = sum(df['math'])
print(df2)

# Use DataFrame.loc[] and pandas.Series() to get total of columns
df.loc['Total'] = pd.Series(df['mathantics'].sum(), index = ['mathantics'])
print(df)

# Get total of columns using DataFrame.loc[] method
df.loc['Total'] = df["math"].sum()
print(df)

# Use DataFrame.loc[] & DataFrame.sum() Method
df.loc["Total", "math"] = df.math.sum()
print(df)

# Use DataFrame.at[] method to get total of columns
df.at['Total', "math"] = df["math"].sum()
print(df)

# Use DataFrame.append() method
df2 = df.append(pd.DataFrame(df.math.sum(), index = ["Total"], columns=[ "math"]))
print(df2)

Conclusion

In this article, you have learned how to get total of column by using DataFrame.sum(), DataFrame.loc[],DataFrame.at[], DataFrame.append() and pandas.Series() for all or given column with examples.

Happy Learning !!

References

Leave a Reply

You are currently viewing Pandas Get Total | Sum of Column