How to collapse long data set into wide data using date

Question

I am very new at R. I would like to collapse data into one row using date. Below is my original data set with three columns. Two rows have same ID. The first Subscription start date is 2017-08-20, the first subscription end is 2018-09-10. The next subscription start date is 2019-05-10 and the end date is NA (as the subscription is going on)

ID | Sbscrption_strt_date | Sbscription_end_date

1234 | 2017-08-20 | 2018-09-10

1234 | 2019-05-10 | NA

I would like to see the result in one row instead of two rows. Two new columns will be made Sbsrciption_strt_date1 and Sbscription_end_date1. The new column Sbsrciption_strt_date1 will have 2019-05-10 and the Sbscrption_end_date1 will have NA

ID | Sbscription_strt_date1 | Sbscription_strt_date1 |Sbscription_end_date |Sbscription_end_date1

1234 | 2017-08-20 | 2018-09-10 |2019-05-10 | NA

I would like to collapse two rows into one. Thanks a lot for the help!

score 2 · Accepted Answer · answered Jul 02 '20 at 20:24

Here is a base R option with reshape

reshape(transform(df,ids = 1:nrow(df)),direction = "wide",idvar = "ID",timevar = "ids")

such that

  ID Enrollement_date.1 Enrollement_End_date.1 Enrollement_date.2
1 XX         2007-02-01             2017-09-01         2018-09-01
  Enrollement_End_date.2
1                   <NA>

Data

df <- structure(list(ID = c("XX", "XX"), Enrollement_date = c("2007-02-01", 
"2018-09-01"), Enrollement_End_date = c("2017-09-01", NA)), class = "data.frame", row.names = c(NA, 
-2L))

How to collapse long data set into wide data using date

1 Answers1