How to dump a dictionary into an .xlsx file with proper column alignment?
Solution 1
This is what I think should help:
import xlsxwriter
workbook = xlsxwriter.Workbook('myfile.xlsx')
worksheet = workbook.add_worksheet()
row = 0
col = 0
order=sorted(d.keys())
for key in order:
row += 1
worksheet.write(row, col, key)
i =1
for item in d[key]:
worksheet.write(row, col + i, item)
i += 1
workbook.close()
IN:
d={'10071353':(0, 0),'06030011':(6, 0),'06030016':(2, 10)
OUT:
A B C
06030001 6 0
06030002 2 10
06030003 0 0
Solution 2
The best way is to use pandas
for this task. Some documentation is available here (http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_excel.html )
import pandas as pd
a = {'1':[1,2,3,4], '2':[5,6,7,8]}
a = pd.DataFrame(a)
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
a.to_excel(writer, sheet_name='Sheet1')
writer.save()
You may need to install xlsxwriter
package
Solution 3
I think you just misplaced a variable.
worksheet.write(row, col + 1, item)
row += 1
this is wrong, row +=1
should be replaced with col +=1
This is correct way, You can use the same variable.
import xlsxwriter
workbook = xlsxwriter.Workbook('myfile.xlsx')
worksheet = workbook.add_worksheet()
row = 0
col = 0
order=sorted(d.keys())
for key in order:
row += 1
print(key)
worksheet.write(row, col, key)
for item in d[key]:
print(item,row, col+1)
worksheet.write(row, col + 1, item)
col += 1
col = 0
workbook.close()
Output:
Related videos on Youtube
FaCoffee
NHL Winnipeg Jets fan - GO JETS GO! Getting a lot of valuable help here. For DOWN VOTERS: if you are going to down vote, tell the recipient why you are doing so - this way he/she can actually make improvements and gain confidence. Improductive critics should be banned.
Updated on June 04, 2022Comments
-
FaCoffee almost 2 years
I have a dictionary with 2000 items which looks like this:
d = {'10071353': (0, 0), '06030011': (6, 0), '06030016': (2, 10), ...}
Given that I want to write it to an
.xlsx
file, I use this code (taken from here):import xlsxwriter workbook = xlsxwriter.Workbook('myfile.xlsx') worksheet = workbook.add_worksheet() row = 0 col = 0 order=sorted(d.keys()) for key in order: row += 1 worksheet.write(row, col, key) for item in d[key]: worksheet.write(row, col + 1, item) row += 1 workbook.close()
This produces an
.xlsx
file with the following alignment:A B 06030001 0 10 06030002 10 10 06030003 5 10
However, this is the alignment I am after:
A B C 06030001 0 10 06030002 10 10 06030003 5 10
What should I change in the script to achieve this?