How to get ID numbers directly from pivot_wider

February 2, 2023

I have a dataframe like this:

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

I want to to use pivot_wider to convert df to a dataframe like this:

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

Is there a way to do this with the pivot_wider function? Note that the reviewer ID numbers in the second table are not included in the first table.

>Solution :

library(dplyr)
library(tidyr)
df %>%
  group_by(First, Last) %>%
  mutate(rn = row_number()) %>%
  ungroup() %>%
  pivot_wider(
    c(First, Last), names_from = rn,
    values_from = c(Reviewer, Assessment, Amount),
    names_sep = "_")
# # 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

(order of columns notwithstanding)