Transpose Columns in a single comma separated row

问题:

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 can I do this using awk, sed, etc?

回答1:

One way using 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 implementation of awk doesn't 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 any problems for the script, what output are you getting? I suspect it's probably a line ending issue. Run dos2unix file to fix the line ending.

$ 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

回答2:

Code for GNU sed:

---

I made a question for this and got a very good & 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