Worksheet 15 — 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

September 10, 2026

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

Recap from Worksheet 14

Claimed a final-project topic and a candidate dataset
Sized it up in R with read_csv(), glimpse(), dim(), head()
Wrote H₀ and Hₐ for your own question

Today’s Objectives

Read a messy, wide government text file directly from the web
Understand why wide data breaks ggplot() and group_by()
Use pivot_longer() to make it tidy — and pivot_wider() to make a summary table
Fix a real-world date wrinkle: winters that cross the new year
Rebuild NOAA’s “spaghetti plot” of every ice season
Use slice_max() to pull each winter’s peak ice cover
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

Pivoting is easy to fake your way through by staring at dim() output after the fact. Don’t. Before each ▶ Run this block, cover the output and write down what shape you expect — how many rows, how many columns — then type the pivot call yourself rather than pasting it in. If your prediction is wrong, that gap is exactly where you’ll find which argument (cols, names_to, values_to) you misunderstood; typing it out is also what makes you notice the small stuff, like the stray X glued onto every year, before R does.

🧩 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 12. 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:

Read a messy space-separated text file with read.table() and rownames_to_column()
Explain why wide data breaks ggplot() and group_by()
Use pivot_longer() to make wide data tidy
Use pivot_wider() to build a human-readable summary table
Fix seasonal dates that cross the new year with if_else() + ymd()
Rebuild a multi-layer spaghetti plot with three geom_line() calls
Use slice_max() to pull one extreme row per group
Fit and interpret lm(ice_cover ~ year) on the yearly maximum

✏️ 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:

Extension — out of class (~30–40 min)

Turn this in with your worksheet. You built the ice workflow on Lake Superior. Now run it end-to-end on a different Great Lake you pick and compare trends.

Important

E2 and E3 must be handwritten on paper, photographed, and embedded (![caption](my_photo.jpg)). Typed answers get at most half credit, even if correct — I want your own prediction and reasoning about your own lake.

E1 · Pivot a different lake (4 pts)

Pick one: mic.txt (Michigan), hur.txt (Huron), eri.txt (Erie), ont.txt (Ontario). Change only the file name in the URL and re-run the whole workflow:

url <- "https://www.glerl.noaa.gov/data/ice/glicd/daily/eri.txt"   # <- your lake
# read + rownames_to_column -> pivot_longer (names_prefix = "X")
#   -> year to numeric, drop NA ice -> slice_max() per year
#   -> lm(ice_cover ~ year), report the slope (% per year)

Report your lake, its yearly-maximum ice slope, and — Lake Superior’s is about −0.3 %/year — whether your lake is losing ice faster or slower.

E2 · Predict, then check — ✍️ by hand (3 pts)

Before running E1: your lake is shallower or deeper than Superior. Predict, by hand, whether its max-ice trend will be steeper or shallower than Superior’s, and why. Then run E1 and write whether you were right.

E3 · Explain it — ✍️ by hand, with YOUR slope (3 pts)

Using your actual slope, explain in plain language how fast your lake is losing (or gaining) maximum ice cover, and over the record how much total change that adds up to.
Shallower lakes hold less heat. Explain, in your own words, how depth could make one lake’s ice respond faster to the same warming than another’s.

Getting unstuck

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

💡 Key idea: Whatever data you pick for your final project, there’s a good chance it’ll arrive shaped like sup.txt — one column per year, site, or category. Recognizing that shape, and knowing pivot_longer() fixes it, will save you real time.

End of the Lake Superior Ice worksheet.

Other Formats

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

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

Recap from Worksheet 14

Today’s Objectives

How to use this worksheet

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

Part 1 · Load libraries and read the raw file

Libraries

Read the messy text file straight from the web

Part 2 · Inspect the wide shape

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

Part 3 · Pivot from wide to long

Part 4 · Fix winters that cross the new year

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

Part 5 · Summarize now that we’re long

Part 6 · Pivot back to a wide summary table

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

Part 7 · Rebuild NOAA’s spaghetti plot

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

Part 9 · Model the trend in maximum ice cover

Part 10 · Review and checkpoint

Extension — out of class (~30–40 min)

E1 · Pivot a different lake (4 pts)

E2 · Predict, then check — ✍️ by hand (3 pts)

E3 · Explain it — ✍️ by hand, with YOUR slope (3 pts)

Getting unstuck

Other Formats

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

Recap from Worksheet 14

Today’s Objectives

How to use this worksheet

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

Part 1 · Load libraries and read the raw file

Libraries

Read the messy text file straight from the web

Part 2 · Inspect the wide shape

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

Part 3 · Pivot from wide to long

Part 4 · Fix winters that cross the new year

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

Part 5 · Summarize now that we’re long

Part 6 · Pivot back to a wide summary table

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

Part 7 · Rebuild NOAA’s spaghetti plot

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

Part 9 · Model the trend in maximum ice cover

Part 10 · Review and checkpoint

Extension — out of class (~30–40 min)

E1 · Pivot a different lake (4 pts)

E2 · Predict, then check — ✍️ by hand (3 pts)

E3 · Explain it — ✍️ by hand, with YOUR slope (3 pts)

Getting unstuck

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