Docs
A reference on messy data: the problems real files have, the words used to talk about them, and why getting it right matters.
- Field guide to messy data44 problems
The problems real files have: what each looks like, how it happens, what it breaks, and how to fix it.
- Glossary120 terms
The vocabulary of data cleaning and data preparation, defined in plain language.
- Why clean data matters1 essay, 6 case examples
Messy data rarely fails loudly. It produces answers that look fine and are wrong, and the cost shows up somewhere else, later.
171 of 171
Field guide: Dates and times
Field guide: Names and identity
Field guide: Duplicates
Field guide: Missing values
Field guide: Categories and codes
Field guide: Numbers and units
Field guide: Text and encoding
Field guide: Structure and layout
Field guide: Dates and times
- 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.
Field guide: Names and identity
- 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.
Field guide: Duplicates
- 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.
Field guide: Missing values
- 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.
Field guide: Categories and codes
- 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.
Field guide: Numbers and units
- 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.
Field guide: Text and encoding
- 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.
Field guide: Structure and layout
- 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.
Glossary
- AccuracyHow closely a recorded value matches the real thing it describes. A birth date that is a well-formed date and still not the person's birth date is inaccurate.
- AnomalyAnything in a dataset that does not fit the expected pattern: an order dated before the customer signed up, or a day with far more rows than usual.
- Audit trailA record of what was changed in a dataset, when, and why, kept so that someone else can check the work later.
- BatchA set of records processed together as one unit, such as a day of orders loaded overnight or one uploaded file.
- BlockingIn record matching, sorting records into groups that share a cheap key, such as the same postal code, so that only records inside one group are compared with each other.
- BooleanA data type with two possible values, true and false. A column such as subscribed or is_active is Boolean in meaning, whatever is typed into it.
- Business ruleA condition that data must meet because of how the organization works, not because of its format: a ship date cannot be earlier than its order date.
- Byte order markAn invisible character that some programs write at the very start of a text file to signal its encoding. It is often shortened to BOM.
- Canonical valueThe one agreed spelling for a thing that can be written several ways, such as VA for Virginia, virginia, and Va.
- CardinalityThe number of distinct values in a column. A country column has low cardinality; an email column has high cardinality.
- Categorical dataValues that place each row in one of a limited set of groups, such as a status of open, pending, or closed. The values are labels, not quantities.
- ChecksumA short value calculated from the contents of a file or record. If the contents change at all, the checksum almost certainly changes too.
- ColumnOne vertical slice of a table, holding the same kind of fact for every row: every customer's city, every order's total.
- CompletenessHow much of the data that should be present is present. It applies to values, such as a blank email, and to whole records, such as a month of orders that never arrived.
- Composite keyA key made of two or more columns that identify a row only when taken together, such as order number plus line number.
- Confidence scoreA number attached to an automated decision, such as a proposed match or correction, saying how sure the method is that the decision is right.
- ConsistencyWhether data agrees with itself: the same fact written the same way everywhere, and related values that do not contradict each other.
- ConstraintA rule that a database or file format enforces on a column, such as not blank, unique, or within a range. Data that breaks the rule is rejected.
- Controlled vocabularyA fixed, agreed list of the values a field is allowed to hold, such as the two-letter codes for US states.
- CrosswalkA table that pairs each code in one system with its equivalent in another, such as an old product code beside the new one that replaced it.
- CSVComma-separated values: a plain text file format in which each line is a row and commas separate the values in it.
- Currency codeA three-letter code that names a currency without ambiguity, such as USD, EUR, or JPY. The codes are defined by the ISO 4217 standard.
- Data cleaningFinding and correcting values in a dataset that are wrong, inconsistent, duplicated, or missing, so the data can be relied on.
- Data dictionaryA document that describes each column in a dataset: its name, meaning, data type, allowed values, and units.
- Data governanceThe policies, roles, and processes an organization uses to decide who owns its data, who may change it, and what standard it must meet.
- Data integrityThe assurance that data stays correct and intact as it is stored, moved, and changed, with nothing lost, altered, or left pointing at records that no longer exist.
- Data lineageThe path data has taken from its original source to where it is now, including each system it passed through and each transformation applied on the way.
- Data preparationEverything done to raw data to make it ready for a particular use: cleaning, reshaping, combining, and selecting.
- Data profilingExamining a dataset to describe what is actually in it: types, ranges, distinct values, blanks, and patterns, column by column.
- Data qualityHow well a dataset serves the purpose it is used for. Usually judged by accuracy, completeness, consistency, validity, uniqueness, and timeliness.
- Data stewardThe person responsible for the quality and correct use of a particular set of data, such as the customer list or the product catalog.
- Data typeThe kind of value a column holds, such as text, whole number, decimal, date, or true and false. The type decides what operations make sense.
- Data wranglingThe hands-on work of getting raw data into a usable form: cleaning it, reshaping it, and combining it with other data.
- Decimal separatorThe character that divides the whole part of a number from the fraction. It is a period in the United States, as in 3.14, and a comma in much of Europe, as in 3,14.
- DeduplicationFinding records that describe the same real thing and reducing them to one.
- Default valueThe value a system fills in when the person entering data supplies none, such as a country field that starts on the first item in its list.
- DelimiterThe character that separates values: the comma between fields in a CSV file, or the semicolon between items in a list packed into one cell.
- DenormalizationDeliberately storing the same fact in more than one place, usually by combining related tables into one wide table, so the data is easier or faster to read.
- DimensionIn data quality, one of the aspects on which data is judged, such as accuracy or completeness. In analysis, a descriptive attribute used to group numbers, such as region or product.
- DuplicateA record that describes the same real thing as another record in the same dataset. An exact duplicate matches in every field; a near duplicate differs in spelling, format, or detail.
- ELTExtract, load, transform: copying data from its source into a destination system as it is, and cleaning and reshaping it there afterward.
- Empty stringA text value with no characters in it. It is a value that exists and happens to be empty, which is different from null, where no value exists at all.
- EncodingThe rule that maps the bytes in a file to characters. Reading a file with the wrong encoding turns accented letters and symbols into nonsense.
- Entity resolutionDeciding which records refer to the same real-world person, company, or thing, when no shared identifier says so.
- ETLExtract, transform, load: taking data out of a source system, cleaning and reshaping it, and then writing the result into a destination.
- False negativeA real case that a check or matching step failed to flag: two records for the same customer left unmatched, or a wrong value that passed validation.
- False positiveA case that a check or matching step flagged wrongly: two different people treated as one, or a correct value reported as an error.
- FieldA single named piece of information in a record, such as the email address of one customer. The word is also used loosely to mean the whole column.
- Flat fileA plain file that holds one table, usually one record per line, with no links to other tables. A CSV export is the most familiar example.
- Floating pointThe usual way computers store numbers with a fractional part, using a fixed number of binary digits. Most decimal fractions cannot be stored exactly, so 0.1 plus 0.2 comes out as 0.30000000000000004.
- Foreign keyA column whose values refer to the primary key of another table, such as the customer ID stored on each order.
- Free textA field in which a person can type anything, such as a notes or comments box, with no fixed list of choices and no required format.
- Fuzzy matchingComparing two pieces of text for similarity instead of exact equality, so that Jon Smith and John Smith count as close.
- Golden recordThe single best version of a customer, product, or other entity, assembled from all the records that describe it.
- GranularityThe level of detail that one row represents. A table with one row per order line is finer grained than a table with one row per order.
- HeaderThe row at the top of a table that holds the column names. In data files it is normally the first line.
- Human reviewHaving a person examine a proposed change, and accept, correct, or reject it, before it is applied to the data.
- IdentifierA value whose job is to name one particular thing, such as an account number, a part number, or a postal code. It is a label, even when it is made entirely of digits.
- ImputationFilling in a missing value with an estimate, such as the column average or a value predicted from other columns.
- IntegerA whole number with no fractional part, such as 42 or -7. It is the natural type for counts.
- ISO 8601The international standard for writing dates and times, year first: 2025-03-04 for the fourth of March 2025.
- JoinCombining two tables by matching rows that share a value, such as attaching customer names to orders through a customer ID.
- JSONJavaScript Object Notation: a text format that stores data as named values that can be nested inside one another, such as a customer containing a list of orders.
- Leading zerosZeros at the start of a value that are part of the value, as in the postal code 02134 or the account number 000417.
- LocaleThe set of regional conventions software uses to read and write values: the order of day and month, the decimal and thousands separators, the currency symbol.
- Long formatA table layout with one row per observation, in which a repeated measurement gets a row each time: one row for each store in each month.
- Lookup tableA table that translates codes into their meanings or one set of values into another, such as state abbreviations to state names.
- MappingA stated correspondence between a source and a target. In data preparation it means either which source column feeds which target column, or which source value becomes which standard value, such as Calif. to CA.
- Master dataThe core lists an organization relies on across its systems, such as customers, products, suppliers, and locations.
- Match thresholdThe similarity score at or above which two records are treated as the same thing. Pairs scoring below it are left as separate records.
- MergeCombining two or more records judged to describe the same thing into a single record. The word is also used for joining two tables, and for merged cells in a spreadsheet; in data cleaning the first meaning is the usual one.
- MetadataData that describes other data: column names and types, when a file was created, where it came from, and how many rows it holds.
- Missing valueA cell where a value should exist and does not. The reason matters: not collected, not applicable, and lost are different situations.
- MojibakeThe garbled text produced when a file is read with a different encoding from the one it was written in, so that José appears as José.
- Natural keyA key made from a value that already means something outside the database, such as an email address, a tax number, or a vehicle identification number.
- NormalizationConverting values to a common form or scale. The word is used for text (one case, one spacing), for numbers (a common range), and for database design (removing repetition).
- NullThe explicit marker a database or file uses for no value. It is different from zero, from an empty string, and from the text NULL typed into a cell.
- OutlierA value far from the rest of its column. It may be an error, or it may be the most interesting row in the file.
- ParsingReading text and working out its structure: turning the characters 03/04/2025 into a date, or one line of a file into separate fields.
- PivotReshaping a table by turning the values of one column into new column headers, for example one row per store per month into one row per store with a column for each month.
- PlaceholderText entered to fill a field that had no real answer, such as N/A, TBD, unknown, a single dash, or test@test.com.
- PrecisionFor numbers, how finely a value is recorded: 3.14159 carries more precision than 3.1. In matching and error detection the word has a second meaning: of everything that was flagged, how much was flagged correctly.
- Primary keyA column, or set of columns, whose value is unique for every row and identifies it: a customer number, an order ID.
- ProvenanceWhere a value came from and what has been done to it since: the source file, the steps applied, and who or what applied them.
- Raw dataData exactly as it was received or collected, before anything has been corrected, reformatted, or removed.
- ReconciliationComparing two sets of data that should agree, such as a source system and its export, and accounting for every difference between them.
- RecordOne row of a table: the set of values that together describe a single thing, such as one customer or one transaction.
- Record linkageFinding the records in two or more datasets that refer to the same person, company, or thing, when the datasets share no common identifier.
- Reference dataThe agreed lists of permitted values that other data is classified by, such as country codes, currency codes, and order statuses.
- Regular expressionA compact notation for describing a pattern of characters, used to find, check, or extract text: five digits in a row, or anything that looks like an email address.
- RoundingReplacing a number with a nearby one that has fewer digits, such as 2.675 with 2.68.
- SamplingExamining a subset of the rows in place of all of them, either to get a quick picture of a dataset or to check the result of a change by hand.
- SchemaThe declared structure of a dataset: its columns, their names and types, and the rules they must satisfy.
- Schema driftUnannounced change in the structure of a recurring data feed: a column renamed, added, dropped, reordered, or changed in type between one delivery and the next.
- Sentinel valueAn ordinary-looking value used as a code for something else, most often 999, -1, or 1900-01-01 standing in for unknown.
- Serial dateA date stored as a count of days from a fixed starting date, which is how a spreadsheet keeps dates internally. The number 45000 is a day in March 2023 under the most common starting date.
- Source of truthThe one system or dataset agreed to hold the authoritative version of a piece of information. When copies disagree, it is the one that is taken to be right.
- SpreadsheetA program, or a file made with one, that arranges values in a grid of cells where any cell can hold any kind of value or a formula.
- StandardizationRewriting values so that everything of one kind follows one format: every date year first, every phone number in the same pattern.
- StringA value stored as a sequence of characters, which is to say text. The characters 2025 held as a string are four symbols, not a number.
- Surrogate keyA key generated by a system only to identify a row, with no meaning outside it: a running number, or a random unique ID.
- SurvivorshipThe rules that decide which value is kept when duplicate records are merged and they disagree, such as keeping the most recently updated address.
- Tabular dataData arranged in rows and columns, where each row is one item and each column holds one attribute of every item.
- Thousands separatorA character placed between groups of digits to make large numbers easier to read: the comma in 1,250,000. Other conventions use a period, a space, or an apostrophe.
- Tidy dataA table layout in which each row is one observation, each column is one variable, and each cell holds one value.
- Time zoneA region that keeps the same clock time, described by its offset from Coordinated Universal Time (UTC). A time of day identifies a single moment only once its zone is known.
- TimelinessWhether data is recent enough for the use it is being put to. An address that was correct three years ago may not be correct today.
- TimestampA value that records a date and a time of day together, marking one moment: 2025-03-04T14:30:00Z.
- TransformationAny step that turns data from one form into another: converting a type, splitting a column, combining tables, or calculating a new field.
- TrimRemoving whitespace from the start and end of a text value, so that a name followed by a stray space becomes equal to the same name without one.
- Type coercionConverting a value from one data type to another, such as the text 1,200 to the number 1200. It may be requested, or done automatically by software.
- UnicodeThe standard that assigns a number to the characters of nearly all the world's writing systems, along with symbols and emoji, so that text in any of them can be represented in one scheme.
- UniquenessWhether each real thing appears only once in a dataset: one row per customer, and no order number used twice.
- Unit of measureThe scale a number is expressed in, such as kilograms, pounds, meters, or hours. A number without its unit is not yet a measurement.
- UpsertA single operation that updates a row if its key already exists and inserts a new row if it does not. The name joins update and insert.
- UTF-8The most widely used encoding for Unicode text. It stores each character in one to four bytes, with plain English letters and digits taking one byte each.
- ValidationChecking values against rules, such as a date being in the past or a state code being on the allowed list, and reporting what fails.
- ValidityWhether a value follows the rules for its column: the right type, the right format, and within the allowed range or list. A date of February 30 is invalid.
- WhitespaceCharacters that take up space without showing a mark: spaces, tabs, line breaks, and less obvious ones such as the non-breaking space.
- Wide formatA table layout in which repeated measurements of the same thing are spread across columns: one row per store, with a separate sales column for each month.
Why clean data matters
- Why clean data mattersMessy data rarely fails loudly. It produces answers that look fine and are wrong, and the cost shows up somewhere else, later.
- Non-profitsA donor list that counted the same people twice
- Academic researchA survey whose result depended on how blanks were coded
- Marketing analyticsFive clients, five definitions of a campaign
- UtilitiesEquipment records that could not be tied to what was spent on them
- E-commerceA catalog where the same product was listed four times
- Finance teamsA vendor list that paid the same invoice twice