{"course":{"slug":"python-for-ai","title":"Python, from Zero, for AI","url":"https://addaly.com/learn/python-for-ai","level":"beginner","track":"build"},"lesson":{"slug":"cleaning-data-in-pandas","title":"Cleaning a table: missing values, the string that is not missing, and duplicates that are not identical","url":"https://addaly.com/learn/python-for-ai/cleaning-data-in-pandas","position":76,"of":89,"minutes":9,"answer":"isna finds only real NaN, so the strings 'Unknown', 'N/A' and empty must be converted first; fill or drop by column with a reason each time.","keyIdea":"isna finds only real NaN, so the strings 'Unknown', 'N/A' and empty must be converted first; fill or drop by column with a reason each time, and deduplicate on the key that defines a row rather than on every column."},"body":{"format":"markdown","text":"## Missing is a value, until it is not\n\npandas 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`.\n\nNone 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:\n\n```python\nSENTINELS = [\"N/A\", \"NA\", \"n/a\", \"-\", \"?\", \"\", \" \", \"Unknown\", \"unknown\", \"null\", \"None\"]\ndf = pd.read_csv(path, na_values=SENTINELS, keep_default_na=True)\n```\n\nor after the fact:\n\n```python\ndf[\"city\"] = df[\"city\"].replace(SENTINELS, pd.NA)\n```\n\nThen, 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.\n\n> Figure: One column of 1,000 rows: what counts as missing. Bars (rows): Real NaN, which isna() sees 42; The string N/A 61; A single hyphen 28; Empty once stripped of spaces 17; The string Unknown 9. df.isna().sum() reports 42. The column is actually missing 157 values, sixteen per cent rather than four, and every mean, count and groupby computed before the sentinels were converted is wrong by that much, with no error anywhere. Pass the na_values list at read_csv, or replace afterwards, then check value_counts on every string column.\n\n## Deciding what to do\n\nThere 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:\n\n```python\ndf = df.dropna(subset=[\"order_id\"])                   # a row with no ID is not an order\ndf[\"quantity\"] = df[\"quantity\"].fillna(1)             # missing quantity means one item, per the export docs\ndf[\"discount\"] = df[\"discount\"].fillna(0.0)           # no discount recorded means none\ndf[\"city\"] = df[\"city\"].fillna(\"unknown\")             # keep the row; the model can learn from absence\n```\n\n`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.\n\n`df.isna().mean()` gives the *fraction* missing per column, which is what decides whether a column is worth keeping at all.\n\n## Types, again\n\nAfter the sentinels are gone, columns that were `object` because of one `N/A` can become numeric:\n\n```python\ndf[\"price\"] = pd.to_numeric(df[\"price\"], errors=\"coerce\")\n```\n\n`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.\n\nDates 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.\n\nCategorical 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.\n\n## Strings that are almost the same\n\n```python\ndf[\"city\"].value_counts().head(20)\n```\n\nwill show `Mumbai`, `mumbai`, `Mumbai ` and `MUMBAI` as four cities. Normalise before grouping:\n\n```python\ndf[\"city\"] = df[\"city\"].str.strip().str.lower()\n```\n\n`.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.\n\n## Duplicates\n\n```python\ndf.duplicated().sum()                          # rows identical in every column\ndf.duplicated(subset=[\"order_id\"]).sum()       # rows sharing an ID\ndf = df.drop_duplicates(subset=[\"order_id\"], keep=\"last\")\n```\n\nFull-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.\n\n## Ranges and impossibilities\n\n```python\ndf.describe()\n(df[\"price\"] < 0).sum()\n(df[\"age\"] > 120).sum()\ndf[\"created_at\"].min(), df[\"created_at\"].max()\n```\n\nA 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.\n\n## Keep the raw file\n\nEvery 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.\n\n## Try this now\n\nTake 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."},"check":{"question":"`df[\"city\"].isna().sum()` returns 0, but `df[\"city\"].value_counts()` shows 4,200 rows of `Unknown` and 310 of `unknown`. A colleague says the column has no missing data. What is wrong with that claim?","options":[{"text":"`isna` only counts values that were blank in the original file, and this file was exported from a system that never leaves cells blank, so the count is correct for what it measures.","correct":false,"tempting":"Close to the mechanism, but the claim is about the data, not the exporter, and the fix is a conversion step the option does not name."},{"text":"`value_counts` is case-sensitive and `isna` is not, so the two counts cannot be compared.","correct":false,"tempting":"`isna` has no notion of case; it tests for NaN and None, and the strings are neither."},{"text":"4,510 rows out of the total is below the threshold at which pandas reports a column as having missing values.","correct":false,"tempting":"There is no threshold; `isna` counts every NaN, and here there are none because the sentinels are strings."},{"text":"The column stores missingness as the strings `Unknown` and `unknown`, which `isna` cannot see; they must become NaN before any count or fill means anything.","correct":true}]},"prev":{"slug":"pandas-dataframes","title":"pandas: a table with named columns, and the four things to look at first","url":"https://addaly.com/learn/python-for-ai/pandas-dataframes"},"next":{"slug":"groupby-merge-and-reshape","title":"groupby, merge and pivot: getting from rows to the table a question needs","url":"https://addaly.com/learn/python-for-ai/groupby-merge-and-reshape"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Source and assessment review continues. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Cleaning a table: missing values, the string that is not missing, and duplicates that are not identical', in Python, from Zero, for AI. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/python-for-ai/cleaning-data-in-pandas (Accessed: 21 November 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Cleaning a table: missing values, the string that is not missing, and duplicates that are not identical","courseTitle":"Python, from Zero, for AI","module":{"index":8,"slug":"numbers-in-bulk","title":"Numbers in bulk: NumPy, pandas and the vector under every model"},"answer":null,"keyIdea":"isna finds only real NaN, so the strings ‘Unknown’, ‘N/A’ and empty must be converted first; fill or drop by column with a reason each time, and deduplicate on the key that defines a row rather than on every column.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"caption":"df.isna().sum() reports 42. The column is actually missing 157 values, sixteen per cent rather than four, and every mean, count and groupby computed before the sentinels were converted is wrong by that much, with no error anywhere. Pass the na_values list at read_csv, or replace afterwards, then check value_counts on every string column.","series":[{"highlight":true,"label":"Real NaN, which isna() sees","value":42},{"label":"The string N/A","value":61},{"label":"A single hyphen","value":28},{"label":"Empty once stripped of spaces","value":17},{"label":"The string Unknown","value":9}],"title":"One column of 1,000 rows: what counts as missing","type":"bars","unit":"rows"},"alt":"Figure: One column of 1,000 rows: what counts as missing. Bars (rows): Real NaN, which isna() sees 42; The string N/A 61; A single hyphen 28; Empty once stripped of spaces 17; The string Unknown 9. df.isna().sum() reports 42. The column is actually missing 157 values, sixteen per cent rather than four, and every mean, count and groupby computed before the sentinels were converted is wrong by that much, with no error anywhere. Pass the na_values list at read_csv, or replace afterwards, then check value_counts on every string column."}},"blocks":[{"t":"h2","id":"missing-is-a-value-until-it-is-not","runs":[{"text":"Missing is a value, until it is not"}]},{"t":"p","runs":[{"text":"pandas marks a missing value as "},{"text":"NaN","code":true},{"text":" (the floating-point not-a-number) or "},{"text":"None","code":true},{"text":" in object columns, and a family of functions understands it: "},{"text":"isna","code":true},{"text":", "},{"text":"notna","code":true},{"text":", "},{"text":"fillna","code":true},{"text":", "},{"text":"dropna","code":true},{"text":", and every aggregation, which skips it by default. A column with three missing prices has a mean over the other rows, not "},{"text":"NaN","code":true},{"text":"."}]},{"t":"p","runs":[{"text":"None of that machinery sees a missing value that arrived as a "},{"text":"string","b":true},{"text":". Real files mark absence with "},{"text":"N/A","code":true},{"text":", "},{"text":"NA","code":true},{"text":", "},{"text":"-","code":true},{"text":", "},{"text":"?","code":true},{"text":", "},{"text":"Unknown","code":true},{"text":", "},{"text":"none","code":true},{"text":", "},{"text":"null","code":true},{"text":", an empty string, a single space, or "},{"text":"0","code":true},{"text":" where zero is impossible. To pandas those are all present values. The first job of cleaning is to make missingness real:"}]},{"t":"code","lang":"python","label":"Python","text":"SENTINELS = [\"N/A\", \"NA\", \"n/a\", \"-\", \"?\", \"\", \" \", \"Unknown\", \"unknown\", \"null\", \"None\"]\ndf = pd.read_csv(path, na_values=SENTINELS, keep_default_na=True)"},{"t":"p","runs":[{"text":"or after the fact:"}]},{"t":"code","lang":"python","label":"Python","text":"df[\"city\"] = df[\"city\"].replace(SENTINELS, pd.NA)"},{"t":"p","runs":[{"text":"Then, and only then, "},{"text":"df.isna().sum()","code":true},{"text":" tells the truth. Check "},{"text":"value_counts()","code":true},{"text":" on each string column before trusting it; the sentinel you did not list is the one in your data."}]},{"t":"h2","id":"deciding-what-to-do","runs":[{"text":"Deciding what to do"}]},{"t":"p","runs":[{"text":"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:"}]},{"t":"code","lang":"python","label":"Python","text":"df = df.dropna(subset=[\"order_id\"])                   # a row with no ID is not an order\ndf[\"quantity\"] = df[\"quantity\"].fillna(1)             # missing quantity means one item, per the export docs\ndf[\"discount\"] = df[\"discount\"].fillna(0.0)           # no discount recorded means none\ndf[\"city\"] = df[\"city\"].fillna(\"unknown\")             # keep the row; the model can learn from absence"},{"t":"p","runs":[{"text":"dropna()","code":true},{"text":" with no arguments drops any row with any missing cell, which in a wide table can be most of them. Always pass "},{"text":"subset=","code":true},{"text":". 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 "},{"text":"fillna","code":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"df.isna().mean()","code":true},{"text":" gives the "},{"text":"fraction","i":true},{"text":" missing per column, which is what decides whether a column is worth keeping at all."}]},{"t":"h2","id":"types-again","runs":[{"text":"Types, again"}]},{"t":"p","runs":[{"text":"After the sentinels are gone, columns that were "},{"text":"object","code":true},{"text":" because of one "},{"text":"N/A","code":true},{"text":" can become numeric:"}]},{"t":"code","lang":"python","label":"Python","text":"df[\"price\"] = pd.to_numeric(df[\"price\"], errors=\"coerce\")"},{"t":"p","runs":[{"text":"errors=\"coerce\"","code":true},{"text":" turns anything unparseable into "},{"text":"NaN","code":true},{"text":" rather than raising, so a stray "},{"text":"12,50","code":true},{"text":" 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 ("},{"text":"str.replace(\",\", \".\")","code":true},{"text":" first) rather than lose the rows."}]},{"t":"p","runs":[{"text":"Dates the same way: "},{"text":"pd.to_datetime(df[\"created_at\"], errors=\"coerce\", format=\"%d/%m/%Y\")","code":true},{"text":". Give the format. Without it, "},{"text":"01/02/2026","code":true},{"text":" is parsed as January 2nd by an American default and February 1st by nobody, and the ambiguity is silent."}]},{"t":"p","runs":[{"text":"Categorical columns with a small set of values (status, country, plan) save memory and speed up groupby as the "},{"text":"category","code":true},{"text":" dtype: "},{"text":"df[\"status\"] = df[\"status\"].astype(\"category\")","code":true},{"text":". A 10-million-row string column can drop from 600 MB to 10."}]},{"t":"h2","id":"strings-that-are-almost-the-same","runs":[{"text":"Strings that are almost the same"}]},{"t":"code","lang":"python","label":"Python","text":"df[\"city\"].value_counts().head(20)"},{"t":"p","runs":[{"text":"will show "},{"text":"Mumbai","code":true},{"text":", "},{"text":"mumbai","code":true},{"text":", "},{"text":"Mumbai ","code":true},{"text":" and "},{"text":"MUMBAI","code":true},{"text":" as four cities. Normalise before grouping:"}]},{"t":"code","lang":"python","label":"Python","text":"df[\"city\"] = df[\"city\"].str.strip().str.lower()"},{"t":"p","runs":[{"text":".str.strip()","code":true},{"text":" removes the whitespace that Excel exports leave on every cell. "},{"text":".str.lower()","code":true},{"text":" or "},{"text":".str.casefold()","code":true},{"text":" (the latter handles non-Latin case rules) collapses the case variants. "},{"text":".str.normalize(\"NFC\")","code":true},{"text":" unifies the two ways Unicode can spell an accented letter, which module 5’s encoding lesson introduced and which makes "},{"text":"café","code":true},{"text":" and "},{"text":"café","code":true},{"text":" compare unequal when they look identical."}]},{"t":"h2","id":"duplicates","runs":[{"text":"Duplicates"}]},{"t":"code","lang":"python","label":"Python","text":"df.duplicated().sum()                          # rows identical in every column\ndf.duplicated(subset=[\"order_id\"]).sum()       # rows sharing an ID\ndf = df.drop_duplicates(subset=[\"order_id\"], keep=\"last\")"},{"t":"p","runs":[{"text":"Full-row duplicates are usually an export run twice. The more common and more dangerous case is two rows with the same key and "},{"text":"different","i":true},{"text":" other columns: an order updated, exported before and after. "},{"text":"duplicated()","code":true},{"text":" with no subset misses these. Decide which row is the truth ("},{"text":"keep=\"last\"","code":true},{"text":" if the file is in time order; sort by an updated-at column first if it is not) and write the reason down."}]},{"t":"h2","id":"ranges-and-impossibilities","runs":[{"text":"Ranges and impossibilities"}]},{"t":"code","lang":"python","label":"Python","text":"df.describe()\n(df[\"price\"] < 0).sum()\n(df[\"age\"] > 120).sum()\ndf[\"created_at\"].min(), df[\"created_at\"].max()"},{"t":"p","runs":[{"text":"A negative price, a birth date in 2031, a quantity of 10,000 where the maximum order is 20: "},{"text":"describe","code":true},{"text":" 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."}]},{"t":"h2","id":"keep-the-raw-file","runs":[{"text":"Keep the raw file"}]},{"t":"p","runs":[{"text":"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. "},{"text":"clean.py","code":true},{"text":" with a "},{"text":"clean(df) -> df","code":true},{"text":" at the top is the shape."}]},{"t":"h2","id":"try-this-now","runs":[{"text":"Try this now"}],"role":"try"},{"t":"p","runs":[{"text":"Take any CSV, list the distinct values of each string column with "},{"text":"value_counts()","code":true},{"text":", and write the sentinel list it actually needs. Load with "},{"text":"na_values=","code":true},{"text":", print "},{"text":"isna().mean()","code":true},{"text":", 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."}]}],"check":{"question":[{"text":"df[\"city\"].isna().sum()","code":true},{"text":" returns 0, but "},{"text":"df[\"city\"].value_counts()","code":true},{"text":" shows 4,200 rows of "},{"text":"Unknown","code":true},{"text":" and 310 of "},{"text":"unknown","code":true},{"text":". A colleague says the column has no missing data. What is wrong with that claim?"}],"options":[{"text":[{"text":"isna","code":true},{"text":" only counts values that were blank in the original file, and this file was exported from a system that never leaves cells blank, so the count is correct for what it measures."}],"correct":false,"tempting":[{"text":"Close to the mechanism, but the claim is about the data, not the exporter, and the fix is a conversion step the option does not name."}]},{"text":[{"text":"value_counts","code":true},{"text":" is case-sensitive and "},{"text":"isna","code":true},{"text":" is not, so the two counts cannot be compared."}],"correct":false,"tempting":[{"text":"isna","code":true},{"text":" has no notion of case; it tests for NaN and None, and the strings are neither."}]},{"text":[{"text":"4,510 rows out of the total is below the threshold at which pandas reports a column as having missing values."}],"correct":false,"tempting":[{"text":"There is no threshold; "},{"text":"isna","code":true},{"text":" counts every NaN, and here there are none because the sentinels are strings."}]},{"text":[{"text":"The column stores missingness as the strings "},{"text":"Unknown","code":true},{"text":" and "},{"text":"unknown","code":true},{"text":", which "},{"text":"isna","code":true},{"text":" cannot see; they must become NaN before any count or fill means anything."}],"correct":true,"tempting":null}],"order":[1,3,2,0],"why":null},"prev":{"slug":"pandas-dataframes","title":"pandas: a table with named columns, and the four things to look at first","minutes":9},"next":{"slug":"groupby-merge-and-reshape","title":"groupby, merge and pivot: getting from rows to the table a question needs","minutes":9}}}