Choose from options based on values from previous rows

Question

** The data is a sample data of 20 orders across a universe of 3 stores.Trying to write an algorithm to identify which store would cater to which order based on the rules **

If there is more than 1 store eligible for an order, the store that has catered to the least quantities of order cumulatively will cater to the current order. For example, If S1, S2, & S3 has catered to 100, 150, 75 units of orders till order #3 and S1 & S2 are eligible for order #4, then S1 will cater to order #4

OrderNum|OrderQty|StoresEligible
------- |--------|--------------
1|12|S3
2|10|S1
3|19|S1,S2
4|7|S1,S2
5|14|S1,S2,S3
6|19|S2
7|5|S1,S2
8|17|S2
9|13|S3
10|5|S1

In reality, there are 2,000 stores in the US and 1M orders

I cannot figure out how to proceed with this problem. I started with getting the count for each store for each row and store them in a separate table, and then run a loop through the OrderNum to identify the store with less numbers for the (i+1) th row. The logic sounds pretty interesting but am finding it pretty difficult to code. — user3117837, Jul 04 '17 at 14:33
it would be better to know what store actually delivered each order. Because if you don't have this variable, you'll have to re-compute iteratively the whole history at each new order... — agenis, Jul 04 '17 at 15:04
Yes agenis, that is exactly what I am trying to calculate. Which store is going to deliver. For order 1 S3 is the obvious choice, for 2 S1 is the choice. But for 3 both S1 & S2 can deliver, but S2 should deliver as S2 has delivered less numbers than S1 and so on. So I am eventually trying to come up with a column which will have only store who has actually delivered the order when there are multiple options — user3117837, Jul 04 '17 at 15:11
can you explain the selection criteria so it's easier to understand? — DCR, Jul 04 '17 at 15:20
1) If there is more than 1 store eligible for an order, the store that has catered to the least quantities of order cumulatively will cater to the current order. For example, If S1, S2, & S3 has catered to 80, 120, 75 units of orders till order #3 and S1 & S2 are eligible for order #4, then S1 will cater to order #4 2) If more than 1 store eligible for an order have catered to equal quantities of order cumulatively, then any of the eligible store can cater to the order 3) An order cannot be split across stores. An order has to be catered completely by only one store. — user3117837, Jul 04 '17 at 15:22

agenis · Answer 1 · 2017-07-04T15:45:22.320

OK this solves part of your problem, it's a function to add a new line to your data, taking as input the past data (to which I added the ChoosenStore information) the quantity and the eligible stores; it outputs the data.frame with the N+1 order completed:

You will have to apply iteratively this function to your past data to complete it.

library(dplyr); library(tidyr)
# initialise the data.frame with just the first 3 lines
df=data.frame(OrderNum=1:3, OrderQty=c(12,10,19),
              StoresEligible=c("S3", "S1", "S1, S2"), 
              ChoosenStore=c("S3", "S1", "S2"),stringsAsFactors=FALSE)
# function to compute new incoming order
NewOrder = function(df, quantity, eligible){
  choosen <- df %>% filter(ChoosenStore %in% eligible) %>%
    group_by(ChoosenStore) %>% 
    summarise(CumQty=sum(OrderQty)) %>%
    top_n(-1, CumQty) %>% .$ChoosenStore
  return(rbind(df, c(tail(df$OrderNum, 1)+1,
                     quantity,
                     paste0(eligible, collapse=", "),
                     choosen)))}
# try it with the 4th line
NewOrder(df, 7, c("S1", "S2"))

Choose from options based on values from previous rows

1 Answers1