带约束条件的DataFrame季度数据比率差异最小化实现

现有一份包含季度数据的DataFrame，结构如下：

原始数据 #

需求约束 #

当修改总计（Total）列的数值后，需重新计算Q1和Q2的数值，需满足以下条件：

• 每行Q1与Q2的和等于该行的总计（Total）值；
• Q1和Q2的列总计保持不变；
• Q1_ratio和Q2_ratio尽可能接近原数值，最小化更新前后季度比率的差异，保留季节性特征。

调整示例 #

将USA的Total改为80，Canada改为90，Japan改为130，直接沿用原比率计算会导致Q1列总计为162、Q2列总计为138，不符合列总计不变的约束。调整后符合要求的结果如下：

计算逻辑 #

这个问题本质是带约束的优化问题，核心思路如下：

1. 变量定义：设每个国家的新Q1值为x_i，则Q2值为Total_i - x_i（满足每行和为Total的约束）。
2. 目标函数：最小化所有国家的(x_i/Total_i - original_Q1_ratio_i)^2的总和，让新的Q1比率尽可能接近原比率，间接保证Q2比率也接近原数值。
3. 约束条件：
   • 所有x_i的总和等于原Q1列总计；
   • 每个x_i需满足0 ≤ x_i ≤ Total_i（确保Q1、Q2数值非负）。

这是一个二次规划问题，可通过拉格朗日乘数法求解，或直接使用优化库计算。

Python实现方案 #

使用scipy.optimize.minimize求解带约束的优化问题，代码如下：

import pandas as pd
import numpy as np
from scipy.optimize import minimize

# 初始化原始数据
data = {
    '国家（Country）': ['USA', 'Canada', 'Japan'],
    'Q1': [70, 60, 40],
    'Q2': [30, 40, 60],
    'Q1_ratio': [0.7, 0.6, 0.4],
    'Q2_ratio': [0.3, 0.4, 0.6],
    '总计（Total）': [100, 100, 100]
}
df = pd.DataFrame(data)

# 修改后的Total值
new_totals = [80, 90, 130]
df['总计（Total）'] = new_totals

# 原Q1列总计
total_q1 = 170
original_ratios = df['Q1_ratio'].values

# 目标函数：最小化比率差异的平方和
def objective(x):
    ratios = x / df['总计（Total）'].values
    return np.sum((ratios - original_ratios)**2)

# 约束条件：x的总和等于total_q1
constraint = {'type': 'eq', 'fun': lambda x: np.sum(x) - total_q1}

# 边界条件：0 ≤ x_i ≤ Total_i
bounds = [(0, total) for total in df['总计（Total）'].values]

# 初始猜测：用原比率计算的Q1值
x0 = df['总计（Total）'].values * original_ratios

# 求解优化问题
result = minimize(objective, x0, method='SLSQP', bounds=bounds, constraints=constraint)

# 更新Q1和Q2列
df['Q1'] = result.x.round(1)
df['Q2'] = df['总计（Total）'] - df['Q1']
# 更新比率
df['Q1_ratio'] = (df['Q1'] / df['总计（Total）']).round(3)
df['Q2_ratio'] = (df['Q2'] / df['总计（Total）']).round(3)

# 添加总计行
total_row = pd.DataFrame({
    '国家（Country）': ['TOTAL'],
    'Q1': [df['Q1'].sum().round(0)],
    'Q2': [df['Q2'].sum().round(0)],
    'Q1_ratio': [np.nan],
    'Q2_ratio': [np.nan],
    '总计（Total）': [np.nan]
})
df = pd.concat([df, total_row], ignore_index=True)

print(df)

运行代码后，输出结果将与示例中的调整结果一致，满足所有约束条件。

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