rdataframedplyrgroup-bytidyr

How to make longer based on ID column, *without* collapsing the remaining columns? - R


Based on this question and answer, that is close to satisfying my needs: R - how to make long rows by matching an element of original rows

df <- data.table::data.table(ID = c(123,123,123,234,456, 234),
             event_type = c("Museum","Concert","Park","Garden","Museum", "Museum"),
             event_info = c("5496:C", "5434:D", "1236:C", "534126:A", "5496:C", "0129:Z"))

and the output I want is:

ID event_type.1 event_type.2 event_type.3 event_info.1 event_info.2 event_info.3 
123      Museum      Concert         Park       5496:C       5434:D       1236:C 
234      Garden       Museum           NA     534126:A       0129:Z           NA
456      Garden           NA           NA       5496:C           NA           NA

In my real data, the min number of times an ID appears is 2x and the maximum is 4x.

per the solution linked, I tried aggregate but also tried a couple of group_by approaches but the issue is that I dont want to combine any columns or make new columns. I want to be able to see, for each ID, what are the pairs (or more than pair) of event_type while retaining the event_info for each of the event_type

Thank you!


Solution

  • First I add a variable instance to track how many times we have encountered a given ID so far. Then I pivot wider using instance and the two value columns. By default, the new column names will first show the value columns (i.e. event_type or event_info) and then _ and the instance number.

    library(tidyverse)
    df |>
      mutate(instance = row_number(), .by = ID) |>
      pivot_wider(names_from = instance, values_from = c(event_type, event_info))
      # add names_sep = "." to get the column name formatting in the OP
    

    Result

    # A tibble: 3 × 7
         ID event_type_1 event_type_2 event_type_3 event_info_1 event_info_2 event_info_3
      <dbl> <chr>        <chr>        <chr>        <chr>        <chr>        <chr>       
    1   123 Museum       Concert      Park         5496:C       5434:D       1236:C      
    2   234 Garden       Museum       NA           534126:A     0129:Z       NA          
    3   456 Museum       NA           NA           5496:C       NA           NA