# Chapter 3 — Data Cleaning with AI

# ── PROMPT 1: detect errors ─────────────────────────────────────
I have a sales dataset. Here are 10 rows:
[PASTE sales_sample_10rows.csv here]

Identify any data quality issues you can see:
- Missing or invalid values
- Values that don't match expected format
- Logical inconsistencies (e.g. total ≠ quantity × unit_price)
List each issue as: Row | Column | Issue | Suggested fix

# ── PROMPT 2: write cleaning steps ──────────────────────────────
I need to clean my sales CSV file. The common issues are:
- Status column sometimes has typos: "Deliverd", "Canceld", "Shiped"
- Some customer names have extra spaces or inconsistent capitalisation
- Some total values don't match quantity × unit_price (rounding issues)

Write the Python code to clean this CSV file using pandas.
Assume the file is called sales.csv and save the cleaned version
as sales_clean.csv.

# ── PROMPT 3: validation rules ──────────────────────────────────
Write Python code that validates a sales CSV file and reports:
1. Rows where status is not one of: Delivered, Shipped, Pending, Cancelled
2. Rows where total ≠ quantity × unit_price (allow €0.02 tolerance)
3. Rows where date is not a valid YYYY-MM-DD format
4. Rows where customer name is empty
Print a summary: total rows, rows with errors, error details.

# ── PROMPT 4: VBA data cleaner ──────────────────────────────────
Write a VBA Sub called CleanData() that:
1. Loops column B (customer) and trims leading/trailing spaces
2. Fixes typos in column I (status):
   "Deliverd" → "Delivered", "Canceld" → "Cancelled", "Shiped" → "Shipped"
3. Recalculates column H (total) = column F (quantity) × column G (unit_price)
   for any row where the difference is > €0.01
4. Reports: "X cells cleaned" in a MsgBox
