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

7601
56
05-08-2015 08:06 AM
ChukwuemekaEzeiruaku
New Contributor II

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 Table
Observation1555100001
555100105
555100217
555100345
555100435
Observation2600200103
600200226
600200335

   

DataUniqueIDBegin EndItem IDOutput Table
Observation1555011001
555131001; 1002
555341001; 1002; 1004
555451001; 1002; 1003; 1004
555571002
Observation2600022001; 2002
600232001; 2002
600352002; 2003
600562003

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.

0 Kudos
56 Replies
BlakeTerhune
MVP Regular Contributor

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

0 Kudos
ChukwuemekaEzeiruaku
New Contributor II

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.

0 Kudos
BlakeTerhune
MVP Regular Contributor

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()
0 Kudos
ChukwuemekaEzeiruaku
New Contributor II

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.

      

UniqueIDItemIDBegin_Station_Num_mEnd_Station_Num_mTestScoreTestDate
110440155653032.918144011/17/1998
11044015565032.9181303.02144011/12/1998
1104401556491303.021310.923144010/22/1998
110770055585-9.1934526.8914402/2/1999
1107700555844526.895358.38414402/3/1999
1107700682005202.6315373.014144011/2/2011
1107700555835358.3846493.76414402/3/1999
1107700555826493.7648760.56214402/4/1999
1107700555818760.56213235.63514402/4/1999
11077005557813235.63515883.73814402/5/1999
11077005557615883.73823348.89914401/25/1999
11077005557723348.89925256.03314401/25/1999
11077005557325256.03327711.80614401/24/1999
11077005557227711.80631326.4314401/23/1999
11077005557531326.4333917.53414401/23/1999
11077005557433917.53439080.23714401/23/1999
11077005558639080.23740664.89214402/2/1999
11077005558740664.89244023.78814402/3/1999
11077005558844023.78851158.24214402/3/1999
11077005557951158.24251166.77614401/28/1999
11077005558951166.77655466.28514402/4/1999
11077005559055466.28559929.16614402/2/1999
11077005555659929.16670217.69144011/21/1998
11077005555470217.6971075.397144011/14/1998
11077005555571075.39797066.608144011/16/1998
11077005555097066.60899547.985144011/7/1998
11077005555199547.985102256.133144011/7/1998
110770055552102256.133105059.683144011/8/1998
110770055553105059.683112981.13144011/10/1998
110770068201112937.544112981.13144010/17/2011
110770055569112981.13113000.63714401/6/1999
110770055609113000.637116894.153144010/17/1998
110770055611116894.153118538.244144010/23/1998
110770055612118538.244121654.824144010/23/1998
110770055613121654.824123223.629144010/26/1998
110770055614123223.629126232.31144010/26/1998
110770055605126232.31139808.712144010/8/1998
110770055616139808.712157859.577144011/10/1998
110770055617157859.577177810.871144011/17/1998
110770055618177810.871182154.881144011/16/1998
110770055619182154.881195965.673144011/18/1998
110770055620195965.673198532.699144011/24/1998
110770055621198532.699203721.614144011/28/1998
110770055622203721.614209226.302144012/3/1998
110770055623209226.302213641.33144012/4/1998
110770055624213641.33220382.287144012/15/1998
110770055625220382.287224331.885144012/31/1998
110770055626224331.885225924.7714401/3/1999
110770055627225924.77227517.9614401/3/1999
110770055628227517.96231409.34114401/5/1999
110770055629231409.341231485.51714401/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.

0 Kudos
ChukwuemekaEzeiruaku
New Contributor II

Just to follow up on the date request;

Example: Input Table.

  

UniqueIDItem IDBegin EndDate
5551000019/5/1999
5551001059/15/1999
5551002179/19/1999
5551003459/20/1999
5551004359/21/1999
6002001039/22/1999
6002002269/23/1999
6002003359/24/1999

OutPut Table

  

UniqueIDBegin EndItem IDDate
5550110019/5/1999
555131001; 10029/19/1999
555341001; 1002; 10049/21/1999
555451001; 1002; 1003; 10049/21/1999
5555710029/19/1999
600022001; 20029/23/1999
600232001; 20029/23/1999
600352002; 20039/24/1999
6005620039/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.

0 Kudos
BlakeTerhune
MVP Regular Contributor

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?

0 Kudos
ChukwuemekaEzeiruaku
New Contributor II

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

I'm very grateful Blake and Xander for your solutions

0 Kudos
BlakeTerhune
MVP Regular Contributor

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

...

0 Kudos
ChukwuemekaEzeiruaku
New Contributor II

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

0 Kudos