how to create header in excel from a python dictionary keys

Question

I am trying to take a dictionary in python and place it into an excel worksheet where the keys are displayed in the header section of the sheet and the values are in to columns. I am close I am just missing something small and cannot figure it out here is my code. Caution I use way to many imports

import os
import re
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
import xlsxwriter
import pprint
from openpyxl.workbook import Workbook
from openpyxl.worksheet.copier import WorksheetCopy


workbook = xlsxwriter.Workbook('dicExcel.xlsx')
worksheet = workbook.add_worksheet()

d = {'a':['Alpha','Bata','Gamma'], 'b':['1','2','3'], 'c':['1.0','2.0','3.0']}
row = 0
col = 1

for key in d.keys():
row += 1
worksheet.write(row, col, key)
for item in d[key]:
    worksheet.write(row, col + 1, item)
    row += 1

workbook.close()

LeopoldVonBuschLight · Accepted Answer · 2017-07-19 21:33:24Z

1

I think this is what you are trying to do:

import xlsxwriter

workbook = xlsxwriter.Workbook('dicExcel.xlsx')
worksheet = workbook.add_worksheet()

d = {'a':['Alpha','Bata','Gamma'], 'b':['1','2','3'], 'c':['1.0','2.0','3.0']}
row = 0
col = 0

for key in d.keys():
    row = 0
    worksheet.write(row, col, key)
    row += 1
    for item in d[key]:
        worksheet.write(row, col, item)
        row += 1
    col += 1

workbook.close()

This puts the data in this format:

a       c      b
Alpha   1.0    1
Bata    2.0    2
Gamma   3.0    3

Is this what you wanted?

answered Jul 19, 2017 at 21:33

LeopoldVonBuschLight

9115 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

C. Nelson Over a year ago

Exactly and thank you for taking the time to answer my question. Have a great day.

Rakesh Adhikesavan · Accepted Answer · 2017-07-19 21:37:12Z

0

One option is to use the pandas package, you won't need too many imports.

import pandas as pd

d = {'a':['Alpha','Bata','Gamma'], 'b':['1','2','3'], 'c':['1.0','2.0','3.0']}

df = pd.DataFrame(d)

df will look like this:

       a  b    c
0  Alpha  1  1.0
1   Bata  2  2.0
2  Gamma  3  3.0

To write the DataFrame back in to an Excel file:

df.to_excel("Path to write Excel File + File Name")

edited Jul 19, 2017 at 21:37

answered Jul 19, 2017 at 21:30

Rakesh Adhikesavan

13k18 gold badges58 silver badges78 bronze badges

Collectives™ on Stack Overflow

how to create header in excel from a python dictionary keys

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related