Help to calculate a field from duplicate record

Question

I have a polygon feature class (19,664 features) that approximately half of are duplicates. One of each is mostly null but has one attribute that I need to populate the other with before I delete the mostly null feature. (This happened as a result of combining two different feature classes during a gdb restructure migration.)

The Room ID matches for the duplicate features and the number in Room Name is what I need to replace the "ROOM NAME" text values. Some Room ID's are repeated more than twice, so there is more than one record to change sometimes. I'm pretty new to Python so I've been trying a few things but nothing has worked yet. Any help or tips would be greatly appreciated. I'm using ArcMap 10.6.1

JohannesLindner · Answer

NOT TESTED AT ALL, TEST FIRST ON A COPY OF YOUR FC!

fc = "path:/to/your/fc"

# the screenshot you shared shows the field aliases (they have spaces).
# you need to use the actual field names in this script!

# get the correct room names as dict {room_id: room_name}
cursor = arcpy.da.SearchCursor(fc, ["RoomID", "RoomName"], "RoomName <> 'ROOM NAME'")
room_dict = dict([row for row in cursor])

# go through each wrong record an insert the right room number
# notice the different SQL query!
with arcpy.da.SearchCursor(fc, ["RoomID", "RoomName"], "RoomName = 'ROOM NAME'") as cursor:
    for row in cursor:
        try:
            name = room_dict[row[0]]
            cursor.updateRow([row[0], name])
        except KeyError:
            print("No RoomNumber found for RoomID {}".format(row[0]))

SukhwinderSingh · Answer

I suggest to apply query on this and select all the Room selector fields. Afterwards go to field calculator and in arcade/python, put value what ever you like or calculate as you desire. I think you can also edit it after importing table into csv and then apply filter and then make require editing's. I hope this will help you out.

danretired · Answer

@_Kara_ it depends on the table.  Technically the results of identifying the duplicates into a separate file would allow one to split that table into two parts... the parts that you want to keep and the records that you don't.  You would have two tables then and you should be able to join the tables to keep the bits you want.

In your simple example, a query on the RoomName field which has just Room Name as an entry would be simple.  All those records could be saved to a new file.  Then Switch Selection in the table and save to another file.  You then have 2 tables and you could join the 2 together using the room_id field.  What you do with it after that might suggest other changes