Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Extract columns by matching ids in two files

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Extract columns by matching ids in two files
# 1  
Old 04-08-2012
Extract columns by matching ids in two files
Hello,

I want to extract columns from file2 to file3 by matching ids between file1 and file2. The extracted columns should be in same order as file1 ids.

for example:

file1.txt
Code:
1823
607
R2A9
802
771



file2.txt
Code:
1823    1     2   4
22        11   4   29
607      12   3   3
R2A9   34   4   9
D33      2    1   0
802      30   8   1
771     3     0   9
3RE      6    3   1



output file3.txt should be printed in this way

Code:
1823    1     2   4
607      12   3   3
R2A9   34   4   9
802      30   8   1
7713     0   9

Please suggest me something.

Thanks,



Moderator's Comments:
Mod Comment Welcome to the UNIX and Linux Forums. Please use code tags. Video tutorial on how to use them
Last edited by Scrutinizer; 04-08-2012 at 07:23 AM..
# 2  
Old 04-08-2012
Hi ryan9011,

One way using perl:
Code:
$ cat file1.txt                                                                                                                                                                                                                              
1823                                                                                                                                                                                                                                         
607                                                                                                                                                                                                                                          
R2A9                                                                                                                                                                                                                                         
802                                                                                                                                                                                                                                          
771                                                                                                                                                                                                                                          
$ cat file2.txt                                                                                                                                                                                                                              
1823 1 2 4                                                                                                                                                                                                                                   
22 11 4 29                                                                                                                                                                                                                                   
607 12 3 3                                                                                                                                                                                                                                   
R2A9 34 4 9                                                                                                                                                                                                                                  
D33 2 1 0                                                                                                                                                                                                                                    
802 30 8 1                                                                                                                                                                                                                                   
771 3 0 9                                                                                                                                                                                                                                    
3RE 6 3 1                                                                                                                                                                                                                                    
$ cat script.pl                                                                                                                                                                                                                              
use warnings;                                                                                                                                                                                                                                
use strict;                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                             
die qq[Usage: perl $0 <file1> <file2>\n] unless @ARGV == 2;                                                                                                                                                                                  

@ARGV[0,1] = @ARGV[1,0];

my $file = 1;
my %id;

while ( <> ) {
        chomp;
        if ( $file == 1 ) {
                my ($id, undef) = split /\s+/, $_, 2;
                $id{ $id } = $_;
                next;
        }

        ## Second file.
        if ( exists $id{ $_ } ) {
                printf qq[%s\n], $id{ $_ };
        }
}
continue {
        ++$file if eof;
}
$ perl script.pl file[12].txt
1823 1 2 4
607 12 3 3
R2A9 34 4 9
802 30 8 1
771 3 0 9

Last edited by birei; 04-08-2012 at 07:31 AM..
# 3  
Old 04-08-2012
@birei, if you change file{1,2}.txt to file[12].txt it will work in any shell instead of just bash/ksh93..
This User Gave Thanks to Scrutinizer For This Post:
birei
# 4  
Old 04-08-2012
Thanks.

Edited my previous post to change that.
This User Gave Thanks to birei For This Post:
ryan9011
# 5  
Old 04-08-2012
Try searching the forum for "compare files field" for example.. This question has been answered before...
This User Gave Thanks to Scrutinizer For This Post:
ryan9011
# 6  
Old 04-08-2012
Code:
grep -f file1 file2

# 7  
Old 04-08-2012
I tired it but i wanted the it wasn't displaying results according to file1 id order. anyways thanks! Smilie

---------- Post updated at 08:38 AM ---------- Previous update was at 08:37 AM ----------

Thanks Birei! You are a great programmer 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

How to extract the partial matching strings among two files?

I have a two file as shown below, file:1 >Contig_152_415 (REVERSE SENSE) >Contig_152_420 (REVERSE SENSE) >Contig_152_472 (REVERSE SENSE) >Contig_152_484 (REVERSE SENSE) File:2 >Contig_152:49081-49929 ATCGAGCAGCGCCGCGTGCGGTGCACCCTTGTGCAGATCGGGAGTAACCACGCGCACGGC... (2 Replies)
Discussion started by: dineshkumarsrk
2 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. UNIX for Dummies Questions & Answers

Merging two files based on matching columns

Hi, I am facing issues while accomplishing below task. We have two files Test1.txt and Test2.txt. We have to match 1st column of Test1.txt file with 2nd column of Test2.txt and then merge 2nd file with the 1st file. In the output we should select column 1 and 2 from the 1st file and column 1... (5 Replies)
Discussion started by: Prathmesh
5 Replies

4. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 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

Merge two files matching columns

Hi! I need to merge two files when col1 (x:x:x) matching and adds second column from file1.txt. # cat 1.txt aaa;a12 bbb;b13 ccc;c33 ddd;d55 eee;e11 # cat 2.txt bbb;b55;34444;d55 aaa;a15;35666;a44 I try with this awk and I get succesfully first column from 1.txt: # awk -F";"... (2 Replies)
Discussion started by: fhluque
2 Replies

7. 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

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. UNIX for Dummies Questions & Answers

matching IDs from two files and editting

I have two files. One has: ID# 0 a b c d e f g h i j k....................~2 milion columns ID# 0 l m n o p q r s t u v....................~2 milion columns . . . ~6000 lines Other has: ID# 1 or ID# 2 . . ~6000 lines (2 Replies)
Discussion started by: polly_falconer
2 Replies
Login or Register to Ask a Question