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

Ordering fields within arcpy cursor to iterate groups and delete extras not working properly

MollyMoore

I am attempting to order based on two fields within an arcpy cursor so that I can then eventually delete the rows I do not want. Below is an example of the table I am working with:

Within each grouping of "El_Season" that is the same (in this case OIDs 1&2 and OIDs 3&4) I would like to sort based on "COUNT_OccProb" and by "prob_code" in descending order within a cursor so that once sorted, I can delete the extras within groups that have lower values. Currently, the code does not sort successfully and is just printing OIDs 2 and 4 as the extras where it should be printing OIDs 1 and 3 if working as I would like. This is the code that I am working with:

<SPAN class="">case_fields </SPAN><SPAN class="">=</SPAN><SPAN class=""> </SPAN><SPAN class="">[</SPAN><SPAN class="">"El_Season"</SPAN><SPAN class="">]
</SPAN><SPAN class="">max_field </SPAN><SPAN class="">=</SPAN><SPAN class=""> </SPAN><SPAN class="">[</SPAN><SPAN class="">"COUNT_OccProb"</SPAN><SPAN class="">,</SPAN><SPAN class=""> </SPAN><SPAN class="">"prob_code"</SPAN><SPAN class="">]
</SPAN><SPAN class="">sql_orderby </SPAN><SPAN class="">=</SPAN><SPAN class=""> </SPAN><SPAN class="">"ORDER BY {}, {} DESC"</SPAN><SPAN class="">.</SPAN><SPAN class="">format</SPAN><SPAN class="">(</SPAN><SPAN class="">case_fields</SPAN><SPAN class="">,</SPAN><SPAN class=""> </SPAN><SPAN class="">","</SPAN><SPAN class="">.</SPAN><SPAN class="">join</SPAN><SPAN class="">(</SPAN><SPAN class="">max_field</SPAN><SPAN class="">))

</SPAN><SPAN class=""></SPAN><SPAN class="">with</SPAN><SPAN class=""> arcpy</SPAN><SPAN class="">.</SPAN><SPAN class="">da</SPAN><SPAN class="">.</SPAN><SPAN class="">UpdateCursor</SPAN><SPAN class="">(</SPAN><SPAN class="">statistics</SPAN><SPAN class="">,</SPAN><SPAN class=""> </SPAN><SPAN class="">"*"</SPAN><SPAN class="">,</SPAN><SPAN class=""> sql_clause</SPAN><SPAN class="">=(</SPAN><SPAN class="">None</SPAN><SPAN class="">,</SPAN><SPAN class=""> sql_orderby</SPAN><SPAN class="">))</SPAN><SPAN class=""> </SPAN><SPAN class="">as</SPAN><SPAN class=""> cursor</SPAN><SPAN class="">:
&nbsp;&nbsp;&nbsp; </SPAN><SPAN class="">case_func </SPAN><SPAN class="">=</SPAN><SPAN class=""> itemgetter</SPAN><SPAN class="">(*(</SPAN><SPAN class="">cursor</SPAN><SPAN class="">.</SPAN><SPAN class="">fields</SPAN><SPAN class="">.</SPAN><SPAN class="">index</SPAN><SPAN class="">(</SPAN><SPAN class="">fld</SPAN><SPAN class="">)</SPAN><SPAN class=""> </SPAN><SPAN class="">for</SPAN><SPAN class=""> fld </SPAN><SPAN class="">in</SPAN><SPAN class=""> case_fields</SPAN><SPAN class="">))
&nbsp;&nbsp;&nbsp; </SPAN><SPAN class=""></SPAN><SPAN class="">for</SPAN><SPAN class=""> key</SPAN><SPAN class="">,</SPAN><SPAN class=""> group </SPAN><SPAN class="">in</SPAN><SPAN class=""> groupby</SPAN><SPAN class="">(</SPAN><SPAN class="">cursor</SPAN><SPAN class="">,</SPAN><SPAN class=""> case_func</SPAN><SPAN class="">):
</SPAN><SPAN class="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; next</SPAN><SPAN class="">(</SPAN><SPAN class="">group</SPAN><SPAN class="">)
</SPAN><SPAN class="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><SPAN class="">for</SPAN><SPAN class=""> extra </SPAN><SPAN class="">in</SPAN><SPAN class=""> group</SPAN><SPAN class="">:
</SPAN><SPAN class="">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><SPAN class="">print</SPAN><SPAN class=""> extra </SPAN><SPAN class=""># currently have it printing extra instead of deleting for testing purposes</SPAN>

Najděte více příspěvků označených jako

Accepted answers

JoshuaBixby

From Using in-memory workspace—Help | ArcGIS Desktop (see orange text):

The following considerations must be made in deciding to write output to the in-memory workspace:
Caution:
Data written to the in-memory workspace is temporary and will be deleted when the application is closed.
Tables, feature classes, and rasters can be written to the in-memory workspace.
The in-memory workspace does not support extended geodatabase elements such as subtypes, domains, representations, topologies, geometric networks, and network datasets.
Feature datasets or folders cannot be created in the in-memory workspace.

From SearchCursor—Help | ArcGIS Desktop :

An SQL postfix clause is positioned in the second position and will be appended to the SELECT statement, following the where clause. The SQL postfix clause is most commonly used for clauses such as ORDER BY.
Note:
DISTINCT, ORDER BY, and ALL are only supported when working with databases. They are not supported by other data sources (such as dBASE or INFO tables).
TOP is only supported by SQL Server and MS Access databases.

Although it isn't explicitly stated, SQL postfix clauses aren't really supported with in-memory workspaces. Although tables, feature classes, and other data types can be stored in-memory, the in-memory workspace is not a fully functional DBMS workspace.

All comments

MollyMoore

I am working with a table within a file geodatabase. The field types by which I am trying to sort are long and short. I investigated with just the search cursor and printed out the ordered rows and could not get it to order properly.

Here is a link to a zipped folder with the geodatabase that contains the table: https://app.box.com/s/qhgshw0wdld4v9b49rp4m7ub7mkk0sqb

Here is a picture of the table:

The outcome that I would like to achieve is that for each grouping of identical El_Season entries, I would like to delete that which has a lower COUNT_OccProb value. If the COUNT_OccProb values are the same, as is the case for OIDs 3 and 4, I would like to delete the record with the lower prob_code value.

At this point, I cannot even get the cursor to sort by one of the fields and I am not sure why.

JoshuaBixby

Before involving groupby or any other itertools, maybe just print out some statements with a search cursor using the same parameters to see if the sorting from the DBMS engine is what you expect. If so, then you can dive into the Python code.

What storage format are you working with? Shape files, file geodatabase, enterprise geodatabase? Can you upload a small sample/subet of your data?

MollyMoore

This is still giving the same result and returning the last record per group according to OID instead of desired sorted fields.

JoshuaBixby

Barring any issues with the cursor code, you weren't constructing the ORDER BY clause correctly. Try the following and see if it gives you expected results:

case_fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"El_Season"</SPAN><SPAN class="punctuation token">]</SPAN>
max_field <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"COUNT_OccProb"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"prob_code"</SPAN><SPAN class="punctuation token">]</SPAN>
sql_orderby <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ORDER BY "</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"{} DESC"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>field<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> field <SPAN class="keyword token">in</SPAN> case_fields <SPAN class="operator token">+</SPAN> max_field<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>statistics<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"*"</SPAN><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> cursor<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><SPAN class="punctuation token">(</SPAN>cursor<SPAN class="punctuation token">.</SPAN>fields<SPAN class="punctuation token">.</SPAN>index<SPAN class="punctuation token">(</SPAN>fld<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> fld <SPAN class="keyword token">in</SPAN> case_fields<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>cursor<SPAN class="punctuation token">,</SPAN> case_func<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; next<SPAN class="punctuation token">(</SPAN>group<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> extra <SPAN class="keyword token">in</SPAN> group<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> extra <SPAN class="comment token"># currently have it printing extra instead of deleting for testing purposes</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>

JoshuaBixby

Just a side note while I look over this. Your use of OID[1,2] for groups is very confusing. In Python, square brackets are used for looking up items by an index value. On the surface, OID[1,2] looks like you are trying to slice a list named OID and return values 1 and 2. I think it would be more clear to go with something more like plain English and less like Python code, e.g., OIDs 1&2 and OIDs 3&4

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Přihlásit se
Registrovat

Rychlé odkazy

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