Bdtjtk

I have a dataframe based on this data:

np.random.seed(1111)

df = pd.DataFrame({

'Category':np.random.choice( ['Group A','Group B'], 10000),

'Sub-Category':np.random.choice( ['X','Y','Z'], 10000),

'Sub-Category-2':np.random.choice( ['G','F','I'], 10000),

'Product':np.random.choice( ['Product 1','Product 2','Product 3'], 10000),

'Units_Sold':np.random.randint(1,100, size=(10000)),

'Dollars_Sold':np.random.randint(100,1000, size=10000),

'Customer':np.random.choice(pd.util.testing.rands_array(10,25,dtype='str'),10000),

'Date':np.random.choice( pd.date_range('1/1/2016','12/31/2018',  

                      freq='D'), 10000)})

I then create a groupby to format my data as I'd like:

sales = df.groupby([df.Date.dt.month,'Customer','Product'])['Units_Sold','Dollars_Sold'].sum()

I'd like to iterate through this groupby to write each 'Customer' to it's own Excel workbook with the file being saved as 'Customer'.xlsx. Inside each workbook for each customer, I'd like to have each 'Date' (aka Month) write to it's own worksheet.

Here is an example of a loop I'm currently using to write each customer to it's own sheet:

idx = pd.IndexSlice

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

for c in sales.index.get_level_values(1).unique():

    sales.loc[idx[:,c,:],idx[:]]

    .to_excel(writer, sheet_name=c)

writer.save()

I have found a way to write each to it's own sheet inside a workbook, but can't seem to put it all together. Thanks for your help!

This worked for me. I essentially reset the index on the sales groupby object to get a regular dataframe. Take the unique list of customers from the customer column. Iterate over the list of customers, making a dataframe for each customer.

Group that by the date, then iterate over the groupby object using d, s where d = date, and s = sales.

Use string formatting to enter the customer id as the workbook name, and use str(d) as the sheet name. Use pandas to_excel to write the sheets within the workbook and save the finished workbook at the end of each iteration of the loop.

import pandas as pd

import numpy as np



np.random.seed(1111)

df = pd.DataFrame({

'Category':np.random.choice( ['Group A','Group B'], 10000),

'Sub-Category':np.random.choice( ['X','Y','Z'], 10000),

'Sub-Category-2':np.random.choice( ['G','F','I'], 10000),

'Product':np.random.choice( ['Product 1','Product 2','Product 3'], 10000),

'Units_Sold':np.random.randint(1,100, size=(10000)),

'Dollars_Sold':np.random.randint(100,1000, size=10000),

'Customer':np.random.choice(pd.util.testing.rands_array(10,25,dtype='str'),10000),

'Date':np.random.choice( pd.date_range('1/1/2016','12/31/2018',  

                      freq='D'), 10000)})



sales = df.groupby([df.Date.dt.month,'Customer','Product'])['Units_Sold','Dollars_Sold'].sum().reset_index()



customers = sales['Customer'].unique()



for customer in customers:

    writer = pd.ExcelWriter('{}.xlsx'.format(customer), engine='xlsxwriter')

    temp = sales[sales['Customer'] == customer]

    temp = temp.drop(columns='Customer')

    temp = temp.groupby('Date')

    for d, s in temp:

        s.to_excel(writer, sheet_name=str(d), index=False)

    writer.save()