两个具有不同时间戳和不同数据点数量的时间序列数据 [英] Two Time series data with different time stamps and a different number of data points

问题描述

我有两个时间序列数据，它们具有不同的时间戳和不同数量的数据点.

I have 2-time series data with different time stamps and a different number of data points.

第一个数据框是:

      Time         Power_kW
10/9/2017 1:14:12   0.185
10/9/2017 1:14:53   0.182
10/9/2017 1:15:13   0.184
10/9/2017 1:15:53   0.175
. . 
. . 
10/9/2017 1:44:37   0.175
. . 
. . 
10/9/2017 2:13:38   0.181
. . 
. . 
10/9/2017 2:24:40   0.179

第二个数据框是:

    Local Time    Value
10/9/2017 1:13:01   0
10/9/2017 1:42:10   1
10/9/2017 2:11:58   0
10/9/2017 2:23:30   1

第二个数据框值表示提到的两个日期之间的值.这意味着0"的值应该从 10/9/2017 1:13:01 0 传播到 10/9/2017 1:42:10.以及从 10/9/2017 1:42:10 到 10/9/2017 2:23:30 等日期的值 1.

The second data frame values indicate the value in between 2 dates mentioned. It means the value of '0' should be propagated from 10/9/2017 1:13:01 0 to 10/9/2017 1:42:10. and the value 1 from the date of 10/9/2017 1:42:10 up to 10/9/2017 2:23:30 and so on.

我希望通过合并这两个数据框列值来获得与第一个数据框相同数量的数据点.我有许多与第二个数据框相似的数据框，它们都将帮助我与时间相关联.

I wish to have the same number of data points as the first data frame by merging these two data frames columns values. I have many similar data frames as the Second dataframe which all will help me making correlations with the respect to the time.

我试图在合并后得到以下结果:

I am trying to get the following result after merging:

   Time            Power_kW Value
10/9/2017 1:14:12   0.185   0
10/9/2017 1:14:53   0.182   0
10/9/2017 1:15:13   0.184   0
10/9/2017 1:15:53   0.175   0
. .     
. .     
10/9/2017 1:44:37   0.175   1
10/9/2017 1:45:47   0.176   1
. .     
10/9/2017 2:13:38   0.181   0
. .     
. .     
10/9/2017 2:24:40   0.179   1

(我在结果中添加了一些点来表示整个数据应该是什么样子.)

(I have added some points in the result to represent how the whole data should look like.)

我确实参考了这些线程，但它们的目的并未传播.

I did refer to these threads and their purpose are not propagating through.

R:合并两个不规则时间序列

合并两个不同时间粒度的时间序列

谁能给我一盏灯?

推荐答案

尝试使用 R 的 data.table 包，很好地解释了 在本博客中:

library(data.table)
df1 <- fread("Time,Power_kW
10/9/2017 1:14:12,0.185
10/9/2017 1:14:53,0.182
10/9/2017 1:15:13,0.184
10/9/2017 1:15:53,0.175
10/9/2017 1:44:37,0.175
10/9/2017 1:45:47,0.176 
10/9/2017 2:13:38,0.181
10/9/2017 2:24:40,0.179")
df2 <- fread("LocalTime,Value
10/9/2017 1:13:01,0
10/9/2017 1:42:10,1
10/9/2017 2:11:58,0
10/9/2017 2:23:30,1")
df1$Time <- as.POSIXct(df1$Time, format="%m/%d/%Y %T")
df2$LocalTime <- as.POSIXct(df2$LocalTime, format="%m/%d/%Y %T")
setkey(df1, Time)
setkey(df2, LocalTime)
df2[df1, roll=Inf]
# LocalTime Value Power_kW
# 1: 2017-10-09 01:14:12     0    0.185
# 2: 2017-10-09 01:14:53     0    0.182
# 3: 2017-10-09 01:15:13     0    0.184
# 4: 2017-10-09 01:15:53     0    0.175
# 5: 2017-10-09 01:44:37     1    0.175
# 6: 2017-10-09 01:45:47     1    0.176
# 7: 2017-10-09 02:13:38     0    0.181
# 8: 2017-10-09 02:24:40     1    0.179

这篇关于两个具有不同时间戳和不同数据点数量的时间序列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！