AleFrontier

Sample Case: Cleaning a Messy Orders CSV

Sample Case: Cleaning a Messy Orders CSV

Disclaimer: This is a synthetic example created for demonstration and learning purposes, using entirely invented data and scenarios.

Messy CSV files blur the picture. Clean rows and consistent formats make decisions sharper. This case walks through a small orders file and shows how a simple, disciplined Diagnose → Clean → Deliver pass turns chaos into something you can actually trust.

1. Starting Data Issues

The starting file is an invented orders.csv with 120 rows. On the surface, it looks normal: order IDs, dates, customer names, and totals. Under the hood, the audit turned up five concrete problems:

  • Mixed date formats in the same column: some rows use DD/MM/YYYY, others use YYYY-MM-DD.
  • Currency symbols mixed with plain numbers in the Total column: $89.50 on one row, 102.00 on the next.
  • Three fully duplicate rows where Order ID, Customer Name, and Total all match.
  • Blank Customer Name on 11 rows. The orders are valid but the name field is empty.
  • One phantom column to the far right, with no header and only empty cells.

Here is a small slice of the uncleaned file (4 sample rows):

Order ID,Order Date,Customer Name,Total,Unnamed Column
A-1001,2024-01-05,Riverline Studio,$89.50,
A-1002,07/01/2024,,102.00,
A-1003,2024-01-08,Neon Grid Co,$75,
A-1003,08/01/2024,Neon Grid Co,$75,
A-1004,09/01/2024,,$120.00,

Even in a tiny sample, you can see the mixed date formats, the currency symbols, the blank names, and one duplicate row waiting to cause double counting.

2. Cleaning Rules Applied

Instead of guessing, each issue gets a clear rule. That keeps the work reproducible and easier to review.

  1. Mixed date formats → Standardised all values in Order Date to ISO 8601 (YYYY-MM-DD) by parsing both DD/MM/YYYY and YYYY-MM-DD inputs and reformatting to a single, consistent style.
  2. Currency symbols in Total → Stripped all currency symbols and commas from Total and converted the column to a plain decimal number suitable for numeric calculations.
  3. Duplicate rows → Removed 3 duplicate rows after confirming that Order ID, Order Date, Customer Name, and Total were all identical.
  4. Blank Customer Name → Flagged the 11 rows with a blank Customer Name by filling the field with [Unknown] instead of deleting the orders.
  5. Phantom column → Deleted the empty, unnamed rightmost column because it contained no header and every cell was blank.

These rules are simple on purpose. The goal is clear data, not clever tricks.

3. Before and After

Below is a compact view of how a few rows looked before and after the clean. The focus stays on the four core columns.

BEFORE

Order ID,Order Date,Customer Name,Total
A-1001,2024-01-05,Riverline Studio,$89.50
A-1002,07/01/2024,,102.00
A-1003,2024-01-08,Neon Grid Co,$75
A-1003,08/01/2024,Neon Grid Co,$75
A-1004,09/01/2024,,$120.00

AFTER

Order ID,Order Date,Customer Name,Total
A-1001,2024-01-05,Riverline Studio,89.50
A-1002,2024-01-07,[Unknown],102.00
A-1003,2024-01-08,Neon Grid Co,75.00
A-1004,2024-01-09,[Unknown],120.00
A-1005,2024-01-10,Midnight Relay,64.25

In the cleaned slice you can see: dates lined up in one format, totals as pure numbers, duplicate orders removed, and missing names clearly marked instead of silently dropped.

4. Documentation Notes

Every step in the clean was logged. The cleaning log captured the decision, the exact rule, the row count touched, and a short note on why that move was chosen.

  • For date standardisation, the log notes that 120 rows were checked, 120 values were converted to YYYY-MM-DD, and that both DD/MM/YYYY and YYYY-MM-DD inputs were accepted and normalised.
  • For totals, the log records that the Total column was cast to numeric, all dollar signs and commas were removed, and 120 rows successfully converted without loss of precision.
  • For duplicates, the log lists the 3 removed rows by Order ID and confirms that every field matched a surviving row, so no unique information was lost.
  • For blank names, the log explains that 11 rows were updated to [Unknown], keeping revenue accurate while making the missing customer detail obvious for future follow-up.
  • For the phantom column, the log states that the unnamed column held 120 empty cells and was removed to prevent confusion in later analysis.

Documenting these choices means anyone can retrace the work, understand the tradeoffs, and repeat the same clean on the next messy orders CSV.

Leave a comment