group_by and pivot_wider combination

Question

I have a data frame with three columns:

responseid = c(rep("R_cJ6dbDcou", 6), rep("R_a3LWPfGC", 6), rep("R_e3b9tIJo", 6))
year = c(rep(2022, 6), rep(2022, 6), rep(2022, 6)) 
code = c(000, 0500, 0033, 0036, 0102, 0486, 000, 0500, 0032, 
         0039, 0101, 0466, 000, 0500, 0012, 0049, 0111, 0446)
Data = data.frame(responseid , year , code)

I would like it to appear with each row as a unique responseid, and each code related to that responseid as a column:

responseid	code1	code2	code3	code4	code5	code6
R_cJ6dbDcou	000	0500	0033	0036	0102	0486
R_a3LWPfGC	000	0500	0032	0039	0101	0466

I've tried grouping by responseid then pivot_wider, but this doesn't seem to work. I end up with each responseid as a column and a list in each cell with the codes for that responseid.

Answer 1

I didn't get from your question whether you expect each column to contain the same code or not.

If yes, the following should work:

library(tidyr)
library(dplyr)

responseid = c(rep("123xyz", 6), rep("456abc", 6), rep("789def", 6)) 
year = c(rep(2022, 6), rep(2021, 6), rep(2022, 6))
code = c(000, 0500, 0033, 0036, 0102, 0486, 000, 0500, 0032, 0039, 0101, 0466, 000, 0500, 0012, 0049, 0111, 0446) 
Data = data.frame(responseid , year , code = as.character(code))

Data_name <- Data |> 
  mutate(name = paste("code", code, sep = "_"))

Data_piv <- Data_name |> 
  pivot_wider(id_cols = responseid,
              values_from = code,
              names_from = name)

> Data_piv
# A tibble: 3 × 15
  responseid code_0 code_500 code_33 code_36 code_102 code_486 code_32 code_39 code_101 code_466 code_12
  <chr>      <chr>  <chr>    <chr>   <chr>   <chr>    <chr>    <chr>   <chr>   <chr>    <chr>    <chr>  
1 123xyz     0      500      33      36      102      486      NA      NA      NA       NA       NA     
2 456abc     0      500      NA      NA      NA       NA       32      39      101      466      NA     
3 789def     0      500      NA      NA      NA       NA       NA      NA      NA       NA       12     
# ℹ 3 more variables: code_49 <chr>, code_111 <chr>, code_446 <chr>

Else, this should work:

Data_name2 <- Data |> 
  group_by(responseid) |>
  mutate(i = 1:n()) |>
  mutate(name = paste("code", i, sep = "_"))

Data_piv2 <- Data_name2 |> 
  pivot_wider(id_cols = responseid,
              values_from = code,
              names_from = name)

> Data_piv2
# A tibble: 3 × 7
# Groups:   responseid [3]
  responseid code_1 code_2 code_3 code_4 code_5 code_6
  <chr>      <chr>  <chr>  <chr>  <chr>  <chr>  <chr> 
1 123xyz     0      500    33     36     102    486   
2 456abc     0      500    32     39     101    466   
3 789def     0      500    12     49     111    446   
> 

Answer 2

a data.table approach

library(data.table)
dcast(setDT(Data), 
      responseid ~ paste0("code", Data[, seq_len(.N), by=responseid]$V1), 
      value.var = "code")
# Key: <responseid>
#    responseid code1 code2 code3 code4 code5 code6
#        <char> <num> <num> <num> <num> <num> <num>
# 1:     123xyz     0   500    33    36   102   486
# 2:     456abc     0   500    32    39   101   466
# 3:     789def     0   500    12    49   111   446

Answer 3

Base

Updated to meet edited sample data.

> aggregate(code ~ responseid, data = Data[-2], FUN = I)
   responseid code.1 code.2 code.3 code.4 code.5 code.6
1  R_a3LWPfGC      0    500     32     39    101    466
2 R_cJ6dbDcou      0    500     33     36    102    486
3  R_e3b9tIJo      0    500     12     49    111    446

Answer 4

Another dplyr/tidyr approach:

library(dplyr)                                              
library(tidyr)


df %>%
  select(-year) %>%
  mutate(id = row_number(), .by = responseid) %>%
  pivot_wider(values_from = code,
              names_from = id,
              names_glue = "{.value}{id}")
#> # A tibble: 3 × 7
#>   responseid code1 code2 code3 code4 code5 code6
#>   <chr>      <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 123xyz         0   500    33    36   102   486
#> 2 456abc         0   500    32    39   101   466
#> 3 789def         0   500    12    49   111   446

Data

df <- data.frame(
  responseid = c(rep("123xyz", 6), rep("456abc", 6), rep("789def", 6)),
  year = c(rep(2022, 6), rep(2021, 6), rep(2022, 6)),
  code = c(000, 0500, 0033, 0036, 0102, 0486, 000, 0500,
           0032, 0039, 0101, 0466, 000, 0500, 0012, 0049, 0111, 0446) 
)