How to take the values from two columns in a txt file and match them to values in another

Question

I have two text files formatted as follows:

e.g. File1:

   900        480   10060.62       2740 -266864.19 3843493.50    2740.00       2740     176.07
   900        479   10046.50       2741 -266874.34 3843486.00    2741.00       2741     176.07
   900        478   10032.38       2742 -266884.47 3843478.50    2742.00       2742     176.07
   900        477   10018.26       2743 -266894.62 3843471.00    2743.00       2743     176.07

e.g. File2:

   -2.68785700E+05  3.84401270E+06         313.33   2740.00    313.33 2740 1.401298E-044 2 LY1-0900
   -2.68783800E+05  3.84400040E+06         313.35   2741.00    313.35 2741 1.401298E-044 2 LY1-0900
   -2.68781900E+05  3.84398810E+06         313.36   2742.00    313.36 2742 1.401298E-044 2 LY1-0900
   -2.68780000E+05  3.84397580E+06         313.38   2743.00    313.38 2743 1.401298E-044 2 LY1-0900

I need to replace the first two columns in file 2 with the 5th and 6th columns in File1, based on matches to the values in the 1st and 7th columns in file 1 and 9th and 4th columns in file 2.

That sounds very confusing to let me give an example of what the script would do...

Take the first line from file 2, find the line in file 1 where the first column value (900) matches the last 4 digits of the 9th column (0900) AND the 7th (2740.00) matches the 4th (2740.00) then replace column 1 and 2 in file2 with the 5th and 6th column from file 1.

i.e.

   -2.68785700E+05  3.84401270E+06         313.33   2740.00    313.33 2740 1.401298E-044 2 LY1-0900

becomes

   -266864.19           3843493.50         313.33   2740.00    313.33 2740 1.401298E-044 2 LY1-0900

THEN move onto the next line etc. finally outputting a new file.

The desired output would look like this:

   -266864.19     3843493.50         313.33   2740.00    313.33 2740 1.401298E-044 2 LY1-0900
   -266874.34     3843486.00         313.35   2741.00    313.35 2741 1.401298E-044 2 LY1-0900
   -266884.47     3843478.50         313.36   2742.00    313.36 2742 1.401298E-044 2 LY1-0900
   -266894.62     3843471.00         313.38   2743.00    313.38 2743 1.401298E-044 2 LY1-0900

I am basically trying to lookup new spatial coordinates of a 2 dimensional surface.

The values may not always be in order and the files may be relatively large (6 million lines) so it would be useful if the script was efficient.

Answer 1

You can take the following script as example:

#!/bin/sh
outfile="outfile"
echo "testfile1:"
cat testfile1
echo "testfile2:"
cat testfile2
cat /dev/null > $outfile
cat testfile1 | while read line; do
    matchfirst="`echo $line | awk '{print $3}'`"
    matchsecond="`echo $line | awk '{print $4}'`"
    finded="false"
    while read defline; do
        tplfirst="`echo $defline | awk '{print $3}'`"
        tplsecond="`echo $defline | awk '{print $4}'`"
        if [ "$tplfirst" = "$matchfirst" ] && [ "$tplsecond" = "$matchsecond" ]; then
            echo -n "`echo $defline | awk '{print $1}'` `echo $defline | awk '{print $2}'` `echo $line | awk '{print $3}'` `echo $line | awk '{print $4}'`"  >> $outfile
            echo >> $outfile
            finded="true"
        fi
    done < testfile2 
    if [ "$finded" = "false" ]; then
        echo $line >> $outfile
    fi
done

echo "outfile:"
cat outfile

Usage example:

➜ sild@$work 15:29:55 [test]$ ./replacer.sh 
testfile1:
1 2 3 4
5 6 7 8
9 10 11 12
testfile2:
11 21 3 4
51 61 7 8
9 10 111 121
outfile:
11 21 3 4
51 61 7 8
9 10 11 12

Is it your goal?

Answer 2

Try this:

awk 'FNR==NR{
    a[$1"@"$7]=$5" "$6;
    next
}
{
    i=length($NF);
    n=substr( $NF, i-2, i);
    if( n"@"$4 in a) {
        split(a[n"@"$4],b," ")
    };
    $1=b[1];
    $2=b[2]
}
1' file1 file2