data_preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>Accession</th>\n",
       "      <th>Title</th>\n",
       "      <th>Sample Type</th>\n",
       "      <th>Taxonomy</th>\n",
       "      <th>Channels</th>\n",
       "      <th>Platform</th>\n",
       "      <th>Series</th>\n",
       "      <th>Supplementary Types</th>\n",
       "      <th>Supplementary Links</th>\n",
       "      <th>SRA Accession</th>\n",
       "      <th>Contact</th>\n",
       "      <th>Release Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GSM2171880</td>\n",
       "      <td>21yr_male_cell1</td>\n",
       "      <td>SRA</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1</td>\n",
       "      <td>GPL18573</td>\n",
       "      <td>GSE81547</td>\n",
       "      <td>CSV;SRA Run Selector</td>\n",
       "      <td>ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2171...</td>\n",
       "      <td>SRX1787822</td>\n",
       "      <td>Martin Enge</td>\n",
       "      <td>Sep 28, 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GSM2171881</td>\n",
       "      <td>21yr_male_cell2</td>\n",
       "      <td>SRA</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1</td>\n",
       "      <td>GPL18573</td>\n",
       "      <td>GSE81547</td>\n",
       "      <td>CSV;SRA Run Selector</td>\n",
       "      <td>ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2171...</td>\n",
       "      <td>SRX1787823</td>\n",
       "      <td>Martin Enge</td>\n",
       "      <td>Sep 28, 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GSM2171882</td>\n",
       "      <td>21yr_male_cell3</td>\n",
       "      <td>SRA</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1</td>\n",
       "      <td>GPL18573</td>\n",
       "      <td>GSE81547</td>\n",
       "      <td>CSV;SRA Run Selector</td>\n",
       "      <td>ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2171...</td>\n",
       "      <td>SRX1787824</td>\n",
       "      <td>Martin Enge</td>\n",
       "      <td>Sep 28, 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GSM2171883</td>\n",
       "      <td>21yr_male_cell4</td>\n",
       "      <td>SRA</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1</td>\n",
       "      <td>GPL18573</td>\n",
       "      <td>GSE81547</td>\n",
       "      <td>CSV;SRA Run Selector</td>\n",
       "      <td>ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2171...</td>\n",
       "      <td>SRX1787825</td>\n",
       "      <td>Martin Enge</td>\n",
       "      <td>Sep 28, 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GSM2171884</td>\n",
       "      <td>21yr_male_cell5</td>\n",
       "      <td>SRA</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1</td>\n",
       "      <td>GPL18573</td>\n",
       "      <td>GSE81547</td>\n",
       "      <td>CSV;SRA Run Selector</td>\n",
       "      <td>ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2171...</td>\n",
       "      <td>SRX1787826</td>\n",
       "      <td>Martin Enge</td>\n",
       "      <td>Sep 28, 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2539</th>\n",
       "      <td>GSM2174421</td>\n",
       "      <td>22yr_male_cell2540</td>\n",
       "      <td>SRA</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1</td>\n",
       "      <td>GPL18573</td>\n",
       "      <td>GSE81547</td>\n",
       "      <td>CSV;SRA Run Selector</td>\n",
       "      <td>ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2174...</td>\n",
       "      <td>SRX1790361</td>\n",
       "      <td>Martin Enge</td>\n",
       "      <td>Sep 28, 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2540</th>\n",
       "      <td>GSM2174422</td>\n",
       "      <td>22yr_male_cell2541</td>\n",
       "      <td>SRA</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1</td>\n",
       "      <td>GPL18573</td>\n",
       "      <td>GSE81547</td>\n",
       "      <td>CSV;SRA Run Selector</td>\n",
       "      <td>ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2174...</td>\n",
       "      <td>SRX1790362</td>\n",
       "      <td>Martin Enge</td>\n",
       "      <td>Sep 28, 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2541</th>\n",
       "      <td>GSM2174423</td>\n",
       "      <td>22yr_male_cell2542</td>\n",
       "      <td>SRA</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1</td>\n",
       "      <td>GPL18573</td>\n",
       "      <td>GSE81547</td>\n",
       "      <td>CSV;SRA Run Selector</td>\n",
       "      <td>ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2174...</td>\n",
       "      <td>SRX1790363</td>\n",
       "      <td>Martin Enge</td>\n",
       "      <td>Sep 28, 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2542</th>\n",
       "      <td>GSM2174424</td>\n",
       "      <td>22yr_male_cell2543</td>\n",
       "      <td>SRA</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1</td>\n",
       "      <td>GPL18573</td>\n",
       "      <td>GSE81547</td>\n",
       "      <td>CSV;SRA Run Selector</td>\n",
       "      <td>ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2174...</td>\n",
       "      <td>SRX1790364</td>\n",
       "      <td>Martin Enge</td>\n",
       "      <td>Sep 28, 2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2543</th>\n",
       "      <td>GSM2174425</td>\n",
       "      <td>22yr_male_cell2544</td>\n",
       "      <td>SRA</td>\n",
       "      <td>Homo sapiens</td>\n",
       "      <td>1</td>\n",
       "      <td>GPL18573</td>\n",
       "      <td>GSE81547</td>\n",
       "      <td>CSV;SRA Run Selector</td>\n",
       "      <td>ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2174...</td>\n",
       "      <td>SRX1790365</td>\n",
       "      <td>Martin Enge</td>\n",
       "      <td>Sep 28, 2017</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2544 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Accession               Title Sample Type      Taxonomy  Channels  \\\n",
       "0     GSM2171880     21yr_male_cell1         SRA  Homo sapiens         1   \n",
       "1     GSM2171881     21yr_male_cell2         SRA  Homo sapiens         1   \n",
       "2     GSM2171882     21yr_male_cell3         SRA  Homo sapiens         1   \n",
       "3     GSM2171883     21yr_male_cell4         SRA  Homo sapiens         1   \n",
       "4     GSM2171884     21yr_male_cell5         SRA  Homo sapiens         1   \n",
       "...          ...                 ...         ...           ...       ...   \n",
       "2539  GSM2174421  22yr_male_cell2540         SRA  Homo sapiens         1   \n",
       "2540  GSM2174422  22yr_male_cell2541         SRA  Homo sapiens         1   \n",
       "2541  GSM2174423  22yr_male_cell2542         SRA  Homo sapiens         1   \n",
       "2542  GSM2174424  22yr_male_cell2543         SRA  Homo sapiens         1   \n",
       "2543  GSM2174425  22yr_male_cell2544         SRA  Homo sapiens         1   \n",
       "\n",
       "      Platform    Series   Supplementary Types  \\\n",
       "0     GPL18573  GSE81547  CSV;SRA Run Selector   \n",
       "1     GPL18573  GSE81547  CSV;SRA Run Selector   \n",
       "2     GPL18573  GSE81547  CSV;SRA Run Selector   \n",
       "3     GPL18573  GSE81547  CSV;SRA Run Selector   \n",
       "4     GPL18573  GSE81547  CSV;SRA Run Selector   \n",
       "...        ...       ...                   ...   \n",
       "2539  GPL18573  GSE81547  CSV;SRA Run Selector   \n",
       "2540  GPL18573  GSE81547  CSV;SRA Run Selector   \n",
       "2541  GPL18573  GSE81547  CSV;SRA Run Selector   \n",
       "2542  GPL18573  GSE81547  CSV;SRA Run Selector   \n",
       "2543  GPL18573  GSE81547  CSV;SRA Run Selector   \n",
       "\n",
       "                                    Supplementary Links SRA Accession  \\\n",
       "0     ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2171...    SRX1787822   \n",
       "1     ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2171...    SRX1787823   \n",
       "2     ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2171...    SRX1787824   \n",
       "3     ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2171...    SRX1787825   \n",
       "4     ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2171...    SRX1787826   \n",
       "...                                                 ...           ...   \n",
       "2539  ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2174...    SRX1790361   \n",
       "2540  ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2174...    SRX1790362   \n",
       "2541  ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2174...    SRX1790363   \n",
       "2542  ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2174...    SRX1790364   \n",
       "2543  ftp://ftp.ncbi.nlm.nih.gov/geo/samples/GSM2174...    SRX1790365   \n",
       "\n",
       "          Contact  Release Date  \n",
       "0     Martin Enge  Sep 28, 2017  \n",
       "1     Martin Enge  Sep 28, 2017  \n",
       "2     Martin Enge  Sep 28, 2017  \n",
       "3     Martin Enge  Sep 28, 2017  \n",
       "4     Martin Enge  Sep 28, 2017  \n",
       "...           ...           ...  \n",
       "2539  Martin Enge  Sep 28, 2017  \n",
       "2540  Martin Enge  Sep 28, 2017  \n",
       "2541  Martin Enge  Sep 28, 2017  \n",
       "2542  Martin Enge  Sep 28, 2017  \n",
       "2543  Martin Enge  Sep 28, 2017  \n",
       "\n",
       "[2544 rows x 12 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read the annotation table as downloaded from the accession viewer\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\"sample.csv\", sep = \",\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    2544.000000\n",
       "mean       25.443789\n",
       "std        17.092678\n",
       "min         1.000000\n",
       "25%         6.000000\n",
       "50%        22.000000\n",
       "75%        38.000000\n",
       "max        54.000000\n",
       "Name: Age, dtype: float64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#change the \"Title\" column to an age column to extract the ages\n",
    "df[\"Age\"] = df[\"Title\"].apply(lambda x: x.split(\"yr\")[0])\n",
    "df[\"Age\"] = df[\"Age\"].astype(int)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>1/2-SBSRNA4</th>\n",
       "      <th>A1BG</th>\n",
       "      <th>A1BG-AS1</th>\n",
       "      <th>A1CF</th>\n",
       "      <th>A2LD1</th>\n",
       "      <th>A2M</th>\n",
       "      <th>A2ML1</th>\n",
       "      <th>A2MP1</th>\n",
       "      <th>A4GALT</th>\n",
       "      <th>A4GNT</th>\n",
       "      <th>...</th>\n",
       "      <th>ZYG11B</th>\n",
       "      <th>ZYX</th>\n",
       "      <th>ZZEF1</th>\n",
       "      <th>ZZZ3</th>\n",
       "      <th>alignment_not_unique</th>\n",
       "      <th>ambiguous</th>\n",
       "      <th>no_feature</th>\n",
       "      <th>not_aligned</th>\n",
       "      <th>tAKR</th>\n",
       "      <th>too_low_aQual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>GSM2171880</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>651</td>\n",
       "      <td>85563</td>\n",
       "      <td>3754</td>\n",
       "      <td>407558</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM2171881</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>320</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>152</td>\n",
       "      <td>70223</td>\n",
       "      <td>4592</td>\n",
       "      <td>359410</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM2171882</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>39277</td>\n",
       "      <td>4473</td>\n",
       "      <td>172353</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM2171883</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>66</td>\n",
       "      <td>14</td>\n",
       "      <td>86043</td>\n",
       "      <td>2618</td>\n",
       "      <td>596866</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM2171884</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>578</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>45023</td>\n",
       "      <td>2342</td>\n",
       "      <td>275800</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM2174421</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1253</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>77533</td>\n",
       "      <td>6142</td>\n",
       "      <td>186226</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM2174422</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>73</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>84350</td>\n",
       "      <td>9231</td>\n",
       "      <td>388780</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM2174423</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2724</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>52278</td>\n",
       "      <td>3883</td>\n",
       "      <td>194324</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM2174424</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>83</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>49</td>\n",
       "      <td>0</td>\n",
       "      <td>81459</td>\n",
       "      <td>3700</td>\n",
       "      <td>318373</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GSM2174425</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>38748</td>\n",
       "      <td>1548</td>\n",
       "      <td>214199</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2544 rows × 23465 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "0           1/2-SBSRNA4  A1BG  A1BG-AS1  A1CF  A2LD1   A2M  A2ML1  A2MP1  \\\n",
       "GSM2171880            0     0         0     0      0     0      0      0   \n",
       "GSM2171881            0     0         0     0      0     0      0      0   \n",
       "GSM2171882            0     0         0     1      0     0      0      0   \n",
       "GSM2171883            0     0         0     2      0     0      0      0   \n",
       "GSM2171884            0     0         0     0      0     0      0      0   \n",
       "...                 ...   ...       ...   ...    ...   ...    ...    ...   \n",
       "GSM2174421            0     0         0     0      0  1253      0      0   \n",
       "GSM2174422            0     0         0     0      0     0      0      0   \n",
       "GSM2174423            0     0         0     0      0  2724      0      0   \n",
       "GSM2174424            0     0         0    83      0     0      0      0   \n",
       "GSM2174425            0     0         0     0      0     0      0      0   \n",
       "\n",
       "0           A4GALT  A4GNT  ...  ZYG11B  ZYX  ZZEF1  ZZZ3  \\\n",
       "GSM2171880       0      0  ...       0    0      0   651   \n",
       "GSM2171881       0      0  ...     320    2      0   152   \n",
       "GSM2171882       0      0  ...       0    0      0     0   \n",
       "GSM2171883       0      0  ...       0    3     66    14   \n",
       "GSM2171884       0      0  ...     578    0      0     0   \n",
       "...            ...    ...  ...     ...  ...    ...   ...   \n",
       "GSM2174421       0      0  ...       0    0      0     0   \n",
       "GSM2174422       0      0  ...      73    0      0     0   \n",
       "GSM2174423       0      0  ...       0   29      0     0   \n",
       "GSM2174424       0      0  ...       0    6     49     0   \n",
       "GSM2174425       0      0  ...       0    0      0     0   \n",
       "\n",
       "0           alignment_not_unique  ambiguous  no_feature  not_aligned  tAKR  \\\n",
       "GSM2171880                 85563       3754      407558            0     0   \n",
       "GSM2171881                 70223       4592      359410            0     0   \n",
       "GSM2171882                 39277       4473      172353            0     0   \n",
       "GSM2171883                 86043       2618      596866            0     0   \n",
       "GSM2171884                 45023       2342      275800            0     0   \n",
       "...                          ...        ...         ...          ...   ...   \n",
       "GSM2174421                 77533       6142      186226            0     0   \n",
       "GSM2174422                 84350       9231      388780            0     0   \n",
       "GSM2174423                 52278       3883      194324            0     0   \n",
       "GSM2174424                 81459       3700      318373            0     0   \n",
       "GSM2174425                 38748       1548      214199            0     0   \n",
       "\n",
       "0           too_low_aQual  \n",
       "GSM2171880              0  \n",
       "GSM2171881              0  \n",
       "GSM2171882              0  \n",
       "GSM2171883              0  \n",
       "GSM2171884              0  \n",
       "...                   ...  \n",
       "GSM2174421              0  \n",
       "GSM2174422              0  \n",
       "GSM2174423              0  \n",
       "GSM2174424              0  \n",
       "GSM2174425              0  \n",
       "\n",
       "[2544 rows x 23465 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Combining individual samples into a second dataframe (gene expression matrix)\n",
    "import os\n",
    "\n",
    "p = './GSE81547_data/'\n",
    "cells = pd.DataFrame()\n",
    "\n",
    "for filename in os.listdir(p):\n",
    "    f = os.path.join(p,filename)\n",
    "    if os.path.isfile(f):\n",
    "        cellname = f.split(\"_\")[1].split(\"/\")[1]\n",
    "        cell = pd.read_csv(f, sep = \"\\t\", header = None)\n",
    "        cell = cell.pivot_table(columns = 0, values = 1)\n",
    "        cell.rename(index={1:cellname}, inplace=True)\n",
    "        cells = pd.concat([cells,cell])\n",
    "cells"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "#save gene expression matrix\n",
    "cells.to_csv(\"GSE81547.csv\", sep = \",\", index = True)\n",
    "#save age matrix\n",
    "df.set_index(\"Accession\", inplace = True)\n",
    "df[\"Age\"].to_csv(\"Ages.csv\", sep = \",\", index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv(\"GSE81547.csv\", sep = \",\", index_col = 0)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop unwanted columns\n",
    "lowerCols = []\n",
    "for c in df.columns:\n",
    "    if c[0].islower(): lowerCols.append(c)\n",
    "lowerCols\n",
    "\n",
    "df = df[[c for c in df.columns if c not in lowerCols]]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "sparsedf = df.replace(0, np.nan)\n",
    "sum = sparsedf.isna().sum(axis = 0) #count NANs in whole data and calculate sum of NANs for each column\n",
    "drop = sum >= 2519  #2519 approx. equals 99% of the samples\n",
    "datlist = list(drop[drop].index.values) \n",
    "sparsedf.drop(datlist, axis =1, inplace=True) #drop all genes (columns) that have more than 99% NAN's (are expressed in less than 1% of the data)\n",
    "sparsedf.fillna(value = 0, inplace=True)\n",
    "sparsedf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pseudocounts of 1 are added 0's to do log transformation\n",
    "logdf = sparsedf.apply(lambda x: x.apply(lambda y: y if y != 0 else 1))\n",
    "logdf = np.log10(logdf)\n",
    "\n",
    "# row-wise normalization: sum of each row adds up to one\n",
    "logdf = logdf.apply(lambda x: x / x.sum(), axis = 1)\n",
    "logdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculate variance of each column and remove every gene below a variance threshold, create new data frame with remaining genes (400-500)\n",
    "threshold = 0.57e-07\n",
    "\n",
    "_500genes = logdf.apply(lambda x: x if x.var() >= threshold else [np.NaN]*len(x), axis = 0)\n",
    "_500genes.dropna(axis=1, inplace = True)\n",
    "_500genes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "threshold = 1.3e-07\n",
    "\n",
    "_50genes = logdf.apply(lambda x: x if x.var() >= threshold else [np.NaN]*len(x), axis = 0)\n",
    "_50genes.dropna(axis=1, inplace = True)\n",
    "_50genes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#safe data matrices\n",
    "_500genes.to_csv(\"500_variable_genes.csv\", sep = \",\", index = True)\n",
    "_50genes.to_csv(\"50_variable_genes.csv\", sep = \",\", index = True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "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.10.4"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "0b6a373e1e92ca1bfe732d6817c16ff5c6def667c661376bf5284b27936ab9ce"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}