在R语言中如何依据行列条件实现单元格选择性映射并完成数据格式转换？

Great question! This is a common wide-to-wide data transformation task, and the key to solving it (and making it generalizable) is understanding how the regular expression in pivot_longer's names_pattern parameter works. Let's break this down step by step.

1. First: Understand the Regex X\\d+(.*) #

This regex is used to split your column names into meaningful groups, which is critical for pivot_longer to reorganize your data correctly. Here's what each part does:

• X: Matches the fixed starting letter in your column names (like X1, X5)
• \\d+: Matches one or more digits (the \\ is an escape character required in R regex to represent a single \)
• (.*): This is a capturing group that grabs everything after the X+digits part. This captured text becomes the name of the column in the long-format data (thanks to names_to = '.value').

Example Breakdown: #

• For your first dataset's column X1Score: X\\d+ matches X1, and (.*) captures Score
• For column X1 in the first dataset: X\\d+ matches X1, and (.*) captures an empty string (which we'll rename later to Name)

Your modified dataset uses column names like X1_Name/X1_Score, so the original regex won't work—we need to adjust it to X\\d+_(.*) to account for the underscore separator. This way:

• X1_Name gets split into X1_ (matched by X\\d+_) and Name (captured by (.*))
• X1_Score gets split into X1_ and Score

2. Solution for Your Original Dataset #

Your first dataset has columns like X1 (variable names) and X1Score (scores). Here's the working code:

library(tidyr)
library(dplyr)

# Original dataset
df <- structure(list(ID = 1:6, X1 = c("Name_A", "Name_C", "Name_B", "Name_C", "Name_A", "Name_C"), X1Score = c(4.58, 5.35, 5.59, 5.36, 5.39, 4.91), X2 = c("Name_C", "Name_B", "Name_C", "Name_B", "Name_B", "Name_A"), X2Score = c(4.79, 5.33, 5.48, 5.04, 5.27, 4.99), X3 = c("Name_B", "Name_A", "Name_A", "Name_A", "Name_C", "Name_B"), X3Score = c(5.22, 5.61, 4.89, 4.93, 5.11, 5.01), Name_A = c(NA, NA, NA, NA, NA, NA), Name_B = c(NA, NA, NA, NA, NA, NA), Name_C = c(NA, NA, NA, NA, NA, NA)), row.names = c(NA, -6L), class = "data.frame")

df_transformed <- df %>%
  # Remove empty target columns first to avoid conflicts
  select(-Name_A, -Name_B, -Name_C) %>%
  # Convert to long format: group variable names and scores
  pivot_longer(
    cols = -ID,
    names_to = ".value",
    names_pattern = "X\\d+(.*)"
  ) %>%
  # Rename the empty column (from X1/X2/X3) to "Name"
  rename(Name = "") %>%
  # Convert back to wide format: map names to columns, scores to values
  pivot_wider(names_from = Name, values_from = Score)

print(df_transformed)

This will give you the clean wide format where each ID has its corresponding scores in Name_A, Name_B, Name_C.

3. Solution for Your Modified Dataset (Underscore-Separated Columns) #

For the dataset with columns like X1_Name/X1_Score, adjust the regex to account for the underscore, and the code simplifies:

library(tidyr)
library(dplyr)

# Modified dataset
df <- structure(list(ID = 1:6, X1_Name = c("Name_A", "Name_C", "Name_B", "Name_C", "Name_A", "Name_C"), X1_Score = c(4.58, 5.35, 5.59, 5.36, 5.39, 4.91), X5_Name = c("Name_C", "Name_B", "Name_C", "Name_B", "Name_B", "Name_A"), X5_Score = c(4.79, 5.33, 5.48, 5.04, 5.27, 4.99), X19_Name = c("Name_B", "Name_A", "Name_A", "Name_A", "Name_C", "Name_B"), X19_Score = c(5.22, 5.61, 4.89, 4.93, 5.11, 5.01)), row.names = c(NA, -6L), class = "data.frame")

df_transformed <- df %>%
  pivot_longer(
    cols = -ID,
    names_to = ".value",
    # Adjust regex to match X+digits+underscore, capture Name/Score
    names_pattern = "X\\d+_(.*)"
  ) %>%
  pivot_wider(names_from = Name, values_from = Score)

print(df_transformed)

This code won't throw errors because the regex correctly captures Name and Score as the column names for the long-format data, which we then pivot back to wide.

4. Generalizable Takeaway #

To make this work for any similar dataset:

1. Identify column name patterns: Look for consistent prefixes (like X+digits) and suffixes (like Name/Score)
2. Adjust the regex: Match the prefix, then use a capturing group to grab the suffix that defines the data type (variable name vs. score)
3. Leverage pivot_longer + pivot_wider: Convert to long format to group related data, then back to wide to get your target structure

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