如何用列表推导式生成与Pandas一致的表格列标题？

我尝试用列表推导式爬取某网页表格的列标题，但生成的结果和Pandas生成的差异显著，Pandas生成的标题能覆盖所有列。

我的代码 #

from bs4 import BeautifulSoup
import pandas as pd
import requests

link = 'https://www03.cmhc-schl.gc.ca/hmip-pimh/en/TableMapChart/TableMatchingCriteria?GeographyType=SurveyZone&GeographyId=011002&CategoryLevel1=Primary%20Rental%20Market&CategoryLevel2=Vacancy%20Rate%20%28%25%29&ColumnField=2&RowField=TIMESERIES'

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36',
    'X-Requested-With': 'XMLHttpRequest',
}

res = requests.get(link,headers=headers)
soup = BeautifulSoup(res.text,"html.parser")

col_headers = [item.text.replace("\xa0","Unnamed: 0") for i in soup.select("table.CawdDataTable thead tr") for item in i.select("th, td")]
print(col_headers)

selector = soup.select_one("table.CawdDataTable")
df = pd.read_html(str(selector))[0]
print(list(df.columns))

输出结果 #

['Unnamed: 0', 'Bachelor', '1 Bedroom', '2 Bedroom', '3 Bedroom +', 'Total']
['Unnamed: 0', 'Bachelor', 'Bachelor.1', '1 Bedroom', '1 Bedroom.1', '2 Bedroom', '2 Bedroom.1', '3 Bedroom +', '3 Bedroom +.1', 'Total', 'Total.1']

请问我能否用列表推导式生成与Pandas一致的列标题？

可以实现。差异出现的核心原因是：网页表格的<thead>包含两行表头，Pandas解析时会自动识别重复列名，并通过添加.1、.2后缀区分；而你原列表推导式仅提取了表头文本，未处理重复名称的情况。

要模拟Pandas的命名逻辑，只需在提取文本后增加重复名称的处理步骤：

1. 先提取所有表头单元格的文本（保留原\xa0替换逻辑）
2. 用字典跟踪每个名称的出现次数，首次出现用原名，后续重复名称添加.次数后缀（次数从1开始）

修改后的代码片段 #

# 提取所有表头原始文本
raw_headers = [item.text.replace("\xa0", "Unnamed: 0") for i in soup.select("table.CawdDataTable thead tr") for item in i.select("th, td")]

# 模拟Pandas重复列名处理逻辑
name_counter = {}
col_headers = []
for name in raw_headers:
    if name not in name_counter:
        name_counter[name] = 0
        col_headers.append(name)
    else:
        name_counter[name] += 1
        col_headers.append(f"{name}.{name_counter[name]}")

print(col_headers)

运行后输出的列标题将与Pandas生成的完全一致：

['Unnamed: 0', 'Bachelor', 'Bachelor.1', '1 Bedroom', '1 Bedroom.1', '2 Bedroom', '2 Bedroom.1', '3 Bedroom +', '3 Bedroom +.1', 'Total', 'Total.1']

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