Data Analysis Data Wrangling Tutorial

pivot_table() Method: Pivot DataFrame with Aggregation Operation

Pivot tables allow us to perform group-bys on columns and specify aggregate metrics for columns too. This data analysis technique is very popular in GUI spreadsheet applications and also works well in Python using the pandas package and the DataFrame pivot_table() method.

Import Modules

import pandas as pd
import seaborn as sns
import numpy as np

Example 1: Pivot Tables with Flights Dataset

Get Data

Let's get the flights dataset included in the seaborn library and assign it to the DataFrame df_flights.

df_flights = sns.load_dataset('flights')

Preview the first few rows of df_flights.

Each row represents a month's flight history details. The passengers column represents that total number of passengers that flew that month.

df_flights.head()
year month passengers
0 1949 January 112
1 1949 February 118
2 1949 March 132
3 1949 April 129
4 1949 May 121

Implement Pivot Tables

I want to know the sum of passengers that flew on planes for each year. So, from pandas, we'll call the pivot_table() method and set the following arguments:

  • data to be our DataFrame df_flights
  • index to be 'year' since that's the column from df_flights that we want to appear as a unique value in each row
  • values as 'passengers' since that's the column we want to apply some aggregate operation on
  • aggfunc to 'sum' since we want to sum (aka total) up all values in passengers that belong to a unique year
pd.pivot_table(data=df_flights, index='year', values='passengers', aggfunc='sum')
year
1949    1520
1950    1676
1951    2042
1952    2364
1953    2700
1954    2867
1955    3408
1956    3939
1957    4421
1958    4572
1959    5140
1960    5714
Name: passengers, dtype: int64

We can see above that every year, the total number of passengers that flew increased each year. However, this pivot table is poorly documented. There's no column name or label for the values on the right. To fix this, we'll cast this outputted series to a DataFrame and rename the aggregated column to be clearer.

Use the reset_index() method to cast our series to a DataFrame with a new index and then chain the rename() method to rename the 'passengers' column to 'total_passengers'.

pd.pivot_table(data=df_flights, index='year', values='passengers', aggfunc='sum').reset_index().rename(columns={'passengers': 'total_passengers'})
year total_passengers
0 1949 1520
1 1950 1676
2 1951 2042
3 1952 2364
4 1953 2700
5 1954 2867
6 1955 3408
7 1956 3939
8 1957 4421
9 1958 4572
10 1959 5140
11 1960 5714

Now, I want to know the sum of passengers that flew per month in the dataset. So, from pandas, we'll call the the pivot_table() method and include all of the same arguments above, except we'll set the index to be 'month' since that's the column from df_flights that we want to appear as a unique value in each row.

pd.pivot_table(data=df_flights, index='month', values='passengers', aggfunc='sum').reset_index().rename(columns={'passengers': 'total_passengers'})
month total_passengers
0 January 2901
1 February 2820
2 March 3242
3 April 3205
4 May 3262
5 June 3740
6 July 4216
7 August 4213
8 September 3629
9 October 3199
10 November 2794
11 December 3142

Our results indicate most people flew in the summer months of July and August.

Now, I want to know the average number of passengers that flew per month in the dataset. So, from pandas, we'll call the the pivot_table() method and include all of the same arguments from the previous operation, except we'll set the aggfunc to 'mean' since we want to find the mean (aka average) number of passengers that flew in each unique month.

I also rounded the average_number_of_passengers column to 1 decimal place so it's easier to read.

pd.pivot_table(data=df_flights, index='month', values='passengers', aggfunc='mean').reset_index().rename(columns={'passengers': 'average_number_of_passengers'}).round(1)
month average_number_of_passengers
0 January 241.8
1 February 235.0
2 March 270.2
3 April 267.1
4 May 271.8
5 June 311.7
6 July 351.3
7 August 351.1
8 September 302.4
9 October 266.6
10 November 232.8
11 December 261.8

Now, I want to know the maximum number of passengers that flew per month in the dataset. So, from pandas, we'll call the the pivot_table() method and include all of the same arguments from the previous operation, except we'll set the aggfunc to 'max' since we want to find the maximum (aka largest) number of passengers that flew in each unique month.

pd.pivot_table(data=df_flights, index='month', values='passengers', aggfunc='max').reset_index().rename(columns={'passengers': 'max_number_of_passengers'})
month max_number_of_passengers
0 January 417
1 February 391
2 March 419
3 April 461
4 May 472
5 June 535
6 July 622
7 August 606
8 September 508
9 October 461
10 November 390
11 December 432

Example 2: Pivot Tables with Tips Dataset

Get Data

Let's get the tips dataset included in the seaborn library and assign it to the DataFrame df_tips.

df_tips = sns.load_dataset('tips')

Each row represents a unique meal for a party of people at a restaurant; the dataset includes the following fields:

column name column description
total_bill financial amount of meal in U.S. dollars
tip financial amount of the meal's tip in U.S. dollars
sex gender of server
smoker boolean to represent if server smokes or not
day day of week
time meal name (Lunch or Dinner)
size count of people eating meal

Preview the first 5 rows of df_tips.

df_tips.head()
total_bill tip sex smoker day time size
0 16.99 1.01 Female No Sun Dinner 2
1 10.34 1.66 Male No Sun Dinner 3
2 21.01 3.50 Male No Sun Dinner 3
3 23.68 3.31 Male No Sun Dinner 2
4 24.59 3.61 Female No Sun Dinner 4

Implement Pivot Tables

For each day and meal type, I'm curious to find the median bill amount. So, from pandas, we'll call the pivot_table() method and set the following arguments:

  • data to be our DataFrame df_tips
  • index to be ['day', 'time'] since we want to aggregate by both of those columns so each row represents a unique type of meal for a day
  • values as 'total_bill' since that's the column we want to apply some aggregate operation on
  • aggfunc to 'median' since we want to find the median value in our total_bill column for each day's meal type in our dataset
pd.pivot_table(data=df_tips, index=['day', 'time'], values='total_bill', aggfunc='median').reset_index().rename(columns={'total_bill': 'median_total_bill'})
day time median_total_bill
0 Thur Lunch 16.000
1 Thur Dinner 18.780
2 Fri Lunch 13.420
3 Fri Dinner 18.665
4 Sat Lunch NaN
5 Sat Dinner 18.240
6 Sun Lunch NaN
7 Sun Dinner 19.630

Now, I'm curious for each day's meal type, what was the maximum tip value and median total bill. We can do this in one pivot table operation too! From pandas, we'll call the pivot_table() method and set the following arguments:

  • data to be our DataFrame df_tips
  • index to be ['day', 'time'] since we want to aggregate by both of those columns so each row represents a unique type of meal for a day
  • values as ['total_bill', 'tip'] since we want to perform a specific aggregate operation on each of those columns
  • aggfunc to {'total_bill': 'median', 'tip': 'max'} since we want to find the median value for the total_bill column and the maximum value for the tip column
pd.pivot_table(data=df_tips, index=['day', 'time'], values=['total_bill', 'tip'], aggfunc={'total_bill': 'median', 'tip': 'max'}).reset_index().rename(columns={'tip': 'max_tip', 'total_bill': 'median_total_bill'})
day time max_tip median_total_bill
0 Thur Lunch 6.70 16.000
1 Thur Dinner 3.00 18.780
2 Fri Lunch 3.48 13.420
3 Fri Dinner 4.73 18.665
4 Sat Dinner 10.00 18.240
5 Sun Dinner 6.50 19.630

Now, I'm curious for each day's meal type, what was the total amount of tips and total bills. I'd also like a new row for the sum of the total_tip and total_bill columns. To implement this, from pandas, we'll call the pivot_table() method and set the following arguments:

  • data to be our DataFrame df_tips
  • index to be ['day', 'time'] since we want to aggregate by both of those columns so each row represents a unique type of meal for a day
  • values as ['total_bill', 'tip'] since we want to perform a specific aggregate operation on each of those columns
  • aggfunc to 'sum' since we want to find the sum (aka total) for each column passed to the values argument
  • margins to True to add up the numerical columns
pd.pivot_table(data=df_tips, index=['day', 'time'], values=['total_bill', 'tip'], aggfunc='sum', margins=True).reset_index().rename(columns={'tip': 'total_tip', 'total_bill': 'total_bill'})
day time total_tip total_bill
0 Thur Lunch 168.83 1077.55
1 Thur Dinner 3.00 18.78
2 Fri Lunch 16.68 89.92
3 Fri Dinner 35.28 235.96
4 Sat Lunch NaN NaN
5 Sat Dinner 260.40 1778.40
6 Sun Lunch NaN NaN
7 Sun Dinner 247.39 1627.16
8 All 731.58 4827.77

Instead of the final row saying All, I'd prefer it say total. We can perform this change in our pivot_table() method. Utilize all the same code as above and we'll add a new argument called margins_name and set it to 'total'.

pd.pivot_table(data=df_tips, index=['day', 'time'], values=['total_bill', 'tip'], aggfunc='sum', margins=True, margins_name='total').reset_index().rename(columns={'tip': 'total_tip', 'total_bill': 'total_bill'})
day time total_tip total_bill
0 Thur Lunch 168.83 1077.55
1 Thur Dinner 3.00 18.78
2 Fri Lunch 16.68 89.92
3 Fri Dinner 35.28 235.96
4 Sat Lunch NaN NaN
5 Sat Dinner 260.40 1778.40
6 Sun Lunch NaN NaN
7 Sun Dinner 247.39 1627.16
8 total 731.58 4827.77

I don't like how in the output above, we see NaN values in our columns. Those essentially mean there were no numerical values to perform in the aggregate operations so we're left with a value of NaN equivalent to not a number. We can fill in those NaN values with 0.

We can perform this change in our pivot_table() method. Utilize all the same code as above and we'll add a new argument called fill_value and set it to 0.

pd.pivot_table(data=df_tips, index=['day', 'time'], values=['total_bill', 'tip'], aggfunc='sum', margins=True, margins_name='total', fill_value=0).reset_index().rename(columns={'tip': 'total_tip', 'total_bill': 'total_bill'})
day time total_tip total_bill
0 Thur Lunch 168.83 1077.55
1 Thur Dinner 3.00 18.78
2 Fri Lunch 16.68 89.92
3 Fri Dinner 35.28 235.96
4 Sat Lunch 0.00 0.00
5 Sat Dinner 260.40 1778.40
6 Sun Lunch 0.00 0.00
7 Sun Dinner 247.39 1627.16
8 total 731.58 4827.77

Aggregate Operations

Other aggregate methods you could perform with a groupby() method in pandas are:

method description
sum() summation
mean() average
count() count of all values
size() count of non-null values
max() maximum value
min() minimum value
std() standard deviation
median() median