Shell Programming and Scripting

Hello Everybody:

I have a csv file that i would want to be converted to a table (csv also) filtered, transposed and matched with the header (quite confusing, sorry). So the output can used on a spreadsheet and plot on a grap. I'm using CSH on unix.
To further explain, here is an example input and expected output:

+++++++
Input file:
++++++++

Oct|24|12:08:18|2008,Bin,1,3,4,6,7,8,15,
Oct|24|12:08:18|2008,Total,461,1,4,3,31,1,213,
Oct|24|12:08:18|2008,Percentage,64.5,0.1,0.6,0.4,4.3,0.1,29.8,

Oct|24|12:23:24|2008,Bin,1,3,4,6,7,8,15,16
Oct|24|12:23:24|2008,Total,610,1,10,4,41,1,237,1
Oct|24|12:23:24|2008,Percentage,67.4,0.1,1.1,0.4,4.5,0.1,26.2,0.1

Oct|24|12:38:18|2008,Bin,1,3,4,6,7,8,13,15,16
Oct|24|12:38:18|2008,Total,655,1,12,5,48,1,1,260,1
Oct|24|12:38:18|2008,Percentage,66.6,0.1,1.2,0.5,4.9,0.1,0.1,26.4,0.1

#This input file is a log file which logs every 15 minutes of 3 rows of data, each row for Bin, Total and Percentage, with the date on 1st column. In the example above, there is only 3 log incidents but in actual it can be more than close to 300 lines in 1 day (3rows every 15mins x 4 x 24).

#On the Bin row, some Bin numbers are not present on all log incidents as can be seen on the 1st log - Bin13 is not present but present at 2nd and 3rd incidents, so is Bin16 which not present at 1st but present at 2nd and 3rd incident.

++++++
Output:
++++++
CSV format below. To better visualize, see attached pix in spreadsheet.
# Header is Bin and numbers 1 to 16. (1st columns is a blank " ")
# Values in the Input file in Percentage row of every log incident is transposed if the Bin number of this log incident is matching the Bin header number.
#Some Bin numbers do not appear in all log instances, take note on Bin13 and Bin16 values in the output.

,Bin,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,
Oct|24|12:08:18|2008,Percentage,64.5,,0.1,0.6,,0.4,4.3,0.1,,,,,,,29.8,,
Oct|24|12:23:24|2008,Percentage,67.4,,0.1,1.1,,0.4,4.5,0.1,,,,,,,26.2,0.1,
Oct|24|12:38:18|2008,Percentage,66.6,,0.1,1.2,,0.5,4.9,0.1,,,,,0.1,,26.4,0.1,

Thank you in advance for the help and time!
Hope to have a solution around there.

Something like this:
(use nawk or /usr/xpg4/bin/awk on Solaris)

Code:

awk 'BEGIN { 
  FS = ","; ORS = OFS = ""; max = 16 
  print ",bin,"; while (++c <= max) 
    print c, (c == max ? FS RS : FS) 
  }
/Bin/ { for (i=3; i<=NF; i++) num_bin[$i] }
/Percentage/ { printf "%s,%s,", $1, $2; c = 3
  for (i=1; i<=max; i++) 
    print i in num_bin ? $(c++) : "", (i == max ? FS RS : FS)
  split("", num_bin)
}' infile

The above code produces:

Code:

% cat file
Oct|24|12:08:18|2008,Bin,1,3,4,6,7,8,15,
Oct|24|12:08:18|2008,Total,461,1,4,3,31,1,213,
Oct|24|12:08:18|2008,Percentage,64.5,0.1,0.6,0.4,4.3,0.1,29.8,

Oct|24|12:23:24|2008,Bin,1,3,4,6,7,8,15,16
Oct|24|12:23:24|2008,Total,610,1,10,4,41,1,237,1
Oct|24|12:23:24|2008,Percentage,67.4,0.1,1.1,0.4,4.5,0.1,26.2,0.1

Oct|24|12:38:18|2008,Bin,1,3,4,6,7,8,13,15,16
Oct|24|12:38:18|2008,Total,655,1,12,5,48,1,1,260,1
Oct|24|12:38:18|2008,Percentage,66.6,0.1,1.2,0.5,4.9,0.1,0.1,26.4,0.1

% awk 'BEGIN {    
  FS = ","; ORS = OFS = ""; max = 16
  print ",bin,"; while (++c <= max)
    print c, (c == max ? FS RS : FS)
  }
/Bin/ { for (i=3; i<=NF; i++) num_bin[$i] }
/Percentage/ { printf "%s,%s,", $1, $2; c = 3
  for (i=1; i<=max; i++)
    print i in num_bin ? $(c++) : "", (i == max ? FS RS : FS)
  split("", num_bin)
}' file
,bin,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,
Oct|24|12:08:18|2008,Percentage,64.5,,0.1,0.6,,0.4,4.3,0.1,,,,,,,29.8,,
Oct|24|12:23:24|2008,Percentage,67.4,,0.1,1.1,,0.4,4.5,0.1,,,,,,,26.2,0.1,
Oct|24|12:38:18|2008,Percentage,66.6,,0.1,1.2,,0.5,4.9,0.1,,,,,0.1,,26.4,0.1,

thanks radoulov! your advice helped me lot, just needed to tweak somethings on the codes. thanks again!