Visit Our UNIX and Linux User Community


matching columns


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers matching columns
# 1  
Old 02-01-2012
matching columns

Hello experts,

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

file1
Code:
1.1
1.2
1.3
5.5
1.4
1.5
1.6

file2
Code:
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:

Code:
a
a
a
z
a
a
a

I'm using this code:

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

but I just get this:

Code:
a
z

this is Smilie

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

Thanks in advance
# 2  
Old 02-01-2012
Code:
while read line
do
   num=`echo ${line} | awk -F "." ' { print $1 } '`
   egrep "^${num} " file2  | cut -d" " -f2 >> output
done < file1

This User Gave Thanks to frappa For This Post:
# 3  
Old 02-01-2012
You need to flip file1 and file2 around. try:
Code:
awk 'NR==FNR{A[$1]=$2;next} {i=int($1+.5)} i in A {print A[i]}' file2 file1


Last edited by Scrutinizer; 02-01-2012 at 08:49 AM..
This User Gave Thanks to Scrutinizer For This Post:
# 4  
Old 02-01-2012
Many thanks frappa and Scrutinizer, your codes work very nice in my example Smilie, 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?
# 5  
Old 02-01-2012
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:
Code:
awk 'NR==FNR{A[$1]=$2;next} {i=int($1+.5);print A[i]}' file2 file1

Those non-matches will produce empty lines and that should then produce 15287 lines...
This User Gave Thanks to Scrutinizer For This Post:
# 6  
Old 02-02-2012
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)
Code:
688   6.42
689   missing
690   6.42

fileA.txt
Code:
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)
Code:
689.52  6.42
688.867 6.42
688.935 6.42
688.32  6.42

many thanks again Smilie
# 7  
Old 02-02-2012
Hi, you could for example give every intermediate value the value of the previous known value:
Code:
awk 'NR==FNR{if(!i)i=$1; while(i<=$1)A[i++]=$2;next} {print A[int($1+.5)]}' fileB.txt fileA.txt

This User Gave Thanks to Scrutinizer For This Post:
 

Previous Thread | Next Thread
Test Your Knowledge in Computers #130
Difficulty: Easy
The original Unix code was developed by AT&T.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk Matching Columns - Am I missing something?

I am using awk to match columns and output based on those matches. For some reason it is not printing matching columns, am I missing something? Operating system - windows with cygwin. Command that I am using: sed 's/]*,]*/,/g' $tempdir/file1 > $tempdir/file1.$$ && awk -F, 'FNR==NR{f2=$2... (7 Replies)
Discussion started by: dis0wned
7 Replies

2. Shell Programming and Scripting

Joining Two Files Matching Two Columns

Hi All, I am looking to join two files where column 1 of file A matches with column 1 of file B and column 5 of files A matches with column 2 of file B. After joining the files based on above condition, out should contain entire line of file A and column 3, 4 and 5 of file B. Here is sample... (8 Replies)
Discussion started by: angshuman
8 Replies

3. Shell Programming and Scripting

Matching first 2 columns..

Hello All, I want to make a file which will have primarily lines of file2 but when first 2 fields matches with the file1 it should have those lines of file1.. example is as below.. file1 a;b;1 c;d f;e t;r;5 file2 b;g a;b c;d v;b f;e t;r (2 Replies)
Discussion started by: ailnilanjan
2 Replies

4. Shell Programming and Scripting

Returning specific columns upon matching

Hi All, Need help in this requirement. I have fileA with one column and fileB with 26 columns. I need to match the value from fileA with fileB, if matches I have to return that value from fileB, and the next value, 5th and 6th values. NOTE- the matching value's position changes in... (7 Replies)
Discussion started by: vamsikrishna928
7 Replies

5. Shell Programming and Scripting

Join two files with matching columns

Hi, I need to join two files together with one common value in a column. I think I can use awk or join or a combination but I can't quite get it. Basically my data looks like this, with the TICKER columns matching up in each file File1 TICKER,column 1, column, 2, column, 3, column 4 ... (6 Replies)
Discussion started by: unkleruckus
6 Replies

6. Shell Programming and Scripting

Help with awk Matching columns from two files

Hello, I have two files as following: #bin chrom chromStart chromEnd name score strand observed 585 chr2 29442 29443 rs4637157 0 + C/T 585 chr2 33011 33012 rs13423995 0 + A/G 585 chr2 34502 34503 rs13386087 0 + ... (2 Replies)
Discussion started by: Homa
2 Replies

7. Shell Programming and Scripting

Clustering data by matching columns

I am stuck with by DNA clustering analysis. I thought this forum will be a great help with data manipulations. Please help me. I have a table with 91 columns. First I want to trim the table to only having rows where the column values are single characters which are A,T,G,C or 0. So any row... (4 Replies)
Discussion started by: geetu
4 Replies

8. UNIX for Dummies Questions & Answers

Matching corresponding columns in two different files

Hi to all, I have two separated files: FILE1 "V1" "V2" "V3" Mary James Nicole Robert Francisco Sophie Nancy Antony Matt Josephine Louise Rose Mark Simon Charles FILE2 "V1" "V2" "V3"... (2 Replies)
Discussion started by: eleonoral
2 Replies

9. Shell Programming and Scripting

matching columns from two files

Hey, I have two files that have exactly the same format. They are both tab-delimited and contain 12 columns. However the # of rows vary. What I want to do is match columns # 5,6 and 7 between the two files. If they do match exactly (based on numbers) then I want the whole row from file 2 to... (1 Reply)
Discussion started by: phil_heath
1 Replies

10. Shell Programming and Scripting

matching columns with overlapping value ranges

Hi, I want to match and print columns that match. So my file looks like this: h1 20 30 h1 25 27 h2 50 70 h2 90 95 h2 60 80 h2 70 75 h3 130 150 h3 177 190 h4 140 190 h4 300 305 So there are 6 columns. Column 1 and 4 are names. I am able to get the... (2 Replies)
Discussion started by: kylle345
2 Replies

Featured Tech Videos