pandas批量合并excel，拆分excel

# excel拆分多个 excel
import pandas as pd
work_dir = './coursr/'
split_dir=f"{work_dir}/splits"

import os
if not os.path.exists(split_dir):
    os.mkdir(split_dir)

df_source = pd.read_excel(f'{work_dir}/example.xlsx')

total_row_count = df_source.shape[0]  # 查看多少行

user_names = ['zhang','li','xiao','yian','yuan']
split_size = total_row_count//len(user_names)

if total_row_count%len(user_names) !=0:
    split_size +=1

# 拆分
df_subs = []
for idx,user_name in enumerate(user_names):
    begin = idx*split_size
    end = begin + split_size
    df_sub=df_source.iloc[begin:end]
    df_subs.append((idx,user_name,df_sub))

# 每个dataframe写入
for idx,user_name,df_sub in df_subs:
    file_name = f"{split_dir}/write_{idx}_{user_name}.xlsx"
    df_sub.to_excel(file_name,index=False)



# 合并格式相同的小excel表
import os
excel_names = []
for excel_name in os.listdir(split_dir):
    excel_names.append(excel_name)


df_list = []
for excel_name in excel_names:
    #读取每个excel到df
    excel_path = f"{split_dir}/{excel_name}"
    df_split = pd.read_excel(excel_path)
    #得到user_name
    user_name = excel_name.replace('.xlsx','')[2:]
    # 给每个用户添加一列，即用户名字
    df_split['username']=user_name
    df_list.append(df_split)


df_merge = pd.concat(df_list)
df_merge['username'].value_counts()

df_merge.to_excel(f'{work_dir}/合并.xlsx',index=False)