awk to transpose one column into multiple unknown columns with empty values

user_9592859 0
Viewed 45

Hi I want to be able to transpose one column to an unknown number of columns as shown below.


--Before
c1 c2 c3 typ val
----------
H2|f|1|AQ|2
H2|f|1|MP|1
H2|f|3|MX|1
H1|c|3|MP|1
H1|c|3|MX|1

--after desired
c1 c2 c3 AQ MP MX 
---------
H2|f|1|2|1
H2|f|3|||1
H1|c|3||1|1

Here is my code. It does work but it creates a line for every combination of col1 col2 and col3 even if there are no associated values for a specific combination of those 3 columns as shown below. I could simply remove the empties at the end but I'm more concerned about the inefficiency of this method.

--output from my code with empty entries
c1 c2 c3 AQ MP MX 
---------
H2|f|1|2|1
H2|c|1||
H2|c|3||
H1|c|1||
H1|f|1||
H1|f|3||
H2|f|3|||1
H1|c|3||1|1


awk -F\| '
NR>0 {
    if(!($4 in ps)) { p[++types] = $4 }; ps[$4]++
    if(!($1 in es)) { e[++num1] = $1 }; es[$1]++
    if(!($2 in cs)) { c[++num2] = $2 }; cs[$2]++
    if(!($3 in ss)) { s[++num3] = $3 }; ss[$3]++
    map[$1,$2,$3,$4] = $5
}
END {
    printf "%s;" ,"c1";
    printf "%s;" ,"c2";
    printf "%s;" ,"c3";
    for(prc=1; prc<=types; prc++) {
        printf "%s%s", sep, p[prc];
        sep = ";"
    }
    print "";
    sep = ";"
    for(coun1=1; coun1<=num1; coun1++) {
    for(coun2=1; coun2<=num2; coun2++) {
    for(coun3=1; coun3<=num3; coun3++) {
        printf "%s%s", sep, e[coun1]
        printf "%s%s", sep, c[coun2]
        printf "%s%s", sep, s[coun3]
        for(val=0; val<=types; val++) {
            printf "%s%s", sep, map[e[coun1],c[coun2],s[coun3], p[val]];
        }
        print ""
    }
    }
    }
}' $workfile
transpose awk
1 Answers

something like this will work

$ awk -F'|' 'NR<3 {ps[NR]=$0} 
             NR>2 {k=$1 FS $2 FS $3; h=$4; ks[k]; hs[h]; a[k,h]=$5}
             END  {split(ps[1],p," "); 
                   printf "%s %s %s", p[1],p[2],p[3]; 
                   for(h in hs) printf " %s",h; print "\n"ps[2]; 
                   for(k in ks) 
                      {printf "%s",k; 
                       for(h in hs) printf "%s%s",FS,a[k,h]; print ""}}' file

c1 c2 c3 MP AQ MX
----------
H2|f|1|1|2|
H2|f|3|||1
H1|c|3|1||1

note that the column order not guaranteed with this approach, if important there is some additional bookkeeping is required. Also, your header field separator doesn't match the data portion, seems like an issue to be handled.

user_14358690
Related