CODEWITHSUNDEEP
rdplyrmergetime-seriesinterpolation
MeC
MeC

Reputation: 463

interpolate values and replace empty cells with NA

I would like to linearly interpolate time series data (in steps of 60 seconds in a data frame as follows, where seconds is the variable to be interpolated:

mydf <- data.frame(measurement = c(2,6,4,1,7), other_measurement = c(2,5,8,6,4), seconds = c(60,60,120,360,360))

(The actual data frame is many magnitudes larger than this.)

Then, once those intermediary multiples of 60 are interpolated, I would like to fill in the cells that are generated with NA. I found some solutions that are close to this, like creating a data frame of multiples of 60 and merging it with my data frame, but none that worked exactly and still filled in all of the missing cells across multiple columns. Thank you!

Upvotes: 0

Views: 62

Answers (1)

r2evans
r2evans

Reputation: 160687

base R

newdf <- data.frame(seconds = do.call(seq, c(as.list(range(mydf$seconds)), by = 60)))
merge(mydf, newdf, all = TRUE)
#   seconds measurement other_measurement
# 1      60           2                 2
# 2      60           6                 5
# 3     120           4                 8
# 4     180          NA                NA
# 5     240          NA                NA
# 6     300          NA                NA
# 7     360           1                 6
# 8     360           7                 4

dplyr

library(dplyr)
mydf %>%
  summarize(seconds = seq(min(seconds), max(seconds), by = 60)) %>%
  full_join(mydf, ., by = "seconds")
#   measurement other_measurement seconds
# 1           2                 2      60
# 2           6                 5      60
# 3           4                 8     120
# 4           1                 6     360
# 5           7                 4     360
# 6          NA                NA     180
# 7          NA                NA     240
# 8          NA                NA     300

Upvotes: 1

Related Questions