How to Combine "Year", "Day of the Year", and "Hour" columns to DateTime in R?

user_13701033 0
Viewed 311

I have long dataframe as below (Note: DoY: Day of the Year, Hour: Hour of the Day (e.g., Hour =0.5 means 12:30 AM):

Year   DoY   Hour
2016   126   0.5
2016   126   1
2016   126   1.5
 -      -     -
2016   127   0
2016   127   0.5
 -      -     -
2018   300   23.5
 -      -     -

I am trying to combine these columns to single DateTime column (e.g. 2016-05-05 12:30 AM, 2016-05-05 1:00 AM, etc.). I tried following code:

x <- as.numeric(df$Hour)
x.m <- paste(floor(x), round((x-floor(x))*60), sep=":")
df$HourMinute <- x.m 
df$DateTime <- strptime(paste0(df$Year, df$DoY, df$HourMinute), format = "%Y%j%H:%M")

Above code results into some strange output. After a Year end, It outputs NA values. How to create the desired output column?

dplyr lubridate r datetime
2 Answers

You could use lubridate:

library(lubridate)

df$date <- make_datetime(year = df$Year, min = round(df$Hour*60)) + days(df$DoY-1)

df

#>   Year DoY Hour                date
#> 1 2016 126  0.5 2016-05-05 00:30:00
#> 2 2016 126  1.0 2016-05-05 01:00:00
#> 3 2016 126  1.5 2016-05-05 01:30:00

Data :

df <- structure(list(Year = c(2016L, 2016L, 2016L), DoY = c(126L, 126L, 126L), 
                     Hour = c(0.5, 1, 1.5)),
                     class = "data.frame", row.names = c(NA,-3L))
user_135133280

Here is a base R way.

fun <- function(DF){
  d <- with(DF, paste(Year, DoY))
  d <- as.Date(d, "%Y %j")
  hm <- DF[["Hour"]]*60
  d <- paste(d, paste(hm %/% 60, hm %% 60, 0, sep = ":"))
  d <- as.POSIXct(d, "%Y-%m-%d %H:%M:%S")
  d
}

fun(df)
#[1] "2016-05-05 00:30:00" "2016-05-05 01:00:00"
#[3] "2016-05-05 01:30:00"

This result can be assigned to a new column in the usual way.

df$DateTime <- fun(df)

Data

df <- read.table(text = "
Year   DoY   Hour
2016   126   0.5
2016   126   1
2016   126   1.5
", header = TRUE)
user_82454060
Related