# CSV cleanup demo v2

This is synthetic sales collateral. Every person, email, phone, order, SKU, and transaction is invented for the demo. It is not customer data or paid client work.

The deliberately chaotic `raw.csv` has **60 order rows and 24 columns**. The orderly `cleaned.csv` has **45 order rows and the same 24 columns**. Fifteen repeated order IDs were removed: four rows are byte-for-byte duplicates of earlier data rows, and eleven repeat an earlier ID with casing, spacing, or presentation differences. Both files are standard UTF-8 CSVs and keep the same column order.

## What is intentionally wrong in raw.csv

- Casing and surrounding spaces vary heavily in IDs, names, emails, statuses, country names, SKUs, city names, payment methods, channel labels, tracking codes, and customer types.
- Dates use six formats: US `MM/DD/YYYY`, ISO `YYYY-MM-DD`, `DD Mon YYYY`, `YYYY/MM/DD`, US `MM-DD-YYYY`, and US `MM/DD/YY`.
- USD amounts use `$`, `USD`, thousands commas, inconsistent decimal precision, and extra spaces. Quantities include leading zeroes and extra spaces.
- Statuses, payment methods, sales channels, shipping methods, gift flags, priorities, and customer types use inconsistent labels or abbreviations. `pendng`, `Untied States`, and `expres` are intentional, obvious typos.
- Country cells mix names and codes. US/Canada phone numbers use three punctuation styles. Postal codes mix letter casing and surrounding spaces.
- Some emails, phone numbers, notes, gift flags, priorities, and tracking codes are intentionally blank. Blank fields are not invented during cleanup.
- Duplicate IDs appear throughout the sheet, so sorting or scanning only the bottom rows does not hide them.

## Exact cleanup rules

1. Require all 24 columns in the source header and preserve their order. Reject malformed rows with the wrong number of fields.
2. Trim leading and trailing whitespace from every field.
3. Remove embedded whitespace from `order_id`, uppercase it, and keep the **first** occurrence of each normalized ID. Discard later rows rather than merging fields. Remove embedded whitespace from `sku` and `tracking_code`, then uppercase them.
4. Collapse repeated whitespace within text and title-case `customer_name` and `city`. Lowercase `email`. Do not fill blank email cells.
5. Parse only the six date formats listed above. Slash and dash dates in this synthetic source use US month/day order. Output `YYYY-MM-DD`.
6. Treat every amount as USD in this sample. Strip a leading `$` or `USD ` marker and thousands commas from `amount`, `unit_price`, `shipping_fee`, and `tax_amount`, then output exactly two decimals. Convert zero-padded `quantity` to an ordinary integer string. The cleaner does not recalculate `amount` from other columns.
7. Normalize `status` to `Paid`, `Pending`, `Processing`, `Shipped`, `In Transit`, `Completed`, `Cancelled`, or `Refunded`. `complete` maps to `Completed`, US `canceled` maps to `Cancelled`, and the sample typo `pendng` maps to `Pending`. `In Transit` remains distinct from `Shipped`.
8. Normalize `country` codes and known aliases to `United States`, `Canada`, `United Kingdom`, `Spain`, `Germany`, `Japan`, `Portugal`, or `India`. Correct only the explicit sample typo `Untied States` to `United States`.
9. Normalize `payment_method` to `Card`, `PayPal`, `Bank Transfer`, or `Cash`; `credit card` means `Card` and `bank xfer` means `Bank Transfer` in this synthetic source. Normalize `currency` labels `usd`, `US Dollars`, and `$` to `USD` without converting amounts.
10. Normalize `sales_channel` to `Online`, `Marketplace`, or `Store`; `WEB`, `market place`, and `IN STORE` are the documented source aliases. Normalize `shipping_method` to `Ground`, `Express`, or `Pickup`; `expres` and `pick up` are source variants.
11. Normalize `gift_flag` to `Yes` or `No`, `priority` to `Standard` or `Rush`, and `customer_type` to `Retail`, `Wholesale`, or `Subscriber`. Keep blank gift and priority values blank.
12. Normalize only the synthetic US/Canada phone formats in this dataset to `+1` plus ten digits. Keep blank phones blank. Uppercase letters and collapse spaces in `postal_code`; do not infer a different postal code. Trim `notes` without rewriting their content.

The cleaner rejects any unexpected date, amount, status, country, payment method, channel, shipping method, phone pattern, or nonblank flag rather than guessing.

## What was not changed because it needs business judgment

- No missing email, phone, note, tracking code, gift flag, or priority was filled. A real zero amount or fee remains zero.
- Duplicate rows were not combined field by field. The first normalized order ID wins; deciding whether a later row has a correction needs the business owner.
- Names, email deliverability, phone ownership, country truth, postal codes, order validity, payment state, and shipment state were not checked against outside systems.
- Free-text notes were not rewritten. Statuses such as `In Transit` and `Shipped` were not collapsed. Amounts were not converted between currencies, repriced, taxed again, or recalculated.

## Reproduce both files

Python 3 standard library only:

```text
python generate_raw_demo.py raw.csv
python clean_demo.py raw.csv cleaned.csv
```

`generate_raw_demo.py` deterministically builds this synthetic raw sample. `clean_demo.py` applies the documented rules. No network access or private data is used.