今天嘗試請GPT以Python處理三個檔案的資料比對與合併，但付費版GPT給出的代碼卻無法順利執行。於是我轉向Gemini，以相同的提問詞詢問，卻意外發現Gemini所提供的代碼相當精確且更具效率。以我自己目前對Python淺顯的理解，也能看出Gemini所提供的程式碼相對高階且較有優化，邏輯清晰、架構明確。後來我回頭請GPT針對這兩段代碼進行比較，GPT也同意Gemini的方案更具效率與優勢，這點讓我感覺到GPT在程式生成的能力上仍有一些進步空間，期待GPT未來能持續提升品質，更精確地滿足使用者的需求。
以下是詢問GPT兩者差異回覆的內容
| 功能/邏輯點 | 你提供的版本(gemini) | 我的版本(GPT) |
|---|---|---|
| 檔案讀取 | 使用 try-except 包裝，有錯誤處理 |
沒有包裝，若檔案不存在會直接錯誤 |
| 資料前處理 | 明確轉換 datetime，並加註說明 |
有轉換，但說明較簡略 |
| 合併邏輯 | A→B→C 三步驟清晰，欄位更有註解 | 相同邏輯，但直接寫死欄位選擇，較精簡 |
| 欄位名稱檢查 | 附有對欄位格式的說明與假設 | 假設用戶已知並匹配正確欄位 |
| 欄位過濾與排序 | 明確指定 output_columns，有邏輯順序 |
相同，但未顯示中文註解 |
| 錯誤處理 | 包含 read 與 to_excel 的錯誤處理 |
無錯誤處理（預設成功） |
| 彈性設計 | 有封裝為函式、支援參數設定 | 一次性執行程式，不適合模組化 |
| Dummy 資料產生 | 有產生模擬資料供測試 | 無產生測試資料，需用戶上傳 |
以下代碼沒給予真實的路徑時仍然可以用代碼中的例子產出，相當不錯的方式
import pandas as pd
def reconcile_procurement_data(file_a_path, file_b_path, file_c_path, output_excel_path):
"""
Reconciles procurement, goods receipt, and un-storaged receipt data.
Args:
file_a_path (str): Path to the "請採購明細" (Purchase Request Details) Excel file.
file_b_path (str): Path to the "進料收貨入庫明細" (Goods Received and Stored Details) Excel file.
file_c_path (str): Path to the "已收貨未入庫明細" (Goods Received Not Yet Stored Details) Excel file.
output_excel_path (str): Path to save the reconciled Excel file.
"""
# --- 1. Load Data ---
try:
df_a = pd.read_excel(file_a_path)
df_b = pd.read_excel(file_b_path)
df_c = pd.read_excel(file_c_path)
except FileNotFoundError as e:
print(f"Error: One of the input files was not found. Please check the paths. {e}")
return
except Exception as e:
print(f"An error occurred while reading the Excel files: {e}")
return
# Standardize column names for easier merging (adjust these if your actual column names differ)
# File A (請採購明細) expected columns: '請購部門', '請購單號', '採購單號', '料號', '採購日期'
# File B (進料收貨入庫明細) expected columns: '採購單號', '料號', '收貨日期', '收貨單號', '異動單號', '異動項次', '數量', '送貨單號', '檢驗日期', '檢驗結果'
# File C (已收貨未入庫明細) expected columns: '異動單號', '異動項次', '異動日期'
# Ensure date columns are in datetime format for calculations
df_a['採購日期'] = pd.to_datetime(df_a['採購日期'])
df_b['收貨日期'] = pd.to_datetime(df_b['收貨日期'])
df_c['異動日期'] = pd.to_datetime(df_c['異動日期'])
# --- 2. Step 1: Match A with B ---
# Merge df_a and df_b on '採購單號' and '料號'
# We use a left merge to keep all records from df_a and add matching info from df_b.
# Since one purchase order can have multiple receipts, this will correctly result in
# multiple rows for a single purchase order if it has multiple receipts.
merged_ab = pd.merge(
df_a[['請購部門', '請購單號', '採購單號', '料號', '採購日期']],
df_b[['採購單號', '料號', '收貨日期', '收貨單號', '異動單號', '異動項次', '數量', '送貨單號', '檢驗日期', '檢驗結果']],
on=['採購單號', '料號'],
how='left'
)
# --- 3. Step 2: Match with C ---
# Merge the result from step 1 with df_c on '異動單號' and '異動項次'
final_merged_df = pd.merge(
merged_ab,
df_c[['異動單號', '異動項次', '異動日期']],
on=['異動單號', '異動項次'],
how='left'
)
# --- 4. Step 3: Filter by Date Difference ---
# Calculate the difference in days between '收貨日期' and '採購日期'
final_merged_df['日期差異'] = (final_merged_df['收貨日期'] - final_merged_df['採購日期']).dt.days
# Filter for records where the difference is greater than 7 days
discrepancy_df = final_merged_df[abs(final_merged_df['日期差異']) > 7].copy() # Using abs to catch both earlier and later receipts
# --- 5. Export to Excel ---
# Select the required columns for export
output_columns = [
'請購部門', '請購單號', '採購單號', '料號', '採購日期',
'收貨日期', '收貨單號', '異動單號', '異動項次', '數量',
'送貨單號', '檢驗日期', '檢驗結果', '異動日期', '日期差異'
]
discrepancy_df = discrepancy_df[output_columns]
try:
discrepancy_df.to_excel(output_excel_path, index=False)
print(f"Successfully exported {len(discrepancy_df)} records to '{output_excel_path}' where the date difference is more than 7 days.")
except Exception as e:
print(f"An error occurred while writing the Excel file: {e}")
# --- Usage Example ---
if __name__ == "__main__":
# Create dummy data for demonstration (replace with your actual file paths)
# Ensure these dummy files reflect the actual column names you're using.
# For simplicity, I'm assuming English column names in the dummy data and mapping them
# to the Chinese names expected by the function. You should use the exact Chinese
# column names in your actual Excel files.
# Dummy File A: 請採購明細
data_a = {
'請購部門': ['RD', 'Sales', 'HR', 'RD', 'Sales'],
'請購單號': ['REQ001', 'REQ002', 'REQ003', 'REQ004', 'REQ005'],
'採購單號': ['PO001', 'PO002', 'PO003', 'PO004', 'PO001'],
'料號': ['ITEM001', 'ITEM002', 'ITEM003', 'ITEM001', 'ITEM002'],
'採購日期': ['2025-01-01', '2025-01-05', '2025-01-10', '2025-02-01', '2025-01-05']
}
df_a_dummy = pd.DataFrame(data_a)
df_a_dummy.to_excel("請採購明細.xlsx", index=False)
# Dummy File B: 進料收貨入庫明細
data_b = {
'採購單號': ['PO001', 'PO002', 'PO003', 'PO004', 'PO001', 'PO002'],
'料號': ['ITEM001', 'ITEM002', 'ITEM003', 'ITEM001', 'ITEM001', 'ITEM002'],
'收貨日期': ['2025-01-07', '2025-01-15', '2025-01-12', '2025-02-15', '2025-01-20', '2025-01-10'], # PO001/ITEM001 has two receipts, one within 7 days, one outside
'收貨單號': ['RCV001', 'RCV002', 'RCV003', 'RCV004', 'RCV005', 'RCV006'],
'異動單號': ['TRN001', 'TRN002', 'TRN003', 'TRN004', 'TRN005', 'TRN006'],
'異動項次': [1, 1, 1, 1, 2, 2],
'數量': [10, 5, 20, 8, 12, 6],
'送貨單號': ['DLV001', 'DLV002', 'DLV003', 'DLV004', 'DLV005', 'DLV006'],
'檢驗日期': ['2025-01-07', '2025-01-15', '2025-01-12', '2025-02-15', '2025-01-20', '2025-01-10'],
'檢驗結果': ['OK', 'OK', 'OK', 'NG', 'OK', 'OK']
}
df_b_dummy = pd.DataFrame(data_b)
df_b_dummy.to_excel("進料收貨入庫明細.xlsx", index=False)
# Dummy File C: 已收貨未入庫明細
data_c = {
'異動單號': ['TRN001', 'TRN002', 'TRN003', 'TRN004', 'TRN005', 'TRN006'],
'異動項次': [1, 1, 1, 1, 2, 2],
'異動日期': ['2025-01-08', '2025-01-16', '2025-01-13', '2025-02-16', '2025-01-21', '2025-01-11']
}
df_c_dummy = pd.DataFrame(data_c)
df_c_dummy.to_excel("已收貨未入庫明細.xlsx", index=False)
# Define your file paths
file_a = "請採購明細.xlsx"
file_b = "進料收貨入庫明細.xlsx"
file_c = "已收貨未入庫明細.xlsx"
output_file = "採購收貨日期差異報告.xlsx"
# Run the reconciliation
reconcile_procurement_data(file_a, file_b, file_c, output_file)
沒有留言:
張貼留言