Stop copying and pasting data between spreadsheets. Learn how to automatically find, read, and combine dozens of CSV or Excel files into a single pandas DataFrame using glob, pathlib, and pd.concat — with source tracking built in.
You're a data analyst at a regional retailer. Every month, the regional managers email you their sales data as separate Excel files — sales_north_jan.xlsx, sales_south_jan.xlsx, sales_east_jan.xlsx, and so on. Before you can do any analysis, you need to combine them into one dataset. Doing this manually means opening each file, copying the data, pasting it below the previous block, and praying nothing went wrong. With twelve months of data across five regions, that's sixty files to wrangle by hand. Every quarter.
There's a better way. With Python and pandas, you can write fewer than ten lines of code that automatically find every relevant file in a folder, read each one, and stack them into a single, analysis-ready DataFrame. Change the folder path, run the script again, and it works on next month's files too — no modifications needed.
By the end of this lesson, you'll be able to do exactly that. Whether you're dealing with monthly exports, data from multiple departments, or files split by year, you'll have a repeatable, reliable workflow that scales as your data grows.
What you'll learn:
pd.concat() works and why it's the right tool for stacking filesglob and pathlib modules to find files automaticallyYou should be comfortable reading a single CSV or Excel file into a pandas DataFrame and performing basic operations on it. If that's new to you, work through Your First pandas DataFrame: Loading CSV and Excel Files and Exploring Data first. You should also have Python, pandas, and Jupyter (or VS Code) installed and working — if not, see Setting Up Python for Data Analysis: Python, VS Code, Jupyter, and Virtual Environments.
Before we write any code, it helps to understand what we're actually doing — and why it's different from another common pandas operation.
When your files all contain the same columns and you want to add more rows, that's called concatenation or stacking. Think of it like piling printed spreadsheets on top of each other. January's sales go on the table, February's go on top, March's on top of that — same structure, more records.
This is different from joining, which combines DataFrames side by side based on a shared key (like a VLOOKUP or SQL JOIN). If you need to merge data on a key column, that's covered in Joining DataFrames with pandas merge: SQL Joins and VLOOKUP in Python. For today, we're stacking.
The pandas function for stacking DataFrames is pd.concat(). It takes a list of DataFrames and returns one unified DataFrame. That's the whole trick — everything else in this lesson is about doing that cleanly and reliably.
Before automating anything, let's understand the mechanics with a small, explicit example. Suppose you have three CSV files in a folder called sales_data/:
sales_jan.csvsales_feb.csvsales_mar.csvEach looks like this (with slightly different numbers):
order_id,region,product,quantity,revenue
1001,North,Widget A,5,250.00
1002,South,Widget B,2,90.00
Here's the fully manual approach:
import pandas as pd
# Read each file individually
jan = pd.read_csv('sales_data/sales_jan.csv')
feb = pd.read_csv('sales_data/sales_feb.csv')
mar = pd.read_csv('sales_data/sales_mar.csv')
# Stack them into one DataFrame
combined = pd.concat([jan, feb, mar])
print(combined.shape)
# (180, 5) — assuming 60 rows per file
pd.concat() takes a list (note the square brackets — that's a Python list passed as a single argument) and stacks the DataFrames vertically. The result has all the rows from all three files.
Check what the index looks like now:
print(combined.head(10))
You'll notice something odd: the index probably goes 0, 1, 2 ... 59, 0, 1, 2 ... 59, 0, 1, 2 ... 59. Each file had its own index starting at zero, and pandas kept them all. That's rarely what you want.
Tip
Always use ignore_index=True when concatenating files this way. It resets the index to a clean 0, 1, 2, 3... sequence across the entire combined DataFrame, which prevents subtle bugs later when you use .iloc[] or .reset_index().
combined = pd.concat([jan, feb, mar], ignore_index=True)
Now the index runs cleanly from 0 to 179. Problem solved.
The manual approach breaks down the moment you have more than a few files. If someone drops a fourth month into the folder, your script misses it. If there are twenty regions, you'd need twenty lines just to read the files. Automation to the rescue.
Python's glob module finds files by pattern. Think of it like a search filter — you describe what you're looking for using wildcards, and glob returns a list of matching file paths.
import glob
# Find all CSV files in the sales_data folder
file_list = glob.glob('sales_data/*.csv')
print(file_list)
# ['sales_data/sales_jan.csv', 'sales_data/sales_feb.csv', 'sales_data/sales_mar.csv']
The * is a wildcard that matches anything. *.csv means "any filename ending in .csv." You can be more specific: sales_*.csv would match sales_jan.csv but not returns_jan.csv. This matters in real projects where folders contain multiple types of files.
Now combine this with a loop to read every file:
import pandas as pd
import glob
file_list = glob.glob('sales_data/*.csv')
# Read each file and collect into a list of DataFrames
dataframes = []
for filepath in file_list:
df = pd.read_csv(filepath)
dataframes.append(df)
# Concatenate all at once
combined = pd.concat(dataframes, ignore_index=True)
print(combined.shape)
This is the core pattern you'll use in real work. It scales from 3 files to 300 files with zero changes.
Note
You might see this written more compactly as a list comprehension: pd.concat([pd.read_csv(f) for f in file_list], ignore_index=True). Both approaches do exactly the same thing. The explicit loop is easier to debug when something goes wrong; the list comprehension is more concise once you're comfortable. Choose based on who else reads your code.
glob works well, but Python's pathlib module gives you a more modern, readable alternative — especially useful when you're working across operating systems (Windows uses backslashes; Mac and Linux use forward slashes, and pathlib handles this automatically).
from pathlib import Path
import pandas as pd
data_folder = Path('sales_data')
# Find all CSV files — equivalent to glob's *.csv
file_list = list(data_folder.glob('*.csv'))
dataframes = []
for filepath in file_list:
df = pd.read_csv(filepath)
dataframes.append(df)
combined = pd.concat(dataframes, ignore_index=True)
The advantage becomes clearer when you need to do something with the filename itself — like extracting the month from sales_jan.csv. With pathlib, filepath.stem gives you sales_jan (the filename without the extension), and filepath.name gives you sales_jan.csv. With raw glob strings, you'd have to do string manipulation yourself.
Here's a question that will definitely come up: once all the data is combined, how do you know which row came from which file? If you're combining monthly files and you later want to filter the combined dataset to look at just January, you need some way to tell January's rows apart from March's.
The solution is to add a column that records the source file before you concatenate. This is the single most important technique in this entire lesson for real-world data work.
from pathlib import Path
import pandas as pd
data_folder = Path('sales_data')
file_list = list(data_folder.glob('*.csv'))
dataframes = []
for filepath in file_list:
df = pd.read_csv(filepath)
# Add a column recording where this data came from
df['source_file'] = filepath.name # e.g., 'sales_jan.csv'
dataframes.append(df)
combined = pd.concat(dataframes, ignore_index=True)
print(combined['source_file'].unique())
# ['sales_jan.csv', 'sales_feb.csv', 'sales_mar.csv']
You can make this even more useful by extracting just the meaningful part of the filename. If your files are named sales_jan.csv, sales_feb.csv, etc., you might want a month column, not the full filename:
for filepath in file_list:
df = pd.read_csv(filepath)
# Extract 'jan', 'feb', etc. from 'sales_jan.csv'
month_label = filepath.stem.split('_')[1] # 'sales_jan' → ['sales', 'jan'] → 'jan'
df['month'] = month_label
dataframes.append(df)
Key insight
The source column is cheap to add and invaluable later. Every time you combine files, ask yourself: "Will I need to know where this row came from?" The answer is almost always yes. Add the column by default.
Everything above works identically for Excel files — you just swap pd.read_csv() for pd.read_excel() and update your glob pattern.
from pathlib import Path
import pandas as pd
data_folder = Path('sales_data')
file_list = list(data_folder.glob('*.xlsx'))
dataframes = []
for filepath in file_list:
df = pd.read_excel(filepath)
df['source_file'] = filepath.name
dataframes.append(df)
combined = pd.concat(dataframes, ignore_index=True)
One complication with Excel: a single workbook can have multiple sheets. If your data is always on the first sheet, pd.read_excel() handles it automatically. But if the relevant sheet has a specific name — say, "Data" — specify it explicitly:
df = pd.read_excel(filepath, sheet_name='Data')
If you need to read every sheet from every file and combine all of them, pass sheet_name=None. This returns a dictionary where each key is a sheet name and each value is a DataFrame. You'd then loop over that dictionary too:
for filepath in file_list:
sheets = pd.read_excel(filepath, sheet_name=None) # Returns dict
for sheet_name, sheet_df in sheets.items():
sheet_df['source_file'] = filepath.name
sheet_df['sheet'] = sheet_name
dataframes.append(sheet_df)
Warning
If you're combining Excel files with sheet_name=None, be intentional. If each workbook has a "Summary" sheet alongside the data sheet, you'll pull in the summaries too — which might double-count your numbers. Always inspect a few files manually before automating.
In an ideal world, every file has exactly the same columns in exactly the same order. In the real world, someone renamed "Revenue" to "revenue" in March's file, or April's export includes a notes column that January's doesn't have.
pd.concat() handles this gracefully by default. If a column exists in some files but not others, concat will include it in the combined DataFrame and fill the missing values with NaN for the files that didn't have it. This is usually the right behavior — it preserves all the data without crashing.
# sales_jan.csv has columns: order_id, region, product, revenue
# sales_apr.csv has columns: order_id, region, product, revenue, notes
combined = pd.concat(dataframes, ignore_index=True)
# 'notes' column exists for April rows; NaN for January rows
Tip
After combining files with potential column mismatches, run combined.info() and combined.isnull().sum() to see which columns have unexpected nulls. If notes shows 240 nulls out of 300 rows, you know it only existed in one file — and you can decide whether to keep it or drop it. Cleaning this kind of messiness is covered in detail in Cleaning Messy Data with pandas: Missing Values, Duplicates, and Data Types.
If you want pd.concat() to be strict — to raise an error if columns don't match rather than silently filling with NaN — there's no direct parameter for that, but you can check before concatenating:
# Verify all DataFrames have the same columns
column_sets = [set(df.columns) for df in dataframes]
if len(set(map(frozenset, column_sets))) > 1:
print("Warning: column mismatch detected!")
for i, cols in enumerate(column_sets):
print(f" File {i}: {cols}")
This kind of defensive check is good practice in automated pipelines where you're not manually inspecting every input file.
At this point, you have all the pieces. Let's assemble them into a clean, reusable function — the kind you'd put in a shared module and use across multiple projects.
from pathlib import Path
import pandas as pd
def combine_files(folder_path, pattern='*.csv', add_source=True, **read_kwargs):
"""
Combine all files matching a pattern in a folder into one DataFrame.
Parameters:
folder_path (str or Path): Folder containing the files.
pattern (str): Glob pattern for matching files. Default '*.csv'.
add_source (bool): If True, adds a 'source_file' column. Default True.
**read_kwargs: Additional keyword arguments passed to pd.read_csv or pd.read_excel.
Returns:
pd.DataFrame: Combined DataFrame with all rows from all matched files.
"""
folder = Path(folder_path)
file_list = list(folder.glob(pattern))
if not file_list:
raise FileNotFoundError(f"No files found matching '{pattern}' in '{folder}'")
print(f"Found {len(file_list)} files. Reading...")
dataframes = []
for filepath in sorted(file_list): # sorted() for consistent ordering
# Choose reader based on file extension
ext = filepath.suffix.lower()
if ext == '.csv':
df = pd.read_csv(filepath, **read_kwargs)
elif ext in ('.xlsx', '.xls'):
df = pd.read_excel(filepath, **read_kwargs)
else:
print(f" Skipping unsupported file type: {filepath.name}")
continue
if add_source:
df['source_file'] = filepath.name
print(f" Read {filepath.name}: {len(df)} rows")
dataframes.append(df)
combined = pd.concat(dataframes, ignore_index=True)
print(f"\nCombined DataFrame: {combined.shape[0]} rows × {combined.shape[1]} columns")
return combined
# Usage
sales_data = combine_files('sales_data', pattern='sales_*.csv')
This function is genuinely useful — it handles both CSV and Excel, prints progress so you know what's happening, raises a meaningful error if nothing is found, and sorts the files for consistent ordering. You could drop this into any project.
Key insight
The **read_kwargs parameter lets you pass any argument that pd.read_csv() or pd.read_excel() accepts — like encoding='latin-1' for files with special characters, or skiprows=2 for files with header junk at the top. This makes the function flexible enough to handle real-world quirks without modifying it each time.
Work through this exercise to confirm you've got the concepts.
Setup: Create a folder called quarterly_sales on your machine. Inside it, create three CSV files manually (or in a text editor) with this structure:
q1_sales.csv:
rep_name,region,deals_closed,revenue
Alice,North,12,48000
Bob,West,9,36000
Carol,South,15,62000
q2_sales.csv:
rep_name,region,deals_closed,revenue
Alice,North,14,56000
Bob,West,11,44000
Dana,East,8,32000
q3_sales.csv:
rep_name,region,deals_closed,revenue,notes
Alice,North,18,72000,Top performer
Bob,West,10,40000,
Carol,South,13,52000,
Dana,East,16,64000,
Now complete these tasks:
glob or pathlib to find all three files automatically.quarter column extracted from the filename (e.g., "q1" from q1_sales.csv).quarter column has the right values.combined.isnull().sum() and explain why notes has missing values.The combined DataFrame is empty or has fewer rows than expected
This usually means your glob pattern didn't match any files — or matched fewer than you thought. Print file_list before the loop to verify it contains what you expect. Double-check the folder path and the pattern. Remember that glob is case-sensitive on Linux/Mac: *.CSV won't match sales.csv.
All my data appears in one column
This happens when you read a CSV file that uses a different delimiter (semicolons, tabs, pipes). Pass sep=';' or sep='\t' to pd.read_csv() to fix it. If you're unsure, open the file in a text editor and look at the first few lines.
Duplicate rows after concatenating
If you ran your combination code twice and appended to the same list, or if the same file was matched by multiple glob patterns, you'll get duplicates. Use combined.drop_duplicates() to clean them up, but first investigate why they appeared. Understanding and handling duplicates is covered in Cleaning Messy Data with pandas: Missing Values, Duplicates, and Data Types.
Memory errors when combining large files If each file is large and you have many of them, loading everything into memory at once might fail. The solution is to process files in chunks or use more memory-efficient data types. See Handling Large Datasets in Python: Chunked Reading, Efficient dtypes, and When to Use Polars for strategies tailored to this problem.
Column order is inconsistent after combining
This is cosmetic but can be confusing. If file A has columns [id, name, revenue] and file B has [name, id, revenue], the combined DataFrame column order depends on which file was processed first. Use combined = combined[['id', 'name', 'revenue']] after concatenating to enforce a specific order.
Date columns loaded as strings
When reading CSVs, pandas can't always auto-detect date columns. If you need date parsing, pass parse_dates=['order_date'] to pd.read_csv(), or convert after combining with pd.to_datetime(). Working with dates properly is covered in Working with Dates and Time Series in pandas: Parsing, Resampling, and Rolling Windows.
You now have a complete, production-ready workflow for combining multiple files into one pandas DataFrame. Here's what you built:
pd.concat(list_of_dataframes, ignore_index=True) stacks DataFrames verticallyglob.glob() or pathlib.Path.glob() finds files by pattern automaticallysource_file or descriptive column before concatenating so you can always trace rows back to their originWith this skill, the "combining files" step of your workflow goes from thirty minutes of copy-paste to seconds of automation.
Where to go from here: Once your data is combined, the next natural step is analyzing it. You'll likely want to group and aggregate your combined dataset to calculate totals by region, month, or product — that's where the real insights live. If you need to combine files from a SQL database rather than a folder of CSVs, check out Reading from SQL Databases into pandas with SQLAlchemy. And when you're ready to turn your combined-and-analyzed data into a polished Excel report automatically, Automating Excel Reports with pandas and openpyxl shows you how to get there.