CODEWITHSUNDEEP
linuxawk
Tiago Bruno
Tiago Bruno

Reputation: 413

How can I turn comma separated fields into different rows?

I have a file like that:

A 10,30,50,70 20,40,60,80 +

how can I make this become:

A 10 20 +
A 30 40 + 
A 50 60 + 
A 70 80 +

I tried using this awk command, but it did not work.

awk '{ split($2,a,","); for (i in a) print $1, a[i]; }'

Upvotes: 0

Views: 66

Answers (4)

Sundeep
Sundeep

Reputation: 23667

$ cat ip.txt 
A 10,30,50,70,90 20,40,60,80,00 +
B ab,56,is,jw,23 09,we,io,21,aw +

Generic solution, independent of number of comma separated values in 2nd and 3rd columns... 

$ perl -lane '@a = split/,/,$F[1]; @b = split/,/,$F[2]; foreach (0..$#a){ print "$F[0] $a[$_] $b[$_] $F[3]" }' ip.txt 
A 10 20 +
A 30 40 +
A 50 60 +
A 70 80 +
A 90 00 +
B ab 09 +
B 56 we +
B is io +
B jw 21 +
B 23 aw +


And a solution similar to Ed Morton's answer

$ perl -F'/\s|,/' -lane 'foreach (1..5){ print "$F[0] $F[$_] $F[$_+5] $F[-1]" }' ip.txt 
A 10 20 +
A 30 40 +
A 50 60 +
A 70 80 +
A 90 00 +
B ab 09 +
B 56 we +
B is io +
B jw 21 +
B 23 aw +

where foreach (1..5) and $F[$_+5] indicates 5 comma separated values in 2nd and 3rd columns

Upvotes: 0

Ed Morton
Ed Morton

Reputation: 203522

This might be what you're looking for:

$ awk -F'[ ,]' '{for (i=2;i<6;i++) print $1, $i, $(i+4), $NF}' file
A 10 20 +
A 30 40 +
A 50 60 +
A 70 80 +

or if you don't want to hard-code the number of fields:

$ awk -F'[ ,]' '{n=NF/2; for (i=2;i<=n;i++) print $1, $i, $(i+n-1), $NF}' file
A 10 20 +
A 30 40 +
A 50 60 +
A 70 80 +

Upvotes: 1

Ljm Dullaart
Ljm Dullaart

Reputation: 4969

awk '{  split ($2, a,","); split ($3,b,",");  for (i in a) print $1, a[i], b[i], $NF; }'

should give you the result.

Upvotes: 2

rakib_
rakib_

Reputation: 142625

Create another split-ed array b, where you kept the 3rd column elements: 

echo "A 10,30,50,70 20,40,60,80 +" | awk '{ split($2,a,","); split($3,b,","); for (i in a) print $1, a[i],  b[i], $4; }'

Upvotes: 2

Related Questions