2.2 Activity

Joining, Reshaping & Missing Data¶

Tools: Python, pandas, VS Code and/or Jupyter

Learning objectives¶

By the end of this tutorial, you should be able to:

combine tables using merge();
distinguish inner, left, right, and outer joins;
identify and diagnose unmatched records;
detect duplicate keys and validate join cardinality;
combine datasets using concat();
convert data between wide and long form;
use melt(), pivot(), and pivot_table();
detect and summarize missing data;
apply simple missing-data strategies critically;
validate a multi-step wrangling pipeline.

Part 0 — Set up¶

Create:

notebooks/week04_wrangle_part2.ipynb

At the top:

import pandas as pd
import numpy as np

Create participant information:

participants = pd.DataFrame({
    "id": [101, 102, 103, 104, 105, 106],
    "age": [24, 31, 27, 19, 42, 35],
    "group": ["A", "B", "A", "B", "A", "B"]
})

Create scores:

scores = pd.DataFrame({
    "id": [101, 102, 103, 105, 106, 107],
    "score": [81, 94, 88, 91, 85, 79]
})

Inspect:

participants
scores

Questions¶

Which IDs occur in both tables?
Which ID occurs only in participants?
Which ID occurs only in scores?
What do you expect an inner join to contain?

Do not run a merge yet.

Part 2 — Inner join¶

Run:

inner = participants.merge(
    scores,
    on="id",
    how="inner"
)

Inspect:

inner
inner.shape

Questions¶

Which IDs were removed?
Why?
Did the number of rows match your prediction?

Part 3 — Left join¶

Run:

left = participants.merge(
    scores,
    on="id",
    how="left"
)

Inspect:

left

Questions¶

Which table defines the resulting population?
What happened to ID 104?
Why is its score missing?

Part 4 — Outer join¶

Run:

outer = participants.merge(
    scores,
    on="id",
    how="outer"
)

Inspect:

outer

Challenge¶

Before running a right join, predict which IDs it will contain.

Then:

right = participants.merge(
    scores,
    on="id",
    how="right"
)

Part 5 — Diagnose joins with `indicator=True`¶

Run:

diagnostic = participants.merge(
    scores,
    on="id",
    how="outer",
    indicator=True
)

Inspect:

diagnostic

Then:

diagnostic["_merge"].value_counts()

Questions¶

How many rows matched?
How many occurred only in the participant table?
How many occurred only in the score table?

Challenge¶

Extract only unmatched records.

Part 6 — Check key uniqueness¶

Run:

participants["id"].is_unique
scores["id"].is_unique

Then:

participants["id"].duplicated().sum()
scores["id"].duplicated().sum()

Question¶

Why should this be checked before a one-to-one join?

Part 7 — Validate the merge¶

Run:

validated = participants.merge(
    scores,
    on="id",
    how="left",
    validate="one_to_one"
)

Now create a bad score table:

duplicate_scores = pd.DataFrame({
    "id": [101, 101, 102],
    "score": [81, 83, 94]
})

Try:

participants.merge(
    duplicate_scores,
    on="id",
    validate="one_to_one"
)

Question¶

Why is the error valuable?

Part 8 — Many-to-many multiplication¶

Create:

visits = pd.DataFrame({
    "id": [1, 1, 2],
    "visit": ["pre", "post", "pre"]
})

treatments = pd.DataFrame({
    "id": [1, 1, 2],
    "treatment": ["A", "B", "A"]
})

Run:

visits.merge(
    treatments,
    on="id"
)

Questions¶

Why does ID 1 generate four rows?
Was information duplicated?
Under what circumstances could this be correct?

Part 9 — Concatenate rows¶

Create two semester datasets:

fall = pd.DataFrame({
    "id": [1, 2, 3],
    "semester": ["Fall"] * 3,
    "score": [80, 85, 90]
})

spring = pd.DataFrame({
    "id": [4, 5, 6],
    "semester": ["Spring"] * 3,
    "score": [78, 92, 88]
})

Combine:

all_scores = pd.concat(
    [fall, spring],
    axis=0,
    ignore_index=True
)

Inspect:

all_scores

Question¶

Why is concat() more natural than merge() here?

Part 10 — Concatenating mismatched columns¶

Create:

fall = pd.DataFrame({
    "id": [1, 2],
    "score": [80, 85]
})

spring = pd.DataFrame({
    "id": [3, 4],
    "score": [90, 92],
    "campus": ["Flagstaff", "Flagstaff"]
})

Run:

pd.concat(
    [fall, spring],
    ignore_index=True
)

Question¶

What does pandas do with the column that does not exist in fall?

Part 11 — Wide data¶

Create repeated measurements:

wide = pd.DataFrame({
    "id": [101, 102, 103, 104],
    "group": ["A", "B", "A", "B"],
    "score_pre": [72, 85, 79, 88],
    "score_post": [81, 91, 84, 90]
})

Inspect:

wide

Question¶

How many rows represent each participant?

How are repeated measurements represented?

Part 12 — Wide to long with `melt()`¶

Run:

long = wide.melt(
    id_vars=["id", "group"],
    value_vars=["score_pre", "score_post"],
    var_name="time",
    value_name="score"
)

Inspect:

long

Questions¶

How many rows now represent each participant?
What happened to the original score columns?
What does id_vars mean?
What does value_vars mean?

Part 13 — Clean the melted variable¶

Run:

long["time"] = (
    long["time"]
    .str.replace("score_", "", regex=False)
)

Inspect:

long

Challenge¶

Change:

pre
post

to:

baseline
followup

using:

.replace()

Part 14 — Long to wide with `pivot()`¶

Start from long.

Run:

wide_again = long.pivot(
    index=["id", "group"],
    columns="time",
    values="score"
)

Inspect:

wide_again

Reset the index:

wide_again = wide_again.reset_index()

Question¶

Did you recover the same information as the original wide dataset?

Part 15 — Duplicate combinations and `pivot()`¶

Create:

repeated = pd.DataFrame({
    "id": [1, 1, 1, 2, 2],
    "time": ["pre", "pre", "post", "pre", "post"],
    "score": [70, 74, 82, 80, 88]
})

Try:

repeated.pivot(
    index="id",
    columns="time",
    values="score"
)

Question¶

Why does this fail?

Which assumption is violated?

Part 16 — `pivot_table()`¶

Now use:

summary = repeated.pivot_table(
    index="id",
    columns="time",
    values="score",
    aggfunc="mean"
)

Inspect:

summary

Reflection¶

Why should you investigate duplicate records before deciding that taking the mean is appropriate?

Part 17 — Create missing data¶

Create:

clinical = pd.DataFrame({
    "id": [1, 2, 3, 4, 5, 6],
    "age": [24, 31, np.nan, 45, 28, 39],
    "group": ["A", "B", "A", "B", None, "A"],
    "score": [81, np.nan, 77, 92, 88, np.nan]
})

Inspect:

clinical

Then:

clinical.isna()

and:

clinical.isna().sum()

Calculate percentages:

clinical.isna().mean() * 100

Part 18 — Identify rows with missing data¶

Find rows with missing score:

clinical.loc[
    clinical["score"].isna()
]

Find complete scores:

clinical.loc[
    clinical["score"].notna()
]

Find rows where either age OR score is missing:

clinical.loc[
    clinical[["age", "score"]]
    .isna()
    .any(axis=1)
]

Challenge¶

Find rows where both age and score are observed.

Part 19 — Drop missing observations¶

Run:

complete_score = clinical.dropna(
    subset=["score"]
)

Compare:

len(clinical)
len(complete_score)

Now require both age and score:

complete_cases = clinical.dropna(
    subset=["age", "score"]
)

Questions¶

How many observations were removed?
Has the analysis population changed?
What assumptions might make complete-case analysis problematic?

Part 20 — Filling missing values¶

Create a copy:

filled = clinical.copy()

Fill scores with the mean:

filled["score"] = filled["score"].fillna(
    filled["score"].mean()
)

Compare:

clinical["score"].describe()
filled["score"].describe()

Questions¶

Did the mean change?
Did the standard deviation change?
Why?
Is mean imputation automatically a good statistical strategy?

Part 21 — Missingness introduced by a join¶

Return to:

participants
scores

Create:

joined = participants.merge(
    scores,
    on="id",
    how="left"
)

Find:

joined.loc[
    joined["score"].isna()
]

Question¶

Does a missing score here necessarily mean that the participant failed to provide a score?

What other explanation exists?

Part 22 — Missingness indicator¶

Create:

clinical["score_missing"] = (
    clinical["score"].isna()
)

Inspect:

clinical

Now:

pd.crosstab(
    clinical["group"],
    clinical["score_missing"]
)

Reflection¶

What question does this table help you investigate?

Part 23 — Build a complete wrangling workflow¶

Create demographic data:

demographics = pd.DataFrame({
    "id": [101, 102, 103, 104, 105],
    "age": [24, 31, 27, 42, 36],
    "group": ["A", "B", "A", "B", "A"]
})

Create repeated outcomes:

outcomes = pd.DataFrame({
    "id": [101, 102, 103, 105],
    "pre": [72, 80, 85, 78],
    "post": [81, 87, np.nan, 86]
})

Your task is to produce a long-format analysis dataset containing:

id
age
group
time
score

with all demographic participants preserved.

Part 24 — Step 1: audit the keys¶

Check:

demographics["id"].is_unique
outcomes["id"].is_unique

Compare sets of IDs:

set(demographics["id"]) - set(outcomes["id"])

and:

set(outcomes["id"]) - set(demographics["id"])

Write down what you expect the merge to do.

Part 25 — Step 2: merge¶

Use:

combined = demographics.merge(
    outcomes,
    on="id",
    how="left",
    validate="one_to_one",
    indicator=True
)

Inspect:

combined
combined["_merge"].value_counts()

Remove the diagnostic column after checking:

combined = combined.drop(
    columns="_merge"
)

Part 26 — Step 3: reshape¶

Convert to long format:

analysis = combined.melt(
    id_vars=["id", "age", "group"],
    value_vars=["pre", "post"],
    var_name="time",
    value_name="score"
)

Inspect:

analysis

Part 27 — Step 4: audit missingness¶

Run:

analysis.isna().sum()

Then:

analysis.loc[
    analysis["score"].isna()
]

Questions¶

For each missing score, determine whether it arose because:

an outcome record was absent entirely; or
an outcome row existed but a particular measurement was missing.

These are different data problems.

Part 28 — Step 5: define an analysis dataset¶

Suppose the analysis requires observed scores.

Create:

observed = analysis.dropna(
    subset=["score"]
)

Record:

n_before = len(analysis)
n_after = len(observed)
n_removed = n_before - n_after

Print these quantities.

Reflection¶

Why should this information appear in an analysis report?

Part 29 — Validate the transformation¶

Check:

analysis["id"].nunique()

Compare with:

demographics["id"].nunique()

Check time values:

analysis["time"].value_counts()

Check participant-time uniqueness:

analysis.duplicated(
    subset=["id", "time"]
).sum()

Add assertions:

assert (
    analysis["id"].nunique()
    == demographics["id"].nunique()
)

assert (
    analysis.duplicated(
        subset=["id", "time"]
    ).sum()
    == 0
)

Part 30 — Mini challenge: three files¶

Imagine three monthly extracts:

jan = pd.DataFrame({
    "id": [1, 2],
    "month": ["Jan", "Jan"],
    "score": [80, 85]
})

feb = pd.DataFrame({
    "id": [1, 2],
    "month": ["Feb", "Feb"],
    "score": [82, 88]
})

mar = pd.DataFrame({
    "id": [1, 2],
    "month": ["Mar", "Mar"],
    "score": [84, np.nan]
})

Tasks¶

Concatenate the three tables.
Verify the number of rows.
Identify missing scores.
Pivot into wide form with one row per participant.
Convert back to long form.
Verify that the structure is equivalent to the original combined data.

Part 31 — Written reasoning challenge¶

For each scenario, choose the most appropriate operation.

A¶

Two tables contain the same variables for different months.

merge or concat?

B¶

One table contains demographics and another contains outcomes linked by participant ID.

merge or concat?

C¶

Repeated measurements are stored in score_pre, score_post, and score_followup.

melt or pivot?

D¶

Long data should become one row per participant.

melt or pivot?

E¶

You need every participant from the enrollment table, whether or not an outcome exists.

inner or left join?

Explain each answer.

Part 32 — Git checkpoint¶

Run:

git status

Stage:

git add .

Commit:

git commit -m "Complete Week 4 joining reshaping and missing data"

Push:

git push

Part 33 — Final reflection¶

Answer in Markdown.

1. Merge¶

What is the conceptual difference between an inner join and a left join?

2. Keys¶

Why should you inspect duplicate keys before merging?

3. Validation¶

What does validate="one_to_one" protect against?

4. Concatenation¶

When is concat() more appropriate than merge()?

5. Reshaping¶

What is the difference between wide and long data?

6. Pivoting¶

Why might pivot() fail when pivot_table() succeeds?

7. Missingness¶

Why should missing values introduced by a join be interpreted differently from missing values already present in a source table?

8. Statistical practice¶

Why is dropping missing observations a statistical decision rather than only a programming operation?

Completion checklist¶

Created Week 4 notebook
Performed an inner join
Performed a left join
Performed a right join
Performed an outer join
Used indicator=True
Checked duplicate keys
Used validate=
Explored a many-to-many merge
Concatenated rows
Examined mismatched columns during concatenation
Created wide-format data
Used melt()
Used pivot()
Used pivot_table()
Detected missing values
Calculated missing-data percentages
Used dropna()
Used fillna()
Compared distributions before and after filling
Created a missingness indicator
Completed the full merge → reshape → missingness workflow
Added validation assertions
Completed the monthly-data challenge
Committed work to Git
Pushed work to GitHub

What you should now understand¶

Separate datasets
       ↓
Identify keys
       ↓
Check relationships
       ↓
Merge / concatenate
       ↓
Audit
       ↓
Reshape
       ↓
Inspect missingness
       ↓
Make statistical decisions
       ↓
Validate
       ↓
Analysis-ready data

Next week we will work with external data sources, including SQL databases, APIs, JSON, HTML, and binary file formats.

Joining, Reshaping & Missing Data¶

Learning objectives¶

Part 0 — Set up¶

Part 1 — Create two related tables¶

Questions¶

Part 2 — Inner join¶

Questions¶

Part 3 — Left join¶

Questions¶

Part 4 — Outer join¶

Challenge¶

Part 5 — Diagnose joins with indicator=True¶

Questions¶

Challenge¶

Part 6 — Check key uniqueness¶

Question¶

Part 7 — Validate the merge¶

Question¶

Part 8 — Many-to-many multiplication¶

Questions¶

Part 9 — Concatenate rows¶

Question¶

Part 10 — Concatenating mismatched columns¶

Question¶

Part 11 — Wide data¶

Question¶

Part 12 — Wide to long with melt()¶

Questions¶

Part 13 — Clean the melted variable¶

Challenge¶

Part 14 — Long to wide with pivot()¶

Question¶

Part 15 — Duplicate combinations and pivot()¶

Question¶

Part 16 — pivot_table()¶

Reflection¶

Part 17 — Create missing data¶

Part 18 — Identify rows with missing data¶

Challenge¶

Part 19 — Drop missing observations¶

Questions¶

Part 20 — Filling missing values¶

Questions¶

Part 21 — Missingness introduced by a join¶

Question¶

Part 22 — Missingness indicator¶

Reflection¶

Part 23 — Build a complete wrangling workflow¶

Part 24 — Step 1: audit the keys¶

Part 25 — Step 2: merge¶

Part 26 — Step 3: reshape¶

Part 27 — Step 4: audit missingness¶

Questions¶

Part 28 — Step 5: define an analysis dataset¶

Reflection¶

Part 29 — Validate the transformation¶

Part 30 — Mini challenge: three files¶

Tasks¶

Part 31 — Written reasoning challenge¶

A¶

B¶

C¶

D¶

E¶

Part 32 — Git checkpoint¶

Part 33 — Final reflection¶

1. Merge¶

2. Keys¶

3. Validation¶

4. Concatenation¶

5. Reshaping¶

6. Pivoting¶

7. Missingness¶

8. Statistical practice¶

Completion checklist¶

What you should now understand¶

Part 5 — Diagnose joins with `indicator=True`¶

Part 12 — Wide to long with `melt()`¶

Part 14 — Long to wide with `pivot()`¶

Part 15 — Duplicate combinations and `pivot()`¶

Part 16 — `pivot_table()`¶