R:在csv中查找丢失的时间戳

Question

由于数据大小导致PHP/MySQL或Excel无法解决我的问题，我现在正尝试使用R来完成我的第一步，并且有点挣扎。问题是:我有一个连续半年的CSV文件，看起来是这样的：

metering,timestamp
123,2016-01-01 00:00:00
345,2016-01-01 00:00:01
243,2016-01-01 00:00:02
101,2016-01-01 00:00:04
134,2016-01-01 00:00:06

正如您所看到的，每隔一段时间就会有一些秒的丢失(不要问我，为什么值是在时间戳之前写的，但这就是我如何接收数据…的)。现在，我尝试计算丢失的值的数量(=秒)。

所以我的想法是

1. 若要创建正确的向量(包括所有的逐秒时间戳)，
2. 将给定的CSV文件与该新向量匹配，并且
3. 总结所有没有价值的时间戳。

我设法用以下代码实现了第1步：

RegularTimeSeries <- seq(as.POSIXct("2016-01-01 00:00:00", tz = "UTC"), as.POSIXct("2016-01-01 00:00:30", tz = "UTC"), by = "1 sec")  
write.csv(RegularTimeSeries, file = "RegularTimeSeries.csv")

为了了解我所做的事情，我还将向量导出到CSV，如下所示：

"1",2016-01-01 00:00:00
"2",2016-01-01 00:00:01
"3",2016-01-01 00:00:02
"4",2016-01-01 00:00:03
"5",2016-01-01 00:00:04
"6",2016-01-01 00:00:05
"7",2016-01-01 00:00:06

不幸的是，我不知道如何继续执行第2步和第3步，我发现了一些非常类似的例子(http://www.r-bloggers.com/fix-missing-dates-with-r/，R: Insert rows for missing dates/times)，但是作为一个总体的R，我很难将这些示例转换成我给定的逐秒数据。

一些关于温室效应的提示将非常有用--非常感谢你:)

Answer 1

在潮间带，

library(dplyr)
library(tidyr)

       # parse datetimes
df %>% mutate(timestamp = as.POSIXct(timestamp)) %>% 
    # complete sequence to full sequence from min to max by second
    complete(timestamp = seq.POSIXt(min(timestamp), max(timestamp), by = 'sec'))

## # A tibble: 7 x 2
##             timestamp metering
##                <time>    <int>
## 1 2016-01-01 00:00:00      123
## 2 2016-01-01 00:00:01      345
## 3 2016-01-01 00:00:02      243
## 4 2016-01-01 00:00:03       NA
## 5 2016-01-01 00:00:04      101
## 6 2016-01-01 00:00:05       NA
## 7 2016-01-01 00:00:06      134

如果您想要NAs的数量(即没有数据的秒数)，请添加

%>% tally(is.na(metering))

## # A tibble: 1 x 1
##       n
##   <int>
## 1     2

Answer 2

您可以使用RegularTimeSeries和%in%来检查您的时间序列中有哪些值。首先从您的示例创建BrokenTimeSeries：

RegularTimeSeries <- seq(as.POSIXct("2016-01-01 00:00:00", tz = "UTC"), as.POSIXct("2016-01-01 00:00:30", tz = "UTC"), by = "1 sec")
BrokenTimeSeries <- RegularTimeSeries[-c(3,6,9)] # remove some seconds

这将提供RegularTimeSeries中不存在于BrokenTimeSeries中的值的识别码。

> which(!(RegularTimeSeries %in% BrokenTimeSeries))
[1] 3 6 9

这将返回实际值：

> RegularTimeSeries[which(!(RegularTimeSeries %in% BrokenTimeSeries))]
[1] "2016-01-01 00:00:02 UTC" "2016-01-01 00:00:05 UTC" "2016-01-01 00:00:08 UTC"

也许我误解了你的问题，但你可以计算出丢失的秒数，只需从length中减去破碎时间序列的RegularTimeSeries，或者得到上面两个向量中任意一个的长度。

> length(RegularTimeSeries) - length(BrokenTimeSeries)
[1] 3
> length(which(!(RegularTimeSeries %in% BrokenTimeSeries)))
[1] 3
> length(RegularTimeSeries[which(!(RegularTimeSeries %in% BrokenTimeSeries))])
[1] 3

如果要合并文件以查看缺少的值，可以执行以下操作：

#data with regular time series and a "step"
df <- data.frame(
  RegularTimeSeries
)

df$BrokenTimeSeries[RegularTimeSeries %in% BrokenTimeSeries] <- df$RegularTimeSeries
df$BrokenTimeSeries <- as.POSIXct(df$BrokenTimeSeries, origin="2015-01-01", tz="UTC")

其结果是：

> df[1:12,]
     RegularTimeSeries    BrokenTimeSeries
1  2016-01-01 00:00:00 2016-01-01 00:00:00
2  2016-01-01 00:00:01 2016-01-01 00:00:01
3  2016-01-01 00:00:02                <NA>
4  2016-01-01 00:00:03 2016-01-01 00:00:02
5  2016-01-01 00:00:04 2016-01-01 00:00:03
6  2016-01-01 00:00:05                <NA>
7  2016-01-01 00:00:06 2016-01-01 00:00:04
8  2016-01-01 00:00:07 2016-01-01 00:00:05
9  2016-01-01 00:00:08                <NA>
10 2016-01-01 00:00:09 2016-01-01 00:00:06
11 2016-01-01 00:00:10 2016-01-01 00:00:07
12 2016-01-01 00:00:11 2016-01-01 00:00:08

Answer 3

希望它能帮上忙

d <- (c("2016-01-01 00:00:01",
"2016-01-01 00:00:02",
"2016-01-01 00:00:03",
"2016-01-01 00:00:04",
"2016-01-01 00:00:05",
"2016-01-01 00:00:06",
"2016-01-01 00:00:10",
"2016-01-01 00:00:12",
"2016-01-01 00:00:14",
"2016-01-01 00:00:16",
"2016-01-01 00:00:18",
"2016-01-01 00:00:20",
"2016-01-01 00:00:22"))
d <- as.POSIXct(d)

for (i in 2:length(d)){
  if(difftime(d[i-1],d[i], units = "secs") < -1 ){
    c[i] <- d[i]
  }
  }
 class(c) <- c('POSIXt','POSIXct')
 c
 [1] NA                        NA                        NA                       
 NA                        NA                       
[6] NA                        "2016-01-01 00:00:10 EST" "2016-01-01 00:00:12    
EST" "2016-01-01 00:00:14 EST" "2016-01-01 00:00:16 EST"
[11] "2016-01-01 00:00:18 EST" "2016-01-01 00:00:20 EST" "2016-01-01    
00:00:22 EST"