在R中循环导入CSV文件时，如何可靠地将指定列导入为double类型并正确识别含分隔符的多格式NA值

Hey there, let's work through this tricky CSV import issue you're facing. It sounds like the combination of weird NA values with commas and inconsistent quoting is throwing R for a loop—literally! Here are two solid approaches to fix this, depending on whether you prefer base R or the tidyverse's more robust tools.

Tidyverse (readr) Approach (Recommended) #

The readr package handles messy CSV data far more gracefully than base R's read.csv, especially when dealing with inconsistent quoting and custom NA patterns.

First, install and load the package if you haven't already:

install.packages("readr")
library(readr)

Then update your loop with this code:

for (E in EDCODES) { 
  # Use file.path for cross-platform safe path construction
  Filename <- file.path(".", "Data", "2. Liabilities", E) 
  Framename <- gsub("\\..*", "", E) 
  
  # Read CSV with explicit controls for NA values and column types
  df <- read_csv(
    Filename,
    # Match all your special NA patterns with regex
    na = c("\"ND", "5\"", "ND,5", "ND, \\d+", "ND, \\d+, \\d{4}-\\d{2}"),
    # Force BAA35 to be numeric; read other columns as character first to avoid shifts
    col_types = cols(
      BAA35 = col_double(),
      .default = col_character()
    ),
    # Disable quote parsing to fix EOF warnings and prevent comma-split NA values
    quote = "",
    locale = locale(encoding = "UTF-8")
  )
  
  assign(Framename, df)
}

Why this works: #

• file.path avoids manual path concatenation errors across Windows/macOS/Linux
• The na parameter accepts regular expressions, so we can catch all your special NA formats (like ND, 4, 2023-10) in one go
• Explicit col_types ensures BAA35 is always numeric, while reading other columns as character prevents accidental column shifting from type inference errors
• quote = "" disables quote parsing entirely, eliminating the "EOF within quoted string" warnings and stopping values like ND,5 from being split into two columns

Base R Approach #

If you prefer sticking to base R, we can adjust the workflow to first read all columns as character, then clean NA values and convert BAA35 separately:

for (E in EDCODES) { 
  Filename <- file.path(".", "Data", "2. Liabilities", E)
  Framename <- gsub("\\..*", "", E) 
  
  # Read ALL columns as character to avoid parsing errors upfront
  df <- read.csv(
    Filename,
    header = TRUE,
    sep = ",",
    stringsAsFactors = FALSE,
    na.strings = c(), # Skip NA detection for now
    colClasses = rep("character", 78), # Match your 78 columns
    encoding = "UTF-8",
    quote = "" # Disable quotes to prevent splitting
  )
  
  # Define a regex pattern to match all your special NA values
  na_pattern <- "^(\"ND|5\"|ND,5|ND, \\d+|ND, \\d+, \\d{4}-\\d{2})$"
  
  # Replace all matching values with NA across all columns
  df[] <- lapply(df, function(col) {
    col[grepl(na_pattern, col)] <- NA
    return(col)
  })
  
  # Convert BAA35 to numeric type
  df$BAA35 <- as.double(df$BAA35)
  
  assign(Framename, df)
}

Why this works: #

• Reading all columns as character ensures no premature parsing errors or column shifts
• Using regex to target all NA patterns gives you full control over what gets marked as missing
• Converting BAA35 separately guarantees it ends up as numeric, even after cleaning

Key Takeaways #

• Disable quote parsing with quote = "": This is the most critical fix for preventing ND,5 from splitting into two columns and stopping those EOF warnings.
• Explicit column typing: Never rely on automatic type inference for messy data—either specify types upfront or read as character first and convert later.
• Regex for NA matching: Instead of listing every single NA variant, use regex to cover all patterns in one line.

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