Worksheet 07 — Wide, Long, and Wild: Pivoting Real Data

Reshaping Lake Superior ice cover with pivot_longer() and pivot_wider()

Bill Perry

2026-07-05

Wide, Long, and Wild — Pivoting Lake Superior Ice Data

Recap from Worksheet 06

  • Downloaded real weather data into R with GSODR
  • Used group_by() %>% summarize() to collapse daily data to yearly means
  • Fit lm(TEMP ~ YEAR) and read the warming slope
  • Built a summer vs. winter comparison with case_when()

Today’s Objectives

  1. Read a messy, wide government text file directly from the web
  2. Understand why wide data breaks ggplot() and group_by()
  3. Use pivot_longer() to make it tidy — and pivot_wider() to make a summary table
  4. Fix a real-world date wrinkle: winters that cross the new year
  5. Rebuild NOAA’s “spaghetti plot” of every ice season
  6. Use slice_max() to pull each winter’s peak ice cover
  7. Fit lm() to ask whether maximum ice cover is declining

How to use this worksheet

  • Work through the parts in order — each builds on the last. Type the code into a new R script in Positron and run it line by line.
  • Blocks marked ▶ Run this should be executed as written.
  • Blocks marked ✏️ Your turn ask you to write, modify, or interpret.
  • The Going further section is optional.

🔮 Predict before you run — and type, don’t paste

Before each ▶ Run this block, cover the output and predict what R will print — especially the shape (rows × columns) after each pivot. Then type the code yourself. Guessing the shape first is what turns pivoting from magic into something you understand; typing trains your eye for the errors (a missing date column, a stray X prefix) that break real reshaping.

🧩 Chunk 1 — Why wide data is a problem (after lecture Chunk 1)

Parts 1–2: read NOAA’s raw file and inspect its wide shape.

Part 1 · Load libraries and read the raw file

Libraries

▶ Run this at the top of your script:

# Load packages at the top — always ----------------------
library(tidyverse)   # data manipulation + ggplot2
library(janitor)     # clean_names() and friends

Read the messy text file straight from the web

▶ Run this (it reads a live NOAA text file — you need internet):

# Read NOAA's plain-text ice file — NOT a tidy CSV -------
url <- "https://www.glerl.noaa.gov/data/ice/glicd/daily/sup.txt"

ice_raw <- read.table(
  url,
  header    = TRUE,
  na.strings = c("-999", "-99.00", "NA")
) %>%
  rownames_to_column(var = "date") %>%
  as_tibble()

head(ice_raw)

⚠️ Watch out! This file is space-separated, not comma-separated — that’s why we use read.table() and not read_csv(). The day labels (Nov-10…) start life as row names, so rownames_to_column() turns them into a real date column.

Part 2 · Inspect the wide shape

▶ Run this:

dim(ice_raw)      # rows x columns
names(ice_raw)    # the column names

✏️ Your turn: Look at the output and fill in the blanks.

Rows:
Columns:
What does ONE row represent (a day? a year? a day-of-winter?):
What do the COLUMN names look like (e.g. X1973, X1974 ...):

✏️ Your turn: R added an X to the front of every year column (X1973). Why? (Hint: what rule do R column names have to follow?)

Your answer:

💡 Key idea: “Year” is hiding as 53 separate column names, not as values in a column. That is exactly why ggplot(aes(x = year)) and group_by(year) are impossible right now — there is no year column to point at.

🧩 Chunk 2 — Reshape with pivot_longer & fix dates (after lecture Chunk 2)

Parts 3–4: pivot the data long, then fix the winters that cross the new year.

Part 3 · Pivot from wide to long

🔮 Predict first: ice_raw is about 190 rows × 53 columns. After pivot_longer() collapses every year column, how many columns will the result have, and roughly how many rows? Write your guess.

▶ Run this:

# Collapse all year columns into year + ice_cover --------
ice_long <- ice_raw %>%
  pivot_longer(
    cols         = -date,
    names_to     = "year",
    names_prefix = "X",
    values_to    = "ice_cover"
  )

ice_long <- ice_long %>% arrange(year, date)

head(ice_long)
dim(ice_long)

✏️ Your turn: Check your prediction.

Columns in ice_long:
Rows in ice_long:
Was your prediction close?  Y / N

✏️ Your turn: What does names_prefix = "X" do? What would the year column look like if you left it out?

Your answer:

Part 4 · Fix winters that cross the new year

An ice season runs Nov → May, but NOAA labels the whole season by the year it ends in. So Nov-10 under column 1974 really happened in November 1973.

▶ Run this:

# Assign Nov/Dec to the PREVIOUS calendar year -----------
ice_clean <- ice_long %>%
  mutate(
    year          = as.numeric(year),
    calendar_year = if_else(str_starts(date, "Nov|Dec"), year - 1, year),
    full_date     = ymd(paste(calendar_year, date, sep = "-")),
    month         = month(full_date, label = TRUE)
  ) %>%
  drop_na(ice_cover, full_date)

head(ice_clean)

✏️ Your turn: Explain the if_else() in plain English. What condition is being tested, and what happens when it is TRUE?

Condition tested:
What happens when TRUE:
Why we subtract 1 from year:

🧩 Chunk 3 — Summarize & pivot back wider (after lecture Chunk 3)

Parts 5–6: summarize the tidy data, then rebuild a readable table with pivot_wider().

Part 5 · Summarize now that we’re long

▶ Run this:

# Mean ice cover per year-month --------------------------
monthly_avg <- ice_clean %>%
  group_by(year, month) %>%
  summarize(mean_ice = mean(ice_cover, na.rm = TRUE), .groups = "drop")

head(monthly_avg)

✏️ Your turn: This is the same group_by() %>% summarize() pattern from Worksheet 06. Why was it impossible to run on ice_raw (the wide data)?

Your answer:

Part 6 · Pivot back to a wide summary table

🔮 Predict first: pivot_wider() will make one row per year and one column per month. Roughly how many rows (years) and columns (months + 1) will the table have? Predict before running.

▶ Run this:

# Turn long data into a year-by-month summary table ------
ice_pivot_table <- ice_clean %>%
  pivot_wider(
    id_cols    = year,
    names_from = month,
    values_from = ice_cover,
    values_fn  = mean
  )

print(ice_pivot_table)

✏️ Your turn: Compare pivot_longer() and pivot_wider(). When would you reach for each?

Use pivot_longer() when:
Use pivot_wider() when:

🧩 Chunk 4 — Visualize & model the trend (after lecture Chunk 4)

Parts 7–9: rebuild NOAA’s spaghetti plot, pull each year’s peak, and model the trend.

Part 7 · Rebuild NOAA’s spaghetti plot

▶ Run this (put every winter on one shared axis):

# Relabel every date onto one fake shared year -----------
ice_plot_data <- ice_clean %>%
  mutate(
    plot_date = if_else(
      month(full_date) >= 10,
      update(full_date, year = 1999),
      update(full_date, year = 2000)
    )
  )

historical_avg <- ice_plot_data %>%
  group_by(plot_date) %>%
  summarize(avg_ice = mean(ice_cover, na.rm = TRUE))

current_yr        <- max(ice_plot_data$year)
past_years        <- ice_plot_data %>% filter(year < current_yr)
current_year_data <- ice_plot_data %>% filter(year == current_yr)

▶ Run this (three layers, three geom_line() calls):

ice_spaghetti_plot <- ggplot() +
  geom_line(data = past_years,
            aes(x = plot_date, y = ice_cover, group = year),
            color = "blue", alpha = 0.15) +
  geom_line(data = historical_avg,
            aes(x = plot_date, y = avg_ice),
            color = "red", linewidth = 1.2) +
  geom_line(data = current_year_data,
            aes(x = plot_date, y = ice_cover),
            color = "black", linewidth = 1.2) +
  scale_x_date(date_labels = "%b", date_breaks = "1 month") +
  labs(title = "Lake Superior Average Ice Cover",
       subtitle = paste("Comparing", current_yr, "to Historical Data"),
       x = NULL, y = "Ice Cover (%)") +
  theme_bw()

ice_spaghetti_plot

✏️ Your turn: Open NOAA’s published plot (glerl.noaa.gov/data/ice/spaghetti/sup_ice_compare.png). Did you recreate it? What does the red line represent, and why is the current year drawn last?

Red line represents:
Why current year is drawn last:

Part 8 · Pull each winter’s peak ice with slice_max()

🔮 Predict first: slice_max() keeps the single highest ice-cover day per year. How many rows will the result have? (Hint: how many winters are in the data?)

▶ Run this:

# Keep the single highest ice-cover day each year --------
max_ice_per_year <- ice_clean %>%
  group_by(year) %>%
  slice_max(order_by = ice_cover, n = 1, with_ties = FALSE)

head(max_ice_per_year %>% arrange(ice_cover))

✏️ Your turn: Find the lowest-peak and highest-peak winters in the output.

Lowest maximum ice cover:  year = _____   value = _____ %
Highest maximum ice cover: year = _____   value = _____ %
What does the DATE of each year's peak tell you (early vs. late season)?

Part 9 · Model the trend in maximum ice cover

🔮 Predict first: Over 50+ years, is the maximum ice cover declining, flat, or rising? And given the huge year-to-year scatter, will the slope be significant (p < 0.05)? Commit to both before running.

▶ Run this:

# Fit a regression: max ice cover by year ----------------
max_ice_model <- lm(ice_cover ~ year, data = max_ice_per_year)
summary(max_ice_model)
# Plot the yearly peaks with a trend line ----------------
max_ice_plot <- max_ice_per_year %>%
  ggplot(aes(x = year, y = ice_cover)) +
  geom_point(size = 2) +
  geom_smooth(method = "lm", color = "firebrick") +
  labs(title = "Lake Superior — Annual Maximum Ice Cover",
       x = "Year", y = "Max Ice Cover (%)") +
  theme_minimal()

max_ice_plot

✏️ Your turn: Report the model and interpret it honestly.

Slope (per year) =
p-value for the slope =
R-squared =
Is the trend significant at α = 0.05?  Y / N
In one sentence — is Lake Superior's peak ice cover changing? How confident are you, given the scatter?

💡 Key idea: Real environmental data is messy and honest. A non-significant or weak trend, with lots of scatter driven by ENSO and the polar vortex, is a perfectly valid — and realistic — finding.

Part 10 · Review and checkpoint

At this point you should be able to:

✏️ Your turn — before you move on: Run your whole script top to bottom with Ctrl/Cmd + Shift + Enter. Does it run cleanly?

Ran cleanly?  Y / N
If not, what error appeared:

Part 11 · Going further

Optional — do this if you finish early or want to push deeper.

Try a different Great Lake

The exact same code works for the other lakes — just swap the file name.

▶ Try this: change sup.txt to mic.txt (Michigan), hur.txt (Huron), eri.txt (Erie), or ont.txt (Ontario).

# Repeat the whole workflow for a different lake ---------
url <- "https://www.glerl.noaa.gov/data/ice/glicd/daily/eri.txt"   # Lake Erie
# ... then re-run Parts 1, 3, 4, 8, and 9 with this file

✏️ Your turn: Does your second lake show a stronger or weaker maximum-ice trend than Superior? Why might shallower lakes (like Erie) behave differently?

Your lake:
Its max-ice slope vs. Superior's:
Your explanation:

Getting unstuck

  1. read.table() fails / times out: it reads a live web file — check your internet. If it keeps failing, ask for the saved backup file.
  2. Column 'date' doesn't exist: you skipped rownames_to_column() — the day labels are still row names, not a column.
  3. year column full of X1973: you left out names_prefix = "X" in pivot_longer().
  4. ymd() returns NA: check the pieces you pasted together — the date text must be something ymd() can parse (year first).
  5. Spaghetti plot lines look wrong: confirm group = year is inside aes() for the past-years layer, or every point connects into one giant scribble.
  6. Cheat sheets — https://posit.co/resources/cheatsheets/

💡 Key idea: pivot_longer() is one of the most-used functions in all of data science. Almost every dataset you download for your final project will need some version of this reshape.

End of Worksheet 07. Next: the final-project unit — finding and analyzing data you choose.