3. Pivot tables with slicing & subsetting

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”)

.loc[ ] + slicing

• Pivot tables are just DataFrames with sorted indexes
• The loc and slicing combination is ideal for subsetting pivot tables

dogs_height_by_breed_vs_color = dog_pack.pivot_table(values=“height_cm”, index=“breed”, columns=“color”)

print(dogs_height_by_breed_vs_color)

dogs_height_by_breed_vs_color.loc[“Chow Chow” : “Poodle”]

The axis argument

• The method for calculating summary statistics on a DataFrame, such as mean, have an axis argument
• The default value is “index”, which means “calculate the statistic across rows”
• Calculating summary stats across rows

dogs_height_by_breed_vs_color.mean(axis=“index”)

• Calculating summary stats across columns

dogs_height_by_breed_vs_color.mean(axis=“columns”)

Pivot Table Example

• Example 1

temperatures["year"] = temperatures["date"].dt.year # Add a year column to temperatures

temp_by_country_city_vs_year = temperature.pivot_table(values="avg_temp_c", index=["country", "city"], columns="year") # Pivot avg_temp_c by country and city vs year

print(temp_by_country_city_vs_year)

• Example 2

temp_by_country_city_vs_year.loc["Egypt" : "India"] # Subset for Egypt to India

temp_by_country_city_vs_year.loc[("Egypt","Cairo") : ("India","Delhi")] # Subset for Egypt, Cairo to India, Delhi

temp_by_country_city_vs_year.loc[("Egypt","Cairo") : ("India","Delhi"), "2005": "2010"] # Subset in both directions at once

• Example 3

mean_temp_by_year = temp_by_country_city_vs_year.mean(axis="index") # Get the worldwide mean temp by year

print(mean_temp_by_year[mean_temp_by_year == mean_temp_by_year.max()]) # Filter for the year that had the highest mean temp

mean_temp_by_city = temp_by_country_city_vs_year.mean(axis="columns") # Get the mean temp by city

print(mean_temp_by_city[mean_temp_by_city == mean_temp_by_city.min()]) # Filter for the city that had the lowest mean temp