topic Re: Split by Attribute in Python Questions

Split by Attribute

JohnDye — Wed, 01 May 2013 17:19:04 GMT

I have a Feature Class with 3.2 million records which represents customers. Each customer is assigned to a particular store ID (DID)

So I'm trying to split the customer record into store ID datasets. That is, for each store ID (DID), create a dataset of the customers assigned to it, essentially splitting the FC by each unique attribute in the field.

I was able to pull up a list of the unique store IDs by using:

def Unique_Values(table, field):     with arcpy.da.SearchCursor(table, [field]) as cursor:         return sorted({row[0] for row in cursor})  fc = "C:\Users\username\Documents\ArcGIS\Default.gdb\MyCustomers" field = "DID"  UniqueValues (fc, field)

which resulted in a list of all of my unique store IDs, 957 in all.

What I'm not sure of however, is how to go about iterating through that list and using each value in the list in a where_clause in a "FeatureClassToFeatureClass_conversion" to export each unique dataset.

I know there are third party developed tools to do this, but they don't seem to work due to the number of records. I've tried every single one I could find.

Re: Split by Attribute

ChrisSnyder — Wed, 01 May 2013 17:39:26 GMT

How about something like:

storeIdSet = set([r[0] for r in arcpy.da.SearchCursor(myFC, ["STORE_ID"])]) for storeId in storeIdSet:      outFC = r"C:\temp\test.gdb\store_" + str(storeId)      arcpy.Select_analysis(myFC, outFC, "STORE_ID = " + str(storeId))

Re: Split by Attribute

curtvprice — Sat, 11 Dec 2021 13:12:12 GMT

I was able to pull up a list of the unique store IDs by using:
def Unique_Values(table, field):
    with arcpy.da.SearchCursor(table, [field]) as cursor:
        return sorted({row[0] for row in cursor})

fc = "C:\Users\username\Documents\ArcGIS\Default.gdb\MyCustomers"
field = "DID"

UniqueValues (fc, field)
which resulted in a list of all of my unique store IDs, 957 in all.

Neat. I may steal that.

What I'm not sure of however, is how to go about iterating through that list and using each value in the list in a where_clause in a "FeatureClassToFeatureClass_conversion" to export each unique dataset.

Note, you could avoid scripting altogether and do this in ModelBuilder using Iterate Feature Selection.

But, if you want to do this in Python, it's a matter of making a layer and selecting on it:

lyr = arcpy.CreateFeatureLayer(management(fc, "lyr")
stores = UniqueValues(lyr, field)
for store in stores:
    where = "{0} = {1}".format(field, store)
    outFC = "store{0}".format(store)
    arcpy.Select_analysis(lyr, outFC, where)

UPDATE: Chris beat me to the post. Nice one-liner, Chris!

Just a note about my more verbose approach: If a GP tool gets a layer input, it can assume that the input exists and its field list is already in memory ready to roll. This can make a difference if you need to do 957 select/copy operations.

Re: Split by Attribute

JohnDye — Sat, 11 Dec 2021 13:12:15 GMT

Chris,
I played with this notion earlier but couldn't get it working...

I modified your code a bit to export to a feature dataset and also provide print statements as it ran,
but all in all, right on the money.
Thanks a ton!

It's probably going to take several hours to crunch through all the data but it seems to be chugging
along. The only issue I see is that since I'm running it in the Python Window, it keeps adding each dataset to my TOC. I didnt really expect that. It's not a huge deal because drawing is paused, but nonetheless, something I didnt expect to happen.

So long as it crunches through all the data, I'm going to take a stab at turning it into an Addin later.

For anyone that wants the full code if you're having issues with large datasets, here's what I'm using:

import arcpy
from arcpy import env

# Create Workspace. 'Select_analysis' tool exports to the current workspace, so this is the directory 
# where your exported datasets will end up. I'm dropping mine in a Feature Dataset in the 'Default.gdb'
# on my system.
workspace = arcpy.env.workspace = "C:\Users\Username\My Documents\ArcGIS\Default.gdb\CustomersByStore"

# Create a variable to hold the FC you want to split by attribute.
fc = "C:\Users\Username\My Documents\ArcGIS\Default.gdb\Customers\MyCustomers"

# Instantiate a 'set' of unique values from the 'StoreID' field in the 'fc' dataset using the native 
# python 'set' class. Depending on how large your dataset is, this could take a bit of time.
# Mine was 3.2 million records and only took 3-4 minutes to run.
StoreSet = set([r[0] for r in arcpy.da.SearchCursor (fc, ["StoreID"])])

# Create the 'for' loop that will iterate through your BranchSet by unique ID.
for ID in BranchSet:
 # Create a variable to name each exported record according to the unique value. If your field is an 
 # integer, ensure you convert it to string. For some reason, just converting the integer to string
 # wasn't enough though, I had to also tack on a string ('Customers') as well before the conversion
 # to get it all working. By default, Select_analysis also exports to the current workspace, so if you
 # didn't set one as I did before, you need to hardcode the path here.
 OutFC = "Customers_" + str(DID)
 # Print which recordset the script is currently processing
 print "Exporting " + str(DID) + "..."
 # Export the dataset using the 'Select_analysis' function. Because my variable for 'OutFC' also 
 # holds the variable for my workspace, it drops all the datasets in my workspace. 
 arcpy.Select_analysis(fc, OutFC, "StoreID = " + str(DID))

print "Script Complete."

Re: Split by Attribute

JamesCrandall — Sat, 11 Dec 2021 13:12:17 GMT

How about something like:

storeIdSet = set([r[0] for r in arcpy.da.SearchCursor(myFC, ["STORE_ID"])])
for storeId in storeIdSet:
     outFC = r"C:\temp\test.gdb\store_" + str(storeId)
     arcpy.Select_analysis(myFC, outFC, "STORE_ID = " + str(storeId))

Nice! What a very pythonic way to write. and I am stealing this 🙂

...so tough to remove the ArcObjects from my thinking!

Re: Split by Attribute

JohnDye — Wed, 01 May 2013 19:00:00 GMT

Neat. I may steal that.

Hey Curt,
I thought about doing a MakeFeatureLayer and then running a series of SelectLayerbyAttributes but from my experience, that significantly increases processing time. Not a big deal with small to moderate sized datasets, but with 3.2 million records, I didn't want to play with those implications.

Unfortunately, I can't take credit for the UniqueValues definition either, I lifted it off of an old Cafe Python post. Someone commented on the post about leveraging the TableToNumPyArray function to get better performance, which made me really excited, but alas my feeble mind could not get it to work like I wanted.

Re: Split by Attribute

curtvprice — Wed, 01 May 2013 19:11:04 GMT

Hey Curt,
I thought about doing a MakeFeatureLayer and then running a series of SelectLayerbyAttributes but from my experience, that significantly increases processing time. Not a big deal with small to moderate sized datasets, but with 3.2 million records, I didn't want to play with those implications.

I agree - I fixed the code above so you're just selecting off the layer directly with the Select_analysis tool.

If you added a field index you'd get the time back in spades when you ran the Select tool -- even faster!

Someone commented on the post about leveraging the TableToNumPyArray function to get better performance

Talk about gilding the lily. arcpy.da.SearchCursor is pretty darn fast. It's so fast, I wonder if Esri's team they used a numpy array to do it!

Re: Split by Attribute

JohnDye — Wed, 01 May 2013 19:15:52 GMT

If you added a field index you'd get the time back in spades when you ran the Select tool -- even faster!

That's a good idea, may play with that tomorrow!

ListUnique function

curtvprice — Sat, 11 Dec 2021 13:12:20 GMT

Here's my updated ListUnique script - works with 9x/10x.

Thanks guys, this is a big improvement to what I had before.
A cute tweak I learned that sorted(set()) returns a list.

def ListUnique(inTable,Field):
    """Create a list of unique values from a table/tableview.

    arguments

    inTable  Table or table view
    Field    Field name
    """
    Row, Rows = None, None
    try:
        try:
            # this will only work for 10.1
            import arcpy.da
            lstValues = \
                sorted({r[0] for r in
                    arcpy.da.SearchCursor(inTable, [Field])})
        except:
            import arcgisscripting
            gp = arcgisscripting.create(9.3)
            Rows = gp.SearchCursor(inTable,"","",Field,Field)
            Row = Rows.next()
            lstValues = []
            while Row:
                lstValues.append(Row.getValue(Field))
                Row = Rows.next()
            # unique-ize and sort the list
            lstValues = sorted(set(lstValues))
        return lstValues
    except:
        raise
    finally:
        if Row: del Row
        if Rows: del Rows

Re: Split by Attribute

AmberPerenzin — Sat, 11 Dec 2021 13:12:23 GMT

How about something like:

storeIdSet = set([r[0] for r in arcpy.da.SearchCursor(myFC, ["STORE_ID"])])
for storeId in storeIdSet:
     outFC = r"C:\temp\test.gdb\store_" + str(storeId)
     arcpy.Select_analysis(myFC, outFC, "STORE_ID = " + str(storeId))

Chris, I am getting an error message when I use this script indicating the SQL expression used in the select_analysis tool is invalid. I know how to set the expression to select a specific crime type, but I do not understand how to write an expression that will iterate through each crime type and make a new .shp file for each crime type. How do you write a SQL expression that will iterate through values of a field?

This is how I have modified your code to fit my dataset:

AllCrimes = "CopyCopySample2011_2013.shp"
CrimeTypeSet = set([r[0] for r in arcpy.da.SearchCursor(AllCrimes, ["CrimeType"])])
for CrimeType in CrimeTypeSet:
    out_FC = "H:\\PythonOutput\\" + str(CrimeType)
    arcpy.Select_analysis(AllCrimes, out_FC, "CrimeType = " + str(CrimeType))

the error message reads:

"Executing: Select "H:\Sample Data\CopyCopySample2011_2013.shp" H:\PythonOutput\AgAssault.shp "CrimeType = AgAssault"
Start Time: Wed Apr 23 13:23:04 2014
ERROR 000358: Invalid expression CrimeType = AgAssault
A column was specified that does not exist.
A column was specified that does not exist.
Failed to execute (Select).
Failed at Wed Apr 23 13:23:05 2014 (Elapsed Time: 1.00 seconds)"

I have tried so many things and I am about to throw my computer out the window!! What am I doing wrong?

Re: Split by Attribute

MathewCoyle — Wed, 23 Apr 2014 16:46:41 GMT

Have you tried this tool?

http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=37AEB018-1422-2418-A036-CA6D9920F808

And I imagine the problem with your query is the quotes. Try this using string substitution instead.

"CrimeType = '{0}'".format(CrimeType)

Re: Split by Attribute

AmberPerenzin — Wed, 23 Apr 2014 16:49:24 GMT

Have you tried this tool?

http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=37AEB018-1422-2418-A036-CA6D9920F808

I have--but I need to use the Python script because this is just one of many steps I am programming 😕 the python script for that specific tool is VERY long and complicated. I am new to python, so the script that Chris posted is perfect for what I am doing. I just cannot format the expression correctly. Python is so frustrating!

The string substitution isn't working for me, but I could be entering it wrong. I will keep trying 🙂 thank you for the suggestion!

Re: Split by Attribute

ChrisSnyder — Sat, 11 Dec 2021 13:12:25 GMT

Since "crime type" is probably a string field, you just need single quotes.

So instead of this:

storeIdSet = set([r[0] for r in arcpy.da.SearchCursor(myFC, ["STORE_ID"])])
for storeId in storeIdSet:
     outFC = r"C:\temp\test.gdb\store_" + str(storeId)
     arcpy.Select_analysis(myFC, outFC, "STORE_ID = " + str(storeId))

Do this (difference is in the last line):

storeIdSet = set([r[0] for r in arcpy.da.SearchCursor(myFC, ["STORE_ID"])])
for storeId in storeIdSet:
     outFC = r"C:\temp\test.gdb\store_" + str(storeId)
     arcpy.Select_analysis(myFC, outFC, "STORE_ID = '" + str(storeId) +"'")

Mzcoyle formatting stuff also will work... Basically I think you just need to edit the SQL expression so it will deal with string values instead of numbers...

Re: Split by Attribute

JamesCrandall — Wed, 23 Apr 2014 17:06:10 GMT

Make sure "CrimeType" is a field that actually exists in CopyCopySample2011_2013.shp.

Also: "CopyCopy"????

Re: Split by Attribute

MathewCoyle — Sat, 11 Dec 2021 13:12:28 GMT

It could also be you need delimiters on your field, though it should be unnecessary.

AllCrimes = "CopyCopySample2011_2013.shp"
CrimeTypeSet = set([r[0] for r in arcpy.da.SearchCursor(AllCrimes, ["CrimeType"])])
for CrimeType in CrimeTypeSet:
    out_FC = "H:\\PythonOutput\\" + str(CrimeType)
    query = """{0} = '{1}'""".format(arcpy.AddFieldDelimiters(AllCrimes, "CrimeType"), CrimeType)
    arcpy.Select_analysis(AllCrimes, out_FC, query)

Re: Split by Attribute

AmberPerenzin — Wed, 23 Apr 2014 17:12:48 GMT

SUCCESS!!

Thank you both! When I entered the single quotes everything worked!

mzcoyle: ref "copy copy" Since I am new to python I know I am going to screw up my dataset. Making one backup wasn't enough, I had to backup the backup 🙂

Thank you again! I was stuck on this for longer than I would like to admit (days, not hours...ugh). You are a lifesaver!