# Merging Data Frames¶

Let's learn how to merge Data Frames together (you'll use this in your Final Data Frame Project!)

In [3]:

    ## use character columns of names to get sensible sort order
    authors <- data.frame(
        surname = I(c("Tukey", "Venables", "Tierney", "Ripley", "McNeil")),
        nationality = c("US", "Australia", "US", "UK", "Australia"),
        deceased = c("yes", rep("no", 4)))

In [4]:

    books <- data.frame(
        name = I(c("Tukey", "Venables", "Tierney",
                 "Ripley", "Ripley", "McNeil", "R Core")),
        title = c("Exploratory Data Analysis",
                  "Modern Applied Statistics ...",
                  "LISP-STAT",
                  "Spatial Statistics", "Stochastic Simulation",
                  "Interactive Data Analysis",
                  "An Introduction to R"),
        other.author = c(NA, "Ripley", NA, NA, NA, NA,
                         "Venables & Smith"))

In [6]:

    authors

Out[6]:

| surname | nationality | deceased
--- | --- | --- | ---
1 | Tukey | US | yes
2 | Venables | Australia | no
3 | Tierney | US | no
4 | Ripley | UK | no
5 | McNeil | Australia | no

In [ ]:

In [ ]:

    (m1 <- merge(authors, books, by.x = "surname", by.y = "name"))

In [1]:

    (m2 <- merge(books, authors, by.x = "name", by.y = "surname"))
    stopifnot(as.character(m1[, 1]) == as.character(m2[, 1]),
              all.equal(m1[, -1], m2[, -1][ names(m1)[-1] ]),
              dim(merge(m1, m2, by = integer(0))) == c(36, 10))

    ## "R core" is missing from authors and appears only here :
    merge(authors, books, by.x = "surname", by.y = "name", all = TRUE)

    ## example of using 'incomparables'
    x <- data.frame(k1 = c(NA,NA,3,4,5), k2 = c(1,NA,NA,4,5), data = 1:5)
    y <- data.frame(k1 = c(NA,2,NA,4,5), k2 = c(NA,NA,3,4,5), data = 1:5)
    merge(x, y, by = c("k1","k2")) # NA's match
    merge(x, y, by = "k1") # NA's match, so 6 rows
    merge(x, y, by = "k2", incomparables = NA) # 2 rows

Out[1]:

| surname | nationality | deceased | title | other.author
--- | --- | --- | --- | --- | ---
1 | McNeil | Australia | no | Interactive Data Analysis | NA
2 | Ripley | UK | no | Spatial Statistics | NA
3 | Ripley | UK | no | Stochastic Simulation | NA
4 | Tierney | US | no | LISP-STAT | NA
5 | Tukey | US | yes | Exploratory Data Analysis | NA
6 | Venables | Australia | no | Modern Applied Statistics ... | Ripley

Out[1]:

| name | title | other.author | nationality | deceased
--- | --- | --- | --- | --- | ---
1 | McNeil | Interactive Data Analysis | NA | Australia | no
2 | Ripley | Spatial Statistics | NA | UK | no
3 | Ripley | Stochastic Simulation | NA | UK | no
4 | Tierney | LISP-STAT | NA | US | no
5 | Tukey | Exploratory Data Analysis | NA | US | yes
6 | Venables | Modern Applied Statistics ... | Ripley | Australia | no

Out[1]:

| surname | nationality | deceased | title | other.author
--- | --- | --- | --- | --- | ---
1 | McNeil | Australia | no | Interactive Data Analysis | NA
2 | R Core | NA | NA | An Introduction to R | Venables & Smith
3 | Ripley | UK | no | Spatial Statistics | NA
4 | Ripley | UK | no | Stochastic Simulation | NA
5 | Tierney | US | no | LISP-STAT | NA
6 | Tukey | US | yes | Exploratory Data Analysis | NA
7 | Venables | Australia | no | Modern Applied Statistics ... | Ripley

Out[1]:

| k1 | k2 | data.x | data.y
--- | --- | --- | --- | ---
1 | 4 | 4 | 4 | 4
2 | 5 | 5 | 5 | 5
3 | NA | NA | 2 | 1

Out[1]:

| k1 | k2.x | data.x | k2.y | data.y
--- | --- | --- | --- | --- | ---
1 | 4 | 4 | 4 | 4 | 4
2 | 5 | 5 | 5 | 5 | 5
3 | NA | 1 | 1 | NA | 1
4 | NA | 1 | 1 | 3 | 3
5 | NA | NA | 2 | NA | 1
6 | NA | NA | 2 | 3 | 3

Out[1]:

| k2 | k1.x | data.x | k1.y | data.y
--- | --- | --- | --- | --- | ---
1 | 4 | 4 | 4 | 4 | 4
2 | 5 | 5 | 5 | 5 | 5

In [ ]: