Python Tutorial: How to Merge Multiple CSV Files with Pandas

by

Python Tutorial: How to Merge Multiple CSV Files with Pandas

If you have ever opened ten CSV exports one by one, copied the rows, and pasted them into a master spreadsheet, you already know how error-prone and tedious manual merging is. This tutorial shows you how to merge multiple CSV files with Pandas and glob in a few lines of Python — including how to handle the encoding errors and mismatched columns that break most beginners’ first attempts.

By the end, you will have a reusable script that combines every CSV in a folder into a single clean output file, ready to plug into a larger automated file processing workflow.

The Pain of Merging CSV Files Manually

Manual CSV merging fails in predictable ways:

  • Copy-paste errors. It only takes one missed row or one duplicated header to silently corrupt your dataset.
  • It doesn’t scale. Merging 3 files by hand is annoying. Merging 30 weekly exports is an afternoon gone.
  • It’s not repeatable. Next week you do it all again, from scratch, with new files.

The Python approach solves all three problems at once: the script reads every file the same way every time, scales to hundreds of files without extra effort, and runs on demand — or on a schedule — forever. This is one of the highest-return starting points for anyone automating CSV workflows.

How to Use Python glob to Find and Merge All Files in a Folder Automatically

The first building block is finding the files. Hard-coding filenames defeats the purpose of automation — you want the script to pick up whatever CSVs are in the folder. Python’s built-in glob module does exactly that with wildcard patterns:

import glob

csv_files = glob.glob("data/*.csv")
print(csv_files)
# ['data/sales_jan.csv', 'data/sales_feb.csv', 'data/sales_mar.csv']

The pattern data/*.csv matches every file ending in .csv inside the data folder. A few useful variations:

  • data/**/*.csv with recursive=True — matches CSVs in subfolders too.
  • data/sales_*.csv — matches only files starting with sales_.
  • sorted(glob.glob("data/*.csv")) — sorts results so files merge in a predictable order.

[IMAGE: Directory showing multiple CSV datasets ready for Python glob merge automation]

How to Merge Multiple CSV Files in Python Using pandas

With the file list in hand, merging is a three-step process: read each CSV into a DataFrame, concatenate the DataFrames, and export the result.

Reading CSVs into DataFrames

Loop over the glob results and read each file with pandas.read_csv():

import glob
import pandas as pd

csv_files = sorted(glob.glob("data/*.csv"))

dataframes = []
for file in csv_files:
    df = pd.read_csv(file)
    df["source_file"] = file  # optional: track where each row came from
    dataframes.append(df)

Adding a source_file column is optional but highly recommended — when something looks wrong in the merged output, you can trace every row back to its original file.

Concatenating DataFrames

pd.concat() stacks the DataFrames vertically into one:

merged = pd.concat(dataframes, ignore_index=True)

The ignore_index=True argument rebuilds the row index from 0, instead of repeating each file’s original index. If your files may contain overlapping rows, you can also deduplicate in one line:

merged = merged.drop_duplicates()

Exporting the Merged CSV

Finally, write the combined DataFrame back out:

merged.to_csv("output/merged.csv", index=False)
print(f"Merged {len(csv_files)} files into output/merged.csv "
      f"({len(merged)} rows).")

index=False prevents Pandas from writing its internal row index as an extra column. That’s the whole merge — under 15 lines of code.

[IMAGE: Code snippet demonstrating pandas merge CSV files in Python]

Handling Common Errors When Merging CSVs

Real-world CSVs are messy. These two issues account for most failed merges.

Dealing with Encoding Errors

If you see UnicodeDecodeError when reading a file, the CSV was saved with a different character encoding — common with exports from older Windows systems or non-English locales. The fix is to specify the encoding explicitly:

try:
    df = pd.read_csv(file)
except UnicodeDecodeError:
    # Fall back to a common Windows encoding
    df = pd.read_csv(file, encoding="latin-1")

latin-1 and cp1252 are the most common fallbacks for files produced by legacy systems. If you know your source system, set its encoding directly rather than guessing.

Handling Mismatched Columns

When files have different columns — say one export added a new field halfway through the year — pd.concat() will still work, but it fills missing values with NaN. Sometimes that is what you want. Other times, you want to enforce a consistent schema:

EXPECTED_COLUMNS = ["date", "region", "product", "revenue"]

clean_frames = []
for file in csv_files:
    df = pd.read_csv(file)
    missing = set(EXPECTED_COLUMNS) - set(df.columns)
    if missing:
        print(f"WARNING: {file} is missing columns: {missing} — skipping.")
        continue
    clean_frames.append(df[EXPECTED_COLUMNS])

merged = pd.concat(clean_frames, ignore_index=True)

This version validates each file, skips (and reports) any that don’t match the expected schema, and keeps only the columns you care about — in a consistent order.

Automating the CSV Merging Script

Here is the complete script, combining everything above into one file:

"""merge_csvs.py — merge all CSVs in a folder into one output file."""
import glob
from pathlib import Path

import pandas as pd

INPUT_PATTERN = "data/*.csv"
OUTPUT_FILE = Path("output/merged.csv")
EXPECTED_COLUMNS = ["date", "region", "product", "revenue"]


def read_csv_safely(file: str) -> pd.DataFrame:
    try:
        return pd.read_csv(file)
    except UnicodeDecodeError:
        return pd.read_csv(file, encoding="latin-1")


def main() -> None:
    csv_files = sorted(glob.glob(INPUT_PATTERN))
    if not csv_files:
        print("No CSV files found — nothing to do.")
        return

    frames = []
    for file in csv_files:
        df = read_csv_safely(file)
        missing = set(EXPECTED_COLUMNS) - set(df.columns)
        if missing:
            print(f"WARNING: {file} missing {missing} — skipped.")
            continue
        df = df[EXPECTED_COLUMNS]
        df["source_file"] = file
        frames.append(df)

    merged = pd.concat(frames, ignore_index=True).drop_duplicates()
    OUTPUT_FILE.parent.mkdir(parents=True, exist_ok=True)
    merged.to_csv(OUTPUT_FILE, index=False)
    print(f"Merged {len(frames)} files -> {OUTPUT_FILE} ({len(merged)} rows).")


if __name__ == "__main__":
    main()

To make this run without you, drop it into a scheduled job or folder-watching pipeline. From here, two natural next steps are:

FAQ

What’s the difference between pd.concat() and pd.merge()?

pd.concat() stacks DataFrames on top of each other (or side by side) — the right tool for combining files with the same columns. pd.merge() joins DataFrames on a shared key column, like a SQL join. For combining multiple CSV exports of the same report, you almost always want concat.

How many CSV files can Pandas merge at once?

Pandas is limited by available memory, not file count. Hundreds of typical export files merge comfortably on an ordinary laptop. If your combined data no longer fits in memory, process files in chunks with the chunksize parameter of read_csv, or write intermediate results to disk.

How do I merge only certain CSV files in a folder?

Use a more specific glob pattern, such as glob.glob("data/sales_2026_*.csv"), to match only the files you want. You can also filter the file list in Python with a list comprehension before reading.

Why do I get extra unnamed columns in my merged file?

That usually means a previous export included the DataFrame index as a column. Read those files with pd.read_csv(file, index_col=0), and always write output with to_csv(..., index=False) to prevent the problem from propagating.

Can this script run automatically on a schedule?

Yes — the script is deliberately self-contained so you can schedule it with cron on Linux or Task Scheduler on Windows, or trigger it whenever new files land in the folder as part of a larger automated pipeline.

Leave a Comment