DRAM_Results_01042024.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "3b21820f-896b-433c-8418-d1effee987d6",
   "metadata": {},
   "source": [
    "## Overview of Dram Outputs\n",
    "\n",
    "Looking at Data - what is present? \n",
    "\n",
    "What kind of files do we have?\n",
    "\n",
    "The DRAM output files we'll examine are present here:\n",
    "\n",
    "/storage/lessons/day3_metabolic_lesson/BulkDRAM1\n",
    "\n",
    "Let's take a look in that directory:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "94f9964e-d983-45dd-8916-8050be37ff4d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "annotate.log\t genbank    genes.fna  rrnas.tsv      summary\n",
      "annotations.tsv  genes.faa  genes.gff  scaffolds.fna  trnas.tsv\n"
     ]
    }
   ],
   "source": [
    "!ls /mnt/storage/lessons/day3_metabolic_lesson/BulkDRAM1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e2c5d1a-fa47-4560-ac4a-1562b57166eb",
   "metadata": {},
   "source": [
    "DRAM produced a couple different output files, each with different pieces of useful information."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14135706-1bb6-45c0-a7c2-cabbb9268f2b",
   "metadata": {},
   "source": [
    "Let's go through product.tsv, and the associated product.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1adb3bcf-1ba2-4396-950d-241a706a508f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>genome</th>\n",
       "      <th>3-Hydroxypropionate bi-cycle</th>\n",
       "      <th>Acetyl-CoA pathway, CO2 =&gt; acetyl-CoA</th>\n",
       "      <th>Citrate cycle (TCA cycle, Krebs cycle)</th>\n",
       "      <th>Dicarboxylate-hydroxybutyrate cycle</th>\n",
       "      <th>Entner-Doudoroff pathway, glucose-6P =&gt; glyceraldehyde-3P + pyruvate</th>\n",
       "      <th>Glycolysis (Embden-Meyerhof pathway), glucose =&gt; pyruvate</th>\n",
       "      <th>Glyoxylate cycle</th>\n",
       "      <th>Hydroxypropionate-hydroxybutylate cycle</th>\n",
       "      <th>Methanogenesis, CO2 =&gt; methane</th>\n",
       "      <th>...</th>\n",
       "      <th>SCFA and alcohol conversions: acetate, pt 3</th>\n",
       "      <th>SCFA and alcohol conversions: lactate D</th>\n",
       "      <th>SCFA and alcohol conversions: lactate L</th>\n",
       "      <th>SCFA and alcohol conversions: pyruvate =&gt; acetyl CoA v1</th>\n",
       "      <th>SCFA and alcohol conversions: pyruvate =&gt; acetyl CoA v2</th>\n",
       "      <th>SCFA and alcohol conversions: pyruvate =&gt; acetylCoA f+ formate v3</th>\n",
       "      <th>Sulfur metabolism: Thiosulfate oxidation by SOX complex, thiosulfate =&gt; sulfate</th>\n",
       "      <th>Sulfur metabolism: dissimilatory sulfate reduction (and oxidation) sulfate =&gt; sulfide</th>\n",
       "      <th>Sulfur metabolism: tetrathionate =&gt; thiosulfate</th>\n",
       "      <th>Sulfur metabolism: thiosulfate =&gt; sulfite</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>final_contigs_AG-910-A02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.250</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>final_contigs_AG-910-A04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.250</td>\n",
       "      <td>0.153846</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.222222</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.071429</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>final_contigs_AG-910-A06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.875</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>final_contigs_AG-910-A10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.625</td>\n",
       "      <td>0.153846</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.777778</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 99 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     genome  3-Hydroxypropionate bi-cycle  \\\n",
       "0  final_contigs_AG-910-A02                           0.0   \n",
       "1  final_contigs_AG-910-A04                           0.0   \n",
       "2  final_contigs_AG-910-A06                           0.0   \n",
       "3  final_contigs_AG-910-A10                           0.0   \n",
       "\n",
       "   Acetyl-CoA pathway, CO2 => acetyl-CoA  \\\n",
       "0                                    0.0   \n",
       "1                                    0.0   \n",
       "2                                    0.0   \n",
       "3                                    0.0   \n",
       "\n",
       "   Citrate cycle (TCA cycle, Krebs cycle)  \\\n",
       "0                                   0.250   \n",
       "1                                   0.250   \n",
       "2                                   0.875   \n",
       "3                                   0.625   \n",
       "\n",
       "   Dicarboxylate-hydroxybutyrate cycle  \\\n",
       "0                             0.000000   \n",
       "1                             0.153846   \n",
       "2                             0.307692   \n",
       "3                             0.153846   \n",
       "\n",
       "   Entner-Doudoroff pathway, glucose-6P => glyceraldehyde-3P + pyruvate  \\\n",
       "0                                               0.00                      \n",
       "1                                               0.25                      \n",
       "2                                               0.00                      \n",
       "3                                               0.00                      \n",
       "\n",
       "   Glycolysis (Embden-Meyerhof pathway), glucose => pyruvate  \\\n",
       "0                                           0.333333           \n",
       "1                                           0.222222           \n",
       "2                                           0.555556           \n",
       "3                                           0.777778           \n",
       "\n",
       "   Glyoxylate cycle  Hydroxypropionate-hydroxybutylate cycle  \\\n",
       "0               0.6                                 0.000000   \n",
       "1               0.0                                 0.071429   \n",
       "2               0.8                                 0.000000   \n",
       "3               0.6                                 0.000000   \n",
       "\n",
       "   Methanogenesis, CO2 => methane  ...  \\\n",
       "0                             0.0  ...   \n",
       "1                             0.0  ...   \n",
       "2                             0.0  ...   \n",
       "3                             0.0  ...   \n",
       "\n",
       "   SCFA and alcohol conversions: acetate, pt 3  \\\n",
       "0                                        False   \n",
       "1                                        False   \n",
       "2                                        False   \n",
       "3                                        False   \n",
       "\n",
       "   SCFA and alcohol conversions: lactate D  \\\n",
       "0                                    False   \n",
       "1                                    False   \n",
       "2                                    False   \n",
       "3                                    False   \n",
       "\n",
       "   SCFA and alcohol conversions: lactate L  \\\n",
       "0                                    False   \n",
       "1                                    False   \n",
       "2                                    False   \n",
       "3                                    False   \n",
       "\n",
       "   SCFA and alcohol conversions: pyruvate => acetyl CoA v1  \\\n",
       "0                                              False         \n",
       "1                                              False         \n",
       "2                                               True         \n",
       "3                                               True         \n",
       "\n",
       "   SCFA and alcohol conversions: pyruvate => acetyl CoA v2  \\\n",
       "0                                              False         \n",
       "1                                               True         \n",
       "2                                              False         \n",
       "3                                              False         \n",
       "\n",
       "   SCFA and alcohol conversions: pyruvate => acetylCoA f+ formate v3  \\\n",
       "0                                              False                   \n",
       "1                                              False                   \n",
       "2                                              False                   \n",
       "3                                              False                   \n",
       "\n",
       "   Sulfur metabolism: Thiosulfate oxidation by SOX complex, thiosulfate => sulfate  \\\n",
       "0                                              False                                 \n",
       "1                                              False                                 \n",
       "2                                              False                                 \n",
       "3                                              False                                 \n",
       "\n",
       "   Sulfur metabolism: dissimilatory sulfate reduction (and oxidation) sulfate => sulfide  \\\n",
       "0                                              False                                       \n",
       "1                                              False                                       \n",
       "2                                              False                                       \n",
       "3                                              False                                       \n",
       "\n",
       "   Sulfur metabolism: tetrathionate => thiosulfate  \\\n",
       "0                                            False   \n",
       "1                                            False   \n",
       "2                                            False   \n",
       "3                                            False   \n",
       "\n",
       "   Sulfur metabolism: thiosulfate => sulfite  \n",
       "0                                      False  \n",
       "1                                      False  \n",
       "2                                      False  \n",
       "3                                      False  \n",
       "\n",
       "[4 rows x 99 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import openpyxl\n",
    "\n",
    "#read in data, it's a .tsv file so delimiter is set\n",
    "data = pd.read_csv('/mnt/storage/lessons/day3_metabolic_lesson/BulkDRAM1/summary/product.tsv', delimiter='\\t')\n",
    "#display the first 10 rows\n",
    "data.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c9b45aa-40c4-4f40-bc18-4e52fc17c80b",
   "metadata": {},
   "source": [
    "Here's the output table.  It has a LOT of columns that correspond to different metabolic pathways that we might find in a microbial genome. Let's look at a full list of the columns present:    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6176b1c5-1d11-40bc-a28f-e5984cd1fb37",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['genome', '3-Hydroxypropionate bi-cycle',\n",
      "       'Acetyl-CoA pathway, CO2 => acetyl-CoA',\n",
      "       'Citrate cycle (TCA cycle, Krebs cycle)',\n",
      "       'Dicarboxylate-hydroxybutyrate cycle',\n",
      "       'Entner-Doudoroff pathway, glucose-6P => glyceraldehyde-3P + pyruvate',\n",
      "       'Glycolysis (Embden-Meyerhof pathway), glucose => pyruvate',\n",
      "       'Glyoxylate cycle', 'Hydroxypropionate-hydroxybutylate cycle',\n",
      "       'Methanogenesis, CO2 => methane',\n",
      "       'Pentose phosphate pathway (Pentose phosphate cycle)',\n",
      "       'Reductive acetyl-CoA pathway (Wood-Ljungdahl pathway)',\n",
      "       'Reductive citrate cycle (Arnon-Buchanan cycle)',\n",
      "       'Reductive pentose phosphate cycle (Calvin cycle)',\n",
      "       'Complex I: NAD(P)H:quinone oxidoreductase, chloroplasts and cyanobacteria',\n",
      "       'Complex I: NADH dehydrogenase (ubiquinone) 1 alpha subcomplex',\n",
      "       'Complex I: NADH:quinone oxidoreductase, prokaryotes',\n",
      "       'Complex II: Fumarate reductase, prokaryotes',\n",
      "       'Complex II: Succinate dehydrogenase (ubiquinone)',\n",
      "       'Complex II: Succinate dehydrogenase, prokaryotes',\n",
      "       'Complex III: Cytochrome bc1 complex',\n",
      "       'Complex III: Cytochrome bc1 complex respiratory unit',\n",
      "       'Complex III: Cytochrome bd ubiquinol oxidase',\n",
      "       'Complex IV High affinity: Cytochrome bd ubiquinol oxidase',\n",
      "       'Complex IV High affinity: Cytochrome c oxidase, cbb3-type',\n",
      "       'Complex IV Low affinity: Cytochrome aa3-600 menaquinol oxidase',\n",
      "       'Complex IV Low affinity: Cytochrome c oxidase',\n",
      "       'Complex IV Low affinity: Cytochrome c oxidase, prokaryotes',\n",
      "       'Complex IV Low affinity: Cytochrome o ubiquinol oxidase',\n",
      "       'Complex V: F-type ATPase, eukaryotes',\n",
      "       'Complex V: F-type ATPase, prokaryotes and chloroplasts',\n",
      "       'Complex V: V-type ATPase, eukaryotes',\n",
      "       'Complex V: V/A-type ATPase, prokaryotes', 'CAZy: Alpha-galactans',\n",
      "       'CAZy: Alpha-mannan', 'CAZy: Amorphous Cellulose', 'CAZy: Arabinan',\n",
      "       'CAZy: Arabinose cleavage', 'CAZy: Beta-galactan (pectic galactan)',\n",
      "       'CAZy: Beta-mannan', 'CAZy: Chitin', 'CAZy: Crystalline Cellulose',\n",
      "       'CAZy: Fucose Cleavage', 'CAZy: Mixed-Linkage glucans', 'CAZy: Mucin',\n",
      "       'CAZy: Pectin', 'CAZy: Polyphenolics', 'CAZy: Rhamnose cleavage',\n",
      "       'CAZy: Starch', 'CAZy: Sulf-Polysachharides', 'CAZy: Xylans',\n",
      "       'CAZy: Xyloglucan',\n",
      "       'Methanogenesis and methanotrophy: Key functional gene',\n",
      "       'Methanogenesis and methanotrophy: acetate => methane, pt 1',\n",
      "       'Methanogenesis and methanotrophy: acetate => methane, pt 2',\n",
      "       'Methanogenesis and methanotrophy: acetate => methane, pt 3',\n",
      "       'Methanogenesis and methanotrophy: dimethylamine => monomethylamine',\n",
      "       'Methanogenesis and methanotrophy: methane => methanol, with oxygen (mmo)',\n",
      "       'Methanogenesis and methanotrophy: methane => methanol, with oxygen (pmo)',\n",
      "       'Methanogenesis and methanotrophy: methanol => methane',\n",
      "       'Methanogenesis and methanotrophy: monomethylamine => ammonia',\n",
      "       'Methanogenesis and methanotrophy: putative but not defining CO2 => methane',\n",
      "       'Methanogenesis and methanotrophy: trimethylamine => dimethylamine',\n",
      "       'Nitrogen metabolism: Bacterial (aerobic-specific) ammonia oxidation',\n",
      "       'Nitrogen metabolism: Bacterial (anaerobic-specific) ammonia oxidation',\n",
      "       'Nitrogen metabolism: Bacterial/Archaeal ammonia oxidation',\n",
      "       'Nitrogen metabolism: Dissimilatory nitrite reduction to ammonia (DNRA)',\n",
      "       'Nitrogen metabolism: Nitrogen fixation altennative',\n",
      "       'Nitrogen metabolism: ammonia => nitrite',\n",
      "       'Nitrogen metabolism: nitrate => nitrite',\n",
      "       'Nitrogen metabolism: nitric oxide => nitrous oxide',\n",
      "       'Nitrogen metabolism: nitrite => nitric oxide',\n",
      "       'Nitrogen metabolism: nitrite=> nitrate',\n",
      "       'Nitrogen metabolism: nitrogen => ammonia',\n",
      "       'Nitrogen metabolism: nitrous oxide => nitrogen',\n",
      "       'Other Reductases: TMAO reductase',\n",
      "       'Other Reductases: arsenate reduction, pt 1',\n",
      "       'Other Reductases: arsenate reduction, pt 2',\n",
      "       'Other Reductases: mercury reduction',\n",
      "       'Other Reductases: selenate/Chlorate reduction',\n",
      "       'Photosynthesis: Photosystem I', 'Photosynthesis: Photosystem II',\n",
      "       'SCFA and alcohol conversions: Alcohol production',\n",
      "       'SCFA and alcohol conversions: Butyrate, pt 1',\n",
      "       'SCFA and alcohol conversions: Butyrate, pt 2',\n",
      "       'SCFA and alcohol conversions: Propionate, pt 1',\n",
      "       'SCFA and alcohol conversions: Propionate, pt 2',\n",
      "       'SCFA and alcohol conversions: acetate, pt 1',\n",
      "       'SCFA and alcohol conversions: acetate, pt 2',\n",
      "       'SCFA and alcohol conversions: acetate, pt 3',\n",
      "       'SCFA and alcohol conversions: lactate D',\n",
      "       'SCFA and alcohol conversions: lactate L',\n",
      "       'SCFA and alcohol conversions: pyruvate => acetyl CoA v1',\n",
      "       'SCFA and alcohol conversions: pyruvate => acetyl CoA v2',\n",
      "       'SCFA and alcohol conversions: pyruvate => acetylCoA f+ formate v3',\n",
      "       'Sulfur metabolism: Thiosulfate oxidation by SOX complex, thiosulfate => sulfate',\n",
      "       'Sulfur metabolism: dissimilatory sulfate reduction (and oxidation) sulfate => sulfide',\n",
      "       'Sulfur metabolism: tetrathionate => thiosulfate',\n",
      "       'Sulfur metabolism: thiosulfate => sulfite'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(data.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39e23981-79b8-4f8e-981f-87b4416721dc",
   "metadata": {},
   "source": [
    "We can transform this dataframe so that columns are SAGs and rows are metabolic pathways like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d7345f6d-eb10-4dbb-a0e3-f5e17b79e1ca",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>genome</th>\n",
       "      <th>final_contigs_AG-910-A02</th>\n",
       "      <th>final_contigs_AG-910-A04</th>\n",
       "      <th>final_contigs_AG-910-A06</th>\n",
       "      <th>final_contigs_AG-910-A10</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3-Hydroxypropionate bi-cycle</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Acetyl-CoA pathway, CO2 =&gt; acetyl-CoA</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Citrate cycle (TCA cycle, Krebs cycle)</th>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.875</td>\n",
       "      <td>0.625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dicarboxylate-hydroxybutyrate cycle</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.153846</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>0.153846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Entner-Doudoroff pathway, glucose-6P =&gt; glyceraldehyde-3P + pyruvate</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Glycolysis (Embden-Meyerhof pathway), glucose =&gt; pyruvate</th>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.222222</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>0.777778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Glyoxylate cycle</th>\n",
       "      <td>0.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hydroxypropionate-hydroxybutylate cycle</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.071429</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Methanogenesis, CO2 =&gt; methane</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pentose phosphate pathway (Pentose phosphate cycle)</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.571429</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.571429</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "genome                                             final_contigs_AG-910-A02  \\\n",
       "3-Hydroxypropionate bi-cycle                                            0.0   \n",
       "Acetyl-CoA pathway, CO2 => acetyl-CoA                                   0.0   \n",
       "Citrate cycle (TCA cycle, Krebs cycle)                                 0.25   \n",
       "Dicarboxylate-hydroxybutyrate cycle                                     0.0   \n",
       "Entner-Doudoroff pathway, glucose-6P => glycera...                      0.0   \n",
       "Glycolysis (Embden-Meyerhof pathway), glucose =...                 0.333333   \n",
       "Glyoxylate cycle                                                        0.6   \n",
       "Hydroxypropionate-hydroxybutylate cycle                                 0.0   \n",
       "Methanogenesis, CO2 => methane                                          0.0   \n",
       "Pentose phosphate pathway (Pentose phosphate cy...                      0.0   \n",
       "\n",
       "genome                                             final_contigs_AG-910-A04  \\\n",
       "3-Hydroxypropionate bi-cycle                                            0.0   \n",
       "Acetyl-CoA pathway, CO2 => acetyl-CoA                                   0.0   \n",
       "Citrate cycle (TCA cycle, Krebs cycle)                                 0.25   \n",
       "Dicarboxylate-hydroxybutyrate cycle                                0.153846   \n",
       "Entner-Doudoroff pathway, glucose-6P => glycera...                     0.25   \n",
       "Glycolysis (Embden-Meyerhof pathway), glucose =...                 0.222222   \n",
       "Glyoxylate cycle                                                        0.0   \n",
       "Hydroxypropionate-hydroxybutylate cycle                            0.071429   \n",
       "Methanogenesis, CO2 => methane                                          0.0   \n",
       "Pentose phosphate pathway (Pentose phosphate cy...                 0.571429   \n",
       "\n",
       "genome                                             final_contigs_AG-910-A06  \\\n",
       "3-Hydroxypropionate bi-cycle                                            0.0   \n",
       "Acetyl-CoA pathway, CO2 => acetyl-CoA                                   0.0   \n",
       "Citrate cycle (TCA cycle, Krebs cycle)                                0.875   \n",
       "Dicarboxylate-hydroxybutyrate cycle                                0.307692   \n",
       "Entner-Doudoroff pathway, glucose-6P => glycera...                      0.0   \n",
       "Glycolysis (Embden-Meyerhof pathway), glucose =...                 0.555556   \n",
       "Glyoxylate cycle                                                        0.8   \n",
       "Hydroxypropionate-hydroxybutylate cycle                                 0.0   \n",
       "Methanogenesis, CO2 => methane                                          0.0   \n",
       "Pentose phosphate pathway (Pentose phosphate cy...                 0.285714   \n",
       "\n",
       "genome                                             final_contigs_AG-910-A10  \n",
       "3-Hydroxypropionate bi-cycle                                            0.0  \n",
       "Acetyl-CoA pathway, CO2 => acetyl-CoA                                   0.0  \n",
       "Citrate cycle (TCA cycle, Krebs cycle)                                0.625  \n",
       "Dicarboxylate-hydroxybutyrate cycle                                0.153846  \n",
       "Entner-Doudoroff pathway, glucose-6P => glycera...                      0.0  \n",
       "Glycolysis (Embden-Meyerhof pathway), glucose =...                 0.777778  \n",
       "Glyoxylate cycle                                                        0.6  \n",
       "Hydroxypropionate-hydroxybutylate cycle                                 0.0  \n",
       "Methanogenesis, CO2 => methane                                          0.0  \n",
       "Pentose phosphate pathway (Pentose phosphate cy...                 0.571429  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.set_index('genome').T.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f165a83-cd81-41e2-abd7-7fa8742e65f6",
   "metadata": {},
   "source": [
    "### Metabolism summary\n",
    "\n",
    "Use this output file when you want to get specific.  \n",
    "\n",
    "If you have doubts over an annotation, completeness of a pathway ect (as seen in product.html, or read into your data in product.tsv), this is place to check. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4c8c8dd9-2a11-4e0b-88ae-bcf6ef791838",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gene_id</th>\n",
       "      <th>gene_description</th>\n",
       "      <th>module</th>\n",
       "      <th>header</th>\n",
       "      <th>subheader</th>\n",
       "      <th>final_contigs_AG-910-A02</th>\n",
       "      <th>final_contigs_AG-910-A06</th>\n",
       "      <th>final_contigs_AG-910-A04</th>\n",
       "      <th>final_contigs_AG-910-A10</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K08691</td>\n",
       "      <td>malyl-CoA/(S)-citramalyl-CoA lyase [EC:4.1.3.2...</td>\n",
       "      <td>3-Hydroxypropionate bi-cycle</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K09709</td>\n",
       "      <td>3-methylfumaryl-CoA hydratase [EC:4.2.1.153] [...</td>\n",
       "      <td>3-Hydroxypropionate bi-cycle</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K14449</td>\n",
       "      <td>2-methylfumaryl-CoA hydratase [EC:4.2.1.148] [...</td>\n",
       "      <td>3-Hydroxypropionate bi-cycle</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K14470</td>\n",
       "      <td>2-methylfumaryl-CoA isomerase [EC:5.4.1.3] [RN...</td>\n",
       "      <td>3-Hydroxypropionate bi-cycle</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>K00196</td>\n",
       "      <td>carbon-monoxide dehydrogenase iron sulfur subunit</td>\n",
       "      <td>Acetyl-CoA pathway</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>K03518</td>\n",
       "      <td>carbon-monoxide dehydrogenase small subunit</td>\n",
       "      <td>Acetyl-CoA pathway</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>K03519</td>\n",
       "      <td>carbon-monoxide dehydrogenase medium subunit</td>\n",
       "      <td>Acetyl-CoA pathway</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>K03520</td>\n",
       "      <td>carbon-monoxide dehydrogenase large subunit</td>\n",
       "      <td>Acetyl-CoA pathway</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>K00192</td>\n",
       "      <td>cdhA; anaerobic carbon-monoxide dehydrogenase,...</td>\n",
       "      <td>Acetyl-CoA pathway, CO2 =&gt; acetyl-CoA</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>K00193</td>\n",
       "      <td>cdhC; acetyl-CoA decarbonylase/synthase, CODH/...</td>\n",
       "      <td>Acetyl-CoA pathway, CO2 =&gt; acetyl-CoA</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  gene_id                                   gene_description  \\\n",
       "0  K08691  malyl-CoA/(S)-citramalyl-CoA lyase [EC:4.1.3.2...   \n",
       "1  K09709  3-methylfumaryl-CoA hydratase [EC:4.2.1.153] [...   \n",
       "2  K14449  2-methylfumaryl-CoA hydratase [EC:4.2.1.148] [...   \n",
       "3  K14470  2-methylfumaryl-CoA isomerase [EC:5.4.1.3] [RN...   \n",
       "4  K00196  carbon-monoxide dehydrogenase iron sulfur subunit   \n",
       "5  K03518        carbon-monoxide dehydrogenase small subunit   \n",
       "6  K03519       carbon-monoxide dehydrogenase medium subunit   \n",
       "7  K03520        carbon-monoxide dehydrogenase large subunit   \n",
       "8  K00192  cdhA; anaerobic carbon-monoxide dehydrogenase,...   \n",
       "9  K00193  cdhC; acetyl-CoA decarbonylase/synthase, CODH/...   \n",
       "\n",
       "                                  module header subheader  \\\n",
       "0           3-Hydroxypropionate bi-cycle     C1       NaN   \n",
       "1           3-Hydroxypropionate bi-cycle     C1       NaN   \n",
       "2           3-Hydroxypropionate bi-cycle     C1       NaN   \n",
       "3           3-Hydroxypropionate bi-cycle     C1       NaN   \n",
       "4                     Acetyl-CoA pathway     C1       NaN   \n",
       "5                     Acetyl-CoA pathway     C1       NaN   \n",
       "6                     Acetyl-CoA pathway     C1       NaN   \n",
       "7                     Acetyl-CoA pathway     C1       NaN   \n",
       "8  Acetyl-CoA pathway, CO2 => acetyl-CoA     C1       NaN   \n",
       "9  Acetyl-CoA pathway, CO2 => acetyl-CoA     C1       NaN   \n",
       "\n",
       "   final_contigs_AG-910-A02  final_contigs_AG-910-A06  \\\n",
       "0                       0.0                       0.0   \n",
       "1                       0.0                       0.0   \n",
       "2                       0.0                       0.0   \n",
       "3                       0.0                       0.0   \n",
       "4                       0.0                       0.0   \n",
       "5                       0.0                       0.0   \n",
       "6                       0.0                       0.0   \n",
       "7                       0.0                       0.0   \n",
       "8                       0.0                       0.0   \n",
       "9                       0.0                       0.0   \n",
       "\n",
       "   final_contigs_AG-910-A04  final_contigs_AG-910-A10  \n",
       "0                       0.0                       0.0  \n",
       "1                       0.0                       0.0  \n",
       "2                       0.0                       0.0  \n",
       "3                       0.0                       0.0  \n",
       "4                       0.0                       0.0  \n",
       "5                       0.0                       0.0  \n",
       "6                       0.0                       0.0  \n",
       "7                       0.0                       0.0  \n",
       "8                       0.0                       0.0  \n",
       "9                       0.0                       0.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_energy = pd.read_excel('/mnt/storage/lessons/day3_metabolic_lesson/BulkDRAM1/summary/metabolism_summary.xlsx', sheet_name='Energy', engine=None)\n",
    "\n",
    "df_energy.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30b5db51-b8f2-4658-82e7-6ee3a0c142cc",
   "metadata": {},
   "source": [
    "Let's check out what modules we see in the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a5db63b6-da7c-4a58-963b-5fadfe7d2893",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('hydrogenase', 75),\n",
       " ('Reductive citrate cycle (Arnon-Buchanan cycle)', 43),\n",
       " ('Methanogenesis, CO2 => methane', 37),\n",
       " ('Methanogenesis, acetate => methane', 31),\n",
       " ('Dicarboxylate-hydroxybutyrate cycle', 24),\n",
       " ('Methanogenesis, methylamine/dimethylamine/trimethylamine => methane', 23),\n",
       " ('F-type ATPase, eukaryotes', 20),\n",
       " ('Methanogenesis, methanol => methane', 19),\n",
       " ('Hydroxypropionate-hydroxybutylate cycle', 18),\n",
       " ('Reductive pentose phosphate cycle (Calvin cycle)', 18),\n",
       " ('Cytochrome c oxidase', 18),\n",
       " ('NADH:quinone oxidoreductase, prokaryotes', 17),\n",
       " ('NAD(P)H:quinone oxidoreductase, chloroplasts and cyanobacteria', 14),\n",
       " ('NADH dehydrogenase (ubiquinone) 1 alpha subcomplex', 14),\n",
       " ('V-type ATPase, eukaryotes', 14),\n",
       " ('NADH dehydrogenase (ubiquinone) 1 beta subcomplex', 13),\n",
       " ('Methane oxidation, methanotroph, methane => formaldehyde', 12),\n",
       " ('Reductive pentose phosphate cycle, glyceraldehyde-3P => ribulose-5P', 11),\n",
       " ('NADH dehydrogenase (ubiquinone) Fe-S protein/flavoprotein complex, mitochondria',\n",
       "  11),\n",
       " ('Denitrification, nitrate => nitrogen', 11),\n",
       " ('Cytochrome bc1 complex', 11),\n",
       " ('Cytochrome b6f complex', 11),\n",
       " ('Reductive acetyl-CoA pathway (Wood-Ljungdahl pathway)', 10),\n",
       " ('nitrite + ammonia => nitrogen', 10),\n",
       " ('Cytochrome bc1 complex respiratory unit', 10),\n",
       " ('Assimilatory sulfate reduction, sulfate => H2S', 10),\n",
       " ('V/A-type ATPase, prokaryotes', 9),\n",
       " ('Dissimilatory nitrate reduction, nitrate => ammonia', 9),\n",
       " ('F-type ATPase, prokaryotes and chloroplasts', 8),\n",
       " ('Malonate semialdehyde pathway, propanoyl-CoA => acetyl-CoA', 8),\n",
       " ('Nitrification, ammonia => nitrite', 8),\n",
       " ('Nitrogen fixation, nitrogen => ammonia', 8),\n",
       " ('Reductive pentose phosphate cycle, ribulose-5P => glyceraldehyde-3P', 7),\n",
       " ('Multicomponent Na+/H+ antiporter', 7),\n",
       " ('NADH:ubiquinone oxidoreductase, mitochondria', 7),\n",
       " ('Dissimilatory sulfate reduction, sulfate => H2S', 7),\n",
       " ('Thiosulfate oxidation by SOX complex, thiosulfate => sulfate', 7),\n",
       " ('Na+-transporting NADH:ubiquinone oxidoreductase-', 6),\n",
       " ('RNF complex', 6),\n",
       " ('Assimilatory nitrate reduction, nitrate => ammonia', 6),\n",
       " ('Complete nitrification, comammox, ammonia => nitrite => nitrate', 6),\n",
       " ('Photosystem I', 6),\n",
       " ('Photosystem II', 6),\n",
       " ('Acetyl-CoA pathway, CO2 => acetyl-CoA', 5),\n",
       " ('Coenzyme M biosynthesis', 5),\n",
       " ('Cytochrome c oxidase, cbb3-type', 5),\n",
       " ('Cytochrome c oxidase, prokaryotes', 5),\n",
       " ('Photorespiration', 5),\n",
       " ('3-Hydroxypropionate bi-cycle', 4),\n",
       " ('Acetyl-CoA pathway', 4),\n",
       " ('Fumarate reductase, prokaryotes', 4),\n",
       " ('Succinate dehydrogenase (ubiquinone)', 4),\n",
       " ('Succinate dehydrogenase, prokaryotes', 4),\n",
       " ('Cytochrome aa3-600 menaquinol oxidase', 4),\n",
       " ('Cytochrome bd ubiquinol oxidase', 4),\n",
       " ('Cytochrome o ubiquinol oxidase', 4),\n",
       " ('Anoxygenic photosystem I', 4),\n",
       " (nan, 3),\n",
       " ('F420 biosynthesis', 2),\n",
       " ('Arsenate', 2),\n",
       " ('Anoxygenic photosystem II', 2),\n",
       " ('TMAO', 1),\n",
       " ('Methanogenesis, methanethiol => methane', 1),\n",
       " ('Mercury', 1),\n",
       " ('Selenate/Chlorate', 1),\n",
       " ('Nitrate assimilation', 1),\n",
       " ('Cytochrome d ubiquinol oxidase', 1)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from collections import Counter\n",
    "\n",
    "Counter(df_energy['module']).most_common()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4d51405-1840-491e-8788-bcbeb5e7da36",
   "metadata": {},
   "source": [
    "What about \"Header\"?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8bb8d8b4-44fd-4812-b299-65a6700f3aa4",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Electron transport Chain', 166),\n",
       " ('C1', 164),\n",
       " ('C1-methane', 111),\n",
       " ('Hydrogenases', 75),\n",
       " ('Oxygen', 62),\n",
       " ('Nitrogen', 59),\n",
       " ('Photosynthesis', 34),\n",
       " ('Sulfur', 27),\n",
       " ('Metal Reduction', 4)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Counter(df_energy['header']).most_common()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a00e0db-af1c-4472-b63d-f231b523e126",
   "metadata": {},
   "source": [
    "Let's zero in on a specific metabolism and look at its presence across SAGs.  Why don't we focus on Oxygen metabolism, and filter out all genes not found within any of the SAGs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "fb195b1b-4f3e-41fc-9767-7b93838c3d8a",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gene_id</th>\n",
       "      <th>gene_description</th>\n",
       "      <th>module</th>\n",
       "      <th>header</th>\n",
       "      <th>subheader</th>\n",
       "      <th>final_contigs_AG-910-A02</th>\n",
       "      <th>final_contigs_AG-910-A06</th>\n",
       "      <th>final_contigs_AG-910-A04</th>\n",
       "      <th>final_contigs_AG-910-A10</th>\n",
       "      <th>total_genes_found</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K08691</td>\n",
       "      <td>malyl-CoA/(S)-citramalyl-CoA lyase [EC:4.1.3.2...</td>\n",
       "      <td>3-Hydroxypropionate bi-cycle</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K09709</td>\n",
       "      <td>3-methylfumaryl-CoA hydratase [EC:4.2.1.153] [...</td>\n",
       "      <td>3-Hydroxypropionate bi-cycle</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K14449</td>\n",
       "      <td>2-methylfumaryl-CoA hydratase [EC:4.2.1.148] [...</td>\n",
       "      <td>3-Hydroxypropionate bi-cycle</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K14470</td>\n",
       "      <td>2-methylfumaryl-CoA isomerase [EC:5.4.1.3] [RN...</td>\n",
       "      <td>3-Hydroxypropionate bi-cycle</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>K00196</td>\n",
       "      <td>carbon-monoxide dehydrogenase iron sulfur subunit</td>\n",
       "      <td>Acetyl-CoA pathway</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>K03518</td>\n",
       "      <td>carbon-monoxide dehydrogenase small subunit</td>\n",
       "      <td>Acetyl-CoA pathway</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>K03519</td>\n",
       "      <td>carbon-monoxide dehydrogenase medium subunit</td>\n",
       "      <td>Acetyl-CoA pathway</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>K03520</td>\n",
       "      <td>carbon-monoxide dehydrogenase large subunit</td>\n",
       "      <td>Acetyl-CoA pathway</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>K00192</td>\n",
       "      <td>cdhA; anaerobic carbon-monoxide dehydrogenase,...</td>\n",
       "      <td>Acetyl-CoA pathway, CO2 =&gt; acetyl-CoA</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>K00193</td>\n",
       "      <td>cdhC; acetyl-CoA decarbonylase/synthase, CODH/...</td>\n",
       "      <td>Acetyl-CoA pathway, CO2 =&gt; acetyl-CoA</td>\n",
       "      <td>C1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  gene_id                                   gene_description  \\\n",
       "0  K08691  malyl-CoA/(S)-citramalyl-CoA lyase [EC:4.1.3.2...   \n",
       "1  K09709  3-methylfumaryl-CoA hydratase [EC:4.2.1.153] [...   \n",
       "2  K14449  2-methylfumaryl-CoA hydratase [EC:4.2.1.148] [...   \n",
       "3  K14470  2-methylfumaryl-CoA isomerase [EC:5.4.1.3] [RN...   \n",
       "4  K00196  carbon-monoxide dehydrogenase iron sulfur subunit   \n",
       "5  K03518        carbon-monoxide dehydrogenase small subunit   \n",
       "6  K03519       carbon-monoxide dehydrogenase medium subunit   \n",
       "7  K03520        carbon-monoxide dehydrogenase large subunit   \n",
       "8  K00192  cdhA; anaerobic carbon-monoxide dehydrogenase,...   \n",
       "9  K00193  cdhC; acetyl-CoA decarbonylase/synthase, CODH/...   \n",
       "\n",
       "                                  module header subheader  \\\n",
       "0           3-Hydroxypropionate bi-cycle     C1       NaN   \n",
       "1           3-Hydroxypropionate bi-cycle     C1       NaN   \n",
       "2           3-Hydroxypropionate bi-cycle     C1       NaN   \n",
       "3           3-Hydroxypropionate bi-cycle     C1       NaN   \n",
       "4                     Acetyl-CoA pathway     C1       NaN   \n",
       "5                     Acetyl-CoA pathway     C1       NaN   \n",
       "6                     Acetyl-CoA pathway     C1       NaN   \n",
       "7                     Acetyl-CoA pathway     C1       NaN   \n",
       "8  Acetyl-CoA pathway, CO2 => acetyl-CoA     C1       NaN   \n",
       "9  Acetyl-CoA pathway, CO2 => acetyl-CoA     C1       NaN   \n",
       "\n",
       "   final_contigs_AG-910-A02  final_contigs_AG-910-A06  \\\n",
       "0                       0.0                       0.0   \n",
       "1                       0.0                       0.0   \n",
       "2                       0.0                       0.0   \n",
       "3                       0.0                       0.0   \n",
       "4                       0.0                       0.0   \n",
       "5                       0.0                       0.0   \n",
       "6                       0.0                       0.0   \n",
       "7                       0.0                       0.0   \n",
       "8                       0.0                       0.0   \n",
       "9                       0.0                       0.0   \n",
       "\n",
       "   final_contigs_AG-910-A04  final_contigs_AG-910-A10  total_genes_found  \n",
       "0                       0.0                       0.0                0.0  \n",
       "1                       0.0                       0.0                0.0  \n",
       "2                       0.0                       0.0                0.0  \n",
       "3                       0.0                       0.0                0.0  \n",
       "4                       0.0                       0.0                0.0  \n",
       "5                       0.0                       0.0                0.0  \n",
       "6                       0.0                       0.0                0.0  \n",
       "7                       0.0                       0.0                0.0  \n",
       "8                       0.0                       0.0                0.0  \n",
       "9                       0.0                       0.0                0.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# identifying all columns representing SAGs\n",
    "sag_columns = [i for i in df_energy.columns if 'AM' in i]\n",
    "\n",
    "# adding a column that sums the number of genes identified in each of the SAG columns\n",
    "df_energy['total_genes_found'] = df_energy[sag_columns].sum(axis = 1)\n",
    "\n",
    "# check columns\n",
    "df_energy.head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1a13c604-b138-4d77-85f3-6ca012cb1581",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gene_id</th>\n",
       "      <th>gene_description</th>\n",
       "      <th>module</th>\n",
       "      <th>header</th>\n",
       "      <th>subheader</th>\n",
       "      <th>final_contigs_AG-910-A02</th>\n",
       "      <th>final_contigs_AG-910-A06</th>\n",
       "      <th>final_contigs_AG-910-A04</th>\n",
       "      <th>final_contigs_AG-910-A10</th>\n",
       "      <th>total_genes_found</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [gene_id, gene_description, module, header, subheader, final_contigs_AG-910-A02, final_contigs_AG-910-A06, final_contigs_AG-910-A04, final_contigs_AG-910-A10, total_genes_found]\n",
       "Index: []"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# visualize a data table where at least one gene was found, belonging to the certain header category\n",
    "df_energy[(df_energy['total_genes_found'] != 0) & (df_energy['module'] == 'Acetyl-CoA pathway')]\n",
    "\n",
    "# Be sure that you choose the correct 'header' category, or 'module' category"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fadde0d0-ebb6-4902-841f-6dcac5103864",
   "metadata": {},
   "source": [
    "What other metabolism summaries are you interested in?  Try this command with your broad category of choice (as seen in product.html)\n",
    "\n",
    "Try these commands with the SAGs you chose to annotate/distill during the first part of this activity!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a589893d-1d46-434f-b28d-76f9f54a7f0b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "biopy",
   "language": "python",
   "name": "biopy"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}