Missing Values

Open orders_messy.csv in a spreadsheet and the gaps are easy to miss: an empty quantity cell here, N/A in the channel column there. Load it into pandas and those gaps become NaN, which then flows silently through your calculations: sums skip it, means ignore it, and a fillna(0) written in a hurry turns “we don’t know” into “nothing was sold”. Missing values are not a technical nuisance to clear away; each one is a question about the business.

Finding the gaps

import pandas as pd

messy = pd.read_csv("orders_messy.csv")
print(messy.shape)
print(messy.isna().sum())
print(messy[messy.isna().any(axis=1)].head().to_string())

isna() returns a DataFrame of booleans, and summing it counts the gaps per column: 9 quantities and 7 channels. isna().any(axis=1) asks, per row, whether any column is missing (axis=1 means “across the columns”), which lets you look at the actual damaged rows. Looking at them matters: the 9 missing quantities belong to ordinary orders across both years, not to one broken day.

What read_csv decided for you

The raw file has no empty channel cells. It has the text N/A, and pandas converted it to NaN because N/A is on its default list of missing markers, along with an empty cell, NA, NaN, null, None and a few others. Usually that is helpful. Sometimes it is wrong: a column of country codes would lose Namibia, whose ISO code is NA. Load the file without the conversion to see what is really there:

import pandas as pd

raw = pd.read_csv("orders_messy.csv", keep_default_na=False)
print(raw["channel"].value_counts())
print((raw["quantity"] == "").sum(), "empty quantity cells")

Now N/A shows up as a real value with a count of 7, and the quantity column is all text, because the empty cells stayed empty strings. To control the list precisely, pass na_values=["N/A", ""] together with keep_default_na=False.

Deciding what to do

There is no universal fix. Ask three questions, in this order, and keep the value missing if every answer is no:

Recovery comes first because it is the only option that adds information. The export’s order ids also exist in Cartwheel’s clean orders and order_items tables, so the missing quantities could be looked up there; you will learn the join that does it in Lesson 4.2. Until you can recover a value, do not invent one.

Applied to the export: the missing channel cannot be guessed from anything else in the row, but “we don’t know the channel” is still a fact worth keeping visible, so it gets an explicit label. A blank coupon code in the clean orders table is different again: there, missing means “no coupon used”, which is a real answer.

import pandas as pd

messy = pd.read_csv("orders_messy.csv")
messy["channel"] = messy["channel"].fillna("unknown")
print(messy["channel"].value_counts())
print(len(messy.dropna(subset=["quantity"])), "rows with a known quantity")

dropna(subset=["quantity"]) removes only the rows missing a quantity, and only in the result you store; plain dropna() with no subset removes a row if any column is missing, which on the raw export would throw away 16 rows, including perfectly good totals. Drop for a specific analysis, not from your master copy.

One more trap: you cannot find gaps with ==. NaN is not equal to anything, not even itself, so messy["channel"] == np.nan is False on every row. Always use isna() and notna().

Nullable types: integers that can be missing

The quantity column loaded as float64, so quantities print as 1.0 and 2.0. That is the same effect you saw with referred_by in Lesson 2.2: classic NaN is a float. pandas also has nullable dtypes, written with a capital letter, which store a separate missing marker, pd.NA:

import pandas as pd

messy = pd.read_csv("orders_messy.csv")
qty = messy["quantity"].astype("Int64")
print(qty.dtype, qty.iloc[32:35].tolist())
print(qty.sum(), qty.count(), len(qty))
print(round(qty.mean(), 2))

Int64 keeps whole numbers as integers and shows gaps as <NA>. Aggregations skip missing values by default, so sum() adds the 159 known quantities, and count() versus len() tells you exactly how many are missing. The same family includes Float64, boolean and string, and pd.read_csv(..., dtype_backend="numpy_nullable") loads every column that way. One behaviour to know: comparisons with pd.NA return <NA> rather than False, because “is an unknown quantity equal to 1?” honestly has no answer.

In the exercise you count, label and retype the gaps. Next you clean the text columns, where the country names come in four different spellings.