Validate joins and reshape without changing the question

The merging lesson introduces concatenation, joining and merging. This supplement adds the checks that distinguish a successful function call from a successful integration. It assumes that you can identify a table’s key and explain one-to-many relationships. All values in this lesson are invented teaching data.

State the relationship before joining

One institution can have several sessions; each session should refer to one institution. A session table and an institution lookup therefore have a many-to-one relationship on institution_id. The lookup must have at most one matching row per institution. If it contains two matches, the join can multiply observations.

import pandas as pd
sessions = pd.DataFrame({"session_id": [1, 2, 3], "institution_id": ["N", "N", "S"], "visits": [10, 15, 8]})
lookup = pd.DataFrame({"institution_id": ["N", "S"], "region": ["North", "South"]})
joined = sessions.merge(lookup, on="institution_id", how="left", validate="many_to_one", indicator=True)
print(joined)
assert len(joined) == len(sessions)
assert joined["session_id"].is_unique
assert joined["_merge"].eq("both").all()

   session_id institution_id  visits region _merge
0           1              N      10  North   both
1           2              N      15  North   both
2           3              S       8  South   both

The row-count and key checks verify the intended unit. indicator=True exposes whether rows matched; validate checks the declared relationship. See the Pandas merging guide for these options. A left join retains left-side observations, but it does not guarantee that every lookup succeeded.

Investigate failed matches and multiplication

A key may fail because of spelling, missing coverage, incompatible types or genuinely different entities. Do not resolve every failed match with a guessed string edit. Keep an unmatched-record report and ask the producer when the mapping is unclear. Missing keys also need an explicit policy; Pandas can match null keys to one another, unlike the usual SQL null-comparison expectation.

bad_lookup = pd.concat([lookup, lookup.iloc[[0]]], ignore_index=True)
try:
    sessions.merge(bad_lookup, on="institution_id", validate="many_to_one")
except pd.errors.MergeError:
    print("Rejected: institution lookup is not unique")
else:
    raise AssertionError("Expected a cardinality failure")

Rejected: institution lookup is not unique

A rejected merge is useful feedback. Without the constraint, two North sessions would each match two lookup rows. Summing their visits would inflate the result while still producing plausible numbers. A many_to_many validation setting permits such a relationship; it does not establish that multiplication is scientifically appropriate.

Distinguish stacking from matching

Concatenation stacks compatible row sets or aligns columns by index. It does not search for matching entity keys unless you construct the corresponding index deliberately. Before stacking annual files, compare field names, units and key coverage. A column renamed between years can create two partly missing columns instead of one coherent field. Retain a source-year or source-file field so that later anomalies can be traced.

Move between wide and long representations

A wide table stores a variable in several columns, such as one column per year. A long table stores year as a value and has an explicit observation per institution-year. Both can be valid; the task determines which is clearer. Specify the observation key before reshaping and check that it remains unique afterwards.

wide = pd.DataFrame({"institution": ["N", "S"], "2022": [10, 8], "2023": [15, 11]})
long = wide.melt(id_vars="institution", var_name="year", value_name="visits")
long["year"] = long["year"].astype(int)
assert not long.duplicated(["institution", "year"]).any()
restored = long.pivot(index="institution", columns="year", values="visits")
print(long.sort_values(["institution", "year"]))
assert restored.loc["N", 2023] == 15

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

pivot requires one value per index-column combination. pivot_table can aggregate duplicates, but choosing an aggregation is a substantive decision. Use mean only when a mean answers the question; do not select it merely to make an error disappear. The reshaping guide documents melt, pivot, stack and unstack.

Practice and independent check

Remove South from the lookup and report which sessions are unmatched without discarding them. Add two observations for North in 2023 and decide whether they are duplicate records, separate sessions or revisions. Demonstrate why the observation unit determines whether to reject, retain or aggregate them.

Your transfer deliverable is an integration contract: keys, expected cardinality, unmatched policy, pre- and post-join counts, and two checks that would detect an incorrect result. Then export a long table and explain how its key supports the exploratory and visualization work that follows.

Chapter exercisesWorked answers after an attempt