使用R从Excel工作表读取时如何检测时间 [英] How to detect TIME when reading from an excel sheet using R

问题描述

问题是，当我使用openxlsx包中的read.xlsx从excel工作表中将其读取到R中时，TIME列将转换为分数.

The issue is that when I read from an excel sheet into R using read.xlsx from openxlsx package, the TIME column is converted into a fraction.

这里是一个例子，

dfin <-
DATE          TIME
15/02/2015    8:00 AM
22/01/2014    10:00 PM

library(openxlsx)
test <-  read.xlsx("dfin.xlsx", sheet = 1,
                 detectDates=TRUE, skipEmptyRows = TRUE,
                 skipEmptyCols = TRUE, rows = NULL, cols = NULL, check.names = FALSE,
                 namedRegion = NULL, na.strings = "NA", fillMergedCells = FALSE) 

输出:

  DATE        TIME
  2015-02-15  0.3333333
  2014-01-22  0.9166667

我不确定为什么要这样做，也不确定是否有办法解决此问题，因为我需要同时使用DATE和TIME进行一些计算.

I am not sure why it does that and whether there is a way to fix that as I need to use both DATE and TIME to do some calculations.

推荐答案

R确实没有时间格式，所以我建议使用read_excel来读取它，它会自动检测列的类型.这样会将其转换为带有随机日期的日期时间格式，然后可以将其删除，然后再将其转换为适当的时间戳.

R doesn't have a time format really, so I suggest reading it in using read_excel, which automatically detects the column type. This will turn it into a date-time format with a random date, which you can then remove, before converting it into a proper timestamp.

library(readxl)
library(lubridate)

test <- read_excel('dfin.xlsx',trim_ws = TRUE) %>%
  #return the TIME column to the way it is written in Excel
  mutate(TIME = as.character(gsub(".* ","",TIME)),
  #format the date column
     DATE = dmy(DATE),
  #turn it into a timestamp
     TIMESTAMP = as.POSIXct(paste(DATE,TIME)))

这篇关于使用R从Excel工作表读取时如何检测时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！