Data Cleaning: The Steps, in Order, and What Each Risks

What is cleaning, and what is it not?

Start with the thing most people get wrong before they touch a single cell. Finding a fault and fixing one are two different acts, done at two different moments, and often by two different people. Somebody reads the file against the written column list and says this cell cannot be right. Reading and flagging is one job, and it changes nothing at all: the file after it is exactly the file before it, with a note attached. Then somebody sits down and changes something, and from that moment the numbers coming out are different numbers. Only the second act moves a figure, and every step below is the second act. Finding set against fixing has a treatment of its own, so the faults are taken here as already found and worked on.

Cleaning is not tidying. Cleaning does not make a file look nicer and it does not improve anything. Every step below is a decision about what a cell means, and a decision is a thing that can be right or wrong and can be argued with. A blank cell means something. A cell reading Rs 99,999/- means something. Two rows for one stall in one month mean something. Cleaning does not repair the file. Cleaning states in writing what has been concluded about the file, and then acts on that conclusion.

A household sorting out a year of electricity bills before working out what a month costs on average meets the same problem. One bill is missing. One arrived twice. The first copy was reissued after a meter reading was corrected. One reads ten times the usual amount because somebody wrote an extra zero at the counter, and one reads five times the usual amount because the house was full of guests that month and the air conditioner never stopped. None of that is an arithmetic problem. Each sheet of paper needs a decision, taken one at a time, in an order, and remembered afterwards. A run of decisions like that, in order and remembered, is cleaning.

The order matters more than it looks, and so does the writing down. A decision that is not recorded cannot be undone, cannot be checked and cannot be argued with. The figure it produced has to be taken on trust by everybody downstream of it. Six steps follow. Each one is named, each one has an authority behind it, and each one has an effect on a single headline number, reprinted after every step so the movement can be watched.

Why is the source file never written to?

How to Clean Financial Data Without Overwriting the Source

Step one takes no cleverness at all, and it is the step people skip. Skipping it costs nothing on the day and everything three weeks later. The file that arrived from the market office gets copied, and every one of the five steps that follow happens on the copy. The file as it arrived is kept exactly as it arrived, untouched, with its blank cell and its Rs 99,999/- and its two rows for one stall month all still sitting in it.

The everyday version is a household that photocopies the electricity bill before writing corrections on it. The photocopy can be scribbled on freely. When the supplier disputes the correction, the bill itself is still in the drawer, unmarked, and the argument can be settled in a minute. Once the bill itself carries the scribbles, the argument cannot be settled at all. The only record of what the supplier actually sent now has handwriting on top of it.

The whole value of step one shows up on the day somebody disagrees with step four. Step four is where a cell reading Rs 4,80,000/- becomes a cell reading Rs 48,000/-. If the copy was made, that disagreement is a five minute conversation: here is the original, here is the return slip the correction rested on, here is the line in the log. If the copy was not made, the disagreement has no floor to stand on. Nobody can see what the cell said before, nobody can check whether the correction was the one described, and the only honest answer to the question is that the file used to say something else and nobody now knows what.

Notice what step one does to the headline figure. Nothing. Copying a file changes not one cell, so the average takings for each stall month, computed as the sum of the takings cells that carry a usable number divided by how many carry one, is still Rs 21,40,099/- over 31, still Rs 69,035.45/-. Step one is the step that makes every other step reversible, and it buys that for the price of a copy and no movement in any number at all.

What comes off first, and what does that move?

Step two is setting aside what was never a value in the first place. NB-02 Chandan Tea's month 4 takings cell reads Rs 99,999/-. Nothing in that number announces itself. The code is a whole number of rupees, it sits in a column of whole numbers of rupees, it sorts between other figures and it will happily add and average with them. The written column list is the only thing on the table that knows the truth: Rs 99,999/- is the market office's code for no return received. The figure is not money and never was money. The code is the form's way of writing that nothing arrived.

Setting aside means something narrow and worth stating precisely. Setting aside means taking the cell out of the numbers while leaving the row exactly where it is. NB-02 Chandan Tea still traded in month 4, the stall still has a row, the row still carries its identifierA value whose whole job is to say which thing a row is about. It looks like a number here but it is a label, so adding two of them or averaging them produces something that means nothing., its category and its pitch area. The pretence that the takings cell holds an amount is what has gone. Nothing in the takings column could ever have shown this. Step two cannot be done by looking at the figures, only by reading the written column list.

The headline moves. The sum drops from Rs 21,40,099/- to Rs 20,40,100/- and the count of usable figures drops from 31 to 30, so the average takings for each stall month, still the sum of the cells carrying a usable number divided by how many carry one, becomes Rs 68,003.33/-. The fall is Rs 1,032.12/-. Both the top and the bottom of the calculation changed, so the fall is small. Pulling out one large figure also pulls out one of the things being divided by.

There is a habit hiding in this step that is worth naming. An empty cell provokes a question and Rs 99,999/- provokes nothing, so a code that looks like data is more dangerous than a cell that is obviously empty. The code gets added up. The code gets averaged. The code gets quoted in a summary by somebody who never opened the written column list, and the figure they quote is wrong in a way that no arithmetic check will ever catch. The arithmetic is perfect.

How are two rows answering one question resolved, and why does the figure rise?

NB-03 Harit Greens filed month 2 twice. One return reached the market office on day 5 of the following month reading Rs 36,400/-, and a second reached it on day 19 reading Rs 39,700/-. Two rows, one stall, one month. The record is meant to hold one row for each stall in each month, so both rows cannot stand, and the grid is asking one question that has arrived with two answers.

Here is the part that separates a decision from a guess. The rule is stated before the two figures are looked at, not after. The rule used here is keep the later filing, on the reasoning that a return arriving on day 19 after one arrived on day 5 is a revision and the office's own practice is to treat a revision as replacing what it revises. The rule could have been written before anybody knew what either cell said, and being writable in advance is exactly what makes it a rule rather than a preference dressed up as one. Row for row it drops the Rs 36,400/- and keeps the Rs 39,700/-.

Now watch what happens. Most readers quietly stop trusting the run right here. The sum falls from Rs 20,40,100/- to Rs 20,03,700/- and the count of usable figures falls from 30 to 29. The average takings for each stall month, on the same convention as always, becomes Rs 69,093.10/-. The headline goes up by Rs 1,089.77/-, and it goes up because the figure that was dropped, Rs 36,400/-, sat below the average that was being computed. Take something below the average out of an average and what is left rises. There is nothing subtle about that arithmetic and it still surprises people every time.

Cleaning is not the same thing as making a number smaller. Anybody carrying the assumption that a clean figure is a lower figure will meet this step, feel that something has gone wrong, and start looking for a way to make it go the other way. A well run cleaning job turns into a badly run one exactly there: not through a bad step, but through a good step quietly rejected for moving the wrong way.

What does correcting a proven error move?

NB-08 Peeli Mithai's month 3 takings cell reads Rs 4,80,000/-. The market office keeps the stall's own return slip for month 3 on paper, and it reads Rs 48,000/-. Somebody typing the file up added a zero. The correction is obvious and it is right, and the reason it is right needs saying very carefully. The wrong reason is sitting right there and looks identical.

ProvenanceWhere a figure came from and what paper or system it passed through on the way. Two identical numbers can have completely different provenance, and that is often the only thing that separates them. is what authorises this step. There is a document, it says Rs 48,000/-, the cell says Rs 4,80,000/-, and the document wins because the cell is a transcription of it. Provenance is the whole argument. Nothing about Rs 4,80,000/- being a large number licenses the change, and if the return slip did not exist there would be no step four at all. The size of a figure is a reason to go and look for the paper. Size is never, on its own, a reason to change anything.

Step four is the step that does the work. The sum falls from Rs 20,03,700/- to Rs 15,71,700/-. A corrected cell is still a cell, so the count of usable figures stays at 29. The average takings for each stall month, sum of the usable cells over how many there are, becomes Rs 54,196.55/-. The fall is Rs 14,896.55/-, by a very wide margin the largest move any of the six steps makes. One cell, one extra zero, and a fifth of the headline.

Notice that the one sentence in the market office's day bookA running written ledger kept alongside the file, in sentences rather than columns. It records what happened rather than what was totalled, which is why it can settle a question the columns cannot. about a wedding order, and the one return slip reading Rs 48,000/-, are together worth Rs 14,896.55/- on this record. The word worth is meant literally. The correction happens only because the slip exists, so the slip and the move it makes are one quantity under two names: what a sheet of paper outside the file is worth to a figure inside it.

What is done about a blank, and why is that a decision rather than a step?

There is a row for NB-05 Bansi Flour in month 2, and where its takings should be there is nothing. Not a code, not a zero, not a stray character. Empty. The stall exists, the month exists, the row exists, and the one thing the row was supposed to carry is not there. The nature of a blank like this, why a null valueA cell holding no value at all, as against a cell holding the value nothing. Which of the two a blank represents changes the answer, and it is a question about the record rather than about arithmetic. is not the same thing as a zero, and how the reason for the absence changes what may be done about it, are all covered separately. Step five needs none of that. Step five needs one sentence, written down.

Under the convention this subject uses, printed beside every figure above, the average takings for each stall month is the sum of the cells that carry a usable number divided by how many carry one. A blank carries no number, so it never enters either the top or the bottom of the calculation. The blank was never in the sum, never in the count of 31, and it is not in the count of 29 now. Under that convention step five moves the headline by nothing at all: it stays at Rs 54,196.55/-.

Now change one sentence and nothing else. Suppose the office instead writes that the average is taken over every row that survived the earlier steps, and that a blank takings cell is read as a month in which the stall took nothing. A blank contributes nothing to a sum either way, so the numerator does not move. The denominatorThe figure the total is divided by. Two people can hold the same total and still hand in different answers, because they disagreed about what belonged underneath the line rather than above it. goes from 29 to 30. The same record now reads Rs 52,390/-. Step five's effect is either nothing or Rs 1,806.55/-, and a sentence somebody writes decides which. The sentence has to be written for exactly that reason.

Step five is the step that gets left out of logs, and it is the most dangerous omission on the list. A step that changes a cell leaves a mark: the cell is different and anybody comparing the copy with the source will see it. A step that changes a denominator leaves no mark anywhere. The cells are all identical, the file looks the same, and two people can hand in Rs 54,196.55/- and Rs 52,390/- from the same file with neither of them able to say why the other is wrong.

What is left alone, and what would touching it have cost?

NB-07 Sundari Chaat's month 3 takings cell also reads Rs 4,80,000/-. The same number, in the same month, in the same column, in the same file. Its other three months read Rs 40,000/-, Rs 41,000/- and Rs 42,000/-, so it stands out from its own record exactly as far as NB-08 Peeli Mithai's cell did. And the market office's day book records a wedding order paid to NB-07 Sundari Chaat that month.

NB-07 Sundari Chaat's month 3 is a real month. Nothing is corrected, nothing is set aside, nothing is dropped, and the headline stays at Rs 54,196.55/-. Step six is doing something even though it looks like doing nothing. The alternative was available, was tempting, and was wrong. Restraint counts as a step only when it is a decision rather than an oversight, and a decision has to be written down like every other step.

Here is what touching it would have cost. Correct NB-07 Sundari Chaat's month 3 cell to Rs 40,000/-, in line with its other months, and the sum falls from Rs 15,71,700/- to Rs 11,31,700/- across the same 29 usable figures. The headline lands at Rs 39,024.14/-, a move of Rs 15,172.41/-. The move is further than the Rs 14,896.55/- that fixing the genuine error made. The most expensive step available on this record is the one that cleans something that was never dirty.

The two cells hold the same number. Nothing in the takings column separates them, and how to tell an outlierA figure far away from the others around it. Whether a particular one is worth investigating, and what follows once it has been, is a subject in its own right and it is not settled by how far away the figure sits. that is a real observation from one that is a recording error is a subject of its own, covered separately. The two cells are separated by something outside the record entirely. One cell has a return slip contradicting it. The other has a day book entry supporting it. Both of those are sheets of paper the market office keeps beside the file, and neither of them is a number.

Why does the order matter, and what does the whole run cost?

The six steps are not a checklist that may be worked through in any convenient order. Three of the orderings are forced, and each one is forced for the same kind of reason: a later step counts something that an earlier step changes.

The office code is set aside before cells are counted. Left in, the count of usable figures is 31 when it should be 30, and every average built on that count is wrong from the start. The two rows on one keyThe column or combination of columns that is supposed to identify one row and never repeat. Here it is a stall together with a month, so two rows carrying the same pair is a fault by definition. are resolved before rows are counted. Left in, the denominator counts NB-03 Harit Greens' month 2 twice, so the record claims 30 stall months when the market had 29 to report. And correction comes before averaging, never after: an average computed on the file as handed over and then adjusted by hand is not the same number as an average computed on the corrected file, and nobody downstream can tell which one they have been given.

Run all six in order and the headline moves from Rs 69,035.45/- to Rs 54,196.55/-, a fall of Rs 14,838.90/-. Notice that this net fall is smaller than the single move step four makes on its own. Step three pushed the figure up on the way. Three of the six steps moved it not at all, and those three, the copy, the stated convention and the restraint, are the three that decide whether the figure is right. The three that moved it decided only what it is.

The whole run in one table

What does a cleaning log actually look like?

Everything above is worth nothing to anybody but the person who did it unless it is written down, and the form the writing takes is not a matter of taste. A lender reading a borrower's own figures, an analyst handed a file by somebody else, or a household comparing this year's electricity against last year's all want the same four things, and a log with four columns gives them all four.

Which cell or row. The change itself, stated as what the cell said before and what it says now. On whose authority, naming the written column list or the specific document. And what the headline figure was before the step and after it. The fourth column is the one that gets left out and the only one that makes the log readable. A step whose cost is written down is a step somebody can argue with, and a step nobody can argue with is a step nobody can check.

Read the three filled rows below and notice what a reader can now do that they could not do before. A reader can see that one step accounts for almost all of the movement. A reader can see that another step moved the figure the wrong way and was kept anyway, so the rule was fixed in advance. And a reader can see that the two steps not listed, the blank and the real month, were decided rather than forgotten. A log that names its zero moves is a log written by somebody who understood that a zero move is still a decision.

Where do these numbers come from?

No register, no published series and no office stands behind any amount above. Every rupee amount was constructed for teaching and then recomputed from the small record printed where this subject opens, so the table below names a construction rather than an authority.

What was used	What it actually is	Where it sits	How it is checked
The Neelbagh stall record	An invented file of ten stalls across four months, faulted on purpose	Printed whole where this subject opens	Total the takings column by hand; thirty two rows is small enough to add up
The written column list	The market office's own description of what each column is allowed to hold	Set out where the record's structure is covered	Read the entry for the takings column before accepting any cell in it
The day book and the return slips	Paper the market office keeps beside the file	Kept at the market office, beside the record	Ask the market office for the day book entry and the return slip behind each corrected cell

The Neelbagh market and its fourteen pitches, the Neelbagh stall record, the market office, its day book, and all ten stalls in it, NB-01 Kadamba Idli, NB-02 Chandan Tea, NB-03 Harit Greens, NB-04 Peetal Utensils, NB-05 Bansi Flour, NB-06 Ilaka Fruit, NB-07 Sundari Chaat, NB-08 Peeli Mithai, NB-09 Roshni Juice and NB-10 Amber Rolls, are invented.
Educational material. Not advice on any investment, tax, budget or market position.