From raw files to an analysis-ready table

capstone · 90 min · Objective 2.3

Task

The domain 2 capstone. Build one analysis-ready table of orders from the raw files: invalid rows removed and logged, customers deduplicated, products joined, net value and month derived, and every step checked by a count or a total. It is the preparation every later analysis depends on.

Steps

  1. Remove orders with a quantity of zero or less from data/orders.csv, logging each to lab/ready/rejected.csv with header order_id,reason.
  2. Deduplicate data/customers_raw.csv as in the joins applied lab, and map its regions to the four standard values.
  3. Join the valid orders to the customers and to data/products.csv, and derive net_amount and month (YYYY-MM).
  4. Write lab/ready/orders.csv with header order_id,month,customer_id,region,category,quantity,net_amount.
  5. Write lab/ready/checks.txt with rows: N, rejected: N, unmatched: N and net total: N (two decimal places).

Verify

These checks run in a POSIX shell with Python 3: Terminal on macOS or Linux, and on Windows Git Bash (it comes with Git for Windows) or WSL. On Windows, python3 is often a Microsoft Store placeholder that prints “Python was not found”; run alias python3=py in Git Bash first.

grep -E '^(rows|rejected|unmatched|net total): ' lab/ready/checks.txt
awk -F, 'NR>1 {c[$4]++} END {for (r in c) print r": "c[r]}' lab/ready/orders.csv | sort
python3 -c "import csv; print(round(sum(float(r['net_amount']) for r in csv.DictReader(open('lab/ready/orders.csv'))), 2), 'recomputed from the file')"

1997 rows, 3 rejected and 0 unmatched; four regions only; and a net total of 354627.14 both in the checks file and recomputed from the table itself. That figure is the second of the three revenue totals in the reconciling lesson, and now you know exactly which rows make it so.

This is an independent study companion for CompTIA Data+ DA0-002 and is not produced by or endorsed by CompTIA.