Combine Multiple CSV Files with pandas

If you've ever been handed a folder full of monthly sales reports—sales_jan.csv, sales_feb.csv, sales_mar.csv—you know the pain of opening each file, copying the rows, and pasting them into one master spreadsheet. It's tedious, error-prone, and utterly wasteful. But this isn't just about saving you from spreadsheet drudgery. When your data is scattered across dozens of CSV files, every analysis becomes a multi-step ritual: load each file, align columns, fix mismatches, and then pray the final dataset looks right. In this lesson, you'll learn how to combine multiple CSV files with pandas—a core skill that turns a fragmented pile of files into a single, clean DataFrame you can analyze in seconds.

The problem this lesson solves

When you work with real-world data, it rarely arrives as one neat CSV. You might get:

• Time-based splits: sales data for each month, or logs for each day.
• Group-based splits: data from different regions, departments, or experiments.
• Chunked exports: huge datasets split into multiple files to stay under file-size limits.

Manually merging these files is a nightmare. Excel can only handle so many rows, and copy-pasting introduces mistakes. Even if you use glob and a raw Python loop, you have to handle column alignment, headers, and encoding yourself.

The core pain: Without a reliable method to combine multiple CSV files with pandas, you waste hours on repetitive work and risk corrupting your data. Every analysis downstream—from cleaning to visualization—depends on getting this step right.

Core concept / mental model

Think of pandas as a data table builder. Each CSV file is like a stack of cards with the same layout. To combine them, you have two main approaches:

1. Concatenation (stacking): Place the cards on top of each other, rows from one file going below the previous file. This is useful when files have the same columns.
2. Merging (joining): Combine cards side-by-side by matching a common column, like an ID or date. This is useful when files have complementary columns.

Imagine you have two lists of customers: one with email addresses and one with purchase history. Concatenation would put both lists together only if they have the same columns. Merging would match customers by their ID and bring email and purchases into one row.

In pandas, these map to two core functions:

• pd.concat() — stack DataFrames vertically or horizontally.
• pd.merge() — combine DataFrames by key columns, like SQL joins.

For the common case of combining multiple CSV files with identical structure, pd.concat() is your go-to. For scenario-based data, like order and product tables, pd.merge() is the right tool.

How it works step by step

Step 1: Understand your data structure

Before combining, inspect a few files to see if they share the same columns and order. If they do, concatenation is straightforward. If not, you'll need to align columns or apply merges.

Step 2: Use glob to find all CSV files

Python's glob module can match file patterns like *.csv in a folder. This avoids hardcoding filenames and handles new files automatically.

Step 3: Read each file into a DataFrame

Use pd.read_csv() inside a list comprehension to load every file. Set index_col=False if needed to avoid accidental index alignment.

Step 4: Concatenate all DataFrames

With pd.concat(df_list, ignore_index=True), pandas stacks the rows from all files into one DataFrame. ignore_index=True resets row numbers so you get a clean 0, 1, 2, ... sequence.

Cause and effect: If you skip ignore_index=True, pandas preserves original row indices, causing duplicates. Reset them to keep your final dataset tidy.

Step 5: Handle missing columns or mismatched data

If files have different columns, concat will fill missing values with NaN by default. For merges, you'll decide how to handle keys that don't match (inner, outer, left, or right).

Hands-on walkthrough

Let's put this into practice. Create three sample CSV files in a folder named sales_data/:

import pandas as pd

# Create sample monthly sales files
data = [
    ("sales_jan.csv", [("product", "revenue"), ("A", 100), ("B", 150)]),
    ("sales_feb.csv", [("product", "revenue"), ("A", 120), ("B", 140)]),
    ("sales_mar.csv", [("product", "revenue"), ("B", 160), ("C", 200)]),
]

for filename, rows in data:
    df = pd.DataFrame(rows[1:], columns=rows[0])
    df.to_csv(f"sales_data/{filename}", index=False)

Now combine all CSV files with pandas using glob:

import glob
import pandas as pd

# Find all CSV files in the folder
csv_files = glob.glob("sales_data/*.csv")
print(f"Found {len(csv_files)} files")
# Output: Found 3 files

# Read each file into a DataFrame and store in a list
all_dfs = [pd.read_csv(file) for file in csv_files]

# Concatenate all DataFrames into one, resetting the index
combined_df = pd.concat(all_dfs, ignore_index=True)

print(combined_df)

Expected output:

  product  revenue
0       A      100
1       B      150
2       A      120
3       B      140
4       B      160
5       C      200

Merging files with different columns

Now suppose you have customers.csv and orders.csv and want to combine them by customer_id:

import pandas as pd

customers = pd.DataFrame({
    "customer_id": [1, 2, 3],
    "name": ["Alice", "Bob", "Charlie"]
})

orders = pd.DataFrame({
    "customer_id": [2, 3, 3],
    "amount": [50, 30, 40]
})

merged = pd.merge(customers, orders, on="customer_id", how="inner")
print(merged)

Expected output:

   customer_id     name  amount
0            2      Bob      50
1            3  Charlie      30
2            3  Charlie      40

Pro tip: Always verify the shape of the combined DataFrame. The total rows of concatenated files should equal the sum of individual file rows. If it doesn't, check for headers being included as data or mismatched indices.

Compare options / when to choose what

Variation: You can also use pd.read_csv with a loop and df.append() (deprecated) or Python's csv module, but both are slower and less idiomatic. Stick with concat and merge.

When to use what: If you're dealing with the classic “many files, same schema” case—like monthly exports—pd.concat() is the clear winner. If you have separate tables that relate by a column, use pd.merge(). For more complex transformations, consider pd.DataFrame.join(), which internally uses merge.

Troubleshooting & edge cases

• Indices repeat after concatenation: Use ignore_index=True to avoid duplicate row numbers. If you skip it, you'll see repeated indices (0,1,2,0,1,2) which confuse later operations.
• Column order mismatch: If files have the same columns in different orders, pandas aligns them by column name anyway, but it's safer to explicitly select columns after import.
• Duplicate columns after merge: If both DataFrames have columns with the same name (other than the join key), pandas adds suffixes like _x and _y. Rename them before merging to avoid confusion.
• Mixed data types: When you concatenate, pandas may cast a column to object if one file has strings and another has numbers. Check dtypes after merging.
• Memory issues: Reading thousands of files at once can exhaust memory. Use glob and pd.concat in a loop with chunking if necessary.
• File not found: Double-check your glob pattern and path. Use pathlib.Path.glob("*.csv") for more robust file handling.

What you learned & what's next

You now know how to combine multiple CSV files with pandas—whether by stacking rows with pd.concat() or joining tables with pd.merge(). This is the foundation for cleaning and analyzing any real-world dataset that doesn't come in a single file. You've learned to:

• List CSV files with glob
• Read all files into DataFrames
• Concatenate them into one clean DataFrame with ignore_index=True
• Merge related tables on a key column
• Avoid common pitfalls like duplicate indices and mixed data types

Next in this track, you'll dive into data cleaning with pandas—handling missing values, duplicates, and inconsistent formats. Combining files is only the first step; making the data analysis-ready is the next challenge.