2

I have an input file with single value columns as below:

Input file:

B00000|1|def|b12  
B00001|1|xyz|
B00000|1||b11  
B00001|1|def|ab1  
B00001|2|ghi|bc2  
B00002|1|abc|ac1  
B00002|1|xyz|bc2  

I need to delimit the data in third and fourth columns with respect to the first and second so as to get the output as:

Output:

B00000|1|def|b11,b12  
B00001|1|def,xyz|ab1  
B00001|2|ghi|bc2  
B00002|1|abc,xyz|ac1,bc2  
  • Edited the question considering one more possibility of input data where 3rd or 4th column values could be empty. Thanks @dave_thompson_085 for bringing it to notice – lunix Sep 19 '16 at 6:28
4
#!/usr/bin/gawk -f
BEGIN          { OFS=FS="|"}

a[$1][$2][3]   { a[$1][$2][3] = a[$1][$2][3] "," $3;
                 a[$1][$2][4] = a[$1][$2][4] "," $4;
                 next }

               { a[$1][$2][3] = $3;
                 a[$1][$2][4] = $4   }

END            { for (b in a)
                    for(c in a[b]) print b,c,a[b][c][3],a[b][c][4] }

Please see @dave_thompson_085' comment bellow about a correspondent version for awk (using one-dimensional arrays)

  • 1
    array-of-array is gawk-only, but you could do two parallel (1D) arrays each keyed by the concatentation $1"|"$2 (or prettier $1 FS $2) in any awk. Also, your existence test would malfunction if the first-encountered $3 value(s) for a $1,$2 pair is (each) empty or a representation of zero, although the example data does not suggest that happens; with the 1D method $1"|"$2 in ary3 works for any field values. Finally, if the output should be sorted, in recent gawk (v4) only you can use PROC_INFO["SORTED_IN"] to control the for. – dave_thompson_085 Sep 16 '16 at 9:33
  • @dave_thompson_085, thank you!! I dont know why but my gawk 4.1.3 by default is sorting the for in. – JJoao Sep 16 '16 at 10:01
  • @JJoao can you please update the answer with 1D method. Its not possible for me to run gawk. – lunix Sep 20 '16 at 5:56
1
$ cat ip.txt 
B00000|1|def|b12  
B00001|1|xyz|a11  
B00000|1|abc|b11  
B00001|1|def|ab1  
B00001|2|ghi|bc2  
B00002|1|abc|ac1  
B00002|1|xyz|bc2  

$ perl -ne '($k,$v1,$v2)=/([^|]+\|[^|]+\|)([^|]+)\|([^ ]*)/;
$h1{$k}.=$h1{$k}?",$v1":$v1; $h2{$k}.=$h2{$k}?",$v2":$v2;
END{ foreach (keys %h1){print "$_$h1{$_}|$h2{$_}\n"} }
' ip.txt 
B00001|2|ghi|bc2
B00002|1|abc,xyz|ac1,bc2
B00000|1|def,abc|b12,b11
B00001|1|xyz,def|a11,ab1
  • The input line is split into three parts, for ex: B00000|1| , def and b12. Trailing whitespace in line is ignored
  • In two different hash variables, having same key, the values $v1 and $v2 are appended. , is added if hash value for the key is non-empty
  • After all lines are processed, output is printed

If input and keys are sorted:

$ sort ip.txt | perl -ne '($k,$v1,$v2)=/([^|]+\|[^|]+\|)([^|]+)\|([^ ]*)/;
$h1{$k}.=$h1{$k}?",$v1":$v1; $h2{$k}.=$h2{$k}?",$v2":$v2;
END{ foreach (sort keys %h1){print "$_$h1{$_}|$h2{$_}\n"} }'
B00000|1|abc,def|b11,b12
B00001|1|def,xyz|ab1,a11
B00001|2|ghi|bc2
B00002|1|abc,xyz|ac1,bc2

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.