Activity 2.2

This page is an ipython notebook, meaning you can download it and run the executable cells. It directly follows Activity 2.2. Reference answers appear after the questions, challenges, predictions, and reflections. They show one reasonable solution or interpretation, but other correct approaches may exist.

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:

At the top:

Part 1 — Create two related tables¶

Create participant information:

Create scores:

Inspect:

Questions¶

1. Which IDs occur in both tables?

2. Which ID occurs only in participants?

3. Which ID occurs only in scores?

4. What do you expect an inner join to contain?

Do not run a merge yet.

Reference answer¶

The IDs in both tables are 101, 102, 103, 105, 106.
ID 104 occurs only in participants, and ID 107 occurs only in scores.

An inner join should therefore contain the five shared IDs only.

Part 2 — Inner join¶

Run:

Inspect:

Questions¶

1. Which IDs were removed?

2. Why?

3. Did the number of rows match your prediction?

Reference answer¶

The inner join contains IDs 101, 102, 103, 105, 106.

IDs 104 and 107 are removed because an inner join keeps only keys that appear in both tables. The result has 5 rows, matching the prediction.

Part 3 — Left join¶

Run:

Inspect:

Questions¶

1. Which table defines the resulting population?

2. What happened to ID 104?

3. Why is its score missing?

Reference answer¶

The left table, participants, defines the retained population.

ID 104 remains because it appears in participants, but its score is missing because there is no matching row for ID 104 in scores.

Part 4 — Outer join¶

Run:

Inspect:

Challenge¶

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

Then:

Reference answer¶

An outer join contains every ID appearing in either table:

101, 102, 103, 104, 105, 106, 107

A right join keeps every ID in scores, so it contains:

101, 102, 103, 105, 106, 107

Part 5 — Diagnose joins with indicator=True¶

Run:

Inspect:

Then:

Questions¶

1. How many rows matched?

2. How many occurred only in the participant table?

3. How many occurred only in the score table?

Challenge¶

Extract only unmatched records.

Reference answer¶

The _merge counts are:

both          5
left_only     1
right_only    1

To extract unmatched records:

diagnostic.loc[
    diagnostic["_merge"] != "both"
]

These are IDs 104 and 107.

Part 6 — Check key uniqueness¶

Run:

Then:

Question¶

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

Reference answer¶

Both participants["id"] and scores["id"] are unique, and each has zero duplicated IDs.

This should be checked before a one-to-one join because validate="one_to_one" assumes each key appears at most once in each table. Duplicate keys would violate that relationship and could multiply rows unexpectedly.

Part 7 — Validate the merge¶

Run:

Now create a bad score table:

Try:

Question¶

Why is the error valuable?

Reference answer¶

The validated left merge succeeds because both key columns are unique.

The merge with duplicate_scores raises an error because ID 101 appears twice in the right table, so the relationship is no longer one-to-one.

The error is valuable because it catches a structural data problem rather than silently producing duplicated rows.

Part 8 — Many-to-many multiplication¶

Create:

Run:

Questions¶

1. Why does ID 1 generate four rows?

2. Was information duplicated?

3. Under what circumstances could this be correct?

Reference answer¶

ID 1 appears twice in visits and twice in treatments, so the merge creates all pairwise combinations: 2 × 2 = 4 rows.

The values are repeated across rows, but pandas is not arbitrarily duplicating data; it is representing every valid key match.

This can be correct when both tables genuinely contain multiple records per key and the Cartesian combinations are meaningful. Otherwise, it may signal that the join keys are incomplete.

Part 9 — Concatenate rows¶

Create two semester datasets:

Combine:

Inspect:

Question¶

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

Reference answer¶

concat() is more natural because the two tables have the same variables and represent different observations from different semesters. The goal is to stack rows vertically, not match rows by a shared key.

Part 10 — Concatenating mismatched columns¶

Create:

Run:

Question¶

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

Reference answer¶

Pandas creates the union of the columns. Because campus does not exist in fall, the Fall rows receive missing values (NaN) for campus.

Part 11 — Wide data¶

Create repeated measurements:

Inspect:

Question¶

How many rows represent each participant?

How are repeated measurements represented?

Reference answer¶

In the wide dataset, each participant is represented by one row.

Repeated measurements are stored in separate columns: score_pre and score_post.

Part 12 — Wide to long with melt()¶

Run:

Inspect:

Questions¶

1. How many rows now represent each participant?

2. What happened to the original score columns?

3. What does id_vars mean?

4. What does value_vars mean?

Reference answer¶

After melt(), each participant is represented by two rows, one for each time point.

The original score_pre and score_post columns are converted into values in the new time column, while their measurements go into the new score column.

• id_vars are columns that identify an observational unit and remain repeated across the new rows.

• value_vars are the columns being reshaped from wide form into long form.

Part 13 — Clean the melted variable¶

Run:

Inspect:

Challenge¶

Change:

to:

using:

Reference answer¶

After:

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

the values become simply:

pre
post

This is often preferable because time should describe the measurement occasion rather than repeat the variable name score.

Part 14 — Long to wide with pivot()¶

Start from long.

Run:

Inspect:

Reset the index:

Question¶

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

Reference answer¶

Yes. After pivoting back to wide form, the same participant/time/score information is recovered.

The presentation is not necessarily byte-for-byte identical—for example, column names/index structure may differ until reset_index()—but the underlying information is the same.

Part 15 — Duplicate combinations and pivot()¶

Create:

Try:

Question¶

Why does this fail?

Which assumption is violated?

Reference answer¶

pivot() fails because ID 1 has two rows with the same (id, time) combination for time="pre".

pivot() assumes that every index/column combination maps to exactly one value. Duplicate combinations violate that uniqueness assumption.

Part 16 — pivot_table()¶

Now use:

Inspect:

Reflection¶

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

Reference answer¶

pivot_table(..., aggfunc="mean") succeeds because it resolves duplicate combinations by aggregating them.

You should investigate the duplicates first because averaging may or may not make scientific sense. Duplicates could represent repeated measurements, data-entry errors, distinct visits that need another key, or genuinely replicated observations. Aggregation should follow from the data-generating process, not merely from what makes the code run.

Part 17 — Create missing data¶

Create:

Inspect:

Then:

and:

Calculate percentages:

Reference answer¶

The missingness counts are:

age      1
group    1
score    2

Missing data are represented as NaN for numeric columns and None/missing for the object column.

Part 18 — Identify rows with missing data¶

Find rows with missing score:

Find complete scores:

Find rows where either age OR score is missing:

Challenge¶

Find rows where both age and score are observed.

Reference answer¶

Rows with missing score are IDs 2 and 6.

Rows with observed score are IDs 1, 3, 4, 5.

Rows where both age and score are observed can be selected with:

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

These are IDs 1, 4, 5.

Part 19 — Drop missing observations¶

Run:

Compare:

Now require both age and score:

Questions¶

1. How many observations were removed?

2. Has the analysis population changed?

3. What assumptions might make complete-case analysis problematic?

Reference answer¶

Dropping rows with missing score removes 2 observations, reducing the dataset from 6 to 4 rows.

Yes, the analysis population changes: only participants with observed scores remain.

Complete-case analysis can be problematic if missingness is related to participant characteristics or outcomes. In that case, the retained observations may not be representative of the original population.

Part 20 — Filling missing values¶

Create a copy:

Fill scores with the mean:

Compare:

Questions¶

1. Did the mean change?

2. Did the standard deviation change?

3. Why?

4. Is mean imputation automatically a good statistical strategy?

Reference answer¶

Mean imputation leaves the mean approximately unchanged because the missing scores are replaced with the observed mean.

The standard deviation usually decreases because imputed values are placed exactly at the center of the distribution, adding no new spread.

Mean imputation is not automatically a good strategy: it can understate variability, distort relationships between variables, and give an unrealistic impression that imputed values were actually observed.

Part 21 — Missingness introduced by a join¶

Return to:

Create:

Find:

Question¶

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

What other explanation exists?

Reference answer¶

No. A missing score after the left join does not necessarily mean the participant failed to provide a score.

For ID 104, the missing value arises because there is no matching record for that ID in the scores table. This is join-induced missingness, which is conceptually different from a missing value that was already present in a source record.

Part 22 — Missingness indicator¶

Create:

Inspect:

Now:

Reflection¶

What question does this table help you investigate?

Reference answer¶

The cross-tabulation helps investigate whether score missingness differs across groups.

For example, if one group has a much larger proportion of missing scores, missingness may be related to group membership rather than occurring uniformly across the dataset.

Part 23 — Build a complete wrangling workflow¶

Create demographic data:

Create repeated outcomes:

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

with all demographic participants preserved.

Reference answer¶

The goal of this workflow is to preserve all demographic participants while attaching outcome information where available.

That implies a left join from demographics to outcomes, followed by explicit checks for unmatched IDs and missing measurements.

Part 24 — Step 1: audit the keys¶

Check:

Compare sets of IDs:

and:

Write down what you expect the merge to do.

Reference answer¶

Both ID columns are unique.

The set difference:

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

returns {104}.

The reverse difference is empty.

Therefore, before merging, we should expect every demographic participant to remain, with ID 104 having missing outcome values.

Part 25 — Step 2: merge¶

Use:

Inspect:

Remove the diagnostic column after checking:

Reference answer¶

The left merge should contain 5 rows.

The _merge indicator should show 4 both rows and 1 left_only row (ID 104). No row should be right_only.

Part 26 — Step 3: reshape¶

Convert to long format:

Inspect:

Reference answer¶

After melting pre and post, each participant contributes two rows, so the long dataset has:

5 participants × 2 time points = 10 rows

The columns are id, age, group, time, and score.

Part 27 — Step 4: audit missingness¶

Run:

Then:

Questions¶

For each missing score, determine whether it arose because:

1. an outcome record was absent entirely; or

2. an outcome row existed but a particular measurement was missing.

These are different data problems.

Reference answer¶

There are two different reasons for missing scores:

• ID 104 has missing pre and post because there was no outcome record at all for that participant.

• ID 103 has a missing post because an outcome row exists, but that particular measurement is missing.

Those situations should not automatically be interpreted or handled in the same way.

Part 28 — Step 5: define an analysis dataset¶

Suppose the analysis requires observed scores.

Create:

Record:

Print these quantities.

Reflection¶

Why should this information appear in an analysis report?

Reference answer¶

The long dataset has 10 rows before dropping missing scores.

There are 3 missing scores: ID 103 at post, plus ID 104 at both pre and post.

Therefore:

n_before  = 10
n_after   = 7
n_removed = 3

These quantities should appear in an analysis report because dropping observations changes the effective sample and makes the inclusion/exclusion process transparent.

Part 29 — Validate the transformation¶

Check:

Compare with:

Check time values:

Check participant-time uniqueness:

Add assertions:

Reference answer¶

The validation checks should show:

• analysis["id"].nunique() = 5;

• demographics["id"].nunique() = 5;

• each time point appears 5 times;

• there are zero duplicated (id, time) combinations.

The assertions therefore pass.

Part 30 — Mini challenge: three files¶

Imagine three monthly extracts:

Tasks¶

1. Concatenate the three tables.

2. Verify the number of rows.

3. Identify missing scores.

4. Pivot into wide form with one row per participant.

5. Convert back to long form.

6. Verify that the structure is equivalent to the original combined data.

Reference answer¶

One solution is:

combined_months = pd.concat(
    [jan, feb, mar],
    ignore_index=True
)

assert len(combined_months) == 6

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

monthly_wide = combined_months.pivot(
    index="id",
    columns="month",
    values="score"
).reset_index()

monthly_long = monthly_wide.melt(
    id_vars="id",
    value_vars=["Jan", "Feb", "Mar"],
    var_name="month",
    value_name="score"
)

assert len(monthly_long) == len(combined_months)

The only missing score is participant 2 in March.

Part 31 — Written reasoning challenge¶

For each scenario, choose the most appropriate operation.

A¶

Two tables contain the same variables for different months.

B¶

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

C¶

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

D¶

Long data should become one row per participant.

E¶

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

Explain each answer.

Reference answer¶

• A: concat() — same variables, different monthly rows.

• B: merge() — different variables linked by participant ID.

• C: melt() — repeated-measure columns need to become rows.

• D: pivot() — long observations need to become one row per participant.

• E: left join — every enrollment participant must be retained even if no outcome is available.

Part 32 — Git checkpoint¶

Run:

Stage:

Commit:

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¶

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

Reference answer¶

1. Inner vs. left join: an inner join keeps only matched keys; a left join keeps every key from the left table and fills unmatched right-side values with missing data.

2. Duplicate keys: they determine join cardinality and can unexpectedly multiply rows.

3. validate="one_to_one": it raises an error if either table has duplicate merge keys.

4. concat() vs. merge(): use concat() to stack datasets with compatible structure; use merge() to combine variables using shared keys.

5. Wide vs. long: wide data put repeated measurements in separate columns; long data put repeated measurements in separate rows.

6. pivot() vs. pivot_table(): pivot() requires unique index/column combinations; pivot_table() can aggregate duplicates.

7. Join-induced missingness: it may indicate an unmatched record rather than an unobserved measurement in the source.

8. Dropping missing data: it changes the analysis population and can affect bias, precision, and interpretation, so it is a statistical decision.