Handling Missing Values in a CSV File Without Guessing

A CSV has no NULL. A database table can say "this value is unknown" in a way that is different from the value zero and different from the empty string, and a CSV can say it only in text. So missing arrives as an empty cell, a dash, N/A, n/a, null, NULL, none, -, ?, a space or a number like -999, depending on who exported the file. Handling that well is mostly not a technical problem. It is a matter of finding every form, turning them all into one real "missing", keeping what each one meant, and refusing to invent a value. This article does that on a 200,000 row file with counts. It is part of the cleaning series, and the typed readers' behavior on blanks is in the pandas reading article. The pandas guide to the topic is the missing data user guide.

Take inventory

Before you decide anything, count what the file uses. Read it as text with no NA parsing, so nothing is converted behind your back, and count the candidate tokens in each column:

import sys, io
sys.path.insert(0, "/batches/csv-0001-0200/lab/clean")
import pandas as pd
from clean import *

path = "/batches/csv-0001-0200/lab/data/clean/customers-messy-200k.csv"
df = drop_blank_and_footer(read_raw(path))[0].apply(lambda c: c.str.strip())

# 1. What does "missing" look like, column by column?
tokens = ["", "-", "n/a", "null"]
inv = {c: {t if t else "(empty)": int((df[c].str.lower() == t).sum()) for t in tokens} for c in ["name", "email", "phone", "signup_date", "balance", "units", "country", "status"]}
print(pd.DataFrame(inv).T.to_string())

# 2. What pandas does by default with the same file
default = pd.read_csv(path, dtype={"id": "str", "balance": "str", "units": "str"})
print("\npandas default NA handling, count of NA in each column:")
print(default[["phone", "balance", "units"]].isna().sum().to_dict())
print("tokens it left as text in 'units':", {t: int((default['units'] == t).sum()) for t in ['-', 'N/A', 'null']})

# 3. Turning the tokens into real missing values, and keeping the reason
units_raw = df["units"]
reason = units_raw.str.lower().map({"": "empty", "-": "dash", "n/a": "n/a", "null": "null"}).fillna("")
df["units_n"] = units_raw.map(parse_int)
df["units_reason"] = reason.where(df["units_n"].isna(), "")
print("\nunits: parsed", int(df['units_n'].notna().sum()), "| missing", int(df['units_n'].isna().sum()), "| by reason", df.loc[df['units_n'].isna(), 'units_reason'].value_counts().to_dict())

# 4. What the choice of fill does to the numbers
u = df["units_n"].astype("float")
print(f"\nmean units, missing left missing: {u.mean():.2f}")
print(f"mean units, missing filled with 0: {u.fillna(0).mean():.2f}")
print(f"mean units, missing filled with median ({u.median():.0f}): {u.fillna(u.median()).mean():.2f}")
print(f"sum of units: {u.sum():,.0f} (missing left) vs {u.fillna(0).sum():,.0f} (zero) -> identical, but the mean and the count are not")
print(f"count of rows with units > 1000: {int((u > 1000).sum())} | share of all rows: {(u > 1000).mean():.3f} | share of rows with a value: {(u[u.notna()] > 1000).mean():.3f}")

python /batches/csv-0001-0200/lab/clean/t0115.py

             (empty)     -   n/a  null
name               0     0     0     0
email              0     0     0     0
phone          33303     0     0     0
signup_date        0     0     0     0
balance         2969  2946  3015  3042
units           2987  3038  3033  3025
country            0     0     0     0
status             0     0     0     0

pandas default NA handling, count of NA in each column:
{'phone': 33526, 'balance': 9248, 'units': 9268}
tokens it left as text in 'units': {'-': 3038, 'N/A': 0, 'null': 0}

units: parsed 187695 | missing 12083 | by reason {'dash': 3038, 'n/a': 3033, 'null': 3025, 'empty': 2987}

mean units, missing left missing: 2498.78
mean units, missing filled with 0: 2347.65
mean units, missing filled with median (2500): 2498.85
sum of units: 469,008,743 (missing left) vs 469,008,743 (zero) -> identical, but the mean and the count are not
count of rows with units > 1000: 150053 | share of all rows: 0.751 | share of rows with a value: 0.799

The first table is the inventory. name, email, signup_date, country and status have none of the four tokens. phone has 33,303 empty cells, and only empty. balance and units have all four in roughly equal numbers: for units, 2,987 empty, 3,038 dashes, 3,033 n/a and 3,025 null. Two things follow. A cleaner that knows only about empty cells misses three quarters of the missing values in those columns. And the four tokens are used evenly, which tells me they come from several sources or several versions of one export: it is worth asking.

What the library does by default

pandas treats a list of strings as missing (N/A, null, NA, empty and a few more; the exact list is in the reading article and the read_csv reference). It does not include -. The second block of output shows the consequence for units: with the defaults, N/A and null were converted to NaN, empty cells too, and **all 3,038 dashes were left as the text -**. A column with 3,038 stray dashes is an object column. The first sum you try to take fails or, worse, coerces silently. If you let the library decide, you get a partial cleaning that looks complete in isna().sum().

The same applies to NA read as text: a country code of NA (Namibia) is turned into missing by the default list. Control the list yourself, with keep_default_na=False and your own na_values, or read as text and convert in a separate step, as I do.

Convert to a real missing value, and keep the reason

My rule is that the cleaning step converts tokens to a real missing value and records which token it was:

reason = units_raw.str.lower().map({"": "empty", "-": "dash", "n/a": "n/a", "null": "null"})

The output line for units: parsed 187,695, missing 12,083, and the reason split {'dash': 3038, 'n/a': 3033, 'null': 3025, 'empty': 2987}. The reason column is not clutter. A dash often means "not applicable", null often means the export had no value, and an empty cell often means the user did not fill it in. Those are different facts. If the provider can tell you what each token means, the cleaned data can carry it: units_status of not_applicable, unknown or not_provided. If nobody can, you still have the raw token and the counts, which is more than you will have once the cell has been replaced with NaN. The only information that CSV cannot represent should survive into the cleaned file.

What a fill does to the numbers

The temptation is to fill and move on. The effect is not neutral:

• Missing left missing: mean of units 2,498.78

• **fillna(0):** 2,347.65

• filled with the median, 2,500: 2,498.85

The sum was 469,008,743 in both the first and second cases, identical, because adding zeros changes nothing. But the mean dropped by 151, since the 12,083 unknowns became 12,083 zeros, and the share of rows with units > 1000 was 0.751 over all rows and 0.799 over rows that had a value. Which of those is "the share of customers with more than a thousand units"? It depends on whether a missing value is a customer who bought none or a customer we have no data for. Filling with the median keeps the mean nearly unchanged (2,498.85) and invents 12,083 customers who all bought exactly the middle quantity, which makes the distribution narrower than it is and any variance estimate too small.

None of those is a cleaning decision. They are analysis decisions, and they belong in the analysis, with a person who knows what the column means. A cleaning step should produce a column that says "unknown" honestly, and let the analysis choose between dropping, imputing and modeling. If you must fill, fill in a new column and name it for what it is, units_filled_0.

Missing is not zero, and not an empty string

Three habits that prevent most of the trouble:

1. Keep zero and missing apart. A balance of $0.00 is a fact and a balance of - is the absence of one. The cleaned numeric column needs a way to tell them apart, which NaN or a database NULL gives you and 0 does not.

2. Use the right type for the empty string. In a text column, an empty string and a missing value are different, and many tools write both as nothing. Pick one for the cleaned file (I use empty cell for missing) and say so in the contract with the consumer.

3. Watch the sentinels. A column of ages with -1 or 999, a date of 1900-01-01 or 0000-00-00, a quantity of -9999: all of them are missing written as data. They do not appear in an inventory of text tokens, so look at the minimum and maximum of every numeric column and the most common values of every date column.

Writing it back

When the cleaned data goes to a database, a real missing value becomes NULL, which is what you want. When it goes back to CSV, it becomes an empty cell, and the reason column keeps the token. Decide on the empty cell and write it down, because a consumer that reads empty as zero (Java's beans did this for integers, as the Java article found) turns your carefully preserved unknown into a zero again.

How to tell it worked

Count the missing values before and after: after should equal the sum of the tokens you converted (12,083 for units). Check that no token remains in the cleaned text columns. Check that the parsed numeric column has the dtype you expect and that isna().sum() equals the count of the reason column. Compare a mean with and without the missing rows and write the difference down. And look at the maximum and minimum of numeric columns for sentinels.

What cleaning should not decide

Cleaning should not decide that unknown means zero, that unknown means the average or that a row with unknown values should be dropped. It can make unknown visible, countable and consistent. The decisions belong to whoever uses the numbers. The next article, on standardizing phone numbers, has a missing value problem of its own: a blank phone is fine, and a phone that cannot be parsed is not the same thing as a blank phone.