Pandas案例精进 | 续集：自动分割汇总表写入到子表

r囧r小猫 2022-11-04 14:22 104阅读 0赞

![e55af700feef8f2da37555987907cab0.png][]

大家好！

欢迎来到[**「Pandas案例精进」专栏**][Pandas]，点击橙字查看全部

前文回顾：[Pandas案例精进 | 自动分割汇总表写入到子表中][Pandas_ _]

本文是承接上一篇的实战案例，没看过的小伙伴建议先点击????上方链接查看前文

## Pandas案例需求 ##

![212675d082770029c77015cfa3758502.png][]

现在的新需求跟之前的区别有：

汇总表多了级别字段，需要根据不同的级别对应不同的文件夹：

![34aa2f2a35725a7510a9b9e055aee259.png][]

所属区的xlsx文件有时可能是xls，并不一定是xlsx的。

各级别文件夹中存在一些不能匹配汇总表的垃圾文件需要删除。

汇总表中所有的对应项目并不是都在级别文件夹中存在，不存在的只提示哪些不存在，无需额外处理。

下面是我的实现过程：

## 数据加载 ##

import os
    import pandas as pd
    
    excel_dir = os.getcwd()
    
    data = pd.read_excel(f"{excel_dir}/汇总.xlsx", sheet_name='明细')
    data

本文直接设置`excel_dir`为工作目录，大家如果代码和数据不在同一文件夹，可以根据自己的情况更改设置文件路径。

![33fb058928a608df883d8da13c64a947.png][]

## 遍历计算出每级别所涉及的区 ##

level_areas = {}
    for i, row in data.iterrows():
        areas = level_areas.setdefault(row['级别'], set())
        areas.add(row['所属区'])
    level_areas

执行结果

{'一级': {'B区'}, '二级': {'A区', 'C区'}, '三级': {'D区', 'E区'}}

## 删除多余文件+转换xls ##

这里会用到`win32com.client`库，需要额外安装。

pip install pywin32

也可使用国内清华源来加快Python库的安装速度。

pip install pywin32 -i https://pypi.tuna.tsinghua.edu.cn/simple

安装后使用

import win32com.client as win32
    
    for level in data['级别'].unique():
        areas = level_areas[level]
        files = os.listdir(f"{excel_dir}/{level}")
        for file in files:
            tag = file.replace(".xlsx", "").replace(".xls", "")
            filename = f"{excel_dir}/{level}/{file}"
            if not tag in areas:
                print(f"删除文件：{filename}")
                os.remove(filename)
            elif file.endswith(".xls"):
                print(f"将 {filename} 转换为 {filename}x")
                excel = win32.gencache.EnsureDispatch('Excel.Application')
                try:
                    wb = excel.Workbooks.Open(filename)
                    wb.SaveAs(f"{filename}x", FileFormat=51)  #FileFormat = 51 is for .xlsx extension
                    print("转换成功")
                finally:
                    wb.Close()  #FileFormat = 56 is for .xls extension
                excel.Application.Quit()
                os.remove(filename)

上述代码可以实现删除级别文件夹多余的文件并将xls转换为xlsx。

比如将 `一级/B区.xls` 转换为 `一级/B区.xlsx`

> 注意：使用pywin32转换excel文件格式时，绝对路径的盘符后面的分隔符必须是反斜杠\\，后面的路径分隔符用正斜杠或反斜杠都可以。

## 遍历出级别和区域 ##

for (level, area), df in data.groupby(['级别', '所属区']):
        print(level, area)
        df = df.iloc[:, 2:]
        display(df)

执行结果

![37cd98bad0207663c5519deef561f994.png][]

## 写出结果 ##

最后，利用`openpyxl`库将结果分别写入各级别、各Excel文件中。

from openpyxl import load_workbook
    
    for (level, area), df in data.groupby(['级别', '所属区']):
        print(level, area)
        df = df.iloc[:, 2:]
        out_file_name = f"{excel_dir}/{level}/{area}.xlsx"
        if not os.path.exists(out_file_name):
            print(out_file_name, "文件不存在，跳过")
            continue
        print("准备写出到：", out_file_name)
        workbook = load_workbook(filename=out_file_name)
        sheet = workbook.active
        # 先删除第4行之后的旧数据，预计1000行完全够用
        sheet.delete_rows(idx=4, amount=1000)
        # 然后再进行添加数据
        for row in df.values.tolist():
            sheet.append(row)
            print(row)
        print(f"保存到{out_file_name}文件中")
        workbook.save(filename=out_file_name)
        workbook.close()

![c9b4c1613fcbcba0223c531b5ec161be.png][]

## 小结 ##

估计有朋友觉得本文为了处理几个Excel写了这么多行代码，也没看出来有啥效果。

但是上面只是示例数据，而群友真正工作要处理的数据有几百个文件夹，用代码处理的优势一下子就体现出来了。

不如看看反馈吧：

![e6ca2f40676085b11b27a8b027ab7408.png][]

如果大家喜欢我们的文章，就给右下角点个赞????吧~

### 本文代码和案例数据 ###

如果大家想自己练手，可以通过以下步骤获取代码及案例数据????

![f62a0764342a193b27821cf68410ebb8.png][]

扫描下方二维码????添加我的微信（朱小五mini）为好友，然后回复**关键词**“**20210229**”，关键词是前面的红色数字，建议长按复制！

![600a84e5292ed1c4bcffca2a0c1abdf4.png][]

![084dbcc65d571ac117b7b9a80d7e216a.png][]

最后，还有「凹凸数据」的荐书环节！

介绍一本????《**程序员数学从零开始**》书从人们身边常见的整数讲起，逐步深入，介绍了数论、计数、图论、机器学习等领域的一些典型算法及其原理，尤其是算法背后的数学原理，可以让读者对这些算法有更深入的理解。点击下图可看详情/购买！????

![10d763e72d59c0e0724ed6cb9005e080.png][]

感谢北京大学出版社的赞助与支持！**300币????**限时兑换！

[e55af700feef8f2da37555987907cab0.png]: /images/20221023/b2fd055910a4447485c93c406b250421.png
[Pandas]: https://mp.weixin.qq.com/mp/appmsgalbum?__biz=MzU5Nzg5ODQ3NQ%3D%3D&action=getalbum&album_id=1726442370151317508#wechat_redirect
[Pandas_ _]: https://mp.weixin.qq.com/s?__biz=MzU5Nzg5ODQ3NQ%3D%3D&idx=2&mid=2247508022&scene=21&sn=13772d9297e137b79b90a9a8271090d0#wechat_redirect
[212675d082770029c77015cfa3758502.png]: /images/20221023/34fdc66491b643f58b4bc794172d9847.png
[34aa2f2a35725a7510a9b9e055aee259.png]: /images/20221023/0fa6b195761e4e248e51085df8f2f234.png
[33fb058928a608df883d8da13c64a947.png]: /images/20221023/4c2990eaa3b34dbda75be8732ddecc63.png
[37cd98bad0207663c5519deef561f994.png]: /images/20221023/81b10cbb9ef641aca2bfc88dacbec03e.png
[c9b4c1613fcbcba0223c531b5ec161be.png]: /images/20221023/b6cddc632b714a4e84461ab933e38d6c.png
[e6ca2f40676085b11b27a8b027ab7408.png]: /images/20221023/02c4b635422d466fadd9187bc276c801.png
[f62a0764342a193b27821cf68410ebb8.png]: /images/20221023/5542c478851e43b78ab4cd9479131b41.png
[600a84e5292ed1c4bcffca2a0c1abdf4.png]: /images/20221023/58b9c0c19d2345edbba9cdd43b6ac116.png
[084dbcc65d571ac117b7b9a80d7e216a.png]: /images/20221023/f58b706af88e417da431a727bf33c028.png
[10d763e72d59c0e0724ed6cb9005e080.png]: /images/20221023/db3f7dec2bf945039cd4c729bff7059f.png