Missing Data and Null Values: Absence That Is Not Zero, and Why It Is Missing Decides the Fix

A value is missing when the record has no usable figure where one should be, and a null value is the marker a system writes there. In the Neelbagh stall record, invented, absence comes three ways: an empty cell, a row that is not there, and Rs 99,999/-, the office code that looks exactly like money. Only the first of the three is visible.

Two operations settle all of it: counting a set of things, and dividing one figure by another. No curve fitted to the cells that are present can recover the cell that is absent, and no measure of spread can say why it went. The limit is not modesty. The limit is the point. A gap in a record is a question about where the record came from, and no amount of arithmetic on the cells that are present can answer a question about the one that is not. The reason is found by asking somebody who keeps the record, and once the asking is done the treatment follows from the answer.

Every figure below was worked out from 32 rows of the Neelbagh stall record that can be counted directly. Its three absences are three separate cases, so each one can be looked at on its own. A single stall monthOne stall in one month, and a single row of this record stands for one of them. Eight stalls across four months give thirty two, whether or not the file happens to hold thirty two rows. is the unit throughout, and every rupee figure is an average across some number of those.

What does missing mean, and what is a Null Value?

A value is missing when the record should be carrying a usable figure in a particular place and it is not. The definition is doing a great deal of work. There is a place. Something was expected in that place. And what turned up there cannot be used. Take away any one of those three and what remains is not a missing value; it is something else, and probably something worse.

Now the marker. When a system is asked to store nothing in a place that expects something, it does not leave the machine's memory blank and hope. The system writes a specific marker meaning no usable figure here. The marker is a Null Value. The useful thing about a null value is not what it is but what it is not: it is not a number, it is not text, and it takes no part in arithmetic. A null added to Rs 42,000/- does not give Rs 42,000/-. The answer is either a null again or a complaint, depending on the tool, and both are the tool behaving correctly. Nothing plus an unknown is an unknown.

Consider a shoebox of electricity bills for the year. A month where no bill ever arrived is a missing value. A month where the bill arrived and read zero because the supply was cut off is not missing at all; it is a figure, and the figure is nothing. And a month where somebody put a folded blank sheet in the box to hold the place is the third thing again: there is paper in the slot, so a quick count of sheets says the year is complete, and only opening it says otherwise. Records do all three, and only the first is what most people mean by missing.

The third case, the blank sheet put in to hold the place, has an exact counterpart in a computer record. An empty-looking cell and a null are not always the same thing. A cell holding a single space character looks identical on screen to a cell holding nothing, and it is neither a null nor a figure. A space is text, one character long, and it will behave like whatever the tool guesses it is. The difference between an absence a system has recorded properly and an absence that merely looks like one is the difference between a gap that can be counted and a gap that turns up by accident in six months.

What is the friendly kind of absence, and why did counting find it at once?

NB-05 Bansi Flour trades flour and pulses from a pitch near the north door, and it filed returns for all four months. Its row for month 2 is sitting in the file where it should be, carrying the stall code, the stall name, the licence number, the category, the month number, the pitch area and the day the return reached the office. Everything is there except the one thing the office actually wanted. The takings cell is empty.

The reason is entirely ordinary and has nothing to do with the stall. Bansi Flour traded that month and filed a paper returnThe slip a trader fills in by hand and carries to the office each month. The slip is the original record, and what ends up in the file is somebody's typed copy of it. The copying is where a figure can quietly stop existing. reading Rs 52,000/-. The slip is in a drawer in the market office. Nobody typed it in. A broken link is the whole story, and it is the story behind an enormous share of the empty cells anybody will ever meet: the figure exists, somebody has it, and the chain from the paper to the file broke at one link.

An empty cell is the friendly kind of absence, and it is friendly for one structural reason: the row is there, so the gap has a place to sit. A place to sit means a place to be counted. A count of the rows in the file gives 32. A count of the takings cells carrying a number gives 31. One gap of one, found in two counts by somebody who never read a single figure, and it points at exactly one cell. Nothing clever happened. Two counts of the same file disagreed by one, and the disagreement is the fault announcing itself.

How does a row that is not there hide from every count of the file?

Two pitches along sits NB-04 Peetal Utensils, a seller of steel and brass vessels. Its month 3 row is not empty. There is no month 3 row for NB-04 at all. Where NB-04's rows sit in the file, month 1, month 2 and month 4 run one under the other with nothing between them. There is no row for a cell to belong to, so there is no cell with nothing in it.

Counting what is present can only ever report on things that are present, so an absence at row level is invisible to every count that can be made of what is in the file. Count the rows: 31 in the eight stalls, and 31 is a perfectly sensible number. Count the takings cells with a figure in them: also fine. Count the distinct stall codes: eight, exactly right. Every one of those counts is a question about the file, so every one of them comes back clean. The fault is not in the file at all. The fault is in the difference between the file and the world.

The only way to see it is to know how many rows were expected before anything was opened. Eight stalls, four months, so eight times four is 32 stall months. The file holds 31. Gap of one, and now there is somewhere to go looking. Everyday version: a folder of twelve electricity bills is obviously short only to somebody who knew there were meant to be twelve. Eleven bills in a folder look exactly like eleven bills in a folder. The expectation has to come from somewhere outside the record, and if nobody ever wrote it down, the absence is not hard to find, it is impossible to find.

What happens when an absence is written as a number that looks like money?

The third absence is at NB-02 Chandan Tea, and it is the one that will cost somebody their afternoon. Chandan Tea filed nothing at all for month 4. Rather than leave the cell empty, the clerk did what the office has always done and typed 99999, the house code for no return received. The cell now reads Rs 99,999/-.

Look at what that code survives. The code is a whole number, so the column's type promise holds. The code is present, so the count of takings cells carrying a number comes back at the full 31 and finds nothing. The code sorts, and it sorts into a place that raises no eyebrow: below the genuinely large months and above every ordinary one. A good month would sit in exactly that place. It adds. It averages. In the end it goes into a total that somebody prints. In the takings column Rs 99,999/- is a number, and numbers are what that column holds, so every check that reads only the takings column treats it as money.

Nor will a size rule help, and this is worth working through rather than asserting. Suppose the rule flags any figure more than five times the same stall's smallest other month. Chandan Tea's other three months are Rs 31,000/-, Rs 33,000/- and Rs 32,000/-, so the smallest is Rs 31,000/- and five times that is Rs 1,55,000/-. Rs 99,999/- sails under it with room to spare. Pulling the rule down until it does catch the code sets a cutoff somewhere under Rs 99,999/-. Every genuinely large month in the record is flagged along with it, and the result is a list rather than an answer. The only thing on the premises that knows 99999 is a code is the written column listThe short document kept beside a record that spells out, field by field, the unit a value is carried in and the meaning of anything unusual sitting there. The full contents of such a document are covered separately., where somebody wrote the sentence down.

Why does the reason a value is missing decide the fix?

Three absences, and one sentence covers all three. Nothing in the record says which of the three any one of them is, and the correct treatment of each is different from the correct treatment of the other two. Not slightly different. Different in kind.

NB-05 Bansi Flour traded and the figure exists on a slip in a drawer. The fix is to go and get it. The record was simply incomplete and now it is not, so there is no judgement to make, no convention to adopt and nothing to explain to anybody. Fetching the figure is the best outcome available, and it is the one to look for first, every time, before anything cleverer is reached for.

NB-04 Peetal Utensils traded in month 3 and nobody ever wrote the row. There is no slip. The fix is not to invent a figure; it is to state plainly that the record covers 31 of the 32 stall months it should cover, and to carry that sentence alongside every number published from it. An incomplete record that says so is a usable record. An incomplete record that does not say so is a trap with a total at the bottom.

NB-02 Chandan Tea filed nothing at all, and that is a different situation again. The month's takings were never declared, so there is no figure in a drawer and there never was one. The fix is to set the cell aside. Setting a cell aside means taking it out of the count entirely rather than replacing it with anything. And this one has an extra step ahead of it: setting the cell aside requires knowing that Rs 99,999/- is not money, and that is known only because somebody wrote it down in the column list.

What do the three absences look like side by side?

Three places, three appearances, three reasons, three fixes. Down the appearance column, a person looking only at the file would call the first one a problem, would never see the second, and would call the third a good month for a tea stall.

One more cell in the record looks like an absence and is not. NB-04's month 2 cell reads Rs 0/-, and a zero is not an absence of any kind. The pitch was shut all month while it was re roofed, the stall filed a return saying so, and Rs 0/- is the figure. A real zero is a measurement and belongs in every count; an absence is not a measurement and belongs in none of them. The difference between a zero and an absence is covered separately.

Why does one unchanged total give four different published figures?

Finding an absence and explaining it is one half of the work. The consequence is the other half, and it is larger than most people expect. Adding up the takings cells in the cleaned record that carry a usable number gives Rs 15,71,700/-. Rs 15,71,700/- is the numeratorThe figure on top of a division, the total being shared out., and it does not move by a single rupee in anything that follows. The figure that moves is the denominatorThe figure underneath a division, the count that total is shared across. Change it and the answer changes, even though not one rupee of the total has shifted., and there are four honest ways to choose it. Each gives a different headline figureThe one number that gets quoted out of a whole record, usually in a sentence with no room for the working behind it., and all four are correct arithmetic.

The four choices, and what each one is counting. Over 29 counts the takings cells that carry a usable number, and 29 is the named convention used throughout: the sum of the takings cells that carry a usable number, divided by how many carry one. Over 30 counts every row the cleaned record actually holds, once the repeated keyA key is the combination of fields that should single out exactly one row. Repeated means two rows answer to the same combination, so the record stops saying which of them is the row. How to resolve one is covered separately. at NB-03 has been resolved and the no return code has been set aside, so NB-05's blank month is back in as a row with nothing in it. Over 31 adds NB-04's month 3, the row that is not there. Over 32 adds NB-02's month 4 as well, and divides by every stall month the eight by four grid expects.

Why does one of those four answers need a note about rounding?

Three of the four figures can be printed and forgotten. Both 30 and 31 divide Rs 15,71,700/- cleanly, so Rs 52,390/- and Rs 50,700/- come out exact, with nothing after the decimal point at all. Rs 54,196.55/- does not come out exact, but it is not near any awkward boundary either, so every reasonable tool prints the same two decimal places.

Rs 15,71,700/- divided by 32 is the one to watch. Work it right out and it comes to 49115.625 rupees exactly: not a figure that runs on forever, but one that stops precisely half a paisaOne hundredth of a rupee. Money is usually carried to two of them, so the second paisa is the last place two honest calculations can visibly disagree. past the last place anybody prints. Half a paisa past the last printed place is the worst possible place for a number to stop. A tool that cuts at the paisa prints Rs 49,115.62/-, and a tool that rounds a half upward prints the same figure one paisa more, and both of them are behaving correctly.

The honest course is to print Rs 49,115.62/- and say in the same breath that it is a rounded display. Saying so is not fussiness. Two figures that agree everywhere except the last paisa is exactly how an approximation gets quoted as an identity: somebody sees them side by side, decides the difference is too small to matter, and writes down whichever one is nearer to hand. The difference never mattered. The rounding itself is what mattered, and once the rounding stops being said, nobody downstream can tell a rounded display from an exact one.

What gets written down about every absence?

All three cases collapse into one small habit that a lender's credit analyst, an office clerk and a person sorting out a shop's books all use in the same shape: an absence register. Three columns, no more, and the third one is the whole point.