Update a column based on a condition and groupby

Question

My data is:

Prod   Vend    Capac  Dema   Price
 p1     v2       2      6      1
 p1     v1       3      6      2
 p1     v3       3      6      2
 p2     v1       1      1      1
 p2     v3       2      1      2
 p2     v2       5      1      2
 p3     v1       5      3      3
 p3     v2       3      3      4
 p3     v3       1      3      5

And I require something like:

Prod   Vend    Capac  Dema   Price   Source
 p1     v2       2      6      1       2
 p1     v1       3      6      2       3
 p1     v3       3      6      2       1 
 p2     v1       1      1      1       1
 p2     v3       2      1      2       0
 p2     v2       5      1      2       0
 p3     v1       5      3      3       3
 p3     v2       3      3      4       0 
 p3     v3       1      3      5       0

i have product, vendor, capacity (of vendor), demand (of product), price (of product from vendor). I am sourcing the products based on least price but vendor capacity is a constraint. That is, the selection of a vendor is done by price it has quoted, Price is sorted for the vendors grouped by products.

I am trying it with for loop and if else conditions, the code is getting messier. Is there a clean way to solve it, probably using plyr?

Should there be no difference in choosing two vendors when they both have the same price? How do you break ties if necessary? — Pierre L, Mar 10 '16 at 14:27
have edited my question,it would be the first one,no constraints on that. — Vishal Gupta, Mar 10 '16 at 14:27
It's still unclear to me why p3 v2 and p2 v2 have 0 as source ... (nor what source is in fact) — Tensibai, Mar 10 '16 at 14:31
For product 2, the demand is `1`. The first vendor met that capacity. There is no need to get anything from the other vendors for that product. — Pierre L, Mar 10 '16 at 14:33
@Tensibai because the demands are met,so i dont need vendor v2 for p3 and vendor v2 for product p2...hope this helps — Vishal Gupta, Mar 10 '16 at 14:34
It does absolutely not help me to understand, how and why is there a relation between product p1 and its 'best' vendor and product p3 for the same vendor ???, morever, then why is there a source for product p1 and vendor v1 and v3 ??? The realtion is absolutely unclear, try to explain me in the Q how the slection is done for a specific product for example (where does come 2 3 and 1 for p1 ? — Tensibai, Mar 10 '16 at 14:38
Ok I think I got it: source is demand - capacity of vendor per product ? right ? — Tensibai, Mar 10 '16 at 14:40
@VishalGupta If that is case then why the source for P3 is 3? shouldn't it be 1?? — Veerendra Gadekar, Mar 10 '16 at 14:46
@VeerendraGadekar sir my demand for product p3 is 3 and vendor V1 has least price,i would source it from him..:) — Vishal Gupta, Mar 10 '16 at 14:52
@VishalGupta Is your original dataset already sorted by product and vendor price as your example ? — Tensibai, Mar 10 '16 at 14:55
@VishalGupta if you source from him then the source should be 1 (v1) right? how do you define source? what are the numbers under source column correspond to? — Veerendra Gadekar, Mar 10 '16 at 14:55
Veerendra the Demand for product 3 is `3`. Why would it be `1`? three is the correct number. The question started vague but the logic does make sense. — Pierre L, Mar 10 '16 at 14:57
@VeerendraGadekar see my previous comment, `source = Capa` as long as `Dema > 0`, by product and by row... — Tensibai, Mar 10 '16 at 14:58

Frank · Accepted Answer · 2016-03-10T15:19:59.360

Here's what I would do:

library(data.table)
setDT(DT)

DT[order(Price), src := pmin(Capac, pmax(Dema - shift(cumsum(Capac), fill=0), 0)), by=Prod]

we can see it matches:

   Prod Vend Capac Dema Price Source src
1:   p1   v2     2    6     1      2   2
2:   p1   v1     3    6     2      3   3
3:   p1   v3     3    6     2      1   1
4:   p2   v1     1    1     1      1   1
5:   p2   v3     2    1     2      0   0
6:   p2   v2     5    1     2      0   0
7:   p3   v1     5    3     3      3   3
8:   p3   v2     3    3     4      0   0
9:   p3   v3     1    3     5      0   0

The logic, partly in pseudocode:

shift(cumsum(Capac), fill=0) is capacity from cheaper vendors
max(demand - capacity from cheaper, 0) is residual demand for the vendor
min(capacity, residual demand) is how much to source from the vendor

.

The dplyr analogue:

DT %>% arrange(Price) %>% group_by(Prod) %>% 
  mutate(src = pmin(Capac, pmax(Dema - lag(cumsum(Capac), default=0), 0)))

Great clean approach. +2 for this – Pierre L Mar 10 '16 at 15:25 — Pierre L, Mar 10 '16 at 15:25

Update a column based on a condition and groupby

1 Answers1

Linked