Joining Data Frames - R Programming

1. Which keys appear in the joined result?
   Answer: Keys NA and 2, because na_matches = 'na' is the default, so two missing keys are treated as equal and matched. When working with joins in dplyr, one of the most important (and easily overlooked) details is how NA values in key columns are handled. The default behavior changed in modern dplyr: na_matches = 'na' is now the default, meaning two NA values in a key column are treated as equal and will match each other. Here, x has id = c(1, NA, 2) and y has id = c(NA, 2, 3). An inner join keeps only rows where the key exists in both tables. Walking through each value: 1 appears only in x, so it's dropped. NA appears in both x and y, and under na_matches = 'na' those two NAs match. 2 appears in both, so it matches normally. 3 appears only in y, so it's dropped. The result contains keys NA and 2 — making D correct. A is wrong because it inverts the default: na_matches = 'never' is not the default in current dplyr. You'd have to explicitly set that option to suppress NA matching. B is wrong because the presence of NA doesn't poison the entire join — only rows without a match are excluded, and NA–NA does match here. C describes a left_join, not an inner join; a left join retains all rows from the left table regardless of match status, but an inner join does not. A good study habit: always distinguish join type (inner vs. left vs. full) from join NA behavior (na_matches) — exam questions often conflate the two to create traps like A and C here.
2. Which description of the resulting data frame is correct?
   Answer: It has $4$ rows; o2 appears twice, and o3 has a missing value for tier. When working with joins in R, the key concept to internalize is how duplicate keys behave. A left join keeps every row from the left table and matches it against the right table — but if the right table has multiple rows sharing the same key, the left row gets duplicated once per match, expanding the result. Here, orders has three rows, and customers has customer_id = c(1, 2, 2) — meaning customer 2 appears twice. When left_join matches o2 (which has customer_id = 2) against customers, it finds two matching rows (gold and platinum), so o2 is duplicated: one row gets gold, the other gets platinum. Meanwhile, o3 has customer_id = 3, which doesn't exist in customers at all, so it remains in the result with NA for tier. The final data frame has $3 + 1 = 4$ rows total — confirming B is correct. A is wrong because it claims the second match for customer 2 is silently ignored. R does not drop duplicate matches; it expands the output. C is wrong because a duplicated key in the right table does not produce NA — it produces extra rows. NA appears only when there is no match, as with o3. D is wrong because it claims o3 is omitted, which would be the behavior of an inner_join, not a left_join. A left join always retains every row from the left table, matched or not. A useful rule of thumb: in any join, if the right table has $n$ rows matching a given key, the left table's matching row is repeated $n$ times. Always inspect both tables for duplicates before joining.
3. Which description correctly identifies both the row count and the status columns?
   Answer: The result has $3$ rows and columns status.x and status.y because only id is the key. When working with dplyr joins, you need to track two things independently: how rows multiply and how column naming conflicts resolve. These are separate mechanics that questions love to combine into one trap. For row count, left_join keeps every row from the left table (x) and expands them based on matches in the right table (y). Row id = 1 in x has no match in y, so it contributes exactly one row (with NA values from y). Row id = 2 in x matches two rows in y — (2, 'pending') and (2, 'closed') — so it expands into two rows. That gives you $1 + 2 = 3$ rows total. For the column conflict: both tables have a status column, but since status wasn't listed in by, dplyr automatically renames them status.x (from the left table) and status.y (from the right table). This confirms B is correct. A is wrong because dplyr does not automatically merge same-named columns — it suffixes them to avoid silent data loss. C is wrong on both counts: the row count is $3$, not stated correctly, and dplyr never silently overwrites one column with another. D is the trickiest distractor — id = 3 exists only in y (the right table), and because this is a left join, unmatched right-side rows are dropped entirely. That's why the count stays at $3$, not $4$. A good rule of thumb: in a left join, only unmatched left rows survive (as NA-padded rows); unmatched right rows disappear. Always count expansions from many-to-one matches separately.
4. How do the outputs differ?
   Answer: p1 contains only id 2, while p2 contains ids 1 and 2, including the matched missing score. When working with join types and filtering in R, the key is understanding what each join keeps before any filtering happens — and that NA values are not the same as unmatched rows. A left_join keeps all rows from the left table (x), filling in NA for any columns from y that don't have a match. Here, x has ids 1, 2, and 3. After joining, id 1 gets score = NA (matched but missing), id 2 gets score = 8, and id 3 gets score = NA (unmatched, no row in y). Then filter(!is.na(score)) removes both id 1 and id 3, leaving only id 2. That makes p1 contain just id 2. An inner_join keeps only rows where a match exists in both tables. Since id 3 has no corresponding row in y, it's dropped. But ids 1 and 2 both appear in y, so both are retained — including id 1 with its NA score. inner_join does not filter out NA values; it only filters out unmatched rows. So p2 contains ids 1 and 2, making D the correct answer. Choice A is wrong because p1 loses id 1 due to the NA score filter, not just unmatched rows. Choice B has the outputs backwards and incorrectly claims p2 includes id 3. Choice C gets p1 right but misattributes inner_join's behavior — it removes unmatched rows, not NA values. Remember: inner_join ≠ "remove NAs." It removes rows with no match in the other table. NA values from matched rows survive.
5. Ignoring any many-to-many relationship warning, how many rows are returned?
   Answer: $5$ rows, because the A matches form four combinations and B forms one. When working with joins in R, the critical concept is understanding how many-to-many matches multiply rows. An inner_join returns every combination of matching rows between the two tables — not just one row per key value, and not one row per input row. Here's how to think through the problem systematically. First, identify which keys appear in both x and y. Key 'A' appears in both, key 'B' appears in both, key 'C' appears only in x, and key 'D' appears only in y. Since inner_join keeps only shared keys, 'C' and 'D' rows are dropped entirely. Now count the combinations: x has two 'A' rows and y has two 'A' rows, producing $2 \times 2 = 4$ joined rows. x has one 'B' row and y has one 'B' row, producing $1 \times 1 = 1$ joined row. The total is $4 + 1 = 5$ rows, confirming C is correct. Choice A is wrong because it conflates distinct key values (A, B) with row count — joining produces combinations, not a count of unique keys. Choice B incorrectly assumes each row in x contributes exactly one output row, which is only true when every match is one-to-one; two 'A' rows in x each match two rows in y, so each contributes two joined rows. Choice D overstates the count by implying both copies of 'A' are "counted twice in each input," arriving at 6 through faulty logic — the correct cross-product is $2 \times 2 = 4$, not 6. When you see join questions, always sketch a small cross-product table for repeated keys — that habit will prevent you from falling for the one-to-one assumption trap.
6. What happens under the default common-column matching behavior?
   Answer: The result has $2$ rows, and member 2 receives NA for plan because both id and site are join keys. When you call left_join() in dplyr without specifying by, it automatically detects all column names that appear in both data frames and uses them jointly as the join key. This is the critical concept being tested here. In this scenario, both members and plans share two columns: id and site. Because dplyr uses all common columns by default, the join key is the combination (id, site) — not just id alone. Now consider member 2: in members, row 2 has (id=2, site='east'), but in plans, row 2 has (id=2, site='west'). Since (2, 'east') never matches (2, 'west') on both keys simultaneously, member 2 finds no matching row in plans. A left join preserves all rows from the left table, so member 2 remains in the result but receives NA for plan. That gives you exactly $2$ rows — confirming A is correct. Choice B is wrong because it assumes only id is used as the key. If that were true, member 2 would match plan='standard', but dplyr doesn't silently drop shared columns from the join criteria. Choice C is wrong because a left join never drops rows from the left table — it cannot behave like an inner join regardless of how many join keys exist. Choice D is wrong because no row duplication occurs; a left join doesn't "split" rows based on conflicting values in non-key columns, since here site is itself part of the key. As a study habit, always inspect your data frames for all shared column names before relying on the default by behavior — unexpected shared columns are a common source of join bugs.
7. What value is assigned to total?
   Answer: $65$, because both p1 sales are repeated once for each matching tag. Whenever you see a join followed by an aggregation in R, your first instinct should be to ask: does this join change the number of rows before I summarise? This is the core trap here. A left_join matches every row in the left table to every matching row in the right table. Since tags has two rows for p1, each p1 sale gets duplicated — once per matching tag. That means the sale of $10 appears twice and the sale of $20 appears twice. The p2 sale of $5 has one matching tag row, so it appears once. After the join, sum(amount) computes $10 + 10 + 20 + 20 + 5 = 65$, making C correct. A is wrong because it assumes the join is "safe" and preserves the original row structure. It isn't — one-to-many joins inflate rows, and the original sum of $10 + 20 + 5 = 35$ is destroyed by that inflation. B is wrong because it only accounts for one duplication of the p1 rows rather than both. Getting $45$ would require only one p1 sale to be repeated, but both sales match both tags, so both are duplicated. D is wrong in its logic that p2 also gets duplicated. p2 has only one tag row, so its amount of $5 appears exactly once — no duplication occurs there. The key study tip: always sketch out what the joined table looks like before applying any summarise. Row multiplication from one-to-many joins silently inflates aggregations — a very common source of bugs in real data pipelines.
8. How many rows are in the final result?
   Answer: $3$ rows, because p1 contributes two rows and p3 contributes one row. When chaining joins in R, you need to track row counts step by step — each join transforms the intermediate result, which becomes the input for the next join. Start with tasks, which has one row each for p1, p2, and p3. The left_join(rates, by = 'project') keeps all rows from tasks and expands matches: p1 matches two rows in rates (producing two rows), p2 matches one row (producing one row), and p3 has no match in rates (producing one row with NA values). The intermediate result has $2 + 1 + 1 = 4$ rows. Now apply inner_join(active, by = 'project') to that 4-row intermediate. active contains only p1 and p3, so p2's row is dropped. The two p1 rows both match active's single p1 row, and p3's row matches active's single p3 row. The final count is $2 + 1 = 3$ rows, confirming B. Choice A is tempting but wrong — it assumes the inner join collapses p1 back to one row, ignoring that both p1 rows survive because they each satisfy the match condition. Choice C correctly identifies the intermediate count of four rows but incorrectly assumes the inner join preserves all of them; p2 is dropped since it's absent from active. Choice D reflects a fundamental misunderstanding — joins filter and expand rows based on matches, they do not add row counts from separate joins together. The key strategy: always simulate joins sequentially. Calculate the row count after each join before moving to the next, and remember that an inner join filters rows not present in the right-hand table.
9. Which expression produces the required result?
   Answer: left_join(enrollment, roster, by = c('student_id' = 'id')), which preserves every enrollment row. When joining two tables in R, the most important question to ask is: which table's rows must all be kept? A left_join(x, y) preserves every row from x, filling in NA for any unmatched columns from y. An inner_join keeps only rows that match in both tables, dropping unmatched rows entirely. Here, the requirement is to retain every enrollment row — including student 103, who has no name in roster — so a left_join with enrollment as the left table is the right tool. The by = c('student_id' = 'id') argument tells R to match enrollment's student_id column against roster's id column. Students 101 and 102 get their names attached, and student 103 gets NA for the name column. That makes C the correct answer. A uses inner_join, which would silently drop student 103 because no matching name exists in roster — violating the requirement to keep every enrollment row. B swaps the table order, placing roster on the left. This preserves every roster row, not every enrollment row. Student 103 would be lost entirely, and the semantic intent is reversed. D uses the correct join type (left_join) but passes the wrong by argument — c('id' = 'student_id') — which tries to look up a column named id in enrollment, a column that doesn't exist there, causing an error. A quick memory rule: in left_join(x, y), think "x is protected." Whichever table you need to keep complete goes first.
10. Which operation and row count satisfy the requirement?
    Answer: left_join(accounts, payments, by = 'account'), producing $4$ rows including unmatched account B. When working with joins in R, the two key questions to ask are: which rows survive the join? and how does a one-to-many relationship affect row count? A left_join(x, y) keeps every row from the left table (x), even when no match exists in y. When a left-table row matches multiple right-table rows, it expands into multiple result rows — one per match. Here, accounts has three rows (A, B, C). Account A matches two payment rows, account C matches one, and account B matches none. That gives $2 + 1 + 1 = 4$ rows total, with account B appearing once with NA values for the payment columns. This is exactly what answer A describes, satisfying both requirements: retaining unmatched accounts and preserving every individual payment. Answer B is wrong because it claims left_join produces only $3$ rows — one per account. It ignores that account A's two payment records expand the result to four rows. This is a common misconception: joins don't automatically collapse matches. Answer C is wrong on two counts: inner_join drops account B entirely (only matched rows survive), and the row count of $3$ is still incorrect for the same expansion reason — A's two payments would produce $2 + 1 = 3$ matched rows, but the unmatched B row is lost, violating the requirement. Answer D is wrong because swapping the table order in inner_join still excludes unmatched accounts; no inner_join variant preserves unmatched rows. Study tip: Memorize that left_join = "keep all left rows, fill unmatched with NA," and that one-to-many matches multiply rows — always trace through row counts manually when the data has unequal matches.

Start this quiz