2

I'm new working with linux enviroment and i need to write a script to process some data.

I have a file that looks like this:

File A:

1    X   1    1.0
2    X   1    1.0
3    X   1    1.0
4    Y   2    1.0
5    Y   2    1.0
6    Z   3    1.0

and another file that looks like this:

File B:

1 0.5
2 0.3
3 0.2

I need to replace the numbers from the 4th column of File A, with the numbers that appear in the 2nd column of file B, but it has to be matching the numbers from the 3rd column of file A with the numbers of the 1st column of file B

The desired oputput is:

1    X   1    0.5
2    X   1    0.5
3    X   1    0.5
4    Y   2    0.3
5    Y   2    0.3
6    Z   3    0.2
3

If you can't sort the files, use Awk, which is (like join) designed for handling delimited data.

In this case, pass over the second file first, to construct a lookup table, and then use the lookup table to modify each line of the first file as you pass through it.

$ cat file2
1 0.5
2 0.3
3 0.2
$ cat file1
1    X   1    1.0
2    X   1    1.0
3    X   1    1.0
4    Y   2    1.0
5    Y   2    1.0
6    Z   3    1.0
$ awk 'NR==FNR {a[$1] = $2; next} {$4 = a[$3]} 1' file2 file1
1 X 1 0.5
2 X 1 0.5
3 X 1 0.5
4 Y 2 0.3
5 Y 2 0.3
6 Z 3 0.2
$
  • A little bit of explanation: NR : number of records (lines, usually) read since the beginning. FNR : same, but only for the current File. So NR==FNR is only true for the first file read (which, here, serves to "load" the replacement values into the array a[]). The next at the end of this part ensures that the rest of the treatment is skipped (so only the assignement occurs, while reading the first file). Then (2nd file, the one to be treated) : NR is greater than FNR, so the first part is skipped, and the rest of the treatment occurs. – Olivier Dulac Nov 30 '16 at 12:06
  • @OlivierDulac, feel free to edit that in to the answer. :) Comments are ephemeral. – Wildcard Nov 30 '16 at 16:22
  • I let you do it if you want.. to me editing is to correct typos, not change or add something on the author's words ^^ – Olivier Dulac Nov 30 '16 at 17:50
2

Use join:

join -1 3 -o 1.1,1.2,0,2.2 file1 file2

Output with provided files:

$ cat file1
1    X   1    1.0
2    X   1    1.0
3    X   1    1.0
4    Y   2    1.0
5    Y   2    1.0
6    Z   3    1.0
$ cat file2
1 0.5
2 0.3
3 0.2
$ join -1 3 -o 1.1,1.2,0,2.2 file1 file2
1 X 1 0.5
2 X 1 0.5
3 X 1 0.5
4 Y 2 0.3
5 Y 2 0.3
6 Z 3 0.2
$ 

Note that both input files must be sorted (for the column you wish to join them on). That's lexicographic sorting required, not numeric, so be sure to sort -n the files appropriately if there are more than ten items. (You can resort again after joining.)

1

If you can not sort the input then join won't work but you can use sed instead. The idea is to use sed once to transform fileB into a script, then feed that script back to sed to transform fileA.

So in one line, using standard POSIX shell command substitution:

sed -e "$(sed 's:^\([0-9]*\) \(.*\)$:s/\1  *[^ ]*$/\1    \2/:' file2)" file1

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.