R - converting multiple rows into one based on id (index) column

Question

I am working with a dataframe in R that looks like this:

id	visit_id	value
1	1	234
1	2	120
1	3	125
2	1	456
3	1	128
3	2	245
3	3	546
3	4	340
4	1	300

I am trying to combine rows with the same id into one row, so that each value now becomes a separate column (ie, value1, value2). However, not every id has multiple rows and it is not always the same number of rows. If anyone has any idea how to do this in R?

id	value1	value2	value3	value4
1	234	120	125	NA
2	456	NA	NA	NA
3	128	245	546	340
4	300	NA	NA	NA

score 0 · Answer 1 · answered Sep 23 '21 at 12:24

library(tidyverse)
data <- tibble::tribble(
  ~id, ~visit_id, ~value,
   1L,        1L,   234L,
   1L,        2L,   120L,
   1L,        3L,   125L,
   2L,        1L,   456L,
   3L,        1L,   128L,
   3L,        2L,   245L,
   3L,        3L,   546L,
   3L,        4L,   340L,
   4L,        1L,   300L
  )
data
#> # A tibble: 9 x 3
#>      id visit_id value
#>   <int>    <int> <int>
#> 1     1        1   234
#> 2     1        2   120
#> 3     1        3   125
#> 4     2        1   456
#> 5     3        1   128
#> 6     3        2   245
#> 7     3        3   546
#> 8     3        4   340
#> 9     4        1   300

data %>% pivot_wider(names_from = visit_id, values_from = value, names_prefix = "value")
#> # A tibble: 4 x 5
#>      id value1 value2 value3 value4
#>   <int>  <int>  <int>  <int>  <int>
#> 1     1    234    120    125     NA
#> 2     2    456     NA     NA     NA
#> 3     3    128    245    546    340
#> 4     4    300     NA     NA     NA

^{Created on 2021-09-23 by the reprex package (v2.0.1)}

R - converting multiple rows into one based on id (index) column

1 Answers1