Why is your data always messy?
If you work in an office or run a shop, you deal with data every day: CSVs exported from systems, Excel files from suppliers, spreadsheets colleagues typed by hand. Nine times out of ten they come with the same problems — duplicated rows, spaces before and after phone numbers, an amount column that mixes text and numbers, and plenty of blank cells. Fixing it by hand means rechecking every row and starting over when you slip — easily an hour gone. With Python and pandas, you can clean it up in ten minutes and keep the script to reuse next time.
Step 1: Install pandas and read your file
Install pandas first, then read your data with read_csv. If your data is in Excel, swap it for read_excel.
import pandas as pd
# Read the messy data
df = pd.read_csv("orders.csv", encoding="utf-8")
# Peek at the first rows and see what's wrong
print(df.head())
print(df.info())print(df.head()) shows the first five rows, and print(df.info()) tells you how many blank cells each column has. This step matters — you need to see where the mess is before you can clean it.
Step 2: Drop duplicate rows
The most common problem from exports or manual entry is duplication — one order copied twice, or the same customer entered two times. drop_duplicates() clears them in one go.
# Remove fully duplicated rows
df = df.drop_duplicates()
# Or judge duplicates by one column (keep one row per order number)
df = df.drop_duplicates(subset=["訂單編號"])Step 3: Fill blanks and standardise formats
Blank cells (NaN) break later sums and statistics, and spaces around phone numbers stop you from finding the customer. Handle both together:
# Fill blank customer names with "未知"
df["客戶名"] = df["客戶名"].fillna("未知")
# Treat phone numbers as text and strip surrounding spaces
df["電話"] = df["電話"].astype(str).str.strip()Dates are another common fix. If some are "2026/09/07" and others "07-09-2026", pd.to_datetime standardises them all so you can sort by date later.
df["日期"] = pd.to_datetime(df["日期"], errors="coerce")Step 4: Filter valid rows and export
Finally, drop the useless rows (say amounts of zero or below) and export a clean file. Use utf-8-sig so Chinese text doesn't turn to gibberish when opened in Excel.
# Keep only orders with an amount over 0
df = df[df["金額"] > 0]
# Sort by date
df = df.sort_values("日期")
# Export the clean file
df.to_csv("orders_clean.csv", index=False, encoding="utf-8-sig")
print("Done! Total", len(df), "rows")Once it's clean, automate it
Write the script once and reuse it on every new batch of data — just change the filename. Take it further and combine it with the scheduled-backup and auto-email tricks we've covered: set a schedule to run it daily and arrive at work with a clean report waiting. Data cleaning is the first step of every analysis and automation — only when your data is clean can you talk about forecasting and saving money.