Unit 03 of 03
Data Transformation
Reshaping a dataset that is organized by its source into one organized by the question, and accounting for the rows of every transformation: the records a join leaves unmatched, duplicates, or drops; whether the conversion between wide and long format is lossless; the rows that remain after aggregation and the within-group variability that disappears; the number of rows where a derived variable is undefined; the wrong result produced by encoding an unordered category as an ordered one; documenting the preparation steps with a script; and how many steps can be reproduced.
Published Updated
Lessons
- 01 Merging and Relating The cleaned 1,329 subscribers and 3,471 readings are joined on a key, and the inner join returns 3,302 rows: 169 readings drop because they cannot find a subscriber and 69 subscribers drop because they carry no readings, all without a single warning. The outer join returns 3,371 rows from the same data. When the grain of the key is chosen wrongly, the reading and visit tables shrink from 3,302 rows to 2,047, but that drop is not a filter: 1,901 readings vanish while 488 readings fan out into 1,134 rows, and total usage shifts from 65,185.6 m³ to 40,374.1 m³. The 103 rows where the same subscriber and period pair appears twice inflate the total by 2,124.9 m³. 5 min read
- 02 Reshaping A long-format dataset of 3,199 rows is converted to a wide format of 1,260 rows and three period columns; 3,199 of the 3,780 cells are filled and 581 stay empty. A dataset of 3,302 rows that carries repeated subscriber and period pairs does not fit the same wide format, and 103 rows are overwritten without a warning. The three choices on the way back give three different row counts: skipping empty cells gives 3,199 rows and a mean of 19.713 m³, filling empty cells with zero gives 3,780 rows and 16.683 m³, and skipping through a truthiness test also deletes 119 genuine zero readings, leaving 3,080 rows and 20.474 m³. In all three paths the total stays fixed at 63,060.6 m³. 12 min read
- 03 Grouping and Aggregation The dataset of 3,199 rows shrinks to 5, 15, 53, and 1,260 rows under four grouping decisions; of the 60 cells of the region, tariff tier, and period cross-break, 7 carry no rows and never appear in the output. Three overall means come out of the same dataset: 19.713 m³ from 3,199 rows, 19.532 m³ from five region rows, and 20.721 m³ from 53 cells. The five-row summary does not carry the fact that 431 of the 3,199 readings deviate from their own group mean by more than 50%, that 58 readings exceed 40 m³, and that the east and west regions swap places in 50 readings. 13 min read
- 04 Derived Variables Two derived variables are added to the dataset of 3,199 rows, and neither changes the row count; 3,199 rows come out. Per-person usage stays undefined in 328 rows, 216 where the household is zero and 112 where the household information is missing altogether, and because both causes merge into the same result they cannot be told apart in the output. Four defensible paths to the same question give four answers: a not-a-number value, 9.972, 8.604, and 7.735 m³. The path that filters out the undefined rows leaves 6,372.0 m³, that is 10.1% of total usage, outside the calculation, and the filter does not fall evenly across regions: the share varies between 5.4% and 21.2%. The period change rate is undefined in 1,334 rows in the long format, while in the expected set of cells 581 cells cannot be counted at all. 15 min read
- 05 Categorical Encoding Three categorical fields of the dataset of 3,199 rows are encoded: ordered encoding adds 3 columns, unordered encoding adds 14 columns, and neither drops a single row. When region, an unordered field, is encoded in order, the code comes from an order that does not exist in the data: three defensible orderings apply the same threshold to the same data and select 2,166, 1,678, and 1,633 rows, with means of 19.741, 18.480, and 20.661 m³. None of the 3,199 rows is selected in all three decisions, and 921 rows are selected in only one. In an ordered field the same experiment gives a single answer: two different encodings yield the same 1,615 rows and the same 19.584 m³ at the satisfaction threshold. 13 min read
- 06 Reproducible Preparation The course's seventeen preparation steps are written as data: 39 decisions, 13 items for each of the three topics. The silent loss the steps measured totals 4,614 records, and only 69 of these occurred in the adopted pipeline; the remaining 4,545 records are the measured cost of the common decision that was not chosen. Three steps take their parameters from the run environment rather than from the script, and two runs over the same data give different results in all three; 14 of the pipeline's 17 steps are reproducible, and once the values are written into the script all 17 are. The readability of decisions rises from 7 of 32 (22%) in cells to 36 of 39 in a script with no ledger, and to 39 of 39 with a run ledger. 13 min read