Move the values of one row from one column to another

I have a dataset with this form:

ID  |  1   |   2   |   3    |   4   |
1   |  ?   |   ?   |   d    |   d   |
2   |  a   |   a   |   ?    |   ?   |
3   |  b   |   b   |   ?    |   ?   |
4   |  c   |   c   |   ?    |   ?   |

I want to get this:

ID  |  1   |   2   | 
1   |  d   |   d   | 
2   |  a   |   a   |  
3   |  b   |   b   |  
4   |  c   |   c   |  

So far I’ve used column merge, column filter and rename.

But this is a toy example, in my real data I have many columns, and this would take too much time.
Is there a way of displacing the columns?

Hi @RoyBatty296 , what is the “logic” being applied? Is it just moving data from right to left where the left columns are empty?

Any “tricky” scenarios?

EDIT: For example, what will be the results for this?

ID  |  1   |   2   |   3    |   4   |
1   |  a   |   ?   |   d    |   d   |
2   |  a   |   a   |   ?    |   ?   |
3   |  b   |   b   |   ?    |   ?   |
4   |  c   |   c   |   ?    |   ?   |
2 Likes

Hello @RoyBatty296
May this discussion apply in this case?

BR

2 Likes

Hi @RoyBatty296, and following on from @bruno29a 's questions, what would the rules be if you have many columns?

If say you had 20 columns, will it be that column 1 and 11 are merged, then 2 and 12, then 3 and 13 and so on? Or is there some other rule that would be followed?

btw, In your example, using Column Merger, you shouldn’t need column filter or rename, if you choose the appropriate options in the Column Merger node

image

image
image

image
image

But I can see if you have a large number of columns, that will be painful.

So, maybe the following can be of assistance.

The top part of the flow demos on a dataset how a series of chained Column Merge nodes might achieve what you wish but would be impractical on a very large number of columns.

The lower flow performs the same but with a loop. Overkill for a small number of columns but more scalable. Here a Table Creator is used to “feed” the list of Primary and Secondary columns to be used in the merge process. This list could be entered manually, or if your columns have logical sequential names, could possibly be sourced programatically.

image

image

Column Merging.knwf (30.6 KB)

3 Likes

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