python爬取全球历年GDP数据

Ⅰ.概要

程序语言：python

第三方库：requests/parsel/pandas/openpyxl

目的：爬取1980-2021年全球GDP数据并写入excel，且涵盖全球GDP排名、国家/地区、GDP总量(美元核算)、GDP总量(人民币核算)、所属洲等信息。

Ⅱ.爬网程序(分步)

ii.i.导入第三方库

import requests
import parsel
import pandas as pd
from openpyxl import load_workbook

ii.ii.调用requests发送请求，获取响应

headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.80 Safari/537.36',
           'sec-ch-ua': '" Not A;Brand";v="99", "Chromium";v="98", "Google Chrome";v="98"'}

for i in range(1980, 2022):
    url = 'http://www.8pu.com/gdp/ranking_' + str(i) + '.html'
    response = requests.get(url, headers=headers)
    response.encoding = response.apparent_encoding

ii.iii.调用parsel的Selector对象，通过css选择器获取所需标签信息

    selector = parsel.Selector(response.text)
    lis = selector.css('#table01')
    for li in lis:
        rank = li.css('#US_ > td.rank > font > font::text').getall()
        country = li.css('#US_ > td:nth-child(2) > a > font::text').getall()
        dollar = li.css('#US_ > td.value > font::text').getall()
        rmb = li.css('#US_ > td.rank_prev > font::text').getall()
        continent = li.css('#US_ > td.area > font > font::text').getall()

ii.iv.对提取到的标签信息进行清洗处理

        rank = ','.join(rank).replace('排名第', '').split(',')
        dollar = ','.join(dollar).replace('$', '').split(',')
        rmb = ','.join(rmb).replace('￥', '').replace('亿元', '').split(',')
        continent = ','.join(continent).replace('国家', '').replace('州','洲').split(',')
        dict = {'排名': rank, '国家/地区': country, 'GDP总量(美元核算)': dollar, 'GDP总量(人民币核算)': rmb, '所属洲': continent}
    d = list(zip(rank, country, dollar, rmb, continent))

ii.v.打印输出，查看数据

    print('\n//  ' + str(i) + '年全球GDP  //\n')
    for x1, x2, x3, x4, x5 in d:
        print(x1, x2, x3, x4, x5)

ii.vi.写入excel，存档

    df = pd.DataFrame(d)
    df.to_excel('GDP' + str(i) + '.xlsx', sheet_name=str(i), encoding='gbk', index=0, header=dict)

ii.vii.设置excel表格列宽

    wb = load_workbook('GDP' + str(i) + '.xlsx')
    ws = wb.active
    ws.column_dimensions['A'].width = 15
    ws.column_dimensions['B'].width = 20
    ws.column_dimensions['C'].width = 25
    ws.column_dimensions['D'].width = 25
    ws.column_dimensions['E'].width = 15
    wb.save('GDP' + str(i) + '.xlsx')

Ⅲ.爬网程序(完整)

import requests
import parsel
import pandas as pd
from openpyxl import load_workbook

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.80 Safari/537.36',
    'sec-ch-ua': '" Not A;Brand";v="99", "Chromium";v="98", "Google Chrome";v="98"'}

for i in range(1980, 2022):
    url = 'http://www.8pu.com/gdp/ranking_' + str(i) + '.html'
    response = requests.get(url, headers=headers)
    response.encoding = response.apparent_encoding
    selector = parsel.Selector(response.text)
    lis = selector.css('#table01')
    for li in lis:
        rank = li.css('#US_ > td.rank > font > font::text').getall()
        rank = ','.join(rank).replace('排名第', '').split(',')
        country = li.css('#US_ > td:nth-child(2) > a > font::text').getall()
        dollar = li.css('#US_ > td.value > font::text').getall()
        dollar = ','.join(dollar).replace('$', '').split(',')
        rmb = li.css('#US_ > td.rank_prev > font::text').getall()
        rmb = ','.join(rmb).replace('￥', '').replace('亿元', '').split(',')
        continent = li.css('#US_ > td.area > font > font::text').getall()
        continent = ','.join(continent).replace('国家', '').replace('州','洲').split(',')
        dict = {'排名': rank, '国家/地区': country, 'GDP总量(美元核算)': dollar, 'GDP总量(人民币核算)': rmb, '所属洲': continent}
    d = list(zip(rank, country, dollar, rmb, continent))
    print('\n//  ' + str(i) + '年全球GDP  //\n')
    for x1, x2, x3, x4, x5 in d:
        print(x1, x2, x3, x4, x5)
    df = pd.DataFrame(d)
    df.to_excel('GDP' + str(i) + '.xlsx', sheet_name=str(i), encoding='gbk', index=0, header=dict)

    wb = load_workbook('GDP' + str(i) + '.xlsx')
    ws = wb.active
    ws.column_dimensions['A'].width = 15
    ws.column_dimensions['B'].width = 20
    ws.column_dimensions['C'].width = 25
    ws.column_dimensions['D'].width = 25
    ws.column_dimensions['E'].width = 15
    wb.save('GDP' + str(i) + '.xlsx')

Ⅳ.效果展示

每个excel文件单独存储一张sheet工作表：

Ⅴ.爬网程序2(完整)

import requests
import parsel
import openpyxl
import pandas as pd

headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.80 Safari/537.36',
           'sec-ch-ua': '" Not A;Brand";v="99", "Chromium";v="98", "Google Chrome";v="98"'}

wb = openpyxl.Workbook('GDP.xlsx')

for i in range(1980, 2022):
    url = 'http://www.8pu.com/gdp/ranking_' + str(i) + '.html'
    response = requests.get(url, headers=headers)
    response.encoding = response.apparent_encoding
    selector = parsel.Selector(response.text)
    lis = selector.css('#table01')
    for li in lis:
        rank = li.css('#US_ > td.rank > font > font::text').getall()
        rank = ','.join(rank).replace('排名第', '').split(',')
        country = li.css('#US_ > td:nth-child(2) > a > font::text').getall()
        dollar = li.css('#US_ > td.value > font::text').getall()
        dollar = ','.join(dollar).replace('$', '').split(',')
        rmb = li.css('#US_ > td.rank_prev > font::text').getall()
        rmb = ','.join(rmb).replace('￥', '').replace('亿元', '').split(',')
        continent = li.css('#US_ > td.area > font > font::text').getall()
        continent = ','.join(continent).replace('国家', '').replace('州','洲').split(',')
        dict = {'排名': rank, '国家/地区': country, 'GDP总量(美元核算)': dollar, 'GDP总量(人民币核算)': rmb, '所属洲': continent}
    d = list(zip(rank, country, dollar, rmb, continent))
    print('\n//  ' + str(i) + '年全球GDP  //\n')
    for x1, x2, x3, x4, x5 in d:
        print(x1, x2, x3, x4, x5)
    name=[[u'排名',u'国家/地区',u'GDP总量(美元核算)',u'GDP总量(人民币核算)',u'所属洲']]
    df = pd.DataFrame(d)
    sheet=wb.create_sheet(index=0,title=str(i))
    for r in name:
        sheet.append(r)
    for rr in d:
        sheet.append(rr)
wb.save('GDP.xlsx')

wbb =openpyxl.load_workbook('GDP.xlsx')
sheet_names=wbb.get_sheet_names()
for ii in sheet_names:
    ws=wbb.get_sheet_by_name(ii)
    ws.column_dimensions['A'].width = 15
    ws.column_dimensions['B'].width = 20
    ws.column_dimensions['C'].width = 25
    ws.column_dimensions['D'].width = 25
    ws.column_dimensions['E'].width = 15
wbb.save('GDP.xlsx')

Ⅵ.效果展示2

一个excel文件存储多张sheet工作表：