Way to loop over multiple tables and keep only if condition met?

user_14033843 0
Viewed 345

So I'm working on project that has multiple data tables, separated by month, that I need to iterate through. Speed is of the essence here, and I can't seem to get the time down to something reasonable unless I do a lot of crossjoins through data table functions. So here are my tables:

TABLE 1

Product Date        Cost
A       8/1/2020    10
A       8/2/2020    20
A       8/3/2020    30
B       8/4/2020    15
B       8/5/2020    25
B       8/6/2020    35

and TABLE 2:

Product Date    Price
A       9/1/2020    20
A       9/2/2020    30
A       9/3/2020    40
B       9/4/2020    27
B       9/5/2020    33
B       9/6/2020    42

So I need to iterate over every combination of Table 2 Price - Table 1 Cost, and do it by Product. So output would be:

NEW TABLE

Product Date1         Date2          Profit
A       8/1/2020      9/1/2020       10
A       8/1/2020      9/2/2020       20
...

EDIT: To clarify, the New Table should continue on. Product A should have 27 different profits (3 dates under A x 3 dates under A x 3 discount rates) assuming they are all above 0. If any of the profits are below 0, then I don't want them as part of the New Table.

I also have a Discount factor I need to apply to each permutation of Price as we give discounts quite a bit

Discount = c(10%,12%,18%)

I've tried using a loop and various ways of using apply but the loops take way too long to finish (hours, and some never do). The combinations lead to millions of rows but I only want to keep the profitable ones, where Price*Discount > Cost, which are only maybe 10,000 in number.

My solution is to cross join the data tables to create a massive table that I can vectorize against, which is much faster (around 1 min) but with some of the larger tables I quickly run into memory constraints and it isn't very scalable.

CTbl =setkey(CTbl[,c(k=1,.SD)],k)[Price[,c(k=1,.SD)],allow.cartesian=TRUE][,k:=NULL]
CTbl[,Profit:=(Discount*Price - Cost]
CTbl = setDT(CTbl)[, .SD[Price > Cost ]]
DT = CTbl[,list(MinProfit = min(Profit)),by = Product]

Of course this is quite fast but is a huge of waste of memory when all I really want is profitable rows, and of course the ongoing memory issue.

Can anyone help? I've asked some R users at work but they seem stumped as well, the loops they made couldn't get close to the sub-5 minutes it takes to run the above. I don't mind a bit of extra time if it means I can scale it up.

Thanks!

data.table combinations nested-loops r loops
2 Answers

This is not a complete answer to your question, but maybe you can iterate a loop by products. The following function finds profits for a specified product. The function does not include discount but it can be added if the function works as you want.

profit = function(product, df1, df2) {

    cost = with(df1, df1[which(Product == product), 'Cost'])
    price = with(df2, df2[which(Product == product), 'Price'])
    date = merge(
            with(df1, df1[which(Product == product), 'Date']), 
            (with(df2, df2[which(Product == product), 'Date']))
            )
    product = t(matrix(rep(price, length(cost)), nrow = length(cost)) - t(matrix(rep(cost, length(price)), ncol = length(price))))
    product = data.frame(cbind(date[which(product > 0), ], product[which(product > 0)]))
    names(product) = c('costdate', 'pricedate', 'profit')
    return(product)

}

Example:

df1 = data.frame(Product = c('A', 'A', 'A', 'B', 'B', 'B'), 
                Date = c('8/1/2020', '8/2/2020', '8/3/2020', '8/4/2020', '8/5/2020', '8/6/2020'),
                Cost = c(10, 20, 30, 15, 25, 35))
df2 = data.frame(Product = c('A', 'A', 'A', 'B', 'B', 'B'), 
                Date = c('9/1/2020', '9/2/2020', '9/3/2020', '9/4/2020', '9/5/2020', '9/6/2020'),
                Price = c(20, 30, 40, 27, 33, 42))

> profit('A', df1, df2)
  costdate pricedate profit
1 8/1/2020  9/1/2020     10
4 8/1/2020  9/2/2020     20
5 8/2/2020  9/2/2020     10
7 8/1/2020  9/3/2020     30
8 8/2/2020  9/3/2020     20
9 8/3/2020  9/3/2020     10
> profit('B', df1, df2)

  costdate pricedate profit
1 8/4/2020  9/4/2020     12
2 8/5/2020  9/4/2020      2
4 8/4/2020  9/5/2020     18
5 8/5/2020  9/5/2020      8
7 8/4/2020  9/6/2020     27
8 8/5/2020  9/6/2020     17
9 8/6/2020  9/6/2020      7

I could not test it properly since I have limited data.

user_139023090

This sounds like a problem for the dplyr package, which. The dplyr package allows you to string together data operations in a "pipe" to avoid storing things in memory. The pipe operator %>%takes the output of the function on the left and uses it as the first argument of the function on the right. Each function in the dplyr package works over the entire vector or data tibble, so no need for loops.

So, your operation might look like the following:

# Initialize random data like your first table
df1 <- data.frame(product = sample(LETTERS[1:10], 10000, replace = TRUE),
              date1 = sample(seq(as.Date("2020/08/01"), as.Date("2020/08/31"), 
                                 by = "day"), 10000, replace = TRUE),
              cost = round(runif(10000, 5, 100)))
# Initialize random data like your second table 
df2 <- data.frame(product = sample(LETTERS[1:10], 10000, replace = TRUE),
              date2 = sample(seq(as.Date("2020/09/01"), as.Date("2020/09/30"), 
                                 by = "day"), 10000, replace = TRUE),
              price = round(runif(10000, 5, 100)))
# Initialize discounts 
discounts <- data.frame(product = rep(LETTERS[1:10],4), 
                    discount = rep(c(0, 0.1, 0.12, 0.18), 10))
library(dplyr)
out_table <- df1 %>%
  full_join(df2) %>%
  full_join(discounts) %>%
  mutate(profit = price * discount - cost) %>%
  filter(profit > 0)

For my random data, this takes about 3 seconds on my machine. Furthermore, the filter verb only keeps those rows we want.

user_129294470
Related