如何用Python在Excel中匹配不同SN列的相同行数据

问题描述 #

每次FOR循环处理Excel后会新增一个SN列，需要将各轮的SN列及对应R值按行位置对齐，保留所有行的对应数据。

待处理示例数据 #

print(df)
   SN1   R1   SN2    R2
0  SN1   38  SN20  1000
1  SN2   38  SN21   181
2  SN3  532  SN23     5
3  SN4  521  SN25    38
4  SN6  115  SN26  8718
5  SN7    3  SN27  3452
6  SN8  136  SN28   229
7  SN9  168  SN30   136

期望结果 #

生成按行对齐的表格，每行依次展示各轮的SN值、R值及R≥130的判断结果，列名按SN1/R1/RESULT1、SN2/R2/RESULT2的规则命名，无重复混乱的列。

现有代码（存在问题） #

import pandas as pd
import os

v = int(input("how many periods do you have:\n"))
new_excel_by_user = str(input("write the name of your new EXCEL file:"))
user_excel = pd.ExcelWriter(new_excel_by_user + '.xlsx', engine='xlsxwriter')
sys_excel = pd.ExcelWriter('sys_excel132.xlsx', engine='xlsxwriter')
final_excel = pd.ExcelWriter('final132.xlsx', engine='xlsxwriter')

merged_df_H = pd.DataFrame()
merged_df_L = pd.DataFrame()
final_merged = pd.DataFrame()

for i in range(v):
    print(f"\nperiod {i+1}:")
    excelpathH = input(r"Insert your Excel path(HIGH):").replace('"', '')
    excelpathL = input(r"Insert your Excel path(LOW):").replace('"', '')
    
    dfH = pd.read_excel(os.path.join(excelpathH), engine='openpyxl')
    dfL = pd.read_excel(os.path.join(excelpathL), engine='openpyxl')

    Rnh = dfH['KPIVALUE']
    Rnl = dfL['KPIVALUE']
    R = Rnh - Rnl
    merged_df_H[f'R{i+1}'] = R
    RESULT = merged_df_H[f'R{i+1}'].ge(130)
    
    # 原代码存在mask未定义、concat逻辑混乱问题
    merged_df_H = pd.concat([merged_df_H, dfH.loc[mask, 'SN'].rename(f'SN{i+1}').reset_index(drop=True),
                             R[mask].reset_index(drop=True)], axis=1)
    
    merged_df_H = pd.concat([merged_df_H, dfH, dfL, R, RESULT], axis=1)
    final_merged = pd.concat([final_merged, dfH['SN'], R, RESULT], axis=1)

当前错误结果 #

生成的表格存在列重复、数据错位、SN列未按行对齐的问题，且包含大量无意义的原始数据重复列，无法实现行对齐展示各轮数据的需求。

修改后的代码及说明 #

修正代码 #

import pandas as pd
import os

v = int(input("how many periods do you have:\n"))
new_excel_by_user = str(input("write the name of your new EXCEL file:"))
final_excel = pd.ExcelWriter(f'{new_excel_by_user}.xlsx', engine='xlsxwriter')

# 初始化最终结果DataFrame
final_merged = pd.DataFrame()

for i in range(v):
    print(f"\nperiod {i+1}:")
    excelpathH = input(r"Insert your Excel path(HIGH):").replace('"', '')
    excelpathL = input(r"Insert your Excel path(LOW):").replace('"', '')
    
    dfH = pd.read_excel(os.path.join(excelpathH), engine='openpyxl')
    dfL = pd.read_excel(os.path.join(excelpathL), engine='openpyxl')

    # 计算R值并判断是否≥130
    R = dfH['KPIVALUE'] - dfL['KPIVALUE']
    RESULT = R.ge(130)
    
    # 将当前轮的SN、R、RESULT按行对齐添加到最终结果
    final_merged[f'SN{i+1}'] = dfH['SN'].reset_index(drop=True)
    final_merged[f'R{i+1}'] = R.reset_index(drop=True)
    final_merged[f'RESULT{i+1}'] = RESULT.reset_index(drop=True)

# 保存结果到Excel
final_merged.to_excel(final_excel, index=False)
final_excel.close()

print(f"处理完成，结果已保存到 {new_excel_by_user}.xlsx")

关键修复点 #

1. 移除冗余逻辑：删除无关的merged_df_H、merged_df_L及多余的ExcelWriter实例，聚焦于构建目标结果。
2. 保障行对齐：使用reset_index(drop=True)确保每轮新增列与原有数据的行索引完全匹配，避免错位。
3. 清晰列命名：按SNn/Rn/RESULTn规则命名列，符合需求格式。
4. 修复未定义变量：删除原代码中未定义的mask，默认保留所有行数据；若需过滤R≥130的行，可添加filter_mask = R.ge(130)后对dfH/R/RESULT进行过滤。
5. 避免重复拼接：仅添加每轮必要的SN、R、判断结果，不再重复拼接原始Excel数据。

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