This is a test version of Biostars. For the public version, visit https://www.biostars.org.
convert one column values into row values based on other row values

I have two columns, one column is the name of wells, the other column is the ct values. ex 

well    ct
A    10
A    9
A    NA
A    19

Now I want to put the ct values into one row based on well name. Each column is one ct value, NA is a value too. ex

Well ct
A    10   9    NA   19

I tried to used aggregae in r, but it omits NA and put all the values in a vector into one columns,

r
• 3,578 views • link
updated by venu venu • written by newbiebio newbiebio

This is a programming question not a Bioinformatics one!

• 0 views • link
written by Deepak Tanwar Deepak Tanwar

It's a thin line, considering that this issue is clearly a bio-application which OP tries to solve informatically...

• 0 views • link
written by WouterDeCoster WouterDeCoster

1 answer

An alternate (to R) quick solution 

cat file.txt | datamash -H -s -g 1 collapse 2 > result.txt

result.txt

GroupBy(well)   collapse(ct)
A   10,9,NA,19
B   11,12

P.S: datamash & add | tr ',' '\t' at the end of the command if it is nesessary to convert commas into tabs

• 0 views • link
written by venu venu

Log in to answer this question.

More posts like this