PHASE 14Intermediate ~30 min· topic 7 of 10

Topic 14.7

Loading and Cleaning Data

In one line

Real data arrives messy: missing values, duplicates, stray spaces, text where numbers should be and dates stored as strings. read_csv loads it, and a handful of pandas methods clean it before analysis or model training.

Think of it like this

Sorting a box of mixed receipts before doing your taxes. Some are crumpled (spaces and odd capitals), some are copied twice (duplicates), some have the amount smudged (missing values) and one is not a receipt at all (not a date). You cannot add them up until they are clean. Data scientists say that most of their time goes into this step, and for good reason: a model trained on dirty data learns the dirt.

Words you'll meet

New words in this topic, in plain English. Come back here whenever one feels fuzzy.

CSV: Comma-separated values: a text file where each line is a row and commas separate the columns.
StringIO: Makes a piece of text behave like a file, so read_csv can read it.
NaN: Not a Number: pandas' marker for a missing numeric value.
NaT: Not a Time: the same marker for a missing date.
Imputation: Filling in missing values with a sensible guess, such as the median.
Duplicate: A row that repeats another row.
.str accessor: Lets you use string methods on a whole text column at once.
.dt accessor: Lets you read parts of dates (month, year) from a whole date column.
coerce: Turn values that cannot be converted into missing values instead of raising an error.

Step by step

01Load from text and look first

The text has five data lines. Row 2 appears twice, Ben's age and Chen's score are missing, and one date is not a date. read_csv reads empty fields as NaN.

Look at the table and the dtypes before changing anything. age is float64 only because of the missing values, and joined is object (text) because dates are not converted automatically.

main.pywhole filepython

import io
import pandas as pd

text = """id,name,age,joined,score
1,Asha,23,2024-01-15,88.5
2,Ben,,2024-02-20,92
3,Chen,27,2024-02-20,
2,Ben,,2024-02-20,92
4,  dia ,45,not a date,70
"""
df = pd.read_csv(io.StringIO(text))
print(df.isna().sum())

02Remove duplicates and fix text

drop_duplicates() removes the second Ben row; the index keeps its original numbers (0, 1, 2, 4), so there is a gap. reset_index(drop=True) renumbers if you need it.

df["name"].str.strip().str.title() removes the spaces around dia and capitalises it to Dia. Methods chain because each returns a new column.

03Fill the gaps with a rule

The missing ages are filled with the median of the known ages (27 here, from 23, 27 and 45). The median is less affected by extreme values than the mean. Missing scores become 0.0: acceptable only if "no score" really means zero. Write the rule down.

main.pywhole filepython

df["age"] = df["age"].fillna(df["age"].median()).astype("int64")
df["score"] = df["score"].fillna(0.0)

04Dates, with coerce

pd.to_datetime(df["joined"], errors="coerce") turns real dates into datetime64[ns] and the bad one into NaT. Then .dt.month gives the month number. It would be a float because of the NaT, so .astype("Int64") (capital I) keeps whole numbers with a <NA> for the missing one.

Dates, with coercediagram

Rendering diagram…

05Check, then keep going

After cleaning, run the checks again: df.dtypes, df.isna().sum(), df.shape. A good habit is to write assert df["age"].notna().all() for the things you now rely on (assert stops the program with an error if the condition is false, Topic 7.2), so a bad file fails loudly next time.

Try it yourself

1
Drop instead of fill
Replace the fillna for age with df = df.dropna(subset=["age"]). How many rows remain? Which choice loses less information?
2
Without coerce
Remove errors="coerce" from to_datetime and read the error. When would you prefer the error?
3
Find the bad rows
Print df[df["joined"].isna()] after the date conversion to list the rows with unusable dates. Cleaning should report what it threw away.

Code & diagrams

Load a CSV from text and inspect it New tab

Remove the sixth line (the repeat of row 2) and see which counts change.

Expected output

   id    name   age      joined  score
0   1    Asha  23.0  2024-01-15   88.5
1   2     Ben   NaN  2024-02-20   92.0
2   3    Chen  27.0  2024-02-20    NaN
3   2     Ben   NaN  2024-02-20   92.0
4   4    dia   45.0  not a date   70.0
id          int64
name       object
age       float64
joined     object
score     float64
dtype: object
missing per column:
id        0
name      0
age       2
joined    0
score     1
dtype: int64

Clean it: duplicates, text, gaps, dates New tab

Change the age fill from median to mean and see what changes. Explain why the median is the usual safer choice.

Expected output

   id  name  age     joined  score  month
0   1  Asha   23 2024-01-15   88.5      1
1   2   Ben   27 2024-02-20   92.0      2
2   3  Chen   27 2024-02-20    0.0      2
4   4   Dia   45        NaT   70.0   <NA>
id                 int64
name              object
age                int64
joined    datetime64[ns]
score            float64
month              Int64
dtype: object

When "NA" is a country: how read_csv decides what is missing New tab

By default read_csv treats several words as missing, including NA, N/A, null and an empty field. Namibia's code is NA, so the first load silently loses a country. Tell pandas exactly which tokens mean missing for which column. The last lines show that pandas skips NaN in sum and mean while NumPy does not, and what the nullable Int64 type does with a gap.

Expected output

  country  n_orders  refund
0     NaN       5.0     NaN
1      IN       3.0    12.5
2      US       NaN     0.0
missing: {'country': 1, 'n_orders': 1, 'refund': 1}
  country  n_orders  refund
0      NA       5.0     NaN
1      IN       3.0    12.5
2      US       NaN     0.0
missing: {'country': 0, 'n_orders': 1, 'refund': 1}
pandas sum/mean skip NaN: 10.0 5.0
NumPy does not: nan nan
sum(skipna=False): nan
[1, <NA>, 3]
[False, <NA>, True]
[False, True, False]

Break it on purpose

Errors are the best teachers. Make each change, read the error, guess what went wrong, then reveal the answer.

Break #1

Converting a column that has missing values to int

Call astype on a column with NaN.

main.pywhole filepython

import pandas as pd

s = pd.Series([23.0, None, 27.0])
print(s.astype("int64"))

terminal

$ python main.py

── what you'll see ──

pandas.errors.IntCastingNaNError: Cannot convert non-finite values (NA or inf) to integer

Myth vs fact

Myth

Missing values should always be filled with 0.

Fact

Zero is a real value. Filling with it can change averages and mislead a model. Choose a rule that fits the meaning, or keep the NaN.

Myth

read_csv always gets the types right.

Fact

It guesses. One stray text cell makes a numeric column text. Always check dtypes.

Myth

Cleaning is a one-off job done by hand.

Fact

Do it in code so the same steps can be re-run on new data and reviewed.

Pro corner

Extra depth for experienced readers. New to this? Skip it for now and come back later.

▸
Pass dtype=, parse_dates=, na_values= and usecols= to read_csv to take control while loading: dtype stops wrong guesses, na_values marks tokens such as "N/A" as missing, usecols loads only the columns you need, and chunksize reads huge files in pieces.
▸
Filling with a statistic computed on the whole dataset can leak test information into training. Compute the median from the training rows only, then apply it to both.
▸
The nullable dtypes (Int64, boolean, string) use a real missing-value marker <NA> instead of turning integers into floats. They are the modern way to keep whole numbers with gaps.
▸
Several missing markers exist. NaN is a float (and NaN != NaN), NaT is the date version, None is Python's own, and pd.NA is the newer marker used by the nullable dtypes. pd.isna() and isna() recognise all of them, so never test with == np.nan, which is always False. pd.NA follows three-valued logic: pd.NA > 1 is <NA> (unknown), and bool(pd.NA) raises TypeError, so an if on it fails loudly instead of guessing.
▸
Aggregations skip missing values in pandas (skipna=True), so Series.sum() and mean() ignore NaN, whereas NumPy's np.sum and np.mean return nan. The same words mean different things per library. Also, count() counts non-missing values and len() or size counts rows; a column with 20 percent gaps has a very different count(). See the last runnable example.
▸
**read_csv has a default list of tokens it treats as missing**, which includes NA, N/A, NULL, null, NaN, None and the empty string. A real value such as Namibia's country code NA becomes NaN silently. Use keep_default_na=False together with an explicit na_values= for each column, then count isna() before and after. For very big files, dtype= and usecols= also save memory, and parsing dates with an explicit format= is faster and stricter than guessing.
▸
Imputation has a statistical cost: filling with the mean shrinks the spread of the column and can weaken relationships. For model training, add a flag column such as age_missing so the model can learn that missingness itself carries information, and fit the fill value on the training rows only.

Remember this

1
pd.read_csv(path) reads a CSV (comma-separated values) file into a DataFrame. Here we have no file, so we give it text: pd.read_csv(io.StringIO(text)). StringIO makes a string behave like a file. pandas looks at each column and guesses a dtype: whole numbers become int64, decimals float64, everything else object. Check df.dtypes straight away, because a single bad cell can turn a whole numeric column into text.
2
Missing values appear as NaN ("not a number") in numeric columns and NaT in dates. df.isna() marks them, df.isna().sum() counts per column. A numeric column with missing values becomes float64 (because NaN is a float), which is why an age column of whole numbers can display as 23.0. You then have choices: dropna() removes rows, fillna(value) fills them with a constant, the median (the middle value when the numbers are in order) or the mean, or you keep them for a model that can handle them. Which one is right depends on the data: filling missing ages with the median is reasonable; filling missing prices with 0 may be wrong.
3
Duplicates are repeated rows. df.duplicated() marks the repeats and df.drop_duplicates() removes them, keeping the first. Check the cause too: a duplicate order could be a data-export bug or genuinely two orders.
4
Text columns hide problems: spaces, mixed capitals, spelling variants. Recall from Topic 3.2: strings have methods like strip and lower. The .str accessor (a doorway to those methods) applies them to a whole column: .str.strip(), .str.title(), .str.lower(), .str.replace(...), .str.contains(...). They are vectorised: no loop needed.
5
Dates are read as plain text (Topic 7.11 explains dates and datetime in plain Python). pd.to_datetime(col, errors="coerce") converts them, turning anything that is not a date into NaT instead of raising an error. After that, the .dt accessor gives parts: .dt.month, .dt.year, .dt.dayofweek. Date arithmetic and sorting then work properly.
6
astype converts a column to another type: df["age"].astype("int64"). It fails on NaN for plain integer types, so fill or drop first (or use the nullable "Int64" type, which can hold missing values and shows them as <NA>). Be explicit about int64 here: on some platforms, including the browser, plain int may become a 32-bit type.
7
Cleaning for AI: a model or an embedding step needs consistent inputs. Typical steps: fix types, handle missing values, remove duplicates, and normalise text. Duplicates matter in training data. A training set is the part of the data a model learns from, and a test set is the part held back to check it. If the same row sits in both, the test is rigged, because the model has already seen the answer (this is called data leakage). Keep the raw data untouched and do the cleaning in code you can re-run. Splitting into train and test sets and scaling numbers come next.

Explain it without notes

How do you find missing values in a DataFrame, and what are your options for handling them?

Why can an integer column show as floats after read_csv?

What does errors="coerce" do in to_datetime? When is it dangerous?

Why does removing duplicates matter before training a model?

A CSV has a country column where Namibia (NA) shows up as missing after read_csv. Why, and how do you load it correctly?

How do missing values behave in pandas sums and means compared with NumPy, and what is pd.NA?

Practice

Load a CSV text with columns item,price where one price is missing, and fill the missing price with the median.

Clean the text column [" PEN ", "Book", "pen"] so all three entries match, and count the unique values.

Convert the strings ["2024-03-01", "oops"] to dates with coerce and count the missing results.

Load the CSV text code,n / NA,1 / IN,2 so that the code NA stays a real string, and print the list of codes.

Trade-offs

↔
Dropping versus filling. Dropping loses data; filling invents data. Both change the result: choose and document.
↔
Strict versus forgiving parsing. Errors catch bad data early; coerce keeps going. Use coerce, then count what it removed.
↔
Cleaning in code versus by hand. Code is repeatable and reviewable; hand edits are quicker once but cannot be re-run.

Done when you can

Done when you can load CSV text with read_csv(io.StringIO(...)) and read dtypes.
Done when you can count missing values and fill, drop or keep them for a stated reason.
Done when you can remove duplicates and say why that matters for training data.
Done when you can clean a text column with .str and parse dates with to_datetime.
Done when you can explain why an integer column becomes float, and the Int64 fix.

Command Palette

Words you'll meet

Step by step

01Load from text and look first

02Remove duplicates and fix text

03Fill the gaps with a rule

04Dates, with coerce

05Check, then keep going

Try it yourself

Drop instead of fill

Without coerce

Find the bad rows

Code & diagrams

Break it on purpose

Converting a column that has missing values to int

Myth vs fact

Pro corner

Remember this

Explain it without notes

Practice

Trade-offs

Done when you can