R - converting date and time fields to POSIXct with HHMMSS format

Question

I have a data file which has three columns thus:

20010101 000000  0.833
20010101 000500  0.814
20010101 001000  0.794
20010101 001500  0.772
...

As is fairly clear to human eyes, the first two are date and time. I need to convert them into a POSIXct (or something else if it's better, but my limited past experience of dealing with timestamps in R is to use POSIXct). Normally, having pulled it in with read.table, I would use:

df$DateTime <- as.POSIXct(paste(df$Date, df$Time), format="%Y%m%d %H%M%S")

However, the second column seems to lose its leading zeroes (probably through a type coercion?), and thus it doesn't work correctly.

I've looked at Combine date as integer and time as factor to POSIXct in R and Converting two columns of date and time data to one, but both are using times with delimiters such as :, and so don't have the same problem.

How can I convert these columns to a POSIXct, please?

Francium

Answer 1

You were very close. The following "simply" forces the first two columns to be read as character strings, which saves the leading zeros.

R> df <- read.table(text="20010101 000000  0.833
20010101 000500  0.814
20010101 001000  0.794
20010101 001500  0.772", 
+ header=FALSE, colClasses=c("character", "character", "numeric"), 
+ col.names=c("Date", "Time", "Val"))
R> df
      Date   Time   Val
1 20010101 000000 0.833
2 20010101 000500 0.814
3 20010101 001000 0.794
4 20010101 001500 0.772

Now what you were attempting "just works":

R> df$DateTime <- as.POSIXct(paste(df$Date, df$Time), format="%Y%m%d %H%M%S")
R> df
      Date   Time   Val            DateTime
1 20010101 000000 0.833 2001-01-01 00:00:00
2 20010101 000500 0.814 2001-01-01 00:05:00
3 20010101 001000 0.794 2001-01-01 00:10:00
4 20010101 001500 0.772 2001-01-01 00:15:00
R> 

Hampden

I'm doing everything described here and POSIXct returns NA – Sinistrous

You may have a factor variable. Try anytime::anytime(paste(df$Date, df$Time)) as that will convert a factor variable for you. – Hampden

Thanks for the response, I had some early times like 600 and all they needed was a 0 in front. – Sinistrous

So you input did not correspond to the format requirement. News at 11pm? Glad you have it working. – Hampden

Answer 2

You just need to import the data as character:

txt <- "Date  Time  value
20010101 000000  0.833
20010101 000500  0.814
20010101 001000  0.794
20010101 001500  0.772
"

df <- read.table(text=txt, header=TRUE, 
                 colClasses=c("character", "character", "numeric"))

df$DateTime <- as.POSIXct(paste(df$Date, df$Time), format="%Y%m%d %H%M%S")

Newberry

Thanks. I'm accepting the answer that came first, but this is substantially the same :-) – Francium

Answer 3

Simply you can use lubridate package which is super awesome and fast. for your purpose try this:

df <- read.table(text="20010101 000000  0.833
20010101 000500  0.814
20010101 001000  0.794
20010101 001500  0.772", 
                  header=FALSE, colClasses=c("character", "character",     "numeric"), 
                  col.names=c("Date", "Time", "Val"))

df$mix <- paste(df$Date, df$Time)
df$mix <- parse_date_time(df$mix, 'Ymd HMS')

Just you have to feed the correct format to it. I prefer it to as.POSICct because it is much more flexible and you have other functions to work with time variables.

Petterson