Row transfer to Column

[naraj]

Hi,

I am going to transfer some biological data from rows to columns. So, as you can see what I’m going to do is to transfer the contents in “interaction-type” column to independent columns and then create columns for the interacting residues like:

example.xlsx (9.1 KB)

I tried to do it with Pivoting/Unpivoting nodes but was unsuccessful. I am wondering if anyone knows how to do it?

Best wishes

[ipazin]

Heloo @naraj,

you need separate branches as you are performing two different operations which need to be brought together eventually. One branch needs One to Many node and another simple pivoting. Finally you have to use some table manipulator nodes to get column names/order as desired…
2021_03_18_OnetoManyPlusPivoting_ipazin.knwf (57.4 KB)

Br,
Ivan

[bruno29a]

Hi @naraj , since we can’t know how many interaction-type a Compound ID might have, I use JSON to process/manipulate the data, and then convert back to columns. I also built the workflow so that it’s dynamic and not hardcoded with the interaction-type, as I do not know if you may have other interaction-types, so it will automatically adapt to any interaction-type you have (or if you add more in the future).

Here’s how the workflow look like:

Here’s my input data (same as yours):

Here’s my output data:

Here’s the workflow:
Row transfer to Column.knwf (12.9 KB)

[naraj]

Hi Ivan,

Thanks a lot for your time and the cool workflow!
Best wishes

[naraj]

Hi bruno29a,

This is really cool! Indeed I have different types of interactions! Very nice workflow! Thanks a lot!

[naraj]

Hi again,

In my data sets, some of molecules have more than one H-bonding interaction (or pi-stacking and etc., see attached). I want to add all the similar interactions of a molecule in one row (so one molecule can for example have three H-bonding interactions in one row). Is there any node to do this job? Currently, the “group by” node consider only one Molecule and eliminates the other similar interactions.

Manty thanks
example-2.xlsx (8.9 KB)

[bruno29a]

Hi @naraj , can you please show us what should the expected result be?

[naraj]

Hi @bruno29a

Please find attached the input and expected output in one xlsx file.
Thanks again for your time.

Cheers

example-2.xlsx (9.6 KB)

[bruno29a]

Hi @naraj , since my previous solution worked, I used the same approach, and simply found a way to add a counter per {Compound ID + Interaction Type}:

The rest continues as it was before, with the addition of Distance.

Input data (same as your file):

Results:

Workflow:
Row transfer to Column 2.knwf (32.1 KB)

Note: If you want the columns order to be different, you can do the sorting on the data somewhere between Node 30 and Node 33 (so before or after Node 35)

[naraj]

Hi @bruno29a

Thanks a lot for the workflow, it nicely works!

Cheers