The tidyverse and dplyr

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")
few <- as_tibble(loans) |>
  filter(member %in% c("Farid Karimi", "Ben Okafor"))

# Untidy: the years are column headings, and the cells hold a count.
table(few$member, format(few$loan_date, "%Y"))
#>
#>                2025 2026
#>   Ben Okafor      5    0
#>   Farid Karimi    1    1

# Tidy: one row per member and year, one column per variable.
few |> count(member, year = format(loan_date, "%Y"))
#> # A tibble: 3 × 3
#>   member       year      n
#>   <chr>        <chr> <int>
#> 1 Ben Okafor   2025      5
#> 2 Farid Karimi 2025      1
#> 3 Farid Karimi 2026      1

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")
books_tbl <- as_tibble(books)

# [ with one column: a vector for a data frame, a tibble for a tibble.
class(books[, "year"])
#> [1] "integer"
class(books_tbl[, "year"])
#> [1] "tbl_df"     "tbl"        "data.frame"

# $ matches a partial name on a data frame, never on a tibble.
books$ye[1:3]
#> [1] 1974 1813 2019
tryCatch(books_tbl$ye, warning = conditionMessage)
#> [1] "Unknown or uninitialised column: `ye`."

# Only length-one values are recycled; a column may use an earlier one.
tryCatch(tibble(a = 1:3, b = 1:2), error = conditionMessage)
#> [1] "Tibble columns must have compatible sizes.\n• Size 3: Existing data.\n• Size 2: Column `b`.\nℹ Only values of size one are recycled."
tibble(x = 1:3, y = x * 2)
#> # A tibble: 3 × 2
#>       x     y
#>   <int> <dbl>
#> 1     1     2
#> 2     2     4
#> 3     3     6
tryCatch(data.frame(x = 1:3, y = x * 2), error = conditionMessage)
#> [1] "object 'x' not found"

# Row names are dropped on conversion; ask for them as a column.
named <- data.frame(pages = c(387, 432), row.names = c("dispossessed", "pride"))
as_tibble(named, rownames = "key")
#> # A tibble: 2 × 2
#>   key          pages
#>   <chr>        <dbl>
#> 1 dispossessed   387
#> 2 pride          432

# Names are taken as given, with no check.names mangling.
names(data.frame(`my col` = 1))
#> [1] "my.col"
names(tibble(`my col` = 1))
#> [1] "my col"

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")
books_tbl <- as_tibble(books)

# glimpse(): one line per column, so wide tables fit.
glimpse(books_tbl)
#> Rows: 16
#> Columns: 6
#> $ title  <chr> "The Dispossessed", "Pride and Prejudice", "  the quiet streets…
#> $ author <chr> "Ursula K. Le Guin", "Jane Austen", "Mara Quill", "Mara Quill",…
#> $ isbn   <chr> "978-0-306-40615-7", "978-0-14-143951-8", "9780001000100", "978…
#> $ year   <int> 1974, 1813, 2019, 2021, 2016, 2008, NA, 1999, 2012, 2023, 2005,…
#> $ genre  <chr> "Science fiction", "Classic", "Fiction", "fiction", "Mystery", …
#> $ pages  <int> 387, 432, 214, 198, 301, NA, 96, 280, 352, 256, 88, 410, 175, 3…

# print() takes n and width; the footer says what was left out.
print(books_tbl, n = 3)
#> # A tibble: 16 × 6
#>   title                  author            isbn               year genre   pages
#>   <chr>                  <chr>             <chr>             <int> <chr>   <int>
#> 1 "The Dispossessed"     Ursula K. Le Guin 978-0-306-40615-7  1974 Scienc…   387
#> 2 "Pride and Prejudice"  Jane Austen       978-0-14-143951-8  1813 Classic   432
#> 3 "  the quiet streets " Mara Quill        9780001000100      2019 Fiction   214
#> # ℹ 13 more rows

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")
books <- as_tibble(books)
books$genre <- tolower(trimws(books$genre))

# Conditions separated by commas are combined with &.
books |> filter(genre == "fiction", year >= 2018) |> select(title, year)
#> # A tibble: 4 × 2
#>   title                   year
#>   <chr>                  <int>
#> 1 "  the quiet streets "  2019
#> 2 "Loud Roofs"            2021
#> 3 "THE LONG TABLE"        2023
#> 4 "Ten Winters"           2022

# NA is dropped by filter(); filter_out() names the rows to drop instead and
# keeps the NA rows (dplyr 1.2.0).
books |> filter(year > 2000) |> nrow()
#> [1] 12
books |> filter_out(year > 2000) |> nrow()
#> [1] 4

# when_any() combines conditions with | (dplyr 1.2.0).
books |> filter(when_any(year < 1900, pages < 100)) |> pull(title)
#> [1] "Pride and Prejudice"   "Garden of Small Fires" "Paper Moons"

# if_any() and if_all() build one condition from several columns.
books |> filter(if_any(c(year, pages), is.na)) |> pull(title)
#> [1] "A Map of Salt"         "Garden of Small Fires" "Ten Winters"

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")
books <- as_tibble(books)
books$genre <- tolower(trimws(books$genre))

# arrange(): desc() reverses a column; NA always comes last.
books |> arrange(genre, desc(year)) |> select(genre, year, title) |> head(3)
#> # A tibble: 3 × 3
#>   genre    year title
#>   <chr>   <int> <chr>
#> 1 classic  1999 The Lamp at Noon
#> 2 classic  1813 Pride and Prejudice
#> 3 fiction  2023 THE LONG TABLE

# Text is sorted in the C locale (capitals first); .locale asks for a language.
tibble(x = c("b", "A", "a", "B")) |> arrange(x) |> pull(x)
#> [1] "A" "B" "a" "b"
tibble(x = c("b", "A", "a", "B")) |> arrange(x, .locale = "en") |> pull(x)
#> [1] "a" "A" "b" "B"

# distinct() and count() (with sort); slice_max() with by = for the longest
# book of each genre.
books |> distinct(genre) |> nrow()
#> [1] 6
books |> count(genre, sort = TRUE) |> head(3)
#> # A tibble: 3 × 2
#>   genre               n
#>   <chr>           <int>
#> 1 fiction             4
#> 2 non-fiction         3
#> 3 science fiction     3
books |> slice_max(pages, n = 1, by = genre) |> select(genre, title, pages)
#> # A tibble: 6 × 3
#>   genre           title                 pages
#>   <chr>           <chr>                 <int>
#> 1 science fiction Notes on Gravity        410
#> 2 classic         Pride and Prejudice     432
#> 3 fiction         THE LONG TABLE          256
#> 4 mystery         Midnight Ledger         301
#> 5 non-fiction     Rivers of Glass         352
#> 6 poetry          Garden of Small Fires    96

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")
books <- as_tibble(books)

# select() takes names, ranges, exclusions and helpers.
books |> select(title:isbn) |> names()
#> [1] "title"  "author" "isbn"
books |> select(-isbn, -author) |> names()
#> [1] "title" "year"  "genre" "pages"
books |> select(where(is.numeric)) |> names()
#> [1] "year"  "pages"
books |> select(starts_with("t") | ends_with("s")) |> names()
#> [1] "title" "pages"

# A new name inside select() renames; rename() keeps the other columns.
books |> select(book = title, year) |> names()
#> [1] "book" "year"
books |> rename(n_pages = pages) |> names()
#> [1] "title"   "author"  "isbn"    "year"    "genre"   "n_pages"
books |> rename_with(toupper, .cols = c(title, author)) |> names()
#> [1] "TITLE"  "AUTHOR" "isbn"   "year"   "genre"  "pages"

# relocate() moves columns: to the front by default.
books |> relocate(genre) |> names()
#> [1] "genre"  "title"  "author" "isbn"   "year"   "pages"
books |> relocate(pages, .before = isbn) |> names()
#> [1] "title"  "author" "pages"  "isbn"   "year"   "genre"

# pull() extracts one column as a vector, optionally named.
books |> pull(pages, name = title) |> head(2)
#>    The Dispossessed Pride and Prejudice
#>                 387                 432

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")
books <- as_tibble(books)
books$genre <- tolower(trimws(books$genre))

# New columns go at the end; a later one can use an earlier one.
books |>
  mutate(age = 2026L - year, decade = year %/% 10L * 10L) |>
  select(title, year, age, decade) |>
  head(2)
#> # A tibble: 2 × 4
#>   title                year   age decade
#>   <chr>               <int> <int>  <int>
#> 1 The Dispossessed     1974    52   1970
#> 2 Pride and Prejudice  1813   213   1810

# .before and .after place a column, .keep chooses the columns that survive.
books |> mutate(pages_k = pages / 1000, .after = pages) |> names()
#> [1] "title"   "author"  "isbn"    "year"    "genre"   "pages"   "pages_k"
books |> mutate(age = 2026L - year, .keep = "used") |> names()
#> [1] "year" "age"
books |> mutate(age = 2026L - year, .keep = "none") |> names()
#> [1] "age"

# NULL drops a column. A summary is recycled next to every row of its group.
books |>
  mutate(isbn = NULL, vs_mean = pages - mean(pages, na.rm = TRUE),
         .by = genre) |>
  select(title, genre, pages, vs_mean) |>
  head(3)
#> # A tibble: 3 × 4
#>   title                  genre           pages vs_mean
#>   <chr>                  <chr>           <int>   <dbl>
#> 1 "The Dispossessed"     science fiction   387   14
#> 2 "Pride and Prejudice"  classic           432   76
#> 3 "  the quiet streets " fiction           214   -8.67

# A result of the wrong length is an error.
tryCatch(mutate(books, z = 1:3), error = conditionMessage)
#> [1] "ℹ In argument: `z = 1:3`.\nCaused by error:\n! `z` must be size 16 or 1, not 3."

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")
loans <- as_tibble(loans) |>
  mutate(
    member = na_if(tools::toTitleCase(trimws(member)), ""),
    held = as.integer(coalesce(return_date, shelf_today) - loan_date),
    overdue = held > loan_days
  )

# One row per group, sorted by the key; NA is a group of its own.
by_member <- loans |> group_by(member)
group_vars(by_member)
#> [1] "member"
by_member |>
  summarise(loans = n(), books = n_distinct(isbn)) |>
  tail(2)
#> # A tibble: 2 × 3
#>   member     loans books
#>   <chr>      <int> <int>
#> 1 Hugo Silva    10     8
#> 2 <NA>           1     1

# With two grouping columns the result stays grouped by the first, and a
# message says so; .groups = "drop" returns an ungrouped table.
by_year <- loans |>
  mutate(year = as.integer(format(loan_date, "%Y"))) |>
  group_by(year, overdue)
by_year |> summarise(loans = n())
#> `summarise()` has regrouped the output.
#> ℹ Summaries were computed grouped by year and overdue.
#> ℹ Output is grouped by year.
#> ℹ Use `summarise(.groups = "drop_last")` to silence this message.
#> ℹ Use `summarise(.by = c(year, overdue))` for per-operation grouping
#>   (`?dplyr::dplyr_by`) instead.
#> # A tibble: 5 × 3
#> # Groups:   year [3]
#>    year overdue loans
#>   <int> <lgl>   <int>
#> 1  2025 FALSE      22
#> 2  2025 TRUE       20
#> 3  2026 FALSE       5
#> 4  2026 TRUE       12
#> 5    NA NA          1
by_year |> summarise(loans = n(), .groups = "drop") |> class()
#> [1] "tbl_df"     "tbl"        "data.frame"

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")
books <- as_tibble(books)
books$genre <- tolower(trimws(books$genre))

# across(.cols, .fns): the same function on several columns, chosen here by
# type, with the new names built from a pattern.
books |>
  summarise(
    across(
      where(is.numeric),
      \(x) median(x, na.rm = TRUE),
      .names = "median_{.col}"
    ),
    .by = genre
  ) |>
  head(2)
#> # A tibble: 2 × 3
#>   genre           median_year median_pages
#>   <chr>                 <dbl>        <dbl>
#> 1 science fiction        2014          387
#> 2 classic                1906          356

# In mutate(), across() transforms columns in place.
books |> mutate(across(c(title, author), trimws)) |> pull(title) |> head(2)
#> [1] "The Dispossessed"    "Pride and Prejudice"

# Extra arguments in the dots of across() are deprecated since dplyr 1.1.0.
tryCatch(
  summarise(books, across(c(year, pages), mean, na.rm = TRUE)),
  warning = \(w) cat(conditionMessage(w), "\n")
)
#> There was 1 warning in `summarise()`.
#> ℹ In argument: `across(c(year, pages), mean, na.rm = TRUE)`.
#> Caused by warning:
#> ! The `...` argument of `across()` is deprecated as of dplyr 1.1.0.
#> Supply arguments directly to `.fns` through an anonymous function instead.
#>
#>   # Previously
#>   across(a:b, mean, na.rm = TRUE)
#>
#>   # Now
#>   across(a:b, \(x) mean(x, na.rm = TRUE))

# pick() passes selected columns on as a data frame.
books |>
  mutate(n_missing = rowSums(is.na(pick(year, pages)))) |>
  filter(n_missing > 0) |>
  select(title, n_missing)
#> # A tibble: 3 × 2
#>   title                 n_missing
#>   <chr>                     <dbl>
#> 1 A Map of Salt                 1
#> 2 Garden of Small Fires         1
#> 3 Ten Winters                   1

library(dplyr, warn.conflicts = FALSE)

# The day each loan started, was due and came back.
days <- tibble(start = c(1, 3, 7), due = c(22, 24, 28), back = c(21, 30, 8))

# max() collapses its input, so it needs rowwise(); c_across() collects the
# columns of one row. For sums, rowSums() on pick() is much faster.
days |>
  rowwise() |>
  mutate(
    latest = max(start, due, back),
    span = diff(range(c_across(start:back)))
  ) |>
  ungroup() |>
  mutate(total = rowSums(pick(start:back)))
#> # A tibble: 3 × 6
#>   start   due  back latest  span total
#>   <dbl> <dbl> <dbl>  <dbl> <dbl> <dbl>
#> 1     1    22    21     22    21    44
#> 2     3    24    30     30    27    57
#> 3     7    28     8     28    21    43

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")
ana <- as_tibble(loans) |>
  mutate(
    member = tools::toTitleCase(trimws(member)),
    held = as.integer(coalesce(return_date, shelf_today) - loan_date)
  ) |>
  filter(member == "Ana Ruiz", !is.na(loan_date)) |>
  arrange(loan_date)

# lag() and lead() look at the previous and the next row; cumsum() and
# row_number() give running values.
ana |>
  mutate(
    since_previous = as.integer(loan_date - lag(loan_date)),
    next_held = lead(held),
    running_days = cumsum(held),
    n_so_far = row_number()
  ) |>
  select(loan_id, since_previous, held, next_held, running_days, n_so_far) |>
  head(3)
#> # A tibble: 3 × 6
#>   loan_id since_previous  held next_held running_days n_so_far
#>   <chr>            <int> <int>     <int>        <int>    <int>
#> 1 L008                NA    10         4           10        1
#> 2 L014                41     4        37           14        2
#> 3 L015                28    37        23           51        3

# The ranking functions treat ties differently.
tibble(held = c(30, 10, 10, 20)) |>
  mutate(
    row_number = row_number(held),
    min_rank = min_rank(held),
    dense_rank = dense_rank(held),
    ntile2 = ntile(held, 2)
  )
#> # A tibble: 4 × 5
#>    held row_number min_rank dense_rank ntile2
#>   <dbl>      <int>    <int>      <int>  <int>
#> 1    30          4        4          3      2
#> 2    10          1        1          1      1
#> 3    10          2        1          1      1
#> 4    20          3        3          2      2

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")

# Two copies of one book: the key is not unique in y.
copies <- tibble(
  isbn = c("9780141439518", "9780141439518", "9780306406157"),
  copy = c("A", "B", "A")
)
two_books <- as_tibble(loans) |>
  filter(isbn %in% copies$isbn) |>
  select(loan_id, isbn)
nrow(two_books)
#> [1] 10

# Several loans per book in x and two copies in y: many-to-many. The join
# multiplies the rows (10 become 13) and warns.
tryCatch(left_join(two_books, copies, by = "isbn"), warning = conditionMessage)
#> [1] "Detected an unexpected many-to-many relationship between `x` and `y`.\nℹ Row 1 of `x` matches multiple rows in `y`.\nℹ Row 3 of `y` matches multiple rows in `x`.\nℹ If a many-to-many relationship is expected, set `relationship =\n  \"many-to-many\"` to silence this warning."

# relationship = states the expected cardinality and checks it.
tryCatch(
  left_join(two_books, copies, by = "isbn", relationship = "many-to-one"),
  error = conditionMessage
)
#> [1] "Each row in `x` must match at most 1 row in `y`.\nℹ Row 1 of `x` matches multiple rows in `y`."
two_books |>
  left_join(copies, by = "isbn", relationship = "many-to-many") |>
  nrow()
#> [1] 13

# With a unique key in y the row count is kept.
two_books |>
  left_join(
    distinct(copies, isbn, .keep_all = TRUE),
    by = "isbn",
    relationship = "many-to-one"
  ) |>
  nrow()
#> [1] 10

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")

# An inequality join: each loan takes the rate in force on its due date,
# that is, the closest start date that is not after it (a rolling join).
rates <- tribble(
  ~from_date,            ~fine_per_day,
  as.Date("2025-01-01"), 0.10,
  as.Date("2025-09-01"), 0.15,
  as.Date("2026-03-01"), 0.20
)
as_tibble(loans) |>
  mutate(due = loan_date + loan_days) |>
  left_join(rates, by = join_by(closest(due >= from_date))) |>
  select(loan_id, due, fine_per_day) |>
  slice(c(1, 40, 60))
#> # A tibble: 3 × 3
#>   loan_id due        fine_per_day
#>   <chr>   <date>            <dbl>
#> 1 L001    2025-02-04         0.1
#> 2 L040    2025-12-09         0.15
#> 3 L060    2026-06-18         0.2

# between() matches a value to a range; an unmatched row gets NA.
eras <- tribble(
  ~era,           ~from, ~to,
  "before 1900",   1000, 1899,
  "20th century",  1900, 1999,
  "21st century",  2000, 2099
)
as_tibble(books) |>
  left_join(eras, by = join_by(between(year, from, to))) |>
  count(era)
#> # A tibble: 4 × 2
#>   era              n
#>   <chr>        <int>
#> 1 20th century     2
#> 2 21st century    12
#> 3 before 1900      1
#> 4 <NA>             1

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")

# books: trim blanks, one spelling per author and genre, a bare ISBN-13
# (NA when the field was empty).
books <- as_tibble(books) |>
  mutate(
    title = trimws(title),
    author = gsub("\\s+", " ", trimws(author)),
    author = replace_values(author, "Le Guin, Ursula K." ~ "Ursula K. Le Guin"),
    genre = tolower(trimws(genre)),
    isbn = na_if(normalise_isbn(isbn), "")
  )
books |> count(genre)
#> # A tibble: 6 × 2
#>   genre               n
#>   <chr>           <int>
#> 1 classic             2
#> 2 fiction             4
#> 3 mystery             2
#> 4 non-fiction         3
#> 5 poetry              2
#> 6 science fiction     3
books |> filter(author == "Ursula K. Le Guin") |> pull(title)
#> [1] "The Dispossessed" "The Lamp at Noon"
books |> filter(is.na(isbn)) |> select(title, isbn)
#> # A tibble: 1 × 2
#>   title           isbn
#>   <chr>           <chr>
#> 1 Rivers of Glass <NA>

# loans: tidy the member names; an empty name is NA.
loans <- as_tibble(loans) |>
  mutate(member = na_if(tools::toTitleCase(trimws(member)), ""))
loans |> distinct(member) |> arrange(member) |> pull(member)
#> [1] "Ana Ruiz"      "Ben Okafor"    "Chloe Martin"  "Dev Patel"
#> [5] "Eva Lindqvist" "Farid Karimi"  "Grace Liu"     "Hugo Silva"
#> [9] NA

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")

# Days held: until the return date, or until shelf_today when the book is
# still out. A loan is overdue when it was held for more than loan_days.
loans <- as_tibble(loans) |>
  mutate(
    member = na_if(tools::toTitleCase(trimws(member)), ""),
    held = as.integer(coalesce(return_date, shelf_today) - loan_date),
    overdue = held > loan_days
  )
loans |> count(overdue)
#> # A tibble: 3 × 2
#>   overdue     n
#>   <lgl>   <int>
#> 1 FALSE      27
#> 2 TRUE       32
#> 3 NA          1

# Rows that need a look: a return before the loan, an impossible date.
loans |>
  filter(held < 0 | is.na(held)) |>
  select(loan_id, loan_date, return_date, held)
#> # A tibble: 2 × 4
#>   loan_id loan_date  return_date  held
#>   <chr>   <date>     <date>      <int>
#> 1 L012    2025-04-23 2025-04-20     -3
#> 2 L020    NA         2025-07-15     NA

# The overdue loans that have not come back, longest first.
loans |>
  filter(overdue, is.na(return_date)) |>
  arrange(desc(held)) |>
  select(loan_id, member, held) |>
  head(3)
#> # A tibble: 3 × 3
#>   loan_id member         held
#>   <chr>   <chr>         <int>
#> 1 L054    Chloe Martin     84
#> 2 L055    Eva Lindqvist    81
#> 3 L056    Dev Patel        71

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")

# Loans per month; the loan on 2025-02-30 has no date and is left out.
per_month <- as_tibble(loans) |>
  filter(!is.na(loan_date)) |>
  mutate(month = format(loan_date, "%Y-%m")) |>
  count(month, name = "loans")
per_month |> head(3)
#> # A tibble: 3 × 2
#>   month   loans
#>   <chr>   <int>
#> 1 2025-01     4
#> 2 2025-02     2
#> 3 2025-03     3
c(months = nrow(per_month), loans = sum(per_month$loans))
#> months  loans
#>     17     59
per_month |> slice_max(loans, n = 3, with_ties = FALSE)
#> # A tibble: 3 × 2
#>   month   loans
#>   <chr>   <int>
#> 1 2025-07     7
#> 2 2025-10     7
#> 3 2025-01     4

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")
books <- as_tibble(books) |>
  mutate(genre = tolower(trimws(genre)), isbn = normalise_isbn(isbn))
loans <- as_tibble(loans) |>
  mutate(
    held = as.integer(coalesce(return_date, shelf_today) - loan_date),
    overdue = held > loan_days
  ) |>
  inner_join(select(books, isbn, genre), by = "isbn")

# Overdue rate by genre; the loan with no overdue flag is dropped.
loans |>
  filter(!is.na(overdue)) |>
  summarise(
    loans = n(),
    n_overdue = sum(overdue),
    share = round(n_overdue / loans, 2),
    .by = genre
  ) |>
  arrange(desc(share), genre)
#> # A tibble: 6 × 4
#>   genre           loans n_overdue share
#>   <chr>           <int>     <int> <dbl>
#> 1 poetry              6         4  0.67
#> 2 classic             5         3  0.6
#> 3 non-fiction         5         3  0.6
#> 4 fiction            23        12  0.52
#> 5 mystery             4         2  0.5
#> 6 science fiction    15         7  0.47

# Mean duration by genre, for returned loans with a plausible duration.
loans |>
  mutate(duration = as.integer(return_date - loan_date)) |>
  filter(!is.na(duration), duration >= 0) |>
  summarise(n = n(), mean_days = mean(duration), .by = genre) |>
  arrange(genre)
#> # A tibble: 6 × 3
#>   genre               n mean_days
#>   <chr>           <int>     <dbl>
#> 1 classic             5      21.4
#> 2 fiction            18      19.3
#> 3 mystery             4      20.8
#> 4 non-fiction         5      21.2
#> 5 poetry              5      20
#> 6 science fiction    13      20.5

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")
loans <- as_tibble(loans) |>
  mutate(
    held = as.integer(coalesce(return_date, shelf_today) - loan_date),
    overdue = held > loan_days
  ) |>
  filter(!is.na(overdue))

# A name created in summarise() hides the column for the expressions after
# it: mean(overdue) is the mean of the number just made.
loans |> summarise(overdue = sum(overdue), share = mean(overdue))
#> # A tibble: 1 × 2
#>   overdue share
#>     <int> <dbl>
#> 1      32    32

# Give the summary a new name, or put the expression that needs the column
# first.
loans |> summarise(share = mean(overdue), n_overdue = sum(overdue))
#> # A tibble: 1 × 2
#>   share n_overdue
#>   <dbl>     <int>
#> 1 0.542        32

library(dplyr, warn.conflicts = FALSE)
source("scenario.R")

# Base R, as on the data frames page.
books$isbn <- normalise_isbn(books$isbn)
books$genre <- tolower(trimws(books$genre))
end <- loans$return_date
end[is.na(end)] <- shelf_today
loans$held <- as.integer(end - loans$loan_date)
loans$overdue <- loans$held > loan_days
joined <- merge(loans, books[c("isbn", "genre")], by = "isbn")
joined$n <- 1L
joined$late <- as.integer(joined$overdue)
by_genre <- aggregate(cbind(loans = n, overdue = late) ~ genre, joined, sum)

# dplyr: join once, drop the loans with no overdue flag, summarise by genre.
by_genre_tbl <- as_tibble(loans) |>
  inner_join(select(as_tibble(books), isbn, genre), by = "isbn") |>
  filter(!is.na(overdue)) |>
  summarise(loans = n(), overdue = sum(overdue), .by = genre) |>
  arrange(genre)

# The two agree.
by_genre_tbl
#> # A tibble: 6 × 3
#>   genre           loans overdue
#>   <chr>           <int>   <int>
#> 1 classic             5       3
#> 2 fiction            23      12
#> 3 mystery             4       2
#> 4 non-fiction         5       3
#> 5 poetry              6       4
#> 6 science fiction    15       7
all.equal(as.data.frame(by_genre_tbl), by_genre)
#> [1] TRUE