Skip to main content

Class 11 - Cleaning data

Date: October 1, 2026

⚠️Homework
Homework 3 due October 4, 2026

Reference

For all work with tables, refer to the Tables page in the menu at the top of the page!

Skills: 3​

Notes (review before class)​

Messy Data​

  • Data collected by someone else often has missing or incorrect entries: clean it before analysis, so the analysis is reliable.
  • Example: an events company's online ticket form saved what people entered as a CSV file:
Name,Email,Num Tickets,Discount Code,Delivery,Zip Code
Josie Zhao,jo@mail.com,2,BIRTHDAY,email,90210
Sam Ochibe,s@sweb.com,1,,pickup,30301
Bart Simple,bart@simpson.org,5,STUDENT,yes,60614
Ernie O'Malley,ernie.mail.com,0,none,email,10001
Alvina Velasquez,alvie@schooledu,3,student,emall,73301
Zander,zandaman,10,,email,02115
Shweta Chowpatti,snc@this.org,three, ,pickup,02903
  • Problems: three in Num Tickets; inconsistent capitalization, and both none and blanks, in Discount Code.

Loading Data​

load_table (Class 7) takes a web address, or the name of a file in the same folder as your program:

event_data_orig = load_table("https://raw.githubusercontent.com/neu-pdi/cs2000-public-resources/refs/heads/main/static/support/dcic-events-orig.csv")
  • Column names come from the file's first line.
  • Every cell is loaded as a string, even "2", so load_table sees no problem with "three". It shows up once we use the column as numbers.

Fixing the Source, or Fixing in Code​

  • Well-formed data doesn't mix types in a column. Fix errors like three in the source, before loading: a program could drop values of the wrong type, but only after careful study, so as not to lose data that was just entered incorrectly.
  • Fix mistakes (three, typos) in the source, rather than complicating the analysis; make interpretations ("a blank means X") in code, since a later analysis may need the distinction.
  • Many errors of one kind (dates in several formats)? A function is more reliable than editing by hand, which is slow and can add mistakes.
  • Clean a copy, keeping the original (the source may also send updates). dcic-events-data.csv is a copy with three replaced by 3.

Missing Entries​

table(...) needs a value for every cell, but spreadsheet cells can be empty:

event_data_raw = load_table("https://raw.githubusercontent.com/neu-pdi/cs2000-public-resources/refs/heads/main/static/support/dcic-events-data.csv")

event_data_raw.row(1)["Discount Code"]  # -> ''
event_data_raw.row(6)["Discount Code"]  # -> ' '
  • An empty cell (Sam's) holds the empty string, "".
  • A cell that looks empty may not be: Shweta's holds a space, since Python keeps a cell's actual content.
  • And how is Ernie's "none" different from Sam's ""?

Converting Columns​

Convert columns after loading, with transform_column. int (or float) converts a string of digits to a number, with an error (a ValueError) if it can't:

event_data = event_data_raw.transform_column("Num Tickets", int)
  • Rule of thumb: on loading, convert numeric columns with transform_column, so missing or malformed data is handled explicitly, not silently.
  • Leave string columns (names, emails, discount codes, zip codes) unconverted. Zip codes use digits, but aren't numbers (what is a zip code times 3?), and a leading 0 matters (int("02115") is 2115): they are identifiers (more on Class 12).

Default Values​

  • No default for missing numbers suits every use: 0 is plausible for tickets, not for an age, and would cause errors for an exam grade (later used in a course grade). So int reports an error.
  • When a default is appropriate, a helper can choose it with try / except, like earnings_to_number (Class 9); with float, it would convert decimals:
def string_to_int(s: str, default: int) -> int:
    """converts s to a whole number, or produces default if s does not represent one"""
    try:
        return int(s)
    except ValueError:
        return default

def test_string_to_int():
    assert string_to_int("3", 0) == 3
    assert string_to_int("three", 0) == 0
    assert string_to_int("", 0) == 0
  • With transform_column, supply the default with a lambda: lambda s: string_to_int(s, 0).

Normalizing Data​

  • Normalization: making a collection of data (such as a column) share structure and formatting.
  • Question: how many orders used each discount code? freq_bar_chart (Class 10, in the Tables docs) draws a bar for each value in a column, as tall as its number of rows.
  • Examples first: what chart do we want? That means choosing how to standardize codes and handle missing values. We choose BIRTHDAY (1), STUDENT (2), none (4), so every cell must hold one of those: put "none" in blank cells, and upper-case the rest:
def cell_to_discount_code(s: str) -> str:
    """uppercases all strings other than none, and converts blank cells to none"""
    if s == "" or s == "none":
        return "none"
    else:
        return s.upper()

def test_cell_to_discount_code():
    assert cell_to_discount_code("") == "none"
    assert cell_to_discount_code("none") == "none"
    assert cell_to_discount_code("birthday") == "BIRTHDAY"
    assert cell_to_discount_code("Birthday") == "BIRTHDAY"
  • Missing example: other capitalizations of "none" (unless the data can't have them). This test fails:
    assert cell_to_discount_code("NoNe") == "none"
  • Fix: normalize the input, too: s == "" or s.lower() == "none". Then:
discount_fixed = event_data.transform_column("Discount Code", cell_to_discount_code)
discount_fixed.freq_bar_chart("Discount Code")
  • The chart has a fourth bar, for Shweta's " ".
  • Try: normalize the Delivery column, converting "yes" to "email".

An Error Model​

To catch cases like " ", ask how the data were collected, and so which errors are plausible: an error model, which helps you write robust programs.

  • A drop-down menu normalizes data automatically: use one when users choose from fixed options.
  • A text box allows any typo: swapped or missing letters, leading spaces, capitalization, random junk.
  • A spell-checker could fix swapped or missing letters (especially knowing the valid codes); what to do with junk is policy (ask the company), not programming.
  • Best: prevent bad data at the source.
  • Experience and domain knowledge teach you which errors to expect. (This adversarial thinking is core to security, too.)
  • Try: make cell_to_discount_code also turn strings of only spaces into "none". (Hint: s.replace(" ", "") removes the spaces from s.)

Finding Errors with Programs​

Programs can find data that needs correcting, even when they can't fix it. Say (in an outdated, US-centric way) a valid email contains @ and ends in .com, .edu, or .org. An is_email function can check both (with "@" in s and s.endswith(".com"); for a challenge, also require a character between them). Then event_data.filter(lambda r: not is_email(r["Email"])) gives the rows to correct.

Recap (20 mins)​

A political campaign has a table of voters (synthetic data, modeling some of what US campaigns know about voters). In it, Republican is misspelled once, as Repbulican, so filter(lambda r: r["Party"] == "Republican") misses that row.

First, set up (you'll need it for the exercises): create a file voters.csv in your cs2000-scratch repository, copy the contents of https://raw.githubusercontent.com/neu-pdi/cs2000-public-resources/refs/heads/main/static/support/10-voters.csv (open it in a new tab to copy) into it, and fix the typo. Then read through the program:

# voters.csv: our copy of 10-voters.csv, with "Repbulican" fixed by hand
voter_data = load_table("voters.csv")

def blank_to_indep(s: str) -> str:
    """replaces an empty string with Independent"""
    if s == "":
        return "Independent"
    else:
        return s

def test_blank_to_indep():
    assert blank_to_indep("") == "Independent"
    assert blank_to_indep("Democrat") == "Democrat"

voters_with_indep = voter_data.transform_column("Party", blank_to_indep)
voters_with_indep.filter(lambda r: r["Party"] == "Republican")
  • The typo was fixed in voters.csv, but blank parties become "Independent" in the program. Why not fix the blanks in voters.csv too?
  • After the program runs, which table still has a blank Party cell: voter_data or voters_with_indep?
  • What is blank_to_indep(" ")? What test would catch that?

Class Exercises (35 mins)​

  1. There is another issue with Party -- identify & fix it!
  2. Both State and Zip have a single row that has bad data. Identify what it is. Can this be corrected through an automated process? Or can you correct it manually?
  3. Design a function normalize_phone that transforms all the phone numbers to the form NNNNNNNNNN (i.e., the same form as VoterId 4), where N stands for a digit (e.g., 5551234567). Use transform_column to create a table with these normalized phone numbers.
    • Hint: It may be easier to do this with multiple calls to string methods
  4. Look at the DOB column -- can you normalize these (to, say, YYYY-MM-DD) in the same way as you did phone numbers? Why or why not? Through a combination of manually editing the source and transforming the column with a function, normalize the DOB column.
  5. Now look at the LastVoted column. Unlike with DOB, there are many rows that do not conform to YYYY-MM-DD. While manually editing the one that is of form "Oct 10 2022" may make sense, manually editing many rows is likely prone to error. Think about a strategy to transform those in other forms, likely using slicing (multiple times, with comparisons): s[start:end] is the part of the string s from position start up to (but not including) position end, where the first character is at position 0, so "Oct 10 2022"[0:3] is "Oct". If you are able to carry out your plan, that's great, but start by making a plan (in comments).

Wrap-up (5 mins)​

  • Most data needs to be filtered or cleaned in some way.
  • Sometimes that is done manually, but often it is done via code.