Skip to main content
  • EN

This is the final step to some complicated data sorting - so as an example lets say I have the following data:

 

indexequipmentNamesequenceequipCount1a102b103c204b215c316d307d418b429b5310e5011b6412e6113b7514f70

 

The goal is to get data ordered like this: abbccdd... etc. This works by sorting the sequence column ascending and the equipCount column (unique counter of equipment name) descending. The problem is that if there is a sequence pair that does not contain a zero (both pieces of equipment have appeared before) then its no longer beneficial to sort equipCount descending, instead it needs to be sorted ascending(see index 7 and 8 sequence pair 4, they should not reverse order 2 to 1; and index 11 and 12 sequence pair 6, they should reverse order to be 1 to 4). Again this is all to get the final order to be abbccddbbeebbf. My solution to this was to check if each equipCount value in a sequence pair was not zero, and then to make even numbers = 0, because an even unique count means it is the first of a new equipment pair and needs to come at the end of a sequence pair, the reverse being true for odd numbers - they need to come at the beginning of a sequence pair because they are rounding out an equipment pair. I am trying to accomplish this with a python caller, but I either can't get the syntax right, or do not know how to iterate and index through an attribute of a feature (compare row index 7 equipment count to row index 8 equipment count) in a for loop.

 

The following sudo-code will produce the result... but I am struggling with implementation

 

for rows (i) in table

if sequence(i) == sequence (i+1)

if equipCount(i) * equipCount(i+1) != 0

if modulus 2 of equipCount(i) == 0

equipCount(i) = 0

elif modulus 2 of equipCount(i+1) == 0

equipCount(i+1) = 0

 

I am also open to doing this kind of comparison and conditional attribute calculation without a python caller... but couldn't think of anything clever.

 

Thanks in advance.

Hi @goskoog

I'm not quite sure I understand your requirements fully, but would the attached workspace sort the features in the correct order? There's no Python involved and makes use of the Aggregator and list sorting sortnopython.zip

Hi @goskoog

I'm not quite sure I understand your requirements fully, but would the attached workspace sort the features in the correct order? There's no Python involved and makes use of the Aggregator and list sorting sortnopython.zip

@debbiatsafe thanks for the reply, I was unable to open that workspace - problem on my end. While I resolve it I’ll try to elaborate the original requirements.

My apologies on the lack of clear requirements. I'll back up to the original form of the data, because there may be an entirely better way to do this than the approach I started with. The dataset is representing connections between pieces of equipment (each row represents a connection from a to b, from b to c, and so on). A number of connections may be categorized under one group/purpose, and the sequence number details the order in which connections occur between the "a" equipment and the "z" equipment. The problem is this data is not structured left to right, as in the next rows equipment 1 is not always the previous rows equipment 2, rather equipment 1 and 2 in that row have swapped places. Here's an example set:

groupsequenceequipment 1equipment 2a1ab

 

a2cba3cda4dz

 

it needs to be restructured so that each piece of equipment has its own row like this:

groupsequenceequipmenta1.1aa1.2ba2.1ca2.2ba3.1ca3.2da4.1da4.2z

 

and then reordered so the final table is this:

groupsequenceequipmenta1.1aa1.2ba2.2ba2.1ca3.1ca3.2da4.1da4.2z

 

In this example sequence 2.1 and 2.2 were swapped. This is one of the cases I was trying to describe in the initial post by counting each occurrence of equipment within a group and sorting by that number - making even numbers 0 when they were part of a pair without a 0 to get the correct sorting.

 

Thank you for your time.

For the second option with the original data, could you not just use a pair of attribute creators and a sorter to get the ordering you want?

 

 

sequenceordering.fmwt

For the second option with the original data, could you not just use a pair of attribute creators and a sorter to get the ordering you want?

 

 

sequenceordering.fmwt

@jdh that works perfect for the sample data, however the names of the equipment are not in alphabetical order in the real dataset, which breaks that final sorter if I'm looking at it right.

@debbiatsafe thanks for the reply, I was unable to open that workspace - problem on my end. While I resolve it I’ll try to elaborate the original requirements.

My apologies on the lack of clear requirements. I'll back up to the original form of the data, because there may be an entirely better way to do this than the approach I started with. The dataset is representing connections between pieces of equipment (each row represents a connection from a to b, from b to c, and so on). A number of connections may be categorized under one group/purpose, and the sequence number details the order in which connections occur between the "a" equipment and the "z" equipment. The problem is this data is not structured left to right, as in the next rows equipment 1 is not always the previous rows equipment 2, rather equipment 1 and 2 in that row have swapped places. Here's an example set:

groupsequenceequipment 1equipment 2a1ab

 

a2cba3cda4dz

 

it needs to be restructured so that each piece of equipment has its own row like this:

groupsequenceequipmenta1.1aa1.2ba2.1ca2.2ba3.1ca3.2da4.1da4.2z

 

and then reordered so the final table is this:

groupsequenceequipmenta1.1aa1.2ba2.2ba2.1ca3.1ca3.2da4.1da4.2z

 

In this example sequence 2.1 and 2.2 were swapped. This is one of the cases I was trying to describe in the initial post by counting each occurrence of equipment within a group and sorting by that number - making even numbers 0 when they were part of a pair without a 0 to get the correct sorting.

 

Thank you for your time.

Thanks for the additional information! Please see the reuploaded workspace here sortnopython.fmwt

Thanks for the additional information! Please see the reuploaded workspace here sortnopython.fmwt

The aggregator, list sorter, list exploder was the methodology that did the trick! Thanks for the help!

The aggregator, list sorter, list exploder was the methodology that did the trick! Thanks for the help!

You're welcome!

Based on the original form of your data, there may be an easier way to achieve what you need. This workspace takes the original form of your data and outputs the features in the order desired. sortingbasedonoriginaldata.fmwt

Reply


Community Stats

30,159
Posts
114,479
Replies
37,803
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.