Match unique group from another dataframe - one to many match-CodePudding

df1 is the information in the box. Each box has a different volume size The volume of box A is 30, B is 25, etc.

df1 = pd.DataFrame({'boxID':['A', 'B', 'C', 'D'],'volume':[30,25,30,10]})
df1.set_index("boxID")
    volume
boxID   
A   30
B   25
C   30
D   10

df2 is the information of the product Each product has a different amount

df2 = pd.DataFrame({'Product No':['1', '2', '3', '4', '5', '6', '7'],'amount':[10, 5, 13, 15, 20, 10, 17]})
df2.set_index("Product No")
    amount
Product No  
1   10
2   5
3   13
4   15
5   20
6   10
7   17

insert "box id" column to df2 to find and match the appropriate box id of df1. Like the data frame at the bottom.

output_df2 = pd.DataFrame({'Product No':['1', '2', '3', '4', '5', '6', '7'],'amount':[10, 5, 13, 15, 20, 10, 17], 'box ID':['A', 'A', 'A', 'B', 'C', 'C', 'D']})
output_df2.set_index("Product No")

    amount  box ID
Product No      
1   10  A
2   5   A
3   13  A
4   15  B
5   20  C
6   10  C
7   17  D

Add the amount(df2) in order from the top to get close to the each box volume(df1) but not exceed the each box

For example, since the first box volume of df1 is 30, so it can contain first row product(amount 10) of df2 with the second row(amout 5) and the third(amount 13) is equal to 30 because 10 5 13 = 28. (However, if you add up to the 4th row, 10 5 13 15 = 43, which exceeds 30

Python is still a beginner, so please give me advice from many experts. It's a very important task for me.

match the appropriate box id of df1 in the box id column in df2.

CodePudding user response：

did you try with d2.merge(d1,on='',how='') or pd.concat()

CodePudding user response：

One way using pandas.cut

s1 = df1["volume"].cumsum()
s2 = df2["amount"].cumsum()

df2["box ID"] = pd.cut(s2, [0, *s1], labels=s1.index)
print(df2)

Output:

            amount box ID
Product No               
1               10      A
2                5      A
3               13      A
4               15      B
5               20      C
6               10      C
7               17      D