Matched values in file with lookup in second file

Question

I have three files, one data file, one lookup file, another lookup file

One field of the data file must be altered by a value, which can be found in the lookup file.

The datafile looks like:

2013-04-24;1;0.1635;1.4135
2013-04-24;1;0.9135;1.4135
2013-04-24;2;0.9135;1.4135

The lookup file looks like:

1;2ab1e4c0-de4d-11e2-a934-0f0479162b1b
2;2ab21e90-de4d-11e2-9ce8-d368d9512bad
3;2ab2582e-de4d-11e2-bb5f-6b1f6c4437f8

The result must be:

2013-04-24 2ab1e4c0-de4d-11e2-a934-0f0479162b1b 0.1635 1.4135
2013-04-24 2ab1e4c0-de4d-11e2-a934-0f0479162b1b 0.9135 1.4135
2013-04-24 2ab21e90-de4d-11e2-9ce8-d368d9512bad 0.9135 1.4135

Another lookup file to be used on output above:

2ab1e4c0-de4d-11e2-a934-0f0479162b1b AAA
2ab1e4c0-de4d-11e2-a934-0f0479162b1b AAA
2ab21e90-de4d-11e2-9ce8-d368d9512bad CCC

I want my final output to be:

2013-04-24 2ab1e4c0-de4d-11e2-a934-0f0479162b1b AAA 0.1635 1.4135
2013-04-24 2ab1e4c0-de4d-11e2-a934-0f0479162b1b AAA 0.9135 1.4135
2013-04-24 2ab21e90-de4d-11e2-9ce8-d368d9512bad CCC 0.9135 1.4135

This is my awk command I found also here:

awk -F';' 'NR==FNR{a[$1]=$2;next}{$2=a[$2]}1' Datafile.csv Lookupfile1.csv

@triplee This isn't a inner join on 2 files, but an inner join with *3* files. — oliv, Nov 09 '18 at 08:05
@tripleee Well.. this could be done of course, but I think this is dirty. I believe a single awk script can be more efficient. — oliv, Nov 09 '18 at 08:11
No doubt optimizations are possible, but the OP doesn't seem to want to learn Awk. — tripleee, Nov 09 '18 at 08:13

Juan Diego Godoy Robles · Answer 1 · 2018-11-09T07:56:41.263

0

Swap file order:

awk -F';' 'NR==FNR{a[$1]=$2;next}{$2=a[$2]}1'  Lookupfile1.csv Datafile.csv

The logic goes on for the second lookup:

awk 'NR==FNR{a[$1]=$0;next}{$2=a[$2]}1'  Lookupfile2.csv <(awk -F';' 'NR==FNR{a[$1]=$2;next}{$2=a[$2]}1' Lookupfile1.csv Datafile.csv)

edited Nov 09 '18 at 07:56

answered Nov 09 '18 at 07:22

Juan Diego Godoy Robles

14,447
2
38
52

Mayank Porwal · Answer 2 · 2018-11-09T07:47:50.097

You can use a simple unix command join for this:

mayankp@mayank:~/$ join -1 2 -2 1 -t';' -o 1.1,2.2,1.3,1.4 datafile.txt lookup1.txt | sed 's/;/ /g'
2013-04-24 2ab1e4c0-de4d-11e2-a934-0f0479162b1b 0.1635 1.4135
2013-04-24 2ab1e4c0-de4d-11e2-a934-0f0479162b1b 0.9135 1.4135
2013-04-24 2ab21e90-de4d-11e2-9ce8-d368d9512bad 0.9135 1.4135

Writing the above temporary output in a file res.txt. Then, joining this file with the 2nd lookup file to fetch the final result

mayankp@mayank:~/$ join -1 2 -2 1 -t';' -o 1.1,2.2,1.3,1.4 datafile.txt lookup1.txt | sed 's/;/ /g' > res.txt

mayankp@mayank:~/$ join -1 2 -2 1 res.txt lookup2.txt -o 1.1,1.2,2.2,1.3,1.4 |uniq
2013-04-24 2ab1e4c0-de4d-11e2-a934-0f0479162b1b AAA 0.1635 1.4135
2013-04-24 2ab1e4c0-de4d-11e2-a934-0f0479162b1b AAA 0.9135 1.4135
2013-04-24 2ab21e90-de4d-11e2-9ce8-d368d9512bad CCC 0.9135 1.4135

With this, you don't have to remember complex syntax. Let me know if this helped.

OR

You can combine above commands in one line:

mayankp@mayank:~/$ join -1 2 -2 1 <(join -1 2 -2 1 -t';' -o 1.1,2.2,1.3,1.4 datafile.txt lookup1.txt | sed 's/;
/ /g') lookup2.txt -o 1.1,1.2,2.2,1.3,1.4 |uniq
2013-04-24 2ab1e4c0-de4d-11e2-a934-0f0479162b1b AAA 0.1635 1.4135
2013-04-24 2ab1e4c0-de4d-11e2-a934-0f0479162b1b AAA 0.9135 1.4135
2013-04-24 2ab21e90-de4d-11e2-9ce8-d368d9512bad CCC 0.9135 1.4135

Matched values in file with lookup in second file

2 Answers2