# Sorting results after binning

Hi

I have an input file with a column where I create 8 Bins with equal frequency and use Borders as Bin naming, see forum_question.knwf (96.6 KB)
Now I want to sort the bins in a readable order like this, e.g. to visualise it in a Bar Chart. But the sorting sees the comma, as a decimal-seperator, where I expect a dot of nothing (because the represents thousands)
After sorting the order looks like this
(0,82]
(1,015,1,638]
(1,638,1,826]
(210,430]
(430,690]
(690,1,015]
(82,210]
[0,0]

This is what I need (with or without the dot as a thousand seperator).
[0,0]
(0,82]
(82,210]
(210,430]
(430,690]
(690,1.015]
(1.015,1.638]
(1.638,1.826]

Any ideaâ€™s where to look what to do? Replace the comma in the middle by something else? But what if there are 2/4/6 commaâ€™s.

Tnx Hans

Hi @HansS -

Like this?

I ended up binning twice - once using borders and once using numbered for the bins. Then joined the results by rowID, sorted on the binned version, and plotted the bordered version.

Itâ€™s hacky, but maybe it works for you?

forum_question_SF.knwf (152.5 KB)

2 Likes

Unfortunately, the Auto-Binner has no option to choose a separator (feature request @ScottF? ), so it produces 1 to 3 commas. Since your input is integers only I changed the default setting to 0 decimals. Then I extracted the limits of each interval and sorted by those 2 new columns.

Not sure if I like Scottâ€™s solution more though

Since this will only work with integers, I can try to come up with some other replacement trick to get rid of the commas if neither solution works for you.

edit: I â€śfixedâ€ť it by doing more sophisitcated replacing, so that it works with decimals as well, but I realised that my logic will break for numbers bigger than 1 million. Guess Iâ€™ll stop experimenting and actually do that feature request

sorting results after binning.knwf (138.0 KB)

1 Like

Hi @ScottF

Tnx for replying. A creative solution (need some extra steps when there are >9 Bins) . I used the barchart as an example of the â€śclumsyâ€ť sort order. I need the numbers from binning mulitple columns in a datatable as (part of) my input for a Tableau dashboard.

Hi @Thyme

I think your directions may lead to a solution. I donâ€™t expect to encounter numbers bigger than 1 million, and as far as I know there are no decimals in the dataâ€¦?! So Iâ€™ll give it a try,
But it would be so much easier if the Auto-Binner node has an option to choose a seperator (@ScottF ).

If you only have integers, then using the advanced formatting options is the way to go in your case. For sorting the bins, I think Scotts solution is more robust.

For future people:
I collected all 3 methods into a single WF and added a String Manipulator to Scottâ€™s WF so it can sort more than 9 bins.

sorting results after binning.knwf (362.2 KB)

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.