R语言中实现两个表格的聚合与重组问题求助

原始数据 #

Table 1 #

Test    Comp.Name
A Test  Comp.1
B Test  Comp.1
B Test  Comp.2
C Test  Comp.2
A Test  Comp.3
B Test  Comp.4

对应的R定义代码：

table1 <- data.frame("Test" = c("A Test", "B Test", "B Test", "C Test", "A Test", "B Test"),
                     "Comp Name" = c("Comp.1", "Comp.1", "Comp.2", "Comp.2", "Comp.3", "Comp.4"))

Table 2 #

Test.Name   Comp.1  Comp.2  Comp.3  Comp.4
A Test      5       6       7       8
B Test      10      15      20      25
C Test      2       8       3       212

对应的R定义代码：

table2 <- data.frame("Test Name" = c("A Test", "B Test", "C Test"),
                     "Comp.1" = c(5, 10, 2),
                     "Comp.2" = c(6, 15, 8),
                     "Comp.3" = c(7, 20, 3),
                     "Comp.4" = c(8, 25, 212))

目标结果 #

Test    Comp.Name   Test.Price
A Test  Comp.1      5
B Test  Comp.1      10
B Test  Comp.2      15
C Test  Comp.2      8
A Test  Comp.3      7
B Test  Comp.4      25

对应的目标数据框代码：

table3 <- data.frame("Test" = c("A Test", "B Test", "B Test", "C Test", "A Test", "B Test"),
                     "Comp Name" = c("Comp.1", "Comp.1", "Comp.2", "Comp.2", "Comp.3", "Comp.4"),
                     "Test Price" = c(5,10,15,8,7,25))

尝试的代码 #

table3 <- inner_join(table1, table2, by.x = "Test", by.y="Test Name") %>%
  select("Test Name", "Comp Name", "Test Price" = "Comp Name")

解决方案 #

问题核心是table2是宽格式，无法直接和长格式的table1匹配，需要先将table2转为长格式，再进行连接：

library(dplyr)
library(tidyr)

# 把table2转为长格式，将各公司列转为行
table2_long <- table2 %>%
  pivot_longer(cols = starts_with("Comp."), 
               names_to = "Comp Name", 
               values_to = "Test Price")

# 按Test和Comp Name两个字段内连接，得到目标结果
table3 <- inner_join(table1, table2_long, 
                     by = c("Test" = "Test Name", "Comp Name" = "Comp Name"))

代码说明 #

1. pivot_longer将table2中所有以Comp.开头的列（即各公司价格列）转换为两行：Comp Name存储公司名，Test Price存储对应价格，让table2的结构与table1对齐。
2. inner_join通过Test和Comp Name两个匹配条件，将两个数据框合并，直接得到目标格式的数据。

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