数据治理｜如何用python高效整合分散保存的统计年鉴数据

启研学社由知名学者担任学术顾问，由高校师生与企研数据科学团队联合组建的，是以大数据资源及相关技术助力中国学术、智库与行业研究为宗旨的研究组织。团队当前的主要目标是挖掘行政、经济与社会大数据资源在经济学学术、智库与相关行业研究领域中的应用价值，以学术研究为标准开展大数据治理研究，努力探索大数据分析技术融入中国经济社会研究的可行进路。 

1.需求描述

项目目标：将统计年鉴中的.xls格式分表保存的统计数据，整合到一张大表中。

1.1 原始数据

以原始数据中表  【2012-市-专任教师数：普通小学、普通中学（2-33）.xls】 为例 例表展示如下：续表连接处展示如下：

1.2 目标数据

最终需要的目标数据是这样的：续表处展示如下：

1.3 需求分析

通过原始数据和需求数据的展示，来给大家分析一下需求是怎么样的。

(1)列名称重新命名，原始列名称存在层级，例如普通中学下有全市和市辖区两个层级。在目标数据中，这样的层级关系被命名为'普通中学-全市'，'普通中学-市辖区'。遵循'一级列名称-二级列名称'的规则。

(2)表头，目标数据摒弃了表头处多余的行，只留一行列名称作为表头，列名称下面直接展示数据。

(3)续表连接，目标数据在续表连接处要无缝衔接，不要多余的行。可以看到，原始数据续表处的 '锦州市' 和 '营口市' 已经被连接在一起，不再保留原始数据中续表处多余的行。

(4)列变化，第一，删除 'City' 列。第二，在第一列的位置添加了一列 '省份'，'省份' 列的内容依赖于 '城市' 列，可以看出，'城市' 列的值存在层级关系，第一级为省名称，第二级为市名称，其中市名称的第一个字符为一个[空格]。而 '省份' 列的内容则是 '城市' 列所隶属于的省的名称，如果 '城市' 列的内容本身就是省名称，则 '省份' 列的内容与其相同。

2.原始数据详细说明

(1)数据格式，原始数据表全部为.xls格式的工作表，且有工作表保护(设有密码)。

(2)工作表sheet数量不一致，存在部分表有多个sheet表，其中每一个续表作为一个单独的sheet表。数据展示如下：(3)工作表列名称不完全一致，有的工作表是地方财政收支表，有的工作表是专任教师数量表。这就导致不同种类工作表的列命名需要根据实际情况。

(4)表头行数，续表处多余行数不一致，同样是不同种类工作表的原因，一些工作表的表头行数，续表处多余行数不相同，举个例子：

表头行数：续表处多余行数：可以看出不同种类的工作表中表头行数，续表处多余行数确实是不一致的，然而即使是同种类的工作表，依然存在个别工作表的这两部分行数不一致，这里不再进行展示。

3.案例展示

3.1 单个sheet表案例展示

以文件夹 【市-专任教师数：普通小学、普通中学】下的十个文件为例这十个表都是单个sheet表的工作表。我们可以处理如下：

处理问题1: 读取保护问题

首先，我们要读取数据。

import pandas as pd

path = r'2012-市-专任教师数：普通小学、普通中学（2-33）.xls'
data = pd.read_excel(path)

这里收到报错：

Workbook is encrypted : 工作表是受保护的。

看来是因为工作表有保护，那就换一个方式读取数据。

from openpyxl import Workbook, load_workbook
import warnings
warnings.filterwarnings('ignore')

wb = load_workbook(path)

df = pd.DataFrame(wb[wb.sheetnames].values)
df

处理问题2: 文件格式问题

依然收到报错：根据报错信息，猜测应该是文件格式的问题。既然这样，那就手动改一下文件的格式，另存为.xlsx格式的文件。再试一下：原来改一下文件的格式就可以读取了，那再试一下Pandas能不能读取。Pandas 也能读取。

处理问题3: 批量处理问题

问题又来了，文件那么多，总不能一个一个手动地另存为.xlsx文件吧？好像也不太体面。那就不妨使用Python批量去做这件事情吧，需要先把待转换的.xls文件全部存放到一个文件夹下，再准备一个文件夹用来存放转换好的.xlsx文件。做转换时输入这两个文件夹的路径就可以了。

import os
from ctypes import *
import win32com.client as win32

def transform(input_path,output_path):
    #文件夹下面所有的文件
    fileList = os.listdir(input_path)
    num = len(fileList)
    for i in range(num):
        #文件和格式分开
        file_Name = os.path.splitext(fileList[i])
        if file_Name[1] == '.xls':
            #要转换的excel
            transfile1 = input_path+'\\'+fileList[i]
            #转换出来excel
            transfile2 = output_path+'\\'+file_Name[0]
            excel=win32.gencache.EnsureDispatch('excel.application')
            #打开要转换的excel
            pro=excel.Workbooks.Open(transfile1)
            # 另存为xlsx格式
            pro.SaveAs(transfile2 + ".xlsx", FileFormat=51)
            pro.Close()
            excel.Application.Quit()
if __name__=='__main__':
    #待转换文件所在目录
    path1=r"市-专任教师数：普通小学、普通中学.xls"
    #转换文件存放目录
    path2=r"市-专任教师数：普通小学、普通中学-xlsx"
    transform(path1, path2)

下面是转换好的文件同样的，想要批量处理这些工作表，文件的路径以及文件的名称也不能每次都手动去修改，所以文件路径和名称也需要一次性获取。下面是获取文件路径和名称的代码：

有一个地方要注意一下，所有的文件需要存放在一下文件夹下，不要设置子文件夹，不要存放其他文件。

def get_file_list(path, ex):
    """
    获取指定路径下的符合后缀名条件的文件路径
    path: 监测的路径
    ex: 条件
    return: 路径列表
    """
    file_path_list = []
    for dir, folder, file in os.walk(path):
        for i in file:
            if os.path.splitext(i)[1] in ex:
                file_path_list.append(os.path.join(dir, i))
    return file_path_list

file_path_list = get_file_list(r"市-专任教师数：普通小学、普通中学-xlsx",['.xlsx'])

- 通过切片获取文件名称
print(file_path_list[0].split('\\')[-1])

- 所有文件的路径
file_path_list

接下来读取工作表，观察一下原始数据的特点。

处理问题4: 整理表头和无用的行

上面是使用Pandas读出来的工作表原表，这里可以看到表头处比较杂乱无章，有许多无用的行。接下来要做的就是删除表头和续表连接处不需要的行。那么关键问题就来了，怎么样才能删除这些无用的行呢？简单思考一下，觉得表头多余行比较好处理，在使用pd.read_excel()读取工作表的时候设置一下header参数就可以了。至于续表连接的地方，通过观察工作表，发现一张表所有的连接处的多余的行数都相同(假设为x)，且在第一列，也就是 '城市' 列中，只有在续表连接的地方会出现空值，而且连接处第一行一定是空值，那么找到第一个空值的索引，在向下删除连续的 x 行，照这种方式循环下去，不就刚好去除所有的无用行，得到我想要的数据了吗？于是就写了这样的代码：

# 续表的数量
num = 0
for n in data.iloc[:,0]:
    while '续表' in n:
        num += 1
        break

# 把空值替换成 'NULL'，方便匹配
data = data.fillna('NULL')

for i in range(table_num):
    while 'NULL' in list(data['城市']):
            
        index_ = data['城市'].tolist().index('NULL')

        # 根据情况删除行数：index + 向下删除行数
        data = data.drop(list(range(index_,index_+7)))

运行一下，收到了这样的报错：这个报错明显就是在说是索引出问题了，根据提示可找到问题所在。在一次循环中，删除了一部分无用行之后，索引也跟着被删除，这样的话，原本连续的索引就不再连续，所以pd.drop()方法就找不到要删除的行的索引，引发了KeyError。既然如此，那就重置一下索引，看看能不能走得通。

先试试改一下上面代码的最后一句，添加reset_index()方法。

data = data.drop(list(range(index_,index_+7))).reset_index()

还是收到了报错：看来reset_index()方法不适合在一个数据表中循环使用，那好吧，那就手动添加一个连续的索引放在循环中，每一次循环都得到一个新的索引。下面是修改索引后的代码：

# 续表的数量
num = 0
for n in data.iloc[:,0]:
    while '续表' in n:
        num += 1
        break

# 把空值替换成 'NULL'，方便匹配
data = data.fillna('NULL')

for i in range(num):
    while 'NULL' in list(data['城市']):
            
        index_ = data['城市'].tolist().index('NULL')

        # 根据情况删除行数：index + 向下删除行数
        data = data.drop(list(range(index_,index_+7)))
 
        # 删除行后，原始索引被破坏，这里重新设置连续的
        len = data.shape[0]
        data['index'] = list(range(len))
        data = data.set_index('index')

这次终于成功了。。。原本在 "锦州市" 与 "营口市" 之间的无用数据已经被删除。这个问题解决之后还没来得及高兴，笔者就发觉代码这样写的话，泛化能力实在是太差了，在原始数据情况说明中有提到，一些工作表的表头行数，续表处多余行数不相同。那么要用这段代码来做删除操作的话这样的话，岂不是对每个工作表都要重新设置合适的参数啊？简直不能忍！！！必须重新写一段能照顾到所有文件的代码。再次观察数据，又有了新的发现，在表的第一列("城市"列)中，除去无用的行数据之外，不存在任何缺失值，而且第一列("城市"列)的数据都有着相同的特征，这列数据的值均为："xx省","xx市","xx自治区" 等规范的数据值。那不就可以通过匹配 "省","市","自治区" 等关键字来保留需要的行数据了吗？有了这个发现，成功之路仿佛就在眼前啊！

但着手写代码的时候又会遇到一些其他的问题，这里表头和续表处都会有"城市","城 市","城  市"等妨碍筛选有用的行的数据值。当我想要通过关键字"城市","城 市","城  市"来排除这一特定无用的行的时候，又迎来了新的问题。比如 "晋城市","运城市"等市名中也含有 "城市" 字符。这就尴尬了，差点把需要的数据误删。

有句话这样讲：得不到就毁掉，虽然用在这里不怎么合适，但还是令我幡然醒悟，立马就有了新的想法，既然你容易让我误会，那我就把你替换成不会产生误会的值。

data_city = data.iloc[:,0]
data = data[data_city.str.contains('省') | data_city.str.contains('自治区') | data_city.str.contains('市')]

这样就准确地保留了所有有用的数据，得到了一份干净完整的数据表了。观察上表发现还是有一些小问题，这样得到的新表，它的索引是不连续的，为了方便后续的处理，为这个表赋予一个来连续的索引。

# 索引被破坏，重新设置连续的索引
len=data.shape[0]
data['index'] = list(range(len))
data = data.set_index('index')

处理问题5: 处理列

根据需求，接下来应该处理数据列了，首先要删除表中的城市英文名称 "City" 列。由于有的表原本就没有这一列，那这样写会比较合适。

# 删除 'City' 列
try:
    data = data.drop('City',axis = 1)
except:
    pass

处理完 "City" 列之后要在表第一列的位置添加一列 "省份" 列，"省份" 列的内容则是 "城市" 列所隶属于的省的名称，如果 "城市" 列的内容本身就是省名称，则 "省份" 列的内容与其相同。观察原表不难看出，"城市" 列中的省，市存在层级关系，省名称左边没有空格，而市名称左边第一个字符一定会是一个空格，这样就好办了。

# 在第一列的位置添加一列：'省份'
data.insert(0,'省份',None)
    
dfc = data['城市']
for i in range(len(dfc)):
    if dfc[i][0] != ' ':
        data.loc[i,'省份']=dfc[i]
    else:
        data.loc[i,'省份']=data.loc[i-1,'省份']

这样就得到了目标数据那样的效果。另外还有一个小细节，在一些表中，第一行是城市合计数据。而"省份" 列不需要 "城市合计" 这个值。所以要用空值替换掉它，由于即使不存在 "城市合计" 字符也不会报错，所以这里就不用try - except的组合了。

# 去除 '省份' 列多余的值
 data.replace({"省份":{'城市合计':''}},inplace=True)

保存一下我们需要的表，就算大功告成啦。

data.to_excel('OK_' + file_name,index = False)

最后将这些代码写入到一个循环中，就可以一次性处理这个文件夹下所有的待处理表了。最终批量循环处理的代码附在了最后。

3.2 多个sheet 表案例展示

以表【2011-市-地方一般公共预算收支状况(全市)（2-15）.xlsx】为例。

在上面的2.原始数据情况说明中已经展示了多个sheet表的数据情况。基于上述的单个sheet表的处理情况来看，只需要把多个sheet表连接成一个sheet就可以加入到上面的处理过程参与批量处理。当然，做这些之前需要转换一下文件的格式，以及需要获取文件的路径file_path_list。下面是 多个sheet表连接成一个sheet表的代码。

import openpyxl
import pandas as pd

# 列名称根据表的情况命名
col_names = ['城市',
             'City',
             '地方财政一般预算内收入',
             '地方财政一般预算内支出',
             '科学支出',
             '教育支出']
             

def sheet_merge(file:list):
    for path in file_path_list:

        wb = openpyxl.load_workbook(path)
        # 先读取第一个sheet  
        df = pd.read_excel(path,sheet_name = 0,names = col_names)
        
        # 然后依次添加之后的sheet
        for i in range(1, len(wb.sheetnames)):
            data = pd.read_excel(path,sheet_name = i, names = col_names)
            df = df.append(data,ignore_index=True)

        # 获取文件名称
        file_name = path.split('\\')[-1]

        # 生成本地 单个 sheet表的 excel 工作表
        df.to_excel('1_sheet_' + file_name,index = False)

if __name__=='__main__':
       
    sheet_merge(file = file_path_list)

这样得到的data就是数据表连接后的表了，对于这部分数据，用之前的 "单个sheet表案例展示" 中的操作即可。

4.附件: 完整代码

4.1 批量实现".xls"转".xlsx"

import os
from ctypes import *
import win32com.client as win32

def transform(input_path,output_path):
    #文件夹下面所有的文件
    fileList = os.listdir(input_path)
    num = len(fileList)
    for i in range(num):
        #文件和格式分开
        file_Name = os.path.splitext(fileList[i])
        if file_Name[1] == '.xls':
            #要转换的excel
            transfile1 = input_path+'\\'+fileList[i]
            #转换出来excel
            transfile2 = output_path+'\\'+file_Name[0]
            excel=win32.gencache.EnsureDispatch('excel.application')
            #打开要转换的excel
            pro=excel.Workbooks.Open(transfile1)
            # 另存为xlsx格式
            pro.SaveAs(transfile2 + ".xlsx", FileFormat=51)
            pro.Close()
            excel.Application.Quit()
if __name__=='__main__':
    #待转换文件所在目录
    path1=r"---------'.xls'文件所在路径----------"
    #转换文件存放目录
    path2=r"--------存放'.xlsx'文件的路径--------"
    transform(path1, path2)

4.2 批量获取文件路径

def get_file_list(path, ex):
    """
    获取指定路径下的符合后缀名条件的文件路径
    path: 监测的路径
    ex: 条件
    return: 路径列表
    """
    file_path_list = []
    for dir, folder, file in os.walk(path):
        for i in file:
            if os.path.splitext(i)[1] in ex:
                file_path_list.append(os.path.join(dir, i))
    return file_path_list

#获取路径，文件所在目录(文件全部存放在一个文件夹下，不要设置子文件夹，不要存放其他文件)
file_path_list = get_file_list(r"市-专任教师数：普通小学、普通中学-xlsx",['.xlsx'])

# 通过切片获取文件名称
print(file_path_list[0].split('\\')[-1])

4.3 多个 sheet 表合并成一个 sheet 表

import openpyxl
import pandas as pd

# 列名称根据表的情况命名
col_names = ['城市',
             'City',
             '地方财政一般预算内收入',
             '地方财政一般预算内支出',
             '科学支出',
             '教育支出']
             

def sheet_merge(file:list):
    for path in file_path_list:

        wb = openpyxl.load_workbook(path)
        # 先读取第一个sheet  
        df = pd.read_excel(path,sheet_name = 0,names = col_names)
        
        # 然后依次添加之后的sheet
        for i in range(1, len(wb.sheetnames)):
            data = pd.read_excel(path,sheet_name = i, names = col_names)
            df = df.append(data,ignore_index=True)

        # 获取文件名称
        file_name = path.split('\\')[-1]

        # 生成本地 单个 sheet表的 excel 工作表
        df.to_excel('1_sheet_' + file_name,index = False)

if __name__=='__main__':
       
    sheet_merge(file = file_path_list)

4.4 单个 sheet 表内续表连接

COL_NAME_2010= [
    '城市',
    '普通高等学校-全市',
    '普通高等学校-市辖区',
    '普通中学-全市',
    '普通中学-市辖区',
    '小学-全市',
    '小学-市辖区'
]

COL_NAME_others= [
    '城市',
    'City',
    '普通中学-全市',
    '普通中学-市辖区',
    '小学-全市',
    '小学-市辖区'
]


def table_merging(file:list):
    for path in file:
        
        # 原始数据中列名称和列数量在文件【2010-*】之后发生了变化，这里分情况读入数据
        if eval(file_name[:4]) <= 2010:
            data = pd.read_excel(path,names = COL_NAME_2010)
        else:
            data = pd.read_excel(path,names = COL_NAME_others)
        
        # 替换 '城市','城 市','城  市' 等影响筛选数据的字符
        data.replace({'城市':{'城市':'row_to_del','城 市':'row_to_del','城  市':'row_to_del'}},inplace=True)
        
        # 筛选出需要保留的数据行
        data_city = data.iloc[:,0]
        data = data[data_city.str.contains('省') | data_city.str.contains('自治区') | data_city.str.contains('市')]

        # 索引被破坏，重新设置连续的索引
        le=data.shape[0]
        data['index'] = list(range(le))
        data = data.set_index('index')
        
        # 删除 'City' 列
        try:
            data = data.drop('City',axis = 1)
        except:
            pass
    
        # 在第一列的位置添加一列：'省份'
        data.insert(0,'省份',None)
    
        dfc = data['城市']
        for i in range(len(dfc)):
            if dfc[i][0] != ' ':
                data.loc[i,'省份']=dfc[i]
            else:
                data.loc[i,'省份']=data.loc[i-1,'省份']
    
        # 去除 '省份' 列多余的值
        data.replace({"省份":{'城市合计':''}},inplace=True)

        # 获取文件名称
        file_name = path.split('\\')[-1]

        # 生成本地 excel 工作表
        data.to_excel('OK_' + file_name,index = False)


if __name__=='__main__':

    table_merging(file = file_path_list)

星标⭐我们不迷路！

想要文章及时到，文末“在看”少不了！

点击搜索你感兴趣的内容吧

往期推荐

数据Seminar

这里是大数据、分析技术与学术研究的三叉路口

文 | 任正长

审阅 | 陈煌杰 施丹燕

    欢迎扫描👇二维码添加关注    

点击下方“阅读全文”了解更多