Łączenie ramek danych w R (merge, left_join)

Dwie tabele, jeden klucz

Prawdziwe dane rzadko mieszczą się w jednej tabeli. Klienci są w jednej ramce danych, ich zamówienia w innej, a pytanie "ile wydał każdy klient?" wymaga obu. Łączy je klucz: kolumna obecna w każdej tabeli, która identyfikuje ten sam obiekt. Łączenie tabel po kluczu to join (słowo z SQL) albo merge (słowo z bazowego R); to ta sama operacja.

Oto para tabel używana w dalszej części strony. Zauważ, że klient 5 złożył zamówienie, ale nie ma go w tabeli klientów, a klienci 2 i 4 nigdy nic nie zamówili:

customers <- data.frame(
    id   = c(1, 2, 3, 4),
    name = c("Ana", "Ben", "Chen", "Dana")
)
orders <- data.frame(
    id     = c(1, 1, 3, 5),
    amount = c(250, 40, 120, 60)
)

print(customers)
print(orders)

Niedopasowania są celowe: to, co złączenie robi z niedopasowanymi wierszami, właśnie odróżnia typy złączeń.

merge(): domyślnie złączenie wewnętrzne

merge(x, y, by = "key") dopasowuje wiersze o równych kluczach i skleja ich kolumny. Domyślnie zachowuje tylko klucze obecne w obu tabelach, czyli wykonuje złączenie wewnętrzne (inner join):

customers <- data.frame(
    id   = c(1, 2, 3, 4),
    name = c("Ana", "Ben", "Chen", "Dana")
)
orders <- data.frame(
    id     = c(1, 1, 3, 5),
    amount = c(250, 40, 120, 60)
)

print(merge(customers, orders, by = "id"))

Wracają trzy wiersze. Ana pojawia się dwa razy: ma dwa zamówienia, a złączenie tworzy jeden wiersz wyniku na każdą dopasowaną parę. Bena i Dany nie ma (brak zamówień), podobnie jak tajemniczego zamówienia klienta 5 (brak klienta). Złączenie wewnętrzne po cichu odrzuca niedopasowane wiersze z obu tabel. To dokładnie to, czego chcesz, gdy interesują cię tylko pełne pary, i cichy błąd utraty danych, gdy nie.

Złączenia lewe, prawe i pełne: all.x, all.y, all

Żeby zachować niedopasowane wiersze, powiedz, wiersze której tabeli są nietykalne. all.x = TRUE zachowuje każdy wiersz pierwszej tabeli. To złączenie lewe (left join), najczęściej używane w praktyce:

customers <- data.frame(
    id   = c(1, 2, 3, 4),
    name = c("Ana", "Ben", "Chen", "Dana")
)
orders <- data.frame(
    id     = c(1, 1, 3, 5),
    amount = c(250, 40, 120, 60)
)

print(merge(customers, orders, by = "id", all.x = TRUE))

Teraz Ben i Dana zostają, z NA w amount, co znaczy "ten klient istnieje, ale nie dopasowano żadnego zamówienia". Te NA niosą informację, a nie są śmieciami: is.na(result$amount) to dokładnie lista klientów, którzy nigdy nic nie zamówili (pracę z nimi opisuje artykuł o brakujących wartościach). Pozostałe warianty to ten sam pomysł skierowany gdzie indziej: all.y = TRUE zachowuje każdy wiersz drugiej tabeli (złączenie prawe: zamówienie nieznanego klienta 5 zostałoby z NA w name), a all = TRUE zachowuje wszystko z obu stron (złączenie pełne).

Wybieraj, zadając pytanie: których wierszy nie wolno mi stracić? Wzbogacanie tabeli głównej danymi ze słownika: złączenie lewe, tabela główna pierwsza. Audyt w obu kierunkach: złączenie pełne.

Różne nazwy kluczy: by.x i by.y

Tabele rzadko zgadzają się co do nazw: id w jednej, customer_id w drugiej. Nie zmieniaj nazw, tylko podaj merge() obie:

customers <- data.frame(
    id   = c(1, 2, 3),
    name = c("Ana", "Ben", "Chen")
)
orders <- data.frame(
    customer_id = c(1, 3, 3),
    amount      = c(250, 120, 60)
)

print(merge(customers, orders, by.x = "id", by.y = "customer_id"))

Wynik zachowuje nazwę klucza z pierwszej tabeli. Podobna sprawa: jeśli dwie tabele mają wspólne nazwy kolumn, które nie są kluczem (np. obie mają date), merge dodaje do nich przyrostki date.x i date.y. Zmień ich nazwy od razu, bo czyta się je fatalnie.

Złączenia w dplyr

dplyr daje każdemu typowi złączenia osobny czasownik, więc intencja jest w nazwie funkcji, a nie w argumentach-flagach (przykład statyczny, bo piaskownica uruchamia tylko bazowy R):

library(dplyr)

left_join(customers, orders, by = "id")
inner_join(customers, orders, by = "id")
full_join(customers, orders, by = "id")
left_join(customers, orders, by = c("id" = "customer_id"))  # different names

Poza czytelnością left_join() zachowuje kolejność wierszy pierwszej tabeli (merge() sortuje po kluczu) i głośno ostrzega przed dopasowaniami wiele do wielu. Całą rodzinę czasowników opisuje wprowadzenie do dplyr.

Niedocenianym członkiem rodziny jest anti_join(): zwraca wiersze pierwszej tabeli, które nie mają dopasowania w drugiej, bez dodawania kolumn, same resztki:

anti_join(customers, orders, by = "id")   # customers who never ordered

To pytanie ("które wiersze się nie dopasowały?") ciągle pojawia się przy czyszczeniu danych (niedopasowane ID, osierocone rekordy, nieudane wyszukiwania), a anti_join() odpowiada na nie jednym wywołaniem tam, gdzie bazowy R potrzebuje customers[!(customers$id %in% orders$id), ].

Doklejanie wierszy: rbind()

Złączenie łączy kolumny dwóch tabel. Gdy zamiast tego masz dwie partie wierszy tego samego rodzaju, np. zamówienia ze stycznia i z lutego, doklejasz je przez rbind():

jan <- data.frame(id = c(1, 2), amount = c(250, 40))
feb <- data.frame(id = c(3, 4), amount = c(120, 60))

print(rbind(jan, feb))

Wymaganie jest ścisłe: obie ramki danych muszą mieć te same nazwy kolumn (w dowolnej kolejności, bo rbind dopasowuje po nazwie). Brakująca albo dodatkowa kolumna to błąd, a nie wypełnienie przez NA. Gdy kolumny w partiach się rozjechały, bind_rows() z dplyr jest bardziej wyrozumiałe: wyrównuje po nazwie i wypełnia luki przez NA.

Eksplozja zduplikowanych kluczy

Klasyczny wypadek przy złączeniach: klucze, które uważano za unikalne, wcale takie nie są, i to w obu tabelach. Każde dopasowanie łączy się z każdym dopasowaniem i wiersze się mnożą:

a <- data.frame(id = c(1, 1), x = c("a1", "a2"))
b <- data.frame(id = c(1, 1), y = c("b1", "b2"))

merged <- merge(a, b, by = "id")
print(merged)
cat("rows:", nrow(merged), "\n")

Dwa wiersze złączone z dwoma wierszami dają cztery: każde x w parze z każdym y. Na prawdziwych danych tak właśnie tabela z 10 000 wierszy zamienia się w 3 miliony wierszy i podwojoną sumę przychodów. merge() robi to po cichu. Obroną jest nawyk: przed złączeniem sprawdź klucz, który uważasz za unikalny (anyDuplicated(customers$id) powinno zwrócić 0), a po złączeniu porównaj nrow() z tym, czego się spodziewasz.

Co warto zapamiętać

• merge(x, y, by = "key") to złączenie wewnętrzne: niedopasowane wiersze znikają po cichu.
• all.x = TRUE (lewe), all.y = TRUE (prawe), all = TRUE (pełne) zachowują niedopasowane wiersze i wypełniają je przez NA.
• Różne nazwy kluczy: by.x / by.y w bazowym R, by = c("a" = "b") w dplyr.
• dplyr nazywa każde złączenie osobno; anti_join(), czyli wiersze, które się nie dopasowały, to ten niedoceniany.
• rbind() dokleja tabele o tym samym kształcie; kolumny muszą pasować po nazwie.
• Zduplikowane klucze po cichu mnożą wiersze: sprawdzaj anyDuplicated() przed złączeniem i nrow() po nim.

Dalej: przekształcanie między formatem szerokim i długim przez pivot_longer() i pivot_wider().