making time series regular

Question

I this time series data( I am not able to dput. Because dput give a very long result, copying and pasting it here will make a mess) These are few lines from my text file:

        Time                  Depths   ifc  if   lat        lon
"7814" "2012-03-15 04:45:09-05" 4816 1040 5410 43.213628 -92.27727
"7815" "2012-03-15 04:30:04-05" 4813 1040 5410 43.213628 -92.27727
"7816" "2012-03-15 04:15:14-05" 4807 1040 5410 43.213628 -92.27727
"7817" "2012-03-15 04:00:09-05" 4809 1040 5410 43.213628 -92.27727
"7818" "2012-03-15 03:45:04-05" 4819 1040 5410 43.213628 -92.27727
"7819" "2012-03-15 03:30:15-05" 4816 1040 5410 43.213628 -92.27727
"7820" "2012-02-25 14:45:07-06" 4862 1040 5410 43.213628 -92.27727
"7821" "2012-02-25 14:30:02-06" 4858 1040 5410 43.213628 -92.27727
"7822" "2012-02-25 14:15:13-06" 4852 1040 5410 43.213628 -92.27727
"7823" "2012-02-25 14:00:08-06" 4860 1040 5410 43.213628 -92.27727
"7824" "2012-02-25 13:45:03-06" 4855 1040 5410 43.213628 -92.27727
"7825" "2012-02-25 13:30:13-06" 4869 1040 5410 43.213628 -92.27727
"7826" "2012-02-25 13:15:08-06" 4868 1040 5410 43.213628 -92.27727
"7827" "2012-02-25 13:00:03-06" 4873 1040 5410 43.213628 -92.27727

Here you can see that the value next to row no. 7819 is a jump. I wish to fix this so that it contains continuous time interval of 15min and Depths column in those interval be NA and values in rest of the column to be fill with the constant values as they are in other rows.

I tried this on SO but it did not work. Can somebody please help me with this?

-05 and -06 this is notation of time zone. you can ignore them by: saving these rows in a text file and then reading it using- read.zoo("dat.txt", tz="") — rockswap, Jul 06 '12 at 07:06
I have edited my question, which now includes the dput as per your suggestion. — rockswap, Jul 06 '12 at 07:10
since this is a `zoo` object already, try `dput(t[7814:7827])` instead. — A5C1D2H2I1M1N2O1R2T1, Jul 06 '12 at 07:20
This is a Frequently Asked Question. See zoo FAQ #13: http://cran.r-project.org/web/packages/zoo/vignettes/zoo-faq.pdf . — G. Grothendieck, Jul 06 '12 at 12:19

score 1 · Accepted Answer · answered Jul 06 '12 at 07:41

If I understand your question properly this should do it (this assumes it's not a zoo object and the data.frame is t). You may also have to change the column index 2 to 1

dates <- as.POSIXct(t[,2])
# remove the seconds from the time stamps
dates <- dates - as.numeric(format(dates,"%S"))
# Create a sequence of dates for the entire time.
all_dates <- seq.POSIXt(from=dates[length(dates)], to=dates[1],by="15 min")
# Put them into a data.frame to make merging easier
all_dates_frame <- data.frame(dates_floor=all_dates)
# create a data.frame of the obsereved values with the floored dates
t_floor <- data.frame(dates_floor=dates, t[,-2]) 
# merge the observations onto the grid
together <- merge(all_dates_frame, t_floor, all.y="TRUE", all.x="TRUE")
# If you want to replace the floored times with the actual times then find which ones need replacing
replace_time_index <- !is.na(together[,2])
# replace the time stamps
together[replace_time_index, 1] <- t[,2]

score 1 · Answer 2 · answered Jul 06 '12 at 11:02

@Jase_ provides an example of how you would do this if your data were a data.frame; however, you indicated in your comments and in your initial attempts at using dput that this was an object with the zoo class.

Here is the zoo solution (borrowing conceptually from Jase_'s answer). It utilizes the index attribute of zoo objects.

# First, read in your data as a zoo object via "copy and paste"
require(zoo)
t = read.zoo(text='        Time Depths   ifc  if   lat        lon
"7814" "2012-03-15 04:45:09-05" 4816 1040 5410 43.213628 -92.27727
"7815" "2012-03-15 04:30:04-05" 4813 1040 5410 43.213628 -92.27727
"7816" "2012-03-15 04:15:14-05" 4807 1040 5410 43.213628 -92.27727
"7817" "2012-03-15 04:00:09-05" 4809 1040 5410 43.213628 -92.27727
"7818" "2012-03-15 03:45:04-05" 4819 1040 5410 43.213628 -92.27727
"7819" "2012-03-15 03:30:15-05" 4816 1040 5410 43.213628 -92.27727
"7820" "2012-02-25 14:45:07-06" 4862 1040 5410 43.213628 -92.27727
"7821" "2012-02-25 14:30:02-06" 4858 1040 5410 43.213628 -92.27727
"7822" "2012-02-25 14:15:13-06" 4852 1040 5410 43.213628 -92.27727
"7823" "2012-02-25 14:00:08-06" 4860 1040 5410 43.213628 -92.27727
"7824" "2012-02-25 13:45:03-06" 4855 1040 5410 43.213628 -92.27727
"7825" "2012-02-25 13:30:13-06" 4869 1040 5410 43.213628 -92.27727
"7826" "2012-02-25 13:15:08-06" 4868 1040 5410 43.213628 -92.27727
"7827" "2012-02-25 13:00:03-06" 4873 1040 5410 43.213628 -92.27727
', tz="")

Only two lines for your merged output:

# Modify your index as per Jase_'s answer
index(t) = index(t) - as.numeric(format(index(t), "%S"))
# Merge with an empty zoo object that has an index
# of all the dates that you need.
t.merged = merge(t, zoo(, seq(from=index(t)[1], 
                              to=index(t)[length(index(t))], 
                              by="15 min")))

Let's see what the output looks like:

head(t.merged, 10L)
# 
# 2012-02-25 13:00:00 4873 1040 5410 43.21363 -92.27727
# 2012-02-25 13:15:00 4868 1040 5410 43.21363 -92.27727
# 2012-02-25 13:30:00 4869 1040 5410 43.21363 -92.27727
# 2012-02-25 13:45:00 4855 1040 5410 43.21363 -92.27727
# 2012-02-25 14:00:00 4860 1040 5410 43.21363 -92.27727
# 2012-02-25 14:15:00 4852 1040 5410 43.21363 -92.27727
# 2012-02-25 14:30:00 4858 1040 5410 43.21363 -92.27727
# 2012-02-25 14:45:00 4862 1040 5410 43.21363 -92.27727
# 2012-02-25 15:00:00   NA   NA   NA       NA        NA
# 2012-02-25 15:15:00   NA   NA   NA       NA        NA
tail(t.merged, 10L)
# 
# 2012-03-15 02:30:00   NA   NA   NA       NA        NA
# 2012-03-15 02:45:00   NA   NA   NA       NA        NA
# 2012-03-15 03:00:00   NA   NA   NA       NA        NA
# 2012-03-15 03:15:00   NA   NA   NA       NA        NA
# 2012-03-15 03:30:00 4816 1040 5410 43.21363 -92.27727
# 2012-03-15 03:45:00 4819 1040 5410 43.21363 -92.27727
# 2012-03-15 04:00:00 4809 1040 5410 43.21363 -92.27727
# 2012-03-15 04:15:00 4807 1040 5410 43.21363 -92.27727
# 2012-03-15 04:30:00 4813 1040 5410 43.21363 -92.27727
# 2012-03-15 04:45:00 4816 1040 5410 43.21363 -92.27727

But, this doesn't replace the NA values as you wanted to do.

making time series regular

2 Answers2

Only two lines for your merged output: