https://odysee.com/@MakClass:1/merge-excel-files-with-python-and-pandas:6?r=5tVg6uo6okaG49AYonwXvMrf5rrC1ooK

假設現有幾個 Excel 表，當中有不同學科的成績。每個檔案中，為同一班同學的資料，但每個 Excel 分別儲存了不同科目成績。現在我們想將同一班同學的各科目成績整合，我們使用 Python 的 Pandas 套件配合當中的 DataFrame Merge 功能來達成。

開發環境： 我們將使用Anaconda 配搭 Jupyter Notebook， 如果你使用其他工具，例如 Spyder、VSCode、PyCharm 等，亦是可以的。

而我示範時所使用的 Anaconda 已安裝好 Pandas。 另外 Jupyter Notebook 於處理表格類數據時，亦能提供較易讀的可視化環境， 故我將使用 Anaconda 配搭 Jupyter Notebook 進行講解。

首先，我準備了五個 Excel 檔案。每一個 Excel 檔案中，有分段成績及科目總成績。

例如，以下是中文.xlsx 的數據，其餘幾個科目結構一樣。 

 我們目標是將這幾個 Excel 檔案中的各科總成績整合成一個檔案。如下： 

 為方便起見，我們準備一個 files 列表，記錄各個檔案名稱，方便等會在程序中讀入。

# A list of .xlsx files to merge
files = [
    "中文", 
    "英文", 
    "數學",
    "物理",
    "化學",
]

注：如果將來想更動態地讀入全部 Excel 檔案，可以考慮使用 glob。

載入第一個檔案

通過 pd.read_excel 可以將 Excel 檔案匯入成 DataFrame，並進一步處理。其中每科的 Excel 都有各自的「總成績」欄，通過 rename 可以將每科總成績欄改名為各科目名稱。

而最終我們只需要學生編號和科目總成績這兩個欄位。所以使用 df[["學生編號", files[0]]] 取得。

df = pd.read_excel(files[0] + ".xlsx")
 
# 將總成績改名為科目名稱，不然所有科目都會叫「總成績」
df = df.rename(columns={
    "總成績": files[0]
})
 
# 我們只取學生編號及原總成績兩欄。
df = df[["學生編號", files[0]]]
df

載入第二個檔案

接著我們載入第二個檔案並做相類似的處理。

# 載入第二個檔案

df2 = pd.read_excel(files[1] + ".xlsx")
df2 = df2.rename(columns={
    "總成績": files[1]
})
df2 = df2[["學生編號", files[1]]]
df2

往後每個檔案的代碼也相若，所以最終我們會使用 for-loop 來實現。但在此之前，我們先手動處理這兩個檔案，成功後才複製至所有檔案。

整合兩個檔案

我們使用 DataFrame 的 merge 功能來整合，當中使用 on 來指明兩個 DataFrame 共同所依據的欄位。由於兩邊數據量相同，都是同一班學生的記錄，所以不用使用 how 來指明 left/right/innter/outer。

# 使用 DataFrame Merge 將 df 和 df2 兩組數據整合
df_result = df.merge(df2, on="學生編號")

合併後，我們得出以下數據。 

合併所有 Excel 檔案

有了上述合併兩個檔案的經驗，我們把 df 併 df2 的 df2 變成輪流讀取各檔案並合併。

for topic in files[1:]:
    df2 = pd.read_excel(topic + ".xlsx")
    df2 = df2.rename(columns={
        "總成績": topic
    })
 
    df2 = df2[["學生編號", topic]]
    df = df.merge(df2, on="學生編號")
    
df = df.round()
df["學生編號"] = df["學生編號"].astype(int)
df = df.set_index("學生編號")
df

得出以下數據集。 

最終代碼

把所有代碼放在一起，得出最終代碼：

import numpy as np
import pandas as pd

files = [ "中文", "英文", "數學", "物理", "化學" ]

# The first file
topic = files[0]
df = pd.read_excel(topic + ".xlsx")

df = df.rename(columns={ "總成績": topic })

df = df[["學生編號", topic]]

# All other files

for topic in files[1:]:
    df2 = pd.read_excel(topic + ".xlsx")
    df2 = df2.rename(columns={
        "總成績": topic
    })

    df2 = df2[["學生編號", topic]]
    df = df.merge(df2, on="學生編號")
    
df = df.round()
df["學生編號"] = df["學生編號"].astype(int)
df = df.set_index("學生編號")
df.to_excel("result.xlsx")

最後放上 YouTube 的講解示範：

— 麥麥寫的 麥誠 Makzan，2022-06-23。