How to find specified data in excel by using python (positive number first appeared and negative number nearby)

Question

I want to search data in every row in excel and find out the the first appeared positive number(A) and one negative number(B) just before A in sequence. Like: -5 -6 -3 4 1 I need number -3 and 4

I try to use this kind of code:

import xlrd

testFile=xlrd.open_workbook('test.xls')
mySheet=testFile.sheet_by_name("Sheet1")

nrows=mySheet.nrows
ncols=mySheet.ncols

def f(j):
    value=mySheet.cell(0,j).value
    return value

for j in range(ncols):
    if f(j)*f(j*1)>0:
        print f(j),f(j+1)

but when I run it, error comes out:

Traceback (most recent call last):
File "test1.py", line 15, in (module)
print f(j),f(j+1)
File "test1.py", line 10, in f
value=mySheet.cell(0,j).value
File "C:\Python27\lib\site-packages\xlrd\sheet.py", line 399, in cell
self._cell_types [rowx][colx],
IndexError: array index out of range

I have no idea why I cannot use the function f(j)*f(j+1)>0to find out the first positive number and nearby negative number. Would someone help to find the problems?

Answer 1

Your range is from 0 to j. you are trying to access j+1 value. Hence, IndexError: array index out of range.

Also, you are doing if f(j)*f(j*1)>0: you probably wanted to do if f(j)*f(j+1)<0: Here I changed 2 things. made j*1 to j+1 and >0 to <0. You are checking if negativepositive against negativenegative. f(j)*f(j*1) should remain >0 until u find the first positive number.

And to solve index out of range run your loop until (ncols-1)

You can also do it in a much more efficient way. Just check if the number is positive. If yes then return that and the value at the previous index. You would not have to multiply which is an extra time consuming command