R - concatenate rows based on conditions?

I created a mapping table in R and have provided an example of what it looks like:

ex <- data.frame("id" = c(rep(1234,7)), "claim" = c(1234, 1367, 1234, 1869, 1234, 1367,1234),                   "code1" = c(24, 61, 28, 21, 20, 29,80), date = c('2019-03-18', '2019-04-12',                                                                 '2019-03-18', '2019-03-18',                                                                 '2019-03-18', '2019-04-12', '2019-03-18'),                   'code2' = c(24,29,24,24,24, 29,24), dx1=c("M234","M123",NA,"M434",NA,NA, NA),                   dx2=c(NA,NA,NA,NA,"M789","Z123", "M999"),                   dx3 = c(NA,NA,"M689",NA,NA,NA, NA))  

Is there any way I could find a way to get this as my final output:

ex2 <- data.frame("id" = c(rep(1234,3)), date = c('2019-03-18', '2019-03-18','2019-04-12'),                'code2' = c(24,24,29),                dx1=c("M234","M434","M123"),                dx2=c("M789",NA,"Z123"),                dx3 = c("M689",NA,NA),                dx4 = c("M999", NA, NA))  

I basically would like for any values in dx2 or dx3 in example 1 to just be added onto the same row corresponding to that code2 value. However, if there are multiple values for code2 in dx1, then I would like to keep them as a separate row.

Is there any way I could do something like this in R? Thanks in advance!

edit: In my mapping table (ex) there are only columns dx1, dx2, dx3. I would like for any multiple value in dx2 or dx3 to be added on as new columns (which is why in ex2 there is now a dx4 column). These changes are grouped by code2. So if there are multiple values in dx2 or dx3 for code 24, then that will determine how many new dx2 columns are created

https://stackoverflow.com/questions/65417755/r-concatenate-rows-based-on-conditions December 23, 2020 at 08:54AM