零基础pandas第七天

excel拆分与合并

work_dir="C:\Users\Ziyin\Desktop\Rainfall_data.xlsx"
splits_dir=f"(work_dir)/splits

import os
if not os.path.exists(splits_dir):
    os.mkdir(splits_dir) 
import pandas as pd

df_source=pd.read_excel(f"(work_dir/C:\Users\Ziyin\Desktop\Rainfall_data.xlsx))
df_source.head()
df_source.index
df_source.shape
total_row_count=df.source.shape(0)
total_row_count 

把一个大的excel拆分成多个excel

user_names=["xiaohong","xiaoming""xiaoqiang"]#这个excel分给几个人

split_size=total_row_count//len(user_names)

if total_row_count%len(user_names)!=0:

split_size+=1

spli_size

拆分成多个小的dataframe

df.subs=[]#把拆分出来的存入一个空表中

for idx,user_name in enumerate(user_name)#iloc的开始索引

begin=idx*split_size#iloc的开始索引

end=begin+split_size#iloc的结束索引

df.sub=df_source.iloc[begin:end]#实现df按照iloc拆分

df_subs.append(idx,user_name,df_sub)#将每个字dataframe存入列表

将每个dataframe存入excel

for idx,user_name,df.sub in df.subs:

file_name=f"(splits_dir)/C:\Users\Ziyin\Desktop\Rainfall_data_(idx)_(user_name).xlsx"

df_sub.tp_excel(file_name,index=False)

pandas实现excel的合并

1遍历文件夹，得到要合并的excel名称列表

import os

excel_names=[]

for excel_names in os.listdir(splits_dir):

               excel_names.append(excel_name)

excel_names

2 分别读取到dataframe

df.list=[]

for excel_name in excel_names:#读取每个文件到excel

          excel_path=f"(splits_dir)/(excel_name)"

df.split=pd.read_read(excel_path)

username=excel_name.replace(".xlsx","").replace(".xlsx","")[2:]#得到username

print( excel_name,username)

df_splits["username"]=username#给每个df添加一列

df_list.append(df.splits)

3使用pd.concat进行合并

df.merge=df.concat(df.df_list)

4将合并后的df输出到excel

df.merge.to_excel(f"(work_dir)/xxxxxx,xlsx",index=false)