Data Cleaning Exercises

Module 14

Reshaping

Pivot collisions, aggregation, unpivoting, and zeros versus absent observations.

  1. Pivot without inventing zeros

    Reshape long data into columns while keeping reported zeros, reported unknowns and absent observations distinct. · 10 min

  2. Unpivot without inventing scores

    Reshape wide question columns into one row per answer, and drop unanswered questions instead of scoring them. · 8 min

  3. Pivot collisions

    Detect two values competing for one pivot cell and resolve them with a declared rule. · 8 min

  4. Unpivot step by step

    Turn question columns into rows and drop unanswered questions. · 6 min

Module 14 quiz

Practice questions: choose an answer and check it to see the explanation. Nothing here is timed or graded.

  1. 1. When pivoting monthly readings, a month has no reading for an entity. What should the cell hold?
  2. 2. Why declare the pivot's output columns (Jan, Feb, Mar) instead of letting the data decide?
  3. 3. A pivot finds two values for the same entity and month. What is this called and what should happen?
  4. 4. An entity reported Jan and Mar but not Feb. After pivoting, what should the Feb cell contain?
  5. 5. Unpivoting a wide survey produces rows for unanswered questions. What should you do with them?
  6. 6. Long table with 3 entities × 3 months (all reported). After pivoting months to columns, how many rows?
  7. 7. When is long format better than wide?
  8. 8. A pivot needs columns Jan–Dec, but only Jan–Mar appear in the data. What should the output contain?