I need to count the span of days an individual has been observed, from the first til the last observation day. There can be many observations per ID per day and not everyday has observations, hence the first and last dates are important.

The feature class contains many individuals, one row per observation per ID, so I need to group by ID. ID is a Text field, days are Date (also shows the time). I want to add the count of days to a new field (numeric) in the original datatable called NrDays. If need be I can create a new table and join on ID of course.

enter image description here

It seems simple but I cannot figure out how to do this in ArcGIS Pro. I need to be able to incorporate it into Model Builder, either by Python script or in a tool such as Calculate Field. In a script tool it seems hard to import Pandas for groupby function (the toolbox needs to be used by many different users), and in Calculate Field I don't know how to group by ID, and count the span of days in Arcade doesnt work: "DateDiff("d", Min($feature.Datum), Max($feature.Datum))"

Python script throws the error that ID is a Text field:

import arcpy
from arcpy import env
env.workspace = 'the database'
butterfly = r'butterfly_2019' # the feature class

for i in butterfly['ID']:
    d1 = datetime.date.max('Datum')
    d2 = datetime.date.min('Datum')
    countdays = d2-d1
    butterfly['NrDays'] = antal

out_location = 'the database'
arcpy.FeatureClassToGeodatabase_conversion(butterfly , out_location)

I know one major issue is with referencing the row ("i") which should be a group instead.

  • Can you add to your question a small sample of your data, it makes it easier to understand the problem?
    – Hornbydd
    Jul 23 '19 at 9:04
  • 1
    @Hornbydd I hope this helps, example data as picture though...
    – Lisarv
    Jul 23 '19 at 9:12
  • 1
    Have you tried summary statistics with RANGE as statistics field and your id as group field?
    – BERA
    Jul 23 '19 at 9:42
  • 1
    @BERA Thanks peeps! Actually, Summary Statistics did the trick the easiest way, I can't understand how I forgot about that option. However, Im very happy to learn more about da.SearchCursor and collections.defaultdict(list) as well, will come in handly later on.
    – Lisarv
    Jul 23 '19 at 9:53

An alternative approach that keeps it in ModelBuilder is this simple model:

My example input data is:

Sample data

My model is:


Summary stats tool is set up as:

Summary stats tool

The Calculate field is set up as shown below:

Calculate field tool

The model generates the following output table:



If you dont want pandas you can use collections.defaultdict(list) to group ids. Each row is read using da.SearchCursor and stored in a dictionary.

import arcpy
from collections import defaultdict

fc = r'C:\data.gdb\somefc'
datefield = 'DATE'
groupfield = 'GROUP'

#Create dictionary
d = defaultdict(list)
with arcpy.da.SearchCursor(fc,[groupfield,datefield]) as cursor:
    for group, date in cursor:
#d can now look like: defaultdict(<class 'list'>, {'OXFLYET': [datetime.datetime(2011, 4, 1, 0, 0), datetime.datetime(2011, 4, 1, 0, 0), datetime.datetime(2016, 11, 15, 0, 0), datetime.datetime(2011, 5, 1, 0, 0), datetime.datetime(2011, 5, 1, 0, 0)], 'MÖGSJÖN': [datetime.datetime(1992, 9, 1, 0, 0), None, None, datetime.datetime(1994, 1, 1, 0, 0)]})
#Each group is key (I only have two), and all dates are values in a list

#Calculate delta days per group
for group, datelist in d.items():
    datelist = [x for x in datelist if x is not None]
    if len(datelist) > 1:
        print('{0}: {1} days'.format(group, (max(datelist)-min(datelist)).days))
        print('{0}: not enough dates'.format(group))


OXFLYET: 2055 days
MÖGSJÖN: 487 days

(With pandas it can be done with less code:

df = pd.DataFrame.from_records(data=arcpy.da.SearchCursor(fc,[groupfield,datefield]), columns=[groupfield,datefield])
print(df.groupby(groupfield)[datefield].apply(lambda g: g.max() - g.min()))


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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