How to prevent write.csv from changing POSIXct, dates and times class back to character/factors?

Question

I have a .csv file with one field each for datetime, date and time. Originally they are all character fields and I have converted them accordingly. At the end of my code, if I do:

str(data)

I will get

datetime: POSIXct
date: Date
time: Class 'times'  atomic [1:2820392] (....) attr(*, "format")= chr "h:m:s"

Now, I am very happy with this and I want to create a .csv file, so this is what I have:

write.csv(data, file = "data.csv", row.names = FALSE)

I have also tried

write.table(data, "data.csv", sep = ",", row.names = FALSE)

And I get the same result with both, which is all my convertion gets lost when writing the new .csv: everything is back to being a character.

I suspect I am missing some argument in the write function, but I have been searching all afternoon and I can't find out what. Can some please help?

Acquirement

Answer 1

According to ?write.table:

Any columns in a data frame which are lists or have a class (e.g. dates) will be converted by the appropriate 'as.character' method: such columns are unquoted by default.

Simply put, you can only write text/characters to text files. Use save if you want to preserve the binary R representation of your object(s).

Alcala

Thank you for your answer Joshua. I need my files to read into ArcMap, so saving is not good enough for me - as far as I managed to find out anyway. However, I found out that the .csv is not that bad after all: if the format is the correct one, ArcMap interprets the character types correctly as dates, times, and even date-times - as long as I create my shapefiles inside a geodatabase. – Acquirement

Answer 2

If you want to preserve all of the time information so it can be read in again, this recipe should work:

dat <- data.frame(time=as.POSIXlt("2013-04-25 09:00 BST"), quantity=1)
dat2 <- dat
dat2$time <- format(dat2$time, usetz=TRUE)
write.csv(dat2, "time.csv", row.names=FALSE)

It gives the following CSV file:

"time","quantity"
"2013-04-25 09:00:00 BST",1

in which the timezone information is presented explicitly; if you apply write.csv to the original dat, the formatting is lost.

Repulsive

In the case that you have dplyr and lubridate you can write.csv(mutate(data, time=format(time, "%FT%H:%M:%S%z")), file="test.csv", row.names=FALSE) as a one liner to generate csv that has the dates in ISO8601 (so you don't lose any information). You can reinstate the data frame back from the file with df2 <- read.csv("test.csv") %>% mutate(time=ymd_hms(time)). – Chablis

And without dplyr and lubridate it would be data$time = format(data$time,format="%FT%H:%M:%S%z"); write.csv(data,"time.csv",row.names=FALSE). – Serafinaserafine

@Chablis Should post one of these as a solution. I had the same issue today and used your answer. – Cabob

@Serafinaserafine Should post one of these as a solution. I had the same issue today and used your answer. – Cabob

Answer 3

If you are willing to add dplyr and lubridate as dependencies you can generate the CSV with dates in ISO8601 (so you don't lose any information) like this:

#install.packages("tidyverse")
#install.packages("dplyr")
library(dplyr)
library(lubridate, warn.conflicts = FALSE)
dat <- data.frame(time=as.POSIXlt("2013-04-25 09:00 BST"), quantity=1) # example data
write.csv(mutate(dat, time=format(time, "%FT%H:%M:%S%z")), file="test.csv", row.names=FALSE)

That will generate a CSV file with the following content:

"time","quantity"
"2013-04-25T09:00:00+0200",1

As you can see the CSV contain the date in ISO8601 with the timezone information so no information is lost.

If you want to read back that CSV you can

df2 <- read.csv("test.csv") %>% mutate(time=ymd_hms(time))

Chablis