Welcome To Support Community

Pipeline Pilot

Advanced Search
Ask Search:
CliveClive 

Help with data transformation in PP

I have a very wide table that I would like to transform to long and skinny table. Here is an example (truncated) data.
 
NameChromosomePositionGeneSample1.FreqSample1.RatioSample1.ValueSample1.ConfidenceSample2.FreqSample2.RatioSample2.ValueSample2.ConfidenceSample3.FreqSample3.RatioSample3.ValueSample3.Confidence
rs1231861808SAMD11NaNNaN2 NaNNaN2 NaNNaN311320.19
rs1241863776SAMD110.4942960.0066876712 0.0035797060.13086922 0.0030199090.2492809311320.19
rs1251864490SAMD110.99965850.13336242 0.99285270.23223152 0.48494570.1262707311320.19
rs1261867635SAMD110.99534540.014448252 0.99504510.014613232 0.53072350.05215292311320.19
rs1271873558SAMD110.4897163-0.071889742 0-0.10884182 0.5324048-0.01974613311320.19
rs1281874927SAMD110.002884310.059526992 0-0.22538352 0.000831246-0.08205927311320.19
rs1291876067SAMD110.99971910.050220332 1-0.27132942 10.04340166311320.19

I would like to transform to it to this.
 
NameChromosomePositionGeneSampleFreqRatioValueConfidence
rs1231861808SAMD11Sample1NaNNaN2 
rs1241863776SAMD11Sample10.4942960.0066876712 
rs1251864490SAMD11Sample10.99965850.13336242 
rs1261867635SAMD11Sample10.99534540.014448252 
rs1271873558SAMD11Sample10.4897163-0.071889742 
rs1281874927SAMD11Sample10.002884310.059526992 
rs1291876067SAMD11Sample10.99971910.050220332 
rs1231861808SAMD11Sample2NaNNaN2 
rs1241863776SAMD11Sample20.0035797060.13086922 
rs1251864490SAMD11Sample20.99285270.23223152 
rs1261867635SAMD11Sample20.99504510.014613232 
rs1271873558SAMD11Sample20-0.10884182 
rs1281874927SAMD11Sample20-0.22538352 
rs1291876067SAMD11Sample21-0.27132942 
rs1231861808SAMD11Sample3NaNNaN311320.19
rs1241863776SAMD11Sample30.0030199090.2492809311320.19
rs1251864490SAMD11Sample30.48494570.1262707311320.19
rs1261867635SAMD11Sample30.53072350.05215292311320.19
rs1271873558SAMD11Sample30.5324048-0.01974613311320.19
rs1281874927SAMD11Sample30.000831246-0.08205927311320.19
rs1291876067SAMD11Sample310.04340166311320.19

I tried using unpivot component but it does not seem to be able to handle patterned property names. Any help is appreciated.
Best Answer chosen by Clive
jfeinbergjfeinberg
Here's an updated version of the protocol which includes Sample.  I didn't realize that it would be useful to extract a sample number, but looking at the original table I can see that you would need this too!

All Answers

jfeinbergjfeinberg
I think you would need to write some pilotscript to transform data such as you've provided.  See the example protocol I have included in this post.
CliveClive
Thanks John! The sample protocol missed one column in the output (Sample). I tried to further modify your protocol to include the sample name (#prefix) but couldn't make it work. Can you give a hint? Much appreciated!
jfeinbergjfeinberg
Here's an updated version of the protocol which includes Sample.  I didn't realize that it would be useful to extract a sample number, but looking at the original table I can see that you would need this too!
This was selected as the best answer