Sorting a table based on if-then statement

Question

I have a large table with two attribute fields "layers" and "Meas" both contain numbers, I would like to sort "Meas" in the odd numbered "layers" in ascending order, and "Meas" in the even numbered "layers" in Descending order. This would be part of a larger model in modelbuilder. I am using Arc Desktop, so "If Value Is" tool is not available.

This feels like it should be easy to do, however I have tried unsuccessfully to code something in python and am at a loss about how to approach the issue, any suggestions?

What I have:

[ID,  Layer,  Meas],
[1,     1,     3],
[2,     1,     2],
[3,     1,     1],  
[4,     1,     5],
[5,     2,     3],
[6,     2,     2],
[7,     2,     5], 
[8,     3,     3],
[9,     3,     2],
[10,     3,     1],  
[11,     3,     5]

What I want:

[ID,  Layer,  Meas],
[3,     1,     1],
[2,     1,     2],
[1,     1,     3],
[4,     1,     5],
[7,     2,     5], 
[5,     2,     3],
[6,     2,     2],
[10,     3,     1], 
[9,     3,     2],
[8,     3,     3],
[11,     3,     5]

Perhaps modify one from https://gis.stackexchange.com/questions/193681/calculating-sequential-numbers-into-sorted-table-using-arcgis-desktop/193684#193684 the only difference they both ascending. — FelixIP, Dec 11 '19 at 03:46
An alternative, non-code approach and to keep it all in model builder is to select your rows where layer is an odd number and then use the SORT tool. Then select your rows where layer is even and run the SORT on that selection. Then merge the two outputs into a single dataset. — Hornbydd, Jan 13 '20 at 15:04

score 1 · Answer 1 · answered Dec 10 '19 at 14:10

I'm giving you an idea, you can automate this in Python.

First, you can build a list for each value in the "layer" field, and you will get 3 lists:

list_layer_1 = [[1,     1,     3], [2,     1,     2], [3,     1,     1], [4,     1,     5]]
list_layer_2 = [[5,     2,     3], [6,     2,     2], [7,     2,     5]]
list_layer_3 = [[8,     3,     3], [9,     3,     2], [10,     3,     1], [11,     3,     5]]

In a second step, you will sort each list on your "Meas" field. To do this, you use the Python exited method, the "operator" module, you specify your list, the index of the sort field (Meas is the 3rd field so its index is equal to 2) and the hierarchical direction of the sort. You can execute the following commands with this syntax:

your_list = sorted(your_list, key = operator.itemgetter(index of your sort field), reverse = True or False)

# First list
list_layer_1 = sorted(list_layer_1, key = operator.itemgetter(2))
# Result 
[[3, 1, 1], [2, 1, 2], [1, 1, 3], [4, 1, 5]]

# Second list
list_layer_2 = sorted(list_layer_2, key = operator.itemgetter(2), reverse = True)
# Result
[[7, 2, 5], [5, 2, 3], [6, 2, 2]]

In a third step, you combine the 3 lists:

final_list = list_layer_1 + list_layer_2 + list_layer_3

You can automate this with a loop system with conditions to know the sorting direction.

thanks, My python is pretty rusty, and I managed to get a series of lists, however when I try to sort these, I get a Parsing error: My Code: import arcpy import operator outfile = C:\start.gdb\Layer1 outfile = sorted(outfile, key = operator.itemgetter(2)) — E. Ballent, Dec 10 '19 at 20:13
I have a table in a geodatabase, and I have used the iterate row selection tool to select groups of rows which have the same Layer number. The piece I am missing is how to turn this selection into a list which I can then use to sort — E. Ballent, Dec 11 '19 at 19:39
You must select change element with the same layer number and then iterate on each selection. You have to make several loops. — Vincent Bré, Dec 12 '19 at 07:59

BERA · Answer 2 · 2020-01-13T15:16:15.620

Create two lists, one with even and one with odd numbers. Then sort each and combine. I copy-pasted your data into a csv file.

import arcpy, os

testfile = r"C:\GIS\data\testdata\sortme.csv"
outdb = r"C:\GIS\data\testdata\testdatabas.gdb"
outtable = 'sorted2'

arcpy.MakeTableView_management(in_table=testfile, out_view='tview')
arcpy.TableToTable_conversion(in_rows='tview', out_path=outdb, out_name=outtable) #Create table from view
arcpy.TruncateTable_management(os.path.join(outdb,outtable)) #Empty it

fields = ['Layer','Meas', 'ID']

rows = [i for i in arcpy.da.SearchCursor('tview', fields)] #List all rows
even = list(filter(lambda x: (x[0]%2==0), rows)) #filter out even Layer numbers (Layer is first element in fields list = index[0]
odd = list(filter(lambda x: (x[0]%2!=0), rows)) #odd

even_sorted  = sorted(even, key=lambda x: x[1], reverse=True)
odd_sorted  = sorted(odd, key=lambda x: x[1])
both = sorted(even_sorted+odd_sorted, key=lambda x: x[0])

icur = arcpy.da.InsertCursor(os.path.join(outdb,outtable), fields)
for row in both:
    icur.insertRow(row)

del icur

Sorting a table based on if-then statement

2 Answers2