Python dictionary to columns in xlsx

Question

I want to export a dictionary with the following format:

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

to columns as shown here:

I have tried using pandas and xlsxwriter but I couldn't make it work

have you tried openpyxl? https://openpyxl.readthedocs.io/en/stable/tutorial.html (and post some code, please) — danielarend, Jan 03 '19 at 23:22
How about making a dataframe out of the array of keys and the array of values of your dictionary? — DavidPM, Jan 03 '19 at 23:32

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

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:

DavidPM · Answer 2 · 2019-01-04T00:01:18.047

3

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

edited Jan 04 '19 at 00:01

answered Jan 03 '19 at 23:37

DavidPM

455
4
10

1

the title asks for `xlsx` not `csv` – gold_cy Jan 03 '19 at 23:42
Yes, big difference. – DavidPM Jan 03 '19 at 23:44
I couldn't replicate this because I had more than this 2 columns, like start2, quantity2 with differents number of entries, my bad not letting you know this. – Ricardo Rodriguez Jan 04 '19 at 18:10

flashliquid · Answer 3 · 2023-03-15T23:37:47.070

You can do this neatly using pandas, just change the path to where you want to save your excel sheet. Since Python 3.6 dictionaries are ordered, but be aware that in older versions, dictionary order is not preserved.


    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}
    
    #create empty data frame
    df = pd.DataFrame()
    
    #Use keys to create 'Start' column
    df['Start'] = mydict.keys()
    
    #Use values to create 'Quantity' column
    df['Quantity'] = mydict.values()
    
    #write to excel    
    df.to_excel("C:\Users\User1\Desktop\dict_test.xlsx")

gold_cy · Answer 4 · 2019-01-03T23:58:25.330

1

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

edited Jan 03 '19 at 23:58

answered Jan 03 '19 at 23:41

gold_cy

13,648
3
23
45

Do you need openpyxl when pandas' to_excel will write your dataframe to an excel file? – flashliquid Jan 04 '19 at 00:18
it's in the official docs, https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_excel.html at the very bottom I am not a pandas expert by any means – gold_cy Jan 04 '19 at 00:19

score 1 · Answer 5 · answered Jan 03 '19 at 23:54

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

Python dictionary to columns in xlsx

5 Answers5