Pandas: slicing a dataframe into multiple sheets of the same spreadsheet

Question

Say I have 3 dictionaries of the same length, which I combine into a unique pandas dataframe. Then I dump said dataframe into an Excel file. Example:

import pandas as pd
from itertools import izip_longest

d1={'a':1,'b':2,'c':3,'d':4,'e':5,'f':6}
d2={'a':1,'b':2,'c':3,'d':4,'e':5,'f':6}
d3={'a':1,'b':2,'c':3,'d':4,'e':5,'f':6}

dict_list=[d1,d2,d3]

stats_matrix=[ tuple('dict{}'.format(i+1) for i in range(len(dict_list))) ] + list( izip_longest(*([ v for k,v in sorted(d.items())] for d in dict_list)) )
stats_matrix.pop(0)

mydf=pd.DataFrame(stats_matrix,index=None)

mydf.columns = ['d1','d2','d3']

writer = pd.ExcelWriter('myfile.xlsx', engine='xlsxwriter')
mydf.to_excel(writer, sheet_name='sole')   
writer.save()  

This code produces an Excel file with a unique sheet:

>Sheet1<

d1   d2   d3 
1    1    1
2    2    2
3    3    3
4    4    4
5    5    5
6    6    6

My question: how can I slice this dataframe in such a way that the resulting Excel file has, say, 3 sheets, in which the headers are repeated and there are two rows of values in each sheet?

EDIT

In the example given here the dicts have 6 elements each. In my real case they have 25000, the index of the dataframe starting from 1. So I want to slice this dataframe into 25 different sub-slices, each of which is dumped into a dedicated Excel sheet of the same main file.

Intended result: one Excel file with multiple sheets. Headers are repeated.

>Sheet1<           >Sheet2<           >Sheet3<

d1   d2   d3       d1   d2   d3       d1   d2   d3 
1    1    1        3    3    3        5    5    5
2    2    2        4    4    4        6    6    6

Answer 1

First prep your dataframe for writing like this:

prepdf = mydf.groupby(mydf.index // 2).apply(lambda df: df.reset_index(drop=True))
prepdf

You can use this function to reset you index instead.

def multiindex_me(df, how_many_groups=3, group_names=None):
    m = np.arange(len(df))
    reset = lambda df: df.reset_index(drop=True)
    new_df = df.groupby(m % how_many_groups).apply(reset)
    if group_names is not None:
        new_df.index.set_levels(group_names, level=0, inplace=True)
    return new_df

Use it like this:

new_df = multiindex_me(mydf)

Or:

new_df = multiindex_me(mydf,
                       how_many_groups=4,
                       group_names=['One', 'Two', 'Three', 'Four'])

Then write each cross section to a different sheet like this:

writer = pd.ExcelWriter('myfile.xlsx')
for sheet in prepdf.index.levels[0]:
    sheet_name = 'super_{}'.format(sheet)
    prepdf.xs(sheet).to_excel(writer, sheet_name)
writer.save()