如何用Python抓取航空事故数据并构建规范多列DataFrame？

该网站数据库涵盖1902至2022年的数据，目标是抓取2015和2016年所有事故的表格信息、事故经过（narrative）、可能原因（probable cause）及事故分类（classification）。现有代码仅能抓取表格数据，但生成的DataFrame数据杂乱无章，需调整代码以构建符合期望的多列规范DataFrame。

现有抓取代码 #

import requests
from bs4 import BeautifulSoup
import pandas as pd
from datetime import datetime
import re
import concurrent.futures
import itertools
from random import randint
from time import sleep

def scraping(year):


    headers =   {
        'accept':'*/*',
        'user-agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36',
        }

    url = f'https://aviation-safety.net/database/dblist.php?Year={year}&sorteer=datekey&page=1'
    #sleep(randint(1,3))
    req = requests.get(url, headers=headers)

    soup = BeautifulSoup(req.text,'html.parser')

    page_container = soup.find('div',{'class':'pagenumbers'})

    pages = max([int(page['href'].split('=')[-1]) for page in  page_container.find_all('a')])
        

    #info = []
    tl = []
    for page in range(1,pages+1):

        new_url = f'https://aviation-safety.net/database/dblist.php?Year={year}&lang=&page={page}'
        print(new_url)
        
        #sleep(randint(1,3))
        data = requests.get(new_url,headers=headers)
        soup = BeautifulSoup(data.text,'html.parser')


        table = soup.find('table')
   
    
        for index,row in enumerate(table.find_all('tr')):
            if index == 0:
                continue

            link_ = 'https://aviation-safety.net/'+row.find('a')['href']
            
            #sleep(randint(1,3))
            new_page = requests.get(link_, headers=headers)
            new_soup = BeautifulSoup(new_page.text, 'lxml')
            table1 = new_soup.find('table')
            
           
            for i in table1.find_all('tr'):
                title = i.text
                tl.append(title)
                
                
    df= pd.DataFrame(tl)
    df.columns = ['status'] 
    df.to_csv(f'{year}_aviation-safety_new.csv', encoding='utf-8-sig', index=False)    
          

if __name__ == "__main__":

    START = 2015
    STOP = 2016

    years = [year for year in range(START,STOP+1)]

    print(f'Scraping {len(years)} years of data')

    with concurrent.futures.ThreadPoolExecutor(max_workers=60) as executor:
        final_list = executor.map(scraping,years)

当前数据效果 #

期望数据效果 #

解决方案：调整后的代码 #

原问题核心是未按事故维度分类整理数据，仅将详情页文本无序存入列表。调整思路为：按事故维度合并列表页表格数据与详情页指定字段，最终生成规范DataFrame。

import requests
from bs4 import BeautifulSoup
import pandas as pd
from random import randint
from time import sleep
import concurrent.futures

def get_detail_data(link, headers):
    """抓取事故详情页的narrative、probable cause、classification"""
    sleep(randint(1,3))
    new_page = requests.get(link, headers=headers)
    new_soup = BeautifulSoup(new_page.text, 'lxml')
    
    detail_data = {
        'narrative': '',
        'probable_cause': '',
        'classification': ''
    }
    
    # 提取事故分类
    class_elem = new_soup.find('div', {'class': 'listspan'})
    if class_elem:
        detail_data['classification'] = class_elem.get_text(strip=True).replace('Classification: ', '')
    
    # 提取事故经过和可能原因
    content_divs = new_soup.find_all('div', {'class': 'text16'})
    for div in content_divs:
        strong_tag = div.find('strong')
        if not strong_tag:
            continue
        if 'Narrative' in strong_tag.text:
            detail_data['narrative'] = div.get_text(strip=True).replace('Narrative: ', '')
        elif 'Probable Cause' in strong_tag.text:
            detail_data['probable_cause'] = div.get_text(strip=True).replace('Probable Cause: ', '')
    
    return detail_data

def scraping(year):
    headers = {
        'accept':'*/*',
        'user-agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36',
    }

    # 获取总页数
    url = f'https://aviation-safety.net/database/dblist.php?Year={year}&sorteer=datekey&page=1'
    req = requests.get(url, headers=headers)
    soup = BeautifulSoup(req.text,'html.parser')
    page_container = soup.find('div',{'class':'pagenumbers'})
    pages = max([int(page['href'].split('=')[-1]) for page in page_container.find_all('a')]) if page_container else 1

    all_accidents = []
    
    for page in range(1,pages+1):
        new_url = f'https://aviation-safety.net/database/dblist.php?Year={year}&lang=&page={page}'
        print(f'Processing page {page}/{pages} for year {year}')
        sleep(randint(1,2))
        data = requests.get(new_url,headers=headers)
        soup = BeautifulSoup(data.text,'html.parser')
        table = soup.find('table')
        
        # 获取列表页表格的表头
        if page == 1:
            headers_list = [th.get_text(strip=True) for th in table.find_all('tr')[0].find_all('th')]
            headers_list.extend(['narrative', 'probable_cause', 'classification'])
        
        for index,row in enumerate(table.find_all('tr')):
            if index == 0:
                continue
            # 提取列表页行数据
            row_data = [td.get_text(strip=True) for td in row.find_all('td')]
            # 获取详情页链接
            link_ = 'https://aviation-safety.net' + row.find('a')['href']
            # 抓取详情页数据
            detail_data = get_detail_data(link_, headers)
            # 合并数据
            row_data.extend([detail_data['narrative'], detail_data['probable_cause'], detail_data['classification']])
            all_accidents.append(row_data)
    
    # 生成DataFrame并保存
    df = pd.DataFrame(all_accidents, columns=headers_list)
    df.to_csv(f'{year}_aviation-safety_clean.csv', encoding='utf-8-sig', index=False)
    print(f'Finish scraping year {year}, saved to {year}_aviation-safety_clean.csv')

if __name__ == "__main__":
    START = 2015
    STOP = 2016
    years = [year for year in range(START, STOP+1)]
    print(f'Scraping {len(years)} years of data')
    
    # 降低线程数，避免请求过于频繁被封
    with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
        executor.map(scraping, years)

关键调整说明 #

• 新增get_detail_data函数，精准提取详情页的目标字段
• 按事故维度合并列表页与详情页数据，保证每条记录对应单个事故
• 添加请求延迟、降低线程数，规避网站反爬机制
• 动态获取列表页表头，确保DataFrame列名与数据一一对应

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