text processing - Extracting using cshell and awk

Question

I have a long file containing the following lines repeatedly randomly throughout the file

$CROSS_BEAM_PROPERTY_281
POINT,201656,,-41.0213,-1.00928
POINT,201657,,-37.8216,-4.15746
POINT,201658,,-5.40451,-51.3106
POINT,201659,,-4.24517,-52.0837
POINT,201660,,-1.74418,-53.1687
POINT,201661,,2.03505,-51.2474
SET3,9,POINT,201670,201683,THRU,201701,201682
PBMSECT,1501,150,CP
        OUTP=8,
        BRP=9,
        T=1.3,
        T(1)=[1.3,PT=(201656, 201657)],
        T(2)=[1.3,PT=(201657, 201658)],
$CROSS_BEAM_PROPERTY_109
POINT,201660,,-1.74418,-53.1687
POINT,201661,,2.03505,-51.2474
POINT,201662,,4.249589,-48.9936
POINT,201663,,7.70361,-48.5562
POINT,201664,,9.169905,-48.7962
POINT,201665,,30.79493,-53.7184
POINT,201666,,33.52191,-53.1064
POINT,201667,,27.54975,-45.6262
PBMSECT,1500,150,CP
        OUTP=6,
        BRP=7,
        T=1.3,
        T(1)=[1.3,PT=(201610, 201611)],
        T(2)=[1.3,PT=(201611, 201612)],

For each CROSS_BEAM_PROPERTY, i would like to extract the POINT's 4th and 5th column as an variable array so i can do further processing with it in one go? How do i extract them using cshell awk or sed?

Edit: Just an overview, in the text file, there are a lot of CROSS_BEAM_PROPERTY_XXand the POINT defines the location in x and y. The example above is just a snippet of the file with two cross beam property. I would like to save for each CROSS_BEAM_PROPERTY_XX the 4th column into an array. With the array i could do some further calculation to extract the max value, min value or sum value of the array.

I managed to extract all the 4th column and sum it up using the line below.

cat $file | awk -F ',' '$1 == "POINT" {sum += $4} END {print sum}'

But this sums all the 4th column from all POINT entries in the text file. It does not separate for each CROSS_BEAM_PROPERTY_XX. Besides sum, i would also like to identify the max and min value in the 4th column for each CROSS_BEAM_PROPERTY - hence the need of an array.

Desired output:

-88.20171
113.280564

Is it also possible to get desired output for max of the 4th column as below?

2.03505
33.52191

Answer 1

 cat $file | awk -F "," '/^\$CROSS/,/^PBMSECT/{if($0~/CROSS/){v=$0};if($0~/^POINT/){p[v]+=$4}}END{for(i in p){print p[i]}}'

This outputs the first requested output:

-88.20171
113.280564

And for the second output you reqested the following will do:

cat $file | awk -F "," '/^\$CROSS/,/^PBMSECT/{if($0~/CROSS/){v=$0};if($0~/^POINT/){if($4>p[v]){p[v]=$4}}}END{for(i in p){print p[i]}}'

You could include the cross_beam_property in the output to make the output data nicer

$ cat $file | awk -F "," '/^\$CROSS/,/^PBMSECT/{if($0~/CROSS/){v=$0};if($0~/^POINT/){p[v]+=$4}}END{for(i in p){printf("%s\t%f\n", i, p[i])}}'
$CROSS_BEAM_PROPERTY_281        -88.201710
$CROSS_BEAM_PROPERTY_109        113.280564

Answer 2

I'm not sure exactly what you're looking for based on your explanation, but if you want to get the 4th and 5th column of each POINT row you can easily do that with sed:

sed -n -e 's/^POINT,[^,]*,[^,]*,//p' data.csv

This produces the following output from your example data:

-41.0213,-1.00928
-37.8216,-4.15746
-5.40451,-51.3106
-4.24517,-52.0837
-1.74418,-53.1687
2.03505,-51.2474
-1.74418,-53.1687
2.03505,-51.2474
4.249589,-48.9936
7.70361,-48.5562
9.169905,-48.7962
30.79493,-53.7184
33.52191,-53.1064
27.54975,-45.6262

If you want to put this data into an array, you can use process substitution like so:

set array=`sed -n -e 's/^POINT,[^,]*,[^,]*,//p' data.csv`

You can then access this data via the array variable, e.g.:

foreach point (${array})
echo ${point}
end

Of course you could do the same thing using awk:

awk 'BEGIN{FS=",";OFS=","}/^POINT/{print $4,$5}' data.csv

Or even just using grep and cut:

cat data.csv | grep '^POINT' | cut -d, -f4,5