Mathew K Analytics

Lesson 3 · R Programming: Zero to Expert

Data Wrangling in R with the Tidyverse: dplyr, tidyr & More (Full Course) | R 103

R Programming: Zero to Expert. Goal: filter, reshape, join and summarise real data quickly. R script: 03tidyversewrangling.R. Download it from this page and…

⬇ Download R script⬇ R notebook (.ipynb)

What you'll learn

Files for this lesson

Save these in one folder, open the script in RStudio and set that folder as the working directory (Session → Set Working Directory → To Source File Location).

📓 Full notebook

Download .ipynb

Data Wrangling with the Tidyverse#

Video 3 of the series R Programming: Zero to Expert.

Goal: filter, reshape, join and summarise real data quickly.

R script: 03_tidyverse_wrangling.R. Download it from this page and open it in RStudio, or run this notebook with an R kernel (for example in Google Colab: Runtime → Change runtime type → R).

Quick recap: the base R way#

Base R can do everything we do today, but it gets noisy.

scores <- data.frame(
  student = c("Ana", "Ben", "Cara", "Dev"),
  maths   = c(78, 91, 65, 84),
  english = c(88, 72, 95, 60)
)
scores[scores$maths > 70, c("student", "maths")]

We get Ana, Ben and Dev, with maths scores of seventy eight, ninety one and eighty four. Cara is dropped because she scored sixty five. Notice the row numbers one, two and four. Base R keeps the original row names, which can be confusing later.

scores$average <- (scores$maths + scores$english) / 2
scores[order(-scores$average), ]

Ana comes first with an average of eighty three, then Ben with eighty one point five, Cara with eighty, and Dev last with seventy two.

What is the tidyverse?#

A family of packages that share one design and one grammar:

dplyr, tidyr, readr, stringr, lubridate, forcats, tibble, ggplot2.

Each function does one job, takes data first, returns new data.

library(tidyverse)

The console prints a block of messages. First, attaching core tidyverse packages, with a tick and a version number for each of the nine packages it loaded. Then a conflicts section. It tells us that dplyr's filter masks the filter function from the stats package, and the same for lag. These are just messages, not errors. Masking means that when we type filter, we now get the dplyr version, which is exactly what we want today.

Tibbles: data frames, upgraded#

scores_tbl <- as_tibble(scores)
scores_tbl

The first line says A tibble, four by four: four rows and four columns. Under each column name we see its type: chr for character and dbl for double, which means decimal numbers. That type row is one of the nicest features of tibbles. You always know what kind of data each column holds.

basket <- tibble(
  item  = c("Mug", "Lamp", "Notebook"),
  price = c(8.5, 24, 3.25),
  qty   = c(3, 1, 4),
  total = price * qty
)
basket

Three rows. The mug costs eight fifty times three, so its total is twenty five fifty. The lamp totals twenty four, and four notebooks come to thirteen.

staff <- tribble(
  ~name,    ~team,     ~salary,
  "Grace",  "Sales",   41000,
  "Hamid",  "Finance", 52000,
  "Ivy",    "Sales",   38500
)
staff

A tibble with three rows and three columns: name and team are character columns, and salary is a double.

scores$stud
scores_tbl$stud

The data frame quietly guessed that we meant student and returned all four names. That's called partial matching, and it can hide typos. The tibble refuses to guess. It returns NULL and warns us about an unknown or uninitialised column called stud. Much safer.

scores[, "maths"]
scores_tbl[, "maths"]

The data frame silently drops down to a plain vector: seventy eight, ninety one, sixty five, eighty four. The tibble stays a tibble, with four rows and one column. Tibbles are predictable: square brackets always give you back a tibble.

Our real dataset: Palmer penguins#

344 penguins from 3 islands in Antarctica, measured 2007-2009.

library(palmerpenguins)

Nothing prints, which is good news. The package loaded quietly.

penguins

Because it's a tibble, R prints only the first ten rows instead of flooding the console. The header says three hundred and forty four rows and eight columns. We can see species, island, bill length and depth, flipper length and body mass. Row four is full of NA, so this data has missing values, just like real life. At the bottom it tells us how many more rows there are, and lists any columns that didn't fit, such as sex and year.

glimpse(penguins)

Three hundred and forty four rows and eight columns. Species, island and sex are factors, shown as fct. The bill measurements are doubles, while flipper length, body mass and year are integers. glimpse is the first thing I run on any new dataset. In one screen you see every column, its type and its first few values.

The pipe: read code left to right#

x |> f(y) means f(x, y). Shortcut: Ctrl + Shift + M.

round(mean(penguins$body_mass_g, na.rm = TRUE))

penguins$body_mass_g |>
  mean(na.rm = TRUE) |>
  round()

Both versions print exactly the same answer: four thousand two hundred and two grams. Same result, but the piped version reads in the order things actually happen.

penguins$body_mass_g %>%
  mean(na.rm = TRUE) %>%
  round()

Same answer again, four thousand two hundred and two. In this series we'll use the native pipe, which has been built into R itself since version four point one.

scores_tbl
  |> nrow()

R sees the first line as a complete command and runs it on its own. Then it hits a line that starts with a pipe, and reports an error: unexpected pipe.

This one is meant to fail, so you can see the error message.

scores_tbl |>
  nrow()

Now we get four, the number of rows in our scores tibble.

dplyr verb 1: filter() keeps rows#

Every dplyr verb: data first, then what to do. Returns a tibble.

penguins |>
  filter(species == "Gentoo")

The header now says one hundred and twenty four rows, and every row we can see is a Gentoo penguin from Biscoe island. The original penguins table is unchanged. filter returned a brand new tibble, and since we didn't save it, it was just printed.

penguins |>
  filter(
    species == "Adelie",
    island == "Dream",
    body_mass_g > 4000
  )

Thirteen penguins pass all three tests. Every row is an Adelie from Dream, and the body masses range from four thousand one hundred up to four thousand six hundred and fifty grams.

penguins |>
  filter(island == "Biscoe" | flipper_length_mm > 220) |>
  nrow()

One hundred and sixty eight rows. Interesting: that's exactly the number of penguins on Biscoe, which tells us every penguin with flippers over two hundred and twenty millimetres lives on Biscoe anyway.

penguins |>
  filter(island %in% c("Dream", "Torgersen")) |>
  nrow()

One hundred and seventy six penguins live on Dream or Torgersen. Add the one hundred and sixty eight from Biscoe, and we're back to three hundred and forty four. Nice sanity check.

penguins |>
  filter(between(body_mass_g, 3000, 3200)) |>
  nrow()

Nineteen penguins weigh between three thousand and three thousand two hundred grams, including both ends of the range.

penguins |>
  filter(species = "Gentoo")

An error. dplyr says we detected a named input, and it even asks: did you mean species double equals Gentoo? A single equals sign names an argument, it doesn't compare.

This one is meant to fail, so you can see the error message.

penguins |>
  filter(is.na(sex))

Eleven penguins have no recorded sex. Two of them, rows one and eleven, are missing every measurement too. Keep that number eleven in mind. We'll come back to missing values when we meet tidyr.

gentoo <- penguins |>
  filter(species == "Gentoo", !is.na(body_mass_g))
nrow(gentoo)

One hundred and twenty three. One Gentoo had a missing body mass, so it was dropped.

Pause and try it 1#

Find female Chinstrap penguins heavier than 3,700 grams.

penguins |>
  filter(
    species == "Chinstrap",
    sex == "female",
    body_mass_g > 3700
  )

Six penguins, all Chinstraps from Dream island. The heaviest of them weighs four thousand one hundred and fifty grams.

dplyr verb 2: select() picks columns#

penguins |>
  select(species, island, body_mass_g)

Still three hundred and forty four rows, but now only three columns, in the order we asked for.

penguins |>
  select(-year, -sex) |>
  names()

penguins |>
  select(bill_length_mm:flipper_length_mm) |>
  names()

The first result has six names: everything except year and sex. The second has three: bill length, bill depth and flipper length, the columns sitting between our two endpoints.

penguins |>
  select(species, starts_with("bill")) |>
  head(3)

We get species plus both bill columns, length and depth. The first penguin has a bill thirty nine point one millimetres long and eighteen point seven deep.

penguins |>
  select(ends_with("_mm")) |>
  names()

penguins |>
  select(contains("length")) |>
  names()

ends with found three columns: bill length, bill depth and flipper length. contains found two: bill length and flipper length. Good column naming pays off here. Because the dataset uses consistent names, these helpers work beautifully.

penguins |>
  select(year, sex, everything()) |>
  head(3)

Year and sex now come first, then species, island and the measurements. No columns were lost, they were only reordered.

penguins |>
  rename(mass_g = body_mass_g, flipper_mm = flipper_length_mm) |>
  names()

All eight columns are still there, but two have new, shorter names: flipper mm and mass g.

Pause and try it 2#

Keep species plus all the _mm columns, rename the flipper column

to flipper_mm, and show the first 3 rows.

penguins |>
  select(species, ends_with("_mm")) |>
  rename(flipper_mm = flipper_length_mm) |>
  head(3)

Four columns: species, bill length, bill depth and our renamed flipper mm, with values one eighty one, one eighty six and one ninety five.

dplyr verb 3: mutate() creates columns#

penguins |>
  mutate(
    body_mass_kg = body_mass_g / 1000,
    bill_ratio = round(bill_length_mm / bill_depth_mm, 2)
  ) |>
  select(species, body_mass_kg, bill_ratio) |>
  head(4)

The first penguin weighs three point seven five kilos with a bill ratio of two point oh nine. The fourth row is NA in both columns, because that penguin had missing measurements, and any maths with NA gives NA.

basket |>
  mutate(
    total = price * qty,
    total_with_vat = total * 1.2,
    item = toupper(item)
  )

The items are now MUG, LAMP and NOTEBOOK in capitals. The mug's total with VAT is thirty point six, the lamp's is twenty eight point eight, and the notebooks come to fifteen point six.

penguins |>
  mutate(
    size = if_else(body_mass_g > 4500, "heavy", "light")
  ) |>
  select(species, body_mass_g, size) |>
  head(5)

Rows one, two, three and five are light. Row four is NA, because we can't say whether a penguin with an unknown weight is heavy or light. if else keeps missing values missing, which is honest.

penguins |>
  mutate(
    size = if_else(body_mass_g > 4500, "heavy", 0)
  )

An error: can't combine true, which is a character, and false, which is a double. if else insists both answers have the same type. Base ifelse would happily mix them and quietly turn everything into text. The strict version catches the bug for you.

This one is meant to fail, so you can see the error message.

penguin_sizes <- penguins |>
  mutate(
    size = case_when(
      body_mass_g < 3500 ~ "small",
      body_mass_g < 4750 ~ "medium",
      body_mass_g >= 4750 ~ "large"
    )
  )
table(penguin_sizes$size, useNA = "ifany")

Ninety penguins are large, one hundred and eighty one are medium, and seventy one are small. Two are NA, because no condition can be true when the weight is missing.

penguins |>
  mutate(
    flipper_group = case_when(
      is.na(flipper_length_mm) ~ "unknown",
      flipper_length_mm > 210 ~ "long",
      .default = "short"
    )
  ) |>
  count(flipper_group)

One hundred penguins have long flippers, two hundred and forty two have short ones, and two are unknown. No NA values at all, because we handled them on purpose.

dplyr verb 4: arrange() sorts rows#

penguins |>
  select(species, sex, body_mass_g) |>
  arrange(body_mass_g) |>
  head(4)

The lightest penguin is a female Chinstrap at just two thousand seven hundred grams. Next come two female Adelies at two thousand eight hundred and fifty.

penguins |>
  select(species, sex, body_mass_g) |>
  arrange(desc(body_mass_g)) |>
  head(4)

The heaviest penguins are all male Gentoos. The record holder weighs six thousand three hundred grams, more than twice the lightest penguin. Missing values always sink to the bottom when you arrange, whichever direction you sort.

penguins |>
  select(species, flipper_length_mm) |>
  arrange(species, desc(flipper_length_mm)) |>
  head(4)

Adelie comes first alphabetically, and within the Adelies the longest flippers are two hundred and ten, two hundred and eight, two hundred and five and two hundred and three millimetres.

penguins |>
  filter(species == "Gentoo", !is.na(sex)) |>
  mutate(body_mass_kg = body_mass_g / 1000) |>
  arrange(desc(body_mass_kg)) |>
  select(island, sex, body_mass_kg) |>
  head(5)

The top five are all male Gentoos from Biscoe, weighing six point three, six point oh five, six, six, and five point nine five kilos.

Pause and try it 3#

Add flipper_cm (flipper length / 10) and a column long_flipper

that is TRUE when flipper_cm > 21. Sort longest first, top 3.

penguins |>
  mutate(
    flipper_cm = flipper_length_mm / 10,
    long_flipper = flipper_cm > 21
  ) |>
  arrange(desc(flipper_cm)) |>
  select(species, flipper_cm, long_flipper) |>
  head(3)

The longest flipper is twenty three point one centimetres, on a Gentoo, and the next two are both twenty three. All three are marked TRUE for long flipper.

dplyr verb 5: summarise() with group_by()#

summarise() collapses many rows into one summary row.

group_by() makes it do that once per group.

penguins |>
  summarise(
    mean_mass = mean(body_mass_g, na.rm = TRUE),
    n = n()
  )

One row, two columns. The mean mass is four thousand two hundred and two grams, from three hundred and forty four rows. The whole table became a single summary line.

penguins |>
  summarise(mean_mass = mean(body_mass_g))

The mean mass is simply NA. Just two missing weights out of three hundred and forty four poison the whole average. R doesn't guess, so you must decide how to handle missing values.

penguins |>
  group_by(species) |>
  summarise(
    mean_mass = round(mean(body_mass_g, na.rm = TRUE)),
    max_flipper = max(flipper_length_mm, na.rm = TRUE),
    n = n()
  )

Three rows, one per species. Adelies average three thousand seven hundred and one grams, Chinstraps three thousand seven hundred and thirty three, and Gentoos are much heavier at five thousand and seventy six. Gentoos also have the longest flipper at two hundred and thirty one millimetres, and there are one hundred and fifty two Adelies, sixty eight Chinstraps and one hundred and twenty four Gentoos.

tapply(penguins$body_mass_g, penguins$species,
       mean, na.rm = TRUE)

The same averages, just unrounded: about three thousand seven hundred and one for Adelie, three thousand seven hundred and thirty three for Chinstrap, and five thousand and seventy six for Gentoo. It works, but summarise can compute many statistics at once, and the result is a tibble you can keep piping.

penguins |>
  group_by(species, sex) |>
  summarise(
    mean_mass = round(mean(body_mass_g, na.rm = TRUE)),
    .groups = "drop"
  )

Eight rows. In every species, males are heavier: Gentoo males average five thousand four hundred and eighty five grams, against four thousand six hundred and eighty for females. There are also rows where sex is NA, for the penguins whose sex was never recorded. group by keeps missing values as their own group.

penguins |>
  group_by(species) |>
  mutate(
    avg_mass = mean(body_mass_g, na.rm = TRUE),
    vs_avg = round(body_mass_g - avg_mass)
  ) |>
  select(species, body_mass_g, vs_avg) |>
  head(3)

Look at the line that says Groups: species. The result is still grouped. The first Adelie is forty nine grams above the Adelie average, the second ninety nine above, and the third four hundred and fifty one below.

Counting: count() and n()#

penguins |>
  count(species)

One hundred and fifty two Adelies, sixty eight Chinstraps and one hundred and twenty four Gentoos, in a column called n.

penguins |>
  count(island, species, sort = TRUE)

The biggest group is one hundred and twenty four Gentoos on Biscoe, then sixty eight Chinstraps on Dream. Notice Adelies appear on all three islands, while Gentoos only live on Biscoe and Chinstraps only on Dream.

Top and bottom rows: slice_max() and slice_min()#

penguins |>
  slice_max(body_mass_g, n = 3) |>
  select(species, island, sex, body_mass_g)

We asked for three, but got four rows. Why? The third and fourth penguins are tied at exactly six thousand grams, and slice max keeps ties by default.

penguins |>
  group_by(species) |>
  slice_min(body_mass_g, n = 1, with_ties = FALSE) |>
  select(species, island, sex, body_mass_g)

Three rows. The lightest Adelie weighs two thousand eight hundred and fifty grams, the lightest Chinstrap two thousand seven hundred, and the lightest Gentoo three thousand nine hundred and fifty. All three are females.

penguins |>
  distinct(island)

penguins |>
  distinct(species, island)

Three islands: Torgersen, Biscoe and Dream. Then five species and island combinations, confirming what count showed us: Adelies on three islands, Gentoos on Biscoe and Chinstraps on Dream.

across(): one operation, many columns#

mean_no_na <- function(x) {
  mean(x, na.rm = TRUE)
}

penguins |>
  group_by(species) |>
  summarise(across(ends_with("_mm"), mean_no_na))

One row per species, with the mean of all three measurement columns. Chinstraps have the longest bills on average at forty eight point eight millimetres, while Gentoos have the shallowest at fifteen and the longest flippers at about two hundred and seventeen.

penguins |>
  select(species, ends_with("_mm")) |>
  mutate(across(where(is.numeric), round)) |>
  head(3)

The bill lengths are now whole numbers, thirty nine, forty and forty, and the depths are nineteen, seventeen and eighteen. The species column was left alone, because it isn't numeric.

sd_no_na <- function(x) {
  sd(x, na.rm = TRUE)
}

penguins |>
  summarise(across(
    c(bill_length_mm, body_mass_g),
    list(mean = mean_no_na, sd = sd_no_na)
  ))

Four columns, with names made from the column and the function: bill length mean is about forty three point nine with a standard deviation of five point four six, and body mass mean is about four thousand two hundred and two, with a standard deviation of around eight hundred and two.

Pause and try it 4#

For each island: number of penguins and median flipper length.

Sort by the median, longest first.

penguins |>
  group_by(island) |>
  summarise(
    n = n(),
    median_flipper = median(flipper_length_mm, na.rm = TRUE)
  ) |>
  arrange(desc(median_flipper))

Biscoe comes first with one hundred and sixty eight penguins and a median flipper of two hundred and fourteen millimetres. That makes sense, because Biscoe is home to the big Gentoos. Dream follows at one ninety three, and Torgersen at one ninety one.

Joins: combining tables#

Real data is split across tables that share a key column.

customers <- tribble(
  ~customer_id, ~name,   ~city,
  1,            "Amara", "Nairobi",
  2,            "Brian", "London",
  3,            "Chen",  "Toronto",
  4,            "Dalia", "Nairobi",
  5,            "Eli",   "London"
)
customers

Five customers, with IDs one to five, and their names and cities.

products <- tribble(
  ~product_id, ~product,         ~category,     ~price,
  "P1",        "Wireless mouse", "Electronics", 25,
  "P2",        "USB-C cable",    "Electronics", 12,
  "P3",        "Desk lamp",      "Home",        40,
  "P4",        "Coffee mug",     "Home",        9,
  "P5",        "Notebook",       "Stationery",  4
)

Nothing prints because we only assigned it, but products now appears in the Environment pane with five rows and four columns.

orders <- tribble(
  ~order_id, ~customer_id, ~product_id, ~qty,
  101, 1, "P1", 2,
  102, 2, "P3", 1,
  103, 1, "P4", 4,
  104, 3, "P2", 3,
  105, 4, "P5", 10,
  106, 2, "P1", 1,
  107, 6, "P2", 2,
  108, 3, "P6", 1,
  109, 4, "P3", 1,
  110, 1, "P5", 5
)
orders

Ten orders, numbered one oh one to one ten, with the customer ID, product ID and quantity for each.

orders |>
  left_join(customers, by = "customer_id")

All ten orders are still here, now with the customer's name and city attached. Order one oh one belongs to Amara from Nairobi. But look at order one oh seven, for customer six. Its name and city are NA, because there was no matching customer. A left join never loses rows from the left table.

orders |>
  inner_join(customers, by = "customer_id") |>
  nrow()

Nine rows. The order from the unknown customer six was dropped. Inner joins are clean, but they can silently lose data, so always check your row counts.

customers |>
  anti_join(orders, by = "customer_id")

Just one row: Eli from London, customer five. He signed up but never bought anything. That's a customer the marketing team might want to email.

orders |>
  anti_join(customers, by = "customer_id")

orders |>
  anti_join(products, by = "product_id")

The first check finds order one oh seven, from the unknown customer six. The second finds order one oh eight, for the missing product P6. I run anti joins like these before every analysis. They find broken keys before the broken keys find you.

orders |>
  inner_join(products, by = "product_id") |>
  mutate(revenue = qty * price) |>
  group_by(category) |>
  summarise(orders = n(), revenue = sum(revenue)) |>
  arrange(desc(revenue))

Electronics earned the most, one hundred and thirty five from four orders. Home is next with one hundred and sixteen from three orders, and stationery made sixty from two. That's nine orders in total, because order one oh eight for the unknown product was dropped by the inner join.

cities <- tribble(
  ~town,     ~country,
  "Nairobi", "Kenya",
  "London",  "UK",
  "Toronto", "Canada"
)
customers |>
  left_join(cities)

An error: by must be supplied when x and y have no common variables. dplyr can't guess that town and city mean the same thing.

This one is meant to fail, so you can see the error message.

customers |>
  left_join(cities, by = join_by(city == town))

Every customer now has a country. Amara and Dalia are in Kenya, Brian and Eli in the UK, and Chen in Canada.

Pause and try it 5#

Total spend per customer name (qty * price), biggest first.

Hint: you need two joins.

orders |>
  inner_join(customers, by = "customer_id") |>
  inner_join(products, by = "product_id") |>
  mutate(spend = qty * price) |>
  group_by(name) |>
  summarise(total_spend = sum(spend)) |>
  arrange(desc(total_spend))

Amara is our best customer, with one hundred and six spent. Dalia is next with eighty, then Brian with sixty five, and Chen with thirty six. Chen's order for the unknown product was dropped, so his real total might be higher. Fixing that missing product would be our next job.

tidyr: reshaping data#

Tidy data: each variable is a column, each row an observation,

and each value sits in its own cell.

store_sales <- tribble(
  ~store,    ~Jan, ~Feb, ~Mar,
  "Nairobi", 120,  135,  150,
  "London",  200,  180,  210,
  "Toronto", 90,   NA,   110
)
store_sales

Three stores, three month columns. This wide layout is nice for people to read, but it isn't tidy: the month is a variable, yet it's hiding in the column names.

sales_long <- store_sales |>
  pivot_longer(
    cols = Jan:Mar,
    names_to = "month",
    values_to = "sales"
  )
sales_long

Now we have nine rows: three stores times three months. Each row is one store in one month, with its sales. Toronto in February is NA. This long shape is tidy, and it's the shape dplyr and ggplot2 love.

sales_long |>
  group_by(store) |>
  summarise(total = sum(sales, na.rm = TRUE)) |>
  arrange(desc(total))

London sold five hundred and ninety in total, Nairobi four hundred and five, and Toronto two hundred, because its missing February was skipped.

sales_long |>
  pivot_wider(names_from = month, values_from = sales)

We're back to the original layout, one row per store and one column per month. pivot longer and pivot wider are perfect opposites.

penguins |>
  count(island, species) |>
  pivot_wider(
    names_from = species,
    values_from = n,
    values_fill = 0
  )

A neat cross table. Biscoe has forty four Adelies and one hundred and twenty four Gentoos, Dream has fifty six Adelies and sixty eight Chinstraps, and Torgersen has only Adelies, fifty two of them. The zeros came from values fill.

Splitting and joining columns#

staff_codes <- tibble(
  code = c("SAL-2023-Grace", "FIN-2021-Hamid", "SAL-2024-Ivy")
)
staff_split <- staff_codes |>
  separate_wider_delim(
    code,
    delim = "-",
    names = c("team", "year", "name")
  )
staff_split

Three tidy columns: team, year and name. Grace is in SAL, which is sales, and joined in twenty twenty three. Notice year is still character text, shown as chr.

staff_codes |>
  separate(
    code,
    into = c("team", "year", "name"),
    sep = "-",
    convert = TRUE
  )

Same three columns, but this time year is an integer, shown as int, thanks to convert. So twenty twenty three is now a real number we could do maths with.

staff_split |>
  unite("label", name, team, sep = " / ")

The new label column reads Grace slash SAL, Hamid slash FIN and Ivy slash SAL. The name and team columns were replaced, and year was left untouched.

Missing values: drop_na(), replace_na(), complete()#

nrow(penguins)

penguins |> drop_na() |> nrow()

penguins |> drop_na(body_mass_g) |> nrow()

Three hundred and forty four, then three hundred and thirty three, then three hundred and forty two. So dropping all incomplete rows lost eleven penguins, mostly because of missing sex. Dropping only missing body mass lost just two. Be precise about what you drop.

sales_long |>
  replace_na(list(sales = 0)) |>
  filter(store == "Toronto")

Toronto's February now shows zero instead of NA. Only do this when zero is truly correct, for example when the store was closed. Otherwise you're inventing data.

deliveries <- tribble(
  ~store,    ~month, ~boxes,
  "Nairobi", "Jan",  5,
  "Nairobi", "Mar",  7,
  "London",  "Feb",  4
)
deliveries |>
  complete(store, month, fill = list(boxes = 0))

Six rows now, two stores times three months. The original three deliveries are still there, and the gaps, like London in January, show zero boxes. The months are sorted alphabetically, Feb, Jan, Mar. That's a hint that text months aren't ideal, and lubridate will fix it soon.

readr: fast CSV files#

penguins_clean <- penguins |>
  drop_na() |>
  select(species, island, sex, body_mass_g)
write_csv(penguins_clean, "penguins_clean.csv")
file.exists("penguins_clean.csv")

TRUE. The CSV file now exists in our working directory, and you can see it in the Files pane.

penguins_back <- read_csv("penguins_clean.csv")
penguins_back

First readr prints a column specification: three hundred and thirty three rows and four columns, with three character columns and one double. It's telling us the types it guessed. Then the tibble itself. Notice species is now chr, not a factor. A CSV file is just text, so factor levels don't survive the round trip.

penguins_back <- read_csv(
  "penguins_clean.csv",
  col_types = cols(species = col_factor())
)
glimpse(penguins_back)

Three hundred and thirty three rows, and species is a factor again, shown as fct. Island and sex stay as character, and body mass is a double.

stringr: cleaning text#

Every stringr function starts with str_ and takes the text first.

raw_names <- c("  wireless MOUSE ", "usb-c cable",
               "Desk LAMP  ", " coffee mug")
str_trim(raw_names)

The leading and trailing spaces are gone, but the capitals are still a mess: wireless MOUSE, usb c cable, Desk LAMP and coffee mug.

clean_names <- raw_names |>
  str_trim() |>
  str_to_title()
clean_names

Much better: Wireless Mouse, Usb C Cable, Desk Lamp and Coffee Mug. Although USB really should be all capitals. Let's fix that next.

clean_names <- str_replace(clean_names, "Usb", "USB")
clean_names

str_detect(clean_names, "Cable|Lamp")

The second name is now USB C Cable, with proper capitals. Then str detect returns FALSE, TRUE, TRUE, FALSE: only the cable and the lamp matched. str detect returns true or false, which means it slots perfectly inside filter.

str_sub(clean_names, 1, 4)

str_split_1("red,green,blue", ",")
str_split(c("a-b", "c-d-e"), "-")

The first four characters are Wire, USB with a dash, Desk and Coff. Then red, green and blue as a character vector. Finally a list with two elements: a and b from the first string, and c, d and e from the second. Lists, as we saw in video two, can hold pieces of different lengths.

reviews <- tibble(
  id = 1:4,
  text = c("Great mug, fast delivery", "Lamp arrived broken",
           "great value cable", "Delivery was slow")
)
reviews |>
  mutate(
    about_delivery = str_detect(str_to_lower(text), "delivery"),
    n_chars = str_length(text)
  )

Reviews one and four are flagged as about delivery, even though one says Delivery with a capital D. The lengths range from seventeen to twenty four characters.

reviews |>
  filter(str_detect(text, regex("great", ignore_case = TRUE)))

Two reviews: number one, Great mug, fast delivery, and number three, great value cable. Both versions of great were caught.

lubridate: dates made easy#

ymd("2026-03-15")
dmy("15/03/2026")
mdy("March 15, 2026")
class(ymd("2026-03-15"))

All three print the same date: the fifteenth of March, twenty twenty six, in the standard year month day format. And the class is Date, a real date object, not text. No format codes to remember. You just pick the function that matches the order of your data.

dates <- ymd(c("2026-01-05", "2026-02-14",
               "2026-02-28", "2026-03-09"))
year(dates)
month(dates)
month(dates, label = TRUE)
wday(dates, label = TRUE)

The years are all twenty twenty six. The months are one, two, two, three, and with labels Jan, Feb, Feb, Mar. The output also lists twelve levels, January to December, because the labels are an ordered factor. The days of the week are Monday, Saturday, Saturday and Monday. And because these are ordered factors, they'll sort in calendar order, not alphabetically.

floor_date(dates, unit = "month")

January first, February first twice, and March first. This is perfect for grouping data by month, and we'll use it in our final project.

ymd("2026-12-25") - ymd("2026-03-15")

time_length(
  interval(ymd("1990-06-01"), ymd("2026-03-15")),
  "years"
)

Time difference of two hundred and eighty five days until Christmas. Then thirty five point seven nine years, which is someone's exact age, including the fraction of a year.

shipping <- tibble(
  ordered   = ymd(c("2026-01-05", "2026-01-20", "2026-02-03")),
  delivered = ymd(c("2026-01-08", "2026-01-27", "2026-02-04"))
)
shipping |>
  mutate(
    days = as.numeric(delivered - ordered),
    month = month(ordered, label = TRUE),
    weekday = wday(ordered, label = TRUE)
  )

The deliveries took three, seven and one days. Two orders were placed in January and one in February, on a Monday and two Tuesdays. Notice the date columns are shown with the type date.

forcats: taming factors#

levels(penguins$species)

penguins |>
  mutate(species = fct_infreq(species)) |>
  count(species)

The default levels are Adelie, Chinstrap and Gentoo, alphabetically. After fct infreq the order is Adelie with one hundred and fifty two, Gentoo with one hundred and twenty four, then Chinstrap with sixty eight. Biggest first.

set.seed(25)
survey <- tibble(
  drink = sample(
    c("Latte", "Tea", "Mocha", "Espresso", "Cocoa", "Chai"),
    size = 50, replace = TRUE,
    prob = c(0.3, 0.25, 0.15, 0.12, 0.1, 0.08)
  )
)
survey |> count(drink, sort = TRUE)

Latte wins with eighteen votes, then tea with twelve, mocha seven, espresso six, chai four and cocoa only three.

survey |>
  mutate(drink = fct_lump_n(drink, n = 3)) |>
  count(drink)

Four groups: latte eighteen, mocha seven, tea twelve, and Other with thirteen, which is espresso, chai and cocoa added together. Other always goes last.

penguins |>
  mutate(island = fct_reorder(island, body_mass_g,
                              .fun = median, .na_rm = TRUE)) |>
  pull(island) |>
  levels()

Dream comes first, then Torgersen, then Biscoe, from lightest median body mass to heaviest. Biscoe is last because of those heavy Gentoos. This becomes really useful in the next video, where it sorts the bars of a chart in a sensible order.

Project: monthly sales report for an online shop#

Raw orders -> clean -> join -> summarise -> pivot -> CSV.

set.seed(103)
n <- 300
raw_orders <- tibble(
  order_id   = 1:n,
  order_date = format(
    ymd("2026-01-01") + sample(0:180, n, replace = TRUE)
  ),
  product_id = sample(products$product_id, n, replace = TRUE),
  qty        = sample(c(1:4, NA), n, replace = TRUE,
                      prob = c(0.4, 0.3, 0.15, 0.1, 0.05)),
  channel    = sample(c(" web", "Web", "APP ", "app"), n,
                      replace = TRUE),
  customer_id = sample(1:5, n, replace = TRUE)
)

Nothing prints, but raw orders appears in the Environment pane with three hundred observations of six variables.

glimpse(raw_orders)
raw_orders |> count(channel)
sum(is.na(raw_orders$qty))

glimpse shows three hundred rows and six columns. The order date is chr, meaning text, not a real date, and we can already spot NA in the quantity column. The count shows four different spellings of only two channels, with roughly seventy five orders each. And thirteen quantities are missing.

clean_orders <- raw_orders |>
  drop_na(qty) |>
  mutate(
    order_date = ymd(order_date),
    channel = channel |> str_trim() |> str_to_lower()
  )
nrow(clean_orders)
clean_orders |> count(channel)

Two hundred and eighty seven orders remain, three hundred minus the thirteen with missing quantities. And the four messy spellings have collapsed into just two clean channels: one hundred and forty three app orders and one hundred and forty four web orders.

clean_orders |>
  anti_join(products, by = "product_id") |>
  nrow()

Zero. Every order matches a known product, so a join won't create any surprise NA values.

sales <- clean_orders |>
  left_join(products, by = "product_id") |>
  mutate(
    revenue = qty * price,
    month = month(order_date, label = TRUE)
  )
sales |>
  select(order_date, month, product, qty, revenue) |>
  head(4)

The first order, on the thirteenth of February, was two USB C cables for a revenue of twenty four. Next, one notebook in May for four, and a desk lamp in April for forty. The month column is an ordered factor, shown as ord.

monthly <- sales |>
  group_by(month, category) |>
  summarise(revenue = sum(revenue), .groups = "drop")
monthly

Eighteen rows: six months times three categories. In January, electronics earned four hundred and eighty nine, home one thousand one hundred and ninety eight, and stationery one hundred and twelve. It's tidy and correct, but a manager would find this long list hard to scan. That's what pivoting is for.

report <- monthly |>
  pivot_wider(
    names_from = category,
    values_from = revenue,
    values_fill = 0
  ) |>
  mutate(total = Electronics + Home + Stationery)
report

A clean report table: one row per month from January to June, and columns for electronics, home, stationery and the total. May was the best month, with two thousand three hundred and eighty three in total, and April the weakest at one thousand two hundred and sixty two. The months are in calendar order because month is an ordered factor.

sales |>
  group_by(category) |>
  summarise(revenue = sum(revenue)) |>
  mutate(share = round(100 * revenue / sum(revenue), 1)) |>
  arrange(desc(revenue))

Home leads with five thousand seven hundred and eighty one, which is fifty seven point four percent of all revenue. Electronics brings in thirty eight percent, and stationery only four point six percent. That's a real insight: those cheap notebooks sell lots of units but hardly move the revenue needle.

Pause and try it 6#

Revenue by customer city: join customers, then sum by city.

sales |>
  left_join(customers, by = "customer_id") |>
  group_by(city) |>
  summarise(orders = n(), revenue = sum(revenue)) |>
  arrange(desc(revenue))

London tops the table with four thousand two hundred and sixty two from one hundred and six orders, then Nairobi with three thousand nine hundred and twenty one, even though Nairobi had more orders, one hundred and fourteen. Toronto brought in one thousand eight hundred and eighty six. London and Nairobi each have two customers, while Toronto only has Chen, so that ranking makes sense. And in this simulated data even Eli finally placed some orders.

write_csv(report, "monthly_sales_report.csv")
read_csv("monthly_sales_report.csv", show_col_types = FALSE)

The file reads back perfectly: six months, five columns, the same numbers as our report. One small change: month comes back as plain text, because a CSV can't store factor levels.

Recap#

dplyr: filter, select, mutate, arrange, summarise + group_by,

count, slice_max, distinct, across, and joins.

tidyr: pivots, separate, unite, drop_na, replace_na, complete.

readr, stringr, lubridate and forcats for files, text, dates.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.