Wrangle (3.deo)
Mirjana Veljović
Mart 2019
Relacije između baza podataka
Retko je da analiza podataka uključuje samo jednu tabelu podataka. Obično imamo mnogo tabela podataka i moramo ih kombinovati da bismo dobili odgovore na pitanja koja nas interesuju. Dakle, važni su nam i odnosi, a ne samo pojedinačni skupovi podataka.
Odnosi su uvek definisani između parova tabela. Svi ostali odnosi se grade iz ove jednostavne ideje: odnosi tri ili više tabela se uvek izvode iz odnosa između svakog para.
Ponekad oba elementa u paru mogu biti ista tabela! Ovo nam je potrebno ako, na primer, imamo tabelu ljudi, a svaka osoba ima referencu na svoje roditelje.
Da bismo se bavili ovim problemima, potrebne su nam funkcije koje rade sa parovima tabela. Postoje tri grupe glagola dizajniranih da rade sa ovakvim podacima:
Mutating joins: dodaju nove promenljive jednoj bazi podataka na osnovu podudaranja opservacija sa opservacijama iz druge baze.
Filtering joins: filtriraju opservacije iz jedne baze na osnovu toga da li odgovaraju ili ne opservacijama iz druge baze.
Set operacije: tretiraju opservacije kao setove.
Koncepti u ovom poglavlju su slični onima u SQL-u, ali je njihov izraz u dplyr malo drugačiji.
Generalno, dplyr je malo lakši za upotrebu od SQL-a, jer je dplyr specijalizovan za analizu podataka: čini jednostavne operacije analize podataka lakšim, ali otežava neke druge stvari koje obično nisu potrebne za analizu podataka.
Istraživaćemo relacione podatke iz nycflights13 koristeći glagole za dve tabele iz dplyr.
library(tidyverse)
library(nycflights13)nycflights
Paket nycflights13 sadrži četiri tibla koji su povezani sa flights, koji smo koristili kada smo pričali o transformaciji podataka.
airlinesnam omogućava da pronađemo puno ime prevoznika iz skraćenog koda.
airlines## # A tibble: 16 x 2
## carrier name
## <chr> <chr>
## 1 9E Endeavor Air Inc.
## 2 AA American Airlines Inc.
## 3 AS Alaska Airlines Inc.
## 4 B6 JetBlue Airways
## 5 DL Delta Air Lines Inc.
## 6 EV ExpressJet Airlines Inc.
## 7 F9 Frontier Airlines Inc.
## 8 FL AirTran Airways Corporation
## 9 HA Hawaiian Airlines Inc.
## 10 MQ Envoy Air
## 11 OO SkyWest Airlines Inc.
## 12 UA United Air Lines Inc.
## 13 US US Airways Inc.
## 14 VX Virgin America
## 15 WN Southwest Airlines Co.
## 16 YV Mesa Airlines Inc.airportsdaje informacije o svakom aerodromu, na osnovufaašifre aerodroma.
airports## # A tibble: 1,458 x 8
## faa name lat lon alt tz dst tzone
## <chr> <chr> <dbl> <dbl> <int> <dbl> <chr> <chr>
## 1 04G Lansdowne Airport 41.1 -80.6 1044 -5 A America/New_~
## 2 06A Moton Field Municip~ 32.5 -85.7 264 -6 A America/Chic~
## 3 06C Schaumburg Regional 42.0 -88.1 801 -6 A America/Chic~
## 4 06N Randall Airport 41.4 -74.4 523 -5 A America/New_~
## 5 09J Jekyll Island Airpo~ 31.1 -81.4 11 -5 A America/New_~
## 6 0A9 Elizabethton Munici~ 36.4 -82.2 1593 -5 A America/New_~
## 7 0G6 Williams County Air~ 41.5 -84.5 730 -5 A America/New_~
## 8 0G7 Finger Lakes Region~ 42.9 -76.8 492 -5 A America/New_~
## 9 0P2 Shoestring Aviation~ 39.8 -76.6 1000 -5 U America/New_~
## 10 0S9 Jefferson County In~ 48.1 -123. 108 -8 A America/Los_~
## # ... with 1,448 more rowsplanesdaje informacije o svakom avionu, na osnovu njegovog repnog broja, tj.tailnum:
planes## # A tibble: 3,322 x 9
## tailnum year type manufacturer model engines seats speed engine
## <chr> <int> <chr> <chr> <chr> <int> <int> <int> <chr>
## 1 N10156 2004 Fixed wi~ EMBRAER EMB-1~ 2 55 NA Turbo~
## 2 N102UW 1998 Fixed wi~ AIRBUS INDUS~ A320-~ 2 182 NA Turbo~
## 3 N103US 1999 Fixed wi~ AIRBUS INDUS~ A320-~ 2 182 NA Turbo~
## 4 N104UW 1999 Fixed wi~ AIRBUS INDUS~ A320-~ 2 182 NA Turbo~
## 5 N10575 2002 Fixed wi~ EMBRAER EMB-1~ 2 55 NA Turbo~
## 6 N105UW 1999 Fixed wi~ AIRBUS INDUS~ A320-~ 2 182 NA Turbo~
## 7 N107US 1999 Fixed wi~ AIRBUS INDUS~ A320-~ 2 182 NA Turbo~
## 8 N108UW 1999 Fixed wi~ AIRBUS INDUS~ A320-~ 2 182 NA Turbo~
## 9 N109UW 1999 Fixed wi~ AIRBUS INDUS~ A320-~ 2 182 NA Turbo~
## 10 N110UW 1999 Fixed wi~ AIRBUS INDUS~ A320-~ 2 182 NA Turbo~
## # ... with 3,312 more rowsweatherpruža informacije o vremenu na svakom njujorškom aerodromu za svaki sat.
weather## # A tibble: 26,115 x 15
## origin year month day hour temp dewp humid wind_dir wind_speed
## <chr> <dbl> <dbl> <int> <int> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 EWR 2013 1 1 1 39.0 26.1 59.4 270 10.4
## 2 EWR 2013 1 1 2 39.0 27.0 61.6 250 8.06
## 3 EWR 2013 1 1 3 39.0 28.0 64.4 240 11.5
## 4 EWR 2013 1 1 4 39.9 28.0 62.2 250 12.7
## 5 EWR 2013 1 1 5 39.0 28.0 64.4 260 12.7
## 6 EWR 2013 1 1 6 37.9 28.0 67.2 240 11.5
## 7 EWR 2013 1 1 7 39.0 28.0 64.4 240 15.0
## 8 EWR 2013 1 1 8 39.9 28.0 62.2 250 10.4
## 9 EWR 2013 1 1 9 39.9 28.0 62.2 260 15.0
## 10 EWR 2013 1 1 10 41 28.0 59.6 260 13.8
## # ... with 26,105 more rows, and 5 more variables: wind_gust <dbl>,
## # precip <dbl>, pressure <dbl>, visib <dbl>, time_hour <dttm>Odnosi između tiblova su prikazani na sledećoj slici: 
Ovaj dijagram deluje možda komlikovano, ali je jednostavan u poređenju sa onim što se često sreće u praksi. Ključ za razumevanje ovakvih dijagrama je da zapamtimo da se svaka veza odnosi na par tabela. Ne moramo da razumemo celu stvar; samo treba da razumemo lanac odnosa između tabela koje nas interesuju.
Za nycflights13:
flightsje povezan saplanesjednom promenljivom,tailnum.flightsje povezan saairlinespromenljivomcarrier.flightsje povezan saairportsna dva načina: preko promenljivihoriginidest.flightsje povezan saweatherprekoorigin(lokacija) iyear,month,dayihour(vreme).
Ključevi
Promenljive koje se koriste za povezivanje parova tabela se nazivaju ključevi (“keys”). Ključ je promenljiva (ili skup promenljivih) koja jedinstveno identifikuje opservaciju. U jednostavnim slučajevima, jedna promenljiva je dovoljna za identifikaciju opservacije. Na primer, svaki avion je jednoznačno određen svojim repnim brojem (tailnum). U drugim slučajevima može biti potrebno više promenljivh.
Postoje dva tipa ključeva:
Primarni ključ (Primary key): jedinstveno identifikuje opservaciju u svojoj tabeli. Na primer,
planes$tailnumje primarni ključ jer jedinstveno određuje svaki avion u tabeliplanes.Strani ključ (Foreign key): Jedinstveno identifikuje opservaciju u drugoj tabeli. Na primer,
flights$tailnunje strani ključ jer se pojavljuje uflightstabeli gde povezuje svaki let sa jednim avionom.
Promenljiva može biti i primarni i strani ključ.
Kada smo odredili primarne ključeve u svakoj tabeli, dobra je praksa da potvrdimo da oni zaista identifikuju jedinstveno svaku opservaciju. Jedan od načina da to uradimo je da prebrojimo opservacije (count()) za svaku od vrednosti u primarnom ključu i potražimo gde je n veće od jedan.
planes %>%
count(tailnum) %>%
filter(n > 1)## # A tibble: 0 x 2
## # ... with 2 variables: tailnum <chr>, n <int>weather %>%
count(year, month, day, hour, origin) %>%
filter(n > 1)## # A tibble: 3 x 6
## year month day hour origin n
## <dbl> <dbl> <int> <int> <chr> <int>
## 1 2013 11 3 1 EWR 2
## 2 2013 11 3 1 JFK 2
## 3 2013 11 3 1 LGA 2Ponekad tabela nema eksplicitni primarni ključ: svaki red je opservacija, ali nijedna kombinacija promenljivih je ne identifikuje pouzdano. Na primer, šta je primarni ključ u tabeli flights? Možemo pomisliti da je to date i flight, ili tailnum, ali nijedna kombinacija ne određuje jedinstveno opservacije:
flights %>%
count(year, month, day, flight) %>%
filter(n > 1)## # A tibble: 29,768 x 5
## year month day flight n
## <int> <int> <int> <int> <int>
## 1 2013 1 1 1 2
## 2 2013 1 1 3 2
## 3 2013 1 1 4 2
## 4 2013 1 1 11 3
## 5 2013 1 1 15 2
## 6 2013 1 1 21 2
## 7 2013 1 1 27 4
## 8 2013 1 1 31 2
## 9 2013 1 1 32 2
## 10 2013 1 1 35 2
## # ... with 29,758 more rowsflights %>%
count(year, month, day, tailnum) %>%
filter(n > 1)## # A tibble: 64,928 x 5
## year month day tailnum n
## <int> <int> <int> <chr> <int>
## 1 2013 1 1 N0EGMQ 2
## 2 2013 1 1 N11189 2
## 3 2013 1 1 N11536 2
## 4 2013 1 1 N11544 3
## 5 2013 1 1 N11551 2
## 6 2013 1 1 N12540 2
## 7 2013 1 1 N12567 2
## 8 2013 1 1 N13123 2
## 9 2013 1 1 N13538 3
## 10 2013 1 1 N13566 3
## # ... with 64,918 more rowsKada počnemo rad sa ovim podacima, možemo naivno pretpostaviti da će se svaki broj leta koristiti samo jednom dnevno: to bi znatno olakšalo rešavanje problema za određeni let. Nažalost, to nije slučaj. Ako tabeli nedostaje primarni ključ, ponekad je korisno dodati jedan sa mutate() i row_number(). Taj novi, “veštački” ključ, naziva se zamenskim ključem (“surogate key”).
Primarni ključ i odgovarajući strani ključ u drugoj tabeli formiraju vezu. Odnosi su obično “jedan prema više”. Na primer, svaki let ima jedan avion, ali svaki avion ima mnogo letova. U drugim podacima, povremeno ćemo videti odnos “1 prema 1”. Ovo možemo da posmatramo kao specijalan slučaj “jedan prema više”. Možemo modelirati odnose “više prema više”" sa “više prema 1” i “1 prema više” odnosima. Na primer, u ovim podacima postoji odnos “više prema više” između aviokompanija i aerodroma: svaka aviokompanija leti na mnoge aerodrome; svaki aerodrom prima letove mnogih avio-kompanija.
Mutating joins
Prvi alat koji ćemo razmatrati za kombinovanje parova tabela je mutating join. Ovo spajanje nam omogućava da kombinujemo promenljive iz dve tabele. Prvo se promenljive uklapaju uz pomoć ključeva, a zatim se kopiraju iz jedne tabele u drugu.
Kao i mutate(), funkcije spajanja (join funkcije) dodaju promenljive na kraj, pa ako već imamo mnogo promenljivih, ove nove neće biti prikazane. Zato ćemo napraviti uži skup podataka, da bismo lakše videli šta se dešava:
flights2 = flights %>%
select(year:day, hour, origin, dest, tailnum, carrier)
flights2## # A tibble: 336,776 x 8
## year month day hour origin dest tailnum carrier
## <int> <int> <int> <dbl> <chr> <chr> <chr> <chr>
## 1 2013 1 1 5 EWR IAH N14228 UA
## 2 2013 1 1 5 LGA IAH N24211 UA
## 3 2013 1 1 5 JFK MIA N619AA AA
## 4 2013 1 1 5 JFK BQN N804JB B6
## 5 2013 1 1 6 LGA ATL N668DN DL
## 6 2013 1 1 5 EWR ORD N39463 UA
## 7 2013 1 1 6 EWR FLL N516JB B6
## 8 2013 1 1 6 LGA IAD N829AS EV
## 9 2013 1 1 6 JFK MCO N593JB B6
## 10 2013 1 1 6 LGA ORD N3ALAA AA
## # ... with 336,766 more rows(Prisetimo se, kada koristimo RStudio, takođe možemo da koristimo View da bismo izbegli ovaj problem.)
Recimo da želimo da dodamo puno ime avio-kompanije podacima u flights2. Možemo kombinovati podatke iz flights2 i airlines sa left_join():
flights2 %>%
select(-origin, -dest) %>%
left_join(airlines, by = "carrier")## # A tibble: 336,776 x 7
## year month day hour tailnum carrier name
## <int> <int> <int> <dbl> <chr> <chr> <chr>
## 1 2013 1 1 5 N14228 UA United Air Lines Inc.
## 2 2013 1 1 5 N24211 UA United Air Lines Inc.
## 3 2013 1 1 5 N619AA AA American Airlines Inc.
## 4 2013 1 1 5 N804JB B6 JetBlue Airways
## 5 2013 1 1 6 N668DN DL Delta Air Lines Inc.
## 6 2013 1 1 5 N39463 UA United Air Lines Inc.
## 7 2013 1 1 6 N516JB B6 JetBlue Airways
## 8 2013 1 1 6 N829AS EV ExpressJet Airlines Inc.
## 9 2013 1 1 6 N593JB B6 JetBlue Airways
## 10 2013 1 1 6 N3ALAA AA American Airlines Inc.
## # ... with 336,766 more rowsRezultat pridruživanja baze airlines bazi flights2 je dodatna promenljiva: name. To je razlog što ovu vrstu pridruživanja nazivamo mutating join. U ovom slučaju, mogli smo da dođemo do istog rezultata korišćenjem mutate() i baznog R vađenja podskupova (subsetting):
flights2 %>%
select(-origin, -dest) %>%
mutate(name = airlines$name[match(carrier, airlines$carrier)])## # A tibble: 336,776 x 7
## year month day hour tailnum carrier name
## <int> <int> <int> <dbl> <chr> <chr> <chr>
## 1 2013 1 1 5 N14228 UA United Air Lines Inc.
## 2 2013 1 1 5 N24211 UA United Air Lines Inc.
## 3 2013 1 1 5 N619AA AA American Airlines Inc.
## 4 2013 1 1 5 N804JB B6 JetBlue Airways
## 5 2013 1 1 6 N668DN DL Delta Air Lines Inc.
## 6 2013 1 1 5 N39463 UA United Air Lines Inc.
## 7 2013 1 1 6 N516JB B6 JetBlue Airways
## 8 2013 1 1 6 N829AS EV ExpressJet Airlines Inc.
## 9 2013 1 1 6 N593JB B6 JetBlue Airways
## 10 2013 1 1 6 N3ALAA AA American Airlines Inc.
## # ... with 336,766 more rowsMeđutim, ovo je teško uopštiti kada je potrebno da povežemo više promenljivih.
Sledeći odeljci objašnjavaju kako rade mutating joins. Počećemo tako što ćemo vizuelno prikazati pridruživanja. Zatim ćemo to iskoristiti da objasnimo četiri mutating join funkcije: unutrašnje spajanje (inner join) i tri spoljna spajanja (outer joins).
Razumevanje pridruživanja
Da bismo razumeli kako pridruživanja rade, najbolje je da posmatramo vizuelnu reprezentaciju:
x = tribble(
~key, ~val_x,
1, "x1",
2, "x2",
3, "x3"
)
y = tribble(
~key, ~val_y,
1, "y1",
2, "y2",
4, "y3"
)Obojena kolona predstavlja promenljivu ključa: ona se koristi za pronalaženje podudaranja redova među tabelama. Siva kolona predstavlja kolonu vrednosti. U ovim primerima, prikazaćemo slučaj kada postoji jedna promenljiva ključa, ali ideja se uopštava i na višestruke ključeve i višestruke vrednosti.
Pridruživanje je način povezivanja svakog reda iz x sa nijednim, jednim, ili više redova iz y. Na sledećem dijagramu je prikazano svako potencijalno podudaranje kao presek odgovarajućih linija.
(Ovde je promenjen redosled kolona vrednosti i ključa u x. To je urađeno da bi se naglasilo da se pridruživanja vrše na osnovu podudaranja ključa; vrednost se samo prenosi.)
Prava podudaranja će biti označena tačkama. Broj tačaka = broju podudaranja = broju redova na izlazu.
Inner Join
Najednostavniji tip pridruživanja je inner join. On spaja parove opservacija kad god su njihovi ključevi jednaki:
(Da budemo precizni, ovo je inner equijoin jer se smatra da se ključevi podudaraju ako su jednaki. Budući da je većina pridruživanja baš equijoins, obično ne navodimo to.)
Izlaz je nova baza koja sadrži ključ, vrednosti x-a i vrednosti y-a. Koristimo by da kažemo dplyr-u koja je promenljiva ključ:
x %>%
inner_join(y, by = "key")## # A tibble: 2 x 3
## key val_x val_y
## <dbl> <chr> <chr>
## 1 1 x1 y1
## 2 2 x2 y2Najvažnija osobina inner join-a je da redovi koji nemaju podudaranje nisu uključeni u rezultat. To znači da ova pridruživanja obično nisu prikladna za upotrebu u analizi, jer je previše lako izgubiti opservacije.
Outer joins
Inner join čuva opservacije koje se pojavljuju u obe tabele. Outer join čuva opservacije koje se pojavljuju u bar jednoj od tabela. Postoje tri tipa spoljnih pridruživanja:
Left join čuva sve opservacije iz
x.Right join čuva sve opservacije iz
y.Full join čuva sve opservacije i iz
xiy.
Ova pridruživanja rade dodavanjem “virtuelne” opservacije odgovarajućih tabeli. Ova opservacija ima ključ koji se uvek poklapa (ako nema drugih ključeva koji se poklapaju), i vrednost popunjenu sa NA.
Grafički, to izgleda ovako:
Najčešće korišćeno pridruživanje je left join: ono se koristi svaki put kad tražimo dodatne podatke iz druge tabele, jer čuva izvorne opservacije čak i kad nema podudaranja. Left join bi trebalo da bude naše podrazumevano pridruživanje: koristimo ga uvek, osim ako nemamo jak razlog da koristimo neko od ostalih.
Drugi način prikazivanja različitih tipova pridruživanja je uz pomoć Venovog dijagrama:
Međutim, ovo nije sjajna reprezentacija - dobra je za shvatanje toga koja pridruživanja čuvaju koje opservacije u tabelama, ali pati od glavnog ograničenja: Venov dijagram ne može da pokaže šta se dešava kada ključevi ne identifikuju jedinstveno opservaciju.
Duplirajući ključevi
Do sada smo pretpostavljali da su ključevi jedinstveni. Međutim, to nije uvek slučaj. Ovaj odeljak objašnjava šta se dešava kad ključevi nisu jedinstveni. Postoje dve mogućnosti:
- Jedna tabela ima duplirajuće ključeve. Ovo je korisno kada želimo da dodamo dodatne informacije (obično postoji odnos jedan prema više).
Kolona key je primarni ključ u y i strani ključ u x.
x = tribble(
~key, ~val_x,
1, "x1",
2, "x2",
2, "x3",
1, "x4"
)
y = tribble(
~key, ~val_y,
1, "y1",
2, "y2"
)
left_join(x, y, by = "key")## # A tibble: 4 x 3
## key val_x val_y
## <dbl> <chr> <chr>
## 1 1 x1 y1
## 2 2 x2 y2
## 3 2 x3 y2
## 4 1 x4 y1- Obe tabele imaju duplirajuće ključeve. Ovo je obično greška, jer ni u jednoj tabeli ključevi ne identifikuju jednoznačno opservacije. Kada pridružimo, dobićemo sve moguće kombinacije:
x = tribble(
~key, ~val_x,
1, "x1",
2, "x2",
2, "x3",
3, "x4"
)
y = tribble(
~key, ~val_y,
1, "y1",
2, "y2",
2, "y3",
3, "y4"
)
left_join(x, y, by = "key")## # A tibble: 6 x 3
## key val_x val_y
## <dbl> <chr> <chr>
## 1 1 x1 y1
## 2 2 x2 y2
## 3 2 x2 y3
## 4 2 x3 y2
## 5 2 x3 y3
## 6 3 x4 y4Definisanje kolone ključa
Do sad su parovi tabela uvek bili spajani na osnovu jedne promenljive, i ta promenljiva je imala isto ime u obe tabele. To je naglašavano korišćenjem by = "key". Možemo koristiti i druge vrednosti za by da bismo povezivali tabele na druge načine:
- Podrazumevano,
by = NULLkoristi sve promenljive koje se pojavljuju u obe tabele, to je takozvano prirodno pridruživanje (“natural join”). Na primer, tabeleweatheriflightsse podudaraju u promenljivama:year,month,day,houriorigin.
flights2 %>%
left_join(weather)## Joining, by = c("year", "month", "day", "hour", "origin")## # A tibble: 336,776 x 18
## year month day hour origin dest tailnum carrier temp dewp humid
## <dbl> <dbl> <int> <dbl> <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl>
## 1 2013 1 1 5 EWR IAH N14228 UA 39.0 28.0 64.4
## 2 2013 1 1 5 LGA IAH N24211 UA 39.9 25.0 54.8
## 3 2013 1 1 5 JFK MIA N619AA AA 39.0 27.0 61.6
## 4 2013 1 1 5 JFK BQN N804JB B6 39.0 27.0 61.6
## 5 2013 1 1 6 LGA ATL N668DN DL 39.9 25.0 54.8
## 6 2013 1 1 5 EWR ORD N39463 UA 39.0 28.0 64.4
## 7 2013 1 1 6 EWR FLL N516JB B6 37.9 28.0 67.2
## 8 2013 1 1 6 LGA IAD N829AS EV 39.9 25.0 54.8
## 9 2013 1 1 6 JFK MCO N593JB B6 37.9 27.0 64.3
## 10 2013 1 1 6 LGA ORD N3ALAA AA 39.9 25.0 54.8
## # ... with 336,766 more rows, and 7 more variables: wind_dir <dbl>,
## # wind_speed <dbl>, wind_gust <dbl>, precip <dbl>, pressure <dbl>,
## # visib <dbl>, time_hour <dttm>- Vektor karaktera,
by = "x". Ovo je slično kao prirodno pridruživanje, ali koristi samo neke od zajedničkih promenljivih. Na primer, iflightsiplanesimaju promenljivuyear, ali one podrazumevaju različite stvari, tako da želimo samo da povežemo potailnum.
flights2 %>%
left_join(planes, by = "tailnum")## # A tibble: 336,776 x 16
## year.x month day hour origin dest tailnum carrier year.y type
## <int> <int> <int> <dbl> <chr> <chr> <chr> <chr> <int> <chr>
## 1 2013 1 1 5 EWR IAH N14228 UA 1999 Fixed win~
## 2 2013 1 1 5 LGA IAH N24211 UA 1998 Fixed win~
## 3 2013 1 1 5 JFK MIA N619AA AA 1990 Fixed win~
## 4 2013 1 1 5 JFK BQN N804JB B6 2012 Fixed win~
## 5 2013 1 1 6 LGA ATL N668DN DL 1991 Fixed win~
## 6 2013 1 1 5 EWR ORD N39463 UA 2012 Fixed win~
## 7 2013 1 1 6 EWR FLL N516JB B6 2000 Fixed win~
## 8 2013 1 1 6 LGA IAD N829AS EV 1998 Fixed win~
## 9 2013 1 1 6 JFK MCO N593JB B6 2004 Fixed win~
## 10 2013 1 1 6 LGA ORD N3ALAA AA NA <NA>
## # ... with 336,766 more rows, and 6 more variables: manufacturer <chr>,
## # model <chr>, engines <int>, seats <int>, speed <int>, engine <chr>Primetimo da su promenljive year (koje se pojavljuju u obe tabele, ali nisu jednake) na izlazu prikazane sa sufiksom.
by = c("a" = "b"). Ovo će tražiti podudaranje promenljiveaiz tabelexi promenljivebiz tabeley. Promenljive izxće se koristiti na izlazu.
Na primer, ako želimo da nacrtamo mapu, moramo kombinovati podatke iz flights sa podacima iz airports koji sadrže lokaciju (lat i lon) za svaki aerodrom. Svaki let ima polazni (origin) i dolazni (destination) aerodrom, pa moramo da navedemo koji želimo:
flights2 %>%
left_join(airports, c("dest" = "faa"))## # A tibble: 336,776 x 15
## year month day hour origin dest tailnum carrier name lat lon
## <int> <int> <int> <dbl> <chr> <chr> <chr> <chr> <chr> <dbl> <dbl>
## 1 2013 1 1 5 EWR IAH N14228 UA Georg~ 30.0 -95.3
## 2 2013 1 1 5 LGA IAH N24211 UA Georg~ 30.0 -95.3
## 3 2013 1 1 5 JFK MIA N619AA AA Miami~ 25.8 -80.3
## 4 2013 1 1 5 JFK BQN N804JB B6 <NA> NA NA
## 5 2013 1 1 6 LGA ATL N668DN DL Harts~ 33.6 -84.4
## 6 2013 1 1 5 EWR ORD N39463 UA Chica~ 42.0 -87.9
## 7 2013 1 1 6 EWR FLL N516JB B6 Fort ~ 26.1 -80.2
## 8 2013 1 1 6 LGA IAD N829AS EV Washi~ 38.9 -77.5
## 9 2013 1 1 6 JFK MCO N593JB B6 Orlan~ 28.4 -81.3
## 10 2013 1 1 6 LGA ORD N3ALAA AA Chica~ 42.0 -87.9
## # ... with 336,766 more rows, and 4 more variables: alt <int>, tz <dbl>,
## # dst <chr>, tzone <chr>flights2 %>%
left_join(airports, c("origin" = "faa"))## # A tibble: 336,776 x 15
## year month day hour origin dest tailnum carrier name lat lon
## <int> <int> <int> <dbl> <chr> <chr> <chr> <chr> <chr> <dbl> <dbl>
## 1 2013 1 1 5 EWR IAH N14228 UA Newar~ 40.7 -74.2
## 2 2013 1 1 5 LGA IAH N24211 UA La Gu~ 40.8 -73.9
## 3 2013 1 1 5 JFK MIA N619AA AA John ~ 40.6 -73.8
## 4 2013 1 1 5 JFK BQN N804JB B6 John ~ 40.6 -73.8
## 5 2013 1 1 6 LGA ATL N668DN DL La Gu~ 40.8 -73.9
## 6 2013 1 1 5 EWR ORD N39463 UA Newar~ 40.7 -74.2
## 7 2013 1 1 6 EWR FLL N516JB B6 Newar~ 40.7 -74.2
## 8 2013 1 1 6 LGA IAD N829AS EV La Gu~ 40.8 -73.9
## 9 2013 1 1 6 JFK MCO N593JB B6 John ~ 40.6 -73.8
## 10 2013 1 1 6 LGA ORD N3ALAA AA La Gu~ 40.8 -73.9
## # ... with 336,766 more rows, and 4 more variables: alt <int>, tz <dbl>,
## # dst <chr>, tzone <chr>Druge implementacije
base::merge() može da obavlja sve četiri vrste mutating join-a:
| dplyr | merge |
|---|---|
| inner_join(x, y) | merge(x, y) |
| left_join(x, y) | merge(x, y, all.x - TRUE) |
| right_join(x, y) | merge(x, y, all.y = TRUE) |
| full_join(x, y) | merge(x, y, all.x = TRUE, all.y = TRUE) |
Prednost dplyr glagola je ta što oni jasnije prenose nameru našeg koda: razlika između vrsta pridruživanja je zaista važna, a sakrivena je u argumentima kod merge() funkcija. Takođe, pridruživanja dplyr-a su znatno brža.
SQL je bio inspiracija za ove dplyr funkcije, pa je prevod jednostavan:
| dplyr | SQL |
|---|---|
| inner_join(x, y, by = ‘’z’’) | SELECT * FROM x INNER JOIN y USING (z) |
| left_join(x, y, by = ‘’z’’) | SELECT * FROM x LEFT OUTER JOIN y USING (z) |
| right_join(x, y, by = ‘’z’’) | SELECT * FROM x RIGHT OUTER JOIN y USING (z) |
| full_join(x, y, by = ‘’z’’) | SELECT * FROM x FULL OUTER JOIN y USING (z) |
(“INNER” i “OUTER” su opcioni i često se izostavljaju.)
Spajanje različitih promenljivih među tabelama, npr. inner_join(x, y, by = c("a" = "b")) koristi malo drugačiju sintaksu od SQL-ovog SELECT * FROM x INNER JOIN y ON x.a = y.b. SQL podržava širi raspon tipova pridruživanja od dplyr-a jer možemo povezati tabele koristeći uslove koji nisu jednakosti (non-equijoins).
Filtering joins
Filtering joins spajaju opservacije na isti način kao mutating joins, ali utiču na opservacije, a ne na promenljive. Postoje dva tipa:
semi_join(x, y)zadržava sve opservacije izxkoje imaju podudaranja uy.anti_join(x, y)izbacuje sve opservacije izxkoje imaju podudaranja uy.
Semi-joins su korisni za usklađivanje filtriranih sumarnih tabela sa originalnim tabelama. Na primer, recimo da smo pronašli deset najpopularnijih destinacija:
top_dest = flights %>%
count(dest, sort = TRUE) %>%
head(10)
top_dest## # A tibble: 10 x 2
## dest n
## <chr> <int>
## 1 ORD 17283
## 2 ATL 17215
## 3 LAX 16174
## 4 BOS 15508
## 5 MCO 14082
## 6 CLT 14064
## 7 SFO 13331
## 8 FLL 12055
## 9 MIA 11728
## 10 DCA 9705Sada želimo da pronađemo svaki let koji je otišao na jednu od tih destinacija. Možemo sami da napravimo filter:
flights %>%
filter(dest %in% top_dest$dest)## # A tibble: 141,145 x 19
## year month day dep_time sched_dep_time dep_delay arr_time
## <int> <int> <int> <int> <int> <dbl> <int>
## 1 2013 1 1 542 540 2 923
## 2 2013 1 1 554 600 -6 812
## 3 2013 1 1 554 558 -4 740
## 4 2013 1 1 555 600 -5 913
## 5 2013 1 1 557 600 -3 838
## 6 2013 1 1 558 600 -2 753
## 7 2013 1 1 558 600 -2 924
## 8 2013 1 1 558 600 -2 923
## 9 2013 1 1 559 559 0 702
## 10 2013 1 1 600 600 0 851
## # ... with 141,135 more rows, and 12 more variables: sched_arr_time <int>,
## # arr_delay <dbl>, carrier <chr>, flight <int>, tailnum <chr>,
## # origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>,
## # minute <dbl>, time_hour <dttm>Međutim, teško je proširiti taj pristup na više promenljivih.
Umesto toga, možemo koristiti semi_join(), koja povezuje dve tabele na sličan način kao i mutating join, ali umesto dodavanja novih kolona, zadržava samo redove iz x koji imaju podudaranja u y:
flights %>%
semi_join(top_dest)## Joining, by = "dest"## # A tibble: 141,145 x 19
## year month day dep_time sched_dep_time dep_delay arr_time
## <int> <int> <int> <int> <int> <dbl> <int>
## 1 2013 1 1 542 540 2 923
## 2 2013 1 1 554 600 -6 812
## 3 2013 1 1 554 558 -4 740
## 4 2013 1 1 555 600 -5 913
## 5 2013 1 1 557 600 -3 838
## 6 2013 1 1 558 600 -2 753
## 7 2013 1 1 558 600 -2 924
## 8 2013 1 1 558 600 -2 923
## 9 2013 1 1 559 559 0 702
## 10 2013 1 1 600 600 0 851
## # ... with 141,135 more rows, and 12 more variables: sched_arr_time <int>,
## # arr_delay <dbl>, carrier <chr>, flight <int>, tailnum <chr>,
## # origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>,
## # minute <dbl>, time_hour <dttm>Grafički, semi-join izgeleda ovako:
Samo postojanje podudaranja je važno:
Inverz semi-join-a je anti-join. Anti-join zadržava redove koji nemaju podudaranje: 
Anti-joins su korisni za dijagnostikovanje neusklađenosti spajanja. Na primer, kada povezujemo flights i planes, može nas interesovati da saznamo da li postoji mnogo podataka iz flights koji nemaju podudaranje u planes.
flights %>%
anti_join(planes, by = "tailnum") %>%
count(tailnum, sort = TRUE)## # A tibble: 722 x 2
## tailnum n
## <chr> <int>
## 1 <NA> 2512
## 2 N725MQ 575
## 3 N722MQ 513
## 4 N723MQ 507
## 5 N713MQ 483
## 6 N735MQ 396
## 7 N0EGMQ 371
## 8 N534MQ 364
## 9 N542MQ 363
## 10 N531MQ 349
## # ... with 712 more rowsProblemi spajanja
Podaci sa kojima smo radili su napravljeni tako da imamo što manje problema u radu sa njima. Međutim, malo je verovatno da će naši podaci biti takvi. Postoji nekoliko stvari koje možemo uraditi sa podacima kako bi pridruživanje išlo glatko:
Počinjemo identifikovanjem promenljive koja čini primarni ključ u svakoj tabeli. Ovo bismo obično trebali da radimo na osnovu našeg razumevanja podataka, a ne samo tražeći kombinaciju promenljivih koje daju jedinstveni identifikator. Ako samo tražimo promenljive bez razmišljanja šta one znače, možemo biti (ne)srećne ruke i pronaći kombinaciju koja je jedinstvena u našim trenutnim podacima, ali veza možda nije dobra uopšte.
Proverimo da nijedna vrednost u primarnom ključu ne nedostaje. Ako vrednost nedostaje, onda ne može da identifikuje opservaciju!
Proverimo da li naši strani ključevi (foreign key) odgovaraju primarnim ključevima (primary key) u drugoj tabeli. Najbolji način da se to uradi je
anti_join(). Uobičajeno je da se ključevi ne podudaraju zbog grešaka pri unosu podataka. Popravljanje ovoga često zahteva mnogo posla.
Treba imati na umu da jednostavno proveravanje broja redova pre i posle pridruživanja nije dovoljno da budemo sigurni da je pridruživanje prošlo glatko. Ako imamo inner join sa duplirajućim ključevima u obe tabele, možda nećemo imati sreće jer bi broj ispuštenih redova mogao biti isti broju dupliranih redova!
Set operacije
Poslednji tip glagola za dve tabele su set operacije. Generalno, koriste se najređe, ali mogu biti korisne kada želimo da razbijemo jedan složeni filter u jednostavnije delove. Sve ove operacije rade sa celim redom, poredeći vrednosti svake promenljive. Oni očekuju da argumenti x i y imaju iste promenljive, i tretiraju opservacije kao skupove:
intersect(x, y): vraća samo opservacije koje su uxiy.union(x, y): vraća (jedinstvene) opservacije koje su uxili uy.setdiff(x, y): vraća opservacije koje su ux, ali ne i uy.
Za ove jednostavne podatke:
df1 = tribble(
~x, ~y,
1, 1,
2, 1
)
df2 = tribble(
~x, ~y,
1, 1,
1, 2
)Mogućnosti su:
intersect(df1, df2)## # A tibble: 1 x 2
## x y
## <dbl> <dbl>
## 1 1 1# Primetimo da dobijemo tri reda, a ne cetiri
union(df1, df2)## # A tibble: 3 x 2
## x y
## <dbl> <dbl>
## 1 2 1
## 2 1 1
## 3 1 2setdiff(df1, df2)## # A tibble: 1 x 2
## x y
## <dbl> <dbl>
## 1 2 1setdiff(df2, df1)## # A tibble: 1 x 2
## x y
## <dbl> <dbl>
## 1 1 2