Iterate Select Layer by Attributes

[AFung]

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}'")

[JoeBorgione]

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.

[AFung]

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

[JohannesLindner]

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)

[AFung]

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

[JohannesLindner · Accepted solution]

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-...

[AFung]

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

[JohannesLindner]

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"))