Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Add values to file in 2 new columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Add values to file in 2 new columns
# 1  
Old 02-12-2020
Add values to file in 2 new columns
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

Code:
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

Code:
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

Code:
 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[i] != pv[i+1] || line[i] != 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  
Old 02-12-2020
Code:
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[i] == pp[i+1] && ll[i] == ll[i+1]) {
printf line[i];
} else {
printf line[i]
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[i], pp[i]]==1) {
len[count]=0
ele=0;
}
printf ("%8.2f %8.2f", len[count],ele)
first = i+1
}
print "";
}
}' file

Last edited by rdrtx1; 02-18-2020 at 08:22 PM..
This User Gave Thanks to rdrtx1 For This Post:
jiam912
# 3  
Old 02-12-2020
Are you able to live with "0.00 0.00" in a pair's first line? Try
Code:
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

This User Gave Thanks to RudiC For This Post:
jiam912
# 4  
Old 02-13-2020
Thank you Gents, both codes works perfectly .. Many tks
# 5  
Old 02-13-2020
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
Code:
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
Code:
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
Code:
0.00    0.00

Thanks in advance
# 6  
Old 02-20-2020
Try
Code:
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  
Old 02-21-2020
Hi RudiC.
Thanks a lot for the answer..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Awk: compare values in two columns of the same file

I'm trying to learn awk, but I've hit a roadblock with this problem. I have a hierarchy stored in a file with 3 columns: id name parentID 4 D 2 2 B 1 3 C 1 1 A 5 I need to check if there are any values in column 3 that are not represented anywhere in column 1. I've tried this: awk '{arr;}... (7 Replies)
Discussion started by: kaktus
7 Replies

2. Shell Programming and Scripting

Add values in 2 columns and subtract from third

Hi All, I have a file with thousands of lines in the following format, where Field1=First 8 characters Field2-9-16 characters Field3=17-26 characters I need to add Field 1 and Field2 and subtract the result from Field 3. Field3=Field3 - (Field1 + Field2) 0012.00 0010.00 0001576.53... (4 Replies)
Discussion started by: nua7
4 Replies

3. Shell Programming and Scripting

Add the values in second and third columns with group by on first column.

Hi All, I have a pipe seperated file. I need to add the values in second and third columns with group by on first column. MYFILE_28012012_1115|47|173.90 MYFILE_28012012_1115|4|0.00 MYFILE_28012012_1115|6|22.20 MYFILE_28012012_1116|47|173.90 MYFILE_28012012_1116|4|0.00... (3 Replies)
Discussion started by: angshuman
3 Replies

4. Shell Programming and Scripting

for uniq entries add values in corresponding columns

Hi, I have a file as listed below.. What I want to get is for each unique value in column 1 the corresponding values in the rest of the columns should be summed up.. AAK1 0 1 0 11 AAK1 0 0 1 1 AAK1 0 0 1 2... (2 Replies)
Discussion started by: Diya123
2 Replies

5. UNIX for Dummies Questions & Answers

Removing columns from a text file that do not have any values in second and third columns

I have a text file that has three columns. But at the end of the text file, there are trailing lines that have missing second and third columns: 4 0.04972604 KLHL28 4 0.0497332 CSTB 4 0.04979822 AIF1 4 0.04983331 DECR2 4 0.04990344 KATNB1 4 4 4 4 How can I remove the trailing... (3 Replies)
Discussion started by: evelibertine
3 Replies

6. UNIX for Dummies Questions & Answers

Subtracting values from 2 columns in a file

Hello, I have a file with 5 columns that looks like this: A1BG chr19 + 58863335 58866549 A1BG chr19 - 58858171 58864865 A2LD1 chr13 - 101182417 101186056 A2LD1 chr13 - 101182417 101241046 A2M chr12 - 9220303 9268558 A2ML1 ... (5 Replies)
Discussion started by: wolf_blue
5 Replies

7. Shell Programming and Scripting

Copy values from columns matching in those in second file.

Hi All, I have two sets of files. Set 1: 100 text files with extension .txt with names like 1.txt, 2.txt, 3.txt until 100.txt Set 2: One big file with extension .dat The text files have some records in columns like this: 0.7316431 82628 0.7248189 82577 0.7248182 81369 0.7222999... (1 Reply)
Discussion started by: shoaibjameel123
1 Replies

8. Shell Programming and Scripting

Math operations with file columns values.

Hello everybody, I have some large files containing statistical data. The data is stored in the following generic format: >cat my_file 1, 2, 3 1, 2, 3 1, 2, 3 > The values of columns no.2 and 3 are expressed in bytes. I would like to transform them in Megabytes, by dividing them with... (3 Replies)
Discussion started by: fabian23
3 Replies

9. UNIX for Dummies Questions & Answers

combine the values from the first two columns within a file

Hello everybody, I have a text file containing 10,000 rows and 5000 columns. The values are separated by a tab. Ex. file_ex.ped 1 mike 0 0 2 1 A A G G C T A G 1 jack 0 0 2 2 T A G T C A A C 1 Mary 0 0 1 2 A T G C A T G C ... I would like a out put file 1 mike 0 0 2 1 AA GG CT AG 1... (7 Replies)
Discussion started by: Unilearn
7 Replies

10. Shell Programming and Scripting

how to flip values of two columns and add an extra column

Hi guys, Couldn't find the solution of this problem. Please Help! I have a file- Input_File TC200232 92 30 TC215306 2 74 TC210135 42 14 I want an output file in which if column2>column3, the values are swapped and an additional column with value Rev_Com is... (4 Replies)
Discussion started by: smriti_shridhar
4 Replies
Login or Register to Ask a Question