← All lessons

Concat and merge

Stack tables, look up keys, inner/left/outer joins, and suffixes.

concat stacks tables. merge is a spreadsheet VLOOKUP / SQL JOIN: match rows on a shared key.

Goal

Stack compatible frames, inner/left/outer join on a key, and notice extra rows from duplicate keys.

concat rows

jan = pd.DataFrame({"city": ["Nairobi", "Mombasa"], "units": [12, 9]})
feb = pd.DataFrame({"city": ["Nairobi", "Kisumu"], "units": [5, 11]})
print(pd.concat([jan, feb], ignore_index=True))

ignore_index=True rebuilds 0..n. Without it you get duplicate index labels.

concat columns

left = pd.DataFrame({"units": [12, 7]}, index=["A", "B"])
right = pd.DataFrame({"price": [10.5, 22.0]}, index=["A", "B"])
print(pd.concat([left, right], axis=1))

Alignment is on the index.

merge inner (default)

orders = pd.DataFrame(
    {
        "order_id": [1, 2, 3, 4],
        "user_id": [10, 11, 10, 12],
        "total": [24.0, 18.5, 9.0, 40.0],
    }
)
users = pd.DataFrame(
    {
        "user_id": [10, 11, 12, 99],
        "name": ["Ada", "Alan", "Grace", "Nia"],
    }
)
print(orders.merge(users, on="user_id"))

Inner keeps keys present in both. User 99 disappears; orders for 10 appear twice (two orders).

Left, right, outer

orders = pd.DataFrame(
    {
        "order_id": [1, 2, 3, 4],
        "user_id": [10, 11, 10, 12],
        "total": [24.0, 18.5, 9.0, 40.0],
    }
)
users = pd.DataFrame(
    {
        "user_id": [10, 11, 12, 99],
        "name": ["Ada", "Alan", "Grace", "Nia"],
    }
)
print("left — keep all orders")
print(orders.merge(users, on="user_id", how="left"))
print()
print("right — keep all users")
print(orders.merge(users, on="user_id", how="right"))
print()
print("outer — keep everything")
print(orders.merge(users, on="user_id", how="outer", indicator=True))

indicator=True adds _merge with left_only / right_only / both — the fastest way to debug a join.

Different column names

cities = pd.DataFrame({"town": ["Nairobi", "Mombasa"], "region": ["Central", "Coast"]})
sales = pd.DataFrame({"city": ["Nairobi", "Mombasa", "Kisumu"], "units": [12, 9, 11]})
print(sales.merge(cities, left_on="city", right_on="town", how="left"))

Kisumu has no region — NaN.

Overlapping column names

a = pd.DataFrame({"id": [1, 2], "score": [10, 20]})
b = pd.DataFrame({"id": [1, 2], "score": [11, 18]})
print(a.merge(b, on="id", suffixes=("_old", "_new")))

Duplicate keys explode rows

sales = pd.DataFrame({"city": ["Nairobi", "Nairobi"], "units": [12, 5]})
lookup = pd.DataFrame({"city": ["Nairobi", "Nairobi"], "note": ["HQ", "branch"]})
print(sales.merge(lookup, on="city"))

Two × two = four rows. drop_duplicates on the lookup before merge if the key should be unique.

Tip

Practice joins sales_log.csv to products.csv and regions.csv. Validate with indicator=True and print(out["_merge"].value_counts()).