Home > Mobile >  FIFO distribution pandas
FIFO distribution pandas

Time:10-01

I have a dataframe with residuals and a dataframe with orders

ln [65]: residuals = pd.DataFrame({'area': ['pnos', 'vnp'], 'number': [3, 4], 'balances':[20,95]})
​
In [67]: orders = pd.DataFrame({'area': ['pnos', 'vnp','vnp','vnp', 'vnp'], 'number': [3, 4, 4, 4 1], 'requires':[40,70,20,10,25]})
In [68]:orders
Out[68]:
      area  number      requires
0      pnos    3         40
1      vnp     4         70
2      vnp     4         20
3      vnp     4         10        
4      vnp     1         25
In [69]: residuals
Out[69]:
    area      number    balances
0   pnos       3        20
1   vnp        4        95

You can see area and number product that is a keys in orders by area - 'pnos' and number '3' requires 40 kg and we can see in dataframe 'residuals' and if it have area - 'pnos' and number - '3' and its balances more or equal than requires, so we set 20 and reduce in balances by this amount, else we just set 0

Visually, the result I want would look like this:

      area  number      requires   ready
0      pnos    3         40         20
1      vnp     4         70         70
2      vnp     4         20         20
3      vnp     4         10         5
4      vnp     1         25         0

In the third line (area-pnos,number-4) we set 5 because we have distributed the balance to the previous lines and now the balance is less than required

I solved it, but not in a real elegant way.

def distr(number, area, requiers):
    if residuals[(residuals['number']==number) & (residuals['area']==area)].empty:
        return 0
    elif requiers==0:
        return 0
    elif requiers>0:
        if residuals[(residuals['number']==number) & (residuals['area']==area)]['balances'].iloc[0]  >= requiers:    
            residuals.loc[(residuals['number']==number) & (residuals['area']==area), 'balances'] -= requiers
            return requiers
        else:
 
            result = residuals[(residuals['number']==number) & 
                               (residuals['area']==area)]['balances'].iloc[0]
            residuals.loc[(residuals['number']==number) & (residuals['area']==area), 'balances'] = 0
            
            return result
In [118]: orders['ready'] = orders.apply(lambda x: distr(x['number'], x['area'], x['requires']), axis=1)

how I can distribute data more optimize? I have more than 20000 rows in excel and it calculate for quite a long time

CodePudding user response:

  • First, you need to join the residuals to give the upper limit per area-number pairs.
  • Second, calculated the requires_cum as the cumulative sum of the requires, which is the desired amount from the orders
  • Third, calculate the ready as the requires adjusted with the appropriate upper and lower bounds.
import pandas as pd
residuals = pd.DataFrame({'area': ['pnos', 'vnp'], 'number': [3, 4], 'balances':[20,95]})
orders = pd.DataFrame({'area': ['pnos', 'vnp','vnp','vnp', 'vnp'], 'number': [3, 4, 4, 4, 1], 'requires':[40,70,20,10,25]})

orders = pd.merge(orders, residuals, how="left")
orders.balances.fillna(0, inplace=True)
orders["requires_cum"] = orders.groupby(["area", "number"]).requires.cumsum()
orders["ready"] = orders.requires.clip(upper=orders.balances - orders.requires_cum   orders.requires).clip(lower=0)
orders

yields:

   area  number  requires  balances  requires_cum  ready
0  pnos       3        40      20.0            40     20
1   vnp       4        70      95.0            70     70
2   vnp       4        20      95.0            90     20
3   vnp       4        10      95.0           100      5
4   vnp       1        25       0.0            25      0
  • Related