Join di tabelle con R – DPLYR

La join è l’operazione centrale per l’estrazione di conoscenza da un database relazionale, e consente di mettere in relazione dati di tabelle diverse, aventi una chiave in comune. E’ possibile fare una join tra due tabelle anche direttamente in R ( parliamo in questo caso di dataframe , ma l’operazione è la stessa) , senza la necessità di passare prima da un RDBMS, che non sempre è immediatamente disponibile al data analyst.

Riporto qui le principali opzioni disponibili in R – DPLYR per creare una join tra due tabelle t1 e t2 :

  • inner join, tutti i record presenti in entrambe le tabelle
  • left join, tutti i record presenti nella t1
  • anti join, tutti i record della t1 non presenti nella t2

Un esempio concreto di join tra due tabelle

Il formato generico per l’istruzione di join con DPLYR è del tipo:

typeof_join(t1, t2, by=c(“column-to-join-t1″=”column-to-join-t2”)

Per il nostro esempio utilizziamo come tabella t1 i codici ISO a due e tre digit di 256 nazioni, liberamente scaricabili da https://gist.github.com/tadast/8827699. La tabella contiene codici ISO e descrizione dei singoli paesi e anche le relative coordinate geografiche (latitudine e longitudine). Creiamo invece la tabella t2 partendo dalla pagina wikipedia https://en.wikipedia.org/wiki/List_of_European_countries_by_area che contiene i paesi dell’Europa geografica (include quindi anche nazioni che sono solo parzialmente in Europa come Russia, Turchia etc.) per un totale di 50 occorrenze. t2 è quindi un subset di t1.

Come spesso accade quando i dati provengono da sistemi diversi, non abbiamo un codice ID univoco per la join presente in entrambe le tabelle. Abbiamo però il nome della nazione, che possiamo utilizzare con un po’ di prudenza (non è improbabile che i nomi della stessa nazione possano essere scritti diversamente nelle due fonti dati) per mettere in relazione le due tabelle di partenza.

Inner join

L’operazione più comune è la inner join, che restituisce tutti i record della t1 che sono presenti anche nella t2. Nel nostro caso

# loading t1:   iso country codes 
# Countries with their (ISO 3166-1) Alpha-2 code, 
# Alpha-3 code, UN M49, average latitude and longitude
# from https://gist.github.com/tadast/8827699
# 256 countries and IDs
country_codes <- read_csv("countries_codes_and_coordinates.csv", col_types = cols(`Numeric code` = col_character()))

 loading t2:   european countries
# Country names, areas and notes   
# from https://en.wikipedia.org/wiki/List_of_European_countries_by_area
# converted in xlsx, flags removed
# 49 countries
european_countries <- read_excel("european_countries.xlsx")

#inner jon, all matched records in the two tables 
test_inner_join<-inner_join(country_codes, european_countries, by=c("Country"= "Nation"))

Il risultato della inner join è una tabella contenente tutti i campi delle due t1 e t2, con i soli record comuni a entrambe

E’ da notare che nel nostro caso la join ha restituito solo 39 record (invece che 50) perchè probabilmente il nome (non abbiamo un campo ID per entrambe le tabelle) di alcuni (undici) paesi è scritto diversamente tra t1 e t2. Ne indagheremo ulteriormente il motivo nei prossimi paragrafi.

Left Join

La left Join mostra tutti i record della t1. I campi della t2 sono valorizzati solo in corrispondenza dei match tra t1 e t2, e mostrano NAs negli altri casi. Il campo usato per la join viene mostrato solo per la t1.

#left join, all the records from table 1, null values for unmatched record in table2

test_left_join<-left_join(country_codes, european_countries, by=c("Country"= "Nation"))

Anti join

Con l’anti-join selezioniamo tutti i record in t1 non presenti in t2

#anti-join, records in table 1 unmatched in table 2
test_anti_join<-anti_join(country_codes, european_countries, by=c("Country"= "Nation"))

E’ anche possibile invertire la relazione selezionando tutti i record di tabella 2 che non si trovano in tabella 1:

test_anti_join2<-anti_join(european_countries, country_codes,  by=c("Nation"="Country"))

In particolare vediamo che non vengono ovviamente trovati gli stati che hanno un asterisco (indicante il fatto che la nazione è presente in europa ma anche in altri continenti) e che nella t1 sono invece scritti senza asterisco. Oppure vediamo che non si trova, per esempio, la “Moldova”, che in t1 è scritta come “Moldova, Republic of” e così via. L’anti-join può facilmente diventare uno strumento per l’esplorazione e il cleaning del dataset.

Con queste tre operazioni abbiamo uno strumento potentissimo per fare analisi creando relazioni tra tabelle senza bisogno di utilizzare un RDBMS. Buona analisi.