Book 2 · Chapter 6: Taming Messy Data
This page hands you a data-cleanup prompt pack, a messy practice spreadsheet, and a validation checklist.
This page assumes you've read the chapter. It hands you the tools, not the lessons.
Download · CSVDownload CSV
Practice CSV: the messy contact list
Clean it along with the chapter. The cleanup prompts in the Cleanup Instruction Pack are aimed at exactly this file.
messy-contacts.csv · ~60 rows · columns: first_name, last_name, full_name, email, phone, date_added, notes
Download · PDF
Data Validation Checklist
- 1Spot-check your results. Before you use cleaned data, scan it. Does it look right? Do the numbers make sense? Pick a few records at random and compare them to the original.
- 2Check the edges. Verify the highest and lowest values. Verify the most complex records (the ones with the most variation). These are where errors hide.
- 3Validate totals. If you're working with financial data, sum it up. Compare the total before and after. If the total changed unexpectedly, something's wrong.
- 4Check for red flags. Missing data where there should be data. Impossible values (negative quantities, dates in the future). Weird distributions (all values the same when they shouldn't be).
- 5Spot-check format consistency. Are all the dates in the format you asked for? Are all the phone numbers in the same pattern? Are all the amounts using the same currency symbol?
From the Data Validation Checklist section.
Cleanup Instruction Pack
- The cleanup-prompt skeleton (start here)
What I'm starting with: [format, source, and state of the data] The problems: [be specific: different date formats, inconsistent names, duplicate records, mixed currency, data from two systems that don't match] What the output should look like: [the format and structure you need, with the exact column names and formats]
- Spreadsheet Cleanup (General)
I have a spreadsheet with [NUMBER] rows of [DATA TYPE]. Problems: [LIST SPECIFIC ISSUES]. Clean it up: - [STANDARD 1] - [STANDARD 2] - [STANDARD 3] Return as a CSV with columns: [LIST COLUMN NAMES]
- Duplicate Detection and Removal
I have [SOURCE DATA]. Identify duplicates using [KEY FIELD(S)] as the unique identifier. For each duplicate set, keep the most complete record (most fields filled in). Return a clean list with duplicates removed.
- Format Conversion
I have [SOURCE FORMAT] data. Convert it to [TARGET FORMAT]. Structure: - [COLUMN/FIELD 1]: [desired format] - [COLUMN/FIELD 2]: [desired format] Return as: [target format]
- Text Pattern Extraction
I have [NUMBER] pieces of text (emails, feedback, notes, etc.). Extract: - [FIELD 1] - [FIELD 2] - [FIELD 3] Return as a spreadsheet with one record per [text unit].
- Data Merge (Multiple Sources)
I have customer data from [NUMBER] sources: 1. [Source 1]: [fields] 2. [Source 2]: [fields] 3. [Source 3]: [fields] Match records across sources (customers may have different name formats). Merge them into one spreadsheet with: - [FIELD 1] - [FIELD 2] - [FIELD 3] Flag ambiguous matches for manual review.
- Standardization (Names, Categories, etc.)
I have a list of [THINGS: company names, job titles, categories, etc.]. They're inconsistent. Standardize them against these accepted values: [LIST] For anything that doesn't match, try to map it to the closest match. Flag anything ambiguous.