Skip to content
academia.sh
tr
Log in

Course Intermediate

Data Collection and Preparation

Published Updated

By the end of this course

Start course

01

Data Collection

Counting how many rows the data loses on its way into the analysis environment: reading the same file with two parsing decisions and the rows that silently drop because of quoted fields, line breaks, and encoding; a query's returned row not matching the unit of analysis and a join multiplying rows; the record skipped by consuming pagination wrongly and the collection time set by a queue that obeys the rate limit; the number of selectors that break when the page structure changes and the limit of access permission; the three answers that three sampling decisions give to the same question.

  1. 01 File Formats The export file that produces the previous course's 2,037-row page is read with two parsing decisions, and two different sets come out: the quote-preserving read extracts all 2,037 records, while the naive split extracts 1,934 records from 2,076 physical lines and drops 142 lines without any warning — 78 half-lines split at a line break inside the address, 64 lines whose fields shifted. Of the remaining 1,934 records, 33 survive but carry a house number shifted out of the address into the reading field; the totals diverge by 984.2 m³, and the region name of 1,157 records does not match the list because the file was decoded with the wrong encoding. With the same 2,037 records held in a columnar structure, learning the total reading means reading 4,074 bytes instead of 108,230.
  2. 02 Pulling Data from Databases Three query forms against the same store return three different row sets, and none of them raises an error: joining reading with subscriber gives 1,877 rows; when the visit table is added with an inner join, 766 rows silently drop and 669 rows multiply — about 76% of the input is corrupted, yet the net row loss is only 97 and the total deviates by just 7.0%, so someone looking at the numbers cannot see the damage. The left join drops no rows but raises the set to 2,546 rows and inflates the total 1.34-fold. The same question gives three numbers under three decisions: 20.61 m³ per reading row, 27.64 m³ per subscriber, 20.21 m³ in the set spread over visit rows.
  3. 03 Collecting Data from Interfaces Three collection decisions against the same interface produce three different sets, and all three responses come back ok: a narrow-scope token collects 1,400 records and leaves out the 477 rows of the past period without ever requesting them, offset-based pagination collects exactly 1,877 rows but never receives 35 records and receives 35 records twice, and cursor-based pagination collects all 1,877 records once. The overall total of the offset-based decision matches exactly at 38,690.7 m³ because the skipped and duplicated records carry the same reading value; the damage shows up only in the region breakdown, as a 70.5 m³ deviation. Under a rate limit, the queue that counts the limit response as an empty page stops at 600 records and loses 1,277 records; the queue that obeys the limit collects the same set completely with 38 requests and 3 wait units, and when the page size is raised to 200 the wait drops to zero.
  4. 04 Structural Parsing A page that publishes the same 1,877 rows is read in four structure versions with two selector sets, and the number of broken selectors turns out not to measure the damage: when a single wrapper layer is inserted, the path selector of the positional set breaks and all 1,877 rows drop, but the failure is loud. When a column is inserted, no selector breaks, 1,877 records come out, none drops, and every one is read from the wrong field; the total deviates by only 60.1 m³, 0.16%, and the region field of 1,877 records is no longer even a region name. When the presentation layer is renewed, this time two selectors of the semantic set break and the records out drop to zero. Comparing the total the page itself reports with the computed total is the only check that catches the silent shift.
  5. 05 Sampling Four samples drawn from a population of 1,877 rows give four different answers to the same two questions: the convenience sample gives a mean of 19.99 m³ and a top-tier share of 33.0%, the simple random sample 20.04 m³ and 32.0%, the sample stratified by period 20.50 m³ and 35.0%, and the sample whose draw unit is the subscriber 20.70 m³ and 35.0%; the population's own answer is 20.61 m³ and 35.4%. The region representation error of the convenience sample is 0.0 points, and that is exactly why it inspires confidence — the period representation error of the same sample is 25.4 points, because all 477 rows of the second period and 1,200 of the 1,400 subscribers were left outside. The same random decision gives, over twelve seeds, a range 3.90 m³ wide for n=50 and 0.71 m³ wide for n=800: the error of a single sample cannot be known, its spread is measured.

02

Data Cleaning

Measuring, with row accounting, the defects that surface once the rows are opened up and the decisions that fix them: whether missingness is random and which group dropping removes disproportionately, the two counts that exact matching and a similarity threshold give and the false matches, the silent coercion that raises no error and yields a wrong value, the part where the sets flagged by the interquartile range and the standard score do not overlap and how removing outliers moves the mean, the number of categories left after category harmonization, the matches that text normalization rescues and the ones it destroys.

  1. 01 Missing Values The reading field of the 2,086 rows the collection step left behind is empty in three different ways: the cell is blank in 104 rows, holds only whitespace in 31 rows, and says 'none' in 70 rows. The missingness is not random: the rate varies by region from 3.7% to 28.1%, and one region carries 2.85 times the number a random distribution would predict. Dropping the rows with missing readings reduces the set from 2,068 to 1,864 rows, leaves the network mean at 22.45 m³, and looks harmless; yet 99 of the 204 dropped rows come from a single region, and that region's share of the set falls from 17.0% to 13.6%, a drop of 3.4 points. A filter that counts only the empty cell as missing fails to see 100 rows: 31 silently become zero consumption, 69 silently fall out of the numeric filter, and the mean sinks to 22.09 m³.
  2. 02 Duplicate Records Three deduplication criteria are applied to the 2,068 rows inherited from the previous lesson, and three numbers come out: comparing full rows merges no row, the raw meter code merges 68 rows, and the trimmed, case-normalized code merges 188 rows — the 120 rows in between are the price of a single text decision. In all 188 records that have a second entry the address is written differently, in 92% the tariff class, in 63.8% the meter code, and in none has the reading changed. In record linkage, a job separate from deduplication, 149 of 240 support requests are linked by raw text and 174 by the simplified code, and six go to the wrong subscriber; 17 of the 18 extra links built at a 0.80 threshold are wrong. The step enters with 2,068 rows and leaves with 1,880 rows.
  3. 03 Type Conversions In the 1,880 inherited rows, 542 of the reading fields are written with a decimal comma and 407 of the index fields with a thousands separator. The common number-conversion call produces no error and stops no operation, yet it corrupts 827 rows: 541 readings and 407 indexes turn into wrong values, the reported consumption total comes out 263.6 m³ short, and the mean index falls from 13,481 to 10,558. In the date field, a parser that does not recognize the format resolves 1,054 of the 1,880 rows correctly, writes 361 to the wrong month, and cannot resolve 465 at all, and these rows silently drop out of the date filter; the number of visits in the January period is reported as 805 instead of 1,388. A conversion that recognizes the format first gives zero corrupted records and zero lost rows on the same set.
  4. 04 Outliers On the 1,880 inherited readings the interquartile range flags 64 rows and the standard score flags 14; the second set is a subset of the first and the non-overlapping part is 50 rows. The difference comes not from the sensitivity of the methods but from the standard score hiding its own outliers: on 1,880 rows the standard deviation is 19.68 m³, it falls to 6.35 m³ once the 64 flagged rows are set aside, and the same three-sigma criterion this time flags zero rows. Of the 15 readings in the model whose decimal point shifted, the interquartile range catches 15 and the standard score catches 14. Setting the flagged rows aside pulls the mean from 22.22 m³ to 20.14 m³ (2.08 m³, 9.4%), the median from 20.31 to 19.85, and the total from 41,774.8 m³ to 36,578.3 m³. The step enters with 1,880 rows and leaves with 1,816 rows.
  5. 05 Fixing Categorical Values The tariff field of the inherited 1,816 rows holds 15 distinct spellings. The normalization ladder brings the category count down from 15 to 14, 9, 8, and 6 in turn, and the semantic mapping from the tariff schedule brings it to 5; locale-blind lowercasing merges only five spellings and leaves an invisible mark on two. The row count never changes, but the insides of the classes do: if workplace and commercial are merged, the category count falls to 4 and 368 rows move to the wrong tariff tier, taking the commercial class from 389 to 757. A seven-entry mapping table written against locale-blind lowercasing leaves 216 unmapped rows, raises no error, and makes the groups sum to 1,600 instead of 1,816 when grouping by tariff class.
  6. 06 Text Normalization The address field of the inherited 1,816 records matches the 1,178 distinct addresses in the subscriber register as raw text in only 325 records. The normalization ladder raises the match in turn to 604, 604, 937, 1,490, and 1,816 records: trimming and space collapsing rescues 279, locale-aware lowercasing 333, mark simplification 553, and abbreviation expansion 326 records — while locale-blind lowercasing rescues zero. In total the match is rescued in 1,491 records. Going one step further, dropping all separators, does not raise the match at all and overlays 26 distinct addresses on 13 keys: 'No 5/2' and 'No 52' fall on the same key and 36 records can no longer be tied to a single subscriber. The step enters with 1,816 rows and leaves with 1,816 rows.

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.

  1. 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³.
  2. 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³.
  3. 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.
  4. 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.
  5. 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.
  6. 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.

Final exam

Data Collection and Preparation — Final Exam

In preparation

This course's question bank is not complete yet. The exam opens here automatically once enough questions are added.

Start typing to search.

↑↓ ↵ Esc navigate · open · close