3  Transforming Data in R

Learning Objectives

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

  1. Use the core dplyr verbs (filter, select, mutate, arrange, summarise, group_by) fluently.
  2. Chain operations together with the pipe (|>).
  3. Handle common data cleaning tasks: unit conversion, creating new variables, handling missing values, renaming columns.
  4. Work with dates using lubridate.
  5. Use case_when for multi-condition transformations.
  6. Explain what “tidy data” means and why it matters.

3.1 The Idea of Data Transformation

When you meet a new dataset in the wild, it is almost never in exactly the form you need. Column names are weird. Units are inconsistent. Dates are stored as strings. Some rows belong to a different year. The things you actually want to compute are combinations of things that are in the data. Data transformation is the work of getting from the dataset as it exists to the dataset you can actually analyze.

In Excel, this work happens in a mix of formulas, filters, and manual edits. In R, it happens in a script — which means it is reproducible, auditable, and easy to re-run. This is one of R’s biggest advantages.

The tidyverse package dplyr provides a small set of verbs for data transformation that are powerful enough to express most of what you will ever need. The key insight of dplyr is that most data manipulation is a sequence of simple operations, and if you have the right small vocabulary, complex transformations become readable.

3.2 Getting Set Up

To follow along with this module hands-on, download the small teaching dataset field_yields.csv — 60 canola fields with columns field_id, region, variety, acres, yield_bu_acre, year, seeding_date, and units. Put it in your working directory (see Section 2.6 in Module 2 if you need a reminder about the working directory), then start your script with:

library(tidyverse)

yields <- read_csv("field_yields.csv")

Every example below assumes this yields data frame is loaded. Run yields on its own line to see it, or glimpse(yields) for a compact column-by-column view.

One crucial thing to understand before we start. Every dplyr verb returns a new data frame — it does not change the original. So this:

yields |> filter(yield_bu_acre > 50)

computes the filtered result and prints it, but yields itself is unchanged — the next time you use yields, all 60 rows are still there. If you want to keep a result, you must assign it to an object:

big_fields <- yields |> filter(yield_bu_acre > 50)   # save the result

This is a feature, not a limitation: your original data stays intact while you experiment. Throughout this module, when an example just shows a pipeline without <-, assume we are looking at the result on screen; add name <- in front when you want to save it.

3.3 Tidy Data

Before we dive into verbs, a philosophical detour.

Hadley Wickham (one of the creators of the tidyverse) has written extensively about what he calls tidy data (Wickham et al. 2023). The idea is simple: a dataset is “tidy” if:

  1. Every variable has its own column.
  2. Every observation has its own row.
  3. Every value has its own cell.

That sounds obvious, but a huge fraction of real datasets violate it. Common problems:

  • Column headers are values, not variable names. For example, a table with columns 2020, 2021, 2022, 2023 where each column contains yields. The column header should be a variable called year; the values should be in a single yield column.
  • Multiple variables in one column. For example, a region_variety column that contains "South_InVigor". These should be two separate columns.
  • Observations split across rows. For example, one row for price and another for quantity of the same product. These should be columns.

You can do analysis on untidy data, but it is much harder. Almost every tool in the tidyverse assumes tidy input. If your data is not tidy, your first step should usually be to make it tidy using tools like pivot_longer and pivot_wider (which you will meet in Module 5, or whenever it comes up naturally).

For now the point is: when you are reading in a dataset, look at the shape. Is each row an observation? Is each column a variable? If not, fixing that is the first step.

3.4 The Six Core Verbs

All code in this section uses the yields data frame you loaded in Section 3.2.

A note on the pipe |>

You will see the symbol |> in almost every example below. It is the pipe, and it does something simple: it takes whatever is on its left and feeds it as the first input to the function on its right. So yields |> filter(...) reads as “take yields, then filter it.” You could write the same thing as filter(yields, ...), but the pipe lets you chain several steps into a readable top-to-bottom sequence, which you will see shortly. Read |> as the word “then.” (Older code uses %>% for the same idea; |> is the modern built-in version.)

filter(): Keep Certain Rows

filter() keeps rows where a condition is true.

yields |> filter(yield_bu_acre > 50)

This keeps only rows where yield exceeds 50. You can combine conditions with & (and), | (or), and ! (not):

yields |> filter(yield_bu_acre > 50 & region == "South")
yields |> filter(region == "South" | region == "Central")
yields |> filter(!is.na(yield_bu_acre))  # drop rows with missing yield

A shortcut for “in this set of values”:

yields |> filter(region %in% c("South", "Central"))

Important distinction: use == (two equals signs) to test whether two things are equal. A single = means something different in R — it is used for assigning values and naming arguments, not for testing equality — so writing region = "South" inside filter() will not do what you want. Always use == for comparisons. And note "South" — with quotes — is a string (a piece of text); South — without quotes — would be read as the name of an object, which probably doesn’t exist and would cause an error.

select(): Keep Certain Columns

select() keeps (or drops) columns.

yields |> select(field_id, region, yield_bu_acre)  # keep these three
yields |> select(-field_id)                         # drop field_id
yields |> select(starts_with("yield"))              # columns whose name starts with "yield"

Useful when you have a wide dataset and want to focus on a few columns.

mutate(): Add or Modify Columns

mutate() creates new columns or modifies existing ones.

yields |>
  mutate(yield_t_ha = yield_bu_acre * 0.0560)   # bu/acre to tonnes/ha for canola

The new column is computed from the existing ones. You can create multiple columns in one call, and later columns can use earlier ones:

yields |>
  mutate(
    yield_t_ha = yield_bu_acre * 0.0560,
    above_average = yield_bu_acre > mean(yield_bu_acre, na.rm = TRUE),
    total_bu = yield_bu_acre * acres
  )

arrange(): Sort Rows

yields |> arrange(yield_bu_acre)              # ascending
yields |> arrange(desc(yield_bu_acre))        # descending
yields |> arrange(region, desc(yield_bu_acre))  # sort by region, then by yield within region

summarise(): Collapse to Summary

summarise() computes summary statistics, reducing many rows to one.

yields |>
  summarise(
    mean_yield = mean(yield_bu_acre, na.rm = TRUE),
    sd_yield = sd(yield_bu_acre, na.rm = TRUE),
    n = n()
  )

The result is a data frame with one row containing the requested summaries.

group_by(): Do the Above by Group

This is where things get powerful. group_by() doesn’t change the data — but it tells subsequent operations to apply per group.

yields |>
  group_by(region) |>
  summarise(
    mean_yield = mean(yield_bu_acre, na.rm = TRUE),
    sd_yield = sd(yield_bu_acre, na.rm = TRUE),
    n = n()
  )

The result is a data frame with one row per region, containing the summary statistics for that region. This is the R equivalent of a PivotTable. You can group by multiple columns:

yields |>
  group_by(region, variety) |>
  summarise(mean_yield = mean(yield_bu_acre, na.rm = TRUE))

After you are done summarising, it is good practice to ungroup() — otherwise the grouping persists and can surprise you later:

yields |>
  group_by(region) |>
  summarise(mean_yield = mean(yield_bu_acre, na.rm = TRUE)) |>
  ungroup()

3.5 Unit Conversion: A Worked Example

Canadian ag data often comes in mixed units. Canola yields are traditionally reported in bushels per acre (bu/ac) in Canada, but metric tonnes per hectare (t/ha) in global datasets. The conversion for canola is approximately:

\[ \text{yield (t/ha)} = \text{yield (bu/ac)} \times 0.0560 \]

This factor comes from two facts: a bushel of canola weighs about 22.68 kg (canola’s standard 50-lb bushel), and an acre is about 0.4047 ha. So one bu/ac is \(22.68 \div 0.4047 \div 1000 \approx 0.0560\) t/ha. (The exact factor depends on the crop’s bushel weight — a wheat or corn bushel is 60 or 56 lb and gives a different number — so in a real analysis you would look up the right bushel weight for your crop and cite your source.)

yields_converted <- yields |>
  mutate(yield_t_ha = yield_bu_acre * 0.0560)

Now suppose your dataset is really messy — some rows are in bu/ac and some are already in t/ha, and there is a units column telling you which. You can use case_when:

yields_converted <- yields |>
  mutate(
    yield_t_ha = case_when(
      units == "bu/ac" ~ yield_bu_acre * 0.0560,   # convert the bu/ac rows
      units == "t/ha"  ~ yield_bu_acre,             # already metric: leave as-is
      TRUE             ~ NA_real_                    # anything else becomes missing
    )
  )

(Here we assume the value lives in a yield_bu_acre column and a separate units column records the unit of each row. NA_real_ is just R’s way of writing a missing numeric value — the numeric version of NA.)

case_when is the R analogue of nested IFs in Excel. Each line is a condition and a value. The first matching condition wins. The TRUE ~ ... line is the catch-all “else.” Use case_when whenever you have multi-way conditions; it is much cleaner than nested if_else.

3.6 Handling Missing Values

In R, missing values are represented as NA. They are sneaky because most operations propagate them — any operation involving NA returns NA:

c(1, 2, NA) + 1   # c(2, 3, NA)
mean(c(1, 2, NA)) # NA

This is actually the right default behavior — silently treating missing values as zero (as Excel often does) is a recipe for bugs. But it means you need to deal with NAs explicitly.

A few patterns:

# Check for missing values
is.na(yields$yield)              # logical vector
sum(is.na(yields$yield))          # count of NAs
mean(is.na(yields$yield))         # fraction of NAs

# Remove rows with any missing values
yields_clean <- yields |> drop_na()

# Remove rows with missing values in specific columns
yields_clean <- yields |> drop_na(yield_bu_acre)

# Compute a statistic ignoring NAs
mean(yields$yield_bu_acre, na.rm = TRUE)

# Replace NAs with a value
yields |> mutate(yield_bu_acre = replace_na(yield_bu_acre, 0))

When is it safe to drop missing values and when is it dangerous? It depends on why they are missing. If data is missing completely at random (e.g., a sensor failed arbitrarily), dropping missing values gives you unbiased estimates, just with less data. If data is missing for a reason related to the value itself (e.g., only high-yielding fields were reported), dropping them can bias your results — you will systematically overestimate yields.

The honest answer to “what should I do with missing values” is almost always: think about why they are missing, and document what you decided to do. There is no universal fix.

3.7 Dates with lubridate

Dates are a notorious source of bugs in every programming language. Is 01/02/2023 January 2 (U.S. order) or February 1 (European order)? Is 9:00 AM Regina time the same as 9:00 AM Saskatoon time? Does this dataset account for daylight saving time? (Don’t get me started on leap seconds.)

The tidyverse has a package called lubridate that makes working with dates tolerable. It is loaded automatically with library(tidyverse) in recent versions; if not, load it explicitly.

library(lubridate)

# Parse date strings
ymd("2025-09-15")        # year-month-day
dmy("15/09/2025")        # day-month-year
mdy("09/15/2025")        # month-day-year

# Extract components
today()                        # today's date
year(ymd("2025-09-15"))        # 2025
month(ymd("2025-09-15"))       # 9
day(ymd("2025-09-15"))         # 15
wday(ymd("2025-09-15"), label = TRUE)  # weekday name

# Arithmetic
ymd("2025-09-15") + days(30)   # date 30 days later
ymd("2025-09-15") + months(3)  # careful: months have variable length
today() - ymd("2025-09-15")    # difference in days

A practical workflow: when you read a CSV with a date column, readr will often guess the wrong type. Force it:

yields <- read_csv("yields.csv", col_types = cols(
  planting_date = col_date("%Y-%m-%d")
))

Or parse after reading:

yields <- yields |>
  mutate(planting_date = ymd(planting_date))

3.8 Combining It All

Let’s do something realistic. Suppose we want to answer: “For each region, what was the mean yield (in t/ha) of fields larger than 100 acres, in 2025, for the top three varieties by total production?”

yields |>
  filter(year == 2025, acres > 100) |>
  mutate(yield_t_ha = yield_bu_acre * 0.0560,
         total_tonnes = yield_t_ha * acres * 0.4047) |>
  group_by(variety) |>
  mutate(variety_total = sum(total_tonnes, na.rm = TRUE)) |>
  ungroup() |>
  filter(dense_rank(desc(variety_total)) <= 3) |>
  group_by(region, variety) |>
  summarise(mean_yield_t_ha = mean(yield_t_ha, na.rm = TRUE),
            n_fields = n(),
            .groups = "drop") |>
  arrange(region, desc(mean_yield_t_ha))

This is a lot. Don’t panic. Read it top to bottom and notice how each step does one thing:

  1. Filter to 2025 and fields over 100 acres.
  2. Compute metric yield and total production for each field.
  3. For each variety, compute its total production across all its fields. Here we use group_by(variety) followed by mutate (not summarise) on purpose: summarise would collapse each variety down to a single row, but mutate keeps all the rows and just adds the group total as a new column on each one. (After a grouped operation, call ungroup() to remove the grouping so later steps behave normally.)
  4. Keep the top three varieties by total production. desc(variety_total) sorts largest-first; dense_rank(...) assigns rank 1 to the biggest, 2 to the next, and so on; and <= 3 keeps ranks 1, 2, and 3.
  5. Regroup by region and variety and summarise down to one row per group, computing the mean yield and the number of fields (n() counts the rows in each group). The .groups = "drop" argument just tells summarise to return an ordinary, ungrouped data frame afterward (without it, R prints a chatty message about how it left the grouping).
  6. Sort the final table.

Do not worry about writing something this long yet — the point is that each line is one small, readable step. Writing it as a pipeline makes the logic visible. Compare with how you would do this in Excel — probably three PivotTables, a manual filter, and a lot of copy-pasting between sheets.

3.9 Renaming and Cleaning Column Names

Real-world datasets often have terrible column names: Yield (bu/ac), REGION_NAME, X1. These will work in R but are annoying to type and easy to mistype. Clean them up early:

yields |> rename(yield_bu_ac = "Yield (bu/ac)",
                 region = REGION_NAME)

For wholesale renaming, janitor::clean_names() is invaluable. (This is from the janitor package, not the tidyverse. Install it once with install.packages("janitor").)

library(janitor)
yields <- read_csv("messy_data.csv") |> clean_names()

clean_names converts everything to lowercase with underscores, strips special characters, and generally makes your column names nice. I use it on almost every dataset I read in.

3.10 Test Bank Sample

  1. (Concept.) What does “tidy data” mean? Give an example of untidy data and how to fix it.
  2. (dplyr.) Write a dplyr pipeline that filters yields to fields in the South region, computes yield in t/ha, and sorts descending by yield.
  3. (group_by.) Write a pipeline that computes the mean and standard deviation of yield for each variety, for fields larger than 50 acres.
  4. (case_when.) A dataset has a yield column and a units column. Write code to create a yield_t_ha column that converts as needed.
  5. (Missing values.) Why is it dangerous to blindly drop missing values? Under what conditions is it safe?
  6. (Dates.) You have a character column planting_date in the format "2025-04-15". Write code to parse it as a date and extract the month.

3.11 Practice Exercises

Use the field_yields.csv dataset from Section 3.2 for these.

  1. Read in field_yields.csv and reproduce a table of summary statistics (mean, median, and standard deviation of yield) using dplyr verbs.
  2. Filter to one region and one year, and find the variety with the highest mean yield.
  3. Create a new column that flags whether each field is “above average” in yield for its region.
  4. The dataset has a few fields with a missing (NA) yield. Find them with filter(is.na(yield_bu_acre)), then compute the mean yield with and without na.rm = TRUE and explain the difference.
  5. Add a yield_t_ha column converting yield_bu_acre to tonnes per hectare (× 0.0560), then sort the fields from highest to lowest metric yield.