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)

1

2

3

4

5

6

7

8

我們需要將指定的列向下填充缺失值用于分組聚合，如果直接調用datafream的fillna方法會將整個表所有的列都填充，官方文檔對該方法并沒有出一個只填充指定列的參數。

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

group_columns = ["序號", "姓名", "年齡", "身份證號碼", "家庭住址"] for c in group_columns: df[c].ffill(inplace=True) df.head()

1

2

3

4

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

序號和年齡列由于一開始存在缺失值，導致轉換成浮點數類型，現在已經填充后，我們可以將其轉換回來：

df.序號 = df.序號.astype("int16") df.年齡 = df.年齡.astype("int16")

1

2

注意：int16表示2字節的整數，這么寫是考慮到2字節的整數足夠裝的下年齡，可以節約一點內存，速度也會更快。

根據結果要求，計算一些輔助列：

df.eval("金額匯總=數量*單價", inplace=True) df.藥品 = df.藥品+df.數量.astype(str)+"*"+df.單價.astype(str) df.head()

1

2

3

然后分組聚合：

def join_func(s): return "/".join(s.dropna()) result = df.groupby(group_columns, as_index=False).agg({"疾病名稱": join_func, "藥品": join_func, "金額匯總": "sum"}) result

1

2

3

4

5

數據處理思路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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

數據處理思路3

思路：只填充第一列，其余分組列只取第一項。

import pandas as pd def join_func(s): return s.str.cat(sep='/') df = pd.read_excel('基礎表/靖宇村.xlsx', sheet_name="基礎表") df.序號.ffill(inplace=True) df.年齡.ffill(inplace=True) df = df.astype({"序號": "int16", "年齡": "int16"}, copy=False) df.eval("金額匯總=數量*單價", inplace=True) df.藥品 = df.藥品+df.數量.astype(str)+"*"+df.單價.astype(str) result = df.groupby("序號", as_index=False) \ .agg({"姓名": "first", "年齡": "first", "身份證號碼": "first", "家庭住址": "first", "疾病名稱": join_func, "藥品": join_func, "金額匯總": "sum"}) result

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

將結果寫入模板文件

下面我們將結果寫入到下面的模板文件中：

寫出代碼：

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")

1

2

3

4

5

6

7

8

9

10

結果：

整體處理代碼

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

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}")

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

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

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。