0

My csv file looks like the following:

8 id1 xyz
1 id1 abcdef
2 id1 mv
1 id1 tzfl
6 id2 xyz
1 id2 abcdef
1 id2 mv
5 id3 xyz
2 id3 abcdef
   ...
8 id100 xyz
1 id100 abcdef
2 id100 mv
1 id100 tzfl
4 id100 glkgb

I want to sort this file according to the same ID, and then print column3 and column1 in the following format.

       xyz abcdef mv tzfl glkgb
id1    8   1      2  1
id2    6   1      1
id3    5   2
   ...
id100  8   1      2  1    4

Any suggestions are appreciated!

  • 2
    You should be able to do the cross-tabulation straightforwardly using GNU Datamash (datamash -Ws --filler= crosstab 2,3 unique 1 < file) however AFAIK it doesn't allow control of the output order – steeldriver Mar 15 at 12:19
3

awk is a good tool for this. There is a great tutorial about awk: Awk - A Tutorial and Introduction - by Bruce Barnett - Grymoire

Give a try to this:

awk '
{
  columns[$3]=1 ; ids[$2]=1 ; col1[$2 ":::" $3]=$1
  if (maxcollen< length($1)) maxcollen=length($1)
  if (maxcollen< length($2)) maxcollen=length($2) 
  if (maxcollen< length($3)) maxcollen=length($3) 
}
END {
  printf("%-" maxcollen "s ", "")
  for (key in columns) {
    printf("%-" maxcollen "s ",key)
  }
  for (id in ids) {
    printf("\n%-" maxcollen "s ", id)
    for (key in columns) {
      printf("%-" maxcollen "s ", col1[id ":::" key])
    }
  }
  printf("\n")      
}' myfile.csv

Output:

              glkgb  tzfl   mv     abcdef xyz    
id3                                2      5      
id100         4      1      2      1      8      
id1                  1      2      1      8      
id2                         1      1      6

==

Another version to display the columns and ids in order of appearance:

awk '
{
  if (colname[$3]!=1) {colname[$3]=1 ; cols[++coln]=$3 }
  if (idval[$2]!=1) {idval[$2]=1 ; ids[++idn]=$2 }
  col1[$2 ":::" $3]=$1
  if (maxcollen< length($1)) maxcollen=length($1)
  if (maxcollen< length($2)) maxcollen=length($2) 
  if (maxcollen< length($3)) maxcollen=length($3) 
}
END {
  printf("%-" maxcollen "s ", "")
  for (i=1; i<=coln ; i++ ) {
    printf("%-" maxcollen "s ",cols[i])
  }
  for (j=1; j<=idn ; j++) {
    printf("\n%-" maxcollen "s ", ids[j])
    for (i=1; i<=coln ; i++) {
      printf("%-" maxcollen "s ", col1[ids[j] ":::" cols[i]])
    }
  }
  printf("\n")      
}' myfile.csv

Output:

       xyz    abcdef mv     tzfl   glkgb  
id1    8      1      2      1             
id2    6      1      1                    
id3    5      2                           
id100  8      1      2      1      4

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.