使用数据:
Excel文件中的数据看起来像
TIMESTAMP TYPE BID BIDSIZ
2015-01-04 09:00:00 BID 365 10
2015-04-01 09:00:05 BID 367.8 55
2015-04-01 09:00:33 BID 365 10
2015-04-01 09:00:36 BID 367.8 55
当我运行以下代码时:
require(xlsx)
f1<-read.xlsx2("Canara_Data.xlsx", sheetName = "BID")
f1$TIMESTAMP<-as.POSIXct(f1$TIMESTAMP, format="%Y-%M-%D %H:%M:S")
查看它会导致TIMESTAMP看起来像
View(`f1`)
TIMESTAMP X. BID BIDSIZ
42008.375 BID 365 10
42095.37505787037 BID 367.8 55
42095.37538194445 BID 365 10
42095.37541666667 BID 367.8 55
str(f1)
# 'data.frame': 18214 obs. of 4 variables:
# $ TIMESTAMP: POSIXct, format: NA NA ...
# $ TYPE : Factor w/ 1 level "BID": 1 1 1 1 1 1 1 1 1 1 ...
# $ BID : Factor w/ 344 levels "365","365.1",..: 1 55 1 55 1 55 1 55 59 1 ...
# $ BIDSIZ : Factor w/ 1259 levels "1","10","100",..: 2 854 2 854 2 854 2 854 4 2
请帮助将TIMESTAMP格式为"%Y-%M-%D %H:%M:S",以及BID和BIDSIZ作为字符。
Excel &日期格式通常不是一个好的组合。你可以使用:
f1$TIMESTAMP <- as.POSIXct(f1$TIMESTAMP*86400, origin="1899-12-30",tz="GMT")
将其转换为数据时间格式。
这给:
> f1
TIMESTAMP X. BID BIDSIZ
1 2015-01-04 09:00:00 BID 365.0 10
2 2015-04-01 09:00:05 BID 367.8 55
3 2015-04-01 09:00:33 BID 365.0 10
4 2015-04-01 09:00:36 BID 367.8 55
另一种解决方案是将excel文件导出到.csv
或以制表符分隔的.txt
文件,然后将其读入r。
您可以将BID
和BIDSIZ
列转换为字符列:
f1[,c(3:4)] <- lapply(f1[,c(3:4)], as.character)
使用数据:
f1 <- structure(list(TIMESTAMP = c(42008.375, 42095.3750578704, 42095.3753819444, 42095.3754166667),
X. = structure(c(1L, 1L, 1L, 1L), .Label = "BID", class = "factor"),
BID = c(365, 367.8, 365, 367.8),
BIDSIZ = c(10L, 55L, 10L, 55L)),
.Names = c("TIMESTAMP", "X.", "BID", "BIDSIZ"), class = "data.frame", row.names = c(NA, -4L))
Use Package readxl
excel_sheets("C:\Users\Gaurav Kumar\Documents\Canara_Data.xlsx")
grv<-read_excel("C:\Users\Gaurav Kumar\Documents\Canara_Data.xlsx", sheet = 1, col_names = TRUE, col_types = NULL, na = "", skip = 0)
,它也会读取时间戳。它节省了大量的时间将excel转换为csv,知道csv在保存时间戳方面有问题。
TIMESTAMP EX BID BIDSIZ
1 2015-04-01 09:00:00 N 365.00 10
2 2015-04-01 09:00:05 N 367.80 55
3 2015-04-01 09:00:33 N 365.00 10
4 2015-04-01 09:00:36 N 367.80 55
5 2015-04-01 09:00:41 N 365.00 10
6 2015-04-01 09:00:41 N 367.80 55