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!

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"
    – Philippos
    Aug 13 '19 at 12:09
  • Thanks @Philippos . You can use this exact command while read i; do grep "$i" master_record; done < species_list .
    – kishs1991
    Aug 13 '19 at 12:43
  • I learned about using "while"! Thanks to both @Philippos and @kishs1991!
    – web
    Aug 14 '19 at 2:16
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.

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?
    – web
    Aug 14 '19 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 ..
    – oliv
    Aug 14 '19 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.

2
  • 1
    Probably some technical error when I pasted the texts. Btw, thanks for helping!
    – web
    Aug 14 '19 at 2:08
  • @web please share a screenshot of the error
    – aborruso
    Aug 14 '19 at 7:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.