经常需要汇总多张excel表格,于是有了下面这个小脚本。
import os
import glob
import pandas as pd
# drop duplications according to parameter 'on'
def merge_excel(path, id):
all_data = pd.DataFrame()
for f in glob.glob(path):
print(f)
df = pd.read_excel(f) #header=3 可以设置从第几行开始读取,默认从第0行开始
df_not_null = df.dropna(subset=["目标工作所在地"]) # delete null on some columns
all_data = all_data.append(df_not_null, ignore_index=True)
return all_data.drop_duplicates(id,'last')
if __name__ == '__main__':
source_path = "/Users/sunchenn/sunchenn/mergeExcel/exampleExcels/*.xlsx"
target_path = "/Users/sunchenn/sunchenn/mergeExcel"
df = merge_excel(source_path, "学号")
df.to_excel(os.path.join(target_path,"Total.xlsx"),index=False)