DataFrame – Split Workbooks

Python allows you to load Excel files data into a DataFrame as shown in the Dataframe Input & Output article. However, the DataFrame is stored in the machine memory and can’t be viewed in a workspace library with several tables like with a SQL IDE or a SAS IDE.

Island – Photo taken by me

Imagine that you have a data table with all products of a supermarket, et chaque produit appartiSuppose you have a data table with all the products in a store. Each product belongs to a category. The categories are beverages, dairy, nonfood, personal care.

You need to load this database as a DataFrame and slice the DataFrame with one file per category. You can do it also with VBA, but we will see how to split the DataFrame with Python and save each table of products in an Excel workbook. You need to have the possibility to call one of these tables and use it as a specific DataFrame for reprocessing.

The purpose is to load the DataFrame, keep the list of all categories, split data by product category, and store each DataFrame separately. You will store each DataFrame in a dictionary using the product category for the key. You will use a for loop to extract each DataFrame in a separate Excel workbook.

The code below shows you how to slice a Dataframe into several Dataframes stored in a dictionary and extract them in an excel workbook. The code contains two functions as seen in the DataFrame – Input & Output article to create an Excel workbook and fill the workbook with a Datafame.The main procedure if name == ‘main‘ run the code. To run this Python script you need to import the following packages, pandas for the DataFrame manipulation and openpyxl for the Excel process.

import pandas as pd
import openpyxl as op

def create_workbook_sheets(name_file_to_save, path_file_to_save):
    Procedure to create and save the final Excel workbook and sheets
    : param name_file_to_save: name of the file to save for the workbook
    : param path_file_to_save: the path to save the file

    # create a new excel workbook
    wb = op.Workbook()
    wb.title = name_file_to_save
    sh_data =
    sh_data.title = "Results"

    # zoom & gridlines
    sh_data.sheet_view.zoomScale = 80
    sh_data.sheet_view.showGridLines = True

    # save the workbook

def fill_data_workbook(path_file_to_save, df_data):
    Procedure to fill workbook sheet with DataFrame.
    :param path_file_to_save : name of the file to save
    :param df_data : the DataFrame to write

    # write DataFrame on an Excel file
    wb_result = op.load_workbook(path_file_to_save)
    with pd.ExcelWriter(path_file_to_save, engine="openpyxl", mode="a") as writer: = wb_result
        # ExcelWriter uses writer.sheets to access sheets of the workbook
        writer.sheets = dict((ws.title, ws) for ws in
        df_data.to_excel(writer, "Results", startrow=0, startcol=0, index=False)

    # initialisation sheet
    sh_result = wb_result["Results"]
    sh_result.row_dimensions[1].height = 90

    # save workbook

if __name__ == '__main__':

    # initialisation input file
    path_file_database = "/Users/Name/Documents/Python/Slicing DataFrame/List_products.xlsx"

    # initialisation dataframe
    print("--> Step : Loading DataFrame")
    df_data = pd.read_excel(path_file_database, sheet_name="DATA", index_col=None)

    # list distinct categories
    list_categ = df_data["Category"].unique()

    # slicing DataFrame in multiple DataFrame stored in a dictionary
    dict_df = {elem: pd.DataFrame for elem in list_categ}

    for key in dict_df.keys():
        dict_df[key] = df_data[:][df_data.Category == key]

    # export each dataframe to a new workbook
    print("--> Step : Export Result to Excel")
    path_folder_to_save = "/Users/Name/Documents/Python/Slicing DataFrame/"

    for key in dict_df.keys():
        name_file_to_save = key
        path_file_to_save = path_folder_to_save + name_file_to_save + ".xlsx"
        create_workbook_sheets(name_file_to_save, path_file_to_save)
        fill_data_workbook(path_file_to_save, dict_df[key])

Running the script, you finally get the following file in your folder.