# 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)
pandas批量合并excel,拆分excel
最新推荐文章于 2024-07-20 15:39:26 发布