如何从数据库读取的Pandas表构建或高效转换为NumPy协方差矩阵

嘿，我来帮你搞定这个高效转换的问题！你现在的需求是把从数据库读出来的三元组格式（index1、index2、协方差值）的Pandas表，快速转成对称的NumPy协方差矩阵对吧？你自己写的嵌套循环+try-except的方法确实不够高效，尤其是数据量大的时候，每次loc查询都是耗时操作，循环起来就积少成多了。

下面给你两种更高效的方案，都是利用Pandas的向量式操作（内部已经做了底层优化），比Python层面的循环快得多：

方案一：利用对称特性填充缺失值 #

这个方法直接先构建透视表，再用协方差矩阵的对称性补全缺失位置，步骤简洁高效：

import numpy as np
import pandas as pd

# 模拟从数据库读取的示例数据
pd_cov = pd.DataFrame([
    ['apple', 'apple', 1],
    ['apple', 'orange', 1],
    ['orange', 'orange', 0.5],
    ['lemon', 'lemon', 1.2],
    ['orange', 'lemon', -0.5],
    ['apple', 'lemon', -0.8]
], columns=['index1', 'index2', 'var'])

# 获取所有唯一的索引项（比如这里的水果名称）
# 如果需要和预期结果一致的顺序，可以手动指定：unique_idx = ['lemon', 'orange', 'apple']
unique_idx = pd_cov[['index1', 'index2']].stack().unique()

# 第一步：构建初始透视表，把index1作为行，index2作为列
cov_df = pd_cov.pivot(index='index1', columns='index2', values='var')
# 重新索引，保证行和列都包含所有唯一索引项
cov_df = cov_df.reindex(index=unique_idx, columns=unique_idx)
# 利用协方差矩阵的对称性，用转置后的矩阵填充缺失值
cov_df = cov_df.fillna(cov_df.T)
# 转成NumPy矩阵
cov_matrix = cov_df.to_numpy()

print(cov_matrix)

运行后就能得到你想要的结果：

[[1.2, -0.5, -0.8],
 [-0.5, 0.5, 1.0],
 [-0.8, 1.0, 1.0]]

方案二：合并原数据与转置数据后构建透视表 #

如果你担心原数据里有遗漏的对称项，可以先把原数据和它的转置（交换index1和index2）合并，再构建透视表，确保所有位置都有值：

import numpy as np
import pandas as pd

pd_cov = pd.DataFrame([
    ['apple', 'apple', 1],
    ['apple', 'orange', 1],
    ['orange', 'orange', 0.5],
    ['lemon', 'lemon', 1.2],
    ['orange', 'lemon', -0.5],
    ['apple', 'lemon', -0.8]
], columns=['index1', 'index2', 'var'])

# 合并原数据和它的转置（交换index1和index2）
cov_full = pd.concat([pd_cov, pd_cov.rename(columns={'index1':'index2', 'index2':'index1'})])
# 指定和预期结果一致的索引顺序
unique_idx = ['lemon', 'orange', 'apple']
# 构建透视表，用均值聚合（避免重复项冲突，协方差矩阵里每个对应该只有一个值，用first也可以）
cov_df = cov_full.pivot_table(
    index='index1',
    columns='index2',
    values='var',
    aggfunc='mean'
).reindex(index=unique_idx, columns=unique_idx)
# 转成NumPy矩阵
cov_matrix = cov_df.to_numpy()

print(cov_matrix)

为什么这两种方法更快？ #

你原来的方法用了Python层面的嵌套循环，每次循环都要做loc查询（时间复杂度O(logn)），整体是O(n²logn)的复杂度；而上面的方案用的是Pandas内部优化的向量式操作，底层是C实现的，时间复杂度接近O(n)，数据量越大，效率差距越明显。

备注：内容来源于stack exchange，提问作者shigeru ajisaka