为何Pandas比较CSV时，空单元格与None值会被判定为差异？

问题场景 #

使用Pandas比较两个数据集完全一致的CSV文件时，本该返回"两个文件完全相同"，但出现两种误判：

• 名为"Error"的列全为空值，被识别为存在差异
• 双方对应位置均为"None"值，同样被判定为差异

原代码如下：

import pandas as pd
import numpy as np  # Import numpy for NaN values

# List of file paths
file_paths = ['test_file_1.csv', 'test_file_2.csv']

# Create a list to store DataFrames
dataframes = []

# Load all CSV files into DataFrames
for file_path in file_paths:
    df = pd.read_csv(file_path)
    dataframes.append(df)

# Initialize a dictionary to store differences
differences = {}

# Compare each pair of DataFrames
for i in range(len(dataframes)):
    for j in range(i + 1, len(dataframes)):
        df1 = dataframes[i]
        df2 = dataframes[j]

        # Check if either DataFrame is None or has errors
        if df1 is None or df2 is None:
            continue

        # Fill empty cells with NaN
        df1 = df1.fillna(np.nan)
        df2 = df2.fillna(np.nan)

        # Compare the DataFrames cell by cell
        comparison_df = df1 != df2  # Use != to create a boolean DataFrame where differences are True
        print("BreakPoint")
        # Find the row and column indices where differences occur
        diff_locations = comparison_df.stack().reset_index()
        diff_locations.columns = ['Row', 'Column', 'Different']

        # Filter rows where differences are True
        diff_locations = diff_locations[diff_locations['Different']]

        # Store differences in the dictionary
        key = f'({file_paths[i]}) vs ({file_paths[j]})'
        differences[key] = diff_locations
        print("break point")

# Output the differences
for key, diff_locations in differences.items():
    if diff_locations.empty:
        print(f"{key}: The two CSV files are identical.")
    else:
        print(f"{key}: The two CSV files have differences at the following locations:")
        print(diff_locations)

问题原因 #

1. NaN的比较特性：Python中NaN != NaN会返回True，原代码用!=逐元素比较时，两个DataFrame中的NaN会被误判为差异。
2. 字符串"None"未被转换：如果CSV中的"None"是字符串类型，fillna(np.nan)不会处理这类值，导致双方的"None"字符串被判定为差异。

解决方案 #

方案1：使用Pandas原生compare方法（推荐） #

Pandas的compare方法会自动处理NaN的相等判断，仅返回真正有差异的位置，无需手动处理空值。修改核心逻辑如下：

# 替换原比较逻辑部分
for i in range(len(dataframes)):
    for j in range(i + 1, len(dataframes)):
        # 读取时直接将字符串"None"、空单元格转为NaN
        df1 = pd.read_csv(file_paths[i], na_values=["None", ""])
        df2 = pd.read_csv(file_paths[j], na_values=["None", ""])

        if df1 is None or df2 is None:
            continue

        # 使用compare方法，keep_shape=True保留所有行列便于定位差异
        comparison_df = df1.compare(df2, keep_shape=True)
        diff_locations = comparison_df.stack().reset_index()
        diff_locations.columns = ['Row', 'Column', 'Value_File1', 'Value_File2']

        key = f'({file_paths[i]}) vs ({file_paths[j]})'
        differences[key] = diff_locations

方案2：手动修正逐元素比较逻辑 #

若需保留自定义比较逻辑，需额外判断"两边都是NaN"的情况，将其标记为无差异：

# 替换原comparison_df = df1 != df2这一行
# 先判断元素是否相等，再排除两边都是NaN的场景
comparison_df = ~(df1 == df2) & ~(pd.isna(df1) & pd.isna(df2))

同时读取CSV时需将字符串"None"转为NaN：

df = pd.read_csv(file_path, na_values=["None", ""])

最终效果 #

修改后，全空列、双方均为NaN/原CSV中的"None"值的位置会被判定为相同，仅真正有差异的内容会被标记。

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