CODEWITHSUNDEEP
rdataframedplyr
Marcelo Rodrigues
Marcelo Rodrigues

Reputation: 259

Distributing quantities to meet demand

I have two tables, one contains production locations and the quantity produced. Another table contains orders and quantity ordered. Assuming that it is already ordered by priority, I want to take the products from the first production location and fulfill as many orders as possible, running out of products from the first location I will use the second one and so on

set.seed(595)
prod <- data.frame('local' = LETTERS[1:5], 'qtd' = sample(1:10, 5), 'product' = sample(1:3, 5, replace = T))

set.seed(596)
ord <- data.frame('buyer' = sample(1:15, 15), 'qtd' = sample(1:6, 15, replace = T), 'product' = sample(1:3, 15, replace = T))

local qtd product
A     4   2
B     8   2
C     3   1
D     5   3
E     2   3


   buyer qtd product
1      3   3       2
2      2   6       2
3     14   1       3
4      8   1       1
5     11   3       3
6      5   4       1
7     12   5       2
8      1   2       3
9      4   6       2
10    15   2       3
11    10   1       3
12     7   4       2
13     6   2       3
14    13   6       3
15     9   6       3

From the two tables above I want to assemble something similar to the table below, it doesn't need to have this exact format... I just need to know where the products will come from and in what quantity to fulfill the order

   buyer qtd product local qtd2
1      3   3       2 A     3
2      2   6       2 A,B   1,5
3     14   1       3 D     1
4      8   1       1 C     1
5     11   3       3 D     3
6      5   4       1 C     2
7     12   5       2 B     5
8      1   2       3 D,E   1,1
9      4   6       2
10    15   2       3 E     1
11    10   1       3
12     7   4       2
13     6   2       3
14    13   6       3
15     9   6       3

Upvotes: 2

Views: 80

Answers (1)

TimTeaFan
TimTeaFan

Reputation: 18561

I think I figured it out, we can for loop over each row and need a while loop when one row is not fully supplied with products.

Note, that in your desired output line 7 is wrong, since we only have 12 products 2 and if qtd2 in line 7 is 5 we give away 14 products, which we don't have.

library(dplyr)

ord$local <- ""
ord$qtd2 <- ""

for (row in seq_len(nrow(ord))) { # 
  cur_row <- ord[row, ]
  cur_prod <- cur_row$product
  cur_qtd <- cur_row$qtd

  while(cur_qtd > 0) {
    upd_prod <- prod[prod$product == cur_prod, ][1, ]
    
    # if no product is available
    if(any(is.na(unlist(upd_prod)))) {
      break 
    }
    
    new_cur_qtd <- cur_qtd - upd_prod$qtd
    cur_local <- upd_prod$local
    
    if (new_cur_qtd > 0) {
      prod <- dplyr::anti_join(prod, upd_prod)
      cur_qtd2 <- cur_row$qtd - new_cur_qtd
      cur_qtd <- cur_qtd - cur_qtd2
      
    } else {
      upd_prod$qtd <- upd_prod$qtd - cur_qtd
      prod <- dplyr::rows_update(prod, upd_prod)
      cur_qtd2 <- as.character(cur_qtd)  
      cur_qtd <- 0
    }
    
    # update local
    if (!nzchar(cur_row$local)) {
      cur_row$local <- cur_local  
    } else {
      cur_row$local <- paste(cur_row$local, cur_local, sep = ",")
    }
    
    # update qtd2
    if (!nzchar(cur_row$qtd2)) {
      cur_row$qtd2 <- as.character(cur_qtd2)
    } else {
      cur_row$qtd2 <- paste(cur_row$qtd2, cur_qtd2, sep = ",")
    }
  
    ord <- dplyr::rows_update(ord, cur_row)
    
  }
  
}

ord

#>    buyer qtd product local qtd2
#> 1      3   3       2     A    3
#> 2      2   6       2   A,B  1,5
#> 3     14   1       3     D    1
#> 4      8   1       1     C    1
#> 5     11   3       3     D    3
#> 6      5   4       1     C    2
#> 7     12   5       2     B    3
#> 8      1   2       3   D,E  1,1
#> 9      4   6       2           
#> 10    15   2       3     E    1
#> 11    10   1       3           
#> 12     7   4       2           
#> 13     6   2       3           
#> 14    13   6       3           
#> 15     9   6       3

data from the OP

set.seed(595)
prod <- data.frame('local' = LETTERS[1:5], 'qtd' = sample(1:10, 5), 'product' = sample(1:3, 5, replace = T))

set.seed(596)
ord <- data.frame('buyer' = sample(1:15, 15), 'qtd' = sample(1:6, 15, replace = T), 'product' = sample(1:3, 15, replace = T))

Created on 2023-02-10 with reprex v2.0.2

Upvotes: 1

Related Questions