Pivot to wide, unpivot to long, and check the total survives

short · 40 min · Objective 2.3

Task

Count orders by region and month, pivot the result into a wide table with a column per month, unpivot it back to long, and prove the reshaping moved the numbers around without changing them.

Steps

  1. Join orders to customers in data/shop.db and count orders by region and month. Save the long result to lab/reshape/long.csv with header region,month,orders.
  2. Pivot it to lab/reshape/wide.csv with one row per region and one column per month, 2025-01 to 2025-12.
  3. Unpivot the wide table back to lab/reshape/back.csv with the long header.
  4. Record the grand total of each file in lab/reshape/totals.txt as long: N, wide: N and back: N.

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.

awk -F, 'NR>1 {n++} END {print n" long rows"}' lab/reshape/long.csv
awk -F, 'NR>1 {n++} END {print n" wide rows"}' lab/reshape/wide.csv
awk -F, 'NR>1 {n++} END {print n" rows after unpivoting"}' lab/reshape/back.csv
awk -F': ' '{print $2}' lab/reshape/totals.txt | sort -u

48 long rows, 4 wide rows with twelve month columns, and 48 again after unpivoting -- and a single total, 2000, for all three files. The row count changes with every reshape; the total must not.

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