Pandas批量拆分Excel与合并Excel
实例演示:
将一个大Excel等份拆成多个Excel 将多个小Excel合并成一个大Excel并标记来源
work_dir= "./course_datas/c15_excel_split_merge"
splits_dir= f"{work_dir}/splits"
import os
if not os. path. exists( splits_dir) :
os. mkdir( splits_dir)
0、读取源Excel到Pandas
import pandas as pd
df_source = pd. read_excel( f"{work_dir}/crazyant_blog_articles_source.xlsx" )
df_source. head( )
id title tags 0 2585 Tensorflow怎样接收变长列表特征 python,tensorflow,特征工程 1 2583 Pandas实现数据的合并concat pandas,python,数据分析 2 2574 Pandas的Index索引有什么用途? pandas,python,数据分析 3 2564 机器学习常用数据集大全 python,机器学习 4 2561 一个数据科学家的修炼路径 数据分析
df_source. index
RangeIndex(start=0, stop=258, step=1)
df_source. shape
(258, 3)
total_row_count = df_source. shape[ 0 ]
total_row_count
258
一、将一个大Excel等份拆成多个Excel
使用df.iloc方法,将一个大的dataframe,拆分成多个小dataframe 将使用dataframe.to_excel保存每个小Excel
1、计算拆分后的每个excel的行数
user_names = [ "xiao_shuai" , "xiao_wang" , "xiao_ming" , "xiao_lei" , "xiao_bo" , "xiao_hong" ]
split_size = total_row_count // len ( user_names)
if total_row_count % len ( user_names) != 0 :
split_size += 1
split_size
43
2、拆分成多个dataframe
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) )
3、将每个datafame存入excel
for idx, user_name, df_sub in df_subs:
file_name = f"{splits_dir}/crazyant_blog_articles_{idx}_{user_name}.xlsx"
df_sub. to_excel( file_name, index= False )
二、合并多个小Excel到一个大Excel
遍历文件夹,得到要合并的Excel文件列表 分别读取到dataframe,给每个df添加一列用于标记来源 使用pd.concat进行df批量合并 将合并后的dataframe输出到excel
1. 遍历文件夹,得到要合并的Excel名称列表
import os
excel_names = [ ]
for excel_name in os. listdir( splits_dir) :
excel_names. append( excel_name)
excel_names
['crazyant_blog_articles_0_xiao_shuai.xlsx',
'crazyant_blog_articles_1_xiao_wang.xlsx',
'crazyant_blog_articles_2_xiao_ming.xlsx',
'crazyant_blog_articles_3_xiao_lei.xlsx',
'crazyant_blog_articles_4_xiao_bo.xlsx',
'crazyant_blog_articles_5_xiao_hong.xlsx']
2. 分别读取到dataframe
df_list = [ ]
for excel_name in excel_names:
excel_path = f"{splits_dir}/{excel_name}"
df_split = pd. read_excel( excel_path)
username = excel_name. replace( "crazyant_blog_articles_" , "" ) . replace( ".xlsx" , "" ) [ 2 : ]
print ( excel_name, username)
df_split[ "username" ] = username
df_list. append( df_split)
crazyant_blog_articles_0_xiao_shuai.xlsx xiao_shuai
crazyant_blog_articles_1_xiao_wang.xlsx xiao_wang
crazyant_blog_articles_2_xiao_ming.xlsx xiao_ming
crazyant_blog_articles_3_xiao_lei.xlsx xiao_lei
crazyant_blog_articles_4_xiao_bo.xlsx xiao_bo
crazyant_blog_articles_5_xiao_hong.xlsx xiao_hong
3. 使用pd.concat进行合并
df_merged = pd. concat( df_list)
df_merged. shape
(258, 4)
df_merged. head( )
id title tags username 0 2585 Tensorflow怎样接收变长列表特征 python,tensorflow,特征工程 xiao_shuai 1 2583 Pandas实现数据的合并concat pandas,python,数据分析 xiao_shuai 2 2574 Pandas的Index索引有什么用途? pandas,python,数据分析 xiao_shuai 3 2564 机器学习常用数据集大全 python,机器学习 xiao_shuai 4 2561 一个数据科学家的修炼路径 数据分析 xiao_shuai
df_merged[ "username" ] . value_counts( )
xiao_hong 43
xiao_bo 43
xiao_shuai 43
xiao_lei 43
xiao_wang 43
xiao_ming 43
Name: username, dtype: int64
4. 将合并后的dataframe输出到excel
df_merged. to_excel( f"{work_dir}/crazyant_blog_articles_merged.xlsx" , index= False )