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: DataUniqueIDItem IDBegin EndInput TableObservation1555100001555100105555100217555100345555100435Observation2600200103600200226600200335 DataUniqueIDBegin EndItem IDOutput TableObservation1555011001555131001; 1002555341001; 1002; 1004555451001; 1002; 1003; 1004555571002Observation2600022001; 2002600232001; 2002600352002; 2003600562003 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.

# 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) ) ) This section uses the SQL prefix and postfix options in the SQL Clause of an arcpy.da.SearchCursor() to bring back only distinct (no duplicate) values in the UniqueID field and order them in ascending order. That search cursor is inside something called a generator expression, which is coding shorthand to create a fully populated iterator. This makes a tuple, but you can also do it with lists and dictionaries. In this case, I'm putting all of the values returned by the search cursor (which, remember, are only distinct) into a tuple so I don't have to have two search cursors open on the same table. fields_in = [ "ItemID", "Begin_Station", "End_Station", "TestDate" ] row_tpl = namedtuple('row_tpl', 'iID, begin, end, tDate') fields_out = [ "UniqueID", "Begin", "End", "SegmentItems", "MaxDate" ] with arcpy.da.InsertCursor(table_out, fields_out) as i_cursor: for obsv in observations: Here I'm defining the field names as a list for the input and output tables. I also create the named tuple object and define the value names in there. The field name lists are used for the arcpy.da cursors. At the end, I open an insert cursor on the output table, which will be used to insert rows as they are processed. Before processing the segments and items, I need to do it only for a particular UniqueID (or "Observation"). That last line there is iterating the distinct observations and doing whatever code is inside for each one. # Read table into dictionary with rows as item: (begin, end, date) where_clause = "UniqueID = {}".format(obsv) print("Processing {}".format(where_clause)) with arcpy.da.SearchCursor(table_in, fields_in, where_clause) as s_cursor: table_in_rows = map(row_tpl._make, s_cursor) Here is where I populate the named tuple with all of the rows returned from the input table. I start by defining the where clause for the search cursor so it only bring back the rows for the current observation (UniqueID). Now that I think about it, this also probably could have used a generator expression. # Identify segments allsegments = [row.begin for row in table_in_rows] + [row.end for row in table_in_rows] segments = tuple(sorted(set(allsegments))) ## creates only unique segments del allsegments This section first combines all begin and end segment values into a single list; you can see the concatenation of the two lists with a simple +. However, this list contains duplicates and can be in any order. The duplicates are eliminated using the set() method. They are ordered ascending by the sorted() function. Then, the distinct, sorted list is converted to a tuple to preserve the order (which, in hindsight, might not be necessary. I did it as a precaution but never tested it without). I also delete the allsegments variable to free up memory and because the data is no longer needed; it was just a scratch-pad to create the distinct segments tuple. # Identify items and date in each segment for i in range(len(segments)-1): begin = segments end = segments[i + 1] seg_itemsdict = { row.iID: row.tDate for row in table_in_rows if row.begin = end } Most everything up until now has just been gathering and manipulating the data. This is where the real "logic" is that identifies all the ItemID values that fall within a segment range. It also records the date for each item that will be compared to find the max in the next step. It's a little hard to describe with text, so let me know if you want something specific clarified here. # Write segment items to output table itemstext = str(seg_itemsdict.keys()).strip('[]') itemsdates = [i for i in seg_itemsdict.values() if i is not None] ## Do not attempt to find max date if there ## are no dates for the items in the segment. if len(itemsdates) > 0: itemsdates_max = max(itemsdates) else: itemsdates_max = None row = (obsv, begin, end, itemstext, itemsdates_max) i_cursor.insertRow(row) Finally, this is where the data is actually written to the output table. It starts by formatting the list of ItemID's as plain text so it looks nice in the table. Then I use another generator expression that puts all of the date values for the items in the segment into a list. That list is then first checked for length to see if there are any dates. If there are, then it finds the maximum, which is what will be written to the output table. The last line is where we call the insert cursor to write the row.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

ChukwuemekaEzeiruaku

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.

Find more posts tagged with

Accepted answers

All comments

blake.terhune

I don't have any experience coding native mobile apps. There is a GeoNet community space for native app developers with subspaces for each mobile operating system (iOS, Android, etc).

ChukwuemekaEzeiruaku

Hi Blake T hope you're doing great? just want to ask if you have any knowledge about mobile app creation?

ChukwuemekaEzeiruaku

I've looked at a few things online and books and yet to find good stuff, if you wouldn't mind recommending a stuff that can help me especially in understanding this sect of the script(see below):

# 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

}

ChukwuemekaEzeiruaku

Thanks a lot Blake for your assistance thus far, i really appreciate every help.

But, I just have one more question/request hope you can help still....

I'm trying to make a script tool for the script for just the input and output parameters and i came up with:

After making the script tool i get the following error:

blake.terhune

I don't get the duplicates when I run the script, so I can't modify the code unless I know what the problem is. Maybe this would be a good time for you to jump into the Python pool and try modifying the code yourself. Use my comments (and the other code posted by Xander) and look up what some of the functions are doing. Empower yourself to solve your own problems and develop new solutions. I don't mean for that to sound harsh, I'm just trying to help.

ChukwuemekaEzeiruaku

thanks Blake for the codes, i was able to get one of the previous script to work(see below)

is there a way to include a loop statement in an attempt to eliminate duplicate records?

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

ChukwuemekaEzeiruaku

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.

ChukwuemekaEzeiruaku

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

ChukwuemekaEzeiruaku

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

blake.terhune

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

ChukwuemekaEzeiruaku

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...

blake.terhune

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

...

blake.terhune

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?

ChukwuemekaEzeiruaku

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.

ChukwuemekaEzeiruaku

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.

blake.terhune

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

ChukwuemekaEzeiruaku

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.

blake.terhune

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.