Scrapy爬取FCA手册后，如何将数据按预期格式导出至Excel？

我用Scrapy爬取FCA手册的指定章节，已获取全部数据，但生成的Excel里，unique_ids、topics、clauses字段都是逗号分隔的字符串，无法匹配预期的“每个条目单独一行”的格式。

当前代码 #

Spider代码 #

import scrapy
from urllib.parse import urlencode
from scrapy.selector import Selector


class HandBook(scrapy.Spider):
    name = "handbook_spider"

    custom_settings = {
        "LOG_FILE": "handbook_spider.log",
        "ITEM_PIPELINES": {
            "handbook_spider.pipelines.HandbookExcelPipeline": 300,
        },
    }

    headers = {
        "authority": "www.handbook.fca.org.uk",
        "accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
        "accept-language": "en,ru;q=0.9",
        "cache-control": "max-age=0",
        "sec-ch-ua": '"Chromium";v="106", "Yandex";v="22", "Not;A=Brand";v="99"',
        "sec-ch-ua-mobile": "?0",
        "sec-ch-ua-platform": '"Linux"',
        "sec-fetch-dest": "document",
        "sec-fetch-mode": "navigate",
        "sec-fetch-site": "cross-site",
        "sec-fetch-user": "?1",
        "upgrade-insecure-requests": "1",
        "user-agent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/106.0.0.0 YaBrowser/22.11.3.832 (beta) Yowser/2.5 Safari/537.36",
    }

    params = {
        "date": "2030-12-01",
        "timeline": "True",
        "view": "chapter",
    }

    url = "https://www.handbook.fca.org.uk/handbook/PRIN/2A/?"

    def start_requests(self):
        base_url = self.url + urlencode(self.params)
        yield scrapy.Request(
            url=base_url, headers=self.headers, callback=self.parse_details
        )

    def parse_details(self, response):
        item = {}
        item["unique_ids"] = ""
        item["chapter_ref"] = ""
        item["chapter"] = ""
        item["topics"] = ""
        item["clauses"] = ""
        handbook_content = response.css("div.handbook-content").extract()
        for content in handbook_content:
            str_content = Selector(text=content)
            item["unique_ids"] = str_content.css(
                "div.details > span.extended::text"
            ).extract()
            item["chapter_ref"] = str_content.css("h1 > span.extended::text").get()
            item["chapter"] = str_content.css("h1::text").extract()[-1]
            item["topics"] = str_content.css("h2.crosstitle::text").extract()
            item["clauses"] = [
                c.split(".")[-1]
                for c in str_content.css("div.details > span.extended::text").extract()
            ]

            yield item

Pipeline代码 #

from scrapy import signals
import os
import pandas as pd
from scrapy.exporters import CsvItemExporter

import logging


class HandbookExcelPipeline(object):
    def __init__(self):
        self.files = {}

    @classmethod
    def from_crawler(cls, crawler):
        pipeline = cls()
        crawler.signals.connect(pipeline.spider_opened, signals.spider_opened)
        crawler.signals.connect(pipeline.spider_closed, signals.spider_closed)
        return pipeline

    def spider_opened(self, spider):
        export_file = open("%s_items_excel.csv" % spider.name, "wb")
        self.file = export_file
        self.files[spider] = self.file
        self.exporter = CsvItemExporter(self.file)
        self.exporter.encoding = "utf-8"
        self.exporter.start_exporting()

    def process_item(self, item, spider):
        self.exporter.export_item(item)
        return item

    def spider_closed(self, spider):
        self.exporter.finish_exporting()
        self.file.close()
        try:
            df = pd.read_csv("%s_items_excel.csv" % spider.name).fillna("")
            # sorted_df = df.sort_values(by=["unique_ids"])
            output_path = "%s-items-final.xlsx" % spider.name
            with pd.ExcelWriter(output_path, engine="xlsxwriter") as writer:
                explode_cols.to_excel(
                    writer, sheet_name="Sheet1", header=True, index=False
                )

            # os.remove("%s_items_excel.csv" % spider.name)
        except pd.errors.EmptyDataError:
            spider.log("Excel pipeline No columns to parse from file", logging.ERROR)
            os.remove("%s_items_excel.csv" % spider.name)

问题核心 #

当前输出中，unique_ids、topics、clauses是列表被转为逗号分隔的字符串，全部挤在一行；预期是每个列表元素单独成一行，同时保留对应的chapter_ref和chapter信息。

解决方案 #

1. 修改Spider的parse_details方法（优先推荐） #

不要一次性把所有列表值放到一个item里，而是遍历每个条目，生成独立的item：

def parse_details(self, response):
    handbook_content = response.css("div.handbook-content")
    for content in handbook_content:
        chapter_ref = content.css("h1 > span.extended::text").get()
        chapter = content.css("h1::text").extract()[-1].strip()
        # 遍历每个主题区块
        sections = content.css("div.section")
        for section in sections:
            topic = section.css("h2.crosstitle::text").get().strip() if section.css("h2.crosstitle::text") else ""
            # 遍历每个条款详情
            details = section.css("div.details > span.extended::text").extract()
            for detail in details:
                unique_id = detail.strip()
                clause = unique_id.split(".")[-1]
                yield {
                    "unique_ids": unique_id,
                    "chapter_ref": chapter_ref,
                    "chapter": chapter,
                    "topics": topic,
                    "clauses": clause
                }

这样每个条目都会生成单独的item，后续导出时自然是一行一条数据，无需额外处理。

2. 修复Pipeline中的错误（备选方案） #

若仍需要从CSV拆分列表，可修改spider_closed方法，补充未定义的explode_cols逻辑：

def spider_closed(self, spider):
    self.exporter.finish_exporting()
    self.file.close()
    try:
        df = pd.read_csv("%s_items_excel.csv" % spider.name).fillna("")
        # 拆分列表字段为多行
        explode_cols = df.explode(["unique_ids", "topics", "clauses"], ignore_index=True)
        output_path = "%s-items-final.xlsx" % spider.name
        with pd.ExcelWriter(output_path, engine="xlsxwriter") as writer:
            explode_cols.to_excel(writer, sheet_name="Sheet1", header=True, index=False)
        os.remove("%s_items_excel.csv" % spider.name)
    except pd.errors.EmptyDataError:
        spider.log("Excel pipeline: No columns to parse from file", logging.ERROR)
        os.remove("%s_items_excel.csv" % spider.name)

内容的提问来源于stack exchange，提问作者X-something