R语言导入Excel日期字段变为NA的解决方法咨询

问题说明 #

• 数据集含Date字段，Excel内日期格式为20-08-2022（日-月-年）
• 用readxl包导入后，Date字段全部显示为NA
• 尝试readexcel2工具但无法使用
• 当前使用的R代码：

multiplesheets <- function(fname) {
  
  # getting info about all excel sheets
  sheets <- readxl::excel_sheets(fname)
  tibble <- lapply(sheets, function(x) readxl::read_excel(fname, sheet = x))
  data_frame <- lapply(tibble, as.data.frame)
  
  # assigning names to data frames
  names(data_frame) <- sheets
  
  # print data frame
  print(data_frame)
 }

sheets <- multiplesheets("PATH")
a<-sheets$a
b<-sheets$b

解决方案 #

方法1：指定列类型后手动解析日期 #

readxl可能误判日期格式，先把Date列按文本读取，再用lubridate包的dmy()函数（对应日-月-年格式）解析：

library(readxl)
library(lubridate)

multiplesheets <- function(fname) {
  sheets <- excel_sheets(fname)
  tibble <- lapply(sheets, function(x) {
    # 读取时将Date列指定为文本，其他列自动识别（假设Date是第1列，可根据实际调整col_types顺序）
    read_excel(fname, sheet = x, col_types = c("text", rep("guess", ncol(read_excel(fname, sheet=x))-1)))
  })
  data_frame <- lapply(tibble, function(df) {
    # 解析Date列，列名以实际数据集为准
    df$Date <- dmy(df$Date)
    as.data.frame(df)
  })
  names(data_frame) <- sheets
  print(data_frame)
}

sheets <- multiplesheets("PATH")
a <- sheets$a
b <- sheets$b

方法2：增大猜测行数提升识别准确率 #

readxl默认只查看前1000行判断列类型，若前1000行的Date列有异常，会导致识别失败。增大guess_max参数让它扫描更多行：

multiplesheets <- function(fname) {
  sheets <- readxl::excel_sheets(fname)
  tibble <- lapply(sheets, function(x) {
    # 根据数据集大小调整guess_max数值，比如设为10000
    readxl::read_excel(fname, sheet = x, guess_max = 10000)
  })
  data_frame <- lapply(tibble, as.data.frame)
  names(data_frame) <- sheets
  print(data_frame)
}

sheets <- multiplesheets("PATH")
a <- sheets$a
b <- sheets$b

方法3：换用openxlsx包导入 #

如果readxl始终有问题，试试openxlsx，它对日期格式的兼容性更好：

library(openxlsx)

multiplesheets <- function(fname) {
  sheets <- getSheetNames(fname)
  data_frame <- lapply(sheets, function(x) {
    # detectDates参数自动识别日期
    read.xlsx(fname, sheet = x, detectDates = TRUE)
  })
  names(data_frame) <- sheets
  print(data_frame)
}

sheets <- multiplesheets("PATH")
a <- sheets$a
b <- sheets$b

问题原因 #

出现NA的核心原因是：readxl默认按**月-日-年（MDY）识别日期，而你的数据是日-月-年（DMY）**格式，导致无法匹配的日期被转为NA；或者Excel中的日期实际是以文本形式存储，readxl没正确识别为日期类型。

内容的提问来源于stack exchange，提问作者Inuraghe