Customer Data Does Not Look Like Sample Data

In one line

The real decision in data cleansing is not which tool you use but who sets the criteria for what to throw away.

Why this was needed

The CSVs in tutorials have a constant number of columns, no blank cells, and only numbers in the number columns. The CSVs customers give you are not like that.

Listing what you actually run into, it goes like this. A row that is one field short. A row with an empty customer name. A row with N/A or - in the amount cell. A negative amount. A name with spaces at the front and back. The same company arriving in two forms, MOONSHOP and moonshop. The same order number appearing twice, with only the date format differing on one side. And 2026-08-20, 2026/08/20, and 08/20/2026 mixed in one file.

The mistake a beginner makes here is to start writing a cleansing script right away. Then two things are sure to happen. First, the script quietly deletes data. Second, the customer later asks, "why are our sales only this much?"

How it works

The order is count, classify, agree, cleanse.

First you count. Out of how many rows in total, how many break the rules. Without this number, you cannot judge whether the cleansing result is right. 5 broken rows out of 226 and 180 broken rows out of 226 are completely different situations, and in the latter case you have to redo the data extraction itself, not the cleansing.

Next you classify. You divide the reasons for breakage by kind. Structural problems (the number of fields), missing values (empty values), type problems (text where a number belongs), range problems (negative or unrealistic values), and duplicates. This classification matters because each kind has a different handling policy. A row whose field count does not match should usually be thrown away, but a row with an empty customer name can be thrown away or filled in with 미상 (the Korean word for "unknown"). That decision is made not by the engineer but by the business owner.

Then comes the agreement. This is the FDE's job. Asking a question like, "the 4 rows with negative amounts look like refunds — should we exclude them from this aggregation, or count them separately?" If you do not ask this question, you have no grounds to explain with later when the numbers do not match.

Last comes the cleansing. And the cleansing script must print the number of rows it threw away. A script that deletes quietly is a time bomb.

What it looks like in the field

Let me introduce the accident that happens most often in duplicate removal.

There is code that groups and tidies with the rule "if the email is the same, it is a duplicate, so keep only one." In most cases it works well. But if there are rows whose email is empty, they all get lumped into a single group, and different customers are all deleted except one.

The same trap exists in name normalization. Stripping the spaces at the front and back and converting to lowercase is a good habit, but after that two originally different customers such as kimcoffee and kimcoffee31 start to look alike. Normalization is for comparison, not grounds for merging.

So here is one practical rule. If the duplicate-detection key can contain missing values, leave the rows with missing values out of the duplicate detection altogether and count them separately.

How do you prove the cleansing result

What you show to prove the cleansing is finished is half of the real work. With "I ran it", nobody can use that number. So the cleansing script must output, together with the result data, a report of what happened.

At minimum these five lines must be there.

• The number of input rows and the number of output rows, and the difference
• The count of discarded rows split by reason
• The count of rows whose values were fixed (whitespace removal, case unification, date format unification)
• The count merged as duplicates, and what was kept when that happened
• The before-and-after values of the numbers the business actually uses, such as a total

The last line is especially important. If the row count dropped by 2% but the sales total dropped by 30%, that is not cleansing but an accident. It means a few large transactions were filtered out, and rows like that are all the more likely to have an unusual format. If you look only at the row count, you miss this.

Making it reversible is also important. Do not overwrite the original, and instead of deleting the discarded rows, leave them in a separate file. Then to the question "why is this order not in the aggregation?" you can answer in seconds. It is good to also write the original line number in the discarded-rows file. The conversation goes faster if the customer can open that line in their own file directly.

Finally, the rules must be written in the same words in both the document and the code, not only in code. If the cleansing rules are only in the code, a few months later nobody knows them, and a new owner builds them again with different rules. From then on two different numbers start coming out of the same original, and the grounds for judging which of the two is right disappear.

In the document, write for each rule the person who decided it and the date. The person to ask whether you may change a rule later has to be written on that line, so that a newcomer does not end up deciding alone and moving on.

What you will do in the next lab

From a 25-row order CSV, you filter out the broken rows by the rules, count the discarded rows, and produce the total of the cleansed result and even a per-day aggregation. The scale is small, but the kinds are just as in the field.