使用read_excel导入Excel时NR被误转成NA的问题求助

方法1：强制指定目标列类型为字符型 #

问题根源是read_excel自动将包含"NR"的列推断为数值型，导致非数值的"NR"被强制转为NA。通过col_types参数指定该列为字符型，即可保留"NR"，同时按要求处理其他缺失值：

library(readxl)
# 按列位置指定（示例：第15列为目标列，根据实际调整）
df <- read_excel("path", 
                 col_names = TRUE, 
                 na = c("n/a", "N/A", "n/A", "N/a","na","NA", "n a",""),
                 col_types = c(rep("guess", 14), "text", rep("guess", 58-15)))

# 按列名指定（更直观，替换"目标列名"为实际列名）
df <- read_excel("path", 
                 col_names = TRUE, 
                 na = c("n/a", "N/A", "n/A", "N/a","na","NA", "n a",""),
                 col_types = list(目标列名 = "text", .default = "guess"))

方法2：先全量导入再批量替换缺失值 #

先不设置na参数导入数据（确保"NR"完整保留），再手动将各种缺失值标识替换为NA，灵活性更高：

library(readxl)
library(dplyr)

# 先导入所有原始值
df <- read_excel("path", col_names = TRUE)

# 批量替换指定缺失值为NA，保留"NR"
df <- df %>%
  mutate(across(everything(), ~case_when(
    .x %in% c("n/a", "N/A", "n/A", "N/a","na","NA", "n a", "") ~ NA_character_,
    TRUE ~ .x
  )))

# 可选：将原本为数值型的列自动转回数值类型
df <- type.convert(df, as.is = TRUE)

方法3：简化版列类型指定 #

如果仅需确保某列的"NR"不被转NA，直接给该列指定字符型即可，其他列保持自动推断：

df <- read_excel("path", 
                 col_names = TRUE, 
                 na = c("n/a", "N/A", "n/A", "N/a","na","NA", "n a",""),
                 col_types = list(目标列名 = "text"))

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