Python Forum

Hello,

I don't really understand the def() function so I'm not really sure what I need to put at line def. Right now nothing is printing out when I run this portion of the script. If I remove def and return the script fines my string but I need it to return a different cell once it finds my string so that's why I added in def and return.

TFile = xl.load_workbook(T)
Sheet = TFile.worksheets[0]

String=pd.read_excel(input_file, usecols="A",nrows=1,header=None,squeeze=True).str.slice(start=28,stop=-2).to_string(index=False).strip()
   
   def TA(String):
    for cellObj in Sheet['B2:B10000']:
     for obj in cellObj:
        if (obj.value) == String:
          return obj.offset(column=2, row=6).value  
          TA = obj.offset
    print(TA)   

beginning in line 6, shift indentation left 4 spaces.
after that, indentation needs to be in increments of 4 spaces per level

there are also basic python errors in code
line 11 is not reachable as written, because it is shown as part of the if statement, which has already issued a return
probably more

There are a number of issues. First to answer your question - a function is a piece of code that performs a task. You can think of a function as something you wish was built into the language but isn't, so in essence you are expanding the language for a particular purpose. Any task that is done repeatedly should be done as a loop (for or while) if just in one place, or as a function if done in multiple places.

A function has its own "namespace" - variables you use inside the function are not visible outside the function, and variables outside the function are not visible inside. The exception to this is globals, which are frowned upon as they get you into messes. So, you pass in the values that the function will need in order to accomplish its task, and it returns the values you need as a result of the task.

After the function executes the return statement, nothing else in the function executes - rather you are returned to the calling program.

A couple nitpicky things before we proceed. Capitals are used in class definitions but avoided in variables and function names. CamelCase is also to be avoided, rather using the underscore to separate words (camel_case would be the preferred name). Finally, indentation is CRITICAL in Python. Standard is 4 spaces. It appears on my screen that you are using 1 space, and that the print(TA) statement would line up under the for cellObj statement and therefore is part of the function, which I don't think you want.

I don't understand your logic, in that you have named a variable after the return statement - that line will never be executed. The print statement, assuming you want that out of the function, does not actually call the function. To do that you would use print(TA(String)). As it is you would only get the address of the function.

So take this and fix the logic and let us know the next issue. Note that I used different variable names inside and outside the function. This helps avoid confusion as to where you are and what variables are active. You won't accidentally think you have access to Sheet, for example, when in fact you do not when inside the function.

t_file = xl.load_workbook(T)
sheet = t_file.worksheets[0]
 
big_string=pd.read_excel(input_file, usecols="A",nrows=1,header=None,squeeze=True).str.slice(start=28,stop=-2).to_string(index=False).strip()
    
def ta(the_string, the_sheet):
    for cellobj in the_sheet['B2:B10000']:
        for obj in cellobj:
            if (obj.value) == the_string:
                return obj.offset(column=2, row=6).value  
#TA = obj.offset I don't know what you are trying to do here
print(ta(big_string, sheet))

(Jul-22-2020, 06:31 PM)jefsummers Wrote: [ -> ]There are a number of issues. First to answer your question - a function is a piece of code that performs a task. You can think of a function as something you wish was built into the language but isn't, so in essence you are expanding the language for a particular purpose. Any task that is done repeatedly should be done as a loop (for or while) if just in one place, or as a function if done in multiple places.

A function has its own "namespace" - variables you use inside the function are not visible outside the function, and variables outside the function are not visible inside. The exception to this is globals, which are frowned upon as they get you into messes. So, you pass in the values that the function will need in order to accomplish its task, and it returns the values you need as a result of the task.

After the function executes the return statement, nothing else in the function executes - rather you are returned to the calling program.

A couple nitpicky things before we proceed. Capitals are used in class definitions but avoided in variables and function names. CamelCase is also to be avoided, rather using the underscore to separate words (camel_case would be the preferred name). Finally, indentation is CRITICAL in Python. Standard is 4 spaces. It appears on my screen that you are using 1 space, and that the print(TA) statement would line up under the for cellObj statement and therefore is part of the function, which I don't think you want.

I don't understand your logic, in that you have named a variable after the return statement - that line will never be executed. The print statement, assuming you want that out of the function, does not actually call the function. To do that you would use print(TA(String)). As it is you would only get the address of the function.

So take this and fix the logic and let us know the next issue. Note that I used different variable names inside and outside the function. This helps avoid confusion as to where you are and what variables are active. You won't accidentally think you have access to Sheet, for example, when in fact you do not when inside the function.

t_file = xl.load_workbook(T)
sheet = t_file.worksheets[0]
 
big_string=pd.read_excel(input_file, usecols="A",nrows=1,header=None,squeeze=True).str.slice(start=28,stop=-2).to_string(index=False).strip()
    
def ta(the_string, the_sheet):
    for cellobj in the_sheet['B2:B10000']:
        for obj in cellobj:
            if (obj.value) == the_string:
                return obj.offset(column=2, row=6).value  
#TA = obj.offset I don't know what you are trying to do here
print(ta(big_string, sheet))

I need to get the value to print out for 'return obj.offset' So, I was defining TA = obj.offset to print out TA.

But that is also the name of your function. In some languages you define your return value by setting it to the function name. That is not true with Python.
Please recognize that by having the return inside an if statement it returns the value only if the first obj.value is equal to the_string. If not, it simply drops to the end of the function and returns nothing.
You could print the value rather than return it (generally bad idea), or build a list of values that match and return that. The latter would be

t_file = xl.load_workbook(T)
sheet = t_file.worksheets[0]
 
big_string=pd.read_excel(input_file, usecols="A",nrows=1,header=None,squeeze=True).str.slice(start=28,stop=-2).to_string(index=False).strip()
    
def ta(the_string, the_sheet):
    out_list = []
    for cellobj in the_sheet['B2:B10000']:
        for obj in cellobj:
            if (obj.value) == the_string:
                outlist.append(obj.offset(column=2, row=6).value)
    return out_list  
#TA = obj.offset I don't know what you are trying to do here
print(ta(big_string, sheet))

(Jul-22-2020, 07:02 PM)jefsummers Wrote: [ -> ]But that is also the name of your function. In some languages you define your return value by setting it to the function name. That is not true with Python.
Please recognize that by having the return inside an if statement it returns the value only if the first obj.value is equal to the_string. If not, it simply drops to the end of the function and returns nothing.
You could print the value rather than return it (generally bad idea), or build a list of values that match and return that. The latter would be

t_file = xl.load_workbook(T)
sheet = t_file.worksheets[0]
 
big_string=pd.read_excel(input_file, usecols="A",nrows=1,header=None,squeeze=True).str.slice(start=28,stop=-2).to_string(index=False).strip()
    
def ta(the_string, the_sheet):
    out_list = []
    for cellobj in the_sheet['B2:B10000']:
        for obj in cellobj:
            if (obj.value) == the_string:
                outlist.append(obj.offset(column=2, row=6).value)
    return out_list  
#TA = obj.offset I don't know what you are trying to do here
print(ta(big_string, sheet))

When I use this it prints out [None]

This is what I had at first. This finds my string and prints it out A1180. Now that I found the string I need to get the string that is 9 cells below the first string same column, and thats where I though offset could be used. Or am I going about this completely wrong?

  
TrainFile = xl.load_workbook(Train)
TrainFileSheet = TrainFile.worksheets[0]
String=pd.read_excel(input_file, usecols="A",nrows=1,header=None,squeeze=True).str.slice(start=28,stop=-2).to_string(index=False).strip()
 

for cellObj in TrainFileSheet['B2:B10000']:
     for obj in cellObj:
         if (obj.value) == String:
         TA = obj.value
print(TA)
A1180
   

If that gets the answer you want, certainly use it. That is not the code you originally posted, and I would still point out the "style" issues with variable names and the like.

I point out that the code I have supplied was completely untested as I had no access to your data.

(Jul-23-2020, 01:06 AM)jefsummers Wrote: [ -> ]If that gets the answer you want, certainly use it. That is not the code you originally posted, and I would still point out the "style" issues with variable names and the like.

I point out that the code I have supplied was completely untested as I had no access to your data.

It doesn’t get me the full answer. I said now I need to figure out how to get the value that is 9 cells below the one that was found. This is where I’m having the problem. I was told offset could be used but I’m not having any luck.

Still a problem in your if statement - whether true or false it just progresses to the next line and assigns the value to TA.

Personally, I would read the sheet into a Pandas dataframe and use iloc in a loop to find the values, then simply add 9 when found and print that value.

Alternatively, look at using iterrows such as in the article here

(Jul-23-2020, 05:26 PM)jefsummers Wrote: [ -> ]Still a problem in your if statement - whether true or false it just progresses to the next line and assigns the value to TA.

Personally, I would read the sheet into a Pandas dataframe and use iloc in a loop to find the values, then simply add 9 when found and print that value.

Alternatively, look at using iterrows such as in the article here

I ended up reading it in with Pandas. I'm able to find the string inside the train file. How at this point would I add 9 rows now that the string has been found?

   train_file= pd.read_excel(Train,usecols = 'B')
   string=ws1['A1'].value[28:][:4]
   train_file[train_file['Name'].str.match(string,na=False)]