Sum values from two tables with a condition in R

Question

I have two tables as follow:

a<-data.frame("Task"=c("A","B","C","D","E"),"FC"=c(100,NA,300,500,400),"FH"=c(NA,100,200,NA,300))
  Task  FC  FH
1    A 100  NA
2    B  NA 100
3    C 300 200
4    D 500  NA
5    E 400 300

b<-data.frame("Task"=c("A","B","C"),FC=c(10,20,30),FH=c(20,10,30))

  Task FC FH
1    A 10 20
2    B 20 10
3    C 30 30

I want the output with sum of the corresponding values from table a and table b but if the value is NA the output is NA The output is like this:

  Task  FC  FH
1    A 110  NA
2    B  NA 110
3    C 330 230

Relevant: [How to merge and sum two data frames](https://stackoverflow.com/questions/38468502/how-to-merge-and-sum-two-data-frames) and [How to merge two data frames on common columns in R with sum of others?](https://stackoverflow.com/questions/5769320/how-to-merge-two-data-frames-on-common-columns-in-r-with-sum-of-others) — markus, Jul 20 '19 at 20:51

tmfmnk · Accepted Answer · 2019-07-20T21:33:03.330

With base R, you can try:

data <- rbind(a[a$Task %in% b$Task, ], b)
aggregate(. ~ Task, sum, na.action = "na.pass", data = data)

  Task  FC  FH
1    A 110  NA
2    B  NA 110
3    C 330 230

Or the same with dplyr:

bind_rows(a[a$Task %in% b$Task, ], b) %>%
 group_by(Task) %>%
 summarise_all(sum)

  Task     FC    FH
  <chr> <dbl> <dbl>
1 A       110    NA
2 B        NA   110
3 C       330   230

Or to have it even more dplyr-like:

bind_rows(a, b, .id = "ID") %>%
 group_by(Task) %>%
 filter(n_distinct(ID) != 1) %>%
 select(-ID) %>%
 summarise_all(sum)

Sum values from two tables with a condition in R

1 Answers1