Return first two columns if match found among two files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Return first two columns if match found among two files
# 1  
Old 09-24-2014
Return first two columns if match found among two files

Hi,

I have FileA with one column.
File B with 15 columns separated by comma delimiter.

I need to compare the FILEA value with all 15 columns of FILEB... if matches, need to return the 1st, 2nd column values of FILEB.

How to achieve this through shell script? Thanks in advance.
# 2  
Old 09-24-2014
Can you please provide sample input and expected output data? What have you tried so far?

So just to understand your requiements...
FileA contains one column.
FileB contains 15 columns.

What do you mean by "compare the FILEA value"?

Am I right in assuming each of the columns in FileB contains only one record? And that the one column in FileA contains 15 rows? I.E. Being a vertical representation of FileB?
# 3  
Old 09-24-2014
Hi,

FileA is having a single column with 1000 rows.
FileB is having 15 columns with 13000 rows.

Need to compare File A values with FileB, and if that comparing value matches with any of the value from all 15 columns of fileB, it should return the 1st, 2nd columns of FileB among 15 columns.

Last edited by vamsikrishna928; 09-24-2014 at 01:54 PM..
# 4  
Old 09-24-2014
Try Something like this

Code:
awk 'FNR==NR{A[$1];next}{for(i=1;i<=NF;i++){ if($i in A){ print $1,$2 ; next  } }}' FILEA FILEB

These 3 Users Gave Thanks to Akshay Hegde For This Post:
# 5  
Old 09-24-2014
Just this little detail in case our friend doesn't know how to add a Field Separator,

Quote:
Originally Posted by Akshay Hegde
Try Something like this

Code:
awk 'BEGIN{FS=","} FNR==NR{A[$1];next}{for(i=1;i<=NF;i++){ if($i in A){ print $1,$2 ; next  } }}' FILEA FILEB

This User Gave Thanks to Kibou For This Post:
# 6  
Old 09-27-2014
Thanks Akshay & Kibou. That code worked! Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Data match 2 files based on first 2 columns matching only and join if match

Hi, i have 2 files , the data i need to match is in masterfile and i need to pull out column 3 from master if column 1 and 2 match and output entire row to new file I have tried with join and awk and i keep getting blank outputs or same file is there an easier way than what i am... (4 Replies)
Discussion started by: axis88
4 Replies

2. UNIX for Dummies Questions & Answers

Match the columns between two files and output

Hi Help, I have two files namely a.txt and b.txt a.txt looks like a.txt 1 2 2 1 3 3 2 4 4 4 5 6 6 7 7 b.txt looks like, b.txt 1 2 1 1 3 2 2 4 3 3 4 4 4 5 5 (2 Replies)
Discussion started by: Indra2011
2 Replies

3. Shell Programming and Scripting

Match first two columns and average third from multiple files

I have the following format of input from multiple files File 1 24.01 -81.01 1.0 24.02 -81.02 5.0 24.03 -81.03 0.0 File 2 24.01 -81.01 2.0 24.02 -81.02 -5.0 24.03 -81.03 10.0 I need to scan through the files and when the first 2 columns match I... (18 Replies)
Discussion started by: ncwxpanther
18 Replies

4. Shell Programming and Scripting

Match the columns between 2 files

I have two files I want to match ids in the 5th column of the file 1 with the first column of the file 2 and get the description for the matched ids as shown in the output sno nm no nm2 ID 1 cc 574372 yyyi |6810|51234| 2 bb 119721 nmjk |6810|51234|51179| ... (4 Replies)
Discussion started by: raj_k
4 Replies

5. Shell Programming and Scripting

Match two columns from two files and print output

Hello, I have two files which are of the following format File 1 which has two columns Protein_ID Substitution NP_997239 T53R NP_060668 V267M NP_058515 P856A NP_001206 T55M NP_006601 D371Y ... (2 Replies)
Discussion started by: nans
2 Replies

6. Shell Programming and Scripting

Match files based on either of the two columns awk

Dear Shell experts, I have 2 files with structure: File 1: ID and count head test_GI_count1.txt 1000094 2 10039307 1 10039641 1 10047177 11 10047359 1 1008555 2 10120302 1 10120672 13 10121776 1 10121865 32 And 2nd file: head Protein_gi_GeneID_symbol.txt protein_gi GeneID... (11 Replies)
Discussion started by: smitra
11 Replies

7. Shell Programming and Scripting

Match columns several files

Hey fellas! Here come my problem. I appreciate if you have a look at it. I have several files with following structure: file_1:1 21 4 45 file_2:2 31 4 153 6 341 and so on... and I have a 'reference' file look like this: File_ref:A 1 B 2 C 3 (5 Replies)
Discussion started by: @man
5 Replies

8. Shell Programming and Scripting

awk Help -- If match found return the count

Hi All, I need to get the count of records in the file, if the passing parameter matches with the list of records in the file. Below is my example source file: Test1.dat 20120913 20120913 20120912 20120912 20120912 20120912 20120912 20120913 20120913 20120912 In my script I am... (5 Replies)
Discussion started by: bbc17484
5 Replies

9. Shell Programming and Scripting

Comparing two files and printing 2nd column if match found

Hi guys, I'm rather new at using UNIX based systems, and when it comes to scripting etc I'm even newer. I have two files which i need to compare. file1: (some random ID's) 451245 451288 136588 784522 file2: (random ID's + e-mail assigned to ID) 123888 xc@xc.com 451245 ... (21 Replies)
Discussion started by: spirm8
21 Replies

10. Shell Programming and Scripting

Match strings in two files and compare columns of both

Good Morning, I was wondering if anybody could tell me how to achieve the following, preferably with a little commenting for understanding. I have 2 files, each with multiple rows with multiple columns. I need to find each row where the value in column 1 of file 1 matches column 1... (10 Replies)
Discussion started by: GarciasMuffin
10 Replies
Login or Register to Ask a Question