python|Pandas实例|药品发放汇总与excel表数据回填 excel|数据处理

作者：小小明

需求有一个卫生院需要统计一下每个村扶贫药品发放的数据。
数据形式是在一个文件夹下，每个村的数据都存储在一个独立的excel文件中，需要将每个村的数据进行汇总，汇总形式如下：

文章图片

数据处理流程首先读取该文件夹下的其中一个文件进行测试：

from pathlib import Path import pandas as pdfor name in Path(r"F:\jupyter\test\药品数据汇总\基础表").glob("[!~]*.xls*"): filename = str(name.absolute()) df = pd.read_excel(filename, sheet_name="基础表") break df.head(10)

文章图片

我们需要将指定的列向下填充缺失值用于分组聚合，如果直接调用datafream的fillna方法会将整个表所有的列都填充，官方文档对该方法并没有出一个只填充指定列的参数。
【python|Pandas实例|药品发放汇总与excel表数据回填】所以我采用以下方法对指定的列进行填充：

group_columns = ["序号", "姓名", "年龄", "身份证号码", "家庭住址"] for c in group_columns: df[c].ffill(inplace=True) df.head()

文章图片

注意：ffill方法等价于fillna(method=‘ffill’)

序号和年龄列由于一开始存在缺失值，导致转换成浮点数类型，现在已经填充后，我们可以将其转换回来：

df.序号 = df.序号.astype("int16") df.年龄 = df.年龄.astype("int16")

注意：int16表示2字节的整数，这么写是考虑到2字节的整数足够装的下年龄，可以节约一点内存，速度也会更快。

根据结果要求，计算一些辅助列：

df.eval("金额汇总=数量*单价", inplace=True) df.药品 = df.药品+df.数量.astype(str)+"*"+df.单价.astype(str) df.head()

文章图片

然后分组聚合：

def join_func(s): return "/".join(s.dropna())result = df.groupby(group_columns, as_index=False).agg({ "疾病名称": join_func, "药品": join_func, "金额汇总": "sum"}) result

文章图片

数据处理思路2 区别在于，前面的代码的思路是，先填充指定列，聚合时去空值再拼接。这里的思路是一次性全部填充，聚合时去除重复值再拼接。
完整代码：

import pandas as pddef join_func(s): return s.drop_duplicates().str.cat(sep='/')df = pd.read_excel('基础表/靖宇村.xlsx', sheet_name="基础表") df.ffill(inplace=True) df = df.astype({ "序号": "int16", "年龄": "int16"}, copy=False) df.eval("金额汇总=数量*单价", inplace=True) df.药品 = df.药品+df.数量.astype(str)+"*"+df.单价.astype(str) group_columns = ["序号", "姓名", "年龄", "身份证号码", "家庭住址"] result = df.groupby(group_columns, as_index=False) \ .agg({ "疾病名称": join_func, "药品": join_func, "金额汇总": "sum"}) result

文章图片

将结果写入模板文件下面我们将结果写入到下面的模板文件中：

文章图片

写出代码：

from openpyxl import load_workbookbook = load_workbook("合计模板.xlsx") sheet = book["合计表"] length = result.shape[0] data = https://www.it610.com/article/result.values for i, row in enumerate(sheet[f"A4:H{length+3}"]): for j, cell in enumerate(row): cell.value = https://www.it610.com/article/data[i, j] book.save("结果表/靖宇村.xlsx")

结果：

文章图片

整体处理代码已经全部测试完成，下面整理一下完整代码：

import os from pathlib import Path import pandas as pdfrom openpyxl import load_workbook import copyif not os.path.exists("结果表"): os.mkdir("结果表")def join_func(s): return "/".join(s.dropna())group_columns = ["序号", "姓名", "年龄", "身份证号码", "家庭住址"] for name in Path("基础表").glob("[!~]*.xls*"): book = load_workbook("合计模板.xlsx") sheet = book["合计表"] filename = str(name.absolute()) df = pd.read_excel(filename, sheet_name="基础表") df.ffill(inplace=True) df = df.astype({ "序号": "int16", "年龄": "int16"}, copy=False) df.eval("金额汇总=数量*单价", inplace=True) df.药品 = df.药品+df.数量.astype(str)+"*"+df.单价.astype(str) group_columns = ["序号", "姓名", "年龄", "身份证号码", "家庭住址"] result = df.groupby(group_columns, as_index=False) \ .agg({ "疾病名称": join_func, "药品": join_func, "金额汇总": "sum"}) length = result.shape[0] data = https://www.it610.com/article/result.values for i, row in enumerate(sheet[f"A4:H{length+3}"]): for j, cell in enumerate(row): cell.value = https://www.it610.com/article/data[i, j] book.save(f"结果表/{name.name}")

执行后，已经顺利得到每个村对应的汇总结果。