2

I have a list of species and a master record from database. I wanted to search for the match of species in the third column of master record file and print out the whole line.

species_list

Methanocaldococcus jannaschii
Methanosarcina mazei
Methanosarcina acetivorans
Archaeoglobus fulgidus
Pyrococcus furiosus
Sulfolobus solfataricus
Aeropyrum pernix
Halobacterium sp.
Sulfolobus tokodaii
Nanoarchaeum equitans
Methanothermobacter thermautotrophicus
Pirellula sp.
Borrelia burgdorferi

Given the first column in the file species_list is genus and the second column is the species

master_record

taxon_id     STRING_type     STRING_name_compact     official_name_NCBI
243232  core    Methanocaldococcus jannaschii   Methanocaldococcus jannaschii DSM2661
573063  periphery       Methanocaldococcus infernus     Methanocaldococcus infernus ME
573064  core    Methanocaldococcus fervens      Methanocaldococcus fervens AG86
579137  periphery       Methanocaldococcus vulcanius    Methanocaldococcus vulcanius M7
644281  periphery       Methanocaldococcus sp. FS40622  Methanocaldococcus sp. FS406-22
243232  core    Methanocaldococcus jannaschii   Methanocaldococcus jannaschii DSM2661
192952  periphery       Methanosarcina mazei    Methanosarcina mazei Go1
269797  core    Methanosarcina barkeri  Methanosarcina barkeri str. Fusaro
192952  periphery       Methanosarcina mazei    Methanosarcina mazei Go1
192952  periphery       Methanosarcina mazei    Methanosarcina mazei Go1
269797  core    Methanosarcina barkeri  Methanosarcina barkeri str. Fusaro
565033  core    Geoglobus acetivorans   Geoglobus acetivorans
694431  core    Desulfurella acetivorans        Desulfurella acetivorans A63
1123296 core    Stenoxybacter acetivorans       Stenoxybacter acetivorans DSM19021
224325  core    Archaeoglobus fulgidus  Archaeoglobus fulgidus DSM4304

Desired output:

243232  core    Methanocaldococcus jannaschii   Methanocaldococcus jannaschii DSM2661
243232  core    Methanocaldococcus jannaschii   Methanocaldococcus jannaschii DSM2661
192952  periphery       Methanosarcina mazei    Methanosarcina mazei Go1
192952  periphery       Methanosarcina mazei    Methanosarcina mazei Go1
192952  periphery       Methanosarcina mazei    Methanosarcina mazei Go1
192952  periphery       Methanosarcina mazei    Methanosarcina mazei Go1
192952  periphery       Methanosarcina mazei    Methanosarcina mazei Go1
224325  core    Archaeoglobus fulgidus  Archaeoglobus fulgidus DSM4304

I was trying grep in for loop

for i in $(cat species_list); do grep -w "$i" master_record; done

but all I managed to get were either the line with matched genus or species instead of both simultaneously. Also, it doesn't specify the search on third column.

I tried using awk as well

awk 'NR=FNR{a[$0]; next}{if ($3 in a){print $0}}' species_list master_record

but having no result.

PS: I am a beginner in scripting. I would appreciate any help given. Thanks!

Improve this question
3
  • 1
    Your lines get split at the space. Try with setting IFS or with while read i ... <species_list instead of your for attempt and place double quotes around your variable: "$i" Commented Aug 13, 2019 at 12:09
  • Thanks @Philippos . You can use this exact command while read i; do grep "$i" master_record; done < species_list . Commented Aug 13, 2019 at 12:43
  • I learned about using "while"! Thanks to both @Philippos and @kishs1991! Commented Aug 14, 2019 at 2:16

2 Answers 2

Reset to default
2

You could use either awk or grep (and no for loop):

grep -f species master_record

-f allows to give a file with a list of regular expression

or

awk 'NR==FNR{a[$0];next}(($3 " " $4) in a)' file1 file2

This is almost the same command as yours, except the matching array element.

Improve this answer
2
  • Thanks for the help! Now I can get the output with both strings matched. Btw, I tried the two suggestion but both of them generated different number of output lines. 269 lines for grep and 267 lines for awk. Wonder if you have any idea why is it so? Commented Aug 14, 2019 at 2:12
  • Please diff both command outputs to see what the difference are. A possible clue is the dot in the species file that are treated as a regex. You could try grep -f <(sed 's/\./\\./g' file1) file2 to escape these .. Commented Aug 14, 2019 at 6:21
1

Using Miller (https://github.com/johnkerl/miller) you could do a join

mlr --nidx --fs " " --repifs join -j 1,2 -l 3,4 -r 1,2 -f master_record.csv species_list.csv

It gives your 

243232 core Methanocaldococcus jannaschii DSM2661
243232 core Methanocaldococcus jannaschii DSM2661
192952 periphery Methanosarcina mazei Go1
192952 periphery Methanosarcina mazei Go1
192952 periphery Methanosarcina mazei Go1
224325 core Archaeoglobus fulgidus DSM4304

In your desired output you have 5 time "Methanosarcina mazei". Why?

In the master_record you have it only three time.

Improve this answer
2
  • 1
    Probably some technical error when I pasted the texts. Btw, thanks for helping! Commented Aug 14, 2019 at 2:08
  • @web please share a screenshot of the error Commented Aug 14, 2019 at 7:58

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.