Find pattern and print next column (two files)

2
votes

I would like to look for patterns in column 1 of File1 in File2, then print second column of File1 next to File2:

File1 (two columns tab-separated):

APBW    lung
APCA    non virulent
ABKM    lung
APBX    lung
KK020   -
APBZ    non virulent
AOSU    lung
APBY    non virulent
APBV    joint; lung; CNS
CP001321    virulent
APBT    virulent
APBU    non-virulent
APCB    moderadamente virulenta (nose)
CP005384    -

File2 (two columns tab-separated):

HS372_00243 gi|219690483|gb|CP001321.1|
HS372_00436 gi|529264994|gb|APBX01000055.1|
HS372_00445 gi|529256455|gb|APBT01000061.1|
HS372_00544 gi|529259149|gb|APBV01000035.1|
HS372_00545 gi|529259149|gb|APBV01000035.1|
HS372_00546 gi|529259149|gb|APBV01000035.1|

Desired output (three columns tab-separated):

HS372_00243 gi|219690483|gb|CP001321.1| virulent
HS372_00436 gi|529264994|gb|APBX01000055.1| lung
HS372_00445 gi|529256455|gb|APBT01000061.1| virulent
HS372_00544 gi|529259149|gb|APBV01000035.1| jointlungCNS
HS372_00545 gi|529259149|gb|APBV01000035.1| jointlungCNS
HS372_00546 gi|529259149|gb|APBV01000035.1| jointlungCNS

Provisional bash code (not working), but open to other languages:

while read vl; do grep "$vl" File2 ; done < File1

Also tried with awk (is not working because it seems it's looking for an exact match and my string in File2 is surrounded by other things):

awk 'BEGIN { FS = OFS = "\t" } FNR==NR{a[$1]=$0;next}($1 in a){print a[$1],$2,$3}' File1 File2

Thanks, Bernardo

3
pythonperlbashawkbioinformatics
It is not clear the pattern you use from file 1 to file 2. Sometimes it is the same one, sometimes the first four letters... - fedorqui 'SO stop harming'
Patterns in File1 have different lengths - biotech
What I mean is that given your sample file1, nothing should be written when APBX01000055 is found in file 2, because APBX01000055 is not in file1. - fedorqui 'SO stop harming'
OK, I understood. The thing is that I would like to print 'lung' with just finding the incomplete 'APBX' string - biotech
Mmm this is not consistent. If we get this, then it means we have to check 4 chars. But then what happens with CP001321? - fedorqui 'SO stop harming'

3 Answers

1
votes

Something like this sounds like what you're asking for:

awk '
BEGIN { FS=OFS="\t" }
NR==FNR { map[$1] = $2; next }
{
    for (key in map)
        if ($0 ~ key)
            $0 = $0 OFS map[key]
    print
}
' file1 file2
0
votes

Hope this might help you 

file2_contents = [i.strip() for i in open("file2.txt").readlines()]

with open("file1.txt") as file1:
    for each_line in file1.readlines():
        code=each_line.split('\t')[0].strip()
        part=each_line.split('\t')[1].strip()
        for each_file2_contents in file2_contents:
            if code in each_file2_contents:
                print  each_file2_contents+'\t'+part
0
votes

In Perl:

use warnings;
use strict;
use Data::Dumper;

my $str1 = <<"EOS1";
APBW    lung
APCA    non virulent
ABKM    lung
APBX    lung
KK020   -
APBZ    non virulent
AOSU    lung
APBY    non virulent
APBV    joint; lung; CNS
CP001321    virulent
APBT    virulent
APBU    non-virulent
APCB    moderadamente virulenta (nose)
CP005384    -
EOS1

my $str2 = <<"EOS2";
HS372_00243 gi|219690483|gb|CP001321.1|
HS372_00436 gi|529264994|gb|APBX01000055.1|
HS372_00445 gi|529256455|gb|APBT01000061.1|
HS372_00544 gi|529259149|gb|APBV01000035.1|
HS372_00545 gi|529259149|gb|APBV01000035.1|
HS372_00546 gi|529259149|gb|APBV01000035.1|
EOS2

#HS372_00243 gi|219690483|gb|CP001321.1| virulent

my %data1;my %data2;
foreach my $line ( split(/\n+/,$str1) ){
    my @f = split(/\t/,$line);
    $data1{$f[0]} = $f[1];
}
my @data2 = split(/\n+/,$str2);
foreach my $line ( split(/\n+/,$str2) ){
    my @f  = split(/\t/,$line);
    my @sf = split(/\|/,$f[1]);

    $data2{$sf[3]} = $line;
}
#print Dumper(\%data2);
foreach my $s1 ( sort { length($b) <=> length($a) } keys %data1){
    foreach my $d2 (@data2){
        my @f  = split(/\t/,$d2);
        my @sf = split(/\|/,$f[1]);
        if ($sf[3] =~ m!^$s1!is){
            #warn "found $s1 in $d2\n";
            print "$d2\t$data1{$s1}\n";
        }
    }
}

output:
HS372_00243     gi|219690483|gb|CP001321.1|     virulent
HS372_00445     gi|529256455|gb|APBT01000061.1| virulent
HS372_00544     gi|529259149|gb|APBV01000035.1| joint; lung; CNS
HS372_00545     gi|529259149|gb|APBV01000035.1| joint; lung; CNS
HS372_00546     gi|529259149|gb|APBV01000035.1| joint; lung; CNS
HS372_00436     gi|529264994|gb|APBX01000055.1| lung