Stacking Tables with concat
pd.concat stacks tables that share their columns into one, ignore_index=True numbers the rows again from 0, a column added before stacking says which file each row came from, and a column named differently in one file shows up as a new column full of gaps.
Apprentice · Python · Updated
After this lesson you can
- Stack tables that share their columns with pd.concat and ignore_index=True, and count the rows before and after
- Add a column that names each row's source before stacking, and spot columns that don't match after it
Key takeaways
- pd.concat([fall, spring]) puts the rows of the second table under the first, matching the columns by name.
- Without ignore_index=True, each table keeps its own row labels, so two rows can share the label 0.
- Add a column such as term with assign before stacking, or nothing in the stacked table says which file a row came from.
- A column spelled differently in one table becomes an extra column, with NaN wherever a table didn't have it. Count the columns and rows before and after.
Data rarely arrives as one file. A gradebook exports one file per term, a store one per month, a sensor one per day, and the question you were asked ("how did the whole year go?") needs all of them in one table. Stacking tables is the first step of that kind of analysis, and it takes one line of pandas once you know the two things that can go wrong.
The example all chapter is Oakridge Middle School's records. This lesson uses two of its files, fall_scores.csv and spring_scores.csv: each term's four quiz scores, one row per student, a column per quiz. (VS Code draws each table below as a grid; here they're shown as text.)
Two files, one question
The principal wants every score from the whole year in one table. Here's what you have:
import pandas as pd
fall = pd.read_csv("fall_scores.csv")
spring = pd.read_csv("spring_scores.csv")
print("fall:", fall.shape)
print("spring:", spring.shape)
fall: (240, 5)
spring: (240, 5)
fall.head(3)
student_id quiz_1 quiz_2 quiz_3 quiz_4
0 1001 74 59 84 72
1 1002 85 74 76 79
2 1003 78 74 77 77
The same five columns in both files. Copying 240 rows from one spreadsheet into the bottom of another is the by-hand way. pandas does it for you.
pd.concat: one table under the other
Think of two stacks of report cards, one per term, put into one pile. pd.concat (short for concatenate, "join end to end") takes a list of tables and puts their rows one under the other, matching the columns by name:
both = pd.concat([fall, spring])
print("rows:", len(both))
rows: 480
240 and 240 make 480: counting the rows before and after is how you know nothing went missing. But look where the fall rows end and the spring rows begin:
both.iloc[238:242]
student_id quiz_1 quiz_2 quiz_3 quiz_4
238 1239 84 77 90 93
239 1240 68 52 62 69
0 1001 71 77 67 66
1 1002 82 78 92 89
Each table kept its own row labels, the index, so the labels start again at 0. Now both.loc[0] finds two rows, which surprises everyone the first time. ignore_index=True throws the old labels away and numbers the rows 0 to 479:
both = pd.concat([fall, spring], ignore_index=True)
both.iloc[238:242]
student_id quiz_1 quiz_2 quiz_3 quiz_4
238 1239 84 77 90 93
239 1240 68 52 62 69
240 1001 71 77 67 66
241 1002 82 78 92 89
Which file did this row come from?
Student 1001 now has two rows, and nothing says which is fall and which is spring. Before stacking, give each table a column that says where its rows came from. assign(term="fall") hands back a copy of the table with a new column, term, set to "fall" in every row:
both = pd.concat(
[fall.assign(term="fall"), spring.assign(term="spring")],
ignore_index=True,
)
both[both["student_id"] == 1001]
student_id quiz_1 quiz_2 quiz_3 quiz_4 term
0 1001 74 59 84 72 fall
240 1001 71 77 67 66 spring
both["term"].value_counts() gives 240 for each term, the same counts as the two files. Add the column before stacking; afterward, the information is gone.
When the columns don't match
concat matches columns by their exact names. Say two students who joined in January bring their fall scores from their old school, typed with the last column as Quiz 4:
transfers = pd.DataFrame({
"student_id": [1241, 1242],
"quiz_1": [88, 73],
"quiz_2": [90, 70],
"quiz_3": [85, 77],
"Quiz 4": [92, 68],
})
mixed = pd.concat([fall, transfers], ignore_index=True)
print("columns:", list(mixed.columns))
columns: ['student_id', 'quiz_1', 'quiz_2', 'quiz_3', 'quiz_4', 'Quiz 4']
No error, but six columns instead of five. To pandas, Quiz 4 and quiz_4 are different columns, so each row gets NaN (missing) in the one its table didn't have:
mixed.tail(3)
student_id quiz_1 quiz_2 quiz_3 quiz_4 Quiz 4
239 1240 68 52 62 69.0 NaN
240 1241 88 90 85 NaN 92.0
241 1242 73 70 77 NaN 68.0
The gaps even turned quiz_4 into decimals. The fix is to rename the column before stacking, with transfers.rename(columns={"Quiz 4": "quiz_4"}); then the stacked table has its five columns and 242 rows. Whenever you stack, check both counts: the rows should add up, and the columns should stay the same.
Check yourself
2 questions to try on the lesson page.
So what
pd.concat turns a pile of same-shaped files into one table in a line, and two habits keep it honest: a column that says where each row came from, and a count of rows and columns before and after.