使用Pandas按Molecular weight合并多个DataFrame的技术问询

步骤说明 #

• 为每个输入DataFrame的Score列添加唯一后缀（如Score1、Score2），区分不同来源的数据
• 基于Molecular weight执行外连接合并所有DataFrame
• 将缺失的Score值填充为0
• 统一提取Population Score和Population Error（因所有DataFrame中对应值一致，取任意非空值即可）
• 整理Molecule Name，保留每个Molecular weight对应的首个出现名称

代码实现（双DataFrame场景） #

假设你的两个DataFrame分别为df1和df2，可按以下代码处理：

import pandas as pd

# 1. 重命名每个DataFrame的Score列，添加来源标识
df1_renamed = df1.rename(columns={"Score": "Score1"})
df2_renamed = df2.rename(columns={"Score": "Score2"})

# 2. 按Molecular weight外连接合并
merged_df = pd.merge(df1_renamed, df2_renamed, on="Molecular weight", how="outer", suffixes=("_left", "_right"))

# 3. 填充缺失的Score值为0
merged_df["Score1"] = merged_df["Score1"].fillna(0)
merged_df["Score2"] = merged_df["Score2"].fillna(0)

# 4. 提取Population相关列（取左侧或右侧非空值均可，因值一致）
merged_df["Population Score"] = merged_df["Population Score_left"].combine_first(merged_df["Population Score_right"])
merged_df["Population Error"] = merged_df["Population Error_left"].combine_first(merged_df["Population Error_right"])

# 5. 整理Molecule Name，保留首个出现的名称
merged_df["Molecule Name"] = merged_df["Molecule Name_left"].combine_first(merged_df["Molecule Name_right"])

# 6. 筛选并整理最终列顺序
final_df = merged_df[["Molecule Name", "Molecular weight", "Score1", "Score2", "Population Score", "Population Error"]]

# 查看结果
print(final_df)

多DataFrame通用扩展 #

如果有3个及以上DataFrame，可通过循环批量处理：

import pandas as pd

# 假设所有DataFrame存放在一个列表中
df_list = [df1, df2, df3]

# 批量重命名Score列
processed_dfs = []
for idx, df in enumerate(df_list, start=1):
    renamed_df = df.rename(columns={"Score": f"Score{idx}"})
    processed_dfs.append(renamed_df)

# 逐步外连接合并所有DataFrame
merged_df = processed_dfs[0]
for df in processed_dfs[1:]:
    merged_df = pd.merge(merged_df, df, on="Molecular weight", how="outer", suffixes=("_prev", "_curr"))
    
    # 合并Population相关列
    merged_df["Population Score"] = merged_df["Population Score_prev"].combine_first(merged_df["Population Score_curr"])
    merged_df["Population Error"] = merged_df["Population Error_prev"].combine_first(merged_df["Population Error_curr"])
    
    # 合并Molecule Name列
    merged_df["Molecule Name"] = merged_df["Molecule Name_prev"].combine_first(merged_df["Molecule Name_curr"])
    
    # 删除临时后缀列
    merged_df = merged_df.drop(columns=["Population Score_prev", "Population Score_curr", 
                                       "Population Error_prev", "Population Error_curr",
                                       "Molecule Name_prev", "Molecule Name_curr"])

# 填充所有Score列的缺失值为0
score_cols = [col for col in merged_df.columns if col.startswith("Score")]
merged_df[score_cols] = merged_df[score_cols].fillna(0)

# 调整列顺序
final_cols = ["Molecule Name", "Molecular weight"] + score_cols + ["Population Score", "Population Error"]
final_df = merged_df[final_cols]

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