add two data frames together with unequal size

user_2300940 0
Viewed 28

I want add data frame A and B together to make data frame C. If a ID and colname is common between A and B, then I want to just add the two values together. If a ID and colname is not found in one of the data frames, then just add that to the final data frame C.

I tried join(B,A,type="left"), but it is not what I am after.

 > 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))
dataframe r
1 Answers

I'll first find out which column is missing between A and B, then fill the missing column with NA. Then full_join the two dataframes, group_by their ID, and sum all rows in the columns.

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
user_166474960
Related