CODEWITHSUNDEEP
rdata-wrangling
kam
kam

Reputation: 345

Gathering data from two columns into 1 and adding extra NA in the other rows

I got a df like this

    structure(list(id = c(1, 1, 2, 3), datein_1 = c("1998/01/09", 
"2006/03/03", "2015/03/10", "2007/04/10"), dateout_1 = c("1998/01/16", 
"2006/06/21", "2015/03/25", "2007/04/11"), datein_2 = c(NA, NA, 
"2011/09/19", "2006/06/01"), date2_out = c(NA, NA, "2015/03/09", 
"2007/04/09"), date3_in = c(NA, NA, "2015/03/09", NA), date3_out = c(NA, 
NA, "2015/03/26", NA)), class = "data.frame", row.names = c(NA, 
-4L))

and I want to restructure the data as follows

    structure(list(id2 = c(1, 1, 2, 2, 3), datein_1 = c("1998/01/09", 
"2006/03/03", "2015/03/10", NA, "2007/04/10"), dateout_1 = c("1998/01/16", 
"2006/06/21", "2015/03/25", NA, "2007/04/11"), datein_2_3 = c(NA, 
NA, "2011/09/19", "2015/03/09", "2006/06/01"), dateout_2_3 = c(NA, 
NA, "2015/03/09", "2015/03/26", "2007/04/09")), class = "data.frame", row.names = c(NA, 
-5L))

I want the columns detain_2 and detain_3 to be one column and datein_3 and date out_3 into one column.We will have to insert an extra line for id 2 here in the datein_1 and dateout_1.

Upvotes: 0

Views: 39

Answers (1)

dy_by
dy_by

Reputation: 1241

rbind.fill solution should work for you

library(dplyr)
plyr::rbind.fill(df[,1:5] %>%
                   rename(datein_2_3 = datein_2, 
                          dateout_2_3 = date2_out),
                 df[,c(1,6:7)] %>%
                   filter(!is.na(date3_in) | !is.na(date3_out)) %>% 
                   rename(datein_2_3 = date3_in    , 
                          dateout_2_3 = date3_out)) %>% 
  arrange(id, datein_1, datein_2_3) ## suppose thats a proper order


output:
  id   datein_1  dateout_1 datein_2_3 dateout_2_3
1  1 1998/01/09 1998/01/16       <NA>        <NA>
2  1 2006/03/03 2006/06/21       <NA>        <NA>
3  2 2015/03/10 2015/03/25 2011/09/19  2015/03/09
4  2       <NA>       <NA> 2015/03/09  2015/03/26
5  3 2007/04/10 2007/04/11 2006/06/01  2007/04/09

Upvotes: 1

Related Questions