使用Python + Pandas 合并多个科目Excel 成绩档案

Makzan
·
·
IPFS
·
假设现有几个Excel 表,当中有不同学科的成绩。每个档案中,为同一班同学的资料,但每个Excel 分别储存了不同科目成绩。现在我们想将同一班同学的各科目成绩整合,我们使用Python 的Pandas 套件配合当中的DataFrame Merge 功能来达成。

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。




CC BY-NC-ND 2.0 授权

喜欢我的作品吗?别忘了给予支持与赞赏,让我知道在创作的路上有你陪伴,一起延续这份热忱!

logbook icon
Makzan我管理世界職業技能競賽之網站技術項目、舉辦本地設計與開發賽事、開課分享技術心得。一個用網頁來表達自己的作家。
  • 来自作者
  • 相关推荐

期望與放下

甚麼是世界職業技能競賽

【最後兩天】買《所謂「我不投資」,就是 all in 在法定貨幣》親身體驗擁有 NFT