Handling missing values in time series in r

Admin 1
Viewed 155

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

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

My result data frame is as below,

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(A1:A5)
5.1  02/02/2018 07:46:01        mean(A1:A5)  mean(B1:B5)         mean(C1:C5)
5.2  02/02/2018 07:46:02        mean(A1:A5)  mean(B1:B5)         mean(C1:C5) 
5.3  02/02/2018 07:46:03        mean(A1:A5)  mean(B1:B5)         mean(C1:C5) 
5.4  02/02/2018 07:46:04        mean(A1:A5)  mean(B1:B5)         mean(C1:C5)
5.5  02/02/2018 07:46:05        mean(A1:A5)  mean(B1:B5)         mean(C1:C5)
5.6  02/02/2018 07:46:06        mean(A1:A5)  mean(B1:B5)         mean(C1:C5)
5.7  02/02/2018 07:46:07        mean(A1:A5)  mean(B1:B5)         mean(C1:C5)
5.8  02/02/2018 07:46:08        mean(A1:A5)  mean(B1:B5)         mean(C1:C5)
5.9  02/02/2018 07:46:09        mean(A1:A5)  mean(B1:B5)         mean(C1:C5)
6   02/02/2018 07:46:10         112         2323            2323
6.1 02/02/2018 07:46:11         mean(A1:A15) mean(B1:B15)       mean(C1:C15)

Or even it can be the mean of previous rows in that time interval.

 6.1 02/02/2018 07:46:11         mean(A14:A17) mean(B14:B17)      mean(C14:C17)

I.e missing except missing time values .

I have done following code to get mean of whole column.

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())

It gives output for all mean of all the row in the column .

Like following this code It worked perfect but i need this modification . How can do it. kindly Help

time-series r
2 Answers

There's a very intuitive package made exactly for this purpose, called "padr". I think you'll find it serves your needs: cran padr vignette

user_31990760

Here is a combination of tidyverse and base R method to achieve the result. We first create a new column with cumulative mean values for each column. We then complete the missing observations and replace the NAs with respective means from other columns. 

library(tidyverse)
cols <- c("A", "B", "C")

df1 <- df %>%
        mutate_at(cols, list(mean = ~cummean(.))) %>%
        complete(Time_Stamp = seq(min(Time_Stamp), max(Time_Stamp), by = "sec")) %>%
        fill(ends_with("mean")) %>%
        mutate(ID = row_number())

mean_cols <- grep("_mean$", names(df1))
df1[cols] <- Map(function(x, y) ifelse(is.na(x), y, x), df1[cols], df1[mean_cols])

df1[names(df)]

#     ID Time_Stamp              A     B     C
#   <int> <dttm>              <dbl> <dbl> <dbl>
# 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:03  197   256.  218 
# 5     5 2018-02-02 07:45:04  197   256.  218 
# 6     6 2018-02-02 07:45:05  197   256.  218 
# 7     7 2018-02-02 07:45:06  197   256.  218 
# 8     8 2018-02-02 07:45:07  197   256.  218 
# 9     9 2018-02-02 07:45:08  197   256.  218 
#10    10 2018-02-02 07:45:09  197   256.  218 
#11    11 2018-02-02 07:45:10  112  2323  2323 
#12    12 2018-02-02 07:45:11  176.  772.  744.
#13    13 2018-02-02 07:45:12  176.  772.  744.
#14    14 2018-02-02 07:45:13  176.  772.  744.
#15    15 2018-02-02 07:45:14  176.  772.  744.
#16    16 2018-02-02 07:45:15  100    23    12

If you need a running mean for every NA value it becomes a bit simpler

df %>%
  complete(Time_Stamp = seq(min(Time_Stamp), max(Time_Stamp), by = "sec")) %>%
  mutate_at(cols, ~ifelse(is.na(.), cummean(na.omit(.)), .)) %>%
  mutate(ID = row_number())

data

df <- structure(list(ID = c(1, 2, 3, 4, 5), Time_Stamp = structure(c(1517557500, 
1517557501, 1517557502, 1517557510, 1517557515), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), A = c(123, 234, 234, 112, 100), B = c(567, 
100, 100, 2323, 23), C = c(434, 110, 110, 2323, 12)), row.names = c(NA, 
-5L), class = "data.frame")

which looks like

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
#5  5 2018-02-02 07:45:15 100   23   12
user_39629140
Related