Help to calculate a field from duplicate record

195
5
12-30-2021 12:06 PM
_Kara_
by
New Contributor

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.)

KaraPrather_0-1640894446837.png

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

0 Kudos
5 Replies
DanPatterson
MVP Esteemed Contributor

Find Identical (Data Management)—ArcGIS Pro | Documentation

Have you looked at identifying them and see if trying this on a copy of your gdb would work?

Delete Identical (Data Management)—ArcGIS Pro | Documentation

Exclude your Room Name column in the comparison


... sort of retired...
0 Kudos
_Kara_
by
New Contributor

I haven't, I will look into those, thank you. Have you used those before and been able to preserve a field value from the records being deleted?

0 Kudos
DanPatterson
MVP Esteemed Contributor

@_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


... sort of retired...
0 Kudos
SukhwinderSingh
New Contributor II

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.

0 Kudos
JohannesLindner
MVP Regular Contributor

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]))

Have a great day!
Johannes
0 Kudos