You've probably seen this a thousand times: a spreadsheet where the dates or categories are the column headers. It looks great to a human reading a report, but for R, it's a nightmare. We call this "wide" data. To actually analyze it—filter it, group it, or plot it with ggplot2—we need "long" data, where each row is a single observation.

Let's look at a real-world scenario. I've got some quarterly revenue data for three different product lines. It's currently in a wide format that would make any data scientist cringe.

library(tidyr)
library(dplyr)

# This is how the data usually arrives from a business user
revenue_wide <- data.frame(
  product = c("Widget A", "Widget B", "Widget C"),
  q1 = c(1200, 1500, 800),
  q2 = c(1300, 1400, 900),
  q3 = c(1100, 1600, 850),
  q4 = c(1500, 1700, 1000)
)

Collapsing the quarters into a single column

I want to move those q1 through q4 columns into a single column called "quarter" and their values into a column called "revenue". This is where pivot_longer() comes in. It's the workhorse of tidyr.

# My first attempt at reshaping
revenue_long <- revenue_wide %>%
  pivot_longer(
    cols = everything(), 
    names_to = "quarter", 
    values_to = "revenue"
  )

Correcting the "over-pivot" mistake

If you run the code above, you'll notice something is wrong. I used cols = everything(), which means R pivoted the product column too. Now I have "Widget A" sitting in the "quarter" column, and the actual product name sitting in the "revenue" column. That's a mess.

In pivot_longer, you have to be explicit about which columns should be collapsed. I can either list the columns I want to pivot, or—even better—tell R to pivot everything except the product column using the minus sign.

# The corrected version
revenue_long <- revenue_wide %>%
  pivot_longer(
    cols = -product, 
    names_to = "quarter", 
    values_to = "revenue"
  )

print(revenue_long)

Now we have a tidy dataset. Each row is a unique combination of a product and a quarter. This is the format you need if you ever want to use group_by() or create a line chart of revenue over time.

Reorganizing for a custom summary

Sometimes you need to go the other way. Maybe you've done some calculations and you want to create a specific cross-tabulation for a presentation. That's where pivot_wider() fits in. It's essentially the inverse of pivot_longer.

Let's say I've calculated a "performance" flag for each quarter (I'll just simulate some dummy data for this), and I want to see a grid of products vs. quarters showing that flag.

# Adding a dummy performance column
revenue_long <- revenue_long %>%
  mutate(status = if_else(revenue > 1300, "High", "Low"))

# Now, let's widen it back out, but using 'status' instead of 'revenue'
status_grid <- revenue_long %>%
  select(product, quarter, status) %>%
  pivot_wider(
    names_from = quarter, 
    values_from = status
  )

print(status_grid)

By using names_from and values_from, I've turned the data back into a wide format, but I've changed the actual content of the cells. This flexibility is why tidyr is so powerful—it's not just about "fixing" data, but about sculpting it into the shape required for the next step of your pipeline.

📋 Practical Task

Normalizing the City Temperature Records

You have been handed a dataset of average monthly temperatures for three cities. The data is currently "wide," with months as columns. This makes it impossible to calculate the annual average temperature per city using a simple group_by.

temp_data <- data.frame(
  city = c("New York", "London", "Tokyo"),
  Jan = c(3, 5, 6),
  Feb = c(4, 6, 7),
  Mar = c(8, 9, 10)
)

Your Task: Use pivot_longer() to reshape this data so that it has three columns: city, month, and avg_temp. Ensure that the city column remains as an identifier and is not pivoted into the month column.