9  Combining and Reshaping Data

9.1 Merge two tables

import pandas as pd

cities = pd.DataFrame({
    "city_id": [1, 2, 3],
    "city": ["A", "B", "C"],
})

values = pd.DataFrame({
    "city_id": [1, 3],
    "value": [100, 300],
})

result = cities.merge(values, on="city_id", how="left")
result

9.2 Find unmatched records

unmatched = result.loc[result["value"].isna()]
unmatched

9.3 Group and aggregate

summary = (
    result
    .groupby("city", as_index=False)
    .agg(total=("value", "sum"))
)

summary

9.4 Things to remember

• Verify whether the join key is unique.
• Check row counts after a merge.
• Choose left, inner, right, or outer according to the analytical question.