R语言左连接返回NA空值：按mrn匹配且日期差≤30合并数据框

问题背景 #

需要将两个数据框按mrn = mrn_G字段匹配，同时满足两个日期差值≤30天的条件执行合并，使用现有代码运行后结果出现大量NA空值，以下为问题成因分析与正确实现方案。

原始数据结构 #

# 第一个数据框
acutedm11 <- structure(list(V1 = 1:29, mdm_link_id = c("sfdiuwe9889w4thehugsdgdf1", "sfdiuwe9889w4thehugsdgdf2", "sfdiuwe9889w4thehugsdgdf3", "sfdiuwe9889w4thehugsdgdf4", "sfdiuwe9889w4thehugsdgdf5", "sfdiuwe9889w4thehugsdgdf6", "sfdiuwe9889w4thehugsdgdf7", "sfdiuwe9889w4thehugsdgdf8", "sfdiuwe9889w4thehugsdgdf9", "sfdiuwe9889w4thehugsdgdf10", "sfdiuwe9889w4thehugsdgdf11", "sfdiuwe9889w4thehugsdgdf12", "sfdiuwe9889w4thehugsdgdf13", "sfdiuwe9889w4thehugsdgdf14", "sfdiuwe9889w4thehugsdgdf15", "sfdiuwe9889w4thehugsdgdf16", "sfdiuwe9889w4thehugsdgdf17", "sfdiuwe9889w4thehugsdgdf18", "sfdiuwe9889w4thehugsdgdf19", "sfdiuwe9889w4thehugsdgdf20", "sfdiuwe9889w4thehugsdgdf21", "sfdiuwe9889w4thehugsdgdf22", "sfdiuwe9889w4thehugsdgdf23", "sfdiuwe9889w4thehugsdgdf24", "sfdiuwe9889w4thehugsdgdf25", "sfdiuwe9889w4thehugsdgdf26", "sfdiuwe9889w4thehugsdgdf27", "sfdiuwe9889w4thehugsdgdf28", "sfdiuwe9889w4thehugsdgdf29"), time0 = c("1/2/2016 22:10", "1/2/2021 0:00", "1/1/2021 1:31", "1/5/2021 3:34", "5/9/2021 5:33", "5/8/2021 3:39", "12/12/2021 2:30", "12/11/2021 9:21", "1/2/2016 22:10", "1/2/2021 0:00", "1/1/2021 1:31", "1/5/2021 3:34", "5/9/2021 5:33", "5/8/2021 3:39", "12/12/2021 2:30", "12/11/2021 9:21", "1/2/2016 22:10", "1/2/2021 0:00", "1/1/2021 1:31", "1/5/2021 3:34", "1/2/2016 22:10", "1/2/2021 0:00", "1/1/2021 1:31", "1/5/2021 3:34", "5/9/2021 5:33", "5/8/2021 3:39", "12/12/2021 2:30", "12/11/2021 9:21", "1/2/2016 22:10"), hr_min_preadmit_vital = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), rr_min_preadmit_vital = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), sbp_min_preadmit_vital = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), `time 00` = c("1/2/2016 22:10", "1/2/2021 0:00", "1/1/2021 1:31", "1/5/2021 3:34", "5/9/2021 5:33", "5/8/2021 3:39", "12/12/2021 2:30", "12/11/2021 9:21", "1/2/2016 22:10", "1/2/2021 0:00", "1/1/2021 1:31", "1/5/2021 3:34", "5/9/2021 5:33", "5/8/2021 3:39", "12/12/2021 2:30", "12/11/2021 9:21", "1/2/2016 22:10", "1/2/2021 0:00", "1/1/2021 1:31", "1/5/2021 3:34", "1/2/2016 22:10", "1/2/2021 0:00", "1/1/2021 1:31", "1/5/2021 3:34", "5/9/2021 5:33", "5/8/2021 3:39", "12/12/2021 2:30", "12/11/2021 9:21", "1/2/2016 22:10"), mrn = 100:128, Date = structure(c(1451692800, 1609545600, 1609459200, 1609804800, 1620518400, 1620432000, 1639267200, 1639180800, 1451692800, 1609545600, 1609459200, 1609804800, 1620518400, 1620432000, 1639267200, 1639180800, 1451692800, 1609545600, 1609459200, 1609804800, 1451692800, 1609545600, 1609459200, 1609804800, 1620518400, 1620432000, 1639267200, 1639180800, 1451692800), class = c("POSIXct", "POSIXt"), tzone = "UTC"), Date_m30 = structure(c(1449100800, 1606953600, 1606867200, 1607212800, 1617926400, 1617840000, 1636675200, 1636588800, 1449100800, 1606953600, 1606867200, 1607212800, 1617926400, 1617840000, 1636675200, 1636588800, 1449100800, 1606953600, 1606867200, 1607212800, 1449100800, 1606953600, 1606867200, 1607212800, 1617926400, 1617840000, 1636675200, 1636588800, 1449100800), class = c("POSIXct", "POSIXt"), tzone = "UTC"), Date_p30 = structure(c(1454284800, 1612137600, 1612051200, 1612396800, 1623110400, 1623024000, 1641859200, 1641772800, 1454284800, 1612137600, 1612051200, 1612396800, 1623110400, 1623024000, 1641859200, 1641772800, 1454284800, 1612137600, 1612051200, 1612396800, 1454284800, 1612137600, 1612051200, 1612396800, 1623110400, 1623024000, 1641859200, 1641772800, 1454284800), class = c("POSIXct", "POSIXt"), tzone = "UTC")), row.names = c(NA, -29L), class = c("tbl_df", "tbl", "data.frame"))

# 第二个数据框
gwlfullflattened22 <- structure(list(V1_G = 1:20, mrn_G = 100:119, Full.name_G = c("Aintzane Eilert", "Dervila Muriel", "Hermes Ingólfr", "Yordana Hadley", "Talaat Archembald", "Erato Hozan", "Abram Eli", "Drahoslava Gottfrid", "Itxaro Csenge", "Isokrates Linas", "Yejide Calixto", "Bohuslav Fedlimid", "Siva Jerneja", "Mae Albie", "Rodolfo Slavomír", "Neptune Mahesh", "Madhavi Luka", "Lexia Lành", "Marnie Urien", "Hovsep Tase"), date_of_birth_G = c("1/1/1990", "1/1/1991", "1/1/1992", "1/1/1993", "1/1/1994", "1/1/1995", "1/1/1996", "1/1/1997", "1/1/1998", "1/1/1999", "1/1/2000", "1/1/2001", "1/1/2002", "1/1/2003", "1/1/2004", "1/1/2005", "1/1/2006", "1/1/2007", "1/1/2008", "1/1/2009"), EncounterDate_G = structure(c(16805, 18631, 18648, 18772, 18766, 18764, 18963, 18962, 16805, 18631, 18648, 18772, 18766, 18764, 18963, 18962, 16805, 18631, 18648, 18772), class = "Date")), row.names = c(NA, -20L), class = c("tbl_df", "tbl", "data.frame"))

原报错代码 #

library(sqldf)

acutedm3 <- sqldf::sqldf("
    select acutedm11.*, gwlfullflattened2.*
    from acutedm11
       left join gwlfullflattened2 on acutedm11.mrn = gwlfullflattened2.mrn_G
        and gwlfullflattened2.EncounterDate_G between acutedm11.Date_m30 and acutedm11.Date_p30") %>%
  select(-Date_m30, -Date_p30)

NA空值成因 #

• 表名笔误：代码中关联的表名是gwlfullflattened2，但实际定义的表名是gwlfullflattened22，少了末尾的数字2，sqldf无法识别不存在的表，所有匹配值自然返回NA。
• 日期类型不兼容：acutedm11中的Date_m30、Date_p30是POSIXct时间戳类型，gwlfullflattened22中的EncounterDate_G是Date类型，两类日期格式在SQL逻辑中隐式转换时会出现错位，导致日期范围判断全部失效。
• 匹配范围天然缺失：acutedm11的mrn范围是100_{128共29条记录，`gwlfullflattened22`的mrn_G只有100}119共20条记录，120~128的mrn没有对应匹配项，左连接后这部分记录的关联字段必然为NA。

正确合并方案 #

方案1：修正sqldf代码逻辑 #

先统一两个表的日期格式，再修正表名即可正常匹配：

library(sqldf)
library(dplyr)

# 统一日期类型为Date格式
acutedm11 <- acutedm11 %>% 
  mutate(
    Date_m30 = as.Date(Date_m30),
    Date_p30 = as.Date(Date_p30)
  )

# 修正表名执行匹配，如需全连接将LEFT JOIN改为FULL OUTER JOIN即可
acutedm3 <- sqldf("
    SELECT a.*, g.*
    FROM acutedm11 a
    LEFT JOIN gwlfullflattened22 g 
    ON a.mrn = g.mrn_G
    AND g.EncounterDate_G BETWEEN a.Date_m30 AND a.Date_p30
")