Two Different Averages: Rules for Missing and for Same

Goal

You build nulls.py, a tool that counts the notations of missing by kind, shows in numbers how the average changes when missing is filled with 0, separates exact duplicates, key duplicates, and things that are the same only to human eyes, and groups the same person by normalization, blocking, and judgment rules.

Why it matters

It is common for two averages to come out of looking at the same file. One side counted leaving out the rows with no value, and the other side counted filling them with 0. Neither raises an error, so nobody notices. That is why, when you state an average, you must state the denominator with it, and to do that you first have to count the cases per notation. 0 and missing are different facts. A customer with 0 won is "has not bought yet" and a customer with a blank cell is "we do not know". If the file says 0, you cannot tell from the file alone which of the two it is, so you count the cases separately, report them, and ask the business. Duplicates are not of one kind either. A line identical down to the character needs only one kept, but lines with the same identifier and different content cannot be fixed automatically, and the same thing with only the notation different has to be judged by rules. The most common accident here is merging just because the normalized values became the same. The grader does not trust your wording. It sets up a roster it made in a temporary directory, actually runs your tool, and checks the counts, the averages, and the grouped clusters against the values it counted itself. The names and amounts change on every run.

Steps

Create and run /root/miss/gen_customers.py to produce /root/miss/raw/customers.csv. It contains missing notations and three kinds of duplicates.
Build census <파일> <칼럼> (the placeholders are the file and the column) in /root/miss/nulls.py so that it counts the notations of missing by kind.
Add stats <파일> <칼럼> so that it outputs side by side the average with missing filled with 0, the average with missing left out, and the average with 0s left out as well, and write the result in /root/miss/stats.json.
Add aging <파일> so that it measures how a sentinel date wrecks the average processing time, and write the result in /root/miss/aging.json.
Add dupes <파일> so that it counts exact duplicates and key duplicates separately.
Add blocks <파일> so that after normalization it divides into blocks by the last four digits of the phone number, and outputs how far the candidate pairs to compare were reduced.
Add match <파일> so that it groups the same person by judgment rules and even measures the pairs that blocking missed. Write the rules in /root/miss/rules.json.
Process everything in one go to produce /root/miss/miss_report.json and /root/miss/miss_report.md.

Notes

Execution contract: python3 /root/miss/nulls.py <명령> <파일> [칼럼] (the placeholders are the command, the file, and the column). census and stats take one more argument, a column name, and aging, dupes, blocks, and match take only the file. On success the exit code is 0, if the file does not exist it is 3, and if the command or the number of arguments is wrong it is 2.
The notations counted as missing are the blank cell and NULL, NA, N/A, NONE, -, and ?, and case is not distinguished. The sentinel date is 9999-12-31. 0 is not counted as missing and is counted separately — because you cannot tell from the file alone whether it is a real 0 or a filled-in 0. This list is an assumption of this lab.
census response: {"column": 이름, "total": 정수, "blank": 정수, "tokens": {"NULL": 정수, ...}, "zero": 정수, "sentinel": 정수, "present": 정수} (the Korean words in the code mean "name" and "integer"). The keys of tokens are normalized to uppercase. present is the value obtained by subtracting blank, sentinel, and the sum of tokens from total.
stats response: column, n_all, sum_naive, mean_naive, n_known, sum_known, mean_known, n_nonzero, mean_nonzero, and gap. The averages are rounded at the second decimal place. mean_naive is the one with missing filled with 0, so its denominator is n_all, and mean_known has n_known as its denominator. gap is the value of mean_known minus mean_naive.
aging response: n_all, n_closed, open_count, unknown, avg_days_naive, and avg_days_closed. avg_days_naive is the average obtained by accepting the sentinel date as a date as it is, and avg_days_closed is the average with the sentinel date left out. If the start date or the end date is missing, it is counted as unknown.
dupes response: rows, exact, key_dupes, and keys. exact is the count of the excess beyond the first of identical lines that appear several times, and key_dupes is the number of keys for which record_id appears two or more times.
Normalization rules: for names, strip leading and trailing whitespace → remove corporate-form markers ((주), ㈜, (유), 주식회사, and 유한회사, the Korean markers for a joint-stock company and a limited company) → collapse to single spaces → lowercase. For emails, strip leading and trailing whitespace and then lowercase. For phone numbers, keep only the digits.
The block key is the last four digits of the normalized phone number, and NONE if there are not four digits. blocks response: rows, blocks, candidate_pairs, and pairs_without_blocking.
Judgment rules: if the normalized emails are non-empty and the same, it is the same person, or if the normalized name and phone number are both non-empty and the same, it is the same person. match response: rows, clusters, merged, groups, and recall_loss. groups holds only the clusters in which two or more lines are grouped, and each cluster is an array of the row numbers (from 1), excluding the header, in ascending order. recall_loss is the number of pairs found by exhaustive comparison minus the number of pairs found within blocks.
Official documents: python csv · python statistics · python datetime · python collections
Common mistakes: filling missing with 0 but leaving the denominator as the total, computing with the sentinel date as a date, grouping rows whose email is empty, merging because the normalized values became the same, and not measuring the loss of blocking.

Build the roster merged from three systems

Create and run /root/miss/gen_customers.py to produce /root/miss/raw/customers.csv. It contains five notations of missing, along with exact duplicates, key duplicates, and the same person written differently.

First build 36 people, and then put 12 of them in again with only the company name notation and the phone number format changed. Next add exact duplicates, copied straight from earlier lines, and lines that share only a record_id but are different people. Mix blanks, NULL, NA, hyphens, 0, and the sentinel date into the amount and the end date.

Count the notations of missing by kind

Build census <파일> <칼럼> (the placeholders are the file and the column) in /root/miss/nulls.py so that it outputs total, blank, tokens, zero, sentinel, and present. 0 is not counted as missing but counted separately.

Strip only the leading and trailing whitespace from the value, convert to uppercase, and compare with the list. The reason not to put 0 among the missing is the heart of this step — you cannot tell from the file alone whether it is a real 0 or a filled-in 0, so counting it and asking the business is the right thing.

The average filled with 0 and the average with missing left out

Add stats <파일> <칼럼> (the placeholders are the file and the column) so that it outputs the three averages side by side, and save the result for the amount column in /root/miss/stats.json.

The difference between the three averages is the denominator. If you fill missing with 0, the sum stays the same and only the denominator grows, so the average goes down. The average with the 0s left out as well has a smaller denominator, so the average goes up. All three numbers are right, and what is wrong is a report that does not say what was counted.

The thousands of years that 9999-12-31 made

Add aging <파일> (the placeholder is the file) so that it outputs together the average obtained by accepting the sentinel date as a date and the average with it left out, and save the result in /root/miss/aging.json.

9999-12-31 is not a date but a marker for 'not finished yet'. If you calculate with it as a date, the average becomes thousands of years. It looks silly, but if only a few are mixed in, the average quietly inflates and nobody notices. If the start date or the end date is missing, leave it out of the calculation and count it as unknown.

Separate exact duplicates from key duplicates

Add dupes <파일> (the placeholder is the file) so that it outputs rows, exact, key_dupes, and keys. exact is the excess beyond the first of identical lines that appear several times, and key_dupes is the number of keys for which record_id appears two or more times.

The reason to separate the two kinds is that their handling policies differ. A line identical down to the character needs only one kept, with nothing to judge, but for lines with the same identifier and different content, the machine does not know which side is right. Such keys must be left as a list and handed to the business.

Normalize and narrow the candidates by blocking

Add blocks <파일> (the placeholder is the file) so that it outputs rows, blocks, candidate_pairs, and pairs_without_blocking. The block key is the last four digits of the normalized phone number, and NONE if there are not four digits.

If you pair up and compare everything, the count grows in proportion to the square of the row count. If you first divide by a cheap key, the pairs to compare drop greatly. Normalization is for comparison and does not merge anything in this step — the merging is done by the rules of the next step.

Group the same person, and the pairs blocking missed

Add match <파일> (the placeholder is the file) so that it outputs rows, clusters, merged, groups, and recall_loss. Write the rules in /root/miss/rules.json as block_key, normalize, match, and not_match.

If you group rows whose email is empty as 'the emails are the same', dozens of different customers become one person — be sure to put 'non-empty' in the rule. recall_loss is the pairs found by exhaustive comparison minus the pairs found within blocks. It is the place to leave, as a number, the fact that blocking is not free.

Report missing values and duplicates on one sheet

Process everything in one go and write rows, missing, mean_naive, mean_known, exact, key_dupes, clusters, merged, and recall_loss in /root/miss/miss_report.json, and write /root/miss/miss_report.md in four sections: ## 없음은 몇 가지로 적혀 있었나 ## 0 으로 채우면 무엇이 달라지나 ## 무엇을 같은 것으로 봤나 ## 남은 위험 (the Korean headings mean "In how many ways was missing written", "What changes when you fill with 0", "What we treated as the same", and "The remaining risks").

missing is an object keyed by column name that holds the census result. Put in the two columns amount and closed_at. In the report, write the three averages together with their denominators, and write the number of pairs blocking missed under the remaining risks — nobody reports those pairs.