Python自动化办公——实现透视表

谁践踏了优雅 2022-11-27 00:54 261阅读 0赞

大家好呀~今儿给大家分享一个朋友问的问题。数据如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM1Mjk4MQ_size_16_color_FFFFFF_t_70_pic_center]  
他的需求是：  
• 以公司为行索引  
• 时间为列索引  
• 票面金额为值  
• 最终将每月的数据保存到同一 .xls 文件中的不同 sheet中

以图示例需求：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM1Mjk4MQ_size_16_color_FFFFFF_t_70_pic_center 1]  
看完需求之后，如果大家接触的 excel 比较多的话，会很明显的发现使用 excel 的透视表能够快速实现。如下 GIF 所示：  
![在这里插入图片描述][2020081222173565.gif_pic_center]  
所以我也就跟他说，直接用 excel 可能还直截了当些，但是他的回复让我觉得很赞。在工作中，努力并不是重复，而是解决重复。所以今天我就来跟大家分享熟练掌握 Python 的魅力。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM1Mjk4MQ_size_16_color_FFFFFF_t_70_pic_center 2]  
当然，每个人的工作需求都不一样。并不是说，你看了这篇文章就能多牛逼儿，但是仍然希望，把这种释放双手自己动的思想传递给大家。那我们就开始学习叭~

### 开始学习叭 ###

#### 天才第一步，雀氏导入库 ####

我们想要通过 Python 来实现相应的功能，首先要想着能不能借助前辈封装好的库来实现。此处恰好是对接 excel 的表格数据处理、保存。所以我们就需要导入 numpy 、pandas。

import pandas as pd
    import numpy as np

#### 读取excel数据 ####

df = pd.read_excel("合并1.xls")
    df.head()

![在这里插入图片描述][20200812221922302.png_pic_center]

#### 生成透视表 ####

Pandas 可以通过 pivot\_table 方法来生成透视表

df_pivot = pd.pivot_table(df,index='到期日/返售日', columns='额度占用人名称', values='票面金额',aggfunc=np.sum)
    df_pivot.head()

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM1Mjk4MQ_size_16_color_FFFFFF_t_70_pic_center 3]

#### 实现按月取值 ####

如果 到期日/返售日 为时间序列索引的话，那么实际上，想要通过月份去取此月所有的值，就可以直接通过 时间序列索引的切片 来实现。

此时，我们就来查看一下 透视表 的到期日/返售日是否就是时间序列索引类型。

df_pivot.info()

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM1Mjk4MQ_size_16_color_FFFFFF_t_70_pic_center 4]  
我们可以发现，其是整数索引类型，所以接下来我们需要将其 转为时间序列索引。

# 重置索引
    df_pivot = df_pivot.reset_index()
    df_pivot.head()
    
    # 将 到期日/返售日 修改为obejct类型
    df_pivot["到期日/返售日"] = df_pivot["到期日/返售日"].astype("object")
    df_pivot.dtypes
    
    # 转为时间序列类型
    df_pivot["到期日/返售日"] = pd.to_datetime(df_pivot["到期日/返售日"],format="%Y%m%d")
    df_pivot
    
    # 将时间序列设置为索引
    df_pivot.set_index("到期日/返售日",inplace=True)
    df_pivot.info()
    df_pivot

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM1Mjk4MQ_size_16_color_FFFFFF_t_70_pic_center 5]

#### 创建工作簿，将每月数据保存到不同工作表中 ####

此时，如果说我们的目的是将每月的数据保存到不同的工作簿中，那么直接使用 df.to\_excel() 即可。但是，此时的需求是需要保存到同一工作簿的不同工作表中，怎么来实现呢？就可以通过 pd.ExcelWriter() 来实现。

# 创建 工作簿 对象
    writer = pd.ExcelWriter('拆分2.xls')
    
    # 将最开始读出来的 df 数据 保存到 总表 中
    df.to_excel(writer,sheet_name = '总表',index=False)

按照这个思路，我们很流畅的就会认为，通过时间序列索引切片出来的数据直接保存到不同的工作表中

df_pivot["2020-05"].to_excel(writer, sheet_name="2020-05")

但是此时大家会发现这个工作量是巨大的，因为我的数据是从 2020-05月 至 2021-05 月，整整一年呀~那怎么办呢？开动我们的脑筋(喜洋洋上线)

如果我们有一个盒子，里面装着 2020-05,2020-06,2020-07…2021-04,2021-05 所有数据，那我们就可以通过 for 循环，将每一个值取出传入 df\_pivot\[“值”\] 中。

但是，这样如果这样做，数据量是10年甚至更大的时候怎么办？  
• 生成以 月 为频率的时间索引  
• 将其进行日期格式的调整  
• 进行时间序列索引取值  
• 保存至工作表中  
• 切记，保存工作簿

for i in pd.date_range("2020-05","2021-05",freq="MS"):
        df_time = datetime.strftime(i,"%Y-%m")
        df_data = df_pivot[df_time]
        df_data.to_excel(writer,sheet_name=str(df_time))
        
    writer.save()

就这样将它征服，切断了所有重复。如果你需要传参，就自己封装成函数~我的天，我都唱起来了hhhh，如果觉得有收获的话，请给我一个小小的赞把！康桑思密达。也可以关注我的公众号，看更多的技术文章奥！  
![在这里插入图片描述][20200812222422250.gif_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM1Mjk4MQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221124/6a4407ccc9cf44b79b6a0b3c2c1b46e8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM1Mjk4MQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221124/6ab6c06eaace4d528f11ff133b197601.png
[2020081222173565.gif_pic_center]: /images/20221124/2dddbc1179c34346a3803861c9a80316.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM1Mjk4MQ_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221124/542c01f3693e4c83bb67c4bf83e445a4.png
[20200812221922302.png_pic_center]: /images/20221124/7f2d014f73cf452a95daf523e52e4c42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM1Mjk4MQ_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221124/ea7a25f82df3410ab2c2f6734a4f2fd6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM1Mjk4MQ_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221124/93cab72ea7d940629dc3a7c7e15b7e3c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM1Mjk4MQ_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221124/f16abe634ba14e59ace0a6823767379f.png
[20200812222422250.gif_pic_center]: /images/20221124/233fe12ed8724a7fa291f37faeab61d0.png