基于DataFrame的Child列值合并两个DataFrame的方法

步骤1：处理df2，提取分数并建立类别映射 #

首先从df2的Similarity_Score列提取纯数值，同时手动关联df1中Child列的对应类别（从预期结果可知，Severe weather对应第一个分数，Sunny类对应第二个分数）：

import pandas as pd

# 原数据
df1 = pd.DataFrame({'Parent': ['Stay home', "Stay home","Stay home", 'Go outside', "Go Outside","Go outside"],
                    'Child' : ['Severe weather', "Severe weather", "Severe weather", 'Sunny', "Sunny", "sunny"]})

df2 = pd.DataFrame({'Similarity_Score': ['SimilarityScore:0.43693185876069784', 'SimilarityScore:0.299807821163373']})

# 提取分数数值并转换为浮点数
df2['score'] = df2['Similarity_Score'].str.split(':').str[1].astype(float)
# 添加对应Child类别
df2['Child'] = ['Severe weather', 'Sunny']
# 保留需要的列
df2 = df2[['Child', 'score']]

步骤2：统一Child列的大小写，避免匹配误差 #

df1的Child列存在Sunny和sunny的大小写差异，需要统一格式：

# 对两个DataFrame的Child列做归一化处理
df1['Child_normalized'] = df1['Child'].str.capitalize()
df2['Child_normalized'] = df2['Child'].str.capitalize()

步骤3：合并DataFrame #

使用merge方法基于归一化后的Child列匹配：

result = pd.merge(df1, df2, on='Child_normalized', how='left')
# 整理列顺序并删除临时列
result = result[['Parent', 'Child', 'score']].rename(columns={'score': 'Similarity_Score'})

最终结果 #

运行后得到的result即为预期格式：

Parent           Child  Similarity_Score
0  Stay home  Severe weather          0.436932
1  Stay home  Severe weather          0.436932
2  Stay home  Severe weather          0.436932
3  Go outside           Sunny          0.299808
4  Go Outside           Sunny          0.299808
5  Go outside           sunny          0.299808

简化写法（可选） #

如果不想创建临时列，可直接用映射字典完成：

# 建立分数映射字典
score_map = {
    'Severe weather': float(df2['Similarity_Score'].iloc[0].split(':')[1]),
    'Sunny': float(df2['Similarity_Score'].iloc[1].split(':')[1])
}

# 对df1的Child列统一格式后映射分数
df1['Similarity_Score'] = df1['Child'].str.capitalize().map(score_map)

内容的提问来源于stack exchange，提问作者xavi