Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 64 ยท Combining Data

Concatenating DataFrames

Combine DataFrames with pd.concat. Stack rows, join columns, handle differing columns with join modes, track the source with keys, and avoid appending in loops.

Real data rarely arrives in one piece. Sales come as one file per month, logs as one file per server, survey answers as batches. Before you can analyse them you have to put the pieces together. pandas has two families of tools for that: concatenation (stack pieces on top of each other, or put them side by side) and merging (match rows by a key, like a database join). This lesson covers concatenation with pd.concat(); the next one, Merge and Join, covers keys.

Think of concat as gluing. It does not look at the values in your data, only at the labels: the columns when you stack rows, and the index when you place frames side by side.

flowchart TB
    subgraph rows["axis=0 (default): stack rows"]
        A1["jan"] --> R["result: jan rows,<br/>then feb rows"]
        A2["feb"] --> R
    end
    subgraph cols["axis=1: place side by side"]
        B1["ids"] --> C["result: columns of ids<br/>and names, aligned by index"]
        B2["names"] --> C
    end

With axis=0 columns are matched by name. With axis=1 rows are matched by index label.

Stacking rows

pd.concat([df1, df2, ...]) takes a list of frames (or a dict, see below) and stacks them vertically. Columns are matched by name, not by position. If one frame has a column the other does not, the missing cells are filled with NaN (the join argument, below, controls this).

The index is kept as it is. If both pieces have the index 0, 1, the result has the labels 0, 1, 0, 1: that is legal but usually not what you want. Pass ignore_index=True to get a fresh 0, 1, 2, 3.

Python Stack two months of sales

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The promo column exists only in feb, so January's rows get NaN. Notice the dtype in the last output: a column that mixes True/False with missing values cannot stay bool, so it becomes object. Whenever concatenation introduces gaps, check the dtypes afterwards and convert if needed (see Data Types and Conversion).

join modes

join= Columns in the result
"outer" (default) Union of all columns; gaps become NaN
"inner" Only columns present in every frame

inner is a quick way to drop extra columns, but it also silently discards data. Before choosing it, print set(df1.columns) ^ set(df2.columns) (the columns that are in one but not both) so you know what you are losing. Another common surprise is a spelling difference such as Sold versus sold: pandas treats those as different columns and you get two half-empty ones. Fix names with rename before concatenating.

Tracking where each row came from

After concatenation, you often need to know which piece a row belongs to. There are two clean ways:

  1. keys= labels each piece and adds that label as an extra index level.
  2. Assign a column first (df.assign(month="jan")) and concatenate afterwards. A normal column is easier to filter and group by.

A dict works as a shortcut for keys: its keys become the labels.

Python Source tracking with keys and assign

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

verify_integrity=True makes pandas raise an error if the result would contain duplicate index labels. It is a helpful guard when your index carries meaning (order ids, timestamps) and duplicates would indicate a bug, such as loading the same file twice. When the index is just 0..n-1 row numbers, use ignore_index=True instead.

Side by side: axis=1

With axis=1, concat places frames next to each other and aligns them by index label, not by row position. Labels that exist in only one frame produce NaN in the other frame's columns. This is similar to an outer join on the index.

  • join="outer" (default) keeps every label.
  • join="inner" keeps only labels that appear in all frames.

It also works on Series: pd.concat([s1, s2], axis=1) builds a DataFrame, using each Series' name for the column (keys=[...] supplies names for unnamed Series).

If you want to combine tables by the values in a column, such as customer ids, concat(axis=1) is the wrong tool; use merge. Only use axis=1 when the index labels already identify the same entities in each frame.

Python Aligning by index with axis=1

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Row "b" has no name, so its name cell is NaN in the outer join. With join="inner", only labels "a" and "c" survive. If you had positioned the frames purely by order, the names would have landed on the wrong ids. That is what makes alignment by label both powerful and something to watch.

Do not append in a loop

A tempting pattern for reading many files is:

Python
result = pd.DataFrame()
for path in paths:
    piece = pd.read_csv(path)
    result = pd.concat([result, piece])      # slow, and dtype-unsafe

This is a bad idea for two reasons. Every iteration copies everything collected so far, so the total work grows with the square of the number of pieces. And a starter frame such as pd.DataFrame(columns=["a", "b"]) is full of object columns, which can turn the real dtypes of the pieces into object as well. Instead, collect the pieces in a list and call concat once:

Python
pieces = []
for path in paths:
    pieces.append(pd.read_csv(path))
result = pd.concat(pieces, ignore_index=True)

Or with a comprehension: pd.concat((pd.read_csv(p) for p in paths), ignore_index=True). The old DataFrame.append() method was removed in pandas 2.0, so you will only see it in old code. It had the same problem.

When you read many files and need to know which file each row came from, pass them as a dict: pd.concat({path: pd.read_csv(path) for path in paths}, names=["file", None]). The file name becomes an index level, and reset_index(level="file") turns it into a column.

Adding rows, and why append is gone

Older tutorials add a row with df.append(new_row). That method was removed in pandas 2.0, because it copied the entire table on every call and made loops painfully slow. Today you build the new row as a small DataFrame (or dictionary) and use concat, which also lets you choose the position.

Python Add a row at the end, at the top and in the middle

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

ignore_index=True renumbers the rows 0, 1, 2 and so on. Without it, each piece keeps its own labels and you get duplicates. df.loc[len(df)] = [...] also adds one row, but it works in place, copies data and quietly fills by position, so use it only for one-off edits and never in a loop.

Aligning, combining and comparing

Three small tools work on two objects that have different labels.

  • left, right = a.align(b, join="outer") reindexes both objects to the union (or intersection, with join="inner") of their labels, so that you can compare or calculate on them side by side. Missing labels become NaN, and fill_value= replaces them.
  • a.combine(b, func) applies a function to matching pairs of values from two Series. Unlike a + b, it can use any function (max, min, your own), and fill_value fills the labels that exist on only one side.
  • a.compare(b) lists the differences between two Series or DataFrames with identical labels. It returns self and other columns for the cells that differ. keep_shape=True keeps all rows and columns, keep_equal=True also shows the cells that are equal, and align_axis=0 stacks the two sides as rows.
Python align, combine and compare

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

For compare, the two frames must have the same labels. If they do not (rows added or removed), align them first, or use a merge with indicator=True as shown in Advanced Merging. To patch values, see combine_first and update in the same lesson.

Concat or merge?

Situation Use
Same columns, more rows (monthly files, batches) concat (axis 0)
Same rows, extra columns, index labels match concat(axis=1)
Rows must be matched by a key column merge
Combine on nearest time or inexact keys merge_asof

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does pd.concat([a, b]) do when b has a column that a lacks?

    The default join="outer" keeps the union of the columns, so rows from a get NaN for the extra column. Use join="inner" to keep only the shared columns.
  2. 2. Two frames both have the index 0, 1. What is the index of pd.concat([a, b]) without extra arguments?

    The labels are kept as they are, so duplicates are allowed. Use ignore_index=True for a fresh 0..n-1 index, or verify_integrity=True to raise an error.
  3. 3. Why is repeatedly doing result = pd.concat([result, piece]) in a loop a bad pattern?

    Each concat creates a new frame from scratch, so the work grows with the square of the number of pieces. Appending to a list and calling concat once copies the data a single time.
  4. 4. How does concat(axis=1) decide which rows belong together?

    axis=1 aligns frames on their index labels. Labels missing from one frame give NaN in its columns (outer join), or are dropped with join="inner".

Summary

  • pd.concat([df1, df2, ...]) stacks rows by default and matches columns by name; axis=1 places frames side by side and matches index labels.
  • Use ignore_index=True for a fresh row index and verify_integrity=True to catch duplicate labels.
  • join="outer" (default) keeps all columns and introduces NaN; join="inner" keeps only the shared ones.
  • Track the source with keys= or a dict, or add a column with assign before concatenating.
  • Collect pieces in a list and call concat once instead of appending in a loop; DataFrame.append no longer exists.
  • align, combine and compare work on two objects with different (or identical) labels and show or reconcile their differences.
  • To match rows by the values of a column, use merge.

Next: Merge and Join