Python计算两数据源绝对差异百分比出现负值问题求助

原因分析 #

你代码里出现负百分比的核心原因是分母norm_diff为负数：

• 虽然你用np.abs()确保了分子diff是绝对值（非负），但norm_diff是c_df[f'{col}_s1']的均值，如果该列整体数值为负，均值就会是负数。
• 正数除以负数得到负数，最终计算出的percentage就会出现负值，直接格式化后就显示成负百分比了。

另外代码里存在变量名混淆的小问题：列表推导式里的diff和前面定义的绝对差异变量重名，虽不影响运行，但可读性较差。

修复方案 #

针对分母为负的情况，有两种可行修复方式：

方式一：对分母也取绝对值

计算norm_diff时直接取均值的绝对值，确保分母始终为正：

# Define the columns you want to process
columns = ['a', 'b', 'c', 'd']

# Create the results DataFrame
results_df = pd.DataFrame()

results_df['date'] = c_df['date']
results_df['id'] = c_df['id']

for col in columns:
    # calculating the absolute difference
    diff = np.abs(c_df[f'{col}_s1'] - c_df[f'{col}_s2'])

    # calculating mean for snowflake columns, take absolute value
    norm_diff = np.abs(c_df[f'{col}_s1'].mean())

    # Avoid division by zero
    if norm_diff == 0:
        percentage = np.zeros(len(diff))
    else:
        # calculating percentage difference b/w the columns in both datasets
        percentage = (diff / norm_diff) * 100

    # Round the percentage_difference and add % symbol, fix variable name conflict
    results_df[col] = [f"{round(pct)}%" for pct in percentage]

display(results_df)

方式二：对整个比例取绝对值

在计算百分比时，对diff/norm_diff的结果取绝对值，确保最终百分比非负：

# Define the columns you want to process
columns = ['a', 'b', 'c', 'd']

# Create the results DataFrame
results_df = pd.DataFrame()

results_df['date'] = c_df['date']
results_df['id'] = c_df['id']

for col in columns:
    # calculating the absolute difference
    diff = np.abs(c_df[f'{col}_s1'] - c_df[f'{col}_s2'])

    # calculating mean for snowflake columns
    norm_diff = c_df[f'{col}_s1'].mean()

    # Avoid division by zero
    if norm_diff == 0:
        percentage = np.zeros(len(diff))
    else:
        # calculating percentage difference b/w the columns in both datasets, take absolute value
        percentage = np.abs(diff / norm_diff) * 100

    # Round the percentage_difference and add % symbol, fix variable name conflict
    results_df[col] = [f"{round(pct)}%" for pct in percentage]

display(results_df)

额外说明 #

两种方式都能解决负百分比问题，选择哪种取决于你的业务逻辑：

• 若需要以均值的绝对值作为基准进行对比，选方式一；
• 若只是想确保百分比结果非负，选方式二。

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