Resolved: Match unique group from another dataframe - one to many match

In this post, we will see how to resolve Match unique group from another dataframe – one to many match

Question:

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.

Best Answer:

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

If you have better answer, please add a comment about this, thank you!

Source: Stackoverflow.com

Resolved: Match unique group from another dataframe – one to many match

Question:

Best Answer:

Related