Chapter 9 worked answers: Combining and Reshaping Data

Compare your reasoning after an attempt. Different valid implementations are welcome when they preserve the observation set and the stated meaning.

B2-09-E01 · Reject a multiplying lookup

Show that the declared many-to-one merge fails instead of returning four apparent observations.

Each left row has two matches. A cardinality assertion exposes the violated lookup contract.

left=pd.DataFrame({"id":[1,2],"key":["N","N"]})
right=pd.DataFrame({"key":["N","N"],"region":["a","b"]})
try:
    left.merge(right,on="key",validate="many_to_one")
except pd.errors.MergeError:
    print("rejected")
else:
    raise AssertionError("Expected key violation")

rejected

B2-09-E02 · Retain unmatched observations

Use a left merge and report the unmatched key without discarding it.

Retaining S makes the missing lookup coverage visible. An inner join would change the observation set.

left=pd.DataFrame({"key":["N","S"]})
right=pd.DataFrame({"key":["N"],"region":["North"]})
result=left.merge(right,on="key",how="left",indicator=True,validate="many_to_one")
unmatched=result.loc[result["_merge"].eq("left_only"),"key"].tolist()
assert unmatched==["S"]
print(unmatched)

['S']

B2-09-E03 · Reshape a unique key

Create four institution-year observations and verify the combined key.

Year becomes a value rather than being embedded in column names.

wide=pd.DataFrame({"institution":["N","S"],"2022":[10,8],"2023":[15,11]})
long=wide.melt(id_vars="institution",var_name="year",value_name="visits")
assert len(long)==4 and not long.duplicated(["institution","year"]).any()
print(long)

  institution  year  visits
0           N  2022      10
1           S  2022       8
2           N  2023      15
3           S  2023      11

Return to practice

Change one supplied input, predict the effect and rerun your own implementation. Return to the exercise prompts to identify a task you can now explain independently.

Chapter exercisesWorked answers after an attempt