复杂键

在 R 中使用 data.table 进行数据表连接

Scott Ritchie

Postdoctoral Researcher in Systems Genomics

错误指定的连接

当未使用正确列作为连接键时会怎样？

抛出错误
结果是畸形的 data.table

列类型不匹配

在连接键列类型不一致时会报错

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)

列类型不匹配

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)

全连接错误——无共同键值

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

右/左连接错误——无共同键值

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

内连接错误——无共同键值

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

连接错误——偶然的共同键值

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

避免错误指定的连接

在连接前了解每列含义，可避免出错

不同列名的键

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]

多列键

在 merge() 中指定多个键

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

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

使用 data.table 语法指定多个键

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")]

结束幻灯片

在 R 中使用 data.table 进行数据表连接