ArcGIS Pro – Populate NULL from Existing Value from Another Record

arcgis-desktoparcgis-profield-calculatorpython-parser

I'm using ArcGIS Pro on the table below (ACCT_ID field is sorted). The goal is to calculate/populate the "Asset" field where if rows have the same "ACCT_ID" and at least one of these rows has "Asset" is not null, calculate the null Asset field(s) with the same Asset that has a matching ACCT_ID.
For example, for the four ACCT_IDs of 1976550000, calculate Asset to be 00070925 for the three remaining nulls.
Or for the two ACCT_IDs of 3988856629, calculate Asset 00146135 for the null record
Or for the two ACCT_IDs 366804000, leave Asset null.
I assume this is best done using field calculator but not sure how to go about it.

Table

Best Answer

The field calculator loops over records in order, so there's no way to know what the first <Null> should be for a given ID if a valid value hasn't been seen yet. It would take two loops over the data, once to determine the mapping and then another to fill the nulls. So this would be better suited to python cursors than the field calculator.

Here's a snippet as an example. Has not been tested, so back up your data if necessary:

fc = r'C:\example.gdb\the_feature_class'
fields = ['ACCT_ID', 'Asset']
lookup = {}

with arcpy.da.SearchCursor(fc, fields) as cursor:
    for acct_id, asset in cursor:
        if asset:
            lookup[acct_id] = asset

with arcpy.da.UpdateCursor(fc, fields) as cursor:
    for acct_id, asset in cursor:
        if not asset:
            try:
                asset = lookup[acct_id]
            except KeyError:
                print('No asset for "{}"'.format(acct_id))
            else:
                cursor.updateRow([acct_id, asset])

This assumes one unique asset per acct_id.

Related Question