PDF与Excel账号比对异常：为何仍出现交集账号？

我需要完成以下任务：解析多份PDF文件提取账号，从Excel表格提取账号，对比两者找出仅存在于PDF中的账号。该逻辑在其他文件测试正常，但本次运行异常——结果里出现了同时存在于PDF和Excel中的账号，而非预期的差异账号。已尝试用集合和普通循环两种方式比对，仍未解决问题。

相关代码（已隐去文件路径）：

import PyPDF2
import re
import pandas as pd
import os

def pdf_pull():
    pdfFileObj = open(r"pdf_file", 'rb')
    account_numbers = []
    pdfReader = PyPDF2.PdfReader(pdfFileObj)
    for i in range(len(pdfReader.pages)):
        pageObj = pdfReader.pages[i]
        extracted_text = pageObj.extract_text()
        n_list = extracted_text.split()

    #--------   get account numbers from first page -----------

        if len(n_list) > 0 and "#" in n_list:
            acct_index = n_list.index("#")
            next_el = n_list[acct_index + 1]
            if next_el.isdigit():
                account_numbers.append(next_el)

    # #--------   get account numbers from second page -----------

        if len(n_list) > 1 and n_list[0] == "UDC":
            for item in n_list:
                if item.isdigit() and len(item) >= 10:
                    # print(item)
                    account_numbers.append(item)
                if "-" in item and len(item) > 15:
                    account_numbers.append(item[10:])
                if len(item) > 20 and item.isdigit() == True:
                    account_numbers.append(item[5:])
                    account_numbers.remove(item)


    # #--------   get account numbers with letters and characters -----------

        for a in n_list:
            pe_num = re.compile(r"[A-Za-z]+[0-9]+", re.IGNORECASE)
            if len(a) >= 15 and len(a) < 23 and pe_num.match(a) and a not in account_numbers:
                account_numbers.append(a)


    #------ ensure there are no duplicates or empty strings -------
    account_numbers = list(set((account_numbers)))
    for number in account_numbers:
        if len(number) > 22:
            account_numbers.append(number[4:])
            account_numbers.remove(number)
    for n in account_numbers:
        if len(n) < 1:
            account_numbers.remove(n)


    #------- extract data from excel -------

    df = pd.read_excel(r"excel_file")

    # # #------ delete columns that aren't the ldc account number -------

    df.drop(df.columns.difference(["LDC Account"]), axis=1, inplace=True)

    # #------turn column into list -------

    df_list = df["LDC Account"].tolist()


    in_excel_not_pdfs = []
    in_pdfs_not_excel = []

    # # #------- find differences -------

    for li in df_list:
        if li not in account_numbers:
            in_excel_not_pdfs.append(li)
    for an in account_numbers:
        if an not in df_list:
            in_pdfs_not_excel.append(an)


    if len(in_excel_not_pdfs) == 0 and len(in_pdfs_not_excel) == 0:
        print("There are no differences")
    else:
        print("These are in excel, but not in pdfs: ", in_excel_not_pdfs, len(in_excel_not_pdfs))
        print("These are in pdfs, but not excel: ", in_pdfs_not_excel, len(in_pdfs_not_excel))

pdf_pull()

解决建议 #

• 优先排查数据格式不一致问题

  • 类型不匹配：Excel中账号可能是数字类型，PDF提取的是字符串，导致12345和"12345"被判定为不同。需要统一转成字符串后比对：

    # 统一转字符串并清洗PDF账号
    account_numbers = [str(num).strip() for num in account_numbers if str(num).strip()]
    # 清洗Excel数据，同时处理空值
    df_list = [str(num).strip() for num in df_list if pd.notna(num) and str(num).strip()]
    

  • 隐藏字符：PDF提取的账号可能带有空格、换行符或不可见字符，通过strip()可以去除首尾空白，必要时可加入正则替换所有非账号字符。

• 修正PDF提取逻辑的漏洞

  • 当前代码仅处理单份PDF，若需解析多份，需遍历文件夹下所有PDF文件，逐个打开处理
  • 避免在循环中修改列表：比如account_numbers.append(item[5:]); account_numbers.remove(item)这种操作，容易导致索引错乱或数据遗漏，建议先收集所有可能的账号，再统一做切割、去重处理

• 调整数据清洗顺序
  当前先转集合去重，再处理长账号切割，会导致切割后的新账号可能重复，且原长账号被移除后无法二次校验。建议顺序改为：先清洗所有账号（切割、去空、去隐藏字符），再转集合去重。

• 优化比对逻辑
  用集合操作替代循环比对，更高效且不易出错：

  account_set = set(account_numbers)
  excel_set = set(df_list)
  in_pdfs_not_excel = list(account_set - excel_set)
  in_excel_not_pdfs = list(excel_set - account_set)
  

• 添加调试步骤
  在关键节点打印数据样本，直接查看两边数据的实际形态：

  print("PDF提取的账号样本：", account_numbers[:5])
  print("Excel提取的账号样本：", df_list[:5])
  print("PDF账号类型：", type(account_numbers[0]) if account_numbers else None)
  print("Excel账号类型：", type(df_list[0]) if df_list else None)
  

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