Converting data to xts changes the timestamp data

Question

I am currently doing research on soil moisture and have to get hourly and daily means from my time series data.

When I convert the dataframe into an xts object, the time series changes and I can't figure out why.

The data in the data frame looks like this:

  time                MC temp

1 2018-06-27 11:30:00 17.1 15.8

2 2018-06-27 11:45:00 17.0 15.8

3 2018-06-27 12:00:00 17.0 15.8

4 2018-06-27 12:15:00 17.0 15.9

5 2018-06-27 12:30:00 17.2 15.9

6 2018-06-27 12:45:00 17.0 16.0

But when I convert it, the time stamp begins at 2018-01-09 00:00 and proceeds to make 5 min time increments. This is the code I am using:

sm_xts <- xts(sm.data[,2:3], as.Date(sm.data$time))
sm_zoo <- read.zoo(sm.data, index.column = 1)
dat_xts <- as.xts(sm_zoo)

I have already converted the time stamp to a as.POSIXct class and checked for duplicates in my time series.

> anyDuplicated(sm.data$time)
[1] 0

Serhii Kushchenko Serhii Kushchenko · Accepted Answer · 2019-04-05T07:42:23

Maybe you have some problems with your initial data input process.

    df
#                  time   MC temp
# 1 2018-06-27 11:30:00 17.1 15.8
# 2 2018-06-27 11:45:00 17.0 15.8
# 3 2018-06-27 12:00:00 17.0 15.8
# 4 2018-06-27 12:15:00 17.0 15.9
# 5 2018-06-27 12:30:00 17.2 15.9
# 6 2018-06-27 12:45:00 17.0 16.0

Now the date and time values are stored in a single variable as a char

str(df$time)
# chr [1:6] "2018-06-27 11:30:00" "2018-06-27 11:45:00" ...

Let's convert it into real date and time so as not to lose time information:

strptime(df$time, "%Y-%m-%d %H:%M:%S")
# [1] "2018-06-27 11:30:00 EEST" "2018-06-27 11:45:00 EEST"
# [3] "2018-06-27 12:00:00 EEST" "2018-06-27 12:15:00 EEST"
# [5] "2018-06-27 12:30:00 EEST" "2018-06-27 12:45:00 EEST"

Seems like it works. The possible problems with timezone are beyond the scope of this answer.

Now let's convert the data frame to xts. In the finished xts, we do not need date and time in the symbolic form. So we exclude the first column.

df2xts <- xts(df[,2:3], order.by=strptime(df$time, "%Y-%m-%d %H:%M:%S"))
df2xts
#                 time   MC temp
# 1 2018-06-27 11:30:00 17.1 15.8
# 2 2018-06-27 11:45:00 17.0 15.8
# 3 2018-06-27 12:00:00 17.0 15.8
# 4 2018-06-27 12:15:00 17.0 15.9
# 5 2018-06-27 12:30:00 17.2 15.9
# 6 2018-06-27 12:45:00 17.0 16.0

Converting data to xts changes the timestamp data

1 Answers