q3_2022_gb_counts.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fcbcdaa3",
   "metadata": {
    "papermill": {
     "duration": 0.035842,
     "end_time": "2021-10-20T18:01:02.036399",
     "exception": false,
     "start_time": "2021-10-20T18:01:02.000557",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "**Update these values, and then Restart and Run All**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "84bb1d84",
   "metadata": {
    "papermill": {
     "duration": 0.04924,
     "end_time": "2021-10-20T18:01:02.119110",
     "exception": false,
     "start_time": "2021-10-20T18:01:02.069870",
     "status": "completed"
    },
    "tags": [
     "parameters"
    ]
   },
   "outputs": [],
   "source": [
    "Q = 3\n",
    "YEAR = 2022"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "72a46a07",
   "metadata": {
    "papermill": {
     "duration": 0.039199,
     "end_time": "2021-10-20T18:01:02.189824",
     "exception": false,
     "start_time": "2021-10-20T18:01:02.150625",
     "status": "completed"
    },
    "tags": [
     "injected-parameters"
    ]
   },
   "outputs": [],
   "source": [
    "# Parameters\n",
    "Q = 3\n",
    "YEAR = 2022\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "25a53d02",
   "metadata": {
    "papermill": {
     "duration": 1.033895,
     "end_time": "2021-10-20T18:01:03.255834",
     "exception": false,
     "start_time": "2021-10-20T18:01:02.221939",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import genetic_collections as gc\n",
    "import tarfile\n",
    "import io\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21e4ddd2",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Downloading GenBank Records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e35921c5",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "q_end_dates = {1: '3/31',\n",
    "               2: '6/30',\n",
    "               3: '9/31',\n",
    "               4: '12/31'}\n",
    "bioprojects = {'GGI': '384793',\n",
    "               'SIBN': '81359'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e2095248",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "GGI 14251\n",
      "GGI 14251\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14251 entries, 0 to 14250\n",
      "Data columns (total 20 columns):\n",
      " #   Column            Non-Null Count  Dtype \n",
      "---  ------            --------------  ----- \n",
      " 0   accession         14251 non-null  object\n",
      " 1   scientific_name   14251 non-null  object\n",
      " 2   publish_date      14251 non-null  object\n",
      " 3   update_date       14251 non-null  object\n",
      " 4   keyword           14251 non-null  object\n",
      " 5   bioproject        14251 non-null  object\n",
      " 6   seq_len           14251 non-null  object\n",
      " 7   submit_authors    14251 non-null  object\n",
      " 8   submit_date       14251 non-null  object\n",
      " 9   submit_inst       14251 non-null  object\n",
      " 10  specimen_voucher  14201 non-null  object\n",
      " 11  taxid             14251 non-null  object\n",
      " 12  country           14196 non-null  object\n",
      " 13  collection_date   12313 non-null  object\n",
      " 14  collected_by      12213 non-null  object\n",
      " 15  PCR_primers       12106 non-null  object\n",
      " 16  lat_lon           11668 non-null  object\n",
      " 17  bold_id           4315 non-null   object\n",
      " 18  identified_by     1781 non-null   object\n",
      " 19  initiative        14251 non-null  object\n",
      "dtypes: object(20)\n",
      "memory usage: 2.2+ MB\n",
      "None\n",
      "SIBN 32575\n",
      "SIBN 32575\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 32575 entries, 0 to 32574\n",
      "Data columns (total 22 columns):\n",
      " #   Column            Non-Null Count  Dtype \n",
      "---  ------            --------------  ----- \n",
      " 0   accession         32575 non-null  object\n",
      " 1   scientific_name   32575 non-null  object\n",
      " 2   publish_date      32575 non-null  object\n",
      " 3   update_date       32575 non-null  object\n",
      " 4   keyword           32575 non-null  object\n",
      " 5   bioproject        32575 non-null  object\n",
      " 6   seq_len           32575 non-null  object\n",
      " 7   submit_authors    32575 non-null  object\n",
      " 8   submit_date       32575 non-null  object\n",
      " 9   submit_inst       32575 non-null  object\n",
      " 10  specimen_voucher  32553 non-null  object\n",
      " 11  taxid             32575 non-null  object\n",
      " 12  biosample         324 non-null    object\n",
      " 13  sra               20 non-null     object\n",
      " 14  country           31839 non-null  object\n",
      " 15  collection_date   30749 non-null  object\n",
      " 16  lat_lon           23589 non-null  object\n",
      " 17  collected_by      22420 non-null  object\n",
      " 18  identified_by     21125 non-null  object\n",
      " 19  PCR_primers       27788 non-null  object\n",
      " 20  bold_id           25137 non-null  object\n",
      " 21  initiative        32575 non-null  object\n",
      "dtypes: object(22)\n",
      "memory usage: 5.5+ MB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "project_results = []\n",
    "for initiative, bioproject_code in bioprojects.items():\n",
    "    gb_query = f'{bioproject_code}[BioProject] AND (\"1900/01/01\"[PDAT] : \"{YEAR}/{q_end_dates[Q]}\" [PDAT])'\n",
    "    gb_search_results = gc.gb_search(raw_query=gb_query)\n",
    "    print(initiative, len(gb_search_results.id_list))\n",
    "    gb_results = gc.gb_fetch_from_id_list(gb_search_results.id_list)\n",
    "    print(initiative, len(gb_results))\n",
    "    project_df = pd.DataFrame(gb_results)\n",
    "    project_df['initiative'] = initiative\n",
    "    print(project_df.info())\n",
    "    project_results.append(project_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d70f94c8",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 46826 entries, 0 to 32574\n",
      "Data columns (total 7 columns):\n",
      " #   Column            Non-Null Count  Dtype \n",
      "---  ------            --------------  ----- \n",
      " 0   initiative        46826 non-null  object\n",
      " 1   bioproject        46826 non-null  object\n",
      " 2   accession         46826 non-null  object\n",
      " 3   specimen_voucher  46754 non-null  object\n",
      " 4   publish_date      46826 non-null  object\n",
      " 5   quarter           46826 non-null  object\n",
      " 6   taxid             46826 non-null  object\n",
      "dtypes: object(7)\n",
      "memory usage: 2.9+ MB\n"
     ]
    }
   ],
   "source": [
    "gb_df = pd.concat(project_results)\n",
    "gb_df['quarter'] = pd.to_datetime(gb_df['publish_date']).dt.year.astype(str) + '-Q' +\\\n",
    "                     pd.to_datetime(gb_df['publish_date']).dt.quarter.astype(str)\n",
    "keep_cols = ['initiative','bioproject','accession','specimen_voucher','publish_date','quarter','taxid']\n",
    "gb_df = gb_df[keep_cols]\n",
    "gb_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "06e94430",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "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>initiative</th>\n",
       "      <th>bioproject</th>\n",
       "      <th>accession</th>\n",
       "      <th>specimen_voucher</th>\n",
       "      <th>publish_date</th>\n",
       "      <th>quarter</th>\n",
       "      <th>taxid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GGI</td>\n",
       "      <td>PRJNA801314</td>\n",
       "      <td>OM460701</td>\n",
       "      <td>US:D&amp;ML 70468</td>\n",
       "      <td>30-MAR-2022</td>\n",
       "      <td>2022-Q1</td>\n",
       "      <td>74090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GGI</td>\n",
       "      <td>PRJNA801314</td>\n",
       "      <td>OM460700</td>\n",
       "      <td>US:D&amp;ML 69713</td>\n",
       "      <td>30-MAR-2022</td>\n",
       "      <td>2022-Q1</td>\n",
       "      <td>2071691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GGI</td>\n",
       "      <td>PRJNA801314</td>\n",
       "      <td>OM460699</td>\n",
       "      <td>US:D&amp;ML 68816</td>\n",
       "      <td>30-MAR-2022</td>\n",
       "      <td>2022-Q1</td>\n",
       "      <td>2877961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GGI</td>\n",
       "      <td>PRJNA801314</td>\n",
       "      <td>OM460698</td>\n",
       "      <td>US:D&amp;ML 68293</td>\n",
       "      <td>30-MAR-2022</td>\n",
       "      <td>2022-Q1</td>\n",
       "      <td>2306629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GGI</td>\n",
       "      <td>PRJNA801314</td>\n",
       "      <td>OM460697</td>\n",
       "      <td>US:D&amp;ML 63797</td>\n",
       "      <td>30-MAR-2022</td>\n",
       "      <td>2022-Q1</td>\n",
       "      <td>2116083</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  initiative   bioproject accession specimen_voucher publish_date  quarter  \\\n",
       "0        GGI  PRJNA801314  OM460701    US:D&ML 70468  30-MAR-2022  2022-Q1   \n",
       "1        GGI  PRJNA801314  OM460700    US:D&ML 69713  30-MAR-2022  2022-Q1   \n",
       "2        GGI  PRJNA801314  OM460699    US:D&ML 68816  30-MAR-2022  2022-Q1   \n",
       "3        GGI  PRJNA801314  OM460698    US:D&ML 68293  30-MAR-2022  2022-Q1   \n",
       "4        GGI  PRJNA801314  OM460697    US:D&ML 63797  30-MAR-2022  2022-Q1   \n",
       "\n",
       "     taxid  \n",
       "0    74090  \n",
       "1  2071691  \n",
       "2  2877961  \n",
       "3  2306629  \n",
       "4  2116083  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gb_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "19f9beb0",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "gb_df['taxid'] = gb_df['taxid'].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f363c9cf",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Downloading NCBI Taxonomy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "2f925b2f",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "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>taxid</th>\n",
       "      <th>tax_name</th>\n",
       "      <th>species</th>\n",
       "      <th>genus</th>\n",
       "      <th>family</th>\n",
       "      <th>order</th>\n",
       "      <th>class</th>\n",
       "      <th>phylum</th>\n",
       "      <th>kingdom</th>\n",
       "      <th>superkingdom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>root</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>131567</td>\n",
       "      <td>cellular organisms</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2157</td>\n",
       "      <td>Archaea</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1935183</td>\n",
       "      <td>Asgard group</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Archaea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2798909</td>\n",
       "      <td>Candidatus Baldrarchaeota</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Archaea</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     taxid                   tax_name species genus family order class phylum  \\\n",
       "0        1                       root     NaN   NaN    NaN   NaN   NaN    NaN   \n",
       "1   131567         cellular organisms     NaN   NaN    NaN   NaN   NaN    NaN   \n",
       "2     2157                    Archaea     NaN   NaN    NaN   NaN   NaN    NaN   \n",
       "3  1935183               Asgard group     NaN   NaN    NaN   NaN   NaN    NaN   \n",
       "4  2798909  Candidatus Baldrarchaeota     NaN   NaN    NaN   NaN   NaN    NaN   \n",
       "\n",
       "  kingdom superkingdom  \n",
       "0     NaN               \n",
       "1     NaN               \n",
       "2     NaN               \n",
       "3     NaN      Archaea  \n",
       "4     NaN      Archaea  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#hydra_tax_dir = Path('/scratch/dbs/blast/taxonomy')\n",
    "#new_tax_loc = hydra_tax_dir / 'new_taxdump.latest.tar.gz'\n",
    "new_tax_loc = 'new_taxdump.tar.gz'\n",
    "\n",
    "with tarfile.open(new_tax_loc, \"r:gz\") as tar:\n",
    "    rankedlineage = tar.extractfile('rankedlineage.dmp').read()\n",
    "    ranked_taxa = pd.read_csv(io.BytesIO(rankedlineage), \n",
    "                     encoding='utf8', \n",
    "                     sep='\\t\\|\\t', engine='python',\n",
    "                     error_bad_lines=False,\n",
    "                     names=['taxid','tax_name','species','genus','family',\n",
    "                            'order','class','phylum','kingdom','superkingdom'])\n",
    "ranked_taxa['superkingdom'] = ranked_taxa['superkingdom'].str.rstrip('\\t|')\n",
    "ranked_taxa.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b6e5cb5",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Combining GenBank with Taxonomy and exporting report"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "63d1fae2",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20332\n",
      "[  74090 2071691 2877961 2306629 2116083 2850695  911171  437227 2575620\n",
      "  485435]\n"
     ]
    }
   ],
   "source": [
    "unique_taxids = gb_df['taxid'].unique()\n",
    "print(len(unique_taxids))\n",
    "print(unique_taxids[:10])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d3169f7d",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 20332 entries, 92895 to 2209809\n",
      "Data columns (total 10 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   taxid         20332 non-null  int64 \n",
      " 1   tax_name      20332 non-null  object\n",
      " 2   species       346 non-null    object\n",
      " 3   genus         17577 non-null  object\n",
      " 4   family        19625 non-null  object\n",
      " 5   order         19936 non-null  object\n",
      " 6   class         20040 non-null  object\n",
      " 7   phylum        20323 non-null  object\n",
      " 8   kingdom       20210 non-null  object\n",
      " 9   superkingdom  20332 non-null  object\n",
      "dtypes: int64(1), object(9)\n",
      "memory usage: 1.7+ MB\n"
     ]
    }
   ],
   "source": [
    "taxa_subset = ranked_taxa[ranked_taxa['taxid'].isin(unique_taxids)]\n",
    "taxa_subset.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a4e78349",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  initiative   bioproject accession specimen_voucher publish_date  quarter  \\\n",
      "0        GGI  PRJNA801314  OM460701    US:D&ML 70468  30-MAR-2022  2022-Q1   \n",
      "1        GGI  PRJNA801314  OM460700    US:D&ML 69713  30-MAR-2022  2022-Q1   \n",
      "2        GGI  PRJNA801314  OM460699    US:D&ML 68816  30-MAR-2022  2022-Q1   \n",
      "3        GGI  PRJNA801314  OM460698    US:D&ML 68293  30-MAR-2022  2022-Q1   \n",
      "4        GGI  PRJNA801314  OM460697    US:D&ML 63797  30-MAR-2022  2022-Q1   \n",
      "\n",
      "     taxid            family           genus  \n",
      "0    74090      Sargassaceae       Sargassum  \n",
      "1  2071691    Spongitidaceae  Neogoniolithon  \n",
      "2  2877961  Scytosiphonaceae   Hydroclathrus  \n",
      "3  2306629     Rhodomelaceae         Amansia  \n",
      "4  2116083  Peyssonneliaceae      Ramicrusta  \n"
     ]
    }
   ],
   "source": [
    "merged_df = gb_df.merge(taxa_subset[['taxid','family','genus']], on='taxid')\n",
    "print(merged_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "3e3a222c",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "merged_outfile = f'GB_Taxonomy_Results_{YEAR}_Q{Q}.tsv'\n",
    "merged_df.to_csv(merged_outfile, sep='\\t', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8affbb7b",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Calculating Quarterly Totals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6f09f942",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "initiative  quarter\n",
       "GGI         2016-Q1     145\n",
       "            2017-Q1       8\n",
       "            2017-Q2     107\n",
       "            2017-Q3    2034\n",
       "            2017-Q4    3043\n",
       "            2018-Q1     372\n",
       "            2018-Q2    1856\n",
       "            2018-Q3     165\n",
       "            2018-Q4    1314\n",
       "            2019-Q1       2\n",
       "            2019-Q2      28\n",
       "            2019-Q3     400\n",
       "            2019-Q4      86\n",
       "            2020-Q2      61\n",
       "            2020-Q4     193\n",
       "            2021-Q1     113\n",
       "            2021-Q2     659\n",
       "            2021-Q3     346\n",
       "            2021-Q4     359\n",
       "            2022-Q1    2960\n",
       "SIBN        2011-Q4    2778\n",
       "            2014-Q2      12\n",
       "            2015-Q1     805\n",
       "            2015-Q3     966\n",
       "            2015-Q4    1836\n",
       "            2016-Q1     534\n",
       "            2016-Q2     498\n",
       "            2016-Q4     256\n",
       "            2017-Q2      27\n",
       "            2017-Q4    1947\n",
       "            2018-Q1     920\n",
       "            2018-Q2     906\n",
       "            2018-Q3    2266\n",
       "            2018-Q4    1728\n",
       "            2019-Q1     380\n",
       "            2019-Q2     100\n",
       "            2019-Q3    4726\n",
       "            2019-Q4    2773\n",
       "            2020-Q2    2253\n",
       "            2021-Q2    2235\n",
       "            2021-Q3    3707\n",
       "            2021-Q4      78\n",
       "            2022-Q1     199\n",
       "            2022-Q2      40\n",
       "            2022-Q3     605\n",
       "dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.groupby(['initiative','quarter']).size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "c1eb64d2",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "quarter_target = f'{YEAR}-Q{Q}'\n",
    "\n",
    "sibn_q = merged_df[(merged_df['initiative'] == 'SIBN') & (merged_df['quarter'] == quarter_target)]\n",
    "sibn_total = merged_df[merged_df['initiative'] == 'SIBN']\n",
    "ggi_q = merged_df[(merged_df['initiative'] == 'GGI') & (merged_df['quarter'] == quarter_target)]\n",
    "ggi_total = merged_df[merged_df['initiative'] == 'GGI']\n",
    "both_q = merged_df[merged_df['quarter'] == quarter_target]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d0238aae",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "from collections import OrderedDict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "11782054",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_labels = OrderedDict([(f'SIBN Q{Q}', sibn_q),\n",
    "                         ('SIBN Total', sibn_total),\n",
    "                         (f'GGI Q{Q}', ggi_q),\n",
    "                         ('GGI Total', ggi_total),\n",
    "                         (f'Both Initiatives Q{Q}', both_q),\n",
    "                         ('Both Initiatives Total', merged_df)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "d76aa2d6",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style  type=\"text/css\" >\n",
       "</style><table id=\"T_d2ffe_\" ><thead>    <tr>        <th class=\"blank level0\" ></th>        <th class=\"col_heading level0 col0\" >SIBN Q3</th>        <th class=\"col_heading level0 col1\" >SIBN Total</th>        <th class=\"col_heading level0 col2\" >GGI Q3</th>        <th class=\"col_heading level0 col3\" >GGI Total</th>        <th class=\"col_heading level0 col4\" >Both Initiatives Q3</th>        <th class=\"col_heading level0 col5\" >Both Initiatives Total</th>    </tr></thead><tbody>\n",
       "                <tr>\n",
       "                        <th id=\"T_d2ffe_level0_row0\" class=\"row_heading level0 row0\" >Sequence Records</th>\n",
       "                        <td id=\"T_d2ffe_row0_col0\" class=\"data row0 col0\" >605</td>\n",
       "                        <td id=\"T_d2ffe_row0_col1\" class=\"data row0 col1\" >32575</td>\n",
       "                        <td id=\"T_d2ffe_row0_col2\" class=\"data row0 col2\" >0</td>\n",
       "                        <td id=\"T_d2ffe_row0_col3\" class=\"data row0 col3\" >14251</td>\n",
       "                        <td id=\"T_d2ffe_row0_col4\" class=\"data row0 col4\" >605</td>\n",
       "                        <td id=\"T_d2ffe_row0_col5\" class=\"data row0 col5\" >46826</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_d2ffe_level0_row1\" class=\"row_heading level0 row1\" >Specimens</th>\n",
       "                        <td id=\"T_d2ffe_row1_col0\" class=\"data row1 col0\" >596</td>\n",
       "                        <td id=\"T_d2ffe_row1_col1\" class=\"data row1 col1\" >29698</td>\n",
       "                        <td id=\"T_d2ffe_row1_col2\" class=\"data row1 col2\" >0</td>\n",
       "                        <td id=\"T_d2ffe_row1_col3\" class=\"data row1 col3\" >7397</td>\n",
       "                        <td id=\"T_d2ffe_row1_col4\" class=\"data row1 col4\" >596</td>\n",
       "                        <td id=\"T_d2ffe_row1_col5\" class=\"data row1 col5\" >37095</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_d2ffe_level0_row2\" class=\"row_heading level0 row2\" >Families</th>\n",
       "                        <td id=\"T_d2ffe_row2_col0\" class=\"data row2 col0\" >132</td>\n",
       "                        <td id=\"T_d2ffe_row2_col1\" class=\"data row2 col1\" >1205</td>\n",
       "                        <td id=\"T_d2ffe_row2_col2\" class=\"data row2 col2\" >0</td>\n",
       "                        <td id=\"T_d2ffe_row2_col3\" class=\"data row2 col3\" >1046</td>\n",
       "                        <td id=\"T_d2ffe_row2_col4\" class=\"data row2 col4\" >132</td>\n",
       "                        <td id=\"T_d2ffe_row2_col5\" class=\"data row2 col5\" >1865</td>\n",
       "            </tr>\n",
       "            <tr>\n",
       "                        <th id=\"T_d2ffe_level0_row3\" class=\"row_heading level0 row3\" >Genera</th>\n",
       "                        <td id=\"T_d2ffe_row3_col0\" class=\"data row3 col0\" >357</td>\n",
       "                        <td id=\"T_d2ffe_row3_col1\" class=\"data row3 col1\" >7551</td>\n",
       "                        <td id=\"T_d2ffe_row3_col2\" class=\"data row3 col2\" >0</td>\n",
       "                        <td id=\"T_d2ffe_row3_col3\" class=\"data row3 col3\" >3249</td>\n",
       "                        <td id=\"T_d2ffe_row3_col4\" class=\"data row3 col4\" >357</td>\n",
       "                        <td id=\"T_d2ffe_row3_col5\" class=\"data row3 col5\" >10349</td>\n",
       "            </tr>\n",
       "    </tbody></table>"
      ],
      "text/plain": [
       "<pandas.io.formats.style.Styler at 0x2aac89f7d310>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counts_dict = OrderedDict()\n",
    "for title, df in df_labels.items():\n",
    "    counts_dict[title] = OrderedDict([\n",
    "                            ('Sequence Records', len(df)),\n",
    "                            ('Specimens', df['specimen_voucher'].nunique()),\n",
    "                            ('Families', df['family'].nunique()),\n",
    "                            ('Genera', df['genus'].nunique())])\n",
    "count_df = pd.DataFrame(counts_dict)\n",
    "count_df.style"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "ae7795c9",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "count_df.to_csv(f'GenBank_Counts_{YEAR}_Q{Q}.tsv', sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "d9d04140",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "summary_file = f'GenBank_Counts_{YEAR}_Q{Q}.xlsx'\n",
    "count_df.to_excel(summary_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3c1827e",
   "metadata": {
    "papermill": {
     "duration": null,
     "end_time": null,
     "exception": null,
     "start_time": null,
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "celltoolbar": "Tags",
  "kernelspec": {
   "display_name": "sibn_kernel",
   "language": "python",
   "name": "sibn_kernel"
  },
  "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.9.4"
  },
  "metadata": {
   "interpreter": {
    "hash": "919fa3b59220ff833e47a1bcc23e9d11987d3b1bf0ede7efbcf6655fc077b907"
   }
  },
  "papermill": {
   "default_parameters": {},
   "duration": 4.174255,
   "end_time": "2021-10-20T18:01:03.772952",
   "environment_variables": {},
   "exception": null,
   "input_path": "Quarterly_GenBank_Counts.ipynb",
   "output_path": "q3_2021_gb_counts.ipynb",
   "parameters": {
    "Q": 3,
    "YEAR": 2021
   },
   "start_time": "2021-10-20T18:00:59.598697",
   "version": "2.3.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}