CODEWITHSUNDEEP
rdataframedatedatetime
Stoner
Stoner

Reputation: 906

R - convert date column in daily format to hourly granularity

I have the following dataframe df:

> head(df)
        date    demand
1 2019-09-21    6
2 2019-09-21    1
3 2019-09-21    9
4 2019-09-21    3
5 2019-09-21    12
6 2019-09-21    3

and I will like it to have hourly granularity as follows:

> head(df)
                 date   demand
1 2019-09-21 00:00:00   6
2 2019-09-21 01:00:00   1
3 2019-09-21 02:00:00   9
4 2019-09-21 03:00:00   3
5 2019-09-21 04:00:00   12
6 2019-09-21 05:00:00   3

Is there a way to do this efficiently in R? The closest answer I've found is R Programming: Create hourly time intervals in an array with for loop and Matching and Replacing values of a column in a dataframe by date in r but I can't seem implement them for my problem.. Some insights will be deeply appreciated!

Remark: There are multiple days in the dataframe, each day contains 24 hours of daates as above in df. The first date should have hour starting at 12am (00:00:00) and the last date has hour at 11pm (23:00:00).

Upvotes: 0

Views: 329

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388982

Probably, something like this would help : 

library(dplyr)
df %>%
  group_by(date) %>%
  mutate(datetime = as.POSIXct(date) + 3600 * 0:(n() - 1))
  #We can also use another variation suggested by @thelatemail
  #mutate(datetime = as.POSIXct(date) + as.difftime(seq_len(n())-1, unit="hours")) 

#  date       demand datetime           
#  <fct>       <int> <dttm>             
#1 2019-09-21      6 2019-09-21 00:00:00
#2 2019-09-21      1 2019-09-21 01:00:00
#3 2019-09-21      9 2019-09-21 02:00:00
#4 2019-09-21      3 2019-09-21 03:00:00
#5 2019-09-21     12 2019-09-21 04:00:00
#6 2019-09-21      3 2019-09-21 05:00:00

This can also be written in base R : 

df$datetime <- with(df, ave(as.POSIXct(date), date, 
                    FUN = function(x) x + 3600 * 0:(length(x) - 1)))

Upvotes: 2

Related Questions