How to Merge Tables Using R

This article describes how to merge two tables with differing dimensions into a combined R table. We include examples of merging by row and by column. The function that you use depends on what you ultimately want your final table to look like and how your original tables are structured. These requirements are outlined below.

• Method 1 - Merge Up-and-Down
• Method 2 - Merge Side-by-Side

Requirements

• Two tables to merge, showing only one statistic. If showing more than one statistic, you must merge each statistic separately.
• An R object - this can be an R variable, R data set, custom calculation, etc. See the different options in How to Use R in Displayr. You will be making edits in the R Code editor.

Method 1 - Merge Up-and-Down

In this example, we have the following two tables:

We wish to merge the first table below the second table and only keep the common columns:

1. rbind

Merges more than one table. This requires the input tables to have the same number of columns (you can subset within the function like below) and the same exact column names in the same order.

#identify name of first table
tab1 = table.Gender.by.Preferred.Cola
#identify name of second table
tab2 = table.Brand.Attitude
#merge only the first 6 columns of first table that match columns in second
rbind(tab1[,1:6], tab2)

2. flipTables::Rbind

Merges more than one table. Matches based on column name no matter what order, and can include non-matching columns (such as Dislike all cola and Don't care):

#identify name of first table
tab1 = table.Gender.by.Preferred.Cola
#identify name of second table
tab2 = table.Brand.Attitude
#merge keeping all columns
flipTables::Rbind(tab1, tab2)

By default, Rbind() will keep all non-matching columns, but you can prevent this by using the keep.all=F argument:

#merge keeping only matching columns
flipTables::Rbind(tab1 ,tab2 , keep.all=F)

Method 2 - Merge Side-by-Side

In this example, we have the following two tables:

We wish to merge the second table beside the first table and only keep the common rows:

1. cbind

Merges more than one table. This requires the input tables to have the same number of rows (you can subset within the function) and the same exact row names in the same order.

#identify name of first table
tab1 = table.Gender.by.Preferred.Cola
#identify name of second table
tab2 = table.Brand.Attitude
#merge in columns based on matching rows
cbind(tab1[1:6] ,tab2 )

2. Cbind

Merges more than one table. Matches based on row name no matter what order, and can include non-matching rows:

#identify name of first table
tab1 = table.Gender.by.Preferred.Cola
#identify name of second table
tab2 = table.Brand.Attitude
#merge in columns based on matching rows
flipTables::Cbind(tab1, tab2)

By default, this function will keep all non-matching rows, but you can prevent this by using the keep.all=F argument:

#merge in columns keeping all rows
flipTables::Cbind(tab1, tab2, keep.all=F)

3. data.frame

Merges more than one table. Must have the same row number and data should be in the same order (data.frame does not try to match row names or reorder anything). Use this when you want to combine tables with different data types (numeric, text, etc). 

#identify name of first table
tab1 = table.Gender.by.Preferred.Cola
#identify name of second table
tab2 = table.Brand.Attitude
#merge in columns using first 6 rows of tab1 to match tab2 rows
data.frame(Prefer=tab1[1:6], tab2, check.names=F)

The check.names=F argument is useful here as setting this to False will prevent R from automatically replacing spaces in the column names with dots.

4. merge

Merges only two tables. Can merge based on row names or one or more columns found in each table.:

#set tables as data.frames
tab1 = data.frame(Prefer=table.Preferred.Cola, check.names=F)
tab2 = data.frame(table.Brand.Attitude, check.names=F)
#merge based on the row names - only keeps matching rows see other options in article below
mytable = merge(tab1, tab2, by=0, all=F)
#[OPTIONAL: If you want to give your rows names instead of having them be numbers
#set the row names to the data in the first column
rownames(mytable) = mytable[,1]
#return final result without the first column as we've used that as the rownames
mytable = mytable[,-1]

The merge function offers the options of matching by:

• Row name: by=0
• Specifying a common column(s): by="Brands" or by=c("Brands","Models")
• Mapping different fields across the tables: by.x = "Brand", by.y = "Brands"

And joining by:

• Outer join - keep all rows of both tables (even if unmatching): all=T
• Left join - all rows in the first table (even if not in the second): all.x=T
• Right join - all rows in the second table (even if not in the first): all.y=T

Next

How to Sort Multiple Column Tables Using R

How to Remove a Row or Column from a Table Using R

How to Relabel Rows and Columns in an R Table

How to Use R in Displayr