python - 将字典写入 xlsx 文件

Question

我有一本看起来像这样的字典：

{'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'}}

我想把这个字典写成一个 xlsx 文件，所以我做了这个代码：

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()

但是我得到这个输出：

我想这样：

我究竟做错了什么？

标签： python

Answer 1

使用pandas.ExcelWriter大大简化了这个任务：

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()