Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

complex transposing of columns with pure sed

I tried a couple of hours to find a pure sed solution for this question. Obviously, unfortunately I had not succeed. A really tricky question.


Examples (from the awk question):

  • input:
aaa 111    
aaa 222
aaa 333
bbb 444
bbb 555
ccc 666
  • output:
aaa 111,222,333
bbb 444,555
ccc 666

  • input
APM00065101435 189
APM00065101435 190
APM00065101435 191
APM00065101435 390
190104555 00C7
190104555 00D1
190104555 00E1
190104555 0454
190104555 0462
APM00065101435 391
APM00065101435 392
  • output
APM00065101435 189,190,191,390
190104555 00C7,00D1,00E1,0454,0462
APM00065101435 391,392

What have I tried? Some of my non working examples:

sed -nr '1{h;b};H;x;/(\S+).*\n\1.*\'/M{x;b};s/.*\'//m;s/\n\S*\s*/,/g;s/,$//;p' file
sed -nr '1{h;b};H;x;h;s/(\S+).*\n(\S+).*\'/\1\n\2/m;/(\S+)\n\1\'/M{$!b;g;bk};g;s/\n.*\'//m;:k;s/^\S+\s//2mg;s/\n/,/g;p;x;s/.*\n//;h;$l' file2
sed -nr 'H;g;s/(\S+)\s.*/\1/gm;/(\S+)\n\1\'/M{$!b;g;bk};g;1d;s/\n.*\'//m;:k;s/\n\S+\s/,/2g;s/\n//;p;g;s/\n.*(\n.*)$/\1/;h' file2

Thanks for reading this.

like image 506
captcha Avatar asked Jul 01 '13 22:07

captcha


People also ask

Which transformation can be used to transpose rows to columns?

Using a T-SQL Pivot function is one of the simplest method for transposing rows into columns.

How do I transpose rows to columns dynamically in MySQL?

If you want to transpose only select row values as columns, you can add WHERE clause in your 1st select GROUP_CONCAT statement. If you want to filter rows in your final pivot table, you can add the WHERE clause in your SET statement.

How do I transpose columns to rows in Linux?

Highlight the column you want to handle, right-click on it and select Copy: Copy context menu. And then right-click on the first cell of the target row and select Paste Transpose/Paste Link Transpose to paste the copied column as a row without/with links from the source column.


1 Answers

This might work for you (GNU sed):

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

or if you prefer:

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

This reads 2 lines into the pattern space, compares the beginning of each line and if they are the same replaces the beginning of the second line that matches the first with a comma and repeats. If the lines do not match it prints out the first line.

like image 130
potong Avatar answered Sep 28 '22 07:09

potong