Date time in write.csv in R

0
votes

I am having data frame named "data" with following column

>  2007-01-02 10:02:00
   2007-01-02 10:03:00
   2007-01-02 10:04:00
   2007-01-02 10:05:00
   2007-01-02 10:06:00
   2007-01-02 10:07:00

When I save data using write.csv(data,"Data.csv",row.names=F) and csv open like

>  Column1            Column2
  2007-01-02    10:02:00
  2007-01-02    10:03:00
  2007-01-02    10:04:00
  2007-01-02    10:05:00
  2007-01-02    10:06:00
  2007-01-02    10:07:00

When I import the data in R again using read.csv("Data.csv"). It shows exact data format. 

>  2007-01-02 10:02:00
   2007-01-02 10:03:00
   2007-01-02 10:04:00
   2007-01-02 10:05:00
   2007-01-02 10:06:00
   2007-01-02 10:07:00

I have no idea how come excel shows date and time in 2 separate columns while the datetime column in actually 1 column. How can I get the datetime in 1 column in csv format?

3
rdatecsvxtsposixct

3 Answers

1
votes

How do you import the csv file into Excel? It usually offers a dialog box and you specify the separator, etc. My guess is you have the separator set to space, not comma.

0
votes

Try write.table instead and use sep = ",". I opened the resulting .csv file in LibreOffice Calc and it worked fine (well, despite the fact that LibreOffice reformats datetime column according to German conventions...).

Here's my code:

# Sample data
data <- as.data.frame(strptime(c("2007-01-02 10:02:00",
          "2007-01-02 10:03:00",
          "2007-01-02 10:04:00",
          "2007-01-02 10:05:00",
          "2007-01-02 10:06:00",
          "2007-01-02 10:07:00"), format = "%Y-%m-%d %H:%M:%S"))
names(data) <- "datetime"

# Write table
write.table(data, "Data.csv", sep = ",", col.names = FALSE, row.names = FALSE)

# Read table
read.csv("Data.csv", header = FALSE)
0
votes

Your date should be stored as as.character(as.Date()) before to export into excel