Python dictionary to columns in xlsx

If you are using Python3.6+, dictionaries are ordered. You can read more about the specifics at Are dictionaries ordered in Python3.6+.

If you satisfy these versions, you can use xlsxwriter like this:

import xlsxwriter

d = {'66': 74, '62': 32, '69': 18}

# Create an new Excel file and add a worksheet.
with xlsxwriter.Workbook('demo.xlsx') as workbook:

    # Add worksheet
    worksheet = workbook.add_worksheet()

    # Write headers
    worksheet.write(0, 0, 'Start')
    worksheet.write(0, 1, 'Quanitity')

    # Write dict data
    for i, (k, v) in enumerate(d.items(), start=1):
        worksheet.write(i, 0, k)
        worksheet.write(i, 1, v)

Otherwise, store you data in an ordered sequence, such as a list of tuples, and do the same thing:

import xlsxwriter

d = [('66', 74), ('62', 32), ('69', 18)]

# Create an new Excel file and add a worksheet.
with xlsxwriter.Workbook('demo.xlsx') as workbook:

    # Add worksheet
    worksheet = workbook.add_worksheet()

    # Write headers
    worksheet.write(0, 0, 'Start')
    worksheet.write(0, 1, 'Quanitity')

    # Write list data
    for i, (k, v) in enumerate(d, start=1):
        worksheet.write(i, 0, k)
        worksheet.write(i, 1, v)

This approach is safer if you want your code to work for all python versions.

demo.xlsx:

How about this?

import pandas as pd

keys = my_dict.keys()
values = my_dict.values()

Build data frame in pandas and then convert it to 'csv':

df = pd.DataFrame({"Start": keys, "Quantity": values})
df.to_csv("fname.csv")

Or, if preferred, directly as 'xlsx':

df.to_excel("fname.xlsx")   

You can do this neatly using pandas, just change the path to where you want to save your excel sheet. However, the order of the key:value pairs is not preserved when parsing dictionaries, which might be an issue for you.

import pandas as pd

mydict= {'66': 74, '62': 32, '69': 18, '72': 14, '64': 37, '192': 60, '51': 70, '46': 42, '129': 7, '85': 24, '83': 73, '65': 14, '87': 28, '185': 233, '171': 7, '176': 127, '89': 42, '80': 32, '5':
54, '93': 56, '104': 53, '138': 7, '162': 28, '204': 28, '79': 46, '178': 60, '144': 21, '90': 136, '193': 42, '88': 52, '212': 22, '199': 35, '198': 21, '149': 22, '84': 82, '213': 49, '47': 189, '195': 46, '31': 152, '71': 21, '70': 4, '207': 7, '158': 14, '109': 7, '163': 46, '142': 14, '94': 14, '173': 11, '78': 7, '134': 7, '96': 7, '128': 7, '54': 14, '63': 4, '120': 28, '121': 7, '37': 22, '13': 7, '45': 14, '23': 10, '180': 7, '50': 14, '188': 35, '24': 7, '139': 18, '148': 12, '151': 4, '2': 18, '34': 4, '77': 32, '81': 44, '82': 11, '92': 19, '95': 29, '98': 7, '217': 21, '172': 14, '35': 148, '146': 7, '91': 21, '103': 21, '184': 28, '165': 7, '108': 7, '112': 7, '118': 7, '159': 7, '183': 7, '186': 7, '205': 7, '60': 7, '67': 7, '76': 7, '86': 7, '209': 7, '174': 7, '194': 1}

df = pd.DataFrame()
df['Start'] = mydict.keys()
df['Quantity'] = mydict.values()


df.to_excel("C:\Users\David\Desktop\dict_test.xlsx")

Here is how I would do it

First you need pandas and openpyxl so get them first

df = pd.DataFrame.from_records(list(data.items()), columns=['Start', 'Quantity'])

writer = pd.ExcelWriter('out.xlsx')
df.to_excel(writer, 'Sheet1', index=False)
writer.save()

where data is your dictionary

Hope this helps, you first need to rearrange the information contained in the dictionary and save it with pandas Excel Writer

import pandas as pd

dic = {'66': 74, '62': 32, '69': 18, '72': 14, '64': 37, '192': 60, '51': 70, '46': 42, '129': 7, '85': 24, '83': 73, '65': 14, '87': 28, '185': 233, '171': 7, '176': 127, '89': 42, '80': 32, '5':
54, '93': 56, '104': 53, '138': 7, '162': 28, '204': 28, '79': 46, '178': 60, '144': 21, '90': 136, '193': 42, '88': 52, '212': 22, '199': 35, '198': 21, '149': 22, '84': 82, '213': 49, '47': 189, '195': 46, '31': 152, '71': 21, '70': 4, '207': 7, '158': 14, '109': 7, '163': 46, '142': 14, '94': 14, '173': 11, '78': 7, '134': 7, '96': 7, '128': 7, '54': 14, '63': 4, '120': 28, '121': 7, '37': 22, '13': 7, '45': 14, '23': 10, '180': 7, '50': 14, '188': 35, '24': 7, '139': 18, '148': 12, '151': 4, '2': 18, '34': 4, '77': 32, '81': 44, '82': 11, '92': 19, '95': 29, '98': 7, '217': 21, '172': 14, '35': 148, '146': 7, '91': 21, '103': 21, '184': 28, '165': 7, '108': 7, '112': 7, '118': 7, '159': 7, '183': 7, '186': 7, '205': 7, '60': 7, '67': 7, '76': 7, '86': 7, '209': 7, '174': 7, '194': 1}

table = pd.DataFrame(dic, index=[0])
y = [int(item) for item in table.columns.tolist()]
table.loc[1] = table.loc[0]
table.loc[0] = y
table = table.transpose()
table.columns = ['Start', 'Quantity']
table.index = list(range(len(table.index)))
writer = pd.ExcelWriter('output.xlsx')
table.to_excel(writer,'Sheet1', index = False)
writer.save()