处理时间序列中的缺失值 [英] Handling missing values in time series
问题描述
我正在处理时间序列数据，我需要有连续的时间戳，但在捕获时几乎没有丢失数据时间戳点，如下所示，
I am dealing with time series data where I need to have continuous time stamps but few of the data timestamp points has been missed while capturing like as below,
DF
ID Time_Stamp A B C
1 02/02/2018 07:45:00 123 567 434
2 02/02/2018 07:45:01
..... ...
5 02/02/2018 07:46:00
6 02/02/2018 07:46:10 112 2323 2323
如上面的示例df所示，时间戳一直持续到row 5，但在之间错过了.我的数据框大约 10秒的捕获数据第 5 行和第 6 行60000 行，手动识别缺失值很乏味.因此，我正在寻找使用 R 自动处理缺失值的过程
As shown in the sample df above, time stamps is continuous till row 5 but missed capturing data of 10 seconds between 5th and 6th row. My data frame is about 60000 rows and identifying missing values manually is tedious.
Hence I was looking for automating the procedure of handling missing values using R
我的结果数据框如下，
ID Time_Stamp A B C
1 02/02/2018 07:45:00 123 567 434
2 02/02/2018 07:45:01
..... ...
5 02/02/2018 07:46:00 mean(A)
5.1 02/02/2018 07:46:01 mean(A) mean(b) mean(c)
5.2 02/02/2018 07:46:02 mean(A) mean(b) mean(c)
5.3 02/02/2018 07:46:03 mean(A) mean(b) mean(c)
5.4 02/02/2018 07:46:04 mean(A) mean(b) mean(c)
5.5 02/02/2018 07:46:05 mean(A) mean(b) mean(c)
5.6 02/02/2018 07:46:06 mean(A) mean(b) mean(c)
5.7 02/02/2018 07:46:07 mean(A) mean(b) mean(c)
5.8 02/02/2018 07:46:08 mean(A) mean(b) mean(c)
5.9 02/02/2018 07:46:09 mean(A) mean(b) mean(c)
6 02/02/2018 07:46:10 112 2323 2323
请帮助！
推荐答案
最好有一个具体的例子来展示具体的预期输出，这样就不会有歧义和假设的空间.但是，我根据自己的理解创建了一个虚拟数据并尝试相应地解决它.
It is always better to have a specific example showing specific expected output so that there is little space for ambiguity and assumption. However, I have created a dummy data based on my understanding and tried to solve it accordingly.
如果我对你的理解正确的话，你的时间序列数据每秒都有一个数据点，但有时你想用该列的 mean 填充它.
If I have understood you correctly, you have time series data with data point every second but sometimes there are some seconds missing which you want to fill it with mean of that column.
我们可以使用 complete 通过在 min 和 max Time_Stamp 之间生成一个序列来实现这一点并通过相应列中的 mean 填充缺失值.ID 看起来像是每一行的唯一标识符，所以用 row_number() 填充它.
We can achieve this using complete by generating a sequence for every second between the min and max Time_Stamp and fill the missing values by the mean in the respective column. ID looks like an unique identifier for each row so filled it with row_number().
library(dplyr)
library(tidyr)
df %>%
complete(Time_Stamp = seq(min(Time_Stamp), max(Time_Stamp), by = "sec")) %>%
mutate_at(vars(A:C), ~replace(., is.na(.), mean(., na.rm = TRUE))) %>%
mutate(ID = row_number())
# A tibble: 11 x 5
# Time_Stamp ID A B C
# <dttm> <int> <dbl> <dbl> <dbl>
# 1 2018-02-02 07:45:00 1 123 567 434
# 2 2018-02-02 07:45:01 2 234 100 110
# 3 2018-02-02 07:45:02 3 234 100 110
# 4 2018-02-02 07:45:03 4 176. 772. 744.
# 5 2018-02-02 07:45:04 5 176. 772. 744.
# 6 2018-02-02 07:45:05 6 176. 772. 744.
# 7 2018-02-02 07:45:06 7 176. 772. 744.
# 8 2018-02-02 07:45:07 8 176. 772. 744.
# 9 2018-02-02 07:45:08 9 176. 772. 744.
#10 2018-02-02 07:45:09 10 176. 772. 744.
#11 2018-02-02 07:45:10 11 112 2323 2323
如果您检查最后 3 列的列均值，您可以看到这些值已被准确替换.
If you check the column means for last 3 columns, you can see those value are accurately replaced.
colMeans(df[3:5])
# A B C
#175.75 772.50 744.25
数据
df <- structure(list(ID = 1:4, Time_Stamp = structure(c(1517557500,
1517557501, 1517557502, 1517557510), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), A = c(123L, 234L, 234L, 112L), B = c(567L,
100L, 100L, 2323L), C = c(434L, 110L, 110L, 2323L)), class = "data.frame",
row.names = c(NA, -4L))
看起来像
df
# ID Time_Stamp A B C
#1 1 2018-02-02 07:45:00 123 567 434
#2 2 2018-02-02 07:45:01 234 100 110
#3 3 2018-02-02 07:45:02 234 100 110
#4 4 2018-02-02 07:45:10 112 2323 2323
这篇关于处理时间序列中的缺失值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
