Re-binning Mixed Column of Simple Numbers

[wisemanleo]

I’ve got a column that looks like this:

Blank
0
New
2
0 - 5
6-10
7
8
11-15
16-30
21
22
31-50
51-99
63

I’m looking to take the true numbers and place them in the bins as the other rows are in. In the above example, these would be:
0 to New (this is already done with String Replacere)
2 to 0-5
7 to 6-10
8 to 6-10
21 to 16-30
22 to 16-30
63 to 51-99

I’ve tried to use Rule Engine, a rule such as:
$col$ > 0 AND $col$ =< 5 binned cells that were not in this range.

Any tips would be appreciated.

[gonhaddock]

Hello @wisemanleo

Please find attached a possible solution for your challenge:

20230222_Re-binning_Mixed_Column_v1.knwf (33.5 KB)

BR

P.S.- You can’t use this syntax in your Rule Engine as your column is string type.

[gonhaddock]

Sorry @wisemanleo , because reviewing it, I misunderstood the logic of the problem in the first attempt…

This could be a valid solution for your case:

20230222_Re-binning_Mixed_Column_v3.knwf (57.6 KB)

BR

[wisemanleo]

Ah! I was going to reply to say the logic may have been interpreted too literally , but wanted to at least take some time to try to learn the approach - but alas, you beat me to it :).

Thanks very much - I think this should work!!!

[gonhaddock]

@wisemanleo
I’ve just updated the workflow to v3 (edit), because an edition change in Rule Engine code.

Thank you for validate the solution

[wisemanleo]

One small additional puzzle - so the initial row set is 14 rows, but the final is 7. The logic currently excludes those that are already “binned” as “False”. I took a pass at “quarantining” those rows that were already binned, and then merging them back in at the end (see column “Approx Age Label”) in the last node. Still landing at a difference of 13 rows vs 14.

I attached an updated workflow.

20230222_Re-binning_Mixed_Column_v3.5.knwf (42.8 KB)

[gonhaddock]

Hello @wisemanleo

Two false are unassigned (‘11-15’ and ‘31-50’) in results out from 7 binned falses including ‘New’; that represents 5 of them in the result; and two of them have double match. The seven upper rows represent the ‘binned trues’

The error comes from the filter allocated in the first by-pass “Set aside already binned rows” as it is not considering ‘New’ as ‘binned false’… here is the place where you loosed the missed row. But visually you see the ‘New’ false in the upper rows, however it represents the reference digit 0

You can make it more clear by merging the first column assigning the reference digits instead the assigned falses.

BR

[wisemanleo]

Ah - those errors are on point. Thanks very much. I’m not sure I understood the merge tip, but I set aside “New” in the same manner as the other already binned set. I might be doing this workflow in way too may nodes, but that’s perhaps a challenge for a different day.

Hopefully I may ask one last question - I’ll have to connect these “age buckets” back to their respective rows amongst other headers (sample columns in original Table Creator), but any advice on how I might reconnect them back? The final column is “shuffled”, so to speak.

20230222_Re-binning_Mixed_Column_v3.6.knwf (49.9 KB)