AverageIfs Conversion Two Datasets, R

Language: Layout:

EG_df <- data.frame(id = c("red_blue", "white_blue", "red_yellow","white_yellow", "brown_blue", "brown_yellow"), 
                    StartDate = as.Date(c('2019-1-1','2019-3-1','2019-7-1','2018-1-1','2018-3-1','2018-7-1')),
                    EndDate = as.Date(c('2019-6-1','2019-12-1','2019-8-1','2018-1-1','2018-3-1','2018-7-1'))
                    )

source <- data.frame(source.id = c("red_blue", "red_blue", "red_blue","brown_yellow", "brown_yellow", "brown_yellow"),
                     source.Date = as.Date(c('2019-1-1','2019-2-1','2019-3-1','2018-7-1','2018-8-1','2018-9-1')),
                     source.Value = c(22,56,32,31,14,7)
                    )

# MERGE > SUBSET > AGGREGATE
agg_df <- aggregate(cbind(avgValue=source.Value) ~ id + StartDate + EndDate,
                    subset(merge(EG_df, source, by.x="id", by.y="source.id", all.x=TRUE),
                           source.Date >= StartDate & source.Date <= EndDate),
                    FUN=mean)

# MERGE WITH ORIGINAL DATASET
merge(EG_df, agg_df, by=c("id", "StartDate", "EndDate"), all.x=TRUE)

[ + ] Show input
Absolute running time: 2.13 sec, cpu time: 0.79 sec, memory peak: 32 Mb, absolute service time: 2,14 sec