CODEWITHSUNDEEP
pythonexcelopenpyxlxlsx
William
William

Reputation: 85

TypeError: '>' not supported between instances of 'tuple' and 'int' when removing rows using Openpyxl

Given a .xlsx with many Groupings/Levels:

Not expanded: enter image description here

Expanded: enter image description here

I am trying to iterate through the .xlsx and remove any rows, where Management Style is == any value in the management_style_rows_remove list.

ValueError: I receive this ValueError when running the script:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
C:\Users\WILLIA~1.FOR\AppData\Local\Temp/ipykernel_20732/4051092418.py in <module>
     18                 data_sheet.delete_rows(row)
     19 
---> 20 row_cleansing()

C:\Users\WILLIA~1.FOR\AppData\Local\Temp/ipykernel_20732/4051092418.py in row_cleansing()
     16         for cell in row:
     17             if cell.value in management_style_rows_remove:
---> 18                 data_sheet.delete_rows(row)
     19 
     20 row_cleansing()

~\.conda\envs\JPDevelopment\lib\site-packages\openpyxl\worksheet\worksheet.py in delete_rows(self, idx, amount)
    727         """
    728 
--> 729         remainder = _gutter(idx, amount, self.max_row)
    730 
    731         self._move_cells(min_row=idx+amount, offset=-amount, row_or_col="row")

~\.conda\envs\JPDevelopment\lib\site-packages\openpyxl\worksheet\worksheet.py in _gutter(idx, offset, max_val)
    898     range(cells_to_delete) > range(cell_to_be_moved)
    899     """
--> 900     gutter = range(max(max_val+1-offset, idx), min(idx+offset, max_val)+1)
    901     return gutter

TypeError: '>' not supported between instances of 'tuple' and 'int'

Code: here is my v.basic script that I expected to achieve the result described above:

import openpyxl
from openpyxl import load_workbook

def row_cleansing():

    management_style_rows_remove = ['Corporate', 'Global Core Tax-Loss Harvesting', 'High Yield - Corporate', 'High Yield - Municipal', 'Investment Grade - Agg', 'Investment Grade - Agg ESG',
                               'Investment Grade - Municipal', 'Investment Grade - Municipal ESG', 'Investment Grade TIPS', 'Investment Grade Treasurys', 'MLPs', 'Multi-Asset 55ip', 'Multi-Asset Class',
                               'Non-US Core Tax-Exempt ESG', 'Non-US Core Tax-Exempt ESG FX Hedge', 'Non-US Core Tax-Loss Harvesting ESG']

    input_file = 'annonamized_test_data_to_be_split.xlsx'

    workbook = load_workbook(input_file)
    data_sheet = workbook.active
    
    for row in data_sheet.iter_rows(min_col=3, max_col=3, min_row=2, max_row=None):
        for cell in row:
            if cell.value in management_style_rows_remove:
                data_sheet.delete_rows(row)

row_cleansing()

Help: I haven't been able to triangulate exactly what this ValueError means, as it relates to my script.

What I've tried/observed:

  1. This ValueError only flags when there is a match in the management_style_rows_remove list with a value in the Management Style in the .xlsx.
  2. This ValueError flags regardless of whether I reference the list (as per the code) or if I test the code by testing against a single value by changing the code from if cell.value in management_style_rows_remove: to if cell.value '== 'MLPs'.

Is anyone able to offer hints/tips on my code and why this `ValueError' might be flagging?

Upvotes: 0

Views: 785

Answers (2)

user18907109
user18907109

Reputation: 38

I've had the same problem just now. I figured in someone else's code that you have to access the row by inserting an index and .row method as well.

Here is an example of what has worked for me. When iterating through the rows of column 5, if the cell value has 'Schedule' in it, I want to delete the current row and 3 more others:

#Deleting those breaker lines
for row in ws.iter_rows(min_col=5, max_col=5):
    for cell in row:
        if 'Schedule' in str(cell.value):
            ws.delete_rows(row[0].row,4)

In your case, I believe it would work if:

In the following block:

    for row in data_sheet.iter_rows(min_col=3, max_col=3, min_row=2, max_row=None):
    for cell in row:
        if cell.value in management_style_rows_remove:
            data_sheet.delete_rows(row)

You changed the last bit to:

data_sheet.delete_rows(row[0].row)

Hope it helps just like it helped me!

Upvotes: 0

jpg997
jpg997

Reputation: 52

The hint is thar any iteration you do with these methods the object you will get is a tuple (you can check it with type function).

Any tuple is inmutable.

You should consider work with a dict instead of.

Saying “thanks” is appreciated, but it doesn’t answer the question. Instead, vote up the answers that helped you the most! If these answers were helpful to you, please consider saying thank you in a more constructive way – by contributing your own answers to questions your peers have asked here.

Upvotes: 1

Related Questions