03_picrust2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7432d9e5-c0ca-4668-b57e-f4c2762725d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "from scipy.stats import f_oneway\n",
    "from statsmodels.stats.multicomp import pairwise_tukeyhsd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f72d466b-120e-43d6-be72-f2d083de6309",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "# Creating PICRUSt2 file (only needs to run once)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3c95e6d7-67f4-4446-b642-1232ec7d19cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the CSV files\n",
    "EC_gibbs_info = pd.read_csv('PICRUSt2/GIBBs.EC.numbers.csv')\n",
    "EC_gibbs = pd.read_csv('PICRUSt2/EMU_database.GIBBs.EC.PICRUSt2.R.csv')\n",
    "\n",
    "# Set row names\n",
    "EC_gibbs = EC_gibbs.set_index('tax_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cf0e2945-edeb-4166-aef6-2e5d8729afb3",
   "metadata": {},
   "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>Kingdom</th>\n",
       "      <th>Phylum</th>\n",
       "      <th>Class</th>\n",
       "      <th>Order</th>\n",
       "      <th>Family</th>\n",
       "      <th>Genus</th>\n",
       "      <th>Species</th>\n",
       "      <th>16S_rRNA_Count</th>\n",
       "      <th>metadata_NSTI</th>\n",
       "      <th>gc</th>\n",
       "      <th>...</th>\n",
       "      <th>EC.1.14.18.3</th>\n",
       "      <th>EC.1.14.99.39</th>\n",
       "      <th>EC.1.7.2.6</th>\n",
       "      <th>EC.1.7.1.15</th>\n",
       "      <th>EC.1.7.2.2</th>\n",
       "      <th>EC.1.7.1.4</th>\n",
       "      <th>EC.1.7.7.1</th>\n",
       "      <th>EC.1.7.2.1</th>\n",
       "      <th>EC.1.7.2.5</th>\n",
       "      <th>EC.1.7.2.4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tax_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Bacteria</td>\n",
       "      <td>Proteobacteria</td>\n",
       "      <td>Alphaproteobacteria</td>\n",
       "      <td>Hyphomicrobiales</td>\n",
       "      <td>Xanthobacteraceae</td>\n",
       "      <td>Ancylobacter</td>\n",
       "      <td>Ancylobacter aquaticus</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.009404</td>\n",
       "      <td>0.559407</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100053</th>\n",
       "      <td>Bacteria</td>\n",
       "      <td>Spirochaetes</td>\n",
       "      <td>Spirochaetia</td>\n",
       "      <td>Leptospirales</td>\n",
       "      <td>Leptospiraceae</td>\n",
       "      <td>Leptospira</td>\n",
       "      <td>Leptospira alexanderi</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.025089</td>\n",
       "      <td>0.528302</td>\n",
       "      <td>...</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",
       "      <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>1000566</th>\n",
       "      <td>Bacteria</td>\n",
       "      <td>Actinobacteria</td>\n",
       "      <td>Actinomycetia</td>\n",
       "      <td>Pseudonocardiales</td>\n",
       "      <td>Pseudonocardiaceae</td>\n",
       "      <td>Halosaccharopolyspora</td>\n",
       "      <td>Halosaccharopolyspora lacisalsi</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.030423</td>\n",
       "      <td>0.597183</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</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",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1001</th>\n",
       "      <td>Bacteria</td>\n",
       "      <td>Bacteroidetes</td>\n",
       "      <td>Cytophagia</td>\n",
       "      <td>Cytophagales</td>\n",
       "      <td>Berrdetiaceae</td>\n",
       "      <td>Garritya</td>\n",
       "      <td>Garritya polymorpha</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.402240</td>\n",
       "      <td>0.497630</td>\n",
       "      <td>...</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",
       "      <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>1001240</th>\n",
       "      <td>Bacteria</td>\n",
       "      <td>Actinobacteria</td>\n",
       "      <td>Actinomycetia</td>\n",
       "      <td>Micrococcales</td>\n",
       "      <td>Microbacteriaceae</td>\n",
       "      <td>Cryobacterium</td>\n",
       "      <td>Cryobacterium roopkundense</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.016080</td>\n",
       "      <td>0.562714</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</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",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 48 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Kingdom          Phylum                Class              Order  \\\n",
       "tax_id                                                                      \n",
       "100      Bacteria  Proteobacteria  Alphaproteobacteria   Hyphomicrobiales   \n",
       "100053   Bacteria    Spirochaetes         Spirochaetia      Leptospirales   \n",
       "1000566  Bacteria  Actinobacteria        Actinomycetia  Pseudonocardiales   \n",
       "1001     Bacteria   Bacteroidetes           Cytophagia       Cytophagales   \n",
       "1001240  Bacteria  Actinobacteria        Actinomycetia      Micrococcales   \n",
       "\n",
       "                     Family                  Genus  \\\n",
       "tax_id                                               \n",
       "100       Xanthobacteraceae           Ancylobacter   \n",
       "100053       Leptospiraceae             Leptospira   \n",
       "1000566  Pseudonocardiaceae  Halosaccharopolyspora   \n",
       "1001          Berrdetiaceae               Garritya   \n",
       "1001240   Microbacteriaceae          Cryobacterium   \n",
       "\n",
       "                                 Species  16S_rRNA_Count  metadata_NSTI  \\\n",
       "tax_id                                                                    \n",
       "100               Ancylobacter aquaticus             2.0       0.009404   \n",
       "100053             Leptospira alexanderi             1.0       0.025089   \n",
       "1000566  Halosaccharopolyspora lacisalsi             1.0       0.030423   \n",
       "1001                 Garritya polymorpha             1.0       0.402240   \n",
       "1001240       Cryobacterium roopkundense             1.0       0.016080   \n",
       "\n",
       "               gc  ...  EC.1.14.18.3  EC.1.14.99.39  EC.1.7.2.6  EC.1.7.1.15  \\\n",
       "tax_id             ...                                                         \n",
       "100      0.559407  ...           0.0            0.0         0.0          0.5   \n",
       "100053   0.528302  ...           0.0            0.0         0.0          0.0   \n",
       "1000566  0.597183  ...           0.0            0.0         0.0          2.0   \n",
       "1001     0.497630  ...           0.0            0.0         0.0          0.0   \n",
       "1001240  0.562714  ...           0.0            0.0         0.0          2.0   \n",
       "\n",
       "         EC.1.7.2.2  EC.1.7.1.4  EC.1.7.7.1  EC.1.7.2.1  EC.1.7.2.5  \\\n",
       "tax_id                                                                \n",
       "100             0.0         0.0         0.5         0.0         0.0   \n",
       "100053          0.0         0.0         0.0         0.0         0.0   \n",
       "1000566         0.0         0.0         0.0         0.0         0.0   \n",
       "1001            0.0         0.0         0.0         0.0         0.0   \n",
       "1001240         0.0         0.0         0.0         0.0         0.0   \n",
       "\n",
       "         EC.1.7.2.4  \n",
       "tax_id               \n",
       "100             0.0  \n",
       "100053          0.0  \n",
       "1000566         0.0  \n",
       "1001            0.0  \n",
       "1001240         0.0  \n",
       "\n",
       "[5 rows x 48 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "EC_gibbs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "89700ec3-9b14-40c1-b071-3483cd3e363c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read another CSV file\n",
    "df = pd.read_csv('rel_abund_16S_ABC_qpcr_adj.csv')\n",
    "\n",
    "# Copying the dataframe\n",
    "EC_sub = EC_gibbs.copy()\n",
    "\n",
    "# Binarizing the numeric columns\n",
    "EC_bin = EC_sub.apply(lambda x: (x > 0).astype(int) if np.issubdtype(x.dtype, np.number) else x)\n",
    "\n",
    "# Get the sample names and enzyme names\n",
    "samples = df.columns[9:]\n",
    "enzymes = EC_sub.columns[11:]\n",
    "\n",
    "# Calculate the column sums\n",
    "reads = df[samples].sum(axis=0, skipna=True)\n",
    "\n",
    "# Initialize the final dataframe\n",
    "final = pd.DataFrame(index=enzymes, columns=samples)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e9c70c03-44f0-45c1-a72f-0bf2864fa154",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Nested loop for calculation\n",
    "for i in samples:  # Limiting to first 3 samples\n",
    "    for j in enzymes:\n",
    "        # Calculation without the skipna parameter\n",
    "        value = np.sum(df[i] / EC_bin['16S_rRNA_Count'] * EC_bin[j]) / reads[i]\n",
    "        final.at[j, i] = value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "3608bca6-c43c-49d6-9ea0-0cdffcc6d47f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# If you want to save 'final' to a CSV file\n",
    "final.to_csv('PICRUSt2/GIBBs.rel_abund.final.A.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "f5ddd236-b39f-40a1-aae7-3c235970aeef",
   "metadata": {},
   "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>barcode02A</th>\n",
       "      <th>barcode03A</th>\n",
       "      <th>barcode04A</th>\n",
       "      <th>barcode05A</th>\n",
       "      <th>barcode06A</th>\n",
       "      <th>barcode07A</th>\n",
       "      <th>barcode08A</th>\n",
       "      <th>barcode09A</th>\n",
       "      <th>barcode10A</th>\n",
       "      <th>barcode11A</th>\n",
       "      <th>...</th>\n",
       "      <th>barcode74C</th>\n",
       "      <th>barcode75C</th>\n",
       "      <th>barcode76C</th>\n",
       "      <th>barcode77C</th>\n",
       "      <th>barcode78C</th>\n",
       "      <th>barcode79C</th>\n",
       "      <th>barcode80C</th>\n",
       "      <th>barcode81C</th>\n",
       "      <th>barcode82C</th>\n",
       "      <th>barcode83C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>EC.4.1.1.5</th>\n",
       "      <td>0.070501</td>\n",
       "      <td>0.032222</td>\n",
       "      <td>0.038203</td>\n",
       "      <td>0.069625</td>\n",
       "      <td>0.061416</td>\n",
       "      <td>0.052594</td>\n",
       "      <td>0.070817</td>\n",
       "      <td>0.029874</td>\n",
       "      <td>0.027948</td>\n",
       "      <td>0.058781</td>\n",
       "      <td>...</td>\n",
       "      <td>0.053962</td>\n",
       "      <td>0.048658</td>\n",
       "      <td>0.049178</td>\n",
       "      <td>0.047255</td>\n",
       "      <td>0.058373</td>\n",
       "      <td>0.049791</td>\n",
       "      <td>0.046048</td>\n",
       "      <td>0.059305</td>\n",
       "      <td>0.073815</td>\n",
       "      <td>0.066628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EC.1.1.1.76</th>\n",
       "      <td>0.094335</td>\n",
       "      <td>0.094093</td>\n",
       "      <td>0.060159</td>\n",
       "      <td>0.113105</td>\n",
       "      <td>0.086322</td>\n",
       "      <td>0.07846</td>\n",
       "      <td>0.117633</td>\n",
       "      <td>0.075767</td>\n",
       "      <td>0.099996</td>\n",
       "      <td>0.100183</td>\n",
       "      <td>...</td>\n",
       "      <td>0.069324</td>\n",
       "      <td>0.063201</td>\n",
       "      <td>0.0675</td>\n",
       "      <td>0.066325</td>\n",
       "      <td>0.065032</td>\n",
       "      <td>0.061272</td>\n",
       "      <td>0.055558</td>\n",
       "      <td>0.070769</td>\n",
       "      <td>0.091271</td>\n",
       "      <td>0.091634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EC.3.2.1.6</th>\n",
       "      <td>0.00258</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.003189</td>\n",
       "      <td>0.008596</td>\n",
       "      <td>0.001846</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.002505</td>\n",
       "      <td>...</td>\n",
       "      <td>0.002026</td>\n",
       "      <td>0.001127</td>\n",
       "      <td>0.000866</td>\n",
       "      <td>0.00153</td>\n",
       "      <td>0.000426</td>\n",
       "      <td>0.001286</td>\n",
       "      <td>0.001494</td>\n",
       "      <td>0.001252</td>\n",
       "      <td>0.001364</td>\n",
       "      <td>0.002906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EC.3.2.1.14</th>\n",
       "      <td>0.17726</td>\n",
       "      <td>0.217941</td>\n",
       "      <td>0.131829</td>\n",
       "      <td>0.198505</td>\n",
       "      <td>0.172831</td>\n",
       "      <td>0.128928</td>\n",
       "      <td>0.190086</td>\n",
       "      <td>0.157443</td>\n",
       "      <td>0.143782</td>\n",
       "      <td>0.223027</td>\n",
       "      <td>...</td>\n",
       "      <td>0.127043</td>\n",
       "      <td>0.127601</td>\n",
       "      <td>0.111586</td>\n",
       "      <td>0.139139</td>\n",
       "      <td>0.146757</td>\n",
       "      <td>0.117479</td>\n",
       "      <td>0.107287</td>\n",
       "      <td>0.124374</td>\n",
       "      <td>0.137074</td>\n",
       "      <td>0.13714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EC.2.6.1.86</th>\n",
       "      <td>0.002902</td>\n",
       "      <td>0.009929</td>\n",
       "      <td>0.004662</td>\n",
       "      <td>0.008633</td>\n",
       "      <td>0.00826</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.005652</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00501</td>\n",
       "      <td>...</td>\n",
       "      <td>0.005069</td>\n",
       "      <td>0.005806</td>\n",
       "      <td>0.006803</td>\n",
       "      <td>0.005564</td>\n",
       "      <td>0.012218</td>\n",
       "      <td>0.003162</td>\n",
       "      <td>0.004271</td>\n",
       "      <td>0.002479</td>\n",
       "      <td>0.003745</td>\n",
       "      <td>0.006964</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 241 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            barcode02A barcode03A barcode04A barcode05A barcode06A barcode07A  \\\n",
       "EC.4.1.1.5    0.070501   0.032222   0.038203   0.069625   0.061416   0.052594   \n",
       "EC.1.1.1.76   0.094335   0.094093   0.060159   0.113105   0.086322    0.07846   \n",
       "EC.3.2.1.6     0.00258        0.0   0.003189   0.008596   0.001846        0.0   \n",
       "EC.3.2.1.14    0.17726   0.217941   0.131829   0.198505   0.172831   0.128928   \n",
       "EC.2.6.1.86   0.002902   0.009929   0.004662   0.008633    0.00826        0.0   \n",
       "\n",
       "            barcode08A barcode09A barcode10A barcode11A  ... barcode74C  \\\n",
       "EC.4.1.1.5    0.070817   0.029874   0.027948   0.058781  ...   0.053962   \n",
       "EC.1.1.1.76   0.117633   0.075767   0.099996   0.100183  ...   0.069324   \n",
       "EC.3.2.1.6         0.0        0.0        0.0   0.002505  ...   0.002026   \n",
       "EC.3.2.1.14   0.190086   0.157443   0.143782   0.223027  ...   0.127043   \n",
       "EC.2.6.1.86   0.005652        0.0        0.0    0.00501  ...   0.005069   \n",
       "\n",
       "            barcode75C barcode76C barcode77C barcode78C barcode79C barcode80C  \\\n",
       "EC.4.1.1.5    0.048658   0.049178   0.047255   0.058373   0.049791   0.046048   \n",
       "EC.1.1.1.76   0.063201     0.0675   0.066325   0.065032   0.061272   0.055558   \n",
       "EC.3.2.1.6    0.001127   0.000866    0.00153   0.000426   0.001286   0.001494   \n",
       "EC.3.2.1.14   0.127601   0.111586   0.139139   0.146757   0.117479   0.107287   \n",
       "EC.2.6.1.86   0.005806   0.006803   0.005564   0.012218   0.003162   0.004271   \n",
       "\n",
       "            barcode81C barcode82C barcode83C  \n",
       "EC.4.1.1.5    0.059305   0.073815   0.066628  \n",
       "EC.1.1.1.76   0.070769   0.091271   0.091634  \n",
       "EC.3.2.1.6    0.001252   0.001364   0.002906  \n",
       "EC.3.2.1.14   0.124374   0.137074    0.13714  \n",
       "EC.2.6.1.86   0.002479   0.003745   0.006964  \n",
       "\n",
       "[5 rows x 241 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1403814-3c9a-4e4f-a00c-8f7bf11c986a",
   "metadata": {},
   "source": [
    "# Load PICRUSt2 file and mapping file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8ce057d9-2145-4794-becb-7d711f49dcd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a6caf9f9-4981-49ce-bd8a-7cf1684d600b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the datasets\n",
    "gibbs_df = pd.read_csv('PICRUSt2/GIBBs.rel_abund.final.A.csv', index_col = 0)\n",
    "mapping_df = pd.read_csv('mapping_file_with_metadata_harvest_biomass_2022.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4fddae74-7237-4726-9332-b62b5b0ea9ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'2021-06-15',\n",
       " '2021-06-30',\n",
       " '2021-07-27',\n",
       " '2021-08-10',\n",
       " '2021-09-23',\n",
       " '2022-05-26',\n",
       " '2022-06-22',\n",
       " '2022-07-20',\n",
       " '2022-09-01',\n",
       " '2022-09-27'}"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(mapping_df['Sample date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "95904e7b-f401-4ae1-964a-548242aae7e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Transpose the filtered GIBBS dataframe to have EC numbers as columns\n",
    "gibbs_df = gibbs_df.transpose()\n",
    "\n",
    "# Merge the GIBBS data with the mapping data on the Barcode_key column\n",
    "merged_df = pd.merge(mapping_df, gibbs_df, left_on='Barcode_key', right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c79dc8d1-8176-4306-8edc-3b1416a7c0df",
   "metadata": {},
   "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>Unnamed: 0</th>\n",
       "      <th>Sample name</th>\n",
       "      <th>Sample date</th>\n",
       "      <th>Sample ID</th>\n",
       "      <th>Plot</th>\n",
       "      <th>ID</th>\n",
       "      <th>Nitrogen</th>\n",
       "      <th>Water</th>\n",
       "      <th>Block</th>\n",
       "      <th>Direction</th>\n",
       "      <th>...</th>\n",
       "      <th>EC.1.14.18.3</th>\n",
       "      <th>EC.1.14.99.39</th>\n",
       "      <th>EC.1.7.2.6</th>\n",
       "      <th>EC.1.7.1.15</th>\n",
       "      <th>EC.1.7.2.2</th>\n",
       "      <th>EC.1.7.1.4</th>\n",
       "      <th>EC.1.7.7.1</th>\n",
       "      <th>EC.1.7.2.1</th>\n",
       "      <th>EC.1.7.2.5</th>\n",
       "      <th>EC.1.7.2.4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>193</td>\n",
       "      <td>2021-06-15</td>\n",
       "      <td>2N</td>\n",
       "      <td>2N</td>\n",
       "      <td>111.0</td>\n",
       "      <td>Low</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>0.011413</td>\n",
       "      <td>0.011413</td>\n",
       "      <td>0.001463</td>\n",
       "      <td>0.255915</td>\n",
       "      <td>0.146247</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.019078</td>\n",
       "      <td>0.070940</td>\n",
       "      <td>0.097735</td>\n",
       "      <td>0.044308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>205</td>\n",
       "      <td>2021-06-15</td>\n",
       "      <td>2S</td>\n",
       "      <td>2S</td>\n",
       "      <td>111.0</td>\n",
       "      <td>Low</td>\n",
       "      <td>High</td>\n",
       "      <td>1</td>\n",
       "      <td>S</td>\n",
       "      <td>...</td>\n",
       "      <td>0.010296</td>\n",
       "      <td>0.010296</td>\n",
       "      <td>0.001793</td>\n",
       "      <td>0.278651</td>\n",
       "      <td>0.138524</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.018311</td>\n",
       "      <td>0.076365</td>\n",
       "      <td>0.104344</td>\n",
       "      <td>0.037114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>212</td>\n",
       "      <td>2021-06-15</td>\n",
       "      <td>23S</td>\n",
       "      <td>23S</td>\n",
       "      <td>211.0</td>\n",
       "      <td>Low</td>\n",
       "      <td>High</td>\n",
       "      <td>2</td>\n",
       "      <td>S</td>\n",
       "      <td>...</td>\n",
       "      <td>0.009314</td>\n",
       "      <td>0.009314</td>\n",
       "      <td>0.001190</td>\n",
       "      <td>0.339663</td>\n",
       "      <td>0.110387</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.006431</td>\n",
       "      <td>0.063950</td>\n",
       "      <td>0.091039</td>\n",
       "      <td>0.024709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>221</td>\n",
       "      <td>2021-06-15</td>\n",
       "      <td>23N</td>\n",
       "      <td>23N</td>\n",
       "      <td>211.0</td>\n",
       "      <td>Low</td>\n",
       "      <td>High</td>\n",
       "      <td>2</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>0.009552</td>\n",
       "      <td>0.009552</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.269004</td>\n",
       "      <td>0.133448</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.014147</td>\n",
       "      <td>0.074577</td>\n",
       "      <td>0.104856</td>\n",
       "      <td>0.044248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>195</td>\n",
       "      <td>2021-06-15</td>\n",
       "      <td>32N</td>\n",
       "      <td>32N</td>\n",
       "      <td>311.0</td>\n",
       "      <td>Low</td>\n",
       "      <td>High</td>\n",
       "      <td>3</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>0.006488</td>\n",
       "      <td>0.006488</td>\n",
       "      <td>0.001442</td>\n",
       "      <td>0.316970</td>\n",
       "      <td>0.131443</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.016739</td>\n",
       "      <td>0.063306</td>\n",
       "      <td>0.078908</td>\n",
       "      <td>0.026063</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 79 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Sample name Sample date Sample ID Plot     ID Nitrogen Water  \\\n",
       "0           0          193  2021-06-15        2N   2N  111.0      Low  High   \n",
       "1           1          205  2021-06-15        2S   2S  111.0      Low  High   \n",
       "2           2          212  2021-06-15       23S  23S  211.0      Low  High   \n",
       "3           3          221  2021-06-15       23N  23N  211.0      Low  High   \n",
       "4           4          195  2021-06-15       32N  32N  311.0      Low  High   \n",
       "\n",
       "   Block Direction  ...  EC.1.14.18.3 EC.1.14.99.39 EC.1.7.2.6 EC.1.7.1.15  \\\n",
       "0      1         N  ...      0.011413      0.011413   0.001463    0.255915   \n",
       "1      1         S  ...      0.010296      0.010296   0.001793    0.278651   \n",
       "2      2         S  ...      0.009314      0.009314   0.001190    0.339663   \n",
       "3      2         N  ...      0.009552      0.009552   0.000000    0.269004   \n",
       "4      3         N  ...      0.006488      0.006488   0.001442    0.316970   \n",
       "\n",
       "   EC.1.7.2.2  EC.1.7.1.4  EC.1.7.7.1  EC.1.7.2.1  EC.1.7.2.5  EC.1.7.2.4  \n",
       "0    0.146247         0.0    0.019078    0.070940    0.097735    0.044308  \n",
       "1    0.138524         0.0    0.018311    0.076365    0.104344    0.037114  \n",
       "2    0.110387         0.0    0.006431    0.063950    0.091039    0.024709  \n",
       "3    0.133448         0.0    0.014147    0.074577    0.104856    0.044248  \n",
       "4    0.131443         0.0    0.016739    0.063306    0.078908    0.026063  \n",
       "\n",
       "[5 rows x 79 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.to_csv('Data/PICRUSt2/picrust_mapping_file.csv')\n",
    "merged_df.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py311",
   "language": "python",
   "name": "py311"
  },
  "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}