MFR MFR - 12 days ago 6
R Question

Find the value for all the ids

I have the following dataset

dts1 <- data.frame (id = c(1,2,3,2,1), transactionID =c ("A","","", "B","A"))


I wish to copy the value of transactionID for all of them

so I want to convert this:

id transactionID
1 1 A
2 2
3 3
4 2 B
5 1 A


to

id transactionID
1 1 A
2 2 B
3 3
4 2 B
5 1 A


Which is my ideal output
Because the transactionID for id 2 is missed in the second row and I replaced it.

I tried this

library(dplyr)
dts1 %>% group_by(id) %>% mutate(transactionID = paste(transactionID, collapse=""))


Which gives me the following:

id transactionID
(dbl) (chr)
1 1 AA
2 2 B
3 3
4 2 B
5 1 AA

Answer

We can use data.table

library(data.table)
setDT(dts1)[, transactionID := if(all(!nzchar(as.character(transactionID)))) transactionID
         else transactionID[nzchar(as.character(transactionID))][1], by = id]
dts1
#   id transactionID
#1:  1             A
#2:  2             B
#3:  3              
#4:  2             B
#5:  1             A
Comments