Python Pandas Tutorial: Data Cleaning & Excel Automation
Mastering Data Cleaning & Pandas: 2 Essential Workflows for Developers
Working with real-world data is rarely as simple as it looks in tutorials. You might receive a CSV file with duplicate records, inconsistent dates, and missing values. Or perhaps your team sends you multiple Excel sheets every week, and you have to combine everything into a single report.
At first, these tasks might seem easy to handle manually. But as the amount of data grows, cleaning files and merging spreadsheets can quickly become repetitive and time-consuming.
This is where Python comes in handy.
With libraries like Pandas and Openpyxl, you can automate common data preparation tasks, reduce manual work, and make your workflows easier to maintain.
In this guide, we'll walk through two practical examples:
- Cleaning messy CSV files using Python Pandas.
- Combining multiple Excel sheets into a single master report.
Both examples are useful for developers, beginners, and data analysts who want to spend less time fixing spreadsheets and more time working with their data.
Let's get started!
Watch the Video Tutorial
Prefer learning by watching? Check out the video below and follow along with the tutorial.
If the embedded video doesn't load, watch the video directly on YouTube.
1. How to Clean Messy CSV Files Using Python Pandas
CSV files are widely used for exchanging data between applications, databases, and reporting tools. However, the files you receive are not always ready for analysis.
A typical dataset might contain:
- Duplicate records that affect calculations.
- Missing values in important columns.
- Extra spaces in names and other text fields.
- Dates stored in different formats.
- Inconsistent representations of missing information, such as empty strings, NA, or null.
You could fix these problems manually in a spreadsheet, but repeating the same process every time isn't an efficient solution. Let's automate the process using Pandas.
Step 1: Install Pandas
Make sure Python is installed on your computer. Open your terminal or command prompt and run:
pip install pandasStep 2: Create the CSV Cleaning Script
Suppose you have a file named raw_sales_data.csv containing sales records. Your goal is to remove duplicate rows, handle missing values, standardize date columns, and save the cleaned data to a new file.
Here is the script:
import pandas as pd
def clean_messy_csv(input_file, output_file):
try:
print(f"Loading dataset: {input_file}")
# Read the CSV file
df = pd.read_csv(input_file)
initial_rows = len(df)
print(f"Initial row count: {initial_rows}")
# 1. Remove completely duplicated rows
df = df.drop_duplicates()
duplicates_removed = initial_rows - len(df)
print(f"Duplicate rows removed: {duplicates_removed}")
# 2. Clean text columns
text_columns = df.select_dtypes(
include=["object", "string"]
).columns
for col in text_columns:
df[col] = df[col].str.strip()
df[col] = df[col].replace(
["", "NA", "N/A", "null", "None"],
pd.NA
)
# 3. Handle missing values
for col in df.columns:
if pd.api.types.is_numeric_dtype(df[col]):
if df[col].notna().any():
df[col] = df[col].fillna(df[col].median())
else:
df[col] = df[col].fillna("Unknown")
# 4. Standardize columns containing "date"
date_columns = [
col for col in df.columns
if "date" in col.lower()
]
for col in date_columns:
parsed_dates = pd.to_datetime(
df[col].replace("Unknown", pd.NA),
errors="coerce"
)
df[col] = parsed_dates.dt.strftime("%Y-%m-%d")
print(f"Standardized date column: {col}")
# 5. Save the cleaned dataset
df.to_csv(output_file, index=False)
print(f"Cleaned row count: {len(df)}")
print(f"Cleaned dataset saved to: {output_file}")
except (OSError, ValueError, pd.errors.ParserError) as error:
print(f"Unable to process the CSV file: {error}")
if __name__ == "__main__":
clean_messy_csv(
"raw_sales_data.csv",
"cleaned_sales_data.csv"
)Step 3: Run the Script
Save the code in a file named clean_csv.py. Place raw_sales_data.csv in the same directory and run:
python clean_csv.pyOnce the script finishes, you'll have a new file named cleaned_sales_data.csv. The original file remains unchanged.
How Does the Script Work?
1. Removes duplicate rows: The drop_duplicates() method removes rows that contain identical values across all columns. If your dataset has a unique identifier, you may need to specify which columns determine whether a record is duplicated.
2. Cleans text values: The str.strip() method removes leading and trailing whitespace. This prevents values such as "Vijayawada" and " Vijayawada " from being treated as different strings.
3. Handles missing values: The script uses the median for numeric columns and the placeholder "Unknown" for other columns. Choose a strategy that makes sense for your dataset rather than automatically filling every missing value.
4. Standardizes dates: The script attempts to convert columns containing the word "date" in their names into the YYYY-MM-DD format. Ambiguous dates should be interpreted according to the source file's date conventions.
2. How to Merge Multiple Excel Sheets Automatically Using Python
Imagine you're preparing a monthly sales report. Your team maintains separate worksheets for different regions, such as North, South, East, and West. At the end of the month, you need to combine all those records into one master spreadsheet.
Copying and pasting the data manually might work when you have a few rows. However, it becomes tedious when the workbook contains thousands of records or when the same task needs to be repeated every week.
Fortunately, Pandas can read multiple worksheets and combine their data into a single DataFrame.
Step 1: Install the Required Libraries
Run this command in your terminal:
pip install pandas openpyxlPandas handles data processing, while Openpyxl supports reading and writing Excel workbooks in the XLSX format.
Step 2: Create the Excel Merging Script
Assume you have an Excel workbook named regional_sales_2026.xlsx. It contains separate worksheets for different regions, each using compatible column names.
The following script combines the worksheets into one master report and records the source worksheet for every row.
import pandas as pd
from pathlib import Path
def merge_excel_sheets(input_file, output_file):
input_path = Path(input_file)
output_path = Path(output_file)
# Check whether the input workbook exists
if not input_path.is_file():
print(f"File not found: {input_path}")
return
try:
print(f"Reading workbook: {input_path.name}")
# Read all worksheets
all_sheets = pd.read_excel(
input_path,
sheet_name=None,
engine="openpyxl"
)
if not all_sheets:
print("No worksheets were found.")
return
dataframes = []
for sheet_name, sheet_df in all_sheets.items():
if sheet_df.empty:
print(f"Skipping empty sheet: {sheet_name}")
continue
# Remove completely empty rows
sheet_df = sheet_df.dropna(how="all").copy()
if sheet_df.empty:
continue
print(
f"Processing '{sheet_name}': "
f"{len(sheet_df)} rows"
)
# Record the source worksheet
sheet_df["Source_Sheet"] = sheet_name
dataframes.append(sheet_df)
if not dataframes:
print("No data was available to merge.")
return
# Combine all worksheets
master_df = pd.concat(
dataframes,
ignore_index=True,
sort=False
)
# Create the output directory if necessary
output_path.parent.mkdir(
parents=True,
exist_ok=True
)
# Export the combined data
master_df.to_excel(
output_path,
index=False,
engine="openpyxl"
)
print(f"Total rows merged: {len(master_df)}")
print(f"Master report saved to: {output_path}")
except (OSError, ValueError, KeyError) as error:
print(f"Unable to merge the Excel workbook: {error}")
if __name__ == "__main__":
merge_excel_sheets(
"regional_sales_2026.xlsx",
"master_sales_report.xlsx"
)Step 3: Run the Script
Save the code in a file named merge_excel.py. Place regional_sales_2026.xlsx in the same directory and execute:
python merge_excel.pyThe script creates a new file named master_sales_report.xlsx containing the combined records from all nonempty worksheets.
How Does the Excel Merging Process Work?
1. Reads every worksheet: Setting sheet_name=None tells Pandas to load every worksheet into a dictionary of DataFrames.
2. Tracks the source worksheet: The Source_Sheet column identifies where each record originally came from.
3. Combines the data: The pd.concat() method combines all DataFrames into one. Pandas aligns values by column name, so inconsistent headers can produce separate columns.
4. Exports the report: The to_excel() method writes the consolidated data to a new workbook. Setting index=False prevents the DataFrame index from appearing as an extra column.
3. Bonus: Merge Multiple Excel Files From a Folder
What if your regional data isn't stored in separate worksheets but arrives as multiple Excel files?
For example, your folder might look like this:
sales_data/
north_sales.xlsx
south_sales.xlsx
east_sales.xlsx
west_sales.xlsxYou can use Python's pathlib module to find the Excel files and combine them automatically.
import pandas as pd
from pathlib import Path
def merge_excel_files(folder_path, output_file):
folder = Path(folder_path)
output_path = Path(output_file)
if not folder.is_dir():
print(f"Folder not found: {folder}")
return
# Find XLSX files and ignore temporary Excel lock files
excel_files = sorted(
file
for file in folder.glob("*.xlsx")
if not file.name.startswith("~$")
and file.resolve() != output_path.resolve()
)
if not excel_files:
print("No Excel files found.")
return
dataframes = []
for file in excel_files:
try:
df = pd.read_excel(
file,
engine="openpyxl"
)
if df.empty:
print(f"Skipping empty file: {file.name}")
continue
df["Source_File"] = file.name
dataframes.append(df)
print(f"Loaded {file.name}: {len(df)} rows")
except (OSError, ValueError) as error:
print(f"Skipping {file.name}: {error}")
if not dataframes:
print("No usable data was found.")
return
master_df = pd.concat(
dataframes,
ignore_index=True,
sort=False
)
output_path.parent.mkdir(
parents=True,
exist_ok=True
)
master_df.to_excel(
output_path,
index=False,
engine="openpyxl"
)
print(f"Total rows merged: {len(master_df)}")
print(f"Output saved to: {output_path}")
if __name__ == "__main__":
merge_excel_files(
"sales_data",
"master_sales_report.xlsx"
)This script reads the first worksheet from each XLSX file in the specified folder and combines the records into one report. The Source_File column identifies the original file for each record.
Make sure the files have compatible column structures. This example processes XLSX files only, not the older XLS format.
4. A Few Things to Remember Before Automating Data Cleaning
Automation can save a lot of time, but it's important to make sure your scripts don't accidentally change the meaning of your data.
Don't Remove Duplicates Without Checking Your Data
Two records that look similar aren't always duplicates. A customer might place two separate orders on the same day. Removing records based only on a customer ID could result in lost sales data.
Use drop_duplicates() only when you understand what makes a record unique.
Choose a Sensible Missing-Value Strategy
Filling every missing numerical value with the median might be reasonable for some analytical tasks, but it can be misleading in other situations. Choose a strategy based on the meaning of each column.
Validate Dates Before Using Them
Dates can be particularly tricky when data comes from different countries or applications. A value such as 05/06/2026 could mean June 5 or May 6, depending on the format.
If you know the source uses day-first dates, configure the conversion accordingly and validate the results before using them in reports.
Check the Output Before Sharing It
Even when a script runs without errors, that doesn't automatically mean the output is correct.
For important datasets, compare the number of records before and after processing, check for unexpected missing values, and verify that key columns contain the expected data.
Conclusion
Data cleaning and spreadsheet consolidation are not always the most exciting parts of development, but they're essential to producing reliable results.
With Python and Pandas, you can remove duplicate records, standardize text and dates, handle missing values, and prepare CSV files for analysis. With Pandas and Openpyxl, you can also combine multiple Excel worksheets or consolidate files from an entire folder into a single report.
The real advantage is that once your workflow is written and tested, you can reuse it whenever similar data arrives.
Start with a small dataset, check that the output matches your expectations, and gradually adapt the scripts to your own requirements.
Have you automated any repetitive data-cleaning tasks with Python? Share your approach or questions in the comments!
Comments
Post a Comment