1.背景
这两天脑子修复ing的时候，把mooc上嵩天老师的《python数据分析与展示》刷了一下，
课程主要讲了numpy、pandas和matplotlib库的入门，
正好这段时间就频繁用到了这几个库对数据进行操作，
之前用的时候都是根据需求边百度边敲（chao）代码的，
刷了下课程视频后，算是有个稍微系统性的认识和了解了，
整理归纳一下，加强下理解，争取下次能徒手写~
2.需求
需要定期整理国内，国外，境外的疫情数据。
这些excel中都有两个sheet，第一个sheet是每个省的，第二个sheet是每个省所有城市的数据，需要将这些数据都合并起来。
此外，还需要将这些数据进行宽数据转长数据的操作。
了解到这个需求后，第一反应就是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文件名称列表：
③省份数据合并处理
由于每个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文件里展示一下，如下图。
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文件，也便于查验。
5.最后
同理，国外和境外数据思路和过程相同，换一下数据路径和需要修改的列名就行。
之后再整理的话，如果字段没有发生变化，每次跑下代码结果就出来了。
奈何每次都有变化，也不知道会增加什么字段，这部分只能花些时间看看了~
