7

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?

Catarina Aires
  • 75
  • 1
  • 6

3 Answers3

7

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.

David Sterratt
  • 71
  • 1
  • 1
  • 4
    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))`. – RubenLaguna Sep 07 '16 at 14:28
  • 1
    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)`. – bonna Mar 09 '17 at 10:07
  • @RubenLaguna Should post one of these as a solution. I had the same issue today and used your answer. – Eric Krantz Oct 22 '20 at 21:32
  • @bonna Should post one of these as a solution. I had the same issue today and used your answer. – Eric Krantz Oct 22 '20 at 21:33
4

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).

Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
  • 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. – Catarina Aires Apr 29 '14 at 11:08
1

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))
RubenLaguna
  • 21,435
  • 13
  • 113
  • 151