Combining Datasets - Concat and Merge

Context

Two tables combine in two different ways. Stacking places the rows of one under the rows of the other, for pieces of the same table, and a merge pairs each row of one table with the rows of another that hold the same value in a key column, so the facts of both end up on one row.

Data rarely arrives as one file. Listings come in monthly exports that have to become one training table, and the facts about a neighborhood, its region and its distance to the center, live once in a lookup table that every listing needs a copy of. Most model inputs built from more than one source pass through one of these two operations, and the model of how long a listing stays on the market gets its distance to the center this way.

The worst failure here makes the table bigger. A key that appears twice in the lookup returns every matching listing twice, each of them counts twice in every row count and every sum it enters, and nothing raises, because a bigger table is not an error. An inner merge drops the listings whose key has no match, a stack repeats row labels, and a column spelled differently in one export becomes a second, mostly empty column. Section 2.1 met the same join failures in SQL, and this lesson meets them in pandas, where one argument can refuse the bad join outright.

Goal

• Stack frames with pd.concat, and resolve the repeated labels with ignore_index or keys.
• Merge a lookup onto a table with merge, naming the key and the suffixes of overlapping columns.
• Predict the rows inner, left, right and outer keep, and audit a merge with indicator=True.
• Detect a duplicated lookup key before merging, and guard every lookup merge with validate="many_to_one".

Prerequisites

Optional, and the lesson stands without them. They are here if you want to see two tables become one before you join any.

• YouTube - "How do I merge DataFrames in pandas?" by Data School. What happens during a merge, and the four types of join compared.
• Official docs - pandas User Guide, "Merge, join, concatenate and compare", the "Merge types" section. The left, right, outer and inner joins, each drawn as two small tables and their result.

Table of Contents

• concat - Stacking Tables
• merge - Joining on a Key
• Join Types and indicator
• Guarding the Join

Theory

This lesson's download is regions.csv, a lookup of the neighborhoods: each row holds a neighborhood, its region and its distance to the center in kilometers, the same seven rows as Section 2.1's regions table. Save it into ~/dev/bootcamp beside housing.csv.

Nothing to install. Every Theory block below goes into one script, combine.py, saved in ~/dev/bootcamp and run with python3 combine.py, each block added under the one before it.

concat - Stacking Tables

What. pd.concat([a, b]) stacks frames: the rows of b go under the rows of a, and with axis=1 the frames are placed side by side instead, rows paired by index label. It reads no key column. Why. Data arrives in pieces of the same kind, one export per month or one file per source, and the analysis needs them as one table. When. When the pieces hold the same kind of record with the same columns. Two tables that describe different things and share a key are a merge.

Two monthly exports built in code, each numbered from 0 the way a freshly read file is, show what a stack keeps.

import pandas as pd

march = pd.DataFrame({
    "neighborhood": ["Downtown", "Riverside", "Lakeview"],
    "price": [268_000, 281_000, 437_000],
})
april = pd.DataFrame({
    "neighborhood": ["Hillcrest", "Southgate"],
    "price": [150_000, 203_000],
})
both = pd.concat([march, april])
print(both)
print(both.loc[0])

  neighborhood   price
0     Downtown  268000
1    Riverside  281000
2     Lakeview  437000
0    Hillcrest  150000
1    Southgate  203000
  neighborhood   price
0     Downtown  268000
0    Hillcrest  150000

The stack kept each row's original label, so 0 and 1 now appear twice and both.loc[0] returns two listings. ignore_index=True renumbers the result instead, and keys= labels each piece, which becomes the outer level of 2.6.2's two-level index, so one export stays reachable by name.

renumbered = pd.concat([march, april], ignore_index=True)
print(renumbered.index.tolist())
by_month = pd.concat([march, april], keys=["march", "april"])
print(by_month.loc["april"])

[0, 1, 2, 3, 4]
  neighborhood   price
0    Hillcrest  150000
1    Southgate  203000

A column present in only one piece is not an error. concat keeps every column any piece has and fills each cell a piece could not supply with a missing value, so a column renamed in one export becomes a second column, mostly empty.

may = pd.DataFrame({
    "neighborhood": ["Eastgate"],
    "list_price": [476_000],
})
spring = pd.concat([march, april, may], ignore_index=True)
print(spring)
print(spring.isna().sum().to_dict())

  neighborhood     price  list_price
0     Downtown  268000.0         NaN
1    Riverside  281000.0         NaN
2     Lakeview  437000.0         NaN
3    Hillcrest  150000.0         NaN
4    Southgate  203000.0         NaN
5     Eastgate       NaN    476000.0
{'neighborhood': 0, 'price': 1, 'list_price': 5}

With axis=1 the frames sit side by side, and rows are paired by index label, 2.4.1's alignment, so a label one side lacks gets a missing value.

distance = pd.Series([1.2, 3.5], name="distance_km")
print(pd.concat([march, distance], axis=1))

  neighborhood   price  distance_km
0     Downtown  268000          1.2
1    Riverside  281000          3.5
2     Lakeview  437000          NaN

DON'T:

• DON'T reach into a stack with .loc before checking its labels, both.loc[0] returned two listings because each piece brought its own 0.
• DON'T trust a stack's columns to line up, a column spelled differently in one piece becomes a new column without a warning, so count the columns and the gaps after every concat.
• DON'T use concat(axis=1) to combine tables that share a key column, it pairs index labels, not keys, and pairing by key is a merge.

merge - Joining on a Key

What. listings.merge(regions, on="neighborhood", how="left") pairs each row of the left table with the rows of the right table that hold the same value in the key column, and returns their columns side by side. Why. A fact about a neighborhood belongs in one small table, stored once and changed once, and merge attaches it to every listing that needs it, the reason Lesson 2.1.2 kept regions in their own table. When. When two tables describe different things and share a key, a listing and its neighborhood.

regions.csv, the download saved at the start of Theory, is the lookup. The listings are loaded and cleaned as in Lesson 2.6.1.

listings = pd.read_csv("housing.csv").drop_duplicates()
extreme = (
    (listings["price"] > 2_000_000)
    | (listings["area_sqm"] > 1_000)
    | (listings["rooms"] > 20)
)
listings = listings[~extreme].reset_index(drop=True)
regions = pd.read_csv("regions.csv")
print(regions)

  neighborhood   region  distance_to_center_km
0     Downtown  Central                    1.2
1    Riverside  Central                    3.5
2    Riverside  Central                    3.7
3    Hillcrest    North                    7.8
4     Eastgate     East                    5.1
5     Northend    North                    9.4
6     Lakeview     West                    NaN

merge is Lesson 2.1.2's join written in pandas. on="neighborhood" names the column both tables share, the ON condition of that lesson, and how="left" is its LEFT JOIN.

merged = listings.merge(regions, on="neighborhood", how="left")
print(merged.columns.tolist())
print(merged[["neighborhood", "price", "region", "distance_to_center_km"]].head(4))

['price', 'area_sqm', 'rooms', 'year_built', 'neighborhood', 'condition', 'listing_date', 'region', 'distance_to_center_km']
  neighborhood     price   region  distance_to_center_km
0     Lakeview  437000.0     West                    NaN
1     Downtown  268000.0  Central                    1.2
2    Southgate  268000.0      NaN                    NaN
3    Hillcrest       NaN    North                    7.8

Every listing gained the two lookup columns. Row 2 is a Southgate listing, the lookup has no Southgate row, and a left merge keeps the listing with its new columns empty, as 2.1.2's LEFT JOIN left them NULL. Row 0 is a Lakeview listing that did match, and its distance is empty because the lookup's own Lakeview row has none.

When both tables hold a column of the same name that is not the key, merge keeps both and marks them _x for the left and _y for the right. A neighborhood median from 2.6.2, merged back onto the listings, shows it, and suffixes= names the two instead.

medians = listings.groupby("neighborhood")["price"].median().reset_index()
plain = listings.merge(medians, on="neighborhood", how="left")
print([c for c in plain.columns if c.startswith("price")])
named = listings.merge(
    medians,
    on="neighborhood",
    how="left",
    suffixes=("", "_nb_median"),
)
print(named[["neighborhood", "price", "price_nb_median"]].head(3))

['price_x', 'price_y']
  neighborhood     price  price_nb_median
0     Lakeview  437000.0         391000.0
1     Downtown  268000.0         254000.0
2    Southgate  268000.0         212000.0

The empty left suffix kept the listing's own price under its name. Leaving out on= would not have been a shortcut: merge then joins on every column the two tables share, here neighborhood and price together, and with its default how="inner" it keeps only the listings whose price equals their neighborhood's median.

print(len(listings.merge(medians)))  # 9

DON'T:

• DON'T leave the key for merge to guess, it joins on every shared column, and listings.merge(medians) kept 9 listings out of 200.
• DON'T carry _x and _y past the line that made them, name the columns with suffixes= so the result explains itself.

Join Types and indicator

What. how= decides which rows survive a merge. inner keeps only rows that matched on both sides, left keeps every left row, right every right row, and outer everything from both. indicator=True adds a _merge column saying where each row came from: both, left_only or right_only. Why. The join type decides which listings are in every statistic computed afterwards, and the indicator is the record of what the join actually did. When. left to add columns to a table that must keep every row, inner only when a row without a match is unusable, and outer with indicator=True to audit a join before trusting it.

for how in ["inner", "left", "right", "outer"]:
    joined = listings.merge(regions, on="neighborhood", how=how)
    southgate = (joined["neighborhood"] == "Southgate").sum()
    print(how, len(joined), southgate)

inner 191 0
left 227 36
right 191 0
outer 227 36

Southgate's 36 listings are in the left and outer results and gone from the inner one, without a word, the loss 2.1.2's INNER JOIN produced. right keeps every lookup row and matches inner here, and outer matches left, because every neighborhood in the lookup has listings. The row counts above are wrong in a way no join type explains: a left merge should return each left row once, and 200 listings came back as 227. Guarding the Join finds the 27.

audit = listings.merge(regions, on="neighborhood", how="outer", indicator=True)
print(audit["_merge"].value_counts())

_merge
both          191
left_only      36
right_only      0
Name: count, dtype: int64

left_only counts the listings that found no lookup row, all of them Southgate, and right_only the lookup rows no listing used. The indicator also separates the two kinds of missing distance the first merge above showed, Southgate's and Lakeview's, 65 rows in all.

no_distance = audit[audit["distance_to_center_km"].isna()]
# {'left_only': 36, 'both': 29, 'right_only': 0}
print(no_distance["_merge"].value_counts().to_dict())

DON'T:

• DON'T choose inner because its result looks tidy, it removed Southgate's 36 listings and reported nothing.
• DON'T audit a join by counting missing values in one new column, an unmatched key and a matched key with an empty lookup cell are both NaN there, and _merge tells them apart.

Guarding the Join

What. Guarding a join means three habits: count the rows before and after, check that the lookup's key is unique with duplicated() or .is_unique before merging, and pass validate="many_to_one", which makes pandas raise instead of multiplying rows. Why. A key that appears twice in the lookup returns every matching row twice, so each of them counts twice in every row count and every sum it enters while every single row looks correct, the same silent failure 2.1.2 found in SQL. When. On every merge of a lookup onto a table, before any number computed from the result is trusted.

The count before and after is the first check, and it finds the 27.

print(len(listings), len(merged))  # 200 227
before = (listings["neighborhood"] == "Riverside").sum()
after = (merged["neighborhood"] == "Riverside").sum()
print(before, after)               # 27 54
# {'Central': 72, 'East': 32, 'North': 58, 'West': 29}
print(merged.groupby("region").size().to_dict())

Riverside appears twice in the lookup, at 3.5 and at 3.7 kilometers, so each of its 27 listings matched both rows and came back twice. The table grew by exactly those 27, and Central, which holds Downtown's 18 listings and Riverside's 27, reports 72 instead of 45. Every extra row pairs a real listing with a real lookup row, which is why reading any one row never finds the problem and counting the rows always does.

The duplicate is visible in the lookup before any merge, with 2.5.4's duplicated, where keep=False marks every copy.

print(regions["neighborhood"].is_unique)
print(regions[regions["neighborhood"].duplicated(keep=False)])

False
  neighborhood   region  distance_to_center_km
1    Riverside  Central                    3.5
2    Riverside  Central                    3.7

validate="many_to_one" writes the expectation into the call: many listings may share a key, and the lookup must hold each key once. pandas checks the lookup's key against that promise and raises MergeError, which lives in pd.errors, instead of returning the multiplied table, and its message names the duplicated key.

try:
    listings.merge(
        regions,
        on="neighborhood",
        how="left",
        validate="many_to_one",
    )
except pd.errors.MergeError as err:
    print(f"MergeError: {err}")

MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

Duplicates in right:
 neighborhood
   Riverside ...

The fix belongs in the lookup, and it is a decision rather than a cleanup. Riverside is 3.5 or 3.7 kilometers from the center, or somewhere between, and the data cannot say which. Here the first row is kept, and the decision is written beside the code where the next reader will see it.

# Decision: Riverside keeps its first row, 3.5 km.
lookup = regions.drop_duplicates(subset="neighborhood", keep="first")
guarded = listings.merge(
    lookup,
    on="neighborhood",
    how="left",
    validate="many_to_one",
    indicator=True,
)
print(len(guarded))  # 200
# {'both': 164, 'left_only': 36, 'right_only': 0}
print(guarded["_merge"].value_counts().to_dict())
# {'Central': 45, 'East': 32, 'North': 58, 'West': 29}
print(guarded.groupby("region").size().to_dict())

The row count holds at 200 and Central is back to 45. both fell from the audit's 191 to 164, and the 27 it lost were the copies, the proof that _merge counts result rows and cannot tell a second match from a first.

DON'T:

• DON'T write a merge onto a lookup without validate="many_to_one", it is the one argument that turns a multiplied table into an error.
• DON'T fix a multiplied merge by deduplicating its result, the Riverside copies differ in their distance, so merged.drop_duplicates() keeps all 227 rows.
• DON'T read _merge's both as a count of listings, it counts result rows, and a key matched twice is counted twice.

Lab Exercises

Lab Exercise 1: Three Exports and a Neighborhood Column

Start a new script in ~/dev/bootcamp.

1. Build three exports in code, jan, feb and mar, two listings each with a neighborhood and a price, each numbered from 0. Stack them with keys=["jan", "feb", "mar"], print the stack, and print February's rows reached by its key.
2. An April export arrives with the column spelled Price. Stack all four with ignore_index=True, and print the columns and the number of missing values in each.
3. Load and clean housing.csv as the Theory does, compute each neighborhood's mean area rounded to one decimal, and merge it onto the listings so the listing's own area_sqm keeps its name and the new column is area_sqm_nb_mean. Print the first three rows of neighborhood, area_sqm and area_sqm_nb_mean, and the shape of the result.

Lab Exercise 2: Four Joins, One Audit, One Guard

Start another script that loads and cleans housing.csv and loads regions.csv as the Theory does.

1. Merge the listings with the raw lookup once per join type, and print for each the row count and the number of distinct neighborhoods in the result.
2. Run the left merge with validate="many_to_one" inside a try, and print only the first line of the error message.
3. Deduplicate the lookup keeping the last Riverside row, 3.7 kilometers, with the decision written in a comment. Merge with how="outer", validate="many_to_one" and indicator=True, and print the indicator counts and the neighborhoods marked left_only.
4. Print the mean distance_to_center_km per region from the guarded merge and from the raw left merge, rounded to two decimals, and explain the difference for Central in a comment.

import pandas as pd

jan = pd.DataFrame({
    "neighborhood": ["Downtown", "Eastgate"],
    "price": [268_000, 476_000],
})
feb = pd.DataFrame({
    "neighborhood": ["Riverside", "Hillcrest"],
    "price": [281_000, 150_000],
})
mar = pd.DataFrame({
    "neighborhood": ["Lakeview", "Northend"],
    "price": [437_000, 173_000],
})
stacked = pd.concat([jan, feb, mar], keys=["jan", "feb", "mar"])
print(stacked)
print(stacked.loc["feb"])

apr = pd.DataFrame({"neighborhood": ["Southgate"], "Price": [203_000]})
year = pd.concat([jan, feb, mar, apr], ignore_index=True)
print(year.columns.tolist())
print(year.isna().sum().to_dict())

      neighborhood   price
jan 0     Downtown  268000
    1     Eastgate  476000
feb 0    Riverside  281000
    1    Hillcrest  150000
mar 0     Lakeview  437000
    1     Northend  173000
  neighborhood   price
0    Riverside  281000
1    Hillcrest  150000
['neighborhood', 'price', 'Price']
{'neighborhood': 0, 'price': 1, 'Price': 6}

listings = pd.read_csv("housing.csv").drop_duplicates()
extreme = (
    (listings["price"] > 2_000_000)
    | (listings["area_sqm"] > 1_000)
    | (listings["rooms"] > 20)
)
listings = listings[~extreme].reset_index(drop=True)

area_means = (
    listings.groupby("neighborhood")["area_sqm"].mean().round(1).reset_index()
)
with_area = listings.merge(
    area_means,
    on="neighborhood",
    how="left",
    suffixes=("", "_nb_mean"),
)
print(with_area[["neighborhood", "area_sqm", "area_sqm_nb_mean"]].head(3))
print(with_area.shape)

  neighborhood  area_sqm  area_sqm_nb_mean
0     Lakeview     131.8             100.5
1     Downtown     112.1             103.2
2    Southgate     102.2             102.0
(200, 8)

import pandas as pd

listings = pd.read_csv("housing.csv").drop_duplicates()
extreme = (
    (listings["price"] > 2_000_000)
    | (listings["area_sqm"] > 1_000)
    | (listings["rooms"] > 20)
)
listings = listings[~extreme].reset_index(drop=True)
regions = pd.read_csv("regions.csv")

for how in ["inner", "left", "right", "outer"]:
    joined = listings.merge(regions, on="neighborhood", how=how)
    print(how, len(joined), joined["neighborhood"].nunique())

try:
    listings.merge(
        regions,
        on="neighborhood",
        how="left",
        validate="many_to_one",
    )
except pd.errors.MergeError as err:
    print(str(err).splitlines()[0])

inner 191 6
left 227 7
right 191 6
outer 227 7
Merge keys are not unique in right dataset; not a many-to-one merge

# Decision: Riverside keeps its last row, 3.7 km.
lookup = regions.drop_duplicates(subset="neighborhood", keep="last")
audit = listings.merge(
    lookup,
    on="neighborhood",
    how="outer",
    validate="many_to_one",
    indicator=True,
)
# {'both': 164, 'left_only': 36, 'right_only': 0}
print(audit["_merge"].value_counts().to_dict())
# ['Southgate']
print(audit.loc[audit["_merge"] == "left_only", "neighborhood"].unique().tolist())

raw = listings.merge(regions, on="neighborhood", how="left")
# {'Central': 2.7, 'East': 5.1, 'North': 8.57, 'West': nan}
print(audit.groupby("region")["distance_to_center_km"].mean().round(2).to_dict())
# {'Central': 3.0, 'East': 5.1, 'North': 8.57, 'West': nan}
print(raw.groupby("region")["distance_to_center_km"].mean().round(2).to_dict())
# Central 2.7: 18 Downtown rows at 1.2 and 27 Riverside rows at 3.7.
# Central 3.0: the 27 Riverside rows counted twice, at 3.5 and at 3.7.

Theory Assessment

Q1: Two Rows Labeled 1

both = pd.concat([march, april]) stacks the Theory's two exports. Say what both.loc[1] returns and why, then say how you would reach April's second listing, Southgate, after ignore_index=True and after keys=["march", "april"].

Hint: Each export arrived numbered from 0. Ask which label each row carries after the stack, and what each argument replaces it with.

Q2: The Rows Each Join Keeps

With the deduplicated lookup, which holds one row per neighborhood and no Southgate row, predict the row count of inner, left, right and outer, name the neighborhood behind every difference, and say which join adds region facts to a training table without changing it.

Hint: The cleaned table has 36 Southgate listings, and every neighborhood in the lookup has listings.

Q3: Sixty-Five Missing Distances

After the guarded left merge, distance_to_center_km holds 65 missing values. Explain where they come from, why the count alone cannot tell you what to do about them, and how indicator=True splits them.

Hint: Two different neighborhoods are involved, and only one of them has a row in the lookup.

Q4: Two Fixes That Fix Nothing

A teammate merges the listings with the raw lookup and sees Central report 72 listings. Their first fix is merged = merged.drop_duplicates(). When the count stays at 72, they add a guard, it raises, and their second fix is to change it to validate="many_to_many". Predict the Central count after each fix, say what each fix actually did, and state what Central's mean price becomes once the lookup itself is repaired, compared with the 283259.0 the multiplied table reports.

Hint: Look at the two Riverside rows of the lookup and ask whether their copies are identical rows. Then ask what a declaration that permits every shape can ever refuse.

print(both.loc[1]["neighborhood"].tolist())  # ['Riverside', 'Southgate']
print(renumbered.loc[4, "neighborhood"])     # Southgate
# Southgate
print(by_month.loc[("april", 1), "neighborhood"])

for how in ["inner", "left", "right", "outer"]:
    print(how, len(listings.merge(lookup, on="neighborhood", how=how)))

inner 164
left 200
right 164
outer 200

dedup = merged.drop_duplicates()
# 227 72
print(len(dedup), dedup.groupby("region").size()["Central"])
loose = listings.merge(regions, on="neighborhood", how="left", validate="many_to_many")
print(len(loose))  # 227
# {'Central': 274757.0, 'East': 420172.0, 'North': 189660.0, 'West': 391259.0}
print(guarded.groupby("region")["price"].mean().round(0).to_dict())

Assignment

Assignment: A Training Table From Three Sources

The training table for the model of how long a listing stays on the market needs each listing's region and distance from regions.csv and a school rating that a second team delivers in two pieces, both built in code below, keyed by a column they call area. The listings' row count may not change, and every missing value the merges introduce must be traced to its source.

schools_a = pd.DataFrame({
    "area": ["Downtown", "Riverside", "Hillcrest"],
    "school_rating": [7.5, 6.8, 8.1],
})
schools_b = pd.DataFrame({
    "area": ["Hillcrest", "Eastgate", "Northend", "Southgate", "Westfield"],
    "school_rating": [8.3, 7.2, 6.4, 5.9, 8.8],
})

Stack the two school pieces into one lookup and check it the way this lesson checked regions.csv. Resolve every duplicate as a stated decision: the second piece is the newer rating, so it wins, and Riverside's distance becomes the mean of its two values, computed with 2.6.2's groupby. Then merge both lookups onto the cleaned listings with left joins guarded by validate="many_to_one", assert after each merge that the table still has 200 rows, and print an audit of which neighborhoods each source failed to match.

The school lookup names its key area, so on= cannot be used. merge(left_on="neighborhood", right_on="area") pairs two key columns with different names and keeps both of them in the result. And because merge will not add its indicator under a name the table already holds, a second indicator=True merge on the same table raises a ValueError, so give each indicator its own name: indicator="region_match" names the column it adds. Once the audit has used them, table.drop(columns=[...]) returns the table without the helper columns.

Hints:

• Grouping the lookup by neighborhood and region together keeps the region column through the mean, and reset_index() turns both keys back into columns.
• In a left merge, right_only is always 0, so Westfield, which has a rating and no listings, never appears in the result.

import pandas as pd

listings = pd.read_csv("housing.csv").drop_duplicates()
extreme = (
    (listings["price"] > 2_000_000)
    | (listings["area_sqm"] > 1_000)
    | (listings["rooms"] > 20)
)
listings = listings[~extreme].reset_index(drop=True)
regions = pd.read_csv("regions.csv")

schools_a = pd.DataFrame({
    "area": ["Downtown", "Riverside", "Hillcrest"],
    "school_rating": [7.5, 6.8, 8.1],
})
schools_b = pd.DataFrame({
    "area": ["Hillcrest", "Eastgate", "Northend", "Southgate", "Westfield"],
    "school_rating": [8.3, 7.2, 6.4, 5.9, 8.8],
})
schools = pd.concat([schools_a, schools_b], ignore_index=True)
print(schools["area"].is_unique)
print(schools[schools["area"].duplicated(keep=False)])
# Decision: the second piece is newer, so its Hillcrest rating wins.
schools = schools.drop_duplicates(subset="area", keep="last")

# Decision: Riverside's distance is the mean of its two values.
region_lookup = (
    regions.groupby(["neighborhood", "region"])["distance_to_center_km"]
    .mean()
    .reset_index()
)
print(region_lookup)

False
        area  school_rating
2  Hillcrest            8.1
3  Hillcrest            8.3
  neighborhood   region  distance_to_center_km
0     Downtown  Central                    1.2
1     Eastgate     East                    5.1
2    Hillcrest    North                    7.8
3     Lakeview     West                    NaN
4     Northend    North                    9.4
5    Riverside  Central                    3.6

n = len(listings)
table = listings.merge(
    region_lookup,
    on="neighborhood",
    how="left",
    validate="many_to_one",
    indicator="region_match",
)
assert len(table) == n, "the region merge changed the row count"
table = table.merge(
    schools,
    left_on="neighborhood",
    right_on="area",
    how="left",
    validate="many_to_one",
    indicator="school_match",
)
assert len(table) == n, "the school merge changed the row count"

for col in ["region_match", "school_match"]:
    counts = table[col].value_counts().to_dict()
    unmatched = table.loc[table[col] == "left_only", "neighborhood"].unique()
    print(col, counts, unmatched.tolist())

table = table.drop(columns=["area", "region_match", "school_match"])
print(table.shape)
missing = table[["region", "distance_to_center_km", "school_rating"]].isna().sum()
print(missing.to_dict())

region_match {'both': 164, 'left_only': 36, 'right_only': 0} ['Southgate']
school_match {'both': 171, 'left_only': 29, 'right_only': 0} ['Lakeview']
(200, 10)
{'region': 36, 'distance_to_center_km': 65, 'school_rating': 29}

Additional Resources

Optional, and nothing later depends on them. Worth opening if you want the other ways pandas combines tables.

• Official docs - pandas API reference, DataFrame.join. Joining on the index instead of a column, the shorter call when both keys are already indexes.
• Official docs - pandas API reference, merge_asof. Matching each row to the nearest key rather than an equal one, the join for readings taken at different times.

Summary

The two operations in this lesson look alike and answer different questions. A stack assumes its pieces are the same kind of record and checks nothing, not the labels, not the column names, so everything it gets wrong it gets wrong quietly: labels that name two rows, a column spelled two ways becoming two columns. A merge assumes its key means the same thing on both sides and that the lookup holds it once, and when the second assumption fails it does not complain, it multiplies.

What made the multiplication dangerous was not its size, 27 extra rows, but its invisibility. Every copied row looked correct, each one pairing a real listing with a real lookup row, and the merged table looked like any other. Arithmetic noticed where reading did not: the count before against the count after, a region holding 72 listings when its neighborhoods hold 45, a mean that moved because one neighborhood now counted twice. The tools that caught it, len before and after, duplicated and validate, all work by turning an assumption into something checked, and none of them is more than a few characters.

The decisions were the other half. Which Riverside distance is real, which school rating is current, whether Southgate's listings belong in the analysis at all, none of these has an answer in the data, and each one the lesson settled was written down beside the code that carried it out. A table built from several sources is a record of such choices as much as of the sources, and the next section starts describing and plotting the table these three lessons built.

Cheat Sheet

Part A - Reference Table

Part B - Compact Code Block

import pandas as pd

both = pd.concat([march, april], ignore_index=True)
by_month = pd.concat([march, april], keys=["march", "april"])

regions = pd.read_csv("regions.csv")
print(regions["neighborhood"].is_unique)  # check first
# Decision: Riverside keeps its first row.
lookup = regions.drop_duplicates(subset="neighborhood", keep="first")
merged = listings.merge(
    lookup,
    on="neighborhood",
    how="left",                           # every listing once
    validate="many_to_one",               # raises on a repeated key
    indicator=True,                       # adds '_merge'
)
assert len(merged) == len(listings)
merged["_merge"].value_counts()           # both, left_only, right_only