print columns(file-1) vs column(file-2) help needed multiple input file

[xptional]

Hi ,
I have two files.
file-1 named "countNeigbor.txt" have 4 columns, --- column 1 has its corresponding value in column 4
file-2 names "countSR.txt", have 3 columns, ----- column 1 values == column 1 values of file-1 but it repeates .
file-2 : column 1 has its correspondance reference with file-2 column 2

-----------------------
file-1 -- total values are upto 101 (column-1), its just a sample.

0 44 316 7.18182
1 46 372 8.08696
2 47 570 12.1277
3 47 626 13.3191
4 50 1335 26.7
5 41 633 15.439
6 50 1094 21.88
7 45 602 13.3778
8 48 1066 22.2083
9 42 581 13.8333
.
.
.
101 45 303 6.73333

-----------------
file-2, its is just a sample there are 5856 records correponds to column 2. column 2 has unique value, no repeatition.

98 2621 2
44 2622 14
2 2623 19
56 2624 9
95 2625 9
68 2627 8
.
.
.
81 3943 26
56 2163 14
100 2164 37
100 3945 31
13 2165 14
89 3946 35
68 2166 8
37 3947 26
35 3948 20
43 2168 1
87 3949 26
0 2620 11

.........
I need the output as like for every record for file-2 by extracting corresponding values from file-1.
98 2621 2 itsValue
2 2623 19 12.1277
.
.
.
2 1020 20 12.1277
.
.
..........
in file-2 as 1st column values repeats e.g. 2 repeats but its correponding value in file-1 remains the same. So i want to put as I display it above.
Thanks for understanding.

I have tried hard, searching on google, on this forum as well, how getline, FILENAME etc works in awk, using arrays, but fail to come to result.
Thanks in advance for your help.

 

[LKBrwnDBA]

Sort the files on the common column and use the join command.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[PHV]

And, as you asked in the awk forum:
awk 'NR==FNR{v[$1]=$4;next}{print $0,v[$1]}' countNeigbor.txt countSR.txt

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LKBrwnDBA]

PS: You need to post an example with "consistent" data where the expected and complete result corresponds fully with the sample data supplied.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

BTW: This is what I get with your data:

==> cat m3
sort -n -k 3.0 file2 -o file2.srt
join -1 3 -2 1 -o 1.1 1.2 1.3 2.4 file2.srt file1

==> ./m3
43 2168 1 8.08696
98 2621 2 12.1277
68 2166 8 22.2083
68 2627 8 22.2083
56 2624 9 13.8333
95 2625 9 13.8333

==>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Mmmmm, a little more "verbose", but I think this awk works better:

==> cat m0
awk 'BEGIN{
while ((getline line < "file1") > 0){
  split(line,f," ");i=f[1];f1[i]=f[4];print i,f[2],f[4]}
close("file1")}
{k=""; for(i in f1){if(i==$3)k=f1[i];} print $0" "k;}' file2

==> ./m0
98 2621 2 12.1277
44 2622 14
2 2623 19
56 2624 9 13.8333
95 2625 9 13.8333
68 2627 8 22.2083
81 3943 26 26.2666
56 2163 14
100 2164 37
100 3945 31
13 2165 14
89 3946 35
68 2166 8 22.2083
37 3947 26 26.2666
35 3948 20
43 2168 1 8.08696
87 3949 26 26.2666
0 2620 11

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Removed debug code...:

awk 'BEGIN{
while ((getline line < "file1") > 0){
  split(line,f," ");i=f[1];f1[i]=f[4]}
close("file1")}
{k=""; for(i in f1){if(i==$3)k=f1[i];} print $0" "k;}' file2

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[xptional]

Thank you so much for your support and quick responses.
It helps me,Thanks a lot LKBrwnDBA.
But I did not get the result file as I wanted. The values that are mapped are not the values that correponds to 1st column.


I explain it again.
1) File countNeighbor.txt (file-1) and it is sorted based on 1st Column ( 0 - 101). Each element of 1st column has its value on the 4th Column.
2) I need to map the 4th column of file-1 value to the file countSR.txt (file-2) in the 4th column (new) But as like below;

file-1 contains
0 has value 7.18182
.
.
.
101 has value 6.73333

These values 0 and 101 should be put in the file-2 4th column, corresponding to their occurrances in the 1st column of file-2.
Note: In file-2 1st Column, 0 and 101 repeats many times, The values should be put before each occurance in the resulted file.

 

[xptional]

Thanks you so much again for everyone.
I got the result as I wanted. Just needed to change the column in the code provided.

{k=""; for(i in f1){if(i==$3)k=f1[i];} print $0" "k;}' file2

It works.
Thanks you so much.
Please keep helping with a smile!

 

[PHV]

Did you try my suggestion posted 28 Mar 14 9:31 ?

 

[xptional]

MR. PHV (MIS,
I am so much glad to try your code. Its only one line code. and it works
Its what I wanted.
Sorry, did not tried it before.

I thank you a lot to help me. Have a good day !

 

[xptional]

I wonder how the below lines compare the 1st column and put the correspond value ?

awk 'NR==FNR{v[$1]=$4;next}{print $0,v[$1]}' countNeigbor.txt countSR.txt