CODEWITHSUNDEEP
rdatetimetime-seriesxtszoo
user2263130
user2263130

Reputation: 37

Fill in missing time steps (yyyy-mm-dd HH:MM:SS) by adding rows with missing times in R

I have a large data set that looks like:

Time,Volume    
1996-02-05 00:34:00,0.01
1996-02-05 00:51:00,0.01
1996-02-05 00:52:00,0.01
1996-02-05 01:04:00,0.01
1996-02-05 01:19:00,0.01
1996-02-05 05:00:00,0.01
1996-02-05 05:07:00,0.01
1996-02-05 05:08:00,0.01
1996-02-05 05:14:00,0.01

I'd like to sum the Volume column for each 30 minute interval. This is what I've tried:

z <- read.zoo("precip.csv", header = TRUE, sep = ",", FUN = as.chron)
half_hour <- period.apply(z, endpoints(z, "minutes", 30), length)

Which returned:

Time,Volume
02/05/96 00:52:00,3
02/05/96 01:19:00,2
02/05/96 05:14:00,4

I'm trying to get the output to look like:

Time,Volume
02/05/96 00:29:00,0
02/05/96 00:59:00,3
02/05/96 01:29:00,2
02/05/96 01:59:00,0
02/05/96 02:29:00,0
02/05/96 02:59:00,0

...and so on.

Alternatively, I think it would work if I could fill in the original data set so that every minute is accounted for (where missing Volumes equal 0).

I found this post, but couldn't make it work.

> z_xts<- xts(precip[,c("Volume")],precip[,"Time"])
Error in xts(precip[, c("Volume")], precip[, "Time"]) : 
  order.by requires an appropriate time-based object

Upvotes: 3

Views: 645

Answers (2)

Sam
Sam

Reputation: 89

I was a little confused about step 3) mentioned above, so I what I did is:

library("lubridate")
library("xts")
my_data <- read.csv("my_data.csv", stringsAsFactors=FALSE, sep=",", 
header=T) 
colnames(my_data) <- c("Time", "PAR", "NDVI", "LWS")
#It is easier if you subset your data
my_data_short_short <- subset(my_data, select = c("Time", "NDVI")) 
my_data_short$Time <- ymd_hm(my_data_short$Time, tz="UTC") 
beginning <- as.POSIXct("2016-05-12 00:00",format = "%Y-%m-%d %H:%M", 
tz="UTC")
end <- as.POSIXct("2016-06-05 00:00",format = "%Y-%m-%d %H:%M", tz="UTC")
timesteps <- seq(beginning, end, by="5 min")
volume <- rep_len(1, length.out=length(timesteps))
time_series <- data.frame(timesteps, volum)
merge <- merge(time_series, my_data_short, by.x= "timesteps", by.y="Time", 
all.x=TRUE, all.y = FALSE)

#This formats your data to run the package xts
my_data_brief.xts <- xts(x= merge$NDVI, order.by=merge$timesteps, frequency 
= 1, tzone="UTC") 

# Align all index values to 30-minute intervals
a <- align.time(my_data_brief.xts, 60*30)
# 5) Sum the values for Volume in each period
result <- period.apply(a, endpoints(a, "minutes", 30), sum, na.rm=TRUE)

saveRDS (result, file="result.rds")

Upvotes: 0

Joshua Ulrich
Joshua Ulrich

Reputation: 176648

This should do what you want:

library(xts)
x <- as.xts(read.zoo(text="Time,Volume    
1996-02-05 00:34:00,0.01
1996-02-05 00:51:00,0.01
1996-02-05 00:52:00,0.01
1996-02-05 01:04:00,0.01
1996-02-05 01:19:00,0.01
1996-02-05 05:00:00,0.01
1996-02-05 05:07:00,0.01
1996-02-05 05:08:00,0.01
1996-02-05 05:14:00,0.01",
sep=",", FUN=as.POSIXct, header=TRUE, drop=FALSE))

# 1) Create POSIXct sequence from midnight of the first day
#    until the end of the last day    
midnightDay1 <- as.POSIXct(format(start(x),"%Y-%m-%d"))
timesteps <- seq(midnightDay1, end(x), by="30 min")
# 2) Make a copy of your object and set all values for Volume to 1
y <- x
y$Volume <- 1
# 3) Merge the copy with a zero-column xts object that has an index
#    with all the values you want.  Fill missing values with 0.
m <- merge(y, xts(,timesteps), fill=0)
# 4) Align all index values to 30-minute intervals
a <- align.time(m, 60*30)
# 5) Sum the values for Volume in each period
half_hour <- period.apply(a, endpoints(a, "minutes", 30), sum)

Upvotes: 3

Related Questions