concat(): Stacking DataFrames

Season stats usually come in separate pieces: one file for 2023, one for 2024, one per team… Before you can analyse them you must put them together. When the pieces have the same columns and you just want to stack them, the tool is pd.concat() (notebook 10).

import pandas as pd

s23 = pd.DataFrame({"player": ["Kohli", "Gill", "Faf"], "runs": [639, 890, 730]})
s24 = pd.DataFrame({"player": ["Kohli", "Gaikwad"], "runs": [741, 583]})

Definition

pd.concat() joins a list of Series/DataFrames along an axis: with axis=0 (default) it stacks them vertically (more rows); with axis=1 it places them side by side (more columns). Labels on the other axis are aligned — matching labels line up and missing ones are filled with NaN.

• List — you always pass a list: pd.concat([a, b, c]), never pd.concat(a, b).
• Along an axis — axis=0 adds rows, axis=1 adds columns.
• Aligned — when stacking rows, columns are matched by name (not by position); when stacking columns, rows are matched by index label.
• No key matching — concat does not look at the values of any column. It is "glue", not a lookup. (Matching by a key column is merge.)

Diagram

 axis=0 (default): more ROWS               axis=1: more COLUMNS
 ┌──────────────┐                          ┌───────────┐┌──────────────┐
 │ s23 (3 rows) │                          │  runs     ││ sixes  fours │
 ├──────────────┤                          │  3 rows   ││ 3 rows       │
 │ s24 (2 rows) │                          └───────────┘└──────────────┘
 └──────────────┘                          rows matched by index label
 columns matched by NAME

Syntax

pd.concat(objs, axis=0, join="outer", ignore_index=False, keys=None,
          names=None, verify_integrity=False, sort=False)

Example — stacking rows and the duplicate index

print(pd.concat([s23, s24]))
print(pd.concat([s23, s24], ignore_index=True))

    player  runs
0    Kohli   639
1     Gill   890
2      Faf   730
0    Kohli   741
1  Gaikwad   583
    player  runs
0    Kohli   639
1     Gill   890
2      Faf   730
3    Kohli   741
4  Gaikwad   583

How it works: concat keeps each piece's original index, so labels 0 and 1 appear twice — result.loc[0] would return two rows. ignore_index=True renumbers cleanly. verify_integrity=True turns duplicates into an error: ValueError: Indexes have overlapping values: Index([0, 1], dtype='int64').

Example — keys: remember where each row came from

k = pd.concat([s23, s24], keys=[2023, 2024])
print(k)
print(k.loc[2024])

         player  runs
2023 0    Kohli   639
     1     Gill   890
     2      Faf   730
2024 0    Kohli   741
     1  Gaikwad   583
    player  runs
0    Kohli   741
1  Gaikwad   583

How it works: each key becomes the outer level of a MultiIndex. To turn it into a normal column, add names=["season", "row"] and .reset_index(level="season") — or simpler, tag each piece before concatenating: pd.concat([s23.assign(season=2023), s24.assign(season=2024)], ignore_index=True).

Example — mismatched columns

s24b = pd.DataFrame({"player": ["Kohli", "Gaikwad"], "runs": [741, 583], "sixes": [38, 18]})
r = pd.concat([s23, s24b], ignore_index=True)
print(r)
print(pd.concat([s23, s24b], join="inner", ignore_index=True))

    player  runs  sixes
0    Kohli   639    NaN
1     Gill   890    NaN
2      Faf   730    NaN
3    Kohli   741   38.0
4  Gaikwad   583   18.0
    player  runs
0    Kohli   639
1     Gill   890
2      Faf   730
3    Kohli   741
4  Gaikwad   583

How it works: with the default join="outer", the result has the union of columns; 2023 has no sixes, so those cells are NaN (and sixes becomes float). join="inner" keeps only the common columns. Column names must match exactly — "Runs" and "runs" would become two different columns.

Example — axis=1: side by side

runs = pd.DataFrame({"player": ["Kohli", "Gill", "Faf"], "runs": [639, 890, 730]})
extra = pd.DataFrame({"sixes": [24, 33, 31], "fours": [65, 85, 71]})
print(pd.concat([runs, extra], axis=1))

extra2 = pd.DataFrame({"sixes": [24, 33]}, index=[1, 2])
print(pd.concat([runs, extra2], axis=1))

  player  runs  sixes  fours
0  Kohli   639     24     65
1   Gill   890     33     85
2    Faf   730     31     71
  player  runs  sixes
0  Kohli   639    NaN
1   Gill   890   24.0
2    Faf   730   33.0

How it works: axis=1 lines rows up by index label, not by position. In the second call extra2's labels are 1 and 2, so Kohli (label 0) gets NaN and Gill wrongly receives the 24 meant for Kohli. That is why the study guide warns: horizontal concat assumes the rows are in the same order with the same index — if you have a real key (player name), use merge instead.

With Series that share meaningful labels, axis=1 is a quick way to build a table:

a = pd.Series([639, 890], index=["Kohli", "Gill"], name="runs_2023")
b = pd.Series([741, 583], index=["Kohli", "Gaikwad"], name="runs_2024")
print(pd.concat([a, b], axis=1))

         runs_2023  runs_2024
Kohli        639.0      741.0
Gill         890.0        NaN
Gaikwad        NaN      583.0

Example — combining many pieces in a loop

frames = []
for season, runs_list in [(2022, [341, 483]), (2023, [639, 890]), (2024, [741, 426])]:
    frames.append(pd.DataFrame({"season": season, "player": ["Kohli", "Gill"], "runs": runs_list}))
allp = pd.concat(frames, ignore_index=True)
print(allp)

   season player  runs
0    2022  Kohli   341
1    2022   Gill   483
2    2023  Kohli   639
3    2023   Gill   890
4    2024  Kohli   741
5    2024   Gill   426

Collect pieces in a list and call concat once at the end. Concatenating inside the loop copies the growing table every time and gets slow.

Note: DataFrame.append() was removed in pandas 2.0. Old tutorials use df.append(other); today write pd.concat([df, other], ignore_index=True). To add one row: pd.concat([df, pd.DataFrame([{"player": "Pant", "runs": 446}])], ignore_index=True).

Common mistakes

• ❌ pd.concat(s23, s24) → TypeError. ✅ Pass a list: pd.concat([s23, s24]).
• ❌ Ending up with duplicate index labels and .loc returning two rows. ✅ ignore_index=True.
• ❌ Columns that differ only in spelling/case producing NaN-filled extra columns. ✅ Rename to identical names before concatenating.
• ❌ Using axis=1 to "join" tables whose rows are in a different order. ✅ Use merge on a key column.
• ❌ Calling concat inside a loop. ✅ Append to a list, concat once.

Interview tip

Q: What is the difference between pd.concat([a, b]) and pd.concat([a, b], axis=1)? A: The first stacks rows (columns aligned by name, union of columns by default). The second puts the frames side by side, aligning rows by index label and filling NaN where a label exists in only one input.