pandas练习：多个Excel 按照索引匹配 数据覆盖

笔记：

import numpy as np
import pandas as pd

# .columns.tolist() : 返回列名数组（如果列名不在同一行，即换行，则说明下面一行的列名在数据里面）【正常情况下即使数据再多，列名都在同一行，都不会换行】
# index_col=0 : 作为索引的列会从.columns.tolist()中消失，即该列名换行成为数据的一部分
homework = pd.read_excel('hw2.xlsx', skiprows=[0, 1], usecols=[1, 9], names=['学号', '分数'], index_col=0)
# totalHomework不能取两列(usecols)，如果取两列，最后保存的时候TotalScore.xlsx也只有两列（多一列序号）
totalHomework = pd.read_excel('TotalScore.xlsx')
res = pd.merge(homework, totalHomework, on='学号')
totalHomework['作业2'] = res['分数']
totalHomework.to_excel('TotalScore.xlsx')

完整代码：

import numpy as np
import pandas as pd
hws = ['hw1.xlsx', 'hw2.xlsx', 'hw3.xlsx', 'hw4.xlsx', 'hw5.xlsx', 'hw6.xlsx', 'hw7.xlsx', 'hw8.xlsx', 'hw9.xlsx','hw11.xlsx', 'hw12.xlsx', 'hw13.xlsx', 'FinalReport.xlsx']
totalHomework = pd.read_excel('TotalScore.xlsx')

for i, hw in enumerate(hws):
    homework = pd.read_excel(hw, skiprows=[0, 1], usecols=[1, 9], names=['学号', '分数'])
    res = pd.merge(totalHomework[['学号']], homework, on=['学号'], how='left')
    "左连接后存在空值,空值用-1代替"
    res[['分数']] = res['分数'].where(res['分数'].notnull(), -1)
    if i != len(hws) - 1:
        totalHomework[['作业' + str(i + 1)]] = res[['分数']]
    else:
        totalHomework[['期末成绩(必填)']] = res[['分数']]
    totalHomework.to_excel('TotalScore.xlsx')