Challenging (?) Task - Grouping Marked Rows

[badger101]

Hi all, I’d like to throw this out. The input table has this pattern:

The task is to use the marking from Column 2 to group together rows from Column 1 to get this table:

The solution must satisfy these conditions:

1. The first row can sometimes be marked X on Column 2, and can sometimes be a missing value.
2. The last row is always marked X.
3. I’m looking for a fully automated solution.

Please have a go at this.

Thanks!

[mlauber71]

@badger101 you could adapt this workflow and use the blocks to fill in the desired markers with a Counter creator and the use the Group By node to create the “Group Members”. Maybe you upload a file that would represent your complete task

Move head rows to a column

@willy_oracle typically you would mark all lines with a Counter. Then identify the ‘headers’ with a rule engine [IF Col1 LIKE “Head*” => Counter] and keep that (starting) row in a separate column. Then you fill the missings with the previous value so a block would emerge marking the data table: [image] So now you have the blocks marked an can use a Table Row To Variable Loop Start – KNIME Hub or Group Loop Start – KNIME Hub to do something with the blocks (export them delete lines with missing…

[badger101]

@mlauber71 The group loop method was one of the first things I’ve tried. It won’t work since my markings are not unique. The tables you see represent the true pattern of my dataset.

Update: Ah never mind what I said, I see what you mean with the Rule Engine. Let me have a try on that. Will update here.

[mlauber71]

@badger101

The tables you see represent the true pattern of my dataset.

If you could provide them someone might have a try

[badger101]

Done! Thanks to your suggestion with the Rule Engine+Counter, it works now! The only difference is that my table will use the ‘Next value’ for the Missing Values replacement. @mlauber71

[HansS]

Hi @badger101

See this wf challenging.knwf (44.7 KB).

gr. Hans

[badger101]

Thank you @HansS , you were 1 second late But I love that your solution doesn’t require looping. Can be a great alternative for larger datasets.

[Daniel_Weikert]

Without having the table I assume Moving aggregation should also do the trick
br and enjoy your weekend guys