How do I combine many DataFrames efficiently in pandas?

The short version

Build a list of DataFrames and call pd.concat(list) a single time at the end. Calling concat inside a loop copies the entire accumulated result on every iteration, making the work grow quadratically with the number of frames and turning seconds into minutes.

The slow pattern to avoid

import pandas as pd

# BAD: rebuilds the whole frame every time round the loop
result = pd.DataFrame()
for path in paths:
    result = pd.concat([result, pd.read_csv(path)])

Each concat allocates a brand-new frame containing everything so far, then throws the old one away. By the hundredth file you are copying ninety-nine files' worth of data to add one. The total work grows with the square of the number of files.

The fast pattern

# GOOD: one concat at the end
frames = [pd.read_csv(path) for path in paths]
result = pd.concat(frames, ignore_index=True)

Collecting first and concatenating once means the data is copied a single time. On many files this is dramatically faster, and it is no harder to write.

Why ignore_index matters here

Each file's frame carries its own 0, 1, 2 index, so concatenating them produces repeated labels. ignore_index=True renumbers the result cleanly, which avoids the duplicate-index problems that would otherwise follow you around.

The same rule applies to rows

Building a frame by appending one row at a time is the same anti-pattern, and worse. Collect the rows as dictionaries in a list, then construct the frame once.

rows = []
for item in source:
    rows.append({"city": item.city, "amount": item.amount})

df = pd.DataFrame(rows)

Growing a DataFrame row by row is one of the slowest things you can do in pandas. Building a list and converting once is often hundreds of times faster.

Where the old append method went

DataFrame.append was removed in pandas 2.0. If you find it in old code, replace it with pd.concat, which does the same job and takes a list, encouraging the correct batched pattern in the first place.

Different columns across frames

concat aligns on column names, so frames with different columns produce NaN where a column was absent. That is usually the right behaviour, but check it: if you expected identical columns, a stray NaN column reveals that one file has a different header, which is worth knowing.

result.isna().mean()   # a quick check for unexpectedly missing columns

The general principle

This is an instance of a broader rule: do not grow a data structure incrementally inside a loop when you can build the pieces and combine them once. It applies to lists, strings and DataFrames alike, and recognising the pattern is one of the easiest large performance wins available.