Using search cursor or select for related table data in ArcPy

Question

I am very new to ArcPy and code.

I have about 600 zones that overlay ~8000 different census blocks, which have been divided so that census blocks that aren't fully inside a zone are split, meaning that there's about 14,500 total rows in my census table. The population for these blocks have already been proportionally calculated, but I'm trying to add the adjusted populations for each fragmented block back into the zone. The row for each census block fragment has the corresponding FID for which of the 600 zones it falls in.

My goal is to be able to automate adding the values of the adjusted populations together, because I don't want to do it by hand. I was able to create a dictionary that has the count of how many times each zone is represented in the census block table, and how many times each census block is represented as well (blockvalue and zonecount). I assumed I would be able to use this to know how many rows to run through when trying to add, i.e. for zone 1, add the data from 15 rows, then for zone 2, only 6 rows, and so on.

However, now that I'm trying to actually update the original zone attribute table with the number of fragmented blocks in it, it keeps giving me an error:

KeyError Traceback (most recent call last) In [2]: Line 37: cb_count = zonecount[zoneno] KeyError: ('060790130001097', -1, 0)

I know the first number corresponds to a GEOID for a census block, however I have no idea what the -1 and 0 numbers are because there are no zonecounts = -1.

Am I trying to do too much?

Is there an easier way for me to be able to automate adding all of these back together?

I tried to do the "union" geoprocessing tool but it didn't add any of my values and I didn't think it would be able to sum them.

Is there a way to code something where I can do the "union" tool and have it add a specific column together?

    for geoid, fid, adjpop in rows:
        adjustedzones = {(geoid, adjpop) in rows}
        block_zone = fid
            #add the census pop total for each zone
            #for fid in row, pop = adjpop in row + adjpop in row 
        blockvalue = {}
with arcpy.da.SearchCursor(blocks, ["GEOID"]) as cursor: 
#counting number of block sections/rows in zone
    for row in cursor:
        blockcount = row[0]
        #blockcount = geoid 
        if blockcount in blockvalue:
            blockvalue[blockcount] += 1
            #adds 1 every appearance 
        else:
            blockvalue[blockcount] = 1
zonecount = {}
with arcpy.da.SearchCursor(blocks, ["FIDzone"]) as cursor: 
#counting number of appearances for each block
    for row in cursor:
        zoneno = row[0]
        if zoneno in zonecount:
            zonecount[zoneno] += 1
        else:
            zonecount[zoneno] = 1
with arcpy.da.UpdateCursor(evaczones, ["fidadj", "cbcount"]) as curs:
    for zone_fidadj, cb_count in curs: 
        for zoneno in rows:
            #for each individual zone 
            if zoneno != -1:
                cb_count = zonecount[zoneno]
                print(f"'{zoneno}' is '{cb_count}'")
                row.updateRow(row)

Answer 1

Instead of all that, let's try using the FREQUENCY Statistics Geoprocessing tool, operating over the BLOCK data, with the zoneID as frequency field and SUM(population) as the computed sum field:

arcpy.analysis.Frequency("blocks", "blocks_zone_sum_of_pop", "FIDzone", "adjpop")

Then you can JOIN that resulting table back to your Zones Features and Calculate Field from join table to base table.

More detailed arcpy examples are given in the online docs for FREQUENCY, JOIN, and CALCULATE FIELD.

Remember, you can try this manually until you get the correct inputs and outputs, then copy the python from your Geoprocessing History pane, then assemble and parameterize a proper python script once you have confidence that the tools are behaving as expected.