Move the values of one row from one column to another

[RoyBatty296]

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?

[bruno29a]

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   |   ?    |   ?   |

[gonhaddock]

Hello @RoyBatty296
May this discussion apply in this case?

Filter columns with missing values per row up to the column that has a value

Okay… I have a table with donations per donor (rows) per year (column). This is what my table could look like: 2010 2011 2012 2013 Row1 ? 5 10 5 Row2 ? 20 ? 20 Row3 10 ? ? ? Row4 5 5 5 5 Row5 ? ? 50 10 I want to transform this to a table that tells me how much someone donated in their first, second, etc. year. This is what I want it to look like: Year 1 Year 2 Year 3 Year 4 Row1 5 10 5 ? Row2 20 ? 20 ? Row3 10 ? ? ? Row4 5 5 5 5 Row5 50 10 ? ? I don’t wa…

BR

[takbb]

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

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.

Column Merging.knwf (30.6 KB)