HTML爬取表格合并单元格数据规整：R语言批量处理方案问询

Got it, let's work through this problem—those merged cell headers that end up cluttering your DataFrame columns are a total headache when scraping HTML tables, especially with 1M+ tables to process. We need a flexible, robust solution that can handle your unstructured data without hardcoding patterns.

The Core Issue #

Your scraped table has grouping labels (like "chicken") scattered alongside actual data values in the same columns. We need to:

1. Identify those grouping labels
2. Propagate them down to all rows belonging to that group
3. Filter out the original label rows and clean up the columns to match your target format

Solution Code (Using Tidyverse) #

First, let's use the tidyverse suite (since you're already using rvest for scraping, this will fit right in):

library(tidyverse)

# Your sample messy DataFrame
df <- data.frame(
  year = c("chicken",2000,2001,2002,"chicken without bones",2003,2004,2005, "chicken without bones and feet", 2006, 2007, 2008), 
  weight = c("chicken",5,6,4,"chicken without bones",2,1,3,"chicken without bones and feet", 1, 1.5, 2),
  stringsAsFactors = FALSE
)

# Clean the data
df_cleaned <- df %>%
  # Step 1: Identify grouping labels (rows where all columns have the same value)
  mutate(new_variable = ifelse(all(year == weight), year, NA)) %>%
  # Step 2: Fill the label down to all subsequent rows until the next label
  fill(new_variable, .direction = "down") %>%
  # Step 3: Remove the original label rows
  filter(!all(year == weight)) %>%
  # Step 4: Fix column types and reorder to match your target
  mutate(
    year = as.integer(year),
    number = as.numeric(weight)
  ) %>%
  select(year, number, new_variable)

# Check the result
print(df_cleaned)

How This Works #

1. Identify Labels: The ifelse(all(year == weight), year, NA) checks if every column in a row has the same value—this is a telltale sign of a merged cell that got filled across columns by html_table(fill = TRUE). We store this value as our grouping label.
2. Fill Labels: fill(new_variable, .direction = "down") propagates the label downwards, replacing NAs until it hits the next new label.
3. Filter Rows: We drop the original label rows (where all columns matched) since we don't need them anymore.
4. Clean Columns: We convert the year and weight columns to proper numeric types and reorder them to match your desired df2 format.

Making It Universal for Your 1M+ Tables #

Since your tables don't have a fixed pattern, this logic is flexible:

• If some tables have labels that don't match across all columns, adjust the label check to look for non-numeric values in your data column (e.g., ifelse(is.na(as.numeric(year)), year, NA) instead of the all() check).
• Wrap this code in a function to apply it across all your scraped tables:

  clean_scraped_table <- function(messy_df) {
    messy_df %>%
      mutate(new_variable = ifelse(all(.[[1]] == .[[2]]), .[[1]], NA)) %>%
      fill(new_variable, .direction = "down") %>%
      filter(!all(.[[1]] == .[[2]])) %>%
      mutate(
        year = as.integer(.[[1]]),
        number = as.numeric(.[[2]])
      ) %>%
      select(year, number, new_variable)
  }
  
  # Apply to all your tables (e.g., if stored in a list called table_list)
  cleaned_tables <- map(table_list, clean_scraped_table)
  

This function uses column positions (.[[1]], .[[2]]) instead of hardcoded names, so it works even if your tables have different column headers.

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