4. Pivot tables

Group by to pivot table

• The values argument is the column to summarize or column name containing value to aggregate
• The index column is the column to group by and display in row
• The columns argument lists the columns to group by and display in columns
• By default, pivot table takes the mean value for each group

dogs.pivot_table(values=“weight_kg”, index=“color”)

Different statistics

• Use the aggfunc and pass it a function

import numpy as np

dogs.pivot_table(values=“weight_kg”, index=“color”, aggfunc=np.median)

Multiple statistics

• Pass a list of functions to aggfunc argument

dogs.pivot_table(values=“weight_kg”, index=“color”, aggfunc=[np.mean, np.median])

Pivot on to variables

• To group two variables, can pass a second variable name into the column argument

dogs.pivot_table(values=“weight_kg”, index=“color”, columns=“breed”)

Filling missing values in pivot table

• All the NaNs get filled in with zeros

dogs.pivot_table(values=“weight_kg”, index=“color”, columns=“breed”, fill_value=0)

Summing with pivot tables

• Set the margins argument to True, the last row and last column of the pivot table contain the mean of all the values in the column or row

dogs.pivot_table(values=“weight_kg”, index=“color”, columns=“breed”, fill_value=0, margins=True)

Example 1

# Pivot for mean weekly_sales for each store type

mean_sales_by_type = sales.pivot_table(values="weekly_sales",index="type")

print(mean_sales_by_type)

import numpy as np

# Pivot for mean and median weekly_sales for each store type

mean_med_sales_by_type = sales.pivot_table(values="weekly_sales", index="type", aggfunc=[np.mean, np.median])

print(mean_med_sales_by_type)

# Pivot for mean weekly_sales by store type and holiday

mean_sales_by_type_holiday = sales.pivot_table(values="weekly_sales",index="type", columns="is_holiday")

print(mean_sales_by_type_holiday)

Example 2

# Print the mean weekly_sales by department and type; fill missing values with 0s; sum all rows and cols

print(sales.pivot_table(values="weekly_sales", index="department", columns="type", fill_value=0, margins=True))