Pandas實體|藥品發放匯總與excel表資料回填

作者：小小明

需求

有一個衛生院需要統計一下每個村扶貧藥品發放的資料，

資料形式是在一個檔案夾下，每個村的資料都存盤在一個獨立的excel檔案中，需要將每個村的資料進行匯總，匯總形式如下：

資料處理流程

首先讀取該檔案夾下的其中一個檔案進行測驗：

from pathlib import Path
import pandas as pd

for 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方法會將整個表所有的列都填充，官方檔案對該方法并沒有出一個只填充指定列的引數，

所以我采用以下方法對指定的列進行填充：

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 pd


def 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_workbook

book = load_workbook("合計模板.xlsx")
sheet = book["合計表"]
length = result.shape[0]
data = result.values
for i, row in enumerate(sheet[f"A4:H{length+3}"]):
    for j, cell in enumerate(row):
        cell.value = data[i, j]
book.save("結果表/靖宇村.xlsx")

結果：

整體處理代碼

已經全部測驗完成，下面整理一下完整代碼：

import os
from pathlib import Path
import pandas as pd

from openpyxl import load_workbook
import copy

if 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 = result.values
    for i, row in enumerate(sheet[f"A4:H{length+3}"]):
        for j, cell in enumerate(row):
            cell.value = data[i, j]
    book.save(f"結果表/{name.name}")

執行后，已經順利得到每個村對應的匯總結果，