clean_notebooks/ExampleDataLoading/loadInMetaData.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# load in technical meta data: relationships between sequencing runs, biospecieman annotation, study \n",
    "\n",
    "Also contains bowtie and fastqc read mapping statistics to targetted SNP reference.  \n",
    "\n",
    "For more information about the IDs being used, please reference the follow site:\n",
    "\n",
    "https://www.ncbi.nlm.nih.gov/books/NBK56913/\n",
    "\n",
    "\n",
    "Change the meta data directory to your own local copy with the same file name. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### layout of technical_meta_data_df\n",
    "\n",
    "##### SRAmeta (column level 1)\n",
    "Index: SRA sequencing runs (SRR Ids)\n",
    "\n",
    "|Column name| meaning|\n",
    "|:---:|:----:|\n",
    "|Experiment| SRA Experiment ID|\n",
    "|Sample| SRA biospecieman ID|\n",
    "|Study| SRA Study ID|\n",
    "|ScientificName|Species|\n",
    "|LibraryStrategy| {RNAseq, WXS, Chip-seq, WGS, ...} |\n",
    "|LibraryLayout| [PAIRED, SINGLE]|\n",
    "|Spots||\n",
    "|Bases| |\n",
    "|Status||\n",
    "|proj_accession_Submission||\n",
    "|proj_accession_Status||\n",
    "|proj_accession_Updated||\n",
    "|proj_accession_Published||\n",
    "|proj_accession_Received||\n",
    "|proj_accession_Type||\n",
    "|proj_accession_Center||\n",
    "|proj_accession_Visibility||\n",
    "|proj_accession_Loaded||\n",
    "|proj_accession_ReplacedBy||\n",
    "|new_ScientificName||\n",
    "|Run_db||\n",
    "|Run_digits||\n",
    "|Member_Name||\n",
    "\n",
    "##### SRAmeta (column level 1)\n",
    "** bowtie2: mapping statistics on reference SNP genome **\n",
    "\n",
    "\n",
    "|Column name| meaning|\n",
    "|:---:|:----:|\n",
    "|(\\d+) \\([0-9.]*%\\) aligned >1 times| % multi mapped reads|\n",
    "|(\\d+) \\([0-9.]*%\\) aligned exactly 1 time| % multi uniquely mapped reads |\n",
    "|(\\d+) mates make up the pairs| # of pair ended reads |\n",
    "|(\\d+) reads; of these:| # of single ended reads |\n",
    "\n",
    "##### FASTQC (column level 1)\n",
    "|Column name| meaning|\n",
    "|:---:|:----:|\n",
    "|Adapter sequence:(.*)| |\n",
    "|Reads with adapters:\\s+([0-9,]+)||\n",
    "|Total basepairs processed:\\s+([0-9,]+)||\n",
    "|Total reads processed:\\s+([0-9,]+)||\n",
    "|Total written \\(filtered\\):\\s+([0-9,]+)||"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "%%time\n",
    "sra_dump_pickle_dir='/cellar/users/btsui/Data/SRA/DUMP/sra_dump.fastqc.bowtie_algn.pickle'\n",
    "\n",
    "technical_meta_data_df=pd.read_pickle(sra_dump_pickle_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.641389754"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "technical_meta_data_df.memory_usage().sum()/10**9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# of sequencing runs in metadata table 3764409\n"
     ]
    }
   ],
   "source": [
    "print ('# of sequencing runs in metadata table',technical_meta_data_df.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"12\" halign=\"left\">SRAmeta</th>\n",
       "      <th colspan=\"4\" halign=\"left\">bowtie2</th>\n",
       "      <th colspan=\"5\" halign=\"left\">fastqc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Member_Name</th>\n",
       "      <th>Experiment</th>\n",
       "      <th>Sample</th>\n",
       "      <th>Study</th>\n",
       "      <th>Spots</th>\n",
       "      <th>Bases</th>\n",
       "      <th>Status</th>\n",
       "      <th>ScientificName</th>\n",
       "      <th>LibraryStrategy</th>\n",
       "      <th>LibraryLayout</th>\n",
       "      <th>...</th>\n",
       "      <th>Run_digits</th>\n",
       "      <th>(\\d+) \\([0-9.]*%\\) aligned &gt;1 times</th>\n",
       "      <th>(\\d+) \\([0-9.]*%\\) aligned exactly 1 time</th>\n",
       "      <th>(\\d+) mates make up the pairs</th>\n",
       "      <th>(\\d+) reads; of these:</th>\n",
       "      <th>Adapter sequence:(.*)</th>\n",
       "      <th>Reads with adapters:\\s+([0-9,]+)</th>\n",
       "      <th>Total basepairs processed:\\s+([0-9,]+)</th>\n",
       "      <th>Total reads processed:\\s+([0-9,]+)</th>\n",
       "      <th>Total written \\(filtered\\):\\s+([0-9,]+)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>SRR1767434</th>\n",
       "      <td>default</td>\n",
       "      <td>SRX848761</td>\n",
       "      <td>SRS823734</td>\n",
       "      <td>SRP052625</td>\n",
       "      <td>420572.0</td>\n",
       "      <td>175416162.0</td>\n",
       "      <td>live</td>\n",
       "      <td>Homo_sapiens</td>\n",
       "      <td>AMPLICON</td>\n",
       "      <td>SINGLE</td>\n",
       "      <td>...</td>\n",
       "      <td>1767434.0</td>\n",
       "      <td>0</td>\n",
       "      <td>918</td>\n",
       "      <td>NaN</td>\n",
       "      <td>420572</td>\n",
       "      <td>'AGATCGGAAGAGC' (Illumina TruSeq, Sanger iPCR...</td>\n",
       "      <td>205</td>\n",
       "      <td>1,000,204</td>\n",
       "      <td>2,500</td>\n",
       "      <td>965,089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRR5009468</th>\n",
       "      <td>default</td>\n",
       "      <td>SRX2340664</td>\n",
       "      <td>SRS1757305</td>\n",
       "      <td>SRP091981</td>\n",
       "      <td>81741.0</td>\n",
       "      <td>243413017.0</td>\n",
       "      <td>live</td>\n",
       "      <td>Homo_sapiens</td>\n",
       "      <td>RNA-Seq</td>\n",
       "      <td>SINGLE</td>\n",
       "      <td>...</td>\n",
       "      <td>5009468.0</td>\n",
       "      <td>0</td>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>583</td>\n",
       "      <td>'AGATCGGAAGAGC' (Illumina TruSeq, Sanger iPCR...</td>\n",
       "      <td>59</td>\n",
       "      <td>1,000,797</td>\n",
       "      <td>583</td>\n",
       "      <td>973,015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRR033781</th>\n",
       "      <td>default</td>\n",
       "      <td>SRX015665</td>\n",
       "      <td>SRS009770</td>\n",
       "      <td>SRP001703</td>\n",
       "      <td>1149373.0</td>\n",
       "      <td>654086916.0</td>\n",
       "      <td>live</td>\n",
       "      <td>Homo_sapiens</td>\n",
       "      <td>WGS</td>\n",
       "      <td>SINGLE</td>\n",
       "      <td>...</td>\n",
       "      <td>33781.0</td>\n",
       "      <td>572</td>\n",
       "      <td>35441</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1149373</td>\n",
       "      <td>'AGATCGGAAGAGC' (Illumina TruSeq, Sanger iPCR...</td>\n",
       "      <td>940</td>\n",
       "      <td>1,001,415</td>\n",
       "      <td>2,500</td>\n",
       "      <td>498,825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRR018411</th>\n",
       "      <td>default</td>\n",
       "      <td>SRX006209</td>\n",
       "      <td>SRS001567</td>\n",
       "      <td>SRP000033</td>\n",
       "      <td>1434559.0</td>\n",
       "      <td>736979719.0</td>\n",
       "      <td>live</td>\n",
       "      <td>Homo_sapiens</td>\n",
       "      <td>WXS</td>\n",
       "      <td>SINGLE</td>\n",
       "      <td>...</td>\n",
       "      <td>18411.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>'ATGGAATTCTCG' (Illumina small RNA adapter; a...</td>\n",
       "      <td>953</td>\n",
       "      <td>1,003,271</td>\n",
       "      <td>2,500</td>\n",
       "      <td>416,837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRR1371483</th>\n",
       "      <td>default</td>\n",
       "      <td>SRX585854</td>\n",
       "      <td>SRS632167</td>\n",
       "      <td>SRP002462</td>\n",
       "      <td>325657.0</td>\n",
       "      <td>178295744.0</td>\n",
       "      <td>live</td>\n",
       "      <td>human_metagenome</td>\n",
       "      <td>AMPLICON</td>\n",
       "      <td>SINGLE</td>\n",
       "      <td>...</td>\n",
       "      <td>1371483.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>'AGATCGGAAGAGC' (Illumina TruSeq, Sanger iPCR...</td>\n",
       "      <td>622</td>\n",
       "      <td>1,004,192</td>\n",
       "      <td>2,500</td>\n",
       "      <td>839,087</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               SRAmeta                                                \\\n",
       "           Member_Name  Experiment      Sample      Study      Spots   \n",
       "SRR1767434     default   SRX848761   SRS823734  SRP052625   420572.0   \n",
       "SRR5009468     default  SRX2340664  SRS1757305  SRP091981    81741.0   \n",
       "SRR033781      default   SRX015665   SRS009770  SRP001703  1149373.0   \n",
       "SRR018411      default   SRX006209   SRS001567  SRP000033  1434559.0   \n",
       "SRR1371483     default   SRX585854   SRS632167  SRP002462   325657.0   \n",
       "\n",
       "                                                                  \\\n",
       "                  Bases Status    ScientificName LibraryStrategy   \n",
       "SRR1767434  175416162.0   live      Homo_sapiens        AMPLICON   \n",
       "SRR5009468  243413017.0   live      Homo_sapiens         RNA-Seq   \n",
       "SRR033781   654086916.0   live      Homo_sapiens             WGS   \n",
       "SRR018411   736979719.0   live      Homo_sapiens             WXS   \n",
       "SRR1371483  178295744.0   live  human_metagenome        AMPLICON   \n",
       "\n",
       "                                           ...                               \\\n",
       "           LibraryLayout                   ...                   Run_digits   \n",
       "SRR1767434        SINGLE                   ...                    1767434.0   \n",
       "SRR5009468        SINGLE                   ...                    5009468.0   \n",
       "SRR033781         SINGLE                   ...                      33781.0   \n",
       "SRR018411         SINGLE                   ...                      18411.0   \n",
       "SRR1371483        SINGLE                   ...                    1371483.0   \n",
       "\n",
       "                                       bowtie2  \\\n",
       "           (\\d+) \\([0-9.]*%\\) aligned >1 times   \n",
       "SRR1767434                                   0   \n",
       "SRR5009468                                   0   \n",
       "SRR033781                                  572   \n",
       "SRR018411                                  NaN   \n",
       "SRR1371483                                 NaN   \n",
       "\n",
       "                                                      \\\n",
       "           (\\d+) \\([0-9.]*%\\) aligned exactly 1 time   \n",
       "SRR1767434                                       918   \n",
       "SRR5009468                                        64   \n",
       "SRR033781                                      35441   \n",
       "SRR018411                                        NaN   \n",
       "SRR1371483                                       NaN   \n",
       "\n",
       "                                                                 \\\n",
       "           (\\d+) mates make up the pairs (\\d+) reads; of these:   \n",
       "SRR1767434                           NaN                 420572   \n",
       "SRR5009468                           NaN                    583   \n",
       "SRR033781                            NaN                1149373   \n",
       "SRR018411                            NaN                    NaN   \n",
       "SRR1371483                           NaN                    NaN   \n",
       "\n",
       "                                                       fastqc  \\\n",
       "                                        Adapter sequence:(.*)   \n",
       "SRR1767434   'AGATCGGAAGAGC' (Illumina TruSeq, Sanger iPCR...   \n",
       "SRR5009468   'AGATCGGAAGAGC' (Illumina TruSeq, Sanger iPCR...   \n",
       "SRR033781    'AGATCGGAAGAGC' (Illumina TruSeq, Sanger iPCR...   \n",
       "SRR018411    'ATGGAATTCTCG' (Illumina small RNA adapter; a...   \n",
       "SRR1371483   'AGATCGGAAGAGC' (Illumina TruSeq, Sanger iPCR...   \n",
       "\n",
       "                                             \\\n",
       "           Reads with adapters:\\s+([0-9,]+)   \n",
       "SRR1767434                              205   \n",
       "SRR5009468                               59   \n",
       "SRR033781                               940   \n",
       "SRR018411                               953   \n",
       "SRR1371483                              622   \n",
       "\n",
       "                                                   \\\n",
       "           Total basepairs processed:\\s+([0-9,]+)   \n",
       "SRR1767434                              1,000,204   \n",
       "SRR5009468                              1,000,797   \n",
       "SRR033781                               1,001,415   \n",
       "SRR018411                               1,003,271   \n",
       "SRR1371483                              1,004,192   \n",
       "\n",
       "                                               \\\n",
       "           Total reads processed:\\s+([0-9,]+)   \n",
       "SRR1767434                              2,500   \n",
       "SRR5009468                                583   \n",
       "SRR033781                               2,500   \n",
       "SRR018411                               2,500   \n",
       "SRR1371483                              2,500   \n",
       "\n",
       "                                                    \n",
       "           Total written \\(filtered\\):\\s+([0-9,]+)  \n",
       "SRR1767434                                 965,089  \n",
       "SRR5009468                                 973,015  \n",
       "SRR033781                                  498,825  \n",
       "SRR018411                                  416,837  \n",
       "SRR1371483                                 839,087  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "technical_meta_data_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### slicing a single  sequencing run (SRR)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!ls -lah /data/cellardata/users/btsui/SRA/DUMP/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 12 ms, sys: 0 ns, total: 12 ms\n",
      "Wall time: 12.5 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "SRAmeta  Member_Name                                                                            default\n",
       "         Experiment                                                                           SRX159751\n",
       "         Sample                                                                               SRS350134\n",
       "         Study                                                                                SRP014312\n",
       "         Spots                                                                                   272785\n",
       "         Bases                                                                              1.52357e+08\n",
       "         Status                                                                                    live\n",
       "         ScientificName                                                                    Homo_sapiens\n",
       "         LibraryStrategy                                                                            WGS\n",
       "         LibraryLayout                                                                           SINGLE\n",
       "         proj_accession_Submission                                                            SRA055939\n",
       "         proj_accession_Status                                                                     live\n",
       "         proj_accession_Updated                                                     2013-07-29 00:00:00\n",
       "         proj_accession_Published                                                   2012-07-17 00:00:00\n",
       "         proj_accession_Received                                                    2012-07-13 00:00:00\n",
       "         proj_accession_Type                                                                        RUN\n",
       "         proj_accession_Center                                                  Medical University Graz\n",
       "         proj_accession_Visibility                                                               public\n",
       "         proj_accession_Loaded                                                                        1\n",
       "         proj_accession_ReplacedBy                                                                    -\n",
       "         new_ScientificName                                                                Homo_sapiens\n",
       "         Run_db                                                                                     SRR\n",
       "         Run_digits                                                                              521279\n",
       "bowtie2  (\\d+) \\([0-9.]*%\\) aligned >1 times                                                         83\n",
       "         (\\d+) \\([0-9.]*%\\) aligned exactly 1 time                                                86679\n",
       "         (\\d+) mates make up the pairs                                                              NaN\n",
       "         (\\d+) reads; of these:                                                                  272785\n",
       "fastqc   Adapter sequence:(.*)                         'AGATCGGAAGAGC' (Illumina TruSeq, Sanger iPCR...\n",
       "         Reads with adapters:\\s+([0-9,]+)                                                           863\n",
       "         Total basepairs processed:\\s+([0-9,]+)                                               1,027,903\n",
       "         Total reads processed:\\s+([0-9,]+)                                                       2,500\n",
       "         Total written \\(filtered\\):\\s+([0-9,]+)                                                768,714\n",
       "Name: SRR521279, dtype: object"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "querySrrId='SRR521279'\n",
    "%time technical_meta_data_df.loc[querySrrId]#.head(n=15)#.to_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  biospcieman metadata (SRA SRS)\n",
    "\n",
    "|Level| name|\n",
    "|--|--|\n",
    "|allSRS.index.get_level_values(0)| SRS ids|\n",
    "|allSRS.index.get_level_values(1)| BioSample attribute|\n",
    "|allSRS.values|textual annotation from submitters|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total 4.3G\r\n",
      "drwxr-xr-x  3 btsui users   14 Jul 24 20:13 .\r\n",
      "drwxr-xr-x 13 btsui users   14 Mar  1  2018 ..\r\n",
      "drwxr-xr-x  3 btsui users    3 Jan  5  2018 FULL_SRA_meta\r\n",
      "-rw-r--r--  1 btsui users 1.9G Jul 24 20:13 NCBI_SRA_Metadata_Full_20180702.tar.gz\r\n",
      "-rw-r--r--  1 btsui users 4.0G Jul 24 20:13 SRA_Accessions.tab\r\n",
      "-rw-r--r--  1 btsui users 556M Jul 24 20:13 SRA_Run_Members.tab\r\n",
      "-rw-r--r--  1 btsui users 241M Jan  5  2018 allSRS.pickle.gz\r\n",
      "-rw-r--r--  1 btsui users 133M Jan  5  2018 allSRX.pickle.gz\r\n",
      "-rw-r--r--  1 btsui users 175M Oct 30  2017 file_meta.txt\r\n",
      "-rw-r--r--  1 btsui users 139M Jul 19 18:52 merged_variant_aligning_statistics.tsv\r\n",
      "-rw-r--r--  1 btsui users 175M Oct 30  2017 meta.txt\r\n",
      "-rw-r--r--  1 btsui users 212M Aug 28 18:31 sra_dump.csv.gz\r\n",
      "-rw-r--r--  1 btsui users 659M Jul 21 12:11 sra_dump.fastqc.bowtie_algn.pickle\r\n",
      "-rw-r--r--  1 btsui users 372M May 16 08:08 sra_dump.pickle\r\n"
     ]
    }
   ],
   "source": [
    "!ls -alh /cellar/users/btsui/Data/SRA/DUMP/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "allSRS_pickle_dir='/cellar/users/btsui/Data/SRA/DUMP/allSRS.pickle.gz'\n",
    "allSRS=pd.read_pickle(allSRS_pickle_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "scientificNameS=allSRS[allSRS.index.get_level_values(1)=='SCIENTIFIC_NAME']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "#((scientificNameS.value_counts()/len(scientificNameS))*100).to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ERS018283  SUBMITTER_ID          0073_93-sc-2011-01-25-1067919\n",
       "           Strain                                      0073/93\n",
       "           Sample Description                                 \n",
       "ERS166165  TITLE                         Staphylococcus aureus\n",
       "           SCIENTIFIC_NAME               Staphylococcus aureus\n",
       "dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allSRS.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "memoryS=allSRS.reset_index().memory_usage()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.124429552"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "memoryS.sum()/10**9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "memoryS=allSRS.memory_usage()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.765613144"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "memoryS/10**9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# of samples retrieved: 3124314\n"
     ]
    }
   ],
   "source": [
    "print (\"# of samples retrieved:\",allSRS.index.get_level_values(0).nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 27.8 s, sys: 304 ms, total: 28.1 s\n",
      "Wall time: 25.1 s\n"
     ]
    }
   ],
   "source": [
    "%%time \n",
    "query_keyword='blood'\n",
    "m_blood=allSRS.str.contains(query_keyword)\n",
    "hitS=allSRS[m_blood]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SRS1348489  TITLE                Non-tumor DNA sample from blood of a human fem...\n",
       "            body site                                                        blood\n",
       "ERS1609426  TITLE                                                Pere_davids_blood\n",
       "            SUBMITTER_ID                                       Pere_davids_blood_1\n",
       "SRS2503524  TITLE                Non-tumor DNA sample from blood of a human mal...\n",
       "            body site                                                        blood\n",
       "SRS1324301  TITLE                Non-tumor DNA sample from blood of a human mal...\n",
       "            body site                                                        blood\n",
       "SRS530526   histological type                                                blood\n",
       "SRS532583   histological type                                                blood\n",
       "SRS1705643  TITLE                DNA sample from peripheral blood of a human fe...\n",
       "            body site                                             peripheral blood\n",
       "SRS1231458  TITLE                DNA sample from peripheral blood of a human fe...\n",
       "            body site                                             peripheral blood\n",
       "SRS1152818  TITLE                Non-tumor DNA sample from blood of a human par...\n",
       "            body site                                                        blood\n",
       "SRS1280528  TITLE                Non-tumor DNA sample from blood of a human fem...\n",
       "            body site                                                        blood\n",
       "SRS1646140  TITLE                DNA sample from blood of a human female partic...\n",
       "            body site                                                        blood\n",
       "            histological type                                                blood\n",
       "SRS1903854  TITLE                DNA sample from peripheral blood of a human fe...\n",
       "            body site                                             peripheral blood\n",
       "ERS358062   isolation_source                                                 blood\n",
       "SRS506872   TITLE                DNA sample from blood of a human female partic...\n",
       "            body site                                                        blood\n",
       "SRS1672003  TITLE                DNA sample from peripheral blood of a human ma...\n",
       "            body site                                             peripheral blood\n",
       "ERS072892   isolation_source                                                 blood\n",
       "SRS2239067  tissue                                                           blood\n",
       "                                                       ...                        \n",
       "SRS1474377  body site                                                        blood\n",
       "SRS859842   source_name          human regulatory T cells obtained from periphe...\n",
       "SRS859843   source_name          human regulatory T cells obtained from periphe...\n",
       "SRS859844   source_name          human regulatory T cells obtained from periphe...\n",
       "SRS859845   source_name          human regulatory T cells obtained from periphe...\n",
       "SRS859846   source_name          human regulatory T cells obtained from periphe...\n",
       "SRS859847   source_name          human regulatory T cells obtained from periphe...\n",
       "SRS859848   source_name          human regulatory T cells obtained from periphe...\n",
       "SRS859849   source_name          human regulatory T cells obtained from periphe...\n",
       "SRS859850   source_name          human regulatory T cells obtained from periphe...\n",
       "SRS859851   source_name          human regulatory T cells obtained from periphe...\n",
       "SRS859852   source_name          human regulatory T cells obtained from periphe...\n",
       "SRS1645746  TITLE                DNA sample from blood of a human female partic...\n",
       "            body site                                                        blood\n",
       "            histological type                                                blood\n",
       "SRS1889244  TITLE                DNA sample from Whole blood of a human male pa...\n",
       "            body site                                                  Whole blood\n",
       "SRS506133   TITLE                DNA sample from blood of a human female partic...\n",
       "            body site                                                        blood\n",
       "ERS073568   isolation_source                                                 blood\n",
       "SRS1336622  TITLE                DNA sample from blood of a human female partic...\n",
       "            body site                                                        blood\n",
       "SRS1523861  TITLE                Non-tumor DNA sample from blood of a human fem...\n",
       "            body site                                                        blood\n",
       "SRS1888646  TITLE                DNA sample from Whole blood of a human male pa...\n",
       "            body site                                                  Whole blood\n",
       "SRS1324284  TITLE                DNA sample from peripheral blood of a human fe...\n",
       "            body site                                             peripheral blood\n",
       "SRS1205392  TITLE                Non-tumor DNA sample from peripheral blood of ...\n",
       "            body site                                             peripheral blood\n",
       "Length: 199030, dtype: object"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hitS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### slicing out a single SRS biospecieman annotation records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TITLE                                     Streptococcus pneumoniae\n",
       "SCIENTIFIC_NAME                           Streptococcus pneumoniae\n",
       "SUBMITTER_ID                 22757-sc-2011-11-03T20:05:36Z-1139056\n",
       "Strain                                                       22757\n",
       "Sample Description                                                \n",
       "ArrayExpress-StrainOrLine                                    22757\n",
       "ArrayExpress-Sex                                    not applicable\n",
       "ArrayExpress-Species                      Streptococcus pneumoniae\n",
       "dtype: object"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allSRS.loc['ERS069382']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### slicing attribute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SRS677174  age    Adult mice (3-month-old)\n",
       "SRS677175  age    Adult mice (3-month-old)\n",
       "SRS677176  age    Adult mice (3-month-old)\n",
       "SRS677177  age    Adult mice (3-month-old)\n",
       "SRS677178  age    Adult mice (3-month-old)\n",
       "dtype: object"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "age_m=allSRS.index.get_level_values(1)=='age'\n",
    "\n",
    "allSRS_age=allSRS[age_m]\n",
    "\n",
    "allSRS_age.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# of samples: 399946\n"
     ]
    }
   ],
   "source": [
    "print (\"# of samples:\",len(allSRS_age))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "use simple regex to extract the annotations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 652 ms, sys: 4 ms, total: 656 ms\n",
      "Wall time: 652 ms\n",
      "CPU times: user 340 ms, sys: 8 ms, total: 348 ms\n",
      "Wall time: 347 ms\n",
      "# of samples with age annotation: 61092\n"
     ]
    }
   ],
   "source": [
    "%time digitS=allSRS_age.str.extract(\"(\\d+)\",expand=False)\n",
    "\n",
    "%time unitS=allSRS_age.str.extract(\"(month|day|year)\",expand=False)\n",
    "\n",
    "ageAnnotDf=pd.DataFrame({'digit':digitS,'unit':unitS,'original_text':allSRS_age}).dropna()\n",
    "print ('# of samples with age annotation:',ageAnnotDf.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "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></th>\n",
       "      <th>digit</th>\n",
       "      <th>unit</th>\n",
       "      <th>original_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>SRS677174</th>\n",
       "      <th>age</th>\n",
       "      <td>3</td>\n",
       "      <td>month</td>\n",
       "      <td>Adult mice (3-month-old)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS677175</th>\n",
       "      <th>age</th>\n",
       "      <td>3</td>\n",
       "      <td>month</td>\n",
       "      <td>Adult mice (3-month-old)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS677176</th>\n",
       "      <th>age</th>\n",
       "      <td>3</td>\n",
       "      <td>month</td>\n",
       "      <td>Adult mice (3-month-old)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS677177</th>\n",
       "      <th>age</th>\n",
       "      <td>3</td>\n",
       "      <td>month</td>\n",
       "      <td>Adult mice (3-month-old)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS677178</th>\n",
       "      <th>age</th>\n",
       "      <td>3</td>\n",
       "      <td>month</td>\n",
       "      <td>Adult mice (3-month-old)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS677179</th>\n",
       "      <th>age</th>\n",
       "      <td>3</td>\n",
       "      <td>month</td>\n",
       "      <td>Adult mice (3-month-old)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1860896</th>\n",
       "      <th>age</th>\n",
       "      <td>53</td>\n",
       "      <td>year</td>\n",
       "      <td>53 year</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1860897</th>\n",
       "      <th>age</th>\n",
       "      <td>53</td>\n",
       "      <td>year</td>\n",
       "      <td>53 year</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS2627719</th>\n",
       "      <th>age</th>\n",
       "      <td>53</td>\n",
       "      <td>year</td>\n",
       "      <td>53 year</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS2627721</th>\n",
       "      <th>age</th>\n",
       "      <td>53</td>\n",
       "      <td>year</td>\n",
       "      <td>53 year</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ERS215384</th>\n",
       "      <th>age</th>\n",
       "      <td>12</td>\n",
       "      <td>day</td>\n",
       "      <td>12.5 day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ERS215385</th>\n",
       "      <th>age</th>\n",
       "      <td>12</td>\n",
       "      <td>day</td>\n",
       "      <td>12.5 day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS417361</th>\n",
       "      <th>age</th>\n",
       "      <td>90</td>\n",
       "      <td>day</td>\n",
       "      <td>day 90 bovine embryos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS417362</th>\n",
       "      <th>age</th>\n",
       "      <td>2</td>\n",
       "      <td>year</td>\n",
       "      <td>2-year-old</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1691528</th>\n",
       "      <th>age</th>\n",
       "      <td>12</td>\n",
       "      <td>day</td>\n",
       "      <td>Post natal day 12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1691529</th>\n",
       "      <th>age</th>\n",
       "      <td>12</td>\n",
       "      <td>day</td>\n",
       "      <td>Post natal day 12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1691530</th>\n",
       "      <th>age</th>\n",
       "      <td>12</td>\n",
       "      <td>day</td>\n",
       "      <td>Post natal day 12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1691531</th>\n",
       "      <th>age</th>\n",
       "      <td>12</td>\n",
       "      <td>day</td>\n",
       "      <td>Post natal day 12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1691532</th>\n",
       "      <th>age</th>\n",
       "      <td>12</td>\n",
       "      <td>day</td>\n",
       "      <td>Post natal day 12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1691533</th>\n",
       "      <th>age</th>\n",
       "      <td>12</td>\n",
       "      <td>day</td>\n",
       "      <td>Post natal day 12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1765084</th>\n",
       "      <th>age</th>\n",
       "      <td>3</td>\n",
       "      <td>month</td>\n",
       "      <td>3 months</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1765597</th>\n",
       "      <th>age</th>\n",
       "      <td>3</td>\n",
       "      <td>month</td>\n",
       "      <td>3 months</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1765602</th>\n",
       "      <th>age</th>\n",
       "      <td>3</td>\n",
       "      <td>month</td>\n",
       "      <td>3 months</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1765603</th>\n",
       "      <th>age</th>\n",
       "      <td>3</td>\n",
       "      <td>month</td>\n",
       "      <td>3 months</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1765682</th>\n",
       "      <th>age</th>\n",
       "      <td>3</td>\n",
       "      <td>month</td>\n",
       "      <td>3 months</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1765683</th>\n",
       "      <th>age</th>\n",
       "      <td>3</td>\n",
       "      <td>month</td>\n",
       "      <td>3 months</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1765684</th>\n",
       "      <th>age</th>\n",
       "      <td>3</td>\n",
       "      <td>month</td>\n",
       "      <td>3 months</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1765685</th>\n",
       "      <th>age</th>\n",
       "      <td>4</td>\n",
       "      <td>month</td>\n",
       "      <td>4 months</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1356144</th>\n",
       "      <th>age</th>\n",
       "      <td>8</td>\n",
       "      <td>day</td>\n",
       "      <td>8 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SRS1834910</th>\n",
       "      <th>age</th>\n",
       "      <td>21</td>\n",
       "      <td>day</td>\n",
       "      <td>day 21 of gestation</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               digit   unit             original_text\n",
       "SRS677174  age     3  month  Adult mice (3-month-old)\n",
       "SRS677175  age     3  month  Adult mice (3-month-old)\n",
       "SRS677176  age     3  month  Adult mice (3-month-old)\n",
       "SRS677177  age     3  month  Adult mice (3-month-old)\n",
       "SRS677178  age     3  month  Adult mice (3-month-old)\n",
       "SRS677179  age     3  month  Adult mice (3-month-old)\n",
       "SRS1860896 age    53   year                   53 year\n",
       "SRS1860897 age    53   year                   53 year\n",
       "SRS2627719 age    53   year                   53 year\n",
       "SRS2627721 age    53   year                   53 year\n",
       "ERS215384  age    12    day                  12.5 day\n",
       "ERS215385  age    12    day                  12.5 day\n",
       "SRS417361  age    90    day     day 90 bovine embryos\n",
       "SRS417362  age     2   year                2-year-old\n",
       "SRS1691528 age    12    day         Post natal day 12\n",
       "SRS1691529 age    12    day         Post natal day 12\n",
       "SRS1691530 age    12    day         Post natal day 12\n",
       "SRS1691531 age    12    day         Post natal day 12\n",
       "SRS1691532 age    12    day         Post natal day 12\n",
       "SRS1691533 age    12    day         Post natal day 12\n",
       "SRS1765084 age     3  month                  3 months\n",
       "SRS1765597 age     3  month                  3 months\n",
       "SRS1765602 age     3  month                  3 months\n",
       "SRS1765603 age     3  month                  3 months\n",
       "SRS1765682 age     3  month                  3 months\n",
       "SRS1765683 age     3  month                  3 months\n",
       "SRS1765684 age     3  month                  3 months\n",
       "SRS1765685 age     4  month                  4 months\n",
       "SRS1356144 age     8    day                    8 days\n",
       "SRS1834910 age    21    day       day 21 of gestation"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ageAnnotDf.head(n=30)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# experiment metadata (SRA SRX)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 4min 16s, sys: 4.07 s, total: 4min 20s\n",
      "Wall time: 4min 20s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "allSRX_pickle_dir='/cellar/users/btsui/Data/SRA/DUMP/allSRX.pickle.gz'\n",
    "allSRX=pd.read_pickle(allSRX_pickle_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ERX1269638  TITLE                Illumina HiSeq 1500 paired end sequencing\n",
       "            LIBRARY_STRATEGY                                           EST\n",
       "            LIBRARY_SOURCE                                         GENOMIC\n",
       "            LIBRARY_SELECTION                                       RANDOM\n",
       "            LIBRARY_LAYOUT                                          PAIRED\n",
       "dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allSRX.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 812 ms, sys: 20 ms, total: 832 ms\n",
      "Wall time: 70.5 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "TITLE                                             Illumina HiSeq 2000 sequencing; Hybrid aspen (...\n",
       "DESIGN_DESCRIPTION                                Hybrid aspen (Populus tremula x tremuloides)1h...\n",
       "LIBRARY_STRATEGY                                                                            RNA-Seq\n",
       "LIBRARY_SOURCE                                                                       TRANSCRIPTOMIC\n",
       "LIBRARY_SELECTION                                                                              cDNA\n",
       "LIBRARY_LAYOUT                                                                               SINGLE\n",
       "Experimental Factor: 0                                                                         dose\n",
       "Experimental Factor: isopentenyl adenine (2ip)                                             compound\n",
       "dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time allSRX.loc['ERX1425391']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19996"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allSRS.index.get_level_values(1).nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}