使用R从URL导入表格，数值列存为字符型的批量解决方法求助

Hey there! I’ve run into this exact problem with German-formatted numbers (where . is the thousands separator and , is the decimal) before—let’s work through a solution that works for single data frames and scales to multiple ones too.

Why Your Previous Attempts Failed #

The dec="," argument in html_table() tells R to treat commas as decimal points, but it doesn’t handle thousands separators (the dots). So your column ends up as character type because R can’t parse values like 1.234,56 directly into numbers. We need to first strip out the thousands separators, then swap the decimal commas to dots, and finally convert to numeric.

Step 1: Fix a Single Column (for Testing) #

First, let’s confirm the fix on your problematic column Ca[,4]:

# Strip thousands separators (dots), swap commas to dots, then convert
Ca[,4] <- as.numeric(gsub(",", ".", gsub("\\.", "", Ca[,4])))

Note the double backslash \\.—this escapes the dot in regex, since a single dot matches any character.

Step 2: Function to Batch Convert a Single Data Frame #

To avoid repeating this for every column, let’s make a reusable function that automatically detects and converts character columns with German numeric formatting:

convert_german_numerics <- function(df, keep_non_numeric = FALSE) {
  # Loop through each column in the data frame
  for (col_name in colnames(df)) {
    col <- df[[col_name]]
    # Check if the column is character and contains dots/commas
    if (is.character(col) && any(grepl("[.,]", col))) {
      # Clean the values: remove thousands dots, swap commas to dots
      cleaned <- gsub(",", ".", gsub("\\.", "", col))
      # Convert to numeric
      numeric_col <- as.numeric(cleaned)
      
      # Optional: Keep non-numeric values instead of turning them to NA
      if (keep_non_numeric) {
        numeric_col[is.na(numeric_col)] <- col[is.na(numeric_col)]
      }
      
      df[[col_name]] <- numeric_col
    }
  }
  return(df)
}

# Apply it to your Ca data frame
Ca <- convert_german_numerics(Ca)

Step 3: Batch Process Multiple Data Frames (e.g., from HTML Import) #

If you’re importing multiple tables from HTML (which html_table() returns as a list), use lapply() to run the function on every data frame in the list:

# Import all tables from the URL
url <- "https://lebensmittel-naehrstoffe.de/calciumhaltige-lebensmittel/"
page <- read_html(url)
tables_list <- html_table(page, fill = TRUE, dec = ",")

# Convert all tables in the list
processed_tables <- lapply(tables_list, convert_german_numerics)

# Extract your specific Ca data frame (adjust the index as needed)
Ca <- processed_tables[[1]]

Bonus: Handle Edge Cases #

If some cells in your columns aren’t numbers (e.g., text labels), the keep_non_numeric parameter in the function will preserve those values instead of turning them into NA. Just set it to TRUE when calling the function:

Ca <- convert_german_numerics(Ca, keep_non_numeric = TRUE)

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