【python实战】批量合并excel

1.背景

这两天脑子修复ing的时候，把mooc上嵩天老师的《python数据分析与展示》刷了一下，

课程主要讲了numpy、pandas和matplotlib库的入门，

正好这段时间就频繁用到了这几个库对数据进行操作，

之前用的时候都是根据需求边百度边敲（chao）代码的，

刷了下课程视频后，算是有个稍微系统性的认识和了解了，

整理归纳一下，加强下理解，争取下次能徒手写~

2.需求

需要定期整理国内，国外，境外的疫情数据。

这些excel中都有两个sheet，第一个sheet是每个省的，第二个sheet是每个省所有城市的数据，需要将这些数据都合并起来。

此外，还需要将这些数据进行宽数据转长数据的操作。

excels.png

了解到这个需求后，第一反应就是stata和python都是可以操作的。其中，我要是用起stata来会更加顺手。

但是，一想到用stata来合并的话，stata操作的其实是dta数据，并不是excel数据，而且，虽然stata16允许多个dataframe在内存里跑，但是，总觉得会很慢。

抱着学习和试验的心态吧，就用python来进行批量合并excel，结果效果还是不错的，虽然碰到不少坑，花了些时间，但最后跑出结果也就2-3分钟的，还是挺有成就感的。

大概记录一下主要思路和代码。

3. 思路

总的思路来说，就是：

①先将所有excel的第一个sheet的省的数据纵向合并起来，并插入一列字段“地市”；

②再将所有excel的第二个sheet的城市的数据纵向合并起来；

③将省合并的数据和城市合并后的数据进行合并。

④统一将多列字段的宽数据转为字段名,数值的长数据。

4.过程

①导入库

import os,time,re,openpyxl,xlrd
import pandas as pd
import numpy as np

②路径文件选取

#合并后数据存储路径
newfile_dir='合并后数据\\截止0409'
#获取当前日期
current_date=time.strftime("%Y-%m-%d")
#合并后数据路径名称
sheng_filename=newfile_dir+'\\'+current_date+'_国内（省）.xlsx'
shi_filename=newfile_dir+'\\'+current_date+'_国内（市）.xlsx'

# 原始数据文件路径
file_dir='data\\4.9\\国内'
# 获取原始数据excel文件列表
file_list=os.listdir(file_dir)

获得的excel文件名称列表：

file_list.png

③省份数据合并处理

由于每个excel的字段名并不是十分相同，即使字段名表达的意思相同，但是，在1个excel里可能会多个空格或者少个字，因此，需要先纵向合并后，再看看哪些字段需要进行修改。

合并的思路就是，先将每个excel导入成dataframe，并存到列表中，再用pd.concat将列表中的dataframe进行合并。

#新建列表，用于装dataframe
sheng_list=[]
#对每个excel文件进行循环
for file in file_list:
    #将每个excel文件的路径获取
    file_path=os.path.join(file_dir,file)
    #将每个excel文件读入成dataframe并赋给对象dataframe
    dataframe=pd.read_excel(file_path)
    #获取dataframe的列名列表
    dfcolls=list(dataframe.columns.values)
    #原始字段名（举例），这个需要多次合并后后续不断补充
    oldcolnamels=['累计病亡率(%)','累计确诊率（%）','累计治愈率（%）','累计确诊率 ']
    #新的字段名（举例），这个需要多次合并后后续不断补充
    newcolnamels=['累计病亡率','累计确诊率','累计治愈率','累计确诊率']
    #批量修改字段名，若原始字段名存在某个excel的列名中，则将原始字段名替换成对应的新字段名
    for i in range(len(oldcolnamels)):
        if oldcolnamels[i] in dfcolls:
            dataframe.rename(columns={oldcolnamels[i]:newcolnamels[i]},inplace=True)
            #打印显示，哪些excel进行了列名替换
            print(file,oldcolnamels[i],'替换成',newcolnamels[i])
    #将dataframe添加到列表中
    sheng_list.append(dataframe)

#将列表中的各个省份的dataframe纵向合并
sheng=pd.concat(sheng_list)
# 删除不需要的列
sheng.drop(columns=['累计无症状感染者病例（待删）','Unnamed: 36',], axis=1, inplace=True)

#在第二列添加‘地市’字段，先对列名列表插入元素，再用.reindex整理列
colls=list(sheng.columns.values)
colls.insert(2,'地市')
sheng = sheng.reindex(columns=colls, fill_value='')
#将'省份'列数据复制到'地市'列
sheng['地市']=sheng['省份']
#将合并后的省数据导出excel，不显示索引
sheng.to_excel(sheng_filename,index=False)

④合并市的数据

所有地市的数据都在excel的第二个sheet，但有些excel也没有地市数据。

因此，这里涉及判断是否存在第二个sheet，如果有的话，将第二个sheet的数据读取并合并，而读取合并的过程与合并省的数据的过程一样。

所以，关键点就是判断并获取excel的第二个sheet的数据。

shi_list=[]
for file in file_list:
    file_path=os.path.join(file_dir,file)
    #读取excel，并赋给对象b
    b=xlrd.open_workbook(file_path)
    #获取excel的sheet数目
    sheet_num=len(b.sheets())
    #判断sheet数目是否为2
    if sheet_num==2:
        #若excel存在两个sheet，则读取第二个sheet
        sheetname=b.sheets()[1]
        sheetname=sheetname.name
        #将每个excel的第二个sheet（存在的话）读入并转为dataframe
        dataframe=pd.read_excel(file_path,sheet_name=sheetname)
        #下同上述的省合并数据
        dfcolls=list(dataframe.columns.values)
        oldcolnamels=['累计病亡率(%)','累计确诊率（%）','累计治愈率（%）','累计确诊率 ']
        newcolnamels=['累计病亡率','累计确诊率','累计治愈率','累计确诊率']
        for i in range(len(oldcolnamels)):
            if oldcolnamels[i] in dfcolls:
                dataframe.rename(columns={oldcolnamels[i]:newcolnamels[i]},inplace=True)
                print(file,oldcolnamels[i],'替换成',newcolnamels[i])
        shi_list.append(dataframe)
shi=pd.concat(shi_list)
shi.drop(columns=['地区代码（待删）'], axis=1, inplace=True)
shi.to_excel(shi_filename,index=False)

⑤合并省市数据

将省和市的数据合并起来，过程一样，用pd.concat将两个dataframe纵向合并。

由于合并后的数据太大，电脑带不起来，所以，就每次都要将那些之后日期的数据或者空行删除，

这就涉及到合并后的数据筛选的问题，在这里有个坑，琢磨了好久才发现原因。

两个数据框合并后，相同列名会合并到一起，不同的就单列，这个很好理解，

但是，数据框除了有column索引，还有index索引（理解成行名），

当两个数据框纵向合并的时候，如果没有自定义index索引的话，

index索引就很可能重复，从而之后的操作，如筛选数据上出现问题。

因此，纵向合并数据框后，要再操作的话，最好重新设置一下index索引。

#省市数据合并后excel文件路径
guonei_filename=newfile_dir+'\\'+current_date+'_国内.xlsx'
#将省市数据合并
guonei=pd.concat([sheng,shi])
#删除字段'省份'
guonei.drop(columns=['省份'], axis=1, inplace=True)
#将字段'日期'转为字符串格式
guonei['日期']=guonei['日期'].astype(str)
#！！！重置index索引
guonei=guonei.reset_index(drop=True)
#删除字段'日期'为空的行
guonei=guonei.drop(guonei[guonei['日期']=='NaT'].index)
#删除字段'日期'大于cut_data的行
cut_data='2020-04-09'
guonei=guonei.drop(guonei[guonei['日期'] > cut_date].index)
#将数据导出excel
guonei.to_excel(guonei_filename,index=False)

⑥宽数据变长数据

由于需要把数据写入数据库，因此，需要将数据转为长数据。

长宽数据的形式，用stata的help文件里展示一下，如下图。

长宽数据.png

stata里进行转换，好像还得保证宽数据各个列名前缀相同，

但是，在python里，并不强求，用.melt(id_vars=[''])方式，就可以轻松转换。

#宽数据转成长数据，数据条数就很多了，所以，先选择目标日期的数据
date=['2020-04-08','2020-04-09'] 
guonei=guonei[guonei['日期'].isin(date)]
#转换后excel路径名
guonei_chang_filename=newfile_dir+'\\'+current_date+'_国内(SQL).xlsx'
#！！！宽数据转长数据，保留字段'日期','地市'和'地区代码'，其他字段转为两列，一列字段名为'variable'，另一列为'value'
guonei_chang=guonei.melt(id_vars=['日期','地市','地区代码'])
#删除字段'value'为空值的行
guonei_chang=guonei_chang.dropna(subset=['value'])
#通过拼接字符串生成sql语句
guonei_chang['s1']="INSERT INTO `db`.`nCoV2019` (`NominalTime`, `SpaceName`, `SpaceCode`, `Index`, `DataValue`) VALUES ('"
guonei_chang['s2']="', '"
guonei_chang['s3']="');"
guonei_chang=guonei_chang.fillna('null')
#多列字符串拼接
guonei_chang['sql']=guonei_chang['s1'].str.cat([guonei_chang['日期'],guonei_chang['s2'],
    guonei_chang['地市'],guonei_chang['s2'],guonei_chang['地区代码'].map(lambda x:str(x)),guonei_chang['s2'],guonei_chang['variable'],
    guonei_chang['s2'],guonei_chang['value'].map(lambda x:str(x)),guonei_chang['s3']],sep='')
#删除补充字符串列
guonei_chang.drop(columns=['s1','s2','s3'], axis=1, inplace=True)
#结果导出excel
guonei_chang.to_excel(guonei_chang_filename,index=False,header=False)

至此，就导出以下excel文件，也便于查验。

生成excels.png

5.最后

同理，国外和境外数据思路和过程相同，换一下数据路径和需要修改的列名就行。

之后再整理的话，如果字段没有发生变化，每次跑下代码结果就出来了。

奈何每次都有变化，也不知道会增加什么字段，这部分只能花些时间看看了~