Pivot tables

Data Manipulation with pandas

Maggie Matsui

Senior Content Developer at DataCamp

Group by to pivot table

dogs.groupby("color")["weight_kg"].mean()
color
Black    26
Brown    24
Gray     17
Tan       2
White    74
Name: weight_kg, dtype: int64
dogs.pivot_table(values="weight_kg", 
                 index="color")
       weight_kg
color           
Black       26.5
Brown       24.0
Gray        17.0
Tan          2.0
White       74.0
Data Manipulation with pandas

Different statistics

import numpy as np
dogs.pivot_table(values="weight_kg", index="color", aggfunc=np.median)
       weight_kg
color           
Black       26.5
Brown       24.0
Gray        17.0
Tan          2.0
White       74.0
Data Manipulation with pandas

Multiple statistics

dogs.pivot_table(values="weight_kg", index="color", aggfunc=[np.mean, np.median])
           mean    median
      weight_kg weight_kg
color                    
Black      26.5      26.5
Brown      24.0      24.0
Gray       17.0      17.0
Tan         2.0       2.0
White      74.0      74.0
Data Manipulation with pandas

Pivot on two variables

dogs.groupby(["color", "breed"])["weight_kg"].mean()
dogs.pivot_table(values="weight_kg", index="color", columns="breed")
breed  Chihuahua  Chow Chow  Labrador  Poodle  Schnauzer  St. Bernard
color                                                                
Black        NaN        NaN      29.0    24.0        NaN          NaN
Brown        NaN       24.0      24.0     NaN        NaN          NaN
Gray         NaN        NaN       NaN     NaN       17.0          NaN
Tan          2.0        NaN       NaN     NaN        NaN          NaN
White        NaN        NaN       NaN     NaN        NaN         74.0
Data Manipulation with pandas

Filling missing values in pivot tables

dogs.pivot_table(values="weight_kg", index="color", columns="breed", fill_value=0)
breed  Chihuahua  Chow Chow  Labrador  Poodle  Schnauzer  St. Bernard
color                                                                
Black          0          0        29      24          0            0
Brown          0         24        24       0          0            0
Gray           0          0         0       0         17            0
Tan            2          0         0       0          0            0
White          0          0         0       0          0           74
Data Manipulation with pandas

Summing with pivot tables

dogs.pivot_table(values="weight_kg", index="color", columns="breed", 
                 fill_value=0, margins=True)
breed  Chihuahua  Chow Chow  Labrador  Poodle  Schnauzer  St. Bernard        All
color                                                                           
Black          0          0        29      24          0            0  26.500000
Brown          0         24        24       0          0            0  24.000000
Gray           0          0         0       0         17            0  17.000000
Tan            2          0         0       0          0            0   2.000000
White          0          0         0       0          0           74  74.000000
All            2         24        26      24         17           74  27.714286
Data Manipulation with pandas

Let's practice!

Data Manipulation with pandas

Preparing Video For Download...