Arcpy Script to loop through field and run Union Analysis

[ssintelmann]

 have a polygon file in form of a fishnet. Also another feature class with polygons named Trawl_Buffers. There is a unique field within Trawl_Buffers based on YEAR. I'd like to create a script to run a selection on YEAR, and then perform a union analysis with the fishnet polygon for each YEAR. So the desired output would be "Trawl_Buffers_union2003", "Trawl_Buffers_union2004" etc. I have a function that will get me the unique list of the years, and puts them in a list which i called vals.

Then seems I need to run a for loop over this list of unique years, create a temporary selection, then use that as input for the union, but I am having trouble implementing the query process.

Here is where I started, but seriously tripping:

import arcpy
 
#Set the data environment 
arcpy.env.overwriteOutput = True

arcpy.env.workspace = r'C:\Data\working\AK_Fishing_VMS\2021_Delivery\ArcPro_proj\ArcPro_proj.gdb'
trawlBuffs = r'C:\Data\working\AK_Fishing_VMS\2021_Delivery\ArcPro_proj\ArcPro_proj.gdb\buffers\buffers_testing'
fishnet = r'C:\Data\working\AK_Fishing_VMS\2021_Delivery\ArcPro_proj\ArcPro_proj.gdb\fishnets\vms_net1k'
unionOut = r'C:\Data\working\AK_Fishing_VMS\2021_Delivery\ArcPro_proj\ArcPro_proj.gdb\unions\union'

# function to get unique values for the YEAR field found within the trawlBuffs fc
def unique_values(table, field):
    with arcpy.da.SearchCursor(table, [field]) as cursor:
        return sorted({row[0] for row in cursor})

# Get the unique values for the field 'YEAR' found within the 'trawl_buffs' featureclass table
vals = unique_values(trawlBuffs, "YEAR")

# Create a query string for the selected country
yearSelectionClause = '"YEAR" = ' + "'" + vals + "'"

#loop through the years, create selection, union, make permanent
for year in vals:
    year_layer = str(year) + "_union"
    arcpy.MakeFeatureLayer_management(trawlBuffs, year_layer)
    arcpy.SelectLayerByAttribute_management(year_layer, "NEW_SELECTION", "\"YEAR"\" = %d" % (year))
    arcpy.Union_analysis(fishnet, year_layer , unionOut)
    arcpy.CopyFeatures_management(year_layer, "union_" + str(year))

[DavidPike · Accepted solution]

import arcpy
 
#Set the data environment 
arcpy.env.overwriteOutput = True

arcpy.env.workspace = r'C:\Data\working\AK_Fishing_VMS\2021_Delivery\ArcPro_proj\ArcPro_proj.gdb'
trawlBuffs = r'C:\Data\working\AK_Fishing_VMS\2021_Delivery\ArcPro_proj\ArcPro_proj.gdb\buffers\buffers_testing'
fishnet = r'C:\Data\working\AK_Fishing_VMS\2021_Delivery\ArcPro_proj\ArcPro_proj.gdb\fishnets\vms_net1k'
unionOut = r'C:\Data\working\AK_Fishing_VMS\2021_Delivery\ArcPro_proj\ArcPro_proj.gdb\unions\union_'

# function to get unique values for the YEAR field found within the trawlBuffs fc
#how is this unique? just a sorted list of every value - and why sort it?
def unique_values(table, field):
    with arcpy.da.SearchCursor(table, [field]) as cursor:
        return sorted({row[0] for row in cursor})

# Get the unique values for the field 'YEAR' found within the 'trawl_buffs' featureclass table
vals = unique_values(trawlBuffs, "YEAR")
#a set would be unique
unique_vals = set(vals)

# Create a query string for the selected country
#vals is a list??
#yearSelectionClause = '"YEAR" = ' + "'" + vals + "'"

#loop through the years, create selection, union, make permanent
#your clause goes in the loop instead
for year in unique_vals:
    #not sure if year is text or number?? this affects the query
    #if number
    yearSelectionClause = '{0} = {1}'.format('YEAR',year)
    #if string
    #yearSelectionClause = '{0} = "{1}"'.format('YEAR',year)
    
    year_layer = str(year) + "_union"
    arcpy.MakeFeatureLayer_management(trawlBuffs, year_layer)
    #i have no idea what this is
    #arcpy.SelectLayerByAttribute_management(year_layer, "NEW_SELECTION", "\"YEAR"\" = %d" % (year))
    arcpy.SelectLayerByAttribute_management(year_layer, "NEW_SELECTION", yearSelectionClause)

    #should be a list of input??
    #unionOut will just overwrite itself
    #arcpy.Union_analysis(fishnet, year_layer , unionOut)

    out_feat = unionOut + str(year)
    arcpy.Union_analysis([fishnet, year_layer] , out_feat)

    #why?
    #arcpy.CopyFeatures_management(year_layer, "union_" + str(year))

[ssintelmann]

Thank you for straightening me out, I appreciate it.  For my own help, could you kindly explain what is actually going on in Line 31 of your code?  The rest is clear for me.  

[DavidPike]

I would look at the .format python method, I would just print that line with some dummy values in a new script to see how it works.  Personally I find .format() simplifies building these where clauses which can get a bit convoluted with single, double even triple quotes.

essentially the printed statement should look like how it would appear if created in the select by attributes GUI 

[ssintelmann]

ahhh, I think I get it, basically {} are indexes it seems.  Soooo, taking what you worked out, what if I wanted to take it a step further and instead create a new unique union feature for each YEAR, like above but ALSO on another string field entitled "AGENCY_GEAR".  So output files would be:

gear1_2003_union

gear2_2003_union

gear1_2004_union

gear2_2004_union, etc

Would you nest for loops, separate selection clause, or combined selection clause ?  Aimlessly stabbed below, where I used the same function to gather unique values of the AGENCY_GEAR.:

import arcpy

#Set the data environment
arcpy.env.overwriteOutput = True

arcpy.env.workspace = r'C:\Data\working\AK_Fishing_VMS\2021_Delivery\ArcPro_proj\ArcPro_proj.gdb'
trawlBuffs = r'C:\Data\working\AK_Fishing_VMS\2021_Delivery\ArcPro_proj\ArcPro_proj.gdb\buffers\buff_test_subset'
fishnet = r'C:\Data\working\AK_Fishing_VMS\2021_Delivery\ArcPro_proj\ArcPro_proj.gdb\fishnets\vms_net1k'
unionOut = r'C:\Data\working\AK_Fishing_VMS\2021_Delivery\ArcPro_proj\ArcPro_proj.gdb\unions\union'

# function to get unique values for the YEAR field found within the trawlBuffs fc
def unique_values(table, field):
with arcpy.da.SearchCursor(table, [field]) as cursor:
return sorted({row[0] for row in cursor})
# Get the unique values for the field 'YEAR' found within the 'trawl_buffs' featureclass table
yearVals = unique_values(trawlBuffs, "YEAR")
unique_year = set(yearVals)
# Get the unique values for the field 'AGENCY_GEAR' found within the 'trawl_buffs' featureclass table
gearVals = unique_values(trawlBuffs, "AGENCY_GEAR")
unique_gear = set(gearVals)

#loop through the years, create selection, union, make permanent
for year in unique_year:
yearSelectionClause = '{0} = {1}'.format('YEAR', year)
for gear in unique_gear:
gearSelectionClause = '{0} = "{1}"'.format('AGENCY_GEAR', gear)
new_layer = str([year, gear]) + "_union"
arcpy.MakeFeatureLayer_management(trawlBuffs, new_layer)
arcpy.SelectLayerByAttribute_management(new_layer, "NEW_SELECTION", newSelectionClause)
out_feat = unionOut + str(year)
arcpy.Union_analysis([fishnet, new_layer], out_feat)

[ssintelmann]

Revised double select above, this gets it closer, but fails at arcpy.SelectLayerByAttribute_management. 

#loop through the years/gears, create selection, union

for year in unique_year:
    for gear in unique_gear:
        new_layer = str(year) + str(gear) + "_union"
        selectionClause = '{0} = {1}'.format('YEAR', year), '{0} = "{1}"'.format('AGENCY_GEAR', gear)
        #selectionClause = '"YEAR" = ' + "'" + str(year) + "'", '"AGENCY_GEAR" = ' + "'" + gear + "'"
        arcpy.MakeFeatureLayer_management(trawlBuffs, new_layer)
        arcpy.SelectLayerByAttribute_management(new_layer, "NEW_SELECTION", selectionClause)
        out_feat = unionOut + str(year) + str(gear)
        arcpy.Union_analysis([fishnet, new_layer], out_feat)

[ssintelmann]

beat it up for hourS.  sql...ugh. this seemed to work.  thanks much for your help put in the right direction

try:
    for year in unique_year:
        for gear in unique_gear:
            selectionClause = '{0} = {1}'.format('YEAR', year) + str(" AND ") + '{0} = '"'{1}'"''.format('AGENCY_GEAR',gear)
            new_layer = str(year) + str(gear) 
            arcpy.MakeFeatureLayer_management(trawlBuffs, new_layer)
            arcpy.SelectLayerByAttribute_management(new_layer, "NEW_SELECTION", selectionClause)
            out_feat = unionOut + str(year) + str(gear)        
            arcpy.Union_analysis([fishnet, new_layer], out_feat)
            print("Writing Data File: " + out_feat)
except Exception as e:
    #dump errors
    print("Error: " + e.args[0]) 
finally:
    #Clean up the temp layer
    arcpy.Delete_management(new_layer)

[RhettZufelt]

Hard to really say without seeing the data, or at least what "seriously tripping" means (I.e., what error are you getting).

But, in this line:

yearSelectionClause = '"YEAR" = ' + "'" + vals + "'"

 you are trying to concatenate a string to a list object, and will error out. 

Plus, I don't see anywhere that yearSelectionClause is being used anyway.

Suspect you want to move that into the loop, and replace the clause in the SelectLayer line.

Something like this (which is now using the year from the vals list, not the entire list):

for year in vals:
    yearSelectionClause = '"YEAR" = ' + "'" + year + "'"
    year_layer = str(year) + "_union"
    arcpy.MakeFeatureLayer_management(trawlBuffs, year_layer)
    arcpy.SelectLayerByAttribute_management(year_layer, "NEW_SELECTION",yearSelectionClause)

You might even be able to skip the SelectBy line and just put the where clause in the makefeaturelayer:

arcpy.management.MakeFeatureLayer(trawlBuffs, year_layer, yearSelectionClause)

R_