0

I have ExpandedGrid 11760 obs of 4 variables:

Date - date format
Device - factor
Creative - factor
Partner - factor

I also have a MediaPlanDF 215 obs of 6 variables:

Interval - an interval of dates I created using lubridate
Partner - factor
Device - factor
Creative - factor
Daily Spend - num
Daily Impressions - num

Here is my trouble.

I need to sum daily spend and daily impressions in respective columns in MediaPlanDF, based on the following 2 criteria:

Criterion 1

- ExpandedGrid$Device matches MediaPlanDF$Device
- ExpandedGrid$Creative matches MediaPlanDF$Creative
- ExpandedGrid$Partner matches MediaPlanDF$Partner

Criterion 2

- ExpandedGrid$Date falls within MediaPlanDF$Interval

Now I can pull this off for each criteria on its own, but I am having the hardest time putting them together without getting errors, and my search for answers hasn't ended in very much success (a lot of great examples but nothing I have the skill to adapt to my context). I've tried a variety of methods but my mind is starting to wander towards overly complicated solutions and I need help.

I've tried indexing like so:

indexb <- as.character(ExpandedGrid$Device) == as.character(MediaPlanDF$Device);
indexc <- as.character(ExpandedGrid$Creative) == as.character(MediaPlanDF$Creative);
indexd <- as.character(ExpandedGrid$Partner) == as.character(MediaPlanDF$Partner);
index <- ExpandedGrid$Date %within% MediaPlanDF$Interval;

KEYDF <- data.frame(index, indexb, indexc, indexd)
KEYDF$Key <- apply(KEYDF, 1, function(x)(all(x) || all(!x)))
KEYDF$Key.cha <- as.character(KEYDF$Key)

outputbydim <- do.call(rbind, lapply(KEYDF$Key.cha, function(x){
  index <- x == "TRUE";
  list(impressions = sum(MediaPlanDF$Daily.Impressions[index]),
       spend = sum(MediaPlanDF$Daily.Spend[index]))}))

Unfortunately this excludes values from being summed correctly, but the sum values for those that are true are incorrect.

Here is a data snippet:

ExpandedGrid:

Date          Device     Creative     Partner
2015-08-31   "Desktop"  "Standard"   "ACCUEN"

MediaPlanDF

Interval                                            Device     Creative     Partner     Daily Spend      Daily Impressions
2015-08-30 17:00:00 PDT--2015-10-03 17:00:00 PDT   "Desktop"  "Standard"   "ACCUEN"     1696.27          1000339.17

Does anyone know where to go from here?

Thanks in advance!

YungBoy
  • 235
  • 4
  • 14
  • This looks like it's going to take a fairly long conditional. Few questions, what is Interval stored as? A list? A begin date and an end date? Also, did you mean to say that Daily Spend and Daily Impressions is in the ExpandedGrid and not MediaPlanDF? What do you mean by "matches" that its within the MediaPlanDF? – giraffehere Oct 16 '15 at 19:58
  • It's not going to be easy to help you without a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). Include some sample data and the desired output. Show any code you've tried and describe exactly where you are having trouble. – MrFlick Oct 16 '15 at 20:23
  • @MrFlick added what you requested, I picked up R with no coding experience a few months ago so apologies if I'm missing obvious things that should be included in my questions. @giraffehere I used this to create the interval: `MediaPlanDF$interval <- interval(MediaPlanDF$Flight.start.date, MediaPlanDF$Flight.end.date)`. It is stored as Formal class 'Interval'. Yes that is what I meant, it has been edited. By match I meant that the string values equal (ie "Desktop" = "Desktop" but not "Mobile") – YungBoy Oct 16 '15 at 20:45

0 Answers0