Pandas列值更新异常：组合列匹配Other ServiceTax问题

问题背景 #

用户使用Pandas编写代码，目标是找到衍生数值列的组合和与Other ServiceTax值（误差容限为2）匹配的情况，并将匹配列的值替换为对应的乘数。

问题现象 #

当前代码仅对FFC相关的衍生列（如FFC_0.103）完成了正确替换，其余带下划线的衍生列（如PBC_0.103）未按期望替换，仍保留原计算的乘积值。

期望效果 #

所有非零原列对应的衍生列均需替换为对应的乘数，而非原乘积值。

现有代码 #

import pandas as pd
import numpy as np
from itertools import combinations
data = {'FFC': [200, 52880],
    'CLUB': [0, 0],
    'PBC': [200, 0],
    'ESC': [200, 0],
    'LPLC': [250, 0],
    'FPLC': [0, 0],
    'Other ServiceTax': [105, 5447]}

df = pd.DataFrame(data)

# Define multiplier values for specific columns
multiplier_values = {'FFC': [0.1030, 0.1236, 0.14], 
                     'PBC': [0.1030, 0.1236, 0.14],
                     'ESC': [0.1030, 0.1236, 0.14], 
                     'LPLC': [0.1030, 0.1236, 0.14],
                     'FPLC': [0.1030, 0.1236, 0.14],
                     'CLUB': [0.1030, 0.1236, 0.14],}

for col, multipliers in multiplier_values.items():
    for multiplier in multipliers:
        new_column_name = f'{col}_{multiplier}'
        df[new_column_name] = (df[col] * multiplier)

numeric_columns = df.columns[df.columns.str.contains(r'\d')]

# Create a list to store DataFrames and concatenate them at the end
result_dfs = []

# Generate all combinations of columns for each row individually
tolerance = 2
for index, row in df.iterrows():
    desired_sum = row['Other ServiceTax']
    all_combinations = [comb for r in range(1, len(numeric_columns) + 1) for comb in 
    combinations(numeric_columns, r)]
    
    for combination in all_combinations:
        subset_sum = row[list(combination)].sum()
        if abs(subset_sum - desired_sum) <= tolerance:
            result_row = row.copy()
            print(result_row)

            for col in combination:
                multiplier_value = float(col.split('_')[1])
                if row[col] != 0:
                    result_row[col] = multiplier_value
                else:
                    result_row[col] = 0
            print(result_row)
            exit()

当前输出 #

FFC                 200.0000
CLUB                  0.0000
PBC                 200.0000
ESC                 200.0000
LPLC                250.0000
FPLC                  0.0000
Other ServiceTax    105.0000
FFC_0.103             0.1030
FFC_0.1236            0.1236
FFC_0.14              0.1400
PBC_0.103            20.6000
PBC_0.1236           24.7200
PBC_0.14             28.0000
ESC_0.103            20.6000
ESC_0.1236           24.7200
ESC_0.14             28.0000
LPLC_0.103           25.7500
LPLC_0.1236           0.1236
LPLC_0.14            35.0000
FPLC_0.103            0.0000
FPLC_0.1236           0.0000
FPLC_0.14             0.0000
CLUB_0.103            0.0000
CLUB_0.1236           0.0000
CLUB_0.14             0.0000

期望输出 #

FFC                 200.0000
CLUB                  0.0000
PBC                 200.0000
ESC                 200.0000
LPLC                250.0000
FPLC                  0.0000
Other ServiceTax    105.0000
FFC_0.103             0.1030
FFC_0.1236            0.1236
FFC_0.14              0.1400
PBC_0.103             0.1030
PBC_0.1236            0.1236
PBC_0.14              0.1400
ESC_0.103             0.1030
ESC_0.1236            0.1236
ESC_0.14              0.1400
LPLC_0.103            0.1030
LPLC_0.1236           0.1236
LPLC_0.14             0.1400
FPLC_0.103            0.0000
FPLC_0.1236           0.0000
FPLC_0.14             0.0000
CLUB_0.103            0.0000
CLUB_0.1236           0.0000
CLUB_0.14             0.0000

问题分析与修复 #

原代码存在两个核心问题：

1. 找到第一个符合条件的组合后直接调用exit()终止程序，导致仅处理了部分列的替换
2. 仅对找到的组合内的列进行替换，未覆盖所有非零原列对应的衍生列

修复后的代码如下：

import pandas as pd
import numpy as np
from itertools import combinations
data = {'FFC': [200, 52880],
    'CLUB': [0, 0],
    'PBC': [200, 0],
    'ESC': [200, 0],
    'LPLC': [250, 0],
    'FPLC': [0, 0],
    'Other ServiceTax': [105, 5447]}

df = pd.DataFrame(data)

# Define multiplier values for specific columns
multiplier_values = {'FFC': [0.1030, 0.1236, 0.14], 
                     'PBC': [0.1030, 0.1236, 0.14],
                     'ESC': [0.1030, 0.1236, 0.14], 
                     'LPLC': [0.1030, 0.1236, 0.14],
                     'FPLC': [0.1030, 0.1236, 0.14],
                     'CLUB': [0.1030, 0.1236, 0.14],}

for col, multipliers in multiplier_values.items():
    for multiplier in multipliers:
        new_column_name = f'{col}_{multiplier}'
        df[new_column_name] = (df[col] * multiplier)

numeric_columns = df.columns[df.columns.str.contains(r'\d')]

# Create a list to store DataFrames and concatenate them at the end
result_dfs = []

# Generate all combinations of columns for each row individually
tolerance = 2
for index, row in df.iterrows():
    desired_sum = row['Other ServiceTax']
    all_combinations = [comb for r in range(1, len(numeric_columns) + 1) for comb in 
    combinations(numeric_columns, r)]
    
    matched = False
    for combination in all_combinations:
        subset_sum = row[list(combination)].sum()
        if abs(subset_sum - desired_sum) <= tolerance:
            result_row = row.copy()
            # 遍历所有衍生列，替换非零原列对应的衍生列值为乘数
            for col in numeric_columns:
                base_col, multiplier_str = col.split('_')
                multiplier_value = float(multiplier_str)
                if row[base_col] != 0:
                    result_row[col] = multiplier_value
                else:
                    result_row[col] = 0
            result_dfs.append(result_row)
            matched = True
            break  # 找到第一个匹配组合后处理当前行，继续下一行
    if not matched:
        # 无匹配组合时保留原行
        result_dfs.append(row)

# 合并结果并输出
result_df = pd.DataFrame(result_dfs)
print(result_df.iloc[0])  # 打印第一行验证

修复说明 #

• 移除exit()，改为找到匹配组合后处理当前行并进入下一行的循环
• 替换逻辑从仅处理组合内列，改为遍历所有衍生列：根据衍生列名称拆分出原列名，若原列值非零则将衍生列替换为对应乘数，否则设为0
• 增加matched标记处理无匹配组合的情况，避免遗漏行

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