Python iteration logic: iterate over a list in groups of 200 in openpyxl

Question

I can't wrap the logic around my head. Can someone suggest a solution.

I have an excel file with 10000 items in row A of worksheet0 I want to iterate over the rows, but in groups of 200. Here is my dry algorithm that I can't seem to put into code:

read an excel file, worksheet0 as source
create an empty excel worksheet using openpyxl, worksheet1
itereate over the first 200 string items of 10000 total in column A from source worksheet0

a. strip() each string item b. save the 'stripped' new strings in worksheet1 columnA, row1:row200

create a new empty worksheet, worksheet2
iterate over the next 200 string items from worksheet0 row201:400 a. strip() b. save in worksheet2

repeat this creation of new worksheet, stripping and saving in this new worksheet for every group of 200

I just can't get the algorithm to iterate over groups of 200 and every new group create a new worksheet file. Can anyone help with the algo?

# rowMax is the last row in columnA aka the length of my list to iterate over

for count in range(1, int(rowMax/200)):
    _=wb.create_sheet(str(count))
    for row in range(count*2, 200*count):
        targetEmail = str(ws1.cell(column=1, row=row).value).strip()
        sourceEmail = str(ws1.cell(column=1, row=row).value)
        if targetEmail != sourceEmail:
            print("Correction required for: ", sourceEmail, "\nChanged to: ", targetEmail)

        _.cell(column=1, row=row).value = targetEmail

The above doesn't work. I'm just putting it there so you understand that every 200 items I create a new worksheet and then save the next 200 items in that new worksheet, and then repeat this process for each 200 item chunk

Possible duplicate of [What is the most "pythonic" way to iterate over a list in chunks?](http://stackoverflow.com/questions/434287/what-is-the-most-pythonic-way-to-iterate-over-a-list-in-chunks) — matias elgart, Dec 02 '16 at 18:27
Yes, indeed the other post answers the question but using itertools. I want to understand how I'd dry code this myself. — Michael 1022, Dec 02 '16 at 18:29
You could use something like `for count, x in enumerate(my_list):` and then use the modulo operator to perform some action on every 200th item e.g. `if count % 200 == 0: `. Not quite sure what you're after. — roganjosh, Dec 02 '16 at 18:31
or you can use python slices to increment over the list in 'slices' of the list. but this use case is exactly why that itertools solution exists. — matias elgart, Dec 02 '16 at 18:32
Regarding the modulo - yes, but I not only want to do something every 200th item, i want the new group starting 201+ to be saved in a new worksheet. I'll add my code in the original post — Michael 1022, Dec 02 '16 at 18:33
Which is why on the 200th item you'd perform your write operation and define your worksheet name as something new. — roganjosh, Dec 02 '16 at 18:34
and why you don't want to use itertools?? that is one of the best if not the best way to solve the problem — Copperfield, Dec 03 '16 at 16:26

score 1 · Accepted Answer · answered Dec 03 '16 at 14:51

This question is too broad. You need to provide more code and be more specific as to which problem you wish to solve.

Something like the following should help you get going.

colA = ws1['A']
for idx, cell in colA:
    if not idx % 200:
        ws2 = wb.create_sheet()
    ws2.append(cell.value.strip())

Python iteration logic: iterate over a list in groups of 200 in openpyxl

1 Answers1