<p>The <code>pandas</code> solution is here:</p>
<pre><code>#!/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')
</code></pre>
<p>Output</p>
<pre><code>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
</code></pre>
<p>Both <code>g__f__Vampyrellidae</code> and <code>g__c__Xanthophyceae</code> are there, i.e. the code was tested and worked fine.</p>
<p>The point for the <code>pandas</code> approach is you've a natural table format so it makes sense to take it straight to a dataframe (table -&gt; dataframe). regex-ing using <code>\t</code> and <code>\n</code> is of course the alternative and you need to formulate the table as a hash (Perl) or dictionary (Python).</p>
<p><strong>Notes</strong> The <code>df</code> and <code>df2</code> can reduced a single dataframe by updating the <code>df</code> (<code>inplace</code> was omitted and will work for an index), rather than creating a new <code>df</code>. However, the <code>df3</code> is needed (need to copy the dataframe) because 'inplace' didn't work. <code>inplace</code> is a command to force the dataframe to update. The <code>set_option</code> is just so I can print the display without truncation.</p>
<p><em>Finally</em>
You substitute the infile and outfile to correct path and name values, save it as a <code>.py</code> file and just <code>python3 myscript.py</code>.</p>

CC BY-SA 4.0
2022-08-04T07:17:59.343Z
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.
Marco
CC BY-SA 4.0
2022-08-04T11:44:30.147Z
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.
M__
CC BY-SA 4.0
2022-08-02T18:58:13.983Z
2022-08-04T11:50:07.333Z
2022-08-04T11:50:07.333Z
M__
<p>I have the following file in tab separated values (TSV).</p>
<div class="s-table-container">
<table class="s-table">
<thead>
<tr>
<th style="text-align: left;">ID</th>
<th style="text-align: left;">Taxonomy</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left;">76980f6d906c6baaef3f96559fc7ba1b</td>
<td style="text-align: left;">d__Eukaryota; p__Cercozoa; c__Vampyrellidae; o__Vampyrellidae; f__Vampyrellidae; g__uncultured</td>
</tr>
<tr>
<td style="text-align: left;">4f808d3adec894395304661391037369</td>
<td style="text-align: left;">d__Eukaryota; p__Ochrophyta; c__Xanthophyceae; o__uncultured; f__uncultured; g__uncultured</td>
</tr>
<tr>
<td style="text-align: left;">777584465097568f5b30bc9e2b6584c1</td>
<td style="text-align: left;">d__Eukaryota; p__Labyrinthulomycetes; c__uncultured; o__uncultured; f__uncultured; g__uncultured</td>
</tr>
<tr>
<td style="text-align: left;">5a99dafabe78847d8f5fed0d37f8672a</td>
<td style="text-align: left;">d__Eukaryota; p__Labyrinthulomycetes; c__Labyrinthulomycetes; o__Labyrinthulomycetes; f__Thraustochytriaceae; g__uncultured</td>
</tr>
</tbody>
</table>
</div>
<p>I would like to modify the &quot;g__uncultured&quot; with the last known rank possibly using either shell or R scripts.</p>
<p>The new table would then read:</p>
<div class="s-table-container">
<table class="s-table">
<thead>
<tr>
<th style="text-align: left;">ID</th>
<th style="text-align: left;">Taxonomy</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left;">76980f6d906c6baaef3f96559fc7ba1b</td>
<td style="text-align: left;">d__Eukaryota; p__Cercozoa; c__Vampyrellidae; o__Vampyrellidae; f__Vampyrellidae; g__f__Vampyrellidae</td>
</tr>
<tr>
<td style="text-align: left;">4f808d3adec894395304661391037369</td>
<td style="text-align: left;">d__Eukaryota; p__Ochrophyta; c__Xanthophyceae; o__uncultured; f__uncultured; g__c__Xanthophyceae</td>
</tr>
<tr>
<td style="text-align: left;">777584465097568f5b30bc9e2b6584c1</td>
<td style="text-align: left;">d__Eukaryota; p__Labyrinthulomycetes; c__uncultured; o__uncultured; f__uncultured; g__p__Labyrinthulomycetes</td>
</tr>
<tr>
<td style="text-align: left;">5a99dafabe78847d8f5fed0d37f8672a</td>
<td style="text-align: left;">d__Eukaryota; p__Labyrinthulomycetes; c__Labyrinthulomycetes; o__Labyrinthulomycetes; f__Thraustochytriaceae; g__f__Thraustochytriaceae</td>
</tr>
</tbody>
</table>
</div>
CC BY-SA 4.0
2022-08-04T07:52:15.713Z
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.
llrs
<r><phylogenetics><taxonomy><parsing>
Parsing text within a taxonomy.csv file
# Parsing text within a taxonomy.csv file

(Asked by: username_0 on 2022-08-02)

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 |

**Tags:** r, phylogenetics, taxonomy, parsing
> **Comments:**
> **username_1**: 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.  

---

## 1 answers

### Answer by username_2 on 2022-08-02. Score: 1

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.

> **Comments:**
> **username_0**: 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.  
> **username_2**: 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.  

---

