# Data Wrangling with the Tidyverse ----
# Video 3 of the series R Programming: Zero to Expert.
# Goal: filter, reshape, join and summarise real data quickly.

# 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")]

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

# 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)

# Tibbles: data frames, upgraded ----

scores_tbl <- as_tibble(scores)
scores_tbl

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

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

scores$stud
scores_tbl$stud

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

# Our real dataset: Palmer penguins ----
# 344 penguins from 3 islands in Antarctica, measured 2007-2009.

library(palmerpenguins)

penguins

glimpse(penguins)

# 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()

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

scores_tbl
  |> nrow()

scores_tbl |>
  nrow()

# dplyr verb 1: filter() keeps rows ----
# Every dplyr verb: data first, then what to do. Returns a tibble.

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

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

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

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

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

penguins |>
  filter(species = "Gentoo")

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

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

# Pause and try it 1
# Find female Chinstrap penguins heavier than 3,700 grams.

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

# dplyr verb 2: select() picks columns ----

penguins |>
  select(species, island, body_mass_g)

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

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

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

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

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

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

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

# 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)

# 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)

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

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

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

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")

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

# dplyr verb 4: arrange() sorts rows ----

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

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

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

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)

# 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)

# 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()
  )

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

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()
  )

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

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

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)

# Counting: count() and n() ----

penguins |>
  count(species)

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

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

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

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

penguins |>
  distinct(island)

penguins |>
  distinct(species, island)

# 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))

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

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)
  ))

# 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))

# 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

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
)

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

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

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

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

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

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

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

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

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

# 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))

# 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

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

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

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

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

# 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

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

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

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

nrow(penguins)

penguins |> drop_na() |> nrow()

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

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

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

# 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")

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

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

# 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)

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

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

str_detect(clean_names, "Cable|Lamp")

str_sub(clean_names, 1, 4)

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

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 |>
  filter(str_detect(text, regex("great", ignore_case = TRUE)))

# lubridate: dates made easy ----

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

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)

floor_date(dates, unit = "month")

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

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

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)
  )

# forcats: taming factors ----

levels(penguins$species)

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

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)

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

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

# 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)
)

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

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)

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

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)

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

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

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

# 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))

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

# 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.