如何基于现有Excel数据批量补充帆船属性信息？

以下是针对2500条帆船数据批量补充beam、draft、displacement等属性的几种实用方法：

1. Python爬虫批量抓取 #

通过编写简单的爬虫脚本，利用现有数据中的Make和Variant作为关键词，从专业帆船数据网站抓取目标属性，再与原表合并：

• 核心流程：
  1. 用pandas读取原始Excel数据；
  2. 编写抓取函数，针对目标网站的结构解析beam、draft、displacement字段；
  3. 批量遍历数据条目执行抓取，将结果存入新列；
  4. 保存更新后的数据集回Excel。
• 简化代码示例：

import pandas as pd
import requests
from bs4 import BeautifulSoup
import time

# 读取原始数据
df = pd.read_excel("sailboat_prices.xlsx")

def fetch_specs(make, variant):
    # 替换为实际目标网站的URL构造规则
    target_url = f"https://example-sailboat-db.com/search?make={make.replace(' ', '%20')}&model={variant.replace(' ', '%20')}"
    headers = {"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36"}
    
    try:
        response = requests.get(target_url, headers=headers, timeout=10)
        soup = BeautifulSoup(response.text, "html.parser")
        # 替换为目标网站对应的元素定位逻辑
        beam = soup.find("div", id="beam-value").text.strip() if soup.find("div", id="beam-value") else "N/A"
        draft = soup.find("div", id="draft-value").text.strip() if soup.find("div", id="draft-value") else "N/A"
        displacement = soup.find("div", id="displacement-value").text.strip() if soup.find("div", id="displacement-value") else "N/A"
        return beam, draft, displacement
    except Exception as e:
        print(f"Failed to fetch {make} {variant}: {str(e)}")
        return "N/A", "N/A", "N/A"

# 批量抓取并添加列
df[["beam", "draft", "displacement"]] = df.apply(lambda row: pd.Series(fetch_specs(row["Make"], row["Variant"])), axis=1)
# 添加请求延迟避免反爬
time.sleep(1)

# 保存结果
df.to_excel("sailboat_prices_with_specs.xlsx", index=False)

• 注意事项：添加请求延迟避免触发反爬机制；提前处理关键词中的特殊字符；对抓取失败的条目标记为N/A便于后续处理。

2. Excel Power Query 可视化批量抓取 #

无需代码，利用Excel内置的Power Query工具实现网页数据批量提取与合并：

• 操作步骤：
  1. 将原始数据导入Power Query（数据选项卡 → 自表格/区域）；
  2. 添加自定义列，用Make和Variant拼接成目标网站的查询URL；
  3. 对自定义列使用“从Web”功能，批量加载网页内容；
  4. 从加载的网页中提取beam、draft、displacement字段（可通过选择网页元素或CSS选择器定位）；
  5. 合并提取的属性列与原数据集，加载回Excel。
• 优势：可视化操作门槛低，适合无编程基础的用户；自带数据清洗功能，便于处理空值和格式不一致问题。

3. 调用公开帆船数据API #

如果存在公开的船舶数据API，可通过脚本批量请求获取结构化属性：

• 核心流程：
  1. 确认API的请求参数（通常需要Make和Variant作为查询条件）与返回格式（多为JSON）；
  2. 用Python或Excel VBA编写批量请求脚本，解析返回的JSON数据提取目标属性；
  3. 将解析结果合并到原始Excel表中。
• VBA简化示例（需提前引入VBA-JSON库解析返回数据）：

Sub BatchGetBoatSpecs()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("RawData")
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim xmlhttp As Object
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    Dim jsonObj As Object
    Dim makeStr As String, variantStr As String
    
    For i = 2 To lastRow
        makeStr = Replace(ws.Cells(i, "Make").Value, " ", "%20")
        variantStr = Replace(ws.Cells(i, "Variant").Value, " ", "%20")
        apiUrl = "https://api-sailboat-db.com/v1/boats?make=" & makeStr & "&model=" & variantStr
        
        xmlhttp.Open "GET", apiUrl, False
        xmlhttp.SetRequestHeader "User-Agent", "Mozilla/5.0"
        xmlhttp.Send
        
        If xmlhttp.Status = 200 Then
            Set jsonObj = ParseJson(xmlhttp.responseText)
            ws.Cells(i, "beam").Value = jsonObj("specs")("beam")
            ws.Cells(i, "draft").Value = jsonObj("specs")("draft")
            ws.Cells(i, "displacement").Value = jsonObj("specs")("displacement")
        Else
            ws.Cells(i, "beam").Value = "API Error"
        End If
        Application.Wait Now + TimeValue("00:00:01") ' 添加延迟避免触发API限制
    Next i
End Sub

• 注意事项：提前确认API的调用额度、频率限制；部分API需要申请密钥，需在请求头中携带认证信息。

4. 结构化数据集批量匹配 #

如果能找到包含目标属性的现成结构化数据集（如CSV/Excel格式的帆船数据库），可通过匹配字段快速补充：

• 操作步骤：
  1. 清洗原始数据与目标数据集的Make和Variant字段（统一大小写、去除多余空格、修正缩写）；
  2. 用Excel的XLOOKUP函数或Power Query的“合并查询”功能，以Make+Variant为匹配键，批量将目标属性导入原始表；
  3. 检查匹配结果，手动处理少量匹配失败的条目。
• 优势：速度最快，无需网络请求；适合数据格式规范的场景。

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