如何在含多组带_1/_2后缀的重复列名数据中使用gather()？

Got it, let's break this down step by step to reshape your duplicated columns using gather() (and I'll also share a more modern alternative with pivot_longer() since it's more intuitive for this scenario).

First, let's replicate your exact data scenario so we're working with the same setup:

# Load required packages
library(tidyverse)

# Create a sample dataset with duplicate column names
original_data <- tibble(
  A = 1:3, B = 4:6, C = 7:9, D = 10:12,
  A = 13:15, B = 16:18, C = 19:21, D = 22:24,
  A = 25:27, B = 28:30, C = 31:33, D = 34:36
)

# Write to CSV, then read back with read_csv (auto-adds _1/_2 suffixes)
write_csv(original_data, "duplicate_cols.csv")
df <- read_csv("duplicate_cols.csv")

After importing, your column names will look like: A, B, C, D, A_1, B_1, C_1, D_1, A_2, B_2, C_2, D_2

Using gather() to Reshape the Data #

The core idea is to first pull all columns into a long format, then parse the auto-generated column names to recover the original column name and which copy it is.

# Step 1: Gather all columns into key-value pairs
tidy_df <- df %>%
  gather(key = "col_key", value = "value") %>%
  # Step 2: Extract original column name and copy number from the key
  mutate(
    original_col = str_remove(col_key, "_\\d+$"),  # Remove _1/_2 suffix
    copy_id = case_when(
      # Mark the first (unsuffixed) column as "1"
      !str_detect(col_key, "_\\d+$") ~ "1",
      # Extract the number from suffixes like _1/_2
      TRUE ~ str_extract(col_key, "\\d+")
    )
  ) %>%
  # Reorder columns for clarity
  select(original_col, copy_id, value)

This will give you a clean long-format table where each row represents one value from a specific original column and copy number. For example:

Bonus: Using pivot_longer() (Modern Alternative) #

If you're using a newer version of tidyr, pivot_longer() is more flexible and readable for this task. It lets you parse column names directly during reshaping:

tidy_df_pivot <- df %>%
  pivot_longer(
    cols = everything(),  # Target all columns
    # Split column names into original name and copy ID using regex
    names_to = c("original_col", "copy_id"),
    names_pattern = "(.*?)(_\\d+)?$",  # Capture base name and optional _# suffix
    values_to = "value"
  ) %>%
  # Clean up copy_id (replace NA for unsuffixed columns, remove underscore)
  mutate(copy_id = str_remove(copy_id, "_") %>% replace_na("1"))

This achieves the same result as gather() but with fewer steps and more explicit logic.

If You Want Wide Format (Optional) #

If you prefer to keep each original column as a separate column but organize copies as rows, you can use spread() after gathering:

wide_tidy_df <- tidy_df %>%
  spread(key = original_col, value = value)

This will give you rows for each copy, with columns A, B, C, D holding the values from each respective copy.

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