R语言中如何将两个不同维度的dataframe按行列名匹配后对应值相加

现有数据 #

第一个dataframe：passesComb，维度7×15 #

passesComb <- structure(list(P1_Good = c(0, 1, 0, 0, 0, 0, 1), P2_Good = c(2, 
0, 0, 0, 0, 0, 2), P3_Good = c(0, 1, 0, 0, 0, 0, 1), P4_Good = c(0, 
0, 1, 0, 0, 0, 1), P5_Good = c(0, 0, 0, 1, 0, 0, 1), P1_Bad = c(0, 
0, 0, 0, 0, 0, 0), P2_Bad = c(0, 0, 0, 0, 0, 0, 0), P3_Bad = c(0, 
0, 0, 0, 0, 0, 0), P4_Bad = c(0, 0, 1, 0, 0, 0, 1), P5_Bad = c(0, 
0, 0, 0, 0, 0, 0), `Bad Pass` = c(0, 0, 1, 0, 0, 1, 1), `Good Pass` = c(2, 
2, 1, 1, 0, 3, 6), `Intercepted Pass` = c(0, 0, 0, 0, 0, 1, 0
), Turnover = c(0, 0, 0, 0, 0, 1, 0), totalEvents = c(2, 2, 2, 
1, 0, 6, 7)), row.names = c("P1", "P2", "P3", "P4", "P5", "Opponent", 
"VT"), class = "data.frame")

第二个dataframe：copyComb，维度6×14 #

copyComb <- structure(list(P1_Good = c(0, 1, 0, 0, 0, 1), P2_Good = c(2, 
0, 0, 0, 0, 2), P4_Good = c(0, 0, 0, 0, 0, 0), P5_Good = c(0, 
0, 1, 0, 0, 1), P1_Bad = c(0, 0, 0, 0, 0, 0), P2_Bad = c(0, 0, 
0, 0, 0, 0), P3_Bad = c(0, 0, 0, 0, 0, 0), P4_Bad = c(0, 0, 0, 
0, 0, 0), P5_Bad = c(0, 0, 0, 0, 0, 0), `Bad Pass` = c(0, 0, 
0, 0, 1, 0), `Good Pass` = c(2, 1, 1, 0, 3, 4), `Intercepted Pass` = c(0, 
0, 0, 0, 0, 1, 0), Turnover = c(0, 0, 0, 0, 0, 1, 0), totalEvents = c(2, 
1, 1, 0, 6, 4)), row.names = c("P1", "P2", "P4", "P5", "Opponent", 
"VT"), class = "data.frame")

也可以通过以下代码从passesComb生成copyComb：

copyComb <- passesComb
copyComb <- copyComb[-3,-3]
#Updating specific cells since [3,3] is removed
copyComb[2,11] <- 1
copyComb[2,14] <- 1
copyComb[6,8] <- 0
copyComb[6,3] <- 0
copyComb[6,10] <- 0
copyComb[6,11] <- 4
copyComb[6,14] <- 4
#This now equals the copyComb from dput() above

需求 #

将两个dataframe按匹配的行名、列名对应单元格的数值相加，未匹配到的行/列保留原dataframe的数值即可。例如passesComb["P2","P1_Good"]值为1，copyComb["P2","P1_Good"]值为1，最终结果gamesComb对应位置的值应为2，所有行列名匹配的单元格都遵循该规则。

原有尝试的问题 #

之前编写的代码如下：

gamesComb <- data.frame(matrix(NA, nrow = ifelse(nrow(passesComb) >= nrow(copyComb), nrow(passesComb),nrow(copyComb)),
                               ncol = ifelse(ncol(passesComb) >= ncol(copyComb), ncol(passesComb),ncol(copyComb))))
                        
gamesComb[row.names(ifelse(nrow(passesComb) >= nrow(copyComb), passesComb, copyComb)),
                           colnames(ifelse(ncol(passesComb) >= ncol(copyComb), passesComb, copyComb))] <- passesComb

该代码仅创建了维度为7×15的gamesComb，但没有正确设置行名和列名，也没有实现两个dataframe数值相加的效果。

预期输出 #

expectedOutput <- structure(list(P1_Good = c(0, 2, 0, 0, 0, 0, 2), P2_Good = c(4, 
0, 0, 0, 0, 0, 4), P3_Good = c(0, 1, 0, 0, 0, 0, 1), P4_Good = c(0, 
0, 1, 0, 0, 0, 1), P5_Good = c(0, 0, 0, 2, 0, 0, 2), P1_Bad = c(0, 
0, 0, 0, 0, 0, 0), P2_Bad = c(0, 0, 0, 0, 0, 0, 0), P3_Bad = c(0, 
0, 0, 0, 0, 0, 0), P4_Bad = c(0, 0, 1, 0, 0, 0, 1), P5_Bad = c(0, 
0, 0, 0, 0, 0, 0), `Bad Pass` = c(0, 0, 1, 0, 0, 2, 1), `Good Pass` = c(4, 
3, 1, 2, 0, 6, 10), `Intercepted Pass` = c(0, 0, 0, 0, 0, 2, 
0), Turnover = c(0, 0, 0, 0, 0, 2, 0), totalEvents = c(4, 3, 
2, 2, 0, 12, 11)), row.names = c("P1", "P2", "P3", "P4", "P5", 
"Opponent", "VT"), class = "data.frame")

解决方案 #

通过取两个dataframe行名、列名的并集构建结果容器，再分别累加两个原始df的数值即可实现需求，代码如下：

# 提取两个df行名、列名的并集
all_rows <- union(rownames(passesComb), rownames(copyComb))
all_cols <- union(colnames(passesComb), colnames(copyComb))

# 初始化全0的结果df，指定行名列名
gamesComb <- data.frame(matrix(0, 
                               nrow = length(all_rows), 
                               ncol = length(all_cols),
                               dimnames = list(all_rows, all_cols)))

# 累加passesComb的数值
gamesComb[rownames(passesComb), colnames(passesComb)] <- gamesComb[rownames(passesComb), colnames(passesComb)] + as.matrix(passesComb)
# 累加copyComb的数值
gamesComb[rownames(copyComb), colnames(copyComb)] <- gamesComb[rownames(copyComb), colnames(copyComb)] + as.matrix(copyComb)

运行后执行all.equal(gamesComb, expectedOutput)可验证结果与预期完全一致。

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