如何使用pivot_wider为数据添加序号并实现宽表转换

我有如下tibble格式的数据框：

library(tibble)
df <- tribble(~First, ~Last, ~Reviewer, ~Assessment, ~Amount,
              "a", "b", "c", "Yes", 10,
              "a", "b", "d", "No", 8,
              "e", "f", "c", "No", 7,
              "e", "f", "e", "Yes", 6)
df

#> # A tibble: 4 × 5
#>   First Last  Reviewer Assessment Amount
#>   <chr> <chr> <chr>    <chr>       <dbl>
#> 1 a     b     c        Yes            10
#> 2 a     b     d        No              8
#> 3 e     f     c        No              7
#> 4 e     f     e        Yes             6

希望使用pivot_wider函数将其转换为如下格式的数据框：

tribble(~First, ~Last, ~Reviewer_1, ~Assessment_1, ~Amount_1,  ~Reviewer_2, ~Assessment_2, ~Amount_2,
               "a", "b", "c", "Yes", 10, "d", "No", 8,
               "e", "f", "c", "No", 7, "e", "Yes", 6)
#> # A tibble: 2 × 8
#>   First Last  Reviewer_1 Assessment_1 Amount_1 Reviewer_2 Assessment_2 Amount_2
#>   <chr> <chr> <chr>      <chr>           <dbl> <chr>      <chr>           <dbl>
#> 1 a     b     c          Yes                10 d          No                  8
#> 2 e     f     c          No                  7 e          Yes                 6

请问是否可以通过pivot_wider实现该需求？注意目标表中的序号并未包含在原数据中。

完全可以实现，核心思路是先给每组（按First和Last分组）添加序号列，再用pivot_wider完成宽表转换：

1. 添加分组内序号：用dplyr::group_by按First和Last分组，再用dplyr::row_number()生成每组内的序号，这个序号就是目标格式里_1、_2后缀的来源。
2. 执行宽表转换：调用pivot_wider时，指定names_from为刚生成的序号列，values_from为需要扩展的三列（Reviewer、Assessment、Amount），通过names_glue参数设置列名拼接规则，让列名变成{.value}_{name}的格式（{.value}对应原列名，{name}对应序号）。

完整代码如下：

library(tibble)
library(dplyr)
library(tidyr)

df <- tribble(~First, ~Last, ~Reviewer, ~Assessment, ~Amount,
              "a", "b", "c", "Yes", 10,
              "a", "b", "d", "No", 8,
              "e", "f", "c", "No", 7,
              "e", "f", "e", "Yes", 6)

df_wide <- df %>%
  group_by(First, Last) %>%
  mutate(row_id = row_number()) %>%
  ungroup() %>%
  pivot_wider(
    id_cols = c(First, Last),
    names_from = row_id,
    values_from = c(Reviewer, Assessment, Amount),
    names_glue = "{.value}_{name}"
  )

df_wide

运行后得到的结果与目标格式一致：

#> # A tibble: 2 × 8
#>   First Last  Reviewer_1 Reviewer_2 Assessment_1 Assessment_2 Amount_1 Amount_2
#>   <chr> <chr> <chr>      <chr>      <chr>        <chr>            <dbl>    <dbl>
#> 1 a     b     c          d          Yes          No                  10        8
#> 2 e     f     c          e          No           Yes                  7        6

如果需要调整列的顺序（比如把同序号的列放在一起），可以用dplyr::select手动排序：

df_wide %>%
  select(First, Last, Reviewer_1, Assessment_1, Amount_1, Reviewer_2, Assessment_2, Amount_2)

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