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

By

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.

Key terms

Related