data-cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning and preprocessing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here I ensure that the names for drug resistance and gene expression match for each of the different datasets. Only those Drugs for which I can find data on both will be used."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CCL name cleaning GDSC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdsc_dr = pd.read_csv('data/GDSC/metrics.csv').rename(columns={'Cell_line':'CCL'})\n",
    "gdsc_ge = pd.read_csv('data/GDSC/ge.csv', sep='\\t').rename(columns={'Unnamed: 0':'CCL'}).transpose()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean(s):\n",
    "    new = []\n",
    "    for i in s:\n",
    "        new.append(re.sub(\"[^A-Za-z0-9]\", '', i).upper())\n",
    "    return new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "poz_r = list(gdsc_dr['CCL'].unique())\n",
    "cell_r = list(gdsc_ge.index.to_series().reset_index()[0])\n",
    "\n",
    "cell = clean(cell_r)\n",
    "poz = clean(poz_r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['786O', 'COLO320', 'EOL1', 'KNS81', 'NCIH510', 'U266B1', 'UO31']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "miss1 = []\n",
    "for ccl in poz:\n",
    "    if ccl not in cell:\n",
    "        miss1.append(ccl)\n",
    "\n",
    "miss1.remove('TTTHYROID')\n",
    "print(len(miss1))\n",
    "miss1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "poz2 = {k:re.sub(\"[^A-Za-z0-9]\", '', k).upper() for k in poz_r if re.sub(\"[^A-Za-z0-9]\", '', k).upper() not in miss1}\n",
    "\n",
    "miss1_sub = ['7860', 'COLO320HSR','EOL1CELL', 'KNS81FD', 'NCIH510A' , 'U266', 'U031']\n",
    "\n",
    "for i, ele in enumerate(miss1):\n",
    "    poz2[ele] = miss1_sub[i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdsc_dr['CCL'] = gdsc_dr['CCL'].replace(poz2)\n",
    "gdsc_dr = gdsc_dr[gdsc_dr['CCL']!='TTTHYROID']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdsc_dr.to_csv('data/Processed/gdsc_poz_dr.csv', index =False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdsc_ge = gdsc_ge.reset_index()\n",
    "gdsc_ge.columns = gdsc_ge.iloc[0]\n",
    "gdsc_ge = gdsc_ge[1:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "cell_sub = {k:re.sub(\"[^A-Za-z0-9]\", '', k).upper() for k in gdsc_ge['CCL']}\n",
    "gdsc_ge['CCL'] = gdsc_ge['CCL'].replace(cell_sub)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "drop = ['index']\n",
    "for i in gdsc_ge['CCL']:\n",
    "    if i not in gdsc_dr['CCL'].unique():\n",
    "        drop.append(i)\n",
    "gdsc_ge = gdsc_ge[~gdsc_ge['CCL'].isin(drop)].reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A1BG</th>\n",
       "      <th>A1CF</th>\n",
       "      <th>A2M</th>\n",
       "      <th>A2ML1</th>\n",
       "      <th>A3GALT2</th>\n",
       "      <th>A3GALT2P</th>\n",
       "      <th>A4GALT</th>\n",
       "      <th>A4GNT</th>\n",
       "      <th>AAAS</th>\n",
       "      <th>AACS</th>\n",
       "      <th>...</th>\n",
       "      <th>ZW10</th>\n",
       "      <th>ZWILCH</th>\n",
       "      <th>ZWINT</th>\n",
       "      <th>ZXDB</th>\n",
       "      <th>ZXDC</th>\n",
       "      <th>ZYG11A</th>\n",
       "      <th>ZYG11B</th>\n",
       "      <th>ZYX</th>\n",
       "      <th>ZZEF1</th>\n",
       "      <th>ZZZ3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CCL</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",
       "      <th>KMH2</th>\n",
       "      <td>5.57091</td>\n",
       "      <td>2.9072</td>\n",
       "      <td>3.82674</td>\n",
       "      <td>2.81857</td>\n",
       "      <td>3.24614</td>\n",
       "      <td>3.24614</td>\n",
       "      <td>3.16653</td>\n",
       "      <td>3.35596</td>\n",
       "      <td>5.29611</td>\n",
       "      <td>3.98259</td>\n",
       "      <td>...</td>\n",
       "      <td>7.44839</td>\n",
       "      <td>8.26429</td>\n",
       "      <td>9.63864</td>\n",
       "      <td>4.21265</td>\n",
       "      <td>4.64859</td>\n",
       "      <td>4.58405</td>\n",
       "      <td>4.62811</td>\n",
       "      <td>5.06164</td>\n",
       "      <td>4.7705</td>\n",
       "      <td>8.02462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KMH2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 19562 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "0        A1BG    A1CF      A2M    A2ML1  A3GALT2 A3GALT2P   A4GALT    A4GNT  \\\n",
       "CCL                                                                           \n",
       "KMH2  5.57091  2.9072  3.82674  2.81857  3.24614  3.24614  3.16653  3.35596   \n",
       "KMH2      NaN     NaN      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "\n",
       "0        AAAS     AACS  ...     ZW10   ZWILCH    ZWINT     ZXDB     ZXDC  \\\n",
       "CCL                     ...                                                \n",
       "KMH2  5.29611  3.98259  ...  7.44839  8.26429  9.63864  4.21265  4.64859   \n",
       "KMH2      NaN      NaN  ...      NaN      NaN      NaN      NaN      NaN   \n",
       "\n",
       "0      ZYG11A   ZYG11B      ZYX   ZZEF1     ZZZ3  \n",
       "CCL                                               \n",
       "KMH2  4.58405  4.62811  5.06164  4.7705  8.02462  \n",
       "KMH2      NaN      NaN      NaN     NaN      NaN  \n",
       "\n",
       "[2 rows x 19562 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdsc_ge.set_index('CCL').drop('index', axis=1).loc['KMH2']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdsc_ge = gdsc_ge.drop_duplicates('CCL', keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdsc_ge.drop('index', axis=1).to_csv('data/Processed/gdsc_cell_ge.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CCL name cleaning CCLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "ccle_ge = pd.read_csv('data/CCLE/ge.csv', sep='\\t').rename(columns={'Unnamed: 0':'CCL'}).transpose()\n",
    "ccle_dr = pd.read_csv('data/CCLE/metrics.csv').rename(columns={'Cell_line':'CCL'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "poz = list(ccle_dr['CCL'].unique())\n",
    "cell = list(ccle_ge.index.to_series().str.upper().reset_index()[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "poz = clean(poz)\n",
    "cell = clean(cell)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['BGC823',\n",
       " 'GLC82',\n",
       " 'KP1N',\n",
       " 'KP1NL',\n",
       " 'M059J',\n",
       " 'MB157',\n",
       " 'OVCAR3',\n",
       " 'SF8657',\n",
       " 'SNUC2B']"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "miss2 = []\n",
    "for ccl in poz:\n",
    "    if ccl not in cell:\n",
    "        miss2.append(ccl)\n",
    "miss2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "ccle_dr_2 = ccle_dr[~ccle_dr['CCL'].isin(['BGC823', 'GLC82', 'KP1N', 'KP1NL', 'M059J', 'SF8657', 'SNUC2B', 'MB157'])]\n",
    "ccle_dr_2.to_csv('data/Processed/ccle_poz_dr.csv', index =False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "ccle_ge = ccle_ge.reset_index()\n",
    "ccle_ge.columns = ccle_ge.iloc[0]\n",
    "ccle_ge = ccle_ge[1:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "tags": [
     "outputPrepend",
     "outputPrepend",
     "outputPrepend",
     "outputPrepend",
     "outputPrepend",
     "outputPrepend",
     "outputPrepend",
     "outputPrepend"
    ]
   },
   "outputs": [],
   "source": [
    "cell_sub = {k:re.sub(\"[^A-Za-z0-9]\", '', k).upper() for k in ccle_ge['CCL'].unique()}\n",
    "cell_sub['NIH:OVCAR-3'] = 'OVCAR3'\n",
    "ccle_ge['CCL'] = ccle_ge['CCL'].replace(cell_sub)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "drop = ['index']\n",
    "for i in ccle_ge['CCL']:\n",
    "    if i not in ccle_dr['CCL'].unique():\n",
    "        drop.append(i)\n",
    "ccle_ge = ccle_ge[~ccle_ge['CCL'].isin(drop)].reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "ccle_ge = ccle_ge.drop('index', axis=1)\n",
    "ccle_ge.to_csv('data/Processed/ccle_cell_ge.csv', index =False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CCL name cleaning CTRP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctrp_ge = pd.read_csv('data/CTRP/cell_ge.csv', sep='\\t').rename(columns={'Unnamed: 0':'CCL'}).transpose()\n",
    "ctrp_dr = pd.read_csv('data/CTRP/metrics.csv').rename(columns={'Cell_line':'CCL'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "\n",
    "poz_r = list(ctrp_dr['CCL'].unique())\n",
    "poz = clean(poz_r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "49"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "miss = []\n",
    "for ccl in poz:\n",
    "    if ccl not in cell:\n",
    "        miss.append(ccl)\n",
    "len(miss)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2004',\n",
       " 'ASKA',\n",
       " 'BT112',\n",
       " 'BT131',\n",
       " 'BT139',\n",
       " 'BT145',\n",
       " 'BT147',\n",
       " 'BT159',\n",
       " 'BT16',\n",
       " 'BT164',\n",
       " 'BT172',\n",
       " 'BT179',\n",
       " 'BT216',\n",
       " 'BT224',\n",
       " 'BT228',\n",
       " 'BT231',\n",
       " 'BT232',\n",
       " 'BT239',\n",
       " 'BT245',\n",
       " 'BT248',\n",
       " 'BT271',\n",
       " 'BT286',\n",
       " 'BT320',\n",
       " 'BT328',\n",
       " 'BT330',\n",
       " 'BT333',\n",
       " 'BT340',\n",
       " 'BT359',\n",
       " 'BT416',\n",
       " 'BT422',\n",
       " 'BT428',\n",
       " 'BT440',\n",
       " 'BT444',\n",
       " 'BT482',\n",
       " 'BT498',\n",
       " 'BT504',\n",
       " 'H292',\n",
       " 'HS578BST',\n",
       " 'HS888LU',\n",
       " 'HSTS',\n",
       " 'KRIJ',\n",
       " 'PSTS',\n",
       " 'SW982',\n",
       " 'SYO1',\n",
       " 'TM8716',\n",
       " 'TTTHYROID',\n",
       " 'UCH1',\n",
       " 'YAMATO',\n",
       " 'BT187']"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "miss"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will drop all CCL's of the series BT and some others not found on cellminercdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "drop = [v for v in miss if v[:2]=='BT']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "drop.extend(['2004', 'ASKA', 'HSTS', 'KRIJ', 'PSTS', 'HS578BST', 'SW982', 'SYO1', 'TM8716', 'UCH1', 'YAMATO'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "poz2 = {k:re.sub(\"[^A-Za-z0-9]\", '', k) for k in poz_r if re.sub(\"[^A-Za-z0-9]\", '', k).upper() not in miss}\n",
    "\n",
    "miss1_sub = ['NCIH292', 'HS888T', 'TT']\n",
    "\n",
    "miss = [v for v in miss if v not in drop]\n",
    "for i, ele in enumerate(miss):\n",
    "    poz2[ele] = miss1_sub[i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctrp_dr = ctrp_dr[~ctrp_dr['CCL'].isin(drop)]\n",
    "ctrp_dr['CCL'] = ctrp_dr['CCL'].replace(poz2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctrp_dr = ctrp_dr[ctrp_dr['CCL'].isin(ccle_ge['CCL'])]\n",
    "ctrp_dr.to_csv('data/Processed/ctrp_poz_dr.csv', index =False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctrp_ge = ccle_ge\n",
    "drop = ['index']\n",
    "for i in ctrp_ge['CCL']:\n",
    "    if i not in ctrp_dr['CCL'].unique():\n",
    "        drop.append(i)\n",
    "ctrp_ge = ctrp_ge[~ctrp_ge['CCL'].isin(drop)].reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(449, 19852)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ctrp_ge = ctrp_ge.drop(['index'], axis=1)\n",
    "ctrp_ge.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctrp_ge.to_csv('data/Processed/ctrp_cell_ge.csv', index =False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "file_extension": ".py",
  "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.3"
  },
  "mimetype": "text/x-python",
  "name": "python",
  "npconvert_exporter": "python",
  "pygments_lexer": "ipython3",
  "version": 3
 },
 "nbformat": 4,
 "nbformat_minor": 4
}