-2

I have a data set I need to allocate values from receipt points (RP) and delivery points (DP) that have varying connectivity relationships (one-to-one, one-to-few, one-to-many, many-to-one, many-to-few, many-to-many). These receipt and delivery values need to be allocated by a contract number, and values for each contract between the receipt and delivery points always sum equally (i.e receiptsums == deliverysums), but the individual receipt and delivery values may not (i.e not a bin packing problem). The one-to-one relationships are easily solved by filtering where contract number's receipt value and delivery value sum and only have one row of each (two rows total/contract).

Many-to-few (or vice versa) is proving problematic.

It does not matter from which RP the DP receives its allocation.

The solution for this question somewhat addresses the issue at hand, and is what I'm working off of R: Distributing an amount as evenly as possible II

I started learning R on saturday for this work project fwiw. In excel I would do a min function on an asc rank, but that doesn't work here as I need to create new rows in some instances while keeping the RP & DP names and contract number.

I've included an image below.

Any pointers would be helpful.

Thank you, Karl

Volume Datatables

enter image description here

Alain Merigot
  • 10,667
  • 3
  • 18
  • 31
Karl
  • 41
  • 6

1 Answers1

0

Here is the solution i found.

databuild <-function() {
        allonew <<- data.frame(Contract.Number=character(), Contract.Receipt.Point=character(),Contract.Delivery.Point=character(),Contract.Volumes.Mdthd=numeric())
        allorow <- data.frame(Contract.Number=character(), Contract.Receipt.Point=character(),Contract.Delivery.Point=character(),Contract.Volumes.Mdthd=numeric())
        deliveryct <<- 1
        receipt.vols <- numeric()
        receipt.vols <- c(200,310,100,40)
        receipt.name <<- c("rtest1", "rtest2", "rtest3", "rtest4")
        delivery.name <<- c("dtest1","dtest2")
        contract <<- 1260
        r.count <-length(receipt.vols)
        contract.receipt.vols <<- receipt.vols[!is.na(receipt.vols)]
        rec.sum <<- sum(receipt.vols)
        contract.vols.sum <-rec.sum*2
            delivery.vols <- c(500,150)
        d.count <-length(delivery.vols)
        contract.delivery.vols <<- delivery.vols[!is.na(delivery.vols)]
        receipt.vols <- receipt.vols[receiptct]
        delivery.vols <- delivery.vols[deliveryct]
        browser()
            while(contract.vols.sum/rec.sum !=1) {
              if(receipt.vols == delivery.vols) {
                    while(receipt.vols == delivery.vols)  {
                        contract.vols <-min(receipt.vols,delivery.vols)
                        contract.vols.sum <- contract.vols.sum - contract.vols
                        allorow <- as.data.frame(t(c(contract[1],receipt.name[receiptct],delivery.name[deliveryct],contract.vols)))
                        colnames(allorow) <- colnames(allonew)
                        allonew <-rbind(allonew,allorow)
                        deliveryct <-deliveryct + 1
                        receiptct <- receiptct + 1
                          if(receiptct > r.count) {
                          break
                        }  
                        delivery.vols <- contract.delivery.vols[deliveryct]
                        receipt.vols <- contract.receipt.vols[receiptct] 
                        }
                } else if(receipt.vols < delivery.vols) {
                        while(receipt.vols < delivery.vols) {
                            contract.vols <- min(receipt.vols,delivery.vols)
                            delivery.vols <- delivery.vols - contract.vols
                            contract.vols.sum <- contract.vols.sum - contract.vols
                            allorow <- as.data.frame(t(c(contract[1],receipt.name[receiptct],delivery.name[deliveryct],contract.vols)))
                            colnames(allorow) <- colnames(allonew)
                            allonew <-rbind(allonew,allorow)
                            receiptct <- receiptct + 1
                            receipt.vols <- contract.receipt.vols[receiptct]
                    }
                } else if(receipt.vols > delivery.vols) {
                        while(receipt.vols > delivery.vols) {
                            contract.vols <-min(receipt.vols,delivery.vols)
                            receipt.vols <- receipt.vols- contract.vols
                            contract.vols.sum <- contract.vols.sum - contract.vols
                            allorow <- as.data.frame(t(c(contract[1],receipt.name[receiptct],delivery.name[deliveryct],contract.vols)),stringsAsFactors = FALSE)
                                colnames(allorow) <- colnames(allonew)
                            allonew <-rbind(allonew,allorow)
                            deliveryct <- deliveryct + 1
                            delivery.vols <- contract.delivery.vols[deliveryct]
                        }
                } else next

            }
    allonew <<- allonew
Karl
  • 41
  • 6