Python DataFram Export to Excel Overwriting Previous Line

Question

I am trying to export to Excel and it keeps overwriting the information so I only get the last line. I've gone through all of the similar posts here and all the solutions haven't worked.

The code is relatively simple; I am calling a function to create a dictionary with sub-dictionaries for a word count. That all works fine. The problem is exporting it to Excel.

Files = FileList()
for file in Files:
    results = {file:(KeySearch(file,b))}
    data = pd.DataFrame.from_dict(results, orient = 'index')
    print(data)
    data.to_excel('GFG.xlsx')

The data prints fine, and the Excel file is correctly formatted. The problem is it is only the last line.

I understand that it's being overwritten but I can't figure out how to get around it.

• I already tried removing the indent at data (and several permutations of this)

• I already tried putting it in a separate dictionary.

• I already tried putting it in a list and appending it; this kind of worked, but each appendix included all 3 rows in each and so was duplicating.

I know I'm probably missing something dumb, but I really have gone over every solution I've come across and none of them seem to work. Any help would be greatly appreciated.

Answer 1

Each time you call data.to_excel('GFG.xlsx'), the file is recreated.

You can simply append each result to the DataFrame first e.g. all_data = pd.concat([all_data, data]). Then, move the last line out of the iteration.

for file in Files:
    results = {file: KeySearch(file, b)}
    data = pd.DataFrame.from_dict(results, orient='index')
    all_data = pd.concat([all_data, data])

all_data.to_excel('GFG.xlsx')