Join, stack, and the named-vector lookup

◈ 10 cards

merge(lines, products, by = "sku") is the inner join — 4 rows, D400 dropped; all.x = TRUE keeps it with NA (5 rows, the VLOOKUP result); all = TRUE is the full join; by.x / by.y when the key names differ. rbind(jan, feb, mar) stacks 4 + 4 + 4 = 12 rows and needs identical column names. A named vector indexed by key — map[orders$prov] — is VLOOKUP exact match.

The join, in R

Module 6 priced the five order lines by looking each SKU up in products, and Lesson 6.6 named that operation a join on the key sku. merge() is the join. Both frames, and the key:

> merge(lines, products, by = "sku")
   sku line_id qty       product unit_price
1 A100       1   2 Ledger binder         24
2 A100       4   5 Ledger binder         24
3 B200       3   1     Desk lamp         60
4 C300       2   4  Office chair        180

Four rows. Line 5 — SKU D400, which is not in products — is gone: the default all = FALSE is an inner join, keeping only keys present on both sides. The VLOOKUP sheet kept that line and showed #N/A; to get that behaviour, keep everything from the left frame:

> merge(lines, products, by = "sku", all.x = TRUE)
   sku line_id qty       product unit_price
1 A100       1   2 Ledger binder         24
2 A100       4   5 Ledger binder         24
3 B200       3   1     Desk lamp         60
4 C300       2   4  Office chair        180
5 D400       5   3          <NA>         NA

all.x = TRUE is the left join — 5 rows, NA where the lookup failed. all.y = TRUE keeps unmatched products instead; all = TRUE keeps both sides (the full outer join, also 5 rows here). The key column moves to the front and the rows come back sorted by it — note line 4 printed before line 2.

Worked example — keys with different names

The products master calls its key product_sku. by needs one name; by.x and by.y take one each:

> merge(lines, products2, by.x = "sku", by.y = "product_sku")
   sku line_id qty       product unit_price
1 A100       1   2 Ledger binder         24
2 A100       4   5 Ledger binder         24
3 B200       3   1     Desk lamp         60
4 C300       2   4  Office chair        180

Omit by altogether and merge() joins on every column name the two frames share. With sku in both that is fine — nrow(merge(lines, products)) is 4. With no shared name it silently pairs every line with every product: nrow(merge(lines, products2)) is 15, five lines × three products, and nothing warns you. Name the key.

Stacking months

January, February and March arrive as three files with the same columns. rbind() — row bind — stacks them:

> all3 <- rbind(jan, feb, mar)
> nrow(all3)
[1] 12

Four rows each, twelve together. The requirement is identical column names — the same set, any order; rbind() matches by name. Rename one column in February and it refuses:

Error in match.names(clabs, names(xi)) : 
  names do not match previous names

cbind() is the other direction — columns side by side — and that one needs equal row counts. Stack months with rbind; add a column with cbind or, better, df$new <- ….

Recoding with a named vector

Module 5's dictionary maps ON to Ontario. A named vector is that two-column table, and indexing it by the codes is the lookup:

> map <- c(ON = "Ontario", QC = "Quebec", BC = "British Columbia", AB = "Alberta")
> map["ON"]
       ON 
"Ontario" 
> orders$prov_name <- map[orders$prov]
> head(orders$prov_name, 4)
[1] "Ontario"          "Quebec"           "Ontario"          "British Columbia"

Twelve codes in, twelve names out, in row order — VLOOKUP(B2, dictionary, 2, FALSE) filled down, with no $ anchors to forget. A code not in map (the "ONT" from L12.3) comes back NA, which is the #N/A — and the signal to fix the dictionary. unname() strips the carried-over names if they get in the way.

Type the three, then read the stacked row count

CRISP-DM: a join and a stack are data preparation → integrate data; a recode is format data.

The L6.6 join, now in R. merge(lines, products, by = "sku") pairs rows on sku; all.x = TRUE keeps the D400 line with NA.