SPB-DB 資料清洗流程與欄位對應文件

版本: 2026-05-27 | 系統: spb-db ETL System
資料庫: PostgreSQL 17 + pgvector | 清洗引擎: Python + Polars

一、原始檔案與處理流程總覽

data/raw/
├── breeding_record.xlsx  ──→ load_breeding.py  ──→ breeding_events (46,166 筆)
├── current_sows.xlsx     ──→ load_sows.py      ──→ sow_master (2,973 筆)
├── farrowing_data.xlsx   ──→ load_farrowing.py ──→ farrowing_events (20,355 筆)
└── boar_usage.xlsx       ──→ load_boar.py      ──→ boar_master (3,810 筆)
                                    │
                                    ▼
                            data_quality.py (自動修正)
                                    │
                                    ▼
                            compute_metrics.py (認證統計)

處理步驟摘要

步驟	操作	說明
1. 讀取	`pl.read_excel()`	Polars 讀取指定 sheet，跳過標題列
2. 選欄	`df.select([pl.nth(i)])`	只保留對應欄位，捨棄公式/空白欄
3. 重新命名	`df.rename(rename)`	Excel 欄位名 → Python 內部欄位名
4. 過濾空值	`filter(ear_tag.is_not_null())`	移除無耳刻的空白行
5. 型別轉換	`cast(Int32/Float32/String)`	統一字串/數值型別
6. 格式正規化	`str.strip_chars()`	去除前後空白
7. 特殊轉換	status map / unpivot / ear_tag split	業務邏輯轉換
8. 寫入 DB	`INSERT INTO ... ON CONFLICT`	寫入 PostgreSQL

二、breeding_record.xlsx → breeding_events

檔案資訊

Sheet: 104配種紀錄
原始行數: 20,435 行（跳過 3 列標題後）
產出筆數: 46,166 筆（寬表 unpivot 展開後）

關鍵轉換：寬表 → 窄表 (Unpivot)

原始 Excel 每一列是一頭母豬的完整配種歷史（多次配種在同一列），
透過 unpivot 展開為每筆配種事件獨立一列。

原始寬表（一列多筆配種）:
母豬耳號 | 品種 | ai1_date | ai2_date | ai3_date | ...
──────────────────────────────────────────────────
0590-05   | D    | 2014-01  | 2014-05  | NULL     | ...

unpivot 窄表（每筆配種獨立）:
母豬耳號 | 品種 | ai_sequence | ai_date  | ...
───────────────────────────────────────────
0590-05   | D    | 1           | 2014-01  |
0590-05   | D    | 2           | 2014-05  |

Excel → 資料表欄位對應

Excel Col	Python 欄位	DB 欄位	型別	轉換
0	piglet_breed	— (中間欄位)	—	—
1	sow_breed	sow_breed	VARCHAR	strip
2	sow_ear_tag	sow_ear_tag	VARCHAR	strip, 過濾空值
5	pen	pen	VARCHAR	strip
7	boar_breed	boar_breed	VARCHAR	strip
8	boar_ear_tag	boar_ear_tag	VARCHAR	strip
12	parity	parity	INTEGER	—
13	sow_status	sow_status_code	INTEGER	A→1,B→2,C→3,D→4
14	wean_date	wean_date	VARCHAR	—
16	heat_date	heat_date	VARCHAR	—
18	staff_code	staff_code	VARCHAR	strip
19	ai1_date	ai_date	VARCHAR	unpivot
20	ai1_time	ai_time	VARCHAR	unpivot
22	ai2_date	ai_date	VARCHAR	unpivot
25	ai3_date	ai_date	VARCHAR	unpivot
31	preg_test_date	preg_test_date	VARCHAR	—
32	preg_test_result	preg_test_result	VARCHAR	—
33	notes	notes	TEXT	strip
38	wean_to_heat	wean_to_heat	FLOAT	—
—	ai_sequence	ai_sequence	VARCHAR	unpivot 時產生 "1"/"2"/"3"
—	reflex_score	reflex_score	FLOAT	`+++`→1.0, `++`→0.5, `+`→0.1

跳過的 Excel 欄位（公式/空白）

Col 3-4, 6, 9-11, 35-37: Excel 公式計算區，不匯入資料庫。

三、current_sows.xlsx → sow_master

檔案資訊

Sheet: 109-11504
原始行數: 跳過 2 列標題
產出筆數: 2,973 筆

關鍵轉換：從 notes 欄位提取淘汰資訊

notes 欄位原始內容: "2015.03.15 汰, 腳痛"
                       ↓ regex 提取
cull_date_str: "2015.03.15"
cull_reason:   "腳痛"

Excel → 資料表欄位對應

Excel Col	Python 欄位	DB 欄位	型別	轉換
0	ear_tag_tai_ci	ear_tag_tai_ci	VARCHAR	strip
1	ear_tag_year	ear_tag_year	VARCHAR	—
2	pen	pen	VARCHAR	strip
3	breed	breed	VARCHAR	strip
4	sex	sex	VARCHAR	—
5	purpose	purpose	VARCHAR	—
6	teat_count	teat_count	VARCHAR	—
7	ear_tag	ear_tag	VARCHAR	strip, 過濾空值
8	birth_date	birth_date	VARCHAR	—
9	parity	parity	INTEGER	—
10	reg_number	reg_number	VARCHAR	strip
11	name	name	VARCHAR	strip
12	sire_name	sire_name	VARCHAR	—
13	sire_reg	sire_reg	VARCHAR	—
14	dam_name	dam_name	VARCHAR	—
15	dam_reg	dam_reg	VARCHAR	—
16	stress_gene	stress_gene	VARCHAR	strip
17	meat_gene	meat_gene	VARCHAR	strip
18	prolific_gene	prolific_gene	VARCHAR	strip
19	notes	notes	TEXT	strip
—	cull_date_str	cull_date_str	VARCHAR	regex 提取
—	cull_reason	cull_reason	VARCHAR	regex 提取

四、farrowing_data.xlsx → farrowing_events

檔案資訊

Sheet: 原始資料103-107配種
原始行數: 20,355 行（跳過 1 列標題）
產出筆數: 20,355 筆

Excel → 資料表欄位對應

Excel Col	Python 欄位	DB 欄位	型別	轉換
0	piglet_breed	piglet_breed	VARCHAR	strip
1	sow_breed	sow_breed	VARCHAR	strip
2	sow_ear_tag	sow_ear_tag	VARCHAR	strip, 過濾空值
3	sow_reg	—	—	中間欄位
4	sow_birth	—	—	中間欄位
5	sow_status	sow_status_code	INTEGER	A/B/C/D→1/2/3/4
6	boar_breed	boar_breed	VARCHAR	strip
7	boar_ear_tag	boar_ear_tag	VARCHAR	strip
8	boar_reg	—	—	中間欄位
9	boar_birth	—	—	中間欄位
10	name	—	—	中間欄位
11	breed_date	breed_date	VARCHAR	—
12	staff_code	staff_code	VARCHAR	strip
13	parity	parity	INTEGER	—
14	pen	pen	VARCHAR	strip
15	due_date	due_date	VARCHAR	—
17	notes_raw	notes	TEXT	rename→notes
19	farrow_date	farrow_date	VARCHAR	—
20	alive_male	alive_male	INTEGER	—
21	alive_female	alive_female	INTEGER	—
22	mummy	mummy	INTEGER	—
23	stillborn_male	stillborn_male	INTEGER	—
24	stillborn_female	stillborn_female	INTEGER	—

跳過的中間欄位

Col 3-4, 8-10, 16, 18: 母豬/公豬登記號、出生日期、驗證欄位，僅用於來源檔案內部比對。

五、boar_usage.xlsx → boar_master

檔案資訊

兩個 Sheet 合併處理：
完檢資料: 檢定站公豬資料（性狀測定值）
原始資料: 場內公豬使用記錄（育種價、系譜）
產出筆數: 3,810 筆（合併去重後）

關鍵轉換：耳刻拆分

原始耳刻: "D0083-06" 或 "Y8164"
            ↓ split_ear_tag()
boar_breed:  "D" (品種前綴)
boar_ear_tag: "0083-06" (耳號主體)

識別順序: ["D","L","Y","YL","LD","LY","DL"] → 單字母

Sheet 1: 完檢資料 → 欄位對應

Excel Col	Python 欄位	DB 欄位	型別	轉換
2	breed	breed	VARCHAR	strip
3	ear_tag_raw	ear_tag_raw	VARCHAR	→ 拆分為 breed+ear_tag
5	sex	sex	VARCHAR	strip
6	sire_ear_tag	sire_ear_tag	VARCHAR	strip
8	dam_ear_tag	dam_ear_tag	VARCHAR	strip
10	birth_date	birth_date	VARCHAR	—
11	inspect_date	inspect_date	VARCHAR	—
14	weight_date	weight_date	VARCHAR	—
15	weight	weight	FLOAT	—
18	bf2	bf2	FLOAT	—
20	weight_date_act	weight_date_act	VARCHAR	—
22	a110	a110	FLOAT	—
30	bf_110	bf_110	FLOAT	—
49	loin_depth	loin_depth	FLOAT	—
50	loin_area	loin_area	FLOAT	—

Sheet 2: 原始資料 → 欄位對應

Excel Col	Python 欄位	DB 欄位	型別	轉換
4	pen	pen	VARCHAR	strip
5	ear_tag_raw	ear_tag_raw	VARCHAR	→ 拆分
8	name	name	VARCHAR	strip
10	birth_date	birth_date	VARCHAR	—
13	reg_number	reg_number	VARCHAR	strip
14	a110	a110	FLOAT	—
15	bf_110	bf_110	FLOAT	—
16	adg	adg	FLOAT	—
17	loin_area	loin_area	FLOAT	—
18	fe	fe	FLOAT	—
19	index_score	index_score	FLOAT	—
20	source	source_type	VARCHAR	—
21	genotype	genotype	VARCHAR	strip
24	sire_ear_tag	sire_ear_tag	VARCHAR	strip
28	dam_ear_tag	dam_ear_tag	VARCHAR	strip

六、已知資料品質問題

問題	影響範圍	自動修正	狀態
母豬耳刻重複（308 組）	JOIN 膨脹假警報	`DISTINCT ON`	✅
品種不一致（2,739 筆）	統計偏差	以 sow_master.breed 為準	🔶 待確認
孤兒公豬（12 筆）	配種記錄無法追溯	—	🔶 人工判斷
分娩無配種（68 筆）	跨週期疑慮	—	🔶 人工判斷
關鍵欄位空值（12 筆）	無法定位	標記不修正	🔶
品種 = "0"（髒資料）	統計混入無效群組	SQL 過濾 `!= '0'`	✅
ai_date 型別為 VARCHAR	TO_CHAR 呼叫失敗	SP 指示用 LEFT/SUBSTRING	✅
耳刻含品種前綴	無法直接關聯	split_ear_tag()	✅

七、ETL 執行方式

# 單檔執行
cd /opt/spb-db && source .venv/bin/activate
python3 flows/extract/load_breeding.py [檔案路徑]

# 完整管線（含品質修正 + 統計重算）
python3 flows/etl_pipeline.py