I am testing a small scale scenario before rolling it out in a larger production environment and am experiencing a strange occurrence.
I have 2 data sets:
dtL <- data.table(URN=c(1,2,3,4,5), DonorType=c("Cash","RG","Emergency","Emergency","Cash"))
dtL[,c("EmergVal","EmergDate") := list(as.numeric(NA),as.Date(NA))]
setkey(dtL,URN)
dtR <- data.table(URN = c(1,1,1,2,3,3 ,3 ,4,4, 4,4,5),
class=c(5,5,5,1,5,40,40,5,40,5,40,5),
xx= c(25,50,25,10,100,20,25,20,40,35,20,25),
xdate=as.Date(c("2013-01-01","2013-06-05","2014-05-27","2014-10-14",
"2014-06-09","2014-04-07","2014-10-16",
"2014-07-16","2014-10-21","2014-10-22","2014-09-18","2013-12-19")))
setkey(dtR,URN)
I am wanting to update dtL
where the DonorType
is equal to "Emergency", but only for a subset of records from dtR
. I have seen Update subset of data.table based on join and thus have used that as a foundation for my solution.
dtL[dtR[class==40,list(maxxx=max(xx)),by=URN],
EmergVal := ifelse(DonorType=="Emergency",i.maxxx,as.numeric(NA))]
dtL[dtR[class==40,list(maxdate=max(xdate)),by=URN],
EmergDate := ifelse(DonorType=="Emergency",as.Date(i.maxdate),as.Date(NA)),nomatch=0]
I don't get any errors, however when I look at the data now in dtL
it has changed the datatype for EmergDate
to num
rather than what it originally was (i.e. Date
).
So three questions
- Why has it changed the data type (especially when it is a Date when first created in dtL, and I tell it to put it as a date in my ifelse statement?
- How do I get it to keep the date type when I assign it? or will I have to do some post assignment conversion/castint?
- Is there a clean way I could do my assignment of EmergVal and EmergDate in a single statement given that I don't have a field DonorType in dtR and I don't want to add it in (so can't use a multiple key for the join)?