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