Hi,
I have two files (file 1 has one column and file 2 four columns), I
have to choose the rows of file 2 where column 2 & 3 of file 2 matches
with column 1 of file 1. Anybody has any idea?


To add to this with an example,

file 1 :
1_8
1_9
1_10
1_11


file 2 :
1 1_500 1_600 0.000 1.0 0.0 0.0
1 1_500 1_500 0.000 0.0 0.0 1.0
1 1_9 1_100 0.000 0.50000 0.50000 0.00000
1 1_9 1_200 0.000 0.50000 0.50000 0.00000
1 1_9 1_400 0.000 1.0 0.0 0.0
.....
1 1_8 1_500 2.107 0.59766 0.40234 0.00000
1 1_8 1_9 2.107 0.89431 0.10569 0.00000
1 1_8 1_300 2.107 0.0 1.0 0.0


merge two files such that it will print
1 1_8 1_9 2.107 0.89431 0.10569 0.00000


i.e. the rows of file 2 where col.2 and col.3 matches with any two
entries in file 1

Any would be extremely well appreciated.
Thanks

mainak.sen@gmail.com wrote:
> Hi,
> I have two files (file 1 has one column and file 2 four columns), I
> have to choose the rows of file 2 where column 2 & 3 of file 2 matches
> with column 1 of file 1. Anybody has any idea?
>
>
> To add to this with an example,
>
> file 1 :
> 1_8
> 1_9
> 1_10
> 1_11
>
>
> file 2 :
> 1 1_500 1_600 0.000 1.0 0.0 0.0
> 1 1_500 1_500 0.000 0.0 0.0 1.0
> 1 1_9 1_100 0.000 0.50000 0.50000 0.00000
> 1 1_9 1_200 0.000 0.50000 0.50000 0.00000
> 1 1_9 1_400 0.000 1.0 0.0 0.0
> ....
> 1 1_8 1_500 2.107 0.59766 0.40234 0.00000
> 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
> 1 1_8 1_300 2.107 0.0 1.0 0.0
>
>
> merge two files such that it will print
> 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
>
>
> i.e. the rows of file 2 where col.2 and col.3 matches with any two
> entries in file 1
>
> Any would be extremely well appreciated.
> Thanks

Two questions:
1. If 1_8 is in both the second and third col, is that a match, or does
it need two different values from file 1?

2. Is everything space delimited ?

Dean G.

mainak.sen@gmail.com wrote:
> Hi,
> I have two files (file 1 has one column and file 2 four columns), I
> have to choose the rows of file 2 where column 2 & 3 of file 2 matches
> with column 1 of file 1. Anybody has any idea?
>
>
> To add to this with an example,
>
> file 1 :
> 1_8
> 1_9
> 1_10
> 1_11
>
>
> file 2 :
> 1 1_500 1_600 0.000 1.0 0.0 0.0
> 1 1_500 1_500 0.000 0.0 0.0 1.0
> 1 1_9 1_100 0.000 0.50000 0.50000 0.00000
> 1 1_9 1_200 0.000 0.50000 0.50000 0.00000
> 1 1_9 1_400 0.000 1.0 0.0 0.0
> ....
> 1 1_8 1_500 2.107 0.59766 0.40234 0.00000
> 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
> 1 1_8 1_300 2.107 0.0 1.0 0.0
>
>
> merge two files such that it will print
> 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
>
>
> i.e. the rows of file 2 where col.2 and col.3 matches with any two
> entries in file 1
>
> Any would be extremely well appreciated.
> Thanks
>

awk 'NR==FNR{arr[$1];next}($2 in arr)&&($3 in arr)' file1 file2

What if col2 and col3 of file2 have the same value and only match with
one entry in file1?

Ed.

mainak.sen@gmail.com wrote:
> Hi,
> I have two files (file 1 has one column and file 2 four columns), I
> have to choose the rows of file 2 where column 2 & 3 of file 2 matches
> with column 1 of file 1. Anybody has any idea?
>
>
> To add to this with an example,
>
> file 1 :
> 1_8
> 1_9
> 1_10
> 1_11
>
>
> file 2 :
> 1 1_500 1_600 0.000 1.0 0.0 0.0
> 1 1_500 1_500 0.000 0.0 0.0 1.0
> 1 1_9 1_100 0.000 0.50000 0.50000 0.00000
> 1 1_9 1_200 0.000 0.50000 0.50000 0.00000
> 1 1_9 1_400 0.000 1.0 0.0 0.0
> ....
> 1 1_8 1_500 2.107 0.59766 0.40234 0.00000
> 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
> 1 1_8 1_300 2.107 0.0 1.0 0.0
>
>
> merge two files such that it will print
> 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
>
>
> i.e. the rows of file 2 where col.2 and col.3 matches with any two
> entries in file 1
>
> Any would be extremely well appreciated.
> Thanks
>

First read in data from file 1, then check whether the selected rows of
file 2 are in the stored data set.

awk 'NR==FNR {s[$1]} NR!=FNR && ($2 in s) && ($3 in s)' file1 file2


Janis

mainak.sen@gmail.com wrote:
>
> I have two files (file 1 has one column and file 2 four columns), I
> have to choose the rows of file 2 where column 2 & 3 of file 2 matches
> with column 1 of file 1. Anybody has any idea?
>
>
> To add to this with an example,
>
> file 1 :
> 1_8
> 1_9
> 1_10
> 1_11
>
>
> file 2 :
> 1 1_500 1_600 0.000 1.0 0.0 0.0
> 1 1_500 1_500 0.000 0.0 0.0 1.0
> 1 1_9 1_100 0.000 0.50000 0.50000 0.00000
> 1 1_9 1_200 0.000 0.50000 0.50000 0.00000
> 1 1_9 1_400 0.000 1.0 0.0 0.0
> ....
> 1 1_8 1_500 2.107 0.59766 0.40234 0.00000
> 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
> 1 1_8 1_300 2.107 0.0 1.0 0.0
>
>
> merge two files such that it will print
> 1 1_8 1_9 2.107 0.89431 0.10569 0.00000

perl -ane'BEGIN { @x{ grep [ chomp ], `cat file1` } = () }
exists $x{ $F[ 1 ] } && exists $x{ $F[ 2 ] } && print' file2



John
--
Perl isn't a toolbox, but a small machine shop where you can special-order
certain sorts of tools at low cost and in short order. -- Larry Wall

Yes, that's a match. Ideally the column 2&3 should have different
numbers, but even if it's same, I can write a one liner awk making them
different: awk '$2!=$3 {print $0}'...
Thanks.



Dean G. wrote:
> mainak.sen@gmail.com wrote:
> > Hi,
> > I have two files (file 1 has one column and file 2 four columns), I
> > have to choose the rows of file 2 where column 2 & 3 of file 2 matches
> > with column 1 of file 1. Anybody has any idea?
> >
> >
> > To add to this with an example,
> >
> > file 1 :
> > 1_8
> > 1_9
> > 1_10
> > 1_11
> >
> >
> > file 2 :
> > 1 1_500 1_600 0.000 1.0 0.0 0.0
> > 1 1_500 1_500 0.000 0.0 0.0 1.0
> > 1 1_9 1_100 0.000 0.50000 0.50000 0.00000
> > 1 1_9 1_200 0.000 0.50000 0.50000 0.00000
> > 1 1_9 1_400 0.000 1.0 0.0 0.0
> > ....
> > 1 1_8 1_500 2.107 0.59766 0.40234 0.00000
> > 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
> > 1 1_8 1_300 2.107 0.0 1.0 0.0
> >
> >
> > merge two files such that it will print
> > 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
> >
> >
> > i.e. the rows of file 2 where col.2 and col.3 matches with any two
> > entries in file 1
> >
> > Any would be extremely well appreciated.
> > Thanks
>
> Two questions:
> 1. If 1_8 is in both the second and third col, is that a match, or does
> it need two different values from file 1?
>
> 2. Is everything space delimited ?
>
> Dean G.

and yes, everything space delimited.

Dean G. wrote:
> mainak.sen@gmail.com wrote:
> > Hi,
> > I have two files (file 1 has one column and file 2 four columns), I
> > have to choose the rows of file 2 where column 2 & 3 of file 2 matches
> > with column 1 of file 1. Anybody has any idea?
> >
> >
> > To add to this with an example,
> >
> > file 1 :
> > 1_8
> > 1_9
> > 1_10
> > 1_11
> >
> >
> > file 2 :
> > 1 1_500 1_600 0.000 1.0 0.0 0.0
> > 1 1_500 1_500 0.000 0.0 0.0 1.0
> > 1 1_9 1_100 0.000 0.50000 0.50000 0.00000
> > 1 1_9 1_200 0.000 0.50000 0.50000 0.00000
> > 1 1_9 1_400 0.000 1.0 0.0 0.0
> > ....
> > 1 1_8 1_500 2.107 0.59766 0.40234 0.00000
> > 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
> > 1 1_8 1_300 2.107 0.0 1.0 0.0
> >
> >
> > merge two files such that it will print
> > 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
> >
> >
> > i.e. the rows of file 2 where col.2 and col.3 matches with any two
> > entries in file 1
> >
> > Any would be extremely well appreciated.
> > Thanks
>
> Two questions:
> 1. If 1_8 is in both the second and third col, is that a match, or does
> it need two different values from file 1?
>
> 2. Is everything space delimited ?
>
> Dean G.

Thanks a lot, it works!



Janis Papanagnou wrote:
> mainak.sen@gmail.com wrote:
> > Hi,
> > I have two files (file 1 has one column and file 2 four columns), I
> > have to choose the rows of file 2 where column 2 & 3 of file 2 matches
> > with column 1 of file 1. Anybody has any idea?
> >
> >
> > To add to this with an example,
> >
> > file 1 :
> > 1_8
> > 1_9
> > 1_10
> > 1_11
> >
> >
> > file 2 :
> > 1 1_500 1_600 0.000 1.0 0.0 0.0
> > 1 1_500 1_500 0.000 0.0 0.0 1.0
> > 1 1_9 1_100 0.000 0.50000 0.50000 0.00000
> > 1 1_9 1_200 0.000 0.50000 0.50000 0.00000
> > 1 1_9 1_400 0.000 1.0 0.0 0.0
> > ....
> > 1 1_8 1_500 2.107 0.59766 0.40234 0.00000
> > 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
> > 1 1_8 1_300 2.107 0.0 1.0 0.0
> >
> >
> > merge two files such that it will print
> > 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
> >
> >
> > i.e. the rows of file 2 where col.2 and col.3 matches with any two
> > entries in file 1
> >
> > Any would be extremely well appreciated.
> > Thanks
> >
>
> First read in data from file 1, then check whether the selected rows of
> file 2 are in the stored data set.
>
> awk 'NR==FNR {s[$1]} NR!=FNR && ($2 in s) && ($3 in s)' file1 file2
>
>
> Janis

Yes, it works. Thanks.



Ed Morton wrote:
> mainak.sen@gmail.com wrote:
> > Hi,
> > I have two files (file 1 has one column and file 2 four columns), I
> > have to choose the rows of file 2 where column 2 & 3 of file 2 matches
> > with column 1 of file 1. Anybody has any idea?
> >
> >
> > To add to this with an example,
> >
> > file 1 :
> > 1_8
> > 1_9
> > 1_10
> > 1_11
> >
> >
> > file 2 :
> > 1 1_500 1_600 0.000 1.0 0.0 0.0
> > 1 1_500 1_500 0.000 0.0 0.0 1.0
> > 1 1_9 1_100 0.000 0.50000 0.50000 0.00000
> > 1 1_9 1_200 0.000 0.50000 0.50000 0.00000
> > 1 1_9 1_400 0.000 1.0 0.0 0.0
> > ....
> > 1 1_8 1_500 2.107 0.59766 0.40234 0.00000
> > 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
> > 1 1_8 1_300 2.107 0.0 1.0 0.0
> >
> >
> > merge two files such that it will print
> > 1 1_8 1_9 2.107 0.89431 0.10569 0.00000
> >
> >
> > i.e. the rows of file 2 where col.2 and col.3 matches with any two
> > entries in file 1
> >
> > Any would be extremely well appreciated.
> > Thanks
> >
>
> awk 'NR==FNR{arr[$1];next}($2 in arr)&&($3 in arr)' file1 file2
>
> What if col2 and col3 of file2 have the same value and only match with
> one entry in file1?
>
> Ed.