How to merge tables in R?

Question

I think this will have a simple answer, but I can't work it out! Here is an example using the iris dataset:

a <- table(iris[,2])
b <- table(iris[,3])

How do I add these two tables together? For example, the variable 3 would have a value of 27 (26+1) and variable 3.3 a value of 8 (6+2) in the new output table.

Any help much appreciated.

Sven Hohenstein · Accepted Answer · 2012-10-18T11:20:25.563

9

This will work if you want to use the variables which are present in both a and b:

n <- intersect(names(a), names(b))
a[n] + b[n]

#  3 3.3 3.5 3.6 3.7 3.8 3.9   4 4.1 4.2 4.4 
# 27   8   8   5   4   7   5   6   4   5   5

If you want to use all variables:

n <- intersect(names(a), names(b)) 

res <- c(a[!(names(a) %in% n)], b[!(names(b) %in% n)], a[n] + b[n])

res[order(names(res))] # sort the results

edited Oct 18 '12 at 11:20

answered Oct 15 '12 at 14:06

Sven Hohenstein

80,497
17
145
168

Thanks for this. Can it be modified to keep the values that aren't present in both datasets and only present in one though? I need a total that incorporates **all** data including the merged common values as your code has shown. – JPD Oct 15 '12 at 15:28
@SvenHohenstein thanks for the update. Using the `iris` example provided, `[-n]` appears to be invalid. – JPD Oct 18 '12 at 09:14
@SvenHohenstein That's one step closer! However, 3.8 for example occurs 3 times in the resulting table. Ideally the final table would capture only the sum of the 2 values and not the originals. Any way to keep just the combined value and remove the others? Thanks again for your help. – JPD Oct 18 '12 at 11:18

score 3 · Answer 2 · answered Oct 15 '12 at 14:07

temp<-merge(a,b,by='Var1')
temp$sum<-temp$Freq.x + temp$Freq.y

   Var1 Freq.x Freq.y sum
1     3     26      1  27
2   3.3      6      2   8
3   3.5      6      2   8
4   3.6      4      1   5
5   3.7      3      1   4
6   3.8      6      1   7
7   3.9      2      3   5
8     4      1      5   6
9   4.1      1      3   4
10  4.2      1      4   5
11  4.4      1      4   5

score 3 · Answer 3 · answered Oct 15 '12 at 14:09

Here is another one:

transform(merge(a,b, by="Var1"), sum=Freq.x + Freq.y)
   Var1 Freq.x Freq.y sum
1     3     26      1  27
2   3.3      6      2   8
3   3.5      6      2   8
4   3.6      4      1   5
5   3.7      3      1   4
6   3.8      6      1   7
7   3.9      2      3   5
8     4      1      5   6
9   4.1      1      3   4
10  4.2      1      4   5
11  4.4      1      4   5

score 2 · Answer 4 · answered Feb 19 '15 at 00:20

Here's a slightly tortured one-liner version of the merge() solution:

do.call(function(Var1, Freq.x, Freq.y) data.frame(Var1=Var1, Freq=rowSums(cbind(Freq.x, Freq.y))), merge(a, b, by="Var1"))

Here's the one if you want to use all variables:

do.call(function(Var1, Freq.x, Freq.y) data.frame(Var1=Var1, Freq=rowSums(cbind(Freq.x, Freq.y), na.rm=TRUE)), merge(a, b, by="Var1", all=TRUE))

Unlike the transform() one-liner, it doesn't accumulate .x and .y so it can be used iteratively.

score 1 · Answer 5 · answered Jul 24 '19 at 19:21

1

The merge function of the data.table package may be what you want: https://rpubs.com/ronasta/join_data_tables

answered Jul 24 '19 at 19:21

smoe

500
3
13

How to merge tables in R?

5 Answers5

Linked