I have 2 files with the format of
File A
01 20200111 28.56 22.07 40.14 49.79 22.81 49.31 33.75 31.24 39.41 36.18
02 20200118 32.41 14.89 38.82 60.54 11.54 49.10 34.34 25.53 36.96 34.30
03 20200125 21.95 18.48 28.45 42.94 22.45 42.40 36.43 34.53 43.51 33.28
...
01 20210109 29.32 24.60 34.41 46.56 29.38 44.06 34.47 33.75 41.12 36.04
02 20210116 29.38 28.39 33.07 42.63 29.46 39.41 32.45 33.60 43.81 34.93
03 20210123 27.51 19.55 32.98 45.88 26.05 46.88 37.58 31.98 44.52 35.90
File B
01 19912020 24.20 16.70 31.28 45.98 19.26 42.57 33.43 29.35 39.96 32.22
02 19912020 24.29 15.46 29.02 44.47 19.24 40.57 32.98 29.80 40.26 31.36
03 19912020 20.69 14.07 29.90 45.89 21.34 44.71 35.90 31.61 42.00 33.08
I would like to take the difference of Columns 3-12 of each id (Column 1) in File A from the same unique id in File B and add it at the end of each line in File A. File A has multiple instances of the same id. So each instance would be compared to the same id in File B.
My desired output is:
01 20200111 28.56 22.07 40.14 49.79 22.81 49.31 33.75 31.24 39.41 36.18 4.36 5.37 8.86 3.81 3.55 6.74 0.32 1.89 -0.55 3.96
02 20200118 32.41 14.89 38.82 60.54 11.54 49.10 34.34 25.53 36.96 34.30 8.12 -0.57 9.8 16.07 -7.7 8.53 1.36 -4.27 -3.3 2.94
03 20200125 21.95 18.48 28.45 42.94 22.45 42.40 36.43 34.53 43.51 33.28 1.26 4.41 -1.45 -2.95 1.11 -2.31 0.53 2.92 1.51 0.2
...
01 20210109 29.32 24.60 34.41 46.56 29.38 44.06 34.47 33.75 41.12 36.04 5.12 7.9 3.13 0.58 10.12 1.49 1.04 4.4 1.16 3.82
02 20210116 24.29 15.46 29.02 44.47 19.24 40.57 32.98 29.80 40.26 31.36 5.09 12.93 4.05 -1.84 10.22 -1.16 -0.53 3.8 3.55 3.57
03 20210123 20.69 14.07 29.90 45.89 21.34 44.71 35.90 31.61 42.00 33.08 6.82 5.48 3.08 -0.01 4.71 2.17 1.68 0.37 2.52 2.82
The above output (cols 3-22) needs to be in the following format (%8.2f)
XX 20230708 74.05 68.12 76.00 81.01 63.48 81.06 73.59 67.78 74.86 73.59 5.60 -1.14 1.22 2.50 -4.27 0.37 0.03 5.25 1.74 0.61
I attempted to use something like the below (I edited the code provided by markp-fuso(thanks!)) but the output appears to be alternating b/t File A and File B for each ID.
awk '
FNR==NR { for (i=3;i<=NF;i++) # 1st file: loop through 3rd-12th fields
b[$1][i]=$i # store field values in 2-dimensional array
next # skip to next input line
}
$1 in b { printf "%s %s%s", $1, OFS, $2 # 2nd file: if $1 is index in first dimension of array b[] then printf first 2 fields and then ...
for (i=3;i<=NF;i++) # loop through 3rd-12th fields
printf "%s%8.2f%8.2f", OFS, $i,$i-b[$1][i] # print the difference
print "" # terminate the current line of output
}
Any help is appreciated.