首页 文章

根据时间顺序子集数据帧

提问于
浏览 次
0

我有一个名为DF的数据框，列有时间和日期 . 我想根据这些列中的值对DF进行子集化 . 对于日期，我有DATES中的日期列表，并且在DATES中存在DF $ Date的DF行的子集 . 当时，我想从00:04:00到00:06:00进行分组 . 我不知道怎么做后者 .

理想情况下，我希望通过指定范围来进行子集，如00:04:00到00:06:00，以及指定前瞻的起点和分钟，如00:04:00和3分钟（两种不同的方法） . 我想这一切都归结为制作一段时间，并将这样的序列放在一个单独的向量中，然后我可以用它进行匹配 .

请注意，这只是一个简短的可重现的例子 . 我正在寻找一种通用的方法来实现这一点，因为在实践中我想要分配大量的时间 . 另请注意，即使在此示例中只有一个匹配日期，实际上会有多个匹配日期跨越多年 . 这就是为什么我认为不可能使用 POSIXlt 来制作时间序列 . 非常感谢你 .

#DF looks like this:
  #               DateTime  XXX      Time      Date
  #1371 2016-04-25 00:08:00  14 00:08:00 2016-04-25
  #1372 2016-04-25 00:07:00  13 00:07:00 2016-04-25
  #1373 2016-04-25 00:06:00  14 00:06:00 2016-04-25
  #1374 2016-04-25 00:05:00   3 00:05:00 2016-04-25
  #1375 2016-04-25 00:04:00   2 00:04:00 2016-04-25
  #1376 2016-04-25 00:03:00   4 00:03:00 2016-04-25
  #1377 2016-04-25 00:02:00   6 00:02:00 2016-04-25
  #1387 2016-04-24 23:52:00  41 23:52:00 2016-04-24
  #1388 2016-04-24 23:51:00  93 23:51:00 2016-04-24
  #1389 2016-04-24 23:50:00  53 23:50:00 2016-04-24

  #Code for DF, DATES, and to subset DF based on DATES
  DF <- structure(list(DateTime = structure(list(sec = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), min = c(8L, 7L, 6L, 5L, 4L, 3L, 2L, 1L, 0L, 59L, 58L, 57L, 56L, 55L, 54L, 53L, 52L, 51L, 50L), hour = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 23L, 23L, 23L, 23L, 23L, 23L, 23L, 23L, 23L, 23L), mday = c(25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 25L, 24L, 24L, 24L, 24L, 24L, 24L, 24L, 24L, 24L, 24L), mon = c(3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), year = c(116L, 116L, 116L, 116L, 116L, 116L, 116L, 116L, 116L, 116L, 116L, 116L, 116L, 116L, 116L, 116L, 116L, 116L, 116L), wday = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), yday = c(115L, 115L, 115L, 115L, 115L, 115L, 115L, 115L, 115L, 114L, 114L, 114L, 114L, 114L, 114L, 114L, 114L, 114L, 114L), isdst = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), zone = c("EDT", "EDT", "EDT", "EDT", "EDT", "EDT", "EDT", "EDT", "EDT", "EDT", "EDT", "EDT", "EDT", "EDT", "EDT", "EDT", "EDT", "EDT", "EDT"), gmtoff = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_)), .Names = c("sec", "min", "hour", "mday", "mon", "year", "wday", "yday", "isdst", "zone", "gmtoff"), class = c("POSIXlt", "POSIXt")), Open = c(14, 13, 14, 3, 2, 4, 6, 4, 15, 15, 23, 24, 33, 14, 65, 54, 41, 93, 53), Time = c("00:08:00", "00:07:00", "00:06:00", "00:05:00", "00:04:00", "00:03:00", "00:02:00", "00:01:00", "00:00:00", "23:59:00", "23:58:00", "23:57:00", "23:56:00", "23:55:00", "23:54:00", "23:53:00", "23:52:00", "23:51:00", "23:50:00"), Date = structure(c(16916, 16916, 16916, 16916, 16916, 16916, 16916, 16916, 16916, 16915, 16915, 16915, 16915, 16915, 16915, 16915, 16915, 16915, 16915), class = "Date")), .Names = c("DateTime", "XXX", "Time", "Date"), row.names = c("1371", "1372", "1373", "1374", "1375", "1376", "1377", "1378", "1379", "1380", "1381", "1382", "1383", "1384", "1385", "1386", "1387", "1388", "1389"), class = "data.frame")
  DATES <- structure(c(12431, 12432, 10445, 10480, 11487, 12494, 12501, 12508, 13115, 13522, 14529, 15536, 16916, 16935), class = "Date")
  SELEC <- DF[DF$Date %in% DATES,]

  #Result of subsetting by Date:
  #                 DateTime XXX     Time       Date
  # 1371 2016-04-25 00:08:00  14 00:08:00 2016-04-25
  # 1372 2016-04-25 00:07:00  13 00:07:00 2016-04-25
  # 1373 2016-04-25 00:06:00  14 00:06:00 2016-04-25
  # 1374 2016-04-25 00:05:00   3 00:05:00 2016-04-25
  # 1375 2016-04-25 00:04:00   2 00:04:00 2016-04-25
  # 1376 2016-04-25 00:03:00   4 00:03:00 2016-04-25
  # 1377 2016-04-25 00:02:00   6 00:02:00 2016-04-25
  # 1378 2016-04-25 00:01:00   4 00:01:00 2016-04-25
  # 1379 2016-04-25 00:00:00  15 00:00:00 2016-04-25

  #How the final product would look like if using a larger data base spanning many years:
  #           DateTime XXX     Time       Date
  #2016-04-25 00:06:00  13 00:06:00 2016-04-25
  #2016-04-25 00:05:00  14 00:05:00 2016-04-25
  #2016-04-25 00:04:00   3 00:04:00 2016-04-25
  #2014-03-11 00:06:00  94 00:06:00 2014-03-11
  #2014-03-11 00:05:00   6 00:05:00 2014-03-11
  #2014-03-11 00:04:00  14 00:04:00 2014-03-11
  #2011-08-06 00:06:00  13 00:06:00 2011-08-06
  #2011-08-06 00:05:00  19 00:05:00 2011-08-06
  #2011-08-06 00:04:00  41 00:04:00 2011-08-06
r time sequence subset

2 回答

  • 0

    Possibility 1: lexicographic comparison

    如果所有时间值都存储为具有相同分隔符的零填充24小时字符串，例如 %H:%M:%S ，则可以使用词典比较来应用过滤器 .

    DF[DF$Date%in%DATES & DF$Time>='00:04:00' & DF$Time<='00:06:00',];
##                 DateTime XXX     Time       Date
## 1373 2016-04-25 00:06:00  14 00:06:00 2016-04-25
## 1374 2016-04-25 00:05:00   3 00:05:00 2016-04-25
## 1375 2016-04-25 00:04:00   2 00:04:00 2016-04-25

    当然，词典解决方案并不理想，因为它们不适合基于时间的数学运算，例如加，减，乘，除等 .

    更好的解决方案涉及将时间值转换为数字类型，该数字类型将持续时间编码为显式或未指定基准时间的偏移量 . 这是流行的日期/时间库编码类型的方式，例如boost date_time表示C，Joda-Time表示Java，POSIXct表示difftime，而Rubridate表示R.

    Possibility 2: manual numerics

    可以自己解析字符串以构造表示持续时间的数字，并使用数字比较来应用过滤器 .

    hmsToDouble <- function(hms) as.double(substr(hms,1,2))*3600 + as.double(substr(hms,4,5))*60 + as.double(substr(hms,7,8));
DF[DF$Date%in%DATES & hmsToDouble(DF$Time)>=hmsToDouble('00:04:00') & hmsToDouble(DF$Time)<=hmsToDouble('00:06:00'),];
##                 DateTime XXX     Time       Date
## 1373 2016-04-25 00:06:00  14 00:06:00 2016-04-25
## 1374 2016-04-25 00:05:00   3 00:05:00 2016-04-25
## 1375 2016-04-25 00:04:00   2 00:04:00 2016-04-25

    Possibility 3: POSIXt

    我们可以生成POSIXt（即POSIXct或POSIXlt）值的向量，并对这些向量使用矢量化比较 .

    DF[DF$Date%in%DATES & DF$DateTime>=as.POSIXct(paste0(DF$Date,' 00:04:00')) & DF$DateTime<=as.POSIXct(paste0(DF$Date,' 00:06:00')),];
##                 DateTime XXX     Time       Date
## 1373 2016-04-25 00:06:00  14 00:06:00 2016-04-25
## 1374 2016-04-25 00:05:00   3 00:05:00 2016-04-25
## 1375 2016-04-25 00:04:00   2 00:04:00 2016-04-25

    Possibility 4: difftime

    R中唯一的内置持续时间数据类型是difftime类型，使用它可能有点挑剔 . 但对于这个问题，它相当简单 .

    DF[DF$Date%in%DATES & as.difftime(DF$Time)>=as.difftime('00:04:00') & as.difftime(DF$Time)<=as.difftime('00:06:00'),];
##                 DateTime XXX     Time       Date
## 1373 2016-04-25 00:06:00  14 00:06:00 2016-04-25
## 1374 2016-04-25 00:05:00   3 00:05:00 2016-04-25
## 1375 2016-04-25 00:04:00   2 00:04:00 2016-04-25

    Possibility 5: lubridate

    lubridate包被广泛认为是R中日期/时间处理的最佳包 . 它提供表示常规持续时间的持续时间类型，以及允许表示各种不规则时间单位计数的句点类型 . 从历史上看，日期/时间库有时会失败，因为他们缺乏对不规则时段和常规时段之间区别的认识 .

    在以下解决方案中， hms() 调用返回句点类型的实例，因此我们实际上是在比较不同的时间单位 . 顺便提一下，关于单独时间单位的实际存储，lubridate的设计是将秒值存储为双向量的实际有效载荷，其余单位（分钟，小时，天，月和年）作为属性存储物体 .

    library(lubridate);
DF[DF$Date%in%DATES & hms(DF$Time)>=hms('00:04:00') & hms(DF$Time)<=hms('00:06:00'),];
##                 DateTime XXX     Time       Date
## 1373 2016-04-25 00:06:00  14 00:06:00 2016-04-25
## 1374 2016-04-25 00:05:00   3 00:05:00 2016-04-25
## 1375 2016-04-25 00:04:00   2 00:04:00 2016-04-25
    回复于
  • 0

    这个怎么样？

    DF$Time <- strptime(DF$Time,format = '%H:%M:%S')

timeCondition <- (DF$Time >= strptime('00:04:00',format = '%H:%M:%S')) & (DF$Time <= strptime('00:06:00',format = '%H:%M:%S'))

SELEC <- DF[timeCondition & DF$Date %in% DATES,]

    这使：

    DateTime XXX                Time       Date
1373 2016-04-25 00:06:00  14 2016-05-14 00:06:00 2016-04-25
1374 2016-04-25 00:05:00   3 2016-05-14 00:05:00 2016-04-25
1375 2016-04-25 00:04:00   2 2016-05-14 00:04:00 2016-04-25
    回复于

相关问题