9  Joining and Cleaning

Data arrives in pieces: yields in one file, prices in another, weather in a third. It also arrives with problems that nobody mentions. This chapter covers putting the pieces together and finding out what is wrong with them.

Learning Objectives

By the end of this chapter you should be able to:

1. Join two tables on a shared key with left_join() and friends.
2. Say what happens to rows that do not match, and check for them.
3. Explain why a join can return more rows than it started with.
4. Find duplicates, impossible values, inconsistent categories and mixed units in a new file.

9.1 Joining Two Tables

XLOOKUP (Section 2.2) pulled a price across from a second table in Excel. The R equivalent is a join:

library(tidyverse)

yields <- read_csv("data/field_yields.csv")
prices <- read_csv("data/crop_prices.csv")

yields |>
  left_join(prices, by = "crop")

The by argument names the key, the column both tables share, which decides which row matches which. Everything about joins comes back to the key.

The four joins you need

They differ in which rows survive.

• left_join(a, b) keeps every row of a and fills in NA where b has no match. This is usually the one you want: your data stays intact and the extra columns come along.
• inner_join(a, b) keeps only rows that matched in both, and drops the rest without saying so.
• full_join(a, b) keeps everything from both, with NA wherever either side is missing.
• anti_join(a, b) keeps rows of a with no match in b. It returns no columns from b at all, which sounds useless until you need to find out what failed to match.

Check what did not match

left_join does not complain when nothing matches. It hands you a column of NA and lets you carry on, so a join can fail completely without producing an error.

anti_join is how you look:

yields |>
  anti_join(prices, by = "crop") |>
  distinct(crop)

Run that on real Saskatchewan data and the problem shows up straight away. The variety trial data calls it "Canola/Rapeseed" and the price list says "Canola". The trial says "Soybean", the prices say "Soybeans". Neither file is wrong. They were written by different people, and a join matches on exact text, so every canola row comes back with a missing price.

Reconciling the categories is Section 9.2 below. Checking before you assume the join worked is the habit worth building.

The row-count trap

A join can return more rows than it started with.

This happens when the key is not unique in the second table. If prices has one row per crop, each yield row matches one price and the row count does not change. If prices has one row per crop per year and you join on crop alone, every yield row matches several price rows and R returns all the combinations. Sixty fields become two hundred and forty, and every summary computed afterwards is wrong in a way that still looks plausible.

Check the count:

nrow(yields)                                    # before
joined <- yields |> left_join(prices, by = "crop")
nrow(joined)                                    # after -- should be the same

If it grew, the key is incomplete. Join on everything that identifies a row:

yields |> left_join(prices, by = c("crop", "year"))

This is the composite key from Section 2.2, where RM and year had to be glued together with & to identify a row. R lets you name several key columns instead of building one.

9.2 Cleaning a New File

Assume a new file is broken until you have checked. Data gets entered by tired people, exported by software with opinions, and combined from sources that disagree with each other.

The examples below use grain_deliveries_messy.csv, a small file of grain deliveries carrying every defect in this section.

deliveries <- read_csv("data/grain_deliveries_messy.csv")
glimpse(deliveries)

Duplicates

There are two kinds, and the second is the harder one.

An exact duplicate is the same row entered twice:

deliveries |> filter(duplicated(deliveries))     # show them
deliveries |> distinct()                          # drop them

A conflicting duplicate is the same identifier with different values: ticket T0030 appearing twice with two different weights. distinct() leaves these alone, because the rows are not identical. Count to find them:

deliveries |>
  count(ticket_id) |>
  filter(n > 1)

No code fixes this one. Somebody entered the delivery twice, or corrected it and left both rows, and you have to find out which is right. If you cannot, say so in the write-up and explain what you did.

Impossible values

Every variable has values it cannot take. A weight cannot be negative, a moisture percentage cannot be 250, and a delivery of exactly zero tonnes is possible but odd.

summary() catches most of this in one line, because impossible values turn up in the minimum and maximum:

summary(deliveries)

Then look at the offending rows:

deliveries |> filter(weight_tonnes <= 0 | moisture_pct > 100)

A negative weight is probably a sign error or a reversed transaction. A moisture of 250 is probably 25.0 with a lost decimal point. Both of those are guesses, so record them as guesses. Dropping the rows without saying anything is the thing to avoid.

Inconsistent categories

The same thing spelled several ways is the most common defect I run into, and it is easy to miss because every individual value looks fine. Count the categories:

deliveries |> count(crop)

  crop            n
  "CANOLA"        2
  "Canola"        6
  "canola"        4
  "Spring Wheat"  2
  "Spring wheat"  8
  "spring wheat"  5
  ...

Three spellings of canola and three of spring wheat, which group_by(crop) reads as six different crops.

Fix the mechanical cases first:

deliveries <- deliveries |>
  mutate(crop = str_trim(crop),        # strip stray spaces
         crop = str_to_title(crop))    # "CANOLA" and "canola" -> "Canola"

str_to_title handles capitalisation. read_csv already strips surrounding spaces, so str_trim is belt-and-braces here, but other readers do not, and a space you cannot see is a match you cannot explain.

Genuine synonyms like "Canola" and "Canola/Rapeseed" are a judgement call, so put them in an explicit recode where a reader can see the decision:

deliveries <- deliveries |>
  mutate(crop = case_when(
    crop == "Canola/Rapeseed" ~ "Canola",
    crop == "Soybean"         ~ "Soybeans",
    TRUE                      ~ crop
  ))

Mixed units

A right value in the wrong unit is harder to catch than a wrong value, because nothing looks broken. In the deliveries file most moisture readings are percentages around 9.5, but a few were entered as decimals: 0.094 rather than 9.4. No error, no warning, and the mean comes out too low.

Knowing what range the variable should occupy is what catches it:

deliveries |> filter(moisture_pct < 1)

Grain moisture is normally 8 to 16 percent, so anything under 1 is in the wrong unit. Convert rather than drop:

deliveries <- deliveries |>
  mutate(moisture_pct = if_else(moisture_pct < 1, moisture_pct * 100, moisture_pct))

A file that records its own units, like the units column in Section 5.4, lets you convert with case_when instead of inferring from magnitude.

Missing values in disguise

Missing data (Section 5.5) arrives in real files wearing several costumes: an empty cell, the text "NA", "N/A", "-", ".", or -999. R recognises the blank and NA. The rest come in as ordinary text and turn their column into <chr>.

Tell read_csv what counts as missing:

deliveries <- read_csv("data/grain_deliveries_messy.csv",
                       na = c("", "NA", "N/A", "-", "."))

Then count what you have before deciding anything:

deliveries |> summarise(across(everything(), ~ sum(is.na(.))))

A numeric code like -999 is the dangerous one. It will not turn the column to text; it will just be included in the mean as a number, which is another reason to run summary() on arrival and look at the minimum.

A checklist

Run through this on every new file:

1. glimpse() – are the column types what you expect?
2. summary() – are the minimums and maximums possible?
3. count() each categorical column – are the categories consistent?
4. Count rows, and check for duplicated identifiers.
5. Count NAs per column, and ask whether the missingness is random.
6. Check the range of each numeric column against what the unit should be.

9.3 Test Bank Sample

1. (Joins.) What is the difference between left_join and inner_join, and which is safer when you are not sure the keys match?
2. (Diagnosis.) After a join, a column you expected to be full is entirely NA. What is the most likely cause, and which function finds it?
3. (Row counts.) You join 60 yield rows to a price table and get 240 rows. Explain what happened and how to fix it.
4. (Duplicates.) Distinguish an exact duplicate from a conflicting duplicate. Why does distinct() only solve one of them?
5. (Categories.) Give three ways the same crop name can appear in a file, and say what group_by(crop) would do with them.
6. (Units.) Why is a value in the wrong unit harder to catch than a value that is simply wrong?

9.4 Practice Exercises

1. Join the variety trial yields to crop_prices.csv. Use anti_join to find the crops that did not match, and list them.
2. Reconcile the crop names so the join succeeds, then compute revenue per acre by crop.
3. Read grain_deliveries_messy.csv and find every duplicated ticket_id. How many are exact duplicates and how many conflict?
4. Find the impossible values in the file. For each, say what you think the intended value was and why.
5. Count the spellings of each crop. Clean them with str_trim and str_to_title, then check the count again.
6. Three moisture readings are in the wrong unit. Find them, convert them, and say how you knew.