write a dictionary into an xlsx file

Question

I have a dictionary that looks like this:

{'4F703:00013:02038': {'100.000, Oryza rufipogon x Oryza sativa', '100.000, 
    Oryza rufipogon', '100.000, Oryza sativa', '100.000, 
    Oryza sativa Japonica Group', '100.000, Oryza sativa f. spontanea'}}

And I want to write this dictionary into an xlsx file, so I did this code:

workbook=xlsxwriter.Workbook('demo.xlsx')
worksheet=workbook.add_worksheet()
row=0
col=0
for key in new_dict.keys():
    row += 1
    worksheet.write(row,col,   key)
    for item in new_dict[key]:
        print(item,row,col+1)
        worksheet.write(row,col + 1,item)
        col += 1
    col = 0
workbook.close()

However I get this output:

And I would like to have it this way:

what am I doing wrong?

Answer 1

Using pandas.ExcelWriter greatly simplifies this task:

import pandas as pd
data = {'4F703:00013:02038': {'100.000, Oryza sativa', '100.000, Oryza rufipogon x Oryza sativa', '100.000, Oryza rufipogon', '100.000, Oryza sativa f. spontanea', '100.000, Oryza sativa Japonica Group'}}
new_data = [[a, [i.split(', ') for i in b]] for a, b in data.items()]
with pd.ExcelWriter('spreadsheet.xlsx') as writer:
   df = pd.DataFrame([i for a, [c, *d] in new_data for i in [[a, *c], *[['', *k] for k in d]]])
   df.to_excel(writer, sheet_name='sheet1', startrow=0, index=False)
   writer.save()