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

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

1 answer

  • answered 2021-09-23 12:24 danlooo

    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)

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum