Combining date and time columns into one column introduces NA values-CodePudding

I have a data frame consisting of hourly data for the past 30 years. When I combine the DATE column with the TIME column to create a new column of DateTime, I get periodic NA values every 8000-9000 rows. Any idea why this is happening and how to not make it happen?

Example Data

# Create dataframe
start <- as.Date("1993-01-01")
end <- as.Date("2022-06-02")
hours <- as.data.frame(rep(seq(0,2300,100), 10745))
colnames(hours)[1] <- "TIME"
dates <- as.data.frame(rep(seq(start,end,by = "day"), 24))
colnames(dates)[1] <- "DATE"
dates <- dates %>% arrange(DATE)
x <- cbind(dates,hours)

# Convert the time from number to character (HH:MM)
x$TIME <- substr(as.POSIXct(sprintf(".0f", x$TIME), format='%H%M'), 12, 16)

# Combine date and time into one column
x$DateTime <- as.POSIXct(as.character(paste(x$DATE, x$TIME)), format="%Y-%m-%d %H:%M")

# See rows with NA values for DateTime
which(is.na(x$DateTime))

CodePudding user response：

We may use ymd_hm from lubridate

library(lubridate)
x$DateTime <-ymd_hm(paste(x$DATE, x$TIME))