Unique binning challenge
Greetings y’all!
I’ve ran through the different options of binning nodes in Knime and could not find a workaround on my challenge. Hopefully someone can help with this.
I have a table of 3 columns as such:
INPUT A:
| quantity | group name | cumulative aggregate |
| 2 | A | 2 |
| 333 | B | 335 |
| 100 | B | 435 |
| 5555 | D | 5990 |
| 11 | X | 6001 |
| 0 | B | 6001 |
| 80 | A | 6081 |
| 1000 | R | 7081 |
| 1111 | Y | 8192 |I want to create an equal-width bin groups in 1000’s, except for the remaining values which will be the last row, as such:
OUTPUT A:
| bins | initial group | ending group |
| 1000 | A | A |
| 1000 | A | A |
| 1000 | A | A |
| 1000 | A | A |
| 1000 | A | A |
| 1000 | A | D |
| 1000 | D | R |
| 1000 | R | Y |
| 192 | Y | Y |Here’s some context:
There are no missing values in any cell.
The initial group name comes from the first group name each time we first started counting from 0 to 1000.
The last group name is simply the last group we ended up with on the 1000th count (or the remainder).
Here’s another scenario of an input and output table, following similar rules as above:
INPUT B:
| quantity | group name | cumulative aggregate |
| 5000 | C | 5000 |
| 0 | X | 5000 |
| 0 | H | 5000 |
| 100 | V | 5100 |
| 500 | C | 5500 |
| 20 | D | 5520 |
| 10000 | X | 15520 |OUTPUT B:
| bins | initial group | ending group |
| 1000 | C | C |
| 1000 | C | C |
| 1000 | C | C |
| 1000 | C | C |
| 1000 | C | H |
| 1000 | H | X |
| 1000 | X | X |
| 1000 | X | X |
| 1000 | X | X |
| 1000 | X | X |
| 1000 | X | X |
| 1000 | X | X |
| 1000 | X | X |
| 1000 | X | X |
| 1000 | X | X |
| 520 | X | X |Let me know if you need anything else, thanks a lot!