user2063 user2063 - 10 months ago 72
Python Question

Retrieving value from Excel table from an intersection of values using Python

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):

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.

Answer Source

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.