pandas for Excel Users: Cleaning Messy System Exports

Hello, tech enthusiasts! 👋

You click Export in your company system, and you get a file full of surprises. Names have extra spaces. Some dates are text. Numbers have commas, so Excel thinks they are words. Some rows appear twice. You spend 30 minutes cleaning before you can even start your real work.

Today we will let Python do the cleaning. We will use pandas, a free Python library for working with tables. A library is like a toolbox: someone already built the tools, and you just use them. If you know Excel, pandas will feel familiar. A pandas table is called a DataFrame, and it is very much like a sheet with columns and rows.

What you need

  • Python 3 from https://www.python.org
  • pandas and openpyxl (openpyxl lets pandas read and write .xlsx files)
  • A messy file, for example export.xlsx or export.csv

Install the libraries by typing this in the Command Prompt:

pip install pandas openpyxl

Step 1: Read the file

Create a file called clean.py and start with this:

import pandas as pd

# Read an Excel export (change the sheet name if needed)
df = pd.read_excel("export.xlsx", sheet_name=0)

# For a CSV export, use this line instead:
# df = pd.read_csv("export.csv", encoding="utf-8")

print(df.head())   # show the first 5 rows
print(df.dtypes)   # show the type of each column

df.dtypes is very useful. If a number column shows object, pandas sees it as text. We will fix that.

Step 2: Clean the column names and the text

System exports often have spaces in the headers, like " Customer ". Let us make the names clean and simple:

# " Invoice Date " becomes "invoice_date"
df.columns = (
    df.columns.str.strip()
              .str.lower()
              .str.replace(" ", "_")
)

# Remove extra spaces from every text column
for col in df.select_dtypes(include="object").columns:
    df[col] = df[col].str.strip()

str.strip() works like the TRIM function in Excel. It removes spaces at the start and the end.

Step 3: Fix the dates

Dates stored as text cannot be sorted or filtered by month. Let us turn them into real dates:

df["invoice_date"] = pd.to_datetime(
    df["invoice_date"], dayfirst=True, errors="coerce"
)
  • dayfirst=True reads 05/03/2023 as 5 March, not May 3. Use it if your system writes the day first.
  • errors="coerce" turns bad values into empty dates (NaT) instead of stopping the script.

Step 4: Fix numbers stored as text

Values like "1,250.00" or "SAR 300" are text. We remove everything that is not a digit, a dot or a minus sign, then convert:

df["amount"] = (
    df["amount"].astype(str)
                .str.replace(r"[^0-9.\-]", "", regex=True)
)
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")

The strange text [^0-9.\-] is a regular expression. It is a small pattern language. This one means “any character that is not a digit, a dot or a minus”.

Step 5: Remove duplicates and empty rows

before = len(df)

# Remove rows that are exactly the same
df = df.drop_duplicates()

# Or remove rows with the same invoice number, keeping the first one
df = df.drop_duplicates(subset=["invoice_number"], keep="first")

# Remove rows where the amount is missing
df = df.dropna(subset=["amount"])

print(f"Removed {before - len(df)} rows")

Printing how many rows you removed is a good habit. If the number is huge, something is wrong, and you will see it fast.

Step 6: Save the clean file

df.to_excel("export_clean.xlsx", index=False)
print("Saved export_clean.xlsx")

index=False stops pandas from adding an extra column with row numbers. Run the script:

python clean.py

Open export_clean.xlsx. The dates are real dates, the amounts are real numbers, and the duplicates are gone. 🎉 Your original file is not changed, so you can always start again.

Common problems

  • KeyError: 'invoice_date' → The column has a different name in your file. Print df.columns and use the exact name.
  • ModuleNotFoundError: No module named 'openpyxl' → Run pip install openpyxl.
  • Strange letters in a CSV (like é). → Try encoding="utf-8-sig" or encoding="cp1252" in read_csv.
  • PermissionError when saving. → The output file is open in Excel. Close it and run again.

Wrap-up

You now have a cleaning script that you can use every week. Next week, export the new file with the same name, run python clean.py, and the cleaning is done in seconds. The steps never get tired and never forget a step.

Next, try adding a summary at the end, like df.groupby("region")["amount"].sum(). It works like a PivotTable, and you can save it to a second file. Happy cleaning!

fsaeed

, , ,