如何使用R的gather函数处理带二级分组列的DataFrame？

I need to use the gather function on a DataFrame that has a two-level grouped column structure (in the example, group1 has 2 sub-columns, group2 has 3 sub-columns). I know how to use gather on regular columns, but I want to retain the grouping information after transformation so I can perform grouping operations as needed later. How can I achieve this?

Original data:

Week    Region   group1 group1 group2 group2 group2
201601  somehing 0      0      0      0      0
201602  somehing 0      0      0      0      0
201603  somehing 0      0      0      0      0
201604  somehing 0      0      79.03  0      20675
201605  somehing 0      0      68.28  0      955157
201606  somehing 0      0      46.13  0      943991
201607  somehing 0      0      0      0      935029
201608  somehing 0      0      0      0      899158
201609  somehing 0      0      0      0      127633
201610  somehing 0      0      0      0      0
201611  somehing 0      0      0      0      0
201612  somehing 0      0      0      0      0
201613  somehing 0      0      0      0      0
201614  somehing 0      94.71  0      0      0

Great question! Since your columns have duplicate group names, the core trick is to first make those column names unique, then extract the group/sub-column details during or after the reshaping process. Here are two practical approaches using tidyr (with dplyr for smooth workflow):

Approach 1: Using gather (the function you specified) #

First, we'll fix duplicate column names with make.unique(), which adds numeric suffixes to duplicates. Then we'll reshape to long format and split the combined key into separate group and sub-column identifiers:

library(tidyr)
library(dplyr)

# Assume your DataFrame is named 'df'
colnames(df) <- make.unique(colnames(df))

df_long <- df %>%
  # Gather all group-related columns into long format
  gather(key = "group_sub", value = "value", starts_with("group")) %>%
  # Split the combined key into group name and sub-column number
  separate(group_sub, into = c("group", "sub_col"), sep = "\\.") %>%
  # Convert sub_col to integer for easier sorting/filtering
  mutate(sub_col = as.integer(sub_col))

The resulting DataFrame will include columns: Week, Region, group (either "group1" or "group2"), sub_col (1/2 for group1; 1/2/3 for group2), and value (the original cell values).

Approach 2: Using pivot_longer (tidyr 1.0+ recommended alternative) #

pivot_longer is more flexible for structured column names. We can use a regular expression to directly extract group and sub-column info without pre-renaming columns:

df_long <- df %>%
  pivot_longer(
    # Target all columns starting with "group"
    cols = starts_with("group"),
    # Split column names into two parts: group and sub-column
    names_to = c("group", "sub_col"),
    # Regex to match "groupX" and the numeric suffix from make.unique()
    names_pattern = "(group\\d+)\\.(\\d+)",
    # Name for the value column
    values_to = "value"
  )

Using the reshaped data for grouping #

With this long-format DataFrame, you can easily run grouped operations like:

• Summarize by main group: df_long %>% group_by(group) %>% summarize(avg_value = mean(value, na.rm = TRUE))
• Analyze by group and sub-column: df_long %>% group_by(group, sub_col) %>% filter(value > 0)

If your original sub-columns had custom names (instead of just duplicates), adjust the sep parameter or names_pattern regex to match your actual column naming structure.

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