Add values to file in 2 new columns

UNIX for Beginners Q & A
1

Columns 4 and 5 are X and Y coordinates, column 6 is the elevation

I would like to add 2 new columns at the end of the file with values

the distance between first(X)(Y) and last location (X)(Y), based in 2 rows
the difference in elevation = ($6-prev6)

How to calculate the requested values and add to last columns, and when the records appears only 1 time(columns 1 and 2 single row ), write values 0.00 0.00

Input file

48047 41513  2  373512.79     2542085.84     154.53      12.64      90.63
48047 41513  3  373513.29     2542085.00     154.52      12.34      91.02
48047 41525  2  373663.51     2542087.65     153.93      12.36      90.70
48047 41525  3  373662.27     2542088.44     153.99      12.76      86.99
48049 39785  2  351912.93     2542112.25     160.91      12.16      90.61
48049 39797  2  352063.17     2542112.38     160.19      12.10      90.66
48049 39809  2  352213.12     2542113.01     159.96      12.24      90.75

Desired Output

48047 41513  2  373512.79     2542085.84     154.53      12.64      90.63
48047 41513  3  373513.29     2542085.00     154.52      12.34      91.02    0.98     0.01 
48047 41525  2  373663.51     2542087.65     153.93      12.36      90.70
48047 41525  3  373662.27     2542088.44     153.99      12.76      86.99    1.47     0.06
48049 39785  2  351912.93     2542112.25     160.91      12.16      90.61    0.00     0.00
48049 39797  2  352063.17     2542112.38     160.19      12.10      90.66    0.00     0.00
48049 39809  2  352213.12     2542113.01     159.96      12.24      90.75    0.00     0.00

My code works only when the records in columns 1 and 2 are duplicate. If they are not duplicate my code delete the single lines.

Code I use to get the values

 awk '{
       pp[NR]  = $2
       ll[NR]  = $1
        x[NR]  = $4
        y[NR]  = $5
          z[NR]  = $6
      } END {
               first = 1
               count = 0
               for ( i=1 ; i <= NR ; i++ ) {
                   if (pv != pv[i+1] || line != line[i+1]) {
                      last = i
                      count++
                      dx = x[first]-x[last]
                      dy = y[first]-y[last]
                      el = z[first]-z[last]
                      ele = sqrt(el^2)
                      len[count] = sqrt(dx^2+dy^2)
                      if ( len[count] <= 1000 && len[count] > 0 ) {
                      printf ("%8.2f %8.2f \n",
                      len[count],ele)
                      }\
                      first = i+1
                  }
               }
              }' file

Thanks in advance.

2 
awk '{
line[NR]=$0;
pp[NR] = $2
ll[NR] = $1
x[NR] = $4
y[NR] = $5
z[NR] = $6
rc[$1,$2]++
} END {
first = 1
count = 0
for ( i=1 ; i <= NR ; i++ ) {
if (pp == pp[i+1] && ll == ll[i+1]) {
printf line;
} else {
printf line
last = i
count++
dx = x[first]-x[last]
dy = y[first]-y[last]
el = z[first]-z[last]
ele = sqrt(el^2)
len[count] = sqrt(dx^2+dy^2)
if (rc[ll, pp]==1) {
len[count]=0
ele=0;
}
printf ("%8.2f %8.2f", len[count],ele)
first = i+1
}
print "";
}
}' file
1 Like
3

Are you able to live with "0.00 0.00" in a pair's first line? Try

awk '
                {printf "%s" OFS, $0
                }
!X[$1,$2]       {print "0.00", "0.00"
                }
X[$1,$2]++      {print sqrt (($4-LX)^2 + ($5-LY)^2), $6 - LZ
                }
                {LX=$4
                 LY=$5
                 LZ=$6
                }
' OFS="\t" OFMT="%.2f" file
48047 41513  2  373512.79     2542085.84     154.53      12.64      90.63    0.00    0.00
48047 41513  3  373513.29     2542085.00     154.52      12.34      91.02    0.98    -0.01
48047 41525  2  373663.51     2542087.65     153.93      12.36      90.70    0.00    0.00
48047 41525  3  373662.27     2542088.44     153.99      12.76      86.99    1.47    0.06
48049 39785  2  351912.93     2542112.25     160.91      12.16      90.61    0.00    0.00
48049 39797  2  352063.17     2542112.38     160.19      12.10      90.66    0.00    0.00
48049 39809  2  352213.12     2542113.01     159.96      12.24      90.75    0.00    0.00
1 Like
4

Thank you Gents, both codes works perfectly .. Many tks

5

Dear RudiC,

Tks a lot for the code

It is possible to change your code to get in both same values in in a pair's first line, intestead of 0.00 0.00

Now

48047 41513  2  373512.79     2542085.84     154.53      12.64      90.63    0.00    0.00
48047 41513  3  373513.29     2542085.00     154.52      12.34      91.02    0.98    -0.01

After

48047 41513  2  373512.79     2542085.84     154.53      12.64      90.63    0.98    -0.01
48047 41513  3  373513.29     2542085.00     154.52      12.34      91.02    0.98    -0.01

For single lines, all the time will be

0.00    0.00

Thanks in advance

6

Try

awk '
L0              {printf "%s" OFS, L0
                }
!X[$1,$2] && L0 {if (SAME)      print DLT
                  else          print "0.00", "0.00"
                 SAME = 0
                }
X[$1,$2]++      {print DLT = sprintf ("%.2f%s%.2f", sqrt (($4-LX)^2 + ($5-LY)^2), OFS, $6 - LZ)
                 SAME = 1
                }
                {L0 = $0
                 LX = $4
                 LY = $5
                 LZ = $6
                }
END             {print L0, SAME?DLT:"0.00" OFS "0.00"
                }
' OFS="\t" OFMT="%.2f" file
48047 41513  2  373512.79     2542085.84     154.53      12.64      90.63    0.98    -0.01
48047 41513  3  373513.29     2542085.00     154.52      12.34      91.02    0.98    -0.01
48047 41525  2  373663.51     2542087.65     153.93      12.36      90.70    1.47    0.06
48047 41525  3  373662.27     2542088.44     153.99      12.76      86.99    1.47    0.06
48049 39785  2  351912.93     2542112.25     160.91      12.16      90.61    0.00    0.00
48049 39797  2  352063.17     2542112.38     160.19      12.10      90.66    0.00    0.00
48049 39809  2  352213.12     2542113.01     159.96      12.24      90.75    0.00    0.00
7

Hi RudiC.
Thanks a lot for the answer..