Transposition of columns in a separate line, separated by a comma, conditionally

Question

I have an input file that looks like this:

aaa 111 aaa 222 aaa 333 bbb 444 bbb 555 

I want to create a transposed output file that looks like this:

 aaa 111,222,333 bbb 444,555 

How to do this using awk , sed , etc.

Answer 1

One way: awk :

 $ awk '{a[$1]=a[$1]?a[$1]","$2:$2}END{for(k in a)print k,a[k]}' file aaa 111,222,333 bbb 444,555 

And if your awk implementation does not support the ternary operator, then:

 $ awk 'a[$1]{a[$1]=a[$1]","$2;next}{a[$1]=$2}END{for(k in a)print k,a[k]}' file aaa 111,222,333 bbb 444,555 

---

Your new file does not cause problems for the script, what result do you get? I suspect this is probably a line ending problem. Run dos2unix file to fix line termination.

 $ cat file APM00065101435 189 APM00065101435 190 APM00065101435 191 APM00065101435 390 190104555 00C7 190104555 00D1 190104555 00E1 190104555 0454 190104555 0462 $ awk '{a[$1]=a[$1]?a[$1]","$2:$2}END{for(k in a)print k,a[k]}' file APM00065101435 189,190,191,390 190104555 00C7,00D1,00E1,0454,0462 

Answer 2

Code for GNU sed :

---

I made a question for this and got a very nice and useful answer from potong :

 sed -r ':a;$!N;s/^(([^ ]+ ).*)\n\2/\1,/;ta;P;D' file 

 sed -r ':a;$!N;s/^((\S+\s).*)\n\2/\1,/;ta;P;D' file