10

I have attribute data with owner names. I need to select data that contains the last name twice.

For example, I may have an owner name that reads "BENNETT MCCARL & ARNETTE BENNETT".

I would like to select any rows in the attribute table that have a reoccurring last name such as the example above. Does anyone know how I can go about selecting that data?

  • What GIS are you using? Is Python an option? – Aaron Feb 2 '16 at 23:05
  • 3
    This distils to a Python question that I think you will find the Python code for by researching/asking on Stack Overflow. – PolyGeo Feb 2 '16 at 23:12
  • 1
    Is this a list of last names or two people, one named Bennett McCarl and another Arnette Bennett? It appears that one person has a Bennett first name and another has a Bennett last name? – Aaron Feb 2 '16 at 23:19
  • 1
    To do this I think you need to count the unique words in your string, and if it is less than the number of words in your string then there is at least one word duplicated. Distinguishing words which are or may be surnames from other words will be a separate exercise. I think you should edit your question here to make your precise requirements clearer, and combine that with Python research at Stack Overflow. – PolyGeo Feb 2 '16 at 23:28
  • 1
    I've revised your question at stackoverflow.com/questions/35165648/… because it was phrased in "ArcGIS-speak" rather than "Python-speak". Hopefully, it won't get too many downvotes while awaiting my edit to be approved. – PolyGeo Feb 3 '16 at 0:16
2

Field calculator expression based on this answer

def cw(sentence):
 words = sentence.split()
 counts = {}
 nMax=0
 for word in words:
    if word not in counts:counts[word] = 0
    counts[word] += 1
    nMax=max(nMax, counts[word])
 return nMax

'==================================================

cw( !MUID!)

It will return maximum count of same words in the string

8

You can use the Python collections module and an Update Cursor to accomplish this. This method adds a new field and populates it with a 1 if there are any duplicates, otherwise a 0 if there are no duplicates.

import arcpy, collections

shp = r'C:\temp\names.shp'

# Add a field called "check" to store binary data.

arcpy.AddField_management(shp, field_name = "check", field_type = "SHORT")

# Use an Update Cursor to query the table and write to new rows
# 1 = has duplicates
# 0 = no duplicates
with arcpy.da.UpdateCursor(shp, ["last_names", "check"]) as cursor:
    for row in cursor:
        names = row[0].replace("&", "").split() # Clean the string
        counts = collections.Counter(names) #create dictionary to count occurrences of words
        if any(x > 1 for x in list([count for name, count in counts.items()])):
            row[1] = 1
        else:
            row[1] = 0
        cursor.updateRow(row)

enter image description here

  • 1
    Nice use of this collections module, never knew it existed. I was thinking on the same lines but using regular expressions to extract out repeating words to create a flag field. I think your approach using the counter function is much easier to understand. I will be checking out that module later! – Hornbydd Feb 3 '16 at 8:49
6

What about using re and set and setting a flag ( here 0 and 1) in python- re will extract all the names (last and first) from BENNETT MCCARL & ARNETTE BENNETT without &. For pattern matching re is of highest priority- you can use re how you want.

import re
def sorter(val):
    words = re.findall(r'\w+',val)
    uniques = set(words)
    if len(words)>len(uniques):
        return 1
    else:
        return 0

And call sorter( !N! )


demo


**See how regex grabs words at LIVE DEMO

Note that all of these answers deal the problem supposing that your data is sanitized i.e. have proper space between words but what if your data is something like BENNETTMCCARL&ARNETTEBENNETT then all these would fail. In that case you may need to use Suffix Tree algorithm and fortunately python has some library as here.

  • regex to the rescue, nice! – Hornbydd Feb 3 '16 at 16:52
2

Assuming your source data is a FeatureClass/Table in a File GeoDatabase then the following query will select the rows you require:

SUBSTRING(name FROM 1 FOR 7) = 'BENNETT' AND SUBSTRING(name FROM (CHAR_LENGTH(name) - 6) FOR 7) = 'BENNETT

name is the field, I just happened to call it name. The first part is testing the left hand side the second part is testing the right. This query is obviously hard coded to search for BENNETT, if you need to select by other surnames hopefully you can work out what needs changing?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.