0

I have two data frames:

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

d1 <- data.frame(d1.Kids, d1.States)

d1

   d1.Kids d1.States
1    Jack        CA
2    Jill        MA
3 Jillian        DE
4    John        HI
5   James        PA

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

d2 <- data.frame(d2.Kids, d2.Ages)
d2
   d2.Kids d2.Ages
1    Jill      10
2 Jillian       7
3    Jack      12
4    Mary      30

when I merge these two data frames I get the result below:

merge(d1,d2)

The Result:

 d1.Kids d1.States d2.Kids d2.Ages
1     Jack        CA    Jill      10
2     Jill        MA    Jill      10
3  Jillian        DE    Jill      10
4     John        HI    Jill      10
5    James        PA    Jill      10
6     Jack        CA Jillian       7
7     Jill        MA Jillian       7
8  Jillian        DE Jillian       7
9     John        HI Jillian       7
10   James        PA Jillian       7
11    Jack        CA    Jack      12
12    Jill        MA    Jack      12
13 Jillian        DE    Jack      12
14    John        HI    Jack      12
15   James        PA    Jack      12
16    Jack        CA    Mary      30
17    Jill        MA    Mary      30
18 Jillian        DE    Mary      30
19    John        HI    Mary      30
20   James        PA    Mary      30

I want to get this result:

   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
sm925
  • 2,648
  • 1
  • 16
  • 28
joshuacos2777
  • 7
  • 5

1 Answers1

1

Without using by, it would do a cross join, we can avoid that with the the by option. As the column names are different in both columns, use the by.x, by.y and do a full join with all = TRUE

out <- merge(d1,d2, by.x = 'd1.Kids', by.y = 'd2.Kids', all = TRUE)

and change the names of 'out' by removing the prefix part

names(out) <- sub("^[^.]+\\.", "", names(out))
akrun
  • 874,273
  • 37
  • 540
  • 662
  • I want to merge this two data frames without merge functions by using union,match or %in% operator. – joshuacos2777 Jun 21 '19 at 17:15
  • @CoskunOlcucu But your code was using `merge` and question title is `How to merge two data frames in R by using merge with desired output?` – akrun Jun 21 '19 at 17:27