3-analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aefe31f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash \n",
    "pip install arch\n",
    "pip install numpy\n",
    "pip install pandas\n",
    "pip install seaborn\n",
    "pip install mlxtend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "17197b62",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "pd.options.mode.chained_assignment = None\n",
    "import numpy as np\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.ticker\n",
    "import os\n",
    "import seaborn as sns\n",
    "from scipy import stats\n",
    "from mlxtend.evaluate import permutation_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "ea5f0712",
   "metadata": {},
   "outputs": [],
   "source": [
    "#A=pd.read_pickle(\"data/allyears-avg_plate.pkl\")\n",
    "A=pd.read_pickle(\"data/allyears-avg_plate.pkl\").copy(deep=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "912d367b",
   "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>Date</th>\n",
       "      <th>Location</th>\n",
       "      <th>festival</th>\n",
       "      <th>rooms</th>\n",
       "      <th>year</th>\n",
       "      <th>rain</th>\n",
       "      <th>Bioindicator</th>\n",
       "      <th>Coliform</th>\n",
       "      <th>total</th>\n",
       "      <th>week</th>\n",
       "      <th>Bioindicator UV+</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-06-21</td>\n",
       "      <td>MRD</td>\n",
       "      <td>before</td>\n",
       "      <td>3069.600000</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>66.666667</td>\n",
       "      <td>466.666667</td>\n",
       "      <td>900.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-06-21</td>\n",
       "      <td>SVT</td>\n",
       "      <td>before</td>\n",
       "      <td>3069.600000</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33.333333</td>\n",
       "      <td>33.333333</td>\n",
       "      <td>133.333333</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-06-21</td>\n",
       "      <td>VNX</td>\n",
       "      <td>before</td>\n",
       "      <td>3069.600000</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>66.666667</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-06-28</td>\n",
       "      <td>MRD</td>\n",
       "      <td>before</td>\n",
       "      <td>3069.600000</td>\n",
       "      <td>16</td>\n",
       "      <td>1.6</td>\n",
       "      <td>8.333333</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>491.666667</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-06-28</td>\n",
       "      <td>SVT</td>\n",
       "      <td>before</td>\n",
       "      <td>3069.600000</td>\n",
       "      <td>16</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.333333</td>\n",
       "      <td>391.666667</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>2020-07-22</td>\n",
       "      <td>SVT</td>\n",
       "      <td>before</td>\n",
       "      <td>8058.967742</td>\n",
       "      <td>20</td>\n",
       "      <td>7.8</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>6066.666667</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>2020-07-22</td>\n",
       "      <td>VNX</td>\n",
       "      <td>before</td>\n",
       "      <td>8058.967742</td>\n",
       "      <td>20</td>\n",
       "      <td>7.8</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>200.000000</td>\n",
       "      <td>666.666667</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>2020-08-06</td>\n",
       "      <td>MRD</td>\n",
       "      <td>before</td>\n",
       "      <td>7890.741935</td>\n",
       "      <td>20</td>\n",
       "      <td>1.8</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>233.333333</td>\n",
       "      <td>3100.000000</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>2020-08-06</td>\n",
       "      <td>SVT</td>\n",
       "      <td>before</td>\n",
       "      <td>7890.741935</td>\n",
       "      <td>20</td>\n",
       "      <td>1.8</td>\n",
       "      <td>166.666667</td>\n",
       "      <td>633.333333</td>\n",
       "      <td>3366.666667</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>2020-08-06</td>\n",
       "      <td>VNX</td>\n",
       "      <td>before</td>\n",
       "      <td>7890.741935</td>\n",
       "      <td>20</td>\n",
       "      <td>1.8</td>\n",
       "      <td>66.666667</td>\n",
       "      <td>366.666667</td>\n",
       "      <td>3966.666667</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date Location festival        rooms  year  rain  Bioindicator  \\\n",
       "0   2016-06-21      MRD   before  3069.600000    16   0.0     66.666667   \n",
       "1   2016-06-21      SVT   before  3069.600000    16   0.0     33.333333   \n",
       "2   2016-06-21      VNX   before  3069.600000    16   0.0      0.000000   \n",
       "3   2016-06-28      MRD   before  3069.600000    16   1.6      8.333333   \n",
       "4   2016-06-28      SVT   before  3069.600000    16   1.6      0.000000   \n",
       "..         ...      ...      ...          ...   ...   ...           ...   \n",
       "67  2020-07-22      SVT   before  8058.967742    20   7.8    100.000000   \n",
       "68  2020-07-22      VNX   before  8058.967742    20   7.8      0.000000   \n",
       "69  2020-08-06      MRD   before  7890.741935    20   1.8      0.000000   \n",
       "70  2020-08-06      SVT   before  7890.741935    20   1.8    166.666667   \n",
       "71  2020-08-06      VNX   before  7890.741935    20   1.8     66.666667   \n",
       "\n",
       "       Coliform        total  week  Bioindicator UV+  \n",
       "0    466.666667   900.000000     1               NaN  \n",
       "1     33.333333   133.333333     1               NaN  \n",
       "2      0.000000    66.666667     1               NaN  \n",
       "3     25.000000   491.666667     2               NaN  \n",
       "4      8.333333   391.666667     2               NaN  \n",
       "..          ...          ...   ...               ...  \n",
       "67  1000.000000  6066.666667     7               NaN  \n",
       "68   200.000000   666.666667     7               NaN  \n",
       "69   233.333333  3100.000000     8               NaN  \n",
       "70   633.333333  3366.666667     8               NaN  \n",
       "71   366.666667  3966.666667     8               NaN  \n",
       "\n",
       "[72 rows x 11 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(A)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2028370b",
   "metadata": {},
   "source": [
    "## Permutation test figure 4B"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "fd5cb156",
   "metadata": {},
   "outputs": [],
   "source": [
    "def print_full(x):\n",
    "    pd.set_option('display.max_rows', len(x))\n",
    "    print(x)\n",
    "    pd.reset_option('display.max_rows')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "dcb8dd32",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.00019998000199980003\n"
     ]
    }
   ],
   "source": [
    "p_value = permutation_test( A.loc[((A['year']==16) | (A['year']==17)) & (A['festival'] == 'before'), 'Bioindicator'] , \n",
    "                            A.loc[((A['year']==16) | (A['year']==17)) & (A['festival'] == 'during'), 'Bioindicator'], \n",
    "                           method='approximate',\n",
    "                           num_rounds=10000,\n",
    "                           seed=0)\n",
    "print(p_value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "822540e7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.035996400359964\n"
     ]
    }
   ],
   "source": [
    "p_value = permutation_test( A.loc[((A['year']==16) | (A['year']==17)) & (A['festival'] == 'during'), 'Bioindicator'] , \n",
    "                            A.loc[((A['year']==16) | (A['year']==17)) & (A['festival'] == 'after'), 'Bioindicator'], \n",
    "                           method='approximate',\n",
    "                           num_rounds=10000,\n",
    "                           seed=0)\n",
    "print(p_value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "6176c9fd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.10208979102089791\n"
     ]
    }
   ],
   "source": [
    "p_value = permutation_test( A.loc[((A['year']==16) | (A['year']==17)) & (A['festival'] == 'before'), 'Bioindicator'] , \n",
    "                            A.loc[((A['year']==16) | (A['year']==17)) & (A['festival'] == 'after'), 'Bioindicator'], \n",
    "                           method='approximate',\n",
    "                           num_rounds=10000,\n",
    "                           seed=0)\n",
    "print(p_value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "22aea309",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.03690\n"
     ]
    }
   ],
   "source": [
    "p_value = permutation_test( A.loc[((A['year']==16) | (A['year']==17)) & (A['festival'] == 'before'), 'Bioindicator'] , \n",
    "                            A.loc[(A['year']==20), 'Bioindicator'], \n",
    "                           method='approximate',\n",
    "                           num_rounds=10000,\n",
    "                           seed=0)\n",
    "print(format(p_value, \".5f\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "7097ad30",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.0051\n"
     ]
    }
   ],
   "source": [
    "p_value = permutation_test( A.loc[((A['year']==16) | (A['year']==17)) & (A['festival'] == 'during'), 'Bioindicator'] , \n",
    "                            A.loc[((A['year']==20)), 'Bioindicator'], \n",
    "                           method='approximate',\n",
    "                           num_rounds=10000,\n",
    "                           seed=0)\n",
    "print(format(p_value, \".4f\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "286a98bd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.6967\n"
     ]
    }
   ],
   "source": [
    "p_value = permutation_test( A.loc[((A['year']==16) | (A['year']==17)) & (A['festival'] == 'after'), 'Bioindicator'] , \n",
    "                            A.loc[((A['year']==20)), 'Bioindicator'], \n",
    "                           method='approximate',\n",
    "                           num_rounds=10000,\n",
    "                           seed=0)\n",
    "print(format(p_value, \".4f\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc50af0f",
   "metadata": {},
   "source": [
    "## Correlation with Spearman Method 24 Hours \n",
    "\n",
    "https://docs.scipy.org/doc/scipy/reference/generated/scipy.stats.spearmanr.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c134c774",
   "metadata": {},
   "outputs": [],
   "source": [
    "A=pd.read_pickle(\"data/allyears.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "28ae9b7c",
   "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>Bioindicator</th>\n",
       "      <th>Coliform</th>\n",
       "      <th>Bioindicator UV+</th>\n",
       "      <th>total</th>\n",
       "      <th>rain</th>\n",
       "      <th>rooms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bioindicator</th>\n",
       "      <td>1.0***</td>\n",
       "      <td>0.09***</td>\n",
       "      <td>0.99***</td>\n",
       "      <td>0.16***</td>\n",
       "      <td>-0.03*</td>\n",
       "      <td>0.23***</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coliform</th>\n",
       "      <td>0.09***</td>\n",
       "      <td>1.0***</td>\n",
       "      <td>0.46***</td>\n",
       "      <td>0.68***</td>\n",
       "      <td>-0.12</td>\n",
       "      <td>0.04***</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bioindicator UV+</th>\n",
       "      <td>0.99***</td>\n",
       "      <td>0.46***</td>\n",
       "      <td>1.0***</td>\n",
       "      <td>0.42***</td>\n",
       "      <td>-0.3</td>\n",
       "      <td>0.31*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total</th>\n",
       "      <td>0.16***</td>\n",
       "      <td>0.68***</td>\n",
       "      <td>0.42***</td>\n",
       "      <td>1.0***</td>\n",
       "      <td>-0.01</td>\n",
       "      <td>0.25***</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rain</th>\n",
       "      <td>-0.03*</td>\n",
       "      <td>-0.12</td>\n",
       "      <td>-0.3</td>\n",
       "      <td>-0.01</td>\n",
       "      <td>1.0***</td>\n",
       "      <td>0.25***</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rooms</th>\n",
       "      <td>0.23***</td>\n",
       "      <td>0.04***</td>\n",
       "      <td>0.31*</td>\n",
       "      <td>0.25***</td>\n",
       "      <td>0.25***</td>\n",
       "      <td>1.0***</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Bioindicator Coliform Bioindicator UV+    total     rain  \\\n",
       "Bioindicator           1.0***  0.09***          0.99***  0.16***   -0.03*   \n",
       "Coliform              0.09***   1.0***          0.46***  0.68***    -0.12   \n",
       "Bioindicator UV+      0.99***  0.46***           1.0***  0.42***     -0.3   \n",
       "total                 0.16***  0.68***          0.42***   1.0***    -0.01   \n",
       "rain                   -0.03*    -0.12             -0.3    -0.01   1.0***   \n",
       "rooms                 0.23***  0.04***            0.31*  0.25***  0.25***   \n",
       "\n",
       "                    rooms  \n",
       "Bioindicator      0.23***  \n",
       "Coliform          0.04***  \n",
       "Bioindicator UV+    0.31*  \n",
       "total             0.25***  \n",
       "rain              0.25***  \n",
       "rooms              1.0***  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from scipy.stats import spearmanr\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "def calculate_pvalues(df):\n",
    "    df = df.dropna()._get_numeric_data()\n",
    "    dfcols = pd.DataFrame(columns=df.columns)\n",
    "    pvalues = dfcols.transpose().join(dfcols, how='outer')\n",
    "    for r in df.columns:\n",
    "        for c in df.columns:\n",
    "            pvalues[r][c] = round(pearsonr(df[r], df[c])[1], 4)\n",
    "    return pvalues\n",
    "\n",
    "\n",
    "main_vars=['Bioindicator', 'Coliform', 'Bioindicator UV+',  'total' ,'rain', 'rooms']\n",
    "p_df = A[main_vars]\n",
    "\n",
    "rho = p_df.corr()\n",
    "pval = p_df.corr(method=lambda x, y: spearmanr(x, y)[1]) - np.eye(*rho.shape)\n",
    "p = pval.applymap(lambda x: ''.join(['*' for t in [0.01,0.05,0.1] if x<=t]))\n",
    "rho.round(2).astype(str) + p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "fed2e364",
   "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>Bioindicator</th>\n",
       "      <th>Coliform</th>\n",
       "      <th>Bioindicator UV+</th>\n",
       "      <th>total</th>\n",
       "      <th>rain</th>\n",
       "      <th>rooms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bioindicator</th>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>0.09 p-value: 0.00001</td>\n",
       "      <td>0.99 p-value: 0.00000</td>\n",
       "      <td>0.16 p-value: 0.00000</td>\n",
       "      <td>-0.03 p-value: 0.06981</td>\n",
       "      <td>0.23 p-value: 0.00002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coliform</th>\n",
       "      <td>0.09 p-value: 0.00001</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>0.46 p-value: 0.00004</td>\n",
       "      <td>0.68 p-value: 0.00000</td>\n",
       "      <td>-0.12 p-value: 0.66865</td>\n",
       "      <td>0.04 p-value: 0.00239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bioindicator UV+</th>\n",
       "      <td>0.99 p-value: 0.00000</td>\n",
       "      <td>0.46 p-value: 0.00004</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>0.42 p-value: 0.00000</td>\n",
       "      <td>-0.3 p-value: 0.12247</td>\n",
       "      <td>0.31 p-value: 0.05533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total</th>\n",
       "      <td>0.16 p-value: 0.00000</td>\n",
       "      <td>0.68 p-value: 0.00000</td>\n",
       "      <td>0.42 p-value: 0.00000</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>-0.01 p-value: 0.43823</td>\n",
       "      <td>0.25 p-value: 0.00238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rain</th>\n",
       "      <td>-0.03 p-value: 0.06981</td>\n",
       "      <td>-0.12 p-value: 0.66865</td>\n",
       "      <td>-0.3 p-value: 0.12247</td>\n",
       "      <td>-0.01 p-value: 0.43823</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>0.25 p-value: 0.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rooms</th>\n",
       "      <td>0.23 p-value: 0.00002</td>\n",
       "      <td>0.04 p-value: 0.00239</td>\n",
       "      <td>0.31 p-value: 0.05533</td>\n",
       "      <td>0.25 p-value: 0.00238</td>\n",
       "      <td>0.25 p-value: 0.00000</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Bioindicator                Coliform  \\\n",
       "Bioindicator        1.0 p-value: 0.00000   0.09 p-value: 0.00001   \n",
       "Coliform           0.09 p-value: 0.00001    1.0 p-value: 0.00000   \n",
       "Bioindicator UV+   0.99 p-value: 0.00000   0.46 p-value: 0.00004   \n",
       "total              0.16 p-value: 0.00000   0.68 p-value: 0.00000   \n",
       "rain              -0.03 p-value: 0.06981  -0.12 p-value: 0.66865   \n",
       "rooms              0.23 p-value: 0.00002   0.04 p-value: 0.00239   \n",
       "\n",
       "                       Bioindicator UV+                   total  \\\n",
       "Bioindicator      0.99 p-value: 0.00000   0.16 p-value: 0.00000   \n",
       "Coliform          0.46 p-value: 0.00004   0.68 p-value: 0.00000   \n",
       "Bioindicator UV+   1.0 p-value: 0.00000   0.42 p-value: 0.00000   \n",
       "total             0.42 p-value: 0.00000    1.0 p-value: 0.00000   \n",
       "rain              -0.3 p-value: 0.12247  -0.01 p-value: 0.43823   \n",
       "rooms             0.31 p-value: 0.05533   0.25 p-value: 0.00238   \n",
       "\n",
       "                                    rain                  rooms  \n",
       "Bioindicator      -0.03 p-value: 0.06981  0.23 p-value: 0.00002  \n",
       "Coliform          -0.12 p-value: 0.66865  0.04 p-value: 0.00239  \n",
       "Bioindicator UV+   -0.3 p-value: 0.12247  0.31 p-value: 0.05533  \n",
       "total             -0.01 p-value: 0.43823  0.25 p-value: 0.00238  \n",
       "rain                1.0 p-value: 0.00000  0.25 p-value: 0.00000  \n",
       "rooms              0.25 p-value: 0.00000   1.0 p-value: 0.00000  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_vars=['Bioindicator', 'Coliform', 'Bioindicator UV+',  'total' ,'rain', 'rooms']\n",
    "p_df = A[main_vars]\n",
    "\n",
    "rho = p_df.corr()\n",
    "pval = p_df.corr(method=lambda x, y: spearmanr(x, y)[1]) - np.eye(*rho.shape)\n",
    "p = pval.applymap(lambda x: ''.join([' p-value: ', str(format(x, \".5f\"))]))\n",
    "rho.round(2).astype(str) + p"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d859d18f",
   "metadata": {},
   "source": [
    "## Correlation with Spearman Method cumulative 48 Hours"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "923979f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "A=pd.read_pickle(\"data/allyears_48h_cumulative.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "20e515e4",
   "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>Bioindicator</th>\n",
       "      <th>Coliform</th>\n",
       "      <th>Bioindicator UV+</th>\n",
       "      <th>total</th>\n",
       "      <th>rain</th>\n",
       "      <th>rooms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bioindicator</th>\n",
       "      <td>1.0***</td>\n",
       "      <td>0.09***</td>\n",
       "      <td>0.99***</td>\n",
       "      <td>0.16***</td>\n",
       "      <td>-0.13</td>\n",
       "      <td>0.16***</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coliform</th>\n",
       "      <td>0.09***</td>\n",
       "      <td>1.0***</td>\n",
       "      <td>0.46***</td>\n",
       "      <td>0.68***</td>\n",
       "      <td>-0.06</td>\n",
       "      <td>-0.2***</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bioindicator UV+</th>\n",
       "      <td>0.99***</td>\n",
       "      <td>0.46***</td>\n",
       "      <td>1.0***</td>\n",
       "      <td>0.42***</td>\n",
       "      <td>0.17**</td>\n",
       "      <td>-0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total</th>\n",
       "      <td>0.16***</td>\n",
       "      <td>0.68***</td>\n",
       "      <td>0.42***</td>\n",
       "      <td>1.0***</td>\n",
       "      <td>-0.09**</td>\n",
       "      <td>-0.27***</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rain</th>\n",
       "      <td>-0.13</td>\n",
       "      <td>-0.06</td>\n",
       "      <td>0.17**</td>\n",
       "      <td>-0.09**</td>\n",
       "      <td>1.0***</td>\n",
       "      <td>-0.43***</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rooms</th>\n",
       "      <td>0.16***</td>\n",
       "      <td>-0.2***</td>\n",
       "      <td>-0.03</td>\n",
       "      <td>-0.27***</td>\n",
       "      <td>-0.43***</td>\n",
       "      <td>1.0***</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Bioindicator Coliform Bioindicator UV+     total      rain  \\\n",
       "Bioindicator           1.0***  0.09***          0.99***   0.16***     -0.13   \n",
       "Coliform              0.09***   1.0***          0.46***   0.68***     -0.06   \n",
       "Bioindicator UV+      0.99***  0.46***           1.0***   0.42***    0.17**   \n",
       "total                 0.16***  0.68***          0.42***    1.0***   -0.09**   \n",
       "rain                    -0.13    -0.06           0.17**   -0.09**    1.0***   \n",
       "rooms                 0.16***  -0.2***            -0.03  -0.27***  -0.43***   \n",
       "\n",
       "                     rooms  \n",
       "Bioindicator       0.16***  \n",
       "Coliform           -0.2***  \n",
       "Bioindicator UV+     -0.03  \n",
       "total             -0.27***  \n",
       "rain              -0.43***  \n",
       "rooms               1.0***  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from scipy.stats import spearmanr\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "def calculate_pvalues(df):\n",
    "    df = df.dropna()._get_numeric_data()\n",
    "    dfcols = pd.DataFrame(columns=df.columns)\n",
    "    pvalues = dfcols.transpose().join(dfcols, how='outer')\n",
    "    for r in df.columns:\n",
    "        for c in df.columns:\n",
    "            pvalues[r][c] = round(pearsonr(df[r], df[c])[1], 4)\n",
    "    return pvalues\n",
    "\n",
    "\n",
    "main_vars=['Bioindicator', 'Coliform', 'Bioindicator UV+',  'total' ,'rain', 'rooms']\n",
    "p_df = A[main_vars]\n",
    "\n",
    "rho = p_df.corr()\n",
    "pval = p_df.corr(method=lambda x, y: spearmanr(x, y)[1]) - np.eye(*rho.shape)\n",
    "p = pval.applymap(lambda x: ''.join(['*' for t in [0.01,0.05,0.1] if x<=t]))\n",
    "rho.round(2).astype(str) + p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ad9e0c13",
   "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>Bioindicator</th>\n",
       "      <th>Coliform</th>\n",
       "      <th>Bioindicator UV+</th>\n",
       "      <th>total</th>\n",
       "      <th>rain</th>\n",
       "      <th>rooms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bioindicator</th>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>0.09 p-value: 0.00001</td>\n",
       "      <td>0.99 p-value: 0.00000</td>\n",
       "      <td>0.16 p-value: 0.00000</td>\n",
       "      <td>-0.13 p-value: 0.65244</td>\n",
       "      <td>0.16 p-value: 0.00314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coliform</th>\n",
       "      <td>0.09 p-value: 0.00001</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>0.46 p-value: 0.00004</td>\n",
       "      <td>0.68 p-value: 0.00000</td>\n",
       "      <td>-0.06 p-value: 0.29490</td>\n",
       "      <td>-0.2 p-value: 0.00002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bioindicator UV+</th>\n",
       "      <td>0.99 p-value: 0.00000</td>\n",
       "      <td>0.46 p-value: 0.00004</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>0.42 p-value: 0.00000</td>\n",
       "      <td>0.17 p-value: 0.02381</td>\n",
       "      <td>-0.03 p-value: 0.70203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total</th>\n",
       "      <td>0.16 p-value: 0.00000</td>\n",
       "      <td>0.68 p-value: 0.00000</td>\n",
       "      <td>0.42 p-value: 0.00000</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>-0.09 p-value: 0.04796</td>\n",
       "      <td>-0.27 p-value: 0.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rain</th>\n",
       "      <td>-0.13 p-value: 0.65244</td>\n",
       "      <td>-0.06 p-value: 0.29490</td>\n",
       "      <td>0.17 p-value: 0.02381</td>\n",
       "      <td>-0.09 p-value: 0.04796</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>-0.43 p-value: 0.00951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rooms</th>\n",
       "      <td>0.16 p-value: 0.00314</td>\n",
       "      <td>-0.2 p-value: 0.00002</td>\n",
       "      <td>-0.03 p-value: 0.70203</td>\n",
       "      <td>-0.27 p-value: 0.00000</td>\n",
       "      <td>-0.43 p-value: 0.00951</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Bioindicator                Coliform  \\\n",
       "Bioindicator        1.0 p-value: 0.00000   0.09 p-value: 0.00001   \n",
       "Coliform           0.09 p-value: 0.00001    1.0 p-value: 0.00000   \n",
       "Bioindicator UV+   0.99 p-value: 0.00000   0.46 p-value: 0.00004   \n",
       "total              0.16 p-value: 0.00000   0.68 p-value: 0.00000   \n",
       "rain              -0.13 p-value: 0.65244  -0.06 p-value: 0.29490   \n",
       "rooms              0.16 p-value: 0.00314   -0.2 p-value: 0.00002   \n",
       "\n",
       "                        Bioindicator UV+                   total  \\\n",
       "Bioindicator       0.99 p-value: 0.00000   0.16 p-value: 0.00000   \n",
       "Coliform           0.46 p-value: 0.00004   0.68 p-value: 0.00000   \n",
       "Bioindicator UV+    1.0 p-value: 0.00000   0.42 p-value: 0.00000   \n",
       "total              0.42 p-value: 0.00000    1.0 p-value: 0.00000   \n",
       "rain               0.17 p-value: 0.02381  -0.09 p-value: 0.04796   \n",
       "rooms             -0.03 p-value: 0.70203  -0.27 p-value: 0.00000   \n",
       "\n",
       "                                    rain                   rooms  \n",
       "Bioindicator      -0.13 p-value: 0.65244   0.16 p-value: 0.00314  \n",
       "Coliform          -0.06 p-value: 0.29490   -0.2 p-value: 0.00002  \n",
       "Bioindicator UV+   0.17 p-value: 0.02381  -0.03 p-value: 0.70203  \n",
       "total             -0.09 p-value: 0.04796  -0.27 p-value: 0.00000  \n",
       "rain                1.0 p-value: 0.00000  -0.43 p-value: 0.00951  \n",
       "rooms             -0.43 p-value: 0.00951    1.0 p-value: 0.00000  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_vars=['Bioindicator', 'Coliform', 'Bioindicator UV+',  'total' ,'rain', 'rooms']\n",
    "p_df = A[main_vars]\n",
    "\n",
    "rho = p_df.corr()\n",
    "pval = p_df.corr(method=lambda x, y: spearmanr(x, y)[1]) - np.eye(*rho.shape)\n",
    "p = pval.applymap(lambda x: ''.join([' p-value: ', str(format(x, \".5f\"))]))\n",
    "rho.round(2).astype(str) + p"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1882670d",
   "metadata": {},
   "source": [
    "## Correlation with Spearman Method cumulative 72 Hours"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "39bb3e59",
   "metadata": {},
   "outputs": [],
   "source": [
    "A=pd.read_pickle(\"data/allyears_72h_cumulative.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e40eaa71",
   "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>Bioindicator</th>\n",
       "      <th>Coliform</th>\n",
       "      <th>Bioindicator UV+</th>\n",
       "      <th>total</th>\n",
       "      <th>rain</th>\n",
       "      <th>rooms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bioindicator</th>\n",
       "      <td>1.0***</td>\n",
       "      <td>0.09***</td>\n",
       "      <td>0.99***</td>\n",
       "      <td>0.16***</td>\n",
       "      <td>-0.16</td>\n",
       "      <td>0.16***</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coliform</th>\n",
       "      <td>0.09***</td>\n",
       "      <td>1.0***</td>\n",
       "      <td>0.46***</td>\n",
       "      <td>0.68***</td>\n",
       "      <td>-0.12</td>\n",
       "      <td>-0.2***</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bioindicator UV+</th>\n",
       "      <td>0.99***</td>\n",
       "      <td>0.46***</td>\n",
       "      <td>1.0***</td>\n",
       "      <td>0.42***</td>\n",
       "      <td>0.13***</td>\n",
       "      <td>-0.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total</th>\n",
       "      <td>0.16***</td>\n",
       "      <td>0.68***</td>\n",
       "      <td>0.42***</td>\n",
       "      <td>1.0***</td>\n",
       "      <td>-0.12</td>\n",
       "      <td>-0.27***</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rain</th>\n",
       "      <td>-0.16</td>\n",
       "      <td>-0.12</td>\n",
       "      <td>0.13***</td>\n",
       "      <td>-0.12</td>\n",
       "      <td>1.0***</td>\n",
       "      <td>-0.41***</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rooms</th>\n",
       "      <td>0.16***</td>\n",
       "      <td>-0.2***</td>\n",
       "      <td>-0.03</td>\n",
       "      <td>-0.27***</td>\n",
       "      <td>-0.41***</td>\n",
       "      <td>1.0***</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Bioindicator Coliform Bioindicator UV+     total      rain  \\\n",
       "Bioindicator           1.0***  0.09***          0.99***   0.16***     -0.16   \n",
       "Coliform              0.09***   1.0***          0.46***   0.68***     -0.12   \n",
       "Bioindicator UV+      0.99***  0.46***           1.0***   0.42***   0.13***   \n",
       "total                 0.16***  0.68***          0.42***    1.0***     -0.12   \n",
       "rain                    -0.16    -0.12          0.13***     -0.12    1.0***   \n",
       "rooms                 0.16***  -0.2***            -0.03  -0.27***  -0.41***   \n",
       "\n",
       "                     rooms  \n",
       "Bioindicator       0.16***  \n",
       "Coliform           -0.2***  \n",
       "Bioindicator UV+     -0.03  \n",
       "total             -0.27***  \n",
       "rain              -0.41***  \n",
       "rooms               1.0***  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from scipy.stats import spearmanr\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "def calculate_pvalues(df):\n",
    "    df = df.dropna()._get_numeric_data()\n",
    "    dfcols = pd.DataFrame(columns=df.columns)\n",
    "    pvalues = dfcols.transpose().join(dfcols, how='outer')\n",
    "    for r in df.columns:\n",
    "        for c in df.columns:\n",
    "            pvalues[r][c] = round(pearsonr(df[r], df[c])[1], 4)\n",
    "    return pvalues\n",
    "\n",
    "\n",
    "main_vars=['Bioindicator', 'Coliform', 'Bioindicator UV+',  'total' ,'rain', 'rooms']\n",
    "p_df = A[main_vars]\n",
    "\n",
    "rho = p_df.corr()\n",
    "pval = p_df.corr(method=lambda x, y: spearmanr(x, y)[1]) - np.eye(*rho.shape)\n",
    "p = pval.applymap(lambda x: ''.join(['*' for t in [0.01,0.05,0.1] if x<=t]))\n",
    "rho.round(2).astype(str) + p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e199f407",
   "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>Bioindicator</th>\n",
       "      <th>Coliform</th>\n",
       "      <th>Bioindicator UV+</th>\n",
       "      <th>total</th>\n",
       "      <th>rain</th>\n",
       "      <th>rooms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bioindicator</th>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>0.09 p-value: 0.00001</td>\n",
       "      <td>0.99 p-value: 0.00000</td>\n",
       "      <td>0.16 p-value: 0.00000</td>\n",
       "      <td>-0.16 p-value: 0.68576</td>\n",
       "      <td>0.16 p-value: 0.00314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coliform</th>\n",
       "      <td>0.09 p-value: 0.00001</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>0.46 p-value: 0.00004</td>\n",
       "      <td>0.68 p-value: 0.00000</td>\n",
       "      <td>-0.12 p-value: 0.76425</td>\n",
       "      <td>-0.2 p-value: 0.00002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bioindicator UV+</th>\n",
       "      <td>0.99 p-value: 0.00000</td>\n",
       "      <td>0.46 p-value: 0.00004</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>0.42 p-value: 0.00000</td>\n",
       "      <td>0.13 p-value: 0.00963</td>\n",
       "      <td>-0.03 p-value: 0.70203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>total</th>\n",
       "      <td>0.16 p-value: 0.00000</td>\n",
       "      <td>0.68 p-value: 0.00000</td>\n",
       "      <td>0.42 p-value: 0.00000</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>-0.12 p-value: 0.63589</td>\n",
       "      <td>-0.27 p-value: 0.00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rain</th>\n",
       "      <td>-0.16 p-value: 0.68576</td>\n",
       "      <td>-0.12 p-value: 0.76425</td>\n",
       "      <td>0.13 p-value: 0.00963</td>\n",
       "      <td>-0.12 p-value: 0.63589</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "      <td>-0.41 p-value: 0.00658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rooms</th>\n",
       "      <td>0.16 p-value: 0.00314</td>\n",
       "      <td>-0.2 p-value: 0.00002</td>\n",
       "      <td>-0.03 p-value: 0.70203</td>\n",
       "      <td>-0.27 p-value: 0.00000</td>\n",
       "      <td>-0.41 p-value: 0.00658</td>\n",
       "      <td>1.0 p-value: 0.00000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Bioindicator                Coliform  \\\n",
       "Bioindicator        1.0 p-value: 0.00000   0.09 p-value: 0.00001   \n",
       "Coliform           0.09 p-value: 0.00001    1.0 p-value: 0.00000   \n",
       "Bioindicator UV+   0.99 p-value: 0.00000   0.46 p-value: 0.00004   \n",
       "total              0.16 p-value: 0.00000   0.68 p-value: 0.00000   \n",
       "rain              -0.16 p-value: 0.68576  -0.12 p-value: 0.76425   \n",
       "rooms              0.16 p-value: 0.00314   -0.2 p-value: 0.00002   \n",
       "\n",
       "                        Bioindicator UV+                   total  \\\n",
       "Bioindicator       0.99 p-value: 0.00000   0.16 p-value: 0.00000   \n",
       "Coliform           0.46 p-value: 0.00004   0.68 p-value: 0.00000   \n",
       "Bioindicator UV+    1.0 p-value: 0.00000   0.42 p-value: 0.00000   \n",
       "total              0.42 p-value: 0.00000    1.0 p-value: 0.00000   \n",
       "rain               0.13 p-value: 0.00963  -0.12 p-value: 0.63589   \n",
       "rooms             -0.03 p-value: 0.70203  -0.27 p-value: 0.00000   \n",
       "\n",
       "                                    rain                   rooms  \n",
       "Bioindicator      -0.16 p-value: 0.68576   0.16 p-value: 0.00314  \n",
       "Coliform          -0.12 p-value: 0.76425   -0.2 p-value: 0.00002  \n",
       "Bioindicator UV+   0.13 p-value: 0.00963  -0.03 p-value: 0.70203  \n",
       "total             -0.12 p-value: 0.63589  -0.27 p-value: 0.00000  \n",
       "rain                1.0 p-value: 0.00000  -0.41 p-value: 0.00658  \n",
       "rooms             -0.41 p-value: 0.00658    1.0 p-value: 0.00000  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_vars=['Bioindicator', 'Coliform', 'Bioindicator UV+',  'total' ,'rain', 'rooms']\n",
    "p_df = A[main_vars]\n",
    "\n",
    "rho = p_df.corr()\n",
    "pval = p_df.corr(method=lambda x, y: spearmanr(x, y)[1]) - np.eye(*rho.shape)\n",
    "p = pval.applymap(lambda x: ''.join([' p-value: ', str(format(x, \".5f\"))]))\n",
    "rho.round(2).astype(str) + p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "d4107da1",
   "metadata": {},
   "outputs": [],
   "source": [
    "p = pval.applymap(lambda x: ''.join(str(format(x, \".5f\"))))\n",
    "\n",
    "rho.to_csv('tables/Spearman_correlation.csv')\n",
    "p.to_csv('tables/Spearman_p_values.csv')\n",
    "#with pd.ExcelWriter('tables/Spearman_correlation.xlsx') as writer:  \n",
    "#    rho.to_(writer, sheet_name='Correlations')\n",
    "#    p.to_excel(writer, sheet_name='P-Value')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (myenv)",
   "language": "python",
   "name": "myenv"
  },
  "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.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}