src/notebooks/package_review_vs_INSDC.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# requires (unimported) openpyxl\n",
    "import pandas as pd\n",
    "import sqlite3\n",
    "\n",
    "import scoped_mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://github.com/microbiomedata/nmdc-metadata/issues/371#event-4949651397\n",
    "# https://docs.google.com/spreadsheets/d/1WErXj8sM5uJi51VVLNQZDilDF7wMiyBC2T4zELp7Axc/edit#gid=1239761730\n",
    "\n",
    "package_review_blm_fp = '../../data/EnvO_triad_terms_MIxS_soil_package_review_05182021.xlsx'\n",
    "\n",
    "package_review_vs_INSDC_fp = \"../../target/package_review_vs_INSDC.tsv\"\n",
    "\n",
    "biosample_sqlite_file = \"../../target/harmonized-table.db\"\n",
    "\n",
    "biosample_cnx = sqlite3.connect(biosample_sqlite_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# implicitly opens first sheet \"Subset_EnvO_Broad_Local_Medium_\"\n",
    "package_review_blm = pd.read_excel(\n",
    "     package_review_blm_fp, engine='openpyxl'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>slot</th>\n",
       "      <th>class</th>\n",
       "      <th>label</th>\n",
       "      <th>packages</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:00000011</td>\n",
       "      <td>garden</td>\n",
       "      <td>Soil|Plant-associated</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:00000014</td>\n",
       "      <td>canal</td>\n",
       "      <td>Water</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:00000020</td>\n",
       "      <td>lake</td>\n",
       "      <td>Sediment|Water</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:00000022</td>\n",
       "      <td>river</td>\n",
       "      <td>Sediment|Water</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:00000023</td>\n",
       "      <td>stream</td>\n",
       "      <td>Water</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239</th>\n",
       "      <td>env_medium</td>\n",
       "      <td>ENVO:01001841</td>\n",
       "      <td>volcanic soil</td>\n",
       "      <td>Soil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:02000049</td>\n",
       "      <td>coastal water body</td>\n",
       "      <td>Water</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>env_medium</td>\n",
       "      <td>ENVO:02000059</td>\n",
       "      <td>surface soil</td>\n",
       "      <td>Soil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:03000117</td>\n",
       "      <td>channel</td>\n",
       "      <td>Soil|Sediment|Water</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:2000004</td>\n",
       "      <td>algal bloom</td>\n",
       "      <td>Water|Host-associted</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>241 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                slot          class               label               packages\n",
       "103  env_local_scale  ENVO:00000011              garden  Soil|Plant-associated\n",
       "86   env_local_scale  ENVO:00000014               canal                  Water\n",
       "115  env_local_scale  ENVO:00000020                lake         Sediment|Water\n",
       "136  env_local_scale  ENVO:00000022               river         Sediment|Water\n",
       "144  env_local_scale  ENVO:00000023              stream                  Water\n",
       "..               ...            ...                 ...                    ...\n",
       "239       env_medium  ENVO:01001841       volcanic soil                   Soil\n",
       "92   env_local_scale  ENVO:02000049  coastal water body                  Water\n",
       "229       env_medium  ENVO:02000059        surface soil                   Soil\n",
       "89   env_local_scale  ENVO:03000117             channel    Soil|Sediment|Water\n",
       "80   env_local_scale   ENVO:2000004         algal bloom   Water|Host-associted\n",
       "\n",
       "[241 rows x 4 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "package_review_blm.columns=['class','label','definition','packages','extra']\n",
    "package_review_blm['slot'] = package_review_blm.index\n",
    "package_review_blm = package_review_blm[['slot','class','label','packages']]\n",
    "package_review_blm = package_review_blm.reset_index(drop=True)\n",
    "\n",
    "# convert class IRIs to CURIEs... all envo?\n",
    "# not the best approach by a long shot\n",
    "package_review_blm['class'] = package_review_blm['class'].str.replace('<http://purl.obolibrary.org/obo/ENVO_', 'ENVO:', regex=False)\n",
    "package_review_blm['class'] = package_review_blm['class'].str.replace('>', '', regex=False)\n",
    "package_review_blm = package_review_blm.sort_values(by = ['class'])\n",
    "\n",
    "package_review_blm\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Soil                              141\n",
       "Water                              43\n",
       "Sediment|Water                     15\n",
       "Water|Sediment                      8\n",
       "Sediment                            6\n",
       "Soil|Water                          5\n",
       "Soil|Sediment|Water                 4\n",
       "Water|Host-associted                3\n",
       "Soil|Sediment                       3\n",
       "HCR-cores|HCR-fluids-swabs          3\n",
       "Soil|Plant-associated               3\n",
       "Water|Host-associated               2\n",
       "Soil|Sediment|Plant-associated      1\n",
       "Water|Air                           1\n",
       "Built-environment                   1\n",
       "Host-associated                     1\n",
       "Plant-associated                    1\n",
       "Name: packages, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "package_review_blm['packages'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "miniframes = []\n",
    "for row in package_review_blm.itertuples(index=True, name='Pandas'):\n",
    "    tuple_index = row[0]\n",
    "    rp = row.packages\n",
    "    rps = rp.split(\"|\")\n",
    "    rpss = pd.Series(rps)\n",
    "    rps_len = len(rps)\n",
    "    index_rep = [tuple_index] * rps_len\n",
    "    irs = pd.Series(index_rep)\n",
    "    temp = pd.concat([irs, rpss], axis=1)\n",
    "    miniframes.append(temp)\n",
    "    \n",
    "maxiframe = pd.concat(miniframes)\n",
    "\n",
    "maxiframe.columns = ['saved_index','split_val']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>saved_index</th>\n",
       "      <th>split_val</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>103</td>\n",
       "      <td>Soil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>103</td>\n",
       "      <td>Plant-associated</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>86</td>\n",
       "      <td>Water</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>115</td>\n",
       "      <td>Sediment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>115</td>\n",
       "      <td>Water</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>89</td>\n",
       "      <td>Soil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>89</td>\n",
       "      <td>Sediment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>89</td>\n",
       "      <td>Water</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80</td>\n",
       "      <td>Water</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>80</td>\n",
       "      <td>Host-associted</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>294 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    saved_index         split_val\n",
       "0           103              Soil\n",
       "1           103  Plant-associated\n",
       "0            86             Water\n",
       "0           115          Sediment\n",
       "1           115             Water\n",
       "..          ...               ...\n",
       "0            89              Soil\n",
       "1            89          Sediment\n",
       "2            89             Water\n",
       "0            80             Water\n",
       "1            80    Host-associted\n",
       "\n",
       "[294 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "maxiframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged = package_review_blm.merge(maxiframe, left_index=True, right_on='saved_index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>package</th>\n",
       "      <th>slot</th>\n",
       "      <th>class</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>soil</td>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:00000011</td>\n",
       "      <td>garden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>plant-associated</td>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:00000011</td>\n",
       "      <td>garden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>water</td>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:00000014</td>\n",
       "      <td>canal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sediment</td>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:00000020</td>\n",
       "      <td>lake</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>water</td>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:00000020</td>\n",
       "      <td>lake</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>soil</td>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:03000117</td>\n",
       "      <td>channel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sediment</td>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:03000117</td>\n",
       "      <td>channel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>water</td>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:03000117</td>\n",
       "      <td>channel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>water</td>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:2000004</td>\n",
       "      <td>algal bloom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>host-associted</td>\n",
       "      <td>env_local_scale</td>\n",
       "      <td>ENVO:2000004</td>\n",
       "      <td>algal bloom</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>294 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             package             slot          class        label\n",
       "0               soil  env_local_scale  ENVO:00000011       garden\n",
       "1   plant-associated  env_local_scale  ENVO:00000011       garden\n",
       "0              water  env_local_scale  ENVO:00000014        canal\n",
       "0           sediment  env_local_scale  ENVO:00000020         lake\n",
       "1              water  env_local_scale  ENVO:00000020         lake\n",
       "..               ...              ...            ...          ...\n",
       "0               soil  env_local_scale  ENVO:03000117      channel\n",
       "1           sediment  env_local_scale  ENVO:03000117      channel\n",
       "2              water  env_local_scale  ENVO:03000117      channel\n",
       "0              water  env_local_scale   ENVO:2000004  algal bloom\n",
       "1     host-associted  env_local_scale   ENVO:2000004  algal bloom\n",
       "\n",
       "[294 rows x 4 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged = merged[['split_val','slot','class','label']]\n",
    "\n",
    "merged.columns = ['package','slot','class','label']\n",
    "\n",
    "merged['package'] = merged['package'].str.lower()\n",
    "merged['label'] = merged['label'].str.lower()\n",
    "\n",
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged.to_csv(\"package_review_blm_long.tsv\", sep='\\t', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0:00:00.186243\n"
     ]
    }
   ],
   "source": [
    "q = \"\"\"\n",
    "select\n",
    "\tscoping_value ,\n",
    "\tbiosample_col_to_map,\n",
    "\tconsensus_id ,\n",
    "\tconsensus_lab,\n",
    "\tcount(1) as count\n",
    "from\n",
    "\trepaired_long rl\n",
    "group by\n",
    "\tscoping_value ,\n",
    "\tbiosample_col_to_map,\n",
    "\tconsensus_id ,\n",
    "\tconsensus_lab\n",
    "order by\n",
    "\tscoping_value ,\n",
    "\tbiosample_col_to_map,\n",
    "\tconsensus_lab;\n",
    "\"\"\"\n",
    "[insdc_mixs_envo_usage, query_duration] = scoped_mapping.timed_query(q, biosample_cnx)\n",
    "\n",
    "print(query_duration)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>scoping_value</th>\n",
       "      <th>biosample_col_to_map</th>\n",
       "      <th>consensus_id</th>\n",
       "      <th>consensus_lab</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>plant-associated</td>\n",
       "      <td>env_broad_scale</td>\n",
       "      <td>ENVO:00000114</td>\n",
       "      <td>agricultural field</td>\n",
       "      <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>plant-associated</td>\n",
       "      <td>env_broad_scale</td>\n",
       "      <td>ENVO:01000245</td>\n",
       "      <td>cropland biome</td>\n",
       "      <td>825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>plant-associated</td>\n",
       "      <td>env_broad_scale</td>\n",
       "      <td>ENVO:01000311</td>\n",
       "      <td>cultivated environment</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>plant-associated</td>\n",
       "      <td>env_broad_scale</td>\n",
       "      <td>ENVO:00002011</td>\n",
       "      <td>fresh water</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>plant-associated</td>\n",
       "      <td>env_broad_scale</td>\n",
       "      <td>ENVO:00000873</td>\n",
       "      <td>freshwater biome</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>711</th>\n",
       "      <td>water</td>\n",
       "      <td>env_medium</td>\n",
       "      <td>ENVO:00002001</td>\n",
       "      <td>waste water</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>712</th>\n",
       "      <td>water</td>\n",
       "      <td>env_medium</td>\n",
       "      <td>CHEBI:15377</td>\n",
       "      <td>water</td>\n",
       "      <td>4301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>713</th>\n",
       "      <td>water</td>\n",
       "      <td>env_medium</td>\n",
       "      <td>ENVO:06105011</td>\n",
       "      <td>water column</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>714</th>\n",
       "      <td>water</td>\n",
       "      <td>env_medium</td>\n",
       "      <td>ENVO:01000277</td>\n",
       "      <td>water ice</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>715</th>\n",
       "      <td>water</td>\n",
       "      <td>env_medium</td>\n",
       "      <td>ENVO:01000436</td>\n",
       "      <td>waterborne particulate matter</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>716 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        scoping_value biosample_col_to_map   consensus_id  \\\n",
       "0    plant-associated      env_broad_scale  ENVO:00000114   \n",
       "1    plant-associated      env_broad_scale  ENVO:01000245   \n",
       "2    plant-associated      env_broad_scale  ENVO:01000311   \n",
       "3    plant-associated      env_broad_scale  ENVO:00002011   \n",
       "4    plant-associated      env_broad_scale  ENVO:00000873   \n",
       "..                ...                  ...            ...   \n",
       "711             water           env_medium  ENVO:00002001   \n",
       "712             water           env_medium    CHEBI:15377   \n",
       "713             water           env_medium  ENVO:06105011   \n",
       "714             water           env_medium  ENVO:01000277   \n",
       "715             water           env_medium  ENVO:01000436   \n",
       "\n",
       "                     consensus_lab  count  \n",
       "0               agricultural field    220  \n",
       "1                   cropland biome    825  \n",
       "2           cultivated environment      1  \n",
       "3                      fresh water     50  \n",
       "4                 freshwater biome     57  \n",
       "..                             ...    ...  \n",
       "711                    waste water      3  \n",
       "712                          water   4301  \n",
       "713                   water column     40  \n",
       "714                      water ice      5  \n",
       "715  waterborne particulate matter     13  \n",
       "\n",
       "[716 rows x 5 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "insdc_mixs_envo_usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "package_review_vs_INSDC = insdc_mixs_envo_usage.merge(merged, \n",
    "                                                      how = \"outer\", \n",
    "                                                      left_on=['consensus_id', 'scoping_value', 'biosample_col_to_map'], \n",
    "                                                      right_on=['class', 'package', 'slot'])\n",
    "\n",
    "package_review_vs_INSDC['reccomended'] = ~ package_review_vs_INSDC['class'].isna()\n",
    "package_review_vs_INSDC['package'].fillna(package_review_vs_INSDC['scoping_value'], inplace=True)\n",
    "package_review_vs_INSDC['slot'].fillna(package_review_vs_INSDC['biosample_col_to_map'], inplace=True)\n",
    "package_review_vs_INSDC['class'].fillna(package_review_vs_INSDC['consensus_id'], inplace=True)\n",
    "package_review_vs_INSDC['label'].fillna(package_review_vs_INSDC['consensus_lab'], inplace=True)\n",
    "package_review_vs_INSDC['count'].fillna(0, inplace=True)\n",
    "\n",
    "package_review_vs_INSDC = package_review_vs_INSDC[['package', 'slot', 'class', 'label', 'count', 'reccomended']]\n",
    "\n",
    "package_review_vs_INSDC = package_review_vs_INSDC.sort_values(by = ['reccomended','count'], ascending = [False, False])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>package</th>\n",
       "      <th>slot</th>\n",
       "      <th>class</th>\n",
       "      <th>label</th>\n",
       "      <th>count</th>\n",
       "      <th>reccomended</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>486</th>\n",
       "      <td>water</td>\n",
       "      <td>env_broad_scale</td>\n",
       "      <td>ENVO:00000447</td>\n",
       "      <td>marine biome</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506</th>\n",
       "      <td>water</td>\n",
       "      <td>env_broad_scale</td>\n",
       "      <td>ENVO:01000048</td>\n",
       "      <td>ocean biome</td>\n",
       "      <td>1498.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>470</th>\n",
       "      <td>water</td>\n",
       "      <td>env_broad_scale</td>\n",
       "      <td>ENVO:00000873</td>\n",
       "      <td>freshwater biome</td>\n",
       "      <td>1068.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292</th>\n",
       "      <td>soil</td>\n",
       "      <td>env_broad_scale</td>\n",
       "      <td>ENVO:01000249</td>\n",
       "      <td>urban biome</td>\n",
       "      <td>1011.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291</th>\n",
       "      <td>soil</td>\n",
       "      <td>env_broad_scale</td>\n",
       "      <td>ENVO:01000180</td>\n",
       "      <td>tundra biome</td>\n",
       "      <td>524.0</td>\n",
       "      <td>True</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>678</th>\n",
       "      <td>water</td>\n",
       "      <td>env_medium</td>\n",
       "      <td>ENVO:00000020</td>\n",
       "      <td>lake</td>\n",
       "      <td>1.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>685</th>\n",
       "      <td>water</td>\n",
       "      <td>env_medium</td>\n",
       "      <td>ENVO:01001038</td>\n",
       "      <td>moderately saline water</td>\n",
       "      <td>1.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>686</th>\n",
       "      <td>water</td>\n",
       "      <td>env_medium</td>\n",
       "      <td>ENVO:00000569</td>\n",
       "      <td>obsolete marine habitat</td>\n",
       "      <td>1.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688</th>\n",
       "      <td>water</td>\n",
       "      <td>env_medium</td>\n",
       "      <td>ENVO:00002227</td>\n",
       "      <td>obsolete saline water habitat</td>\n",
       "      <td>1.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>696</th>\n",
       "      <td>water</td>\n",
       "      <td>env_medium</td>\n",
       "      <td>ENVO:00002154</td>\n",
       "      <td>radioactive sediment</td>\n",
       "      <td>1.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>921 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    package             slot          class                          label  \\\n",
       "486   water  env_broad_scale  ENVO:00000447                   marine biome   \n",
       "506   water  env_broad_scale  ENVO:01000048                    ocean biome   \n",
       "470   water  env_broad_scale  ENVO:00000873               freshwater biome   \n",
       "292    soil  env_broad_scale  ENVO:01000249                    urban biome   \n",
       "291    soil  env_broad_scale  ENVO:01000180                   tundra biome   \n",
       "..      ...              ...            ...                            ...   \n",
       "678   water       env_medium  ENVO:00000020                           lake   \n",
       "685   water       env_medium  ENVO:01001038        moderately saline water   \n",
       "686   water       env_medium  ENVO:00000569        obsolete marine habitat   \n",
       "688   water       env_medium  ENVO:00002227  obsolete saline water habitat   \n",
       "696   water       env_medium  ENVO:00002154           radioactive sediment   \n",
       "\n",
       "      count  reccomended  \n",
       "486  3680.0         True  \n",
       "506  1498.0         True  \n",
       "470  1068.0         True  \n",
       "292  1011.0         True  \n",
       "291   524.0         True  \n",
       "..      ...          ...  \n",
       "678     1.0        False  \n",
       "685     1.0        False  \n",
       "686     1.0        False  \n",
       "688     1.0        False  \n",
       "696     1.0        False  \n",
       "\n",
       "[921 rows x 6 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "package_review_vs_INSDC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "package_review_vs_INSDC.to_csv(package_review_vs_INSDC_fp, sep='\\t', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}