I have a table in Excel and I want to be able to read it (I know how to achieve this) but also tell Python that if there are certain values of d, D and B, for it to return the dimension value in the top first row as a variable in my programme. I have read various posts concerning intersections and I am not sure whether they were what I was looking for therefore I have decided to ask my own question.
My Excel table is in the format as follows (just a small example since I can't post images):
Dimensions
d D | 17 27 37 47 17-47
| B | rsmin
0.6 2 0.8 - - - 0.05
1 2.5 1 - - - 0.05
1.5 3 1 - 1.8 - 0.05
2 4 1.2 - 2 - 0.05
If I take an example and I have a d = 2, D = 4 (these two values will always be in the same row) and B = 2. I therefore would like to return the value Dimension = 37 to my programme. I also have the problem that I have several worksheets to read so I will refer to this table as Table1 and I must initially read through all worksheets which include one table each in the same .xls file.
Here's how to search your table, based on guesses as to what you want. You should be able to do the loop-over-5-tables stunt yourself.
def search(table_iterator, d, D, B):
headings = next(table_iterator)
junk = next(table_iterator)
key = [d, D]
for row in table_iterator:
if row[0:2] != key: continue
for index, value in enumerate(row[2:-1]):
if value == B:
return headings(2 + index)
return None
Update after questions asked in comment:
"""This should return the dimension value I want?"""
Yes, it should. But it's a generalised approach. I don't answer "gimme teh codez" questions literally. You have to do some work, either to write a "table_iterator" suitable to the tool (presumably xlrd) with which you are reading the table(s), or treat it as pseudocode which you take as a guide and completely rewrite to suit the tool.
"""In the end I have compiled all my tables into one .xls document but using several worksheets. Is there a particular way I could go about searching all worksheets and then applying this code?"""
As I said, you should be able to do the loop-over-5-tables stunt yourself. Searching all worksheets and then applying this code seems a strange approach. You need to iterate over the worksheets, searching each one, until you find a matching row.