【Pandas 小记】不同场景下的Excel、sheet合并

在日常数据处理分析过程中，大家都会遇到将一个Excel内的多个sheet合并，或者多个Excel的相同sheet合并的情况。手动复制粘贴或者使用VBA都可以实现，但是数据量多时就GG，即使是VBA也会卡住，至从学了Pandas后，都可以搞定。

1，同一个Excel内不同sheet合并

def oexcel_msheet(dirpath):
    file_list = os.listdir(dirpath)
    for file in file_list:
        dfs = []
        for sheet in pd.ExcelFile(dirpath+file).sheet_names:    # 获取sheet
            df = pd.read_excel(dirpath+file,sheet_name=sheet)
            df['学科'] = sheet
            dfs.append(df)
        result = pd.concat(dfs,axis=0,ignore_index=True)   # 列表中sheet垂直拼接    
        with pd.ExcelWriter(dirpath+file,mode='a') as writer:  # 追加到原文件中新的sheet中
            result.to_excel(writer, sheet_name='汇总',index=False)

oexcel_msheet('E:\\data\\class\\')    

将每个学科的学生成绩汇总到一个sheet中。

2，多个Excel相同sheet合并到新文件

def mexcel_ssheet(dirpath,resultfile):
    file_list = os.listdir(dirpath)
    writer = pd.ExcelWriter(result_file,mode='w')   # 使用writer 实现多sheet写入 mode = 'w' 会自动创建文件  
    for sheet in pd.ExcelFile(dirpath+file_list[0]).sheet_names:  # 获取第一个文件的sheet名称
        dfs=[]
        for file in file_list:                     # 获取不同文件的sheet内容
            df = pd.read_excel(dirpath+file,sheet_name=sheet)
            df['class'] = os.path.splitext(file)[0]           # 获取文件名作为class字段值
            dfs.append(df)
        result = pd.concat(dfs,axis=0,ignore_index=True)  # 将不同文件的sheeth合并
        result.to_excel(writer, sheet_name=sheet,index=False)   # 使用writer 实现多sheet写入
    writer.save()     # 写完所有sheet在保存，否则会覆盖
    writer.close()    # 记住关闭    

dirpath = 'E:\\data\\class\\'    
result_file = 'E:\\data\\class\\all.xlsx'  
mexcel_ssheet(dirpath,result_file)

将两个Excel中的相同sheet合并后，将结果写入新文件。
注意：使用ExcelWriter实现多sheet的追加写入，最后保存并关闭。

3，多个Excel多个sheet合并到新文件

def mexcel_msheet(dirpath,resultfile):
    file_list = os.listdir(dirpath)
    file_dfs=[]
    writer = pd.ExcelWriter(resultfile,mode='w')
    for file in file_list:
        dfs = []
        sheets = pd.ExcelFile(dirpath+file).sheet_names
        for sheet in sheets:
            df = pd.read_excel(dirpath+file,sheet_name=sheet)
            dfs.append(df)
        concat_result = pd.concat(dfs,axis=1,keys=sheets)   # 文件中的sheet水平合并,用sheetname作为分层索引的一级索引
        file_dfs.append(concat_result)  
    file_results = pd.concat(file_dfs,axis=0,ignore_index=True)  # 不同文件中合并的结果进行垂直合并,相同的columns会并
    print(file_results)
    
    
    #file_results.columns.levels[0] = ['语文','数学','英语','物理']
    for key in file_results.columns.levels[0]:     #  根据一级索引，获取各个sheet合并的结果
        result=file_results[key].dropna(how='all')    # 删除整行都为NAN的数据
        result.to_excel(writer, sheet_name=key,index=False)   # 将结果追加写入新文件
    writer.save()     # 写完所有sheet在保存，否则会覆盖
    writer.close()    # 记住关闭
    
dirpath = 'E:\\data\\class\\'    
result_file = 'E:\\data\\class\\all.xlsx' 
mexcel_msheet(dirpath,result_file)

class1，包含语文，数学，英语三个sheet，字段都为‘学号’，‘姓名’，‘分数’，其中语文多了一列‘等级’。

class2，包含语文，数学，物理三个sheet,字段都为‘学号’，‘姓名’，‘分数’

合并后的all
语文，class2的学生没有等级

数学，两个class合并在一起

物理，只有class2的记录
英语，只有class1的记录

4，将多个Excel按不同sheet合并到同个文件中

# 将多个Excel按不同sheet合并到同个文件中
def merge_excel(dirpath,resultfile):
    file_list = os.listdir(dirpath)
    writer = pd.ExcelWriter(result_file,mode='w')   # 使用writer 实现多sheet写入 mode = 'w' 会自动创建文件
    for file in file_list:
        df = pd.read_excel(dirpath+file)
        sheet = os.path.splitext(file)[0]
        df.to_excel(writer, sheet_name=sheet,index=False)   # 使用writer 实现多sheet写入
    writer.save()     # 写完所有sheet在保存，否则会覆盖
    writer.close()    # 记住关闭  
    
dirpath = 'E:\\data\\class\\'    
result_file = 'E:\\data\\class\\all.xlsx'  
merge_excel(dirpath,result_file)

将每个班级的语文成绩合并到同个文件，

四种场景都合并如上所述，除此之外还可以在加入其它复杂转换逻辑进去，比如第三种的场景结果中字段顺序乱了，可以做调整。如果有更优解或有错误，欢迎留言交流