[Python] Looking for a way to group together rows so that columns match and numbers of one...

My first post here, hope I provide the right info for you to be able to help.

I have a long Python/Pandas program written which works ok but i am looking to fine tune one element of it.

I am working on a project so that items in different physical areas can be consolidated for maximum area optimization.

So I have a list of items with different status', types and areas stored in. Along with a quantity of that item in that particular area. Each area also has an associated quantity which is available to still be stored in there until it is full.

What i would like to do is link the items together so that the items are consolidated together in a way that the area with the least quantity is merged with an area that has that at least that number of packs left available to fill. I also need the status and type to match for the area to be a match.

It would be ideal if this was presented in the form of a data frame so that the dataframe would output a list of possible moves that satisfy the above statement.

see example dataset/code below:

import pandas as pd

data = {
'ITEM NUMBER': [100, 105, 100, 100, 100, 100, 105, 105, 105, 105, 100],
'STATUS': ["OK", "OK", "NG", "NG", "OK", "OK", "OK", "NG", "OK", "OK", "NG"],
'TYPE': ["RED", "YELLOW", "RED", "BLACK", "RED", "BLACK", "YELLOW", "YELLOW", "RED", "YELLOW", "BLACK"],
'AREA': ['A01', 'B01', "A02", "A03", "A04", "A05", "B02", "B03", "B04", "B05", "A06"],
'QUANTITY': [5, 15, 8, 4, 9, 2, 19, 20, 3, 4, 1],
'PACKS TO FILL': [10, 5, 2, 6, 1, 8, 1, 0, 17, 16, 9]
}

df = pd.DataFrame(data)
df.set_index("ITEM NUMBER", inplace=True)
print(df)


Based on the above conditions, Item 100 from area A04 should merge into area A01 as it has the same item number, status and type as currently stored in A01 and the quantity in area A04 is less than packs to fill in area A01. This is the same for the other two items in the example dataframe below:

data = {
'ITEM NUMBER': [100, 105, 100],
'STATUS': ["OK", "OK", "NG"],
'TYPE': ["RED", "YELLOW", "BLACK"],
'AREA': ['A04', 'B05', "A06"],
'MOVE TO': ['A01', "B01", "A03"]

}

df = pd.DataFrame(data)
df.set_index("ITEM NUMBER", inplace=True)
print(df)


I have previously been using pivot tables to group items together and manually link areas together but this is a long process so would like to automate it if possible.

thank you.

Continue reading...