1.2 Prepare mapping file (Python).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook is to add additional covariates (health, stool info, medication, dietary history) to the mapping file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "mf = pd.read_csv('../data/mapping_MrOS.txt', sep='\\t', dtype=str, index_col='#SampleID') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(599, 64)\n"
     ]
    },
    {
     "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>BarcodeSequence</th>\n",
       "      <th>LinkerPrimerSequence</th>\n",
       "      <th>Experiment_Design_Description</th>\n",
       "      <th>Library_Construction_Protocol</th>\n",
       "      <th>Linker</th>\n",
       "      <th>Platform</th>\n",
       "      <th>Center_Name</th>\n",
       "      <th>Center_Project</th>\n",
       "      <th>Instrument_Model</th>\n",
       "      <th>Title</th>\n",
       "      <th>...</th>\n",
       "      <th>OHVD3</th>\n",
       "      <th>OHVD2</th>\n",
       "      <th>OHV1D2</th>\n",
       "      <th>OHV1D2CT</th>\n",
       "      <th>OHVD2CT</th>\n",
       "      <th>OHVDTOT</th>\n",
       "      <th>OHV1DTOT</th>\n",
       "      <th>OHSEAS</th>\n",
       "      <th>VDstatus</th>\n",
       "      <th>Description</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#SampleID</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>BI0023</th>\n",
       "      <td>TCTGGTGACATT</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>25.8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>25.8</td>\n",
       "      <td>39.3</td>\n",
       "      <td>3:SUMMER</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0023.BI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0056</th>\n",
       "      <td>CAAGCATGCCTA</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>39.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>39.2</td>\n",
       "      <td>61.9</td>\n",
       "      <td>2:SPRING</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0056.BI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0131</th>\n",
       "      <td>CTATTTGCGACA</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>23.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>23.1</td>\n",
       "      <td>52.1</td>\n",
       "      <td>2:SPRING</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0131.BI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0153</th>\n",
       "      <td>ATCGGCGTTACA</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>27.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>27.3</td>\n",
       "      <td>43.1</td>\n",
       "      <td>2:SPRING</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0153.BI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0215</th>\n",
       "      <td>CCTCTCGTGATC</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>33.0</td>\n",
       "      <td>50.2</td>\n",
       "      <td>4:FALL</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0215.BI</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 64 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          BarcodeSequence  LinkerPrimerSequence  \\\n",
       "#SampleID                                         \n",
       "BI0023       TCTGGTGACATT  GGACTACHVGGGTWTCTAAT   \n",
       "BI0056       CAAGCATGCCTA  GGACTACHVGGGTWTCTAAT   \n",
       "BI0131       CTATTTGCGACA  GGACTACHVGGGTWTCTAAT   \n",
       "BI0153       ATCGGCGTTACA  GGACTACHVGGGTWTCTAAT   \n",
       "BI0215       CCTCTCGTGATC  GGACTACHVGGGTWTCTAAT   \n",
       "\n",
       "                               Experiment_Design_Description  \\\n",
       "#SampleID                                                      \n",
       "BI0023     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0056     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0131     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0153     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0215     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "\n",
       "          Library_Construction_Protocol Linker  Platform Center_Name  \\\n",
       "#SampleID                                                              \n",
       "BI0023                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0056                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0131                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0153                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0215                      16S rRNA v4     GT  Illumina          BI   \n",
       "\n",
       "          Center_Project Instrument_Model          Title  ... OHVD3 OHVD2  \\\n",
       "#SampleID                                                 ...               \n",
       "BI0023              MrOS   Illumina MiSeq  MrOS_VitaminD  ...  25.8   0.0   \n",
       "BI0056              MrOS   Illumina MiSeq  MrOS_VitaminD  ...  39.2   0.0   \n",
       "BI0131              MrOS   Illumina MiSeq  MrOS_VitaminD  ...  23.1   0.0   \n",
       "BI0153              MrOS   Illumina MiSeq  MrOS_VitaminD  ...  27.3   0.0   \n",
       "BI0215              MrOS   Illumina MiSeq  MrOS_VitaminD  ...    33   0.0   \n",
       "\n",
       "          OHV1D2 OHV1D2CT OHVD2CT OHVDTOT OHV1DTOT    OHSEAS     VDstatus  \\\n",
       "#SampleID                                                                   \n",
       "BI0023       0.0   1: Yes  1: Yes    25.8     39.3  3:SUMMER  sufficiency   \n",
       "BI0056       0.0   1: Yes  1: Yes    39.2     61.9  2:SPRING  sufficiency   \n",
       "BI0131       0.0   1: Yes  1: Yes    23.1     52.1  2:SPRING  sufficiency   \n",
       "BI0153       0.0   1: Yes  1: Yes    27.3     43.1  2:SPRING  sufficiency   \n",
       "BI0215       0.0   1: Yes  1: Yes    33.0     50.2    4:FALL  sufficiency   \n",
       "\n",
       "                Description  \n",
       "#SampleID                    \n",
       "BI0023     Orwoll.BI0023.BI  \n",
       "BI0056     Orwoll.BI0056.BI  \n",
       "BI0131     Orwoll.BI0131.BI  \n",
       "BI0153     Orwoll.BI0153.BI  \n",
       "BI0215     Orwoll.BI0215.BI  \n",
       "\n",
       "[5 rows x 64 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(mf.shape)\n",
    "mf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## convert covariates to proper continuous or categorical format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "vars_cat = np.array(['BarcodeSequence', 'LinkerPrimerSequence', 'Experiment_Design_Description',\n",
    "             'Library_Construction_Protocol', 'Linker', 'Platform', 'Center_Name', 'Center_Project', 'Instrument_Model',\n",
    "             'Title', 'Anonymized_Name', 'Scientific_Name', 'Taxon_ID', 'Sample_Type', 'Geo_Loc_Name', 'Elevation', 'Env_Biome',\n",
    "             'Env_Feature', 'Env_Material', 'Env_Package', 'Collection_Timestamp', 'DNA_Extracted', 'Physical_Specimen_Location',\n",
    "             'Physical_Specimen_Remaining', 'Age_Units', 'Host_Subject_ID', 'Host_Taxid','Host_Scientific_Name', 'Host_Common_Name',\n",
    "             'Life_Stage', 'Sex', 'Height_Units', 'Weight_Units', 'Body_Habitat', 'Body_Site', 'Body_Product', 'GIERACE', 'SITE',\n",
    "             'TUDRAMT', 'TURSMOKE', 'M1ADEPR', 'M1VITMND', 'M1ANTIB', 'M1PROBI', 'OHSEAS', 'VDstatus', 'Description',\n",
    "             'OHV1D2CT', 'OHVD2CT'])\n",
    "vars_cts = np.array(['Latitude', 'Longitude', 'Age', 'Height', 'Weight', 'BMI', 'PASCORE', 'DTVITD', \n",
    "             'OHV1D3', 'OHV24D3', 'OHVD3', 'OHVD2', 'OHV1D2',  'OHVDTOT', 'OHV1DTOT'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert vars_cts to numeric and vars_cat to factors\n",
    "df = mf.copy()\n",
    "df[vars_cts] = df[vars_cts].apply(pd.to_numeric, errors='coerce')\n",
    "df[vars_cat] = df[vars_cat].apply(lambda x: x.astype('category'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert all pg/ml to ng/ml note: 1 ng/ml = 1000 pg/ml\n",
    "df.OHV1D3 = df.OHV1D3/1000\n",
    "df.OHV1D2 = df.OHV1D2/1000\n",
    "df.OHV1DTOT = df.OHV1DTOT/1000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## add two ratio variables of Vitamin D"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['ratio_activation'] = df.OHV1D3/df.OHVD3\n",
    "df['ratio_catabolism'] = df.OHV24D3/df.OHVD3 \n",
    "vars_cts = np.append(vars_cts, ['ratio_activation', 'ratio_catabolism'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Age</th>\n",
       "      <th>Height</th>\n",
       "      <th>Weight</th>\n",
       "      <th>BMI</th>\n",
       "      <th>PASCORE</th>\n",
       "      <th>DTVITD</th>\n",
       "      <th>OHV1D3</th>\n",
       "      <th>OHV24D3</th>\n",
       "      <th>OHVD3</th>\n",
       "      <th>OHVD2</th>\n",
       "      <th>OHV1D2</th>\n",
       "      <th>OHVDTOT</th>\n",
       "      <th>OHV1DTOT</th>\n",
       "      <th>ratio_activation</th>\n",
       "      <th>ratio_catabolism</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>599.000000</td>\n",
       "      <td>599.000000</td>\n",
       "      <td>599.000000</td>\n",
       "      <td>599.000000</td>\n",
       "      <td>599.000000</td>\n",
       "      <td>599.000000</td>\n",
       "      <td>599.000000</td>\n",
       "      <td>584.000000</td>\n",
       "      <td>567.000000</td>\n",
       "      <td>567.000000</td>\n",
       "      <td>556.000000</td>\n",
       "      <td>567.000000</td>\n",
       "      <td>567.000000</td>\n",
       "      <td>556.000000</td>\n",
       "      <td>567.000000</td>\n",
       "      <td>556.000000</td>\n",
       "      <td>556.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>39.131492</td>\n",
       "      <td>-105.850862</td>\n",
       "      <td>84.237062</td>\n",
       "      <td>172.143573</td>\n",
       "      <td>80.173957</td>\n",
       "      <td>27.009647</td>\n",
       "      <td>122.482829</td>\n",
       "      <td>164.395957</td>\n",
       "      <td>0.057775</td>\n",
       "      <td>3.430864</td>\n",
       "      <td>35.229137</td>\n",
       "      <td>0.769136</td>\n",
       "      <td>0.000177</td>\n",
       "      <td>36.013489</td>\n",
       "      <td>0.057951</td>\n",
       "      <td>0.001772</td>\n",
       "      <td>0.094776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>5.226215</td>\n",
       "      <td>17.221362</td>\n",
       "      <td>4.061471</td>\n",
       "      <td>6.803620</td>\n",
       "      <td>12.994918</td>\n",
       "      <td>3.788662</td>\n",
       "      <td>66.667650</td>\n",
       "      <td>127.458600</td>\n",
       "      <td>0.019773</td>\n",
       "      <td>1.834771</td>\n",
       "      <td>12.450758</td>\n",
       "      <td>4.266210</td>\n",
       "      <td>0.002232</td>\n",
       "      <td>12.410942</td>\n",
       "      <td>0.019656</td>\n",
       "      <td>0.000735</td>\n",
       "      <td>0.029770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>32.715738</td>\n",
       "      <td>-122.676500</td>\n",
       "      <td>78.000000</td>\n",
       "      <td>153.850000</td>\n",
       "      <td>51.400000</td>\n",
       "      <td>17.599566</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.429000</td>\n",
       "      <td>0.010700</td>\n",
       "      <td>0.300000</td>\n",
       "      <td>7.800000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>7.800000</td>\n",
       "      <td>0.010700</td>\n",
       "      <td>0.000398</td>\n",
       "      <td>0.018788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>33.520661</td>\n",
       "      <td>-122.143000</td>\n",
       "      <td>81.000000</td>\n",
       "      <td>167.325000</td>\n",
       "      <td>71.000000</td>\n",
       "      <td>24.506948</td>\n",
       "      <td>70.142857</td>\n",
       "      <td>77.962500</td>\n",
       "      <td>0.044100</td>\n",
       "      <td>2.175000</td>\n",
       "      <td>27.400000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>28.100000</td>\n",
       "      <td>0.044250</td>\n",
       "      <td>0.001316</td>\n",
       "      <td>0.074216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>40.440625</td>\n",
       "      <td>-117.161100</td>\n",
       "      <td>83.000000</td>\n",
       "      <td>172.100000</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>26.724552</td>\n",
       "      <td>116.750000</td>\n",
       "      <td>128.125000</td>\n",
       "      <td>0.055500</td>\n",
       "      <td>3.180000</td>\n",
       "      <td>33.650000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>34.150000</td>\n",
       "      <td>0.055500</td>\n",
       "      <td>0.001660</td>\n",
       "      <td>0.092821</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>44.977753</td>\n",
       "      <td>-86.802490</td>\n",
       "      <td>87.000000</td>\n",
       "      <td>176.725000</td>\n",
       "      <td>87.500000</td>\n",
       "      <td>28.975480</td>\n",
       "      <td>165.785714</td>\n",
       "      <td>211.642500</td>\n",
       "      <td>0.066300</td>\n",
       "      <td>4.235000</td>\n",
       "      <td>41.825000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>42.300000</td>\n",
       "      <td>0.066450</td>\n",
       "      <td>0.002081</td>\n",
       "      <td>0.112849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>45.523062</td>\n",
       "      <td>-79.995890</td>\n",
       "      <td>98.000000</td>\n",
       "      <td>194.650000</td>\n",
       "      <td>127.600000</td>\n",
       "      <td>42.931509</td>\n",
       "      <td>359.142857</td>\n",
       "      <td>982.830000</td>\n",
       "      <td>0.156000</td>\n",
       "      <td>14.070000</td>\n",
       "      <td>104.000000</td>\n",
       "      <td>59.000000</td>\n",
       "      <td>0.037800</td>\n",
       "      <td>104.000000</td>\n",
       "      <td>0.156000</td>\n",
       "      <td>0.006727</td>\n",
       "      <td>0.197786</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Latitude   Longitude         Age      Height      Weight         BMI  \\\n",
       "count  599.000000  599.000000  599.000000  599.000000  599.000000  599.000000   \n",
       "mean    39.131492 -105.850862   84.237062  172.143573   80.173957   27.009647   \n",
       "std      5.226215   17.221362    4.061471    6.803620   12.994918    3.788662   \n",
       "min     32.715738 -122.676500   78.000000  153.850000   51.400000   17.599566   \n",
       "25%     33.520661 -122.143000   81.000000  167.325000   71.000000   24.506948   \n",
       "50%     40.440625 -117.161100   83.000000  172.100000   79.000000   26.724552   \n",
       "75%     44.977753  -86.802490   87.000000  176.725000   87.500000   28.975480   \n",
       "max     45.523062  -79.995890   98.000000  194.650000  127.600000   42.931509   \n",
       "\n",
       "          PASCORE      DTVITD      OHV1D3     OHV24D3       OHVD3       OHVD2  \\\n",
       "count  599.000000  584.000000  567.000000  567.000000  556.000000  567.000000   \n",
       "mean   122.482829  164.395957    0.057775    3.430864   35.229137    0.769136   \n",
       "std     66.667650  127.458600    0.019773    1.834771   12.450758    4.266210   \n",
       "min      0.000000    0.429000    0.010700    0.300000    7.800000    0.000000   \n",
       "25%     70.142857   77.962500    0.044100    2.175000   27.400000    0.000000   \n",
       "50%    116.750000  128.125000    0.055500    3.180000   33.650000    0.000000   \n",
       "75%    165.785714  211.642500    0.066300    4.235000   41.825000    0.000000   \n",
       "max    359.142857  982.830000    0.156000   14.070000  104.000000   59.000000   \n",
       "\n",
       "           OHV1D2     OHVDTOT    OHV1DTOT  ratio_activation  ratio_catabolism  \n",
       "count  567.000000  556.000000  567.000000        556.000000        556.000000  \n",
       "mean     0.000177   36.013489    0.057951          0.001772          0.094776  \n",
       "std      0.002232   12.410942    0.019656          0.000735          0.029770  \n",
       "min      0.000000    7.800000    0.010700          0.000398          0.018788  \n",
       "25%      0.000000   28.100000    0.044250          0.001316          0.074216  \n",
       "50%      0.000000   34.150000    0.055500          0.001660          0.092821  \n",
       "75%      0.000000   42.300000    0.066450          0.002081          0.112849  \n",
       "max      0.037800  104.000000    0.156000          0.006727          0.197786  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[vars_cts].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>BarcodeSequence</th>\n",
       "      <th>LinkerPrimerSequence</th>\n",
       "      <th>Experiment_Design_Description</th>\n",
       "      <th>Library_Construction_Protocol</th>\n",
       "      <th>Linker</th>\n",
       "      <th>Platform</th>\n",
       "      <th>Center_Name</th>\n",
       "      <th>Center_Project</th>\n",
       "      <th>Instrument_Model</th>\n",
       "      <th>Title</th>\n",
       "      <th>...</th>\n",
       "      <th>TURSMOKE</th>\n",
       "      <th>M1ADEPR</th>\n",
       "      <th>M1VITMND</th>\n",
       "      <th>M1ANTIB</th>\n",
       "      <th>M1PROBI</th>\n",
       "      <th>OHSEAS</th>\n",
       "      <th>VDstatus</th>\n",
       "      <th>Description</th>\n",
       "      <th>OHV1D2CT</th>\n",
       "      <th>OHVD2CT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>...</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>588</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>599</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>599</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>TTGTCTGGAAGC</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>SD</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>1:PAST</td>\n",
       "      <td>0: No</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0: No</td>\n",
       "      <td>0: No</td>\n",
       "      <td>3:SUMMER</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.SD9009.SD</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>1: Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>134</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>599</td>\n",
       "      <td>...</td>\n",
       "      <td>289</td>\n",
       "      <td>546</td>\n",
       "      <td>445</td>\n",
       "      <td>558</td>\n",
       "      <td>573</td>\n",
       "      <td>220</td>\n",
       "      <td>516</td>\n",
       "      <td>1</td>\n",
       "      <td>563</td>\n",
       "      <td>448</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 49 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       BarcodeSequence  LinkerPrimerSequence  \\\n",
       "count              599                   599   \n",
       "unique             599                     1   \n",
       "top       TTGTCTGGAAGC  GGACTACHVGGGTWTCTAAT   \n",
       "freq                 1                   599   \n",
       "\n",
       "                            Experiment_Design_Description  \\\n",
       "count                                                 599   \n",
       "unique                                                  1   \n",
       "top     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "freq                                                  599   \n",
       "\n",
       "       Library_Construction_Protocol Linker  Platform Center_Name  \\\n",
       "count                            599    599       599         599   \n",
       "unique                             1      1         1           6   \n",
       "top                      16S rRNA v4     GT  Illumina          SD   \n",
       "freq                             599    599       599         134   \n",
       "\n",
       "       Center_Project Instrument_Model          Title  ... TURSMOKE M1ADEPR  \\\n",
       "count             599              599            599  ...      599     599   \n",
       "unique              1                1              1  ...        4       2   \n",
       "top              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   1:PAST   0: No   \n",
       "freq              599              599            599  ...      289     546   \n",
       "\n",
       "       M1VITMND M1ANTIB M1PROBI    OHSEAS     VDstatus       Description  \\\n",
       "count       599     599     599       599          588               599   \n",
       "unique        2       2       2         5            3               599   \n",
       "top      1: Yes   0: No   0: No  3:SUMMER  sufficiency  Orwoll.SD9009.SD   \n",
       "freq        445     558     573       220          516                 1   \n",
       "\n",
       "       OHV1D2CT OHVD2CT  \n",
       "count       599     599  \n",
       "unique        3       3  \n",
       "top      1: Yes  1: Yes  \n",
       "freq        563     448  \n",
       "\n",
       "[4 rows x 49 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[vars_cat].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Latitude             0\n",
       "Longitude            0\n",
       "Age                  0\n",
       "Height               0\n",
       "Weight               0\n",
       "BMI                  0\n",
       "PASCORE              0\n",
       "DTVITD              15\n",
       "OHV1D3              32\n",
       "OHV24D3             32\n",
       "OHVD3               43\n",
       "OHVD2               32\n",
       "OHV1D2              32\n",
       "OHVDTOT             43\n",
       "OHV1DTOT            32\n",
       "ratio_activation    43\n",
       "ratio_catabolism    43\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count missing values\n",
    "df[vars_cts].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(599, 66)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## add health variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "health = pd.read_csv('../data/MrOS_healthvari.csv', sep=',', dtype=str)\n",
    "health = health.rename(columns={'ID': '#SampleID'}).set_index('#SampleID')\n",
    "health['QLCOMP'] = health['QLCOMP'].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(df, health, left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(599, 67)\n"
     ]
    },
    {
     "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>BarcodeSequence</th>\n",
       "      <th>LinkerPrimerSequence</th>\n",
       "      <th>Experiment_Design_Description</th>\n",
       "      <th>Library_Construction_Protocol</th>\n",
       "      <th>Linker</th>\n",
       "      <th>Platform</th>\n",
       "      <th>Center_Name</th>\n",
       "      <th>Center_Project</th>\n",
       "      <th>Instrument_Model</th>\n",
       "      <th>Title</th>\n",
       "      <th>...</th>\n",
       "      <th>OHV1D2CT</th>\n",
       "      <th>OHVD2CT</th>\n",
       "      <th>OHVDTOT</th>\n",
       "      <th>OHV1DTOT</th>\n",
       "      <th>OHSEAS</th>\n",
       "      <th>VDstatus</th>\n",
       "      <th>Description</th>\n",
       "      <th>ratio_activation</th>\n",
       "      <th>ratio_catabolism</th>\n",
       "      <th>QLCOMP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#SampleID</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>BI0023</th>\n",
       "      <td>TCTGGTGACATT</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>25.8</td>\n",
       "      <td>0.0393</td>\n",
       "      <td>3:SUMMER</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0023.BI</td>\n",
       "      <td>0.001523</td>\n",
       "      <td>0.068605</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0056</th>\n",
       "      <td>CAAGCATGCCTA</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>39.2</td>\n",
       "      <td>0.0619</td>\n",
       "      <td>2:SPRING</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0056.BI</td>\n",
       "      <td>0.001579</td>\n",
       "      <td>0.099745</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0131</th>\n",
       "      <td>CTATTTGCGACA</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>23.1</td>\n",
       "      <td>0.0521</td>\n",
       "      <td>2:SPRING</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0131.BI</td>\n",
       "      <td>0.002255</td>\n",
       "      <td>0.064502</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0153</th>\n",
       "      <td>ATCGGCGTTACA</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>27.3</td>\n",
       "      <td>0.0431</td>\n",
       "      <td>2:SPRING</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0153.BI</td>\n",
       "      <td>0.001579</td>\n",
       "      <td>0.078388</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0215</th>\n",
       "      <td>CCTCTCGTGATC</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0.0502</td>\n",
       "      <td>4:FALL</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0215.BI</td>\n",
       "      <td>0.001521</td>\n",
       "      <td>0.109697</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 67 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          BarcodeSequence  LinkerPrimerSequence  \\\n",
       "#SampleID                                         \n",
       "BI0023       TCTGGTGACATT  GGACTACHVGGGTWTCTAAT   \n",
       "BI0056       CAAGCATGCCTA  GGACTACHVGGGTWTCTAAT   \n",
       "BI0131       CTATTTGCGACA  GGACTACHVGGGTWTCTAAT   \n",
       "BI0153       ATCGGCGTTACA  GGACTACHVGGGTWTCTAAT   \n",
       "BI0215       CCTCTCGTGATC  GGACTACHVGGGTWTCTAAT   \n",
       "\n",
       "                               Experiment_Design_Description  \\\n",
       "#SampleID                                                      \n",
       "BI0023     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0056     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0131     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0153     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0215     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "\n",
       "          Library_Construction_Protocol Linker  Platform Center_Name  \\\n",
       "#SampleID                                                              \n",
       "BI0023                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0056                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0131                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0153                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0215                      16S rRNA v4     GT  Illumina          BI   \n",
       "\n",
       "          Center_Project Instrument_Model          Title  ... OHV1D2CT  \\\n",
       "#SampleID                                                 ...            \n",
       "BI0023              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   1: Yes   \n",
       "BI0056              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   1: Yes   \n",
       "BI0131              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   1: Yes   \n",
       "BI0153              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   1: Yes   \n",
       "BI0215              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   1: Yes   \n",
       "\n",
       "          OHVD2CT OHVDTOT OHV1DTOT    OHSEAS     VDstatus       Description  \\\n",
       "#SampleID                                                                     \n",
       "BI0023     1: Yes    25.8   0.0393  3:SUMMER  sufficiency  Orwoll.BI0023.BI   \n",
       "BI0056     1: Yes    39.2   0.0619  2:SPRING  sufficiency  Orwoll.BI0056.BI   \n",
       "BI0131     1: Yes    23.1   0.0521  2:SPRING  sufficiency  Orwoll.BI0131.BI   \n",
       "BI0153     1: Yes    27.3   0.0431  2:SPRING  sufficiency  Orwoll.BI0153.BI   \n",
       "BI0215     1: Yes    33.0   0.0502    4:FALL  sufficiency  Orwoll.BI0215.BI   \n",
       "\n",
       "          ratio_activation ratio_catabolism            QLCOMP  \n",
       "#SampleID                                                      \n",
       "BI0023            0.001523         0.068605  1:GOOD/EXCELLENT  \n",
       "BI0056            0.001579         0.099745  1:GOOD/EXCELLENT  \n",
       "BI0131            0.002255         0.064502  1:GOOD/EXCELLENT  \n",
       "BI0153            0.001579         0.078388  1:GOOD/EXCELLENT  \n",
       "BI0215            0.001521         0.109697  1:GOOD/EXCELLENT  \n",
       "\n",
       "[5 rows x 67 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## add stool sample variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "stool = pd.read_csv('../data/Stool600comments_JS.txt', sep='\\t', dtype=str)\n",
    "stool = stool.rename(columns={'ID': '#SampleID'}).set_index('#SampleID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PO7020\n",
      "PO7029\n",
      "PO7020 \n",
      "PO7029 \n",
      "PO7100\n"
     ]
    }
   ],
   "source": [
    "id_stool = list(stool.index)\n",
    "id_mapping = list(df.index)\n",
    "\n",
    "for i in id_mapping:\n",
    "    if i not in id_stool:\n",
    "        print(i)\n",
    "        \n",
    "for i in id_stool:\n",
    "    if i not in id_mapping:\n",
    "        print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "stool['Timestool '] = stool['Timestool '].astype('category') # notice a space in name\n",
    "stool['Timestoolc'] = stool['Timestoolc'].astype('category')\n",
    "stool['StoolCollected'] = stool['StoolCollected'].astype('category')\n",
    "stool['StoolShipped'] = stool['StoolShipped'].astype('category')\n",
    "stool['Days( between collection and shipment)'] = stool['Days( between collection and shipment)'].astype('category')\n",
    "stool['StoolQualityC'] = stool['StoolQualityC'].astype('category')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## add medication variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "med = pd.read_csv('../data/Microbiom_medsupdate.csv', sep=',', dtype=str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>ID</th>\n",
       "      <th>M1STATIN</th>\n",
       "      <th>Antihistamine</th>\n",
       "      <th>Laxative</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BI0023</td>\n",
       "      <td>0: No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BI0056</td>\n",
       "      <td>0: No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BI0131</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BI0153</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BI0215</td>\n",
       "      <td>0: No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ID M1STATIN Antihistamine Laxative\n",
       "0  BI0023    0: No          0:No     0:No\n",
       "1  BI0056    0: No          0:No     0:No\n",
       "2  BI0131   1: Yes          0:No     0:No\n",
       "3  BI0153   1: Yes          0:No     0:No\n",
       "4  BI0215    0: No          0:No     0:No"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "med.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "med = med.rename(columns={'ID': '#SampleID'}).set_index('#SampleID')\n",
    "med = med.apply(lambda x: x.astype('category'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>M1STATIN</th>\n",
       "      <th>Antihistamine</th>\n",
       "      <th>Laxative</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#SampleID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BI0023</th>\n",
       "      <td>0: No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0056</th>\n",
       "      <td>0: No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0131</th>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0153</th>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0215</th>\n",
       "      <td>0: No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          M1STATIN Antihistamine Laxative\n",
       "#SampleID                                \n",
       "BI0023       0: No          0:No     0:No\n",
       "BI0056       0: No          0:No     0:No\n",
       "BI0131      1: Yes          0:No     0:No\n",
       "BI0153      1: Yes          0:No     0:No\n",
       "BI0215       0: No          0:No     0:No"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "med.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(df, med, left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(599, 70)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>BarcodeSequence</th>\n",
       "      <th>LinkerPrimerSequence</th>\n",
       "      <th>Experiment_Design_Description</th>\n",
       "      <th>Library_Construction_Protocol</th>\n",
       "      <th>Linker</th>\n",
       "      <th>Platform</th>\n",
       "      <th>Center_Name</th>\n",
       "      <th>Center_Project</th>\n",
       "      <th>Instrument_Model</th>\n",
       "      <th>Title</th>\n",
       "      <th>...</th>\n",
       "      <th>OHV1DTOT</th>\n",
       "      <th>OHSEAS</th>\n",
       "      <th>VDstatus</th>\n",
       "      <th>Description</th>\n",
       "      <th>ratio_activation</th>\n",
       "      <th>ratio_catabolism</th>\n",
       "      <th>QLCOMP</th>\n",
       "      <th>M1STATIN</th>\n",
       "      <th>Antihistamine</th>\n",
       "      <th>Laxative</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#SampleID</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>BI0023</th>\n",
       "      <td>TCTGGTGACATT</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0393</td>\n",
       "      <td>3:SUMMER</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0023.BI</td>\n",
       "      <td>0.001523</td>\n",
       "      <td>0.068605</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "      <td>0: No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0056</th>\n",
       "      <td>CAAGCATGCCTA</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0619</td>\n",
       "      <td>2:SPRING</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0056.BI</td>\n",
       "      <td>0.001579</td>\n",
       "      <td>0.099745</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "      <td>0: No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0131</th>\n",
       "      <td>CTATTTGCGACA</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0521</td>\n",
       "      <td>2:SPRING</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0131.BI</td>\n",
       "      <td>0.002255</td>\n",
       "      <td>0.064502</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0153</th>\n",
       "      <td>ATCGGCGTTACA</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0431</td>\n",
       "      <td>2:SPRING</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0153.BI</td>\n",
       "      <td>0.001579</td>\n",
       "      <td>0.078388</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0215</th>\n",
       "      <td>CCTCTCGTGATC</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0502</td>\n",
       "      <td>4:FALL</td>\n",
       "      <td>sufficiency</td>\n",
       "      <td>Orwoll.BI0215.BI</td>\n",
       "      <td>0.001521</td>\n",
       "      <td>0.109697</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "      <td>0: No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 70 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          BarcodeSequence  LinkerPrimerSequence  \\\n",
       "#SampleID                                         \n",
       "BI0023       TCTGGTGACATT  GGACTACHVGGGTWTCTAAT   \n",
       "BI0056       CAAGCATGCCTA  GGACTACHVGGGTWTCTAAT   \n",
       "BI0131       CTATTTGCGACA  GGACTACHVGGGTWTCTAAT   \n",
       "BI0153       ATCGGCGTTACA  GGACTACHVGGGTWTCTAAT   \n",
       "BI0215       CCTCTCGTGATC  GGACTACHVGGGTWTCTAAT   \n",
       "\n",
       "                               Experiment_Design_Description  \\\n",
       "#SampleID                                                      \n",
       "BI0023     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0056     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0131     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0153     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0215     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "\n",
       "          Library_Construction_Protocol Linker  Platform Center_Name  \\\n",
       "#SampleID                                                              \n",
       "BI0023                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0056                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0131                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0153                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0215                      16S rRNA v4     GT  Illumina          BI   \n",
       "\n",
       "          Center_Project Instrument_Model          Title  ... OHV1DTOT  \\\n",
       "#SampleID                                                 ...            \n",
       "BI0023              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   0.0393   \n",
       "BI0056              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   0.0619   \n",
       "BI0131              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   0.0521   \n",
       "BI0153              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   0.0431   \n",
       "BI0215              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   0.0502   \n",
       "\n",
       "             OHSEAS     VDstatus       Description ratio_activation  \\\n",
       "#SampleID                                                             \n",
       "BI0023     3:SUMMER  sufficiency  Orwoll.BI0023.BI         0.001523   \n",
       "BI0056     2:SPRING  sufficiency  Orwoll.BI0056.BI         0.001579   \n",
       "BI0131     2:SPRING  sufficiency  Orwoll.BI0131.BI         0.002255   \n",
       "BI0153     2:SPRING  sufficiency  Orwoll.BI0153.BI         0.001579   \n",
       "BI0215       4:FALL  sufficiency  Orwoll.BI0215.BI         0.001521   \n",
       "\n",
       "          ratio_catabolism            QLCOMP M1STATIN Antihistamine Laxative  \n",
       "#SampleID                                                                     \n",
       "BI0023            0.068605  1:GOOD/EXCELLENT    0: No          0:No     0:No  \n",
       "BI0056            0.099745  1:GOOD/EXCELLENT    0: No          0:No     0:No  \n",
       "BI0131            0.064502  1:GOOD/EXCELLENT   1: Yes          0:No     0:No  \n",
       "BI0153            0.078388  1:GOOD/EXCELLENT   1: Yes          0:No     0:No  \n",
       "BI0215            0.109697  1:GOOD/EXCELLENT    0: No          0:No     0:No  \n",
       "\n",
       "[5 rows x 70 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## add starch variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "st = pd.read_csv('../data/Microbiome_DietaryStarchesnew.csv', sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(600, 4)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "st.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "st = st.rename(columns={'ID': '#SampleID', 'Rstarches_c (0: <5 and 1>=5)': 'Rstarches_c'}).set_index('#SampleID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>MIDATA</th>\n",
       "      <th>Rstarch_total</th>\n",
       "      <th>Rstarches_c</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#SampleID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BI0023</th>\n",
       "      <td>1: Yes</td>\n",
       "      <td>3.066569</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0056</th>\n",
       "      <td>1: Yes</td>\n",
       "      <td>3.038136</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0131</th>\n",
       "      <td>1: Yes</td>\n",
       "      <td>2.840599</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0153</th>\n",
       "      <td>1: Yes</td>\n",
       "      <td>4.106798</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0215</th>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0.971114</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           MIDATA  Rstarch_total  Rstarches_c\n",
       "#SampleID                                    \n",
       "BI0023     1: Yes       3.066569          0.0\n",
       "BI0056     1: Yes       3.038136          0.0\n",
       "BI0131     1: Yes       2.840599          0.0\n",
       "BI0153     1: Yes       4.106798          0.0\n",
       "BI0215     1: Yes       0.971114          0.0"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "st.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(df, st, left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(599, 73)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### add PPI medication information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "ppi = pd.read_csv('../data/PPI_info_mros.txt', sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(599, 3)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ppi.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "ppi = ppi.rename(columns={'ID': '#SampleID'}).set_index('#SampleID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>MIDATA</th>\n",
       "      <th>M1PPUMP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#SampleID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>BI0023</th>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0: No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0056</th>\n",
       "      <td>1: Yes</td>\n",
       "      <td>1: Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0131</th>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0: No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0153</th>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0: No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0215</th>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0: No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           MIDATA M1PPUMP\n",
       "#SampleID                \n",
       "BI0023     1: Yes   0: No\n",
       "BI0056     1: Yes  1: Yes\n",
       "BI0131     1: Yes   0: No\n",
       "BI0153     1: Yes   0: No\n",
       "BI0215     1: Yes   0: No"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ppi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "ppi = ppi.drop(columns=['MIDATA'])\n",
    "df = pd.merge(df, ppi, left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(599, 74)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## output new mapping file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('../data/mapping_MrOS_add.txt', sep='\\t', index_label='#SampleID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>BarcodeSequence</th>\n",
       "      <th>LinkerPrimerSequence</th>\n",
       "      <th>Experiment_Design_Description</th>\n",
       "      <th>Library_Construction_Protocol</th>\n",
       "      <th>Linker</th>\n",
       "      <th>Platform</th>\n",
       "      <th>Center_Name</th>\n",
       "      <th>Center_Project</th>\n",
       "      <th>Instrument_Model</th>\n",
       "      <th>Title</th>\n",
       "      <th>...</th>\n",
       "      <th>ratio_activation</th>\n",
       "      <th>ratio_catabolism</th>\n",
       "      <th>QLCOMP</th>\n",
       "      <th>M1STATIN</th>\n",
       "      <th>Antihistamine</th>\n",
       "      <th>Laxative</th>\n",
       "      <th>MIDATA</th>\n",
       "      <th>Rstarch_total</th>\n",
       "      <th>Rstarches_c</th>\n",
       "      <th>M1PPUMP</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>#SampleID</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>BI0023</th>\n",
       "      <td>TCTGGTGACATT</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>0.001523</td>\n",
       "      <td>0.068605</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "      <td>0: No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>3.066569</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0: No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0056</th>\n",
       "      <td>CAAGCATGCCTA</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>0.001579</td>\n",
       "      <td>0.099745</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "      <td>0: No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>3.038136</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1: Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0131</th>\n",
       "      <td>CTATTTGCGACA</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>0.002255</td>\n",
       "      <td>0.064502</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>2.840599</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0: No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0153</th>\n",
       "      <td>ATCGGCGTTACA</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>0.001579</td>\n",
       "      <td>0.078388</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>4.106798</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0: No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BI0215</th>\n",
       "      <td>CCTCTCGTGATC</td>\n",
       "      <td>GGACTACHVGGGTWTCTAAT</td>\n",
       "      <td>16S stool samples sequenced for MrOS Vitamin D...</td>\n",
       "      <td>16S rRNA v4</td>\n",
       "      <td>GT</td>\n",
       "      <td>Illumina</td>\n",
       "      <td>BI</td>\n",
       "      <td>MrOS</td>\n",
       "      <td>Illumina MiSeq</td>\n",
       "      <td>MrOS_VitaminD</td>\n",
       "      <td>...</td>\n",
       "      <td>0.001521</td>\n",
       "      <td>0.109697</td>\n",
       "      <td>1:GOOD/EXCELLENT</td>\n",
       "      <td>0: No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>0:No</td>\n",
       "      <td>1: Yes</td>\n",
       "      <td>0.971114</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0: No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 74 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          BarcodeSequence  LinkerPrimerSequence  \\\n",
       "#SampleID                                         \n",
       "BI0023       TCTGGTGACATT  GGACTACHVGGGTWTCTAAT   \n",
       "BI0056       CAAGCATGCCTA  GGACTACHVGGGTWTCTAAT   \n",
       "BI0131       CTATTTGCGACA  GGACTACHVGGGTWTCTAAT   \n",
       "BI0153       ATCGGCGTTACA  GGACTACHVGGGTWTCTAAT   \n",
       "BI0215       CCTCTCGTGATC  GGACTACHVGGGTWTCTAAT   \n",
       "\n",
       "                               Experiment_Design_Description  \\\n",
       "#SampleID                                                      \n",
       "BI0023     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0056     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0131     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0153     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "BI0215     16S stool samples sequenced for MrOS Vitamin D...   \n",
       "\n",
       "          Library_Construction_Protocol Linker  Platform Center_Name  \\\n",
       "#SampleID                                                              \n",
       "BI0023                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0056                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0131                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0153                      16S rRNA v4     GT  Illumina          BI   \n",
       "BI0215                      16S rRNA v4     GT  Illumina          BI   \n",
       "\n",
       "          Center_Project Instrument_Model          Title  ...  \\\n",
       "#SampleID                                                 ...   \n",
       "BI0023              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   \n",
       "BI0056              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   \n",
       "BI0131              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   \n",
       "BI0153              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   \n",
       "BI0215              MrOS   Illumina MiSeq  MrOS_VitaminD  ...   \n",
       "\n",
       "          ratio_activation ratio_catabolism            QLCOMP M1STATIN  \\\n",
       "#SampleID                                                                \n",
       "BI0023            0.001523         0.068605  1:GOOD/EXCELLENT    0: No   \n",
       "BI0056            0.001579         0.099745  1:GOOD/EXCELLENT    0: No   \n",
       "BI0131            0.002255         0.064502  1:GOOD/EXCELLENT   1: Yes   \n",
       "BI0153            0.001579         0.078388  1:GOOD/EXCELLENT   1: Yes   \n",
       "BI0215            0.001521         0.109697  1:GOOD/EXCELLENT    0: No   \n",
       "\n",
       "          Antihistamine Laxative  MIDATA Rstarch_total Rstarches_c M1PPUMP  \n",
       "#SampleID                                                                   \n",
       "BI0023             0:No     0:No  1: Yes      3.066569         0.0   0: No  \n",
       "BI0056             0:No     0:No  1: Yes      3.038136         0.0  1: Yes  \n",
       "BI0131             0:No     0:No  1: Yes      2.840599         0.0   0: No  \n",
       "BI0153             0:No     0:No  1: Yes      4.106798         0.0   0: No  \n",
       "BI0215             0:No     0:No  1: Yes      0.971114         0.0   0: No  \n",
       "\n",
       "[5 rows x 74 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}