How do I fix my Nested while loop with different column conditions?

Question

So I have this data frame and function code:

df = pd.DataFrame({'SUPPLIER': ['SUP1', 'SUP1', 'SUP1', 'SUP2', 'SUP2', 'SUP2'],
                  'PRODUCTID': ['P1', 'P1', 'P1', 'P4', 'P4', 'P4'],
                  'STOREID': ['STR1', 'STR2', 'STR3', 'STR1', 'STR2', 'STR3'],
                  'BALANCE': [50, 6, 74, 35, 5, 54],
                  'AVG_SALES': [5, 4, 4, 3, 4, 7],
                  'TO_SHIP': [18, 18, 18, 500, 500, 500],
                  'EXTRA_SHIP': [2, 2, 2, 3, 3, 3],})
        
def find_min(x):
  num_mins = x["Wk_bal"].loc[x["Wk_bal"] == x["Wk_bal"].min()].shape[0]
  if num_mins == 1:
    return(x["Wk_bal"].idxmin())
  else:
    min_df = x.loc[x["Wk_bal"] == x["Wk_bal"].min()]
  return(min_df["AVG_SALES"].idxmax())

I have a while loop below that distributes shipments (TO_SHIP) to the stores with the lowest weeks of supply. The code works well. I'm trying to add another while loop or if else inside the while statement to distribute the EXTRA_SHIP. But it's not working for me.

Here's the code without extra shipment which works just to allocate / send the packages from the TO_SHIP.

    df['SEND_PKGS'] = 0
    df['SEND_EXTRA_PKGS'] = 0
    df['Wk_bal'] = (df['BALANCE']+1) / df['AVG_SALES']


 while (df['TO_SHIP'] != 0).any():
  lowest_idx = df[df['TO_SHIP'] > 0].groupby(["SUPPLIER", "PRODUCTID"])[['Wk_bal', 'AVG_SALES']].apply(find_min)
  df.loc[lowest_idx, 'SEND_PKGS'] += 1
  df['Wk_bal'] = ((df['BALANCE']+1) + df['SEND_PKGS']) / df['AVG_SALES']
  df.loc[df['TO_SHIP'] > 0, 'TO_SHIP'] -= 1

Here's the code where I'm trying to create a nested while loop to allocate extra shipment based on similar conditions while extra shipment > 0. It doesn't seem to work for me.

while (df['TO_SHIP'] != 0).any():
  lowest_idx = df[df['TO_SHIP'] > 0].groupby(["SUPPLIER", "PRODUCTID"])[['Wk_bal', 'AVG_SALES']].apply(find_min)
  df.loc[lowest_idx, 'SEND_PKGS'] += 1
  while (df['EXTRA_SHIP'] != 0).any():
    df.loc[lowest_idx, 'SEND_EXTRA_PKGS'] += 1
    df.loc[df['EXTRA_SHIP'] > 0, 'EXTRA_SHIP'] -= 1
  df['Wk_bal'] = ((df['BALANCE']+1) + df['SEND_PKGS']) / df['AVG_SALES']
  df.loc[df['TO_SHIP'] > 0, 'TO_SHIP'] -= 1

How can I fix this so that I can have an output with Send pkgs and Send extra pkgs?

score 0 · Answer 1 · answered Nov 27 '21 at 03:34

This question is similar to the one I responded to, as well as another. How do I create a while loop for this df that has moving average in every stage?

Each question adds a bit more complexity, so that is understandable. I want to to point out if performance is an issue, you might want to check whether the while loop option or the groupby.apply() option I proposed is worth considering.

In my testing to do the first part of the to_ship allocation, the while loop had this performance:

2.61 s ± 1.32 s per loop (mean ± std. dev. of 7 runs, 1 loop each)

and the groupby.apply() method had this performance:

466 ms ± 6.41 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

The solution I'm proposing here just adds onto my previous solution. You can reconfigure for a while loop as well. Essentially, I just added another step to allocate the extra_shipments - if nesting is a must, then I can rethink this, but I'm not sure it is. So, for the while loop, just a run a second while loop.

df1 = pd.DataFrame({'SUPPLIER': ['SUP1', 'SUP1', 'SUP1', 'SUP2', 'SUP2', 'SUP2'],
                  'PRODUCTID': ['P1', 'P1', 'P1', 'P4', 'P4', 'P4'],
                  'STOREID': ['STR1', 'STR2', 'STR3', 'STR1', 'STR2', 'STR3'],
                  'BALANCE': [50, 6, 74, 35, 5, 54],
                  'AVG_SALES': [5, 4, 4, 3, 4, 7],
                  'TO_SHIP': [18, 18, 18, 500, 500, 500],
                  'EXTRA_SHIP': [2, 2, 2, 3, 3, 3],})

def calc_to_ship(x):
    # print(x.iloc[0]['SHIP'])
    for i in range(x.iloc[0]['TO_SHIP']):
          x['Wk_bal'] = ((x['BALANCE']+1) + x['SEND_PKGS']) / x['AVG_SALES']
          x['TO_SHIP'] = x['TO_SHIP']-1
          x = x.sort_values('Wk_bal').reset_index(drop=True)
          x.loc[0, 'SEND_PKGS'] = x['SEND_PKGS'][0] + 1
    return x

def calc_extra_ship(x):
    # print(x.iloc[0]['SHIP'])
    for i in range(x.iloc[0]['EXTRA_SHIP']):
          x['Wk_bal'] = ((x['BALANCE']+1) + x['SEND_EXTRA_PKGS']) / x['AVG_SALES']
          x['EXTRA_SHIP'] = x['EXTRA_SHIP']-1
          x = x.sort_values('Wk_bal').reset_index(drop=True)
          x.loc[0, 'SEND_EXTRA_PKGS'] = x['SEND_EXTRA_PKGS'][0] + 1
    return x

df1['SEND_PKGS'] = 0
df1['SEND_EXTRA_PKGS'] = 0
df1['Wk_bal'] = (df1['BALANCE']+1) / df1['AVG_SALES']
# df_final = df.groupby('ID').apply(calc_something).reset_index(drop=True).sort_values(['ID', 'STOREID'])
df2 = df1.groupby('SUPPLIER').apply(calc_to_ship).reset_index(drop=True).sort_values(['SUPPLIER', 'STOREID'])
df2
df_final = df2.groupby('SUPPLIER').apply(calc_extra_ship).reset_index(drop=True).sort_values(['SUPPLIER', 'STOREID'])
df_final

  SUPPLIER PRODUCTID STOREID  BALANCE  AVG_SALES  TO_SHIP  EXTRA_SHIP  SEND_PKGS  SEND_EXTRA_PKGS     Wk_bal
1     SUP1        P1    STR1       50          5        0           0          0                0  10.200000
0     SUP1        P1    STR2        6          4        0           0         18                2   2.000000
2     SUP1        P1    STR3       74          4        0           0          0                0  18.750000
5     SUP2        P4    STR1       35          3        0           0         92                0  12.000000
3     SUP2        P4    STR2        5          4        0           0        165                3   2.000000
4     SUP2        P4    STR3       54          7        0           0        243                0   7.857143

Performance with the added step:

474 ms ± 4.86 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

How do I fix my Nested while loop with different column conditions?

1 Answers1