r - Allocate along members of a group in data.table -
i have table of demand looks this:
set.seed(1) dtd <- data.table(loc="l1", product="p1", cust=c("c1","c2","c3"), period=c("per1","per2","per3","per4"), qty=runif(12,min=0,max=100), key=c("loc","product","cust","period")) dtd[] # loc product cust period qty #1: l1 p1 c1 per1 12.97134 #2: l1 p1 c1 per2 65.37663 #3: l1 p1 c1 per3 34.21633 #4: l1 p1 c1 per4 24.23550 #5: l1 p1 c2 per1 85.68853 #6: l1 p1 c2 per2 98.22407 #7: l1 p1 c2 per3 92.24086 #8: l1 p1 c2 per4 70.62672 #9: l1 p1 c3 per1 62.12432 #10: l1 p1 c3 per2 84.08788 #11: l1 p1 c3 per3 82.67184 #12: l1 p1 c3 per4 53.63538 and table of supply looks this:
dts <- data.table(loc="l1", product="p1", period=c("per1","per2","per3","per4"), qty=runif(4,min=0,max=200), key=c("loc","product","period")) dts[] # loc product period qty #1: l1 p1 per1 9.23293 #2: l1 p1 per2 74.03622 #3: l1 p1 per3 133.54770 #4: l1 p1 per4 123.43913 i need allocate supply corresponding demand on priority basis , add column 'allocated' demand table. purpose of example assume priority smallest demand first.
this outcome looking for.
#loc product cust period qty alloc #1: l1 p1 c1 per1 12.97134 9.232930 #2: l1 p1 c1 per2 65.37663 65.376625 #3: l1 p1 c1 per3 34.21633 34.216329 #4: l1 p1 c1 per4 24.23550 24.235499 #5: l1 p1 c2 per1 85.68853 0.000000 #6: l1 p1 c2 per2 98.22407 0.000000 #7: l1 p1 c2 per3 92.24086 16.659531 #8: l1 p1 c2 per4 70.62672 45.568249 #9: l1 p1 c3 per1 62.12432 0.000000 #10: l1 p1 c3 per2 84.08788 8.659591 #11: l1 p1 c3 per3 82.67184 82.671841 #12: l1 p1 c3 per4 53.63538 53.635379 i don't see way efficiently using features of data.table. seem reduced looping through rows , updating using set on row row basis. code used in case.
#set key on demand match supply , order qty (for prioritising setkey(dtd, loc, product, period, qty) #add column allocated quantity dtd[,alloc:=0] #loop through rows of supply, using row number (s in dts[, .i]) { key <- dts[s, .(loc, product, period)] suppqty <- dts[s, qty] #loop through corresponding demand , return row number (d in dtd[key, which=true]) { if (suppqty == 0) break #determine quantity allocate demand row allocqty <- dtd[d, ifelse(qty < suppqty, qty, suppqty)] #update alloc qty on row set(dtd, d, 6l, allocqty) #reduce amount outstanding suppqty <- suppqty - allocqty } } #restore original keys setkey(dtd, loc, product, cust, period) any suggestions better way achieve part of appreciated. (in practice tables quite large , priority rules can quite complex, in case first pass determine priority , use in allocation pass).
you can do
setnames(dts, "qty", "suppqty") setnames(dtd, "qty", "demqty") setorder(dtd, loc, product, period, demqty) # put priority column last here dtd[dts, alloc := { resid_supply = shift(pmax(suppqty - cumsum(demqty), 0), fill=suppqty[1l]) pmin(demqty, resid_supply) }, by=.eachi, on=c("loc", "product", "period")] the result is
loc product cust period demqty alloc 1: l1 p1 c2 per1 20.168193 20.168193 2: l1 p1 c1 per1 26.550866 26.550866 3: l1 p1 c3 per1 62.911404 62.911404 4: l1 p1 c1 per2 6.178627 6.178627 5: l1 p1 c2 per2 37.212390 37.212390 6: l1 p1 c3 per2 89.838968 33.429727 7: l1 p1 c2 per3 20.597457 20.597457 8: l1 p1 c3 per3 57.285336 57.285336 9: l1 p1 c1 per3 94.467527 76.085490 10: l1 p1 c3 per4 17.655675 17.655675 11: l1 p1 c2 per4 66.079779 66.079779 12: l1 p1 c1 per4 90.820779 15.804394 you don't need set key before merge these days, described 1 of package authors, arun, in post:
in cases therefore, there shouldn't need set keys anymore. recommend using
on=wherever possible, unless setting key has dramatic improvement in performance you'd exploit.
for similar computation (procurement lowest-price priority), you can see other answer.
Comments
Post a Comment