Imagine you're organizing a physical filing cabinet for a medical clinic. Instead of having one folder per patient with a neat list of visits inside, some overly enthusiastic admin decided to create a giant, wide index card for every patient. On this card, they wrote: "Week1_Weight", "Week1_BP", "Week2_Weight", and "Week2_BP".

If you want to find a patient's weight trend, you have to scan across the card, jumping over the blood pressure (BP) entries. It's a mess. To fix this, you wouldn't just list the labels in one column and the values in another; you'd want a clean table where "Week" is one column, "Weight" is another, and "BP" is a third. You're not just pivoting; you're decomposing the label itself into two different things: a time marker and a metric type.

In R, specifically using tidyr, this is where we move past simple pivoting and start using .value. Here is how that mapping works:

• The Index Card → Your wide data frame.
• "Week1" or "Week2" → The part of the column name that becomes a value in a new column (e.g., a "Visit" column).
• "Weight" or "BP" → The part of the column name that actually defines the name of a new column.
• The actual number written on the card → The value that fills the cell.

Dealing with Smashed Column Names

I've seen this a lot in legacy datasets. Someone saves a CSV where the columns are named 2023_Q1_Revenue, 2023_Q1_Profit, 2023_Q2_Revenue, and so on. If you use a standard pivot_longer, you'll end up with a "name" column containing "2023_Q1_Revenue" and a "value" column. That's useless because Revenue and Profit are different units—you can't put them in the same column.

The trick is telling R: "Part of this column name is actually the name of a new column." We do that by putting .value in the names_to argument.

library(tidyr)
library(dplyr)

# A typical 'messy' wide dataset
clinical_data <- data.frame(
  patient_id = 1:2,
  day1_systolic = c(120, 130),
  day1_diastolic = c(80, 85),
  day7_systolic = c(118, 128),
  day7_diastolic = c(78, 82)
)

# The magic happens here
cleaned_data <- clinical_data %>%
  pivot_longer(
    cols = contains("_"), 
    names_to = c("visit_day", ".value"), 
    names_pattern = "(.*)_(.*)"
  )

print(cleaned_data)

Breaking Down the Pattern

Look closely at names_to = c("visit_day", ".value"). This is the critical part. I'm telling R that the column name needs to be split into two pieces. The first piece goes into a new column called visit_day. The second piece, .value, tells R: "Don't make a column called '.value'; instead, use this text as the actual column header."

But how does R know where to split? That's what names_pattern is for. I used "(.*)_(.*)". This is a regular expression that says: "Grab everything before the underscore, and grab everything after the underscore."

I'll be honest: regular expressions can be a headache. If your columns are separated by a dot or a dash instead of an underscore, you just change that character in the pattern. If you have three pieces of information in the name (e.g., 2023_Q1_North_Revenue), you'd just add more elements to names_to and more capture groups (.*) to your pattern.

📋 Practical Task

Exercise: Unpacking the Multi-City Environmental Sensor Log

You have been handed a dataset from a group of environmental sensors. The data is wide and "smashed." Each row is a specific sensor ID, but the columns contain both the city name and the metric measured (Temperature and Humidity).

The Goal: Reshape this data so that you have one column for sensor_id, one column for city, one column for temp, and one column for humidity.

library(tidyr)
library(dplyr)

# The messy sensor data
sensor_logs <- data.frame(
  sensor_id = c("S1", "S2", "S3"),
  London_temp = c(15.2, 14.8, 16.1),
  London_hum = c(82, 85, 80),
  Paris_temp = c(18.1, 17.5, 19.0),
  Paris_hum = c(65, 68, 62),
  Berlin_temp = c(12.4, 13.1, 11.9),
  Berlin_hum = c(70, 72, 75)
)

# YOUR CODE HERE:
# Use pivot_longer with .value to reshape the sensor_logs data frame.
# Hint: Your names_pattern should look for the underscore separating the city and the metric.
# Ensure your final columns are: sensor_id, city, temp, and hum.