Python读取Excel正斜杠问题：为何n/a无法被检测识别？

我有一个包含20余列的Excel表格，希望筛选出不含文本n/a的行，编写了如下Python代码进行检测，但运行后发现仅能检测到entfallen和tbd，无法识别n/a，怀疑是n/a中的正斜杠导致问题，请问该如何解决？

import pandas as pd
import re
import os

def extract_data(input_file):
    # Read the input Excel file
    df = pd.read_excel(input_file)

    # Check if 'agreed' is present in column 5
    if not df.iloc[:, 4].astype(str).str.contains('Agreed', case=False, na=False).any():
        print("No 'agreed' found in column 5")
        return

    # Filter rows where column 5 contains 'agreed'
    filtered_df = df[df.iloc[:, 4].astype(str).str.contains('Agreed', case=False, na=False)]

    # Initialize DataFrames for the four sheets
    volumen_df = pd.DataFrame()
    mrr_premium_df = pd.DataFrame()
    lrr_df = pd.DataFrame()
    meb_df = pd.DataFrame()

    # Define a function to extract values from text
    def extract_values(text, pattern):
        match = re.search(pattern, text, re.IGNORECASE)
        return match.group(1) if match else None

    # Function to check if exclusion texts are in the specified column
    def contains_exclusion_texts(value):
        exclusion_texts = ["n/a", "entfallen", "tbd"]
        return any(excluded_text in str(value).lower() for excluded_text in exclusion_texts)

    # Process each row individually
    for index, row in filtered_df.iterrows():
        col13 = contains_exclusion_texts(row.iloc[13])
        col14 = contains_exclusion_texts(row.iloc[14])
        col15 = contains_exclusion_texts(row.iloc[15])
        
        pdu_short_name = str(row.iloc[11]).replace('FAULT_', '')
        cycle_time = extract_values(str(row.iloc[20]), r'(\d+\s*ms)')
        n_value = extract_values(str(row.iloc[20]), r'n\s*=\s*(\d+)')
        q_value = extract_values(str(row.iloc[20]), r'q\s*=\s*(\d+)')
        max_delta_counter = extract_values(str(row.iloc[20]), r'MaxDeltaCounterInit\s*=\s*(\d+)')
        no_new_or_repeated_data = extract_values(str(row.iloc[20]), r'NoNewOrRepeatedData\s*=\s*(\d+)')

        data = {
            'PDU short name': pdu_short_name,
            'Cycle time': cycle_time,
            'n': n_value,
            'q': q_value,
            'Max Delta Counter': max_delta_counter,
            'No New Or Repeated Data': no_new_or_repeated_data
        }

        if not col13:
            # Volumen
            volumen_df = volumen_df.append(data, ignore_index=True)
        if not col14 :
            # Check column 12 for LRR or MRR_Premium
            type_col_value = str(row.iloc[12])
            if 'LRR' in type_col_value:
                lrr_df = lrr_df.append(data, ignore_index=True)
            if 'MRR_Premium' in type_col_value:
                mrr_premium_df = mrr_premium_df.append(data, ignore_index=True)
        if not col15 :
            # Meb
            meb_df = meb_df.append(data, ignore_index=True)

    # Define output file path
    output_file = os.path.join(os.path.dirname(input_file), 'extracted_data3.xlsx')

    # Save the extracted data to a new Excel file with different sheets
    with pd.ExcelWriter(output_file) as writer:
        volumen_df.to_excel(writer, sheet_name='Volumen', index=False)
        mrr_premium_df.to_excel(writer, sheet_name='MRR Premium', index=False)
        lrr_df.to_excel(writer, sheet_name='LRR', index=False)
        meb_df.to_excel(writer, sheet_name='Meb', index=False)

    print(f"Data extracted and saved to {output_file}")

# Get the input file path from the user
input_file_path = input("Enter the path to your input Excel file: ")

# Call the function with the user-provided file path
extract_data(input_file_path)

问题核心不是正斜杠，而是Excel中的n/a常被识别为缺失值（NaN），当你用str(value).lower()转换时，NaN会变成字符串'nan'，自然匹配不到'n/a'。另外，字符串形式的n/a也可能存在大小写不匹配的问题。

修复步骤 #

1. 显式处理缺失值：先判断值是否为NaN，若是则直接替换为'n/a'字符串，再统一转小写
2. 统一匹配规则：把排除文本也转成小写，避免大小写差异导致匹配失败

修改后的contains_exclusion_texts函数：

def contains_exclusion_texts(value):
    # 处理NaN，转小写统一格式
    str_value = str(value).lower() if pd.notna(value) else 'n/a'
    exclusion_texts = ["n/a", "entfallen", "tbd"]
    # 排除文本也转小写，确保匹配一致性
    return any(excluded_text.lower() in str_value for excluded_text in exclusion_texts)

额外优化建议 #

• 替换已弃用的append()方法：改用列表存储数据后转DataFrame，效率更高
• 保留核心逻辑的同时，提升代码可维护性

修改后的完整代码：

import pandas as pd
import re
import os

def extract_data(input_file):
    # Read the input Excel file
    df = pd.read_excel(input_file)

    # Check if 'agreed' is present in column 5
    if not df.iloc[:, 4].astype(str).str.contains('Agreed', case=False, na=False).any():
        print("No 'agreed' found in column 5")
        return

    # Filter rows where column 5 contains 'agreed'
    filtered_df = df[df.iloc[:, 4].astype(str).str.contains('Agreed', case=False, na=False)]

    # 用列表存储数据，替代已弃用的append()
    volumen_data = []
    mrr_premium_data = []
    lrr_data = []
    meb_data = []

    # Define a function to extract values from text
    def extract_values(text, pattern):
        match = re.search(pattern, text, re.IGNORECASE)
        return match.group(1) if match else None

    # Function to check if exclusion texts are in the specified column
    def contains_exclusion_texts(value):
        str_value = str(value).lower() if pd.notna(value) else 'n/a'
        exclusion_texts = ["n/a", "entfallen", "tbd"]
        return any(excluded_text.lower() in str_value for excluded_text in exclusion_texts)

    # Process each row individually
    for index, row in filtered_df.iterrows():
        col13 = contains_exclusion_texts(row.iloc[13])
        col14 = contains_exclusion_texts(row.iloc[14])
        col15 = contains_exclusion_texts(row.iloc[15])
        
        pdu_short_name = str(row.iloc[11]).replace('FAULT_', '')
        cycle_time = extract_values(str(row.iloc[20]), r'(\d+\s*ms)')
        n_value = extract_values(str(row.iloc[20]), r'n\s*=\s*(\d+)')
        q_value = extract_values(str(row.iloc[20]), r'q\s*=\s*(\d+)')
        max_delta_counter = extract_values(str(row.iloc[20]), r'MaxDeltaCounterInit\s*=\s*(\d+)')
        no_new_or_repeated_data = extract_values(str(row.iloc[20]), r'NoNewOrRepeatedData\s*=\s*(\d+)')

        data = {
            'PDU short name': pdu_short_name,
            'Cycle time': cycle_time,
            'n': n_value,
            'q': q_value,
            'Max Delta Counter': max_delta_counter,
            'No New Or Repeated Data': no_new_or_repeated_data
        }

        if not col13:
            volumen_data.append(data)
        if not col14 :
            type_col_value = str(row.iloc[12])
            if 'LRR' in type_col_value:
                lrr_data.append(data)
            if 'MRR_Premium' in type_col_value:
                mrr_premium_data.append(data)
        if not col15 :
            meb_data.append(data)

    # 转换为DataFrame
    volumen_df = pd.DataFrame(volumen_data)
    mrr_premium_df = pd.DataFrame(mrr_premium_data)
    lrr_df = pd.DataFrame(lrr_data)
    meb_df = pd.DataFrame(meb_data)

    # Define output file path
    output_file = os.path.join(os.path.dirname(input_file), 'extracted_data3.xlsx')

    # Save the extracted data to a new Excel file with different sheets
    with pd.ExcelWriter(output_file) as writer:
        volumen_df.to_excel(writer, sheet_name='Volumen', index=False)
        mrr_premium_df.to_excel(writer, sheet_name='MRR Premium', index=False)
        lrr_df.to_excel(writer, sheet_name='LRR', index=False)
        meb_df.to_excel(writer, sheet_name='Meb', index=False)

    print(f"Data extracted and saved to {output_file}")

# Get the input file path from the user
input_file_path = input("Enter the path to your input Excel file: ")

# Call the function with the user-provided file path
extract_data(input_file_path)

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