使用pandas拆分合并excel文件

- 日理万妓 2023-02-26 02:19 189阅读 0赞

import pandas as pd
    import os
    
    df_source = pd.read_excel(
        "./pandas/ant-learn-pandas/course_datas/c15_excel_split_merge/crazyant_blog_articles_source.xlsx")
    
    print(df_source.head(5))
    
    # 创建文件夹
    if not os.path.exists("splits_dir"):
        os.mkdir("splits_dir")
    
    res = df_source.index
    print(res)
    
    # 获取行数
    total_row_count = df_source.shape[0]
    print(total_row_count)

### 使用df.iloc方法，将一个大的dataframe，拆分成多个小dataframe ###

# 使用df.iloc方法，将一个大的dataframe，拆分成多个小dataframe
    # 这个大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
    
    # 拆分成多个dataframe
    df_subs = list()
    for idx, user_names in enumerate(user_names):
        # iloc开始索引
        begin = idx*split_size
        # 结束索引
        end = begin+split_size
        # 拆分
        df_sub = df_source.iloc[begin:end]
        df_subs.append((idx, user_names, df_sub))
    
    # 将每个dataframe存入excel
    for idx, user_names, df_sub in df_subs:
        file_name = f"splits_dir/crazyant_blog_articles_{idx}_{user_names}.xlsx"
        df_sub.to_excel(file_name, index=False)

### 使用pd.concat进行df批量合并 ###

# 使用pd.concat进行df批量合并
    # 遍历文件夹，得到要合并的Excel名称列表
    excel_names = list()
    for excel_name in os.listdir("splits_dir"):
        excel_names.append(excel_name)
    
    # 分别读取到dataframe
    df_list = list()
    for excel_name in excel_names:
        excel_path = f"splits_dir/{excel_name}"
        df_split = pd.read_excel(excel_path)
        # 得到username
        username = excel_name.replace("crazyant_blog_articles_", "").replace(".xlsx", "")[2:]
        # 给每一个df添加1列
        df_split["username"] = username
    
        df_list.append(df_split)
    
    # 使用pd.concat进行合并
    df_merged = pd.concat(df_list)
    
    print(df_merged.head(5))
    
    # 将合并后的dataframe输出到excel
    df_merged.to_excel(f"splits_dir/merged_data.xlsx", index=False)