2

I'm trying to do the following for a while but without success.

The data I received has comma separated values in each separate columns. The first value in column 6 before the comma is always related to the first value in column 7 before the comma. I want to extract data and put them into a table in the right order as such:

Input Data:

1 2 3 4 5 A1,A2 B1,B2
1 7 3 3 5 C1,C2,C3 D1,D2,D3
1 2 R 4 b E1,E2,E3,E4 G1,G2,G3,G4

Output Data:

1 2 3 4 5 A1 B1
1 2 3 4 5 A2 B2
1 7 3 3 5 C1 D1 
1 7 3 3 5 C2 D2
1 7 3 3 5 C3 D3
1 2 R 4 b E1 G1
1 2 R 4 b E2 G2
1 2 R 4 b E3 G3
1 2 R 4 b E4 G4

I understand I need to split them by \t before placing them into an array of sort, but I'm utterly new to this and having received such huge data.

1

With awk:

awk '{split($6,a,","); split($7,b,","); for(i in a){print $1,$2,$3,$4,$5,a[i],b[i]}}' file
  • awk reads the input space or tab delimited, default: [\t ]+.
  • split($6,a,",") split the 6th field $6 separated by comma , and store the output in an array called a.
  • split($7,b,",") split the 7th field $7 separated by comma , and store the output in an array called b.
  • for(i in a) now loop trough the a array...
    • print ...,a[i],b[i] ... and print the values $1 to $5 and the two array values a[i] and b[i] by their indexes i.

The output:

1 2 3 4 5 A1 B1
1 2 3 4 5 A2 B2
1 7 3 3 5 C1 D1
1 7 3 3 5 C2 D2
1 7 3 3 5 C3 D3
1 2 R 4 b E1 G1
1 2 R 4 b E2 G2
1 2 R 4 b E3 G3
1 2 R 4 b E4 G4
  • awesome thanks! this worked like a charm! – royston Oct 14 '15 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.