[人工智能]实训1_获取产业数据并存储_预处理与简单分析

目录

本文jupyter notebook资料下载地址百度网盘

1. 实训一. 获取产业数据并存储、预处理与分析

1.1 实训内容概述

• 1. 爬取"https://s.askci.com/data/economy/" 前5个行业(左侧导航栏前5个)的经济指标、资产负债、收入利润、成本费用、产值指标；
• 2. 将爬取的结果按产业名为文件名，5个指标名为表单名存储为excel文件；
• 3. 以煤炭开采和洗选业为分析对象，查看经济指标的总体数据信息、统计数据信息、前5行、后5行数数据等；
• 4. 以煤炭开采和洗选业为分析对象，查看经济指标的中企业个数、亏损企业数的变化情况，并绘制出图形予以展示；
• 5. 以煤炭开采和洗选业为分析对象，查看2021年度 经济指标的中亏损总额并可视化，再查看其最大值、最小值及相应的月份的详细经济指标；
• 6. 以煤炭开采和洗选业为分析对象，查看成本费用中的中管理费用，并采用3西格玛方法或者箱型图法查看是否有异常的点。
• 7. 附加补充题：将爬取结果保存到MySQL中。

1.2 实训知识点：

1.2.1 爬取网页数据

1.2.1.1 一般格式

• 爬虫一般步骤：

  • 导包
  • 设置请求头
  • 发起get请求获得响应
  • 对响应进行编码转换
  • 解析响应
  • 获取目标字段

• 爬虫解析方法：

  • lxml解析(xpath)

    # 导库
    import requests   
    from lxml import etree
    
    # 获得响应，解析响应
    r = requests.get(url,headers)
    html = etree.HTML(r.text)

    # 获得xpath路径,输入xpath方法获得元素内容
    element = html.xpath('xpath路径')

- BeautifulSoup(css、find...)                  

   # 导库
   import requests
   from bs4 import BeautifulSoup
   
   # 获得响应，解析响应
   r = requests.get(url,headers)
   soup = BeautifulSoup(r.text, 'lxml')
   
   # 分步解析获得各元素
   # css选择器
   # 方法：F12-->选择元素-->右键菜单：复制selector  
   element = soup.select("CSS选择器")
   
   # find方法
   # 方法：F12-->选择元素，观察
   element = soup.find("标签",class_= "类名")  #等其他选择

• 乱码解决
  • 响应中包含的内容：
  • r.status_code：HTTP请求的返回状态，200表示连接成功，404表示失败
  • r.text：HTTP响应内容的字符串形式，即url对应的页面内容
  • r.encoding：从HTTP header中猜测的响应内容编码方式(
  • r.apparent_encoding：从内容中分析出的响应内容编码方式（备选编码方式）
  • r.content：HTTP响应内容的二进制形式
  • r.url：返回对象的网址
  • r.cookies：返回对象的cookis

实例： 采用xpath获取行业名

# 导入库        
import requests
from lxml import etree

# 设置请求头
headers = {
    'user-Agent':'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.71 Safari/537.36',
    'Host':'s.askci.com'
}
# 设置请求url
url = 'https://s.askci.com/data/economy/00001/'   

# 获得响应
r = requests.get(url, headers = headers) 

# 编码转换，避免乱码
r.encoding = r.apparent_encoding

# 采用etree.HTML解析
html = etree.HTML(r.text)

# 采用xpath获得行业名
html.xpath('/html/body/div[4]/div[2]/div[1]/a[3]/text()')

1.2.1.2 采用pandas读取网页表格数据

一般格式

pandas.read_html(io,
             match='.+', 
             flavor=None, 
             header=None, 
             index_col=None, 
             skiprows=None, 
             attrs=None, 
             parse_dates=False, 
             thousands=',', 
             encoding=None, 
             decimal='.',
             converters=None, 
             na_values=None, 
             keep_default_na=True, 
             displayed_only=True)

输入参数：

• io：str、路径对象或 file-like 对象
  字符串、路径对象(实现 os.PathLike[str] )或 file-like 对象实现字符串 read() 函数。该字符串可以表示 URL 或 HTML 本身。请注意，lxml 只接受 http、ftp 和文件 url 协议。如果您有一个以 ‘https’ 开头的 URL，您可以尝试删除 ‘s’ 。

• match：str 或编译的正则表达式，可选
  将返回包含与此正则表达式或字符串匹配的文本的表集。除非 HTML 非常简单，否则您可能需要在此处传递一个非空字符串。默认为“.+”(匹配任何非空字符串)。默认值将返回页面中包含的所有表。此值被转换为正则表达式，以便 Beautiful Soup 和 lxml 之间的行为一致。

• flavor：str，可选
  要使用的解析引擎。 ‘bs4’ 和 ‘html5lib’ 是同义词，它们都是为了向后兼容。 None 的默认值尝试使用 lxml 进行解析，如果失败，则返回 bs4 + html5lib 。

• header：int 或 list-like，可选
  用于制作列标题的行(或 MultiIndex 的行列表)。

• index_col：int 或 list-like，可选
  用于创建索引的列(或列列表)。

• skiprows：int, list-like 或切片，可选
  解析列整数后要跳过的行数。基于 0。如果给定整数序列或切片，将跳过由该序列索引的行。请注意，单个元素序列表示“跳过第 n 行”，而整数表示“跳过 n 行”。

• attrs：字典，可选
  这是一个属性字典，您可以通过它来识别 HTML 中的表格。在传递给 lxml 或 Beautiful Soup 之前，不会检查它们的有效性。但是，这些属性必须是有效的 HTML 表格属性才能正常工作。例如，

  • attrs = {‘id’:‘table’}
    是一个有效的属性字典，因为 ‘id’ HTML 标记属性是本文档中任何 HTML 标记的有效 HTML 属性。

  • attrs = {‘asdf’:‘table’}
    不是有效的属性字典，因为 ‘asdf’ 不是有效的 HTML 属性，即使它是有效的 XML 属性。可以在此处找到有效的 HTML 4.01 表格属性。可以在此处找到 HTML 5 规范的工作草案。它包含有关现代 Web 表属性的最新信息。

• parse_dates：布尔型，可选
  有关详细信息，请参阅read_csv()。

• thousands：str，可选
  用于解析数千的分隔符。默认为 ‘,’ 。

• encoding：str，可选
  用于解码网页的编码。默认为 None 。None 保留先前的编码行为，这取决于底层解析器库(例如，解析器库将尝试使用文档提供的编码)。

• decimal：str，默认“。”
  识别为小数点的字符(例如，对欧洲数据使用“,”)。

• converters：字典，默认无
  用于转换某些列中的值的函数的字典。键可以是整数或列标签，值是接受一个输入参数、单元格(不是列)内容并返回转换后的内容的函数。

• na_values：可迭代，默认无
  自定义 NA 值。

• keep_default_na：布尔值，默认为真
  如果指定了 na_values 并且 keep_default_na 为 False，则默认 NaN 值将被覆盖，否则将附加到它们。

• displayed_only：布尔值，默认为真
  是否应该解析带有“display:none” 的元素。

返回值：

• dfs
  数据帧列表。

注意：

在使用此函数之前，您应该阅读有关 HTML 解析库的问题。

调用此函数后，期望进行一些清理。例如，如果在传递 header=0 参数时列名转换为 NaN，则可能需要手动分配列名。我们尝试尽可能少地假设表格的结构，并将表格中包含的 HTML 的特性推送给用户。

这个函数搜索 **< table >**元素并且仅用于 < tr > 和 < th > 行和 < td > 每个内的元素 < tr > 或者表中的元素。 < td > 代表“table data”。该函数试图正确处理colspan和rowspan属性。如果函数有 < thead > 参数，它用于构造标题，否则该函数会尝试在正文中查找标题(通过仅放置行 **< th >**元素到标题中)。

与read_csv() 类似，在应用skiprows 之后应用header 参数。

此函数将始终返回 DataFrame 列表，否则将失败，例如，它不会返回空列表。

1.2.2 DataFrame存储为excel表格

• 使用to_excel()函数将DataFrame导出到excel文件
  要将单个对象写入excel文件, 我们必须指定目标文件名。如果要写入多个工作表, 则需要使用目标文件名创建一个ExcelWriter对象, 并且还需要在必须写入的文件中指定工作表。也可以通过指定唯一的sheet_name来写入多张纸。必须保存所有写入文件的数据的更改。

1.2.2.1 DataFrame的.to_execel()函数

• 一般格式:

    DataFrame.to_excel(excel_writer, 
                 sheet_name='Sheet1', 
                 na_rep='', 
                 float_format=None, 
                 columns=None,
                 header=True, 
                 index=True, 
                 index_label=None, 
                 startrow=0, 
                 startcol=0, 
                 engine=None,
                 merge_cells=True, 
                 encoding=None, 
                 inf_rep='inf', 
                 verbose=True, 
                 freeze_panes=None)

• 输入参数：
  • excel_writer：文件路径或现有的ExcelWriter。
  • sheet_name：它是指包含DataFrame的工作表的名称。
  • na_rep：指定要写入的表如果有空值（NAN）,则需要填入的字符串。
  • float_format：这是一个可选参数, 用于格式化浮点数字符串。
  • columns：指定哪些列要写入新表。
  • header：是否把列名也写入excel表，默认为True，即写入
  • index：是否把索引也写入excel表，默认为True，即写入。
  • index_label：引用索引列的列标签。如果未指定, 并且标头和索引为True, 则使用索引名称。如果DataFrame使用MultiIndex, 则应给出一个序列。
  • startrow：默认值0。它指向转储DataFrame的左上单元格行。
  • startcol：默认值0。它指向转储DataFrame的左上方单元格列。
  • engine：这是一个可选参数, 用于写入要使用的引擎, openpyxl或xlsxwriter。
  • merge_cells：返回布尔值, 其默认值为True。它将MultiIndex和Hierarchical行写为合并的单元格。
  • encoding：这是一个可选参数, 可对生成的excel文件进??行编码。仅对于xlwt是必需的。
  • inf_rep：它也是一个可选参数, 默认值为inf。它通常表示无穷大。
  • verbose：返回一个布尔值。它的默认值为True。
    它用于在错误日志中显示更多信息。
  • Frozen_panes：它也是一个可选参数, 用于指定要冻结的最底部一行和最右边一列。

1.2.2.2 DataFrame的.to_excel()函数存储多个sheet的问题

大家尝试df.to_excel()会发现，多次调用此函数存同一个excel文件的不同sheet时，会把旧文件覆盖掉，无法保存为多个sheet。
其解决办法如下：

    if not os.path.exists(excelFileName):
    html_tables[0].to_excel(excelFileName,sheet_name=sheet_names[0], index=True)
else:
    with pd.ExcelWriter(excelFileName, engine='openpyxl', mode='a') as writer:
            html_tables[0].to_excel(writer,sheet_name=sheet_names[0], index=True)

• 第?个if语句是在Excel?件不存在时，?于直接构建；
• 第?个else语句则是?ExcelWriter对已存在的Excel?件进?追加。
• 注意，文件名"excelFileName"需使用".xlsx",若用".xls"可能会出现 **" File is not a zip file"**错误

1.2.2.3 Pandas的ExcelWriter构造函数

• 一般格式
  以下首先对ExcelWriter的帮助文档进行整合：
  ExcelWriter是一个用于将DataFrame对象写入Excel工作表的类。默认的处理是对xls文件使用xlwt方法，对xlsx文件使用openpyxl方法，对ods文件使用odf方法。可以参见DataFrame.to_excel的文档查看这三种方法的典型用法。ExcelWriter应当视为一个上下文管理器（context manager），否则，call close()函数进行保存并关闭任何已打开的文件的处理（handles）

通过查看ExcelWriter的py文件，可以看到该类的定义框架如下所示：

class ExcelWriter(builtins.object):
    def ExcelWriter(
        path: 'FilePathOrBuffer | ExcelWriter', 
        engine=None, 
        date_format=None, 
        datetime_format=None, 
        mode: 'str' = 'w', 
        storage_options: 'StorageOptions' = None, 
        if_sheet_exists: 'str | None' = None, 
        engine_kwargs: 'dict | None' = None, 
        **kwargs):

• 参数详解
  • path ：str 或打字。BinaryIO，xls 或xlsx 或ods 文件的路径。
  • engine ：str(可选)。用于写作的引擎。如果没有，默认为io.excel.< extension >.writer。注意：只能作为关键字参数传递。
  • date_format ：str，默认无。写入Excel 文件的日期格式字符串(例如“YYYY-MM-DD”)。
  • datetime_format ：str，默认无。写入Excel 文件的日期时间对象的格式字符串。(例如“YYYY-MM-DD HH:MM:SS”)。
  • mode ：{‘w’, ‘a’}，默认’w’。要使用的文件模式(写入或附加)。附加不适用于fsspec URL。
  • storage_options ：字典，可选。对特定存储连接有意义的额外选项，例如主机、端口、用户名、密码等，如果使用将由 fsspec 解析的URL，例如，从“s3://”、“gcs://” 开始。
  • if_sheet_exists ：{‘error’, ‘new’, ‘replace’, ‘overlay’}，默认’error’。尝试写入已存在的工作表时的行为方式(仅限附加模式)。
    • ‘error’：引发ValueError。
    • ‘new’：创建一个新工作表，其名称由引擎确定。
    • ‘replace’：在写入之前删除工作表的内容。
    • ‘overlay’：将内容写入现有工作表而不删除旧内容。
  • engine_kwargs ：字典，可选. 要传递到引擎的关键字参数。这些将传递给各个引擎的以下函数：
    • xlsxwriter：xlsxwriter.Workbook(file, **engine_kwargs)
    • openpyxl(写入模式)：openpyxl.Workbook(**engine_kwargs)
    • openpyxl(追加模式)：openpyxl.load_workbook(file, **engine_kwargs)
    • odswriter:odf.opendocument.OpenDocumentSpreadsheet(**engine_kwargs)
  • **kwargs ：字典，可选。要传递到引擎的关键字参数。

其中，最基本的应用是：

    ExcelWriter(path, 
                engine=None, 
                date_format=None, 
                datetime_format=None,
                mode=‘w’)

• 参数详解
  • path：文件保存路径，如"D:\test\test.xlsx"（绝对路径和文件名都要有）
  • engine：写入excel文件使用的引擎，默认为"None"；一般情况下默认即可。
  • date_format：将写入Excel文件中的日期格式化，如"YYYY-MM-DD"；默认为"None"；
  • datetime_format：将写入excel文件中的时间格式化，如"YYYY-MM-DD HH: MM: SS"。默认为"None"；
  • mode：写入模式，有"w"–写入和"a"–追加；默认为"w"；

1.2.3 Pandas的DataFrame基本使用

1.2.3.1 Pandas的基本函数与属性

# 查看DataFrame的基本信息
df.info()

# 查看DataFrame的统计信息
df.describe()

# 查看DataFrame的前n行
df.head(n)

# 查看DataFrame的后n行
df.tail(n)

# 返回DataFrame的列索引
df.columns

# 返回DataFrame的行索引
df.index

1.2.3.2 Pandas中的统计函数

• count() 统计个数，空值NaN不计算
• min()最小值
• max()最大值
• sum()总和
• mean()平均值
• median()中位数
• var()方差
• std()标准差
• argmin() 统计最小值的索引位置
• argmax()统计最大值的索引位置
• idxmin() 统计最小值的索引值
• idxmax() 统计最大值的索引值

1.2.3.3 Pandas的索引

• 获取列

  • 字典式
    df[columnName]
  • 对象式
    df.columnName

• 获取行

  • 绝对索引
    df.loc[索引名]
  • 相对索引
    df.iloc[整数相对索引值]

• 获取标量

  • df.loc[行索引名称,列索引名称]方式

  • df.loc[行索引名称][列索引名称]方式

  • df.iloc[行索引值,列索引值]方式

  • df.iloc[行索引值][列索引值]方式

  • df.at[行索引名称,列索引名称] 方式

  • df.iat[行索引值,列索引值]方式

• 切片

  • 行切片

    • df[起始行索引名称:终止行索引名称:步长]

    • df.loc[起始行索引名称:终止行索引名称:步长]

    • df.iloc[起始行索引值:终止行索引值:步长]

  • 列切片

    • df[[列索引名1,列索引名2,列索引名3,...]]

    • df.loc[:,起始列索引名:终止列索引名:步长]

    • df.iloc[:,起始列索引值:终止列索引值:步长]

  • 花式切片

    • df.loc[起始行索引名:终止行索引名:行步长, 起始列索引名:终止列索引名:列步长]

    • df.iloc[起始行索引值:终止行索引值:行步长, 起始列索引值:终止列索引值:列步长]

• 布尔索引
  例如:

  # 找出df的A列值最小的那行数据
  df[df.A==df.A.min()]
  

1.2.4 Pandas读取excel文件

• 一般格式：

    pandas.read_excel(io, 
                sheet_name=0, 
                header=0, 
                names=None, 
                index_col=None, 
                usecols=None, 
                squeeze=False, 
                dtype=None, 
                engine=None, 
                converters=None, 
                true_values=None, 
                false_values=None, 
                skiprows=None, 
                nrows=None, 
                na_values=None, 
                keep_default_na=True, 
                na_filter=True, 
                verbose=False, 
                parse_dates=False, 
                date_parser=None, 
                thousands=None, 
                comment=None, 
                skipfooter=0, 
                convert_float=True,
                mangle_dupe_cols=True, 
                storage_options=None)

• 主要参数说明
  • io：文件路径，可以是绝对路径或者相对路径
  • sheet_name：指定读取的sheet名。可以是数字，sheet名，以列表形式表现的sheet名
    在未指定的情况下默认为0，即读取第一个sheet，
  • sheet_name = 1，读取第二个sheet
  • sheet_name = “主营首版”，读取名为主营首版的sheet
  • sheet_name = [0,1,‘主营首版’]，同时读取列表中的sheet
  • sheet_name = None，读取所有的sheet
  • header：指定标题行,不指定默认第一行为标题行
  • header = 1 ，从第2行开始读取数据，并且将第2行设置为标题行。
  • header = [0,1] ，第一行和第二行为标题行
  • header = None，没有标题行，所有都是数据
  • names：在header=None的前提下，补充列名
  • pd.read_excel(filename,sheet_name =1,header =None,names=[1,2,3,4,5,6,7,8,9,10,11,12,13]) # names元素的个数必须和dataframe的列数一致。
  • index_col：用于指定索引,默认为None，即索引默认为0开始的数字
  • index_col = 0，将第一列设置为索引
  • index_col=[0,1]，将第一列和第二列设置为索引，其余列为数据
  • header 如果为1，则第一行数据就会被抛弃，index_col如果为1，第一列的数据会自动后移和其他数据合并在一起，不会被抛弃
  • usecols：用于指定读取的列
  • usecols = [1,2,3]，读取第2-4列
  • usecols = None，读取所有列,其他形式的写法验证都不通过。
  • squeeze：如果源数据数据或者读取的数据只有一列，squeeze = False时读取的结果是*行1列的dataframe结构，如果squeeze = True时读取的结果是一个Series结构。
  • converters={“可滚动收入”:lambda x: x/100,“收入”:lambda x: x/100} # 将可滚动收入和收入的数值都除以100
  • skiprows:省略指定行数的数据，从第一行开始
  • skipfooter:省略指定行数的数据，是从尾部数的行开始。

1.2.5 Pandas数据可视化

1.2.5.1 DataFrame.plot()函数

一般格式:

DataFrame.plot(x=None, y=None, kind='line', ax=None, subplots=False, 
                sharex=None, sharey=False, layout=None, figsize=None, 
                use_index=True, title=None, grid=None, legend=True, 
                style=None, logx=False, logy=False, loglog=False, 
                xticks=None, yticks=None, xlim=None, ylim=None, rot=None, 
                fontsize=None, colormap=None, position=0.5, table=False, yerr=None, 
                xerr=None, stacked=True/False, sort_columns=False, 
                secondary_y=False, mark_right=True, **kwds)

主要参数解释:

• x : label or position, default None#指数据框列的标签或位置参数
• y : label or position, default None
• kind : str
  • ‘line’ : line plot (default)#折线图
  • ‘bar’ : vertical bar plot#条形图
  • ‘barh’ : horizontal bar plot#横向条形图
  • ‘hist’ : histogram#柱状图
  • ‘box’ : boxplot#箱线图
  • ‘kde’ : Kernel Density Estimation plot#Kernel 的密度估计图，主要对柱状图添加Kernel 概率密度线
  • ‘density’ : same as ‘kde’
  • ‘area’ : area plot#不了解此图
  • ‘pie’ : pie plot#饼图
  • ‘scatter’ : scatter plot#散点图 需要传入columns方向的索引
  • ‘hexbin’ : hexbin plot#不了解此图
• ax : matplotlib axes object, default None#子图(axes, 也可以理解成坐标轴) 要在其上进行绘制的matplotlib subplot对象。如果没有设置，则使用当前matplotlib subplot其中，变量和函数通过改变figure和axes中的元素（例如：title,label,点和线等等）一起描述figure和axes，也就是在画布上绘图。
• subplots : boolean, default False#判断图片中是否有子图
• Make separate subplots for each column
• sharex : boolean, default True if ax is None else False#如果有子图，子图共x轴刻度，标签
• In case subplots=True, share x axis and set some x axis labels to invisible; defaults to True if ax is None otherwise False if an ax is passed in; Be aware, that passing in both an ax and sharex=True will alter all x axis labels for all axis in a figure!
• sharey : boolean, default False#如果有子图，子图共y轴刻度，标签
• In case subplots=True, share y axis and set some y axis labels to invisible
• layout : tuple (optional)#子图的行列布局
• (rows, columns) for the layout of subplots
• figsize : a tuple (width, height) in inches#图片尺寸大小
• use_index : boolean, default True#默认用索引做x轴
• title : string#图片的标题用字符串
• grid : boolean, default None (matlab style default)#图片是否有网格
• Axis grid lines
• legend : False/True/’reverse’#子图的图例，添加一个subplot图例(默认为True)
• Place legend on axis subplots
• style : list or dict#对每列折线图设置线的类型
• matplotlib line style per column
• logx : boolean, default False#设置x轴刻度是否取对数
• Use log scaling on x axis
• logy : boolean, default False
• Use log scaling on y axis
• loglog : boolean, default False#同时设置x，y轴刻度是否取对数
• Use log scaling on both x and y axes
• xticks : sequence#设置x轴刻度值，序列形式（比如列表）
• Values to use for the xticks
• yticks : sequence#设置y轴刻度，序列形式（比如列表）
• Values to use for the yticks
• xlim : 2-tuple/list#设置坐标轴的范围，列表或元组形式
• ylim : 2-tuple/list
• rot : int, default None#设置轴标签（轴刻度）的显示旋转度数
• Rotation for ticks (xticks for vertical, yticks for horizontal plots)
• fontsize : int, default None#设置轴刻度的字体大小
• Font size for xticks and yticks
• colormap : str or matplotlib colormap object, default None#设置图的区域颜色
• Colormap to select colors from. If string, load colormap with that name from matplotlib.
• colorbar : boolean, optional #图片柱子
• If True, plot colorbar (only relevant for ‘scatter’ and ‘hexbin’ plots)
• position : float
• Specify relative alignments for bar plot layout. From 0 (left/bottom-end) to 1 (right/top-end). Default is 0.5 (center)
• layout : tuple (optional) #布局
• (rows, columns) for the layout of the plot
• table : boolean, Series or DataFrame, default False #如果为正，则选择DataFrame类型的数据并且转换匹配matplotlib的布局。
• If True, draw a table using the data in the DataFrame and the data will be transposed to meet matplotlib’s default layout. If a Series or DataFrame is passed, use passed data to draw a table.
• yerr : DataFrame, Series, array-like, dict and str
• See Plotting with Error Bars for detail.
• xerr : same types as yerr.
• stacked : boolean, default False in line and
• bar plots, and True in area plot. If True, create stacked plot.
• sort_columns : boolean, default False # 以字母表顺序绘制各列，默认使用前列顺序
• secondary_y : boolean or sequence, default False ##设置第二个y轴（右y轴）
• Whether to plot on the secondary y-axis If a list/tuple, which columns to plot on secondary y-axis
• mark_right : boolean, default True
• When using a secondary_y axis, automatically mark the column labels with “(right)” in the legend
• kwds : keywords
• Options to pass to matplotlib plotting method
• Returns:axes : matplotlib.AxesSubplot or np.array of them

1.2.5.2 中文字符显示设置

Pandas在绘图时,会显示中文为方块,主要原因有二: matplotlib 字体问题，seaborn 字体问题。
没有中文字体，所以我们只要手动添加中文字体的名称就可以了，不过并不是添加我们熟悉的“宋体”或“黑体”这类的名称，而是要添加字体管理器识别出的字体名称，matplotlib自身实现的字体管理器在文件font_manager.py中，自动生成的可用字体信息在保存在文件fontList.cache里，可以搜索这个文件查看对应字体的名称，例如simhei.ttf对应的名称为’SimHei’，simkai.ttf对应的名称为’KaiTi_GB2312’等。因此我们只要把这些名称添加到配置文件中去就可以让matplotlib显示中文。

import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei']  
# plt.rcParams['font.sans-serif'] = ['Arial Unicode MS'] #MAC中设置字体为"Arial Unicode MS"
plt.rcParams['font.serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False # 解决保存图像是负号'-'显示为方块的问题,或者转换负号为字符串

1.2.5.3 可视化图形美化

• 加图标题： plt.title()
• 加x轴标签: plt.xlabel()
• 加y轴标签: plt.ylabel()
• 自定义设置x刻度: plt.xticks(loc,labels)
• 设置图例: plt.lagend(labels,loc)

1.2.6 异常值检测

1.2.6.1 3σ原则

3σ原则，又称为拉依达原则，它是先假设一组检测数据只含有随机误差，对该组数据进行计算处理得到标准偏差，按一定概率确定一个区间，凡是超过这个区间的误差不属于随机误差而是粗大误差，含有粗大误差范围内的数据（视为异常值）应予以剔除。

3σ原则异常值检测函数

import numpy as np
import pandas as pd

def three_sigma(ser):
    """
    ser参数：被检测的数据，是一个Series
    返回值：异常值及其对应的行索引
    """
    
    # 计算平均值
    mean_data = ser.mean()
    # 计算标准差sigma
    std_data = ser.std()
    
    #小于（均值-3倍标准差） 或者 大于 （均值+3倍标准差）的数值都是异常的
    rule = ((mean_data-3*std_data)>ser) | ((mean_data+3*std_data)<ser)
    
    # 返回异常值的行索引
    index = np.arange(ser.shape[0])[rule]
    # 获取异常值
    outliers = ser.iloc[index]
    return outliers
    

1.2.6.2 箱形图异常值检测

除了使用3σ原则检测异常值之外，还可以使用箱形图检测异常值。
需要说明的是，箱形图对检测数据没有任何要求，即使不符合正态分布的数据集是能被检测的。
箱形图是一种用于显示一组数据分散情况的统计图，它通常由上边缘、上四分位数、中位数、下四分位数、下边缘和异常值组成。箱形图能直观地反映出一组数据的分散情况，一旦图中出现离群点（远离大多数值的点），就认为该离群点可能为异常值。

箱形图异常值检测函数

import pandas as pd
import numpy as np

def box_outliers(ser):
   # 对待检测的数据集进行排序
   new_ser = ser.sort_values()
   # 判断待检测数据集数的个数数偶数还是奇数
   if new_ser.count() %2==0:
       #计算Q3，Q1
       Q3 = new_ser[int(len(new_ser)/2):].median()
       Q1 = new_ser[:int(len(new_ser)/2)].median()
   else:
       #计算Q3，Q1
       Q3 = new_ser[int((len(new_ser)-1)/2):].median()
       Q1 = new_ser[:int((len(new_ser)-1)/2)].median()
       
   #计算IQR
   IQR = round(Q3-Q1,1)
   # 制定异常值规则
   rule = (round(Q3+1.5*IQR,1)<ser)|(round(Q1-1.5*IQR,1)>ser)
   
   # 返回异常值的行索引
   index = np.arange(ser.shape[0])[rule]
   # 获取异常值
   outliers = ser.iloc[index]
   return outliers
                        

1.2.7 Pandas读写MySQL

1.2.7.1 Python+sqlalchemy创建数据库

在使用Pandas进行数据库操作之前，先学会如何使用Python+sqlalchemy创建数据库。
其方法有如下两种：（数据库连接用户名： root，密码：123456，本地地址127.0.0.1，端口号：3306，创建数据库:sx1db)

from sqlalchemy import create_engine

# 方法一， 利用sqlalchemy_utils库的create_databse模块
# 需要提前用"pip install sqlalchemy_utils"安装sqlalchemy_utils
from sqlalchemy_utils import database_exists, create_database

engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/sx1db')
if not database_exists(engine.url):
    create_database(engine.url,encoding='gb2312')
print(database_exists(engine.url))


# 方法二 单纯使用sqlalchemy

with create_engine('mysql+pymysql://root:123456@localhost:3306/',
                   isolation_level='AUTOCOMMIT').connect() as connection:
    connection.execute('CREATE DATABASE sx1db charset="gb2312"')

• create_engine()基本格式

   engine = create_engine('dialect+driver://username:password@host:port/database',encoding='gb2312')

• 参数说明
  • dialect：数据库类型
  • driver：数据库驱动选择
  • username：数据库用户名
  • password： 用户密码
  • host：服务器地址
  • port：端口
  • database：数据库
  • charset:指定字符集

?create_engine

1.2.7.2 DataFrame.to_sql()存入MySQL

• df.to_sql()方法一般格式：

    .to_sql(name, 
         con, 
         flavor=None,
         schema=None, 
         if_exists='fail', 
         index=True, 
         index_label=None, 
         chunksize=None, 
         dtype=None)
    ```
- **参数详解**
    - name:指定的是将输入接入数据库当做的哪个表

    - con：与数据库链接的方式，推荐使用sqlalchemy的engine类型
 
    - schema: 相应数据库的引擎，不设置则使用数据库的默认引擎，如mysql中的innodb引擎

    - if_exists: 当数据库中已经存在数据表时对数据表的操作
        - replace:替换
        - append:追加
        - fail:则当表存在时提示ValueError。
 
    - index：对DataFrame的index索引的处理，为True时索引也将作为数据写入数据表
 
    - index_label:当上一个参数index为True时，设置写入数据表时index的列名称
 
    - chunsize：设置整数，如20000，一次写入数据时的数据行数量，当数据量很大时，需要设置，否则会链接超时写入失败。

    - dtype：写入数据表时，可以设置列的名称(The keys should be the column names and the values should be the SQLAlchemy types or strings for the sqlite3 legacy mode),需要设置时，类型需要和sqlalchemy的类型保持一致.当不设置时，to_sql生成表时会自动兼容最大的类型。

  .to_sql()参数中除 name、con必填外，可选参数index推荐使用False，同时dtype推荐不使用。

#### 1.2.7.3 Pandas从MySQL中获取数据
- **.read_sql()方法的一般格式**
```python
pandas.read_sql(sql, 
           con, 
           index_col=None,
           coerce_float=True, 
           params=None,
           parse_dates=None, 
           columns=None,
           chunksize=None)

• 常用参数说明
  • sql:SQL命令字符串
  • con：连接sql数据库的engine，一般可以用SQLalchemy或者pymysql之类的包建立
  • index_col: 选择某1列或几列作为index（或MultiIndex），字符串或字符串列表
  • coerce_float:非常有用，将数字形式的字符串直接以float型读入
  • parse_dates:将某一列日期型字符串转换为datetime型数据，与pd.to_datetime函数功能类似。
    可以直接提供需要转换的列名以默认的日期形式转换，也可以用字典的格式提供列名和转换的日期格式。
    比如{column_name: format string}（format string：“%Y:%m:%H:%M:%S”）。
  • columns:要选取的列。一般没啥用，因为在sql命令里面一般就指定要选择的列了
  • chunksize：如果提供了一个整数值，那么就会返回一个generator，每次输出的行数就是提供的值的大小。

1.2.8 汉字转拼音构建数据库名的方法

在实训中，要求将不同行业的数据存入到不同的的MySQL数据库中，数据库命名一个自然的想法是采用行业名的英文或汉语拼音为名。
汉语拼音全拼时太长，因此打算用汉语拼音每个字首字母大写+"db"作为数据库的名称。
爬取时，外面获得的行业名为汉语文字，如何将其转换为汉语拼音，网上有很多个共享的工具包可用。
这里用pinyin工具包，使用前先用python pip install pinyin进行安装。
构建数据库名称的代码如下：

hyName = '煤炭开采和洗选业'
dbName = [''+pinyin.get(x)[0].upper() for x in hyName] # mysql中部区分大小写，所以转换成大写起始没啥用
dbName = "".join(dbName)+'db'
print(dbName)

hyName = '煤炭开采和洗选业'
dbName = [''+pinyin.get(x)[0].upper() for x in hyName]  # mysql中部区分大小写，所以转换成大写起始没啥用
dbName = "".join(dbName)+'db'
print(dbName)

MTKCHXXYdb

1.3 实训操作

1.3.1 单步对一个行业进行分析处理

1.3.1.1 请求并解析网页数据

# 导入库        
import requests
from lxml import etree
import pandas as pd 
import csv

# 设置请求头
headers = {
    'user-Agent':'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.71 Safari/537.36',
    'Host':'s.askci.com'
}

# 设置请求url
url = 'https://s.askci.com/data/economy/00001/'   

# 获得响应
r = requests.get(url, headers = headers) 

# 编码转换，避免乱码
r.encoding = r.apparent_encoding

# 采用etree.HTML解析
html = etree.HTML(r.text)

# 采用xpath获得行业名
hyName = html.xpath('/html/body/div[4]/div[2]/div[1]/a[3]/text()')[0]

# 获取表格数据
html_tables = pd.read_html(r.content,header=0,index_col=0)

print("读取的类别:",type(html_tables),"\n表格的个数：",len(html_tables))

读取的类别: <class 'list'> 
表格的个数： 5

1.3.1.2 浏览各表格了解基本情况

html_tables[0].head()

html_tables[0].info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 112 entries, 202203 to 201202
Data columns (total 8 columns):
 #   Column     Non-Null Count  Dtype  
---  ------     --------------  -----  
 0   企业数量（个)    112 non-null    int64  
 1   亏损企业数（个)   112 non-null    int64  
 2   亏损总额（亿元)   112 non-null    float64
 3   累计增长（%)    112 non-null    float64
 4   存货（亿元)     112 non-null    float64
 5   累计增长（%).1  112 non-null    float64
 6   产成品（亿元)    112 non-null    float64
 7   累计增长（%).2  112 non-null    float64
dtypes: float64(6), int64(2)
memory usage: 7.9 KB

html_tables[1].head()

html_tables[2].head()

html_tables[3].head()

html_tables[4].head()

1.3.1.3 各DataFrame采用df.to_excel()存入excel文件

import os

excelFileName = hyName+".xlsx"
hyParaNames = ["经济指标","资产负债","收入利润","成本费用","产值指标"]
sheet_names = hyParaNames

# 经济指标
if not os.path.exists(excelFileName):
    html_tables[0].to_excel(excelFileName,sheet_name=sheet_names[0], index=True)
else:
    with pd.ExcelWriter(excelFileName, engine='openpyxl', mode='a',if_sheet_exists='replace') as writer:
            html_tables[0].to_excel(writer,sheet_name=sheet_names[0], index=True)

# 资产负债
if not os.path.exists(excelFileName):
    html_tables[1].to_excel(excelFileName,sheet_name=sheet_names[1], index=True)
else:
    with pd.ExcelWriter(excelFileName, engine='openpyxl', mode='a',if_sheet_exists='replace') as writer:
            html_tables[1].to_excel(writer,sheet_name=sheet_names[1], index=True)

# 收入利润
if not os.path.exists(excelFileName):
    html_tables[2].to_excel(excelFileName,sheet_name=sheet_names[2], index=True)
else:
    with pd.ExcelWriter(excelFileName, engine='openpyxl', mode='a',if_sheet_exists='replace') as writer:
            html_tables[2].to_excel(writer,sheet_name=sheet_names[2], index=True)

# 成本费用
if not os.path.exists(excelFileName):
    html_tables[3].to_excel(excelFileName,sheet_name=sheet_names[3], index=True)
else:
    with pd.ExcelWriter(excelFileName, engine='openpyxl', mode='a',if_sheet_exists='replace') as writer:
            html_tables[3].to_excel(writer,sheet_name=sheet_names[3], index=True)

# 产值指标
if not os.path.exists(excelFileName):
    html_tables[4].to_excel(excelFileName,sheet_name=sheet_names[4], index=True)
else:
    with pd.ExcelWriter(excelFileName, engine='openpyxl', mode='a',if_sheet_exists='replace') as writer:
            html_tables[4].to_excel(writer,sheet_name=sheet_names[4], index=True)

1.3.1.4 从excel中读取"煤炭开采和洗选业"的"经济指标"并查看基本信息

df_mt_jj = pd.read_excel("煤炭开采和洗选业.xlsx",sheet_name="经济指标",index_col=0)
# 查看开始5行
df_mt_jj.head()

# 查看后5行
df_mt_jj.tail()

# 查看总体信息
df_mt_jj.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 112 entries, 202203 to 201202
Data columns (total 8 columns):
 #   Column     Non-Null Count  Dtype  
---  ------     --------------  -----  
 0   企业数量（个)    112 non-null    int64  
 1   亏损企业数（个)   112 non-null    int64  
 2   亏损总额（亿元)   112 non-null    float64
 3   累计增长（%)    112 non-null    float64
 4   存货（亿元)     112 non-null    float64
 5   累计增长（%).1  112 non-null    float64
 6   产成品（亿元)    112 non-null    float64
 7   累计增长（%).2  112 non-null    float64
dtypes: float64(6), int64(2)
memory usage: 7.9 KB

# 查看统计描述
df_mt_jj.describe()

1.3.1.5 以煤炭开采和洗选业为分析对象，查看经济指标的中企业个数和亏损企业个数的变化情况，并绘制出图形予以展示

# 企业个数
data_qygs = df_mt_jj["企业数量（个)"]
data_qygs

类别年份
202203    4546
202202    4539
202112    4343
202111    4343
202110    4334
          ... 
201206    7690
201205    7690
201204    7695
201203    7696
201202    7696
Name: 企业数量（个), Length: 112, dtype: int64

# 亏损企业个数
data_ksqy = df_mt_jj["亏损企业数（个)"]
data_ksqy

类别年份
202203    1498
202202    1395
202112     970
202111    1024
202110    1083
          ... 
201206    1374
201205    1316
201204    1356
201203    1455
201202    1453
Name: 亏损企业数（个), Length: 112, dtype: int64

# 一次获取多列
data_qy = df_mt_jj[["企业数量（个)","亏损企业数（个)"]]
data_qy

112 rows × 2 columns

1.3.1.5.1 绘制折线图

import matplotlib.pyplot as plt

# 中文显示
plt.rcParams['font.sans-serif'] = ['SimHei'] 

# 绘制亏损企业个数和企业个数折线图
data_ksqy.plot(label="亏损企业数")
data_qygs.plot(label="企业数");

# 添加x标签与y标签
plt.xlabel("年月")
plt.ylabel("个数")

# 添加标题
plt.title("企业个数与亏损企业个数")

# 添加图例
plt.legend(loc="best");

import matplotlib.pyplot as plt

# 中文显示
plt.rcParams['font.sans-serif'] = ['SimHei'] 

# 绘制亏损企业个数和企业个数折线图
data_qy.plot()
# 添加x标签与y标签
plt.xlabel("年月")
plt.ylabel("个数")

# 添加标题
plt.title("企业个数与亏损企业个数")

# 添加图例
plt.legend(loc="best");

1.3.1.5.2 绘制柱状图

plt.rcParams['font.sans-serif'] = ['SimHei'] 
data_ksqy.plot(kind='bar',label="亏损企业数");
data_qygs.plot.bar("企业数",label="企业数")

# 添加x标签与y标签
plt.xlabel("年月")
plt.ylabel("个数")

# 添加标题
plt.title("企业个数与亏损企业个数")

# 添加图例
plt.legend(loc="best");

柱状图不适合绘制数目过多的数据
下面绘制2021年度的

data_qy.loc["202112":"202101"]

plt.rcParams['font.sans-serif'] = ['SimHei'] 
data_qy.loc["202112":"202101"].plot(kind='bar')

# 添加x标签与y标签
plt.xlabel("年月")
plt.ylabel("个数")

# 添加标题
plt.title("企业个数与亏损企业个数")

# 添加图例
plt.legend(loc="best");

在plt.legend()函数中加入若干参数：

    plt.legend(bbox_to_anchor=(num1, num2), loc=num3, borderaxespad=num4)

bbox_to_anchor(num1,num2)表示legend的位置和图像的位置关系，num1表示水平位置，num2表示垂直位置。num1=0表示legend位于图像的左侧垂直线(这里的其它参数设置：num2=0,num3=3,num4=0)。

plt.rcParams['font.sans-serif'] = ['SimHei'] 
data_qy.loc["202112":"202101"].plot(kind='bar')

# 添加x标签与y标签
plt.xlabel("年月")
plt.ylabel("个数")

# 添加标题
plt.title("企业个数与亏损企业个数")

# 添加图例到axis外
num1=1.05
num2=0
num3=3
num4=0
plt.legend(bbox_to_anchor=(num1, num2), loc=num3, borderaxespad=num4);

1.3.1.6 以煤炭开采和洗选业为分析对象，查看2021年度 经济指标的中亏损总额，可视化出来，并查看其最大值、最小值及相应的月份的详细经济指标

# 取的经济指标中2021年度数据
df_mt_jj_2021 = df_mt_jj.loc["202112":"202101"]
df_mt_jj_2021

# 查看2021年煤炭开采和洗选业的亏损总额
data_ksze = df_mt_jj.loc["202112":"202101","亏损总额（亿元)"]
data_ksze

类别年份
202112    560.9
202111    440.1
202110    382.8
202109    383.1
202108    358.1
202107    323.6
202106    262.1
202105    232.5
202104    208.0
202103    165.5
202102    110.2
Name: 亏损总额（亿元), dtype: float64

# 亏损总额最大值
data_ksze.max()

560.9

# 亏损总额最大值 
# 直接从df_mt_jj_2021中获取
df_mt_jj_2021["亏损总额（亿元)"].max()

560.9

# 亏损总额最小值
data_ksze.min()

110.2

# 查看亏损总额最大的月份的经济指标
df_mt_jj_2021[df_mt_jj_2021["亏损总额（亿元)"]==df_mt_jj_2021["亏损总额（亿元)"].max()]

# 查看亏损总额最少的月份的经济指标
df_mt_jj_2021[df_mt_jj_2021["亏损总额（亿元)"]==df_mt_jj_2021["亏损总额（亿元)"].min()]

# 绘制出2021年度亏损总额的曲线图，并添加图表题、x标签、y标签
df_mt_jj_2021["亏损总额（亿元)"].plot()
plt.title("2021年度亏损总额")
plt.ylabel("亏损总额(亿元)")
loc=df_mt_jj_2021.index
plt.xticks(loc,[str(x)+"月" for x in range(2,13)])
plt.xlabel("月份");

# 绘制出2021年度亏损总额的条形图，并添加图表题、x标签、y标签
df_mt_jj_2021["亏损总额（亿元)"].plot.bar(label="亏损总额")
# 添加数据标注
for x, y in enumerate(df_mt_jj_2021["亏损总额（亿元)"].values):   
    plt.text(x+0.4, y+1,y,ha='right',va='bottom')

plt.title("2021年度亏损总额")
plt.ylabel("亏损总额(亿元)")
plt.xlabel("月份")
plt.legend(loc='best');

1.3.1.7 以煤炭开采和洗选业为分析对象，查看成本费用总的管理费用，并采用3西格玛方法或者箱型图法查看是否有异常的点

1.3.1.7.0 获取成本费用中的管理费用

df_mt_cb = pd.read_excel("煤炭开采和洗选业.xlsx",sheet_name="成本费用",index_col=0)
# 查看开始5行
df_mt_cb.head()

ser_cb_gl = df_mt_cb["管理费用（亿元)"]
ser_cb_gl

类别年份
202203     498.90
202202     290.90
202112    1897.20
202111    1590.00
202110    1412.20
           ...   
201206    1103.77
201205     921.51
201204     727.64
201203     533.49
201202     331.51
Name: 管理费用（亿元), Length: 112, dtype: float64

1.3.1.7.1 3σ原则异常值检测

• 定义3σ原则异常值检测

import numpy as np
import pandas as pd

def three_sigma(ser):
    """
    ser参数：被检测的数据，是一个Series
    返回值：异常值及其对应的行索引
    """
    
    # 计算平均值
    mean_data = ser.mean()
    # 计算标准差sigma
    std_data = ser.std()
    
    #小于（均值-3倍标准差） 或者 大于 （均值+3倍标准差）的数值都是异常的
    rule = ((mean_data-3*std_data)>ser) | ((mean_data+3*std_data)<ser)
    
    # 返回异常值的行索引
    index = np.arange(ser.shape[0])[rule]
    # 获取异常值
    outliers = ser.iloc[index]
    return outliers    

• 使用3σ原则异常值检测异常点

three_sigma(ser_cb_gl)

Series([], Name: 管理费用（亿元), dtype: float64)

结果为空值，说明没有异常点出现

1.3.1.7.2 箱形图异常值检测

• 定义箱形图异常值检测函数

import pandas as pd
import numpy as np

def box_outliers(ser):
    # 对待检测的数据集进行排序
    new_ser = ser.sort_values()
    # 判断待检测数据集数的个数数偶数还是奇数
    if new_ser.count() %2==0:
        #计算Q3，Q1
        Q3 = new_ser[int(len(new_ser)/2):].median()
        Q1 = new_ser[:int(len(new_ser)/2)].median()
    else:
        #计算Q3，Q1
        Q3 = new_ser[int((len(new_ser)-1)/2):].median()
        Q1 = new_ser[:int((len(new_ser)-1)/2)].median()
        
    #计算IQR
    IQR = round(Q3-Q1,1)
    # 制定异常值规则
    rule = (round(Q3+1.5*IQR,1)<ser)|(round(Q1-1.5*IQR,1)>ser)
    
    # 返回异常值的行索引
    index = np.arange(ser.shape[0])[rule]
    # 获取异常值
    outliers = ser.iloc[index]
    return outliers

• 使用箱形图异常值检测

box_outliers(ser_cb_gl)

Series([], Name: 管理费用（亿元), dtype: float64)

1.3.1.8 煤炭开采和洗选业数据存取MySQL

1.3.1.8.1 创建数据库

# 根据爬取的行业名(hyName)自动构建数据库名
hyName

'煤炭开采和洗选业'

import pinyin

dbName = [''+pinyin.get(x)[0].upper() for x in hyName] 
dbName = "".join(dbName)+'db'
dbName

'MTKCHXXYdb'

from sqlalchemy import create_engine

# 方法一， 利用sqlalchemy_utils库的create_databse模块，
# 需要提前用"pip install sqlalchemy_utils"安装sqlalchemy_utils
from sqlalchemy_utils import database_exists, create_database

connection = create_engine(f'mysql+pymysql://root:123456@127.0.0.1:3306/{dbName}',encoding='gb2312')
if not database_exists(connection.url):
    create_database(connection.url,encoding='gb2312')
print(database_exists(connection.url))

True

或采用方法二实现如下：

from sqlalchemy import create_engine

# 方法二 单纯使用sqlalchemyf

with create_engine('mysql+pymysql://root:123456@localhost:3306/',
                   isolation_level='AUTOCOMMIT').connect() as connection:
    connection.execute(f'CREATE DATABASE {dbName} charset="gb2312"')

1.3.1.8.2 将煤炭开采和选洗业数据存入MySQL

list('煤炭开采和洗选业')

['煤', '炭', '开', '采', '和', '洗', '选', '业']

hyParaNames

['经济指标', '资产负债', '收入利润', '成本费用', '产值指标']

tableName = hyParaNames[0]
tableName

'经济指标'

# 经济指标
html_tables[0]

112 rows × 8 columns

html_tables[0].columns#=['企业数量', '亏损企业数', '亏损总额', '累计增长', '存货', '累计增长','产成品', '累计增长']

Index(['企业数量（个)', '亏损企业数（个)', '亏损总额（亿元)', '累计增长（%)', '存货（亿元)', '累计增长（%).1',
       '产成品（亿元)', '累计增长（%).2'],
      dtype='object')

坑点1：想法是将列索引作为MySQL的列名创建数据表，发现列名含“（”的后续有问题
坑点2：列名有些是相同的，应该想办法更改为不同（最后的解决办法可能最好是直接重新赋值）
解决办法如下

[xx[:xx.find("（")] if xx.find("（")>0 else xx for xx in html_tables[0].columns]

['企业数量', '亏损企业数', '亏损总额', '累计增长', '存货', '累计增长', '产成品', '累计增长']

采用如上方法能够比较方便的取得括号之前的部分，但对于重复列名（如"累计增长")不是很容易处理。
若要用程序处理，可以用循环+判断实现将其与前一列名并入进来予以明确。
但对于本应用，字段是明确的，且并不太繁杂，可以采用下面的方式，直接定义好列名，在存储到数据库前从次取出去替换掉。

columns = [['企业数量', '亏损企业数', '亏损总额', '亏损总额累计增长', '存货', '存货累计增长', '产成品', '产成品累计增长'],
           ['流动资产合计', '流动资产合计累计增长', '应收帐款', '应收帐款累计增长', '资产总计', '资产总计累计增长', '负债合计', '负债合计累计增长'],
           ['主营业务收入', '主营业务收入累计增长', '利润总额', '利润总额累计增长', '主营业务税金及附加', '主营业务税金及附加累计增长', '应交增值税', '应交增值税累计增长'],
           ['主营业务成本', '主营业务成本累计增长', '销售费用', '销售费用累计增长', '管理费用', '管理费用累计增长', '财务费用', '财务费用累计增长'],
           ['增加值同比增长', '增加值累计增长', '出口交货值当月值', '同比增长', '出口交货值累计值', '累计增长']
          ]
columns

[['企业数量', '亏损企业数', '亏损总额', '亏损总额累计增长', '存货', '存货累计增长', '产成品', '产成品累计增长'],
 ['流动资产合计',
  '流动资产合计累计增长',
  '应收帐款',
  '应收帐款累计增长',
  '资产总计',
  '资产总计累计增长',
  '负债合计',
  '负债合计累计增长'],
 ['主营业务收入',
  '主营业务收入累计增长',
  '利润总额',
  '利润总额累计增长',
  '主营业务税金及附加',
  '主营业务税金及附加累计增长',
  '应交增值税',
  '应交增值税累计增长'],
 ['主营业务成本',
  '主营业务成本累计增长',
  '销售费用',
  '销售费用累计增长',
  '管理费用',
  '管理费用累计增长',
  '财务费用',
  '财务费用累计增长'],
 ['增加值同比增长', '增加值累计增长', '出口交货值当月值', '同比增长', '出口交货值累计值', '累计增长']]

columns[0]

['企业数量', '亏损企业数', '亏损总额', '亏损总额累计增长', '存货', '存货累计增长', '产成品', '产成品累计增长']

columns[1]

['流动资产合计',
 '流动资产合计累计增长',
 '应收帐款',
 '应收帐款累计增长',
 '资产总计',
 '资产总计累计增长',
 '负债合计',
 '负债合计累计增长']

html_tables[0].columns=columns[0]

html_tables[0].head()

# 数据表名以各参数名为表名
hyParaNames

['经济指标', '资产负债', '收入利润', '成本费用', '产值指标']

# 经济指标存入数据库
# 获取数据表名
tableName = hyParaNames[0]
# 更换表中列名
html_tables[0].columns=columns[0]
# 存入数据库
html_tables[0].to_sql(name=tableName, con=connection, if_exists='replace', index=True)

# 资产负债 情况存入数据库
tableName = hyParaNames[1]
html_tables[1].columns=columns[1]
html_tables[1].to_sql(name=tableName, con=connection, if_exists='replace', index=True)

html_tables[2]

112 rows × 8 columns

# 收入利润 情况存入数据库
tableName = hyParaNames[2]
html_tables[2].columns=columns[2]
html_tables[2].to_sql(name=tableName, con=connection, if_exists='replace', index=True)

#成本费用 情况存入数据库
tableName = hyParaNames[3]
html_tables[3].columns=columns[3]
html_tables[3].to_sql(name=tableName, con=connection, if_exists='replace', index=True)

#产值指标 情况存入数据库
tableName = hyParaNames[4]
html_tables[4].columns=columns[4]
html_tables[4].to_sql(name=tableName, con=connection, if_exists='replace', index=True)

1.3.1.8.3 从MySQL中读取数据

• 从hsjskcxydb(黑色金属矿采选业)读取经济指标数据表

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database

# 创建连接
dbName = 'hsjskcxydb'
connection = create_engine(f'mysql+pymysql://root:123456@127.0.0.1:3306/{dbName}',encoding='gb2312')
if not database_exists(connection.url):
    print(f"数据库{dbNamee}不存在!")

# 读取表中所有列数据sql语句
sqlStr = "SELECT * FROM 经济指标"
# 把sqlStr查询结果读取为数据框
dfAll = pd.read_sql(sqlStr,connection,index_col='类别年份')
dfAll.head()

# 读取表中["企业数量","亏损企业数","亏损总额"]列数据SQL语句
sqlStr = "SELECT 类别年份,企业数量,亏损企业数,亏损总额 FROM 经济指标"
# 把sqlStr查询结果读取为数据框
dfSelect = pd.read_sql(sqlStr,connection,index_col='类别年份')
dfSelect.head()

1.3.2 根据实训总体要求，循环爬取多页并存储

数据采集并存储为excel和mysql数据部分代码汇总如下。
各数据查看及可视化就不再累述。

# 导入库        
import requests
from lxml import etree
import pandas as pd 
import csv
import os
import pinyin
from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database

# 设置请求头
headers = {
    'user-Agent':'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.71 Safari/537.36',
    'Host':'s.askci.com'
}

# 各参数名称
hyParaNames = ["经济指标","资产负债","收入利润","成本费用","产值指标"]

# 各参数表头
columns = [['企业数量', '亏损企业数', '亏损总额', '亏损总额累计增长', '存货', '存货累计增长', '产成品', '产成品累计增长'],
           ['流动资产合计', '流动资产合计累计增长', '应收帐款', '应收帐款累计增长', '资产总计', '资产总计累计增长', '负债合计', '负债合计累计增长'],
           ['主营业务收入', '主营业务收入累计增长', '利润总额', '利润总额累计增长', '主营业务税金及附加', '主营业务税金及附加累计增长', '应交增值税', '应交增值税累计增长'],
           ['主营业务成本', '主营业务成本累计增长', '销售费用', '销售费用累计增长', '管理费用', '管理费用累计增长', '财务费用', '财务费用累计增长'],
           ['增加值同比增长', '增加值累计增长', '出口交货值当月值', '同比增长', '出口交货值累计值', '累计增长']
          ]

for i in range(0,5):
    # 设置请求url
    url = f'https://s.askci.com/data/economy/0000{i+1}/'  

    # 获得响应
    r = requests.get(url, headers = headers) 

    # 编码转换，避免乱码
    r.encoding = r.apparent_encoding

    # 采用etree.HTML解析
    html = etree.HTML(r.text)

    # 采用xpath获得行业名
    hyName = html.xpath('/html/body/div[4]/div[2]/div[1]/a[3]/text()')[0]

    # 获取表格数据
    html_tables = pd.read_html(r.content,header=0,index_col=0)
    
    # 将数据存入excel表
    excelFileName = hyName+".xlsx"
    sheet_names = hyParaNames    
    for k in range(5):
        if not os.path.exists(excelFileName):
            html_tables[k].to_excel(excelFileName,sheet_name=sheet_names[k], index=True)
        else:
            with pd.ExcelWriter(excelFileName, engine='openpyxl', mode='a',if_sheet_exists='replace') as writer:
                    html_tables[k].to_excel(writer,sheet_name=sheet_names[k], index=True)
    
    # 将数据出如MySQL
    # 创建数据库
    dbName = [''+pinyin.get(x)[0].upper() for x in hyName] 
    dbName = "".join(dbName)+'db'
    connection = create_engine(f'mysql+pymysql://root:123456@127.0.0.1:3306/{dbName}',encoding='gb2312')
    if not database_exists(connection.url):
        create_database(connection.url,encoding='gb2312')
    print(f"数据库{dbName}已准备好!" if database_exists(connection.url) else f"数据库{dbName}准备失败!")
    
    # 数据存入MySQL
    for m in range(len(hyParaNames)):
        tableName = hyParaNames[m]
        html_tables[m].columns=columns[m]
        html_tables[m].to_sql(name=tableName, con=connection, if_exists='replace', index=True)
    print(f"数据库{dbName}已数据已存储!")
    

数据库MTKCHXXYdb已准备好!
数据库MTKCHXXYdb已数据已存储!
数据库SYHTRQKCYdb已准备好!
数据库SYHTRQKCYdb已数据已存储!
数据库HSJSKCXYdb已准备好!
数据库HSJSKCXYdb已数据已存储!
数据库YSJSKCXYdb已准备好!
数据库YSJSKCXYdb已数据已存储!
数据库FJSKCXYdb已准备好!
数据库FJSKCXYdb已数据已存储!

1.4 实训小结

开发者涨薪指南

48位大咖的思考法则、工作方式、逻辑体系