Is there a way to create Stata's _merge indicator variable with R's merge()?

Question

Stata automatically creates a variable called "_merge" indicating the matched variables in both datasets after merge. Is there a way to get such variable generated by R's merge() function?

Spirit

Related: #40111144 – Goldstein

Answer 1

The possible values of _merge in Stata are (note merge can also have values 4 and 5)

              1       master             observation appeared in master only
              2       using              observation appeared in using only
              3       match              observation appeared in both

In R, you can do that by entering the argument as either all=TRUE or all.x=TRUE or all.y=TRUE

e.g.,

merge(x, y, by = intersect(names(x), names(y)),by.x = by, by.y = by, all = TRUE)
 merge(x, y, by = intersect(names(x), names(y)),by.x = by, by.y = by, all.x = TRUE)
 merge(x, y, by = intersect(names(x), names(y)),by.x = by, by.y = by, all.y = TRUE)

Trawler

Thanks for your reply. This is quite laborious. I would like to have the same _merge variable created after using merge() and just apply summary to it. – Spirit

Well, R is not Stata. – Trawler

Answer 2

I have written the following function based on @Metrics answer. It creates a variable "merge" in the resulting dataset that indicates observations as Stata does.

stata.merge <- function(x,y, by = intersect(names(x), names(y))){

x[is.na(x)] <- Inf
y[is.na(y)] <- Inf

matched <- merge(x, y, by.x = by, by.y = by, all = TRUE)
matched <- matched[complete.cases(matched),]
matched$merge <- "matched"
master <- merge(x, y, by.x = by, by.y = by, all.x = TRUE)
master <- master[!complete.cases(master),]
master$merge <- "master"
using <- merge(x, y, by.x = by, by.y = by, all.y = TRUE)
using <- using[!complete.cases(using),]
using$merge <- "using"

df <- rbind(matched, master,using)
df[sapply(df, is.infinite)] <- NA
df
}

Test.

df1 <- data.frame(id = letters[c(1:5,8:9)], v1=c(1:5,8:9))
df1

   id v1
1  a  1
2  b  2
3  c  3
4  d  4
5  e  5
6  h  8
7  i  9

df2 <- data.frame(id = letters[1:8], v1=c(1:7,NA))
df2

  id v1
1  a  1
2  b  2
3  c  3
4  d  4
5  e  5
6  f  6
7  g  7
8  h NA

stata.merge(df1,df2, by = "id")

   id v1.x v1.y   merge
1   a    1    1 matched
2   b    2    2 matched
3   c    3    3 matched
4   d    4    4 matched
5   e    5    5 matched
6   h    8   NA matched
7   i    9   NA  master
71  f   NA    6   using
8   g   NA    7   using

Spirit

Answer 3

Here is (I think) a far simpler and more efficient version of the previous person's stata.merge function. This assumes you don't have variables named "new1" or "new2" in your data frames. If this assumption is wrong, change the variable names in this function. This function takes 3 variables, the first data frame, the second data frame, and the value to enter into the "by =" part of the merge function.

stata.merge <- function(x,y, name){
  x$new1 <- 1
  y$new2 <- 2
  df <- merge(x,y, by = name, all = TRUE)
  df$stat.merge.variable <- rowSums(df[,c("new1", "new2")], na.rm=TRUE)
  df$new1 <- NULL
  df$new2<- NULL
  df
}

Olwen

As I read your code, you do assume names new1 new2 but explain that can be changed. SImple edit implied.: don't to do. – Restrain