topic Re: Dynamic segmentation with respect to a control factor Additional Requirements in Python Questions

Re: Dynamic segmentation with respect to a control factor Additional Requirements

ChukwuemekaEzeiruaku — Fri, 08 May 2015 15:06:57 GMT

I was able to figure out the error from earlier, but i have one more request.

If i have other fields in the table i want to also show on the out_table for example see table below:

Data	UniqueID	Item ID	Begin	End	Input Table
Observation1	555	1000	0	1
	555	1001	0	5
	555	1002	1	7
	555	1003	4	5
	555	1004	3	5
Observation2	600	2001	0	3
	600	2002	2	6
	600	2003	3	5

Data	UniqueID	Begin	End	Item ID	Output Table
Observation1	555	0	1	1001
	555	1	3	1001; 1002
	555	3	4	1001; 1002; 1004
	555	4	5	1001; 1002; 1003; 1004
	555	5	7	1002
Observation2	600	0	2	2001; 2002
	600	2	3	2001; 2002
	600	3	5	2002; 2003
	600	5	6	2003

Is there a way to differentiate between records that are within a certain range(with UniqueID) as shown in the table above, plus have other fields for that range (with same uniqueID) in the output table?

I am really grateful for your help thus far guys, Thanks.

Re: Dynamic segmentation with respect to a control factor Additional Requirements

BlakeTerhune — Fri, 08 May 2015 17:22:45 GMT

Can you upload a copy of the actual input data table?

Re: Dynamic segmentation with respect to a control factor Additional Requirements

ChukwuemekaEzeiruaku — Fri, 08 May 2015 17:32:30 GMT

Clarification on the tables above:-

For both the Input and Output table, the Data field should not take the part in the segmentation as it was put in there to help visually differentiate between the UniqueIDs.

Re: Dynamic segmentation with respect to a control factor Additional Requirements

BlakeTerhune — Sun, 12 Dec 2021 06:59:38 GMT

I think you have some errors in the output table you put together here, but I think this modified code should accomplish what you're after. I also rearranged some things so it only opens the insert cursor once.

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", "SHORT")
    arcpy.AddField_management(table_out, "Begin", "SHORT")
    arcpy.AddField_management(table_out, "End", "SHORT")
    arcpy.AddField_management(table_out, "SegmentItems", "TEXT", field_length=255)

    # 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_out = ["UniqueID", "Begin", "End", "SegmentItems"]
    with arcpy.da.InsertCursor(table_out, fields_out) as i_cursor:
        for obsv in observations:
            # Read item and (begin, end) into dictionary
            fields_in = ["ItemID", "Begin", "End"]
            where_clause = "UniqueID = {}".format(obsv)
            itemsdict = {r[0]:(r[1], r[2]) for r in arcpy.da.SearchCursor(table_in, fields_in, where_clause)}

            # Identify segments
            allsegments = [i[0] for i in itemsdict.values()] + [i[1] for i in itemsdict.values()]
            segments = tuple(sorted(set(allsegments)))
            del allsegments

            # Identify items in each segment
            for enum, seg in enumerate(segments):
                try:
                    begin = seg
                    end = segments[enum + 1]
                except IndexError:
                    break
                seg_items = [k for k, v in itemsdict.items() if v[0] <= begin and v[1] >= end]

                # Write segment items to output table
                row = (obsv, begin, end, str(seg_items).strip('[]'))
                i_cursor.insertRow(row)


if __name__ == '__main__':
    main()

Re: Dynamic segmentation with respect to a control factor Additional Requirements

ChukwuemekaEzeiruaku — Fri, 08 May 2015 19:37:38 GMT

Thanks Blake...

I just tested the new script, it did get the UniqueIDs but it returned so many instance of a segment (in other words redundant rows/records). i have attached a table of what the actual table looks like.

UniqueID	ItemID	Begin_Station_Num_m	End_Station_Num_m	TestScore	TestDate
1104401	55653	0	32.918	1440	11/17/1998
1104401	55650	32.918	1303.02	1440	11/12/1998
1104401	55649	1303.02	1310.923	1440	10/22/1998
1107700	55585	-9.193	4526.89	1440	2/2/1999
1107700	55584	4526.89	5358.384	1440	2/3/1999
1107700	68200	5202.631	5373.014	1440	11/2/2011
1107700	55583	5358.384	6493.764	1440	2/3/1999
1107700	55582	6493.764	8760.562	1440	2/4/1999
1107700	55581	8760.562	13235.635	1440	2/4/1999
1107700	55578	13235.635	15883.738	1440	2/5/1999
1107700	55576	15883.738	23348.899	1440	1/25/1999
1107700	55577	23348.899	25256.033	1440	1/25/1999
1107700	55573	25256.033	27711.806	1440	1/24/1999
1107700	55572	27711.806	31326.43	1440	1/23/1999
1107700	55575	31326.43	33917.534	1440	1/23/1999
1107700	55574	33917.534	39080.237	1440	1/23/1999
1107700	55586	39080.237	40664.892	1440	2/2/1999
1107700	55587	40664.892	44023.788	1440	2/3/1999
1107700	55588	44023.788	51158.242	1440	2/3/1999
1107700	55579	51158.242	51166.776	1440	1/28/1999
1107700	55589	51166.776	55466.285	1440	2/4/1999
1107700	55590	55466.285	59929.166	1440	2/2/1999
1107700	55556	59929.166	70217.69	1440	11/21/1998
1107700	55554	70217.69	71075.397	1440	11/14/1998
1107700	55555	71075.397	97066.608	1440	11/16/1998
1107700	55550	97066.608	99547.985	1440	11/7/1998
1107700	55551	99547.985	102256.133	1440	11/7/1998
1107700	55552	102256.133	105059.683	1440	11/8/1998
1107700	55553	105059.683	112981.13	1440	11/10/1998
1107700	68201	112937.544	112981.13	1440	10/17/2011
1107700	55569	112981.13	113000.637	1440	1/6/1999
1107700	55609	113000.637	116894.153	1440	10/17/1998
1107700	55611	116894.153	118538.244	1440	10/23/1998
1107700	55612	118538.244	121654.824	1440	10/23/1998
1107700	55613	121654.824	123223.629	1440	10/26/1998
1107700	55614	123223.629	126232.31	1440	10/26/1998
1107700	55605	126232.31	139808.712	1440	10/8/1998
1107700	55616	139808.712	157859.577	1440	11/10/1998
1107700	55617	157859.577	177810.871	1440	11/17/1998
1107700	55618	177810.871	182154.881	1440	11/16/1998
1107700	55619	182154.881	195965.673	1440	11/18/1998
1107700	55620	195965.673	198532.699	1440	11/24/1998
1107700	55621	198532.699	203721.614	1440	11/28/1998
1107700	55622	203721.614	209226.302	1440	12/3/1998
1107700	55623	209226.302	213641.33	1440	12/4/1998
1107700	55624	213641.33	220382.287	1440	12/15/1998
1107700	55625	220382.287	224331.885	1440	12/31/1998
1107700	55626	224331.885	225924.77	1440	1/3/1999
1107700	55627	225924.77	227517.96	1440	1/3/1999
1107700	55628	227517.96	231409.341	1440	1/5/1999
1107700	55629	231409.341	231485.517	1440	1/6/1999

if its also possible to have, we can have a date field that contains only the most recent date per segment of an ItemID.

Re: Dynamic segmentation with respect to a control factor Additional Requirements

ChukwuemekaEzeiruaku — Fri, 08 May 2015 19:46:28 GMT

Just to follow up on the date request;

Example: Input Table.

UniqueID	Item ID	Begin	End	Date
555	1000	0	1	9/5/1999
555	1001	0	5	9/15/1999
555	1002	1	7	9/19/1999
555	1003	4	5	9/20/1999
555	1004	3	5	9/21/1999
600	2001	0	3	9/22/1999
600	2002	2	6	9/23/1999
600	2003	3	5	9/24/1999

OutPut Table

UniqueID	Begin	End	Item ID	Date
555	0	1	1001	9/5/1999
555	1	3	1001; 1002	9/19/1999
555	3	4	1001; 1002; 1004	9/21/1999
555	4	5	1001; 1002; 1003; 1004	9/21/1999
555	5	7	1002	9/19/1999
600	0	2	2001; 2002	9/23/1999
600	2	3	2001; 2002	9/23/1999
600	3	5	2002; 2003	9/24/1999
600	5	6	2003	9/24/1999

The most recent date is entered in the date field per segment.

And if it is too much to ask i can understand.

Thanks Again.

Re: Dynamic segmentation with respect to a control factor Additional Requirements

BlakeTerhune — Fri, 08 May 2015 20:15:52 GMT

Have you ever heard of the term, "Scope Creep"?

Seriously though, are there any other requirements we need to know about before spending time to solve your problem?

Re: Dynamic segmentation with respect to a control factor Additional Requirements

BlakeTerhune — Fri, 08 May 2015 20:52:27 GMT

I just noticed you have a negative number in there. Is that correct? Is that supposed to come before 0? Like this:

-9.193 to 0

0 to 32.918

32.918 to 1303.02

...

Re: Dynamic segmentation with respect to a control factor Additional Requirements

ChukwuemekaEzeiruaku — Fri, 08 May 2015 21:17:46 GMT

it is correct, but the -9.193 to 0 is for a different UniqueID(1107700) and
32.918 to 1303.02 is a section of the 1104401 UniqueID...

Re: Dynamic segmentation with respect to a control factor Additional Requirements

ChukwuemekaEzeiruaku — Fri, 08 May 2015 21:20:04 GMT

There are no further requirements other than the ones I've mentioned thus far...

I'm very grateful Blake and Xander for your solutions

Re: Dynamic segmentation with respect to a control factor Additional Requirements

BlakeTerhune — Sun, 12 Dec 2021 06:59:41 GMT

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

Re: Dynamic segmentation with respect to a control factor Additional Requirements

ChukwuemekaEzeiruaku — Sat, 09 May 2015 01:07:11 GMT

Thanks Blake, i'll test it and let you know...

Re: Dynamic segmentation with respect to a control factor Additional Requirements

ChukwuemekaEzeiruaku — Mon, 11 May 2015 14:47:35 GMT

Hi Blake, hope you had a good weekend?

I just tested the code and it worked the only thing i noticed is that the process repeats so many times and resulted in huge redundancy (repetition of rows). Is there a way to stop it after the first segmentation?

I will provide a snapshot of the output table

Re: Dynamic segmentation with respect to a control factor Additional Requirements

ChukwuemekaEzeiruaku — Mon, 11 May 2015 14:53:22 GMT

I counted the number times the repetition happened and the "UniqueID 1104401" occurred 3 times while the "UniqueID 1107700" occurred 48 times.

I also noticed that it is the same data that's written for this number of times mentioned above.

Re: Dynamic segmentation with respect to a control factor Additional Requirements

ChukwuemekaEzeiruaku — Mon, 11 May 2015 14:54:18 GMT

Re: Dynamic segmentation with respect to a control factor Additional Requirements

ChukwuemekaEzeiruaku — Mon, 11 May 2015 14:58:33 GMT

the highlighted section shows the repetition

Re: Dynamic segmentation with respect to a control factor Additional Requirements

BlakeTerhune — Mon, 11 May 2015 15:54:07 GMT

Sorry, but I don't have that problem with the test data you posted. Please attach a copy of your input table (not a copy/paste) so I can test like for like. See my attachment on this post as an example.

Re: Dynamic segmentation with respect to a control factor Additional Requirements

ChukwuemekaEzeiruaku — Mon, 11 May 2015 15:59:51 GMT

Dropbox - Book2.xlsx

thats the link to the

Re: Dynamic segmentation with respect to a control factor Additional Requirements

ChukwuemekaEzeiruaku — Mon, 11 May 2015 16:01:44 GMT

I just posted the complete table, i tried to use the code on it and i got the error below:

Re: Dynamic segmentation with respect to a control factor Additional Requirements

BlakeTerhune — Mon, 11 May 2015 16:23:23 GMT

That error is because some of your records are missing Date values. My code does not have any error handling and assumes there will always be at least one date.