Create a loop which repeats based on the # of columns in 1st row

[keusch]

Ok you wizards, how do i create a loop that repeats the awk part of this script.

I have a process that needs to be repeated between 80 and 100 times for each file, depending on the number of names in row 1.
Here's a sample of the input. Row one contains the date and names (comma delimited) (this example has only 2 names instead of 80 or 100. Rows >1 contain the date and data needed):
DATE,"VPR ""A"" #02","VPR ""A"" #03"
01-Jan-00,0,139.39
02-Jan-00,0,125.06
03-Jan-00,0,120.64
04-Jan-00,0,123.56
05-Jan-00,0,16.23
06-Jan-00,0,133.91
07-Jan-00,0,122.43
08-Jan-00,0,130.66
Here's the script so far:
I've written the sed part out to a file - it seemed faster.
The awk part of the script needs to pick up the name in row one, then print out that name, then the date in $1 and the data in the same column as the name.

I need a loop to repeat and append to a file, the NAME, DATE, GAS, and the newly created DayGas FOR EACH COLUMN.
------ script so far.-----
echo "NAME" "\t" "DATE" "\t" "GAS" "\t" "DayGas" > A.txt

sed '
s/",/,/g
s/,"/,/g
s/""/"/g' Agas.csv > agas.tmp
awk -F, '
$1 == "DATE" { NAME = $2 }
{ if ($1 ~ /DATE/ )
next
else
if ( $2 > 0 )
Day = 1
else
Day = 0
print NAME "\t" $1 "\t" $2"\t" Day }
' agas.tmp >> A.txt
------
If I were to create the next awk statement, it would be:
awk -F, '
$1 == "DATE" { NAME = $3 }
{ if ($1 ~ /DATE/ )
next
else
if ( $3 > 0 )
Day = 1
else
Day = 0
print NAME "\t" $1 "\t" $3"\t" Day }
' agas.tmp >> A.txt

I've tried... but I'd rather figure this out this than paste the awk part 80 to 100 times and edit. Although, I probably could have done that by now.
Thanks

 

[CaKiwi]

This may do what you want for the awk part of the script.

{
  if (NR==1) {
    num = NF
    for (j=2;j<=num;j++) name[j] = $j
  }
  else {
    for (j=2;j<=num;j++) {
      Day = 0
      if ($j>0) Day = 1
      print NAME &quot;\t&quot; $1 &quot;\t&quot; $j&quot;\t&quot; Day
    }
  }
}

You could also
1. print the heading out in a BEGIN section in the awk script instead of using the echo.
2. Pipe the sed output directly into the awk script instead of using the temporary file.
3. Use gsub (or substr) in the awk script to replace the unwanted quotes instead of using sed.

Hope this helps. CaKiwi

 

[keusch]

Ok. I should have been clearer. The output I wanted was the name from the 1st row,the date, and the corresponding data from the name column
NAME DATE GAS DayGas
VPR "A" #02 01-Jan-00 0 0
VPR "A" #02 02-Jan-00 0 0
VPR "A" #02 03-Jan-00 0 0
VPR "A" #02 04-Jan-00 0 0
VPR "A" #02 05-Jan-00 0 0
VPR "A" #02 06-Jan-00 0 0
VPR "A" #02 07-Jan-00 0 0
VPR "A" #02 08-Jan-00 0 0
VPR "A" #03 01-Jan-00 139.39 1
VPR "A" #03 02-Jan-00 125.06 1
VPR "A" #03 03-Jan-00 120.64 1
VPR "A" #03 04-Jan-00 123.56 1
VPR "A" #03 05-Jan-00 16.23 1
VPR "A" #03 06-Jan-00 133.91 1
VPR "A" #03 07-Jan-00 122.43 1
VPR "A" #03 08-Jan-00 130.66 1

This is what I get from running your suggestion.

NAME DATE GAS DayGas
01-Jan-00 0 0
01-Jan-00 139.39 1
02-Jan-00 0 0
02-Jan-00 125.06 1
03-Jan-00 0 0
03-Jan-00 120.64 1
04-Jan-00 0 0
04-Jan-00 123.56 1
05-Jan-00 0 0
05-Jan-00 16.23 1
06-Jan-00 0 0
06-Jan-00 133.91 1
07-Jan-00 0 0
07-Jan-00 122.43 1
08-Jan-00 0 0
08-Jan-00 130.66 1
Can you try again? The NF can only relate to the 1st line, the record with Date as the 1st column.
Thanks

 

[CaKiwi]

Sorry, the print statement should have been:

print name[j] "\t" $1 "\t" $j"\t" Day

Could you run the output through sort to get the order you want? Otherwise, the easiest way to do it would probably be to use getline to loop through the file num times. CaKiwi

 

[keusch]

Thanks - that worked. I was more concerned with the sorting issue and missed the print statement. I will do a post sort on the data as you suggested. I was trying to script with a getline and loop but that's when I started having problems. I hadn't thought of a post sort routine. The tmp file for the sed script was created for that purpose rather than pipe it through as you suggested.
Thanks again.

 

[CaKiwi]

Here's a solution using getline. I had to read your temporary file directly because I couldn't figure out how to close the standard awk input file.

{
  num = NF
  for (j=2;j<=num;j++) name[j] = $j
  for (j=2;j<=num;j++) {
    getline < &quot;agas.tmp&quot;
    while ((getline < &quot;agas.tmp&quot;) > 0) {
      Day = 0
      if ($j>0) Day = 1
      print name[j] &quot;\t&quot; $1 &quot;\t&quot; $j&quot;\t&quot; Day
    }
    close(&quot;agas.tmp&quot;)
  }
  exit
}

CaKiwi