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
  • 15,872
  • 4
  • 15
  • 40
qnp1521
  • 675
  • 3
  • 13

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
  • 9,369
  • 2
  • 10
  • 24