Interactive Session

A cartoon panda is getting a bubble bath. MidJourney 5

For three days we have asked questions of tables that (mostly) behaved themselves. Today we will learn what to do with tables full of data that are less consistent and that need cleaning up.

Real environmental data arrives with gaps, duplications, and formatting errors. This morning we will learn to use pandas commands to find those problems, decide what to do about each one, and practice writing down what we decided (and why!).

Our dataset is NOAA’s marine microplastics archive, and we will use it throughout most of the day. This morning we will clean it and we will use the first session of the afternoon to transform it. Finally, the end-of-day practice will use the data to answer questions about the distribution of marine plastics. Only the afternoon colab uses a different file.

By the end of this session you will be able to:

find gaps in a dataset, and specify which columns are missing values
remove rows with .dropna(), and explain why using this command with precision is so important
fill gaps with .fillna(value) when removal isn’t practical
find duplicated rows with .duplicated(), and drop them with .drop_duplicates()
read a column’s dtype and change it with .astype()

Getting Started

Create the file. In the Explorer, hover over the EDS217 heading and click New File…, then type the name in full, extension included: Session_4A_Cleaning_Data.ipynb
Check the kernel. The Kernel Selector in the notebook’s action bar should read Python 3.11.15 (Conda: eds217). If it reads anything else, click it, choose Change Kernel, and pick the eds217 entry.
Add a title cell. Click + Markdown in the action bar, and give it this content, with today’s date:

# Day 4: Session 4A - Missing, Duplicated, Miscast

[Session Webpage](https://eds-217-essential-python.github.io/course-materials/interactive-sessions/4a_cleaning_data.html)

Date: 09/03/2026

Save with Ctrl + S (Cmd + S on macOS), and keep saving as you go.
Read in the marine microplastics data:

Code

import pandas as pd

url = 'https://eds-217-essential-python.github.io/data/marine_microplastics.csv'
plastics = pd.read_csv(url)
plastics.shape

(16245, 22)

16,245 rows and 22 columns. Every row is one sample taken somewhere in an ocean, on some day between 1972 and 2022. Most are water samples, but not all: about six thousand of them are sediment grabs or timed shoreline counts, and the sampling method turns out to matter a great deal for what is missing.

Code

plastics.columns.tolist()

['OBJECTID',
 'Oceans',
 'Regions',
 'SubRegions',
 'Sampling Method',
 'Measurement',
 'Unit',
 'Density Range',
 'Density Class',
 'Short Reference',
 'Long Reference',
 'DOI',
 'Organization',
 'Keywords',
 'Accession Number',
 'Accession Link',
 'Latitude',
 'Longitude',
 'Date',
 'GlobalID',
 'x',
 'y']

What is missing

We met this pattern on Day 2, and it should be the first thing you run on any table you did not build yourself:

Code

plastics.isnull().sum()

OBJECTID                0
Oceans                271
Regions              8249
SubRegions          15657
Sampling Method         0
Measurement          5792
Unit                    0
Density Range           0
Density Class           0
Short Reference         0
Long Reference          0
DOI                     0
Organization            0
Keywords               18
Accession Number        0
Accession Link          0
Latitude                0
Longitude               0
Date                    0
GlobalID                0
x                       0
y                       0
dtype: int64

Five columns have holes in them, and the three worst are worth looking at one at a time:

Code

plastics.isnull().sum().sort_values(ascending=False).head(5)

SubRegions     15657
Regions         8249
Measurement     5792
Oceans           271
Keywords          18
dtype: int64

SubRegions is missing on 15,657 rows out of 16,245, and Regions on 8,249. Measurement, which is the actual number this whole archive exists to record, is missing on 5,792.

Note

🐍 .isnull() returns a True/False for every cell, and .sum() counts the Trues, one column at a time. It is the same “add up a mask” trick we used on Day 3 when we counted the rows that passed a filter.

Dropping rows: `.dropna()`

The obvious move is to throw away the incomplete rows. .dropna() does that:

Code

plastics.dropna().shape

(588, 22)

588 rows. We started with 16,245 and kept 3.6% of them.

Throwing away 96% of the rows is not a cleaning step, it is destroying the dataset. Notice how quietly it happened, too: one method, no arguments, no warning, and no error message.

Bare .dropna() is almost always the wrong tool

With no arguments, .dropna() removes every row that has a missing value in any column at all. Here, a sample with a perfectly good measurement gets deleted because nobody recorded its SubRegions.

The fix is to say which columns you actually care about.

Dropping on purpose: `.dropna(subset=[...])`

subset= takes a list of column names, and only a gap in one of those columns can delete a row:

Code

measured = plastics.dropna(subset=['Measurement'])
measured.shape

(10453, 22)

10,453 rows. Dropping on Measurement is either a defensible cleaning step or a terrible one, depending entirely on what the rows we just deleted have in common. Compare the units before and after to find out:

Code

plastics['Unit'].value_counts()

Unit
pieces/m3           10178
pieces/10 mins       5792
pieces kg-1 d.w.      275
Name: count, dtype: int64

Code

measured['Unit'].value_counts()

Unit
pieces/m3           10178
pieces kg-1 d.w.      275
Name: count, dtype: int64

An entire unit vanished. All 5,792 pieces/10 mins samples had a missing Measurement, so dropping on Measurement deleted every one of them, and with them every sample taken by the one study that used that method.

Missing values are rarely scattered at random

On Day 2 we learned that a value column can stack incompatible units. The other half of that lesson is here: missingness is itself a variable. In this file it lines up exactly with sampling method, so a filter that looks like it removes bad rows actually removes one team’s entire field campaign.

Before you drop anything, count what you are dropping and look at what it has in common.

Knowing why the 5,792 rows are missing a measurement does not mean you should keep them. If you are about to average measurements, rows with no measurement are useless to you. Dropping them is fine, as long as you can say out loud what you removed and you write that decision down in your notebook.

✏️ Test your knowledge

Oceans is missing on 271 rows. Use .dropna() with subset= to build a table called located that has none of them, and report its shape. Then check whether those 271 rows had anything in common: compare plastics['Unit'].value_counts() with the same call on located.

Filling gaps: `.fillna(value)`

Sometimes the missing value means something, and removing the row throws that meaning away. Regions is missing on 8,249 rows not because anyone forgot, but because the sample was in open ocean, outside any named sea.

.fillna() replaces the gaps with a value you choose:

Code

plastics['Regions'] = plastics['Regions'].fillna('Unspecified')
plastics['Regions'].value_counts().head(5)

Regions
Unspecified          8249
Gulf of Mexico       4817
Caribbean Sea        1886
Mediterranean Sea     345
North Sea             252
Name: count, dtype: int64

Two things are worth noticing in that one line. .fillna() hangs off a column, not the whole table. And it returns a new column, so you have to assign it back to plastics['Regions'] for the change to stick. The assign-it-back rule is the same one we have met every day this week: pandas gives you back a result, and keeping it or discarding it is up to you.

Code

plastics.isnull().sum().sort_values(ascending=False).head(3)

SubRegions     15657
Measurement     5792
Oceans           271
dtype: int64

Regions has dropped out of the top three entirely, which is what going to zero looks like here.

Note

🐍 Choose your fill value on purpose. 'Unspecified' is honest: it says “we know this is blank.” Filling with 0 would have been a lie, because zero is a real measurement. Never fill a numeric column with a number that could be mistaken for data.

✏️ Test your knowledge

SubRegions is missing on 15,657 rows. Fill it with 'Unspecified', assign the result back, and re-run plastics.isnull().sum() to confirm it worked. Then, in a markdown cell, say whether filling or dropping was the right call for that column, and why.

Duplicated rows

The other classic defect is the same sample entered twice. .duplicated() returns a mask, one True or False per row, and .sum() counts them:

Code

plastics.duplicated().sum()

np.int64(0)

Zero. Clean data, then.

Except no. Look at the columns again:

Code

plastics[['OBJECTID', 'GlobalID', 'Latitude', 'Longitude', 'Date', 'Measurement']].head(3)

	OBJECTID	GlobalID	Latitude	Longitude	Date	Measurement
0	10008	1e5b8e71-037b-4887-a276-f1e4552acb1f	-58.428300	-64.1640	2/3/2017 12:00:00 AM	0.020000
1	8680	a40f7f7c-1025-4aac-ad16-ee4cba196870	-51.308200	-60.5467	11/17/2013 12:00:00 AM	0.008000
2	13257	febf79b8-7e2c-46e6-bc15-e08492ec2029	-51.826667	-72.5750	12/26/2015 12:00:00 AM	0.019886

OBJECTID and GlobalID are identifiers, assigned one per record when the archive was built. No two rows can ever share them, so no two rows can ever be identical, so .duplicated() can only ever return zero. The answer was never about the data.

subset= fixes this the same way it fixed .dropna(). Point it at the columns that hold the science:

Code

sample_columns = ['Latitude', 'Longitude', 'Date', 'Measurement', 'Unit']
plastics.duplicated(subset=sample_columns).sum()

np.int64(856)

856 rows record the same measurement, in the same unit, at the same coordinates, on the same day, as some earlier row.

.duplicated() is only as good as the columns you point it at

A bare .duplicated() is an easy way to convince yourself that a dataset is clean when it is not. Any table with an auto-generated ID column will report zero duplicates forever.

Decide what “the same observation” means for your data, put those column names in a list, and pass the list to subset=.

.drop_duplicates() takes the same argument and returns a table with the repeats removed, keeping the first of each:

Code

deduplicated = plastics.drop_duplicates(subset=sample_columns)
deduplicated.shape

(15389, 22)

The hard part comes next, and pandas cannot do it for you. Are those 856 rows really duplicates? Two tows through the same patch of water on the same day, both finding zero pieces per cubic metre, would look exactly like this and would both be real:

Code

repeats = plastics[plastics.duplicated(subset=sample_columns, keep=False)]
print(repeats.shape)
repeats[['Measurement', 'Unit', 'Sampling Method', 'Short Reference']].head(6)

(1465, 22)

	Measurement	Unit	Sampling Method	Short Reference
6	0.000000	pieces kg-1 d.w.	Megacorer	Courtene-Jones et al. 2020
37	2115.655853	pieces/m3	PVC cylinder	Alvarez-Zeferino et al. 2020
40	NaN	pieces/10 mins	Hand picking	Tunnell et al. 2020
46	NaN	pieces/10 mins	Hand picking	Tunnell et al. 2020
52	0.000000	pieces/m3	Neuston net	Law et al.2010
73	0.002088	pieces/m3	Neuston net	Law et al.2010

Code

(repeats['Measurement'] == 0).sum()

np.int64(652)

The 856 duplicates sit inside 1,465 rows, because keep=False marks every member of a group rather than only the extras. Of those 1,465 rows, 652 report a measurement of zero. All those zeros are a strong hint that many of these may be genuine repeat samples that happened to agree, rather than double entries, so we will keep them and say so in our notebooks. We built deduplicated to look at rather than to use, so set it aside: everything below works from plastics.

Note

🐍 keep=False marks every member of a duplicated group, not just the later ones. Use it when you want to look at duplicates; use the default when you want to drop them.

✏️ Test your knowledge

Build a different list of columns that you think defines “the same observation” here, and count the duplicates it finds. Try dropping Measurement from the list, or adding Sampling Method. Write one sentence in a markdown cell saying which definition you would defend to a reviewer.

Types: reading them and changing them

Every column has a dtype, which is the type of data pandas has assigned to that column:

Code

plastics.dtypes

OBJECTID              int64
Oceans               object
Regions              object
SubRegions           object
Sampling Method      object
Measurement         float64
Unit                 object
Density Range        object
Density Class        object
Short Reference      object
Long Reference       object
DOI                  object
Organization         object
Keywords             object
Accession Number      int64
Accession Link       object
Latitude            float64
Longitude           float64
Date                 object
GlobalID             object
x                   float64
y                   float64
dtype: object

float64 is a decimal number, int64 a whole number, and object is the catch-all for text, or for anything pandas could not identify. Three of these dtypes are wrong for what the column actually means, and we deal with each of them below.

Accession Number is stored as an integer:

Code

plastics['Accession Number'].head(3)

0    211009
1    211009
2    276422
Name: Accession Number, dtype: int64

It is not a quantity. Nobody will ever add two accession numbers together or take their mean. It is a label that happens to be spelled with digits, and leaving it numeric means it can end up averaged into some later summary without anyone noticing. .astype() converts a column to the type you name:

Code

plastics['Accession Number'] = plastics['Accession Number'].astype(str)
plastics['Accession Number'].head(3)

0    211009
1    211009
2    276422
Name: Accession Number, dtype: object

Code

plastics['Accession Number'].dtype

dtype('O')

object, which is what text looks like. The same assign-it-back rule applies: .astype() returns a converted column and changes nothing until you store it.

The three conversions you will use all week are .astype(float), .astype(int), and .astype(str):

Code

plastics['Latitude'].astype(int).head(3)

0   -58
1   -51
2   -51
Name: Latitude, dtype: int64

Casting latitude to int is a demonstration, not a suggestion. .astype(int) truncates toward zero, so -58.4283 becomes -58, and nearly half a degree of latitude, about 47 kilometres, is gone. Converting to a narrower type throws information away, and it does it silently.

.astype() fails loudly on text it cannot parse

If a numeric column has even one entry like 'not recorded', .astype(float) raises a ValueError naming the offending value. A loud failure is much better than a silent one here, because the error message tells you exactly which entry to go and look at. Read the value it names, fix that entry, and convert again.

You will meet exactly this problem in the colab this afternoon.

Date is an object too, because it is text like 2/3/2017 12:00:00 AM. Dates are their own subject and they get a whole session on Day 6, so we will leave that one alone for now.

✏️ Test your knowledge

Convert OBJECTID to text with .astype(str), assign it back, and confirm the change with .dtype. Then, in a markdown cell, name the one miscast column we have not converted, say what its dtype should be, and say why we are leaving it alone until Day 6.

Putting it together

A cleaning pass is a short, ordered list of decisions, each one a line of code with a comment saying why:

Code

clean = pd.read_csv(url)

# Open-ocean samples have no named region or subregion; that is information, not a gap.
clean['Regions'] = clean['Regions'].fillna('Unspecified')
clean['SubRegions'] = clean['SubRegions'].fillna('Unspecified')

# Accession numbers are labels, not quantities.
clean['Accession Number'] = clean['Accession Number'].astype(str)

# Keep only samples that have a measurement. This drops all 5,792 'pieces/10 mins'
# rows, which is acceptable here because we are about to average measurements.
clean = clean.dropna(subset=['Measurement'])

clean.shape

(10453, 22)

Code

clean.isnull().sum().sort_values(ascending=False).head(3)

Keywords    18
OBJECTID     0
Oceans       0
dtype: int64

Five lines, three decisions, and a note in the code saying what the third one cost. Notice that the block starts from read_csv rather than from the table we have been editing all morning, so it is the whole record and not the last part of it. If a colleague asks you next month why your row count is 10,453 and not 16,245, the answer is right there, and it will still be right after you restart the kernel.

Key points

Run df.isnull().sum() first, every time, on any table you did not build yourself.
Bare .dropna() deletes a row for a gap in any column. In this file that cost 96% of the data. Use .dropna(subset=['col']) and name the columns you actually need.
Before you drop, count what you are dropping and look at what it has in common. Missingness usually lines up with something.
.fillna(value) replaces gaps with a value you choose. Choose one that cannot be mistaken for data.
.duplicated() and .drop_duplicates() both take subset=[...]. Without it, any table with an ID column reports zero duplicates forever.
.astype(str/int/float) converts a column. It returns a new column, so assign it back, and remember that narrowing a type throws information away.

Getting Started

What is missing

Dropping rows: .dropna()

Dropping on purpose: .dropna(subset=[...])

Filling gaps: .fillna(value)

Duplicated rows

Types: reading them and changing them

Putting it together

Key points

Resources

Dropping rows: `.dropna()`

Dropping on purpose: `.dropna(subset=[...])`

Filling gaps: `.fillna(value)`