Two files; if cells match then copy over other columns

Renyulb28 has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I'd just like to thank all of you who are so willing to help those in need; it's really great to see such a helpful community. My current issue is dealing with two space delimited files. The first file has column 1 as the sample ID's, then columns 2 - n as the observations. The second file has column 1 as the sample ID's, column 2 as the mother ID's, column 3 as the father ID's, column 4 as the gender, and column 5 as the trait. I would like a perl script that reads the first column of both files, and for the cells that match, copy over columns 2 - 5 from file 2 into the same position in file 1.

File 1
3936 C C C C A C .....

File 2
3936 3451 3607 1 1
3937 3451 3607 1 1
3938 3451 3607 1 1
3939 3451 3607 1 1
3940 3451 3607 2 1
3941 3451 3607 2 1
3942 3451 3607 2 1
3943 3451 3607 2 1
3944 3451 3607 2 1

Final File 

3936 3451 3607 1 1 C C C C A C .....
[download]

I have tried the method of unix with the awk command for each column, but it did not work the way I wanted nor was it efficient:

awk 'NR==FNR{A[$1]=$1}A[$3]{sub($3,A[$3]);print}' file2 file1 > new fi
+le
[download]

Thank you for any help

Comment on Two files; if cells match then copy over other columns Select or Download Code

Replies are listed 'Best First'.
Re: Two files; if cells match then copy over other columns by moritz (Cardinal) on Apr 14, 2011 at 15:46 UTC
First read the first file into a hash, with the first column as the key. Then iterate over the seond file line by line, and see if the first column exist in the hash, and if yes, retrieve the rest of the line from the first file. perlintro should get you started, if you troubles somewhere along the way, feel free to ask (and show the code you're having problems with). Perl 6 - second systems done right	[reply]
Re: Two files; if cells match then copy over other columns by ww (Archbishop) on Apr 14, 2011 at 21:47 UTC
As the prophet observed, 'honey is definitely better than vinegar for catching flies.' "I'd just like to thank all of you who are so willing to help those in need; it's really great to see such a helpful community." And you have definitely lathered on the sweet words. For future reference, however, you may wish to heed the Monastery guidance that SOPW should include code (that is, Perl code, IMO) to show what effort you've made and an explanation of why that which you've tried (relevant even though your code is not Perl) isn't what you expected/wanted. Please see On asking for help	[reply]
Re: Two files; if cells match then copy over other columns by umasuresh (Hermit) on Apr 14, 2011 at 20:48 UTC
A non perl solution if you the know the no.of.cols in File1: $ for i in `cut -d" " -f1 f2.txt`; do R1=`grep -w ${i} f2.txt\| cut -d" + " -f2-6` ; R2=`grep -w ${i} f1.txt `; printf "$R2 $R1\n"; done [download]	[reply] [d/l]