New poster but long-time reader. I came accross a problem that I have not found a solution in R. Basically conditional sumif accross two dataframes.
Each data frame has multiple XID and Dates.
What I would like is to to a SUMIF in D1
Can someone please explain how to do it ?
Thanks a lot.
** EDIT*
hi the two dataframes are below:
D1:
structure(list(datecreat = structure(c(17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17295, 17295, 17295, 17295, 17295, 17295, 17295, 17295, 17295, 17295, 17295, 17295, 17295, 17295, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17240, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17309, 17295, 17295), class = "Date"), dateadj = structure(c(17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395, 17395 ), class = "Date"), dlisted = c(155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 155, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 86, 100, 100), adjdlisted = c(105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 105, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 50, 50), XID = c("1661533JPY", "1661533RUE", "1661533MYE", "1661533AUE", "1661533SGE", "1661533BHE", "1661533QAE", "1661533SKE", "1661533AEE", "1661533TWE", "1661533NZE", "1661533USE", "1661533HKE", "1661533CNE", "1661333HKE", "1661333AEE", "1661333BHE", "1661333USE", "1661333MYE", "1661333JPY", "1661333AUE", "1661333SKE", "1661333NZE", "1661333TWE", "1661333QAE", "1661333RUE", "1661333CNE", "1661333SGE", "1534203JPY", "1534203RUE", "1534203TWE", "1534203SKE", "1534203USE", "1534203NZE", "1534203QAE", "1534203HKE", "1534203SGE", "1534203MYE", "1534203AUE", "1534203BHE", "1534203CNE", "1534203AEE", "1536523CNE", "1536523QAE", "1536523SKE", "1536523BHE", "1536523RUE", "1536523AUE", "1536523MYE", "1536523JPY", "1536523HKE", "1536523NZE", "1536523TWE", "1536523USE", "1536523AEE", "1536523SGE", "1536533SKE", "1536533NZE", "1536533HKE", "1536533QAE", "1536533BHE", "1536533AEE", "1536533AUE", "1536533SGE", "1536533RUE", "1536533TWE", "1536533CNE", "1536533MYE", "1536533JPY", "1536533USE", "1536693HKE", "1536693CNE", "1536693SKE", "1536693NZE", "1536693TWE", "1536693MYE", "1536693AEE", "1536693JPY", "1536693QAE", "1536693AUE", "1536693SGE", "1536693USE", "1536693RUE", "1536693BHE", "1536703HKE", "1536703BHE", "1536703CNE", "1536703MYE", "1536703TWE", "1536703NZE", "1536703AUE", "1536703USE", "1536703JPY", "1536703AEE", "1536703SKE", "1536703SGE", "1536703QAE", "1536703RUE", "1724593TWE", "1724593QAE")), .Names = c("datecreat", "dateadj", "dlisted", "adjdlisted", "XID"), row.names = c(NA, 100L), class = "data.frame")
D2:
structure(list(Product.ID = c(1098803, 1098803, 1098803, 1132503, 1132503, 1132503, 1132503, 1138833, 1138833, 1142033, 1145443, 1149133, 1149133, 1149883, 1151313, 1154713, 1154733, 1154733, 1155523, 1155523, 1155883, 1156803, 1158133, 1158883, 1158903, 1158983, 1159943, 1160033, 1160043, 1160763, 1161003, 1163693, 1163693, 1164633, 1165193, 1165193, 1166843, 1166843, 1168183, 1168493, 1170133, 1170313, 1172513, 1173083, 1174213, 1174213, 1174213, 1174213, 1174213, 1174443, 1174473, 1174473, 1174473, 1174473, 1174473, 1174473, 1174523, 1178173, 1178173, 1178233, 1181483, 1181483, 1181483, 1182023, 1182023, 1182023, 1182083, 1185163, 1185163, 1185303, 1187793, 1187793, 1187793, 1187793, 1187793, 1187873, 1189703, 1189703, 1190023, 1190353, 1190353, 1190363, 1190363, 1190363, 1190403, 1193383, 1193383, 1193433, 1193473, 1193473, 1196043, 1197753, 1198223, 1198223, 1198223, 1198223, 1198223, 1198223, 1198223, 1198223), Currency = structure(c(4L, 8L, 17L, 4L, 5L, 8L, 17L, 17L, 18L, 4L, 17L, 2L, 17L, 17L, 4L, 17L, 6L, 18L, 13L, 14L, 17L, 17L, 4L, 17L, 5L, 2L, 17L, 17L, 17L, 17L, 4L, 5L, 7L, 4L, 2L, 17L, 8L, 17L, 11L, 5L, 17L, 6L, 18L, 2L, 4L, 6L, 13L, 15L, 17L, 4L, 2L, 4L, 7L, 9L, 17L, 18L, 6L, 4L, 17L, 17L, 9L, 17L, 18L, 2L, 9L, 18L, 17L, 7L, 17L, 2L, 6L, 7L, 14L, 17L, 18L, 9L, 4L, 17L, 7L, 4L, 18L, 4L, 5L, 18L, 7L, 4L, 15L, 17L, 5L, 17L, 17L, 4L, 2L, 4L, 5L, 6L, 7L, 10L, 15L, 17L), .Label = c("AEE", "AUE", "BHE", "CAD", "CNE", "EUR", "GBP", "HKE", "JPY", "MYE", "NZE", "QAE", "RUE", "SGE", "SKE", "TWE", "USD", "USE"), class = "factor"), Page.Views = c(1L, 1L, 1L, 3L, 2L, 4L, 4L, 14L, 1L, 1L, 1L, 1L, 3L, 1L, 5L, 1L, 1L, 1L, 1L, 1L, 1L, 4L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 1L, 1L, 1L, 1L, 2L, 1L, 6L, 3L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 1L, 1L, 3L, 10L, 3L, 5L, 6L, 1L, 1L, 5L, 2L, 1L, 1L, 1L, 1L, 1L, 5L, 2L, 2L, 1L, 1L, 1L, 2L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 1L, 2L, 2L, 10L, 6L, 1L, 4L, 1L, 1L, 12L), date = structure(c(17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360, 17360), class = "Date"), XID = c("1098803CAD", "1098803HKE", "1098803USD", "1132503CAD", "1132503CNE", "1132503HKE", "1132503USD", "1138833USD", "1138833USE", "1142033CAD", "1145443USD", "1149133AUE", "1149133USD", "1149883USD", "1151313CAD", "1154713USD", "1154733EUR", "1154733USE", "1155523RUE", "1155523SGE", "1155883USD", "1156803USD", "1158133CAD", "1158883USD", "1158903CNE", "1158983AUE", "1159943USD", "1160033USD", "1160043USD", "1160763USD", "1161003CAD", "1163693CNE", "1163693GBP", "1164633CAD", "1165193AUE", "1165193USD", "1166843HKE", "1166843USD", "1168183NZE", "1168493CNE", "1170133USD", "1170313EUR", "1172513USE", "1173083AUE", "1174213CAD", "1174213EUR", "1174213RUE", "1174213SKE", "1174213USD", "1174443CAD", "1174473AUE", "1174473CAD", "1174473GBP", "1174473JPY", "1174473USD", "1174473USE", "1174523EUR", "1178173CAD", "1178173USD", "1178233USD", "1181483JPY", "1181483USD", "1181483USE", "1182023AUE", "1182023JPY", "1182023USE", "1182083USD", "1185163GBP", "1185163USD", "1185303AUE", "1187793EUR", "1187793GBP", "1187793SGE", "1187793USD", "1187793USE", "1187873JPY", "1189703CAD", "1189703USD", "1190023GBP", "1190353CAD", "1190353USE", "1190363CAD", "1190363CNE", "1190363USE", "1190403GBP", "1193383CAD", "1193383SKE", "1193433USD", "1193473CNE", "1193473USD", "1196043USD", "1197753CAD", "1198223AUE", "1198223CAD", "1198223CNE", "1198223EUR", "1198223GBP", "1198223MYE", "1198223SKE", "1198223USD")), .Names = c("Product.ID", "Currency", "Page.Views", "date", "XID"), row.names = c(NA, 100L), class = "data.frame")
What I would like is to * add new column in D1 with the total of D2$Page.Views IF D1$XID == D2$XID and D2$date is < D1$dateadj.*
Thanks
Though data mentioned in OP seems inaccurate to me as matching XID values are not found between D1 and D2. mapply function can be used to perform the conditional sum.
#Function accept value of XID and dateadj and return sum from D2
Summ <- function(x, y){
sum(D2[D2$XID == x & D2$date < y, "Page.Views"])
}
#use mapply function to call Summ on row-wise data on D1
D1$TPage.View <- mapply(Summ, D1$XID, D1$dateadj)