Shift part of row to left or right

[achot]

Hi all, got an issue trying to achieve something quite basic in Excel, but in KNIME I am out of ideas.

As you can see in the attached pic, I have a couple of rows (far more in reality), where cell contents are not aligned. How can I manipulate/wrangle this in KNIME so I get my desired result.

E.g. row 3has an extra option with respect to row2, so I would need to insert one blank cell in row 2 in order that the content of the Explanation column is the same for all rows.

Row4 has useless content in cell1 so that cell needs to be deleted and the remainder of the row shift to left by one cell.

Basically given the data n the first pic I want to achieve the following outcome:

I tried string manipulation and Rule Engine but they just replace the content of the cell and not the cell itself. I want to create a workflow which helps me automatically solve this for 1000s of rows where the pattern for max 100 rows is as described above.

Would you recommend a Column Name extractor and manipulating data differently based on exception?

Does anyone have an idea, indication as to which tools I might explore to achieve this?

Muchas gracias!

[ArjenEX]

Hi @achot

Welcome to the KNIME community!

A way to solve this is via the Column Aggregator and splitting route.

After importing the data, I use a Column Expression node to cover all the business that you have. For example, the Outcome may only be Pass or Fail and otherwise make it null because it’s coming from somewhere else. Which I evaluate by

if (column("Outcome").equals("Pass") || column("Outcome").equals("Fail")) {
    column("Outcome")
} else {
    null
}

Similar to the Explanation column whereby I assumed that this should contain the words is the correct answer. Since you want to create a move to the right, I add a comma in front of the string for later usage. The condition check is done through the contains() function.

if (contains(column("Explanation"),"is the correct answer") == true) {
    "," + column("Explanation")
} else {
    column("Explanation")
}

After all rules have been applied, I create a “collection” of all column values through the Column Aggregator node whereby the method of aggregation is concatenate. Important here is to disable the Missing option. That way, nulls are excluded like with Row2 of Outcome.

I opt to only keep this collection column and therefore split it again with the set delimiter and the output as new columns. Mind the trick with the comma in the Column Expression node again, it comes into play here since I’m splitting it on the comma as well.

Only thing left is to rename the columns again, but that is a simple action.

See WF:
Shift part of row to left or right.knwf (26.2 KB)

Hope this provides some inspiration!

PS: Please include such a data file as attachment to your post next time, it takes a lot of time to manually all type it over into KNIME

[achot]

Perfect! It was quite simple, but somehow never thought of a concatenation:)