在R语言中使用sqldf包通过行ID关联数据框以获取指定时间数据的技术咨询

我来帮你搞定这个问题！你想用sqldf把peak_refined里的IDs列和dat的行名匹配，提取对应行的Time值，但卡在了引用dat行名这一步对吧？咱们一步步来解决：

先回顾你的数据和测试代码 #

peak_refined数据结构 #

structure(list(IDs = c(1L, 6L, 28L, 31L, 668L, 2841L, 2862L, 2865L, 2906L, 2917L, 2925L, 2957L, 2960L, 2966L, 2984L, 2993L, 3000L)), class = "data.frame", row.names = c("X1", "X2", "X3", "X4", "X5", "X6", "X7", "X8", "X9", "X10", "X11", "X12", "X13", "X14", "X15", "X16", "X17"))

dat数据结构及Time值 #

structure(list(Time=c("2021-06-18 12:36:00", "2021-06-18 12:38:00", "2021-06-18 12:40:00", "2021-06-18 12:42:00", "2021-06-18 12:44:00", "2021-06-18 12:46:00", "2021-06-18 12:48:00", "2021-06-18 12:50:00", "2021-06-18 12:52:00", "2021-06-18 12:54:00"))

dat的Time值列表：

[1] "2021-06-18 12:36:00" "2021-06-18 12:38:00" "2021-06-18 12:40:00" 
[4] "2021-06-18 12:42:00" "2021-06-18 12:44:00" "2021-06-18 12:46:00" 
[7] "2021-06-18 12:48:00" "2021-06-18 12:50:00" "2021-06-18 12:52:00" 
[10] "2021-06-18 12:54:00"

你的测试代码 #

library(sqldf)
sq <- sqldf("SELECT peak_refined.IDs, dat.row.names FROM peak_refined INNER JOIN dat ON peak_refined.IDs = dat.row.names")

问题根源 #

sqldf是基于SQL语法查询数据框的，而数据框的行名不属于正式列，所以SQL语句里没法直接用dat.row.names来引用。解决思路就是把行名转换成数据框的一个普通列，再进行关联。

具体解决方案 #

步骤1：给dat添加行名列 #

先把dat的行名提取出来，转成整数类型的列（因为peak_refined$IDs是整数，类型一致才能正确匹配）：

# 给dat新增row_id列，存储行名的整数形式
dat$row_id <- as.integer(rownames(dat))

步骤2：用sqldf执行关联查询 #

现在就可以正常用SQL关联两个数据框，提取你需要的IDs和对应的Time值了：

library(sqldf)
# 用INNER JOIN只保留两边匹配成功的记录
result <- sqldf("SELECT pr.IDs, d.Time 
                 FROM peak_refined pr 
                 INNER JOIN dat d ON pr.IDs = d.row_id")

执行后，result里就会包含匹配成功的记录——比如你要的第1条和第6条记录的Time值都会在结果里。

可选：保留peak_refined所有记录 #

如果peak_refined里的IDs有超出dat行范围的（比如示例里的28、31等），INNER JOIN会丢弃这些不匹配的记录。如果想保留peak_refined的所有记录，没有匹配的Time显示NA，可以改用LEFT JOIN：

result <- sqldf("SELECT pr.IDs, d.Time 
                 FROM peak_refined pr 
                 LEFT JOIN dat d ON pr.IDs = d.row_id")

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