如何使用pandas计算Excel表格中的相关系数r并制作相关表排查多重共线性

1. 导入依赖库 #

import pandas as pd
import numpy as np
from statsmodels.stats.outliers_influence import variance_inflation_factor

2. 生成与因变量相关的汇总表 #

你需要的相关性表可以直接通过pandas内置的相关系数计算方法生成，筛选和目标因变量n_nnld_trp的相关结果后按绝对值排序即可：

# 读取Excel数据，替换为你本地的文件路径
df = pd.read_excel("你的数据文件路径.xlsx")
# 先处理缺失值，避免计算报错
df = df.dropna()
# 计算所有变量的皮尔逊相关系数矩阵
corr_matrix = df.corr()
# 筛选和因变量n_nnld_trp的相关结果，保留两位小数，按绝对值降序排序
target_corr = corr_matrix[['n_nnld_trp']].round(2).sort_values(by='n_nnld_trp', key=abs, ascending=False)
# 输出查看相关性汇总表
print(target_corr)
# 若需要导出到Excel可加这行
target_corr.to_excel("变量相关性汇总表.xlsx")

3. 排查多重共线性（计算VIF） #

你提到的多重共线性排查使用方差膨胀因子VIF即可，对应公式为$VIF_i = \frac{1}{1-R_i^2}$，通常VIF大于10说明对应变量存在严重多重共线性，需要剔除：

# 提取所有自变量（排除因变量n_nnld_trp）
X = df.drop(columns=['n_nnld_trp'])
# 计算每个自变量的VIF值
vif_data = pd.DataFrame()
vif_data["自变量名称"] = X.columns
vif_data["VIF值"] = [variance_inflation_factor(X.values, i) for i in range(X.shape[1])]
# 按VIF值降序排序
vif_data = vif_data.sort_values(by='VIF值', ascending=False).round(2)
print(vif_data)

4. 合并得到完整汇总表 #

如果需要把相关系数、VIF值合并到同一张表，可执行以下代码：

summary_table = target_corr.join(vif_data.set_index('自变量名称')).rename(columns={'n_nnld_trp': '与n_nnld_trp的相关系数'})
print(summary_table)
# 导出到本地Excel
summary_table.to_excel("变量筛选汇总表.xlsx")

补充说明 #

• 相关系数绝对值大于0.7可判定为和因变量高度相关，可优先选择这类变量进入线性回归模型
• 如果你需要把之前计算的行均值纳入汇总表，直接用join方法把trip_mean和上面的summary_table合并即可

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