Missing Comes in Five Spellings, and So Does Same
In one line
Within the same file, "missing" arrives written in five shapes, and "the same" is used in three senses. Both have to be handled by rules, not by notation, so that the numbers do not wobble.
Why this was needed
You received the customer roster and computed the average purchase amount. It came out to 57,500 won. The customer says it is 46,000 won. You are looking at the same file, yet the answers differ.
The cause is usually in one place. For the rows with no value in the amount cell, one side counted them leaving them out, and the other side counted them filling in 0. But what is more troublesome is that neither side knows it is doing so. The average function of a spreadsheet leaves out blank cells, and code written in Python often fills in 0 with float(x or 0). Neither raises an error.
On top of this, a notation problem overlaps. Within one file, missing arrives mixed as a blank cell, NULL, NA, -, 0, and a sentinel date such as 9999-12-31. With three systems there are three notations, because nobody unified them when they were merged.
How it works
The first job is counting. Before fixing, you first have to produce how many cases there are per notation. Without that table, whatever handling you apply, you cannot judge whether that handling was right.
The second is separating 0 from missing. These two are different facts. A customer whose purchase amount is 0 won is "signed up but has not bought yet", and a customer whose purchase amount is empty is "we do not know how much". When computing the average, the former must go into the denominator and the latter must be left out. But if the file says 0, you cannot tell from the file alone which of the two it is. So you count the 0s separately and report them, and ask the business which it is.
같은 400행, 같은 금액 칸
결측을 0 으로 채운 평균 46,000원 분모 400
결측을 뺀 평균 57,500원 분모 320
0 까지 뺀 평균 61,333원 분모 300
All three numbers are right. What differs is what was counted, and a report that does not say so is a wrong report.
The third is the sentinel value. 9999-12-31 is a notation that forces "not finished yet" into a date cell. If you accept this as a date and average the processing time, it comes out to thousands of years. It looks silly, but in reality even if only a few such values are mixed in, the average quietly inflates and nobody notices.
What it looks like in the field
Next is duplicates. "The same" has three meanings.
| Kind | What it is | How to handle it |
|---|---|---|
| Exact duplicate | The identical line, down to the character, twice | Keep one. There is nothing to judge |
| Key duplicate | The same identifier but different content | It cannot be fixed automatically. Ask the business which side is right |
| Same only to human eyes | The same thing with only the notation different | Normalize and judge by setting rules |
The third is the hard one. 문샵, (주)문샵, and 문샵 주식회사 (Moonshop written three ways, with and without the Korean company-form marker) are one to a person but three to a machine. So you normalize before comparing — strip the leading and trailing whitespace, remove the corporate-form marker, match the case, and keep only the digits of the phone number.
The most common accident happens here. Normalization is for comparison, not grounds for merging. Two values becoming the same after normalizing the names does not mean they are the same company. The branches may differ, or the names may coincide by chance. So the judgment rule looks at several fields together — for example, "if the normalized emails are the same, it is the same person" or "if both the name and the phone number are the same, it is the same person."
And a field that contains missing values cannot be used as a judgment key. If you group the rows whose email is empty as "the emails are the same", dozens of different customers become one person. This accident is quiet, and judging from the result alone, it looks as if the deduplication went very well.
The last is blocking. If you pair up and compare all 10,000 rows, it is about 50 million pairs. So you first divide the rows with a cheap key and compare only within the same block. Something like the last four digits of the phone number becomes the block key. Blocking is not free — rows in different blocks never meet. So once you have chosen the block key, you measure "how many pairs does this key miss?" by exhaustive comparison on a small sample, and write that loss in the document.
What really matters in practice
- Count before fixing. The table of counts per notation is the basis for every judgment.
- When you state an average, state the denominator with it. A report with only one number always gets asked again.
- 0 and missing are different facts. If you cannot tell from the file alone, ask the business and write the answer in the document.
- Normalization is for comparison; judgment is by rules. And do not use a field that contains missing values as a key.
- Measure the loss of blocking. The pairs that never met are quiet.
What you will do in the next lab
You build yourself a customer roster merged from three systems, count the notations of missing by kind, and put three averages 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. You also see in numbers how a sentinel date wrecks the average processing time. Then you separate exact duplicates from key duplicates, narrow the candidates to compare by normalization and blocking, and group the same person by judgment rules. In the end you measure by exhaustive comparison the pairs that blocking missed and report even that loss. The grader makes a different roster each time, actually runs your tool, and checks the answers.