How in pandas/python to generate this last in first out row logic in Dispatch column?-CodePudding

I have 3 commodity. For example I received 60 orders for cake. I need to dispatch/ship the earliest batch first. Took a look at "BUN" commodity. I need script to generate this dispatch column. The logic here is that the sum of dispatch column for each commodity should not exceed the order received. I have attached the image.

enter image description here

CodePudding user response：

This isn't a solution as I don't know pandas well enough.

To do this it's best to work with cumulative values for both orders and batches. Then take the minimum of the batch and order cumulatives for each intersection between the two arrays.

Below is a numpy script, perhaps it will help inspire a pandas one.

import numpy as np

orders = np.array( [10, 20, 15, 25] ) 
cum_orders = orders.cumsum() 

batches = np.array( [ 20, 20, 20, 20 ] )                               
cum_batches = batches.cumsum()

temp = np.minimum( cum_batches[:, None], cum_orders[ None, : ] )

cum_orders, temp                                                         
# Orders: array([10, 30, 45, 70]),   # cum_batches

# temp:  array([[10, 20, 20, 20],    # [ 20,
#               [10, 30, 40, 40],    #   40,
#               [10, 30, 45, 60],    #   60,
#               [10, 30, 45, 70]]))  #   80 ]

inter = np.diff( temp, prepend = 0, axis = 1 )  # Decum across columns.

np.diff( inter, prepend = 0, axis = 0 )  # Decum down the rows.

# orders [10, 20, 15, 25]    # Batches

# array([[10, 10,  0,  0],   #  [ 20,
#        [ 0, 10, 10,  0],   #    20,
#        [ 0,  0,  5, 15],   #    20,
#        [ 0,  0,  0, 10]])  #    20 ]

To get LIFO (Last In First Out) the batches need to be with the newest batches before the older ones in the array. Can the equivalent be arranged in pandas?

For the 'bun' example data, simplified if there is only one order.

batches = np.array( [ 15, 5, 25, 10, 5 ] )                             
cum_batches = batches.cumsum()                                         

inter = np.minimum( cum_batches, 50 )
inter                                       
# array([15, 20, 45, 50, 50])

np.diff( inter, prepend = 0 )                  
# array([15,  5, 25,  5,  0])

Assuming two orders [ 30, 20 ]

orders = np.array( [ 30, 20 ] )
cum_orders = orders.cumsum()

batches = np.array( [ 15, 5, 25, 10, 5 ] )                             
cum_batches = batches.cumsum()

temp = np.minimum( cum_batches[:, None], cum_orders[ None, : ] )
#                       batches to rows, orders to columns

inter = np.diff( temp, prepend = 0, axis = 1 )  # Decum across columns.

np.diff( inter, prepend = 0, axis = 0 )  # Decum down the rows.

# array([[15,  0],
#        [ 5,  0],
#        [10, 15],
#        [ 0,  5],
#        [ 0,  0]])