Pivoting fails with table having only strings

Dear KNIMErs,
I find your workbench really super, congrats. If I manage to get some results which makes sense I will consider going for the COM version of it.
I got a table with 2 columns:

DRUGNAME AdverseEffect
ABACAVIR SULFATE ARRHYTHMIA
ABACAVIR SULFATE ARRHYTHMIA
ABACAVIR SULFATE DEATH
ABARELIX DEATH
ABARELIX Electrocardiogram QT prolonged

I would like to convert it to the following format:

DRUGNAME ARRHYTHMIA DEATH Electrocardiogram QT prolonged
ABACAVIR SULFATE 1 1 0
ABARELIX 0 1 1

For this purpose, I tried to use the “Pivoting” Node however unfortunately it reports the error
The dialog cannot be opened for the following reason: No column in spec compatible to “DoubleValue”.
What shall I do ?

I guess I will go for an R function since that is something well done there.

Hi Luca,

Just in case you have problems with R node I might suggest using my KNIME utilitiesPivot node. Does not gives exactly what you want, but in my opinion it is easy to convert to your needs. Here is the result table:
“DRUGNAME_ARRHYTHMIA”,“DRUGNAME_DEATH”,“DRUGNAME_Electrocardiogram QT prolonged”
“ABACAVIR SULFATE”,“ABACAVIR SULFATE”,?
?,“ABARELIX”,“ABARELIX”

(It is not trivial to use it (you need to calculate the domain of Adverse Effect, and have this kind of order of your values), so if you found an easier way it does not worth trying this one. Although if you are interested I can put a workflow somewhere with these.)
Cheers, gabor

Sorry about bothering you again. It is absolutely doable with Pivoting node. :slight_smile: I just realized that adding a column with 1s does the trick here. (So no need for my node, or R.) (Actually it gives 2,1,0;0,1,1 for your data, but you might set a different option to give your preferred one.)
Bests, gabor

Dear Gabor,
thankyou for your help and creativity. However meanwhile I’ve solved it in R with a very simple way:
R<-table®

THANKS A LOT !!!

Well, it at least remembered me that there are great treasures in R. :slight_smile: Thanks, gabor

Thanks for the problem report (and the workaround). We have fixed that problem on the trunk, it will be available in v2.1 (no need to add a new column with '1’s then anymore).

Thanks!
Bernd

Btw, the Scorer node does exactly the same job when only counting co-occurrences is required…
Best, Thomas