Wrangling with dplyr

Optional · not taught in class

Slides · Live script · Source

Subsetting with better grammar: the dplyr verbs, chained with a pipe into lines you can read aloud. By the end, the two-month hole in the regulatory series is measured exactly, in three lines.

What this page is

This page is not on the calendar. It is written, runnable, and about one class meeting of material.

Everything on this page has a base R equivalent, and the verb table at the end gives the translation. dplyr is the data-manipulation package of the tidyverse, a family of packages that share this grammar, and most R code you meet outside this course uses it. Read the page when you meet that code, or when a base R line gets too long to read aloud.

Objectives

By the end of this page you can:

  • Translate the base-R subsetting you already know into dplyr verbs.
  • Build grouped summaries with group_by() and summarize().
  • Read a pipe chain aloud as a sentence.
  • Predict row and group counts before running.
  • Join two tables on a shared key with left_join(), and check the row count before and after.

The first package

Everything so far ran on base R, the toolkit every R installation ships with. A package is R code somebody else wrote, tested, and shared: functions, help pages, sometimes data. dplyr is the standard package for wrangling data frames, and it is the first one this course needs.

dplyr belongs to a coordinated family of packages called the tidyverse. The course adds members as it needs them.

One command fetches it from CRAN, the archive R installs packages from. Type it in the console, not in a script:

install.packages("dplyr")

The console fills with a minute of download messages and ends with a line saying the package was installed. That is the whole install. You do it once per machine, the way you installed R itself once. The command does not belong in a script: a script that carries an install line reinstalls the package on every run.

Load it, every session

Installing put dplyr on the machine. library() puts it into the current R session:

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

The block about masked objects is information, not an error. dplyr provides functions named filter() and lag(), and base R already had functions with those names. Loading dplyr puts the new ones in front, and the message lists what moved. You will see it at every library(dplyr).

The rule: install once per machine, load in every session, and every script opens with its library() lines.

Rebuild the frame

Reading stays base R: the same read, the same repairs, the same checks.

pm <- read.csv("data/epa_pm25_compton_2025.csv",
               colClasses = c("Site.ID"          = "character",
                              "State.FIPS.Code"  = "character",
                              "County.FIPS.Code" = "character"))
names(pm)[names(pm) == "Daily.Mean.PM2.5.Concentration"] <- "pm25"
pm$date <- as.Date(pm$Date, format = "%m/%d/%Y")

nrow(pm)   # the whole year, both instruments: 662 rows
#> [1] 662

dplyr changes nothing about reading a file. read.csv(), the identifier protection, the rename, the date conversion, and the quick checks are unchanged. What dplyr replaces is everything that came after this point with brackets and $. If data/ is missing the file, the data folder has it, alongside the provenance notes.

filter(): keep rows

Base R cuts the winter window with brackets. Same subset, first verb:

# base R:  pm[pm$date <= as.Date("2025-02-28") &
#                pm$POC == 1 & pm$AQS.Parameter.Code == 88101, ]

one <- filter(pm,
              date <= as.Date("2025-02-28"),
              POC == 1,
              AQS.Parameter.Code == 88101)

nrow(one)   # must be 59: one row per calendar day
#> [1] 59

filter() keeps the rows where every condition holds. Three things changed from the base line. The data frame comes first, then the conditions. Column names stand bare, because filter() looks them up inside pm, so no pm$ and no brackets. Commas join conditions the way & did; | still spells or, inside a single condition. The answer did not change: 59 rows, one per calendar day of the window.

select(): keep columns

Base needed two coordinates and quoted names. select() takes bare names, in the order you want them:

# base R:  one[, c("date", "pm25")]

head(select(one, date, pm25), 3)
#>         date pm25
#> 1 2025-01-01 53.2
#> 2 2025-01-02 33.6
#> 3 2025-01-03 27.7

A minus sign drops instead of keeps: select(one, -Date) returns every column except the raw text date. The result is still a data frame, which is why head() works on it unchanged.

mutate(): add columns

mutate() adds columns computed from existing ones. Two at once here, and both get used below. Before running it, set the expectation: four days in this window clear 35 µg/m³, the 24-hour standard set by EPA (the US environmental agency) — January 1, 8, 9, and 10. So a TRUE/FALSE column for pm25 > 35 must sum to 4.

# base R:  one$above35 <- one$pm25 > 35

one <- mutate(one,
              above35 = pm25 > 35,
              month   = format(date, "%b"))

head(select(one, date, pm25, above35, month), 3)
#>         date pm25 above35 month
#> 1 2025-01-01 53.2    TRUE   Jan
#> 2 2025-01-02 33.6   FALSE   Jan
#> 3 2025-01-03 27.7   FALSE   Jan
sum(one$above35)   # must be 4
#> [1] 4

Each new column is name = expression, computed inside the data frame, so pm25 and date stand bare. format(date, "%b") pulls the short month name, "Jan", out of a date: the same format strings, run in reverse. The sum confirms the count, with TRUE counting as 1.

arrange(): sort rows

arrange() sorts rows by a column. desc() flips it to descending:

# base R:  head(one[order(-one$pm25), c("date", "pm25")], 5)

worst <- arrange(one, desc(pm25))
head(select(worst, date, pm25), 3)
#>         date pm25
#> 1 2025-01-01 53.2
#> 2 2025-01-09 47.7
#> 3 2025-01-08 44.6

January 1 is back on top at 53.2, exactly where the base sort put it. Notice the cost of working verb by verb: worst is a stored intermediate that nothing else will ever use. The base line avoided the intermediate by nesting the subset inside head(), and paid in readability. The pipe removes that trade-off.

The pipe

|> passes whatever is on its left to the function on its right, as that function’s first argument:

nrow(filter(pm, POC == 1))         # nested: read inside out
#> [1] 301
pm |> filter(POC == 1) |> nrow()   # piped: read left to right
#> [1] 301

Both lines run the same computation and print the same 301. The piped one reads in the order things happen: start from pm, keep the regulatory instrument’s rows, count them. Read |> aloud as “then”. Every dplyr verb takes the data frame as its first argument precisely so chains like this work.

Two practical notes. RStudio types the pipe for you with Cmd+Shift+M (Mac) or Ctrl+Shift+M (Windows), after you tick “Use native pipe operator” once under Tools, Global Options, Code. And you will meet an older pipe, %>%, all over existing code and AI-generated code. It does nearly the same job. Read it the same way; write |>.

The chain, end to end

# base R:  head(one[order(-one$pm25), c("date", "pm25")], 5)

pm |>
  filter(date <= as.Date("2025-02-28"), POC == 1, AQS.Parameter.Code == 88101) |>
  arrange(desc(pm25)) |>
  select(date, pm25) |>
  head(5)
#>         date pm25
#> 1 2025-01-01 53.2
#> 2 2025-01-09 47.7
#> 3 2025-01-08 44.6
#> 4 2025-01-10 42.2
#> 5 2025-01-02 33.6

Read it aloud: take the year, keep the winter window from the regulatory sampler, sort worst first, keep the date and the reading, show five. That is one sentence. The five rows match the base sort to the decimal: 53.2, 47.7, 44.6, 42.2, 33.6.

The chain also changes how you edit. Each verb sits on its own line, so adding a step later means inserting a line, not rebuilding a bracket expression from the inside out.

group_by() and summarize()

group_by() declares groups; every summarize() after it computes once per group. Before running the block below, write down what it should print. You already have some of the evidence. Days: the window has no gaps, so January contributes 31 rows and February 28. Means against medians: all four days above the standard belong to January, and in a right-skewed month the mean sits above the median, so January’s mean should clear its median with room and February’s should sit close to its median. Write your numbers down, then run.

one |>
  group_by(month) |>
  summarize(days        = n(),
            mean_pm25   = mean(pm25),
            median_pm25 = median(pm25))
#> # A tibble: 2 x 4
#>   month  days mean_pm25 median_pm25
#>   <chr> <int>     <dbl>       <dbl>
#> 1 Feb      28      13.4        12.6
#> 2 Jan      31      21.3        16.9

Reading the grouped result

Reconcile line by line. The days column reads 31 and 28: n() takes no arguments and counts the rows of whatever group it stands in, and it only works inside summarize() and friends. The medians come back at 16.9 and 12.6. January’s mean, 21.3, sits far above its own median because the four event days pull it up; February’s, 13.4, barely moves off its median. (summarise() with an s does the same thing; both spellings work.)

The header row is new. A tibble is dplyr’s flavor of data frame: it behaves like the data frames you know and prints with its dimensions and column types attached. Grouped summaries come back as tibbles.

And one thing nobody predicted: February printed before January. That is not a dplyr bug. month is text, and text sorts alphabetically. It is "10" before "9", back again.

Factors put months in order

month.abb   # a vector R ships with
#>  [1] "Jan" "Feb" "Mar" "Apr" "May" "Jun" "Jul" "Aug" "Sep" "Oct" "Nov" "Dec"
one <- mutate(one, month = factor(month, levels = month.abb))

one |>
  group_by(month) |>
  summarize(days = n(), median_pm25 = median(pm25))
#> # A tibble: 2 x 3
#>   month  days median_pm25
#>   <fct> <int>       <dbl>
#> 1 Jan      31        16.9
#> 2 Feb      28        12.6

A factor is a categorical column with a level table. Here the level table does real work: levels = month.abb stores the categories in calendar order, and every grouped result now follows that order. This is what factors are for. Categories usually have an order that is not alphabetical: months, damage classes, education levels, income brackets. Set the levels once and every table downstream prints in the order you meant. (The standing warning: as.numeric() on a factor returns level codes, not the labels.)

count(): the counting shorthand

# base R:  table(pm$POC, pm$AQS.Parameter.Code)

pm |> count(POC, AQS.Parameter.Code)
#>   POC AQS.Parameter.Code   n
#> 1   1              88101 301
#> 2   3              88101   7
#> 3   3              88502 354

count(x) is group_by(x) plus summarize(n = n()) in one verb. Give it two columns and it counts every combination that occurs. Base R answers this question with table(); count() answers with a data frame, so the answer itself can go through more verbs. The numbers match it: 301 regulatory days, 7 stray rows under the wrong code, 354 days from the continuous instrument. They sum to 662, every row in the file.

The missing spring, measured

The regulatory series has 301 rows where a full year has 365. A chain you can now read says which months the missing days fall in:

Write down the twelve counts this should print. The evidence: the count() above says the regulatory series has 301 rows, and a full month has 28 to 31 days, so 64 days are missing somewhere. Your twelve numbers must sum to 301. Decide where you think the gap falls before you look.

year <- pm |>
  filter(POC == 1, AQS.Parameter.Code == 88101) |>
  mutate(month = factor(format(date, "%b"), levels = month.abb))

The twelve counts

year |> count(month)
#>    month  n
#> 1    Jan 31
#> 2    Feb 28
#> 3    Mar 30
#> 4    Apr  2
#> 5    May  1
#> 6    Jun 28
#> 7    Jul 31
#> 8    Aug 31
#> 9    Sep 27
#> 10   Oct 31
#> 11   Nov 30
#> 12   Dec 31

Reading the coverage table

Reconcile against your prediction. April and May land at 2 days and 1: the sampler was down for nearly two months. The counts sum to 301, and a complete year has 365 days, so the regulatory series is missing 64. Of those, 58 are the spring outage: April is short 28 days and May 30.

The other six days are the news. If you predicted a full 31 for March, the table corrects you by one day: March logged 30. June logged 28 of 30, September 27 of 30. A count is exact, and it turned up six missing days that no summary of the whole year would have shown.

One consequence, computed. The series’ “2025 annual mean” is an average of the 301 days that exist:

mean(year$pm25)   # "the 2025 annual mean" -- of the days that exist
#> [1] 13.38405

Is 13.4 high, low, or fine? The missing days decide. The site’s continuous instrument ran through the spring, and its numbers say April and May were the cleanest stretch of its year:

other <- pm |> filter(POC == 3, AQS.Parameter.Code == 88502)

mean(other$pm25)   # its full-year mean
#> [1] 12.27881
other |>
  mutate(month = format(date, "%b")) |>
  filter(month == "Apr" | month == "May") |>
  summarize(days = n(), mean_pm25 = mean(pm25))
#>   days mean_pm25
#> 1   61  10.13115

The continuous instrument averages 12.3 over its year and 10.1 over April and May. The two instruments measure by different methods, so the levels are not exactly comparable. The direction is: the regulatory series is missing precisely the months that would have pulled its average down, so its 13.4 leans high. Every mean is a mean of the days that are there. You can now put numbers on what that costs.

Reading the weather file

Readings in one table, the weather in another. The weather file carries three lines of station metadata before its header, so skip = 3:

wx <- read.csv("data/la-weather-dec2024-feb2025.csv", skip = 3)
names(wx)[names(wx) == "precipitation_sum..mm."] <- "rain"
wx$date <- as.Date(wx$time)

nrow(one); nrow(wx)   # count BEFORE: 59 days of readings, 90 days of weather
#> [1] 59
#> [1] 90

The read is base R again: skip, a rename, a date conversion. The weather runs December through February, the readings January through February, and both tables carry a date column. That shared column is the key.

left_join(): joining two tables

# base R:  merge(one, wx, by = "date", all.x = TRUE)

both <- left_join(select(one, date, pm25),
                  select(wx, date, rain),
                  by = "date")
nrow(both)            # and after: 59
#> [1] 59
sum(is.na(both$rain))
#> [1] 0

by names the column the two tables share. left_join() keeps every row of the first table and glues on the matching columns of the second; a day with no weather row would come back with rain as NA. Fifty-nine rows in, fifty-nine out, and no NA: every day of the window found its weather. Write down the row count you expect before you run a join, and compare. That check is the whole discipline of joining.

Rain days against dry days

The join is what makes the next question one chain:

both |>
  mutate(wet = rain > 0) |>
  group_by(wet) |>
  summarize(days = n(), mean_pm25 = mean(pm25))
#> # A tibble: 2 x 3
#>   wet    days mean_pm25
#>   <lgl> <int>     <dbl>
#> 1 FALSE    48     20.1 
#> 2 TRUE     11      6.43

Eleven days of the window had rain. Their mean PM2.5 is 6.4, against 20.1 on the 48 dry days. The biggest storm, 37.9 mm on February 13, sits under a reading of 3.2. Rain scrubs the air, and the number that says so took one join and one chain.

How a join drops and multiplies rows

partial <- filter(wx, date != as.Date("2025-01-07"))   # pretend one day is missing

nrow(inner_join(one, partial, by = "date"))   # dropped
#> [1] 58
nrow(left_join(one, partial, by = "date"))    # kept, with NA
#> [1] 59

Rows can disappear. inner_join() keeps only keys present in both tables, so a day missing from the weather takes its reading with it: 58 rows where you asked about 59, with no error and no warning. left_join() keeps every row of the left table and fills the missing side with NA, which is almost always what you want: an NA you can see beats a row you cannot.

Rows can also multiply. If the right table has two rows for one date, every matching row on the left is repeated to pair with each. Ask for 59 and get 60. Run unique() on the right table’s key before you trust a join, and count after it.

Keys have to be the same type. Ask dplyr to join a Date column to the same dates stored as text and it stops with an error naming both types. That error is a favor: it is the one join failure that does not pass silently.

The one-line stopwatch

One more habit. Wrap any line in system.time() and R reports how long it took:

system.time(read.csv("data/epa_pm25_compton_2025.csv"))
#>    user  system elapsed 
#>   0.003   0.000   0.004

Read the elapsed column: the seconds you actually waited. For this file the answer is effectively zero. Satellite rasters, statewide parcel tables, and folders of hourly files make single lines take minutes. The habit costs one line: when something feels slow, wrap it once, note the number, and compare after you change something.

Quick reference

The verbs, with the base R you already know

dplyr It keeps or makes Base equivalent
filter(d, x > 1, g == "a") Rows where all conditions hold d[d$x > 1 & d$g == "a", ]
select(d, date, pm25) The named columns; -name drops d[, c("date", "pm25")]
mutate(d, new = expr) Adds computed columns d$new <- expr
arrange(d, desc(x)) Rows sorted; desc() descending d[order(-d$x), ]
group_by(d, g) Declares groups for what follows no separate step
summarize(d, m = mean(x)) One row per group tapply(d$x, d$g, mean)
count(d, g) Grouped row counts table(d$g)
left_join(a, b, by = "k") Rows of a, matching columns of b glued on merge(a, b, by = "k", all.x = TRUE)

The pipe and the helpers

The pipe itself: x |> f() |> g() passes each left side into the next call as its first argument. Read it as “then”.

Code What it does
n() Rows in the current group; only inside summarize() and friends
desc(x) Descending order inside arrange()
head(d, 5) Still base R, still works at the end of a chain
x %in% c("a", "b") Is each value one of these; tidier than or-chains of ==
factor(x, levels = month.abb) Categories in the order you mean, not alphabetical

Things that bite

Symptom Cause
“Masked objects” message at library(dplyr) Normal. dplyr’s filter() and lag() stand in front of the base ones
filter() errors before library(dplyr) The package is not loaded in this session
filter(d, x = 1) errors = assigns; the test is ==
The chain printed, but nothing changed You never assigned; put d <- in front of the chain
Reinstalls on every run install.packages() is in the script; it belongs in the console
Months print Apr, Aug, Dec, … Text sorts alphabetically; make a factor with levels = month.abb
n() errors on its own It only counts inside summarize() and friends
A join returns fewer rows than the left table inner_join() dropped the keys it could not match; use left_join()
A join returns more rows than the left table A duplicated key on the right; unique() it first

Links