Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to merge tables in R?

Tags:

merge

r

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.

like image 597
JPD Avatar asked Oct 15 '12 13:10

JPD


People also ask

How do you combine tables in R studio?

In R we use merge() function to merge two dataframes in R. This function is present inside join() function of dplyr package. The most important condition for joining two dataframes is that the column type should be the same on which the merging happens.

How do I link two tables in R?

We can merge two data frames in R by using the merge() function or by using family of join() function in dplyr package. The data frames must have same column names on which the merging happens. Merge() Function in R is similar to database join operation in SQL.

How do I merge in R?

Using cbind() to merge two R data frames We will start with the cbind() R function . This a simple way to join multiple datasets in R where the rows are in the same order and the number of records are the same.


5 Answers

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
like image 108
Sven Hohenstein Avatar answered Oct 04 '22 21:10

Sven Hohenstein


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
like image 23
nograpes Avatar answered Oct 04 '22 19:10

nograpes


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
like image 23
johannes Avatar answered Oct 04 '22 20:10

johannes


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.

like image 40
verbamour Avatar answered Oct 04 '22 21:10

verbamour


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

like image 28
smoe Avatar answered Oct 04 '22 19:10

smoe