Lesson 3. Run Calculations and Summary Statistics on Pandas Dataframes


Learning Objectives

After completing this page, you will be able to:

  • View and sort data in pandas dataframes.
  • Run calculations and summary statistics (e.g. mean, minimum, maximum) on columns in pandas dataframes.

Review of Methods and Attributes in Python

Methods in Python

Previous chapters in this textbook have introduced the concept of functions as commands that can take inputs that are used to produce output. For example, you have used many functions, including the print() function to display the results of your code and to write messages about the results.

You have also used functions provided by Python packages such as numpy to run calculations on numpy arrays. For example, you used np.mean() to calculate the average value of specified numpy array. To run these numpy functions, you explicitly provided the name of the variable as an input parameter.

np.mean(arrayname)

In Python, data structures such as pandas dataframes can also provide built-in functions that are referred to as methods. Each data structure has its own set of methods, based on how the data is organized and the types of operations supported by the data structure. A method can be called by adding the .method_name() after the name of the data structure (i.e. object):

object_name.method()

rather than providing the name as an input parameter to a function:

function(object_name)

In this chapter, you will explore some methods (i.e. functions specific to certain objects) that are accessible for pandas dataframes.

Object Attributes in Python

In addition to functions and methods, you have also worked with attributes, which are automatically created characteristics (i.e. metadata) about the data structure or object that you are working with. For example, you used .shape to get the structure (i.e. rows, columns) of a specific numpy array using array.shape. This attribute .shape is automatically generated for a numpy array when it is created.

In this chapter, you will use attributes (i.e. metadata) to get more information about pandas dataframes that is automatically generated when it is created.

Pandas documentation provides a list of all attributes and methods of pandas dataframes.

Import Python Packages and Get Data

Begin by importing the necessary Python packages and then downloading and importing data into pandas dataframes. As you learned previously in this textbook, you can use the earthpy package to download the data files, os to set the working directory, and pandas to import data files into pandas dataframes.

# Import packages
import os

import matplotlib.pyplot as plt
import pandas as pd
import earthpy as et
# URL for .csv with avg monthly precip data
avg_monthly_precip_url = "https://ndownloader.figshare.com/files/12710618"

# Download file
et.data.get_data(url=avg_monthly_precip_url)
'/root/earth-analytics/data/earthpy-downloads/avg-precip-months-seasons.csv'
# Set working directory to earth-analytics
os.chdir(os.path.join(et.io.HOME,
                      "earth-analytics",
                      "data"))
# Import data from .csv file
fname = os.path.join("earthpy-downloads",
                     "avg-precip-months-seasons.csv")

avg_monthly_precip = pd.read_csv(fname)

avg_monthly_precip
monthsprecipseasons
0Jan0.70Winter
1Feb0.75Winter
2Mar1.85Spring
3Apr2.93Spring
4May3.05Spring
5June2.02Summer
6July1.93Summer
7Aug1.62Summer
8Sept1.84Fall
9Oct1.31Fall
10Nov1.39Fall
11Dec0.84Winter

View Contents of Pandas Dataframes

Rather than seeing all of the dataframe at once, you can choose to see the first few rows or the last few rows of a pandas dataframe using the methods .head() or .tail() (e.g. dataframe.tail()).

This capability can be very useful for large datasets which cannot easily be displayed within Jupyter Notebook.

# See first 5 rows
avg_monthly_precip.head()
monthsprecipseasons
0Jan0.70Winter
1Feb0.75Winter
2Mar1.85Spring
3Apr2.93Spring
4May3.05Spring
# See last 5 rows
avg_monthly_precip.tail()
monthsprecipseasons
7Aug1.62Summer
8Sept1.84Fall
9Oct1.31Fall
10Nov1.39Fall
11Dec0.84Winter

Describe Contents of Pandas Dataframes

You can use the method .info() to get details about a pandas dataframe (e.g. dataframe.info()) such as the number of rows and columns and the column names. The output of the .info() method shows you the number of rows (or entries) and the number of columns, as well as the columns names and the types of data they contain (e.g. float64 which is the default decimal type in Python).

# Information about the dataframe
avg_monthly_precip.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 12 entries, 0 to 11
Data columns (total 3 columns):
 #   Column   Non-Null Count  Dtype  
---  ------   --------------  -----  
 0   months   12 non-null     object 
 1   precip   12 non-null     float64
 2   seasons  12 non-null     object 
dtypes: float64(1), object(2)
memory usage: 416.0+ bytes

You can also use the attribute .columns to see just the column names in a dataframe, or the attribute .shape to just see the number of rows and columns.

# Get column names
avg_monthly_precip.columns
Index(['months', 'precip', 'seasons'], dtype='object')
# Number of rows and columns
avg_monthly_precip.shape
(12, 3)

Run Summary Statistics on Numeric Values in Pandas Dataframes

Pandas dataframes also provide methods to summarize numeric values contained within the dataframe. For example, you can use the method .describe() to run summary statistics on all of the numeric columns in a pandas dataframe:

dataframe.describe()

such as the count, mean, minimum and maximum values.

The output of .describe() is provided in a nicely formatted dataframe. Note that in the example dataset, the column called precip is the only column with numeric values, so the output of .describe() only includes that column.

# Summary stats of all numeric columns
avg_monthly_precip.describe()
precip
count12.000000
mean1.685833
std0.764383
min0.700000
25%1.192500
50%1.730000
75%1.952500
max3.050000

Recall that in the lessons on numpy arrays, you ran multiple functions to get the mean, minimum and maximum values of numpy arrays. This fast calculation of summary statistics is one benefit of using pandas dataframes. Note that the .describe() method also provides the standard deviation (i.e. a measure of the amount of variation, or spread, across the data) as well as the quantiles of the pandas dataframes, which tell us how the data are distributed between the minimum and maximum values (e.g. the 25% quantile indicates the cut-off for the lowest 25% values in the data).

You can also run other summary statistics that are not included in describe() such as .median(), .sum(), etc, which provide the output in a pandas series (i.e. a one-dimensional array for pandas).

# Median of all numeric columns
avg_monthly_precip.median()
precip    1.73
dtype: float64

Run Summary Statistics on Individual Columns

You can also run .describe() on individual columns in a pandas dataframe using:

dataframe[["column_name"]].describe()

Note that using the double set of brackets [] around column_name selects the column as a dataframe, and thus, the output is also provided as a dataframe.

# Summary stats on precip column as dataframe
avg_monthly_precip[["precip"]].describe()
precip
count12.000000
mean1.685833
std0.764383
min0.700000
25%1.192500
50%1.730000
75%1.952500
max3.050000

Using a single set of brackets (e.g. dataframe["column_name"].describe()) selects the column as a pandas series (a one-dimensional array of the column) and thus, the output is also provided as a pandas series.

# Summary stats on precip column as series
avg_monthly_precip["precip"].describe()
count    12.000000
mean      1.685833
std       0.764383
min       0.700000
25%       1.192500
50%       1.730000
75%       1.952500
max       3.050000
Name: precip, dtype: float64

Sort Data Values in Pandas Dataframes

Recall that in the lessons on numpy arrays, you can easily identify the minimum or maximum value, but not the month in which that value occurred. This is because the individual numpy arrays for precip and months were not connected in an easy way that would allow you to determine the month that matches the values.

Using pandas dataframes, you can sort the values with the method .sort_values(), which takes as input:

  • the name of the column to sort
  • a Boolean value of True or False for the parameter ascending

dataframe.sort_values(by="column_name", ascending = True)

Using this method, you can sort by the values in the precip column in descending order (ascending = False) to find the maximum value and its corresponding month.

# Sort in descending order for precip
avg_monthly_precip.sort_values(by="precip",
                               ascending=False)
monthsprecipseasons
4May3.05Spring
3Apr2.93Spring
5June2.02Summer
6July1.93Summer
2Mar1.85Spring
8Sept1.84Fall
7Aug1.62Summer
10Nov1.39Fall
9Oct1.31Fall
11Dec0.84Winter
1Feb0.75Winter
0Jan0.70Winter

Run Calculations on Columns Within Pandas Dataframes

You can run mathematical calculations on columns within pandas dataframes using any mathematical calculation and assignment operator such as:

dataframe["column_name"] *= 25.4

which would multiply each value in the column by 25.4.

Using an assignment operator, you can convert the values in the precip column from inches to millimeters (recall that one inch is equal to 25.4 millimeters).

# Convert values from inches to millimeters
avg_monthly_precip["precip"] *= 25.4

avg_monthly_precip
monthsprecipseasons
0Jan17.780Winter
1Feb19.050Winter
2Mar46.990Spring
3Apr74.422Spring
4May77.470Spring
5June51.308Summer
6July49.022Summer
7Aug41.148Summer
8Sept46.736Fall
9Oct33.274Fall
10Nov35.306Fall
11Dec21.336Winter

You can also easily replace or create a new column within a pandas dataframe using mathematical calculations on itself or other columns such as:

dataframe["column_name_2"] = dataframe["column_name_1"] / 25.4

If column_name_2 already exists, then the values are replaced by the calculation (e.g. values in column_name_2 are set to values of column_name_1 divided by 25.4). If column_name_2 does not already exist, then the column is created as the new last column of the dataframe.

The approach below is preferred over directly modifying your existing data!

# Create new column with precip in the original units (inches)
avg_monthly_precip["precip_in"] = avg_monthly_precip["precip"] / 25.4

avg_monthly_precip
monthsprecipseasonsprecip_in
0Jan17.780Winter0.70
1Feb19.050Winter0.75
2Mar46.990Spring1.85
3Apr74.422Spring2.93
4May77.470Spring3.05
5June51.308Summer2.02
6July49.022Summer1.93
7Aug41.148Summer1.62
8Sept46.736Fall1.84
9Oct33.274Fall1.31
10Nov35.306Fall1.39
11Dec21.336Winter0.84
# Plot the data
f, ax = plt.subplots()

ax.bar(x=avg_monthly_precip.months,
       height=avg_monthly_precip.precip,
       color="purple")

ax.set(title="Plot of Average Monthly Precipitation in mm")
plt.show()
Bar plot of monthly precipitation in mm.
Bar plot of monthly precipitation in mm.

Group Values in Pandas Dataframes

Another benefit of pandas dataframes is that you can group data using a shared common value and then summarize the values in another column using those groups. To do this, you can use the following syntax:

dataframe.groupby(['label_column'])[["value_column"]].method()

in which the label_column is the column is used to create the groups and the value_column is the column that will be summarized for each group.

For example, you could group the example dataframe by the seasons and then run the describe() method on precip. This would run .describe() on the precipitation values for each season as a grouped dataset. In this example, the label_column on which you want to group data is seasons and the value_column that you want to summarize is precip.

# Group data by seasons and summarize precip
precip_by_season=avg_monthly_precip.groupby(["seasons"])[["precip"]].describe()
precip_by_season
precip
countmeanstdmin25%50%75%max
seasons
Fall3.038.4386677.25717333.27434.29035.30641.02146.736
Spring3.066.29400016.78707546.99060.70674.42275.94677.470
Summer3.047.1593335.32996741.14845.08549.02250.16551.308
Winter3.019.3886671.80202817.78018.41519.05020.19321.336

To plot your grouped data, you will have to adjust the column headings. Above you have what’s called a multiindex dataframe. It has two sets of indexes:

  1. precip and
  2. the summary statistics: count, meant, std, etc
precip_by_season.columns
MultiIndex([('precip', 'count'),
            ('precip',  'mean'),
            ('precip',   'std'),
            ('precip',   'min'),
            ('precip',   '25%'),
            ('precip',   '50%'),
            ('precip',   '75%'),
            ('precip',   'max')],
           )
# Drop a level so there is only one index
precip_by_season.columns = precip_by_season.columns.droplevel(0)
precip_by_season
countmeanstdmin25%50%75%max
seasons
Fall3.038.4386677.25717333.27434.29035.30641.02146.736
Spring3.066.29400016.78707546.99060.70674.42275.94677.470
Summer3.047.1593335.32996741.14845.08549.02250.16551.308
Winter3.019.3886671.80202817.78018.41519.05020.19321.336
# Plot the data
f, ax = plt.subplots()

ax.bar(precip_by_season.index,
        precip_by_season["mean"],
        color="purple")

ax.set(title="Bar Plot of Seasonal Monthly Precipitation in mm")
plt.show()
Bar plot showing mean precipitation values by season.
Bar plot showing mean precipitation values by season.

In addition to running .describe() using a groupby, you can also run individual statistics such as:

  • .count() to get the number of rows belonging to a specific group (e.g. season)
  • other summary statistics such as .median(), .sum(), .mean(), etc, to calculate these summary statistics by a chosen group

In the example below, a new dataframe is created by running .median() on precip using a groupby on seasons.

# Save median of precip for each season to dataframe
avg_monthly_precip_median = avg_monthly_precip.groupby(
    ["seasons"])[["precip"]].median()

avg_monthly_precip_median
precip
seasons
Fall35.306
Spring74.422
Summer49.022
Winter19.050

Note the structure of the new dataframe. Does it look different than other dataframes you have seen?

When you run the .info() on this new dataframe, you will see that the index is now the season names, which means that seasons is no longer a column.

avg_monthly_precip_median.info()
<class 'pandas.core.frame.DataFrame'>
Index: 4 entries, Fall to Winter
Data columns (total 1 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   precip  4 non-null      float64
dtypes: float64(1)
memory usage: 64.0+ bytes

To avoid setting a new index when running summary statistics such as median(), you can add a Boolean value of False for the parameter as_index of groupby() to keep the original index.

# Save to new dataframe with original index
avg_monthly_precip_median = avg_monthly_precip.groupby(
    ["seasons"], as_index=False)[["precip"]].median()

avg_monthly_precip_median
seasonsprecip
0Fall35.306
1Spring74.422
2Summer49.022
3Winter19.050

Reset Index of Pandas Dataframes

You can also easily reset the index of any dataframe back to a range index (i.e. starting at [0]) as needed using the syntax:

dataframe.reset_index(inplace=True)

The inplace=True tells the method reset_index to replace the named dataframe with the reset. Running this syntax on any dataframe will reset the index to a range index (i.e. starting at [0]).

In the example below, the index is reset back to a range index starting at [0], rather than using the season name.

# Save summary stats of precip for each season to dataframe
avg_monthly_precip_stats = avg_monthly_precip.groupby(
    ["seasons"])[["precip"]].describe()

avg_monthly_precip_stats
precip
countmeanstdmin25%50%75%max
seasons
Fall3.038.4386677.25717333.27434.29035.30641.02146.736
Spring3.066.29400016.78707546.99060.70674.42275.94677.470
Summer3.047.1593335.32996741.14845.08549.02250.16551.308
Winter3.019.3886671.80202817.78018.41519.05020.19321.336
# Reset index
avg_monthly_precip_stats.reset_index(inplace=True)

avg_monthly_precip_stats
seasonsprecip
countmeanstdmin25%50%75%max
0Fall3.038.4386677.25717333.27434.29035.30641.02146.736
1Spring3.066.29400016.78707546.99060.70674.42275.94677.470
2Summer3.047.1593335.32996741.14845.08549.02250.16551.308
3Winter3.019.3886671.80202817.78018.41519.05020.19321.336

Note in this example that the dataframe is back to a familiar format with a range index starting at [0], but also retains the values calculated with groupby().

You have now learned how to run calculations and summary statistics on columns in pandas dataframes. On the next page, you will learn various ways to select data from pandas dataframes, including indexing and filtering of values.

Leave a Comment