rdatasettransformationwide-column-store

R dataset from long to wide - under a specific condition


I want to transform a long and chronological dataset into a wide but chronological dataset by ID Let's see an example:

ID Product Date
1 Bike 1/1/2000
1 Tire 2/1/2000
2 Car 15/2/2000
2 Seat 17/2/2000
1 Chronometer 20/2/2000

Into the following table:

ID 1st 2nd 3rd etc
1 Bike Tire Chronometer
2 Car Seat

The order of the products bought must not be changed.

Can you help me guys?

Thanks a lot!


Solution

  • arrange the data for each ID and Date, give a unique row number for each ID and cast the data to wide format.

    library(dplyr)
    
    df %>%
      mutate(Date = as.Date(Date, '%d/%m/%Y')) %>%
      arrange(ID, Date) %>%
      group_by(ID) %>%
      mutate(row = row_number()) %>%
      tidyr::pivot_wider(names_from = row, values_from = c(Product, Date))
    
    #     ID Product_1 Product_2 Product_3   Date_1     Date_2     Date_3    
    #  <int> <chr>     <chr>     <chr>       <date>     <date>     <date>    
    #1     1 Bike      Tire      Chronometer 2000-01-01 2000-01-02 2000-02-20
    #2     2 Car       Seat      NA          2000-02-15 2000-02-17 NA        
    

    data

    df <- structure(list(ID = c(1L, 1L, 2L, 2L, 1L), Product = c("Bike", 
    "Tire", "Car", "Seat", "Chronometer"), Date = c("1/1/2000", "2/1/2000", 
    "15/2/2000", "17/2/2000", "20/2/2000")), class = "data.frame", row.names = c(NA, -5L))