Converting datetime from character to POSIXct object
Solution 1
For your real data issue replace the %m%
with %m
:
## Reading in the file:
fpath <- "c:/r/data/real_data.txt"
x <- read.csv(fpath, skip = 1, header = FALSE, sep = "", stringsAsFactors = FALSE)
names(x) <- c("date","time","bscat","scat_coef","pressure_mbar","temp_K","CH1","CH2") ## This is data from a Radiance Research Integrating Nephelometer Model M903 for anyone who is interested!
## issue was the %m% - fixed
x$datetime1 <- as.POSIXct(paste(x$date, x$time), format = "%Y-%m-%d %H:%M:%S", tz = "UTC")
## Here too - fixed
x$datetime2 <- strptime(paste(x$date, x$time), format = "%Y-%m-%d %H:%M:%S", tz = "UTC")
head(x)
Solution 2
There was a format string error causing the NAs; try this:
## This is no longer producing NAs:
data$datetime2 <- strptime(paste(data$date, data$time), format = "%Y-%m-%d %H:%M:%S",tz="UTC")
class(data$datetime2)
philiporlando
Updated on July 05, 2020Comments
-
philiporlando almost 4 years
I have an instrument that exports data in an unruly time format. I need to combine the
date
andtime
vectors into a newdatetime
vector in the followingPOSIXct
format:%Y-%m-%d %H:%M:%S
. Out of curiosity, I attempted to do this in three different ways, usingas.POSIXct()
,strftime()
, andstrptime()
. When using my example data below, only theas.POSIXct()
andstrftime()
functions work, but I am curious as to whystrptime()
is producingNAs
? Also, I cannot convert thestrftime()
output into aPOSIXct
object usingas.POSIXct()
...When trying these same functions on my real data (of which I've only provided you with the first for rows), I am running into an entirely different problem. Only the
strftime()
function is working. For some reason theas.POSIXct()
function is also producingNAs
, which is the only command I actually need for converting mydatetime
into aPOSIXct
object...It seems like there are subtle differences between these functions, and I want to know how to use them more effectively. Thanks!
Reproducible Example:
## Creating dataframe: date <- c("2017-04-14", "2017-04-14","2017-04-14","2017-04-14") time <- c("14:24:24.992000","14:24:25.491000","14:24:26.005000","14:24:26.511000") value <- c("4.106e-06","4.106e-06","4.106e-06","4.106e-06") data <- data.frame(date, time) data <- data.frame(data, value) ## I'm sure there is a better way to combine three vectors... head(data) ## Creating 3 different datetime vectors: ## This works in my example code, but not with my real data... data$datetime1 <- as.POSIXct(paste(data$date, data$time), format = "%Y-%m-%d %H:%M:%S",tz="UTC") class(data$datetime1) ## This is producing NAs, and I'm not sure why: data$datetime2 <- strptime(paste(data$date, data$time), format = "%Y-%m-%d %H:%M%:%S", tz = "UTC") class(data$datetime2) ## This is working just fine data$datetime3 <- strftime(paste(data$date, data$time), format = "%Y-%m-%d %H:%M%:%S", tz = "UTC") class(data$datetime3) head(data) ## Since I cannot get the as.POSIXct() function to work with my real data, I tried this workaround. Unfortunately I am running into trouble... data$datetime4 <- as.POSIXct(x$datetime3, format = "%Y-%m-%d %H:%M%:%S", tz = "UTC")
Link to real data: here
Example using real_data.txt:
## Reading in the file: fpath <- "~/real_data.txt" x <- read.csv(fpath, skip = 1, header = FALSE, sep = "", stringsAsFactors = FALSE) names(x) <- c("date","time","bscat","scat_coef","pressure_mbar","temp_K","CH1","CH2") ## This is data from a Radiance Research Integrating Nephelometer Model M903 for anyone who is interested! ## If anyone could get this to work that would be awesome! x$datetime1 <- as.POSIXct(paste(x$date, x$time), format = "%Y-%m-%d %H:%M%:%S", tz = "UTC") ## This still doesn't work... x$datetime2 <- strptime(paste(x$date, x$time), format = "%Y-%m-%d %H:%M%:%S", tz = "UTC") ## This works: x$datetime3 <- strftime(paste(x$date, x$time), format = "%Y-%m-%d %H:%M%:%S", tz = "UTC") ## But I cannot convert from strftime character to POSIXct object, so it doesn't help me at all... x$datetime4 <- as.POSIXct(x$datetime3, format = "%Y-%m-%d %H:%M%:%S", tz = "UTC") head(x)
Solution:
I was not providing the
as.POSIXct()
function with the correct format string. Once I changed%Y-%m-%d %H:%M%:%S
to%Y-%m-%d %H:%M:%S
, thedata$datetime2
,data$datetime4
,x$datetime1
andx$datetime2
were working properly! Big thanks toPhilC
for debugging!