Handling Missing Data

June's orders export has a few holes: some amounts are blank, one customer name is empty, and a whole afternoon has no rows at all. Every analysis tool has a default way of handling blanks, and none of them knows why the value is missing. You do, or you can find out. That reason decides the fix.

The rule

Ask why it's missing before deciding how to fill it.

Missing values come from different places, and each points to a different fix:

• It can be worked out from other columns. An amount is missing but the item and quantity are there: amount = price × qty. Recalculate it.
• It was never recorded, for no particular reason. A few customer names skipped at a busy till. Leave them blank, or label them Unknown, and move on.
• It is missing for a reason that matters. The card machine failed, so an afternoon's card orders were never logged. Filling or dropping hides a real gap; report it instead.
• It really is zero or "none". A day the café was closed has no sales. A blank there should be an explicit 0 or closed, depending on the question.

Then choose an action:

Whatever you do, add a flag column such as amount_filled = yes, so anyone can see which values are original.

The shortcut

Count before you fix: how many are missing, where, and do they cluster? A handful scattered at random is a small problem. Many bunched on one day, one till or one item is a story.

The trap is the blanket fill: replacing every gap with the column average. It invents values that nobody ordered and hides patterns you should have spotted.

Drills

Answer fast — trust the rule.

Remember

• Ask why it's missing before deciding how to fill it.
• Shortcut: count, locate, look for clusters; recalculate when you can.
• Trap: blanket-filling with the average invents data; always flag what you filled.