使用openxlsx读取xlsx时如何避免数值被转换为带小数的格式

问题原因 #

混合值列同时存在数值、字符串两类内容时，openxlsx默认仅扫描前100行判断列类型，如果前100行都是数值就会将列判定为数值型，后续遇到字符串时会强制整列转为字符型，原本的整数值就会保留浮点型的.0后缀，最终出现"1.0" == 1返回FALSE的逻辑异常。

解决方案 #

方案1：读取时优化类型识别（推荐） #

调整read.xlsx的参数，保留自动识别日期特性的同时避免类型误判：

# detectDates=TRUE保留日期自动识别，guessMax=Inf扫描全表判断列类型避免误判
data <- openxlsx::read.xlsx(
  "Data.xlsx", 
  sheet = 1,
  detectDates = TRUE,
  guessMax = Inf
)

如果提前知道列的类型，也可以手动指定colTypes参数锁定列类型，比如指定第3列为数值型，其他列自动识别：

data <- openxlsx::read.xlsx(
  "Data.xlsx", 
  sheet = 1,
  detectDates = TRUE,
  colTypes = c(NA, NA, "numeric", NA) # NA代表自动识别对应列类型
)

方案2：读取后批量清洗数据 #

如果已经完成读取，可以自定义函数清洗带.0后缀的整数字符串，同时保留原本的非数值字符串和带小数的数值：

# 定义清洗函数
clean_mixed_column <- function(col) {
  # 匹配纯整数加.0格式的字符串
  int_like_mask <- grepl("^\\d+\\.0$", col, perl = TRUE)
  # 转换为整数后再转字符，去掉.0后缀
  col[int_like_mask] <- as.character(as.integer(as.numeric(col[int_like_mask])))
  return(col)
}

# 对目标列应用清洗函数，这里假设要处理第2列
data[[2]] <- clean_mixed_column(data[[2]])

如果不需要保留字符串格式，也可以直接将列转为数值型，后续比较逻辑直接用数值判断即可：

data[[2]] <- as.numeric(data[[2]])

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