Finding number of days per ID in table using ArcPy?

Question

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.

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.

Answer 1

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

My example input data is:

My model is:

Summary stats tool is set up as:

The Calculate field is set up as shown below:

The model generates the following output table:

Answer 2

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[group].append(date)
#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))
    else:
        print('{0}: not enough dates'.format(group))

Output:

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

)