Python按行计算协方差并赋值至DataFrame列的实现方案

我有一个约40万行、30多列的Pandas DataFrame，需要为每行计算账户月度收益率与基准月度收益率的协方差，结果存入新列。计算所需的两组数据（12个账户收益率列、12个基准收益率列）都在同一行内，我已经能通过列名列表动态计算均值，但协方差的动态实现遇到困难。目前只能手动写死所有步骤，但这种方法灵活性太差。

示例数据与现有代码 #

import pandas as pd
import numpy as np

# 示例DataFrame
df = pd.DataFrame({'ACCT_ID':['A_12345','A_23456','A_34567','A_45678','A_56789'],
                  'Acct_m1_RoR':[-0.025, -0.035, -0.055, 0.0127, -0.065],
                  'Acct_m2_RoR':[0.025, 0.035, 0.055, 0.0127, 0.065],
                  'Acct_m3_RoR':[0.065, -0.075, -0.015, 0.0527, 0.015],
                  'Acct_m4_RoR':[-0.009, 0.015, -0.065, 0.0827, -0.025],
                  'BCHMK_m1_RoR':[-0.025, -0.035, -0.055, 0.0127, -0.065],
                  'BCHMK_m2_RoR':[-0.025, -0.035, -0.055, 0.0127, -0.065],
                  'BCHMK_m3_RoR':[-0.025, -0.035, -0.055, 0.0127, -0.065],
                  'BCHMK_m4_RoR':[-0.025, -0.035, -0.055, 0.0127, -0.065]})

# 列名列表（12个月度数据）
a1 = ['Acct_m1_RoR','Acct_m2_RoR','Acct_m3_RoR','Acct_m4_RoR','Acct_m5_RoR','Acct_m6_RoR','Acct_m7_RoR','Acct_m8_RoR','Acct_m9_RoR','Acct_m10_RoR','Acct_m11_RoR','Acct_m12_RoR']
b1 = ['BCHMK_m1_RoR','BCHMK_m2_RoR','BCHMK_m3_RoR','BCHMK_m4_RoR','BCHMK_m5_RoR','BCHMK_m6_RoR','BCHMK_m7_RoR','BCHMK_m8_RoR','BCHMK_m9_RoR','BCHMK_m10_RoR','BCHMK_m11_RoR','BCHMK_m12_RoR']

# 已实现的动态均值计算
df['acct_mean'] = np.mean(df[a1], axis=1)
df['bchmk_mean'] = np.mean(df[b1], axis=1)

半手动实现方案（灵活性差） #

df['cov'] = (((df['Acct_m1_RoR'] - df['acct_mean']) * (df['BCHMK_m1_RoR'] - df['bchmk_mean'])) 
+ ((df['Acct_m2_RoR'] - df['acct_mean']) * (df['BCHMK_m2_RoR'] - df['bchmk_mean'])) 
+ ((df['Acct_m3_RoR'] - df['acct_mean']) * (df['BCHMK_m3_RoR'] - df['bchmk_mean'])) 
+ ((df['Acct_m4_RoR'] - df['acct_mean']) * (df['BCHMK_m4_RoR'] - df['bchmk_mean'])) 
+ ((df['Acct_m5_RoR'] - df['acct_mean']) * (df['BCHMK_m5_RoR'] - df['bchmk_mean'])) 
+ ((df['Acct_m6_RoR'] - df['acct_mean']) * (df['BCHMK_m6_RoR'] - df['bchmk_mean'])) 
+ ((df['Acct_m7_RoR'] - df['acct_mean']) * (df['BCHMK_m7_RoR'] - df['bchmk_mean'])) 
+ ((df['Acct_m8_RoR'] - df['acct_mean']) * (df['BCHMK_m8_RoR'] - df['bchmk_mean'])) 
+ ((df['Acct_m9_RoR'] - df['acct_mean']) * (df['BCHMK_m9_RoR'] - df['bchmk_mean'])) 
+ ((df['Acct_m10_RoR'] - df['acct_mean']) * (df['BCHMK_m10_RoR'] - df['bchmk_mean'])) 
+ ((df['Acct_m11_RoR'] - df['acct_mean']) * (df['BCHMK_m11_RoR'] - df['bchmk_mean'])) 
+ ((df['Acct_m11_RoR'] - df['acct_mean']) * (df['BCHMK_m12_RoR'] - df['bchmk_mean']))) / 12

针对40万行的大数据量，优先使用向量化操作避免循环，同时通过列名列表实现动态计算。

方法一：基于广播的向量化计算（最优性能） #

利用Pandas的广播特性，直接对列组进行操作，无需手动展开每一列：

# 提取账户和基准的收益率列组
acct_cols = df[a1]
bchmk_cols = df[b1]

# 计算每行的离均差
acct_deviations = acct_cols.sub(df['acct_mean'], axis=0)
bchmk_deviations = bchmk_cols.sub(df['bchmk_mean'], axis=0)

# 计算协方差：逐元素相乘后按行求和，再除以样本数（12）
df['cov'] = (acct_deviations * bchmk_deviations).sum(axis=1) / len(a1)

方法二：利用numpy协方差函数（简洁但性能略低） #

numpy的np.cov默认按列计算，调整维度后按行计算并提取对应协方差值：

# 合并两组数据为每行的2x12数组，按行计算协方差并取[0,1]位置的值（即两组的协方差）
df['cov'] = df.apply(lambda row: np.cov([row[a1], row[b1]])[0,1], axis=1)

注意：此方法用到apply，性能略低于方法一，40万行数据建议优先选方法一。

关键说明 #

• 两种方法都完全依赖a1和b1列名列表，无需修改代码即可适配列数变化；
• 方法一的向量化操作是Pandas处理大数据的最优选择，计算速度远快于手动展开或循环；
• 协方差计算逻辑与半手动方案一致：$\text{cov}(X,Y) = \frac{1}{n}\sum_{i=1}^n (X_i - \bar{X})(Y_i - \bar{Y})$

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