Simple R merge method and how to compare it with T-SQL

Merge statement in R language is a powerful, simple, straightforward method for joining data frames. Nevertheless, it also serves with some neat features that give R users fast data wrangling.

I will be comparing this feature with T-SQL language, to show the simplicity of the merge method.

Creating data.frames and tables

We will create two tables using T-SQL and populate each table with some sample rows.

CREATE TABLE dbo.Users (
     UserID INT
    ,GroupID CHAR(1)
    ,DateCreated datetime
    ,TotalKM INT
    ,Age INT )

CREATE TABLE dbo.UserRun (
     UserID INT
    ,GroupID CHAR(1)
    ,Run INT
    ,RunName VARCHAR(50) )

INSERT INTO dbo.Users
          SELECT 1, 'X','2022/04/10',22,34
UNION ALL SELECT 1, 'X','2022/04/11',33,34
UNION ALL SELECT 2, 'Y','2022/04/13',33,41
UNION ALL SELECT 2, 'Y','2022/04/14',42,41
UNION ALL SELECT 3, 'X','2022/04/11',6,18
UNION ALL SELECT 4, 'Z','2022/04/13',8,56

INSERT INTO dbo.UserRun
          SELECT 1, 'X',1,'Short'
UNION ALL SELECT 1, 'X',0,'Over Hill'
UNION ALL SELECT 2, 'Y',0,'Short'
UNION ALL SELECT 2, 'Y',0,'Miller'
UNION ALL SELECT 3, 'X',0,'River'
UNION ALL SELECT 4, 'Z',0,'Mountain top'
UNION ALL SELECT 5, 'T',0,'City'

And create data.frames with the same data for R language:

Users = data.frame(UserID = c(1,1,2,2,3,4) 
                     ,GroupID = c("X","X","Y","Y","X","Z")
                     ,DateCreated = c("2022-04-10","2022-04-11","2022-04-13","2022-04-14","2022-04-11","2022-04-13")
                     ,TotalKM = c(22,33,33,42,6,8)
                     ,Age = c(34,34,41,41,18,56)
)

UserRun = data.frame(UserID = c(1,1,2,2,3,4,5)
                     ,GroupID = c("X","X","Y","Y","X","Z","T")
                     ,Run = c(1,0,0,0,0,0,0)
                     ,RunName = c("Short", "Over Hill","Short","Miller","River","Mountain top","City")
                     
)

Merge and T-SQL joins

Merging data is part of the daily data engineering task. Handling data and delivering results is crucial and the understanding Merge method is relevant for correct result delivery.

Simple T-SQL inner join:

SELECT  *
FROM dbo.Users as U 
JOIN dbo.UserRun AS UR
ON U.UserID = UR.UserID
AND U.GroupID = UR.GroupID

And on the other side, R Merge method for inner join:

IJ_Us_UsR <- merge(x=Users, y=UserRun, by = c("UserID", "GroupID"))
IJ_Us_UsR

gives same result.

Interoperable semi-joins

The order of SQL tables in the join clause is important for the join operation that determines the output of the query. The order of the tables in the join clause with semi-joins therefore must be carefully chosen. Taking the SQL query from above and changing the INNER to RIGHT returns another additional row:

SELECT *
FROM dbo.Users as U 
RIGHT JOIN dbo.UserRun AS UR
ON U.UserID = UR.UserID
AND U.GroupID = UR.GroupID

And the “same” result can be achieved by using LEFT JOIN with reversed table order:

SELECT *
FROM dbo.UserRun as U 
LEFT JOIN dbo.Users AS UR
ON U.UserID = UR.UserID
AND U.GroupID = UR.GroupID

In R Merge method, things are simplified when using semi- (or anti-) joins. But thus, extra caution must be considered.

We can achieve left join with:

# Left join all.x = TRUE
LJ_Us_UsR <- merge(x = Users, y = UserRun, by = c("UserID", "GroupID") , all.x = TRUE) 

but with switching all.x = TRUE to all.y = TRUE, we can get the right join:

#right join all.x = TRUE
RJ_Us_UsR <- merge(x = Users, y = UserRun, by = c("UserID", "GroupID") , all.y = TRUE) 

But the same goes, if we reverse the data.frame order and get the left join:

LJ_Us_UsR <- merge(x = UserRun, y = Users, by = c("UserID", "GroupID") , all.y = TRUE) 

But omitting the all.y = TRUE we can easily slip into inner join:

#right join all.x = FALSE becomes inner join
RJ_Us_UsR <- merge(x = Users, y = UserRun, by = c("UserID", "GroupID") , all.y = FALSE) 

And if you leave the defaults on, or ignore all attributes, you might again get the different result as expected! Even though the merge statement is short, simple, and powerful, you should be careful about the code.

Joins without unique value

Both data frames have one (or two) column(s) to define a unique user. But both columns UserID and GroupID are repeated in both data frames. And since there is no uniquely defined column(s), with repeated values in columns that are part of the ON clause, there will be a multiplication of rows.

Both data frames have UserID = 1 (from same groupID) inserted two times. When joining the data frames, we can specify how to join, but the uniqueness is still missing and we get the cartesian product of 2×2 = 4 rows in the final result for userID = 1.

Logically, we would want to get UsedID = 1 only two times. With T-SQL joins, we would need to create a windows function and select the unique ones (sort of unique value) and return the result. There are many other ways, but essentially, this is the database design question!

With R, the merge statement is powerful enough to do this by using row.names instead of defining the ON clause.

# Join by Row Names / Internal unieque value
RowNameJ_Us_UsR <- merge(x=Users, y=UserRun, by = "row.names")
RowNameJ_Us_UsR

Same result can be achieved by using defining the 0th column for join:

RowNameJ_Us_UsR <- merge(x=Users, y=UserRun, by = 0)  

If we define the by attribute with NULL instead of 0, we get the Cross-join!

CJ_Us_UsR <- merge(x = Users, y = UserRun, by = NULL )  
CJ_Us_UsR

Joins with than two data frames

Merge statement can be used also to join three or more data frames in R. Let’s create the third data frame:

# Joining more than two data.frames
Run = data.frame(UserID = c(1,1,2,2,3,4,6,8) 
                   ,GroupID = c("X","X","Y","Y","X","Z","H","K")
                   ,Trainer = c(1,1,1,1,1,0,0,0)
)

A merge statement is designed two work with two data. frames at once, but nesting the merge statements will bring the next data frame to the previous result set. So if you would be joining four data frames, you would end up with 3 (n-1) merge statements.

Three_IJ <- merge(merge(x=Users, y=UserRun, by = c("UserID", "GroupID")), y=Run, by.y = c("UserID", "GroupID"))
Three_IJ

As always, you can also use many other R packages to achieve the same result, e.g.: dplyr, data.table, tidyverse, sqldf just to name a few.

Code is available at Github in Useless – Useful R functions repository.

Happy R-Coding and Happy Easter 2022!

‹ Python and R have matrix and simple loops. What can you use in T-SQL?

Little useless-useful R functions – Animating datasets ›

Tagged with: data.frame, join, R, T-SQL, table
Posted in Uncategorized, Useless R functions