Unit 02 of 03
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.
Published Updated
Lessons
- 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³. 13 min read
- 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. 13 min read
- 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. 11 min read
- 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. 14 min read
- 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. 14 min read
- 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. 13 min read