<p>Save the following as a python script (e.g., named <code>splitty.py</code>). Make the script executable by doing <code>chmod +x splitty.py</code>. Then, if your <code>MEGAN</code> data are in a file called <code>taxonfile.csv</code>, run <code>splitty.py</code> by doing <code>./splitty.py taxonfile.csv</code>. Get help on the script by doing <code>splitty.py -h</code>.</p>
<pre><code>#!/usr/bin/env python3

&quot;&quot;&quot;
Splitty

Use this to split up a taxonomy table
&quot;&quot;&quot;

def argsparser():
    &quot;&quot;&quot;
    argsparser
    &quot;&quot;&quot;
    import argparse
    parser = argparse.ArgumentParser(prog=&quot;splitty&quot;,
                                    formatter_class=argparse.ArgumentDefaultsHelpFormatter,
                                    description=&quot;Uset splitty to get taxon table.&quot;)
    parser.add_argument(&quot;table&quot;, help=&quot;Taxon table in semicolon separated format.&quot;)
    return parser

def parseline(line_in):
    &quot;&quot;&quot;
    Parse each line_in as return a dict
    &quot;&quot;&quot;
    transl = {&quot;d&quot;: &quot;Domain&quot;,
             &quot;p&quot;: &quot;Phylum&quot;,
             &quot;c&quot;: &quot;Class&quot;,
             &quot;o&quot;: &quot;Order&quot;,
             &quot;f&quot;: &quot;Family&quot;,
             &quot;g&quot;: &quot;Genus&quot;,
             &quot;s&quot;: &quot;Species&quot;}
    return {transl[val[0]]: val[1] for val in [i.split(&quot;__&quot;) for i in line_in]}

def main(args):
    &quot;&quot;&quot;
    The main routine.
    &quot;&quot;&quot;
    import pandas as pd
    df_list = []
    with open(args.table, &quot;r&quot;) as input_handle:
        for line in input_handle.readlines():
            linelist = parseline(list(filter(None, [i.strip() for i in line.rstrip().split(&quot;;&quot;)]))[1:])
            df_list.append(pd.DataFrame(linelist, index=[&quot;Taxon&quot;]))
    print(pd.concat(df_list).to_csv(sep=&quot;\t&quot;, index=False))

if __name__ == &quot;__main__&quot;:
    main(argsparser().parse_args())
</code></pre>

CC BY-SA 4.0
2023-05-26T15:42:47.923Z
Thank you this got things sorted great !
dunc4n
CC BY-SA 4.0
2023-05-09T03:08:42.953Z
2023-05-09T03:08:42.953Z
user3479780
<p>I am analysing some WGS data in MEGAN and would like to do some additional analysis in Python/R</p>
<p>I am having trouble Tidying the Taxonomic data in a format which would be conducive to this. Originally I tried exporting Taxon Path to count and using the Split function in Pandas to break up taxonomy into new columns as this worked well for KEGG Pathways. Due to varying specificity of taxonomic assignments this didn't work.</p>
<p>I've had more luck Exporting Data as a BIOM file format and converting it to TSV resulting in a file with the following column containing all taxonomic info (See below)</p>
<p>Ideally , I'd like to generate columns for; Domain, Phylum , Class , Order, Family and Species by accessing the  features contained in Taxonomy. I'm unaware of any functions which would let me do that.</p>
<p>an idea of what I'm trying to achived is</p>
<div class="s-table-container">
<table class="s-table">
<thead>
<tr>
<th>Domain</th>
<th>Phylum</th>
<th>Class</th>
<th>Order</th>
<th>Family</th>
<th>Species</th>
</tr>
</thead>
<tbody>
<tr>
<td>Bacteria</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>uncultured bacterium</td>
</tr>
<tr>
<td>Bacteria</td>
<td>Bacteroidetes</td>
<td>Bacteroidia</td>
<td>Bacteroidaceae</td>
<td>Bacteroides</td>
<td>Bacteroides acidifaciens</td>
</tr>
</tbody>
</table>
</div>
<p>From</p>
<div class="s-table-container">
<table class="s-table">
<thead>
<tr>
<th>Taxonomy</th>
</tr>
</thead>
<tbody>
<tr>
<td>d__Bacteria; s__uncultured bacterium</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides acidifaciens</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides caccae</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides cellulosilyticus</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides faecis</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides finegoldii</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides fragilis</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides intestinalis</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae;</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae;</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides salyersiae</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides stercoris</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides thetaiotaomicron</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides uniformis</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides xylanisolvens</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides cellulosilyticus CAG:158</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__uncultured Bacteroides sp.</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides sp. 2_2_4</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides sp. 3_1_23</td>
</tr>
<tr>
<td>d__Bacteria; p__Bacteroidetes; c__Bacteroidia; o__Bacteroidales; f__Bacteroidaceae; g__Bacteroides; s__Bacteroides sp. D2</td>
</tr>
</tbody>
</table>
</div>
CC BY-SA 4.0
2023-05-03T15:50:40.150Z
Thanks @dunc4n, `pandas` is a good solution. It's fairly easy. I will not have time in the next two weeks to answer this, particularly coding from an inline table. If you supplied some code that would be helpful, particularly the data you want as a Python list rather then a inline table.e
M__
<r><python><pandas><dataframe>
Tidying MEGAN Taxonomy for Python/R Analysis
# Tidying MEGAN Taxonomy for Python/R Analysis

(Asked by: username_0 on 2023-05-03)

I am analysing some WGS data in MEGAN and would like to do some additional analysis in Python/R

I am having trouble Tidying the Taxonomic data in a format which would be conducive to this. Originally I tried exporting Taxon Path to count and using the Split function in Pandas to break up taxonomy into new columns as this worked well for KEGG Pathways. Due to varying specificity of taxonomic assignments this didn't work.

I've had more luck Exporting Data as a BIOM file format and converting it to TSV resulting in a file with the following column containing all taxonomic info (See below)

Ideally , I'd like to generate columns for; Domain, Phylum , Class , Order, Family and Species by accessing the features contained in Taxonomy. I'm unaware of any functions which would let me do that.

an idea of what I'm trying to achived is

| Domain | Phylum | Class | Order | Family | Species |
| --- | --- | --- | --- | --- | --- |
| Bacteria |  |  |  |  | uncultured bacterium |
| Bacteria | Bacteroidetes | Bacteroidia | Bacteroidaceae | Bacteroides | Bacteroides acidifaciens |

From

| Taxonomy |
| --- |
| d\_\_Bacteria; s\_\_uncultured bacterium |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides acidifaciens |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides caccae |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides cellulosilyticus |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides faecis |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides finegoldii |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides fragilis |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides intestinalis |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides salyersiae |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides stercoris |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides thetaiotaomicron |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides uniformis |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides xylanisolvens |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides cellulosilyticus CAG:158 |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_uncultured Bacteroides sp. |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides sp. 2\_2\_4 |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides sp. 3\_1\_23 |
| d\_\_Bacteria; p\_\_Bacteroidetes; c\_\_Bacteroidia; o\_\_Bacteroidales; f\_\_Bacteroidaceae; g\_\_Bacteroides; s\_\_Bacteroides sp. D2 |

**Tags:** r, python, pandas, dataframe
> **Comments:**
> **username_1**: Thanks @dunc4n, `pandas` is a good solution. It's fairly easy. I will not have time in the next two weeks to answer this, particularly coding from an inline table. If you supplied some code that would be helpful, particularly the data you want as a Python list rather then a inline table.e  

---

## 1 answers

### Answer by username_2 on 2023-05-09. Score: 3 | Accepted answer

Save the following as a python script (e.g., named splitty.py). Make the script executable by doing chmod +x splitty.py. Then, if your MEGAN data are in a file called taxonfile.csv, run splitty.py by doing ./splitty.py taxonfile.csv. Get help on the script by doing splitty.py -h.
#!/usr/bin/env python3

"""
Splitty

Use this to split up a taxonomy table
"""

def argsparser():
    """
    argsparser
    """
    import argparse
    parser = argparse.ArgumentParser(prog="splitty",
                                    formatter_class=argparse.ArgumentDefaultsHelpFormatter,
                                    description="Uset splitty to get taxon table.")
    parser.add_argument("table", help="Taxon table in semicolon separated format.")
    return parser

def parseline(line_in):
    """
    Parse each line_in as return a dict
    """
    transl = {"d": "Domain",
             "p": "Phylum",
             "c": "Class",
             "o": "Order",
             "f": "Family",
             "g": "Genus",
             "s": "Species"}
    return {transl[val[0]]: val[1] for val in [i.split("__") for i in line_in]}

def main(args):
    """
    The main routine.
    """
    import pandas as pd
    df_list = []
    with open(args.table, "r") as input_handle:
        for line in input_handle.readlines():
            linelist = parseline(list(filter(None, [i.strip() for i in line.rstrip().split(";")]))[1:])
            df_list.append(pd.DataFrame(linelist, index=["Taxon"]))
    print(pd.concat(df_list).to_csv(sep="\t", index=False))

if __name__ == "__main__":
    main(argsparser().parse_args())

> **Comments:**
> **username_0**: Thank you this got things sorted great !  

---

