1

I've 2 different data.tables. I need to merge and sum based on a row values. The examples of two tables are given as Input below and expected output shown below.

Input

Table 1

X   A   B
A   3   
B   4   6
C   5   
D   9   12

Table 2

X   A   B    
A   1   5
B   6   8
C   7   14
D   5   
E   1   1
F   2   3
G   5   6

Expected Output:

X   A   B
A   4   5
B   10  14
C   12  14
D   14  12
E   1   1
F   2   3
G   5   6
Sotos
  • 51,121
  • 6
  • 32
  • 66
Praveen Kumar
  • 107
  • 1
  • 7
  • 3
    You have two data.tables or data.frames? Also, if you have just empty strings there, your columns aren't of numeric type. – David Arenburg Nov 01 '17 at 12:42

3 Answers3

4

We can do this by rbinding the two tables and then do a group by sum

library(data.table)
rbindlist(list(df1, df2))[, lapply(.SD, sum, na.rm = TRUE), by = X]
#   X  A  B
#1: A  4  5
#2: B 10 14
#3: C 12 14
#4: D 14 12
#5: E  1  1
#6: F  2  3
#7: G  5  6

Or using a similar approach with dplyr

library(dplyr)
bind_rows(df1, df2) %>%
    group_by(X) %>%
    summarise_all(funs(sum(., na.rm = TRUE)))

Note: Here, we assume that the blanks are NA and the 'A' and 'B' columns are numeric/integer class

akrun
  • 874,273
  • 37
  • 540
  • 662
0

Merge your tables together first, then do the sum. If you later want to drop the individual values you can do so easily.

out <- merge(df1, df2, by.x="X", by.y="X", all.x=T, all.y=T)
out$sum <- rowSums(out[2:3])
out$A <- out$B <- NULL  # drop original values
cparmstrong
  • 799
  • 6
  • 23
0

Below code will help you to do required job for all numeric columns at once

library(dplyr)
Table = Table1 %>% full_join(Table2) %>% 
  group_by(X) %>% summarise_all(funs(sum(.,na.rm = T)))
Sai Prabhanjan Reddy
  • 536
  • 3
  • 16