transpose selected columns

Can I transform input like the below ?
Note: Insert zeros if there is no value to transform.

Input

key    name    score
key1    abc    10
key2    abc    20
key1    xxx    100
key2    xxx    20
key1    zzz    0
key2    zzz    29
key3    zzz    129
key1    yyy    39

output

    abc    xxx    zzz     yyy
key1    10    100    0    39
key2    20    20    29    0
key3    0      0     129    0
awk 'NR>1{ k[$1]++; n[$2]++; s[$1$2]=$3 } 
END{ 
  for(i in n){printf "\t"i;a[++j]=i} printf"\n"
  for(i in k) {
    printf i"\t"
    for(m=1;m<=j;m++) {
      printf (s[i a[m]]?s[i a[m]]:0)"\t"
    } printf "\n" 
  } printf "\n" 
}' infile

--ahamed

1 Like