如何按条件合并两个DataFrame并将结果写入文本文件？

我有两个DataFrame（示例为df1和df2，实际数据集较大），需要实现以下操作：迭代取出df1的每一行写入文本文件，随后在df2中筛选满足df1当前行P4=df2.D4、P5=df2.D5、P6=df2.D6的行写入同一文件；若无匹配行则写入'No event'。编写的脚本无法得到预期结果，请求改进。

示例数据 #

df1 = pd.DataFrame({'P1': [2019, 2019, 2018, 2019, 2019, 2019],
                    'P2': [1, 2, 8, 3, 4, 5],
                    'P3': [1, 1, 8, 1, 1, 1],
                    'P4': [6, 2.3, 8.8, 4.6, 5.3, 7],
                    'P5': [11.4, 18, 18.8, 25, 12, 27.4],
                    'P6': [32.44, 31.56, 18, 33.01, 31.24, 31.95]
                   })


df2 = pd.DataFrame({'D1': [2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2018, 2018, 2018, 2018, 2018],
                    'D2': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 1, 2, 3, 4, 5],
                    'D3': [5, 6, 3, 2, 1, 10, 11, 12, 7, 6, 5, 4, 1, 2, 6],
                    'D4': [6, 2.3, 4.6, 5.3, 7, 6, 2.3, 4.6, 5.3, 7,6, 2.3, 4.6, 5.3, 7],
                    'D5': [11.4, 18, 25, 12, 27.4, 11.4, 18, 25, 12, 27.4, 11.4, 18, 25, 12, 27.4],
                    'D6': [32.44, 31.56, 33.01, 31.24, 31.95, 32.44, 31.56, 33.01, 31.24, 31.95, 32.44, 31.56, 33.01, 31.24, 31.95],
                    'ST': ['AB', 'BC', 'CD', 'EF', 'GH', 'IJ', 'KL', 'ZY', 'ST', 'QD', 'YT', 'RT', 'EW', 'SD', 'FF']
                   })

预期输出 #

0  2019   1   1  6.0  11.4  32.44
1 2019   1   5  6.0  11.4  32.44  AB
2 2019   6  10  6.0  11.4  32.44  IJ
3 2018   1   5  6.0  11.4  32.44  YT
4 2019   2   1  2.3  18.0  31.56
5 2019   2   6  2.3  18.0  31.56  BC
6 2019   7  11  2.3  18.0  31.56  KL
7 2018   2   4  2.3  18.0  31.56  RT
8 2018   8   8  8.8  18.8  18.00
No event 

现有脚本 #

df1 = pd.DataFrame({'P1': [2019, 2019, 2018, 2019, 2019, 2019],
                    'P2': [1, 2, 8, 3, 4, 5],
                    'P3': [1, 1, 8, 1, 1, 1],
                    'P4': [6, 2.3, 8.8, 4.6, 5.3, 7],
                    'P5': [11.4, 18, 18.8, 25, 12, 27.4],
                    'P6': [32.44, 31.56, 18, 33.01, 31.24, 31.95]
                   })


df2 = pd.DataFrame({'D1': [2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2018, 2018, 2018, 2018, 2018],
                    'D2': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 1, 2, 3, 4, 5],
                    'D3': [5, 6, 3, 2, 1, 10, 11, 12, 7, 6, 5, 4, 1, 2, 6],
                    'D4': [6, 2.3, 4.6, 5.3, 7, 6, 2.3, 4.6, 5.3, 7,6, 2.3, 4.6, 5.3, 7],
                    'D5': [11.4, 18, 25, 12, 27.4, 11.4, 18, 25, 12, 27.4, 11.4, 18, 25, 12, 27.4],
                    'D6': [32.44, 31.56, 33.01, 31.24, 31.95, 32.44, 31.56, 33.01, 31.24, 31.95, 32.44, 31.56, 33.01, 31.24, 31.95],
                    'ST': ['AB', 'BC', 'CD', 'EF', 'GH', 'IJ', 'KL', 'ZY', 'ST', 'QD', 'YT', 'RT', 'EW', 'SD', 'FF']
                   })
L=len(df1)

with open('readme.txt', 'w') as f:
    
    for i in range(L):
        a=df1.loc[[i]]
        f.write(a, /n)
        C1=a['P4']
        C2=a['P5']
        C3=a['P6']
        if df3 = df2[(df2['D4'] == C1) and df2['D5']==C2 and df2['D6']==C3]
        f.write(df3, /n)
        else
        f.write(n/,'no event'/n)

print(df1)        

问题分析与改进方案 #

现有脚本存在多个语法和逻辑错误：

1. 语法错误：
   • if语句末尾未加冒号:
   • 赋值操作df3 = ...直接写在if条件中，不符合Python语法
   • 换行符应为\n而非/n或n/
   • f.write()无法直接写入DataFrame对象，需先转为字符串
   • pandas中布尔条件判断应使用&而非and，且需加括号分组
2. 逻辑问题：
   • df1.loc[[i]]返回DataFrame，提取P4/P5/P6得到的是Series，需取出具体值
   • 未处理浮点数精度问题，实际大数据集可能因精度误差导致匹配失败

改进后的代码 #

import pandas as pd
import numpy as np

df1 = pd.DataFrame({'P1': [2019, 2019, 2018, 2019, 2019, 2019],
                    'P2': [1, 2, 8, 3, 4, 5],
                    'P3': [1, 1, 8, 1, 1, 1],
                    'P4': [6, 2.3, 8.8, 4.6, 5.3, 7],
                    'P5': [11.4, 18, 18.8, 25, 12, 27.4],
                    'P6': [32.44, 31.56, 18, 33.01, 31.24, 31.95]
                   })


df2 = pd.DataFrame({'D1': [2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2018, 2018, 2018, 2018, 2018],
                    'D2': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 1, 2, 3, 4, 5],
                    'D3': [5, 6, 3, 2, 1, 10, 11, 12, 7, 6, 5, 4, 1, 2, 6],
                    'D4': [6, 2.3, 4.6, 5.3, 7, 6, 2.3, 4.6, 5.3, 7,6, 2.3, 4.6, 5.3, 7],
                    'D5': [11.4, 18, 25, 12, 27.4, 11.4, 18, 25, 12, 27.4, 11.4, 18, 25, 12, 27.4],
                    'D6': [32.44, 31.56, 33.01, 31.24, 31.95, 32.44, 31.56, 33.01, 31.24, 31.95, 32.44, 31.56, 33.01, 31.24, 31.95],
                    'ST': ['AB', 'BC', 'CD', 'EF', 'GH', 'IJ', 'KL', 'ZY', 'ST', 'QD', 'YT', 'RT', 'EW', 'SD', 'FF']
                   })

with open('readme.txt', 'w') as f:
    # 遍历df1的每一行
    for idx, row in df1.iterrows():
        # 将df1当前行转为字符串写入，去掉表头
        row_str = row.to_string(header=False)
        f.write(row_str + '\n')
        
        # 获取当前行的匹配键值
        p4 = row['P4']
        p5 = row['P5']
        p6 = row['P6']
        
        # 在df2中筛选匹配行，处理浮点数精度问题
        matched = df2[
            np.isclose(df2['D4'], p4) & 
            np.isclose(df2['D5'], p5) & 
            np.isclose(df2['D6'], p6)
        ]
        
        if not matched.empty:
            # 将匹配行转为字符串写入，去掉表头
            matched_str = matched.to_string(header=False)
            f.write(matched_str + '\n')
        else:
            f.write('No event\n')

说明 #

• 使用df1.iterrows()遍历行更简洁，直接获取每行索引和数据
• 用np.isclose处理浮点数匹配，避免精度误差导致的匹配失败
• 所有DataFrame对象转为字符串时去掉表头，保证输出格式与预期一致
• 修正了所有语法错误，代码可正常运行

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