Mathew K Analytics

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…

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.

📓 Full notebook

Download .ipynb

Pandas 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)
(8399, 21)
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)
Product Category Furniture Office Supplies Technology
Year
2009 1472672.0 1034642.0 1701825.0
2010 1252665.0 899874.0 1397142.0
2011 1269661.0 800349.0 1366807.0
2012 1183593.0 1017897.0 1518474.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])
['l32_orders_2009.csv', 'l32_orders_2010.csv', 'l32_orders_2011.csv', 'l32_orders_2012.csv']
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])
[2153, 2142, 2002, 2102]
combined = pd.concat(parts)
print(combined.shape)
print(combined["Sales"].sum().round(2))
print(orders["Sales"].sum().round(2))
(8399, 21)
14915600.82
14915600.82
year_text = combined["Order Date"].str[-4:]
year_text.value_counts().sort_index()
Order Date
2009    2153
2010    2142
2011    2002
2012    2102
Name: count, dtype: int64

Quick check: the rows all arrived. What's odd about the index?

print(combined.index.is_unique)
print(combined.loc[0].shape)
False
(4, 21)
combined = pd.concat(parts, ignore_index=True)
print(combined.index.is_unique, combined.index[-1])
True 8398

Common mistake: passing the tables one by one

pd.concat(parts[0], parts[1])
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[10], line 1
----> 1 pd.concat(parts[0], parts[1])

TypeError: concat() takes 1 positional argument but 2 were given
first_two = pd.concat([parts[0], parts[1]], ignore_index=True)
print(first_two.shape)
(4295, 21)
base = combined.iloc[:-2]
new = combined.tail(2)[["Order Date", "Region", "Sales"]]
print(base.shape, new.shape)
(8397, 21) (2, 3)

Common mistake: using the old append method

base.append(new)
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_12044\459975417.py in ?()
----> 1 base.append(new)

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py in ?(self, name)
   6190             and name not in self._accessors
   6191             and self._info_axis._can_hold_identifiers_and_holds_name(name)
   6192         ):
   6193             return self[name]
-> 6194         return object.__getattribute__(self, name)

AttributeError: 'DataFrame' object has no attribute 'append'
added = pd.concat([base, new], ignore_index=True)
print(added.shape)
added[["Order Date", "Region", "Sales", "Profit"]].tail(2)
(8399, 21)
Order Date Region Sales Profit
8397 9/12/2012 West 322.47 NaN
8398 7/9/2012 West 1041.66 NaN

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]
MultiIndex([(2009, 0),
            (2009, 1)],
           names=['file_year', 'row'])
print(keyed.loc[2011].shape)
by_file = keyed.groupby(level="file_year")["Sales"].sum()
by_file.round(0)
(2002, 21)
file_year
2009    4209139.0
2010    3549681.0
2011    3436817.0
2012    3719964.0
Name: Sales, dtype: float64
in_2012 = keyed.xs(2012, level="file_year")
in_2012["Region"].value_counts().head(3)
Region
West       541
Ontario    440
Prarie     418
Name: count, dtype: int64
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)
file_year Order Date Sales
0 2009 11/25/2009 248.26
1 2009 10/9/2009 191.67
2 2009 10/9/2009 5586.33
orders_per_file = tagged.groupby("file_year")["Order ID"].nunique()
orders_per_file
file_year
2009    1394
2010    1393
2011    1317
2012    1392
Name: Order ID, dtype: int64

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())
file_year
2009    434539.0
2010    363871.0
2011    381456.0
2012    341902.0
Name: Profit, dtype: float64
2009

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)
(4295, 22)
odd = set(legacy.columns) ^ set(parts[1].columns)
print(sorted(odd))
['Sales', 'Sales Total']

Quick check: how many missing values are in Sales now?

mixed[["Sales", "Sales Total"]].isna().sum()
Sales          2153
Sales Total    2142
dtype: int64
fixed = legacy.rename(columns={"Sales Total": "Sales"})
mixed = pd.concat([fixed, parts[1]], ignore_index=True)
print(mixed.shape, mixed["Sales"].isna().sum())
(4295, 21) 0
inner = pd.concat([legacy, parts[1]], join="inner")
print(inner.shape)
print("Sales" in inner.columns)
(4295, 20)
False

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]))
2002 2002

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)
2009 2012
Region
Atlantic 668393.0 480155.0
Northwest Territories 190378.0 169593.0
Nunavut 19580.0 26204.0
Ontario 905639.0 728213.0
Prarie 782526.0 798208.0
Quebec 370323.0 450595.0
West 1045843.0 859127.0
Yukon 226456.0 207869.0
change = (r12 - r09).rename("change")
pd.concat([r09, r12, change], axis=1, keys=["2009", "2012",
                                          "change"]).round(0).head(4)
2009 2012 change
Region
Atlantic 668393.0 480155.0 -188238.0
Northwest Territories 190378.0 169593.0 -20785.0
Nunavut 19580.0 26204.0 6625.0
Ontario 905639.0 728213.0 -177427.0
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)
2009 2012
Product Sub-Category
Binders and Binder Accessories 123971.0 94375.0
Telephones and Communication 79482.0 91476.0
Office Machines 60495.0 66264.0
Chairs & Chairmats 54376.0 34625.0
Copiers and Fax 33389.0 NaN
Computer Peripherals NaN 28317.0
both_years = pd.concat([top09, top12], axis=1, keys=pair_cols,
                       join="inner")
both_years.round(0)
2009 2012
Product Sub-Category
Binders and Binder Accessories 123971.0 94375.0
Telephones and Communication 79482.0 91476.0
Office Machines 60495.0 66264.0
Chairs & Chairmats 54376.0 34625.0
stacked = pd.concat([r09, r12], keys=pair_cols)
print(stacked.shape)
stacked.head(3).round(0)
(16,)
      Region               
2009  Atlantic                 668393.0
      Northwest Territories    190378.0
      Nunavut                   19580.0
Name: Sales, dtype: float64

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)
---------------------------------------------------------------------------
InvalidIndexError                         Traceback (most recent call last)
Cell In[32], line 3
      1 dup = pd.concat(parts)
      2 ranked = dup.sort_values("Sales")[["Profit"]]
----> 3 pd.concat([dup[["Sales"]], ranked], axis=1)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\concat.py:466, in concat(objs, axis, join, ignore_index, keys, levels, names, verify_integrity, sort, copy)
    464 axis = 1 - bm_axis if is_frame else 0
    465 names = names or getattr(keys, "names", None)
--> 466 result = _get_result(
    467     objs,
    468     is_series,
    469     bm_axis,
    470     ignore_index,
    471     intersect,
    472     sort,
    473     keys,
    474     levels,
    475     verify_integrity,
    476     names,
    477     axis,
    478 )
    480 if sort is lib.no_default:
    481     if orig_axis == 0:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\reshape\concat.py:653, in _get_result(objs, is_series, bm_axis, ignore_index, intersect, sort, keys, levels, verify_integrity, names, axis)
    651         obj_labels = obj.axes[1 - ax]
    652         if not new_labels.equals(obj_labels):
--> 653             indexers[ax] = obj_labels.get_indexer(new_labels)
    655     mgrs_indexers.append((obj._mgr, indexers))
    657 new_data = concatenate_managers(
    658     mgrs_indexers, result_axes, concat_axis=bm_axis, copy=False
    659 )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3728, in Index.get_indexer(self, target, method, limit, tolerance)
   3725 self._check_indexing_method(method, limit, tolerance)
   3727 if not self._index_as_unique:
-> 3728     raise InvalidIndexError(self._requires_unique_msg)
   3730 if len(target) == 0:
   3731     return np.array([], dtype=np.intp)

InvalidIndexError: Reindexing only valid with uniquely valued Index objects
ranked = combined.sort_values("Sales")[["Profit"]]
pair = pd.concat([combined[["Sales"]], ranked], axis=1)
print(pair.shape)
pair.head(3)
(8399, 2)
Sales Profit
0 248.26 93.80
1 191.67 -236.27
2 5586.33 80.44
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())
(2153, 2)
[0, 11]

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)
2010 2011 change
Ship Mode
Delivery Truck 71802.0 92850.0 21048.0
Express Air 48862.0 31296.0 -17566.0
Regular Air 243207.0 257310.0 14102.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)
2009 2010 2011 2012
count 2153.0 2142.0 2002.0 2102.0
mean 1955.0 1657.2 1716.7 1769.7
std 4170.9 3256.1 3393.7 3425.1
min 3.4 3.2 2.2 3.6
25% 146.6 146.7 139.0 143.6
50% 477.1 458.9 403.7 465.5
75% 1865.9 1581.1 1595.7 1771.8
max 89061.0 29884.6 29345.3 41343.2
summary.loc[["mean", "50%"]].round(1)
2009 2010 2011 2012
mean 1955.0 1657.2 1716.7 1769.7
50% 477.1 458.9 403.7 465.5

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)
2009    0.039
2010    0.031
2011    0.034
2012    0.034
dtype: float64

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)
2009 2012
Customer Segment
Consumer 0.0511 0.0504
Corporate 0.0481 0.0505
Home Office 0.0473 0.0494
Small Business 0.0499 0.0478

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)
2009 2010 2011 2012
Region
Atlantic 668393.0 508428.0 357272.0 480155.0
Northwest Territories 190378.0 200178.0 240699.0 169593.0
Nunavut 19580.0 31693.0 38900.0 26204.0
Ontario 905639.0 772618.0 656742.0 728213.0
Prarie 782526.0 545010.0 711561.0 798208.0
Quebec 370323.0 369739.0 319538.0 450595.0
West 1045843.0 851790.0 840788.0 859127.0
Yukon 226456.0 270225.0 271317.0 207869.0
total_row = report.sum().to_frame("All regions").T
full = pd.concat([report, total_row])
full.tail(3).round(0)
2009 2010 2011 2012
West 1045843.0 851790.0 840788.0 859127.0
Yukon 226456.0 270225.0 271317.0 207869.0
All regions 4209139.0 3549681.0 3436817.0 3719964.0
ends = report[[2009, 2012]]
(ends / ends.sum()).round(3).sort_values(2012, ascending=False)
2009 2012
Region
West 0.248 0.231
Prarie 0.186 0.215
Ontario 0.215 0.196
Atlantic 0.159 0.129
Quebec 0.088 0.121
Yukon 0.054 0.056
Northwest Territories 0.045 0.046
Nunavut 0.005 0.007
growth = (report[2012] / report[2009] - 1).round(3)
growth.sort_values(ascending=False)
Region
Nunavut                  0.338
Quebec                   0.217
Prarie                   0.020
Yukon                   -0.082
Northwest Territories   -0.109
West                    -0.179
Ontario                 -0.196
Atlantic                -0.282
dtype: float64
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()
No description has been provided for this image
overall = full.loc["All regions"]
change = overall[2012] / overall[2009] - 1
print(f"All regions: {change:.1%}, best: {growth.idxmax()}")
All regions: -11.6%, best: Nunavut

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#

  1. Save one file per Product Category, then concat them back with keys.
  2. Build a Profit report by region and year, like the mini-project.
  3. 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.