Return excel cell values where column and row headers match specified values

Viewed 41

I have a table of census tract information, the first column of which contains a unique identifying code for each tract. The table also includes a header describing the contents of the rows in that column (for example, "pop" for "population", "unempl" for "unemployment", etc.).

I would like to select individual cells from specific census tracts for specific attribues and find sums for those tracts and attributes (for example, returning the total population for census tracts XXX, YYY, and ZZZ).

I know I need to reference the cell position where tract and column header line up but am not sure how to access that. I am open, also, to the possibility that there may be a better way to perform this task.

My code if incomplete but here is what I am working with:

from openpyxl import load_workbook

# Get tracts from user
inputTracts = input("Enter tract number(s): ")

# Create list from tract numbers
inputTracts = inputTracts.split(', ')

# Get number of tracts in the list
numberOfTracts = len(inputTracts)

# Specify headers to look for
searchTerms = ['pop', 'pop60', 'unempl', 'lbrforce', 'hs', 'col', 'pov']

def sum_columns(table):
    total = 0
    tableSum = []

    for cell in range(0, numberOfTracts):
        for tract in inputTracts:
            if cell[0] == tract:
                for cell in table:
                    for term in searchTerms:
                        if cell.value == term:
                            total += # cell position reference here?
                            tableSum.append(total)

    # return the list of totals
    return tableSum
0 Answers
Related