Mass updating feature attributes from new stand alone table

631
1
Jump to solution
11-23-2022 08:47 AM
AustinAverill
New Contributor II

I am currently working with a government agency's data that is published to download in ZIP folders. These resources are outlined below.

Resources: 

  • Shapefile for parcels containing geometry and all attribute information - updated annually. 
  • Standalone table with no geometry containing all attribute information - updated monthly.

Seeking Solution:

The shapefile contains all the applicable  geometry that I will need, however the shapefile is only republished once per year. Despite this, the attribute information that is associated with the shapefile is published one per month as a standalone table. How can I use this table in ArcGIS Pro to update all attribute values of the shapefile layer containing the geometry?

 

0 Kudos
1 Solution

Accepted Solutions
VinceE
by
Occasional Contributor II

Using Join and Field Calculator works, but would be far too tedious and error prone in my opinion, but that might depend on your jurisdiction's parcel dataset complexity. I would use Python and Search/Update Cursors. More flexible long-term, and avoids having to manually use Field Calculator on a ton of fields.

Let's assume "ACCOUNT" is the common field name between the Geometry shapefile and the attribute update table. "PARCEL" is the original geometry table, "PARCEL_TBL" is the published CSV of tabular updates, and "PARCEL_COPY" is the updated version. Note that only the intended rows are modified, and any geometry-specific fields can be left alone during the update.

This will not ADD new records--you'd have to write that into the code, or first manually draw in the geometry with an ACCOUNT ID into the geometry shapefile.

VinceE_0-1669233169352.png

This script reads through the update table and makes updates where appropriate in a COPY of the geometry table.

import arcpy

# Overwrite the output copy. Set the working folder (table and shapefiles).
arcpy.env.overwriteOutput = True
arcpy.env.workspace = r"YOUR_DIRECTORY"

# Input datasets.
polygon = "PARCEL.shp"
table = "PARCEL_TBL.csv"

# Unique field. For parcels, assuming ACCOUNT ID or similar.
account_fld = 'ACCOUNT'

# Read relevant fields from each. Ignore various ObjectID/Geometry-type fields.
# Also remove the ACCOUNT ID field, will be added back in for Cursors.
poly_flds = [f.name for f in arcpy.ListFields(polygon)
             if f.type not in ['Geometry', 'OID']
             and f.name.lower() not in ['shape_area', 'shape_length'
                                        'shape.starea(), shape.stlength()']]
tbl_flds = [f.name for f in arcpy.ListFields(table)
            if f.name != account_fld]

# Get fields that are common to both the Geometry and the Update Table.
common_flds = list(set(poly_flds) & set(tbl_flds))

# Read the update table. Use the ACCOUNT ID as a dictionary key.
with arcpy.da.SearchCursor(table, [account_fld] + common_flds) as scurs:
    fc_dict = {row[0]:row[1:] for row in scurs}

# Make a copy of the original Geometry shapefile.
# This could be modified so that the existing is archived as a backup, and then
#   the current is updated.
poly_copy = f"{polygon.split('.')[0]}_COPY.shp"

# If the Account in the Geom shapefile requires updates (based on ACCOUNT ID),
#   update the row.
arcpy.management.CopyFeatures(polygon, poly_copy)
with arcpy.da.UpdateCursor(poly_copy, [account_fld] + common_flds) as ucurs:
    for acct, *row in ucurs:
        if acct in fc_dict.keys():
            print(f"OLD ROW: {[acct, *row]}")
            ucurs.updateRow([acct, *fc_dict[acct]])
            print(f"UPD ROW: {[acct, *fc_dict[acct]]}\n")

 

View solution in original post

1 Reply
VinceE
by
Occasional Contributor II

Using Join and Field Calculator works, but would be far too tedious and error prone in my opinion, but that might depend on your jurisdiction's parcel dataset complexity. I would use Python and Search/Update Cursors. More flexible long-term, and avoids having to manually use Field Calculator on a ton of fields.

Let's assume "ACCOUNT" is the common field name between the Geometry shapefile and the attribute update table. "PARCEL" is the original geometry table, "PARCEL_TBL" is the published CSV of tabular updates, and "PARCEL_COPY" is the updated version. Note that only the intended rows are modified, and any geometry-specific fields can be left alone during the update.

This will not ADD new records--you'd have to write that into the code, or first manually draw in the geometry with an ACCOUNT ID into the geometry shapefile.

VinceE_0-1669233169352.png

This script reads through the update table and makes updates where appropriate in a COPY of the geometry table.

import arcpy

# Overwrite the output copy. Set the working folder (table and shapefiles).
arcpy.env.overwriteOutput = True
arcpy.env.workspace = r"YOUR_DIRECTORY"

# Input datasets.
polygon = "PARCEL.shp"
table = "PARCEL_TBL.csv"

# Unique field. For parcels, assuming ACCOUNT ID or similar.
account_fld = 'ACCOUNT'

# Read relevant fields from each. Ignore various ObjectID/Geometry-type fields.
# Also remove the ACCOUNT ID field, will be added back in for Cursors.
poly_flds = [f.name for f in arcpy.ListFields(polygon)
             if f.type not in ['Geometry', 'OID']
             and f.name.lower() not in ['shape_area', 'shape_length'
                                        'shape.starea(), shape.stlength()']]
tbl_flds = [f.name for f in arcpy.ListFields(table)
            if f.name != account_fld]

# Get fields that are common to both the Geometry and the Update Table.
common_flds = list(set(poly_flds) & set(tbl_flds))

# Read the update table. Use the ACCOUNT ID as a dictionary key.
with arcpy.da.SearchCursor(table, [account_fld] + common_flds) as scurs:
    fc_dict = {row[0]:row[1:] for row in scurs}

# Make a copy of the original Geometry shapefile.
# This could be modified so that the existing is archived as a backup, and then
#   the current is updated.
poly_copy = f"{polygon.split('.')[0]}_COPY.shp"

# If the Account in the Geom shapefile requires updates (based on ACCOUNT ID),
#   update the row.
arcpy.management.CopyFeatures(polygon, poly_copy)
with arcpy.da.UpdateCursor(poly_copy, [account_fld] + common_flds) as ucurs:
    for acct, *row in ucurs:
        if acct in fc_dict.keys():
            print(f"OLD ROW: {[acct, *row]}")
            ucurs.updateRow([acct, *fc_dict[acct]])
            print(f"UPD ROW: {[acct, *fc_dict[acct]]}\n")