Lecture 15: Data wrangling in Python

Data wrangling ideas so far

• choose certain rows and columns
• calculate summary statistics
• group rows together
• create new columns
• apply functions across columns
• reshape data by pivoting

These ideas are language-agnostic! The implementation is just a bit different

Titanic data

import pandas as pd
import numpy as np
titanic = pd.read_csv("https://raw.githubusercontent.com/pandas-dev/pandas/main/doc/data/titanic.csv")

titanic

     PassengerId  Survived  Pclass  ...     Fare Cabin  Embarked
0              1         0       3  ...   7.2500   NaN         S
1              2         1       1  ...  71.2833   C85         C
2              3         1       3  ...   7.9250   NaN         S
3              4         1       1  ...  53.1000  C123         S
4              5         0       3  ...   8.0500   NaN         S
..           ...       ...     ...  ...      ...   ...       ...
886          887         0       2  ...  13.0000   NaN         S
887          888         1       1  ...  30.0000   B42         S
888          889         0       3  ...  23.4500   NaN         S
889          890         1       1  ...  30.0000  C148         C
890          891         0       3  ...   7.7500   NaN         Q

[891 rows x 12 columns]

Basic information

titanic.shape

(891, 12)

titanic.columns

Index(['PassengerId', 'Survived', 'Pclass', 'Name', 'Sex', 'Age', 'SibSp',
       'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked'],
      dtype='object')

Choosing a column

titanic['Pclass']

0      3
1      1
2      3
3      1
4      3
      ..
886    2
887    1
888    3
889    1
890    3
Name: Pclass, Length: 891, dtype: int64

Multiple columns

titanic[['Pclass', 'Survived']]

     Pclass  Survived
0         3         0
1         1         1
2         3         1
3         1         1
4         3         0
..      ...       ...
886       2         0
887       1         1
888       3         0
889       1         1
890       3         0

[891 rows x 2 columns]

Alternative way to choose columns

titanic.filter(['Pclass', 'Survived'])

     Pclass  Survived
0         3         0
1         1         1
2         3         1
3         1         1
4         3         0
..      ...       ...
886       2         0
887       1         1
888       3         0
889       1         1
890       3         0

[891 rows x 2 columns]

What would the equivalent R code be?

Alternative way to choose columns

titanic.filter(['Pclass', 'Survived'])

     Pclass  Survived
0         3         0
1         1         1
2         3         1
3         1         1
4         3         0
..      ...       ...
886       2         0
887       1         1
888       3         0
889       1         1
890       3         0

[891 rows x 2 columns]

What would the equivalent R code be?

titanic |>
  select(Pclass, Survived)

Choosing rows

Suppose we only want the rows for the first-class passengers:

titanic[titanic['Pclass'] == 1]

     PassengerId  Survived  Pclass  ...     Fare        Cabin  Embarked
1              2         1       1  ...  71.2833          C85         C
3              4         1       1  ...  53.1000         C123         S
6              7         0       1  ...  51.8625          E46         S
11            12         1       1  ...  26.5500         C103         S
23            24         1       1  ...  35.5000           A6         S
..           ...       ...     ...  ...      ...          ...       ...
871          872         1       1  ...  52.5542          D35         S
872          873         0       1  ...   5.0000  B51 B53 B55         S
879          880         1       1  ...  83.1583          C50         C
887          888         1       1  ...  30.0000          B42         S
889          890         1       1  ...  30.0000         C148         C

[216 rows x 12 columns]

Multiple conditions

We can also choose only the first class passengers who survived:

titanic[(titanic['Pclass'] == 1) & (titanic['Survived'] == 1)]

     PassengerId  Survived  Pclass  ...      Fare Cabin  Embarked
1              2         1       1  ...   71.2833   C85         C
3              4         1       1  ...   53.1000  C123         S
11            12         1       1  ...   26.5500  C103         S
23            24         1       1  ...   35.5000    A6         S
31            32         1       1  ...  146.5208   B78         C
..           ...       ...     ...  ...       ...   ...       ...
862          863         1       1  ...   25.9292   D17         S
871          872         1       1  ...   52.5542   D35         S
879          880         1       1  ...   83.1583   C50         C
887          888         1       1  ...   30.0000   B42         S
889          890         1       1  ...   30.0000  C148         C

[136 rows x 12 columns]

Alternative syntax

titanic.query('Pclass == 1 & Survived == 1')

     PassengerId  Survived  Pclass  ...      Fare Cabin  Embarked
1              2         1       1  ...   71.2833   C85         C
3              4         1       1  ...   53.1000  C123         S
11            12         1       1  ...   26.5500  C103         S
23            24         1       1  ...   35.5000    A6         S
31            32         1       1  ...  146.5208   B78         C
..           ...       ...     ...  ...       ...   ...       ...
862          863         1       1  ...   25.9292   D17         S
871          872         1       1  ...   52.5542   D35         S
879          880         1       1  ...   83.1583   C50         C
887          888         1       1  ...   30.0000   B42         S
889          890         1       1  ...   30.0000  C148         C

[136 rows x 12 columns]

What would the equivalent R code be?

Alternative syntax

titanic.query('Pclass == 1 & Survived == 1')

     PassengerId  Survived  Pclass  ...      Fare Cabin  Embarked
1              2         1       1  ...   71.2833   C85         C
3              4         1       1  ...   53.1000  C123         S
11            12         1       1  ...   26.5500  C103         S
23            24         1       1  ...   35.5000    A6         S
31            32         1       1  ...  146.5208   B78         C
..           ...       ...     ...  ...       ...   ...       ...
862          863         1       1  ...   25.9292   D17         S
871          872         1       1  ...   52.5542   D35         S
879          880         1       1  ...   83.1583   C50         C
887          888         1       1  ...   30.0000   B42         S
889          890         1       1  ...   30.0000  C148         C

[136 rows x 12 columns]

What would the equivalent R code be?

titanic |>
  filter(Pclass == 1 & Survived == 1)

Calculating summary statistics

titanic.agg({'Survived': 'mean'})

Survived    0.383838
dtype: float64

titanic.agg({'Survived': np.mean})

Survived    0.383838
dtype: float64

Multiple summary statistics

titanic.agg({'Survived': ['mean', 'std']})

      Survived
mean  0.383838
std   0.486592

Summary statistics for multiple columns

titanic.agg({'Survived': ['mean', 'std'], 'Age': ['mean', 'std']})

      Survived        Age
mean  0.383838  29.699118
std   0.486592  14.526497

titanic[['Survived', 'Age']].agg(['mean', 'std'])

      Survived        Age
mean  0.383838  29.699118
std   0.486592  14.526497

Grouping and summarizing

titanic.groupby(by = ['Pclass', 'Sex']).agg({'Survived': 'mean'})

               Survived
Pclass Sex             
1      female  0.968085
       male    0.368852
2      female  0.921053
       male    0.157407
3      female  0.500000
       male    0.135447

(titanic.groupby(by = ['Pclass', 'Sex'])
        .agg(survival_rate = ('Survived', 'mean')))

               survival_rate
Pclass Sex                  
1      female       0.968085
       male         0.368852
2      female       0.921053
       male         0.157407
3      female       0.500000
       male         0.135447

Note: Splitting longer chains across multiple lines

(titanic.groupby(by = ['Pclass', 'Sex'])
        .agg(survival_rate = ('Survived', 'mean')))

               survival_rate
Pclass Sex                  
1      female       0.968085
       male         0.368852
2      female       0.921053
       male         0.157407
3      female       0.500000
       male         0.135447

What would the equivalent R code be?

Grouping and summarizing

(titanic.groupby(by = ['Pclass', 'Sex'])
        .agg(survival_rate = ('Survived', 'mean')))

               survival_rate
Pclass Sex                  
1      female       0.968085
       male         0.368852
2      female       0.921053
       male         0.157407
3      female       0.500000
       male         0.135447

What would the equivalent R code be?

titanic |> 
  group_by(Pclass, Sex) |> 
  summarize(survival_rate = mean(Survived))

Class activity

https://sta279-f23.github.io/class_activities/ca_lecture_15.html