Hi, I have a challenge to group data based on an attribute, then sort the groups and assign the lowest object ID's global ID to another field for the items in that given group. This needs to be iterated as there will be dozens, if not hundreds of groups. This is what I've come up with so far as an outline. Any pointers on this would be greatly appreciated: import arcpy , time , datetime , os , itertools fields = [ 'OBJECTID' , 'LegacyGUID' , 'GlobalID' , 'LEGACYID' ] groups = [ ] ukeys = [ ] try : with arcpy . da . SearchCursor ( fc , fields , sql_clause = ( '' , 'ORDER BY LEGACYID' ) ) as cursor : for k , g in itertools . groupby ( cursor , lambda x : x [ 3 ] ) : groups . append ( list ( g ) ) ukeys . append ( k ) except : e = sys . exc_info ( ) [ 1 ] GroupFail = ( 'Grouping Operation Failed at ' + str ( datetime . datetime . now ( ) ) + ' ' + ( e . args [ 0 ] ) + '\n' ) flog . write ( GroupFail ) flog . write ( str ( groups ) ) #newgroups = sorted(groups, key=lambda x: x[0])

Since I wrote the code to accept multiple case/grouping fields, I have to create the groupby grouping function dynamically. Since I am grouping based on positions in a list, I used operator.itemgetter, which accepts multiple indexes. To keep it simple, I put the case/grouping fields at the front end of the list of fields so I can use len(case_fields) to setup itemgetter. So, len(case_fields) is being used to identify how many of the first list elements are related to grouping. I am using range to create a sequence for itemgetter that starts at the lowest index in the list, zero, and counts up. I put the star/asterisk in front of range to unpack the sequence when passing it to itemgetter. In terms of the groupby key, you could also right: case_func = lambda x : x [ : len ( case_fields ) ] When working with groupby, the iterable (cursor in this instance) needs to be sorted. I used a compound SQL ORDER BY clause to create sorted groups for the cursor. For this situation, my SQL statement looks like: ORDER BY LegacyID , OBJECTID ASC Since the rows are sorted by LegacyID and then OBJECTID, the first record within a group will contain the GlobalID you want to use for populated the other rows in the group. That is why I manually move the group iterator forward by using next(), and then continue looping using a for statement.

You can do it all in one pass using an update cursor, if I understand what you are trying to do. I will post some code tomorrow. UPDATE: In terms of specifics, there are still a couple of things I am unclear about. For example, where does LegacyGUID come into play. Also, what is the target field you want to update with the LegacyID of the lowest ObjectID of the group? For the code below, I am using LegacyGUID as the target field to be updated, but it is simple to change the field. In the code below, case_fields can be multiple fields if you want the grouping of data to be based on more than one field. import arcpy from itertools import groupby from operator import itemgetter fc = # path to feature class or table case_fields = [ "LegacyID" ] sort_field , sort_order = "OBJECTID" , "ASC" source_field = "GlobalID" target_field = "LegacyGUID" fields = case_fields + [ sort_field , source_field , target_field ] sql_orderby = "ORDER BY {}, {} {}" . format ( ", " . join ( case_fields ) , sort_field , sort_order ) with arcpy . da . UpdateCursor ( fc , fields , sql_clause = ( None , sql_orderby ) ) as cur : case_func = itemgetter ( * range ( len ( case_fields ) ) ) for key , group in groupby ( cur , case_func ) : row = next ( group ) source = row [ - 2 ] row [ - 1 ] = source cur . updateRow ( row ) for row in group : row [ - 1 ] = source cur . updateRow ( row ) del cur

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; } } }

Search Cursor to sort, group data with itertools, then sort on groups in list on lowest OBID

JamesRender

Hi,

I have a challenge to group data based on an attribute, then sort the groups and assign the lowest object ID's global ID to another field for the items in that given group. This needs to be iterated as there will be dozens, if not hundreds of groups. This is what I've come up with so far as an outline. Any pointers on this would be greatly appreciated:

<SPAN class="keyword token">import</SPAN> arcpy<SPAN class="punctuation token">,</SPAN> time<SPAN class="punctuation token">,</SPAN> datetime<SPAN class="punctuation token">,</SPAN> os<SPAN class="punctuation token">,</SPAN> itertools

fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'OBJECTID'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'LegacyGUID'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'GlobalID'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'LEGACYID'</SPAN><SPAN class="punctuation token">]</SPAN>
groups <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>
ukeys <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>


<SPAN class="keyword token">try</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">,</SPAN> sql_clause<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'ORDER BY LEGACYID'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> k<SPAN class="punctuation token">,</SPAN> g <SPAN class="keyword token">in</SPAN> itertools<SPAN class="punctuation token">.</SPAN>groupby<SPAN class="punctuation token">(</SPAN>cursor<SPAN class="punctuation token">,</SPAN> <SPAN class="keyword token">lambda</SPAN> x<SPAN class="punctuation token">:</SPAN> x<SPAN class="punctuation token">[</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; groups<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>list<SPAN class="punctuation token">(</SPAN>g<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ukeys<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>k<SPAN class="punctuation token">)</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
<SPAN class="keyword token">except</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; e <SPAN class="operator token">=</SPAN> sys<SPAN class="punctuation token">.</SPAN>exc_info<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; GroupFail <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Grouping Operation Failed at '</SPAN> <SPAN class="operator token">+</SPAN> str<SPAN class="punctuation token">(</SPAN>datetime<SPAN class="punctuation token">.</SPAN>datetime<SPAN class="punctuation token">.</SPAN>now<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">' '</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="punctuation token">(</SPAN>e<SPAN class="punctuation token">.</SPAN>args<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">'\n'</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; flog<SPAN class="punctuation token">.</SPAN>write<SPAN class="punctuation token">(</SPAN>GroupFail<SPAN class="punctuation token">)</SPAN>

flog<SPAN class="punctuation token">.</SPAN>write<SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">(</SPAN>groups<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token">#newgroups = sorted(groups, key=lambda x: x[0])‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Encuentra más publicaciones etiquetadas con

Accepted answers

All comments

DarrenWiens2

Perhaps you've considered and passed on this idea, but you can get the minimum OID for each group using Summary Statistics, then read those values directly as you cursor through the features, or join all at once rather than cursoring at all.

JamesRender

I'm able to get these groups into a list, but now need to sort each group by object ID and complete the replacement of the other records legacyID, with the lowest object ID's global ID per group.

JamesRender

So create a table of the lowest object ID with it's corresponding legacy ID, and global ID for each group, then join the table to the original feature class and replace values with the UpdateCursor?

That might be the easiest way, but thought there may be some other operators with PY or ArcPY that could get me their without leaving the script.

DarrenWiens2

With Summary Statistics, you can get a table with `Group, Min_OID_for_each_group`. Join this table to your original table by OID & Min_OID_for_each_group. Save the table or read to Python dictionary so you have `Group, Min_OID_for_each_group, GlobalID`, then either join to your original table by Group, or cursor through matching by group to update the LegacyID.

DarrenWiens2

You can run almost all ArcGIS functionality from Python, like:
Add Join—Data Management toolbox | ArcGIS Desktop

Summary Statistics—Help | ArcGIS for Desktop

JoshuaBixby

Do you want to update a feature class? Your current code is using a search cursor, which obviously can't update the feature class or table in question.

Where does LegacyGUID comes into play? At first glance, I don't see where it is being used.

JamesRender

Yes, clearly I'm not attempting to edit at this point in the code. But the end result is to replace the legacyGlobal ID per group of the lowest ob id's global id. So will need to edit after I have a table or list to match to the groups legacyID.

JoshuaBixby

You can do it all in one pass using an update cursor, if I understand what you are trying to do. I will post some code tomorrow.

UPDATE: In terms of specifics, there are still a couple of things I am unclear about. For example, where does LegacyGUID come into play. Also, what is the target field you want to update with the LegacyID of the lowest ObjectID of the group?

For the code below, I am using LegacyGUID as the target field to be updated, but it is simple to change the field. In the code below, case_fields can be multiple fields if you want the grouping of data to be based on more than one field.

<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">from</SPAN> itertools <SPAN class="keyword token">import</SPAN> groupby
<SPAN class="keyword token">from</SPAN> operator <SPAN class="keyword token">import</SPAN> itemgetter

fc <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to feature class or table</SPAN>
case_fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"LegacyID"</SPAN><SPAN class="punctuation token">]</SPAN>
sort_field<SPAN class="punctuation token">,</SPAN> sort_order <SPAN class="operator token">=</SPAN> <SPAN class="string token">"OBJECTID"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"ASC"</SPAN>
source_field <SPAN class="operator token">=</SPAN> <SPAN class="string token">"GlobalID"</SPAN>
target_field <SPAN class="operator token">=</SPAN> <SPAN class="string token">"LegacyGUID"</SPAN>

fields <SPAN class="operator token">=</SPAN> case_fields <SPAN class="operator token">+</SPAN> <SPAN class="punctuation token">[</SPAN>sort_field<SPAN class="punctuation token">,</SPAN> source_field<SPAN class="punctuation token">,</SPAN> target_field<SPAN class="punctuation token">]</SPAN>
sql_orderby <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ORDER BY {}, {} {}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>case_fields<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> sort_field<SPAN class="punctuation token">,</SPAN> sort_order
<SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">,</SPAN> sql_clause<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN> sql_orderby<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cur<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; case_func <SPAN class="operator token">=</SPAN> itemgetter<SPAN class="punctuation token">(</SPAN><SPAN class="operator token">*</SPAN>range<SPAN class="punctuation token">(</SPAN>len<SPAN class="punctuation token">(</SPAN>case_fields<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> key<SPAN class="punctuation token">,</SPAN> group <SPAN class="keyword token">in</SPAN> groupby<SPAN class="punctuation token">(</SPAN>cur<SPAN class="punctuation token">,</SPAN> case_func<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row <SPAN class="operator token">=</SPAN> next<SPAN class="punctuation token">(</SPAN>group<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; source <SPAN class="operator token">=</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row<SPAN class="punctuation token">[</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> source
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cur<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> group<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row<SPAN class="punctuation token">[</SPAN><SPAN class="operator token">-</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> source
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cur<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">del</SPAN> cur<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

JamesRender

Ah, not sure why i didn't think to just double sort in the sql clause...

To answer your question:

LegacyGUID is the field that is going to be updated with the GlobalID per group with the lowest Object ID's Global ID. Maybe this will help

1) Sort by legacyID

2) Group all like LegacyID's together

3) For groups with more than 1 member, calculate into the LegacyGUID field the lowest Object ID's Global ID per group.

4) For groups with 1 member, simply calculate the GlobalID into the LegacyGUID

Regarding your code, can you explain:

Joshua Bixby wrote:

&nbsp;&nbsp;&nbsp; case_func <SPAN class="operator token">=</SPAN> itemgetter<SPAN class="punctuation token">(</SPAN><SPAN class="operator token">*</SPAN>range<SPAN class="punctuation token">(</SPAN>len<SPAN class="punctuation token">(</SPAN>case_fields<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"></SPAN>

More specifically, what *range(len(case_fields))) is doing as a function, then as the grouping argument? Where in your code is it pulling the lowest object ID's global ID of each group?

Thanks for your help.

P1010775.JPG

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

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