8  Getting Data

Agricultural data usually arrives as an Excel workbook rather than a CSV, often with a title and a couple of blank rows above the table. This chapter covers reading those files, and getting data out of Statistics Canada.

Learning Objectives

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

  1. Read an Excel workbook into R, including a specific sheet.
  2. Handle a file whose header does not start on the first row.
  3. Check what R guessed about your column types, and correct it.
  4. Download a table from Statistics Canada and get it into a usable shape.

8.1 Excel Files

The tidyverse package for reading .xlsx files is readxl. It installs with the tidyverse but is not loaded by library(tidyverse), so load it separately:

library(tidyverse)
library(readxl)

deliveries <- read_excel("data/deliveries.xlsx")

A workbook can hold several sheets, and read_excel takes the first one unless you say otherwise. Check what is in the file before assuming:

excel_sheets("data/deliveries.xlsx")
[1] "Notes"  "2024"  "2025"  "Summary"

The first sheet here is a page of notes, so reading the file without checking would hand you that instead of the data. Ask for the sheet you want, by name or number:

deliveries <- read_excel("data/deliveries.xlsx", sheet = "2025")

8.2 When the Header Is Not on Row 1

Spreadsheets built by people usually have something above the table: a title, a note about units, a blank row or two. R takes the first row as column names regardless, which produces something like this:

# A tibble: 62 x 6
  `Saskatchewan grain deliveries` ...2   ...3   ...4
  <chr>                           <chr>  <chr>  <chr>
1 NA                              NA     NA     NA
2 Ticket                          Date   Crop   Weight

Column names like ...2 and a first row of NA mean the real header is further down. Count the rows above it and skip them:

deliveries <- read_excel("data/deliveries.xlsx", sheet = "2025", skip = 3)

read_csv takes the same argument. Print the object after reading it either way; the shape of what comes back is the quickest check that you read the right thing.

8.3 Check What R Guessed

read_csv and read_excel guess each column’s type from the first few hundred values. The guess is usually right, and wrong in one particular way that matters.

A numeric column that contains a single text value comes in as text. A weight typed as "38.2 t" with the unit in the cell will do it, as will "N/A" or a footnote marker. Once the column is text, mean() fails, and arrange() sorts alphabetically, which puts 100 between 10 and 11.

Look at what you got:

glimpse(deliveries)

Every column is labelled: <dbl> for numbers, <chr> for text, <date> for dates. If something you expect to be numeric says <chr>, find the value that caused it:

deliveries |>
  filter(is.na(as.numeric(weight_tonnes))) |>
  select(ticket_id, weight_tonnes)

That returns the rows R could not read as a number. Fixing them is Section 9.2; the point here is to notice on arrival rather than three steps later, when a summary comes back wrong.

You can also state the types instead of letting R guess:

deliveries <- read_csv("data/deliveries.csv",
                       col_types = cols(
                         delivery_date = col_date("%Y-%m-%d"),
                         weight_tonnes = col_double()
                       ))

For a file you read repeatedly this is worth doing. It documents what you expect, and it fails loudly when the file changes rather than quietly doing something else.

8.4 Statistics Canada

Most Canadian agricultural data starts at Statistics Canada. The tables are free, and the interface takes some getting used to: find the table you want, use Add/Remove data to choose years, geographies and commodities, then Download as CSV.

Two things are worth knowing before you do.

Download the whole table and filter in R. Narrowing the selection on the website makes a smaller file, but the selection then lives in a series of clicks you will not remember. In a script it is written down, and re-running with different years means editing one line.

Record the date you downloaded it. StatCan revises past figures as better information arrives, so the same table downloaded twice can differ. Put the date in your README (Section 7.3). When your numbers disagree with somebody else’s, this is usually why.

The files need work once you have them. Expect metadata rows above and below the table, column names with spaces and units in them, footnote markers stuck to numbers, and a shape that is long where you want wide. The reshaping tools in Section 6.2 and the cleaning in Section 9.2 are what these files need.

There is also an R package, cansim, that pulls tables by number:

library(cansim)
wheat <- get_cansim("32-10-0359-01")

This skips the download and records which table you used in the script itself, which makes the analysis re-runnable when the data is revised. It is the better habit once you are comfortable with the tables.

8.5 Test Bank Sample

  1. (Sheets.) You read an Excel file and get one column of notes. What went wrong, and which function tells you what is in the file?
  2. (Header.) You read a spreadsheet and the columns are called ...2, ...3. What does that mean and how do you fix it?
  3. (Types.) A numeric column imports as text. Give two values that would cause this, and say what breaks as a result.
  4. (Provenance.) Why does the date you downloaded a StatCan table matter?
  5. (Workflow.) Why is it usually better to download a wide selection and filter in R than to narrow the selection on the website?

8.6 Practice Exercises

  1. Read a multi-sheet Excel workbook. List its sheets, then read the second one.
  2. Read a file whose header starts on row 4. Confirm the column names are right before going further.
  3. Run glimpse() on a dataset you did not create. Is every column the type you expected?
  4. Download a crop yield table from Statistics Canada for Saskatchewan. Get it into R, filter to one crop, and record in a README where it came from and when.