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?

  • 1
    Is the indenting in the output an error? (If so, please edit and correct.) – Sparhawk Jun 13 '19 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 '19 at 9:50
  • 1
    Also, is file 2 tab- or space(s)-delimited? – Sparhawk Jun 13 '19 at 9:50
  • Sorry, my mistake!!!! – k_a_r_o_l Jun 13 '19 at 10:00
  • file 2 is tab separated – k_a_r_o_l Jun 13 '19 at 10:01
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
| improve this answer | |

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.