Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Loop-help for a newbie?
#1
Hello,
I'm new here and this is my first post. I am aggressively learning Python and very excited to be doing so. Last year I took a 5-month MEAN stack boot camp (PrimeAcademy.io) and realized, despite my 20-year marine engineering career, I am not a natural coder. I'm struggling to 'think like a programmer' and I suspect it might be my system-based former career.
But that's another discussion so here's my question.

I'm building a spreadsheet using Openpyxl and I need some advice on how to loop the code. I'm not going to include all the code here because that seems obnoxious for a first post, but I'll include a snippet that's repetitive. I'm just having trouble how to conceptuale writing the code in a forLoop or While loop.

sheet.merge_cells('B10:C10')
sheet.merge_cells('D10:E10')
sheet.merge_cells('F10:G10')
sheet.merge_cells('H10:I10')
sheet.merge_cells('B11:C11')
sheet.merge_cells('D11:E11')
sheet.merge_cells('F11:G11')
sheet.merge_cells('H11:I11')
sheet.merge_cells('B12:C12')
sheet.merge_cells('D12:E12')
sheet.merge_cells('F12:G12')
sheet.merge_cells('H12:I12')
sheet.merge_cells('B13:C13')
sheet.merge_cells('D13:E13')
sheet.merge_cells('F13:G13')
sheet.merge_cells('H13:I13')
sheet.merge_cells('B17:C17')
sheet.merge_cells('D17:E17')
sheet.merge_cells('F17:G17')
sheet.merge_cells('H17:I17')
sheet.merge_cells('B18:C18')
sheet.merge_cells('D18:E18')
sheet.merge_cells('F18:G18')
sheet.merge_cells('H18:I18')
sheet.merge_cells('B22:C22')
sheet.merge_cells('D22:E22')
sheet.merge_cells('F22:G22')
sheet.merge_cells('H22:I22')
sheet.merge_cells('B23:C23')
sheet.merge_cells('D23:E23')
sheet.merge_cells('F23:G23')
sheet.merge_cells('H23:I23')
sheet.merge_cells('A24:I24')
sheet.merge_cells('B25:C25')
sheet.merge_cells('D25:E25')
sheet.merge_cells('F25:G25')
sheet.merge_cells('H25:I25')
sheet.merge_cells('B29:C29')
sheet.merge_cells('D29:E29')
sheet.merge_cells('F29:G29')
sheet.merge_cells('H29:I29')
sheet.merge_cells('B30:C30')
sheet.merge_cells('D30:E30')
sheet.merge_cells('F30:G30')
sheet.merge_cells('H30:I30')
sheet.merge_cells('B31:C31')
sheet.merge_cells('D31:E31')
sheet.merge_cells('F31:G31')
sheet.merge_cells('H31:I31')
sheet.merge_cells('B32:C32')
sheet.merge_cells('D32:E32')
sheet.merge_cells('F32:G32')
sheet.merge_cells('H32:I32')
sheet.merge_cells('B39:C39')
sheet.merge_cells('D39:E39')
sheet.merge_cells('F39:G39')
sheet.merge_cells('H39:I39')
sheet.merge_cells('B40:C40')
sheet.merge_cells('D40:E40')
sheet.merge_cells('F40:G40')
sheet.merge_cells('H40:I40')
sheet.merge_cells('A41:I41')
sheet.merge_cells('A42:I42')
sheet.merge_cells('A43:I43')
wb.save('daily_log_v1.xlsx')
Thanks for the time.
I'm looking forward to alot of time in this forum.
BTW, what's the best way to get this forum on my Android phone? Tapatalk or another way?
Thanks,
Phil
Reply
#2
Never used Openpyxl and have no advice there, but one way of using loop (with printing out to show result):

>>> cols = list(zip(['B', 'D', 'F', 'H'], ['C', 'E', 'G', 'I']))
>>> for i in range(10, 13):
...     for pair in cols:
...         print(f'{pair[0]}{i}:{pair[1]}{i}')
...
B10:C10
D10:E10
F10:G10
H10:I10
B11:C11
D11:E11
F11:G11
H11:I11
B12:C12
D12:E12
F12:G12
H12:I12
In your actual code you should define correct range and replace print row to:

sheet.merge_cells(f'{pair[0]}{i}:{pair[1]}{i}')
If you have only 4 pairs then zip is probably overkill, you can write it manually:

cols = [('B', 'C'), ('D', 'E'), ('F', 'G'), ('H', 'I')]
I'm not 'in'-sane. Indeed, I am so far 'out' of sane that you appear a tiny blip on the distant coast of sanity. Bucky Katt, Get Fuzzy

Da Bishop: There's a dead bishop on the landing. I don't know who keeps bringing them in here. ....but society is to blame.
Reply
#3
Wow, awesome Perfringo. Thanks.
I was going a different direction by creating a dictionary and working on the letters to swap. Wasn't working out so well. This is so humbling!
merge_cells = {
    'B10' : 'B10:C10', 'D10' : 'D10:E10', 'F10' : 'F10:G10', 'H10' : 'H10:I10'
    }
print (merge_cells)

def merge(x,y):
    for i in merge_cells:
        sheet.merge_cells('i')
Phil

Quote:If you have only 4 pairs then zip is probably overkill, you can write it manually:
cols = [('B', 'C'), ('D', 'E'), ('F', 'G'), ('H', 'I')]
I'm not sure what you mean here by 'zip'. This is a pretty big SS though and I put a very small snippet in this post.
Phil
Reply
#4
Result of this piece of code:

cols = list(zip(['B', 'D', 'F', 'H'], ['C', 'E', 'G', 'I'])) 
is same as this:

cols = [('B', 'C'), ('D', 'E'), ('F', 'G'), ('H', 'I')]
As second way requires less typing (and less conversion too) it may be better. However, zip is very handy if there are already existing lists. For example, if you have already lists for columns (say first and second) created in your code earlier then you can do:

cols = list(zip(first, second)) 
I'm not 'in'-sane. Indeed, I am so far 'out' of sane that you appear a tiny blip on the distant coast of sanity. Bucky Katt, Get Fuzzy

Da Bishop: There's a dead bishop on the landing. I don't know who keeps bringing them in here. ....but society is to blame.
Reply
#5
just to mention you don't need to convert to list, you can iterate over the zip object
columns = zip('BDFH', 'CEGI')
for row in range(10, 13):
    for col1, col2 in columns:
        print(f'{col1}{i}:{col2}{i}')
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#6
(Aug-29-2018, 05:48 AM)buran Wrote: just to mention you don't need to convert to list, you can iterate over the zip object

This is true, but you can iterate over zip object only once. As there is range of values to iterate over this way of doing it produces results only for first value in range.

If would be another story when new zip object is created at every loop:

for col1, col2 in zip('BDFH', 'CEGI'):
I'm not 'in'-sane. Indeed, I am so far 'out' of sane that you appear a tiny blip on the distant coast of sanity. Bucky Katt, Get Fuzzy

Da Bishop: There's a dead bishop on the landing. I don't know who keeps bringing them in here. ....but society is to blame.
Reply
#7
you are right, my bad. That is how i did, then (instead of copy/paste my snippet) I just started to change your code and ended up with this non-working snippet. There is also another error, because i is not defined at the moment. So working snippet

for row in range(10, 13):
    for col1, col2 in zip('BDFH', 'CEGI'):
        print(f'{col1}{row}:{col2}{row}')
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#8
(Aug-29-2018, 06:20 AM)buran Wrote: That is how i did

I think that your solution with unpacking on for-line is superior to messing with indices. It's more concise and readable.
I'm not 'in'-sane. Indeed, I am so far 'out' of sane that you appear a tiny blip on the distant coast of sanity. Bucky Katt, Get Fuzzy

Da Bishop: There's a dead bishop on the landing. I don't know who keeps bringing them in here. ....but society is to blame.
Reply
#9
Thank you for this. I plan on digesting this tomorrow. 11PM and I'm beat right now. I really appreciate the discussion <:

Phil
Reply
#10
I am getting an error with only the f-string.
Can I get your thoughts?
Error line:
sheet.merge_cells(f'{pair[0]}:{pair[1]}{i}')
Traceback Error:
Traceback (most recent call last):
  File "C:\Python36x64\lib\site-packages\openpyxl\descriptors\base.py", line 57, in _convert
    value = expected_type(value)
TypeError: int() argument must be a string, a bytes-like object or a number, not 'NoneType'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:/Python36x64/test.py", line 40, in <module>
    sheet.merge_cells(f'{pair[0]}:{pair[1]}{i}')
  File "C:\Python36x64\lib\site-packages\openpyxl\worksheet\worksheet.py", line 705, in merge_cells
    max_col=end_column, max_row=end_row)
  File "C:\Python36x64\lib\site-packages\openpyxl\worksheet\cell_range.py", line 58, in __init__
    self.min_row = min_row
  File "C:\Python36x64\lib\site-packages\openpyxl\descriptors\base.py", line 107, in __set__
    value = _convert(self.expected_type, value)
  File "C:\Python36x64\lib\site-packages\openpyxl\descriptors\base.py", line 59, in _convert
    raise TypeError('expected ' + str(expected_type))
TypeError: expected <class 'int'>
Thanks,
Phil

Reading the Traceback, I see it looks like a type error due to Openpyxl.
What's the best way to troubleshoot this? Looking in the Openpyxl docs under the base.py, cell_range.py, and/or worksheet.py modules for '_convert' type errors?
This error says,
TypeError: int() argument must be a string
and
TypeError: expected <class 'int'>
Confusing!

f-strings are new in 3.7(?) version.
How could I do this loop without an f-string, very simple so a newbie could understand the loop easily?

Thanks,
phil

looking over the base.py source code, I can see the cause of the error. I'm unsure how to fix it with the f-string.

def _convert(expected_type, value):
    """
    Check value is of or can be converted to expected type.
    """
    if not isinstance(value, expected_type):
        try:
            value = expected_type(value)
        except:
            raise TypeError('expected ' + str(expected_type))
    return value
Source code for openpyxl.descriptors.base

If I understand this, it's looking for a string with the _convert() function which is held within the __set__() function which is called a tthe bottom of the base.py file:
super(MatchPattern, self).__set__(instance, value)
I've added extra quotes around the f-string, but that failed too.
How to continue logical troubleshooting with this code error?
Thanks,
I'm trying Wall
Phil

Okay, I don't know if anyone is still with me, but I'll continue to document my step-by-step for anyone else behind me.
I've looked over the Openpyxl source code for
  1. base.py
  2. cell_range.py
  3. worksheet.py
In the worksheet module, lines 703-725 it talks about merging.
Specifically, look at the comment on line 706:
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
        cr = CellRange(range_string=range_string, min_col=start_column, min_row=start_row,
                      max_col=end_column, max_row=end_row)
        """ Set merge on a cell range.  Range is a cell range (e.g. A1:E1) """

        self.merged_cells.add(cr.coord)
        self._clean_merge_range(cr)
so I think the f-string code,
sheet.merge_cells(f'{pair[0]}:{pair[1]}{i}')
, is not meeting the type function required by this worksheet module.
How could I adjust my code to loop over my repetitive lines while fulfilling the worksheet module requirements of
Range is a cell range (e.g. A1:E1)
Thanks,
Phil
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  newbie while loop error: IndexError: list assignment index out of range msa969 3 75,001 Mar-31-2017, 12:24 PM
Last Post: msa969

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020