Key takeaways
- Inspect the real file structure before writing any transformation.
- Read with explicit encoding, separator, and identifier types.
- Define duplicate and missing-value rules per column, not globally.
- Quarantine rejected rows and record counts so every run is explainable.
On this page
Inspect the file before writing the cleaner
CSV is a container, not a schema. The delimiter may be a comma, semicolon, or tab; the encoding may be UTF-8, UTF-8 with a byte order mark, or a legacy codepage; the header may start on the first row or below a title block. Inspect the first lines in a text editor before writing any transformation.
Record the observed structure in the script: delimiter, encoding, header row, date format, decimal separator. A cleaner built on assumptions will work for the sample and fail on the next export. Where suppliers differ, keep reader settings in a small configuration table rather than scattering them through the code.
Read with explicit settings
Set encoding, separator, and identifier types explicitly. utf-8-sig removes the byte order mark that otherwise becomes part of the first column name. keep_default_na and na_values decide which strings become missing; the defaults treat values such as NA and NULL as missing, which may be correct for one source and destructive for another.
import pandas as pd
raw = pd.read_csv(
'incoming/orders.csv',
encoding='utf-8-sig',
sep=',',
dtype={
'order_id': 'string',
'customer_id': 'string',
'amount': 'string',
},
keep_default_na=False,
)
raw.columns = [
column.strip().lower().replace(' ', '_')
for column in raw.columns
]Standardise names and categories
Column names should be predictable: lowercase, underscores instead of spaces, no punctuation. Do this once, immediately after reading, and keep a mapping to the original names. For values, strip surrounding whitespace, standardise case only where the meaning is unambiguous, and map known variants such as UK, U.K., and United Kingdom to one label.
Do not normalise free-text fields such as complaint notes or product descriptions. Aggressive lowercasing and punctuation removal destroys information a reviewer needs. Restrict standardisation to categorical fields with a defined set of valid values, and log any value outside that set.
Define duplicates and missing values
A duplicate is a business decision. In an order file the order ID may be unique; in a line-level file the key is the order ID plus line number. Deduplicate on that key, choose which record to keep using a stated rule such as the latest update, and report how many rows were removed.
Missing is not the same as zero. A missing quantity may mean the field was not supplied; zero means none were ordered. Decide per column whether to fill, flag, or reject, and keep a flag column so a filled zero can be distinguished from an observed one.
| Problem | Symptom | Handling |
|---|---|---|
| Byte order mark | First column name looks corrupted | Read with utf-8-sig |
| Currency symbols | Amount column is text | Strip symbols, convert, count failures |
| Leading zeros | Customer codes lose digits | Store as text at source; dtype cannot restore lost digits |
| Repeated header rows | Header text appears in the data | Detect and move those rows to rejects |
| Mixed date formats | Some dates become null | Parse per source format and quarantine failures |
| Duplicate records | Totals are inflated | Define the key and a keep rule, then report removals |
Quarantine rows instead of dropping them
A cleaner should produce two outputs: the clean dataset and a reject file with a reason for every excluded row. Rejected rows are evidence. They show whether the supplier export is improving, whether a date format changed, or whether a key is being reused.
Write a short run summary: input rows, output rows, rejected rows by reason, and the source file. Store it beside the output with the run date. When someone asks why a revenue total moved, the summary is the first place to look.
- Validate the header against the expected column set before transforming anything.
- Count rejected rows by reason, not just in total.
- Write a manifest with input file, row counts, and output path.
- Fail the run when the rejection rate crosses an agreed threshold.
Make the script safe to run again
The same input should produce the same output. Avoid adding the current timestamp to every row unless it is a deliberate audit field. Write to a temporary file and replace the previous output only when the run succeeds, so a failure does not leave a partial file in place.
Keep input paths, output paths, and rules at the top of the script or in a small configuration file. Add a check that fails when the input file is older than expected. A scheduled job that quietly reprocesses last week's file is worse than one that stops.
Frequently asked questions
Should I clean CSV files with pandas or the csv module?+
Use pandas when the data fits in memory and you need joins, grouping, or type conversion. Use the csv module when you need to stream a very large file or handle unusual quoting without loading it all.
What should happen to rows that fail validation?+
Write them to a reject file with the reason and the source file name. Do not drop them silently. Review the reject count each run and investigate a sudden change in the pattern.
How do I handle a supplier that changes the file format?+
Validate the header before transforming, keep reader settings per source, and fail with a clear message when the expected columns are missing. A format change should stop the run, not produce a plausible but wrong output.
How do I test a cleanup script?+
Build a small fixture that reproduces the real structure, including a duplicate, a bad date, a currency value, and a blank field. Assert the output counts and the reject reasons, then add the fixture to the repository with synthetic values.
Need help applying this?
Improve your data cleanup & pipelines.
Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.
Free KPI definition templateRelated reading
Python pandas recipes for cleaning business data
Practical pandas patterns for loading, standardising, deduplicating, and validating business data without hiding problems.
Git for analysts: versioning SQL and scripts
How analysts can use Git to version SQL, Python, and reporting logic: repositories, commits, branches, review, and secrets.