topic Re: Iterate Select Layer by Attributes in Python Questions

Iterate Select Layer by Attributes

AFung — Mon, 13 Sep 2021 16:00:23 GMT

Hi all,

I'm a newbie to Python, so I hope the question isn't too dumb.

I want to loop through the date in an attribute field of a point feature class and create new point feature classes for each date. Then, I want to save the layers with names, e.g. 24may98. For instance, I want to have a point layer of 24/5/1998 and so on. I find it tricky to work with as the data type is date/time instead of a string.

I tried to write the code below, but it did work. Thank you in advance for any assistant!

import arcpy

cursor = arcpy.da.SearchCursor("DBF_ExRainfall","Dates")

for row in cursor:

arcpy.management.MakeFeatureLayer(row, Dates_layer)

arcpy.management.SelectLayerByAttribute(Dates_layer, "SUBSET_SELECTION","Date = timestamp '{row}'")

Re: Iterate Select Layer by Attributes

JoeBorgione — Mon, 13 Sep 2021 16:06:52 GMT

In python you need to use strftime() from the datetime module to format date fields as a string. See this for a handy how-to.

Re: Iterate Select Layer by Attributes

JohannesLindner — Tue, 14 Sep 2021 08:59:41 GMT

import arcpy table = "DBF_ExRainfall" date_field = "Dates" # extract the dates from the table and format them: # list of datetime.datetime objects # [datetime.datetime(2018, 9, 25, 8, 36, 54), datetime.datetime(2018, 9, 25, 8, 58, 24)] dates = [row[0] for row in arcpy.da.SearchCursor(table, [date_field], "{} IS NOT NULL".format(date_field))] # convert to str # ['2018-09-25', '2018-09-25'] date_strings = [d.strftime("%Y-%m-%d") for d in dates] # make it distinct # ['2018-09-25'] distinct_date_strings = list(set(date_strings )) # create feature layers for each date for d in distinct_date_strings: where_clause = "DATEDIFF(DAY, {}, '{}') = 0".format(date_field, d) lyr = arcpy.management.MakeFeatureLayer(table, d, where_clause) # if you want it as feature class (saved in database): #out_fc = os.path.join(gdb_path, d) #arcpy.management.CopyFeatures(lyr, out_fc)

Re: Iterate Select Layer by Attributes

AFung — Tue, 14 Sep 2021 15:22:37 GMT

Thanks Johannes!

It solves the problem with the data type! Also, I ran the "Make feature layer" of your script and an error popped up:

Traceback (most recent call last):
File "C:/Users/aldon/OneDrive/Work/Previsico/Debris Flow Research/GIS Analysis/SBA_Loop_t1.py", line 26, in <module>
lyr = arcpy.management.MakeFeatureLayer(table, d, where_clause)
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 8826, in MakeFeatureLayer
raise e
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\management.py", line 8823, in MakeFeatureLayer
retval = convertArcObjectToPythonObject(gp.MakeFeatureLayer_management(*gp_fixargs((in_features, out_layer, where_clause, workspace, field_info), True)))
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\geoprocessing\_base.py", line 512, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 000358: Invalid expression
Failed to execute (MakeFeatureLayer).

I spent some time delving into it but I couldn't figure out a valid expression.

Much appreciated!

AFung

Re: Iterate Select Layer by Attributes

AFung — Tue, 14 Sep 2021 15:25:06 GMT

Thanks Joe! The strftime() function has worked perfectly fine for solving the data type issue!

Re: Iterate Select Layer by Attributes

JohannesLindner — Wed, 15 Sep 2021 07:25:47 GMT

Sorry, I always forget that the sql query depends on the DBMS... The one I used is for SQL Server.

If you're working with a file geodatabase, try this:

import arcpy import datetime table = "DBF_ExRainfall" date_field = "Dates" # extract the dates from the table and format them: # list of datetime.datetime objects # [datetime.datetime(2018, 9, 25, 8, 36, 54), datetime.datetime(2018, 9, 25, 8, 58, 24)] dates = [row[0] for row in arcpy.da.SearchCursor(table, [date_field], "{} IS NOT NULL".format(date_field))] # convert to str # ['2018-09-25', '2018-09-25'] date_strings = [d.strftime("%Y-%m-%d") for d in dates] # make it distinct # ['2018-09-25'] distinct_date_strings = list(set(date_strings )) # create feature layers for each date for d in distinct_date_strings: start_date = datetime.datetime(*[int(x) for x in d.split("-")]) end_date = start_date + datetime.timedelta(days=1) where_clause = "{0} >= date '{1}' AND {0} < date '{2}'".format( date_field, start_date.strftime("%Y-%m-%d"), end_date.strftime("%Y-%m-%d")) lyr = arcpy.management.MakeFeatureLayer(table, d, where_clause) # if you want it as feature class (saved in database): #out_fc = os.path.join(gdb_path, d) #arcpy.management.CopyFeatures(lyr, out_fc)

If you're working on another DBMS, I can't help you with the query, but here is the SQL reference for ArcGIS which includes syntax for filtering by date: https://pro.arcgis.com/de/pro-app/latest/help/mapping/navigation/sql-reference-for-elements-used-in-query-expressions.htm

Re: Iterate Select Layer by Attributes

AFung — Wed, 22 Sep 2021 14:35:59 GMT

Thank you so much for your help! The script works perfectly well!

Sorry that I might have too many questions to ask but would you mind explaining the codes from line 19 to 25 that you added the start date and end date and the where clause query?

Thanks again and have a nice week!

AFung

Re: Iterate Select Layer by Attributes

JohannesLindner — Thu, 23 Sep 2021 09:25:24 GMT

for d in distinct_date_strings: # for file geodatabases (SQL 92), the query has to look like this: # WHERE DateField >= date '2018-09-25' AND DateField < date '2018-09-26' # so "later than the start of this day, but before the start of the next day" # I have "this day" in distinct_day_strings, I have to get "next day" from that # easiest way to do that is using the datetime.datetime methods # to do that, I have to convert "this day" from string back to datetime.datetime # in retrospect, I would have done # start_date = datetime.datetime.strptime(d, "%Y-%m-%d") # but I didn't remember that method, so I did it in a more complicated way... # d = "2018-09-25" # d.split("-") = ["2018", "09", "25"] # [int(x) for x in d.split("-")] = [2018, 9, 25] # datetime.datetime takes arguments for year, month, day, hour, minute second # *[...] unpacks the list into these arguments, it's a shortcut for saing # parts = [int(x)...] # datetime.datetime(parts[0], parts[1], parts[2], ...) start_date = datetime.datetime(*[int(x) for x in d.split("-")]) # add 1 day to start_date end_date = start_date + datetime.timedelta(days=1) # construct the where clause from above # {0}, {1}, and {2} are placeholders that are filled with the format method # the numbers are the indices of the method's arguments where_clause = "{0} >= date '{1}' AND {0} < date '{2}'".format( date_field, start_date.strftime("%Y-%m-%d"), end_date.strftime("%Y-%m-%d"))