A travel survey file arrives with six rows of trip durations. Some durations are entered in seconds and others in minutes, one trip appears twice, one value is blank and one trip took far longer than the others. Averaging the column as it stands would mix units, count one trip twice and quietly decide what the blank means. This lesson shows how to document where data came from and what they mean, how to deal with duplicates, outliers and missing values without hiding the decisions, and how dropping incomplete rows can change a conclusion.
Key terms
Data provenance and data dictionary
Definition Data provenance is the documented history of a data set. It records who or what produced the data, how the units were selected, when and how the values were collected and how the file reached the analyst. The raw data are the entries exactly as first received, kept unchanged in a protected copy. A data dictionary describes each variable with its name, meaning, units, allowed values or range, recording resolution and the code used for a missing value.
In plain words Provenance tells you where the rows came from and which units they can speak for. The dictionary tells you what each column means. A column headed “time” is not enough if one respondent reports time spent traveling and another includes the wait before departure.
Example A constructed survey file has six rows, one per recorded response, and the intended unit is one completed trip.
| Row | Trip ID | Raw duration | Raw unit |
|---|---|---|---|
| 1 | A | 12 | minutes |
| 2 | B | 18 | minutes |
| 3 | C | blank | minutes |
| 4 | D | 900 | seconds |
| 5 | D | 900 | seconds |
| 6 | E | 60 | minutes |
The dictionary says that the analysis column holds the trip duration in minutes, from leaving the starting point to arriving, and that a blank means the duration was not reported. The raw unit of rows 4 and 5 is recorded as seconds, so
is a justified conversion based on the documented unit, not a guess based on the size of the number. A unit mix-up that goes unnoticed produces measurement error, a recorded value that differs from the quantity it was meant to measure. A badly set timer or an ambiguous question can produce it too, so converting the unit does not show that every respondent timed the same interval accurately.
Duplicate records
Definition Duplicate records are two or more records of the same unit on the same occasion, for example one survey response imported twice. Deciding that records are duplicates needs evidence about identity, such as a shared identifier together with the collection or import history. Equal values alone are not that evidence. The analysis copy keeps one record of the unit, and the exclusion of the others is documented. When the copies disagree, the choice of which values to keep needs its own documented reason.
In plain words A duplicate is the same trip counted twice, not two trips that happen to look alike. Keeping both copies gives that trip double weight, while deleting a genuine second trip with matching values throws away a real observation. One traveler making two trips produces two legitimate rows, not a duplicate.
Example Rows 4 and 5 share the trip ID D, and the import log shows that the same response was imported twice. Row 5 is therefore excluded from the analysis copy and the exclusion is logged. Matching durations would not have been enough, since two different trips can both take 15 minutes. Keeping both copies would turn the observed durations into 12, 18, 15, 15 and 60 minutes, whose mean is minutes, instead of the 26.25 minutes found below with one copy.
Outlier
Definition An outlier is an observation that lies unusually far from the bulk of the data, or from the pattern the other observations follow. Whether a value counts as unusual depends on the context and on the comparison being made, and later lessons such as percentiles, quartiles and box plots give numerical rules for flagging values. Being an outlier does not by itself show that a value is an error. The value may be a recording mistake, a unit from outside the intended population or a genuine but unusual observation.
In plain words Unusual and wrong are different things. Check an outlier against its source. Correct it when an error is demonstrated, exclude it when the unit turns out to lie outside the intended population and keep it when it is confirmed. Removing valid values because they are inconvenient changes the group that a summary describes.
Example Trip E took 60 minutes, while the other observed trips took 12, 15 and 18 minutes. The source confirms a genuine long trip of the kind the survey covers, so E stays in the analysis copy. Dropping it would lower the mean of the observed durations from 26.25 to minutes, a summary of only three of the four trips with a known duration.
Missing value and missingness mechanism
Definition A missing value is an entry for which the value of a variable was not obtained or not recorded for a unit. It is not zero, and it is not any other number. Missingness describes which values are absent. The missingness mechanism is the process that decides which values go missing, described by how the chance that a value is missing relates to the data. Three standard cases are distinguished.
- Missing completely at random. The chance that a value is missing does not depend on any of the data, observed or unobserved.
- Missing at random. The chance may depend on other recorded variables, but once those are taken into account it does not depend on the missing value itself.
- Missing not at random. The chance depends on the unobserved value even after the recorded information is taken into account.
In plain words A blank cell looks the same whatever caused it. Why it is blank decides whether the observed values still speak for the whole group, and the table alone cannot fully settle that question.
Example After the unit conversion and the removal of the duplicate, five trips remain and four of them have observed durations, 12, 18, 15 and 60 minutes. Their mean is
Entering 0 for trip C and dividing by 5 gives 21 minutes, but that invents a trip that took no time at all. The blank for C could come from a random form error unrelated to anything in the survey, which is missing completely at random. It could come from travelers who use one mode of transport, recorded in another column, skipping the question more often, while within each mode skipping is unrelated to duration. That is missing at random. Or long trips could be left blank more often because they are harder to recall, which is missing not at random.
Complete-case analysis
Definition A complete-case analysis uses only the records that have observed values on every variable the particular analysis needs and drops the others. Its results describe the complete cases, and they answer a question about all intended units only under an assumption about the missingness mechanism. If values are missing completely at random, the complete cases are a random subset of the intended ones, so dropping the others does not by itself shift the results systematically, although fewer records leave more uncertainty. Under other mechanisms the complete cases can differ systematically from the intended group, a problem called selection after exclusion. Simple imputation, such as filling every blank with the mean of the observed values, rests on assumptions too. It leaves that mean unchanged, but every filled value sits exactly at the mean, so the average distance of the values from their mean shrinks whenever the observed values are not all equal, and later calculations treat a guess as a measurement.
In plain words Dropping incomplete rows is a decision about who is represented, and filling them in is a decision about what the missing answers would have been. Both need a reason, and a report should say how many values were missing and what was done about them. Other options include recovering the source, using a model whose assumptions are justified, and checking how the conclusion changes over a range of plausible values.
Example The complete-case mean of 26.25 minutes describes trips A, B, D and E. Whether it is a fair estimate of the mean of all five trips depends on why C is missing, and if long trips are more often left blank, it tends to understate that mean. Filling C with 26.25 minutes keeps the mean at 26.25 minutes. The distances of the four observed values from 26.25 add to 67.5 minutes, so the average distance from the mean drops from
although nothing new was learned about trip C. A range of plausible values is more honest. If, for illustration, C could have taken anywhere from 10 to 90 minutes, the mean of all five trips lies between and minutes.
Data cleaning log
Definition A data cleaning log is a dated record, kept with the raw data and the analysis copy, of every change made between the two and of every decision to leave a questioned entry as it is. Each entry names the record, the raw entry, the action taken and the reason, with the evidence for it. The log separates corrections verified against a source from assumptions made for the analysis. The raw data stay unchanged, so every value in the analysis copy can be traced back to its origin.
In plain words The log lets another analyst, or you at a later date, rebuild the analysis copy from the raw file and judge every decision, which makes the cleaning reproducible. It should hold no more personal detail than the work needs, since identifiers and combinations of harmless-looking fields can reveal who someone is.
Example A log for the travel file could look like this.
| Record | Raw entry | Action | Reason |
|---|---|---|---|
| Row 4, trip D | 900 seconds | Converted to 15 minutes | Raw unit recorded as seconds |
| Row 5, trip D | 900 seconds | Excluded | Import log shows the same response imported twice |
| Row 6, trip E | 60 minutes | Kept unchanged | Confirmed against the source |
| Row 3, trip C | blank | Left missing | Not reported, reason unknown |
Cleaning does not remove every uncertainty. It remains open whether the absence of C is related to its duration, whether the reported durations were timed accurately and which wider group of trips, if any, these five represent.
Check yourself
- Another survey records five trips, P to T, with durations of 20 minutes, 25 minutes, a blank, 1200 seconds and 35 minutes, and its dictionary says the analysis column is in minutes. Find the mean of the observed durations in minutes. A colleague enters 0 for the blank and reports a mean of 20 minutes. What is wrong with that?
Show the answer
The entry for trip S is minutes. The observed durations 20, 25, 20 and 35 minutes add to 100 minutes, so their mean is minutes. Entering 0 and dividing by 5 gives minutes, but a blank means the duration of trip R is unknown, not that the trip took no time. The 25 minutes describes the four trips with observed durations, and whether it is a good estimate for all five depends on why the value of R is missing.
- In the same survey, trips P and S both last 20 minutes, both went by bus and both took place on the same day, but they have different trip IDs and were submitted at different times. A cleaning script deletes S as a duplicate of P. Is that justified?
Show the answer
No, not on this evidence. Duplicate records are repeated records of the same unit on the same occasion. Here the IDs and the submission times differ, so P and S look like two different trips that happen to share a duration, a mode of transport and a day. Deleting S would most likely remove a real observation, and it would change the mean of the observed durations from 25 minutes to minutes. Only evidence about identity, such as the same ID together with an import log showing one submission entered twice, would justify treating the rows as duplicates.
- The original source is recovered and shows that trip R took 50 minutes. Write the log entries for cleaning this survey and find the mean of all five trips. What did the earlier 25 minutes describe, and which uncertainty remains after cleaning?
Show the answer
A short log could read as follows.
| Record | Raw entry | Action | Reason |
|---|---|---|---|
| Trip S | 1200 seconds | Converted to 20 minutes | Raw unit recorded as seconds |
| Trip R | blank | Set to 50 minutes | Value recovered from the original source |
| Trips P and S | 20 minutes and 1200 seconds | Both kept | Different IDs and submissions, not duplicates |
The five durations now add to minutes, so the mean of all five trips is minutes. The earlier 25 minutes was correct arithmetic for the four trips with observed durations. It understated the five-trip mean by 5 minutes because the missing trip happened to be the longest, which is the risk of treating the observed subset as if it answered the question about all five. Cleaning cannot settle whether the reported durations were timed accurately or which wider group of trips, if any, these five represent.