如何用dplyr实现隔行展开并合并列名整理非整洁数据？

Hey there! Your initial instinct to use tidyr functions (like spread()/unite()) for tidying this data is spot-on—let’s break this down with a concrete example to make it clear, since tidying always clicks better with sample data.

First, let’s simulate a non-tidy dataset that matches the structure you described (with name, name2, and a value column):

library(tidyverse)

# Sample messy data
raw_data <- tibble(
  id = rep(1:2, each = 4),
  name = rep(c("age", "age", "height", "height"), 2),
  name2 = rep(c("2020", "2021"), 4),
  value = c(30, 32, 170, 172, 25, 27, 165, 167)
)

print(raw_data)
#> # A tibble: 8 × 4
#>      id name    name2 value
#>   <int> <chr>   <chr> <dbl>
#> 1     1 age     2020     30
#> 2     1 age     2021     32
#> 3     1 height  2020    170
#> 4     1 height  2021    172
#> 5     2 age     2020     25
#> 6     2 age     2021     27
#> 7     2 height  2020    165
#> 8     2 height  2021    167

Let’s test your initial approach first #

Your idea of using spread() then unite() works, but we need to adjust the order slightly to get the merged column names you want:

1. First, use spread() to widen the data by name
2. Then, reshape back to long format temporarily to merge name2 with the widened columns, then re-widen:

# Your adjusted initial approach
tidy_via_spread <- raw_data %>%
  # Step 1: Widen by `name`
  spread(name, value) %>%
  # Step 2: Reshape back to long to combine `name2` with metric columns
  pivot_longer(cols = c(age, height), names_to = "name", values_to = "value") %>%
  # Step 3: Merge `name` and `name2` into a single column name
  unite(combined_col, name, name2, sep = "_") %>%
  # Step 4: Re-widen to final format
  pivot_wider(names_from = combined_col, values_from = value)

print(tidy_via_spread)
#> # A tibble: 2 × 5
#>      id age_2020 age_2021 height_2020 height_2021
#>   <int>    <dbl>    <dbl>       <dbl>       <dbl>
#> 1     1       30       32         170         172
#> 2     2       25       27         165         167

A more streamlined modern approach #

Note that spread() is now marked as "superseded" in tidyr (meaning it’s still functional but replaced by a better tool). The modern alternative is pivot_wider(), which lets us merge name and name2 directly into column names in one step—no back-and-forth reshaping needed:

# Clean, one-step modern method
tidy_data <- raw_data %>%
  pivot_wider(
    names_from = c(name, name2),  # Use both columns to create new column names
    values_from = value,
    names_sep = "_"  # Choose a separator for the merged names
  )

print(tidy_data)
#> # A tibble: 2 × 5
#>      id age_2020 age_2021 height_2020 height_2021
#>   <int>    <dbl>    <dbl>       <dbl>       <dbl>
#> 1     1       30       32         170         172
#> 2     2       25       27         165         167

If you prefer to explicitly merge the columns first (like your original thought), you can also do this:

# Alternative: Merge first, then widen
tidy_data_alt <- raw_data %>%
  unite(combined_col, name, name2, sep = "_") %>%
  pivot_wider(names_from = combined_col, values_from = value)

Key takeaways #

• Your core logic was correct: we need to combine the two identifier columns (name/name2) into meaningful column names while converting from long to wide format.
• pivot_wider() is more flexible than spread() and handles multi-column name creation natively, so it’s the recommended tool now.

内容的提问来源于stack exchange，提问作者Alex