Combine and reshape tables
Concatenate tables, merge related records by key, and reshape summarized data.
Concatenate similar tables
Use pd.concat to stack DataFrames with compatible columns. ignore_index=True creates a new sequential row index:
import pandas as pd
week_one = pd.DataFrame({"item": ["tea", "coffee"], "units": [8, 5]})
week_two = pd.DataFrame({"item": ["tea", "juice"], "units": [6, 4]})
all_weeks = pd.concat([week_one, week_two], ignore_index=True)
Concatenation aligns columns by label. If inputs have different columns, the result may contain missing values where a particular table had no matching column.
Merge by a key
Use merge to combine records from tables that share a key, much like a SQL join:
orders = pd.DataFrame(
{"product_id": [1, 2, 1], "units": [4, 3, 6]}
)
products = pd.DataFrame(
{"product_id": [1, 2], "name": ["tea", "coffee"], "price": [2.5, 4.0]}
)
enriched = orders.merge(products, on="product_id", how="left", validate="many_to_one")
enriched["revenue"] = enriched["units"] * enriched["price"]
A left merge keeps every row from orders. The validate option checks the assumed key relationship; here many orders may refer to one product row. Check key uniqueness and row counts when joining, since duplicate keys can multiply rows unexpectedly.
Pivot an aggregate to a table
pivot_table summarizes values across row and column categories:
report = enriched.pivot_table(
index="name",
columns="product_id",
values="revenue",
aggfunc="sum",
fill_value=0,
)
print(report)
Unlike pivot, pivot_table can aggregate duplicate combinations. Choose an aggregation function that matches the question, and do not fill missing combinations with zero unless zero is the intended interpretation.
Exercise: Concatenate two monthly order tables, merge them with a product lookup table, and build a product-by-month summary.
Sources: pandas merge, join, concatenate and compare, pandas reshaping and pivot tables