Merging multiple dataframes into single sheet top to bottom using loop

Question

My python is rudimentary. What I want it to do is take the first dataframe, search for a unique number and create a new df in the same formatted template, the use the same unique number, search through the second df and create a new df pertinent to that unique number in the specified format, then merge all the looped data one top of each other.

This is the code

#function
def multiple_dfs(df_list, sheets, file_name, spaces):
writer = pd.ExcelWriter(file_name,engine='xlsxwriter')   
row = 0



for i in uniqueIR:
    dftopi = df_out[df_out['Invoice Reference Number'] == i]
    df2 = df_out_fin[df_out_fin['Invoice Reference Number'] == i]
    df3 = df2.drop(columns = ['Invoice Reference Number'])

    for dataframe in df_list:
        dataframe.to_excel(writer,sheet_name=sheets,startrow=row , startcol=0, index = False)   
        row = row + len(dataframe.index) + spaces  
writer.save()

# list of dataframes
dfs = [dftopi,df3]

# run function
multiple_dfs(dfs, 'Validation', 'test1.xlsx', 1)

This is what I want: table output

Take a look at this SO Post https://stackoverflow.com/a/44729807/6361531 ... Look at the by Acctname groupby. — Scott Boston, Dec 05 '18 at 18:08

score 1 · Accepted Answer · answered Dec 06 '18 at 15:48

Figured out a solution if anyone in the future is wonder:

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

dflist = []
for i in uniqueIR:


    dftopi = df_out[df_out['Invoice Reference Number'] == i]
    df2 = df_out_fin[df_out_fin['Invoice Reference Number'] == i]
    df3 = df2.drop(columns = ['Invoice Reference Number'])

    dftopi.to_excel(writer, sheet_name = 'Top Half' + str(i), index = False)    
    df3.to_excel(writer, sheet_name = 'Bottom Half' + str(i), index = False)

    dflist.append(dftopi)
    dflist.append(df3)
writer.save()


def multiple_dfs(df_list, sheets, file_name, spaces):
    writer = pd.ExcelWriter(file_name,engine='xlsxwriter')   
    row = 0

    for dataframe in df_list:
            dataframe.to_excel(writer,sheet_name=sheets,startrow=row , startcol=0, index = False)   
            row = row + len(dataframe.index) + spaces


    writer.save()

multiple_dfs(dflist, 'Validation', 'test4.xlsx', 1)

Merging multiple dataframes into single sheet top to bottom using loop

1 Answers1