build_master.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "scaler = StandardScaler()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path\n",
    "# read deduplicated data\n",
    "response_path = Path('./data/deduplicated_combined_single_response_rescaled_agg.txt')\n",
    "cell_cancer_types_map_path = Path('./data/combined_cancer_types')\n",
    "cell_name_map_path = Path('./data/cell_line_ID_mapping.txt')\n",
    "drug_descriptors_mordred_path = Path('./data/deduplicated_combined_drug_descriptors_mordred.txt')\n",
    "cell_rnaseq_path = Path('./data/deduplicated_combined_rnaseq_data_combat.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>SOURCE</th>\n",
       "      <th>CELL</th>\n",
       "      <th>DRUG</th>\n",
       "      <th>STUDY</th>\n",
       "      <th>AUC</th>\n",
       "      <th>IC50</th>\n",
       "      <th>EC50</th>\n",
       "      <th>EC50se</th>\n",
       "      <th>R2fit</th>\n",
       "      <th>Einf</th>\n",
       "      <th>HS</th>\n",
       "      <th>AAC1</th>\n",
       "      <th>AUC1</th>\n",
       "      <th>DSS1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>CCLE</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_1</td>\n",
       "      <td>fake_exp</td>\n",
       "      <td>0.7153</td>\n",
       "      <td>5.6600</td>\n",
       "      <td>5.6600</td>\n",
       "      <td>0.6867</td>\n",
       "      <td>0.9533</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.6669</td>\n",
       "      <td>0.2240</td>\n",
       "      <td>0.7760</td>\n",
       "      <td>0.1661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>CCLE</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_6</td>\n",
       "      <td>fake_exp</td>\n",
       "      <td>0.5743</td>\n",
       "      <td>7.0040</td>\n",
       "      <td>7.0600</td>\n",
       "      <td>0.0128</td>\n",
       "      <td>0.9989</td>\n",
       "      <td>0.1652</td>\n",
       "      <td>3.1500</td>\n",
       "      <td>0.4675</td>\n",
       "      <td>0.5325</td>\n",
       "      <td>0.4438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CCLE</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_7</td>\n",
       "      <td>fake_exp</td>\n",
       "      <td>0.9335</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.9570</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.8670</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0665</td>\n",
       "      <td>0.9335</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>CCLE</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_9</td>\n",
       "      <td>fake_exp</td>\n",
       "      <td>0.8823</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.7930</td>\n",
       "      <td>0.0020</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.6063</td>\n",
       "      <td>2.8980</td>\n",
       "      <td>0.0784</td>\n",
       "      <td>0.9216</td>\n",
       "      <td>0.0544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>CCLE</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_11</td>\n",
       "      <td>fake_exp</td>\n",
       "      <td>0.7019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.1290</td>\n",
       "      <td>0.1276</td>\n",
       "      <td>0.8550</td>\n",
       "      <td>0.5668</td>\n",
       "      <td>2.1750</td>\n",
       "      <td>0.3725</td>\n",
       "      <td>0.6275</td>\n",
       "      <td>0.3054</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>523365</td>\n",
       "      <td>GDSC2</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_654</td>\n",
       "      <td>19498</td>\n",
       "      <td>0.9548</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.8900</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.9096</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0452</td>\n",
       "      <td>0.9548</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523366</td>\n",
       "      <td>GDSC2</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_655</td>\n",
       "      <td>19498</td>\n",
       "      <td>0.8190</td>\n",
       "      <td>3.0070</td>\n",
       "      <td>3.0070</td>\n",
       "      <td>46.7600</td>\n",
       "      <td>0.4604</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.1818</td>\n",
       "      <td>0.1943</td>\n",
       "      <td>0.8057</td>\n",
       "      <td>0.1047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523367</td>\n",
       "      <td>GDSC2</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_656</td>\n",
       "      <td>19498</td>\n",
       "      <td>0.9105</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.4040</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.8209</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0895</td>\n",
       "      <td>0.9105</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523368</td>\n",
       "      <td>GDSC2</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_657</td>\n",
       "      <td>19498</td>\n",
       "      <td>0.9566</td>\n",
       "      <td>0.2428</td>\n",
       "      <td>0.2428</td>\n",
       "      <td>233.0000</td>\n",
       "      <td>0.1946</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.2200</td>\n",
       "      <td>0.0438</td>\n",
       "      <td>0.9562</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523369</td>\n",
       "      <td>GDSC2</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_658</td>\n",
       "      <td>19498</td>\n",
       "      <td>0.8426</td>\n",
       "      <td>4.7880</td>\n",
       "      <td>4.7880</td>\n",
       "      <td>2.8020</td>\n",
       "      <td>0.8616</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.6938</td>\n",
       "      <td>0.1111</td>\n",
       "      <td>0.8889</td>\n",
       "      <td>0.0573</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>523370 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       SOURCE     CELL      DRUG     STUDY     AUC    IC50    EC50    EC50se  \\\n",
       "0        CCLE   CCL_61    Drug_1  fake_exp  0.7153  5.6600  5.6600    0.6867   \n",
       "1        CCLE   CCL_61    Drug_6  fake_exp  0.5743  7.0040  7.0600    0.0128   \n",
       "2        CCLE   CCL_61    Drug_7  fake_exp  0.9335     NaN  2.9570    0.0000   \n",
       "3        CCLE   CCL_61    Drug_9  fake_exp  0.8823     NaN  5.7930    0.0020   \n",
       "4        CCLE   CCL_61   Drug_11  fake_exp  0.7019     NaN  8.1290    0.1276   \n",
       "...       ...      ...       ...       ...     ...     ...     ...       ...   \n",
       "523365  GDSC2  CCL_393  Drug_654     19498  0.9548     NaN  7.8900    0.0000   \n",
       "523366  GDSC2  CCL_393  Drug_655     19498  0.8190  3.0070  3.0070   46.7600   \n",
       "523367  GDSC2  CCL_393  Drug_656     19498  0.9105     NaN  3.4040    0.0000   \n",
       "523368  GDSC2  CCL_393  Drug_657     19498  0.9566  0.2428  0.2428  233.0000   \n",
       "523369  GDSC2  CCL_393  Drug_658     19498  0.8426  4.7880  4.7880    2.8020   \n",
       "\n",
       "         R2fit    Einf      HS    AAC1    AUC1    DSS1  \n",
       "0       0.9533  0.0000  0.6669  0.2240  0.7760  0.1661  \n",
       "1       0.9989  0.1652  3.1500  0.4675  0.5325  0.4438  \n",
       "2       0.0000  0.8670  0.0000  0.0665  0.9335  0.0000  \n",
       "3       1.0000  0.6063  2.8980  0.0784  0.9216  0.0544  \n",
       "4       0.8550  0.5668  2.1750  0.3725  0.6275  0.3054  \n",
       "...        ...     ...     ...     ...     ...     ...  \n",
       "523365  0.0000  0.9096  0.0000  0.0452  0.9548  0.0000  \n",
       "523366  0.4604  0.0000  0.1818  0.1943  0.8057  0.1047  \n",
       "523367  0.0000  0.8209  0.0000  0.0895  0.9105  0.0000  \n",
       "523368  0.1946  0.0000  0.2200  0.0438  0.9562  0.0000  \n",
       "523369  0.8616  0.0000  0.6938  0.1111  0.8889  0.0573  \n",
       "\n",
       "[523370 rows x 14 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_response = pd.read_csv(response_path, sep='\\t', engine='c', low_memory=False)\n",
    "df_response"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Identify target cancer types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This step is equivalent to below command\n",
    "\n",
    "``` cut -f 2,3 ./data/combined_single_response_rescaled_agg | sort | uniq > uniq_cl_drugs```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CELL</th>\n",
       "      <th>DRUG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523365</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523366</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523367</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523368</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523369</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_658</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>523370 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           CELL      DRUG\n",
       "0        CCL_61    Drug_1\n",
       "1        CCL_61    Drug_6\n",
       "2        CCL_61    Drug_7\n",
       "3        CCL_61    Drug_9\n",
       "4        CCL_61   Drug_11\n",
       "...         ...       ...\n",
       "523365  CCL_393  Drug_654\n",
       "523366  CCL_393  Drug_655\n",
       "523367  CCL_393  Drug_656\n",
       "523368  CCL_393  Drug_657\n",
       "523369  CCL_393  Drug_658\n",
       "\n",
       "[523370 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_uniq_cl_drugs = df_response[['CELL','DRUG']].drop_duplicates().reset_index(drop=True)\n",
    "df_uniq_cl_drugs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "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>CELL</th>\n",
       "      <th>CANCER_TYPE</th>\n",
       "      <th>CELL_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>CCLE.ALLSIL</td>\n",
       "      <td>Acute_Lymphoblastic_Leukemia</td>\n",
       "      <td>CCL_95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>CCLE.DND41</td>\n",
       "      <td>Acute_Lymphoblastic_Leukemia</td>\n",
       "      <td>CCL_211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CCLE.KE37</td>\n",
       "      <td>Acute_Lymphoblastic_Leukemia</td>\n",
       "      <td>CCL_457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>CCLE.LOUCY</td>\n",
       "      <td>Acute_Lymphoblastic_Leukemia</td>\n",
       "      <td>CCL_537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>CCLE.MOLT13</td>\n",
       "      <td>Acute_Lymphoblastic_Leukemia</td>\n",
       "      <td>CCL_599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2916</td>\n",
       "      <td>gCSI.HEC-6</td>\n",
       "      <td>Uterine_Corpus_Endometrial_Carcinoma</td>\n",
       "      <td>CCL_316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2917</td>\n",
       "      <td>gCSI.KLE</td>\n",
       "      <td>Uterine_Corpus_Endometrial_Carcinoma</td>\n",
       "      <td>CCL_465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2918</td>\n",
       "      <td>gCSI.RL95-2</td>\n",
       "      <td>Uterine_Corpus_Endometrial_Carcinoma</td>\n",
       "      <td>CCL_829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2919</td>\n",
       "      <td>gCSI.SNG-M</td>\n",
       "      <td>Uterine_Corpus_Endometrial_Carcinoma</td>\n",
       "      <td>CCL_900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2920</td>\n",
       "      <td>GDSC.KM-H2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCL_468</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2921 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             CELL                           CANCER_TYPE  CELL_ID\n",
       "0     CCLE.ALLSIL          Acute_Lymphoblastic_Leukemia   CCL_95\n",
       "1      CCLE.DND41          Acute_Lymphoblastic_Leukemia  CCL_211\n",
       "2       CCLE.KE37          Acute_Lymphoblastic_Leukemia  CCL_457\n",
       "3      CCLE.LOUCY          Acute_Lymphoblastic_Leukemia  CCL_537\n",
       "4     CCLE.MOLT13          Acute_Lymphoblastic_Leukemia  CCL_599\n",
       "...           ...                                   ...      ...\n",
       "2916   gCSI.HEC-6  Uterine_Corpus_Endometrial_Carcinoma  CCL_316\n",
       "2917     gCSI.KLE  Uterine_Corpus_Endometrial_Carcinoma  CCL_465\n",
       "2918  gCSI.RL95-2  Uterine_Corpus_Endometrial_Carcinoma  CCL_829\n",
       "2919   gCSI.SNG-M  Uterine_Corpus_Endometrial_Carcinoma  CCL_900\n",
       "2920   GDSC.KM-H2                                   NaN  CCL_468\n",
       "\n",
       "[2921 rows x 3 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cl_cancer_map = pd.read_csv(cell_cancer_types_map_path, sep='\\t', header=None, names=['CELL', 'CANCER_TYPE'])\n",
    "df_cl_name_map = pd.read_csv(cell_name_map_path, sep='\\t', header=0, names=['CELL', 'CELL_ID'])\n",
    "df_cl_cancer_map = df_cl_cancer_map.merge(df_cl_name_map, on='CELL', how='right')\n",
    "df_cl_cancer_map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>CANCER_TYPE</th>\n",
       "      <th>CELL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Acute_Lymphoblastic_Leukemia</td>\n",
       "      <td>CCL_95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Acute_Lymphoblastic_Leukemia</td>\n",
       "      <td>CCL_211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Acute_Lymphoblastic_Leukemia</td>\n",
       "      <td>CCL_457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Acute_Lymphoblastic_Leukemia</td>\n",
       "      <td>CCL_537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Acute_Lymphoblastic_Leukemia</td>\n",
       "      <td>CCL_599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1038</td>\n",
       "      <td>Uterine_Corpus_Endometrial_Carcinoma</td>\n",
       "      <td>CCL_900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1039</td>\n",
       "      <td>Uterine_Corpus_Endometrial_Carcinoma</td>\n",
       "      <td>CCL_906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1040</td>\n",
       "      <td>Uterine_Corpus_Endometrial_Carcinoma</td>\n",
       "      <td>CCL_945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1041</td>\n",
       "      <td>Uterine_Corpus_Endometrial_Carcinoma</td>\n",
       "      <td>CCL_1021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1042</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CCL_468</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1043 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               CANCER_TYPE      CELL\n",
       "0             Acute_Lymphoblastic_Leukemia    CCL_95\n",
       "1             Acute_Lymphoblastic_Leukemia   CCL_211\n",
       "2             Acute_Lymphoblastic_Leukemia   CCL_457\n",
       "3             Acute_Lymphoblastic_Leukemia   CCL_537\n",
       "4             Acute_Lymphoblastic_Leukemia   CCL_599\n",
       "...                                    ...       ...\n",
       "1038  Uterine_Corpus_Endometrial_Carcinoma   CCL_900\n",
       "1039  Uterine_Corpus_Endometrial_Carcinoma   CCL_906\n",
       "1040  Uterine_Corpus_Endometrial_Carcinoma   CCL_945\n",
       "1041  Uterine_Corpus_Endometrial_Carcinoma  CCL_1021\n",
       "1042                                   NaN   CCL_468\n",
       "\n",
       "[1043 rows x 2 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cl_cancer_map.drop(labels=['CELL'], axis=1, inplace=True)\n",
    "df_cl_cancer_map.rename(columns={'CELL_ID':'CELL'}, inplace=True)\n",
    "df_cl_cancer_map = df_cl_cancer_map.drop_duplicates().reset_index(drop=True)\n",
    "df_cl_cancer_map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CELL</th>\n",
       "      <th>DRUG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>CCL_61</td>\n",
       "      <td>Drug_11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523365</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523366</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523367</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523368</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>523369</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_658</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>523370 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           CELL      DRUG\n",
       "0        CCL_61    Drug_1\n",
       "1        CCL_61    Drug_6\n",
       "2        CCL_61    Drug_7\n",
       "3        CCL_61    Drug_9\n",
       "4        CCL_61   Drug_11\n",
       "...         ...       ...\n",
       "523365  CCL_393  Drug_654\n",
       "523366  CCL_393  Drug_655\n",
       "523367  CCL_393  Drug_656\n",
       "523368  CCL_393  Drug_657\n",
       "523369  CCL_393  Drug_658\n",
       "\n",
       "[523370 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_uniq_cl_drugs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge and get top n. This step is equivalent to below command line. \n",
    "\n",
    "```Intersection.pl $df_dir/cell_lines/combined_cancer_types 1 uniq_cl_drugs 1 -i | cut -f 2 | sort | uniq -c | sort -nr | perl -pe 's/^ *//' | perl -pe 's/ /\\t/' > top_cancer_types\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>CANCER_TYPE</th>\n",
       "      <th>CELL</th>\n",
       "      <th>DRUG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Pancreatic_Adenocarcinoma</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>Drug_1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Pancreatic_Adenocarcinoma</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>Drug_2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Pancreatic_Adenocarcinoma</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>Drug_3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Pancreatic_Adenocarcinoma</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>Drug_5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Pancreatic_Adenocarcinoma</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>Drug_6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>530088</td>\n",
       "      <td>Lung_NOS</td>\n",
       "      <td>CCL_998</td>\n",
       "      <td>Drug_494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>530089</td>\n",
       "      <td>Lung_NOS</td>\n",
       "      <td>CCL_998</td>\n",
       "      <td>Drug_496</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>530090</td>\n",
       "      <td>Lung_NOS</td>\n",
       "      <td>CCL_998</td>\n",
       "      <td>Drug_497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>530091</td>\n",
       "      <td>Lung_NOS</td>\n",
       "      <td>CCL_998</td>\n",
       "      <td>Drug_499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>530092</td>\n",
       "      <td>Lung_NOS</td>\n",
       "      <td>CCL_998</td>\n",
       "      <td>Drug_500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>530093 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      CANCER_TYPE     CELL      DRUG\n",
       "0       Pancreatic_Adenocarcinoma  CCL_100    Drug_1\n",
       "1       Pancreatic_Adenocarcinoma  CCL_100    Drug_2\n",
       "2       Pancreatic_Adenocarcinoma  CCL_100    Drug_3\n",
       "3       Pancreatic_Adenocarcinoma  CCL_100    Drug_5\n",
       "4       Pancreatic_Adenocarcinoma  CCL_100    Drug_6\n",
       "...                           ...      ...       ...\n",
       "530088                   Lung_NOS  CCL_998  Drug_494\n",
       "530089                   Lung_NOS  CCL_998  Drug_496\n",
       "530090                   Lung_NOS  CCL_998  Drug_497\n",
       "530091                   Lung_NOS  CCL_998  Drug_499\n",
       "530092                   Lung_NOS  CCL_998  Drug_500\n",
       "\n",
       "[530093 rows x 3 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cl_cancer_drug = df_cl_cancer_map.merge(df_uniq_cl_drugs, on='CELL', how='right', sort='true')\n",
    "df_cl_cancer_drug"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CANCER_TYPE</th>\n",
       "      <th>CELL</th>\n",
       "      <th>DRUG</th>\n",
       "      <th>CELL_DRUG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Pancreatic_Adenocarcinoma</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>Drug_1</td>\n",
       "      <td>CCL_100.Drug_1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Pancreatic_Adenocarcinoma</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>Drug_2</td>\n",
       "      <td>CCL_100.Drug_2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Pancreatic_Adenocarcinoma</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>Drug_3</td>\n",
       "      <td>CCL_100.Drug_3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Pancreatic_Adenocarcinoma</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>Drug_5</td>\n",
       "      <td>CCL_100.Drug_5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Pancreatic_Adenocarcinoma</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>Drug_6</td>\n",
       "      <td>CCL_100.Drug_6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>530088</td>\n",
       "      <td>Lung_NOS</td>\n",
       "      <td>CCL_998</td>\n",
       "      <td>Drug_494</td>\n",
       "      <td>CCL_998.Drug_494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>530089</td>\n",
       "      <td>Lung_NOS</td>\n",
       "      <td>CCL_998</td>\n",
       "      <td>Drug_496</td>\n",
       "      <td>CCL_998.Drug_496</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>530090</td>\n",
       "      <td>Lung_NOS</td>\n",
       "      <td>CCL_998</td>\n",
       "      <td>Drug_497</td>\n",
       "      <td>CCL_998.Drug_497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>530091</td>\n",
       "      <td>Lung_NOS</td>\n",
       "      <td>CCL_998</td>\n",
       "      <td>Drug_499</td>\n",
       "      <td>CCL_998.Drug_499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>530092</td>\n",
       "      <td>Lung_NOS</td>\n",
       "      <td>CCL_998</td>\n",
       "      <td>Drug_500</td>\n",
       "      <td>CCL_998.Drug_500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>530093 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      CANCER_TYPE     CELL      DRUG         CELL_DRUG\n",
       "0       Pancreatic_Adenocarcinoma  CCL_100    Drug_1    CCL_100.Drug_1\n",
       "1       Pancreatic_Adenocarcinoma  CCL_100    Drug_2    CCL_100.Drug_2\n",
       "2       Pancreatic_Adenocarcinoma  CCL_100    Drug_3    CCL_100.Drug_3\n",
       "3       Pancreatic_Adenocarcinoma  CCL_100    Drug_5    CCL_100.Drug_5\n",
       "4       Pancreatic_Adenocarcinoma  CCL_100    Drug_6    CCL_100.Drug_6\n",
       "...                           ...      ...       ...               ...\n",
       "530088                   Lung_NOS  CCL_998  Drug_494  CCL_998.Drug_494\n",
       "530089                   Lung_NOS  CCL_998  Drug_496  CCL_998.Drug_496\n",
       "530090                   Lung_NOS  CCL_998  Drug_497  CCL_998.Drug_497\n",
       "530091                   Lung_NOS  CCL_998  Drug_499  CCL_998.Drug_499\n",
       "530092                   Lung_NOS  CCL_998  Drug_500  CCL_998.Drug_500\n",
       "\n",
       "[530093 rows x 4 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cl_cancer_drug['CELL_DRUG'] = df_cl_cancer_drug.CELL.astype(str) + '.' + df_cl_cancer_drug.DRUG.astype(str)\n",
    "df_cl_cancer_drug"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>CELL</th>\n",
       "      <th>DRUG</th>\n",
       "      <th>CELL_DRUG</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CANCER_TYPE</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Lung_Adenocarcinoma</td>\n",
       "      <td>41389</td>\n",
       "      <td>41389</td>\n",
       "      <td>41389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Skin_Cutaneous_Melanoma</td>\n",
       "      <td>35413</td>\n",
       "      <td>35413</td>\n",
       "      <td>35413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Colon_Adenocarcinoma</td>\n",
       "      <td>33172</td>\n",
       "      <td>33172</td>\n",
       "      <td>33172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Lymphoid_Leukemia</td>\n",
       "      <td>30080</td>\n",
       "      <td>30080</td>\n",
       "      <td>30080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Breast_Invasive_Carcinoma</td>\n",
       "      <td>29982</td>\n",
       "      <td>29982</td>\n",
       "      <td>29982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Lung_Small_Cell_Carcinoma</td>\n",
       "      <td>23222</td>\n",
       "      <td>23222</td>\n",
       "      <td>23222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Sarcoma</td>\n",
       "      <td>20177</td>\n",
       "      <td>20177</td>\n",
       "      <td>20177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Ovarian_Serous_Cystadenocarcinoma</td>\n",
       "      <td>17539</td>\n",
       "      <td>17539</td>\n",
       "      <td>17539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Kidney_Renal_Clear_Cell_Carcinoma</td>\n",
       "      <td>17265</td>\n",
       "      <td>17265</td>\n",
       "      <td>17265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Glioblastoma_Multiforme</td>\n",
       "      <td>16673</td>\n",
       "      <td>16673</td>\n",
       "      <td>16673</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Lung_Non-Small_Cell_Carcinoma</td>\n",
       "      <td>16602</td>\n",
       "      <td>16602</td>\n",
       "      <td>16602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Pancreatic_Adenocarcinoma</td>\n",
       "      <td>16153</td>\n",
       "      <td>16153</td>\n",
       "      <td>16153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Esophageal_Carcinoma</td>\n",
       "      <td>15609</td>\n",
       "      <td>15609</td>\n",
       "      <td>15609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Head_and_Neck_Squamous_Cell_Carcinoma</td>\n",
       "      <td>14986</td>\n",
       "      <td>14986</td>\n",
       "      <td>14986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Uterine_Corpus_Endometrial_Carcinoma</td>\n",
       "      <td>13579</td>\n",
       "      <td>13579</td>\n",
       "      <td>13579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Stomach_Adenocarcinoma</td>\n",
       "      <td>12857</td>\n",
       "      <td>12857</td>\n",
       "      <td>12857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Lymphoid_Neoplasm_Diffuse_Large_B-cell_Lymphoma</td>\n",
       "      <td>12625</td>\n",
       "      <td>12625</td>\n",
       "      <td>12625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Lung_Squamous_Cell_Carcinoma</td>\n",
       "      <td>12221</td>\n",
       "      <td>12221</td>\n",
       "      <td>12221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Acute_Myeloid_Leukemia</td>\n",
       "      <td>12084</td>\n",
       "      <td>12084</td>\n",
       "      <td>12084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Liver_Hepatocellular_Carcinoma</td>\n",
       "      <td>11975</td>\n",
       "      <td>11975</td>\n",
       "      <td>11975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Ovary_NOS</td>\n",
       "      <td>11847</td>\n",
       "      <td>11847</td>\n",
       "      <td>11847</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  CELL   DRUG  CELL_DRUG\n",
       "CANCER_TYPE                                                             \n",
       "Lung_Adenocarcinoma                              41389  41389      41389\n",
       "Skin_Cutaneous_Melanoma                          35413  35413      35413\n",
       "Colon_Adenocarcinoma                             33172  33172      33172\n",
       "Lymphoid_Leukemia                                30080  30080      30080\n",
       "Breast_Invasive_Carcinoma                        29982  29982      29982\n",
       "Lung_Small_Cell_Carcinoma                        23222  23222      23222\n",
       "Sarcoma                                          20177  20177      20177\n",
       "Ovarian_Serous_Cystadenocarcinoma                17539  17539      17539\n",
       "Kidney_Renal_Clear_Cell_Carcinoma                17265  17265      17265\n",
       "Glioblastoma_Multiforme                          16673  16673      16673\n",
       "Lung_Non-Small_Cell_Carcinoma                    16602  16602      16602\n",
       "Pancreatic_Adenocarcinoma                        16153  16153      16153\n",
       "Esophageal_Carcinoma                             15609  15609      15609\n",
       "Head_and_Neck_Squamous_Cell_Carcinoma            14986  14986      14986\n",
       "Uterine_Corpus_Endometrial_Carcinoma             13579  13579      13579\n",
       "Stomach_Adenocarcinoma                           12857  12857      12857\n",
       "Lymphoid_Neoplasm_Diffuse_Large_B-cell_Lymphoma  12625  12625      12625\n",
       "Lung_Squamous_Cell_Carcinoma                     12221  12221      12221\n",
       "Acute_Myeloid_Leukemia                           12084  12084      12084\n",
       "Liver_Hepatocellular_Carcinoma                   11975  11975      11975\n",
       "Ovary_NOS                                        11847  11847      11847"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_n = df_cl_cancer_drug.groupby(['CANCER_TYPE']).count().sort_values('CELL_DRUG', ascending=False).head(21)\n",
    "top_n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Lung_Adenocarcinoma',\n",
       " 'Skin_Cutaneous_Melanoma',\n",
       " 'Colon_Adenocarcinoma',\n",
       " 'Lymphoid_Leukemia',\n",
       " 'Breast_Invasive_Carcinoma',\n",
       " 'Lung_Small_Cell_Carcinoma',\n",
       " 'Sarcoma',\n",
       " 'Ovarian_Serous_Cystadenocarcinoma',\n",
       " 'Kidney_Renal_Clear_Cell_Carcinoma',\n",
       " 'Glioblastoma_Multiforme',\n",
       " 'Lung_Non-Small_Cell_Carcinoma',\n",
       " 'Pancreatic_Adenocarcinoma',\n",
       " 'Esophageal_Carcinoma',\n",
       " 'Head_and_Neck_Squamous_Cell_Carcinoma',\n",
       " 'Uterine_Corpus_Endometrial_Carcinoma',\n",
       " 'Stomach_Adenocarcinoma',\n",
       " 'Lymphoid_Neoplasm_Diffuse_Large_B-cell_Lymphoma',\n",
       " 'Lung_Squamous_Cell_Carcinoma',\n",
       " 'Acute_Myeloid_Leukemia',\n",
       " 'Liver_Hepatocellular_Carcinoma',\n",
       " 'Ovary_NOS']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_n_cancer_types = top_n.index.to_list()\n",
    "top_n_cancer_types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Indentify cell lines associated with the target cancer types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "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>CELL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>CCL_100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>CCL_1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CCL_1001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>CCL_1002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>CCL_1003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>698</td>\n",
       "      <td>CCL_99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>699</td>\n",
       "      <td>CCL_990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>700</td>\n",
       "      <td>CCL_992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>701</td>\n",
       "      <td>CCL_993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>702</td>\n",
       "      <td>CCL_994</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>703 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         CELL\n",
       "0     CCL_100\n",
       "1    CCL_1000\n",
       "2    CCL_1001\n",
       "3    CCL_1002\n",
       "4    CCL_1003\n",
       "..        ...\n",
       "698    CCL_99\n",
       "699   CCL_990\n",
       "700   CCL_992\n",
       "701   CCL_993\n",
       "702   CCL_994\n",
       "\n",
       "[703 rows x 1 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cl = df_cl_cancer_drug[df_cl_cancer_drug['CANCER_TYPE'].isin(top_n_cancer_types)][['CELL']].drop_duplicates().reset_index(drop=True)\n",
    "df_cl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identify drugs associated with the target cancer type & filtered by drug_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>DRUG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Drug_1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Drug_2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Drug_3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Drug_5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Drug_6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1487</td>\n",
       "      <td>Drug_1364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1488</td>\n",
       "      <td>Drug_1557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1489</td>\n",
       "      <td>Drug_1560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1490</td>\n",
       "      <td>Drug_1567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1491</td>\n",
       "      <td>Drug_932</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1492 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           DRUG\n",
       "0        Drug_1\n",
       "1        Drug_2\n",
       "2        Drug_3\n",
       "3        Drug_5\n",
       "4        Drug_6\n",
       "...         ...\n",
       "1487  Drug_1364\n",
       "1488  Drug_1557\n",
       "1489  Drug_1560\n",
       "1490  Drug_1567\n",
       "1491   Drug_932\n",
       "\n",
       "[1492 rows x 1 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_drugs = df_cl_cancer_drug[df_cl_cancer_drug['CANCER_TYPE'].isin(top_n_cancer_types)][['DRUG']].drop_duplicates().reset_index(drop=True)\n",
    "df_drugs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter response by cell lines (730) and drugs (1493)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "cl_filter = df_cl.CELL.to_list()\n",
    "dr_filter = df_drugs.DRUG.to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>CELL</th>\n",
       "      <th>DRUG</th>\n",
       "      <th>AUC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>CCL_65</td>\n",
       "      <td>Drug_1</td>\n",
       "      <td>0.8126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>CCL_65</td>\n",
       "      <td>Drug_3</td>\n",
       "      <td>0.4959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CCL_65</td>\n",
       "      <td>Drug_7</td>\n",
       "      <td>0.8691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>CCL_65</td>\n",
       "      <td>Drug_8</td>\n",
       "      <td>0.7881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>CCL_65</td>\n",
       "      <td>Drug_16</td>\n",
       "      <td>0.7088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>412873</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_654</td>\n",
       "      <td>0.9548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>412874</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_655</td>\n",
       "      <td>0.8190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>412875</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_656</td>\n",
       "      <td>0.9105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>412876</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_657</td>\n",
       "      <td>0.9566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>412877</td>\n",
       "      <td>CCL_393</td>\n",
       "      <td>Drug_658</td>\n",
       "      <td>0.8426</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>412878 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           CELL      DRUG     AUC\n",
       "0        CCL_65    Drug_1  0.8126\n",
       "1        CCL_65    Drug_3  0.4959\n",
       "2        CCL_65    Drug_7  0.8691\n",
       "3        CCL_65    Drug_8  0.7881\n",
       "4        CCL_65   Drug_16  0.7088\n",
       "...         ...       ...     ...\n",
       "412873  CCL_393  Drug_654  0.9548\n",
       "412874  CCL_393  Drug_655  0.8190\n",
       "412875  CCL_393  Drug_656  0.9105\n",
       "412876  CCL_393  Drug_657  0.9566\n",
       "412877  CCL_393  Drug_658  0.8426\n",
       "\n",
       "[412878 rows x 3 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_response = df_response[df_response.CELL.isin(cl_filter) & df_response.DRUG.isin(dr_filter)][['CELL','DRUG','AUC']].drop_duplicates().reset_index(drop=True)\n",
    "df_response "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Join response data with Drug descriptor & RNASeq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_rnaseq = pd.read_csv(cell_rnaseq_path, sep='\\t', low_memory=False)\n",
    "df_rnaseq = df_rnaseq[df_rnaseq['Sample'].isin(cl_filter)].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_rnaseq.rename(columns={'Sample':'CELL'}, inplace=True)\n",
    "df_rnaseq = df_rnaseq.set_index(['CELL'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add prefix on the attribute columns\n",
    "cols = df_rnaseq.columns.to_list()\n",
    "ge_cols = [f'GE_{col}' for col in cols]\n",
    "df_rnaseq.columns = ge_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>GE_A1BG</th>\n",
       "      <th>GE_A1CF</th>\n",
       "      <th>GE_A2M</th>\n",
       "      <th>GE_A2ML1</th>\n",
       "      <th>GE_A3GALT2</th>\n",
       "      <th>GE_A4GALT</th>\n",
       "      <th>GE_A4GNT</th>\n",
       "      <th>GE_AAAS</th>\n",
       "      <th>GE_AACS</th>\n",
       "      <th>GE_AADAC</th>\n",
       "      <th>...</th>\n",
       "      <th>GE_ZWILCH</th>\n",
       "      <th>GE_ZWINT</th>\n",
       "      <th>GE_ZXDA</th>\n",
       "      <th>GE_ZXDB</th>\n",
       "      <th>GE_ZXDC</th>\n",
       "      <th>GE_ZYG11A</th>\n",
       "      <th>GE_ZYG11B</th>\n",
       "      <th>GE_ZYX</th>\n",
       "      <th>GE_ZZEF1</th>\n",
       "      <th>GE_ZZZ3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CELL</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>CCL_62</td>\n",
       "      <td>-1.421816</td>\n",
       "      <td>2.175727</td>\n",
       "      <td>-0.466091</td>\n",
       "      <td>-0.222140</td>\n",
       "      <td>-0.408307</td>\n",
       "      <td>-0.429530</td>\n",
       "      <td>-0.427922</td>\n",
       "      <td>-0.462058</td>\n",
       "      <td>-0.368245</td>\n",
       "      <td>-0.507094</td>\n",
       "      <td>...</td>\n",
       "      <td>0.949667</td>\n",
       "      <td>0.090989</td>\n",
       "      <td>0.491263</td>\n",
       "      <td>1.117663</td>\n",
       "      <td>0.445099</td>\n",
       "      <td>-0.988673</td>\n",
       "      <td>0.801770</td>\n",
       "      <td>-0.364203</td>\n",
       "      <td>-0.014804</td>\n",
       "      <td>1.247922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>CCL_65</td>\n",
       "      <td>1.209724</td>\n",
       "      <td>-0.278733</td>\n",
       "      <td>-0.416899</td>\n",
       "      <td>-0.372810</td>\n",
       "      <td>1.115141</td>\n",
       "      <td>-0.753543</td>\n",
       "      <td>-0.080482</td>\n",
       "      <td>-0.130010</td>\n",
       "      <td>-0.254613</td>\n",
       "      <td>-0.436202</td>\n",
       "      <td>...</td>\n",
       "      <td>0.632992</td>\n",
       "      <td>-0.097421</td>\n",
       "      <td>-0.918279</td>\n",
       "      <td>-1.917117</td>\n",
       "      <td>-1.377897</td>\n",
       "      <td>-1.020918</td>\n",
       "      <td>0.201952</td>\n",
       "      <td>0.551684</td>\n",
       "      <td>-0.176323</td>\n",
       "      <td>1.228964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>CCL_70</td>\n",
       "      <td>1.126623</td>\n",
       "      <td>-0.339088</td>\n",
       "      <td>-0.367707</td>\n",
       "      <td>-0.362765</td>\n",
       "      <td>-0.408307</td>\n",
       "      <td>-1.341159</td>\n",
       "      <td>-0.393178</td>\n",
       "      <td>0.356993</td>\n",
       "      <td>-3.297435</td>\n",
       "      <td>-0.535450</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.079526</td>\n",
       "      <td>0.632668</td>\n",
       "      <td>-1.937947</td>\n",
       "      <td>-0.858835</td>\n",
       "      <td>0.396701</td>\n",
       "      <td>-1.020918</td>\n",
       "      <td>-0.114619</td>\n",
       "      <td>-0.822147</td>\n",
       "      <td>0.049804</td>\n",
       "      <td>-0.534133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>CCL_71</td>\n",
       "      <td>-1.454133</td>\n",
       "      <td>0.777490</td>\n",
       "      <td>-0.373173</td>\n",
       "      <td>-0.332631</td>\n",
       "      <td>0.244599</td>\n",
       "      <td>0.317347</td>\n",
       "      <td>0.475421</td>\n",
       "      <td>0.179901</td>\n",
       "      <td>0.225168</td>\n",
       "      <td>-0.450381</td>\n",
       "      <td>...</td>\n",
       "      <td>0.854664</td>\n",
       "      <td>0.114540</td>\n",
       "      <td>-0.153528</td>\n",
       "      <td>-0.594264</td>\n",
       "      <td>-0.877783</td>\n",
       "      <td>0.309214</td>\n",
       "      <td>-1.064331</td>\n",
       "      <td>1.405687</td>\n",
       "      <td>-0.353995</td>\n",
       "      <td>0.243146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>CCL_72</td>\n",
       "      <td>-1.537235</td>\n",
       "      <td>-0.278733</td>\n",
       "      <td>-0.416899</td>\n",
       "      <td>-0.372810</td>\n",
       "      <td>0.353417</td>\n",
       "      <td>0.136120</td>\n",
       "      <td>-0.184714</td>\n",
       "      <td>-1.081880</td>\n",
       "      <td>0.136787</td>\n",
       "      <td>-0.535450</td>\n",
       "      <td>...</td>\n",
       "      <td>1.931358</td>\n",
       "      <td>0.891732</td>\n",
       "      <td>0.221351</td>\n",
       "      <td>-0.205190</td>\n",
       "      <td>-1.103641</td>\n",
       "      <td>-0.988673</td>\n",
       "      <td>-0.081296</td>\n",
       "      <td>1.418064</td>\n",
       "      <td>-1.387718</td>\n",
       "      <td>0.736055</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>CCL_46</td>\n",
       "      <td>0.934353</td>\n",
       "      <td>-0.265602</td>\n",
       "      <td>2.265315</td>\n",
       "      <td>0.083384</td>\n",
       "      <td>-0.310406</td>\n",
       "      <td>1.769761</td>\n",
       "      <td>-0.158447</td>\n",
       "      <td>1.154452</td>\n",
       "      <td>1.315011</td>\n",
       "      <td>-0.383944</td>\n",
       "      <td>...</td>\n",
       "      <td>1.098708</td>\n",
       "      <td>-0.104170</td>\n",
       "      <td>1.660447</td>\n",
       "      <td>2.175937</td>\n",
       "      <td>1.473070</td>\n",
       "      <td>-1.008849</td>\n",
       "      <td>0.775270</td>\n",
       "      <td>0.448669</td>\n",
       "      <td>0.139148</td>\n",
       "      <td>0.981329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>CCL_50</td>\n",
       "      <td>1.430925</td>\n",
       "      <td>-0.265602</td>\n",
       "      <td>-0.438163</td>\n",
       "      <td>-0.259848</td>\n",
       "      <td>-0.310406</td>\n",
       "      <td>0.053276</td>\n",
       "      <td>-0.158447</td>\n",
       "      <td>-0.808038</td>\n",
       "      <td>-0.593292</td>\n",
       "      <td>-0.383944</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.112214</td>\n",
       "      <td>1.017499</td>\n",
       "      <td>-0.211576</td>\n",
       "      <td>-0.357354</td>\n",
       "      <td>-1.157218</td>\n",
       "      <td>1.111265</td>\n",
       "      <td>-0.490894</td>\n",
       "      <td>0.023324</td>\n",
       "      <td>-1.154084</td>\n",
       "      <td>-1.127772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>CCL_56</td>\n",
       "      <td>-1.439092</td>\n",
       "      <td>-0.265602</td>\n",
       "      <td>-0.460767</td>\n",
       "      <td>-0.259848</td>\n",
       "      <td>-0.310406</td>\n",
       "      <td>-0.021084</td>\n",
       "      <td>-0.158447</td>\n",
       "      <td>-0.760938</td>\n",
       "      <td>0.263190</td>\n",
       "      <td>-0.383944</td>\n",
       "      <td>...</td>\n",
       "      <td>1.078184</td>\n",
       "      <td>-0.410080</td>\n",
       "      <td>-0.917690</td>\n",
       "      <td>-0.743526</td>\n",
       "      <td>-1.231836</td>\n",
       "      <td>0.851970</td>\n",
       "      <td>0.113412</td>\n",
       "      <td>-0.127183</td>\n",
       "      <td>-0.943231</td>\n",
       "      <td>0.845883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>CCL_27</td>\n",
       "      <td>0.631360</td>\n",
       "      <td>-0.265602</td>\n",
       "      <td>-0.383912</td>\n",
       "      <td>-0.133394</td>\n",
       "      <td>-0.310406</td>\n",
       "      <td>-1.495898</td>\n",
       "      <td>-0.158447</td>\n",
       "      <td>0.416556</td>\n",
       "      <td>-0.488110</td>\n",
       "      <td>-0.383944</td>\n",
       "      <td>...</td>\n",
       "      <td>1.406569</td>\n",
       "      <td>0.172605</td>\n",
       "      <td>-1.410328</td>\n",
       "      <td>-0.295566</td>\n",
       "      <td>-0.299110</td>\n",
       "      <td>0.150350</td>\n",
       "      <td>0.214129</td>\n",
       "      <td>0.402862</td>\n",
       "      <td>0.293773</td>\n",
       "      <td>0.265396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>CCL_30</td>\n",
       "      <td>0.311534</td>\n",
       "      <td>-0.265602</td>\n",
       "      <td>-0.460767</td>\n",
       "      <td>-0.259848</td>\n",
       "      <td>-0.310406</td>\n",
       "      <td>0.171013</td>\n",
       "      <td>-0.158447</td>\n",
       "      <td>0.809054</td>\n",
       "      <td>-0.367902</td>\n",
       "      <td>-0.383944</td>\n",
       "      <td>...</td>\n",
       "      <td>0.195648</td>\n",
       "      <td>0.143471</td>\n",
       "      <td>-0.244418</td>\n",
       "      <td>-0.836207</td>\n",
       "      <td>0.801507</td>\n",
       "      <td>1.401065</td>\n",
       "      <td>0.170965</td>\n",
       "      <td>1.286270</td>\n",
       "      <td>0.588967</td>\n",
       "      <td>0.226697</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>703 rows × 17743 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         GE_A1BG   GE_A1CF    GE_A2M  GE_A2ML1  GE_A3GALT2  GE_A4GALT  \\\n",
       "CELL                                                                    \n",
       "CCL_62 -1.421816  2.175727 -0.466091 -0.222140   -0.408307  -0.429530   \n",
       "CCL_65  1.209724 -0.278733 -0.416899 -0.372810    1.115141  -0.753543   \n",
       "CCL_70  1.126623 -0.339088 -0.367707 -0.362765   -0.408307  -1.341159   \n",
       "CCL_71 -1.454133  0.777490 -0.373173 -0.332631    0.244599   0.317347   \n",
       "CCL_72 -1.537235 -0.278733 -0.416899 -0.372810    0.353417   0.136120   \n",
       "...          ...       ...       ...       ...         ...        ...   \n",
       "CCL_46  0.934353 -0.265602  2.265315  0.083384   -0.310406   1.769761   \n",
       "CCL_50  1.430925 -0.265602 -0.438163 -0.259848   -0.310406   0.053276   \n",
       "CCL_56 -1.439092 -0.265602 -0.460767 -0.259848   -0.310406  -0.021084   \n",
       "CCL_27  0.631360 -0.265602 -0.383912 -0.133394   -0.310406  -1.495898   \n",
       "CCL_30  0.311534 -0.265602 -0.460767 -0.259848   -0.310406   0.171013   \n",
       "\n",
       "        GE_A4GNT   GE_AAAS   GE_AACS  GE_AADAC  ...  GE_ZWILCH  GE_ZWINT  \\\n",
       "CELL                                            ...                        \n",
       "CCL_62 -0.427922 -0.462058 -0.368245 -0.507094  ...   0.949667  0.090989   \n",
       "CCL_65 -0.080482 -0.130010 -0.254613 -0.436202  ...   0.632992 -0.097421   \n",
       "CCL_70 -0.393178  0.356993 -3.297435 -0.535450  ...  -0.079526  0.632668   \n",
       "CCL_71  0.475421  0.179901  0.225168 -0.450381  ...   0.854664  0.114540   \n",
       "CCL_72 -0.184714 -1.081880  0.136787 -0.535450  ...   1.931358  0.891732   \n",
       "...          ...       ...       ...       ...  ...        ...       ...   \n",
       "CCL_46 -0.158447  1.154452  1.315011 -0.383944  ...   1.098708 -0.104170   \n",
       "CCL_50 -0.158447 -0.808038 -0.593292 -0.383944  ...  -0.112214  1.017499   \n",
       "CCL_56 -0.158447 -0.760938  0.263190 -0.383944  ...   1.078184 -0.410080   \n",
       "CCL_27 -0.158447  0.416556 -0.488110 -0.383944  ...   1.406569  0.172605   \n",
       "CCL_30 -0.158447  0.809054 -0.367902 -0.383944  ...   0.195648  0.143471   \n",
       "\n",
       "         GE_ZXDA   GE_ZXDB   GE_ZXDC  GE_ZYG11A  GE_ZYG11B    GE_ZYX  \\\n",
       "CELL                                                                   \n",
       "CCL_62  0.491263  1.117663  0.445099  -0.988673   0.801770 -0.364203   \n",
       "CCL_65 -0.918279 -1.917117 -1.377897  -1.020918   0.201952  0.551684   \n",
       "CCL_70 -1.937947 -0.858835  0.396701  -1.020918  -0.114619 -0.822147   \n",
       "CCL_71 -0.153528 -0.594264 -0.877783   0.309214  -1.064331  1.405687   \n",
       "CCL_72  0.221351 -0.205190 -1.103641  -0.988673  -0.081296  1.418064   \n",
       "...          ...       ...       ...        ...        ...       ...   \n",
       "CCL_46  1.660447  2.175937  1.473070  -1.008849   0.775270  0.448669   \n",
       "CCL_50 -0.211576 -0.357354 -1.157218   1.111265  -0.490894  0.023324   \n",
       "CCL_56 -0.917690 -0.743526 -1.231836   0.851970   0.113412 -0.127183   \n",
       "CCL_27 -1.410328 -0.295566 -0.299110   0.150350   0.214129  0.402862   \n",
       "CCL_30 -0.244418 -0.836207  0.801507   1.401065   0.170965  1.286270   \n",
       "\n",
       "        GE_ZZEF1   GE_ZZZ3  \n",
       "CELL                        \n",
       "CCL_62 -0.014804  1.247922  \n",
       "CCL_65 -0.176323  1.228964  \n",
       "CCL_70  0.049804 -0.534133  \n",
       "CCL_71 -0.353995  0.243146  \n",
       "CCL_72 -1.387718  0.736055  \n",
       "...          ...       ...  \n",
       "CCL_46  0.139148  0.981329  \n",
       "CCL_50 -1.154084 -1.127772  \n",
       "CCL_56 -0.943231  0.845883  \n",
       "CCL_27  0.293773  0.265396  \n",
       "CCL_30  0.588967  0.226697  \n",
       "\n",
       "[703 rows x 17743 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_rnaseq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_descriptor = pd.read_csv(drug_descriptors_mordred_path, sep='\\t', low_memory=False, na_values='na')\n",
    "df_descriptor = df_descriptor[df_descriptor.ID.isin(dr_filter)].set_index(['ID']).fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "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>DD_ABC</th>\n",
       "      <th>DD_ABCGG</th>\n",
       "      <th>DD_nAcid</th>\n",
       "      <th>DD_nBase</th>\n",
       "      <th>DD_SpAbs_A</th>\n",
       "      <th>DD_SpMax_A</th>\n",
       "      <th>DD_SpDiam_A</th>\n",
       "      <th>DD_SpAD_A</th>\n",
       "      <th>DD_SpMAD_A</th>\n",
       "      <th>DD_LogEE_A</th>\n",
       "      <th>...</th>\n",
       "      <th>DD_SRW10</th>\n",
       "      <th>DD_TSRW10</th>\n",
       "      <th>DD_MW</th>\n",
       "      <th>DD_AMW</th>\n",
       "      <th>DD_WPath</th>\n",
       "      <th>DD_WPol</th>\n",
       "      <th>DD_Zagreb1</th>\n",
       "      <th>DD_Zagreb2</th>\n",
       "      <th>DD_mZagreb1</th>\n",
       "      <th>DD_mZagreb2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Drug_1</td>\n",
       "      <td>0.178943</td>\n",
       "      <td>0.026685</td>\n",
       "      <td>-0.296681</td>\n",
       "      <td>0.307032</td>\n",
       "      <td>0.187441</td>\n",
       "      <td>0.380043</td>\n",
       "      <td>0.267720</td>\n",
       "      <td>0.187441</td>\n",
       "      <td>0.301024</td>\n",
       "      <td>0.372629</td>\n",
       "      <td>...</td>\n",
       "      <td>0.573775</td>\n",
       "      <td>0.374900</td>\n",
       "      <td>-0.013642</td>\n",
       "      <td>-0.550884</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>-0.031402</td>\n",
       "      <td>0.219639</td>\n",
       "      <td>0.261155</td>\n",
       "      <td>-0.394765</td>\n",
       "      <td>0.054656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_2</td>\n",
       "      <td>-0.066723</td>\n",
       "      <td>-0.179484</td>\n",
       "      <td>-0.296681</td>\n",
       "      <td>0.307032</td>\n",
       "      <td>-0.008114</td>\n",
       "      <td>0.079384</td>\n",
       "      <td>0.163981</td>\n",
       "      <td>-0.008114</td>\n",
       "      <td>0.249253</td>\n",
       "      <td>0.174481</td>\n",
       "      <td>...</td>\n",
       "      <td>0.076371</td>\n",
       "      <td>-0.490116</td>\n",
       "      <td>0.128748</td>\n",
       "      <td>0.368511</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>-0.099157</td>\n",
       "      <td>-0.079380</td>\n",
       "      <td>-0.095725</td>\n",
       "      <td>-0.175268</td>\n",
       "      <td>-0.066202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_3</td>\n",
       "      <td>-0.340374</td>\n",
       "      <td>-0.409640</td>\n",
       "      <td>-0.296681</td>\n",
       "      <td>0.307032</td>\n",
       "      <td>-0.247362</td>\n",
       "      <td>0.048792</td>\n",
       "      <td>-0.128580</td>\n",
       "      <td>-0.247362</td>\n",
       "      <td>0.261627</td>\n",
       "      <td>-0.058516</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.523674</td>\n",
       "      <td>-0.070684</td>\n",
       "      <td>-0.381396</td>\n",
       "      <td>-0.528306</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>-0.505682</td>\n",
       "      <td>-0.401400</td>\n",
       "      <td>-0.452605</td>\n",
       "      <td>-0.353842</td>\n",
       "      <td>-0.243934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_4</td>\n",
       "      <td>0.024618</td>\n",
       "      <td>-0.056992</td>\n",
       "      <td>-0.296681</td>\n",
       "      <td>-0.398811</td>\n",
       "      <td>0.037584</td>\n",
       "      <td>-0.105526</td>\n",
       "      <td>-0.024433</td>\n",
       "      <td>0.037584</td>\n",
       "      <td>-0.035223</td>\n",
       "      <td>0.239573</td>\n",
       "      <td>...</td>\n",
       "      <td>0.025458</td>\n",
       "      <td>-0.393486</td>\n",
       "      <td>0.087830</td>\n",
       "      <td>0.850482</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>-0.099157</td>\n",
       "      <td>-0.010375</td>\n",
       "      <td>-0.075898</td>\n",
       "      <td>0.186531</td>\n",
       "      <td>0.026219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_5</td>\n",
       "      <td>0.773429</td>\n",
       "      <td>0.486916</td>\n",
       "      <td>-0.296681</td>\n",
       "      <td>0.307032</td>\n",
       "      <td>0.846020</td>\n",
       "      <td>0.567565</td>\n",
       "      <td>0.547725</td>\n",
       "      <td>0.846020</td>\n",
       "      <td>0.440356</td>\n",
       "      <td>0.746141</td>\n",
       "      <td>...</td>\n",
       "      <td>1.235954</td>\n",
       "      <td>0.913274</td>\n",
       "      <td>0.586806</td>\n",
       "      <td>-0.467614</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>1.188175</td>\n",
       "      <td>0.932685</td>\n",
       "      <td>1.103790</td>\n",
       "      <td>0.324182</td>\n",
       "      <td>0.612735</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_1563</td>\n",
       "      <td>-0.501424</td>\n",
       "      <td>-0.561854</td>\n",
       "      <td>-0.296681</td>\n",
       "      <td>-0.398811</td>\n",
       "      <td>-0.421235</td>\n",
       "      <td>-0.057452</td>\n",
       "      <td>0.024552</td>\n",
       "      <td>-0.421235</td>\n",
       "      <td>0.302990</td>\n",
       "      <td>-0.223737</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.479939</td>\n",
       "      <td>-0.889142</td>\n",
       "      <td>-0.565242</td>\n",
       "      <td>-0.556871</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>-0.539559</td>\n",
       "      <td>-0.539409</td>\n",
       "      <td>-0.571565</td>\n",
       "      <td>-0.454290</td>\n",
       "      <td>-0.435885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_1564</td>\n",
       "      <td>-0.893817</td>\n",
       "      <td>-0.885040</td>\n",
       "      <td>-0.296681</td>\n",
       "      <td>-0.398811</td>\n",
       "      <td>-0.841376</td>\n",
       "      <td>0.185123</td>\n",
       "      <td>0.016088</td>\n",
       "      <td>-0.841376</td>\n",
       "      <td>0.845137</td>\n",
       "      <td>-0.589147</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.636688</td>\n",
       "      <td>-0.250929</td>\n",
       "      <td>-0.973778</td>\n",
       "      <td>-0.293147</td>\n",
       "      <td>-0.055784</td>\n",
       "      <td>-1.013840</td>\n",
       "      <td>-0.861430</td>\n",
       "      <td>-0.829312</td>\n",
       "      <td>-1.004893</td>\n",
       "      <td>-0.969081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_1565</td>\n",
       "      <td>-0.366195</td>\n",
       "      <td>-0.401324</td>\n",
       "      <td>-0.296681</td>\n",
       "      <td>0.307032</td>\n",
       "      <td>-0.259657</td>\n",
       "      <td>0.103924</td>\n",
       "      <td>0.188986</td>\n",
       "      <td>-0.259657</td>\n",
       "      <td>0.495251</td>\n",
       "      <td>-0.071912</td>\n",
       "      <td>...</td>\n",
       "      <td>0.019703</td>\n",
       "      <td>-0.750884</td>\n",
       "      <td>-0.471206</td>\n",
       "      <td>-0.696284</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>-0.234665</td>\n",
       "      <td>-0.355397</td>\n",
       "      <td>-0.323732</td>\n",
       "      <td>-0.506374</td>\n",
       "      <td>-0.307917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_1566</td>\n",
       "      <td>-1.266211</td>\n",
       "      <td>-1.101860</td>\n",
       "      <td>-0.296681</td>\n",
       "      <td>-0.398811</td>\n",
       "      <td>-1.284552</td>\n",
       "      <td>-0.134919</td>\n",
       "      <td>-0.054383</td>\n",
       "      <td>-1.284552</td>\n",
       "      <td>-1.151456</td>\n",
       "      <td>-1.403381</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.501341</td>\n",
       "      <td>-1.627462</td>\n",
       "      <td>-1.161396</td>\n",
       "      <td>-1.793639</td>\n",
       "      <td>-0.055784</td>\n",
       "      <td>-1.115471</td>\n",
       "      <td>-1.321460</td>\n",
       "      <td>-1.354720</td>\n",
       "      <td>-0.404996</td>\n",
       "      <td>-1.118376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_1567</td>\n",
       "      <td>-0.986739</td>\n",
       "      <td>-0.929145</td>\n",
       "      <td>-0.296681</td>\n",
       "      <td>-0.398811</td>\n",
       "      <td>-0.922879</td>\n",
       "      <td>-0.316179</td>\n",
       "      <td>-0.324368</td>\n",
       "      <td>-0.922879</td>\n",
       "      <td>0.187883</td>\n",
       "      <td>-0.855668</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.569252</td>\n",
       "      <td>-0.755835</td>\n",
       "      <td>-0.977934</td>\n",
       "      <td>-0.459280</td>\n",
       "      <td>-0.055784</td>\n",
       "      <td>-1.115471</td>\n",
       "      <td>-1.045442</td>\n",
       "      <td>-1.096973</td>\n",
       "      <td>-0.766794</td>\n",
       "      <td>-0.876660</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1492 rows × 1613 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             DD_ABC  DD_ABCGG  DD_nAcid  DD_nBase  DD_SpAbs_A  DD_SpMax_A  \\\n",
       "ID                                                                          \n",
       "Drug_1     0.178943  0.026685 -0.296681  0.307032    0.187441    0.380043   \n",
       "Drug_2    -0.066723 -0.179484 -0.296681  0.307032   -0.008114    0.079384   \n",
       "Drug_3    -0.340374 -0.409640 -0.296681  0.307032   -0.247362    0.048792   \n",
       "Drug_4     0.024618 -0.056992 -0.296681 -0.398811    0.037584   -0.105526   \n",
       "Drug_5     0.773429  0.486916 -0.296681  0.307032    0.846020    0.567565   \n",
       "...             ...       ...       ...       ...         ...         ...   \n",
       "Drug_1563 -0.501424 -0.561854 -0.296681 -0.398811   -0.421235   -0.057452   \n",
       "Drug_1564 -0.893817 -0.885040 -0.296681 -0.398811   -0.841376    0.185123   \n",
       "Drug_1565 -0.366195 -0.401324 -0.296681  0.307032   -0.259657    0.103924   \n",
       "Drug_1566 -1.266211 -1.101860 -0.296681 -0.398811   -1.284552   -0.134919   \n",
       "Drug_1567 -0.986739 -0.929145 -0.296681 -0.398811   -0.922879   -0.316179   \n",
       "\n",
       "           DD_SpDiam_A  DD_SpAD_A  DD_SpMAD_A  DD_LogEE_A  ...  DD_SRW10  \\\n",
       "ID                                                         ...             \n",
       "Drug_1        0.267720   0.187441    0.301024    0.372629  ...  0.573775   \n",
       "Drug_2        0.163981  -0.008114    0.249253    0.174481  ...  0.076371   \n",
       "Drug_3       -0.128580  -0.247362    0.261627   -0.058516  ... -0.523674   \n",
       "Drug_4       -0.024433   0.037584   -0.035223    0.239573  ...  0.025458   \n",
       "Drug_5        0.547725   0.846020    0.440356    0.746141  ...  1.235954   \n",
       "...                ...        ...         ...         ...  ...       ...   \n",
       "Drug_1563     0.024552  -0.421235    0.302990   -0.223737  ... -0.479939   \n",
       "Drug_1564     0.016088  -0.841376    0.845137   -0.589147  ... -0.636688   \n",
       "Drug_1565     0.188986  -0.259657    0.495251   -0.071912  ...  0.019703   \n",
       "Drug_1566    -0.054383  -1.284552   -1.151456   -1.403381  ... -1.501341   \n",
       "Drug_1567    -0.324368  -0.922879    0.187883   -0.855668  ... -1.569252   \n",
       "\n",
       "           DD_TSRW10     DD_MW    DD_AMW  DD_WPath   DD_WPol  DD_Zagreb1  \\\n",
       "ID                                                                         \n",
       "Drug_1      0.374900 -0.013642 -0.550884 -0.055783 -0.031402    0.219639   \n",
       "Drug_2     -0.490116  0.128748  0.368511 -0.055783 -0.099157   -0.079380   \n",
       "Drug_3     -0.070684 -0.381396 -0.528306 -0.055783 -0.505682   -0.401400   \n",
       "Drug_4     -0.393486  0.087830  0.850482 -0.055783 -0.099157   -0.010375   \n",
       "Drug_5      0.913274  0.586806 -0.467614 -0.055783  1.188175    0.932685   \n",
       "...              ...       ...       ...       ...       ...         ...   \n",
       "Drug_1563  -0.889142 -0.565242 -0.556871 -0.055783 -0.539559   -0.539409   \n",
       "Drug_1564  -0.250929 -0.973778 -0.293147 -0.055784 -1.013840   -0.861430   \n",
       "Drug_1565  -0.750884 -0.471206 -0.696284 -0.055783 -0.234665   -0.355397   \n",
       "Drug_1566  -1.627462 -1.161396 -1.793639 -0.055784 -1.115471   -1.321460   \n",
       "Drug_1567  -0.755835 -0.977934 -0.459280 -0.055784 -1.115471   -1.045442   \n",
       "\n",
       "           DD_Zagreb2  DD_mZagreb1  DD_mZagreb2  \n",
       "ID                                               \n",
       "Drug_1       0.261155    -0.394765     0.054656  \n",
       "Drug_2      -0.095725    -0.175268    -0.066202  \n",
       "Drug_3      -0.452605    -0.353842    -0.243934  \n",
       "Drug_4      -0.075898     0.186531     0.026219  \n",
       "Drug_5       1.103790     0.324182     0.612735  \n",
       "...               ...          ...          ...  \n",
       "Drug_1563   -0.571565    -0.454290    -0.435885  \n",
       "Drug_1564   -0.829312    -1.004893    -0.969081  \n",
       "Drug_1565   -0.323732    -0.506374    -0.307917  \n",
       "Drug_1566   -1.354720    -0.404996    -1.118376  \n",
       "Drug_1567   -1.096973    -0.766794    -0.876660  \n",
       "\n",
       "[1492 rows x 1613 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_descriptor.drop(labels=['NAME', 'CLEAN_NAME', 'SMILES'], axis=1, inplace=True)\n",
    "df_descriptor.columns = [ col.replace('dd_', 'DD_') for col in df_descriptor.columns]\n",
    "df_descriptor[df_descriptor.columns] = scaler.fit_transform(df_descriptor[df_descriptor.columns]).astype(dtype=np.float32)\n",
    "df_descriptor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 6.34 s, sys: 11.7 s, total: 18 s\n",
      "Wall time: 16.3 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_1 = df_response.merge(df_rnaseq, on='CELL', how='left', sort='true')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>CELL</th>\n",
       "      <th>AUC</th>\n",
       "      <th>GE_A1BG</th>\n",
       "      <th>GE_A1CF</th>\n",
       "      <th>GE_A2M</th>\n",
       "      <th>GE_A2ML1</th>\n",
       "      <th>GE_A3GALT2</th>\n",
       "      <th>GE_A4GALT</th>\n",
       "      <th>GE_A4GNT</th>\n",
       "      <th>GE_AAAS</th>\n",
       "      <th>...</th>\n",
       "      <th>GE_ZWILCH</th>\n",
       "      <th>GE_ZWINT</th>\n",
       "      <th>GE_ZXDA</th>\n",
       "      <th>GE_ZXDB</th>\n",
       "      <th>GE_ZXDC</th>\n",
       "      <th>GE_ZYG11A</th>\n",
       "      <th>GE_ZYG11B</th>\n",
       "      <th>GE_ZYX</th>\n",
       "      <th>GE_ZZEF1</th>\n",
       "      <th>GE_ZZZ3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DRUG</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Drug_1</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>0.7194</td>\n",
       "      <td>-1.440283</td>\n",
       "      <td>3.845564</td>\n",
       "      <td>-0.471557</td>\n",
       "      <td>-0.362765</td>\n",
       "      <td>0.244599</td>\n",
       "      <td>0.767670</td>\n",
       "      <td>-0.427922</td>\n",
       "      <td>0.113491</td>\n",
       "      <td>...</td>\n",
       "      <td>-2.137911</td>\n",
       "      <td>-1.840215</td>\n",
       "      <td>-0.528406</td>\n",
       "      <td>-0.547575</td>\n",
       "      <td>-0.893916</td>\n",
       "      <td>-1.020918</td>\n",
       "      <td>-0.914376</td>\n",
       "      <td>0.075175</td>\n",
       "      <td>-0.418602</td>\n",
       "      <td>-0.609965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_2</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>0.7905</td>\n",
       "      <td>-1.440283</td>\n",
       "      <td>3.845564</td>\n",
       "      <td>-0.471557</td>\n",
       "      <td>-0.362765</td>\n",
       "      <td>0.244599</td>\n",
       "      <td>0.767670</td>\n",
       "      <td>-0.427922</td>\n",
       "      <td>0.113491</td>\n",
       "      <td>...</td>\n",
       "      <td>-2.137911</td>\n",
       "      <td>-1.840215</td>\n",
       "      <td>-0.528406</td>\n",
       "      <td>-0.547575</td>\n",
       "      <td>-0.893916</td>\n",
       "      <td>-1.020918</td>\n",
       "      <td>-0.914376</td>\n",
       "      <td>0.075175</td>\n",
       "      <td>-0.418602</td>\n",
       "      <td>-0.609965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_3</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>0.4439</td>\n",
       "      <td>-1.440283</td>\n",
       "      <td>3.845564</td>\n",
       "      <td>-0.471557</td>\n",
       "      <td>-0.362765</td>\n",
       "      <td>0.244599</td>\n",
       "      <td>0.767670</td>\n",
       "      <td>-0.427922</td>\n",
       "      <td>0.113491</td>\n",
       "      <td>...</td>\n",
       "      <td>-2.137911</td>\n",
       "      <td>-1.840215</td>\n",
       "      <td>-0.528406</td>\n",
       "      <td>-0.547575</td>\n",
       "      <td>-0.893916</td>\n",
       "      <td>-1.020918</td>\n",
       "      <td>-0.914376</td>\n",
       "      <td>0.075175</td>\n",
       "      <td>-0.418602</td>\n",
       "      <td>-0.609965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_5</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>0.6256</td>\n",
       "      <td>-1.440283</td>\n",
       "      <td>3.845564</td>\n",
       "      <td>-0.471557</td>\n",
       "      <td>-0.362765</td>\n",
       "      <td>0.244599</td>\n",
       "      <td>0.767670</td>\n",
       "      <td>-0.427922</td>\n",
       "      <td>0.113491</td>\n",
       "      <td>...</td>\n",
       "      <td>-2.137911</td>\n",
       "      <td>-1.840215</td>\n",
       "      <td>-0.528406</td>\n",
       "      <td>-0.547575</td>\n",
       "      <td>-0.893916</td>\n",
       "      <td>-1.020918</td>\n",
       "      <td>-0.914376</td>\n",
       "      <td>0.075175</td>\n",
       "      <td>-0.418602</td>\n",
       "      <td>-0.609965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_6</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>0.7049</td>\n",
       "      <td>-1.440283</td>\n",
       "      <td>3.845564</td>\n",
       "      <td>-0.471557</td>\n",
       "      <td>-0.362765</td>\n",
       "      <td>0.244599</td>\n",
       "      <td>0.767670</td>\n",
       "      <td>-0.427922</td>\n",
       "      <td>0.113491</td>\n",
       "      <td>...</td>\n",
       "      <td>-2.137911</td>\n",
       "      <td>-1.840215</td>\n",
       "      <td>-0.528406</td>\n",
       "      <td>-0.547575</td>\n",
       "      <td>-0.893916</td>\n",
       "      <td>-1.020918</td>\n",
       "      <td>-0.914376</td>\n",
       "      <td>0.075175</td>\n",
       "      <td>-0.418602</td>\n",
       "      <td>-0.609965</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_654</td>\n",
       "      <td>CCL_994</td>\n",
       "      <td>0.9743</td>\n",
       "      <td>-1.366415</td>\n",
       "      <td>3.312423</td>\n",
       "      <td>-0.444228</td>\n",
       "      <td>-0.372810</td>\n",
       "      <td>-0.408307</td>\n",
       "      <td>-0.819443</td>\n",
       "      <td>-0.427922</td>\n",
       "      <td>-0.240693</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.997882</td>\n",
       "      <td>-0.839286</td>\n",
       "      <td>-0.153528</td>\n",
       "      <td>-0.127375</td>\n",
       "      <td>-0.200210</td>\n",
       "      <td>-1.012857</td>\n",
       "      <td>-0.097957</td>\n",
       "      <td>0.149436</td>\n",
       "      <td>0.195171</td>\n",
       "      <td>-0.761630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_655</td>\n",
       "      <td>CCL_994</td>\n",
       "      <td>0.8121</td>\n",
       "      <td>-1.366415</td>\n",
       "      <td>3.312423</td>\n",
       "      <td>-0.444228</td>\n",
       "      <td>-0.372810</td>\n",
       "      <td>-0.408307</td>\n",
       "      <td>-0.819443</td>\n",
       "      <td>-0.427922</td>\n",
       "      <td>-0.240693</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.997882</td>\n",
       "      <td>-0.839286</td>\n",
       "      <td>-0.153528</td>\n",
       "      <td>-0.127375</td>\n",
       "      <td>-0.200210</td>\n",
       "      <td>-1.012857</td>\n",
       "      <td>-0.097957</td>\n",
       "      <td>0.149436</td>\n",
       "      <td>0.195171</td>\n",
       "      <td>-0.761630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_656</td>\n",
       "      <td>CCL_994</td>\n",
       "      <td>0.9229</td>\n",
       "      <td>-1.366415</td>\n",
       "      <td>3.312423</td>\n",
       "      <td>-0.444228</td>\n",
       "      <td>-0.372810</td>\n",
       "      <td>-0.408307</td>\n",
       "      <td>-0.819443</td>\n",
       "      <td>-0.427922</td>\n",
       "      <td>-0.240693</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.997882</td>\n",
       "      <td>-0.839286</td>\n",
       "      <td>-0.153528</td>\n",
       "      <td>-0.127375</td>\n",
       "      <td>-0.200210</td>\n",
       "      <td>-1.012857</td>\n",
       "      <td>-0.097957</td>\n",
       "      <td>0.149436</td>\n",
       "      <td>0.195171</td>\n",
       "      <td>-0.761630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_657</td>\n",
       "      <td>CCL_994</td>\n",
       "      <td>0.8585</td>\n",
       "      <td>-1.366415</td>\n",
       "      <td>3.312423</td>\n",
       "      <td>-0.444228</td>\n",
       "      <td>-0.372810</td>\n",
       "      <td>-0.408307</td>\n",
       "      <td>-0.819443</td>\n",
       "      <td>-0.427922</td>\n",
       "      <td>-0.240693</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.997882</td>\n",
       "      <td>-0.839286</td>\n",
       "      <td>-0.153528</td>\n",
       "      <td>-0.127375</td>\n",
       "      <td>-0.200210</td>\n",
       "      <td>-1.012857</td>\n",
       "      <td>-0.097957</td>\n",
       "      <td>0.149436</td>\n",
       "      <td>0.195171</td>\n",
       "      <td>-0.761630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Drug_658</td>\n",
       "      <td>CCL_994</td>\n",
       "      <td>0.8369</td>\n",
       "      <td>-1.366415</td>\n",
       "      <td>3.312423</td>\n",
       "      <td>-0.444228</td>\n",
       "      <td>-0.372810</td>\n",
       "      <td>-0.408307</td>\n",
       "      <td>-0.819443</td>\n",
       "      <td>-0.427922</td>\n",
       "      <td>-0.240693</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.997882</td>\n",
       "      <td>-0.839286</td>\n",
       "      <td>-0.153528</td>\n",
       "      <td>-0.127375</td>\n",
       "      <td>-0.200210</td>\n",
       "      <td>-1.012857</td>\n",
       "      <td>-0.097957</td>\n",
       "      <td>0.149436</td>\n",
       "      <td>0.195171</td>\n",
       "      <td>-0.761630</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>412878 rows × 17745 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             CELL     AUC   GE_A1BG   GE_A1CF    GE_A2M  GE_A2ML1  GE_A3GALT2  \\\n",
       "DRUG                                                                            \n",
       "Drug_1    CCL_100  0.7194 -1.440283  3.845564 -0.471557 -0.362765    0.244599   \n",
       "Drug_2    CCL_100  0.7905 -1.440283  3.845564 -0.471557 -0.362765    0.244599   \n",
       "Drug_3    CCL_100  0.4439 -1.440283  3.845564 -0.471557 -0.362765    0.244599   \n",
       "Drug_5    CCL_100  0.6256 -1.440283  3.845564 -0.471557 -0.362765    0.244599   \n",
       "Drug_6    CCL_100  0.7049 -1.440283  3.845564 -0.471557 -0.362765    0.244599   \n",
       "...           ...     ...       ...       ...       ...       ...         ...   \n",
       "Drug_654  CCL_994  0.9743 -1.366415  3.312423 -0.444228 -0.372810   -0.408307   \n",
       "Drug_655  CCL_994  0.8121 -1.366415  3.312423 -0.444228 -0.372810   -0.408307   \n",
       "Drug_656  CCL_994  0.9229 -1.366415  3.312423 -0.444228 -0.372810   -0.408307   \n",
       "Drug_657  CCL_994  0.8585 -1.366415  3.312423 -0.444228 -0.372810   -0.408307   \n",
       "Drug_658  CCL_994  0.8369 -1.366415  3.312423 -0.444228 -0.372810   -0.408307   \n",
       "\n",
       "          GE_A4GALT  GE_A4GNT   GE_AAAS  ...  GE_ZWILCH  GE_ZWINT   GE_ZXDA  \\\n",
       "DRUG                                     ...                                  \n",
       "Drug_1     0.767670 -0.427922  0.113491  ...  -2.137911 -1.840215 -0.528406   \n",
       "Drug_2     0.767670 -0.427922  0.113491  ...  -2.137911 -1.840215 -0.528406   \n",
       "Drug_3     0.767670 -0.427922  0.113491  ...  -2.137911 -1.840215 -0.528406   \n",
       "Drug_5     0.767670 -0.427922  0.113491  ...  -2.137911 -1.840215 -0.528406   \n",
       "Drug_6     0.767670 -0.427922  0.113491  ...  -2.137911 -1.840215 -0.528406   \n",
       "...             ...       ...       ...  ...        ...       ...       ...   \n",
       "Drug_654  -0.819443 -0.427922 -0.240693  ...  -0.997882 -0.839286 -0.153528   \n",
       "Drug_655  -0.819443 -0.427922 -0.240693  ...  -0.997882 -0.839286 -0.153528   \n",
       "Drug_656  -0.819443 -0.427922 -0.240693  ...  -0.997882 -0.839286 -0.153528   \n",
       "Drug_657  -0.819443 -0.427922 -0.240693  ...  -0.997882 -0.839286 -0.153528   \n",
       "Drug_658  -0.819443 -0.427922 -0.240693  ...  -0.997882 -0.839286 -0.153528   \n",
       "\n",
       "           GE_ZXDB   GE_ZXDC  GE_ZYG11A  GE_ZYG11B    GE_ZYX  GE_ZZEF1  \\\n",
       "DRUG                                                                     \n",
       "Drug_1   -0.547575 -0.893916  -1.020918  -0.914376  0.075175 -0.418602   \n",
       "Drug_2   -0.547575 -0.893916  -1.020918  -0.914376  0.075175 -0.418602   \n",
       "Drug_3   -0.547575 -0.893916  -1.020918  -0.914376  0.075175 -0.418602   \n",
       "Drug_5   -0.547575 -0.893916  -1.020918  -0.914376  0.075175 -0.418602   \n",
       "Drug_6   -0.547575 -0.893916  -1.020918  -0.914376  0.075175 -0.418602   \n",
       "...            ...       ...        ...        ...       ...       ...   \n",
       "Drug_654 -0.127375 -0.200210  -1.012857  -0.097957  0.149436  0.195171   \n",
       "Drug_655 -0.127375 -0.200210  -1.012857  -0.097957  0.149436  0.195171   \n",
       "Drug_656 -0.127375 -0.200210  -1.012857  -0.097957  0.149436  0.195171   \n",
       "Drug_657 -0.127375 -0.200210  -1.012857  -0.097957  0.149436  0.195171   \n",
       "Drug_658 -0.127375 -0.200210  -1.012857  -0.097957  0.149436  0.195171   \n",
       "\n",
       "           GE_ZZZ3  \n",
       "DRUG                \n",
       "Drug_1   -0.609965  \n",
       "Drug_2   -0.609965  \n",
       "Drug_3   -0.609965  \n",
       "Drug_5   -0.609965  \n",
       "Drug_6   -0.609965  \n",
       "...            ...  \n",
       "Drug_654 -0.761630  \n",
       "Drug_655 -0.761630  \n",
       "Drug_656 -0.761630  \n",
       "Drug_657 -0.761630  \n",
       "Drug_658 -0.761630  \n",
       "\n",
       "[412878 rows x 17745 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1.set_index(['DRUG'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1min 21s, sys: 36.8 s, total: 1min 58s\n",
      "Wall time: 1min 19s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_2 = df_1.merge(df_descriptor, left_on='DRUG', right_on='ID', how='left', sort='true')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>CELL</th>\n",
       "      <th>DRUG</th>\n",
       "      <th>AUC</th>\n",
       "      <th>GE_A1BG</th>\n",
       "      <th>GE_A1CF</th>\n",
       "      <th>GE_A2M</th>\n",
       "      <th>GE_A2ML1</th>\n",
       "      <th>GE_A3GALT2</th>\n",
       "      <th>GE_A4GALT</th>\n",
       "      <th>GE_A4GNT</th>\n",
       "      <th>...</th>\n",
       "      <th>DD_SRW10</th>\n",
       "      <th>DD_TSRW10</th>\n",
       "      <th>DD_MW</th>\n",
       "      <th>DD_AMW</th>\n",
       "      <th>DD_WPath</th>\n",
       "      <th>DD_WPol</th>\n",
       "      <th>DD_Zagreb1</th>\n",
       "      <th>DD_Zagreb2</th>\n",
       "      <th>DD_mZagreb1</th>\n",
       "      <th>DD_mZagreb2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>CCL_100</td>\n",
       "      <td>Drug_1</td>\n",
       "      <td>0.7194</td>\n",
       "      <td>-1.440283</td>\n",
       "      <td>3.845564</td>\n",
       "      <td>-0.471557</td>\n",
       "      <td>-0.362765</td>\n",
       "      <td>0.244599</td>\n",
       "      <td>0.767670</td>\n",
       "      <td>-0.427922</td>\n",
       "      <td>...</td>\n",
       "      <td>0.573775</td>\n",
       "      <td>0.374900</td>\n",
       "      <td>-0.013642</td>\n",
       "      <td>-0.550884</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>-0.031402</td>\n",
       "      <td>0.219639</td>\n",
       "      <td>0.261155</td>\n",
       "      <td>-0.394765</td>\n",
       "      <td>0.054656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>CCL_1000</td>\n",
       "      <td>Drug_1</td>\n",
       "      <td>0.8588</td>\n",
       "      <td>0.831151</td>\n",
       "      <td>-0.339088</td>\n",
       "      <td>-0.395036</td>\n",
       "      <td>-0.181962</td>\n",
       "      <td>-0.408307</td>\n",
       "      <td>0.548000</td>\n",
       "      <td>0.579653</td>\n",
       "      <td>...</td>\n",
       "      <td>0.573775</td>\n",
       "      <td>0.374900</td>\n",
       "      <td>-0.013642</td>\n",
       "      <td>-0.550884</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>-0.031402</td>\n",
       "      <td>0.219639</td>\n",
       "      <td>0.261155</td>\n",
       "      <td>-0.394765</td>\n",
       "      <td>0.054656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CCL_1001</td>\n",
       "      <td>Drug_1</td>\n",
       "      <td>0.8150</td>\n",
       "      <td>-1.449517</td>\n",
       "      <td>2.467445</td>\n",
       "      <td>-0.482489</td>\n",
       "      <td>-0.292453</td>\n",
       "      <td>-0.408307</td>\n",
       "      <td>-0.264778</td>\n",
       "      <td>-0.427922</td>\n",
       "      <td>...</td>\n",
       "      <td>0.573775</td>\n",
       "      <td>0.374900</td>\n",
       "      <td>-0.013642</td>\n",
       "      <td>-0.550884</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>-0.031402</td>\n",
       "      <td>0.219639</td>\n",
       "      <td>0.261155</td>\n",
       "      <td>-0.394765</td>\n",
       "      <td>0.054656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>CCL_1002</td>\n",
       "      <td>Drug_1</td>\n",
       "      <td>0.7922</td>\n",
       "      <td>1.158940</td>\n",
       "      <td>-0.339088</td>\n",
       "      <td>-0.099885</td>\n",
       "      <td>-0.372810</td>\n",
       "      <td>0.353417</td>\n",
       "      <td>0.421690</td>\n",
       "      <td>0.475421</td>\n",
       "      <td>...</td>\n",
       "      <td>0.573775</td>\n",
       "      <td>0.374900</td>\n",
       "      <td>-0.013642</td>\n",
       "      <td>-0.550884</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>-0.031402</td>\n",
       "      <td>0.219639</td>\n",
       "      <td>0.261155</td>\n",
       "      <td>-0.394765</td>\n",
       "      <td>0.054656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>CCL_1004</td>\n",
       "      <td>Drug_1</td>\n",
       "      <td>0.8194</td>\n",
       "      <td>0.863469</td>\n",
       "      <td>-0.339088</td>\n",
       "      <td>-0.449694</td>\n",
       "      <td>-0.302497</td>\n",
       "      <td>0.244599</td>\n",
       "      <td>0.569967</td>\n",
       "      <td>-0.288946</td>\n",
       "      <td>...</td>\n",
       "      <td>0.573775</td>\n",
       "      <td>0.374900</td>\n",
       "      <td>-0.013642</td>\n",
       "      <td>-0.550884</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>-0.031402</td>\n",
       "      <td>0.219639</td>\n",
       "      <td>0.261155</td>\n",
       "      <td>-0.394765</td>\n",
       "      <td>0.054656</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>412873</td>\n",
       "      <td>CCL_88</td>\n",
       "      <td>Drug_999</td>\n",
       "      <td>0.9109</td>\n",
       "      <td>-0.216848</td>\n",
       "      <td>-0.087607</td>\n",
       "      <td>-0.526215</td>\n",
       "      <td>-0.332631</td>\n",
       "      <td>-0.408307</td>\n",
       "      <td>0.020793</td>\n",
       "      <td>0.822861</td>\n",
       "      <td>...</td>\n",
       "      <td>0.655956</td>\n",
       "      <td>0.901895</td>\n",
       "      <td>0.634924</td>\n",
       "      <td>0.838391</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>0.409001</td>\n",
       "      <td>0.679669</td>\n",
       "      <td>0.746909</td>\n",
       "      <td>-0.115743</td>\n",
       "      <td>0.431448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>412874</td>\n",
       "      <td>CCL_889</td>\n",
       "      <td>Drug_999</td>\n",
       "      <td>0.9730</td>\n",
       "      <td>0.992737</td>\n",
       "      <td>-0.308911</td>\n",
       "      <td>-0.373173</td>\n",
       "      <td>-0.252274</td>\n",
       "      <td>-0.408307</td>\n",
       "      <td>1.124633</td>\n",
       "      <td>-0.184714</td>\n",
       "      <td>...</td>\n",
       "      <td>0.655956</td>\n",
       "      <td>0.901895</td>\n",
       "      <td>0.634924</td>\n",
       "      <td>0.838391</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>0.409001</td>\n",
       "      <td>0.679669</td>\n",
       "      <td>0.746909</td>\n",
       "      <td>-0.115743</td>\n",
       "      <td>0.431448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>412875</td>\n",
       "      <td>CCL_93</td>\n",
       "      <td>Drug_999</td>\n",
       "      <td>0.9461</td>\n",
       "      <td>-1.149429</td>\n",
       "      <td>-0.278733</td>\n",
       "      <td>-0.449694</td>\n",
       "      <td>-0.312542</td>\n",
       "      <td>-0.408307</td>\n",
       "      <td>0.729228</td>\n",
       "      <td>4.783671</td>\n",
       "      <td>...</td>\n",
       "      <td>0.655956</td>\n",
       "      <td>0.901895</td>\n",
       "      <td>0.634924</td>\n",
       "      <td>0.838391</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>0.409001</td>\n",
       "      <td>0.679669</td>\n",
       "      <td>0.746909</td>\n",
       "      <td>-0.115743</td>\n",
       "      <td>0.431448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>412876</td>\n",
       "      <td>CCL_961</td>\n",
       "      <td>Drug_999</td>\n",
       "      <td>0.8778</td>\n",
       "      <td>0.969654</td>\n",
       "      <td>-0.329029</td>\n",
       "      <td>-0.438762</td>\n",
       "      <td>-0.372810</td>\n",
       "      <td>-0.408307</td>\n",
       "      <td>-1.330176</td>\n",
       "      <td>-0.010994</td>\n",
       "      <td>...</td>\n",
       "      <td>0.655956</td>\n",
       "      <td>0.901895</td>\n",
       "      <td>0.634924</td>\n",
       "      <td>0.838391</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>0.409001</td>\n",
       "      <td>0.679669</td>\n",
       "      <td>0.746909</td>\n",
       "      <td>-0.115743</td>\n",
       "      <td>0.431448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>412877</td>\n",
       "      <td>CCL_990</td>\n",
       "      <td>Drug_999</td>\n",
       "      <td>0.9301</td>\n",
       "      <td>-1.532618</td>\n",
       "      <td>1.290512</td>\n",
       "      <td>-0.471557</td>\n",
       "      <td>-0.372810</td>\n",
       "      <td>1.223958</td>\n",
       "      <td>-1.253291</td>\n",
       "      <td>-0.219458</td>\n",
       "      <td>...</td>\n",
       "      <td>0.655956</td>\n",
       "      <td>0.901895</td>\n",
       "      <td>0.634924</td>\n",
       "      <td>0.838391</td>\n",
       "      <td>-0.055783</td>\n",
       "      <td>0.409001</td>\n",
       "      <td>0.679669</td>\n",
       "      <td>0.746909</td>\n",
       "      <td>-0.115743</td>\n",
       "      <td>0.431448</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>412878 rows × 19359 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            CELL      DRUG     AUC   GE_A1BG   GE_A1CF    GE_A2M  GE_A2ML1  \\\n",
       "0        CCL_100    Drug_1  0.7194 -1.440283  3.845564 -0.471557 -0.362765   \n",
       "1       CCL_1000    Drug_1  0.8588  0.831151 -0.339088 -0.395036 -0.181962   \n",
       "2       CCL_1001    Drug_1  0.8150 -1.449517  2.467445 -0.482489 -0.292453   \n",
       "3       CCL_1002    Drug_1  0.7922  1.158940 -0.339088 -0.099885 -0.372810   \n",
       "4       CCL_1004    Drug_1  0.8194  0.863469 -0.339088 -0.449694 -0.302497   \n",
       "...          ...       ...     ...       ...       ...       ...       ...   \n",
       "412873    CCL_88  Drug_999  0.9109 -0.216848 -0.087607 -0.526215 -0.332631   \n",
       "412874   CCL_889  Drug_999  0.9730  0.992737 -0.308911 -0.373173 -0.252274   \n",
       "412875    CCL_93  Drug_999  0.9461 -1.149429 -0.278733 -0.449694 -0.312542   \n",
       "412876   CCL_961  Drug_999  0.8778  0.969654 -0.329029 -0.438762 -0.372810   \n",
       "412877   CCL_990  Drug_999  0.9301 -1.532618  1.290512 -0.471557 -0.372810   \n",
       "\n",
       "        GE_A3GALT2  GE_A4GALT  GE_A4GNT  ...  DD_SRW10  DD_TSRW10     DD_MW  \\\n",
       "0         0.244599   0.767670 -0.427922  ...  0.573775   0.374900 -0.013642   \n",
       "1        -0.408307   0.548000  0.579653  ...  0.573775   0.374900 -0.013642   \n",
       "2        -0.408307  -0.264778 -0.427922  ...  0.573775   0.374900 -0.013642   \n",
       "3         0.353417   0.421690  0.475421  ...  0.573775   0.374900 -0.013642   \n",
       "4         0.244599   0.569967 -0.288946  ...  0.573775   0.374900 -0.013642   \n",
       "...            ...        ...       ...  ...       ...        ...       ...   \n",
       "412873   -0.408307   0.020793  0.822861  ...  0.655956   0.901895  0.634924   \n",
       "412874   -0.408307   1.124633 -0.184714  ...  0.655956   0.901895  0.634924   \n",
       "412875   -0.408307   0.729228  4.783671  ...  0.655956   0.901895  0.634924   \n",
       "412876   -0.408307  -1.330176 -0.010994  ...  0.655956   0.901895  0.634924   \n",
       "412877    1.223958  -1.253291 -0.219458  ...  0.655956   0.901895  0.634924   \n",
       "\n",
       "          DD_AMW  DD_WPath   DD_WPol  DD_Zagreb1  DD_Zagreb2  DD_mZagreb1  \\\n",
       "0      -0.550884 -0.055783 -0.031402    0.219639    0.261155    -0.394765   \n",
       "1      -0.550884 -0.055783 -0.031402    0.219639    0.261155    -0.394765   \n",
       "2      -0.550884 -0.055783 -0.031402    0.219639    0.261155    -0.394765   \n",
       "3      -0.550884 -0.055783 -0.031402    0.219639    0.261155    -0.394765   \n",
       "4      -0.550884 -0.055783 -0.031402    0.219639    0.261155    -0.394765   \n",
       "...          ...       ...       ...         ...         ...          ...   \n",
       "412873  0.838391 -0.055783  0.409001    0.679669    0.746909    -0.115743   \n",
       "412874  0.838391 -0.055783  0.409001    0.679669    0.746909    -0.115743   \n",
       "412875  0.838391 -0.055783  0.409001    0.679669    0.746909    -0.115743   \n",
       "412876  0.838391 -0.055783  0.409001    0.679669    0.746909    -0.115743   \n",
       "412877  0.838391 -0.055783  0.409001    0.679669    0.746909    -0.115743   \n",
       "\n",
       "        DD_mZagreb2  \n",
       "0          0.054656  \n",
       "1          0.054656  \n",
       "2          0.054656  \n",
       "3          0.054656  \n",
       "4          0.054656  \n",
       "...             ...  \n",
       "412873     0.431448  \n",
       "412874     0.431448  \n",
       "412875     0.431448  \n",
       "412876     0.431448  \n",
       "412877     0.431448  \n",
       "\n",
       "[412878 rows x 19359 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2min 11s, sys: 3.12 s, total: 2min 14s\n",
      "Wall time: 2min 14s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_2.to_parquet('top21.parquet', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2min 50s, sys: 1min 52s, total: 4min 42s\n",
      "Wall time: 3min 40s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_2.to_hdf('top21.h5', key='df', mode='w')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "uniq cell labels: 703, profiles: 703\n",
      "uniq drug labels: 1492, profiles: 1492\n"
     ]
    }
   ],
   "source": [
    "# post analysis - count uniq cell/drug label/profiles\n",
    "cols = df_2.columns.to_list()\n",
    "cl_columns = list(filter(lambda x: x.startswith('GE_'), cols))\n",
    "dd_columns = list(filter(lambda x: x.startswith('DD_'), cols))\n",
    "\n",
    "df_cl_label = df_2['CELL'].drop_duplicates()\n",
    "df_cl_prof = df_2[cl_columns].drop_duplicates()\n",
    "print(f'uniq cell labels: {len(df_cl_label)}, profiles: {len(df_cl_prof)}')\n",
    "\n",
    "df_dd_label = df_2['DRUG'].drop_duplicates()\n",
    "df_dd_prof = df_2[dd_columns].drop_duplicates()\n",
    "print(f'uniq drug labels: {len(df_dd_label)}, profiles: {len(df_dd_prof)}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save cell line & drug labels\n",
    "df_cl_label.to_csv('top21_cell.txt', header=False, index=False)\n",
    "df_dd_label.to_csv('top21_drug.txt', header=False, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}