metadata_compile_16S.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "library(dplyr)\n",
    "library(tidyr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merging metadata tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "m <- '~/data/awtp2_16S/metadata.tsv'\n",
    "fgl_data <- '~/data/awtp2_16S/19_0708_KNLK_SD_DNA.csv'\n",
    "mwq <- '~/data/awtp2_16S/mwq_022120.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#what should it all be called?\n",
    "NTB<- \"90%_conditioned/10%_conventional\" #90/10 blends\n",
    "half<- \"45%_conditioned/55%_conventional\" #45/55 blends\n",
    "dpwf<- \"100%_conditioned\"   #source & merge\n",
    "mwtp_s<-\"100%_conventional\" #source & merge\n",
    "\n",
    "mwtp<- \"100%_conventional_water\" # influent composition\n",
    "all_pure<-\"100%_conditioned_water\"  #influent composition\n",
    "\n",
    "all_conv<- \"100%_conventional\" #location_code\n",
    "dpwf_s<- \"100%_conditioned\" #location_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 23</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>id</th><th scope=col>id_full</th><th scope=col>count_raw_reads</th><th scope=col>barcode_forward</th><th scope=col>barcode_reverse</th><th scope=col>plate</th><th scope=col>well</th><th scope=col>metagenomic_sample</th><th scope=col>sample_code_full_and_batch</th><th scope=col>sample_date</th><th scope=col>⋯</th><th scope=col>location_code</th><th scope=col>sample_type</th><th scope=col>sampler_name</th><th scope=col>Ext_batch</th><th scope=col>Extraction_Date</th><th scope=col>Ext_num_unique</th><th scope=col>Microconcentrated.</th><th scope=col>DNA_Ext_conc_ngperuL</th><th scope=col>kit</th><th scope=col>exp_merge</th></tr>\n",
       "\t<tr><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>⋯</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>SD001</td><td>KNLK_SD001</td><td>21320</td><td>TACGAGAC</td><td>GAGCTCAT</td><td>1</td><td>D2</td><td>Y</td><td>EXP_0_field_bulk_9_NA_19          </td><td>2018-04-23</td><td>⋯</td><td>field_blank      </td><td>DEUF   </td><td>Lauren_Kennedy</td><td>19 </td><td>9/8/2018 </td><td>183</td><td>Y</td><td>0.126</td><td>PowerSoil Pro</td><td>Y</td></tr>\n",
       "\t<tr><td>SD002</td><td>KNLK_SD002</td><td>43792</td><td>CTACTATA</td><td>CTCTAGAG</td><td>1</td><td>A3</td><td>Y</td><td>EXP_0_extraction_NA_NA_NA_11      </td><td>NA</td><td>⋯</td><td>negative_control </td><td>NA     </td><td>NA            </td><td>11 </td><td>7/7/2018 </td><td>94 </td><td>Y</td><td>0.226</td><td>PowerSoil Pro</td><td>Y</td></tr>\n",
       "\t<tr><td>SD003</td><td>KNLK_SD003</td><td>52728</td><td>CGTTACTA</td><td>CTCTAGAG</td><td>1</td><td>B3</td><td>Y</td><td>DWDS_F_utility_bulk_9_A_12        </td><td>2018-03-28</td><td>⋯</td><td>100%_conventional</td><td>DEUF   </td><td>Lauren_Kennedy</td><td>12 </td><td>7/9/2018 </td><td>99 </td><td>Y</td><td>0.252</td><td>PowerSoil Pro</td><td>N</td></tr>\n",
       "\t<tr><td>SD004</td><td>KNLK_SD004</td><td>35359</td><td>AGAGTCAC</td><td>CTCTAGAG</td><td>1</td><td>C3</td><td>Y</td><td>SDS_F_5_biofilm_9_NA_8            </td><td>2018-04-09</td><td>⋯</td><td>Loop_5           </td><td>biofilm</td><td>Lauren_Kennedy</td><td>8  </td><td>7/6/2018 </td><td>51 </td><td>Y</td><td>0.302</td><td>PowerSoil Pro</td><td>N</td></tr>\n",
       "\t<tr><td>SD005</td><td>KNLK_SD005</td><td>35380</td><td>TACGAGAC</td><td>CTCTAGAG</td><td>1</td><td>D3</td><td>Y</td><td>DWDS_F_328_bulk_5_A_13            </td><td>2018-04-19</td><td>⋯</td><td>DWDS_328         </td><td>DEUF   </td><td>Lauren_Kennedy</td><td>13 </td><td>8/23/2018</td><td>116</td><td>Y</td><td>0.307</td><td>PowerSoil Pro</td><td>N</td></tr>\n",
       "\t<tr><td>SD006</td><td>KNLK_SD006</td><td>30734</td><td>TACGAGAC</td><td>CATGAGGA</td><td>1</td><td>D5</td><td>Y</td><td>AWTP_2_RO2_biofilm_sep_1_A_RO1_A57</td><td>2018-01-10</td><td>⋯</td><td>RO_BF_separator  </td><td>biofilm</td><td>Rose_Kantor   </td><td>RO1</td><td>9/13/2018</td><td>A57</td><td>Y</td><td>1.17 </td><td>Powersoil Pro</td><td>N</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 23\n",
       "\\begin{tabular}{r|lllllllllllllllllllllll}\n",
       " id & id\\_full & count\\_raw\\_reads & barcode\\_forward & barcode\\_reverse & plate & well & metagenomic\\_sample & sample\\_code\\_full\\_and\\_batch & sample\\_date & batch\\_sample\\_date & project\\_type & sample\\_or\\_control & location\\_code & sample\\_type & sampler\\_name & Ext\\_batch & Extraction\\_Date & Ext\\_num\\_unique & Microconcentrated. & DNA\\_Ext\\_conc\\_ngperuL & kit & exp\\_merge\\\\\n",
       " <fct> & <fct> & <int> & <fct> & <fct> & <fct> & <fct> & <fct> & <fct> & <date> & <fct> & <fct> & <fct> & <chr> & <fct> & <fct> & <fct> & <fct> & <fct> & <fct> & <fct> & <fct> & <chr>\\\\\n",
       "\\hline\n",
       "\t SD001 & KNLK\\_SD001 & 21320 & TACGAGAC & GAGCTCAT & 1 & D2 & Y & EXP\\_0\\_field\\_bulk\\_9\\_NA\\_19           & 2018-04-23 & 4/23/2018 & Experimental & control & field\\_blank       & DEUF    & Lauren\\_Kennedy & 19  & 9/8/2018  & 183 & Y & 0.126 & PowerSoil Pro & Y\\\\\n",
       "\t SD002 & KNLK\\_SD002 & 43792 & CTACTATA & CTCTAGAG & 1 & A3 & Y & EXP\\_0\\_extraction\\_NA\\_NA\\_NA\\_11       & NA & NA        & Experimental & control & negative\\_control  & NA      & NA             & 11  & 7/7/2018  & 94  & Y & 0.226 & PowerSoil Pro & Y\\\\\n",
       "\t SD003 & KNLK\\_SD003 & 52728 & CGTTACTA & CTCTAGAG & 1 & B3 & Y & DWDS\\_F\\_utility\\_bulk\\_9\\_A\\_12         & 2018-03-28 & NA        & Pipe\\_Loop    & sample  & 100\\%\\_conventional & DEUF    & Lauren\\_Kennedy & 12  & 7/9/2018  & 99  & Y & 0.252 & PowerSoil Pro & N\\\\\n",
       "\t SD004 & KNLK\\_SD004 & 35359 & AGAGTCAC & CTCTAGAG & 1 & C3 & Y & SDS\\_F\\_5\\_biofilm\\_9\\_NA\\_8             & 2018-04-09 & 4/9/2018  & Pipe\\_Loop    & sample  & Loop\\_5            & biofilm & Lauren\\_Kennedy & 8   & 7/6/2018  & 51  & Y & 0.302 & PowerSoil Pro & N\\\\\n",
       "\t SD005 & KNLK\\_SD005 & 35380 & TACGAGAC & CTCTAGAG & 1 & D3 & Y & DWDS\\_F\\_328\\_bulk\\_5\\_A\\_13             & 2018-04-19 & NA        & Full\\_Scale   & sample  & DWDS\\_328          & DEUF    & Lauren\\_Kennedy & 13  & 8/23/2018 & 116 & Y & 0.307 & PowerSoil Pro & N\\\\\n",
       "\t SD006 & KNLK\\_SD006 & 30734 & TACGAGAC & CATGAGGA & 1 & D5 & Y & AWTP\\_2\\_RO2\\_biofilm\\_sep\\_1\\_A\\_RO1\\_A57 & 2018-01-10 & NA        & DPWF         & sample  & RO\\_BF\\_separator   & biofilm & Rose\\_Kantor    & RO1 & 9/13/2018 & A57 & Y & 1.17  & Powersoil Pro & N\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 23\n",
       "\n",
       "| id &lt;fct&gt; | id_full &lt;fct&gt; | count_raw_reads &lt;int&gt; | barcode_forward &lt;fct&gt; | barcode_reverse &lt;fct&gt; | plate &lt;fct&gt; | well &lt;fct&gt; | metagenomic_sample &lt;fct&gt; | sample_code_full_and_batch &lt;fct&gt; | sample_date &lt;date&gt; | ⋯ ⋯ | location_code &lt;chr&gt; | sample_type &lt;fct&gt; | sampler_name &lt;fct&gt; | Ext_batch &lt;fct&gt; | Extraction_Date &lt;fct&gt; | Ext_num_unique &lt;fct&gt; | Microconcentrated. &lt;fct&gt; | DNA_Ext_conc_ngperuL &lt;fct&gt; | kit &lt;fct&gt; | exp_merge &lt;chr&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
       "| SD001 | KNLK_SD001 | 21320 | TACGAGAC | GAGCTCAT | 1 | D2 | Y | EXP_0_field_bulk_9_NA_19           | 2018-04-23 | ⋯ | field_blank       | DEUF    | Lauren_Kennedy | 19  | 9/8/2018  | 183 | Y | 0.126 | PowerSoil Pro | Y |\n",
       "| SD002 | KNLK_SD002 | 43792 | CTACTATA | CTCTAGAG | 1 | A3 | Y | EXP_0_extraction_NA_NA_NA_11       | NA | ⋯ | negative_control  | NA      | NA             | 11  | 7/7/2018  | 94  | Y | 0.226 | PowerSoil Pro | Y |\n",
       "| SD003 | KNLK_SD003 | 52728 | CGTTACTA | CTCTAGAG | 1 | B3 | Y | DWDS_F_utility_bulk_9_A_12         | 2018-03-28 | ⋯ | 100%_conventional | DEUF    | Lauren_Kennedy | 12  | 7/9/2018  | 99  | Y | 0.252 | PowerSoil Pro | N |\n",
       "| SD004 | KNLK_SD004 | 35359 | AGAGTCAC | CTCTAGAG | 1 | C3 | Y | SDS_F_5_biofilm_9_NA_8             | 2018-04-09 | ⋯ | Loop_5            | biofilm | Lauren_Kennedy | 8   | 7/6/2018  | 51  | Y | 0.302 | PowerSoil Pro | N |\n",
       "| SD005 | KNLK_SD005 | 35380 | TACGAGAC | CTCTAGAG | 1 | D3 | Y | DWDS_F_328_bulk_5_A_13             | 2018-04-19 | ⋯ | DWDS_328          | DEUF    | Lauren_Kennedy | 13  | 8/23/2018 | 116 | Y | 0.307 | PowerSoil Pro | N |\n",
       "| SD006 | KNLK_SD006 | 30734 | TACGAGAC | CATGAGGA | 1 | D5 | Y | AWTP_2_RO2_biofilm_sep_1_A_RO1_A57 | 2018-01-10 | ⋯ | RO_BF_separator   | biofilm | Rose_Kantor    | RO1 | 9/13/2018 | A57 | Y | 1.17  | Powersoil Pro | N |\n",
       "\n"
      ],
      "text/plain": [
       "  id    id_full    count_raw_reads barcode_forward barcode_reverse plate well\n",
       "1 SD001 KNLK_SD001 21320           TACGAGAC        GAGCTCAT        1     D2  \n",
       "2 SD002 KNLK_SD002 43792           CTACTATA        CTCTAGAG        1     A3  \n",
       "3 SD003 KNLK_SD003 52728           CGTTACTA        CTCTAGAG        1     B3  \n",
       "4 SD004 KNLK_SD004 35359           AGAGTCAC        CTCTAGAG        1     C3  \n",
       "5 SD005 KNLK_SD005 35380           TACGAGAC        CTCTAGAG        1     D3  \n",
       "6 SD006 KNLK_SD006 30734           TACGAGAC        CATGAGGA        1     D5  \n",
       "  metagenomic_sample sample_code_full_and_batch         sample_date ⋯\n",
       "1 Y                  EXP_0_field_bulk_9_NA_19           2018-04-23  ⋯\n",
       "2 Y                  EXP_0_extraction_NA_NA_NA_11       <NA>        ⋯\n",
       "3 Y                  DWDS_F_utility_bulk_9_A_12         2018-03-28  ⋯\n",
       "4 Y                  SDS_F_5_biofilm_9_NA_8             2018-04-09  ⋯\n",
       "5 Y                  DWDS_F_328_bulk_5_A_13             2018-04-19  ⋯\n",
       "6 Y                  AWTP_2_RO2_biofilm_sep_1_A_RO1_A57 2018-01-10  ⋯\n",
       "  location_code     sample_type sampler_name   Ext_batch Extraction_Date\n",
       "1 field_blank       DEUF        Lauren_Kennedy 19        9/8/2018       \n",
       "2 negative_control  NA          NA             11        7/7/2018       \n",
       "3 100%_conventional DEUF        Lauren_Kennedy 12        7/9/2018       \n",
       "4 Loop_5            biofilm     Lauren_Kennedy 8         7/6/2018       \n",
       "5 DWDS_328          DEUF        Lauren_Kennedy 13        8/23/2018      \n",
       "6 RO_BF_separator   biofilm     Rose_Kantor    RO1       9/13/2018      \n",
       "  Ext_num_unique Microconcentrated. DNA_Ext_conc_ngperuL kit          \n",
       "1 183            Y                  0.126                PowerSoil Pro\n",
       "2 94             Y                  0.226                PowerSoil Pro\n",
       "3 99             Y                  0.252                PowerSoil Pro\n",
       "4 51             Y                  0.302                PowerSoil Pro\n",
       "5 116            Y                  0.307                PowerSoil Pro\n",
       "6 A57            Y                  1.17                 Powersoil Pro\n",
       "  exp_merge\n",
       "1 Y        \n",
       "2 Y        \n",
       "3 N        \n",
       "4 N        \n",
       "5 N        \n",
       "6 N        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "seq_data=read.table(m, header=TRUE, sep=\"\\t\")\n",
    "seq_data$sample_date <- as.Date(seq_data$sample_date, \"%m/%d/%Y\")\n",
    "seq_data$location_code<- gsub(\"Loop_MWTP_inf\",all_conv,seq_data$location_code)\n",
    "seq_data$location_code<- gsub(\"Loop_DPWF_inf\",dpwf_s,seq_data$location_code)\n",
    "seq_data$location_code<- gsub(\"DWDS_282B\",\"DWDS_282\",seq_data$location_code)\n",
    "seq_data$location_code<- gsub(\"WW_3ary\",\"WW_tertiary\",seq_data$location_code)\n",
    "\n",
    "#label all samples as \"N\" for non-experimental\n",
    "seq_data$exp_merge<-\"N\"\n",
    "#label experimental samples (negative controls and positive controls) as experimental (because these rows won't have water quality data to merge)\n",
    "seq_data[((seq_data$location_code==\"negative_control\")| (seq_data$location_code==\"positive_control\"))|(seq_data$location_code==\"field_blank\"),]$exp_merge<-\"Y\"\n",
    "\n",
    "head(seq_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 7</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>id</th><th scope=col>buffer</th><th scope=col>volume_uL</th><th scope=col>Avg.Size</th><th scope=col>ng.uL</th><th scope=col>nM</th><th scope=col>uL_for_200ng</th></tr>\n",
       "\t<tr><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>KNLK_SD185</td><td>18 mM TAE bufer                </td><td>15</td><td>385</td><td> 0.1300</td><td>  0.5556</td><td>359.971202</td></tr>\n",
       "\t<tr><td>KNLK_SD003</td><td>20 mM tris-HCl with 0.1 mM EDTA</td><td>50</td><td>393</td><td>10.0087</td><td> 41.8977</td><td>  4.773532</td></tr>\n",
       "\t<tr><td>KNLK_SD004</td><td>20 mM tris-HCl with 0.1 mM EDTA</td><td>50</td><td>390</td><td> 7.1627</td><td> 30.2149</td><td>  6.619251</td></tr>\n",
       "\t<tr><td>KNLK_SD005</td><td>20 mM tris-HCl with 0.1 mM EDTA</td><td>50</td><td>390</td><td>19.2228</td><td> 81.0844</td><td>  2.466566</td></tr>\n",
       "\t<tr><td>KNLK_SD006</td><td>20 mM tris-HCl with 0.1 mM EDTA</td><td>50</td><td>393</td><td>12.7806</td><td> 53.5584</td><td>  3.734242</td></tr>\n",
       "\t<tr><td>KNLK_SD007</td><td>20 mM tris-HCl with 0.1 mM EDTA</td><td>50</td><td>396</td><td>36.0204</td><td>149.5023</td><td>  1.337772</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 7\n",
       "\\begin{tabular}{r|lllllll}\n",
       " id & buffer & volume\\_uL & Avg.Size & ng.uL & nM & uL\\_for\\_200ng\\\\\n",
       " <fct> & <fct> & <int> & <int> & <dbl> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t KNLK\\_SD185 & 18 mM TAE bufer                 & 15 & 385 &  0.1300 &   0.5556 & 359.971202\\\\\n",
       "\t KNLK\\_SD003 & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 393 & 10.0087 &  41.8977 &   4.773532\\\\\n",
       "\t KNLK\\_SD004 & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 390 &  7.1627 &  30.2149 &   6.619251\\\\\n",
       "\t KNLK\\_SD005 & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 390 & 19.2228 &  81.0844 &   2.466566\\\\\n",
       "\t KNLK\\_SD006 & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 393 & 12.7806 &  53.5584 &   3.734242\\\\\n",
       "\t KNLK\\_SD007 & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 396 & 36.0204 & 149.5023 &   1.337772\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 7\n",
       "\n",
       "| id &lt;fct&gt; | buffer &lt;fct&gt; | volume_uL &lt;int&gt; | Avg.Size &lt;int&gt; | ng.uL &lt;dbl&gt; | nM &lt;dbl&gt; | uL_for_200ng &lt;dbl&gt; |\n",
       "|---|---|---|---|---|---|---|\n",
       "| KNLK_SD185 | 18 mM TAE bufer                 | 15 | 385 |  0.1300 |   0.5556 | 359.971202 |\n",
       "| KNLK_SD003 | 20 mM tris-HCl with 0.1 mM EDTA | 50 | 393 | 10.0087 |  41.8977 |   4.773532 |\n",
       "| KNLK_SD004 | 20 mM tris-HCl with 0.1 mM EDTA | 50 | 390 |  7.1627 |  30.2149 |   6.619251 |\n",
       "| KNLK_SD005 | 20 mM tris-HCl with 0.1 mM EDTA | 50 | 390 | 19.2228 |  81.0844 |   2.466566 |\n",
       "| KNLK_SD006 | 20 mM tris-HCl with 0.1 mM EDTA | 50 | 393 | 12.7806 |  53.5584 |   3.734242 |\n",
       "| KNLK_SD007 | 20 mM tris-HCl with 0.1 mM EDTA | 50 | 396 | 36.0204 | 149.5023 |   1.337772 |\n",
       "\n"
      ],
      "text/plain": [
       "  id         buffer                          volume_uL Avg.Size ng.uL  \n",
       "1 KNLK_SD185 18 mM TAE bufer                 15        385       0.1300\n",
       "2 KNLK_SD003 20 mM tris-HCl with 0.1 mM EDTA 50        393      10.0087\n",
       "3 KNLK_SD004 20 mM tris-HCl with 0.1 mM EDTA 50        390       7.1627\n",
       "4 KNLK_SD005 20 mM tris-HCl with 0.1 mM EDTA 50        390      19.2228\n",
       "5 KNLK_SD006 20 mM tris-HCl with 0.1 mM EDTA 50        393      12.7806\n",
       "6 KNLK_SD007 20 mM tris-HCl with 0.1 mM EDTA 50        396      36.0204\n",
       "  nM       uL_for_200ng\n",
       "1   0.5556 359.971202  \n",
       "2  41.8977   4.773532  \n",
       "3  30.2149   6.619251  \n",
       "4  81.0844   2.466566  \n",
       "5  53.5584   3.734242  \n",
       "6 149.5023   1.337772  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ext_data <- read.csv(fgl_data, header=TRUE, row.names = NULL)\n",
    "head(ext_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "wq_data <- read.csv(mwq, header=TRUE, row.names = NULL)\n",
    "wq_data <- select(wq_data, select=-X)\n",
    "wq_data$sample_date <- as.Date(wq_data$sample_date, \"%Y-%m-%d\")\n",
    "wq_data$location_code <- as.character(wq_data$location_code)\n",
    "wq_data$location_code<- gsub(\"WW_Tertiary\",\"WW_tertiary\",wq_data$location_code)\n",
    "wq_data$location_code<- gsub(\"DPWF_inf\",'cond_perm',wq_data$location_code)\n",
    "wq_data$sample_type<- gsub(\"bulkwater_grab\",'DEUF',wq_data$sample_type)\n",
    "wq_data<-subset(wq_data, select=-c(project_type, merge_id))\n",
    "wq_data=subset(wq_data, is.na(wq_data$experimental_detail))\n",
    "wq_data$exp_merge<-\"N\" #sample date and location code shared by some samples so making it easier to merge seq_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "'DEUF'"
      ],
      "text/latex": [
       "'DEUF'"
      ],
      "text/markdown": [
       "'DEUF'"
      ],
      "text/plain": [
       "[1] \"DEUF\""
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "unique(wq_data$sample_type)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>DEUF</li>\n",
       "\t<li>&lt;NA&gt;</li>\n",
       "\t<li>biofilm</li>\n",
       "</ol>\n",
       "\n",
       "<details>\n",
       "\t<summary style=display:list-item;cursor:pointer>\n",
       "\t\t<strong>Levels</strong>:\n",
       "\t</summary>\n",
       "\t<ol class=list-inline>\n",
       "\t\t<li>'biofilm'</li>\n",
       "\t\t<li>'DEUF'</li>\n",
       "\t</ol>\n",
       "</details>"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item DEUF\n",
       "\\item <NA>\n",
       "\\item biofilm\n",
       "\\end{enumerate*}\n",
       "\n",
       "\\emph{Levels}: \\begin{enumerate*}\n",
       "\\item 'biofilm'\n",
       "\\item 'DEUF'\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. DEUF\n",
       "2. &lt;NA&gt;\n",
       "3. biofilm\n",
       "\n",
       "\n",
       "\n",
       "**Levels**: 1. 'biofilm'\n",
       "2. 'DEUF'\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] DEUF    <NA>    biofilm\n",
       "Levels: biofilm DEUF"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "unique(seq_data$sample_type)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Joining, by = c(\"sample_date\", \"location_code\", \"sample_type\", \"exp_merge\")\n",
      "Warning message:\n",
      "“Column `sample_type` joining factor and character vector, coercing into character vector”"
     ]
    }
   ],
   "source": [
    "table_data <- merge(seq_data, ext_data, by.x='id_full', by.y='id', all.x)\n",
    "table_data <- left_join(table_data,wq_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>'id'</li>\n",
       "\t<li>'buffer'</li>\n",
       "\t<li>'volume_uL'</li>\n",
       "\t<li>'Avg.Size'</li>\n",
       "\t<li>'ng.uL'</li>\n",
       "\t<li>'nM'</li>\n",
       "\t<li>'uL_for_200ng'</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 'id'\n",
       "\\item 'buffer'\n",
       "\\item 'volume\\_uL'\n",
       "\\item 'Avg.Size'\n",
       "\\item 'ng.uL'\n",
       "\\item 'nM'\n",
       "\\item 'uL\\_for\\_200ng'\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 'id'\n",
       "2. 'buffer'\n",
       "3. 'volume_uL'\n",
       "4. 'Avg.Size'\n",
       "5. 'ng.uL'\n",
       "6. 'nM'\n",
       "7. 'uL_for_200ng'\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] \"id\"           \"buffer\"       \"volume_uL\"    \"Avg.Size\"     \"ng.uL\"       \n",
       "[6] \"nM\"           \"uL_for_200ng\""
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "colnames(ext_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<ol class=list-inline>\n",
       "\t<li>204</li>\n",
       "\t<li>86</li>\n",
       "</ol>\n"
      ],
      "text/latex": [
       "\\begin{enumerate*}\n",
       "\\item 204\n",
       "\\item 86\n",
       "\\end{enumerate*}\n"
      ],
      "text/markdown": [
       "1. 204\n",
       "2. 86\n",
       "\n",
       "\n"
      ],
      "text/plain": [
       "[1] 204  86"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "dim(table_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 23</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>id</th><th scope=col>id_full</th><th scope=col>count_raw_reads</th><th scope=col>barcode_forward</th><th scope=col>barcode_reverse</th><th scope=col>plate</th><th scope=col>well</th><th scope=col>metagenomic_sample</th><th scope=col>sample_code_full_and_batch</th><th scope=col>sample_date</th><th scope=col>⋯</th><th scope=col>location_code</th><th scope=col>sample_type</th><th scope=col>sampler_name</th><th scope=col>Ext_batch</th><th scope=col>Extraction_Date</th><th scope=col>Ext_num_unique</th><th scope=col>Microconcentrated.</th><th scope=col>DNA_Ext_conc_ngperuL</th><th scope=col>kit</th><th scope=col>exp_merge</th></tr>\n",
       "\t<tr><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>⋯</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>SD001</td><td>KNLK_SD001</td><td>21320</td><td>TACGAGAC</td><td>GAGCTCAT</td><td>1</td><td>D2</td><td>Y</td><td>EXP_0_field_bulk_9_NA_19          </td><td>2018-04-23</td><td>⋯</td><td>field_blank      </td><td>DEUF   </td><td>Lauren_Kennedy</td><td>19 </td><td>9/8/2018 </td><td>183</td><td>Y</td><td>0.126</td><td>PowerSoil Pro</td><td>Y</td></tr>\n",
       "\t<tr><td>SD002</td><td>KNLK_SD002</td><td>43792</td><td>CTACTATA</td><td>CTCTAGAG</td><td>1</td><td>A3</td><td>Y</td><td>EXP_0_extraction_NA_NA_NA_11      </td><td>NA</td><td>⋯</td><td>negative_control </td><td>NA     </td><td>NA            </td><td>11 </td><td>7/7/2018 </td><td>94 </td><td>Y</td><td>0.226</td><td>PowerSoil Pro</td><td>Y</td></tr>\n",
       "\t<tr><td>SD003</td><td>KNLK_SD003</td><td>52728</td><td>CGTTACTA</td><td>CTCTAGAG</td><td>1</td><td>B3</td><td>Y</td><td>DWDS_F_utility_bulk_9_A_12        </td><td>2018-03-28</td><td>⋯</td><td>100%_conventional</td><td>DEUF   </td><td>Lauren_Kennedy</td><td>12 </td><td>7/9/2018 </td><td>99 </td><td>Y</td><td>0.252</td><td>PowerSoil Pro</td><td>N</td></tr>\n",
       "\t<tr><td>SD004</td><td>KNLK_SD004</td><td>35359</td><td>AGAGTCAC</td><td>CTCTAGAG</td><td>1</td><td>C3</td><td>Y</td><td>SDS_F_5_biofilm_9_NA_8            </td><td>2018-04-09</td><td>⋯</td><td>Loop_5           </td><td>biofilm</td><td>Lauren_Kennedy</td><td>8  </td><td>7/6/2018 </td><td>51 </td><td>Y</td><td>0.302</td><td>PowerSoil Pro</td><td>N</td></tr>\n",
       "\t<tr><td>SD005</td><td>KNLK_SD005</td><td>35380</td><td>TACGAGAC</td><td>CTCTAGAG</td><td>1</td><td>D3</td><td>Y</td><td>DWDS_F_328_bulk_5_A_13            </td><td>2018-04-19</td><td>⋯</td><td>DWDS_328         </td><td>DEUF   </td><td>Lauren_Kennedy</td><td>13 </td><td>8/23/2018</td><td>116</td><td>Y</td><td>0.307</td><td>PowerSoil Pro</td><td>N</td></tr>\n",
       "\t<tr><td>SD006</td><td>KNLK_SD006</td><td>30734</td><td>TACGAGAC</td><td>CATGAGGA</td><td>1</td><td>D5</td><td>Y</td><td>AWTP_2_RO2_biofilm_sep_1_A_RO1_A57</td><td>2018-01-10</td><td>⋯</td><td>RO_BF_separator  </td><td>biofilm</td><td>Rose_Kantor   </td><td>RO1</td><td>9/13/2018</td><td>A57</td><td>Y</td><td>1.17 </td><td>Powersoil Pro</td><td>N</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 23\n",
       "\\begin{tabular}{r|lllllllllllllllllllllll}\n",
       " id & id\\_full & count\\_raw\\_reads & barcode\\_forward & barcode\\_reverse & plate & well & metagenomic\\_sample & sample\\_code\\_full\\_and\\_batch & sample\\_date & batch\\_sample\\_date & project\\_type & sample\\_or\\_control & location\\_code & sample\\_type & sampler\\_name & Ext\\_batch & Extraction\\_Date & Ext\\_num\\_unique & Microconcentrated. & DNA\\_Ext\\_conc\\_ngperuL & kit & exp\\_merge\\\\\n",
       " <fct> & <fct> & <int> & <fct> & <fct> & <fct> & <fct> & <fct> & <fct> & <date> & <fct> & <fct> & <fct> & <chr> & <fct> & <fct> & <fct> & <fct> & <fct> & <fct> & <fct> & <fct> & <chr>\\\\\n",
       "\\hline\n",
       "\t SD001 & KNLK\\_SD001 & 21320 & TACGAGAC & GAGCTCAT & 1 & D2 & Y & EXP\\_0\\_field\\_bulk\\_9\\_NA\\_19           & 2018-04-23 & 4/23/2018 & Experimental & control & field\\_blank       & DEUF    & Lauren\\_Kennedy & 19  & 9/8/2018  & 183 & Y & 0.126 & PowerSoil Pro & Y\\\\\n",
       "\t SD002 & KNLK\\_SD002 & 43792 & CTACTATA & CTCTAGAG & 1 & A3 & Y & EXP\\_0\\_extraction\\_NA\\_NA\\_NA\\_11       & NA & NA        & Experimental & control & negative\\_control  & NA      & NA             & 11  & 7/7/2018  & 94  & Y & 0.226 & PowerSoil Pro & Y\\\\\n",
       "\t SD003 & KNLK\\_SD003 & 52728 & CGTTACTA & CTCTAGAG & 1 & B3 & Y & DWDS\\_F\\_utility\\_bulk\\_9\\_A\\_12         & 2018-03-28 & NA        & Pipe\\_Loop    & sample  & 100\\%\\_conventional & DEUF    & Lauren\\_Kennedy & 12  & 7/9/2018  & 99  & Y & 0.252 & PowerSoil Pro & N\\\\\n",
       "\t SD004 & KNLK\\_SD004 & 35359 & AGAGTCAC & CTCTAGAG & 1 & C3 & Y & SDS\\_F\\_5\\_biofilm\\_9\\_NA\\_8             & 2018-04-09 & 4/9/2018  & Pipe\\_Loop    & sample  & Loop\\_5            & biofilm & Lauren\\_Kennedy & 8   & 7/6/2018  & 51  & Y & 0.302 & PowerSoil Pro & N\\\\\n",
       "\t SD005 & KNLK\\_SD005 & 35380 & TACGAGAC & CTCTAGAG & 1 & D3 & Y & DWDS\\_F\\_328\\_bulk\\_5\\_A\\_13             & 2018-04-19 & NA        & Full\\_Scale   & sample  & DWDS\\_328          & DEUF    & Lauren\\_Kennedy & 13  & 8/23/2018 & 116 & Y & 0.307 & PowerSoil Pro & N\\\\\n",
       "\t SD006 & KNLK\\_SD006 & 30734 & TACGAGAC & CATGAGGA & 1 & D5 & Y & AWTP\\_2\\_RO2\\_biofilm\\_sep\\_1\\_A\\_RO1\\_A57 & 2018-01-10 & NA        & DPWF         & sample  & RO\\_BF\\_separator   & biofilm & Rose\\_Kantor    & RO1 & 9/13/2018 & A57 & Y & 1.17  & Powersoil Pro & N\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 23\n",
       "\n",
       "| id &lt;fct&gt; | id_full &lt;fct&gt; | count_raw_reads &lt;int&gt; | barcode_forward &lt;fct&gt; | barcode_reverse &lt;fct&gt; | plate &lt;fct&gt; | well &lt;fct&gt; | metagenomic_sample &lt;fct&gt; | sample_code_full_and_batch &lt;fct&gt; | sample_date &lt;date&gt; | ⋯ ⋯ | location_code &lt;chr&gt; | sample_type &lt;fct&gt; | sampler_name &lt;fct&gt; | Ext_batch &lt;fct&gt; | Extraction_Date &lt;fct&gt; | Ext_num_unique &lt;fct&gt; | Microconcentrated. &lt;fct&gt; | DNA_Ext_conc_ngperuL &lt;fct&gt; | kit &lt;fct&gt; | exp_merge &lt;chr&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
       "| SD001 | KNLK_SD001 | 21320 | TACGAGAC | GAGCTCAT | 1 | D2 | Y | EXP_0_field_bulk_9_NA_19           | 2018-04-23 | ⋯ | field_blank       | DEUF    | Lauren_Kennedy | 19  | 9/8/2018  | 183 | Y | 0.126 | PowerSoil Pro | Y |\n",
       "| SD002 | KNLK_SD002 | 43792 | CTACTATA | CTCTAGAG | 1 | A3 | Y | EXP_0_extraction_NA_NA_NA_11       | NA | ⋯ | negative_control  | NA      | NA             | 11  | 7/7/2018  | 94  | Y | 0.226 | PowerSoil Pro | Y |\n",
       "| SD003 | KNLK_SD003 | 52728 | CGTTACTA | CTCTAGAG | 1 | B3 | Y | DWDS_F_utility_bulk_9_A_12         | 2018-03-28 | ⋯ | 100%_conventional | DEUF    | Lauren_Kennedy | 12  | 7/9/2018  | 99  | Y | 0.252 | PowerSoil Pro | N |\n",
       "| SD004 | KNLK_SD004 | 35359 | AGAGTCAC | CTCTAGAG | 1 | C3 | Y | SDS_F_5_biofilm_9_NA_8             | 2018-04-09 | ⋯ | Loop_5            | biofilm | Lauren_Kennedy | 8   | 7/6/2018  | 51  | Y | 0.302 | PowerSoil Pro | N |\n",
       "| SD005 | KNLK_SD005 | 35380 | TACGAGAC | CTCTAGAG | 1 | D3 | Y | DWDS_F_328_bulk_5_A_13             | 2018-04-19 | ⋯ | DWDS_328          | DEUF    | Lauren_Kennedy | 13  | 8/23/2018 | 116 | Y | 0.307 | PowerSoil Pro | N |\n",
       "| SD006 | KNLK_SD006 | 30734 | TACGAGAC | CATGAGGA | 1 | D5 | Y | AWTP_2_RO2_biofilm_sep_1_A_RO1_A57 | 2018-01-10 | ⋯ | RO_BF_separator   | biofilm | Rose_Kantor    | RO1 | 9/13/2018 | A57 | Y | 1.17  | Powersoil Pro | N |\n",
       "\n"
      ],
      "text/plain": [
       "  id    id_full    count_raw_reads barcode_forward barcode_reverse plate well\n",
       "1 SD001 KNLK_SD001 21320           TACGAGAC        GAGCTCAT        1     D2  \n",
       "2 SD002 KNLK_SD002 43792           CTACTATA        CTCTAGAG        1     A3  \n",
       "3 SD003 KNLK_SD003 52728           CGTTACTA        CTCTAGAG        1     B3  \n",
       "4 SD004 KNLK_SD004 35359           AGAGTCAC        CTCTAGAG        1     C3  \n",
       "5 SD005 KNLK_SD005 35380           TACGAGAC        CTCTAGAG        1     D3  \n",
       "6 SD006 KNLK_SD006 30734           TACGAGAC        CATGAGGA        1     D5  \n",
       "  metagenomic_sample sample_code_full_and_batch         sample_date ⋯\n",
       "1 Y                  EXP_0_field_bulk_9_NA_19           2018-04-23  ⋯\n",
       "2 Y                  EXP_0_extraction_NA_NA_NA_11       <NA>        ⋯\n",
       "3 Y                  DWDS_F_utility_bulk_9_A_12         2018-03-28  ⋯\n",
       "4 Y                  SDS_F_5_biofilm_9_NA_8             2018-04-09  ⋯\n",
       "5 Y                  DWDS_F_328_bulk_5_A_13             2018-04-19  ⋯\n",
       "6 Y                  AWTP_2_RO2_biofilm_sep_1_A_RO1_A57 2018-01-10  ⋯\n",
       "  location_code     sample_type sampler_name   Ext_batch Extraction_Date\n",
       "1 field_blank       DEUF        Lauren_Kennedy 19        9/8/2018       \n",
       "2 negative_control  NA          NA             11        7/7/2018       \n",
       "3 100%_conventional DEUF        Lauren_Kennedy 12        7/9/2018       \n",
       "4 Loop_5            biofilm     Lauren_Kennedy 8         7/6/2018       \n",
       "5 DWDS_328          DEUF        Lauren_Kennedy 13        8/23/2018      \n",
       "6 RO_BF_separator   biofilm     Rose_Kantor    RO1       9/13/2018      \n",
       "  Ext_num_unique Microconcentrated. DNA_Ext_conc_ngperuL kit          \n",
       "1 183            Y                  0.126                PowerSoil Pro\n",
       "2 94             Y                  0.226                PowerSoil Pro\n",
       "3 99             Y                  0.252                PowerSoil Pro\n",
       "4 51             Y                  0.302                PowerSoil Pro\n",
       "5 116            Y                  0.307                PowerSoil Pro\n",
       "6 A57            Y                  1.17                 Powersoil Pro\n",
       "  exp_merge\n",
       "1 Y        \n",
       "2 Y        \n",
       "3 N        \n",
       "4 N        \n",
       "5 N        \n",
       "6 N        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "head(seq_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "ename": "ERROR",
     "evalue": "Error in eval(expr, envir, enclos): object 'table_data' not found\n",
     "output_type": "error",
     "traceback": [
      "Error in eval(expr, envir, enclos): object 'table_data' not found\nTraceback:\n"
     ]
    }
   ],
   "source": [
    "rownames(table_data) <- table_data$id_full\n",
    "\n",
    "# convert to correct class\n",
    "table_data$batch_sample_date <- as.Date(table_data$batch_sample_date, \"%m/%d/%Y\")\n",
    "table_data$sample_type <- as.factor(table_data$sample_type)\n",
    "table_data$location_code <- as.factor(table_data$location_code)\n",
    "table_data$Extraction_Date <- as.Date(table_data$Extraction_Date, \"%m/%d/%Y\")\n",
    "table_data$DNA_Ext_conc_ngperuL <- as.character(table_data$DNA_Ext_conc_ngperuL) # factor to character\n",
    "table_data$DNA_Ext_conc_ngperuL <- as.numeric(table_data$DNA_Ext_conc_ngperuL) # character to numeric/decimal\n",
    "\n",
    "#No wq data taken for pipe loops batch end on 10/23 or 2/19"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 11 × 86</caption>\n",
       "<thead>\n",
       "\t<tr><th></th><th scope=col>id_full</th><th scope=col>id</th><th scope=col>count_raw_reads</th><th scope=col>barcode_forward</th><th scope=col>barcode_reverse</th><th scope=col>plate</th><th scope=col>well</th><th scope=col>metagenomic_sample</th><th scope=col>sample_code_full_and_batch</th><th scope=col>sample_date</th><th scope=col>⋯</th><th scope=col>base_filename</th><th scope=col>FCM_notes</th><th scope=col>avg_cells_per_mL_gmean_SGPI</th><th scope=col>avg_cells_per_mL_gstd_SGPI</th><th scope=col>intra_ATP_gmean_nM</th><th scope=col>total_ATP_gmean_nM</th><th scope=col>extra_ATP_gmean_nM</th><th scope=col>intra_ATP_gstd_nM</th><th scope=col>total_ATP_gstd_nM</th><th scope=col>extra_ATP_gstd_nM</th></tr>\n",
       "\t<tr><th></th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>⋯</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>KNLK_SD028</th><td>KNLK_SD028</td><td>SD028</td><td>35200</td><td>CTACTATA</td><td>TAGACTGA</td><td>1</td><td>A11</td><td>Y</td><td>AWTP_2_BAC_bulk_5_A_2            </td><td>2017-12-14</td><td>⋯</td><td>17_1214_DPWF_3_GAC_SGPI_10x_      </td><td>NA</td><td>  475688.4</td><td>1.691495</td><td>0.11222116</td><td>0.13538441</td><td>0.023163254</td><td>1.450614</td><td>1.011282</td><td>1.039995</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD029</th><td>KNLK_SD029</td><td>SD029</td><td>30964</td><td>CGTTACTA</td><td>TAGACTGA</td><td>1</td><td>B11</td><td>Y</td><td>AWTP_3_inf_bulk_1_A_1            </td><td>2018-03-13</td><td>⋯</td><td>18_0313_ECAWPC_Influent_SGPI_10x_2</td><td>NA</td><td>12146298.6</td><td>1.074069</td><td>        NA</td><td>        NA</td><td>         NA</td><td>      NA</td><td>      NA</td><td>      NA</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD030</th><td>KNLK_SD030</td><td>SD030</td><td>38480</td><td>AGAGTCAC</td><td>TAGACTGA</td><td>1</td><td>C11</td><td>Y</td><td>AWTP_2_inf_bulk_4_A_1            </td><td>2017-11-14</td><td>⋯</td><td>17_1114_DPWF_1_Influent_SGPI_100x_</td><td>NA</td><td> 5461380.2</td><td>1.014661</td><td>0.55397549</td><td>0.65390039</td><td>0.099924893</td><td>1.591198</td><td>1.224577</td><td>1.016032</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD031</th><td>KNLK_SD031</td><td>SD031</td><td>37100</td><td>TACGAGAC</td><td>TAGACTGA</td><td>1</td><td>D11</td><td>Y</td><td>AWTP_2_inf_bulk_5_A_1            </td><td>2017-12-14</td><td>⋯</td><td>17_1214_DPWF_1_Influent_SGPI_100x_</td><td>NA</td><td> 6660896.6</td><td>1.070527</td><td>0.63648601</td><td>0.78860455</td><td>0.152118540</td><td>1.494909</td><td>1.081167</td><td>1.032391</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD032</th><td>KNLK_SD032</td><td>SD032</td><td>23905</td><td>ACGTCTCG</td><td>TAGACTGA</td><td>1</td><td>E11</td><td>Y</td><td>AWTP_2_RO2_biofilm_ret_scrape_1_B</td><td>2018-01-10</td><td>⋯</td><td>NA                                </td><td>NA</td><td>        NA</td><td>      NA</td><td>        NA</td><td>        NA</td><td>         NA</td><td>      NA</td><td>      NA</td><td>      NA</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD033</th><td>KNLK_SD033</td><td>SD033</td><td>41694</td><td>TCGACGAG</td><td>TAGACTGA</td><td>1</td><td>F11</td><td>Y</td><td>AWTP_2_inf_bulk_3_A_1            </td><td>2017-10-10</td><td>⋯</td><td>NA                                </td><td>NA</td><td>        NA</td><td>      NA</td><td>        NA</td><td>        NA</td><td>         NA</td><td>      NA</td><td>      NA</td><td>      NA</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD034</th><td>KNLK_SD034</td><td>SD034</td><td>34252</td><td>GATCGTGT</td><td>TAGACTGA</td><td>1</td><td>G11</td><td>N</td><td>AWTP_2_cond_bulk_7_A_3           </td><td>2018-03-01</td><td>⋯</td><td>NA                                </td><td>NA</td><td>        NA</td><td>      NA</td><td>        NA</td><td>        NA</td><td>         NA</td><td>      NA</td><td>      NA</td><td>      NA</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD035</th><td>KNLK_SD035</td><td>SD035</td><td>31665</td><td>GTCAGATA</td><td>TAGACTGA</td><td>1</td><td>H11</td><td>N</td><td>AWTP_2_inf_bulk_1_A_1            </td><td>2017-09-14</td><td>⋯</td><td>17_0914_DPWF_1_Influent_SGPI_100x_</td><td>NA</td><td> 9227256.9</td><td>1.072321</td><td>0.36737778</td><td>0.67470901</td><td>0.307331225</td><td>1.530134</td><td>1.096372</td><td>1.067370</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD036</th><td>KNLK_SD036</td><td>SD036</td><td>36007</td><td>CTACTATA</td><td>GTATGACG</td><td>1</td><td>A12</td><td>N</td><td>AWTP_2_BAC_bulk_1_A_2            </td><td>2017-09-14</td><td>⋯</td><td>17_0914_DPWF_3_GAC_SGPI_10x_      </td><td>NA</td><td>  709814.7</td><td>1.026051</td><td>0.03903449</td><td>0.06693995</td><td>0.027905461</td><td>1.431873</td><td>1.018637</td><td>1.006299</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD037</th><td>KNLK_SD037</td><td>SD037</td><td>44715</td><td>CGTTACTA</td><td>GTATGACG</td><td>1</td><td>B12</td><td>N</td><td>EXP_pos_library-prep_NA_NA_NA_P3 </td><td>NA</td><td>⋯</td><td>NA                                </td><td>NA</td><td>        NA</td><td>      NA</td><td>        NA</td><td>        NA</td><td>         NA</td><td>      NA</td><td>      NA</td><td>      NA</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD038</th><td>KNLK_SD038</td><td>SD038</td><td>36285</td><td>AGAGTCAC</td><td>GTATGACG</td><td>1</td><td>C12</td><td>N</td><td>SDS_F_1_bulk_2_A_17              </td><td>2017-12-11</td><td>⋯</td><td>17_1211_PL_Loop1_SGPI_            </td><td>NA</td><td>  731955.5</td><td>1.009096</td><td>0.22791853</td><td>0.23384972</td><td>0.005931195</td><td>1.444438</td><td>1.011606</td><td>1.031045</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 11 × 86\n",
       "\\begin{tabular}{r|llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll}\n",
       "  & id\\_full & id & count\\_raw\\_reads & barcode\\_forward & barcode\\_reverse & plate & well & metagenomic\\_sample & sample\\_code\\_full\\_and\\_batch & sample\\_date & batch\\_sample\\_date & project\\_type & sample\\_or\\_control & location\\_code & sample\\_type & sampler\\_name & Ext\\_batch & Extraction\\_Date & Ext\\_num\\_unique & Microconcentrated. & DNA\\_Ext\\_conc\\_ngperuL & kit & exp\\_merge & buffer & volume\\_uL & Avg.Size & ng.uL & nM & uL\\_for\\_200ng & free\\_Cl2\\_mg.L & total\\_Cl2\\_mg.L & pH & temp\\_C & disinfectant & broad\\_location & HPC\\_gmean\\_MPN\\_per\\_100mL & HPC\\_gstd\\_MPN\\_per\\_100mL & experimental\\_detail & percent\\_CRA & batch\\_date & bicarbonate\\_HCO3 & bromide & calcium\\_Ca & calcium\\_hardness\\_CaCO3 & carbonate\\_CO3 & chloride & copper\\_Cu & lead\\_Pb & magnesium\\_Mg & potassium\\_K & ortho\\_phosphate\\_PO4 & partial\\_alkalinity\\_CO3 & nitrate\\_NO3 & fluoride & sodium\\_Na & specific\\_conductance\\_EC & sulfate\\_SO4 & TDS & TOC & total\\_alkalinity\\_CaCO3.eq & total\\_hardness\\_CaCO3 & turbidity\\_NTU & days\\_since\\_delivery & length & diameter\\_in & water\\_main\\_fsn & water\\_main\\_material & ICC\\_to\\_TCC & summer\\_water\\_age\\_h & water\\_age\\_h & flow\\_cytometer & notes & tap\\_notes & wq\\_notes & pressure\\_zone & batch\\_end\\_or\\_start & base\\_filename & FCM\\_notes & avg\\_cells\\_per\\_mL\\_gmean\\_SGPI & avg\\_cells\\_per\\_mL\\_gstd\\_SGPI & intra\\_ATP\\_gmean\\_nM & total\\_ATP\\_gmean\\_nM & extra\\_ATP\\_gmean\\_nM & intra\\_ATP\\_gstd\\_nM & total\\_ATP\\_gstd\\_nM & extra\\_ATP\\_gstd\\_nM\\\\\n",
       "  & <fct> & <fct> & <int> & <fct> & <fct> & <fct> & <fct> & <fct> & <fct> & <date> & <date> & <fct> & <fct> & <fct> & <fct> & <fct> & <fct> & <date> & <fct> & <fct> & <dbl> & <fct> & <chr> & <fct> & <int> & <int> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <fct> & <fct> & <dbl> & <dbl> & <lgl> & <dbl> & <lgl> & <dbl> & <lgl> & <dbl> & <dbl> & <int> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <lgl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <lgl> & <int> & <int> & <int> & <fct> & <dbl> & <dbl> & <dbl> & <fct> & <lgl> & <lgl> & <fct> & <fct> & <fct> & <fct> & <fct> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\tKNLK\\_SD028 & KNLK\\_SD028 & SD028 & 35200 & CTACTATA & TAGACTGA & 1 & A11 & Y & AWTP\\_2\\_BAC\\_bulk\\_5\\_A\\_2             & 2017-12-14 & NA & DPWF         & sample & BAC                       & DEUF    & Scott\\_Miller   & 2  & 2018-06-16 & 15  & N  & 169.0 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 392 & 11.4805 &  48.2037 &  4.1490591 & NA &   NA &   NA &   NA & NA         & AWTP\\_2    &     NA & NA & NA & NA & NA & NA & NA &   NA & NA & NA &   NA & NA & NA &    NA &    NA & NA & NA &        NA &   NA &   NA & NA &   NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & 0.8495532 & NA & NA & Accuri & NA & NA & NA                                                                       & NA & NA        & 17\\_1214\\_DPWF\\_3\\_GAC\\_SGPI\\_10x\\_       & NA &   475688.4 & 1.691495 & 0.11222116 & 0.13538441 & 0.023163254 & 1.450614 & 1.011282 & 1.039995\\\\\n",
       "\tKNLK\\_SD029 & KNLK\\_SD029 & SD029 & 30964 & CGTTACTA & TAGACTGA & 1 & B11 & Y & AWTP\\_3\\_inf\\_bulk\\_1\\_A\\_1             & 2018-03-13 & NA & ECAWPC       & sample & WW\\_tertiary               & DEUF    & Lauren\\_Kennedy & 1  & 2018-06-15 & 6   & N  & 210.0 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 392 & 29.4492 & 123.6550 &  1.6174033 & NA &   NA &   NA &   NA & NA         & AWTP\\_3    &     NA & NA & NA & NA & NA & NA & NA &   NA & NA & NA &   NA & NA & NA &    NA &    NA & NA & NA &        NA &   NA &   NA & NA &   NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & 0.8490913 & NA & NA & Canto  & NA & NA & NA                                                                       & NA & NA        & 18\\_0313\\_ECAWPC\\_Influent\\_SGPI\\_10x\\_2 & NA & 12146298.6 & 1.074069 &         NA &         NA &          NA &       NA &       NA &       NA\\\\\n",
       "\tKNLK\\_SD030 & KNLK\\_SD030 & SD030 & 38480 & AGAGTCAC & TAGACTGA & 1 & C11 & Y & AWTP\\_2\\_inf\\_bulk\\_4\\_A\\_1             & 2017-11-14 & NA & DPWF         & sample & WW\\_tertiary               & DEUF    & Scott\\_Miller   & 1  & 2018-06-15 & 4   & N  & 518.0 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 391 & 15.4793 &  65.0674 &  3.0737358 & NA &   NA &   NA &   NA & NA         & AWTP\\_2    &     NA & NA & NA & NA & NA & NA & NA &   NA & NA & NA &   NA & NA & NA &    NA &    NA & NA & NA &        NA &   NA &   NA & NA &   NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & 0.8554792 & NA & NA & Accuri & NA & NA & NA                                                                       & NA & NA        & 17\\_1114\\_DPWF\\_1\\_Influent\\_SGPI\\_100x\\_ & NA &  5461380.2 & 1.014661 & 0.55397549 & 0.65390039 & 0.099924893 & 1.591198 & 1.224577 & 1.016032\\\\\n",
       "\tKNLK\\_SD031 & KNLK\\_SD031 & SD031 & 37100 & TACGAGAC & TAGACTGA & 1 & D11 & Y & AWTP\\_2\\_inf\\_bulk\\_5\\_A\\_1             & 2017-12-14 & NA & DPWF         & sample & WW\\_tertiary               & DEUF    & Scott\\_Miller   & 1  & 2018-06-15 & 5   & N  & 542.0 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 399 & 27.3964 & 113.0480 &  1.7691600 & NA &   NA &   NA &   NA & NA         & AWTP\\_2    &     NA & NA & NA & NA & NA & NA & NA &   NA & NA & NA &   NA & NA & NA &    NA &    NA & NA & NA &        NA &   NA &   NA & NA &   NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & 0.6872002 & NA & NA & Accuri & NA & NA & NA                                                                       & NA & NA        & 17\\_1214\\_DPWF\\_1\\_Influent\\_SGPI\\_100x\\_ & NA &  6660896.6 & 1.070527 & 0.63648601 & 0.78860455 & 0.152118540 & 1.494909 & 1.081167 & 1.032391\\\\\n",
       "\tKNLK\\_SD032 & KNLK\\_SD032 & SD032 & 23905 & ACGTCTCG & TAGACTGA & 1 & E11 & Y & AWTP\\_2\\_RO2\\_biofilm\\_ret\\_scrape\\_1\\_B & 2018-01-10 & NA & DPWF         & sample & RO\\_BF\\_retentate\\_scrapings & biofilm & Rose\\_Kantor    & 2  & 2018-06-16 & 9   & N  & 600.0 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 392 & 16.8998 &  70.9215 &  2.8200193 & NA &   NA &   NA &   NA & NA         & NA        &     NA & NA & NA & NA & NA & NA & NA &   NA & NA & NA &   NA & NA & NA &    NA &    NA & NA & NA &        NA &   NA &   NA & NA &   NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA &        NA & NA & NA & NA     & NA & NA & NA                                                                       & NA & NA        & NA                                 & NA &         NA &       NA &         NA &         NA &          NA &       NA &       NA &       NA\\\\\n",
       "\tKNLK\\_SD033 & KNLK\\_SD033 & SD033 & 41694 & TCGACGAG & TAGACTGA & 1 & F11 & Y & AWTP\\_2\\_inf\\_bulk\\_3\\_A\\_1             & 2017-10-10 & NA & DPWF         & sample & WW\\_tertiary               & DEUF    & Scott\\_Miller   & 1  & 2018-06-15 & 3   & N  & 680.0 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 394 &  4.3238 &  18.0700 & 11.0680686 & NA &   NA &   NA &   NA & NA         & NA        &     NA & NA & NA & NA & NA & NA & NA &   NA & NA & NA &   NA & NA & NA &    NA &    NA & NA & NA &        NA &   NA &   NA & NA &   NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA &        NA & NA & NA & NA     & NA & NA & NA                                                                       & NA & NA        & NA                                 & NA &         NA &       NA &         NA &         NA &          NA &       NA &       NA &       NA\\\\\n",
       "\tKNLK\\_SD034 & KNLK\\_SD034 & SD034 & 34252 & GATCGTGT & TAGACTGA & 1 & G11 & N & AWTP\\_2\\_cond\\_bulk\\_7\\_A\\_3            & 2018-03-01 & 2018-03-05 & Pipe\\_Loop    & sample & 100\\%\\_conditioned          & DEUF    & Lauren\\_Kennedy & 3  & 2018-06-17 & 22  & N  &  44.8 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 393 & 12.7813 &  53.5154 &  3.7372420 & NA &   NA &   NA &   NA & NA         & NA        &     NA & NA & NA & NA & NA & NA & NA &   NA & NA & NA &   NA & NA & NA &    NA &    NA & NA & NA &        NA &   NA &   NA & NA &   NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA &        NA & NA & NA & NA     & NA & NA & NA                                                                       & NA & NA        & NA                                 & NA &         NA &       NA &         NA &         NA &          NA &       NA &       NA &       NA\\\\\n",
       "\tKNLK\\_SD035 & KNLK\\_SD035 & SD035 & 31665 & GTCAGATA & TAGACTGA & 1 & H11 & N & AWTP\\_2\\_inf\\_bulk\\_1\\_A\\_1             & 2017-09-14 & NA & DPWF         & sample & WW\\_tertiary               & DEUF    & Scott\\_Miller   & 1  & 2018-06-15 & 1   & N  &  35.2 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 398 & 17.8501 &  73.8797 &  2.7071036 & NA &   NA &   NA &   NA & NA         & AWTP\\_2    &     NA & NA & NA & NA & NA & NA & NA &   NA & NA & NA &   NA & NA & NA &    NA &    NA & NA & NA &        NA &   NA &   NA & NA &   NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & 0.7953513 & NA & NA & Accuri & NA & NA & NA                                                                       & NA & NA        & 17\\_0914\\_DPWF\\_1\\_Influent\\_SGPI\\_100x\\_ & NA &  9227256.9 & 1.072321 & 0.36737778 & 0.67470901 & 0.307331225 & 1.530134 & 1.096372 & 1.067370\\\\\n",
       "\tKNLK\\_SD036 & KNLK\\_SD036 & SD036 & 36007 & CTACTATA & GTATGACG & 1 & A12 & N & AWTP\\_2\\_BAC\\_bulk\\_1\\_A\\_2             & 2017-09-14 & NA & DPWF         & sample & BAC                       & DEUF    & Scott\\_Miller   & 2  & 2018-06-16 & 12  & N  &  34.2 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 395 & 27.7337 & 115.6496 &  1.7293618 & NA &   NA &   NA &   NA & NA         & AWTP\\_2    &     NA & NA & NA & NA & NA & NA & NA &   NA & NA & NA &   NA & NA & NA &    NA &    NA & NA & NA &        NA &   NA &   NA & NA &   NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & 0.7824791 & NA & NA & Accuri & NA & NA & NA                                                                       & NA & NA        & 17\\_0914\\_DPWF\\_3\\_GAC\\_SGPI\\_10x\\_       & NA &   709814.7 & 1.026051 & 0.03903449 & 0.06693995 & 0.027905461 & 1.431873 & 1.018637 & 1.006299\\\\\n",
       "\tKNLK\\_SD037 & KNLK\\_SD037 & SD037 & 44715 & CGTTACTA & GTATGACG & 1 & B12 & N & EXP\\_pos\\_library-prep\\_NA\\_NA\\_NA\\_P3  & NA & NA & Experimental & sample & positive\\_control          & NA      & NA             & P3 & 1899-12-31 & P3  & NA &   0.0 & NA            & Y & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 397 & 26.7601 & 110.9176 &  1.8031403 & NA &   NA &   NA &   NA & NA         & NA        &     NA & NA & NA & NA & NA & NA & NA &   NA & NA & NA &   NA & NA & NA &    NA &    NA & NA & NA &        NA &   NA &   NA & NA &   NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA &        NA & NA & NA & NA     & NA & NA & NA                                                                       & NA & NA        & NA                                 & NA &         NA &       NA &         NA &         NA &          NA &       NA &       NA &       NA\\\\\n",
       "\tKNLK\\_SD038 & KNLK\\_SD038 & SD038 & 36285 & AGAGTCAC & GTATGACG & 1 & C12 & N & SDS\\_F\\_1\\_bulk\\_2\\_A\\_17               & 2017-12-11 & 2017-12-11 & Pipe\\_Loop    & sample & Loop\\_1                    & DEUF    & Scott\\_Miller   & 17 & 2018-09-06 & 165 & Y  &  33.9 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 396 & 51.2197 & 212.6621 &  0.9404591 & NA & 0.02 & 8.17 & 19.2 & Chloramine & Pipe\\_Loop & 2419.6 &  1 & NA & NA & NA & NA & NA & 36.9 & NA & NA & 74.1 & NA & NA & 14.35 & 3.805 & NA & NA & 0.6841935 & 0.52 & 62.4 & NA & 88.5 & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & 0.8982416 & NA & NA & Accuri & NA & NA & End using green reservoir; resume direct fill of loops from utility line & NA & Batch\\_End & 17\\_1211\\_PL\\_Loop1\\_SGPI\\_             & NA &   731955.5 & 1.009096 & 0.22791853 & 0.23384972 & 0.005931195 & 1.444438 & 1.011606 & 1.031045\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 11 × 86\n",
       "\n",
       "| <!--/--> | id_full &lt;fct&gt; | id &lt;fct&gt; | count_raw_reads &lt;int&gt; | barcode_forward &lt;fct&gt; | barcode_reverse &lt;fct&gt; | plate &lt;fct&gt; | well &lt;fct&gt; | metagenomic_sample &lt;fct&gt; | sample_code_full_and_batch &lt;fct&gt; | sample_date &lt;date&gt; | ⋯ ⋯ | base_filename &lt;fct&gt; | FCM_notes &lt;fct&gt; | avg_cells_per_mL_gmean_SGPI &lt;dbl&gt; | avg_cells_per_mL_gstd_SGPI &lt;dbl&gt; | intra_ATP_gmean_nM &lt;dbl&gt; | total_ATP_gmean_nM &lt;dbl&gt; | extra_ATP_gmean_nM &lt;dbl&gt; | intra_ATP_gstd_nM &lt;dbl&gt; | total_ATP_gstd_nM &lt;dbl&gt; | extra_ATP_gstd_nM &lt;dbl&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
       "| KNLK_SD028 | KNLK_SD028 | SD028 | 35200 | CTACTATA | TAGACTGA | 1 | A11 | Y | AWTP_2_BAC_bulk_5_A_2             | 2017-12-14 | ⋯ | 17_1214_DPWF_3_GAC_SGPI_10x_       | NA |   475688.4 | 1.691495 | 0.11222116 | 0.13538441 | 0.023163254 | 1.450614 | 1.011282 | 1.039995 |\n",
       "| KNLK_SD029 | KNLK_SD029 | SD029 | 30964 | CGTTACTA | TAGACTGA | 1 | B11 | Y | AWTP_3_inf_bulk_1_A_1             | 2018-03-13 | ⋯ | 18_0313_ECAWPC_Influent_SGPI_10x_2 | NA | 12146298.6 | 1.074069 |         NA |         NA |          NA |       NA |       NA |       NA |\n",
       "| KNLK_SD030 | KNLK_SD030 | SD030 | 38480 | AGAGTCAC | TAGACTGA | 1 | C11 | Y | AWTP_2_inf_bulk_4_A_1             | 2017-11-14 | ⋯ | 17_1114_DPWF_1_Influent_SGPI_100x_ | NA |  5461380.2 | 1.014661 | 0.55397549 | 0.65390039 | 0.099924893 | 1.591198 | 1.224577 | 1.016032 |\n",
       "| KNLK_SD031 | KNLK_SD031 | SD031 | 37100 | TACGAGAC | TAGACTGA | 1 | D11 | Y | AWTP_2_inf_bulk_5_A_1             | 2017-12-14 | ⋯ | 17_1214_DPWF_1_Influent_SGPI_100x_ | NA |  6660896.6 | 1.070527 | 0.63648601 | 0.78860455 | 0.152118540 | 1.494909 | 1.081167 | 1.032391 |\n",
       "| KNLK_SD032 | KNLK_SD032 | SD032 | 23905 | ACGTCTCG | TAGACTGA | 1 | E11 | Y | AWTP_2_RO2_biofilm_ret_scrape_1_B | 2018-01-10 | ⋯ | NA                                 | NA |         NA |       NA |         NA |         NA |          NA |       NA |       NA |       NA |\n",
       "| KNLK_SD033 | KNLK_SD033 | SD033 | 41694 | TCGACGAG | TAGACTGA | 1 | F11 | Y | AWTP_2_inf_bulk_3_A_1             | 2017-10-10 | ⋯ | NA                                 | NA |         NA |       NA |         NA |         NA |          NA |       NA |       NA |       NA |\n",
       "| KNLK_SD034 | KNLK_SD034 | SD034 | 34252 | GATCGTGT | TAGACTGA | 1 | G11 | N | AWTP_2_cond_bulk_7_A_3            | 2018-03-01 | ⋯ | NA                                 | NA |         NA |       NA |         NA |         NA |          NA |       NA |       NA |       NA |\n",
       "| KNLK_SD035 | KNLK_SD035 | SD035 | 31665 | GTCAGATA | TAGACTGA | 1 | H11 | N | AWTP_2_inf_bulk_1_A_1             | 2017-09-14 | ⋯ | 17_0914_DPWF_1_Influent_SGPI_100x_ | NA |  9227256.9 | 1.072321 | 0.36737778 | 0.67470901 | 0.307331225 | 1.530134 | 1.096372 | 1.067370 |\n",
       "| KNLK_SD036 | KNLK_SD036 | SD036 | 36007 | CTACTATA | GTATGACG | 1 | A12 | N | AWTP_2_BAC_bulk_1_A_2             | 2017-09-14 | ⋯ | 17_0914_DPWF_3_GAC_SGPI_10x_       | NA |   709814.7 | 1.026051 | 0.03903449 | 0.06693995 | 0.027905461 | 1.431873 | 1.018637 | 1.006299 |\n",
       "| KNLK_SD037 | KNLK_SD037 | SD037 | 44715 | CGTTACTA | GTATGACG | 1 | B12 | N | EXP_pos_library-prep_NA_NA_NA_P3  | NA | ⋯ | NA                                 | NA |         NA |       NA |         NA |         NA |          NA |       NA |       NA |       NA |\n",
       "| KNLK_SD038 | KNLK_SD038 | SD038 | 36285 | AGAGTCAC | GTATGACG | 1 | C12 | N | SDS_F_1_bulk_2_A_17               | 2017-12-11 | ⋯ | 17_1211_PL_Loop1_SGPI_             | NA |   731955.5 | 1.009096 | 0.22791853 | 0.23384972 | 0.005931195 | 1.444438 | 1.011606 | 1.031045 |\n",
       "\n"
      ],
      "text/plain": [
       "           id_full    id    count_raw_reads barcode_forward barcode_reverse\n",
       "KNLK_SD028 KNLK_SD028 SD028 35200           CTACTATA        TAGACTGA       \n",
       "KNLK_SD029 KNLK_SD029 SD029 30964           CGTTACTA        TAGACTGA       \n",
       "KNLK_SD030 KNLK_SD030 SD030 38480           AGAGTCAC        TAGACTGA       \n",
       "KNLK_SD031 KNLK_SD031 SD031 37100           TACGAGAC        TAGACTGA       \n",
       "KNLK_SD032 KNLK_SD032 SD032 23905           ACGTCTCG        TAGACTGA       \n",
       "KNLK_SD033 KNLK_SD033 SD033 41694           TCGACGAG        TAGACTGA       \n",
       "KNLK_SD034 KNLK_SD034 SD034 34252           GATCGTGT        TAGACTGA       \n",
       "KNLK_SD035 KNLK_SD035 SD035 31665           GTCAGATA        TAGACTGA       \n",
       "KNLK_SD036 KNLK_SD036 SD036 36007           CTACTATA        GTATGACG       \n",
       "KNLK_SD037 KNLK_SD037 SD037 44715           CGTTACTA        GTATGACG       \n",
       "KNLK_SD038 KNLK_SD038 SD038 36285           AGAGTCAC        GTATGACG       \n",
       "           plate well metagenomic_sample sample_code_full_and_batch       \n",
       "KNLK_SD028 1     A11  Y                  AWTP_2_BAC_bulk_5_A_2            \n",
       "KNLK_SD029 1     B11  Y                  AWTP_3_inf_bulk_1_A_1            \n",
       "KNLK_SD030 1     C11  Y                  AWTP_2_inf_bulk_4_A_1            \n",
       "KNLK_SD031 1     D11  Y                  AWTP_2_inf_bulk_5_A_1            \n",
       "KNLK_SD032 1     E11  Y                  AWTP_2_RO2_biofilm_ret_scrape_1_B\n",
       "KNLK_SD033 1     F11  Y                  AWTP_2_inf_bulk_3_A_1            \n",
       "KNLK_SD034 1     G11  N                  AWTP_2_cond_bulk_7_A_3           \n",
       "KNLK_SD035 1     H11  N                  AWTP_2_inf_bulk_1_A_1            \n",
       "KNLK_SD036 1     A12  N                  AWTP_2_BAC_bulk_1_A_2            \n",
       "KNLK_SD037 1     B12  N                  EXP_pos_library-prep_NA_NA_NA_P3 \n",
       "KNLK_SD038 1     C12  N                  SDS_F_1_bulk_2_A_17              \n",
       "           sample_date ⋯ base_filename                      FCM_notes\n",
       "KNLK_SD028 2017-12-14  ⋯ 17_1214_DPWF_3_GAC_SGPI_10x_       NA       \n",
       "KNLK_SD029 2018-03-13  ⋯ 18_0313_ECAWPC_Influent_SGPI_10x_2 NA       \n",
       "KNLK_SD030 2017-11-14  ⋯ 17_1114_DPWF_1_Influent_SGPI_100x_ NA       \n",
       "KNLK_SD031 2017-12-14  ⋯ 17_1214_DPWF_1_Influent_SGPI_100x_ NA       \n",
       "KNLK_SD032 2018-01-10  ⋯ NA                                 NA       \n",
       "KNLK_SD033 2017-10-10  ⋯ NA                                 NA       \n",
       "KNLK_SD034 2018-03-01  ⋯ NA                                 NA       \n",
       "KNLK_SD035 2017-09-14  ⋯ 17_0914_DPWF_1_Influent_SGPI_100x_ NA       \n",
       "KNLK_SD036 2017-09-14  ⋯ 17_0914_DPWF_3_GAC_SGPI_10x_       NA       \n",
       "KNLK_SD037 <NA>        ⋯ NA                                 NA       \n",
       "KNLK_SD038 2017-12-11  ⋯ 17_1211_PL_Loop1_SGPI_             NA       \n",
       "           avg_cells_per_mL_gmean_SGPI avg_cells_per_mL_gstd_SGPI\n",
       "KNLK_SD028   475688.4                  1.691495                  \n",
       "KNLK_SD029 12146298.6                  1.074069                  \n",
       "KNLK_SD030  5461380.2                  1.014661                  \n",
       "KNLK_SD031  6660896.6                  1.070527                  \n",
       "KNLK_SD032         NA                        NA                  \n",
       "KNLK_SD033         NA                        NA                  \n",
       "KNLK_SD034         NA                        NA                  \n",
       "KNLK_SD035  9227256.9                  1.072321                  \n",
       "KNLK_SD036   709814.7                  1.026051                  \n",
       "KNLK_SD037         NA                        NA                  \n",
       "KNLK_SD038   731955.5                  1.009096                  \n",
       "           intra_ATP_gmean_nM total_ATP_gmean_nM extra_ATP_gmean_nM\n",
       "KNLK_SD028 0.11222116         0.13538441         0.023163254       \n",
       "KNLK_SD029         NA                 NA                  NA       \n",
       "KNLK_SD030 0.55397549         0.65390039         0.099924893       \n",
       "KNLK_SD031 0.63648601         0.78860455         0.152118540       \n",
       "KNLK_SD032         NA                 NA                  NA       \n",
       "KNLK_SD033         NA                 NA                  NA       \n",
       "KNLK_SD034         NA                 NA                  NA       \n",
       "KNLK_SD035 0.36737778         0.67470901         0.307331225       \n",
       "KNLK_SD036 0.03903449         0.06693995         0.027905461       \n",
       "KNLK_SD037         NA                 NA                  NA       \n",
       "KNLK_SD038 0.22791853         0.23384972         0.005931195       \n",
       "           intra_ATP_gstd_nM total_ATP_gstd_nM extra_ATP_gstd_nM\n",
       "KNLK_SD028 1.450614          1.011282          1.039995         \n",
       "KNLK_SD029       NA                NA                NA         \n",
       "KNLK_SD030 1.591198          1.224577          1.016032         \n",
       "KNLK_SD031 1.494909          1.081167          1.032391         \n",
       "KNLK_SD032       NA                NA                NA         \n",
       "KNLK_SD033       NA                NA                NA         \n",
       "KNLK_SD034       NA                NA                NA         \n",
       "KNLK_SD035 1.530134          1.096372          1.067370         \n",
       "KNLK_SD036 1.431873          1.018637          1.006299         \n",
       "KNLK_SD037       NA                NA                NA         \n",
       "KNLK_SD038 1.444438          1.011606          1.031045         "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# check that it worked\n",
    "table_data[25:35,]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 6 × 86</caption>\n",
       "<thead>\n",
       "\t<tr><th></th><th scope=col>id</th><th scope=col>id_full</th><th scope=col>count_raw_reads</th><th scope=col>barcode_forward</th><th scope=col>barcode_reverse</th><th scope=col>plate</th><th scope=col>well</th><th scope=col>metagenomic_sample</th><th scope=col>sample_code_full_and_batch</th><th scope=col>sample_date</th><th scope=col>⋯</th><th scope=col>base_filename</th><th scope=col>FCM_notes</th><th scope=col>avg_cells_per_mL_gmean_SGPI</th><th scope=col>avg_cells_per_mL_gstd_SGPI</th><th scope=col>intra_ATP_gmean_nM</th><th scope=col>total_ATP_gmean_nM</th><th scope=col>extra_ATP_gmean_nM</th><th scope=col>intra_ATP_gstd_nM</th><th scope=col>total_ATP_gstd_nM</th><th scope=col>extra_ATP_gstd_nM</th></tr>\n",
       "\t<tr><th></th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>⋯</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;fct&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>KNLK_SD003</th><td>SD003</td><td>KNLK_SD003</td><td>52728</td><td>CGTTACTA</td><td>CTCTAGAG</td><td>1</td><td>B3</td><td>Y</td><td>DWDS_F_utility_bulk_9_A_12        </td><td>2018-03-28</td><td>⋯</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD004</th><td>SD004</td><td>KNLK_SD004</td><td>35359</td><td>AGAGTCAC</td><td>CTCTAGAG</td><td>1</td><td>C3</td><td>Y</td><td>SDS_F_5_biofilm_9_NA_8            </td><td>2018-04-09</td><td>⋯</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD005</th><td>SD005</td><td>KNLK_SD005</td><td>35380</td><td>TACGAGAC</td><td>CTCTAGAG</td><td>1</td><td>D3</td><td>Y</td><td>DWDS_F_328_bulk_5_A_13            </td><td>2018-04-19</td><td>⋯</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD006</th><td>SD006</td><td>KNLK_SD006</td><td>30734</td><td>TACGAGAC</td><td>CATGAGGA</td><td>1</td><td>D5</td><td>Y</td><td>AWTP_2_RO2_biofilm_sep_1_A_RO1_A57</td><td>2018-01-10</td><td>⋯</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD007</th><td>SD007</td><td>KNLK_SD007</td><td>35135</td><td>CTACTATA</td><td>AGTCTAGA</td><td>1</td><td>A6</td><td>Y</td><td>AWTP_3_MF_tank_bulk_1_A_18        </td><td>2018-03-13</td><td>⋯</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td></tr>\n",
       "\t<tr><th scope=row>KNLK_SD008</th><td>SD008</td><td>KNLK_SD008</td><td>16805</td><td>CGTTACTA</td><td>AGTCTAGA</td><td>1</td><td>B6</td><td>Y</td><td>DWDS_F_328_bulk_1_A_12            </td><td>2018-02-14</td><td>⋯</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td><td>NA</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 86\n",
       "\\begin{tabular}{r|llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll}\n",
       "  & id & id\\_full & count\\_raw\\_reads & barcode\\_forward & barcode\\_reverse & plate & well & metagenomic\\_sample & sample\\_code\\_full\\_and\\_batch & sample\\_date & batch\\_sample\\_date & project\\_type & sample\\_or\\_control & location\\_code & sample\\_type & sampler\\_name & Ext\\_batch & Extraction\\_Date & Ext\\_num\\_unique & Microconcentrated. & DNA\\_Ext\\_conc\\_ngperuL & kit & exp\\_merge & buffer & volume\\_uL & Avg.Size & ng.uL & nM & uL\\_for\\_200ng & free\\_Cl2\\_mg.L & total\\_Cl2\\_mg.L & pH & temp\\_C & disinfectant & broad\\_location & HPC\\_gmean\\_MPN\\_per\\_100mL & HPC\\_gstd\\_MPN\\_per\\_100mL & experimental\\_detail & percent\\_CRA & batch\\_date & bicarbonate\\_HCO3 & bromide & calcium\\_Ca & calcium\\_hardness\\_CaCO3 & carbonate\\_CO3 & chloride & copper\\_Cu & lead\\_Pb & magnesium\\_Mg & potassium\\_K & ortho\\_phosphate\\_PO4 & partial\\_alkalinity\\_CO3 & nitrate\\_NO3 & fluoride & sodium\\_Na & specific\\_conductance\\_EC & sulfate\\_SO4 & TDS & TOC & total\\_alkalinity\\_CaCO3.eq & total\\_hardness\\_CaCO3 & turbidity\\_NTU & days\\_since\\_delivery & length & diameter\\_in & water\\_main\\_fsn & water\\_main\\_material & ICC\\_to\\_TCC & summer\\_water\\_age\\_h & water\\_age\\_h & flow\\_cytometer & notes & tap\\_notes & wq\\_notes & pressure\\_zone & batch\\_end\\_or\\_start & base\\_filename & FCM\\_notes & avg\\_cells\\_per\\_mL\\_gmean\\_SGPI & avg\\_cells\\_per\\_mL\\_gstd\\_SGPI & intra\\_ATP\\_gmean\\_nM & total\\_ATP\\_gmean\\_nM & extra\\_ATP\\_gmean\\_nM & intra\\_ATP\\_gstd\\_nM & total\\_ATP\\_gstd\\_nM & extra\\_ATP\\_gstd\\_nM\\\\\n",
       "  & <fct> & <fct> & <int> & <fct> & <fct> & <fct> & <fct> & <fct> & <fct> & <date> & <date> & <fct> & <fct> & <fct> & <fct> & <fct> & <fct> & <date> & <fct> & <fct> & <dbl> & <fct> & <chr> & <fct> & <int> & <int> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <fct> & <fct> & <dbl> & <dbl> & <lgl> & <dbl> & <lgl> & <dbl> & <lgl> & <dbl> & <dbl> & <int> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <lgl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <lgl> & <int> & <int> & <int> & <fct> & <dbl> & <dbl> & <dbl> & <fct> & <lgl> & <lgl> & <fct> & <fct> & <fct> & <fct> & <fct> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\tKNLK\\_SD003 & SD003 & KNLK\\_SD003 & 52728 & CGTTACTA & CTCTAGAG & 1 & B3 & Y & DWDS\\_F\\_utility\\_bulk\\_9\\_A\\_12         & 2018-03-28 & NA & Pipe\\_Loop  & sample & 100\\%\\_conventional & DEUF    & Lauren\\_Kennedy & 12  & 2018-07-09 & 99  & Y & 0.252 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 393 & 10.0087 &  41.8977 & 4.773532 & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA\\\\\n",
       "\tKNLK\\_SD004 & SD004 & KNLK\\_SD004 & 35359 & AGAGTCAC & CTCTAGAG & 1 & C3 & Y & SDS\\_F\\_5\\_biofilm\\_9\\_NA\\_8             & 2018-04-09 & 2018-04-09 & Pipe\\_Loop  & sample & Loop\\_5            & biofilm & Lauren\\_Kennedy & 8   & 2018-07-06 & 51  & Y & 0.302 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 390 &  7.1627 &  30.2149 & 6.619251 & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA\\\\\n",
       "\tKNLK\\_SD005 & SD005 & KNLK\\_SD005 & 35380 & TACGAGAC & CTCTAGAG & 1 & D3 & Y & DWDS\\_F\\_328\\_bulk\\_5\\_A\\_13             & 2018-04-19 & NA & Full\\_Scale & sample & DWDS\\_328          & DEUF    & Lauren\\_Kennedy & 13  & 2018-08-23 & 116 & Y & 0.307 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 390 & 19.2228 &  81.0844 & 2.466566 & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA\\\\\n",
       "\tKNLK\\_SD006 & SD006 & KNLK\\_SD006 & 30734 & TACGAGAC & CATGAGGA & 1 & D5 & Y & AWTP\\_2\\_RO2\\_biofilm\\_sep\\_1\\_A\\_RO1\\_A57 & 2018-01-10 & NA & DPWF       & sample & RO\\_BF\\_separator   & biofilm & Rose\\_Kantor    & RO1 & 2018-09-13 & A57 & Y & 1.170 & Powersoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 393 & 12.7806 &  53.5584 & 3.734242 & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA\\\\\n",
       "\tKNLK\\_SD007 & SD007 & KNLK\\_SD007 & 35135 & CTACTATA & AGTCTAGA & 1 & A6 & Y & AWTP\\_3\\_MF\\_tank\\_bulk\\_1\\_A\\_18         & 2018-03-13 & NA & ECAWPC     & sample & MF\\_tank           & DEUF    & Lauren\\_Kennedy & 18  & 2018-09-08 & 174 & Y & 1.810 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 396 & 36.0204 & 149.5023 & 1.337772 & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA\\\\\n",
       "\tKNLK\\_SD008 & SD008 & KNLK\\_SD008 & 16805 & CGTTACTA & AGTCTAGA & 1 & B6 & Y & DWDS\\_F\\_328\\_bulk\\_1\\_A\\_12             & 2018-02-14 & NA & Full\\_Scale & sample & DWDS\\_328          & DEUF    & Lauren\\_Kennedy & 12  & 2018-07-09 & 103 & Y & 1.870 & PowerSoil Pro & N & 20 mM tris-HCl with 0.1 mM EDTA & 50 & 397 & 37.7257 & 156.5379 & 1.277646 & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA & NA\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 86\n",
       "\n",
       "| <!--/--> | id &lt;fct&gt; | id_full &lt;fct&gt; | count_raw_reads &lt;int&gt; | barcode_forward &lt;fct&gt; | barcode_reverse &lt;fct&gt; | plate &lt;fct&gt; | well &lt;fct&gt; | metagenomic_sample &lt;fct&gt; | sample_code_full_and_batch &lt;fct&gt; | sample_date &lt;date&gt; | ⋯ ⋯ | base_filename &lt;fct&gt; | FCM_notes &lt;fct&gt; | avg_cells_per_mL_gmean_SGPI &lt;dbl&gt; | avg_cells_per_mL_gstd_SGPI &lt;dbl&gt; | intra_ATP_gmean_nM &lt;dbl&gt; | total_ATP_gmean_nM &lt;dbl&gt; | extra_ATP_gmean_nM &lt;dbl&gt; | intra_ATP_gstd_nM &lt;dbl&gt; | total_ATP_gstd_nM &lt;dbl&gt; | extra_ATP_gstd_nM &lt;dbl&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
       "| KNLK_SD003 | SD003 | KNLK_SD003 | 52728 | CGTTACTA | CTCTAGAG | 1 | B3 | Y | DWDS_F_utility_bulk_9_A_12         | 2018-03-28 | ⋯ | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA |\n",
       "| KNLK_SD004 | SD004 | KNLK_SD004 | 35359 | AGAGTCAC | CTCTAGAG | 1 | C3 | Y | SDS_F_5_biofilm_9_NA_8             | 2018-04-09 | ⋯ | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA |\n",
       "| KNLK_SD005 | SD005 | KNLK_SD005 | 35380 | TACGAGAC | CTCTAGAG | 1 | D3 | Y | DWDS_F_328_bulk_5_A_13             | 2018-04-19 | ⋯ | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA |\n",
       "| KNLK_SD006 | SD006 | KNLK_SD006 | 30734 | TACGAGAC | CATGAGGA | 1 | D5 | Y | AWTP_2_RO2_biofilm_sep_1_A_RO1_A57 | 2018-01-10 | ⋯ | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA |\n",
       "| KNLK_SD007 | SD007 | KNLK_SD007 | 35135 | CTACTATA | AGTCTAGA | 1 | A6 | Y | AWTP_3_MF_tank_bulk_1_A_18         | 2018-03-13 | ⋯ | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA |\n",
       "| KNLK_SD008 | SD008 | KNLK_SD008 | 16805 | CGTTACTA | AGTCTAGA | 1 | B6 | Y | DWDS_F_328_bulk_1_A_12             | 2018-02-14 | ⋯ | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA |\n",
       "\n"
      ],
      "text/plain": [
       "           id    id_full    count_raw_reads barcode_forward barcode_reverse\n",
       "KNLK_SD003 SD003 KNLK_SD003 52728           CGTTACTA        CTCTAGAG       \n",
       "KNLK_SD004 SD004 KNLK_SD004 35359           AGAGTCAC        CTCTAGAG       \n",
       "KNLK_SD005 SD005 KNLK_SD005 35380           TACGAGAC        CTCTAGAG       \n",
       "KNLK_SD006 SD006 KNLK_SD006 30734           TACGAGAC        CATGAGGA       \n",
       "KNLK_SD007 SD007 KNLK_SD007 35135           CTACTATA        AGTCTAGA       \n",
       "KNLK_SD008 SD008 KNLK_SD008 16805           CGTTACTA        AGTCTAGA       \n",
       "           plate well metagenomic_sample sample_code_full_and_batch        \n",
       "KNLK_SD003 1     B3   Y                  DWDS_F_utility_bulk_9_A_12        \n",
       "KNLK_SD004 1     C3   Y                  SDS_F_5_biofilm_9_NA_8            \n",
       "KNLK_SD005 1     D3   Y                  DWDS_F_328_bulk_5_A_13            \n",
       "KNLK_SD006 1     D5   Y                  AWTP_2_RO2_biofilm_sep_1_A_RO1_A57\n",
       "KNLK_SD007 1     A6   Y                  AWTP_3_MF_tank_bulk_1_A_18        \n",
       "KNLK_SD008 1     B6   Y                  DWDS_F_328_bulk_1_A_12            \n",
       "           sample_date ⋯ base_filename FCM_notes avg_cells_per_mL_gmean_SGPI\n",
       "KNLK_SD003 2018-03-28  ⋯ NA            NA        NA                         \n",
       "KNLK_SD004 2018-04-09  ⋯ NA            NA        NA                         \n",
       "KNLK_SD005 2018-04-19  ⋯ NA            NA        NA                         \n",
       "KNLK_SD006 2018-01-10  ⋯ NA            NA        NA                         \n",
       "KNLK_SD007 2018-03-13  ⋯ NA            NA        NA                         \n",
       "KNLK_SD008 2018-02-14  ⋯ NA            NA        NA                         \n",
       "           avg_cells_per_mL_gstd_SGPI intra_ATP_gmean_nM total_ATP_gmean_nM\n",
       "KNLK_SD003 NA                         NA                 NA                \n",
       "KNLK_SD004 NA                         NA                 NA                \n",
       "KNLK_SD005 NA                         NA                 NA                \n",
       "KNLK_SD006 NA                         NA                 NA                \n",
       "KNLK_SD007 NA                         NA                 NA                \n",
       "KNLK_SD008 NA                         NA                 NA                \n",
       "           extra_ATP_gmean_nM intra_ATP_gstd_nM total_ATP_gstd_nM\n",
       "KNLK_SD003 NA                 NA                NA               \n",
       "KNLK_SD004 NA                 NA                NA               \n",
       "KNLK_SD005 NA                 NA                NA               \n",
       "KNLK_SD006 NA                 NA                NA               \n",
       "KNLK_SD007 NA                 NA                NA               \n",
       "KNLK_SD008 NA                 NA                NA               \n",
       "           extra_ATP_gstd_nM\n",
       "KNLK_SD003 NA               \n",
       "KNLK_SD004 NA               \n",
       "KNLK_SD005 NA               \n",
       "KNLK_SD006 NA               \n",
       "KNLK_SD007 NA               \n",
       "KNLK_SD008 NA               "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "table_data <- table_data[,c(\"id\",setdiff(names(table_data),\"id\"))] # move id column to be first on the left\n",
    "head(table_data)\n",
    "#write.table(table_data, '~/data/awtp2_16S/metadata_final.tsv', sep=\"\\t\", quote=FALSE, row.names=FALSE)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add to phyloseq object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ps <- readRDS(\"~/data/awtp2_16S/ps_clean_filt.rds\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add metadata\n",
    "metadata_complete <- read.table('~/data/awtp2_16S/metadata_final.tsv', sep=\"\\t\", header=TRUE, row.names=1)\n",
    "metadata_complete$location_code<- gsub(\"WW_tertiary\", \"WW_3ary\", metadata_complete$location_code)\n",
    "\n",
    "#reset order of levels\n",
    "location_levels = c(\n",
    "    'field_blank',\n",
    "    'RO_BF_control',\n",
    "    'negative_control',\n",
    "    'WW_3ary',\n",
    "    'BAC',\n",
    "    'MF_combined',\n",
    "    'MF_tank',\n",
    "    'RO_BF_retentate_scrapings',\n",
    "    'RO_BF_separator',\n",
    "    'RO_combined',\n",
    "    'RO_3stage', 'RO_2stage', 'RO',\n",
    "    'cond_perm', 'site_ut', 'Loop_1', 'Loop_2', 'Loop_3', 'Loop_4', 'Loop_5',\n",
    "    'DWDS_328', 'DWDS_286', 'DWDS_282B')\n",
    "metadata_complete$location_code <- factor(metadata_complete$location_code, levels=location_levels)\n",
    "\n",
    "head(metadata_complete)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sample_data(ps) <- metadata_complete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#change ASV names to short codes, store DNA seqs in ref_seq object in phyloseq\n",
    "dna <- Biostrings::DNAStringSet(taxa_names(ps))\n",
    "names(dna) <- taxa_names(ps)\n",
    "ps <- merge_phyloseq(ps, dna)\n",
    "taxa_names(ps) <- paste0(\"ASV\", seq(ntaxa(ps)))\n",
    "ps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "saveRDS(ps, '~/data/awtp2_16S/ps_clean_filt_metadata.rds')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Update AWTP names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select only samples of interest\n",
    "ps_dpwf <- subset_samples(ps, subset = project_type %in% c(\"DPWF\", \"ECAWPC\"))\n",
    "ps_dpwf <- filter_taxa(ps_dpwf, function(x) sum(x) > 0, TRUE) #keep only seqs that had reads"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sample_data(ps_dpwf)$location_gen <- sample_data(ps_dpwf)$location_code\n",
    "                                            \n",
    "new_levels <- c('WW_3ary',\n",
    "'BAC',\n",
    "'MF_combined',\n",
    "'MF_tank',\n",
    "'MF',\n",
    "'RO_BF_retentate_scrapings',\n",
    "'RO_BF_separator',\n",
    "'RO_combined',\n",
    "'RO_3stage',\n",
    "'RO_2stage',\n",
    "'RO')\n",
    "\n",
    "sample_data(ps_dpwf)$location_gen <- factor(sample_data(ps_dpwf)$location_gen, levels=new_levels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# change factor levels to condense down to simple location\n",
    "sample_data(ps_dpwf)[sample_data(ps_dpwf)$location_code %in% c(\"MF_tank\", \"MF_combined\"),]$location_gen <- \"MF\"\n",
    "sample_data(ps_dpwf)[sample_data(ps_dpwf)$location_code %in% c(\"RO\", \"RO_combined\", \"RO_3stage\", \"RO_2stage\"),]$location_gen <- \"RO\""
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.3.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}