lookup two files and print matched content

Question

I've two files f1.txt

From,Key,Count
abc.org,hello,5
abc.com,hello world,2
def.com,hi there,1
efg.com,hello world,1

f2.txt

h1,hello world
h2,hi there
h3,hello

I want the output file like below

From,Key,Count
abc.org,h3,5
abc.com,h1,2
def.com,h2,1
efg.com,h1,1

where the second column of f1.txt is matched against second column of f2.txt and the value is replaced with second column of f2.txt in the output file, (unique part)

I tried using awk like below:

awk -F',' FNR==NR{a[$2]=$1;next} ($2 in a) {print $1,a[$1],$2}' f2 f1

But it is giving errors and not working as expected

what shall I modify in the awk command?

Update: @Inian That question is merging the two files based on a common field, I've asked for replacing a field based on a mapping from the second file.

@Inian That question is merging the two files based on a common field, I've asked for replacing a field based on a mapping from the second file. — user2340345, Jun 01 '18 at 07:24
@user2340345: We have these questions all over the site. Do make some efforts to browse through the site before posting new ones — Inian, Jun 01 '18 at 07:32

RavinderSingh13 · Accepted Answer · 2018-06-01T06:47:49.533

1

Following awk may help here.

awk 'BEGIN{FS=OFS=","}FNR==NR{a[$NF]=$1;next} ($2 in a){$2=a[$2]} 1' f2.txt f1.txt

Explanation:

awk '
BEGIN{FS=OFS=","}  ##Starting BEGIN section here and setting field separator and output field separator as comma here.
FNR==NR{           ##Checking condition FNR==NR which will be TRUE when first Input_file named f2.txt is being read.
  a[$NF]=$1;       ##Creating an array named a whose index is $NF and value is $1.
  next}            ##Putting next keyword to skip all further statements as of now.
($2 in a){         ##Checking condition if $2 of Input_file2 named f1.txt is coming in aray a then do following.
  $2=a[$2]}        ##Setting current line $2 as value of array a whose index is $2.
1                  ##Printing current line by 1 here.
' f2.txt f1.txt    ##Mentioning Input_file(s) names here.

Output will be as follows.

From,Key,Count
abc.org,h3,5
abc.com,h1,2
def.com,h2,1
efg.com,h1,1

edited Jun 01 '18 at 06:47

answered Jun 01 '18 at 06:33

RavinderSingh13

130,504
14
57
93

1

++ for obvious way to output the header (which didn't occur to me before breakfast :). – James Brown Jun 01 '18 at 06:38
1

Thanks @RavinderSingh13, it works perfectly. Can you please explain what is NF here? – user2340345 Jun 01 '18 at 06:38
@user2340345, check explanation now and let me know if that helps you `$NF` is last field of any line check `man awk` for more details too. – RavinderSingh13 Jun 01 '18 at 06:48
1

Thanks, its well explained :) – user2340345 Jun 01 '18 at 07:00
1

@RavinderSingh13: do close such questions as duplicates next time. Avoid answering these blatant duplicates, they've been asked time and time before, no use in harvesting reputation from answering these – Inian Jun 01 '18 at 07:14

lookup two files and print matched content

1 Answers1