awk specific line from another file

Question

[Hughie]

Hello! everyone:

I'm new to linux, here I got a problem:

I have a file file1 like:

3  
6  
7  
9  
12

and file2 which is tab-delimited:

chr1    3052600 3052800 1       E3  
chr1    3052800 3053000 2       E3  
chr1    3059400 3059600 3       E3  
chr1    3059600 3059800 4       E3  
chr1    3059800 3060000 5       E3  
chr1    3062600 3062800 6       E3  
chr1    3101000 3101200 7       E3  
chr1    3105000 3105200 8       E3  
chr1    3105200 3105400 9       E3  
chr1    3116800 3117000 10      E2  
chr1    3117000 3117200 11      E2  
chr1    3164800 3165000 12      E2

and I want to extract the lines in file2 which its 4-th column equal the number in file1 like below:

chr1 3059400 3059600 3 E3   
chr1 3062600 3062800 6 E3     
chr1 3101000 3101200 7 E3   
chr1 3105200 3105400 9 E3   
chr1 3164800 3165000 12 E2

I have spent several hours including wrote a very slow python script, and I searched for the oneline solution, but I got nothing!

awk -v FS="\t" 'NR==FNR{rows[$1]++;next}(substr($NF,1,length($NF)-1) in rows)'  fiel1 file2

Thanks a lot for some suggestions!

[WouterDeCoster]

Hello Hughie,

Please use appropriate tags. Your question is about formatting and awk. That should have been a tag when you created the question. When you add appropriate tags, users that follow the tag (usually experts interested in helping others in that subject matter) get notified of your question, and this means you stand a better chance at getting a relevant, useful response faster.

[Hughie]

Thank you Wouter!
I have revised the tag

[Ram]

The appropriate tags that Wouter mentioned are formatting and awk, not the one out-of-place formatting and awk tag.

[Hughie]

Thank you !
Revised again!

Answer 1

[5heikki]

This is actually a job for join

join -t $'\t' -1 1 -2 4 -o 2.1,2.2,2.3,2.4,2.5 \
    <(sort -g file1) \
    <(sort -t $'\t' -k4,4g file2) \
    > out

[Hughie]

Thank you 5heikki! This is a new solution

Answer 2

[shenwei356]

simple with https://github.com/shenwei356/csvtk

csvtk grep -H -t -f 4 -P file1 file2 > result

[Hughie]

Thank you! I will try this

Answer 3

[EagleEye]

I assume your file2 is TAB-delimited.

sed -i 's/^/\t/' file1.txt
sed -i 's/$/\t/' file1.txt
grep -fF file1.txt file2.txt > combined.txt

Answer 4

[finswimmer]

Hello Hughie,

but there are some problem

you should describe what these problems are.

Nevertheless this should work:

$ awk 'NR==FNR {a[$1]; next} $4 in a {print $0}' file1 file2

fin swimmer

[cpad0112]

Further shortening awk solution:

$ awk 'NR==FNR {a[$1]++} a[$4]' file1 file2
chr1    3059400 3059600 3   E3
chr1    3062600 3062800 6   E3
chr1    3101000 3101200 7   E3
chr1    3105200 3105400 9   E3
chr1    3164800 3165000 12  E2

[finswimmer]

Nice!

Could you please explain why this works?

fin swimmer

[cpad0112]

@finswimmer: {a[$1]++} = makes an array of first column from first file (from NR==FNR). Since it is single column 1 or 0 should not matter and no other commands as well. When it moves on second file, it makes index from column 4 ($4) from second file. Matches with those lines from array from first step (a[$4]), then prints matching lines from file 2.

Actually it is collapsed from your awk solution above:

awk 'NR==FNR {a[$1]; next} $4 in a {print $0}' file1 file2
awk 'NR==FNR {a[$1]++} $4 in a {print $0}' file1 file2
awk 'NR==FNR {a[$1]++} $4 in a' file1 file2
awk 'NR==FNR {a[$1]++} a[$4]' file1 file2

[finswimmer]

Hello cpad0112,

thanks a lot for this explanation. I didn't know that awk skippes the part after {...} without using next.

fin swimmer

[cpad0112]

Np. You will learn all these tricks from Pierre, Kevin and other stars awk one liners, the way I did.

[finswimmer]

I investigate a little bit more on how this works and I think I got it.

NR==FNR {a[$1]++} makes in array with the first column from the first file as index and assign an positiv number to it (increment with ++).

The part after {...} isn't realy skipped. It is the next condition! If it evaluate to true than the line, which is currently processed by awk, is printed. As the first file haven't a 4th column, this always will be false and nothing will be printed. If we reach the second file, a[$4] evaluates to true if the value from the 4th column is an index if a and returns not 0. This is why we need to increment before (a[$1]=1 would be fine as well).

fin swimmer

[EagleEye]

Hi, this gives weird output with the following example. In f1.txt, I do not have '12' but still the output provides a row with '12'.

cat f1.txt
3
6
7
9

cat f2.txt

chr1    3052600 3052800 1       E3
chr1    3052800 3053000 2       E3
chr1    3059400 3059600 3       E3
chr1    3059600 3059800 4       E3
chr1    3059800 3060000 5       E3
chr1    3062600 3062800 6       E3
chr1    3101000 3101200 7       E3
chr1    3105000 3105200 8       E3
chr1    3105200 3105400 9       E3
chr1    3116800 3117000 10      E2
chr1    3117000 3117200 11      E2
chr1    3164800 3165000 12      E2

awk 'NR==FNR {a[$1]++} a[$4]' f1.txt f2.txt

chr1    3059400 3059600 3       E3
chr1    3062600 3062800 6       E3
chr1    3101000 3101200 7       E3
chr1    3105200 3105400 9       E3
chr1    3164800 3165000 12      E2

[cpad0112]

In OP, first file has 12

[EagleEye]

Yes but with my example above it did not work. I did not have 12 in my 'f1.txt'.

[EagleEye]

Oops sorry it works fine. I did a stupid mistake.

[Hughie]

Thank you cpad0112 for your nice answer! question sloved!

[Hughie]

Thank you fin swimmer for your reply!

[WouterDeCoster]

If an answer was helpful you should upvote it, if the answer resolved your question you should mark it as accepted.