Transpose column to line but with a difference

[madasafish]

I have another conundrum...I hope someone can help me with?

My input data - I have only shown 5 column's (Packs), there are in fact 56 columns and 350 records

CH#,M Pack,M+ Pack,L Pack,XL Pack,etc...
100,1,1,1,1
101,1,1,1,1
102,1,1,1,1
103,1,1,1,1
104,1,1,1,1
105,1,1,1,1
106,1,1,1,1
107,1,1,1,1
108,0,0,0,0
109,0,1,1,1
110,0,0,0,0
111,0,1,1,1
112,0,0,1,1
113,0,0,0,0
114,1,1,1,1
115,1,1,1,1
116,0,0,0,1
etc.....

This is the output I am seeking

M Pack,100,101,102,103,104,105,106,107,114,115,
M+ Pack,100,101,102,103,104,105,106,107,109,111,114,115,
L Pack,100,101,102,103,104,105,106,107,109,111,112,114,115,
XL Pack,100,101,102,103,104,105,106,107,109,111,112,114,115,116,

As always, thanks in advance.
Madasafish

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[madasafish]

I have tried many awk "transpose" suggestions from the net and also tried to amend them to give me the output I want but to no avail.
I manually generated the output shown above with the first piece of code shown below and changed the $5 to suit column and cut and paste into code section.
When it comes to manipulating arrays, I always struggle. Incidently the version of gawk I am using 4.0.1

#!/bin/bash




FILE="test"

gawk -F"," '{ if($5 == 1) { printf $1"," }

}' $FILE


exit 0

awk 'BEGIN {FS="," ; OFS=","}
{
  for(c = 1; c <= NF; c++) {
    a[c, NR] = $c
  }
  if(max_nf < NF) {
    max_nf = NF
  }
}
END {
  for(r = 1; r <= NR; r++) {
    for(c = 1; c <= max_nf; c++) {
      printf("%s,",a[r, c])
    }
    print ""
  }
}
' $FILE


exit 0

awk -F',' 'BEGIN {i=0} {for (i=1;i<=NF;i++) print $i}' $FILE


exit 0

gawk 'BEGIN {FS=","}

{for(i=1;i<=NF;i++) ; a[NR,i]=$i}

END{
  for(i=1;i<=NF;i++){
    for(j=1;j<=NR;j++){
      line=line sep a[j,i]
      sep=FS
    }
    print line
    line=sep=""
  }
}' $FILE



exit 0

gawk 'BEGIN {FS=","}

{for(i=1;i<=NF;i++)
a[NR,i]=$i
} END {
  for(i=1;i<=NF;i++){
    for(j=1;j<=NR;j++){
      line=line sep a[j,i]
      sep=FS
    }
    print line
    line=sep=""
  }
}' $FILE

exit 0



gawk -F"," 'BEGIN {
    max_x =0;
    max_y =0;
}

{
    max_y++;
    for( i=1; i<=NF; i++ )
    {
        if (i>max_x) max_x=i;
        A[i,max_y] = $i;
    }
}

END {
    for ( x=1; x<=max_x; x++ )
    {
        for ( y=1; y<=max_y; y++ )
        {
            if ( (x,y) in A ) printf "%s",A[x,y];
            if ( y!=max_y ) printf " ";
        }
        printf "\n";
    }
}' $FILE

exit 0


gawk -F"," 'BEGIN {OFS=","}   {
                                #pkgl=$8
                                pkgl[$8]++
        } END {
                for (i in pkgl)
                        print pkgl[i],i

        }' $FILE



exit 0

gawk version 4.0.4

 

[PHV]

What about this ?

gawk -F, 'NR==1{for(i=2;i<=NF;++i)a[i]=$i}
{for(i=2;i<=NF;++i)if($i==1)a[i]=a[i]","$1}
END{for(i=2;i in a;++i)print a[i]}
' $FILE

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[madasafish]

Yep...That works!....needs a bit of tidy up on the last output.
I would never have worked it out,
Thank-you.

gawk version 4.0.1

 

[PHV]

needs a bit of tidy up on the last output
Can you, please, share your final code ?

 

[madasafish]

Certainly,

Your code works perfectly.

All that was missing was an "exit 0". I cut and paste your code above my futile attempts shown above.

so after your code, it also ran...

FILE="test"

gawk -F"," '{ if($5 == 1) { printf $1"," }

}' $FILE
exit 0

In my haste to reply and thank-you, I did not double check it at the time.
Once again thank-you.
Madasafish

gawk version 4.0.1