countif - adding up numbers after an if operation in R -

i have set of data consists of number of courses of medication patient has taken @ date.

subject<-c(111,111,111,222,222,333,333,333,333) date<-as.date(c("2010-12-12","2011-12-01","2009-8-7","2010-5-7","2011-3-7","2011-8-5","2013-8-27","2016-9-3","2011-8-5")) medicationcourses<-c(1,0,na,3,4,2,4,5,6)  data<-data.frame(subject,date,medicationcourses)  data    subject      date        medicationcourses 1     111    2010-12-12             1 2     111    2011-12-01             0 3     111    2009-08-07             na 4     222    2010-05-07             3 5     222    2011-03-07             4 6     333    2011-08-05             2 7     333    2013-08-27             4 8     333    2016-09-03             5 9     333    2011-08-05             6 

i have hospital admission date.

hospitalsubject<-c(111,222,333) admissiondate<-as.date(c("2011-12-31","2013-12-31","2013-12-31"))  hospitaldata<-data.frame(hospitalsubject,admissiondate)  hospitaldata    hospitalsubject admissiondate 1             111    2011-12-31 2             222    2013-12-31 3             333    2013-12-31 

i want summarise number of medication courses on or before admission date , produce following outcome:

subject    admissiondate   totalmedicationcourses  111         2011-12-31            1  222         2013-12-31            7  333         2013-12-31            12 

i wonder if let me know how in r? novice user of r guidance appreciated!

one option merge 2 datasets subject/hospitalsubject in 2 datasets, subset rows date <= admissiondate, , sum of 'medicationcourses' grouped 'subject/admissiondate' aggregate

d1 <- subset(merge(data, hospitaldata, by.x='subject',             by.y='hospitalsubject'), date <= admissiondate)  aggregate(medicationcourses~subject+admissiondate, d1, sum,                na.rm=true, na.action=null) #  subject admissiondate medicationcourses #1     111    2011-12-31                 1 #2     222    2013-12-31                 7 #3     333    2013-12-31                12 

or can use data.table converting 'data.frame' 'data.table' (setdt(data)), set key 'subject' (setkey(), , join hospitaldata, filter rows date <= admissiondate , sum of 'medicationcourses', grouped 'subject' , 'admissiondate'.

library(data.table) setkey(setdt(data), subject)[hospitaldata][date <= admissiondate,    list(totalmedicationcourses=sum(medicationcourses, na.rm=true)),         list(subject, admissiondate)] #    subject admissiondate totalmedicationcourses #1:     111    2011-12-31                      1 #2:     222    2013-12-31                      7 #3:     333    2013-12-31                     12 

or similar approach dplyr

library(dplyr)   left_join(data, hospitaldata, by=c('subject'='hospitalsubject')) %>%         filter(date <=admissiondate) %>%          group_by(subject, admissiondate) %>%          summarise(totalmedicationcourses=sum(medicationcourses, na.rm=true))