使用不同大小的两个数据框进行相加

Question

使用不同大小的两个数据框进行相加

3

我想将数据框A和B相加，生成数据框C。如果A和B中有公共的ID和colname，则将它们的值相加。如果一个数据框中找不到ID和colname，则将其添加到最终的数据框C中。我尝试使用join(B,A,type="left")，但这不是我想要的。

 > A
     ID Pat1 Pat2 Pat3
1 Gene1    1    4    1
2 Gene2    2    0    9
3 Gene3    5    5    5
> B
     ID Pat1 Pat2 Pat3 Pat4
1 Gene1    3    9    1    1
2 Gene2    3    0    0    9
3 Gene3    5    5    5    5
4 Gene4    3    1    3    0
> C
     ID Pat1 Pat2 Pat3 Pat4
1 Gene1    4   13    2    1
2 Gene2    5    0    9    9
3 Gene3   10   10   10    5
4 Gene4    3    1    3    0

Dput:

> dput(A)
structure(list(ID = c("Gene1", "Gene2", "Gene3"), Pat1 = c(1, 
2, 5), Pat2 = c(4, 0, 5), Pat3 = c(1, 9, 5)), class = "data.frame", row.names = c(NA, 
-3L))
> dput(B)
structure(list(ID = c("Gene1", "Gene2", "Gene3", "Gene4"), Pat1 = c(3, 
3, 5, 3), Pat2 = c(9, 0, 5, 1), Pat3 = c(1, 0, 5, 3), Pat4 = c(1, 
9, 5, 0)), class = "data.frame", row.names = c(NA, -4L))
> dput(C)
structure(list(ID = c("Gene1", "Gene2", "Gene3", "Gene4"), Pat1 = c(4, 
5, 10, 3), Pat2 = c(13, 0, 10, 1), Pat3 = c(2, 9, 10, 3), Pat4 = c(1, 
9, 5, 0)), class = "data.frame", row.names = c(NA, -4L))

- user2300940

发现 cast 起作用了：mydf <- dcast(melt(mget(ls(pattern = "....")), id.vars = "ID"), ID ~ variable, value.var = "value", fun.aggregate = sum) - user2300940

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- benson23 · Accepted Answer

我将首先查找A和B之间缺失的列，然后使用NA填充该列。然后将两个数据框进行full_join，按其ID进行group_by，并在列中sum所有行。

library(tidyverse)

missing_cols <- setdiff(names(dfB), names(dfA))
  
dfA[missing_cols] <- NA
  
full_join(dfA, dfB) %>% 
  group_by(ID) %>% 
  summarize(across(where(is.numeric), ~ sum(.x, na.rm = T)), .groups = "drop")

# A tibble: 4 x 5
  ID     Pat1  Pat2  Pat3  Pat4
  <chr> <dbl> <dbl> <dbl> <dbl>
1 Gene1     4    13     2     1
2 Gene2     5     0     9     9
3 Gene3    10    10    10     5
4 Gene4     3     1     3     0