Python pandas recipes for cleaning business data

Start by reading only the columns and rows needed. Use read_csv with usecols, dtype, and parse_dates where the schema is known. Explicit types prevent postcodes losing leading zeros and dates being interpreted differently on another machine.

Inspect the result with shape, head, dtypes, and info before transforming anything. A quick profile of null counts and distinct values will reveal most of the problems. If the file is large, read a sample first and confirm the delimiter, encoding, and header row. Record the source file name, extract date, and row count in the output or log, so a later discrepancy can be traced.

Rename columns to consistent snake_case, strip whitespace, and remove stray punctuation. Standardise category labels before grouping, because 'North', ' north', and 'NORTH' will otherwise become three segments. Keep a mapping table for aliases rather than burying replacements in a long script.

Convert dates deliberately. Where formats vary, parse with an explicit format, then use errors='coerce' and inspect the rows that failed. For money, avoid binary floating-point surprises; store minor units as integers or use a decimal type when exact arithmetic matters.

Define the key that should be unique: invoice number, order line, customer ID plus date, or whatever the business uses. Then inspect duplicates with duplicated(subset=[...], keep=False) before dropping anything. Some repeated rows are legitimate line items.

When duplicates are genuine, choose keep='first', keep='last', or a rule based on updated_at. Record how many rows were removed and why. A silent drop_duplicates call is one of the easiest ways to lose revenue from a report. If duplicates must be aggregated rather than removed, make the aggregation rule explicit and test it against a known total.

Distinguish a missing value from zero, not applicable, and unknown. Each has a different meaning and a different effect on an average. Use isna().sum() and inspect patterns by segment, because missingness is often concentrated in one source or period.

Filling every blank with zero or the median changes the distribution and can create false confidence. Drop rows only when the missing field is essential and the loss is documented. Otherwise keep a flag column so downstream users can see what was imputed. For important fields, report the missing rate alongside the result so readers can judge how much the finding depends on imputation.

Use str.strip(), str.lower(), and str.replace() for routine text cleanup, then map known variants to a controlled list. Normalise whitespace and watch for non-breaking spaces that look identical in a spreadsheet. Where encoding is uncertain, read the raw bytes or specify it explicitly.

Keep the original column alongside the cleaned one during development, then decide whether to retain it in the output. A category dtype can save memory and make valid values explicit, but it must be refreshed when new categories appear.

Check row counts before and after each step, uniqueness on the intended key, date ranges, and numeric bounds. Reconcile an important total with the source system or a trusted report. If a merge is involved, use validate='one-to-many' or the appropriate rule and indicator=True to catch unmatched rows.

Export the cleaned data to a stable format such as Parquet and write a short log of changes. Keep the script under version control and test it against a small fixture. The aim is not a perfect one-off file but a repeatable process that fails loudly when the input changes. A small test dataset with deliberate errors is useful for checking that the script catches what it should.