Theory
Two tables, one question
The registrar finally shares the CGPA file: roll, cgpa, one row per student. Your survey holds roll, screen. The question everyone has been waiting to ask: does screen time relate to CGPA?
The two variables live in different data frames, joined only by their shared roll numbers.
You stitched tables in SQL (JOIN) and in pandas (pd.merge). R's needle is merge(), and your join instincts transfer completely: only the switch names change.
Theory
The clerk with two registers, again
Same clerk as BCA303: admissions register in one hand, marks register in the other, matching rows by roll number and stapling them.
The only decision that ever changes: what to do with a roll that has no match: skip the student (inner), keep them with blanks (left), keep everything from both books (full outer). merge() encodes that decision in two words: all.x, all.y.
Practical
The join family, R-spelled
survey <- data.frame(roll = c(101, 102, 103),
screen = c(120, 240, 90))
cgpa <- data.frame(roll = c(101, 103, 104),
cgpa = c(8.2, 6.9, 7.5))
# INNER (the default): only rolls present in BOTH
merge(survey, cgpa, by = "roll")
# roll screen cgpa -> 101 and 103 only
# LEFT: keep every SURVEY row; missing cgpa becomes NA
merge(survey, cgpa, by = "roll", all.x = TRUE)
# 102 keeps its screen, cgpa = NA
# FULL OUTER: keep everything from both
merge(survey, cgpa, by = "roll", all = TRUE)
# 102 (no cgpa) AND 104 (no survey) both appear
# different key names on the two sides:
# merge(survey, cgpa, by.x = "roll", by.y = "student_id")
# find the unmatched after a left join:
m <- merge(survey, cgpa, by = "roll", all.x = TRUE)
m[is.na(m$cgpa), ] # the surveyed-but-no-CGPA students
At a glance
merge() switches = the SQL join family
| SQL join | merge() call | Keeps |
|---|---|---|
| INNER | merge(x, y, by) | Only matched keys (default) |
| LEFT | all.x = TRUE | All of x; NAs fill gaps |
| RIGHT | all.y = TRUE | All of y |
| FULL OUTER | all = TRUE | Everything from both |
Quiz
survey has 60 rows; cgpa has 55 (five students have no CGPA yet). A student runs merge(survey, cgpa, by = "roll") and the report quietly covers 55 students. What happened, and what was the right call for "analyse ALL surveyed students"?
- The default merge is INNER: the five unmatched students vanished silently; all.x = TRUE (left join) keeps all 60 with NA cgpa
- merge() failed: different row counts cannot merge
- all = TRUE was needed to keep the survey rows only
- The five students were deleted from the CSV files
Show the answer
The default merge is INNER: the five unmatched students vanished silently; all.x = TRUE (left join) keeps all 60 with NA cgpa
merge()'s default is the inner join: unmatched rolls simply do not appear, with no warning: the exact silent-shrink you met with SQL's INNER JOIN. "Analyse all surveyed students" is the left-join sentence: all.x = TRUE, and the five appear with NA cgpa (then handled with your NA toolkit). all = TRUE would ADD the extra cgpa-only students instead. Habit that catches this instantly: nrow() before and after every merge.
Think first
Predict the row counts
survey: rolls 101, 102, 103. cgpa: rolls 101, 103, 104. Before tapping, give the row count of each: (1) inner merge, (2) all.x = TRUE, (3) all = TRUE. Then say which rolls carry an NA in version 3.
Show the answer
1. 2 rows (101, 103: present in both).
2. 3 rows (all survey rolls; 102's cgpa = NA).
3. 4 rows (101, 102, 103, 104): roll 102 has NA cgpa and roll 104 has NA screen: each side's orphan carries the other side's blank.
Inner ≤ left ≤ full: if your counts ever violate that ordering, a duplicated key is multiplying rows: the next trap to know.
Watch out
The two silent mergers
The inner default: unmatched rows vanish without a whisper: decide the join TYPE before typing, and nrow() before/after.
Duplicate keys multiply: if cgpa accidentally lists roll 101 twice, the merge pairs EACH copy: 60 rows become 61+ without any error. sum(duplicated(cgpa$roll)) before merging is the ten-second insurance.
Theory
The analysis this enables
With screen and cgpa finally in one data frame, the questions of Units 1-2 come alive on real pairs: a scatter plot of screen vs cgpa (correlation by eye), group means via the summary commands next lesson, cross-tabs of usage band against grade band. Every serious analysis begins with a merge: data about the same people always arrives in separate files.
Summary
Key takeaways
- merge(x, y, by = "key") joins on a shared column; DEFAULT is INNER (matched keys only).
- all.x = TRUE = left join (all of x, NA gaps); all.y = right; all = TRUE = full outer.
- Different key names: by.x / by.y; multiple keys: by = c(...).
- Left-join NAs mark the unmatched: find them with is.na().
- nrow() before/after every merge; duplicate keys silently multiply rows.
- Same family as SQL JOINs and pd.merge(how=): third language, same clerk.
- Memory hook: what does the clerk do with a roll that has no match?