在R中按Name和Job两列分组计算价格变量相关系数的方法

给定如下R语言数据集：

df <- data.frame (Name = c("adam", "adam", "adam", "brad", "brad", "brad", "chris", "chris", "chris", "abby", "abby", "abby", "lexi", "lexi", "lexi", "sarah", "sarah", "sarah"),
              job = c("doctor", "police", "doctor", "police", "doctor", "police", "doctor", "police", "doctor", "police", "doctor", "police", "doctor", "police", "doctor", "police", "doctor", "police"),
              price1 = c(43, 3, 6563, 44, 36, 5, 3, 546, 3, 24, 2, 4, 5, 58, 76, 46, 5, 3),
              price2 = c(0, 34, 43, 43, 34, 43, 354, 5, 4, 4, 6, 3, 5, 6, 4, 64, 5, 5),
              price3 = c(3, 2, 543, 533, 3, 55, 23, 235, 5235, 3, 23, 3, 4, 3, 3, 3, 4, 4)
             )

需要按Name和job两列分组，计算各价格变量（price1、price2、price3）之间的相关系数。直接使用cor(df)只能得到整体的相关系数，无法实现分组计算，求具体实现方法。

方法1：使用dplyr包（tidyverse生态） #

通过分组后生成相关系数矩阵，再整理为易读的格式：

library(dplyr)
library(tidyr)

# 分组计算所有价格变量对的相关系数，输出长格式结果
df %>%
  group_by(Name, job) %>%
  summarise(cor_matrix = list(cor(across(starts_with("price")), use = "pairwise.complete.obs")), .groups = "drop") %>%
  unnest_wider(cor_matrix) %>%
  pivot_longer(cols = starts_with("price"), 
               names_to = c("var1", "var2"), 
               names_sep = "\\.",
               values_to = "correlation")

关键说明：

• across(starts_with("price"))自动选中所有价格变量
• use = "pairwise.complete.obs"处理分组内可能存在的缺失值
• 用list()存储相关矩阵，避免分组后维度不匹配问题
• 最后通过pivot_longer将宽格式矩阵转为每行对应一对变量的长格式结果

如果只需要特定变量对的相关系数，可简化代码：

df %>%
  group_by(Name, job) %>%
  summarise(price1_price2_cor = cor(price1, price2, use = "pairwise.complete.obs"),
            price1_price3_cor = cor(price1, price3, use = "pairwise.complete.obs"),
            price2_price3_cor = cor(price2, price3, use = "pairwise.complete.obs"),
            .groups = "drop")

方法2：使用data.table包（高效处理大数据） #

针对大规模数据集，data.table的分组运算效率更优：

library(data.table)

setDT(df)

# 计算所有价格变量对的相关系数，整理为长格式
df[, .(cor_matrix = list(cor(.SD, use = "pairwise.complete.obs"))), 
   by = .(Name, job), 
   .SDcols = patterns("^price")] %>%
  .[, as.data.table(unlist(cor_matrix, recursive = FALSE)), by = .(Name, job)] %>%
  setnames(c("V1", "V2", "V3"), c("var1", "var2", "correlation"))

关键说明：

• .SDcols = patterns("^price")指定参与计算的价格变量
• 用list()保存分组后的相关矩阵，再通过unlist展开为结构化结果

如果只需要特定变量对，代码可简化为：

df[, .(price1_price2_cor = cor(price1, price2, use = "pairwise.complete.obs"),
       price1_price3_cor = cor(price1, price3, use = "pairwise.complete.obs"),
       price2_price3_cor = cor(price2, price3, use = "pairwise.complete.obs")),
   by = .(Name, job)]

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