excel比较两列差异性和一致性，统计之后降序排列

import pandas as pd
import numpy as np

# 读取Excel文件
file_path = 'last-all.xlsx'
df = pd.read_excel(file_path)

# 指定要比较的列名
column1 = '标注'
column2 = '不含72b'

# 将两列内容尝试转换为数字，无法转换的标记为 NaN
df[column1 + '_num'] = pd.to_numeric(df[column1], errors='coerce')
df[column2 + '_num'] = pd.to_numeric(df[column2], errors='coerce')

# 比较数字列的内容是否一致，非数字内容标记为 NaN
differences = df[column1 + '_num'] != df[column2 + '_num']

# 找出不一致的部分及其行号
diff_df = df[differences].copy()
diff_df['差异'] = df[column1].astype(str) + ' → ' + df[column2].astype(str)

# 统计每类不一致的部分及其比例
diff_counts = diff_df['差异'].value_counts()
diff_percentages = (diff_counts / len(diff_df)) * 100
# 打印一共有多少条不一样的结果
total_differences = len(diff_df)
total_rows = len(df)
percentage_differences = (total_differences / total_rows) * 100
#打印一致性
print(f"一致性为 {(1 - percentage_differences / 100):.2f}")
print(f"\n一共有 {total_differences} 条不一样的结果，占总数的比例为 {percentage_differences:.2f}%。")

# 打印统计结果
print("每类不一致的部分占比 (从高到低排序):")
for diff, percentage in zip(diff_counts.index, diff_percentages):
    print(f"{diff}: {percentage:.2f}%")

# 打印不一致部分及行号
# print("\n不一致部分的详细信息 (行号: 标注列 → 不含列):")
# for index, row in diff_df.iterrows():
#     print(f"行号 {index}: {row['差异']}")


# 输出nan出现的行号
nan_rows = diff_df[(diff_df[column1 + '_num'].isna()) | (diff_df[column2 + '_num'].isna())]
if not nan_rows.empty:
    print("\n含有nan的行号:")
    for index in nan_rows.index:
        print(f"行号 {index}")
else:
    print("\n没有含有nan的行。")

一致性

在这个代码基础上追加功能：比较column2那一列和column1相比一致性最高的类别，并打印类别和一致比例

import pandas as pd
import numpy as np

# 读取Excel文件
file_path = 'last-all.xlsx'
df = pd.read_excel(file_path)

# 指定要比较的列名
column1 = '标注'
column2 = '不含72b'

# 将两列内容尝试转换为数字，无法转换的标记为 NaN
df[column1 + '_num'] = pd.to_numeric(df[column1], errors='coerce')
df[column2 + '_num'] = pd.to_numeric(df[column2], errors='coerce')

# 比较数字列的内容是否一致，非数字内容标记为 NaN
differences = df[column1 + '_num'] != df[column2 + '_num']

# 找出不一致的部分及其行号
diff_df = df[differences].copy()
diff_df['差异'] = df[column1].astype(str) + ' → ' + df[column2].astype(str)

# 统计每类不一致的部分及其比例
diff_counts = diff_df['差异'].value_counts()
diff_percentages = (diff_counts / len(diff_df)) * 100

# 计算总体一致性
total_differences = len(diff_df)
total_rows = len(df)
percentage_differences = (total_differences / total_rows) * 100
consistency = (1 - percentage_differences / 100)
print(f"一致性为 {consistency:.2f}")
print(f"\n一共有 {total_differences} 条不一样的结果，占总数的比例为 {percentage_differences:.2f}%。")

# 打印统计结果
print("每类不一致的部分占比 (从高到低排序):")
for diff, percentage in zip(diff_counts.index, diff_percentages):
    print(f"{diff}: {percentage:.2f}%")

# 找出column2中与column1相比一致性最高的类别
df['一致'] = df[column1 + '_num'] == df[column2 + '_num']
consistency_by_category = df[df['一致']].groupby(column2).size()

# 计算每个类别的总体比例（占column2中的比例）
total_by_category = df.groupby(column2).size()
highest_consistency_category = (consistency_by_category / total_by_category).idxmax()
highest_consistency_percentage = (consistency_by_category / total_by_category).max() * 100

# 打印一致性最高的类别及其比例
print(f"\n在 {column2} 列中，与 {column1} 列相比一致性最高的类别是 '{highest_consistency_category}'，一致比例为 {highest_consistency_percentage:.2f}%。")