0

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.

  • Please edit your question and show us your desired output. What would the final result look like using the files ion your example? – terdon Apr 13 '16 at 10:19
0

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?

  • testfile1: 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 – jaffa1201 Apr 13 '16 at 12:59
  • I give you a fishing rod, please get fish yourself. – Sild Apr 13 '16 at 13:45
  • @Slid. Sorry didn't mean to post that last comment. Your script works, thank you very much. – jaffa1201 Apr 13 '16 at 14:36
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.