Sorting with lambda

Ignition
1

Hello all,

I'm struggling to figure out this simple problem after lots of research. I am attempting to sort a PyDataSet by ApprenticeGroup 0, then ApprenticeGroup descending, then hours descending, then seniority ascending. Here is my current code:

	returnedData = system.dataset.toPyDataSet(system.db.runNamedQuery("Overtime/Trade Tables/Mechanics Sat Sun", {"Date" :Date}))
	NewValue = sorted(
		dict(zip(returnedData.columnNames, list(row)))
		for row in returnedData,
		key = lambda columns: (1 if columns['ApprenticeGrp'] == 0 else 0, 
columns['ApprenticeGrp'], columns['SunHrs'], columns['Senority']),
		reverse = True)

When I put "-" in front of the columns['Senority'] to try and make it sort in ascending order, I am met with a TypeError as you can't do this to strings. How would I work around this? Is there a way?

Thanks in advance for taking the time to read this post/help me out.

2

Can you just sort it on the SQL side in the name query?

3 Likes
3

I can't, it's through a button with an interactive JSON table unfortunately.

4

Sorry, I'm not following, your code is clearly calling a Named Query, why can't you just enforce the sort order there?

1 Like
5

It runs the named query but does not follow the sorting unless I invoke it this way. It's a dataset vs a JSON table.

6

I'm not sure that matters. What component is displaying this data? A table? Tables will happily accept a dataset as opposed to a JSON structure. Or more properly, assuming this is for a perspective table (you haven't tagged the post but JSON is a good hint), then you can provide the sort order in the columns prop.

EDIT: A screen snip to show what I mean:

image
image1186×635 58.3 KB

1 Like
7

At some point, it's clearer and easier to just make a function and use that instead.

But, to the point.. Why not switch 0 and 1, and remove the reverse=True ?
edit: nevermind, didn't see you need a different order for the last column

Also, if you get a TypeError... you can cast things.

Can you give a sample input and expected output ? What do the senority values look like ?

1 Like
8

Define

def getSortOrderCode(senority):
	if senority == "A":
		return 1
	elif senority == "B":
		return 2
	elif senority == "C":
		return 3	
	elif senority == "M":
		return 4	
	elif senority == "P":
		return 5

and change the sort to

sortedData2 = sorted(
				dict(zip(returnedPyData.columnNames, list(row)))
				for row in returnedPyData,
				key = lambda columns: (1 if columns['ApprenticeGrp'] == 0 else 0, columns['ApprenticeGrp'], columns['SunHrs'],getSortOrderCode(columns['Senority'])
				),
				reverse = True)
1 Like
9 
return "ABCMP".index(senority)

But this only work for very simple cases though, where senority is only ever one letter and in those defined.
Which is why I'd really like a sample input.

1 Like
10

image
image1449×750 69.1 KB

These dropdowns are the reason I have to do them the way I do. These dropdowns can be edited from within the JSON table. If I use a regular system.db.runNamedQuery, I cannot have these dropdowns within the table. Sorry for not explaining better, I had a meeting and was in a rush. :confused:

11

So, for example this is the problem I'm currently trying to sort around:

image

These 3 operators all have the same amount of hours (576). After hours, they are chosen by seniority for who will get approved/denied. With this 3-way tie, Willey should get approved first, then Hade, then Malcolm. I can sort in descending, but not ascending so that's where I run into the problem I'm facing now.

12

Sure, but I am guessing that Seniority is a small enumerated list (apprentice, journeyman, master, etc.), and that list will be known and stable and managed here.
I'd put the list in the database and return SortOrder in the query, or several other options.
If senority is YearsOfService that has been stored as a string then
maybe cast the string as a number and multiply by -1.

More info would lead us to a more optimal solution.

1 Like
13

Seniority is a datetime from when they started, ApprenticeGroup is what you are thinking about with 0 being not an apprentice, 5 being bid/placed, 4 and down as years of apprentice. So Seniority is after they are already sorted by who is/isn't an apprentice if that makes sense.

14

If it is a date (or a string containing a date) then return total milliseconds of the date (or -1 * total_millis). Take into account any business rules if there can be gaps in service that count or don't count.

1 Like
15

try this:

now = system.date.toMillis(system.date.now())
data = sorted(
	system.dataset.toPyDataSet(ds),
	key=lambda (name, group, hours, seniority): (
		group == 0, group, hours, now - system.date.toMillis(seniority)
	),
	reverse = True
)

initial dataset:

name group hours senority
steve 0 488 07-06-0099
jim 5 476 10-09-2000
ron 4 472 10-16-2000
mark 0 409 09-08-1998
mike 3 407 06-28-2010
stacy 3 402 03-20-1995
brent 0 401 10-30-1995
chuck 5 401 01-06-2023
howard 4 395 06-28-2010

resulting dataset:

name group hours senority
steve 0 488 07-06-0099
mark 0 409 09-08-1998
brent 0 401 10-30-1995
jim 5 476 10-09-2000
chuck 5 401 01-06-2023
ron 4 472 10-16-2000
howard 4 395 06-28-2010
mike 3 407 06-28-2010
stacy 3 402 03-20-1995

adjust things to match the order you need, but it should get you started.
Also, you might not need now at all, simply putting a minus sign should be enough

edit: yes, steve's been there for 2000 years.

4 Likes
16

Steve is a trooper! We need more people like Steve. This did the trick, thank you very much!