读取包含日期和时间的 csv

发布于 2025-01-07 16:54:50 字数 969 浏览 3 评论 0原文

我正在 R 中工作并阅读 csv，其中第一列中有日期和时间。我想先在R中导入这个csv文件，然后将其转换为zoo对象。

我正在使用 R 中的代码

EURUSD <- as.xts(read.zoo("myfile.csv",sep=",",tz="",header=T))

我的 csv 文件包含以下格式的数据：

Date,Open,Low,High,Close
2006-01-02 10:01:00,2822.9,2825.45,2822.1,2824.9
2006-01-02 10:02:00,2825,2825.9,2824,2824.95
2006-01-02 10:03:00,2824.55,2826.45,2824,2826.45
2006-01-02 10:04:00,2826.45,2826.45,2824.9,2825.5
2006-01-02 10:05:00,2825.15,2825.5,2824,2824.85
2006-01-02 10:06:00,2824.7,2825.5,2823.7,2823.8
2006-01-02 10:07:00,2823.95,2824.45,2823.55,2824
2006-01-02 10:08:00,2824,2824.85,2823.5,2824.85
2006-01-02 10:09:00,2824.25,2825.45,2824,2825.45
2006-01-02 10:10:00,2825.2,2827,2825,2827

当我运行上述命令将数据导入到 RI 时，出现以下错误：

Error in as.POSIXlt.character(x, tz, ...) : 
  character string is not in a standard unambiguous format

我试图找到解决问题的所有方法。我在网上读了很多博客，但没有一个方法适合我。

我希望有人能帮助我。

原文

I am working in R and reading csv which has date and time in its first column.
I want to import this csv file in R first and then convert it to zoo obect.

I am using the code in R

EURUSD <- as.xts(read.zoo("myfile.csv",sep=",",tz="",header=T))

My csv file contain data in the format:

Date,Open,Low,High,Close
2006-01-02 10:01:00,2822.9,2825.45,2822.1,2824.9
2006-01-02 10:02:00,2825,2825.9,2824,2824.95
2006-01-02 10:03:00,2824.55,2826.45,2824,2826.45
2006-01-02 10:04:00,2826.45,2826.45,2824.9,2825.5
2006-01-02 10:05:00,2825.15,2825.5,2824,2824.85
2006-01-02 10:06:00,2824.7,2825.5,2823.7,2823.8
2006-01-02 10:07:00,2823.95,2824.45,2823.55,2824
2006-01-02 10:08:00,2824,2824.85,2823.5,2824.85
2006-01-02 10:09:00,2824.25,2825.45,2824,2825.45
2006-01-02 10:10:00,2825.2,2827,2825,2827

When I run the above command to import the data in to R I get the folowwwing error :

Error in as.POSIXlt.character(x, tz, ...) : 
  character string is not in a standard unambiguous format

I tried to find all the ways to sort out the issue. I read so many blogs over net but none of the method works for me.

I hope someone would help me.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情绪少女 2025-01-14 16:54:50

虽然这似乎是一篇旧文章，但我想分享我的经验，因为我经历了一个类似的非常令人沮丧的过程，试图将时间序列 csv 数据加载到 R 中。
上面的问题是excel把日期和时间的格式改成了下面的%m/%d/%Y %H:%M，基本上就是把秒去掉了。如果您读取具有此格式的文件并且您有第二个分辨率数据，您将获得多个相似的日期时间组合。因此您不能简单地使用忽略秒的格式，因为它会给出以下错误消息。 “字符串不是标准的明确格式”

解决方案是返回 Excel，将日期时间列的格式更改为 %m/%d/%Y %H:%M:%S< /代码>。您可以通过选择与所需格式最接近的日期时间默认格式（在本例中为 %m/%d/%Y %H:%M）然后手动添加来实现此目的：将文件保存为 csv 文件，然后使用以下命令读取它：

Data<-read.zoo("file.csv", tz="", header=TRUE,format='%m/%d/%Y %H:%M:%S')

这对我有用，我读取了大约 900K 行的文件。

Although this seems to be an old post, but I want to share my experience since I went through a similar very frustrating process trying to load time series csv data into R.
The problem above is that excel changes the format of the date and time to the following %m/%d/%Y %H:%M, basically it drops the seconds. If you read a file with this format and you have a second resolution data you get multiple date time combinations that are similar. so you cannot simply use the format that ignores seconds because it gives the following error message . "character string is not in a standard unambiguous format"

The solution is to go back to excel and change the format of the date time column to be %m/%d/%Y %H:%M:%S. You can do that by choosing the closest date time default formats to the desired format (in this case it is %m/%d/%Y %H:%M and then manually add :ss at the end. Save the file as a csv file and then read it using the following command:

Data<-read.zoo("file.csv", tz="", header=TRUE,format='%m/%d/%Y %H:%M:%S')

This worked for me and I read a file that has about 900K rows.

回复收藏 0 原文

落在眉间の轻吻 2025-01-14 16:54:50

看起来错误是由于 R 无法识别您的日期列的格式（它无法计算出 - 日期/月/年？月/日期/年？等等）。

您可以使用 read.zoo 的 format 参数告诉 R 它的格式是什么（请参阅 ?strptime 了解您可以使用的说明符）。

例如，如果是日期/月/年小时（24 小时制）：分钟，您可以执行以下操作：（

EURUSD <- as.xts(read.zoo(file_name,
                          sep=',', 
                          tz='',   
                          header=T,
                          format='%d/%m/%Y %H:%M:%S')) # see the 'format' argument?

注意 - 在您的问题中，您显示的 csv 数据片段不是以逗号分隔的）。

It looks like the error is due to R not recognising what format your date column is in (it can't work out -- date/month/year? month/date/year? etc).

You can tell R what format it is in using the format argument to read.zoo (see ?strptime for the specifiers you can use).

For example, if it was date/month/year hour(24-hour clock):minutes, you could do:

EURUSD <- as.xts(read.zoo(file_name,
                          sep=',', 
                          tz='',   
                          header=T,
                          format='%d/%m/%Y %H:%M:%S')) # see the 'format' argument?

(Note - in your question the snippet of csv data you showed isn't comma-delimited).

回复收藏 0 原文

烛影斜 2025-01-14 16:54:50

当日期列就像一个字符时，不使用 as.xtc 读取文件。然后使用此函数将日期转换为 POSIXlt 类：

library("chron")
DateConvert<-function(x){
  dt<-strsplit(x,split = "T")
  dt<-unlist(dt)
  d1<-dt[1:length(dt) %% 2==1 ]
  d2<-dt[1:length(dt) %% 2==0 ]
  a<-as.POSIXlt(chron(dates.=d1, times.=d2, format = c(dates = "y-m-d", times = "h:m:s")))
  return(a)
}

DateConvert('Your column')

然后对数据使用函数 as.xts 。

Read the file without using as.xtc, when the date column is like a character. And then convert the dates to POSIXlt class with this function:

library("chron")
DateConvert<-function(x){
  dt<-strsplit(x,split = "T")
  dt<-unlist(dt)
  d1<-dt[1:length(dt) %% 2==1 ]
  d2<-dt[1:length(dt) %% 2==0 ]
  a<-as.POSIXlt(chron(dates.=d1, times.=d2, format = c(dates = "y-m-d", times = "h:m:s")))
  return(a)
}

DateConvert('Your column')

and just then use the function as.xts on you data.

回复收藏 0 原文

~没有更多了~