create columns from rows

Hi,

I’ve got a dataset which looks like this

image

and so on.

I would like to create a table which looks more like this:

image

and so on.

So sort of like transpose it but at the same time using something like groupby, because I want to transpose it but because the cells are several times repeating, if I transpose it I get as many columns as I have rows but I only want as many columns as cells I have, then list the values of each cell below its own column. Also, it would be great if I could merge headers as shown but if it’s impossible it’s also good if it just says cella_1 over the time and the voltage in each column. I thought about loops or something but i really am a beginner KNIME user so I just can’t figure this out.

I hope I was clear and I would be grateful if you could help me with this.
Thank you

Welcome to the forum @BendegzHegedus

A KNIME table isn’t a spreadsheet. You can’t have multiple header rows. And ideally each row of data should be a unique record. In my view, the proposed new table would require a lot of manipulation to create and would make further processing quite difficult, so I wonder if it’s the best solution to your problem.

I would suggest using a Pivoting node. Group by “time”, pivot on “cell”, and aggregate on “voltage” using First:

image

Others here might have better ideas.

1 Like

You would have to do this by writing to Excel or another external format. As @elsamuel mentioned, KNIME needs to be based off of a standard structured raw data table.

I think you would have to setup the 1st row merged column headers and then use the Excel Cell Updater to place the pivoted table info below them.

Or you could try to use this on your top row during export.

1 Like

Hello @BendegzHegedus

Your challenge output results to be a ‘nested hierarchy in column header’ table. And as you pointed out, it’s somehow based in grouping: then the best choice is a Group Loop node and some column name rearrangement.

What is conceptually missed in your description is an output ‘row sense’ column, further than a row ID. In this case could be something like measurement_round …

Your workflow could be something like this; considering the data is sorted by time:

BR

PS.- Be aware that once your dada is arranged in this way, it couldn’t be usable for some analysis or visualizations, and return it back to original shape can require complex back transformations.

2 Likes

PS2.- This is the upper most hierarchy level.

@BendegzHegedus you could try and adapt this example of how to create an Excel file with a complex header

Hi,

thanks for all the advice on merged headers but the really important thing would be to format the table as I described, because the headers could look like in @gonhaddock and it is perfectly fine like that as well.

Wow this actually solved it, after i figured out what’s going on! Thank you very much! Although the java edit part is still unclear but I simply renamed the voltage columns with the flow variable cell so now it says time and the number of the cell in the header, but I think that will do.

1 Like

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