I want to search the second col of a file as a sub-part of 4th col of another file and produce a joint output. In the example, search if B is contained as a sub-part in E:B:C (sub-parts separated by colons). Note the second row is not found doesnt find a match as F isnt there in col 4 second file (second line).
file1
A B
E F
G H
file2
C D L E:B:C
Z Y F FFFF:EEE:PP
G H R H:H:I:J
out
A B C D L E:B:C
E F
G H G H R H:H:I:J
Here is what I tried but doesn't work
awk ' NR == FNR { y[":"$2":"] next }{ x = ":"$4":" for (i in y) if (index(x,i)) print i, $0 } file1 file2
awk '
NR == FNR {
A[$0] = $NF
next
}
{
for ( k in A )
{
n = split ( A[k], R, ":" )
for ( i = 1; i <= n; i++ )
{
if ( R == $2 )
{
print $0, k
f = 1
break
}
}
}
if ( !(f) )
print $0
f = 0
}
' file2 file1
$ awk 'FNR==NR{A[$4]=$0;next}{x=0;for(i in A){split(A,X,":");if($2==X[2]){x=1;print $0,A}}if(x==0)print}' file2 file1
A B C D L E:B:C
E F
G H G H R H:H:I:J
is it possible to limit the search by the first hit? That will reduce the runtime considerably, so as soon as the first hit is met, I would like to move on to the next line in file1, similar to a break in C.
Yes, as soon as field2 of file1 == 2nd sub field of field4 of file2 is true print line of file1 and line of file2 and then next , if you use break here it just terminates loop does not move to next line
$ awk 'FNR==NR{A[$4]=$0;next}{x=0;for(i in A){split(A,X,":");if($2==X[2]){x=1;print $0,A;next}}if(x==0)print}' file2 file1
Example :
$ awk 'FNR==NR{A[$4]=$0;next}{x=0;for(i in A){split(A,X,":");if($2==X[2]){x=1;print $0,A}}print "without next and break";if(x==0)print}' file2 file1
A B C D L E:B:C
without next and break
without next and break
E F
G H G H R H:H:I:J
without next and break
$ awk 'FNR==NR{A[$4]=$0;next}{x=0;for(i in A){split(A,X,":");if($2==X[2]){x=1;print $0,A;next}}print "with next";if(x==0)print}' file2 file1
A B C D L E:B:C
with next
E F
G H G H R H:H:I:J
$ awk 'FNR==NR{A[$4]=$0;next}{x=0;for(i in A){split(A,X,":");if($2==X[2]){x=1;print $0,A;break}}print "with break";if(x==0)print}' file2 file1
A B C D L E:B:C
with break
with break
E F
G H G H R H:H:I:J
with break
Yoda's code will find a match if any subpart of field 4 in file2 is matched by field 2 in file1. Akshay's code will find a match only if the 2nd subpart of field 4 in file 2 is matched by field 2 in file1.
If I read your requirements correctly, I think Yoda's interpretation is what was wanted. The following is an alternative approach that should produce the same output. Depending on the sizes of the input files, this should run faster but use a little more memory:
awk '
NR == FNR {
n = split($4, f, /:/)
for(i = 1; i <= n; i++) o[f] = " " $0
next
}
{ print $0 o[$2]
}' file2 file1
Note that if there are multiple lines in file2 with with identical subfields in field4, this will print the last match rather than the 1st. If you need the 1st instead of the last match, change:
for(i = 1; i <= n; i++) o[f] = " " $0
to:
for(i = 1; i <= n; i++) if(!(f in o)) o[f] = " " $0
If you want to try this on a Solaris/SunOS system, use /usr/xpg4/bin/awk , /usr/xpg6/bin/awk , or nawk instead of awk .
Your specification said that field5 had subfields separated by a colon; your real data seems to contain subfields you want to match in fields 5 and 6 separated by a colon and a space. None of the code we provided will work if your input data doesn't match the specifications you supplied.
I`m terribly sorry for the confusion, the output should be the following with only line1 col2 in file1 being found in file2, the rest of the lines appear in the same way as file1.
Please give us a clear and complete description of where we are supposed to find the matching values in file2! The value you seem to want to match in your latest example ( 169605479 ) does not appear in field 4 or field 5 in your input; it is in field 6. Tring to guess at a pattern to use to match your input from a sample of one line (with no English description that matches your input) is a waste of time.
If you have spaces in the middle of your fields and spaces separating fields, how are we supposed to determine which fields are to be processed?
is supposed to be the 5th column in the input file2, I need to search 169605479 from file1 in the 5th column, which is present as a sub-field separated by ':' , I can see there is a leading space before 169605479, can that be trimmed?
I have attached the original input files which are tab delimited, maybe there is some formatting error when I copy paste.
Neither of the input files you just uploaded contained any tab characters; only spaces. But assuming that both inp1.txt and inp2.txt do use tab characters as the field separator and that the match field is field 5 (with a colon followed by zero or more spaces as the subfield separator), the following should do what you want:
awk -F '\t' '
NR == FNR {
n = split($5, f, /: */)
for(i = 1; i <= n; i++) {
o[f] = "\t" $0
}
next
}
{ print $0 o[$2]
}' inp2.txt inp1.txt
There is only one tab character in your manually created sample output file. That is between the end of the 1st line of inp1.txt and the entire contents of inp2.txt.
Please show us the output of the commands:
od -c inp1.txt
od -c inp2.txt
on your system so we can see where the tabs are supposed to be located.
Thank you for being so patient, here is the information you requested. the code still doesnt produce output, it is the same as file1
od -c inp2.txt
0000000 Q 0 U R 5 1 \t Q 0 U R 5 1 _ P H
0000020 A N O \t 5 9 7 3 0 3 7 \t X P _ 0
0000040 0 1 7 9 6 1 6 0 . 1 \t 1 1 1 0 6
0000060 5 7 0 7 : 1 6 9 6 0 5 4 7 9 \t
0000100 \t G O : 0 0 0 4 1 9 0 ; G O :
0000120 0 0 0 6 5 0 8 \t \t U n i R e f 1
0000140 0 0 _ Q 0 U R 5 1 \t U n i R e f
0000160 9 0 _ Q 0 U R 5 1 \t U n i R e f
0000200 5 0 _ N 4 X C Z 1 \t U P I 0 0 0
0000220 0 D D 1 1 E 3 \t \t 3 2 1 6 1 4 \t
0000240 \t \t 1 8 0 2 4 5 7 0 \t C H 4 4 5
0000260 3 3 2 \t E A T 8 6 8 2 7 . 1 \t \t
0000300 \t \t \n
0000303
# od -c inp1.txt
0000000 c o m p 1 0 0 0 0 8 _ c 0 _ s e
0000020 q 1 \t 1 6 9 6 0 5 4 7 9 \r \n c o
0000040 m p 1 0 0 1 5 6 5 _ c 0 _ s e q
0000060 1 \t 3 2 6 4 9 3 8 5 0 \r \n c o m
0000100 p 1 0 0 1 7 _ c 0 _ s e q 1 \t 3
0000120 9 9 1 7 2 2 5 1 \r \n c o m p 1 0
0000140 0 2 0 _ c 0 _ s e q 1 \t 3 2 2 7
0000160 0 3 4 4 7 \r \n c o m p 1 0 0 4 1
0000200 6 _ c 0 _ s e q 1 \t 1 1 5 4 7 1
0000220 2 9 1 \r \n c o m p 1 0 0 4 5 _ c
0000240 0 _ s e q 1 \t 4 7 3 7 8 9 5 0 9
0000260 \r \n c o m p 1 0 0 7 9 8 7 _ c 0
0000300 _ s e q 1 \t 3 5 7 1 1 2 4 1 5 \r
0000320 \n c o m p 1 0 0 8 0 3 _ c 0 _ s
0000340 e q 1 \t 4 7 5 6 1 0 1 0 0 \r \n c
0000360 o m p 1 0 0 9 0 _ c 0 _ s e q 1
0000400 \t 3 2 2 7 0 6 7 7 9 \r \n c o m p
0000420 1 0 0 9 4 0 2 _ c 0 _ s e q 1 \t
0000440 3 2 6 5 1 0 3 3 7 \r \n
OK. It looks like inp1.txt was created on a Windows box. Your matching fields defined in inp2.txt are strings of numbers, but in inp1.txt they are strings of numbers followed by a <carriage-return> character. Let's try once more:
I tested with small samples and everything seems to work, I was wondering about the run-time of the original data.
File1 is 3.5Mb and File2 is 7.9Gb, is the code supposed to run for a long time? It has been 4 hrs, and it hasn't produced any output lines.