Data wrangling widen and consolidate rows

Question

I want to widen a number of rows and then consolidate the rows using R.

Group ID	Person	Role	Payoff
group_1	person_1	A	10
group_1	person_2	B	20
group_2	person_3	A	14
group_2	person_4	B	14
group_3	person_5	A	34
group_3	person_6	B	48

When I use pivot_wider(names_from = Role, values_from = c(Person, Payoff)) the data looks like this:

Group ID	A	B	A Payoff	B Payoff
group_1	person_1	NA	10	NA
group_1	NA	person_2	NA	20
group_2	person_3	NA	14	NA
group_2	NA	person_4	NA	14
group_3	person_5	NA	34	NA
group_3	NA	person_6	Na	48

However, I want data to look the table below. Is there a different command I should be using or another way pivot_wider should be used?

Group ID	A	B	A Payoff	B Payoff
group_1	person_1	person_2	10	20
group_2	person_3	person_4	14	14
group_3	person_5	person_6	34	48

I also tried code similar to the following commands

data %>%
  group_by('session_group_id') %>% 
  pivot_wider(names_from = Role, values_from = c(Person, Payoff))

and

data %>%
  group_by('session_group_id') %>%
  pivot_wider(names_from = Role,
              values_from = c(Person, Payoff),
              values_fn = list)

Answer 1

Using your code, I get the right answer (tidyr v 1.3.0, dplyr v 1.1.0):

library(tidyr)
library(dplyr)
dat <- read.table(header=TRUE, 
           text="
Group_ID    Person  Role    Payoff
group_1 person_1    A   10
group_1 person_2    B   20
group_2 person_3    A   14
group_2 person_4    B   14
group_3 person_5    A   34
group_3 person_6    B   48
")
dat %>% pivot_wider(names_from = Role, values_from = c(Person, Payoff))
#> # A tibble: 3 × 5
#>   Group_ID Person_A Person_B Payoff_A Payoff_B
#>   <chr>    <chr>    <chr>       <int>    <int>
#> 1 group_1  person_1 person_2       10       20
#> 2 group_2  person_3 person_4       14       14
#> 3 group_3  person_5 person_6       34       48

^{Created on 2023-04-03 with reprex v2.0.2}