Data Analysis

Merge

Joining Tables

merge combines rows from two data frames using a shared key column.

Program

Play the script to attach prices to orders and add the prices.

merge_join.R
Replay: real traced execution (multi-file project)
orders <- data.frame(id = c(1, 2), item = c("book", "pen"))
prices <- data.frame(item = c("book", "pen"), price = c(12, 3))
joined <- merge(orders, prices, by = "item")
total <- sum(joined$price)
cat(total, "\n", sep = "")

  1. orders ← 2 rows x 2 cols

    1orders <- data.frame(id = c(1, 2), item = c("book", "pen"))2prices <- data.frame(item = c("book", "pen"), price = c(12, 3))
    values this step2 rows x 2 colsorders

  2. prices ← 2 rows x 2 cols

    1orders <- data.frame(id = c(1, 2), item = c("book", "pen"))2prices <- data.frame(item = c("book", "pen"), price = c(12, 3))3joined <- merge(orders, prices, by = "item")
    values this step2 rows x 2 colsprices

  3. joined ← 2 rows x 3 cols

    2prices <- data.frame(item = c("book", "pen"), price = c(12, 3))3joined <- merge(orders, prices, by = "item")4total <- sum(joined$price)
    values this step2 rows x 3 colsjoined2 rows x 2 colsorders2 rows x 2 colsprices

  4. total ← 15

    3joined <- merge(orders, prices, by = "item")4total <- sum(joined$price)5cat(total, "\n", sep = "")
    values this step15total12, 3joined$price

  5. cat(total, " ", sep = "")

    4total <- sum(joined$price)5cat(total, "\n", sep = "")
    output15
    values this step15total

Follow the Join

  1. orders has item rows (1, book) and (2, pen).
  2. prices has price rows (book, 12) and (pen, 3).
  3. merge(..., by = "item") matches rows with the same item.
  4. The joined rows are (book, 1, 12) and (pen, 2, 3).
  5. The prices add to 15, so the script prints 15. | item | order id | price | included in total | | --- | --- | --- | --- | | book | 1 | 12 | yes | | pen | 2 | 3 | yes | | total | - | 15 | printed |
merge `merge` joins data frames by matching key values.
key column `by = "item"` names the column shared by both tables.
joined data The joined table contains columns from both inputs.

Exercise: merge_join.R

Reproduce the output 15, then trace how book price 12 and pen price 3 join to the two orders.