基于Pandas按行条件转换值：取整至最近偶数并匹配行总和

需求说明 #

将每行Q1 28、Q2 28、Q3 28、Q4 28列的数值取整到最近偶数，需满足：

• 取整后该行的总和与rounded_sum列值完全一致
• 若取整后总和与目标值存在偏差，需调整单个数值补平差异
• 最终结果不能出现负值

原始数据 #

Location    range     type  Q1 28   Q2 28   Q3 28   Q4 28   rounded_sum
NY          low re    AA    1.14    0       0       0       2
NY          low re    BB    0       0       0       0       0
NY          low re    DD    0.51    2       4       0       6
NY          low re    SS    0       0       0       0       0
NY          low stat  AA    1.03    2       2       4       10
NY          low stat  BB    0.45    0       2       2       4
NY          low stat  DD    1.53    2       4       6       14
NY          low stat  SS    0.26    0       0       2       2
CA          low re    AA    0.34    0       2       0       2
CA          low re    BB    0       0       0       0       0
CA          low re    DD    0.69    0       2       0       2
CA          low re    SS    0       0       0       0       0
CA          low stat  AA    0.18    0       0       2       2
CA          low stat  BB    0.2     0       0       0       0
CA          low stat  DD    0.27    0       0       2       2
CA          low stat  SS    0.04    0       0       0       0

期望输出 #

Location    range     type  Q1 28   Q2 28   Q3 28   Q4 28   rounded_sum
NY          low re    AA    2       0       0       0       2
NY          low re    BB    0       0       0       0       0
NY          low re    DD    0       2       4       0       6
NY          low re    SS    0       0       0       0       0
NY          low stat  AA    2       2       2       4       10
NY          low stat  BB    0       0       2       2       4
NY          low stat  DD    2       2       4       6       14
NY          low stat  SS    0       0       0       2       2
CA          low re    AA    0       0       2       0       2
CA          low re    BB    0       0       0       0       0
CA          low re    DD    0       0       2       0       2
CA          low re    SS    0       0       0       0       0
CA          low stat  AA    0       0       0       2       2
CA          low stat  BB    0       0       0       0       0
CA          low stat  DD    0       0       0       2       2
CA          low stat  SS    0       0       0       0       0

当前代码问题分析 #

你提供的代码仅对行总和做了取整处理，未覆盖需求核心逻辑：

• 没有对Q1 28~Q4 28的单个数值取最近偶数
• 未将取整后的行总和与rounded_sum对齐
• 缺失偏差调整与非负约束处理

优化后的代码实现 #

import pandas as pd
import numpy as np

# 假设数据已加载为DataFrame df
# df = pd.read_csv(...)

# 定义需要处理的季度列
q_cols = ['Q1 28', 'Q2 28', 'Q3 28', 'Q4 28']

def adjust_row(row):
    # 1. 将每个数值取整到最近偶数，同时保证非负
    rounded_vals = np.round(row[q_cols] / 2) * 2
    rounded_vals = rounded_vals.clip(lower=0)
    
    # 2. 计算当前总和与目标总和的差值
    current_sum = rounded_vals.sum()
    target_sum = row['rounded_sum']
    diff = target_sum - current_sum
    
    if diff == 0:
        return rounded_vals
    
    # 3. 按与原始值的偏差优先级调整，补平差值
    deviations = np.abs(row[q_cols] - rounded_vals)
    sorted_cols = deviations.sort_values(ascending=False).index
    
    for col in sorted_cols:
        if diff > 0:
            rounded_vals[col] += 2
            diff -= 2
        else:
            if rounded_vals[col] >= 2:
                rounded_vals[col] -= 2
                diff += 2
        if diff == 0:
            break
    
    return rounded_vals

# 应用调整逻辑到每行
df[q_cols] = df.apply(adjust_row, axis=1)

# 输出结果
print(df.to_string(index=False))

代码说明 #

1. 初始取整：通过np.round(val/2)*2得到最近偶数，用clip确保结果非负
2. 差值计算：对比取整后总和与rounded_sum，明确需要调整的幅度
3. 偏差调整：优先调整与原始值偏差最大的列，每次±2，直到总和与目标一致，同时保证调整后数值不小于0

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