Python实现Excel行求和并用于文件对比的技术求助

问题说明 #

现有代码实现了CSV转XLSX、按日期配对FLIS/SEI2文件的功能，但对比逻辑不符合需求：需要将FLIS文件的第3行与第4行对应列求和后，再与SEI2文件的第4行进行差异对比，而非直接对比单一行。

修改后的完整代码 #

import pandas as pd
import os
import re
from datetime import date
from xlsxwriter.workbook import Workbook

# Path
directory = "#The path of the files"

# Expression to find date suffix in CSV for reading files
pattern = r'(\d{4}-\d{2}-\d{2})'

# Dict of ID's
number_to_name = {
    # List removed to shorten post
}

# Function for translating ID
def translate_number(number):
    return number_to_name.get(number, f"Unknown_{number}")

# 重写对比函数：实现FLIS行求和后与SEI2行对比
def compare_excel_files(file1, file2, is_flis_first):
    # 读取文件（不跳过行，手动提取目标行）
    df_flis = pd.read_excel(file1) if is_flis_first else pd.read_excel(file2)
    df_sei2 = pd.read_excel(file2) if is_flis_first else pd.read_excel(file1)

    # 提取FLIS的第3行（索引2）和第4行（索引3），转换为数值类型后求和
    flis_row3 = df_flis.iloc[2].apply(pd.to_numeric, errors='coerce')
    flis_row4 = df_flis.iloc[3].apply(pd.to_numeric, errors='coerce')
    flis_sum_row = flis_row3 + flis_row4

    # 提取SEI2的第4行（索引3），转换为数值类型
    sei2_row4 = df_sei2.iloc[3].apply(pd.to_numeric, errors='coerce')

    # 将两行转换为DataFrame，设置相同索引以便对比
    compare_df = pd.DataFrame({
        'FLIS_Sum(R3+R4)': flis_sum_row,
        'SEI2_R4': sei2_row4
    })

    # 筛选出差异行（排除NaN相等的情况）
    differences = compare_df[compare_df['FLIS_Sum(R3+R4)'] != compare_df['SEI2_R4']]
    # 处理NaN的情况：如果一方是NaN另一方不是，也视为差异
    nan_diffs = compare_df[(compare_df['FLIS_Sum(R3+R4)'].isna() != compare_df['SEI2_R4'].isna())]
    differences = pd.concat([differences, nan_diffs]).drop_duplicates()

    if not differences.empty:
        filename1 = os.path.basename(file1)
        filename2 = os.path.basename(file2)
        print(f"Differences found between {filename1} and {filename2} :")
        print(differences)
        print()
        return True
    return False

# Function to find missing IDs in an Excel sheet
def find_missing_IDs(file_path):
    # Read the Excel file
    df = pd.read_excel(file_path)

    # Find unique ID names in the Excel sheet
    present_IDs = set(df.iloc[-1, :])

    # Find missing IDs
    missing_IDs = set(number_to_name.values()) - present_IDs

    return missing_IDs

# Flag to track if there are differences in the files
differences_found = False

# Go through each file in the directory
for filename in os.listdir(directory):
    if filename.endswith(".csv"):
        # Find date from CSV filename
        match = re.search(pattern, filename)
        if match:
            date_suffix = match.group(1)
        # Skip files if filename does not match the desired format
        else:
            print(f"Skipping file {filename} as it does not have a valid date suffix.")
            continue

        # Read data from CSV file
        df = pd.read_csv(os.path.join(directory, filename), header=None)

        # Split data in the first column out to the necessary number of columns
        max_commas = df[0].str.split(',').transform(len).max()
        df[[f'name_{x}' for x in range(max_commas)]] = df[0].str.split(',', expand=True)

        # Remove the original unnamed column
        df.drop(0, axis=1, inplace=True)

        # Remove last column (name_0 - column only shows report time)
        df = df[df.columns.drop(list(df.filter(regex='name_0')))]

        # Find SOR number
        ids_row = df.iloc[0]

        # Translate SOR number to ID name
        translated_ids_row = ids_row.apply(translate_number)

        # Create new dataframe with row for ID names
        translated_df = pd.DataFrame([translated_ids_row.tolist()], columns=df.columns)

        # Insert new dataframe onto the original dataframe (new row at the bottom with ID name)
        df = pd.concat([df, translated_df], ignore_index=True)

        # Save the new dataframe as an Excel file
        try:
            # Pull filename without path name
            filename_only = os.path.basename(filename)
            output_name = f"{filename_only.split('_')[2].split('-')[0]} {date_suffix}.xlsx"
            output_path = os.path.join(directory, output_name)
            df.to_excel(output_path, index=None)

            # Check if there is a corresponding file to compare with (date pair)
            if "FLIS" in filename:
                sei2_filename = f"SEI2 {date_suffix}.xlsx"
                sei2_file = os.path.join(directory, sei2_filename)
                if os.path.exists(sei2_file):
                    # 调用对比函数，标记第一个文件是FLIS
                    if compare_excel_files(output_path, sei2_file, is_flis_first=True):
                        differences_found = True

            elif "SEI2" in filename:
                flis_filename = f"FLIS {date_suffix}.xlsx"
                flis_file = os.path.join(directory, flis_filename)
                if os.path.exists(flis_file):
                    # 调用对比函数，标记第一个文件是SEI2，第二个是FLIS
                    if compare_excel_files(output_path, flis_file, is_flis_first=False):
                        differences_found = True

        # Print message if there is an error when trying to save
        except Exception as e:
            print(f"There was an error when I tried to save: {e}")

# If there are no differences in date-paired files
if not differences_found:
    print("No differences found in any of the files.")
    print()

# Function to ask the user to show missing IDs
def ask_show_missing_IDs():
    while True:
        answer = input("Would you like a list of which IDs did not appear in the data on the different days? (Yes/No): ").strip().lower()
        print()
        if answer in ["yes", "no"]:
            return answer == "yes"
        else:
            print("Please answer with 'Yes' or 'No'.")

# Function to extract the date from the filename
def extract_date(filename):
    # Remove any path and file type name, and then split by space and '-' to get the date
    return filename.split()[-1].split('.')[0]

# Ask the user if they want to see missing IDs
show_missing_IDs = ask_show_missing_IDs()

# Show missing IDs if the user wants it
if show_missing_IDs:
    missing_IDs_found = False
    for filename in os.listdir(directory):
        if filename.endswith(".xlsx") and filename.startswith("FLIS"):
            output_path = os.path.join(directory, filename)
            missing_IDs = find_missing_IDs(output_path)
            if missing_IDs:
                if not missing_IDs_found:
                    print("Here are the missing IDs for each of the following dates:")
                    missing_IDs_found = True
                # Sort the IDs in alphabetical order before printing
                sorted_missing_IDs = sorted(missing_IDs)
                # Only print the date from the filename without "FLIS"
                print(f"{extract_date(filename)}:")
                for ID in sorted_missing_IDs:
                    print(ID)
                print()
    if not missing_IDs_found:
        print("No missing IDs found in any of the files.")

# Function to ask the user to delete the files
def ask_delete_files():
    while True:
        answer = input("Should the CSV and Excel files be automatically deleted? (Yes/No): ").strip().lower()
        print()
        if answer in ["yes", "no"]:
            return answer == "yes"
        else:
            print("Please answer with 'Yes' or 'No'.")

# Ask the user if they want to delete the files after processing
delete_files = ask_delete_files()

# Delete the files if the user wants it
if delete_files:
    for filename in os.listdir(directory):
        if filename.endswith((".csv", ".xlsx")):
            os.remove(os.path.join(directory, filename))
    print("The files have been deleted.")
    print()

# Wait for user input before window closes
input("Press Enter to exit")

关键修改点 #

1. 重写compare_excel_files函数

   • 新增is_flis_first参数，区分传入的第一个文件是FLIS还是SEI2
   • 手动提取FLIS的第3行（索引2）和第4行（索引3），用pd.to_numeric转换为数值类型后求和，避免字符串拼接
   • 提取SEI2的第4行（索引3），同样转换为数值类型
   • 将两行数据合并为对比DataFrame，筛选出数值不等或一方为NaN的差异项

2. 调整对比调用逻辑

   • 去掉原有的skiprows参数，改为手动提取目标行
   • 根据当前处理的文件类型，正确传递is_flis_first参数，确保FLIS的行求和逻辑正确执行

3. 完善差异判断

   • 不仅判断数值不等的情况，还处理了一方为NaN另一方不为NaN的差异场景，避免遗漏数据缺失类问题

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