pythonexport

Python DataFram Export to Excel Overwriting Previous Line


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 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.


Solution

  • 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')