Trying to merge two data frames without using merge() and using union(), match() or %in%

user_10906504 0
Viewed 81

I want to construct two data frames and merge them without using any form of merge(). Instead I need to use set operations union() and match() or %in% operator. The following output must display the content of d1,d2 and the result of merging d1 and d2.

I have figured out how to do this with merge() but I cannot find out how to do it using union() and match() or %in% operator. Or any other way of doing this. Also my output doesn't match what the output should be. Im a beginner thanks for your help.

d1.Kids <- c("Jack", "Jill", "Jillian", "John", "James")
d1.States <- c("CA", "MA", "DE", "HI", "PA")

d1 <- data.frame(d1.Kids, d1.States, stringsAsFactors = FALSE)

d2.Ages <- c(10, 7, 12, 30)
d2.Kids <- c("Jill", "Jillian", "Jack", "Mary")

d2 <- data.frame(d2.Ages, d2.Kids, stringsAsFactors = FALSE)

# Merging two created data frame
merge <- merge(d1, d2, by.x = "d1.Kids", by.y = "d2.Kids", all = TRUE)

print(merge)

Output should be:

  kids    ages states 
1 Jack    12   CA
2 Jill    10   MA
3 Jillian 7    DE
4 John    NA   HI
5 James   NA   PA
6 Mary    30   NA
r
3 Answers

Something like this will do what the question asks for.
It seems long but in fact it's the same set of instructions for each of the dataframes to be merged.

Kids <- union(d1$d1.Kids, d2$d2.Kids)

States <- rep(NA_character_, length(Kids))
Ages <- rep(NA_real_, length(Kids))

States[match(d1$d1.Kids, Kids)] <- as.character(d1$d1.States)
Ages[match(d2$d2.Kids, Kids)] <- d2$d2.Ages

mrg <- data.frame(Kids, States, Ages)

mrg
#     Kids States Ages
#1    Jack     CA   12
#2    Jill     MA   10
#3 Jillian     DE    7
#4    John     HI   NA
#5   James     PA   NA
#6    Mary   <NA>   30
user_82454060

Using base R:

kids <- unique(c(d1$Kids, d2$Kids))
d3 <- data.frame("Kids" = kids, "ages" = NA, "states" = NA)
for (i in seq_along(kids)) {
if (any(d2$Kids == kids[i])) {
d3[which(d3$Kids == kids[i]),]$ages <- d2[which(d2$Kids == kids[i]),]$ages
} 
if (any(d1$Kids == kids[i])) {
d3[which(d1$Kids == kids[i]),]$states <- d1[which(d2$Kids == kids[i]),]$states
}
}
user_108035680

Here is another way using match and extractions.

nm <- c("kids", "ages", "states")
s1 <- na.omit(match(d1$d1.Kids, d2$d2.Kids))
s2 <- na.omit(match(d2$d2.Kids, d1$d1.Kids))
r1 <- setNames(data.frame(d1[s1, ], d2[s2, 1]), nm)
res <- if (!setequal(d1$d1.Kids, d2$d2.Kids)) {
  r2 <- setNames(data.frame(d1[-s1, ], NA), nm)
  r3 <- setNames(data.frame(d2[-s2, 2], NA, d2[-s2, 1]), nm)
  rbind(r1, r2, r3)
} else {
  r1
}
res
#       kids ages states
# 1     Jack   CA     10
# 2     Jill   MA      7
# 3  Jillian   DE     12
# 4     John   HI     NA
# 5    James   PA     NA
# 11    Mary <NA>     30
user_65740380
Related