Trying to run SelectByAttributes in python for a list of fields

[JohnNunnali]

I am having problems attempting to run the SelectByAttributes function for multiple fields automatically.  In my shapefile I have multiple columns which I am attempting to select the same values from individually and then exporting a jpeg with the selected values from each of the separate columns as a jpeg.  The script is functioning for a single column but I want to code the list of columns into the SelectByAttributes function and then name the exported jpeg with the current column name.  I am trying to avoid having to create individual shapefiles as I don't need the shapefiles.

Any suggestions are greatly appreciated.

[MathewCoyle]

Posting what code you are using might help. You can query by multiple fields in your where clause in the select by attributes tool.

[JohnNunnali]

This is the current code I am trying to use.  I have tried multiple variations of this but just don't have the knowledge of the correct format to code a list in the clause of the SelectByAttribute_management function or the ExportToJPEG function.  A key is that once I get it to cycle through my list in the SelectByAttributes function is that I want it to save the exported jpeg using the name of the current column in which attributes are selected.

#Import arcpy and set workspace
import arcpy
import arcpy.mapping
arcpy.env.workspace = "P:\CoWetlandTools\maps\distribution_maps_Johns_pytest"
import arcpy.sa

specieslist = ['Achillea millefolium', 'Acroptilon repens', 'Actinea osterhoutii', 'Adenocaulon bicolor', 'Adoxa moschatellina', 'Ageratina herbacea', 'Agoseris �?agrestis', 'Agoseris �?montana', 'Agoseris agrestis', 'Agoseris attenuata', 'Agoseris aurantiaca', 'Agoseris frondifera',   'Agoseris glauca']

for X in specieslist:
  #select counties each species is known to occur
  #Create Loop and List to run processing for

  arcpy.SelectLayerByAttribute_management ("counties_edited_datamergetest", "NEW_SELECTION", "X in "str(specieslist)" > 0") 

  #Export selected to jpeg
  mxd = arcpy.mapping.MapDocument(r"CURRENT")
  arcpy.mapping.ExportToJPEG(mxd, r"P:\CoWetlandTools\maps\"X in str(specieslist)".jpg")

  #clear selected attributes
  arcpy.SelectLayerByAttribute_management ("counties_edited_datamergetest", "CLEAR_SELECTION")

#finished
print "Finished"

[MathewCoyle]

You definitely have some issues here. It is not entirely clear what you are trying to do here. What field(s) are you trying to query? Are your species list your field names or are those attributes?

This line needs the raw tag.

arcpy.env.workspace = r"P:\CoWetlandTools\maps\distribution_maps_Johns_pytest" #"r" at beginning of path string

specieslist needs to be outside your for loop. This will error out just on that.

You need + signs to concatenate your strings in your where clause and format for the brackets. Also, this is assuming "X" is your field name, this is very likely incorrect.

where = "X in ('" + '\',\''.join(specieslist) + "')"

Also, it will help posting in [ CODE] [ /CODE] tags to see your indentation. The # sign at the top when posting.

[JohnNunnali]

The specieslist is my field names in which I am attempting to query attributes from.  For each of the species in the list I am trying to select values greater than zero and then for that particular species export a jpeg saved under that specific species name.  Once completed for the first species I would like the code to run through specieslist selecting the attributes and saving a jpeg of each individually.

I am a python beginner and do not know how to loop through a list inside of a function.  You are right "X" is not one of my field names.  I was using it as a place holder for the loop of species names I wish the function to loop through.  I will concatenate the string first thing tomorrow. 

What do I need to replace "X" with in the where clause in order for the function to cycle through the field names I wish to select attributes from?

Thank you for the feedback and the suggestions.

[JohnNunnali]

I made the adjustments to my script recommended by mzcoyle but still receive an SQL error for the SelectLayerByAttribute line in the code.  The error is most likely due to my formatting in the where clause but again I'm a python beginner and haven't been able to find an example of how to code for iteration through a list inside of a function.  Here is my current code:

#Import arcpy and set workspace
import arcpy
import arcpy.mapping
arcpy.env.workspace = r"P:\CoWetlandTools\maps\distribution_maps_Johns_pytest"
import arcpy.sa

specieslist = ['Achillea millefolium', 'Acroptilon repens', 'Actinea osterhoutii', 'Adenocaulon bicolor', 'Adoxa moschatellina', 'Ageratina herbacea', 'Agoseris �?agrestis', 'Agoseris �?montana', 'Agoseris agrestis', 'Agoseris attenuata', 'Agoseris aurantiaca', 'Agoseris frondifera', 'Agoseris glauca']

for X in specieslist:
  #select counties each species is known to occur
  #Create Loop and List to run processing for
  
  arcpy.SelectLayerByAttribute_management ("counties_edited_datamergetest", "NEW_SELECTION", "'Achillea millefolium' in ('"+'\',\''.join(specieslist)+"') > '0'") 

  #Export selected to jpeg
  mxd = arcpy.mapping.MapDocument(r"CURRENT")
  arcpy.mapping.ExportToJPEG(mxd, r"P:\CoWetlandTools\maps\"'Achillea millefolium' in ('"+'\',\''.join(specieslist) + "').jpg")

  #clear selected attributes
  arcpy.SelectLayerByAttribute_management ("counties_edited_datamergetest", "CLEAR_SELECTION")

#finished
print "Finished"

Any suggestions are greatly appreciated.

[curtvprice]

Your data structure is really unclear to me. Do you have a field in  you county table that lists occurring species, comma-delimited, or something like that? If that's the case your query expression could be formed like this:

# where "SpecField" LIKE '%Achillea millefolium%')
where = '\"%s\" LIKE \'%%%s%%\' % (SpecField,X)

[JohnNunnali]

My data is structured with counties as rows and individual species as columns with the presence/absence indicated by a 1 or 0.  So in order to run the code for all species I am trying to select the values > 0 within the column of a species, export a jpeg with those counties selected, clear the selection, and then move to the next column (species) until it runs for all species.

I started with a simple polygon shapefile of counties and then merged the attribute table with a spreadsheet of all species of interest formatted like stated above.  Would it be better to have the species as rows and counties as columns or in a single column with them comma-delimited per species?  If so is there an easy way to do this?

[curtvprice]

My data is structured with counties as rows and individual species as columns with the presence/absence indicated by a 1 or 0.  So in order to run the code for all species I am trying to select the values > 0 within the column of a species, export a jpeg with those counties selected, clear the selection, and then move to the next column (species) until it runs for all species.

This means your integer column's names have spaces in them - this can get you in trouble, but if it's a file geodatabase it could work with an expression like:

where = '\"%s\" > 0' % X

I started with a simple polygon shapefile of counties and then merged the attribute table with a spreadsheet of all species of interest formatted like stated above.  Would it be better to have the species as rows and counties as columns or in a single column with them comma-delimited per species?  If so is there an easy way to do this?

This is actually a better data design because the table doesn't have to be so wide, and you can use a nice, safe column names like SPECIES and COUNTY. The Excel copy / paste special / transposed may get you most of the way there formatting your data, though you may have to dive into pivot tables.

In that case, you can create a table view of a your county-species table, and select for those in your species:

tv = arcpy.MakeTableView(SpecCounty,"tv")
arcpy.SelectLayerByAttribute(tv,"","SPECIES = \'%s\'" % X)
# KEEP_COMMON will unselect non-matching rows
arcpy.AddJoin("County_poly","COUNTY",tv,"COUNTY","KEEP_COMMON")
#
# .. make your plot here ..
#
# Now remove the join (this clears the selection for the next loop)
joinName = gp.Describe("County_poly").Name
arcpy.RemoveJoin("County_poly",joinName)

[JohnNunnali]

The data is currently not a file geodatabase however it should be easy to remove the spaces from the column names.  I didn't think of that potential issue, thanks for the suggestion.

I also shouldn't have to worry about pivot tables because there are not multiple rows or columns with the same name so simply transposing the table may work.

I would like to avoid using a table view because I would like the script to eventually be able to be used for different data sets simply by changing the list and possibly a couple paths.  Trying to keep it simple as possible.

Your suggestion of transposing the table made me wonder if the species were the rows if I could query across all fields (counties as columns) for values greater than 0 in the SelectByAttributes function.  This would remove the need for cycling through the list inside of the function and would simply need to loop through the species list with nothing changing inside the loop other than the file name of the exported jpeg.