2
$\begingroup$

I have the following file in tab separated values (TSV).

ID Taxonomy
76980f6d906c6baaef3f96559fc7ba1b d__Eukaryota; p__Cercozoa; c__Vampyrellidae; o__Vampyrellidae; f__Vampyrellidae; g__uncultured
4f808d3adec894395304661391037369 d__Eukaryota; p__Ochrophyta; c__Xanthophyceae; o__uncultured; f__uncultured; g__uncultured
777584465097568f5b30bc9e2b6584c1 d__Eukaryota; p__Labyrinthulomycetes; c__uncultured; o__uncultured; f__uncultured; g__uncultured
5a99dafabe78847d8f5fed0d37f8672a d__Eukaryota; p__Labyrinthulomycetes; c__Labyrinthulomycetes; o__Labyrinthulomycetes; f__Thraustochytriaceae; g__uncultured

I would like to modify the "g__uncultured" with the last known rank possibly using either shell or R scripts.

The new table would then read:

ID Taxonomy
76980f6d906c6baaef3f96559fc7ba1b d__Eukaryota; p__Cercozoa; c__Vampyrellidae; o__Vampyrellidae; f__Vampyrellidae; g__f__Vampyrellidae
4f808d3adec894395304661391037369 d__Eukaryota; p__Ochrophyta; c__Xanthophyceae; o__uncultured; f__uncultured; g__c__Xanthophyceae
777584465097568f5b30bc9e2b6584c1 d__Eukaryota; p__Labyrinthulomycetes; c__uncultured; o__uncultured; f__uncultured; g__p__Labyrinthulomycetes
5a99dafabe78847d8f5fed0d37f8672a d__Eukaryota; p__Labyrinthulomycetes; c__Labyrinthulomycetes; o__Labyrinthulomycetes; f__Thraustochytriaceae; g__f__Thraustochytriaceae
Improve this question
$\endgroup$
1
  • 1
    $\begingroup$ It would help if you could provide your attempts to solve this, it would be easier to help you fix the issue than propose an answer. $\endgroup$
    – llrs
    Aug 4, 2022 at 7:52

1 Answer 1

Reset to default
1
$\begingroup$

The pandas solution is here:

#!/usr/bin/env python3
import pandas as pd
from pathlib import Path

path = '/Whatever'
infile = 'myfile.csv'
outfile = 'outfile.csv'
pd.set_option('display.max_colwidth', None)
df = pd.read_csv(Path(path,infile), sep='\t')
taxaChange = {'76980f6d906c6baaef3f96559fc7ba1b':'g__f__Vampyrellidae', '4f808d3adec894395304661391037369':'g__c__Xanthophyceae'}
df2 = df.set_index('ID')
df3 = df2
for x,y in taxaChange.items():
    df3.loc[x, 'Taxonomy'] = df2.loc[x, 'Taxonomy'].replace('g__uncultured', y)
print (df3)

df.to_csv(Path(path,outfile), sep='\t')

Output

ID                                       Taxonomy                                                                                                                                             
76980f6d906c6baaef3f96559fc7ba1b         d__Eukaryota; p__Cercozoa; c__Vampyrellidae; o__Vampyrellidae; f__Vampyrellidae; g__f__Vampyrellidae
4f808d3adec894395304661391037369         d__Eukaryota; p__Ochrophyta; c__Xanthophyceae; o__uncultured; f__uncultured; g__c__Xanthophyceae
777584465097568f5b30bc9e2b6584c1         d__Eukaryota; p__Labyrinthulomycetes; c__uncultured; o__uncultured; f__uncultured; g__uncultured
5a99dafabe78847d8f5fed0d37f8672a         d__Eukaryota; p__Labyrinthulomycetes; c__Labyrinthulomycetes; o__Labyrinthulomycetes; f__Thraustochytriaceae; g__uncultured

Both g__f__Vampyrellidae and g__c__Xanthophyceae are there, i.e. the code was tested and worked fine.

The point for the pandas approach is you've a natural table format so it makes sense to take it straight to a dataframe (table -> dataframe). regex-ing using \t and \n is of course the alternative and you need to formulate the table as a hash (Perl) or dictionary (Python).

Notes The df and df2 can reduced a single dataframe by updating the df (inplace was omitted and will work for an index), rather than creating a new df. However, the df3 is needed (need to copy the dataframe) because 'inplace' didn't work. inplace is a command to force the dataframe to update. The set_option is just so I can print the display without truncation.

Finally You substitute the infile and outfile to correct path and name values, save it as a .py file and just python3 myscript.py.

Improve this answer
$\endgroup$
2
  • $\begingroup$ Thanks for your reply. However, my tsv file was only an example. I've a lot of g__uncultured that should be replaced. Hence, the taxaChange should be something more general, able to parse different lines. $\endgroup$
    – Marco
    Aug 4, 2022 at 7:17
  • 1
    $\begingroup$ Hi @Marco. This does answer the specific question in my view and the code was tested and worked fine. It is 70% towards a generalised solution. However, automating the code to process lots of stuff is a separate question as general principle and particularly because its not clear how you want to do that (there are no indicators except for the above comment). I suspect you've got something is specific in mind. You can use this code as the basis for that question if you wish to pursue a pandas solution. pandas is "trendy", but there are lots of ways to do this. $\endgroup$
    – M__ ♦
    Aug 4, 2022 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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