How to recode and recalculate Field Values using ArcPy?

Question

I am trying to figure out how to do several calculations through python and the arcpy set. I have an large dataset (7+ million records of individuals), and I am trying to code a value of 1 that denotes if the individuals are married.

Now I do not have marriage status, but I might be able to get it through following through this logic...

1. I have last name and address, I can create a new field that has this new identifier and determine if more than one person with the same last name lives at that address.
2. I have Age, so I could subtract the highest value from the second highest value to get the difference, and if that difference is say less than 15 years, assume they are married.
3. I do not have gender, but I do have first name. I have a large dataset of first names to determine gender, but there are too many names that are for both men and women, and I want to stay away from this as this process will be too involved.

So I can create the new field easy through update cursor. I dissolve it and join it back to the data set to get the number of people with eh same last name at an address. But after this, I am running into the my issues.

I would like to subtract the largest number that has the same id field, from the second highest number. But I am not sure how to write/code that in python to run it in ArcGIS. Is this even the best solution to solving this problem? I am welcome to all suggestions to help me get through this problem!!!!

Answer 1

Here's what I would try... since you have the age you can use that as a filter for determining whether or not they are "eligible" to be married... we'll use 18 and over...

• Create a string field named HASH

• Make the HASH field a combination of several other fields, like LAST_NAME + ADDRESS + CITY + STATE. I would write it something like this to get rid of all spaces and change everything to Upper case (NOTE: Only calculate the HASH field if the AGE field is >= 18...)

  ("".join(LAST_NAME.split()) + '-' + "".join(ADDRESS.split()) + '-' + "".join(CITY.split()) + '-' + "".join(STATE.split())).upper()
  

Then your HASH field would look something like this... SMITH-1234BROADWAYST-NEWYORKCITY-NEWYORK

Now you have HASH values for everyone 18 and up. Now you can use something like this...

import arcpy

fc = "C:/YourFileGeoDatabase.gdb/Dataset/YourFeatureClass"

where_clause = '"HASH"' + " <> ''"
sort_fields = "HASH A, FIRST_NAME A"

cursor = arcpy.UpdateCursor(fc, where_clause, "", "", sort_fields)
prevFieldValue = ""
for row in cursor:
    if row.getValue("HASH") == prevFieldValue:
        row.setValue("MARRIED", "YES")
        cursor.updateRow(row)

    prevFieldValue = row.getValue("HASH")

The idea is to sort the records based on the hash, then mark the record as MARRIED if the record before it had the same HASH. Note that this only marks the 2nd person as married, though. To mark the first person as married, you would run the code again, but change the sort order of the first name...

sort_fields = "HASH A, FIRST_NAME D"

It's important that you don't set a value if the previous HASH does not equal the current HASH so that when you run the code the 2nd time it doesn't overwrite any values you had calculated the first time.

DISCLAIMER: When I tried to run this code on some sample data it didn't seem to sort properly, so make sure to test the code before running it on 7 million records...

Answer 2

Well, it's far out of date now, but here's my two cents: I would use a dictionary to achieve this, though admittedly I don't have experience using dictionaries with such a high volume of records.

dictionary = {}
for row in cursor....
    address_key = street number + street name + zip
    last_name = last name field value
    age = age field value
    if address_key in dictionary.keys():
        if dictionary[address_key][0] == last_name:
            dictionary[address_key][1].append(age)
            continue
    dictionary[address_key] = (last_name,[age])

couples = 0
for k,v in dictionary.iteritems():
    ages = v[1]
    if len(ages) <= 1:
        continue
    ages.sort().reverse
    if abs(ages[0] - ages[1]) <= 15:
        couples+=1

Code is untested of course, but that's the overall concept: while iterating through the rows, create a dictionary whose keys are the unique address strings and whose values are a tuple. The tuple[0] is the last name and tuple [1] is a list of ages. If the cursor encounters a last name that is already listed at a given address, the age is appended to the list of ages. At the end, just run through the age lists and compare the top two numbers; if they are 15 or less apart, + 1 for the couples counter.

This doesn't take into account multiple couples with the same address, and also doesn't allow for the instances of the same street address in a given zip code... could use a little work...