Komplexa nycklar

Att sammanfoga data med data.table i R

Scott Ritchie

Postdoctoral Researcher in Systems Genomics

Felaktigt specificerade joins

Vad händer när du inte använder rätt kolumner som joinnycklar?

Ett fel uppstår
Resultatet är en felaktig data.table

Kolumntypsmatchning saknas

Att använda joinnycklar med olika datatyper ger ett fel

customers[web_visits, on = .(age = name)]

Error in bmerge(i, x, leftcols, rightcols, io, xo, roll, rollends, 
nomatch,  : 
  typeof x.age (double) != typeof i.name (character)

Kolumntypsmatchning saknas

customers[web_visits, on = .(id)]

Error in bmerge(i, x, leftcols, rightcols, io, xo, roll, rollends, 
nomatch,  : 
  typeof x.id (integer) != typeof i.id(character)

Felaktig full join – inga gemensamma nyckelvärden

merge(customers, web_visits, by.x = "address", by.y = "name", all = TRUE)

Felaktig höger- och vänsterjoin – inga gemensamma nyckelvärden

customers[web_visits, on = .(address = name)]

Felaktig inner join – inga gemensamma nyckelvärden

customers[web_visits, on = .(address = name), nomatch = 0]

Felaktig join – tillfälliga gemensamma nyckelvärden

customers[web_visits, on = .(age = duration), nomatch = O]

Undvika felaktigt specificerade joins

Lär dig vad varje kolumn representerar innan du joinnar – det hjälper dig att undvika fel

Nycklar med olika kolumnnamn

merge(customers, web_visits, by.x = "name", by.y = "person")

customers[web_visits, on = .(name = person)]
customers[web_visits, on = c("name" = "person")]
key <- c("name" = "person")
customers[web_visits, on = key]

Flerkolumnsnycklar

Ange flera nycklar med merge()

merge(purchases, web_visits, by = c("name", "date"))

merge(purchases, web_visits, 
      by.x = c("name", "date"),  
      by.y = c("person", "date")

Ange flera nycklar med data.table-syntaxen

purchases[web_visits, on = .(name, date)]
purchases[web_visits, on = c("name", "date")]

purchases[web_visits, on = .(name = person, date)]
purchases[web_visits, on = c("name" = "person", "date")]

Sista slide

Att sammanfoga data med data.table i R