Stacking Tables with concat
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Stack several tables into one.
- Say why
ignore_index=Trueis usually wanted. - Predict what happens when the columns do not line up.
- Keep track of which table a row came from.
Why This Matters
Twelve monthly exports that should be one table is the commonest shape of real work. concat is straightforward; the two things worth knowing are that the index repeats unless you say otherwise, and that a column present in one file and absent from another fills with NaN rather than complaining.
Step 1: Rows end to end
concat takes a list of tables and stacks them, lining the columns up by name.
The basic stack
Order is the order of the list. The columns are matched by name, not by position.
import pandas as pd
jan = pd.DataFrame({"name": ["Ada"], "score": [90]})
feb = pd.DataFrame({"score": [80], "name": ["Bo"]})
print(pd.concat([jan, feb]))The index repeats
Each table brought its own 0, 1, 2. ignore_index=True renumbers the result, which is what you want unless the index meant something.
import pandas as pd
a = pd.DataFrame({"v": [1]})
b = pd.DataFrame({"v": [2]})
print(pd.concat([a, b]).index.tolist())
print(pd.concat([a, b], ignore_index=True).index.tolist())A repeated index is not an error, but .loc[0] then returns two rows, and that surprise usually surfaces a long way from here.
Remembering where a row came from
Add the column before stacking. Once the tables are one table there is no way to tell.
import pandas as pd
jan = pd.DataFrame({"score": [90]}).assign(month="jan")
feb = pd.DataFrame({"score": [80]}).assign(month="feb")
print(pd.concat([jan, feb], ignore_index=True))Mini Practice #1: Stack and renumber
Try It YourselfStack these two tables, once as-is and once with a fresh index, and print both index lists.
Step 2: Columns that do not line up
A column one table has and another does not is filled with NaN rather than dropped, and nothing warns you.
The union of the columns
This is usually what you want and occasionally a sign that one export had a column renamed. Comparing the column sets first is the cheap check.
import pandas as pd
a = pd.DataFrame({"name": ["Ada"], "score": [90]})
b = pd.DataFrame({"name": ["Bo"], "grade": ["B"]})
print(pd.concat([a, b], ignore_index=True))
print(set(a.columns) ^ set(b.columns))The row count is predictable, so predict it
Stacking is the one combine whose result size you know in advance. Checking it costs one line and catches a file that failed to load.
import pandas as pd
tables = [pd.DataFrame({"v": [1, 2]}), pd.DataFrame({"v": [3]})]
expected = sum(len(t) for t in tables)
out = pd.concat(tables, ignore_index=True)
print(len(out) == expected, len(out))Mini Practice #2: Mismatched columns
Try It YourselfStack these two and print the result, then print which columns only one of them had.
Step 3: When to Use It
The same table in pieces
Monthly files, one export per class: same columns, stacked into one table.
pd.concat([jan, feb], ignore_index=True)Keeping track of the source
Tag each piece with a column before stacking so rows can still be told apart.
When Not to Use It
Do not concat tables whose column names differ slightly. Mismatched names become separate columns full of NaN rather than an error.
End-of-Lesson Exercises
Exercise 1: Stack a list of tables
Write stack(tables) that stacks a list of tables into one with a fresh index, and returns the number of rows.
pd.concat takes the list. ignore_index=True is what stops two rows both being numbered 0.
Exercise 2: Label each row with its source
Write stack_labelled(tables, labels) that stacks the tables, adding a source column holding the matching label for every row from that table, and returns the source column as a list.
Tag each table before stacking — assign(source=label) on a copy of each — then concat. Once they are one table there is no way to tell them apart.