Clean and prepare

01The problem

You have a data quality log with five decisions in it. Now you have to carry them out, and in a way that survives next month. Voltline's tills export a new file every month with the same problems. If your cleaning is a series of manual edits in Excel, you'll redo it every month, slightly differently each time, and nobody will be able to check it.

The goal is a cleaning process that's repeatable (rerun it on new data with one click or one command), documented (each step says what it does), and reconciled (you can show exactly which rows were removed and why).

02The concept

The cleaning steps for Voltline

1. Remove exact duplicate rows (Surulere's double upload).
2. Exclude test transactions (product_code = 'TEST').
3. Take the store from the transaction ID: the first three characters of txn_id.
4. Convert dates to real dates, reading DD/MM/YYYY day first.
5. Calculate net sales: qty × unit_price − discount. Returns have a negative quantity and a negative discount, so they reduce net sales automatically.
6. Keep the raw columns you might need later, and drop the ones you've replaced (branch).

The order matters. Remove duplicates before you calculate anything. Exclude test rows before you check the totals. And convert dates before you filter by month.

Reconcile

Write down the row counts at every step: raw rows, minus duplicates, minus test rows, equals clean rows. If the counts don't add up, a step did something you didn't intend. It's the cleaning equivalent of balancing a bank statement.

03Example

The same cleaning in three tools. Pick the one you'll use for the project.

SQL (in SQLite, DB Browser for SQLite or any database you load the CSVs into):

CREATE VIEW sales_clean AS
SELECT DISTINCT
  txn_id,
  line_no,
  substr(txn_id, 1, 3) AS store_code,
  CASE
    WHEN txn_date LIKE '__/__/____'                     -- Port Harcourt: DD/MM/YYYY
      THEN substr(txn_date, 7, 4) || '-' || substr(txn_date, 4, 2) || '-' || substr(txn_date, 1, 2)
    ELSE txn_date
  END AS sale_date,
  product_code,
  qty,
  unit_price,
  discount,
  qty * unit_price - discount AS net_sales,
  payment_method
FROM sales_raw
WHERE product_code <> 'TEST';

pandas:

import pandas as pd

raw = pd.read_csv("raw/sales_raw.csv", dtype={"txn_date": str})
sales = (
    raw.drop_duplicates()
       .query("product_code != 'TEST'")
       .assign(
           store_code=lambda d: d["txn_id"].str[:3],
           sale_date=lambda d: pd.to_datetime(d["txn_date"], format="mixed", dayfirst=True),
           net_sales=lambda d: d["qty"] * d["unit_price"] - d["discount"],
       )
       .drop(columns=["branch", "txn_date"])
)
print(len(raw), len(raw.drop_duplicates()), len(sales))

Power Query: Home → Remove Rows → Remove Duplicates; filter product_code to exclude TEST; Add Column → Extract → First Characters (3) on txn_id; for the date, add a custom column that uses Date.FromText([txn_date], [Format = "dd/MM/yyyy"]) when the text contains "/" and Date.FromText([txn_date]) otherwise; then a custom column for net sales.

04Walkthrough

1. Copy the raw files into a raw/ folder and never edit them.
2. Build your cleaning steps in your chosen tool, one step at a time, checking the row count after each.
3. Fill in a reconciliation table: raw rows → after removing duplicates → after removing test rows. It should read 27,978 → 27,602 → 27,588.
4. Check the date conversion: count rows per month for Port Harcourt. Every month from January 2025 to June 2026 should appear, with no dates after June 2026.
5. Check net sales: returns should appear as negative net sales, and the total for a test day should match a hand calculation of a few lines.
6. Save the clean table to clean/sales_clean.csv (or keep the query or view), and note the steps in your cleaning log.

05Practice

06Check your understanding