topic Re: Arcpy - search duplicate attributes, then update values in Python Questions

Arcpy - search duplicate attributes, then update values

timdunlevie — Fri, 30 Jan 2015 04:10:24 GMT

Hi all,

Could someone point me in the right direction.

I have limited python scripting experience but know a enough (to be dangerous).

This is what I want to do.

I have a shapefile which has duplicate objects based on an "ID".

Object1: ID1 + Owner1

Object2: ID1 + Owner2

Object3: ID1 + Owner3

in this example there are 3 objects with identical ID but with 3 different owners for the same object.

What I want to do is search through the shapefiles for all identical "ID", then update a new shapefile with :

Object1: ID1 + Owner1 + Owner2 + Owner3

The new shapefile would have 3 extra columns added for the Owner field to be updated to.

so I want 1 object only but keep the owner information in the row.

thanks,

Tim

Re: Arcpy - search duplicate attributes, then update values

AnthonyGiles — Fri, 30 Jan 2015 08:55:10 GMT

Tim,

Have you had a look at the find identical tool

ArcGIS Help 10.1

I would run the tool to find features that have duplicates and then with the created table use this to help populate your new shape file

Re: Arcpy - search duplicate attributes, then update values

BlakeTerhune — Fri, 30 Jan 2015 16:43:51 GMT

So what would happen if the same ID had five different owners? Or ten different owners? You'll just have to keep increasing the number of columns to accommodate the most duplicated ID. Sounds like these are not duplicates but rather you have two key fields. I wouldn't try making extra columns for the additional owners because it will get messy. Is there some kind of dependency requiring you to format your data like this?

In your example, do each of the three objects also have different geometry/location? If the geometry is the same, maybe you could consider using a related table so you can have one object with one ID related to an owner table that has three records with the same ID and each different owner.

Re: Arcpy - search duplicate attributes, then update values

GeoffOlson — Sat, 11 Dec 2021 23:12:39 GMT

I'm in the same boat as you with being a novice with Python scripting. My lastest phase of attribute manipulation has been with using dictionaries to use a one (key) to multiple (value list) data entry. I dont' know how you have your data structured, but using dictionary indices can access the particular values you want. You could probably create a row dictionary and update a dictionary of the dataset to reference for assigning the owners where you want the information to go.

rowDict = {rowID : [Owner1, Owner2, Owner3]}

fullDict.update(rowDict)

Then you can pull your owners out again

fullDict.keys().index(rowID)#this returns the index location of the key

fullDict.values()[fullDict.keys().index(rowID)]#this returns the owners for the row

You can pull each owner from the returned list about and an additional index

fullDict.values()[fullDict.keys().index(rowID)][1]

Here's an example

rowID = 76
Owner1 = "John"
Owner2 = "Sally"
Owner3 = "George"
rowDict = {rowID : [Owner1, Owner2, Owner3]}
fullDict = dict()
fullDict.update(rowDict)#updates the row of information to the full dictionary

fullDict.keys().index(rowID)#returns the owners' names

fullDict.keys().index(rowID)[1]#returns the owner's name of the second owner in the list

the second owner in the list

If the owners names are in separate objects, it's possible to update the dictionary to add the other owners names. The only way to do that is with dict.update() and the new dictionary values has to have the same key as what it's overwriting and the values then need to be a list of the owners from all the objects.

a.update({a.keys()[indexForRow_ID_For_A]:[a.values()[indexForRow_ID_For_A], b.values()[indexForRow_ID_For_B], c.values()[indexForRow_ID_For_C]]})

This would take the key value from A and update the values with the values from A, B, and C.

Like I said, my knowledge of Python is still in the novice section. If I understand what your trying to do, then perhaps dictionaries can help. I hope this helps in some way.

Re: Arcpy - search duplicate attributes, then update values

ChrisPedrezuela — Sun, 01 Feb 2015 20:36:19 GMT

I think Blakes suggestion is best for this kind of data scenario. But if you still want to keep the kind of data structure you have, which is stacked polygons due to multiple owner, I don't get why the need to go through the process of modifying your data. If you are just after a report like info of how many and who are the owners of each polygon based on ID, you just write a script to spit out a csv file which does that summary and leave your data intact. That's just my two cents.

Re: Arcpy - search duplicate attributes, then update values

timdunlevie — Mon, 02 Feb 2015 01:26:06 GMT

Thanks for the replies.

In this case Blake, there will only ever be 9 Owners (max).

The way I was thinking would be to create a new table with the structure I want.

Loop thru the data to get a list of Unique ID+Owner

Then populate the Owners field in the new table with the list of data.

The same IDs have identical geometry.

Thanks,

Tim

Re: Arcpy - search duplicate attributes, then update values

timdunlevie — Mon, 02 Feb 2015 01:28:30 GMT

Thanks Geoff,

I’ll have a play around with this approach!

Yeah I was hoping to loop through the data and create a “list” of ID+Owner for the dataset.

Then update a new field using this data.

Thanks,

Tim

Re: Arcpy - search duplicate attributes, then update values

BlakeTerhune — Sat, 11 Dec 2021 23:12:41 GMT

At first you mentioned you want the results in a shapefile but now you're saying you want a "list." If some kind of printed report is really what you're after, you could try this:

import arcpy

inputTable = r"C:\temp\myworking.gdb\mytablename"
fields = ["ID","OWNER"]

uniqueID = {}

with arcpy.da.SearchCursor(inputTable, fields) as s_cursor:
    for row in s_cursor:
        # Underscore in front of variable names only because "id" is a Python reserved word
        _id = row[0]
        _owner = row[1]
        uniqueID.setdefault(_id, []).append(_owner)

for _id, _owners in uniqueID.items():
    print _id, _owners

It uses an arcpy data access search cursor to read through your data table one row at a time. With each row, it loads the ID and owner into a dictionary (like Geoff Olson mentioned below). However, as each row is loaded, the dictionary setdefault method checks to see if that ID has already been loaded. If it has not, it will create a new dictionary key for that ID and an empty list for the dictionary value. The list append method is the one that actually adds the owner to the list. You're left with a dictionary that has keys of only unique IDs and values as a list of owners. It just prints the results to the Python interpreter window. You could also write the data to a CSV or a new feature class or shapefile.

Here's the credit for the logic I used to populate the dictionary.

Re: Arcpy - search duplicate attributes, then update values

XanderBakker — Sat, 11 Dec 2021 23:12:44 GMT

This is what I came up with:

Upper table is the input featureclass, the lower table is the result (cleaned)

Using this code:

def main():
    import arcpy
    import os

    fc_in = r"D:\Xander\GeoNet\RemoveDuplicates\test.gdb\Parcels"
    fc_out = r"D:\Xander\GeoNet\RemoveDuplicates\test.gdb\Parcels_cleaned"
    fld_id = "ID"
    fld_owner = "OWNER"
    sr = arcpy.Describe(fc_in).spatialReference

    # create empty output fc
    fc_ws, fc_name = os.path.split(fc_out)
    arcpy.CreateFeatureclass_management(fc_ws, fc_name, template=fc_in, spatial_reference=sr)

    # create dictionary with ID's te keep and related owners
    flds = (fld_id, fld_owner)
    dct = {}
    with arcpy.da.SearchCursor(fc_in, flds) as curs_in:
        for row_in in curs_in:
            p_id = row_in[0]
            p_owner = row_in[1]
            if p_id in dct:
                dct[p_id] += ", {0}".format(p_owner)
            else:
                dct[p_id] = p_owner
    del row_in, curs_in

    # now do the update:
    flds = correctFieldList(arcpy.ListFields(fc_out))
    with arcpy.da.InsertCursor(fc_out, flds) as curs_out:
        with arcpy.da.SearchCursor(fc_in, flds) as curs_in:
            # flds_in = curs_in.fields
            for row_in in curs_in:
                lst_row = list(row_in)
                p_id = row_in[flds.index(fld_id)]
                if p_id in dct:
                    owners = dct[p_id]
                    lst_row[flds.index(fld_owner)] = owners
                    print lst_row
                    # remove from dct
                    tmp = dct.pop(p_id)
                    curs_out.insertRow(tuple(lst_row))

def correctFieldList(flds):
    flds_use = ['Shape@']
    fldtypes_not = ['Geometry', 'Guid', 'OID']
    for fld in flds:
        if not fld.type in fldtypes_not:
            flds_use.append(fld.name)
    return flds_use

if __name__ == '__main__':
    main()

Re: Arcpy - search duplicate attributes, then update values

timdunlevie — Tue, 03 Feb 2015 04:16:10 GMT

many thanks!

this works.

The only issue I had was the original shp file "owner" field had a size of 60 characters...

So when the update occurred on the new field (60 characters) it was truncating the result.

Is there a way to alter the field sizes of an existing field in arcpy?

or do you have to create a new field?

thanks to all those who replied.

Tim

Re: Arcpy - search duplicate attributes, then update values

XanderBakker — Tue, 03 Feb 2015 11:45:16 GMT

Good question... I don't think so.

The Alter Field tool that was released not so lng ago only allows you to change the name and alias of an existing field. However, you could delete the field, after the empty output featureclass is created and add the field with the same name, but with a larger length. before you start inserting the information. The downside is that it will change the order of the fields and the way to construct the output row would be a little more complex.

Re: Arcpy - search duplicate attributes, then update values

XanderBakker — Tue, 03 Feb 2015 11:47:59 GMT

If you don't mind the changed order of the fields, you could:

rename the existing OWNER field using Alter Field (ArcGIS Help (10.2, 10.2.1, and 10.2.2) ). eg "OWNER_old"
next add a new field with the name OWNER with the larger length
fill the new OWNER field with the values the "OWNER_old" field (Field Calculator)
Delete the "OWNER_old" field
then run the script

Re: Arcpy - search duplicate attributes, then update values

AnthonyGiles — Tue, 03 Feb 2015 15:01:19 GMT

Tim,

You could use the feature class to feature class tool:

ArcGIS Help 10.1

In the field map parameters right click on your owner field and extend the size. Once created delete your original feature class and rename your new one to the same as your old one

Regards

Anthony

Re: Arcpy - search duplicate attributes, then update values

BlakeTerhune — Tue, 03 Feb 2015 15:10:53 GMT

What is the purpose of removing (and returning) the item from the dictionary just before you write it to the shapefile? I don't see the "tmp" variable used anywhere else.

tmp = dct.pop(p_id)

Re: Arcpy - search duplicate attributes, then update values

XanderBakker — Tue, 03 Feb 2015 16:02:39 GMT

The reason is, when I ommit this, it wil write the equal amount of features to the output. Removing it from the dictionary will cause the condition on line 36 to return False (and not add an ID that has already been added).

If you need any additional explanation on the code, just let me know.

Kind regards, Xander

Re: Arcpy - search duplicate attributes, then update values

BlakeTerhune — Tue, 03 Feb 2015 16:25:09 GMT

Thanks Xander, I see it now. I'm just trying to understand the code and your process.

So you are iterating through the fc_in rows and then checking if the current ID is in the dictionary before writing the data to fc_out. I was thinking it might be easier to iterate through the dictionary instead of fc_in so you wouldn't have to remove the dictionary item but then I don't know how you would find the correct row in fc_in to copy the data in the rest of the fields. Is there a way to do that? Seems like it could be a faster because you wouldn't have to touch every row of in_fc; you're done after checking every item in the dictionary.

Re: Arcpy - search duplicate attributes, then update values

XanderBakker — Tue, 03 Feb 2015 16:32:24 GMT

I see your point. However, I'm using the da cursors which is rather fast. Using the dictionary as basis, you would have to extract the corresponding feature from fc_in. This would require creating a cursor with probably a where clause on oid to extract the feature you're interested in. I think this would create a script that perfoms slower than what I am doing...

Re: Arcpy - search duplicate attributes, then update values

BlakeTerhune — Tue, 03 Feb 2015 16:38:38 GMT

Ah, yes, the where clause when you create the cursor! But then you would have to use a dynamic where clause and create a new cursor each time. I agree with you then, it would be silly to do that compared to just popping the item from the dictionary.

Thanks for the clarification Xander!

Re: Arcpy - search duplicate attributes, then update values

XanderBakker — Tue, 03 Feb 2015 17:05:18 GMT

but you are right, it is possible to fill a dictionary with the features from the first search cursor and use the dictionary during the insert cursor. This might become a problem for large datasets...

Re: Arcpy - search duplicate attributes, then update values

XanderBakker — Sat, 11 Dec 2021 23:12:46 GMT

And this could be the code with a cursor less and an additional dictionary (I think it is a little faster, but I didn't test for speed):

def main():
    import arcpy
    import os

    fc_in = r"D:\Xander\GeoNet\RemoveDuplicates\test.gdb\Parcels"
    fc_out = r"D:\Xander\GeoNet\RemoveDuplicates\test.gdb\Parcels_cleaned"
    fld_id = "ID"
    fld_owner = "OWNER"

    sr = arcpy.Describe(fc_in).spatialReference
    fld_oid = arcpy.Describe(fc_in).OIDFieldName

    # create empty output fc
    fc_ws, fc_name = os.path.split(fc_out)
    arcpy.CreateFeatureclass_management(fc_ws, fc_name, template=fc_in, spatial_reference=sr)

    # create dictionary with ID's and related owners and dictionary with rows
    flds = correctFieldList(arcpy.ListFields(fc_in))
    dct_owner = {}
    dct_ids2use = {}
    with arcpy.da.SearchCursor(fc_in, flds) as curs_in:
        for row_in in curs_in:
            p_id = row_in[flds.index(fld_id)]
            p_owner = row_in[flds.index(fld_owner)]
            if p_id in dct_owner:
                dct_owner[p_id] += ", {0}".format(p_owner)
            else:
                dct_owner[p_id] = p_owner
                dct_ids2use[p_id] = list(row_in) # just register the first encounter of the id
    del row_in, curs_in

    # now do the update:
    with arcpy.da.InsertCursor(fc_out, flds) as curs_out:
        for p_id, lst_row in dct_ids2use.items():
                lst_row[flds.index(fld_owner)] = dct_owner[p_id]
                curs_out.insertRow(tuple(lst_row))

def correctFieldList(flds):
    flds_use = ['Shape@']
    fldtypes_not = ['Geometry', 'Guid', 'OID']
    for fld in flds:
        if not fld.type in fldtypes_not:
            flds_use.append(fld.name)
    return flds_use

if __name__ == '__main__':
    main()