import pandas as pd
import os
#储存数据的文件夹地址
file_d = r'D:\492140\Desktop\DP'
#生成一个空DataFrame用于存储读取的数据
data = pd.DataFrame()
#导入数据、合并数据
for files in os.walk(file_d):
for file in files[2]: #files[2]母文件夹(files[0])及子文件夹(files[1])内所有文件
data_r = pd.read_excel(file_d + '\\' + file, index_col=0)
data = pd.concat([data, data_r], axis = 0)
#重置索引
data = data.reset_index()
#生成账户类型列表
type_ = list(set(data['账号类型']))
#存储excel表明及地址
excelName = file_d + '\\' + '{}.xlsx'.format(data.iloc[0,8].split(' ')[0])
writer = pd.ExcelWriter(excelName)
#数据按sheet名字写入excel
for i in range(len(type_)):
data_t = data[data['账号类型']==type_[i]]
data_t.to_excel(writer,sheet_name=type_[i],index = False)
#保存
writer.save()
writer.close()