0

I have a table of skus that need to be placed in locations. Based on the volume that a sku has determines how many locations a sku needs. There are a limited number of locations so I need to prioritize based on how much volume will be in a location. Then once in order apply the locations. When the location is full the volume for the location should be the location volume, for the last location the remainder volume. Current table setup

So the end result should look like this.

enter image description here

I was hoping to iterate based on the number of locations needed and create a row in a new table while reducing the number of listed locations by row. Something like this.

rows = int(sum(df['locations_needed']))
new_locs = []
   for i in range(rows):
       if df['locations_needed'] > 1:
       new_locs.append(df['SKU'], df['location_amount'])
       df['locations_needed'] - 1
    else:
        new_locs.append(df['SKU'], df['remainder_volume'])
        df['locations_needed'] - 1
Jill Cox
  • 1
  • 1

2 Answers2

0

Use repeat method from pd.Index:

out = (df.reindex(df.index.repeat(df['locations_needed'].fillna(0).astype(int)))
         .reset_index(drop=True))
print(out)

# Output
        SKU  location_amount  locations_needed
0   FAKESKU             2300               3.0
1   FAKESKU             2300               3.0
2   FAKESKU             2300               3.0
3  FAKESKU2             2100               2.0
4  FAKESKU2             2100               2.0
Corralien
  • 109,409
  • 8
  • 28
  • 52
  • This is almost there, but I need to set the location_amount for the last one to the remainder_volume somehow. – Jill Cox Jan 31 '23 at 14:19
  • Can you explain me why there is only one instance of (FAKESKU2, 2100)? – Corralien Jan 31 '23 at 14:32
  • It is based on the needs_slotted_qty. For Fasksku2 I need to slot 3880 units and a location can hold 2100. So the first location is 2100 and the second has the remainder 1780. The distinct is important because there are more skus to slot than locations so the remainder may or may not get a location based on where it falls in the order. – Jill Cox Jan 31 '23 at 14:37
0

Building off of using repeat as suggested by Corralien, you then set the value for the last of the groupby to the remainder volume. The reorder and reset the index again. So,

#create row for each potential location by sku
df=df.loc[df.index.repeat(df.locations_needed)]
#reset index
df= df.reset_index(drop= True)
#fill last row in group (sku) with remainder volume
df2= df['SKU'].duplicated(keep= 'last')
df.loc[~df2,'location_amount'] = df['remainder_volume']
#reorder and reset index
df = df.sort_values(by=['location_amount'], ascending=False)
df['locations_needed] = 1
df= df.reset_index(drop= True)
Jill Cox
  • 1
  • 1