r - Pivot Longer 将值分布到多个列中

Question

已经有很多关于使用 pivot longer 和 pivot wider 以我需要的方式 reshape 数据的问题，但没有一个能得到正确的答案。过去，我成功地结合使用了更长的旋转轴和更宽的旋转轴来获得我需要的具有不同形状数据的输出，但这种方法在这里不太奏效。

library(tidyverse)
sampleData <- tribble(
  ~ID, ~ExamCode_01, ~ExamGrade_01, ~AdminYear_01, ~ExamCode_02, ~ExamGrade_02, ~AdminYear_02,
  123, 4, 4, 22, 26, 5, 22,
  456, 26, 3, 22, 83, 3, 21,
  789, 26, 5, 22, NA, NA, NA
) # In the actual data, these go up to 30 potential Exam Code/Grade/Year combinations

## Attempt to distribute column names/values using names_sep as informed by other StackOverflow posts. I've also wrestled with names_pattern regex but can't get anything more useful than this.
sampleData_long_try1 <- sampleData %>%
  pivot_longer(cols = -1,
               names_to = c("ExamCode", "ExamGrade", "AdminYear"),
               names_sep = "_")

## This approach uses pivot longer/wider in combination to get closer, but the output won't preserve duplicate IDs, instead returning list-values in columns, which is not helpful
sampleData_long_try2 <- sampleData %>%
  pivot_longer(cols = -ID,
               names_to = "type",
               values_to = "value",
               values_drop_na = T)

sampleData_final <- sampleData_long_try2 %>%
  mutate(type = str_replace_all(type, "[:digit:]", "")) %>% # name repair so that it doesn't just  pivot wider back into the original format
  pivot_wider(names_from = "type",
              values_from = "value") # can't figure out how to preserve unique ID/Exam Code combinations rather than consolidating the data into one row per ID with list-cols

## The desired final output needs to have each subject's exam code, score, and administration year on one row, as shown below.
desired_output <- tribble(
  ~ID, ~ExamCode, ~ExamGrade, ~AdminYear,
  123, 4, 4, 22,
  123, 26, 5, 22,
  456, 26, 3, 22,
  456, 83, 3, 21,
  789, 26, 5, 22,
)

如果这个问题确实是重复的，请指出正确的方向。我找到的最接近的答案:

• Pivot_longer for multiple columns of repeated measurements data
• pivot_wider in R with multiple columns, but keep the name of former variables in separate columns

感谢您提供的任何帮助!

Answer 1

使用pivot_longer:

sampleData %>%
  pivot_longer(-ID, names_to='.value', names_pattern='([A-Za-z]+)', values_drop_na=TRUE)

# A tibble: 5 x 4
     ID ExamCode ExamGrade AdminYear
  <dbl>    <dbl>     <dbl>     <dbl>
1   123        4         4        22
2   123       26         5        22
3   456       26         3        22
4   456       83         3        21
5   789       26         5        22