Combining rows based on the id in R

Maddy picture Maddy · Jun 29, 2015 · Viewed 11.5k times · Source

My data,

Id|date1|date2   
1|2008-10-01|NA        
1|NA|2008-10-02     
1|NA|2008-10-03     
2|2008-10-02|NA
2|NA|2008-10-03

I want output this way,

Id|date1|date2|date3    
1|2008-10-01|2008-10-02|2008-10-03        
2|2008-10-02|2008-10-03 

I tried using aggregate and dcast but they are making date into numeric format and na's are still not avoided.

Answer

David Arenburg picture David Arenburg · Jun 29, 2015

You could do this quite easily using data.table though it will get more complicated if the number of non-missing values isn't equal between the columns

library(data.table)
setDT(df)[, lapply(.SD, na.omit), by = Id]
#   Id      date1       date2
# 1:  1 2008-10-02 2008-10-02 
# 2:  2 2008-10-02 2008-10-02