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 Answers 3

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
$
3
  • 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. Commented Nov 30, 2016 at 12:06
  • @OlivierDulac, feel free to edit that in to the answer. :) Comments are ephemeral.
    – Wildcard
    Commented Nov 30, 2016 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 ^^ Commented Nov 30, 2016 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
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .