R语言：拆分列名将宽格式data.frame转换为长格式

Hey there! Let's tackle this reshaping task with the tidyverse—this is exactly the kind of thing it's built for. Here's a straightforward solution that splits your column names and rearranges the data into your desired format:

Step-by-Step Code #

First, make sure you've loaded the tidyverse package:

library(tidyverse)

Then apply this pipeline to your df_original data frame:

# Define your sample data first (for reference)
df_original <- data.frame(
  id = c(1,2,3),
  variable1_partyx = c(4,5,6),
  variable1_partyy = c(14,15,16),
  variable2_partyx = c(24,25,26),
  variable2_partyy = c(34,35,36)
)

# Reshape to df_goal format
df_goal <- df_original %>%
  # Step 1: Convert wide columns to long format, keeping id as identifier
  pivot_longer(
    cols = -id,
    names_to = c("variable", "party"),
    names_sep = "_"  # Split column names at the underscore
  ) %>%
  # Step 2: Convert back to wide format using the split variable names
  pivot_wider(
    names_from = variable,
    values_from = value
  )

What Each Step Does #

• pivot_longer(cols = -id): Takes all columns except id and "lengthens" the data, creating two new columns: name (the original column name) and value (the corresponding value).
• names_to = c("variable", "party"), names_sep = "_": Splits the name column into two new columns (variable and party) using the underscore as the separator.
• pivot_wider(...): Converts the long data back to wide format, using the values in variable (e.g., variable1, variable2) as new column names, and filling them with the corresponding value entries.

Handling Edge Cases (Multiple Underscores) #

If your column names have more than one underscore (e.g., variable_abc_partyx), use a regular expression with names_pattern instead of names_sep to ensure you split correctly at the last underscore before "party":

df_goal <- df_original %>%
  pivot_longer(
    cols = -id,
    names_to = c("variable", "party"),
    # Regex: Capture everything before the final "_party" as "variable", and the rest as "party"
    names_pattern = "(.*)_(party.*)"
  ) %>%
  pivot_wider(
    names_from = variable,
    values_from = value
  )

Result #

Running the code on your sample data will produce df_goal with this structure:

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