在R中将多变量多年份列数据重塑为扁平文件的方法

Perfect, let's break down exactly how to reshape your wide-format data into that flat, long-format structure using tidyr—I'll cover both the gather() function you asked about, plus the more modern pivot_longer() which is now the recommended approach in newer tidyr versions.

First, let's replicate your original dataset correctly (including that blank column between Variable 1 and Variable 2):

library(tidyr)
library(dplyr) # For the pipe operator (%>%), which makes code cleaner to read

# Build your original dataset
original_data <- tibble(
  行ID = c("A", "B", "C", "D"),
  `Variable1_2008` = c(1, 2, 3, 4),
  `Variable1_2009` = c(5, 6, 7, 8),
  `Variable1_2010` = c(9, 10, 11, 12),
  `Variable1_2011` = c(13, 14, 15, 16),
  空白列 = rep(NA, 4), # The empty column between Variable 1 and 2
  `Variable2_2008` = c(5, 25, 45, 65),
  `Variable2_2009` = c(10, 30, 50, 70),
  `Variable2_2010` = c(15, 35, 55, 75),
  `Variable2_2011` = c(20, 40, 60, 80)
)

Step 1: Clean up the empty column #

First, we'll drop that blank column since it doesn't hold any useful data:

clean_data <- original_data %>% select(-空白列)

Option 1: Using gather() (the function you mentioned) #

gather() takes all your wide columns and collapses them into two key-value pairs. Then we'll split the combined key into separate Variable and Year columns:

flat_data_gather <- clean_data %>%
  # Gather all columns except 行ID into a key-value pair
  gather(key = "Variable_Year", value = "Data", -行ID) %>%
  # Split the "Variable1_2008" style keys into Variable and Year
  separate(Variable_Year, into = c("Variable", "Year"), sep = "_") %>%
  # Reorder columns to match your desired format
  select(行ID, Variable, Year, Data)

Option 2: Using pivot_longer() (modern tidyr approach) #

pivot_longer() is more flexible and intuitive for this kind of reshaping. It lets you split column names into multiple variables directly:

flat_data_pivot <- clean_data %>%
  pivot_longer(
    cols = -行ID, # Target all columns except 行ID
    names_to = c("Variable", "Year"), # Split column names into these two variables
    names_sep = "_", # Split at the underscore in column names
    values_to = "Data" # Name the column holding the data values
  ) %>%
  # Reorder columns to match your desired output
  select(行ID, Variable, Year, Data)

What the final output looks like #

If you run head(flat_data_pivot), you'll get exactly the structure you want:

# A tibble: 8 × 4
  行ID  Variable  Year  Data
  <chr> <chr>     <chr> <dbl>
1 A     Variable1 2008     1
2 A     Variable1 2009     5
3 A     Variable1 2010     9
4 A     Variable1 2011    13
5 A     Variable2 2008     5
6 A     Variable2 2009    10
7 A     Variable2 2010    15
8 A     Variable2 2011    20

Quick note if your original column names don't have underscores #

If your raw data's column names are just "2008", "2009", etc. under each Variable header (no underscore), you'll first need to rename them to include the Variable prefix. For example:

# Rename columns to add Variable1/Variable2 prefixes
colnames(clean_data) <- c("行ID", 
                          "Variable1_2008", "Variable1_2009", "Variable1_2010", "Variable1_2011",
                          "Variable2_2008", "Variable2_2009", "Variable2_2010", "Variable2_2011")

Then proceed with the reshaping steps above.

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