matching columns

Hello experts,

I have this problem, I need to match values based on two files, this is what I have:

file1

1.1
1.2
1.3
5.5
1.4
1.5
1.6

file2

1 a
2 B
3 C
4 D
5 z
6 F
7 G
8 H
9 I
10 J
11 K
12 L
13 M
14 N
15 O
16 P
17 Q
18 R
19 S
20 T

and the desired output is:

a
a
a
z
a
a
a

I'm using this code:

awk 'NR==FNR{a[int($1+.5)];next} $1 in a {print $2}' file1 file2

but I just get this:

a
z

this is :wall:

how could I modify the code above to get what I want? any help is very grateful,

Thanks in advance

while read line
do
   num=`echo ${line} | awk -F "." ' { print $1 } '`
   egrep "^${num} " file2  | cut -d" " -f2 >> output
done < file1

You need to flip file1 and file2 around. try:

awk 'NR==FNR{A[$1]=$2;next} {i=int($1+.5)} i in A {print A}' file2 file1

Many thanks frappa and Scrutinizer, your codes work very nice in my example , the thing is that when I use them in bigger files, for some reason the output is not complete, please let me attach those files, they are fileA (two columns file) and fileB (one column file). The output should have the same length as the fileB (15287 records) but only has around 15150 records, why should be that?

It is because for some entries in fileA there is no match in fileB and then those lines do not get printed. If you would use:

awk 'NR==FNR{A[$1]=$2;next} {i=int($1+.5);print A}' file2 file1

Those non-matches will produce empty lines and that should then produce 15287 lines...

wow man, you're really good, thank you so much, I thought my fileB.txt was complete but it isn't. Sorry for bothering you again but, even though I can complete it manually, I was wondering if with your last awk code is possible to "fill" those non-matches with the closest value, I mean, no interpolation between the values in the 2nd column where the value is missing, but matching the closest one in the first column, something like this:

fileB.txt (where 689 record, 1st and 2nd column, is missing)

688   6.42
689   missing
690   6.42 

fileA.txt

689.52
688.867
688.935
688.32

desired output: (where 688.867 is rounded to 689, so no 2nd-column value there, then it may be taken from 688 or 690 in fileB.txt)

689.52  6.42
688.867 6.42
688.935 6.42
688.32  6.42

many thanks again

Hi, you could for example give every intermediate value the value of the previous known value:

awk 'NR==FNR{if(!i)i=$1; while(i<=$1)A[i++]=$2;next} {print A[int($1+.5)]}' fileB.txt fileA.txt

many thanks Scrutinizer, it works wonderful, you're great dude