# ferdo Lab | UCL PALS | Week 2 independent extra example
# Instructor's completed R script — fictional data, not official UCL material.
# HTML explanations: https://paths.ferdo.net/r/pals-masterclass/extra-inner-join-two-keys/
#
# Start by reading the two tables. Discuss the join after students have
# inspected the original rows. Install dplyr once if needed:
# install.packages("dplyr")

library(dplyr)

# ---------------------------------------------------------------------------
# 1. STARTING TABLES: five student rows, seven exam rows.
# ---------------------------------------------------------------------------
# ogrenci_no is unique among students, but repeats among exam records.
# Both tables have the SAME names for ogrenci_no and ders_kodu.
ogrenciler <- data.frame(
  ogrenci_no = c(101, 102, 103, 104, 105),
  ders_kodu  = c("MAT101", "FIZ101", "KIM101", "BIO101", "TAR101"),
  ad         = c("Ali", "Zeynep", "Can", "Elif", "Mert")
)

sinav_sonuclari <- data.frame(
  ogrenci_no = c(101, 101, 101, 102, 103, 104, 106),
  ders_kodu  = c("MAT101", "MAT101", "FIZ101", "FIZ101",
                 "MAT101", "BIO101", "MAT101"),
  tur        = c("Vize", "Final", "Vize", "Final",
                 "Vize", "Final", "Vize"),
  notu       = c(70, 82, 55, 91, 78, 88, 66)
)

# NOTE — print() reveals the original tables before any join changes them.
print(ogrenciler)
print(sinav_sonuclari)

# ---------------------------------------------------------------------------
# 2. JOIN ON ONE COLUMN (student number).
# ---------------------------------------------------------------------------
# TASK: Join the two tables using only ogrenci_no; inspect the full result.
# inner_join(x, y, by = "column") keeps matching IDs from both tables.
# Because ders_kodu exists on both sides but is NOT the join key,
# dplyr retains both as ders_kodu.x (students) and ders_kodu.y (exams).
# Students 101/FIZ101 and 103/MAT101 match by student ID, even though
# the registered course differs from the course on the exam row.
sonuc_tek <- inner_join(
  ogrenciler,
  sinav_sonuclari,
  by = "ogrenci_no"
)
print(sonuc_tek)  # 6 rows. Two rows have different .x and .y course codes.

# OPTIONAL CHECK: show only course mismatches admitted by the ID-only join.
uyusmayan_dersler <- filter(sonuc_tek, ders_kodu.x != ders_kodu.y)
print(uyusmayan_dersler)  # Two rows: 101/FIZ101 and 103/MAT101.

# ---------------------------------------------------------------------------
# 3. JOIN ON TWO COLUMNS (student number AND course code).
# ---------------------------------------------------------------------------
# TASK: Join again using the pair of keys.
# by = c("ogrenci_no", "ders_kodu") means BOTH comparisons must be TRUE.
# The common key ders_kodu appears only once in the returned table.
sonuc_iki <- inner_join(
  ogrenciler,
  sinav_sonuclari,
  by = c("ogrenci_no", "ders_kodu")
)
print(sonuc_iki)  # 4 rows: two exams for 101, one for 102, one for 104.

# ---------------------------------------------------------------------------
# 4. READ THE DIFFERENCE: why are some rows repeated or absent?
# ---------------------------------------------------------------------------
# Student 101 is listed once on the left but has two MAT101 exams on the
# right: this produces TWO output rows (a one-to-many match).
# The FIZ101 exam of 101 is excluded by the two-key join.
# Student 103 has a MAT101 exam, while their registered course is KIM101:
# the ID matches, but the (ID, course) pair does not.
# Student 105 has no exam; exam student 106 has no student record.
#
# nrow(x) counts rows. dplyr::n_distinct(x) counts unique student numbers.
print(c(one_key_rows = nrow(sonuc_tek),
        two_key_rows = nrow(sonuc_iki),
        two_key_students = n_distinct(sonuc_iki$ogrenci_no)))

# anti_join returns the rows of its FIRST table with NO matching key pair
# in the SECOND table. Reverse the table order to see the other direction.
print(anti_join(ogrenciler, sinav_sonuclari,
                by = c("ogrenci_no", "ders_kodu")))  # 103 and 105.
print(anti_join(sinav_sonuclari, ogrenciler,
                by = c("ogrenci_no", "ders_kodu")))  # 101/FIZ, 103/MAT, 106/MAT.

# Deterministic assertions for this illustrative dataset.
stopifnot(
  nrow(ogrenciler) == 5L,
  nrow(sinav_sonuclari) == 7L,
  !anyDuplicated(ogrenciler$ogrenci_no),
  nrow(sonuc_tek) == 6L,
  nrow(uyusmayan_dersler) == 2L,
  nrow(sonuc_iki) == 4L,
  n_distinct(sonuc_iki$ogrenci_no) == 3L
)