读取日期从Excel到R [英] Reading in dates from Excel into R

问题描述

我有多个 csv 文件，我需要读入R.文件的第一列包含日期和时间，我将其转换为 POSIXlt 当我加载数据框时。我的每个 csv 文件都在Excel中以相同的方式格式化日期和时间，但是，某些文件的读取方式不同。

I have multiple csv files which I need to read into R. The first column of the files contain dates and times, which I am converting into POSIXlt when I have loaded the data frame. Each of my csv files have the dates and times formatted in the same way in Excel, however, some files are read in differently.

例如，

导入后我的文件如下所示：

My file looks like this once imported:

  date                value
1 2011/01/01 00:00:00 39
2 2011/01/01 00:15:00 35
3 2011/01/01 00:30:00 38
4 2011/01/01 00:45:00 39
5 2011/01/01 01:00:00 38
6 2011/01/01 01:15:00 38

因此，我用来修改的代码格式为：

Therefore, the code I use to amend the format is:

DATA$date <- as.POSIXlt(DATA$date,format="%Y/%m/%d %H:%M:%S")

但是，正在读取一些文件as：

However, some files are being read in as:

  date             value
1 01/01/2011 00:00 39
2 01/01/2011 00:15 35
3 01/01/2011 00:30 38
4 01/01/2011 00:45 39
5 01/01/2011 01:00 38
6 01/01/2011 01:15 38

这意味着我的代码的格式部分确实如此不工作，并给出错误。因此，无论如何都要自动检测 date 列所在的格式？或者，有没有办法知道它将如何被读取，因为Excel中列的格式在两者上是相同的。

Which means my format section of my code does not work and gives an error. Therefore, is there anyway to automatically detect which format the date column is in? Or, is there a way of knowing how it will be read, since the format of the column in Excel is the same on both.

推荐答案

当您使用错误的格式字符串进行日期输入时，我似乎得到 NA 值。如果是这种情况，则分两步解决此问题。首先，假设您有三小时，分钟和秒来格式化Excel中的日期：

When using the wrong formatting string for your date input, I seem to get NA values. If this be the case, you solve this problem in two steps. First, format the dates from Excel assuming that you have all three of hours, minutes, and seconds:

date.original <- DATA$date
DATA$date <- as.POSIXlt(DATA$date,format="%Y/%m/%d %H:%M:%S")

这应该在日期中留下 NA 值那些缺少秒的日期的列。然后你可以试试这个：

This should leave NA values in the date column for those dates which be missing seconds. Then you can try this:

DATA$date[is.na(DATA$date)] <- as.POSIXlt(date.original, format="%Y/%m/%d %H:%M")

这应该包括剩余数据。

数据

DATA <- data.frame(date=c('2011/01/01 00:00:00', '2011/01/01 00:15',
                          '2011/01/01 00:30:00', '2011/01/01 00:45'),
                   value=c(39, 35, 38, 39))

这篇关于读取日期从Excel到R的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！