Write multiple dataframes into a single flat text file to input into Pandas

Question

I have an excel file with multiple sheets that I convert into a dictionary of dataframes where the key represents the sheet's name:

xl = pd.ExcelFile(r"D:\Python Code\PerformanceTable.xlsx")
pacdict = { i : pd.read_excel(xl, i) for c, i in enumerate(xl.sheet_names, 1)}

I would like to replace this input Excel file with a flat text file -- but would still like to end up with the same outcome of a dictionary of dataframes.

Any suggestions on how I might be able to format the text file so it still contains data for multiple, named tables/sheets and can be read into the above format? Preferrably still making Pandas' built-in functionality do the heavy lifting.

Possible duplicate of [Is it possible to write and read multiple DataFrames to/from one single file?](https://stackoverflow.com/questions/45860317/is-it-possible-to-write-and-read-multiple-dataframes-to-from-one-single-file) — ivan_pozdeev, Oct 07 '19 at 22:54

score 0 · Answer 1 · answered Oct 07 '19 at 18:01

Loop through each sheet. Create a new column called "sheet_source". Concatenate the sheet dataframes to a master dataframe. Lastly export to CSV file.

# create a master dataframe to store the sheets
df_master = pd.DataFrame()

# loop through each dict key
for each_df_key in pacdict.keys():

    # dataframe for each sheet
    sheet_df = pacdict[each_df_key]

    # add column for sheet name
    sheet_df['sheet_source'] = each_df_key

    # concatenate each sheet to the master
    df_master = pd.concat([df_master, sheet_df])

# after the for-loop, export the master dataframe to CSV
df_master.to_csv('new_dataframe.csv', index=False)

Write multiple dataframes into a single flat text file to input into Pandas

1 Answers1