python-3.x - How to speed up or replace the nested loops

Question

Can I ask is there any other way to speed up or replace the nested loops?

I am trying to capture the data from excel file there are 66 rows of data with each "A, B, C, D" lines

import xlwings as xw
import glob as glob   
import itertools   
letters = ['A','B','C','D'] 
    for i in range(66):
            for j in range(len(letters)):
                sht1 = wb.sheets['Pallet Configurations']
                val = (sht1.range(letters[j] + str(i+2)).options(numbers=str).value)
                valueList[j].append(val)

I try to use itertools.product but it's not working.

    for i, j in itertools.product(66,len(letters)):
        sht1 = wb.sheets['Pallet Configurations']
        val = (sht1.range(letters[j] + str(i+2)).options(numbers=str).value)
        valueList[j].append(val)

标签： python-3.xnested-loops

Answer 1

I'm not sure exactly which library you are using to manipulate what looks like workbook sheets, however you shouldn't need to set sht1 every time in your inner for loop. Your code could be written as:

sht1 = wb.sheets['Pallet Configurations']
letters = ['A','B','C','D'] 
for i in range(66):
        for j in range(len(letters)):
            val = (sht1.range(letters[j] + str(i+2)).options(numbers=str).value)
            valueList[j].append(val)

This should save you needing to call .sheets on wb each time. Additionally, it looks like whichever library you are using can read in a range of of cells at a time. It would probably be less expensive to read the whole row once, before you enter your inner loop. You could then take advantage of Python's List.extend() method to add the entire list of cells to valueList.

Sample code might look like:

sht1 = wb.sheets['Pallet Configurations']
letters = ['A','B','C','D'] 
for i in range(66):
    for j in range(len(letters)):
        letter = str(letters[j])
        vals = (sht1.range("{0}{1}{0}{2}".format(letter, "2:", "66").options(numbers=str).value)
        valueList[j].extend(val)

This is still not perfect though and there are probably other ways you could speed up your loops. You need to think about minimising what is done in your loop.