data.table非等值连接含NA及rank函数排序异常问题求助

问题描述 #

需要对数据集dt$myTime在dt.period$start_prev与dt.period$start的区间内进行排名，后续筛选排名为1的记录，但遇到两个问题：

• 非等值连接得到的dt.Left中出现大量ID为NA的记录，且连接时会覆盖辅助列；
• dt$myTime存在重复时间值时，rank函数的排序结果不符合预期。

原始代码 #

library(data.table)
library(lubridate)

start <- seq(from = dmy_hms("31.12.2013 23:59:59") + 1, to = dmy_hms("31.12.2022 23:59:59") + 1, by = "year") -1
end <- start %m+% months(12)
# 辅助列start_previous：用于确定时间段起始时的最新数据
start_prev <- start %m+% months(-12) 
month_between <- interval(start, end) %/% months(1)
dt.period <- data.table(start_prev, start, end, month_between)

dt <- data.table(ID = c(1, 1, 1, 1, 1)
                      , myTime = c(dmy_hms("15.12.2019 12:03:00"), dmy_hms("15.12.2019 12:03:00"), dmy_hms("18.12.2019 12:03:00")
                                   , dmy_hms("15.03.2020 03:10:00"), dmy_hms("16.03.2020 12:03:00")))

dt.period[, `:=`(z = 1, zstart = start, zstart_prev = start_prev)] 
dt[, `:=`(zmyTime = myTime, z = 1)]

dt.Left <-  
  dt[dt.period, on = .(z, zmyTime > zstart_prev, zmyTime <= zstart)][, `:=` (zmyTime = NULL, z = NULL, zmyTime.1 = NULL)]

dt.Left[, vRankOrder:= rank(order(myTime, decreasing = TRUE)), by = list(ID, start)]
dt.Left[, vSort:= sort(myTime, decreasing = TRUE), by = list(ID, start)]
dt.Left[, vOrder:= order(myTime, decreasing = TRUE), by = list(ID, start)]
dt.Left[, vOrderNumeric := rank(order(as.numeric(as.POSIXct(myTime)), decreasing = TRUE)), by = list(ID, start)] 
dt.Left[, vRankOrderNumneric := rank(order(as.numeric(as.POSIXct(myTime)), decreasing = TRUE)), by = list(ID, start)] 

解决方案 #

问题1：非等值连接产生NA记录及辅助列覆盖 #

原因

• 使用dt[dt.period]属于右连接逻辑，会保留dt.period的所有行，当dt中无匹配区间的记录时，就会生成ID为NA的行；
• 手动创建的辅助列z在连接时会被右表的同名列覆盖，属于冗余操作。

修复方法

• 用nomatch=0参数实现内连接，过滤掉无匹配的行，避免NA记录；
• 直接用myTime与时间段字段做非等值连接，无需额外辅助列。

问题2：重复时间值的排名异常 #

原因

嵌套rank(order(...))的用法错误：order()返回的是排序后的索引位置，再对索引做rank会导致结果混乱，无法正确处理重复值。

修复方法

使用data.table内置的frank()函数（快速排名），通过ties.method参数指定重复值的处理规则：

• ties.method="min"：重复值取最小排名（所有最新的重复记录排名都为1）；
• ties.method="first"：重复值按出现顺序分配不同排名。

修正后完整代码 #

library(data.table)
library(lubridate)

# 生成时间段数据
start <- seq(from = dmy_hms("31.12.2013 23:59:59") + 1, to = dmy_hms("31.12.2022 23:59:59") + 1, by = "year") -1
end <- start %m+% months(12)
start_prev <- start %m+% months(-12) 
month_between <- interval(start, end) %/% months(1)
dt.period <- data.table(start_prev, start, end, month_between)

# 业务数据
dt <- data.table(ID = c(1, 1, 1, 1, 1)
                 , myTime = c(dmy_hms("15.12.2019 12:03:00"), dmy_hms("15.12.2019 12:03:00"), dmy_hms("18.12.2019 12:03:00")
                              , dmy_hms("15.03.2020 03:10:00"), dmy_hms("16.03.2020 12:03:00")))

# 非等值内连接：过滤无匹配行，无冗余辅助列
dt.Left <- dt[dt.period, 
              on = .(myTime > start_prev, myTime <= start),
              nomatch = 0,
              allow.cartesian = TRUE]

# 按ID和时间段分组，对myTime降序排名，重复值取最小排名
dt.Left[, vRank := frank(-myTime, ties.method = "min"), by = .(ID, start)]

# 筛选排名为1的记录
dt.top1 <- dt.Left[vRank == 1]

代码说明 #

• 连接逻辑：直接用myTime与start_prev、start做区间匹配，nomatch=0确保只保留有匹配的行，消除ID为NA的情况；
• 排名逻辑：frank(-myTime)等价于按myTime降序排序，ties.method="min"让所有相同的最新时间记录都获得排名1，满足筛选需求；若需区分重复值，替换为ties.method="first"即可。

内容的提问来源于stack exchange，提问作者Irrational