3

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.

Improve this question

2 Answers 2

Reset to default
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
Improve this answer
0
0

Using Raku (formerly known as Perl_6)

~$ raku -ne 'my @split1 = .split(/ \s+ /); 
             my @split2 = @split1.[5..*].map: .split(","); 
             for [Z] @split2 { put (@split1[0..4], $_).join("\t") };'  file.txt

Raku is a programming language in the Perl-family. There are CSV modules which will handle complex CSV files (multi-line headers, embedded newlines, etc.). Above is an answer without loading external modules.

The code directs reading of the file linewise, via the -ne (non-autoprinting, linewise) flags. The first split breaks on whitespace to create array @split1. Since 5 "data" columns are given at the start of each row, index [0..4] are ignored in the second statement, which maps into and splits the [5..*] latter "covariant" columns of @split1 (this time splitting on , comma), to create @split2 array.

The [Z] prefix Zip-reduction operator takes off each element of the @split2 array, effectively transposing elements (so that A1 and B1 are adjacent, etc.). Finally the row is output, "data" columns and "covariant" columns adjacent, joined as necessary.

Sample Input:

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

Sample 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

https://docs.raku.org/routine/split
https://docs.raku.org/language/operators#Reduction_metaoperators
https://raku.org

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.