Search code examples
perlunixawksedsolaris

to arrange the value in columns as per value in 1st column


I have a file with following data

cat text.txt
281475473926267,46,47
281474985385546,310,311
281474984889537,248,249
281475473926267,16,17
281474985385546,20,28
281474984889537,112,68

The values in 1st column are duplicate at some places i want o/p as given below

cat output.txt
281475473926267 16,17,46,47
281474985385546 20,28,310,311
281474984889537 68,112,248,249

It should print uniq values of column 1 and then space and then it should print respective values of other column in one line arranged in ascending order.

I tried below:

cat text.txt | perl -F, -lane ' $kv{$F[0]}{$F[1]}++; END { while(my($x,$y) = each(%kv)) { print "$x ",join(",",keys %$y) }}'

281474984889537 112,248
281474985385546 310,20
281475473926267 46,16

here i am not able to print all the values in front of value in 1st column

for 281474984889537 it should print 68,112,248,249, but its printing only 112,248

also i am not sure how to arrange them in ascending order.

cat text.txt | perl -F, -lane ' $kv{$F[0]}{$F[1]}++; END { while(my($x,$y) = each(%kv)) { print "$x ",join(",",keys %$y) }}'

281474984889537 112,248
281474985385546 310,20
281475473926267 46,16

here i am not able to print all the values in front of value in 1st column


Solution

  • multi-step

    $ awk -F, '{print $1,$2; print $1,$3}' file             | 
      sort -k1n -k2n                                        | 
      awk 'p!=$1{if(p) print p,a[p]; a[$1]=$2; p=$1; next} 
                {a[$1]=a[$1] "," $2} 
           END  {print p,a[p]}'                             | 
      sort -k2n
    
    281475473926267 16,17,46,47
    281474985385546 20,28,310,311
    281474984889537 68,112,248,249