将 excel DateTime 序列号转换为 R DateTime [英] Converting excel DateTime serial number to R DateTime
问题描述
当 Excel 表作为 xy 点导入 ArcGIS 时,我继续丢失每个点的正确日期时间戳.因此,我格式化了 DateTime 序列号,创建了 .shp,并使用 readOGR() 将 .shp 读入 R.
When excel tables are imported as xy points in ArcGIS I continue to lose my correct DateTime stamp for each point. Thus, I have formatted the DateTime serial number, created the .shp, and read the .shp into R using readOGR().
Once in RI 可以使用 as.Date()
和 origin = "1899-12-30"
参数转换为正确的日期,但时间还剩出去.虽然我看过带有唯一 Date 的示例,但我还没有看过带有 DateTime 的示例.我一直在使用 as.Date()
以及 as.POSIXct()
但是这个看似简单的任务有点令人沮丧,因此帖子......
Once in R I can convert to the correct date using as.Date()
and the origin = "1899-12-30"
argument, but the time is left out. While I have seen examples with a sole Date, I have not seen worked examples with DateTime. I have been using as.Date()
as well as as.POSIXct()
but this seemingly simple task as been a bit frustrating, thus the post…
我创建了一个示例数据集,其中包含 10 行正确的 DateTime 格式以及 excel 序列号.
I have created a sample data set with 10 rows of the correct DateTime format as well as the excel serial number.
*感谢 Richard 和 thelatemail 对早期障碍的敏锐观察.我已经更正了数据并在此处重新发布.
*Thanks Richard and thelatemail for their keen eye on an earlier hindrance. I have corrected the data and re-posted here.
这是我的示例数据
helpData <- structure(list(ID = 1:10, DateTime = structure(c(9L, 1L, 2L,
3L, 4L, 5L, 6L, 7L, 8L, 8L), .Label = c("3/11/2011 7:55", "3/13/2011 7:55",
"3/14/2011 0:00", "3/14/2011 10:04", "3/14/2011 7:55", "3/15/2011 19:55",
"3/17/2011 7:55", "3/18/2011 4:04", "3/4/2011 6:00"), class = "factor"),
ExcelNum = c(40606.25, 40613.32986, 40615.32986, 40616, 40616.41944,
40616.32986, 40617.82986, 40619.32986, 40620.16944, 40620.16944
)), .Names = c("ID", "DateTime", "ExcelNum"), class = "data.frame", row.names = c(NA,
-10L))
head(helpData)
日期时间是格林威治标准时间.时间是 24 小时制(即不是 AM/PM).我正在使用 Windows 7,拥有最新的 R 和 ArcGIS 10.
The DateTime is GMT. The time is a 24 hr clock (i.e. not AM/PM). I am working on Windows 7, have the most recent R, and ArcGIS 10.
下面的代码得到了正确的日期,但仍然缺少时间.
The code below gets the correct Date, but the time is still missing.
newDateTime <- as.Date(helpData[ , "ExcelNum"], origin = "1899-12-30")
head(newDateTime)
提前致谢!
推荐答案
您的数字正在计算天数.转换为秒,就大功告成了(少了一个舍入误差)
Your number is counting days. Convert to seconds, and you're all set (less a rounding error)
helpData[["ExcelDate"]] <-
as.POSIXct(helpData[["ExcelNum"]] * (60*60*24)
, origin="1899-12-30"
, tz="GMT")
# ID DateTime ExcelNum ExcelDate
# 1 1 3/4/2011 6:00 40606.25 2011-03-04 06:00:00
# 2 2 3/11/2011 7:55 40613.33 2011-03-11 07:54:59
# 3 3 3/13/2011 7:55 40615.33 2011-03-13 07:54:59
# 4 4 3/14/2011 0:00 40616.00 2011-03-14 00:00:00
# 5 5 3/14/2011 10:04 40616.42 2011-03-14 10:03:59
# 6 6 3/14/2011 7:55 40616.33 2011-03-14 07:54:59
# 7 7 3/15/2011 19:55 40617.83 2011-03-15 19:54:59
# 8 8 3/17/2011 7:55 40619.33 2011-03-17 07:54:59
# 9 9 3/18/2011 4:04 40620.17 2011-03-18 04:03:59
# 10 10 3/18/2011 4:04 40620.17 2011-03-18 04:03:59
这篇关于将 excel DateTime 序列号转换为 R DateTime的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!