Lesson 32 · Pandas Full Course
Combine Many Files into One DataFrame with pd.concat | Pandas Course #32
In this lesson you'll be able to: Stack many files into one table with pd.concat Keep the index clean, or label each piece with keys Handle pieces whose…
- CoursePandas Full Course
- Lesson32 of 65
- FormatJupyter notebook · 44 code cells
- Data1 dataset
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- superstore.csv preview1.7 MB
📓 Full notebook
Download .ipynbPandas 32: Concatenating DataFrames Vertically and Horizontally#
In this lesson you'll be able to:
- Stack many files into one table with pd.concat
- Keep the index clean, or label each piece with keys
- Handle pieces whose columns don't match
- Place tables side by side with axis=1, aligned by label
- Build a multi-year report from separate yearly files Dataset: Superstore orders, 2009-2012 (superstore.csv)
Warm-up: a MultiIndex from lesson 31#
Lesson 31 worked with multi-level indexes. One more on today's data:
import os
import glob
import pandas as pd
import matplotlib.pyplot as plt
orders = pd.read_csv("data/pandas/superstore.csv")
print(orders.shape)
dates = pd.to_datetime(orders["Order Date"], format="%m/%d/%Y")
orders["Year"] = dates.dt.year
keys = ["Year", "Product Category"]
sales = orders.groupby(keys)["Sales"].sum()
sales.unstack().round(0)
Stack yearly files into one table#
Problem: the data arrives as one file per year.
os.makedirs("output/pandas", exist_ok=True)
for year, part in orders.groupby("Year"):
path = f"output/pandas/l32_orders_{year}.csv"
part.drop(columns="Year").to_csv(path, index=False)
print([f for f in sorted(os.listdir("output/pandas")) if "l32" in f])
files = sorted(glob.glob("output/pandas/l32_orders_*.csv"))
parts = [pd.read_csv(f) for f in files]
print([len(p) for p in parts])
combined = pd.concat(parts)
print(combined.shape)
print(combined["Sales"].sum().round(2))
print(orders["Sales"].sum().round(2))
year_text = combined["Order Date"].str[-4:]
year_text.value_counts().sort_index()
Quick check: the rows all arrived. What's odd about the index?
print(combined.index.is_unique)
print(combined.loc[0].shape)
combined = pd.concat(parts, ignore_index=True)
print(combined.index.is_unique, combined.index[-1])
Common mistake: passing the tables one by one
pd.concat(parts[0], parts[1])
first_two = pd.concat([parts[0], parts[1]], ignore_index=True)
print(first_two.shape)
base = combined.iloc[:-2]
new = combined.tail(2)[["Order Date", "Region", "Sales"]]
print(base.shape, new.shape)
Common mistake: using the old append method
base.append(new)
added = pd.concat([base, new], ignore_index=True)
print(added.shape)
added[["Order Date", "Region", "Sales", "Profit"]].tail(2)
Label each piece with keys#
Problem: after stacking, which file did each row come from?
years = [2009, 2010, 2011, 2012]
keyed = pd.concat(parts, keys=years, names=["file_year", "row"])
keyed.index[:2]
print(keyed.loc[2011].shape)
by_file = keyed.groupby(level="file_year")["Sales"].sum()
by_file.round(0)
in_2012 = keyed.xs(2012, level="file_year")
in_2012["Region"].value_counts().head(3)
tagged = pd.concat(parts, keys=years, names=["file_year"])
tagged = tagged.reset_index(level="file_year")
tagged = tagged.reset_index(drop=True)
tagged[["file_year", "Order Date", "Sales"]].head(3)
orders_per_file = tagged.groupby("file_year")["Order ID"].nunique()
orders_per_file
Your turn#
Using keyed, find the total Profit in each file year. Which year was the most profitable?
profit = keyed.groupby(level="file_year")["Profit"].sum()
print(profit.round(0))
print(profit.idxmax())
When the columns don't match#
Problem: one file names a column differently, or has an extra one.
legacy = parts[0].rename(columns={"Sales": "Sales Total"})
mixed = pd.concat([legacy, parts[1]], ignore_index=True)
print(mixed.shape)
odd = set(legacy.columns) ^ set(parts[1].columns)
print(sorted(odd))
Quick check: how many missing values are in Sales now?
mixed[["Sales", "Sales Total"]].isna().sum()
fixed = legacy.rename(columns={"Sales Total": "Sales"})
mixed = pd.concat([fixed, parts[1]], ignore_index=True)
print(mixed.shape, mixed["Sales"].isna().sum())
inner = pd.concat([legacy, parts[1]], join="inner")
print(inner.shape)
print("Sales" in inner.columns)
Your turn#
Add a Margin column (Profit / Sales) to the 2012 file only. Stack 2011 and 2012, then count the missing margins.
p12 = parts[3].copy()
p12["Margin"] = p12["Profit"] / p12["Sales"]
both = pd.concat([parts[2], p12], ignore_index=True)
print(both["Margin"].isna().sum(), len(parts[2]))
Side by side with axis=1#
Problem: compare summaries next to each other, lined up by label.
r09 = parts[0].groupby("Region")["Sales"].sum()
r12 = parts[3].groupby("Region")["Sales"].sum()
side = pd.concat([r09, r12], axis=1, keys=["2009", "2012"])
side.round(0)
change = (r12 - r09).rename("change")
pd.concat([r09, r12, change], axis=1, keys=["2009", "2012",
"change"]).round(0).head(4)
sub = "Product Sub-Category"
top09 = parts[0].groupby(sub)["Profit"].sum().nlargest(5)
top12 = parts[3].groupby(sub)["Profit"].sum().nlargest(5)
pair_cols = ["2009", "2012"]
pd.concat([top09, top12], axis=1, keys=pair_cols).round(0)
both_years = pd.concat([top09, top12], axis=1, keys=pair_cols,
join="inner")
both_years.round(0)
stacked = pd.concat([r09, r12], keys=pair_cols)
print(stacked.shape)
stacked.head(3).round(0)
Common mistake: side by side with repeated row labels
dup = pd.concat(parts)
ranked = dup.sort_values("Sales")[["Profit"]]
pd.concat([dup[["Sales"]], ranked], axis=1)
ranked = combined.sort_values("Sales")[["Profit"]]
pair = pd.concat([combined[["Sales"]], ranked], axis=1)
print(pair.shape)
pair.head(3)
a = parts[0][["Sales"]]
b = parts[1][["Sales"]]
ab = pd.concat([a, b], axis=1, keys=["y2009", "y2010"])
print(ab.shape)
print(ab.isna().sum().tolist())
Your turn#
Put 2010 and 2011 profit by Ship Mode side by side, with a change column.
m10 = parts[1].groupby("Ship Mode")["Profit"].sum()
m11 = parts[2].groupby("Ship Mode")["Profit"].sum()
cols3 = ["2010", "2011", "change"]
pd.concat([m10, m11, m11 - m10], axis=1, keys=cols3).round(0)
Build comparison tables with concat#
Problem: compare the same summary across every file at once.
described = [p["Sales"].describe() for p in parts]
summary = pd.concat(described, axis=1, keys=years)
summary.round(1)
summary.loc[["mean", "50%"]].round(1)
Quick check: mean sales are far above the median. What does that tell us?
big = [(p["Sales"] > 10_000).mean() for p in parts]
pd.Series(big, index=years).round(3)
Your turn#
Compare the mean Discount per Customer Segment in 2009 and 2012, side by side in one table.
seg = "Customer Segment"
d09 = parts[0].groupby(seg)["Discount"].mean()
d12 = parts[3].groupby(seg)["Discount"].mean()
pd.concat([d09, d12], axis=1, keys=pair_cols).round(4)
Mini-project: which regions grew?#
Question: how did each region's sales change from 2009 to 2012?
totals = [p.groupby("Region")["Sales"].sum() for p in parts]
report = pd.concat(totals, axis=1, keys=years)
report.round(0)
total_row = report.sum().to_frame("All regions").T
full = pd.concat([report, total_row])
full.tail(3).round(0)
ends = report[[2009, 2012]]
(ends / ends.sum()).round(3).sort_values(2012, ascending=False)
growth = (report[2012] / report[2009] - 1).round(3)
growth.sort_values(ascending=False)
ax = growth.sort_values().plot(kind="barh", figsize=(7, 4))
ax.set_title("Sales growth by region, 2009 to 2012")
ax.set_xlabel("Change in sales (0.1 = 10%)")
ax.set_ylabel("Region")
plt.show()
overall = full.loc["All regions"]
change = overall[2012] / overall[2009] - 1
print(f"All regions: {change:.1%}, best: {growth.idxmax()}")
Recap#
- pd.concat takes a list of tables and stacks them
- ignore_index=True renumbers rows; keys labels each piece
- Mismatched names create NaN; rename first, or use join='inner'
- axis=1 places tables side by side, matched by row label
- Repeated labels break side-by-side concat
Challenges#
- Save one file per Product Category, then concat them back with keys.
- Build a Profit report by region and year, like the mini-project.
- Add a totals column with axis=1 and check it against the totals row.
What's next#
Lesson 33: Merging and Joining DataFrames (Inner, Outer, Left, Right) Combine tables by matching key columns, not just labels.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



