My eyes are starting to cross. Please test this and see if it's accurate.
def main():
import arcpy
import os
# Local variables
sourcegdb = r"N:\TechTemp\BlakeT\Work\TEMP.gdb"
table_in = os.path.join(sourcegdb, "SegmentSequence")
# Create output table
arcpy.CreateTable_management(sourcegdb, "SegmentSequence_output")
table_out = os.path.join(sourcegdb, "SegmentSequence_output")
arcpy.AddField_management(table_out, "UniqueID", "LONG")
arcpy.AddField_management(table_out, "Begin", "DOUBLE")
arcpy.AddField_management(table_out, "End", "DOUBLE")
arcpy.AddField_management(table_out, "SegmentItems", "TEXT", "", "", 255)
arcpy.AddField_management(table_out, "MaxDate", "DATE")
# Identify observation groups
sqlprefix = "DISTINCT UniqueID"
sqlpostfix = "ORDER BY UniqueID"
observations = tuple(uid[0]
for uid in arcpy.da.SearchCursor(
table_in,
["UniqueID"],
sql_clause=(sqlprefix, sqlpostfix)
)
)
fields_in = [
"ItemID",
"Begin_Station_Num_m",
"End_Station_Num_m",
"TestDate"
]
fields_out = [
"UniqueID",
"Begin",
"End",
"SegmentItems",
"MaxDate"
]
with arcpy.da.InsertCursor(table_out, fields_out) as i_cursor:
for obsv in observations:
# Read table into dictionary with rows as item: (begin, end, date)
where_clause = "UniqueID = {}".format(obsv)
itemsdict = {r[0]:(r[1], r[2], r[3])
for r in arcpy.da.SearchCursor(
table_in,
fields_in,
where_clause
)
}
# Identify segments
allsegments = [s[0] for s in itemsdict.values()] + [s[1] for s in itemsdict.values()]
segments = tuple(sorted(set(allsegments))) ## creates only unique segments
del allsegments
# Identify items and date in each segment
for i in range(len(segments)-1):
begin = segments
end = segments[i + 1]
seg_itemsdict = {k: v[2]
for k, v in itemsdict.items()
if v[0] <= begin and v[1] >= end
}
# Write segment items to output table
itemstext = str(seg_itemsdict.keys()).strip('[]')
maxsegdate = max(seg_itemsdict.values())
row = (obsv, begin, end, itemstext, maxsegdate)
i_cursor.insertRow(row)
if __name__ == '__main__':
main()
