Python Pandas库批量处理Excel数据

需求

前段时间负责企业法人数据地理编码的处理（根据地址获取坐标），给到的数据是湖北省10几个地市，200多个Excel表格，所有Excel表格的总行数差不多一千万行；地理编码使用现成的软件工具即可完成，地理编码相关的处理不作为本文的介绍重点；现成的工具直接读取Excel文件里的地址即可进行地理编码。

为了减少工作量，在地理编码前需要对Excel表格进行数据处理，包括按照地市合并，根据统一社会信用代码字段进行去重，数据分批导出Excel文件，因为数据量超过Excel文件支持的最大行数；数据格式如下：有13个地市（文件夹），每个地市文件夹里包含多个Excel表格。每个Excel表格里的格式都是一样的（这是使用脚本处理的前提）。

地市文件夹中包含多个Excel文件，这些企业法人数据是安装行业进行划分，一个行业保存到一个Excel文件中。

代码

对Excel表格进行合并，去重，然后分批导出为Excel；代码如下：

#coding=utf8
import os
import math
import pandas as pd
rootdir=r"E:\企业法人数据"
savedir=r"E:\各市合并去重"
for root, dirs, files in os.walk(rootdir):
    for dir in dirs:
        dirpath=os.path.join(root, dir)
        print(dirpath)
        dfs = []
        for root2,dirs2,files2 in os.walk(dirpath):
            for file in files2:
                filepath=os.path.join(root2, file)
                #因为这里的Excel表格不是以第一行为表头，所以这里需要指定表头行数
                df = pd.read_excel(filepath, header=2)
                print(df.columns.tolist())
                dfs.append(df)
                print(filepath,df.shape[0])
        #使用concat函数合并多个DataFrame(excel)
        df_all2 = pd.concat(dfs)
        #使用drop_duplicates函数去重
        df_all=df_all2.drop_duplicates(['统一社会信用代码'])
        Rowcount=df_all.shape[0]
        print(dir,Rowcount)
        if(Rowcount<1000000):
            df_all.to_excel(os.path.join(savedir, dir+"_"+str(Rowcount)+".xlsx"), index=False)
        else:
        #Excel表格保存行数上限为100多万，这里分批导出
            f=math.ceil(Rowcount/1000000.0)
            s=1000000
            for i in range(0,f):
                start=i*s
                end=(i+1)*s
                df=None
                savename=""
                if(end<Rowcount):
                    df=df_all.iloc[start:end,:]
                    savename=dir+str(i)+"_"+str(s)+".xlsx"
                else:
                    df = df_all.iloc[start: , :]
                    savename = dir + str(i) + "_" + str(Rowcount-start) + ".xlsx"
                df.to_excel(os.path.join(savedir, savename), index=False)

 删除空白和按条件筛选

#coding=utf8
import pandas as pd
import os
dirpath=r"E:\完成地址编码"
savedirpath=r"E:\完成地址编码_去除匹配失败的地址"
dflist=[]
for root,dirs,files in os.walk(dirpath):
    for file in files:
        df=pd.read_excel(os.path.join(root,file))
        fileName=file.split('_')[0]

        nullDf=df[(df["Lat"]).isnull()]
        dflist.append(nullDf)

        NotNullDf=df.dropna(subset=["Lat"])
        num=NotNullDf.shape[0]
        NotNullDf.to_excel(os.path.join(savedirpath,fileName+"_"+str(num)+".xlsx"))
nullDfs=pd.concat(dflist)
count=nullDfs.shape[0]
nullDfs.to_excel(os.path.join(savedirpath,"nullAdd_"+str(count)+".xlsx"))

涉及的函数

读取Excel

读取Excel使用函数pd.read_excel（），这个函数可以指定行作为表头；类似读取数据的功能函数还有read_csv,read_json等，如下图。

DataFrame合并

使用concat函数可以实现把多个DataFrame沿纵向或者横向合并；axis=0表示纵向合并（默认），axis=1表示横向合并。例如：

pd.concat(dfs)  #dfs为了DataFrame列表

 

去重

去重使用函数drop_duplicates（），函数的定义如下图；

可以按照特定字段进行去重；默认为整行数据相同才认为一样；例如

 

df_all2.drop_duplicates(['统一社会信用代码'])

 

表示按照字段 统一社会信用代码 进行去重，只有这个字段值一样，才认为这些记录是一样的，只保留一个；默认是保留第一条记录，其他的都删除；inplace参数表示是否修改原来的表，默认为False，表示不修改原来数据，这个函数生成一个新的DataFrame.

分片

按索引切片

使用函数loc和iloc可以实现按照行列索引进行切片；

例如上面代码分批导出DataFrame时，使用例子如下

#下面的代码表示选取行索引由start到end之间的记录的所有列；第一个引号两边的索引表示行索引起止；第二个引号两边表示列索引的起止。

df=df_all.iloc[start:end,:]

按条件切片（筛选）

按条件进行切片，可以使用loc函数或者直接使用DataFrame进行；loc函数使用方法如下：

如上面代码例子，也可以直接使用DataFrame[条件]进行切片。

#筛选获取Lat字段值为空的记录

nullDf=df[(df["Lat"]).isnull()]

删除

DataFrame支持三种删除模式：

drop()：删除指定的记录

dropna()：删除空值记录

drop_duplicates():去重；删掉重复记录

例如上面代码用到的去重和删除空值

df.dropna(subset=["Lat"])  #表示删除Lat字段值为空的记录（行）

DataFrame导出

DataFrame支持导出多种文件格式，如下图所示

总结

pandas库在数据分析中是经常用到的一个Python库，本文只是简单介绍了pandas库常用的几个功能，正确使用Pandas库能够高效地提高我们的工作效率；例如本文的Excel数据处理需求，如果不会python，不会pandas，只能手动去操作Office去把200多个Excel去除表头，合并，去重，还要避免行数超出Excel行数上限等问题，虽然我对Office的操作不熟悉，但是我想手动肯定没有Python代码效率高，而且这次是湖北省的数据要处理，说不准下次就是其他的省份了；只要把代码写好，后面来再多的数据都可以批处理了。

参考

https://pandas.pydata.org/docs/reference/index.html

https://pandas.pydata.org/docs/user_guide/index.html

https://blog.csdn.net/qq_42535601/article/details/86523689

https://www.cnblogs.com/zknublx/p/9623080.html