Python: how to export a dictionary of nested lists to Excel

Question

I'm new to Python and I'm having trouble to resolve this issue.

For instance if I have a dictionary like this one:

my_dict = {(40987, 'A'): [[1, 2, 3], [0, 1, 0], [0, 1, 1]], (11233, 'R'): [[2, 0, 2], [0, 2, 4]], (10716, 'R'): [[1, 1, 1]], (11049, 'S'): [[6, 0, 5], [2, 5, 7]]}

The structure of the excel file that I want is:

Code  Letter    List0        List1      ...     List_n

40987   A     [1, 2, 3]    [0, 1, 0]
11233   R     [2, 0, 2]    [0, 2, 4]
....

Is there a way to export this dictionary of nested lists to an excel file?

please specify desired structure of excel, what are the headers, etc.... — gold_cy, Jun 02 '17 at 17:48

score 1 · Accepted Answer · answered Jun 02 '17 at 18:25

You can use the openpyxl module.

from openpyxl import Workbook
wb=Workbook()
dest_filename = 'excelsheet.xlsx'
ws1 = wb.active
ws1.title = "nested lists"
dict={(40987, 'A'): [[1, 2, 3], [0, 1, 0], [0, 1, 1]], (11233, 'R'): [[2, 0, 2], [0, 2, 4]], (10716, 'R'): [[1, 1, 1]], (11049, 'S'): [[6, 0, 5], [2, 5, 7]]}
number=1
for item in dict.keys():

    ws1.cell(row=number,column=1).value=item[0]
    ws1.cell(row=number, column=2).value=item[1]
    r=3
    for list in dict[item]:
        ws1.cell(row=number, column=r).value = str(list)
        r+=1
    number += 1
wb.save(filename = dest_filename)

Sorry if this is not the best way, I'm also a bit new to Python. :)

Nathaniel Rivera Saul · Answer 2 · 2017-06-02T18:43:45.210

This outputs a csv file that you can open up in Excel.

import csv

my_dict = {
    (40987, 'A'): [[1, 2, 3], [0, 1, 0], [0, 1, 1]],
    (11233, 'R'): [[2, 0, 2], [0, 2, 4]],
    (10716, 'R'): [[1, 1, 1]],
    (11049, 'S'): [[6, 0, 5], [2, 5, 7]]
}

# Find the length of the longest list in the dictionary
max_list_size = max(len(x) for _, x in my_dict.items())

with open('my_dict.csv', 'w', newline='') as csvfile:
    dictwriter = csv.writer(csvfile)        

    # Define and write the header row with enough 'listX' columns
    header = ['Code', 'Letter'] + [f'list{i}' for i in range(max_list_size)]
    print(header)
    dictwriter.writerow(header)

    # Iterate through each entry in the dictionary, writing each row
    for key, value in my_dict.items():
        # Extend the list with blank values (not totally necessary, but keeps the csv file uniform)
        row = [*key] + value + [""] * (max_list_size - len(value))
        print(row)
        dictwriter.writerow(row)

Note: this requires a modern Python install. Use 'list{}'.format(i) instead of f'list{i} if you can't update.

MarianD · Answer 3 · 2017-06-02T19:27:32.680

Probably the most simple way is to output it as CSV file and then open this CSV file in Excel:

import csv

my_dict = {(40987, 'A'): [[1, 2, 3], [0, 1, 0], [0, 1, 1]], 
           (11233, 'R'): [[2, 0, 2], [0, 2, 4]], 
           (10716, 'R'): [[1, 1, 1]],
           (11049, 'S'): [[6, 0, 5], [2, 5, 7]]}

with open('output.csv', 'w', newline='') as csvfile:
    csvwriter = csv.writer(csvfile, delimiter=';', quoting=csv.QUOTE_NONNUMERIC)
    for key in my_dict:
         csvwriter.writerow(list(key) + my_dict[key])

Python: how to export a dictionary of nested lists to Excel

3 Answers3

Linked