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

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

r-basics
project-setup

Hands-on companion to the pivoting lecture. Students read NOAA’s messy wide ice-cover file straight from the web, pivot it long, fix winters that cross the new year, summarize, pivot back wider, rebuild NOAA’s spaghetti plot, and model the trend in maximum ice cover.

Author

Bill Perry

Published

July 5, 2026

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.
Note🔮 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.