1

I would like to write a script where I select a layer by the [District] attribute and loop through the field [PickUpDate] to get the count per district per month. My month format is m/d/yyyy, i.e. 1/2/2015. Below I select the district in the Table View, I could easily use GetCount to get the total for my selection, but I need the count per month per District

Example:

alayer = 'PickUpColumns_12_1_2015_dups1'
hbclause = "[District] = 'HB'"
arcpy.SelectLayerByAttribute_management(alayer,"NEW_SELECTION",hbclause)
4
  • I was literally about to ask this question, you saved me :). I will follow this thread. I am working on selecting by layer then using searchcursor, but as my arcGIS is 10.0 the da.searchcursor woun't work for me. what version is your arcgis?
    – yanes
    Dec 1, 2015 at 19:31
  • 1
    I am using 10.2, you can use arcpy.searchcursor. help.arcgis.com/en/arcgisdesktop/10.0/help./index.html#//… Dec 1, 2015 at 19:39
  • Worth noting is that relational databases are designed to do this sort of grouping via SQL. arcpy would be toward the bottom of the list of technologies I would choose for this problem (only as sort of a last resort).
    – jpmc26
    Dec 2, 2015 at 0:36
  • 1
    @ jpmc26 The best is work with distinct districts directly and existing start + end date in the table. I have create a script for that and you can modified as you want Dec 2, 2015 at 21:29

2 Answers 2

1

You can get all values in 2 uniques values list a. Next, create a product to get all possibilities and count. Test with this snippet

Imports:

# -*- coding:utf-8 -*-

import arcpy
import itertools
import calendar
from dateutil.relativedelta import relativedelta 

functions for get unique list of values

def boundary_date(table, field, where_clause=""):
    """ return first date and last date in table
        <tuple>(<datime.date>start_date, <datetime.date>end_date) """

    print "get boundary date"
    values = distinct_values(table, field)
    d1 = values[0]  # start value list
    d2 = values[-1] # end value list
    print d1, d2

    return (d1,d2)


def datetime_mmyyyy_couple_from_boundary(start,end):
    """ return list of date with a month delta based on start and end date
        <list>[(<datime.date>start_mm_date, <datetime.date>end_mm_date),...] """

    # add last day of month for end date
    start = start.replace(day=1)

    # add firt day of month for start date   
    end = end.replace(day=1) 

    # list od month
    count = (end.year - start.year)*12 + end.month - start.month 

    a = [start+relativedelta(months=i) for i in range(count+1)] # list of start month date
    b = [i.replace(day=calendar.monthrange(i.year, i.month)[1]) for i in a] # list of end mount date
    return zip(a,b) 


def distinct_values(table, field):
    """ Return distinct value content in field to list """
    with arcpy.da.SearchCursor(table, [field]) as cursor:
        return sorted({row[0] for row in cursor if row[0]})  # No blank data

Main Process

def main():
    """ Process to get count entities for distinct districts and month in
    PickUpDate"""
    path = 'E:/temp/sample.mdb/sampleTable'  # Need full path for AddFieldDelimiters
    tw = arcpy.MakeTableView_management(path, "sampleTable1")
    arcpy.SelectLayerByAttribute_management(tw, "CLEAR_SELECTION")  # Clear selection if you use existing table and dont use makeTableView

    print "> Get count for all District and PickUpDate (month interval)"

    districts = distinct_values(tw,"District")
    bound_date = boundary_date(tw,"PickUpDate") # <tuple> ({start_date},{end_date})
    list_couple_dates = datetime_mmyyyy_couple_from_boundary(*bound_date)
    print list_couple_dates

    print "District,Start_Date,End_Date,Count"
    for a, b in itertools.product(districts, list_couple_dates):

        # Get data from time interval
        q_district = """{} = '{}'""".format(arcpy.AddFieldDelimiters(path, "District"),a)
        q_date_1 = """{} >= #{}#""".format(arcpy.AddFieldDelimiters(path, "PickUpDate"),b[0].strftime('%Y-%m-%d'))  # Format date for MDB only!!!
        q_date_2 = """{} <= #{}#""".format(arcpy.AddFieldDelimiters(path, "PickUpDate"),b[1].strftime('%Y-%m-%d'))  # Format date for MDB only!!!
        # Get spécifique district
        query = """{} AND {} AND {}""".format(q_district,q_date_1,q_date_2)
        # print query  # Just for check Query
        fl_filter = arcpy.SelectLayerByAttribute_management(tw, "NEW_SELECTION", query)
        count = int(arcpy.GetCount_management(fl_filter).getOutput(0))  

        print "{},{},{},{}".format(a,b[0].strftime('%Y-%m-%d'),b[1].strftime('%Y-%m-%d'),count)

        # clear filter
        arcpy.SelectLayerByAttribute_management(fl_filter, "CLEAR_SELECTION")
    arcpy.Delete_management(fl_filter) #delete table view after work

if __name__ == '__main__':
    main()
7
  • what should fl be defined as? Dec 1, 2015 at 21:05
  • 1
    @GeoffreyWest this is your FeatureLayer or Your FeatureClass but i have just a question : day in PickUpDate is same for one district or is can change? If it can change i need patch the snippet to return couple of start and end date Dec 1, 2015 at 21:17
  • this value can change based on the day that the commodity was picked up. Dec 1, 2015 at 21:19
  • @GeoffreyWest Ok i change my post in few minutes to change pick_up_date couple of values and change query in fl_filter Dec 1, 2015 at 21:23
  • what should a, b be defined as? Dec 1, 2015 at 21:31
1

this should be more efficient than running several selections and counting in a loop.

import arcpy
fc = 'PickUpColumns_12_1_2015_dups1'
fields =  ("District","PickUpDate") 
myDictionnary={}
count=12*[0]

with arcpy.da.SearchCursor(fc, fields) as cursor:
        for row[0] in cursor:
            if row[0] in myDictionnary:
                myDictionnary[row[0]][int(row[1].split("/")[0])-1]+=1
            else:
                myDictionnary[row[0]]=count
                myDictionnary[row[0]][int(row[1].split("/")[0])-1]+=1

Count will contain the number of monthly pickups. Index starts at 0 so january is count[0] (therefore I use -1 for the index). row[0] will be the district name and row[1] the date string(I assumed that it is a string field and not a date field).

You can then query the number of months for any district

number_of_pickUp_for_month_and_district = myDictionnary[district_name][month-1]

rem : If you use SearchCursor instead of the da version, the row values will be row.PickUpDate and row.Districts

As a remark, if you concatenate district and month ( !District! + "_" + !PickUpDate!.split("/")[0] ) in a new field, you can use "summary statistics"

2
  • this script returns an error at line 5; Runtime error SyntaxError: can't assign to function call (<string>, line 5) Dec 1, 2015 at 21:27
  • this is probably because I forgot to removre the [] when copy pasting the whereclause. I've edited my code.
    – radouxju
    Dec 2, 2015 at 6:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.