Trouble comparing 2 excel sheets in Python using nested for loop

Question

Using python 3.4 and I have a problem with a small part of a larger program I am working on. For this part I need to compare column A of two excel sheets 'Bookings.xlsx' and 'Forced.xlsx'.

Column A contains booking numbers in both sheets, bookings.xlsx contains the data I need (in the same row) for every booking number in forced.xlsx

Here is the part I'm having trouble with.

reloc_sheet = reloc_book.sheet_by_index(0)
forced_sheet = forced_book.sheet_by_index(0)
bookings_sheet = bookings_book.sheet_by_index(0)

forced_rentals = []
for force_row in range(4,forced_sheet.nrows): #row 0:3 are headers
        Fnum = forced_sheet.cell(force_row, 0)
        for book_row in range(1,bookings_sheet.nrows): #row 0 is a header
                Bnum = bookings_sheet.cell(book_row,0)
                if Fnum == Bnum:
                        booNum = str(bookings_sheet.cell(book_row,0))
                        renCODate = bookings_sheet.cell(book_row,2)
                        renCOLoc = str(bookings_sheet.cell(book_row,4))
                        renUnit = str(bookings_sheet.cell(book_row,13))
                        renAgent = str(bookings_sheet.cell(book_row,12))
                        forced_rentals += [[booNum,renCODate,renCOLoc,renUnit,renAgent]]

So as far as I understand, this should look at the each booking number in the 'forced' sheet (variable Fnum) and compare it against the 'bookings' sheet (variable Bnum) and when it finds a match it will add the appropriate data from that row to the list 'forced_rentals'.

The problem is that after this loop has finished, the list is empty but it should have found 632 matches and therefore contain 632 nested lists. I'm sure it's a really simple solution but I can't figure it out.

raymelfrancisco · Accepted Answer · 2015-07-19 05:08:22Z

0

Changing cell() to cell_value(),

Fnum = forced_sheet.cell_value(force_row, 0)
Bnum = bookings_sheet.cell_value(book_row,0)

or casting the types of Fnum and Bnum to str will compare them based on their content strings.

if str(Fnum) == str(Bnum):

Take note that cell() returns a xlrd.sheet.Cell object.

And Cell class of xlrd doesn't have __eq__() and __ne()__ to support equality operators. Read more here: https://docs.python.org/2/reference/datamodel.html#object.ne

You can take a look at xlrd's source here, https://github.com/python-excel/xlrd/blob/master/xlrd/sheet.py.

From The xlrd Module:

cell(rowx, colx) [#]

Cell object in the given row and column.

cell_value(rowx, colx) [#]

Value of the cell in the given row and column.

Because of that, the types of Fnum and Bnum is xlrd.sheet.Cell, not str.

>>> type(Fnum)
<class 'xlrd.sheet.Cell'>
>>>
>>> type(Bnum)
<class 'xlrd.sheet.Cell'>

But in using cell_value(),

>>> type(Fnum)
<class 'str'>
>>>
>>> type(Bnum)
<class 'str'>

Then you can compare them based on their string values.

edited Jul 19, 2015 at 5:08

answered Jul 18, 2015 at 10:00

raymelfrancisco

8732 gold badges11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3514222 Over a year ago

Thanks for you explanation on the difference between cell() and cell_value(). I changed cell to cell_value and still have the same problem, I also tried if str(Bnum) == str(Fnum) which didn't work either. I tested with print(Bnum) and print(Fnum) in each loop and in each iteration the variable is assigning to the correct cell value.

raymelfrancisco Over a year ago

@user3514222 Have you tried using cell_value() in assigning to booNum, renCODate, renCOLoc, renUnit, and renAgent`?

user3514222 Over a year ago

yes changed those also. I just ran through the debugger in wing IDE and may have found the problem. I inserted a row in each sheet with the same booking number (so I don't need to step over each iteration until it finds a match). In the stack data on the line where Bnum is compared with Fnum, Bnum="7070129.0" and Fnum="7070129" and therefore don't match. How can I remove the decimal considering some booking numbers start with "W", "R" or "B" followed by 7 numbers?

raymelfrancisco Over a year ago

You don't need to convert it. 7070129.0 == 7070129 after all ( check this answer to find out why ). But if you really want to convert it, int(Bnum) is the way.

user3514222 Over a year ago

Of course, Bnum and Fnum were converted to a string so '7070129' didn't equal '7070129.0'. I've used the following to check if a booking number contains only numbers and then not convert it to a string. Also some bookings contain a '/' just to make it more difficult. if Fnum.startswith('W') == False and Fnum.startswith('B') == False and Fnum.startswith('R') == False: if '/' not in Fnum: Fnum = int(Fnum) Now the 'test' booking' number I entered is being added to the list but not the other 632.

user3514222 · Accepted Answer · 2015-07-22 04:02:37Z

I have solved my problem. First of all here is the snippet of code which now works:

forced_rentals = []
for force_row in range(4,forced_sheet.nrows):
        Fnum = forced_sheet.cell_value(force_row, 0)
        Fnum_type = type(Fnum)
        if type(Fnum) is float:
                Fnum = str(Fnum)
                Fnum = Fnum.replace('.0','')
        if Fnum[-2:] == '/1':
                Fnum = Fnum.replace('/1','')
        for book_row in range(1,bookings_sheet.nrows):
                Bnum = bookings_sheet.cell_value(book_row,0)
                Bnum_type = type(Bnum)
                if type(Bnum) is float:
                        Bnum = str(Bnum)
                        Bnum = Bnum.replace('.0','') 
                if Bnum[-2:] == '/1':
                        Bnum = Bnum.replace('/1','')                
                if Fnum == Bnum:
                        booNum = str(bookings_sheet.cell_value(book_row,0))
                        renCODate = bookings_sheet.cell_value(book_row,2)
                        renCOLoc = str(bookings_sheet.cell_value(book_row,4))
                        renUnit = str(bookings_sheet.cell_value(book_row,13))
                        renAgent = str(bookings_sheet.cell_value(book_row,12))
                        forced_rentals += [[booNum,renCODate,renCOLoc,renUnit,renAgent]]
                        break

1) An all number Bnum or Fnum variable would either be a string eg '7123456' or a float 7123456.0 which wasn't being recognised as the same value. Converting to string by would simply make the float '7123456.0' again not the same. I solved this by:

if type(Fnum) is float:
                Fnum = str(Fnum)
                Fnum = Fnum.replace('.0','')

This converts a float to a string and removes the '.0'

2) Next issue came when I realised that not all booking numbers (Bnum and Fnum variables) will include a /1. Rental 7123456 and rental 7123456/1 are the same but our report server (which generates the excel sheets) will use the two interchangeably meaning the forced sheet may have 7123456 and the booking sheet have 7123456/1. In order to compensate for this I added this:

if Fnum[-2:] == '/1':
                Fnum = Fnum.replace('/1','')

This will look for any booking number ending with '/1' and remove it.

Collectives™ on Stack Overflow

Trouble comparing 2 excel sheets in Python using nested for loop

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related