0

I have a dataset with IDs and Value where one ID could take multiple values. Currently, the same ID is repeated row-wise when it has multiple values, but I hope to keep one ID per row, adding more columns when necessary. Here's a reproducible example:

df <- data.frame(id = c(1,1,1,2,3,3),
                 val = c(10:15))

What I want is

df2 <- data.frame(id = c(1:3),
                  val1 = c(10, 13, 14),
                  val2 = c(11, "", 15), 
                  val3 = c(12, "", ""))

I've tried to get it done using pivot_wider as below but it had two problems: (1) couldn't figure out how to create a column name of my own; (2) wanted to add columns but it creates a list within a cell.

library(tidyr)
df %>% pivot_wider(values_from = val, values_fn = list, names_prefix = "x")
Darren Tsai
  • 32,117
  • 5
  • 21
  • 51
qnp1521
  • 806
  • 6
  • 20

1 Answers1

2

You need to add a sequence column:

df %>% group_by(id) %>%
  mutate(time=row_number()) %>%
  pivot_wider(names_from=time, values_from = val, names_prefix = "val")

# A tibble: 3 x 4
# Groups:   id [3]
     id  val1  val2  val3
  <dbl> <int> <int> <int>
1     1    10    11    12
2     2    13    NA    NA
3     3    14    15    NA

library(tidyr)
library(dplyr)
Edward
  • 10,360
  • 2
  • 11
  • 26