Cleaning a table: missing values, the string that is not missing, and duplicates that are not identical

Missing is a value, until it is not

pandas marks a missing value as NaN — the floating-point not-a-number — or None in object columns, and a family of functions understands it: isna, notna, fillna, dropna, and every aggregation, which skips it by default. A column with three missing prices has a mean over the other rows, not NaN.

None of that machinery sees a missing value that arrived as a string. Real files mark absence with N/A, NA, -, ?, Unknown, none, null, an empty string, a single space, or 0 where zero is impossible. To pandas those are all present values. The first job of cleaning is to make missingness real:

SENTINELS = ["N/A", "NA", "n/a", "-", "?", "", " ", "Unknown", "unknown", "null", "None"]
df = pd.read_csv(path, na_values=SENTINELS, keep_default_na=True)

or after the fact:

df["city"] = df["city"].replace(SENTINELS, pd.NA)

Then, and only then, df.isna().sum() tells the truth. Check value_counts() on each string column before trusting it; the sentinel you did not list is the one in your data.

Deciding what to do

There is no default answer. For each column with missing values, one of three things is right, and the reason should be written next to the line:

df = df.dropna(subset=["order_id"])                   # a row with no ID is not an order
df["quantity"] = df["quantity"].fillna(1)             # missing quantity means one item, per the export docs
df["discount"] = df["discount"].fillna(0.0)           # no discount recorded means none
df["city"] = df["city"].fillna("unknown")             # keep the row; the model can learn from absence

dropna() with no arguments drops any row with any missing cell, which in a wide table can be most of them. Always pass subset=. Filling a numeric column with its mean or median is a modelling decision with consequences the machine learning course explains; here, the Python point is that fillna accepts a scalar, a Series aligned by index, or a dict of per-column values, and that it returns a new frame unless you assign back.

df.isna().mean() gives the fraction missing per column, which is what decides whether a column is worth keeping at all.

Types, again

After the sentinels are gone, columns that were object because of one N/A can become numeric:

df["price"] = pd.to_numeric(df["price"], errors="coerce")

errors="coerce" turns anything unparseable into NaN rather than raising, so a stray 12,50 becomes missing instead of stopping the load. Count the new NaNs afterwards; if there are many, the format is systematic and you should fix it — str.replace(",", ".") first — rather than lose the rows.

Dates the same way: pd.to_datetime(df["created_at"], errors="coerce", format="%d/%m/%Y"). Give the format. Without it, 01/02/2026 is parsed as January 2nd by an American default and February 1st by nobody, and the ambiguity is silent.

Categorical columns with a small set of values — status, country, plan — save memory and speed up groupby as the category dtype: df["status"] = df["status"].astype("category"). A 10-million-row string column can drop from 600 MB to 10.

Strings that are almost the same

df["city"].value_counts().head(20)

will show Mumbai, mumbai, Mumbai and MUMBAI as four cities. Normalise before grouping:

df["city"] = df["city"].str.strip().str.lower()

.str.strip() removes the whitespace that Excel exports leave on every cell. .str.lower() or .str.casefold() — the latter handles non-Latin case rules — collapses the case variants. .str.normalize("NFC") unifies the two ways Unicode can spell an accented letter, which module 5's encoding lesson introduced and which makes café and café compare unequal when they look identical.

Duplicates

df.duplicated().sum()                          # rows identical in every column
df.duplicated(subset=["order_id"]).sum()       # rows sharing an ID
df = df.drop_duplicates(subset=["order_id"], keep="last")

Full-row duplicates are usually an export run twice. The more common and more dangerous case is two rows with the same key and different other columns — an order updated, exported before and after. duplicated() with no subset misses these. Decide which row is the truth (keep="last" if the file is in time order; sort by an updated-at column first if it is not) and write the reason down.

Ranges and impossibilities

df.describe()
(df["price"] < 0).sum()
(df["age"] > 120).sum()
df["created_at"].min(), df["created_at"].max()

A negative price, a birth date in 2031, a quantity of 10,000 where the maximum order is 20: describe and a few comparisons find them in seconds. What to do is the same question as missing values — drop, cap, or flag — and again the reason belongs in a comment.

Keep the raw file

Every step above should be a function that takes the raw frame and returns a clean one, run from a script, with the raw file never modified. When a sentinel you missed turns up, or the rule for duplicates changes, you re-run the function rather than trying to remember what you did to a file by hand three weeks ago. clean.py with a clean(df) -> df at the top is the shape.

Try this now

Take any CSV, list the distinct values of each string column with value_counts(), and write the sentinel list it actually needs. Load with na_values=, print isna().mean(), and make the drop-or-fill decision for each column with a one-line reason. Then find duplicates by key and by full row and explain the difference in count.