CODEWITHSUNDEEP
rexcelexportxlconnect
Gwen
Gwen

Reputation: 109

Load data from Excel to R - extra dates appearing in first column?

In my attempt to load data from Excel to R: 

> book <- loadWorkbook(file.choose())
> signals = readWorksheet(book, sheet = "signals", header = TRUE)
> signals

which returns: 

                 time signal1 signal2
1 1899-12-31 08:30:00    0.43   -0.20
2 1899-12-31 08:31:00    0.54    0.33
3 1899-12-31 08:32:00    0.32   -0.21

Why do I get the column with 1899-12-31? Those are definitely not in the Excel sheet. The rest is correct.

Upvotes: 0

Views: 421

Answers (2)

user4359255
user4359255

Reputation:

Here is how you can save a data frame to an Excel file and load the data frame from the Excel file.

library(lubridate)
library(stringr)
library(xlsx)

#Creating time and date data
t1 <- ymd_hms("2011-06-04 12:00:00")
t2 <- ymd_hms("2011-06-05 11:00:00")
t3 <- ymd_hms("2011-06-06 10:00:00")

#Storing the time and date data in a data frame 
#Storing t1
df <- data.frame(NULL)
df <- rbind(df, data.frame(t(strsplit(as.character(t1), " ")), stringsAsFactors = F))
colnames(df) <- c("V1")
df <- str_split_fixed(df$V1, " ", 2)
df <- gsub("[^0-9.:-]","",df)
colnames(df) <- c("Date", "Time")
#Storing t2
tmp <-  data.frame(t(strsplit(as.character(t2), " ")), stringsAsFactors = F)
colnames(tmp) <- c("V1")
tmp <- str_split_fixed(tmp$V1, " ", 2)
tmp <- gsub("[^0-9.:-]","", tmp)
df <- rbind(df, tmp)
#Storing t3
tmp <-  data.frame(t(strsplit(as.character(t3), " ")), stringsAsFactors = F)
colnames(tmp) <- c("V1")
tmp <- str_split_fixed(tmp$V1, " ", 2)
tmp <- gsub("[^0-9.:-]","", tmp)
df <- rbind(df, tmp)

#Writing the data frame to an Excel file
write.xlsx(x = df, file = "df.xlsx", sheetName = "Sheet1", row.names = FALSE)
#reading the data from the Excel file
readDF <- read.xlsx("df.xlsx", sheetName="Sheet1")

Upvotes: 0

Gwen
Gwen

Reputation: 109

I found this paragraph in the docs:

Forcing conversion from Numeric to DateTime: since Excel understands Dates/Times as Nu- merics with some additional formatting, a conversion from a Numeric to a DateTime is ac- tually possible. Numerics in this case represent the number of days since 1900-01-00 (yes, day 00! - see http://www.cpearson.com/excel/datetime.htm). Note that in R 0 is represented as 1899-12-31 since there is no 1900-01-00. Fractional days represent hours, minutes, and seconds.

This seems to shed some light on the issue.

Upvotes: 2

Related Questions