Field guide to messy data
The problems real files have: what each looks like, how it happens, what it breaks, and how to fix it.
44 of 44
Dates and times
- Mixed date formatsOne column holds dates written in several different styles.
- Ambiguous day and month orderA date such as 03/04/2025 could mean March 4 or April 3, and nothing in the cell says which.
- Two-digit yearsA year written as two digits does not say which century it belongs to.
- Missing or mixed time zonesTimestamps in one column were recorded in different time zones, or carry no zone at all.
- Dates stored as serial numbersA date appears as a plain number, such as 45292, because it was exported as a count of days or seconds instead of as a calendar date.
- Impossible datesA value is shaped like a date but names a day that does not exist or could not have happened.
- Date and time split or mergedSome rows keep the date and the time in separate columns and others pack both into one.
Names and identity
- Name variantsThe same person or organization appears under several spellings, orders, and abbreviations.
- Name order, titles, and suffixesA single name field mixes given names, family names, titles, and suffixes in no fixed order.
- Addresses written many waysThe same place is written with different abbreviations, word order, capitalization, and levels of detail.
- Phone number formatsThe same phone number is written with different punctuation, spacing, prefixes, and extensions.
- Email typos and letter caseEmail addresses differ by capitalization, or contain small typing errors that make them undeliverable.
- Identifiers that lost their leading zerosA code that starts with zero, such as a postal code or account number, was read as a number and the zeros were dropped.
Duplicates
- Near-duplicate recordsTwo rows describe the same real thing but differ enough that an exact comparison misses them.
- Exact duplicate rowsThe same row appears more than once, identical in every column.
- Duplicate keys with conflicting valuesTwo rows share an identifier that should be unique, but disagree on other fields.
- The same entity in two systemsOne person or organization exists in two systems under different identifiers, with nothing linking the records.
Missing values
- Disguised missing valuesA cell is not empty, but what it holds means there is no value: N/A, a dash, 999, or the word none.
- True blanksA cell is really empty, and the file does not say whether the value is unknown, not applicable, or was never asked for.
- Partially filled rowsA row has some of its fields and lacks others, so it is usable for some purposes and not for others.
- Defaults mistaken for dataA value that looks real was filled in automatically by a form or a system, and nobody actually chose it.
Categories and codes
- Inconsistent category labelsA column meant to hold a few fixed values holds many spellings of them.
- Yes and no in many formsA column that should hold only yes or no holds Y, yes, TRUE, 1, and several other ways of saying the same two things.
- Codes without a lookupA column holds short codes such as 3, A, or XR, and the table that says what each code means is not in the file.
- Categories that changed meaningA label stays the same across the file, but what it covers was redefined partway through.
- Free text in a coded fieldA column meant to hold one of a few fixed values also holds comments, explanations, and whole sentences.
Numbers and units
- Numbers stored as textA column of quantities contains symbols, separators, or words that stop software reading it as numbers.
- Mixed unitsValues in one column are measured in different units, sometimes with the unit written in the cell and sometimes not.
- Rounding and precision lossDigits were dropped when a value was displayed, exported, or converted, and the file now holds the shortened number.
- Mixed currenciesAmounts in one column are in different currencies, marked by a symbol, a code, or nothing at all.
- Percentages in two scalesA rate is written as a fraction of one in some rows and as a number out of a hundred in others, so 0.15 and 15 mean the same thing.
- Negative numbers in several notationsNegative amounts are written with a leading minus, a trailing minus, parentheses, or a credit marker, all in one column.
- Outliers from entry errorsA value sits far outside the rest because of a slipped key, an extra zero, or a misplaced decimal point.
Text and encoding
- Stray whitespace and invisible charactersTwo values look identical on screen but differ by a trailing space, a non-breaking space, or a hidden character.
- Broken accented charactersAccented letters appear as pairs of odd symbols or as question marks, because the file was read with the wrong encoding.
- Inconsistent capitalizationThe same value appears in capitals, in lowercase, and in mixed case, and software treats each as different.
- Smart quotes and look-alike charactersA character in the cell looks like an ordinary quote, hyphen, or letter but is a different character underneath.
- Line breaks inside cellsA cell contains a line break, so one record can spill across several lines of the file.
Structure and layout
- Multiple values in one cellA single cell holds a list, or two different facts, joined by a comma, slash, or line break.
- Merged and multi-row headersThe column names are spread over two or more rows, often with merged cells, so no single row names every column.
- Totals and notes rows inside the dataRows that are not records, such as subtotals, grand totals, and footnotes, sit among the rows that are.
- Wide tables that should be longValues of one variable, such as months or years, are spread across the column headers instead of being stored in a column of their own.
- Inconsistent column names across filesFiles that should share one layout name the same column differently, or use the same name for different things.
- One table split across sheetsRows that belong in one table are spread over several sheets or files, one per month, region, or person.