Help with reshaping table from long form to wide form (dcast in R)

[ssheriff]

Hello,

My coworker used dcast in R to do what I’m trying to do in KNIME, if that helps.

Trying to manipulate the following example input table:

patient	gene
A		NRG1
A		RASA1
B		ROS1
B		ROS1

into the following, wide-form table:

patient	NRG1	RASA1	ROS1
A		1		1		0
B		0		0		2

So far, what I’ve done is add a “constant value column” node (with a value of 1 in each row) and a “pivoting” node. This is the result:

patient	Unique concatenate with count(gene)
A		NRG1(1), RASA1(1)
B		ROS1(2)

This isn’t quite what I want; the rows are grouped by patient and counted by the constant value column, but instead of a column for every gene, I have a single column concatenation. New to KNIME, so any advice will be helpful. Not sure if there’s a duplicate question, since this isn’t a straightforward transpose. Thank you.

[ArjenEX]

Hi @ssheriff

Welcome to the KNIME community!

I think you’re very close. If I add the same constant value and then pivot with the aggregation being the sum of the added counter, I’m getting the result you’re after.

(with patient being the group, gene as pivot)

If you actually want the genes to be 0 instead of null this is easily fixed with a Missing Values node that can be used to, in this case, default all integer column to 0.

[ssheriff]

Thank you so much for this reply. Along with the solution to my initial question, I realized from your screenshots that (1) I was using the second output port instead of the first (b) I can use the Node Monitor to view tables instead of exporting to Excel every single time!! Many thanks.