数据分析Python

[Python] 自动化办公 筛选Excel中的行并形成新表

2020-07-15  本文已影响0人  半为花间酒

转载请注明:陈熹 chenx6542@foxmail.com (简书号:半为花间酒)
若公众号内转载请联系公众号:早起Python

本例可以学到的知识点: openpyxl模块的运用和glob模块建立批处理
数据源:阿里云天池的电商婴儿数据(可自行搜索并下载,如果要完成进阶难度可直接将该数据Excel拷贝999次即可,当然这个拷贝可以交给代码来实现)

需求描述

  1. 初级难度 —— 提取 电商婴儿数据.xlsx 中购买数 buy_mount超过50的记录建立新的Excel表

最后形成如下的表格:

  1. 进阶难度 —— 同一个文件夹下有1000份 电商婴儿数据 的Excel表格(命名为 电商婴儿数据1.xlsx,电商婴儿数据2.xlsx至电商婴儿数据100.xlsx),需要提取所有表格中购买数 buy_mount超过50的记录并汇总至一个新的Excel表

让我们先完成初级难度的需求挑战

# 打开已经存在的Excel用load_workbook,创建新的Excel用Workbook
from openpyxl import load_workbook, Workbook

# 数据所在的文件夹目录
path = 'C:/Users/xxxxxx'

# 打开电商婴儿数据工作簿
workbook = load_workbook(path + '/' + '电商婴儿数据.xlsx')
# 打开工作表
sheet = workbook.active
buy_mount = sheet['F']
row_lst = []
for cell in buy_mount:
    if isinstance(cell.value, int) and cell.value > 50:
        print(cell.row)
        row_lst.append(cell.row)

这一步本质上就是对购买数的各个单元格进行判断,如果数值超过50就将其行号放入一个空列表中,间接完成了筛出符合条件的行。注意这一列有可能有的单元格cell的值value不是数值类型,因此需要用isinstance()进行判断,当然也可以将单元格的值先用int()转为整型再判断

筛选出符合条件的行号就可以提取行并且放入新的Excel中了,因此需要先创建新的工作簿

new_workbook = Workbook()
new_sheet = new_workbook.active

# 创建和 电商婴儿数据 一样的表头(第一行)
header = sheet[1]
header_lst = []
for cell in header:
    header_lst.append(cell.value)
new_sheet.append(header_lst)

# 从旧表中根据行号提取符合条件的行,并遍历单元格获取值,以列表形式写入新表
for row in row_lst:
    data_lst = []
    for cell in sheet[row]:
        data_lst.append(cell.value)
    new_sheet.append(data_lst)

# 最后切记保存
new_workbook.save(path + '/' + '符合筛选条件的新表.xlsx')

初级难度的需求已经成功完成,至此我们已经学会从单个表中提取需要的行并且放到新的表格里

有的人可能会说了:一个表格的筛选可以直接用Excel中的 筛选 来完成,不需要用代码写这么复杂,还难以理解。因此就有了进阶需求。现在需要完成的工作变成,获取1000个表格中所有符合条件的行并汇总成一个新表。如果是手动操作的行,需要打开每个表格,然后一通筛选操作后,将所有满足条件的行都复制到新表,并且执行上述操作1000

这显然不现实

而,如果你已经理解初级需求的思路,那么只需要加上几行代码,就可以完成进阶需求。所需要的模块是glob

批处理的大概代码框架如下:

import glob

# 1000份数据所在的文件夹目录
path = 'C:/Users/xxxxxx'

for file in glob.glob(path + '/*'):
    pass

如果需要遍历特定类型的文件可以限定后缀,以xlsx后缀的Excel文件为例

for file in glob.glob(path + '/*.xlsx'):
    pass

最后只需要将上面写好的针对单个文件的代码放到循环体内部,且load_workbook的路径变成循环出的每一个xlsx文件的绝对路径

当然,还需要想清楚有些代码不能在循环体里重复被执行,如创建新表和给新表添加表头,创建新表放在循环体外就可以,添加表头可以用一个单独的变量来判断这个操作是否已经被执行

完整代码如下:

from openpyxl import load_workbook, Workbook
import glob

path = 'C:/Users/xxxxxx'
new_workbook = Workbook()
new_sheet = new_workbook.active

# 用flag变量明确新表是否已经添加了表头,只要添加过一次就无须重复再添加
flag = 0

for file in glob.glob(path + '/*.xlsx'):
    workbook = load_workbook(file)
    sheet = workbook.active
    
    buy_mount = sheet['F']
    row_lst = []
    for cell in buy_mount:
        if isinstance(cell.value, int) and cell.value > 50:
            print(cell.row)
            row_lst.append(cell.row)
    
    if not flag:
        header = sheet[1]
        header_lst = []
        for cell in header:
            header_lst.append(cell.value)
        new_sheet.append(header_lst)
        flag = 1
    
    for row in row_lst:
        data_lst = []
        for cell in sheet[row]:
            data_lst.append(cell.value)
        new_sheet.append(data_lst)

new_workbook.save(path + '/' + '符合筛选条件的新表.xlsx')
上一篇 下一篇

猜你喜欢

热点阅读