Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
283 views
in Technique[技术] by (71.8m points)

python - Merging Pandas Dataframes on Multiple Conditions

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:


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
# 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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...