0

I have 2 csv files:

file (1 column)1:

Prokaryote,Caudovirales,Myoviridae
Prokaryote,Caudovirales,Podoviridae
Prokaryote,Caudovirales,Siphoviridae
Prokaryote,Ligamenvirales,Lipothrixviridae
Prokaryote,Ligamenvirales,Rudiviridae
Prokaryote,Unassigned,Ampullaviridae

and file 2 (2 columns):

NC_038375   Baculoviridae,Betabaculovirus,Trichoplusia_ni_granulovirus
NC_000867   Corticoviridae,Corticovirus,Pseudoalteromonas_virus_PM2
NC_000866   Myoviridae,Tequatrovirus,Escherichia_virus_T4
NC_000929   Myoviridae,Muvirus,Escherichia_virus_Mu
NC_004166   Siphoviridae,,Bacillus_phage_SPP1
NC_005859   Siphoviridae,Tequintavirus,Escherichia_virus_T5
NC_002166   Siphoviridae,Hendrixvirus,Escherichia_virus_HK022
NC_008720   Podoviridae,Enquatrovirus,Escherichia_virus_N4
NC_002371   Podoviridae,Lederbergvirus,Salmonella_virus_P22
NC_011048   Podoviridae,Salasvirus,Bacillus_virus_phi29
NNC_001929  Geminiviridae,Begomovirus,Abutilon_mosaic_virus
NC_002649   Podoviridae,Salasvirus,Bacillus_virus_GA1

I would like to add 2 first names from file 1 at the begining of each value in second column file 2 if there is a match based on the third name in fname. eg: desired output:

NC_038375   Baculoviridae,Betabaculovirus,Trichoplusia_ni_granulovirus
    NC_000867   Corticoviridae,Corticovirus,Pseudoalteromonas_virus_PM2
    NC_000866   Prokaryote,Caudovirales,Myoviridae,Tequatrovirus,Escherichia_virus_T4
    NC_000929   Prokaryote,Caudovirales,Myoviridae,Muvirus,Escherichia_virus_Mu
    NC_004166   Prokaryote,Caudovirales,Siphoviridae,,Bacillus_phage_SPP1
    NC_005859   Prokaryote,Caudovirales,Siphoviridae,Tequintavirus,Escherichia_virus_T5
    NC_002166   Prokaryote,Caudovirales,Siphoviridae,Hendrixvirus,Escherichia_virus_HK022
    NC_008720   Prokaryote,Caudovirales,Podoviridae,Enquatrovirus,Escherichia_virus_N4
    NC_002371   Prokaryote,Caudovirales,Podoviridae,Lederbergvirus,Salmonella_virus_P22
    NC_011048   Prokaryote,Caudovirales,Podoviridae,Salasvirus,Bacillus_virus_phi29
    NNC_001929  Geminiviridae,Begomovirus,Abutilon_mosaic_virus
    NC_002649   Prokaryote,Caudovirales,Podoviridae,Salasvirus,Bacillus_virus_GA1

any help with that?

6
  • 1
    Is the indenting in the output an error? (If so, please edit and correct.)
    – Sparhawk
    Jun 13, 2019 at 9:48
  • So you want to match 3rd column from file1 to the first value in the 2nd column in file2?
    – Thor
    Jun 13, 2019 at 9:50
  • 1
    Also, is file 2 tab- or space(s)-delimited?
    – Sparhawk
    Jun 13, 2019 at 9:50
  • Sorry, my mistake!!!!
    – k_a_r_o_l
    Jun 13, 2019 at 10:00
  • file 2 is tab separated
    – k_a_r_o_l
    Jun 13, 2019 at 10:01

1 Answer 1

1

Assuming an affermative to my question above, you could do it like this in awk:

parse.awk

FNR == NR {              # Only for the first file
  h[$3] = $1 "," $2      # Collect column one and two into 'h' hash
  next
}

{ split($2, a, ",") }    # Split the second column of the second file to array 'a'

a[1] in h {              # If the first element of the second column of the 
  $2 = h[a[1]] "," $2    # second file is in 'h' then prepend the value to $2
}

1                        # Print all lines

Run it like this:

awk -f parse.awk FS=',' file1 FS='\t' OFS='\t' file2

Output:

NC_038375   Baculoviridae,Betabaculovirus,Trichoplusia_ni_granulovirus
NC_000867   Corticoviridae,Corticovirus,Pseudoalteromonas_virus_PM2
NC_000866   Prokaryote,Caudovirales,Myoviridae,Tequatrovirus,Escherichia_virus_T4
NC_000929   Prokaryote,Caudovirales,Myoviridae,Muvirus,Escherichia_virus_Mu
NC_004166   Prokaryote,Caudovirales,Siphoviridae,,Bacillus_phage_SPP1
NC_005859   Prokaryote,Caudovirales,Siphoviridae,Tequintavirus,Escherichia_virus_T5
NC_002166   Prokaryote,Caudovirales,Siphoviridae,Hendrixvirus,Escherichia_virus_HK022
NC_008720   Prokaryote,Caudovirales,Podoviridae,Enquatrovirus,Escherichia_virus_N4
NC_002371   Prokaryote,Caudovirales,Podoviridae,Lederbergvirus,Salmonella_virus_P22
NC_011048   Prokaryote,Caudovirales,Podoviridae,Salasvirus,Bacillus_virus_phi29
NNC_001929  Geminiviridae,Begomovirus,Abutilon_mosaic_virus
NC_002649   Prokaryote,Caudovirales,Podoviridae,Salasvirus,Bacillus_virus_GA1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .