Export data to empty cell row Openpyxl

Question

I am trying to automate number groupings of several lists by exporting the data to ms excel using openpyxl. The output is a list of lists with two sets of numbers per element, the first set being the matched number (0 to 99), and the second is the index number where they matched.

def variable_str_to_list_pairs_overlapping (str):
    return [''.join(pair) for pair in zip(str[:-1], str[1:])]

list1 = variable_str_to_list_pairs_overlapping (list1)
list2 = variable_str_to_list_pairs_overlapping (list2)

lst_result = []
for i in range(len(list2)):
     if list1[i] == list2[i]:
     data = [list1[i], i]
     data[0] = int(list1[i])
     lst_result.append(data)
print(lst_result)

Output:

    [[7, 265], [8, 281], [2, 303], [8, 332], [7, 450], [1, 544], [0, 
    737], [9, 805], [2, 970], [4, 1103], [4, 1145], [8, 1303], [1, 
    1575], [4, 1592], [2, 1593], [3, 1948], [4, 2200], [5, 2419], [3, 
    2464], [9, 2477], [1, 2529], [6, 2785], [2, 2842], [8, 2843], [7, 
    2930], [3, 2991], [8, 3096], [3, 3248], [2, 3437], [7, 3438], [8, 
    3511], [0, 3522], [0, 3523], [5, 3590], [6, 3621], [1, 3622], [2, 
    3671], [6, 3835], [7, 3876]]

I'm looking to export the data to excel in such a way that the first element is assigned as the row index and the second as the value inside the cell

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
dest_filename = 'openpyxltest.xlsx'
for x in lst_result:
    ws.cell(row = x[0] + 1, column = +2).value = x[1]
wb.save(filename = dest_filename)

Actual Output:

Desired Output:

What do I need to change in my code? Thank you in advance for the help. you guys are awesome! :)

@Patrick Artner I appreciate the feedback sir. I've edited my question. I apologize as I am quite new to programming. Thank you. — Marcky, Nov 10 '19 at 15:41

Patrick Artner · Accepted Answer · 2019-11-11T10:23:49.330

You are overwriting the cells - you never adjust the column you write into - so it gets overwritten by later writes...

You could solve this by using a defaultdict(list) to collect all values of one key into a list, sort it and then create the xlsx from the dictionary like so:

lst_result = [[7, 265], [8, 281], [2, 303], [8, 332], [7, 450], [1, 544], [0, 
    737], [9, 805], [2, 970], [4, 1103], [4, 1145], [8, 1303], [1, 
    1575], [4, 1592], [2, 1593], [3, 1948], [4, 2200], [5, 2419], [3, 
    2464], [9, 2477], [1, 2529], [6, 2785], [2, 2842], [8, 2843], [7, 
    2930], [3, 2991], [8, 3096], [3, 3248], [2, 3437], [7, 3438], [8, 
    3511], [0, 3522], [0, 3523], [5, 3590], [6, 3621], [1, 3622], [2, 
    3671], [6, 3835], [7, 3876]]

from collections import defaultdict

# group all datapoints by 1st value
grpd_data = defaultdict(list)
for k,v in lst_result:
    grpd_data[k].append(v)

# sort all grouped values (not needed here, as inputs are sorted)
# for l in grpd_data:
#    grpd_data[l].sort()

grpd_data looks like:

# defaultdict(<type 'list'>, {0: [737, 3522, 3523], 1: [544, 1575, 2529, 3622], 
#     2: [303, 970, 1593, 2842, 3437, 3671], 3: [1948, 2464, 2991, 3248], 
#     4: [1103, 1145, 1592, 2200], 5: [2419, 3590], 6: [2785, 3621, 3835], 
#     7: [265, 450, 2930, 3438, 3876], 8: [281, 332, 1303, 2843, 3096, 3511], 
#     9: [805, 2477]})

Then create the workbook:

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
dest_filename = 'openpyxltest.xlsx'

for x,data in grpd_data.items():
    ws.cell(row = x + 1, column = 1).value = x
    for col,d in enumerate(data,2): 
        ws.cell(row = x + 1, column = col).value = d

wb.save(filename = dest_filename)

Output:

See:

Thank you sir, that worked like a charm. :) If I may, I would also like to ask, since I have several lst_result(s) from different lists, it would immensely help if I can plot them to excel in such a way where it can be indicated which list_result they come from. I was thinking of having the cells color-coded, i.e list_result1 cells are highlighted red, list_result2 would be in yellow, an so on. any insights on this? — Marcky, Nov 11 '19 at 02:45
@Marcky use different [new sheet](https://stackoverflow.com/questions/40385689/add-a-new-sheet-to-a-existing-workbook-in-python) per list or [color your cells](https://stackoverflow.com/questions/42288870/openpyxl-set-background-color-to-a-row-and-column-attribute-error). HTH — Patrick Artner, Nov 11 '19 at 06:09
thank you very much sir, I really appreciate it. I'll look into the resources you provided. — Marcky, Nov 11 '19 at 06:30
@char in this case yes - because the input is sorted.. if not you need to sort the input or the groups. — Patrick Artner, Nov 11 '19 at 10:23
Sure, I was just thinking aloud. `itertools.groupby` would an alternative approach. — Charlie Clark, Nov 11 '19 at 10:34

Export data to empty cell row Openpyxl

1 Answers1