Module 5: Combining tables · Lesson 5.1 · about 15 min
Stacking tables with concat
Stack tables with the same columns on top of each other (monthly files into one), or side by side, with pd.concat; label where each row came from, and avoid the traps of mismatched columns and repeated index labels.
Module 5 · about 15 minutes · uses Lesson 1.4. You’ll be able to combine tables that share columns into one.
In everyday terms: putting this month’s till rolls on top of last month’s. The paper is the same shape, so the pile is just longer; write the month on each roll before you stack it.
1. Stack rows
keys= labels which table each row came from; the reset_index steps turn that label into a normal month column and renumber the rows.
2. Many files at once
On your own computer, a folder of monthly CSVs becomes one table with a list:
from pathlib import Path
files = sorted(Path('exports').glob('sales_2026-*.csv'))
sales = pd.concat([pd.read_csv(f).assign(source=f.name) for f in files], ignore_index=True)
Here the data is already one file, so split it and put it back together to see it work:
3. Mismatched columns
If column names differ only in spelling (Sales vs sales), clean the names first (Lesson 2.1), or the “same” column appears twice.
Side by side: pd.concat([df1, df2], axis=1) puts columns next to each other, matched by index. For matching on a key column, use merge (next lesson).
- pd.concat([a, b])
- Puts b's rows under a's; columns are matched by name.
- ignore_index=True
- Numbers the new table 0, 1, 2... instead of repeating the old labels.
- keys=[...] or a new column
- Records which piece each row came from.
- axis=1
- Places tables side by side, matched on the index.
- Physical meaning
- Stacking only adds rows: nothing is matched or combined, so row counts simply add. A column missing from one piece is filled with NaN, which is pandas telling you the pieces were not quite the same shape.
“concat and merge do the same thing.” concat glues tables along an edge without looking at any key. merge matches rows by a key. Stacking two months is concat; adding customer names to orders is merge.
Watch it happen, one step at a time:
| store | qty |
|---|---|
| S01 | 3 |
| S02 | 5 |
| store | qty |
|---|---|
| S01 | 4 |
| WEB | 2 |
jan.assign(month='Jan'), feb.assign(month='Feb').pd.concat([jan, feb], ignore_index=True)| store | qty | month | |
|---|---|---|---|
| 0 | S01 | 3 | Jan |
| 1 | S02 | 5 | Jan |
| 2 | S01 | 4 | Feb |
| 3 | WEB | 2 | Feb |
4. Your turn
How it works
Principle: concat lines tables up by column name (stacking rows) or by index (side by side). It never looks at the values. That’s the difference from merge, which matches rows by the values in a key column. In SQL terms, stacking rows is UNION ALL.
Speed: collect pieces in a list and call concat once. Calling it inside a loop copies the growing table every time and gets slow.
5. Practice
- Stack 2025 and 2026 tables and keep a
yearcolumn. - What does
join='inner'do inconcat? - Why not
concatinside a loop?
Answers
pd.concat([t25.assign(year=2025), t26.assign(year=2026)], ignore_index=True).- Keeps only the columns that every table has.
- Each call copies everything so far; build a list and concat once.
New words: concat, stack, ignore_index, keys, union of columns.
- concat stacks tables with the same columns; rows add up.
- Use ignore_index=True for a clean 0, 1, 2… index.
- Add a column (or keys=) saying where each row came from.
- NaN after concat means a column was missing from one piece: check the names.
Check yourself
Sources
- pandas documentation, User Guide: Merge, join, concatenate and compare https://pandas.pydata.org/docs/user_guide/merging.html
- McKinney, W. (2022), Python for Data Analysis, 3rd edition, O'Reilly (open access) https://wesmckinney.com/book/
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.