CODEWITHSUNDEEP
rtimestampsequencexts
DwImAwesome
DwImAwesome

Reputation: 3

How to convert xts timeseries to hourly difference from 00:00:00

I have an xts timeseries as

Timestamp           - X
30-07-2019 23:00:00 - 110
31-07-2019 00:00:00 - 120 
31-07-2019 01:00:00 - 105
31-07-2019 02:00:00 - 110 
31-07-2019 03:00:00 - 100 
31-07-2019 04:00:00 - 105 
31-07-2019 05:00:00 - 115 
31-07-2019 06:00:00 - 125

Now i want the timestamp to be the hours to be the hour of the day, for example

Timestamp - X
-1        - 110
0         - 120 
1         - 105
2         - 110 
3         - 100 
4         - 105 
5         - 115 
6         - 125

And still keep it as an xts series, in order to use it with dygraph in a shiny application

Upvotes: 0

Views: 67

Answers (2)

Ekatef
Ekatef

Reputation: 1061

Another option is to use lubridate approach: 

library(lubridate)
hours_from_midnight <- hour(dmy_hms(df$Timestamp))
# boolean vector indicating if the time mome
after_midday <- pm(dmy_hms(df$Timestamp))
# coercion boolean to numerical is used
df$hour <- hours_from_midnight - 24 * after_midday

Upvotes: 0

geekzeus
geekzeus

Reputation: 895

Always try to provide your data in dput or in the way i have made the data

Here is the step by step way

Timestamp <- c("30-07-2019 23:00:00", "31-07-2019 00:00:00", "31-07-2019 01:00:00", "31-07-2019 02:00:00", "31-07-2019 03:00:00", "31-07-2019 04:00:00", "31-07-2019 05:00:00", "31-07-2019 06:00:00")
X <- c("-110","- 120","- 105","- 110","- 100","- 105","- 115","- 125")

#DF creation
df <- data.frame(Timestamp = Timestamp,X = X)

#Extracting `time` like `23:00:00`      
df$Timestamp <- sub(".* ", "", df$Timestamp)

#extracting only hours  
df$Timestamp <- format(strptime(df$Timestamp, format='%H:%M:%S'), '%H')

#converting into `Numeric` or `integer`
df$Timestamp <- as.integer(df$Timestamp)

#finally loop to get the desired result
for(i in df$Timestamp){
    if(i <= 12){
        df[,1][which(df$Timestamp == i)] <- i
    } else if(i > 12) {
        df[,1][which(df$Timestamp == i)] <- i-24
        } 
}

Output

Timestamp  X
-1        - 110
 0        - 120
 1        - 105
 2        - 110
 3        - 100
 4        - 105
 5        - 115
 6        - 125

EDIT: concise way,this should also work

 df <- data.frame(Timestamp = Timestamp,X = X)

 #applying `POSIXct`
 df$Timestamp <- as.integer(format(as.POSIXct(df$Timestamp, format="%d-%m-%Y %H:%M:%S"), "%H"))

 #loop
 df$Timestamp <- ifelse(df$Timestamp<=12,df$Timestamp,df$Timestamp-24)

Upvotes: 1

Related Questions