python - Merging Pandas Dataframes on Multiple Conditions

Question

I am trying to merge Frame A to Frame B on the size asset life conditions specified in Frame B using pandas. I have tried both the merge and mergeasof commands but have not found a solution.

Frame A:

ID	Material	Size
0	A	9
1	B	21
2	B	14

question from:https://stackoverflow.com/questions/65838088/merging-pandas-dataframes-on-multiple-conditions

Answer 1

# step1: set_index('Material') and stack frameB
dfB.columns = ['Material', 'Size <= 10', '10 < Size <= 20', 'Size > 20']
obj_size_map = dfB.set_index('Material').stack()

# print(obj_size_map)
# Material                 
# A         Size <= 10          5
#           10 < Size <= 20    10
#           Size > 20          20
# B         Size <= 10          1
#           10 < Size <= 20     5
#           Size > 20          10
# dtype: int64



# step2. use pd.cut to create a size_tag for frame A, with frameB's columns as labels
dfA['size_tag'] = pd.cut(dfA.Size, bins=[-np.inf, 10, 20, np.inf], labels=dfB.columns[1:])

# print(dfA)
#   ID Material  Size        size_tag
# 0   0        A     9       Size <= 10
# 1   1        B    21        Size > 20
# 2   2        B    14  10 < Size <= 20



# step3. use pandas DF's index to assign a new columns
dfC = dfA.set_index(['Material', 'tag'])
dfC['Asset Life'] = obj_size_map

# print(dfC)
#                           ID  Size         size_tag  Asset Life
# Material tag                                                   
# A        Size <= 10        0     9       Size <= 10           5
# B        Size > 20         1    21        Size > 20          10
#          10 < Size <= 20   2    14  10 < Size <= 20           5



# step4. output
result = dfC.reset_index()[['ID', 'Material', 'Asset Life']]

# print(result)
#    ID Material  Asset Life
# 0   0        A           5
# 1   1        B          10
# 2   2        B           5