Tidy tables and missing values

Real data is messy. Before you calculate anything, check what is in the table. A few common problems:

• Mixed units. One column in millimetres and another in centimetres, or a mix of both in one column. In 1999 NASA lost the Mars Climate Orbiter largely because one team's software gave a quantity in imperial units and the other expected metric. A table cannot warn you; you have to read the units.
• Spelling and format. "Adelie", "adelie" and "Adelie " (with a trailing space) look like three groups to a computer. Dates written as 03/04/2024 mean different days in different countries.
• Impossible values. A body mass of 0 g, a flipper of 2 000 mm, a year of 2099. Sometimes a typo, sometimes a placeholder for "unknown".
• Duplicates. The same unit entered twice, which counts it double.

The most common problem of all is missing values, usually written NA ("not available"). The penguin table has them, and they are a good lesson:

• The table has 344 rows.
• Two rows (rows 4 and 272) have every measurement missing: the birds are listed, with species and island, but there are no bill, flipper or mass values.
• Nine more rows have the measurements but not the sex. In total, 11 rows lack the sex.
• So 342 birds have a body mass, and 333 have a complete record.

The penguin measurements are the Palmer penguins data set: 344 birds measured at Palmer Station, Antarctica, in 2007, 2008 and 2009 (Gorman, Williams and Fraser, 2014), shared in the public domain (CC0).

What should you do about missing values? The honest answer is "it depends on why they are missing". If a few values are missing for reasons unrelated to what you are studying, such as a scale that ran out of battery, dropping those rows changes little. But if the missing values are systematic, dropping them changes the answer. Imagine (a made-up case) that the heaviest birds were too hard to weigh. If you delete the rows with missing weights, your average will be lower than the truth. The rows you removed were not random, so the rows you kept are not typical.

A few habits protect you:

• Look first. Count rows, check the range of each column, list the categories. Surprising values are clues.
• Never silently delete. If you drop rows, say which and why. "We analysed the 333 penguins with complete records" is honest. An unexplained smaller number is not.
• Keep the original. Work on a copy of the data so you can always go back.
• Read the data dictionary. A data dictionary (codebook) says what each column means, its units, how missing values are coded and who collected it. For the penguins it tells you that body mass is in grams and the bill measurements are in millimetres. Without it, the numbers are just numbers.
• Remember measurement error. Every measurement has some error: a ruler read slightly differently, a bird that would not stay still. Small errors are normal; they are part of why data varies.