0

I have 3 dataframes and trying to loop through each category to obtain the total (sum) transactiondata$amount for each week.

Here is my pseudo code:

for a given productIDCat$Category {
           for a given weeklydate$weekid{
                     calculate sum(transactiondata$amount)
                     when transactiondata$date is between weeklydate$begindate
                     and weeklydate$enddate 
                     Append sum to new column in weeklydate
                     as weeklydate$testamount}}

Repeat this process for each week for that given product.

Below are my dataframes:

productIDCat

itemNo  Category
  1  Shoes
  2  Shirts
  3  Panties
  4  Shoes
  5  Shirts
  6  Panties

transactiondata

amount  date itemNo
      12  2014-07-23 1
      33  2014-07-29 1
      6  2014-08-05 2
      21  2014-08-06 2
      23  2014-08-19 3
      32  2014-08-27 3

weeklydate

weekid  begindate enddate
      1  2014-07-21 2014-07-25
      2  2014-07-28 2014-08-01
      3  2014-08-04 2014-08-08
      4  2014-08-11 2014-08-15
      5  2014-08-18 2014-08-22
      6  2014-08-25 2014-08-29
Frank
  • 66,179
  • 8
  • 96
  • 180
BlackHat
  • 736
  • 1
  • 10
  • 24
  • Please show what you tried by updating the question. – Metrics Mar 05 '15 at 01:09
  • This is a big problem and will involve, among other things, changing the names of some of your data frame columns so that we can use the `R` `merge` function to join the three tables together. Could you show us what `R` code you have so far? – Tim Biegeleisen Mar 05 '15 at 01:55
  • 1
    i was able to do something like this using `data.table` and rolling joins. e.g., http://stackoverflow.com/questions/21560500/data-table-merge-based-on-date-ranges – rrs Apr 29 '15 at 17:20

0 Answers0