如何用R修改代码拆分HTML表格中Location列的<br>分隔内容为两列？

我已经成功用R抓取网站HTML表格并合并为单个dataframe，但Location列的HTML单元格包含<br>分隔的两部分内容（例如<td class="nowrap">LMS - City 1, 2, 3 YSA<br><small>612 Glen Rd</small></td>），目前在dataframe中这两部分内容直接拼接成了“LMS - City 1, 2, 3 YSA612 Glen Rd”，需要将其拆分为Location名称（如“LMS - City 1, 2, 3 YSA”）和地址（如“612 Glen Rd”）两列。

修改后的完整代码 #

######### Import Library #########
library(dplyr)
library(tidyverse)
library(writexl)
library(rvest)
library(httr)


######### Pull data from Website ########### 
start.time <- Sys.time()

# Enter login URL
login_url <- "URL"

# Create a session
session <- html_session(login_url)

# Extract the form from the login page
form <- html_form(session)[[1]]

# Fill in the login credentials
filled_form <- set_values(form, 
                          username = "name",
                          password = "pass")

# Submit the form
submit_form(session, filled_form)

# Initialize an empty list to store table data
all_tables <- list()

# Loop over each page
for (i in 1:6) {
  # Construct the URL for each page
  page_url <- paste0("URL.asp?pgNum=", i, "&sortBy=&sortOrder=ASC")

  # Jump to page
  page <- session %>% 
    jump_to(page_url)
  
  # Parse the HTML content of the page
  parsed_page <- page %>% read_html()
  
  # 提取目标表格（按ID定位更准确）
  target_table <- parsed_page %>% html_node("#reportTable")
  
  # 提取表格的所有行
  rows <- target_table %>% html_nodes("tr")
  
  # 提取表头
  headers <- rows[1] %>% html_nodes("th") %>% html_text(trim = TRUE)
  
  # 处理数据行（跳过表头行）
  data_rows <- rows[-1] %>% map_dfr(function(row) {
    cells <- row %>% html_nodes("td")
    
    # 提取每个单元格的基础文本
    cell_texts <- map_chr(cells, function(cell) {
      html_text(cell, trim = TRUE)
    })
    
    # 拆分Location列：提取small标签内容为地址，剩余文本为Location名称
    location_cell <- cells[3]
    location_name <- location_cell %>% html_node(xpath = "./text()") %>% html_text(trim = TRUE)
    address <- location_cell %>% html_node("small") %>% html_text(trim = TRUE)
    
    # 替换原Location列，新增地址列
    cell_texts[3] <- location_name
    tibble(!!!set_names(c(cell_texts, address), c(headers, "地址")))
  })
  
  # Append table data to the list
  all_tables <- c(all_tables, list(data_rows))
}

# Combine all tables into one data frame
combined_table <- do.call(rbind, all_tables)

# Write combined table to Excel file
write.xlsx(combined_table, "excel_file.xlsx", row.names = FALSE, col.names = TRUE)

end.time <- Sys.time()
time.taken <- round(end.time - start.time, 2)
time.taken

关键修改说明 #

1. 精准定位目标表格：改用html_node("#reportTable")按表格ID提取，避免抓取页面中其他无关表格
2. 手动处理单元格内容：不再依赖html_table的批量提取，而是逐行逐单元格处理，重点拆分Location列：
   • 通过XPath提取<br>标签之前的文本作为Location名称
   • 直接提取<small>标签内的文本作为地址
3. 整合拆分结果：将拆分后的两列替换原Location列并新增地址列，保证数据结构符合需求

参考HTML结构 #

<table id="reportTable" class="table table-hover table-responsive" border="0" cellspacing="0" cellpadding="0" style="page-break-before:always !important;">
    <tbody>
        <tr>
            <th class="first"><a class="hidden-print" href="URL.asp?sortBy=TIMEID&amp;sortOrder=ASC&amp;pgSize=10000&amp;pgNum=1">ID</a><span class="visible-print">ID</span></th>
            <th><a class="hidden-print" href="URL.asp?sortBy=UserName&amp;sortOrder=ASC&amp;pgSize=10000&amp;pgNum=1">Name</a><span class="visible-print">Name</span></th>
            <th><a class="hidden-print" href="URL.asp?sortBy=LocationName&amp;sortOrder=ASC&amp;pgSize=10000&amp;pgNum=1">Location</a><span class="visible-print">Location</span></th>
            <th><a class="hidden-print" href="URL.asp?sortBy=TaskName&amp;sortOrder=ASC&amp;pgSize=10000&amp;pgNum=1">Tasks</a><span class="visible-print">Tasks</span></th>
            <th>Field Notes</th>
            <th><a class="hidden-print" href="URL.asp?sortBy=StartTime&amp;sortOrder=ASC&amp;pgSize=10000&amp;pgNum=1">Start</a><span class="visible-print">Start</span></th>
            <th><a class="hidden-print" href="URL.asp?sortBy=Completedtime&amp;sortOrder=ASC&amp;pgSize=10000&amp;pgNum=1">Finish</a><span class="visible-print">Finish</span></th>
            <th>Min</th>
            <th class="last"><a class="hidden-print" href="URL.asp?sortBy=CostEstimate&amp;sortOrder=ASC&amp;pgSize=10000&amp;pgNum=1">Charge</a><span class="visible-print">Charge</span></th>
        </tr>
        <tr>
            <td class="text-center"><small><a href="/timerecords.asp?action=Edit&amp;TIMEID=742900" target="_blank">742900</a></small></td>
            <td class="nowrap">Business1</td>
            <td class="nowrap">LMS - City 1, 2, 3 YSA<br><small>612 Glen Rd</small></td>
            <td class="nowrap">Door Repair</td>
            <td></td>
            <td class="nowrap">7/9/2024 1:00 AM</td>
            <td class="nowrap"> 1:00 AM</td>
            <td>0</td>
            <td class="text-right">195</td>
        </tr>
        <tr>
            <td class="text-center"><small><a href="/timerecords.asp?action=Edit&amp;TIMEID=742899" target="_blank">742899</a></small></td>
            <td class="nowrap">Business2</td>
            <td class="nowrap">LLL Station 123 - Emerson<br><small>1234 Legend Road</small></td>
            <td class="nowrap">Septic Pump</td>
            <td></td>
            <td class="nowrap">7/4/2024 1:00 AM</td>
            <td class="nowrap"> 1:00 AM</td>
            <td>0</td>
            <td class="text-right">956</td>
        </tr>
    </tbody>
</table>

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