Data Cleaning, Recoding & Codebooks

Novice · Data & Analysis track · ~35 min hands-on + readings and quiz

← Back to the Data & Analysis track

Most analysis time is cleaning — let AI speed it up while you keep control.

What you’ll be able to do

  • Have AI flag data-quality issues and propose fixes
  • Recode variables consistently and document the change
  • Auto-draft a codebook and validate transformations

Overview

Most of analysis is cleaning. AI can propose recodes, spot inconsistencies, and draft documentation — but every transformation needs a check that it did what you meant.

A codebook (what each variable means and how it’s coded) is easy to skip and painful to lack. AI can draft one straight from your data, which you then correct.

Practice activities

Activity 1 · Novice — Diagnose the mess (Philadelphia 311)

Time ~18 min · Tools ChatGPT Edu + a browser

Goal. Find data-quality problems before you analyze.

Setup. Open 311 Service & Information Requests. The full set is millions of rows, so grab a slice: download one recent year, or use the dataset’s API with a date filter. Paste ~20 rows plus the full column list into ChatGPT Edu. Public data.

Steps.

  1. Ask for a diagnosis (no changes yet):

    Here is a sample of Philadelphia 311 service requests. List the data-quality issues you can see — inconsistent categories, missing values, suspicious dates, duplicate-looking rows — and propose a fix for each. Don’t change anything yet.

  2. Review its list and mark which fixes you agree with. You make the call.

  3. Probe one field:

    For the service-type field, show the distinct values with counts, and group near-duplicates (different spellings or casing) into clean categories.

Expected result. A list of issues (missing ZIPs, mixed-case categories, placeholder dates) and a proposed cleaning plan.

Check your work. Pick one proposed fix and verify the issue is real in the raw rows before accepting it.

Common pitfalls. AI may “fix” data by inventing values — only accept fixes you can verify. Keep the raw file untouched; never overwrite it.

Stretch (optional). Ask for the cleaning steps as a documented, re-runnable R/Python script you could apply to next year’s data.

Activity 2 · Novice — Recode and document (Philadelphia crime categories)

Time ~18 min · Tools ChatGPT Edu + R or Python

Goal. Standardize a messy category field and produce a codebook.

Setup. Use this 18-row sample of fabricated pedestrian crash records (modeled on PennDOT crash data structure), or grab a real slice from OpenDataPhilly Crime Incidents. This sample has the typical data-cleaning problems built in:

crash_id,date,time,severity,ped_age,ped_sex,speed_limit,road_type,crosswalk,lighting
1001,2024-03-15,16:45,Minor,34,F,25,Local,Yes,Daylight
1002,2024-03-15,17:30,serious,67,M,35,Arterial,No,Dark - lighted
1003,2024/03/16,08:15,Minor,12,F,,School zone,Yes,Daylight
1004,2024-03-16,23:10,Fatal,45,M,40,Arterial,no,Dark - not lighted
1005,2024-03-17,07:00,minor,28,Female,25,Local,Yes,Daylight
1006,2024-03-17,18:45,Serious,71,M,35,arterial,No,Dusk
1007,2024-03-18,14:20,Minor,9,,25,Local,Yes,Daylight
1008,2024-03-18,16:00,Minor,55,F,30,Collector,Yes,Daylight
1009,2024-03-19,17:15,Serious,82,M,40,Arterial,No,Dark - lighted
1010,2024-03-19,17:15,Serious,82,M,40,Arterial,No,Dark - lighted
1011,2024-03-20,12:00,minor,41,F,25,local,Y,Daylight
1012,2024-03-20,19:30,Fatal,38,M,45,Arterial,No,Dark - not lighted
1013,2024-03-21,09:45,Minor,22,F,25,Local,Yes,daylight
1014,9999-03-22,16:30,Serious,56,M,35,Collector,No,Dark - lighted
1015,2024-03-22,15:00,,29,F,25,Local,Yes,Daylight
1016,2024-03-23,07:30,Minor,14,M,25,School zone,Yes,Daylight
1017,2024-03-23,18:00,Serious,60,F,35,Arterial,No,Dusk
1018,2024-03-24,20:45,Minor,33,M,30,Collector,yes,Dark - lighted

Issues planted: inconsistent capitalization (severity, road_type, crosswalk, lighting), mixed date formats (row 3), a duplicate row (1009/1010), missing values (speed_limit in row 3, ped_sex in row 7, severity in row 15), an impossible date (row 14: year 9999), inconsistent sex coding (“Female” vs “F”), and inconsistent Yes/No/Y/yes coding in the crosswalk field.

Steps.

  1. Recode:

    Here are the distinct values of the crime offense field. Propose a recode into 4–6 analysis groups (e.g., violent, property, quality-of-life, other) and write R/Python code mapping each original value to a group. Leave anything ambiguous as “review”.

  2. Generate documentation:

    Generate a codebook entry for the new grouped variable: its name, definition, the full mapping table, and how missing/ambiguous values are handled.

  3. Verify counts before and after.

Expected result. A grouped variable plus a codebook table; counts reconcile.

Check your work. The grouped counts must sum to the original total — confirm nothing was dropped. Inspect the “review” bucket by hand.

Common pitfalls. Don’t let AI silently bucket ambiguous offenses. Grouping choices are substantive decisions — they’re yours, not the model’s.

Stretch (optional). Ask AI to flag any offense whose grouping is genuinely debatable and explain the tradeoff.

Activity 3 · Novice — Same cleaning job, but the AI runs the code itself (Claude Code)

Time ~20 min · Tools Claude Code

Goal. See what changes when the AI can actually execute the cleaning instead of just describing it — Claude Code writes the script, runs it, checks the output, and hands you a cleaned file with the counts to prove it.

If this is your first time hearing “command-line agent”: Claude Code is a paid Anthropic tool that runs in your computer’s terminal (the text-based window, not a browser) and, unlike a chat window, can read and write real files on your machine and run code directly — so when the script errors, it sees the error and fixes it without you copy-pasting anything.

Setup. Save the fabricated crash sample from Activity 2 as crashes_sample.csv in an empty folder. Open your terminal, cd into that folder, and start claude. (Install instructions are at docs.anthropic.com; you need a Claude subscription or API access.)

Steps.

  1. Ask it to diagnose first, then clean — and to actually run the code:

    Read crashes_sample.csv. First list every data quality problem you find, with the crash_id of each affected row. Then write a Python script that fixes them — standardize capitalization and Yes/No coding, normalize dates, recode “Female” to “F”, remove exact duplicates, and set the impossible date to missing rather than guessing. Run the script and save the result as crashes_clean.csv. Don’t delete rows with missing values — leave them missing.

  2. Make it show its receipts:

    Print the row count before and after, show me the duplicate row you removed and the impossible-date row you flagged, and give me a value-counts table for severity, crosswalk, and lighting before vs. after.

  3. Have it document what it did:

    Write a codebook.md describing each variable, its allowed values after cleaning, and every cleaning decision you made — including anything you left as missing and why.

Expected result. A crashes_clean.csv and codebook.md sitting in your folder, plus a before/after summary the agent produced by running the script itself — not by predicting what the output would probably be.

Check your work. The arithmetic should reconcile exactly: 18 rows in, 17 out (one duplicate, 1009/1010, removed). Severity should collapse to Minor/Serious/Fatal plus one missing (crash 1015); crosswalk to Yes/No; crash 1014’s date should be missing, not “fixed” to a made-up year. Open both files yourself — the agent verifying its own work is a check, not the check.

Common pitfalls. Claude Code is not Drexel-supported and is approved for Low Risk data only — this activity works precisely because the CSV is fabricated; never point it (or any personally purchased AI tool) at real crash records, student data, or anything with identifiers. Also watch for over-helpfulness: agents sometimes “fix” the impossible date by inventing a plausible one, which is why the prompt says to set it to missing.

Stretch (optional). Ask it to add a validation step to the script that fails loudly if any severity value falls outside Minor/Serious/Fatal — then corrupt one row and confirm it catches it.

Check your readiness

Answer these, then check — your score suggests whether to dive in or skim the readings first.

Useful resources

  • tidyverse / dplyr — recoding and cleaning verbs in R.
  • OpenRefine — a free tool for cleaning messy tabular data.
  • Quarto — document your cleaning steps reproducibly.