Module 3 lab: getting data in, cleaning it and joining it

Work through this at your own pace. Each step ends with a check – something you should be able to see on your screen before moving on. If a check fails, that is the point to put your hand up rather than pushing on.

Module 3 teaches four jobs – import, reshape, clean, join – in both Power Query and R. This lab alternates: some steps are in Power Query and some are in R, and each ends with a short note on how the other tool does the same job. Doing all four jobs twice would take three hours.

The alternation is not random. Import and the wide-to-long reshape are in Power Query, because the menu path is the part you have to see once to remember. Diagnosis and cleaning are in R, because that is where a cleaning rule is a line you can read back. The join is in both, because that is where the two tools diverge most.

Plan on about an hour for the eleven steps, and half an hour more if you do the Practice boxes. It covers 7  Getting Data, 8  Cleaning Data and 9  Merging Data. If you run out of time, Step 9 onwards is the part to finish at home.

Step 1: Set up

You need both tools open today.

Open Excel. Power Query lives under Data > Get Data on Windows and Data > Get Data (Power Query) on a Mac. If the menu is missing you are in Excel for the web or a very old desktop version, neither of which has Power Query, and you will need one of the lab machines.

Open Positron and open your AREC_261 folder, the one from the Module 2 lab. If you do not have it, make a folder with code, data and output inside it and use File > Open Folder. Every read_csv("data/...") below is relative to the folder Positron has open, so opening module3_lab.R on its own makes the reads fail with a “does not exist” error.

Download these five files into data/:

Make a new R script in code/ called module3_lab.R, with a header:

# ---
# Title: Module 3 lab
# Author: Your Name
# Date: 2026-09-21
# Description:
#   Reading files, reshaping, cleaning and joining.
# ---

# Load packages
library(tidyverse)

You also need janitor, which is not part of the tidyverse. Install it in the console, once:

install.packages("janitor")

install.packages() downloads the package and you run it once, ever, in the console. library() makes it available in the current session and you run it every time, which is why it belongs in the script.

Add the library call to your script, under library(tidyverse):

library(janitor)

Check: both library() lines run without an error. The tidyverse one prints its usual attach message; janitor prints a short note about masking chisq.test and fisher.test, which is normal.

A masking message means two packages define a function with the same name and the one loaded later wins. It is a notice, not a warning. there is no package called 'janitor' is an error, and means the install did not happen.

Step 2: Import a CSV into Excel, the wrong way and the right way

A CSV file is plain text, with no record of what the columns hold, so every program that reads one has to guess. Excel guesses on the way in, before you get a say, and three of the seven columns here are identifiers made of digits.

Find elevator_tickets.csv in your data folder and double-click it. Excel puts a warning bar across the top about removing leading zeros and converting large numbers to scientific notation. Look at the sheet before you close it.

Check: ticket_id in A2 reads 1, not 00001. permit_book in B2 reads 4.52E+15. lot_code in C2 is a date – it shows as 2-Mar or 3-Feb depending on your regional settings, and the formula bar shows a full date. The cells no longer hold what the file holds.

Three different failures. The ticket ID lost its leading zeros because 00001 and 1 are the same number. The permit book lost its last digit because Excel stores numbers to fifteen significant digits and this one has sixteen. The lot code became a date because 3-2 looks like the third of February. None of it is a display problem – formatting the cell as Text changes what you see and not what is stored.

Close it without saving. Now do it properly.

  1. In a new blank workbook, choose Data > Get Data > From Text/CSV (Mac: Data > Get Data (Power Query), then the text/CSV connector) and select elevator_tickets.csv.
  2. In the preview window, set Data Type Detection to Do not detect data types.
  3. Press Transform Data, not Load. The Power Query Editor opens.
  4. In the editor, click the type icon to the left of each column heading and set ticket_id, permit_book and lot_code to Text, delivery_date to Date, and weight_tonnes to Decimal Number.
  5. Choose Home > Close & Load.

The order is the whole point: the type decision goes off at step 2 and back on at step 4. Do not detect data types brings every column in as text, the one type that cannot lose information, and then you set the types yourself. Skip step 2 and Power Query has already converted 00001 to 1 before you reach the editor, and step 4 can only turn 1 into the text "1". The damage is one-way.

Transform Data opens the Power Query Editor: a preview grid, an Applied Steps pane on the right, and a status bar at the bottom left showing the row and column count. Load, the other button, dumps the table straight onto a sheet with whatever types were guessed. Nothing in the editor touches the CSV – you are building a recipe, and it runs against the file each time the query refreshes.

Check: the loaded table has 20 rows and 7 columns, and the status bar at the bottom left of the Power Query Editor said 7 columns, 20 rows before you loaded. ticket_id in the first data row reads 00001 with its zeros, permit_book reads 4520134343304084 in full – sixteen digits, count them – and lot_code reads 3-2. The Applied Steps pane on the right of the editor listed four steps: Source, Promoted Headers, and the type changes.

Click each Applied Step in turn and watch the preview grid change. Each click shows the table as it stood at that moment, which is how you find the step that broke something.

Identifiers are the usual casualties: permit books, ticket numbers, RM numbers, UPC codes. If you would never add two of them together, it should come in as text.

NoteThe same thing in R

read_csv() guesses column types too, and gets permit_book wrong in the same way. The fix is the col_types argument, CSV, TSV and other delimited files. col_types = cols(permit_book = col_character()) is what Do not detect data types plus setting the column to Text does here, except that it is a line in a script.

One trap: the name has to match the file exactly, capitals and spaces and all. Write Permit_book and R does not error – it takes it as a column that happens not to be in the file, guesses permit_book as it always would, and hands you the broken column. Print the result and check the type.

Save the workbook as module3_lab.xlsx in your AREC_261 folder. Saving it as .csv throws the query away.

Practice

The permit_book column was mangled by the double-click import and survived the Power Query import. Why does the warning bar mention leading zeros and scientific notation but say nothing about lot_code?

Because Excel does not consider the lot code conversion a loss. It sees 3-2, decides it is a date, and stores a date serial number. From Excel’s point of view that is a successful type detection, so there is nothing to warn about.

That is the harder kind of problem. The zeros and the scientific notation announce themselves. The lot codes change silently, and you only find out if you look at the column and know what it is supposed to contain.

Step 3: Read data from an R package

Some data never arrives as a file. Statistics Canada publishes through an API, and the cansim package wraps it so you can pull a table by its number instead of clicking through a website.

Install it once, in the console:

install.packages("cansim")

Then, in your script, download the field crops table. It holds area, production and yield for every crop, province and year:

# Load the Statistics Canada wrapper
library(cansim)

# Field crop area, production and yield -- the whole table
field_crops <- get_cansim("32-10-0359-01")

# How big is it
dim(field_crops)

This takes a minute and prints progress messages while it downloads. get_cansim() caches the download, so running the line again in the same session is instant.

Check: 393,079 rows and 24 columns. One table number gave you every crop, every province and every year back to 1908.

That is the pattern with these packages: download the table, then filter it down. Filtering is harder than it looks, because Statistics Canada tables are stored long. Instead of one column per measurement there is a single VALUE column holding every number, and descriptor columns saying what each number is: GEO (15 values), REF_DATE (119 years), Type of crop (49 values) and Harvest disposition (20 values – seeded area, yield in bushels per acre, yield in kilograms per hectare, farm price, and so on).

A filter has to pin down every descriptor, or you get several different measurements stacked on top of each other. Saskatchewan canola in 2024 is eight rows: seeded and harvested area in two units each, a yield of 38.3 bu/ac, the same yield as 2,146 kg/ha, and production in bushels and in tonnes.

# Saskatchewan canola yields, the last six years
sk_canola <- field_crops |>
  filter(
    GEO == "Saskatchewan",
    `Type of crop` == "Canola (rapeseed)",
    `Harvest disposition` == "Average yield (kilograms per hectare)",
    REF_DATE %in% as.character(2020:2025)
  ) |>
  select(REF_DATE, VALUE)

sk_canola

Check: six rows – 2395, 1445, 2161, 2093, 2146 and 2535 kg/ha for 2020 through 2025. The 2021 drought is the 1,445.

Swap the disposition for Average yield (bushels per acre) and the same six years come back as 42.7, 25.8, 38.6, 37.4, 38.3 and 45.2, with nothing in the output to tell you which you asked for. The disposition string is where the units live.

Two things in the code. The backticks around `Type of crop` are needed because the name has spaces; without them R reads Type as a column name and errors. And REF_DATE is text, which is why the filter compares it to as.character(2020:2025). Compare a character column to numbers and the filter matches nothing and does not complain.

Zero rows is the standard outcome of a first attempt, and it is almost always a string that does not match – the real value is Canola (rapeseed), and spring wheat is Wheat, spring. Find the strings before you write the filter:

# The 49 crop names, as they are actually spelled
unique(field_crops$`Type of crop`)

# What is measured, which is also where the units are
unique(field_crops$`Harvest disposition`)

When a filter does come back empty, take the conditions off one at a time until rows reappear. The condition you removed last is the one with the bad string.

NoteThe same thing in Power Query

There is no equivalent. Power Query can read a URL through Data > Get Data > From Other Sources > From Web, but nobody has written a Power Query wrapper for Statistics Canada, so you would be assembling the API request by hand.

Practice

Get Saskatchewan spring wheat yields for the same six years and say which year was worst. The crop is Wheat, spring.

# Saskatchewan spring wheat yields, 2020 to 2025
field_crops |>
  filter(
    GEO == "Saskatchewan",
    `Type of crop` == "Wheat, spring",
    `Harvest disposition` == "Average yield (kilograms per hectare)",
    REF_DATE %in% as.character(2020:2025)
  ) |>
  select(REF_DATE, VALUE) |>
  arrange(VALUE)

2021 is worst, the same year that was worst for canola and, in Step 5, worst for every crop in the RM data. Three different sources agreeing is the useful part: the drought shows up whether you look at RM averages, provincial averages or your own fields.

Step 4: Read straight from a URL, and from a weather API

A path in read_csv() can be a URL. Anywhere you would write "data/something.csv" you can write "https://.../something.csv" and R fetches it. The RM yields used through this module come from the Saskatchewan dashboard. Back in your R script:

# The direct download URL for the RM yield data
rm_yields_url <- "https://dashboard.saskatchewan.ca/export/rm-yields-data/4950.csv"

# Read it straight from the web
rm_yields_live <- read_csv(rm_yields_url)

# Keep a dated copy so you are not dependent on the site
write_csv(rm_yields_live, "data/rm_yields_2026-09-21.csv")

Save the snapshot every time. The agency can change the URL, revise the numbers or take the file down, and a script that silently fetches something different next month is worse than one that fails. Put the date in the filename, so a result you produced in September can still be reproduced in December.

The URL has to point at the file itself, not at the page the download button is on. read_csv() on a dashboard page returns a heap of HTML tags or an error about the number of columns. Right-click the download link and copy the link address to get the real one.

Check: rm_yields_live has 26,197 rows and 18 columns – Year, RM, and one per crop – and a dated copy now sits in your data folder. It goes back to 1938, so it is far bigger than the 2,950-row 2015–2024 extract you will use in Step 5, and it carries crops that extract leaves out: winter wheat, mustard, lentils, canary seed, flax and chickpeas among them. Note that Winter Wheat has a space in it, which is why R prints the name in backticks.

Some providers do not publish a file at all. They run an API: a server you send parameters to – a place, a date range, a variable – which assembles a table for that request. Somebody has usually wrapped it in a package that turns function arguments into the web request and the reply into a data frame. The openmeteo package does this for weather. It is on GitHub rather than CRAN, so installing it takes two steps in the console:

install.packages("remotes")
remotes::install_github("tpisel/openmeteo")

Then ask for daily precipitation at Saskatoon for the 2024 growing season:

# Load the Open-Meteo wrapper
library(openmeteo)

# Daily precipitation for Saskatoon, May to August 2024
saskatoon_rain <- weather_history(
  c(52.13, -106.67),
  start = "2024-05-01",
  end = "2024-08-31",
  daily = "precipitation_sum",
  timezone = "America/Regina"
)

# Total growing-season rain
sum(saskatoon_rain$daily_precipitation_sum)

Check: 123 rows and 2 columns, one row per day from 1 May to 31 August, and the season total is about 296 mm. Open-Meteo is a model and reruns its estimates, so a few millimetres either way is expected rather than a mistake.

The arguments are the whole API request. Two are easy to get wrong. The coordinates go in as c(latitude, longitude) in that order, and the longitude is negative in Saskatchewan – swap them and you are asking for weather in the Indian Ocean, which the server will happily provide. And daily and hourly take different variable names: precipitation_sum is daily, precipitation is hourly. Type ?weather_history for the list.

There is no station here. Open-Meteo interpolates a weather model to the coordinates you asked for, so you get a number anywhere on earth, including places with no gauge for a hundred kilometres. It is an estimate of what fell, not a measurement of it.

NoteThe same thing in Power Query

Power Query reads a URL through Data > Get Data > From Other Sources > From Web, and a refresh re-fetches it. There is no equivalent of the dated snapshot – the query always holds whatever the URL returns today – and no wrapper for Open-Meteo.

Practice

How much rain fell at Saskatoon in each of the four months? You need a month column and a group_by().

# Monthly totals from the daily series
saskatoon_rain |>
  # Pull the month number out of the date
  mutate(month = month(date)) |>
  group_by(month) |>
  summarise(total_mm = sum(daily_precipitation_sum))

May 66.0, June 134.3, July 47.6, August 48.7. June had more than twice any other month. month() comes from lubridate, which the tidyverse loads for you.

Step 5: Wide to long, in Power Query

The RM yields file is wide: one row per RM per year, and one column for each of six crops. The problem is where the crop names are. They are data, sitting in the header row. Spring Wheat is a value of a variable called crop, in the same way that 2015 is a value of a variable called year – but year got a column and crop did not.

Long fixes that by giving crop a column of its own. One row becomes six and the table goes from eight columns to four: Year, RM, crop, yield_bu_ac. Anything you can do to a column, you can now do to crop – filter on it, group by it, colour a chart by it, drag it into the Rows area of a PivotTable. In the wide shape each of those is six separate operations glued together by hand.

The word for this is unpivot in Power Query and pivot_longer() in R.

In module3_lab.xlsx:

  1. Data > Get Data > From Text/CSV, select rm_yields_2015_2024.csv, and press Transform Data. Leave type detection on for this one – the columns are all numbers and dates are not involved.
  2. In the editor, click the Year column heading, then Ctrl-click (Cmd-click on a Mac) RM, so both are selected.
  3. Choose Transform > Unpivot Columns > Unpivot Other Columns.
  4. Double-click the Attribute heading and rename it crop. Rename Value to yield_bu_ac.
  5. Home > Close & Load.

Step 3 is where people go wrong, and the selection is the reason. You select the columns that should stay, then unpivot the other ones. The plain Unpivot Columns on the same menu folds the columns you selected instead, so choosing it here would fold Year and RM and leave the crops alone. Unpivot Other Columns also keeps working when a seventh crop appears.

Watch the preview grid as you press it. The crop names become repeated text down the Attribute column, and the numbers collect into Value. Those two default names are why step 4 exists.

Check: before loading, the status bar at the bottom of the editor reads 4 columns, 15478 rows. The four columns are Year, RM, crop and yield_bu_ac. The Applied Steps pane shows Source, Promoted Headers, Changed Type, Unpivoted Other Columns, and two Renamed Columns steps.

Note that number. The wide table has 2,950 rows and six crop columns, so unpivoting should give 2,950 × 6 = 17,700. It gives 15,478 because Unpivot Other Columns drops any source cell that was null, and 2,222 of the crop cells are empty – mostly durum and oats, neither of which is grown across most of the province.

Power Query made that decision for you and did not say so. For a mean yield per crop it makes no difference, but if you were counting how many RMs grew durum in a given year, the dropped rows are the answer you were looking for. Working out what the row count should have been, before you look at what it is, is the habit that catches this.

NoteThe same thing in R

pivot_longer() is the R version, in Data shape in R. It keeps the empty cells as NA and returns all 17,700 rows. Add values_drop_na = TRUE and you get Power Query’s 15,478. Step 6 does this.

Practice

Build a PivotTable on the long table to get the average yield for each crop across all ten years. In Excel, click inside the loaded table and choose Insert > PivotTable, put crop in Rows and yield_bu_ac in Values, then change the value field setting from Sum to Average.

Six rows. Oats highest at 78.0 bu/ac, then barley 59.3, spring wheat 43.5, durum 38.5, peas 35.5, canola 35.0.

On the wide table this would have taken six separate AVERAGE() formulas and a small table you assembled by hand, and it would have broken the moment a seventh crop appeared. On the long table crop is a field you can drag into the Rows area. That is the reason to reshape.

Step 6: Wide to long, in R

The same job, so you can see the two side by side. In your script:

# Ten years of RM average yields, one column per crop
yields_wide <- read_csv("data/rm_yields_2015_2024.csv")

yields_wide

Check: 2,950 rows and 8 columns. One row is an RM in one year; six of the columns are crops. That is 295 RMs over ten years.

# Crop names into one column, yields into another
yields_long <- yields_wide |>
  pivot_longer(
    # Everything except the two identifier columns
    cols = -c(Year, RM),
    names_to = "crop",
    values_to = "yield_bu_ac"
  )

yields_long
nrow(yields_long)

The three arguments map onto the Power Query clicks. cols = -c(Year, RM) is the R spelling of Unpivot Other Columns. names_to and values_to are the names Power Query calls Attribute and Value and makes you fix afterwards. Listing the six crop columns by name gives the identical result, but the negative form still does the right thing when the file gains a crop.

Check: 17,700 rows and 4 columns – Year, RM, crop, yield_bu_ac. That is 2,950 times six, and 2,222 more than Power Query gave you in Step 5.

# Drop the empty cells, the way Unpivot Other Columns does
yields_long |>
  pivot_longer(
    cols = -c(Year, RM),
    names_to = "crop",
    values_to = "yield_bu_ac",
    values_drop_na = TRUE
  ) |>
  nrow()

Run that on yields_wide rather than yields_long, since yields_long is already long.

Check: 15,478. The two tools now agree.

The R default is the better one for most work. NA says “this RM grew no durum that year, or nobody reported it”, which is information; a dropped row says nothing, and you cannot tell afterwards whether the value was missing or never there.

One row is now an RM-year-crop. The identifier columns repeat six times each, which looks wasteful and is exactly what makes the table easy to group and filter:

# Average yield per crop across the whole province and all ten years
yields_long |>
  group_by(crop) |>
  summarise(
    n = sum(!is.na(yield_bu_ac)),
    mean_yield = mean(yield_bu_ac, na.rm = TRUE)
  ) |>
  arrange(desc(mean_yield))

Check: six rows, and the same six numbers your PivotTable gave in Step 5 – oats 78.0, barley 59.3, spring wheat 43.5, durum 38.5, peas 35.5, canola 35.0. The n column shows how many yields went into each mean: 2,904 for canola, 1,772 for durum.

Those two counts are the reason to include n every time. Canola’s mean rests on 2,904 RM-years and durum’s on 1,772, so they are not the same kind of number even though they print side by side. sum(!is.na(yield_bu_ac)) rather than n(), because n() counts the empty rows too.

pivot_wider() goes back:

# Back to one column per crop
yields_long |>
  pivot_wider(
    names_from = crop,
    values_from = yield_bu_ac
  )

Check: 2,950 rows and 8 columns again, the same shape you started with. The Power Query equivalent is Transform > Pivot Column.

names_from supplies the new headings and values_from the numbers under them. pivot_wider() works out the identifier columns itself: everything you did not name becomes what each row is keyed on. If those leftover columns do not uniquely identify a row it cannot decide which value to put in a cell, so it puts a list there and warns that values are not uniquely identified. A column full of <dbl [295]> is not a column of numbers. The fix is to summarise first and then widen, which is the order the Practice box below uses.

Neither shape is correct in general. Long is easier to compute on; wide is easier to read.

Practice

Make a table with one row per year and one column per crop, holding the provincial average yield. Which year was worst, and by how much?

# Mean yield by year and crop, then spread the crops across columns
yields_long |>
  group_by(year = Year, crop) |>
  summarise(mean_yield = round(mean(yield_bu_ac, na.rm = TRUE), 1),
            .groups = "drop") |>
  pivot_wider(names_from = crop, values_from = mean_yield)

2021, the drought year, and it is not close. Canola averaged 21.9 bu/ac against 42.2 in 2016; spring wheat 30.2 against 49.4 in 2020. Every crop has its minimum in that row.

Note the order: summarise on the long table, then pivot wider to present it. Going the other way is much harder work.

Step 7: Find out what is wrong with a file, in Excel

field_records_messy.csv is a season of spring wheat records, built to contain the problems from Common data quality issues. Do not clean it yet. Diagnose it first, because you cannot fix a problem you have not found. You will do the diagnosis twice, in Excel here and in R in Step 8.

Open a blank workbook, then Data > Get Data > From Text/CSV, pick field_records_messy.csv, and load it. Add a second sheet called Checks. Everything below goes there, so the data sheet stays untouched and the formulas recalculate when the query refreshes.

The columns are Field ID in A, VARIETY_NAME in B, Acres in C, Yield (bu/ac) in D, harvest weight in E, N rate in F and Moisture % in G, with 38 data rows from row 2 to row 39.

The first question is which columns are numbers. COUNTA counts non-empty cells; COUNT counts only the numeric ones. Where they disagree, there is text in a number column.

=COUNTA(Sheet1!D2:D39)   =COUNT(Sheet1!D2:D39)
=COUNTA(Sheet1!E2:E39)   =COUNT(Sheet1!E2:E39)
=COUNTA(Sheet1!G2:G39)   =COUNT(Sheet1!G2:G39)

Check: yield (column D) gives 38 and 38, so it is entirely numeric. Harvest weight (E) gives 38 and 0 – not one cell is a number, because every value carries a t suffix. Moisture (G) gives 37 and 35: one of the 38 cells is empty, and two of the remaining 37 hold text rather than a number.

A count of 0 means every value is text, usually a unit typed into the cell. A small gap is the awkward case: the column looks fine until you take a mean built from only some of the rows.

Now the range of each numeric column. Down a column on Checks, against the yield:

=MIN(Sheet1!D2:D39)      =MAX(Sheet1!D2:D39)
=AVERAGE(Sheet1!D2:D39)  =MEDIAN(Sheet1!D2:D39)

Check: yield runs from -99 to 1250, mean 74.47, median 55.85. A yield cannot be negative and 1,250 bu/ac is not possible for spring wheat, so at least two values are wrong. The gap between the mean and the median is the 1,250 dragging the average up.

The minimum and the maximum do most of the work, because an impossible value is almost always at one end or the other. The mean against the median is the second signal.

Count how many rows are affected:

=COUNTIF(Sheet1!D2:D39,"<0")
=COUNTIF(Sheet1!D2:D39,">200")

Check: four rows below zero, one above 200. Three of the four are the -99 missing-value code and the fourth is a -12, somebody typing a minus sign rather than a system writing a code for “no data”. Both become NA in Step 9, because neither is recoverable.

Five bad rows out of 38 is a cleaning job; twenty out of 38 would be a reason to go back to whoever sent you the file.

Do the same for the nitrogen rate in column F:

=MIN(Sheet1!F2:F39)   =MAX(Sheet1!F2:F39)   =MEDIAN(Sheet1!F2:F39)
=COUNTIF(Sheet1!F2:F39,"<1")

Check: minimum 0.08, maximum 135.9, median 89.9, and 12 rows below 1. Two clusters three orders of magnitude apart is what mixed units look like – twelve rows are in tonnes per hectare and the rest in kilograms.

This one passes a minimum-and-maximum test, because 0.08 and 135.9 are both numbers a fertilizer rate could be. The gap in the middle gives it away: the twelve low values run from 0.08 to 0.13 and the next value up is 80.0.

For the categories, spill the distinct values and count each one:

=UNIQUE(Sheet1!B2:B39)
=COUNTIF(Sheet1!B$2:B$39,D1)     (filled down beside the UNIQUE list)

Check: six varieties. CDC Landmark 13, AAC Viewfield 6, Brandon 6, Brandon AAC 5, Brndon 4, AAC Brandon 4. The last four are one variety typed four ways.

Nothing in the spreadsheet can tell you that, because Brndon and AAC Brandon are as different to Excel as Barley and Canola are. You know it because you know there is a wheat variety called AAC Brandon and none called Brndon. This is the part of cleaning that does not automate. The counts help: 4 rows out of 38 is a candidate for a typo, 13 is probably real.

And the duplicate check, which is one formula:

=ROWS(Sheet1!A2:A39) - ROWS(UNIQUE(Sheet1!A2:G39))

UNIQUE(Sheet1!A2:G39) over the seven-column block spills the distinct rows, not the distinct values in one column, and ROWS() counts them. The range has to cover every column: run UNIQUE on column A alone and two fields genuinely measured twice would look like duplicates.

Check: 2. Two rows are exact copies of another row – F002 and F005, each appearing twice with identical values in all seven columns.

Practice

Find the moisture cells that are not numbers, and the one that is a number but cannot be a moisture reading.

=UNIQUE(Sheet1!G2:G39) spills every distinct value, and the text ones are visible in the list: missing, plus two empty cells. Those are what made COUNT disagree with COUNTA.

=MAX(Sheet1!G2:G39) returns 9999, and =COUNTIF(Sheet1!G2:G39,">100") returns 1. That one is numeric, so no formula flags it as the wrong type. It is a missing-value code, and you only catch it because you know moisture is a percentage.

Some problems the computer finds for you; the rest you find because you know what the numbers mean.

Step 8: Find out what is wrong with it, in R

Same file, same job, different tool. Diagnosis is the one job where the two tools are genuinely different in effort. In Excel it is the worksheet of formulas you just built. In R it is three function calls.

# Read the messy field records
records <- read_csv("data/field_records_messy.csv")

# Column names and types as they arrived
glimpse(records)

Check: 38 rows, 7 columns. harvest weight and Moisture % are <chr> even though both should be numbers. The names have spaces, capitals and a %.

glimpse() is the COUNT-against-COUNTA check from Step 7, for every column at once. The type tag beside each name is read_csv() reporting what it decided, and a <chr> where you expected a number is the flag. A column goes to text when even one value is not a number; one N/A in 38 rows is enough.

Now the summary:

# Min, max, quartiles for every column
summary(records)

summary() prints a block per column: Min., 1st Qu., Median, Mean, 3rd Qu. and Max. for a numeric one, plus an NA's line if there are any, and counts instead for a character one. That is the whole MIN/MAX/AVERAGE/MEDIAN block of Step 7, for every column, in one call.

Check: Yield (bu/ac) runs from -99 to 1250 with a mean of 74.5 and a median of 55.8. N rate has a minimum of 0.08 and a first quartile of 0.13, but a median of 89.9.

The quartiles are the part Excel did not give you. Read the nitrogen ones as a sentence: a quarter of the rows are at or below 0.13, half are at or below 89.9. There is no way to get from 0.13 to 89.9 gradually in one quartile, so the column holds two groups rather than one spread. A histogram makes it obvious:

# Two clusters three orders of magnitude apart
hist(records$`N rate`, breaks = 20)

Backticks around the name, because it has a space in it. records$N rate is a syntax error, and the backticks are needed every time you touch the column until you rename it.

Categories next:

# How many of each variety
records |>
  count(VARIETY_NAME, sort = TRUE)

Check: six varieties listed. Four of them – Brandon (6), Brandon AAC (5), AAC Brandon (4), Brndon (4) – are the same variety typed four ways.

count() is the UNIQUE() plus COUNTIF() pair from Step 7 in one call, and sort = TRUE puts the rare categories together at the bottom. Run it on every categorical column of a new file. It is the only way to find Canola, canola and Canola with a trailing space, which are three categories to R and one crop to you – and the trailing-space version prints identically to the clean one.

And duplicates:

# Rows, then rows once duplicates are dropped
nrow(records)
nrow(distinct(records))

Check: 38 and 36. Two rows are exact duplicates of another row.

distinct() with no arguments compares whole rows, so two fields that happen to share a yield survive. Give it a column name – distinct(records, field_id) – and it means something else: keep one row per field ID and throw away the rest, whatever those rows contained. The first is a safe check; the second discards data and needs a reason.

Two identical rows are not automatically an error either. If two rows could legitimately be identical – two truckloads of the same weight and grade on the same day – distinct() deletes a real observation. Here a row is a field and a field ID cannot appear twice, so removing them is right.

NoteThe same thing in Excel

You built it in Step 7. COUNT against COUNTA is glimpse(), MIN/MAX/AVERAGE/MEDIAN down a column is summary(), UNIQUE with a COUNTIF beside it is count(), and the ROWS subtraction is distinct(). The DataSummary sheet of the workbook in Data cleaning in Excel has the same set laid out on one sheet.

Practice

The moisture column arrived as text. Find out which values are stopping it being numeric.

# Every distinct value in the column
records |>
  count(`Moisture %`, sort = TRUE)

N/A and missing are text where a number should be, and either one on its own forces the whole column to character. There is also a 9999, which is numeric and therefore will not show up this way – it is a missing-value code, and you only catch it by knowing that moisture is a percentage and cannot be 9999.

That is the general shape of this work. Some problems the computer finds for you; the rest you find because you know what the numbers mean.

Step 9: Clean it in R

Ten issues, and each one is a line in a pipeline. Two new functions do most of the work: clean_names() from janitor puts every column name in snake case, and parse_number() pulls the number out of a string like 297.7 t. clean_names() goes first, turning Yield (bu/ac) into yield_bu_ac and Moisture % into moisture_percent, so every line after it is written without backticks.

The order of the rest matters too. Two places where getting it backwards changes the answer:

  • distinct() goes before the variety fix. Run it after, and rows that differed only in how the variety was typed now look identical and get deleted. Here both orders give 36 rows, but that is luck. Deduplicate against the data as it arrived.
  • parse_number() goes before the if_else() on moisture. Reverse them and nothing errors: R compares the text to "100" character by character, and "12.4" > "100" is TRUE because the leading 1s tie and then 2 beats 0. The rule fires on all 37 values, and every moisture reading becomes NA.

Both moisture lines sit inside the same mutate(), which is what makes the second one work: mutate() runs its arguments in order and each sees the results of the ones above it.

Build this up a few lines at a time rather than typing it all out.

# Clean the field records in one pipeline
records_clean <- records |>
  # Issue 1: names to snake case
  clean_names() |>
  rename(variety = variety_name) |>
  # Issue 7: drop the two exact duplicate rows
  distinct() |>
  mutate(
    # Issue 2: four spellings of one variety
    variety = if_else(
      variety %in% c("Brandon", "Brandon AAC", "Brndon"),
      "AAC Brandon",
      variety
    ),
    # Issues 3, 4 and 8: the -99 code, the negative yield and the 1250
    yield_bu_ac = if_else(yield_bu_ac <= 0 | yield_bu_ac > 200, NA, yield_bu_ac),
    # Issue 5: strip the " t" off the weights
    harvest_weight = parse_number(harvest_weight),
    # Issue 6: rates below 1 are t/ha, so convert to kg/ha
    n_rate = if_else(n_rate < 1, n_rate * 1000, n_rate),
    # Issue 9: moisture has text in it
    moisture_percent = parse_number(moisture_percent),
    # Issue 10: 9999 is a missing-value code
    moisture_percent = if_else(moisture_percent > 100, NA, moisture_percent)
  )

records_clean

if_else() takes a test, what to use when it is TRUE, and what to use when it is FALSE. The third argument is the one people forget – naming the column itself is how you say “unchanged”. The yield rule sets the bad values to NA rather than deleting the row, so the field’s acres, nitrogen rate and moisture stay and only the wrong number goes.

parse_number() warns about two parsing failures. That is R telling you N/A and missing became NA, which is what you wanted. The same warning would appear if it had mangled a column you cared about, so look at which rows it names.

Now run the same checks on the cleaned table:

summary(records_clean)
count(records_clean, variety, sort = TRUE)

Check: 36 rows. Yield runs from 41.5 to 76.6 with a mean of 57.0 and 5 NAs. Nitrogen runs from 80.0 to 135.9. Moisture runs from 11.6 to 16.5 with 4 NAs. Three varieties: AAC Brandon 18, CDC Landmark 12, AAC Viewfield 6.

Running the diagnosis again on the cleaned table is the step to not skip. It is the only thing that tells you the rules did what you meant rather than what you typed, and it catches a condition that fired on more rows than intended.

Read the result against what you expected. The nitrogen range has no gap in the middle any more, so the twelve converted rows landed inside the range of the others – had the multiplier been wrong they would sit as a separate cluster again. The 19 Brandon rows became 18, because one was a duplicate. And the yield mean moved from 74.5 to 57.0: a single fake 1,250 was pulling the average up by seventeen bushels.

Write the result somewhere new. The raw file does not get edited:

# The cleaned copy goes to output; data/ keeps the original
write_csv(records_clean, "output/field_records_clean.csv")

You need an output folder inside AREC_261 for that line to work.

Check: output/field_records_clean.csv exists and data/field_records_messy.csv is unchanged.

NoteThe same thing in Power Query

Each mutate() above is a menu item, and each one lands in the Applied Steps pane:

The R line The Power Query step
clean_names() Double-click each heading, or Transform > Rename
distinct() Home > Remove Rows > Remove Duplicates
if_else() on variety Transform > Replace Values, once per spelling
if_else() on yield Add Column > Conditional Column, then delete the original
parse_number() on weight Transform > Replace Values, replacing t with nothing, then Change Type > Decimal Number

The CleanData sheet of the workbook in Data cleaning in Excel is the output of exactly this, and the video for that section walks through the clicks. Both versions give the same 36 rows. The difference is what you can read afterwards: nine commented lines that say what was decided, against a list of step names you have to click through.

Practice

Which variety yielded best on these fields? Report the number of fields as well as the mean.

# Mean yield by variety on the cleaned records
records_clean |>
  group_by(variety) |>
  summarise(
    n = n(),
    mean_yield = mean(yield_bu_ac, na.rm = TRUE)
  ) |>
  arrange(desc(mean_yield))

CDC Landmark 57.7 over 12 fields, AAC Brandon 57.2 over 18, AAC Viewfield 53.6 over 6. The top two are half a bushel apart on a dozen fields each, which is not a difference you would want to report as one.

na.rm = TRUE matters here. Without it every mean comes back NA, because the five yields you set to NA in Step 9 are still in the column. That is the point of NA – it refuses to be ignored by accident.

Step 10: Join two tables in Power Query

The question the module is built around is whether canola yields move with growing-season rain. Yield is in one file and rain is in another, and nothing connects them until you add the lookup table that says which weather station is closest to which RM.

Say what one row of each table is before you touch the merge dialog. In the yield query one row is an RM in a year for one crop, 15,478 of them. In rm_lookup.csv one row is an RM, 296 of them, with the closest weather station. In station_precip.csv one row is a station in a year, 160 rows.

Two merges are needed because the yield and precipitation tables share no column. The lookup is the bridge: it carries both an RM number and a station name, so joining it to the yields puts a station name on every yield row, and only then is there something to match the precipitation against.

The key is the column, or set of columns, used to match rows – here the RM number, RM in the yields and RM_Number in the lookup. Before clicking, say what you expect the row count to do. Many yield rows share an RM and the lookup has one row per RM, so this is a many-to-one merge and the count should not change. That prediction is what makes the check afterwards mean anything.

You already have the unpivoted yield query in module3_lab.xlsx from Step 5. Add the other two.

  1. Data > Get Data > From Text/CSV, select rm_lookup.csv, and press Transform Data. Do the same for station_precip.csv. The Queries pane now lists three queries.
  2. Click the rm_yields_2015_2024 query, then Home > Merge Queries (the plain one, not Merge Queries as New).
  3. Pick rm_lookup as the second table.
  4. Click the RM column heading in the top table and RM_Number in the bottom one.
  5. Leave Join Kind as Left Outer (all from first, matching from second).
  6. Read the line at the bottom of the dialog before you press OK.

Left Outer keeps every row of the first table whether it matched or not, taking columns from the second only where there is a match. Unmatched rows come through with null. That is the right default here, because dropping a yield observation is worse than carrying an unmatched one.

The line at the bottom of the dialog reports the match count before the merge runs, so you can fix a bad key while nothing has happened yet. A number far below the first table’s row count means the keys do not line up, usually a text RM against a numeric RM_Number. A number far above it means repeated keys in the second table, which Step 11 comes back to.

Check: the line at the bottom of the Merge dialog reads The selection matches 15,478 of 15,478 rows from the first table. Every yield observation found an RM. Press OK.

The merged data arrives folded into one new column called rm_lookup, every cell reading [Table]. Nothing is broken. Power Query nests the matched rows inside each cell and waits for you to say which of the lookup’s columns you want.

  1. Click the double-arrow icon in that column’s heading. Untick RM_Number, leave the other five ticked, and untick Use original column name as prefix. Press OK.

RM_Number comes out because it is the key and you already have it as RM. Untick the prefix box or every expanded column arrives named rm_lookup.RM_Name and so on.

Check: the status bar reads 9 columns, 15478 rows. The five new columns are RM_Name, Census_Division, Population_2021, Weather_Station and Station_km. The row count has not changed, which is what a many-to-one left join should do.

Do not click a [Table] link itself. That drills into that one row’s table and throws away every other row. The giveaway is a row count that suddenly reads 1. Delete the step in Applied Steps and expand properly.

Now the second merge. Precipitation is identified by station and year, so the key is two columns: Kipling is ten rows of the precipitation table, one per year, and 2015 is sixteen, one per station. Only the pair picks out one number.

  1. Home > Merge Queries again, this time picking station_precip as the second table.
  2. In the top table click Weather_Station, then Ctrl-click (Cmd-click) Year. In the bottom table click Station, then Ctrl-click Year. Small 1 and 2 badges appear beside the headings to show the matching order.
  3. Leave the join kind as Left Outer and press OK, then expand the new column, ticking Precip_May_Aug_mm and Days_Missing and unticking the prefix box.
  4. Home > Close & Load.

The key columns do not need the same name – Weather_Station matches Station here – but they do need to be selected in the same order. Power Query pairs the 1 in the top table with the 1 in the bottom, so the badges are everything.

Check: the Merge dialog said The selection matches 15,478 of 15,478 rows from the first table. The loaded sheet has 15,478 data rows and 11 columns. In the first data row, RM is 1, Weather_Station is Kipling, Year is 2015 and Precip_May_Aug_mm is 183.2.

Check the badges. Click Year first and Weather_Station second in one table but not the other and Power Query matches years against station names, matches nothing, and fills the column with null without complaining. The match line reads The selection matches 0 of 15,478 rows, and if you press OK anyway you get a sheet of the right size with an empty precipitation column.

Close & Load writes the result to a sheet and leaves the query behind it. Change the source file and Data > Refresh All runs all three queries and both merges again – a recipe rather than a one-off result, which is the difference between this and a sheet of XLOOKUP formulas.

Step 11: The same join in R

Read all three files, cleaning the names as you go so the join keys are predictable:

# Yields, pivoted long, with tidy names
yields <- read_csv("data/rm_yields_2015_2024.csv") |>
  clean_names()

yields_long <- yields |>
  pivot_longer(
    cols = spring_wheat:peas,
    names_to = "crop",
    values_to = "yield_bu_ac"
  )

# One row per RM: its name, division, population and closest station
rm_lookup <- read_csv("data/rm_lookup.csv") |>
  clean_names()

# One row per station per year
precip <- read_csv("data/station_precip.csv") |>
  clean_names()

clean_names() on all three is not cosmetic. It leaves year spelled the same way in every table, so the second join can match it by name alone. Skip it on one of the three and you spend the rest of the step writing join_by(Year == year).

cols = spring_wheat:peas here rather than the -c(Year, RM) of Step 6. The colon means “these two columns and everything between them”, which works because the six crops are adjacent. It is the same six columns either way.

Check: yields_long 17,700 rows, rm_lookup 296 rows and 6 columns, precip 160 rows – sixteen stations over ten years.

Check the small tables the way you checked the big one. rm_lookup should have one row per RM, so 296 rows means nothing is repeated. Had it 300 rows for 296 RMs, four would be listed twice and the join would duplicate every yield row belonging to them. nrow(rm_lookup) against n_distinct(rm_lookup$rm_number) is that check in one line, and it is the thing to run on any table you are about to join to.

# Attach the RM information to every yield observation
with_station <- yields_long |>
  left_join(rm_lookup, by = join_by(rm == rm_number))

# The count is the check
nrow(yields_long)
nrow(with_station)
names(with_station)

Check: 17,700 both times, and 9 columns instead of 4. The five new ones are rm_name, census_division, population_2021, weather_station and station_km.

If the second number were larger, the lookup would have had more than one row for some RM and every yield row belonging to it would have been duplicated. That is the most common way a join goes wrong. Nothing errors, the columns are all correct, and every row looks like a real observation – there are just more of them than there should be, so every mean, count and total afterwards is wrong and none of them look wrong. The cause is usually dull: a lookup appended to itself, an RM that changed name mid-decade and has a row for each.

R will tell you if you ask. relationship = "many-to-one" makes the join error rather than inflate when the right-hand table has repeated keys:

# Fail loudly if rm_lookup has more than one row per RM
yields_long |>
  left_join(rm_lookup, by = join_by(rm == rm_number), relationship = "many-to-one")

This runs clean here.

left_join() keeps every row of the left table whether or not it matched. Rows that found nothing get NA in the new columns, so count them:

# Yield rows that found no RM in the lookup
sum(is.na(with_station$rm_name))

Check: 0. Every yield observation matched.

Counting the NAs is a separate check from counting the rows, and both are needed. A left join that matches nothing still returns 17,700 rows – the right count, with empty new columns. The row count tells you nothing was duplicated; the NA count tells you something was found.

Now the second join, on station and year:

# Add the growing-season rain for that station in that year
merged <- with_station |>
  left_join(precip, by = join_by(weather_station == station, year))

nrow(merged)
sum(is.na(merged$precip_may_aug_mm))
glimpse(merged)

Check: 17,700 rows, 11 columns, and 0 missing precipitation values. Your Power Query version from Step 10 has 15,478 rows and the same 11 columns, because the unpivot dropped the empty yield cells and pivot_longer() kept them.

join_by(weather_station == station, year) matches weather_station to station, and year to year. A key with the same name in both tables is written once; one with different names takes ==. Both forms can appear in the same join_by(), as they do here. This is the two-column key from the Merge dialog, on one line instead of Ctrl-clicked in two panes.

Forgetting year is the mistake this key invites, and it does not error. The “If you finish early” section at the end has you make it on purpose.

# Year-level canola yield against year-level rain
annual_canola <- merged |>
  filter(crop == "canola") |>
  group_by(year) |>
  summarise(
    canola_bu_ac = mean(yield_bu_ac, na.rm = TRUE),
    rain_mm = mean(precip_may_aug_mm, na.rm = TRUE)
  )

cor(annual_canola$canola_bu_ac, annual_canola$rain_mm)

Check: ten rows, one per year, and the correlation is about 0.47.

The yield and the rain are now in the same table, so one line compares them. It is ten points, and Module 10 is where correlation gets treated properly – take this as evidence the pipeline works rather than as a result. The group_by(year) is what makes it ten rows rather than one.

Practice

The lookup has 296 RMs and the yield data has 295. Find the RM that is in the lookup and not in the yields, and say what left_join() did with it.

# Rows of rm_lookup with no match in the yield data
anti_join(rm_lookup, yields_long, by = join_by(rm_number == rm))

RM 521, Lakeland, in Division No. 15.

left_join(yields_long, rm_lookup) dropped it, because a left join keeps the rows of the left table only. It never appears in merged. Swap in inner_join() and you get the identical 17,700 rows, since every yield row matched. Swap in full_join() and you get 17,701 – Lakeland comes back as a row with an RM name and no yield.

Power Query has all four. The Merge dialog’s Join Kind dropdown offers Left Outer, Right Outer, Full Outer, Inner, Left Anti and Right Anti. anti_join() is Left Anti, and it is the one to reach for before a join rather than after. It answers “what will I lose” while there is still time to do something about it.

Practice

Using merged, is there a yield difference between wetter and drier station-years for spring wheat? Split at 200 mm.

# Spring wheat yields, split on growing-season rain
merged |>
  filter(crop == "spring_wheat") |>
  # TRUE for station-years above 200 mm
  mutate(wet = precip_may_aug_mm > 200) |>
  group_by(wet) |>
  summarise(
    n = n(),
    mean_yield = mean(yield_bu_ac, na.rm = TRUE)
  )

48.5 bu/ac on the 1,297 wetter observations against 39.5 on the 1,653 drier ones, a gap of nine bushels.

Be careful what you conclude. Wetter parts of the province differ from drier parts in soil, management and crop mix as well as in rain, and a station 145 km from the RM it is standing in for is a rough measure of what fell on those fields. The comparison is worth making and is not a measurement of what rain does to wheat.

If you finish early

The best use of any time left is the test bank: Module 3 test bank. Your test draws one question from each section, so work through examples from every section – downloading data, data shape, data cleaning, and merging, in both Excel and R – rather than five of the kind you already find easy. Doing them here is the point of the lab: this is the one time you can ask “why did mine come out different?” with the file still open in front of you.

Three other things if you want a break from the bank.

Clean a second file from scratch, in whichever tool you used less today. grain_deliveries_messy.csv has 62 delivery tickets with a fresh set of the same problems. Run glimpse(), summary() and count() on it, or import it with Power Query and build the summary sheet, and see how many you can find before reading on.

Crop is spelled ten ways across four actual crops (barley, Barley, canola, Canola, CANOLA and so on) – str_to_title() fixes all of it at once in R, and Transform > Format > Capitalize Each Word does it in Power Query. weight_tonnes is text, because one row reads 38.2 t, and one weight is -12.4. Three moisture readings are around 0.09 when the rest are near 10, so they were recorded as a fraction rather than a percent, and one is 250. One grade is N/A. One row, ticket T0012, is an exact duplicate. Dates are written in two different formats.

Cleaned, with the impossible values set to NA, you should get 61 rows and four crops: Peas 19, Barley 15, Spring Wheat 15, Canola 12.

Then check whether the year-level correlation in Step 10 holds for other crops. Wrap the annual_canola code in a group_by(crop), or change the filter and run it six times. Durum comes out highest at 0.55 and spring wheat lowest at 0.31 – durum is grown in the dry southwest, where a millimetre of rain matters more.

Last, break a join on purpose so you know the failure when you see it. Run this:

left_join(yields_long, rm_lookup, by = join_by(rm == rm_number)) |> nrow()
left_join(yields_long, precip, by = join_by(year)) |> nrow()

The second joins on year alone, with no station. Every yield row matches all sixteen stations for its year and the table goes from 17,700 rows to 283,200. Nothing errors. R prints a note about an unexpected many-to-many relationship, and it scrolls past in the same grey as every other message.

Look at what the broken table contains. Every RM now carries sixteen precipitation values for the same year, and every one is a real measurement of rain that fell somewhere. Rerun the annual_canola code on it and you get a correlation of 0.53 instead of 0.47 – a plausible number in the right range, computed from a table where the rain in every row has nothing to do with the RM beside it. A broken join of this kind gives you a result rather than an error, which is why the row count is the check.

In Power Query the same mistake shows up in the Merge dialog, but only if you read the match line. Drop Year from the second key and it reports a match count far larger than the row count of the first table.

Before you leave

Save both files. You should have an R script that reads six datasets, reshapes one, cleans another and joins three, and a workbook with five queries whose Applied Steps record the same work.

Most of what you did today has no output worth showing anybody. It is also the part that decides whether the analysis on top of it is worth anything, and it is where most of the time goes on a real project.