Coding Colab

Survey of data scientists, CrowdFlower, 2016.

Just as building a boat is 90% sanding, it often feels like doing data science is 90% cleaning (the survey data says it’s just 60%, so that’s good news!). The time we will spend cleaning data this afternoon is a taste of your data science future.

Today’s file is a season of stream chemistry, 320 rows from six monitoring sites, transcribed from paper field sheets by several people over the course of a season. They were all careful… and they all made reasonable decisions about data entry. Unfortunately, their decisions were not the same as each other’s (maybe the PI should have made a data dictionary to ensure consistency!).

Your task is to turn their work into a table that you could use for rigorous data analysis.

Work in pairs, in one shared notebook, taking turns at the keyboard. Swap every time you finish a numbered task. The person not typing should read the code through (quietly) and say what they expect it to produce, before you run it.

We have about 45 minutes for this colab, so please let Cella or Kelly know if you and your partner are getting stuck!

The data

https://eds-217-essential-python.github.io/data/messy_field_survey.csv

Column	What it should be
`site`	one of six site labels
`collection date`	the date the sample was taken
`temperature_c`	water temperature, °C
`pH`	pH, a decimal number
`dissolved_oxygen_mg_L`	dissolved oxygen, mg/L
`conductivity_uS_cm`	specific conductance, µS/cm
`n_replicates`	how many bottles were filled, a whole number

The code patterns you will need

Most of what you need is from today. The last two lines are older, and they are here because you will need them too.

df.drop_duplicates()                    # and subset=[...]
df.dropna(subset=['col'])               # remove rows missing a named column
df['col'] = df['col'].fillna(value)     # fill the gaps instead
df['col'] = df['col'].astype(float)     # int, float, str
df['col'] = df['col'].str.strip()       # .lower(), .replace(old, new)
df['new'] = expression                  # the derived-column pattern
def my_func(x):  ...                    # write it once, from this morning
df['new'] = df['col'].apply(my_func)    # run your own function down a column

df[df['col'] > value].copy()            # the filter pattern, from Day 3
df.rename(columns={'old': 'new'})       # renaming, from Day 2

Setup

Create a notebook named Colab_4D_Cleaning_Messy_Data.ipynb, and give it a title cell with both partners’ names in it:

# Day 4: Colab 4D - From Field Sheet to DataFrame

Partners: your names here

Date: 09/03/2026

Then read the file in.

Part 1: Find out what is wrong (about 10 minutes)

Do not fix anything yet. Diagnose first, and write what you find in a markdown cell as you go.

How many rows and columns? Run .head() and .info(). Which columns came in as object when you expected a number, and which came in as float64 when you expected a whole number?
Run .isnull().sum(). Which four columns have gaps, and how many each?
Run .duplicated().sum(). How many rows are exact copies of an earlier row?
Run survey['site'].value_counts(). There are six sites. How many distinct labels does the file contain? Look carefully at the quotation marks in the output of survey['site'].unique().
Run .describe() on temperature_c. The minimum is not a temperature any stream has ever had… What’s going on here??

What the minimum means

Dataloggers that fail often write a value chosen to be obviously impossible rather than leaving the field blank. -999, -9999 and NA are the usual suspects. A sentinel value like one of those is missing data wearing a costume… and .isnull() will never find it! It can also really mess up your averages and other statistics. Reading .describe(), and knowing what the measurement can plausibly be, are what keep you from mis-interpreting numerical missing data as actual information.

Part 2: Clean it (about 20 minutes)

Work in the order below and check .shape after each step.

Remove the exact duplicate rows. How many rows are left?
Fix the site column so that all six sites have one label each. You will need three separate statements, one per method, each assigned back to survey['site']: strip the whitespace, lower-case the text, and replace the hyphens with underscores. Confirm with .value_counts() that you have exactly six distinct site labels, reading site_a through site_f.

Do not chain these

Write one method per line. A single chained line does the same three things, but if one of the methods in the chain quietly does nothing, the chain gives you no way to see which one it was. Three separate lines cost you a few seconds and let you look at the column after each step.

pH came in as text. Find out why by looking at survey['pH'].unique(), then fix it with one .str.replace() and one .astype(), in that order. Confirm the dtype is float64 and that .describe() gives a plausible pH range.

Note

🐍 Try survey['pH'].astype(float) before the replace, on purpose, and read the error. The message gives you the exact value that could not be converted, which usually tells you what to fix faster than scrolling through the column would. As is often the case, a little bit of “pre-flight” testing will make your data science journey much smoother.

Three measurement columns have blanks: temperature_c, dissolved_oxygen_mg_L, and conductivity_uS_cm. A row with no measurement is no use to you, so drop those rows in a single .dropna() call with a list in subset=. How many rows did that cost?
n_replicates also has blanks, but here a blank means the field sheet recorded a single bottle and nobody bothered to write “1”. Fill those with 1 instead of dropping the rows, then convert the column to int. Confirm with .value_counts().
Now let’s deal with the impossible temperatures. Use the filter pattern from Day 3 to keep only the rows where temperature_c is above -100, and end the line with .copy(). How many rows did the loggers ruin?
Re-run .describe() on temperature_c. Compare the mean to the one you got in task 5. In a markdown cell, write one short statement about what the sentinel rows did to the average you saw in task 5.
Rename collection date to collection_date, so you can reach it without quoting trouble later. .rename() is from Day 2; it takes columns= and a dictionary.

Part 3: Transform it (about 10 minutes)

Add a column called conductivity_mS_cm holding conductivity in millisiemens per centimetre, which is the microsiemens value divided by 1000.
Add a column called temperature_f holding the temperature in Fahrenheit. Do it twice: once with the derived-column pattern and plain arithmetic, and once by writing a function celsius_to_fahrenheit and using .apply(). Check that the two columns agree.
Write a function called classify_ph that takes a pH value and returns 'acidic' below 6.5, 'alkaline' above 7.5, and 'neutral' in between. Apply it to the pH column, store the result in a column called ph_class, and report the counts.

Part 4: Ask it something (about 5 minutes)

You should now have a table you can trust. Let’s put it to work!

Use the filter pattern to build a table of just the acidic samples. Which sites do they come from? Use .value_counts() on site.
Two of the six sites account for nearly all the acidic samples. Take either one of those two, and compare it against site_d, which is not one of them: filter to each in turn and compare the mean of dissolved_oxygen_mg_L. (Two filters, two .mean() calls. Tomorrow you will learn to do all six at once.)
In a markdown cell of three or four sentences: what would you tell the PI whose project collected this data? Describe the single change to their field sheet that would have saved you the most time this afternoon, and describe what evidence in the file makes you pick that one.

Wrap-up

Before you close the notebook, check that:

your notebook records the row count after every step that removed rows
you can say how many rows the original file had, how many you finished with, and where the difference went
every cleaning decision has a markdown sentence next to it saying why, not just what

If we have time, we will hear two or three pairs on question 19.

If you finish early

Go back to task 11. You filtered the sentinel temperatures out. Would you get the same final table if you had done that step first, before the .dropna() in task 9? Work out the answer by reasoning about the two masks, then test it. Say in a markdown cell whether the order of cleaning steps matters here, and whether you would expect that to be true in general.