10.read-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read raw screen data from spreadsheets and reformat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.0.3'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas\n",
    "pandas.__version__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read validated hit compounds\n",
    "\n",
    "> Of these 72 potential hit compounds, 59 were validated in a secondary screen for dose‐dependency (Appendix Fig S1), with 46 chemicals lengthening and 13 compounds shortening circadian period"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>cas-number</th>\n",
       "      <th>drug_name</th>\n",
       "      <th>type_of_medicine</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>522-51-0</td>\n",
       "      <td>DEQUALINIUM CHLORIDE</td>\n",
       "      <td>DISINFECTANTS, ANTISEPTICS</td>\n",
       "      <td>E3 ubiquitin-protein ligase XIAP\\ncGMP-gated c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>633-65-8</td>\n",
       "      <td>BERBERINE CHLORIDE</td>\n",
       "      <td>GASTROINTESTINAL</td>\n",
       "      <td>Baculoviral IAP repeat-containing protein 5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  cas-number             drug_name            type_of_medicine  \\\n",
       "0   522-51-0  DEQUALINIUM CHLORIDE  DISINFECTANTS, ANTISEPTICS   \n",
       "1   633-65-8    BERBERINE CHLORIDE            GASTROINTESTINAL   \n",
       "\n",
       "                                              target  \n",
       "0  E3 ubiquitin-protein ligase XIAP\\ncGMP-gated c...  \n",
       "1        Baculoviral IAP repeat-containing protein 5  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hit_df = pandas.read_excel(\n",
    "    io=\"data/tamai-screen/emmm201708724-sup-0003-tableev1.xlsx\",\n",
    "    skiprows=1,\n",
    ")\n",
    "hit_df.columns = hit_df.columns.str.lower().str.replace(\" \", \"_\")\n",
    "hit_df = hit_df.dropna(subset=[\"drug_name\"])\n",
    "hit_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "56"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(hit_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read FDA compounds\n",
    "\n",
    "> The SCREENWELL FDA-approved drug library V2 containing 741 compounds was purchased from Enzo Life Sciences (Hayashi Kasei Co., Ltd.), and the International Drug Collection (IDC) containing 311 compounds was purchased from MicroSource Discovery Systems, Inc. (Namiki Shoji Co., Ltd.)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def undo_mangle_dupe_cols(df):\n",
    "    \"\"\"\n",
    "    undo mangle_dupe_cols=True in pandas.read_excel\n",
    "    Removes \".1\" suffix.\n",
    "    \"\"\"\n",
    "    strip_suffix = lambda x: x[:-2] if x.endswith(\".1\") else x\n",
    "    df.columns = df.columns.map(strip_suffix)\n",
    "    return df\n",
    "\n",
    "\n",
    "def read_fda_spreadsheet(concentration):\n",
    "    concentration_to_cols = {\"1 uM\": \"B:H\", \"10 uM\": \"J:P\"}\n",
    "    renamer = {\n",
    "        f\"Period\": \"period_before\",\n",
    "        f\"{concentration} AVG\": \"period_after\",\n",
    "        \"Period Change\": \"period_change\",\n",
    "        \"HITS/non-HITS\": \"compound_name\",\n",
    "        \"Plate\": \"plate\",\n",
    "    }\n",
    "    df = (\n",
    "        pandas.read_excel(\n",
    "            io=\"data/tamai-screen/FDA1_FDA2_Period_24-120.xlsx\",\n",
    "            sheet_name=\"FDA_1uM_10uM_DRUGS_GRAPHS\",\n",
    "            usecols=f\"{concentration_to_cols[concentration]},T\",\n",
    "            mangle_dupe_cols=True,\n",
    "        )\n",
    "        .pipe(undo_mangle_dupe_cols)\n",
    "        .rename(columns=renamer)\n",
    "        .rename(columns=lambda x: \"notes\" if x.startswith(\"Unnamed:\") else x)\n",
    "        # .eval(\"dead = 0 + (notes == 'dead')\")\n",
    "        .assign(concentration = concentration)\n",
    "        .dropna(subset=[\"period_after\", \"compound_name\"])\n",
    "        .assign(compound_set=\"fda\")\n",
    "        .convert_dtypes()\n",
    "    )\n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>96-well</th>\n",
       "      <th>plate</th>\n",
       "      <th>384-well</th>\n",
       "      <th>period_before</th>\n",
       "      <th>period_after</th>\n",
       "      <th>period_change</th>\n",
       "      <th>notes</th>\n",
       "      <th>compound_name</th>\n",
       "      <th>concentration</th>\n",
       "      <th>compound_set</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-A02</td>\n",
       "      <td>1</td>\n",
       "      <td>A4</td>\n",
       "      <td>21.5</td>\n",
       "      <td>21.633333</td>\n",
       "      <td>0.029167</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>Clindamycin·HCl</td>\n",
       "      <td>1 uM</td>\n",
       "      <td>fda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1-A03</td>\n",
       "      <td>1</td>\n",
       "      <td>A7</td>\n",
       "      <td>21.7</td>\n",
       "      <td>21.666667</td>\n",
       "      <td>0.062500</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>Felbamate</td>\n",
       "      <td>1 uM</td>\n",
       "      <td>fda</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  96-well  plate 384-well  period_before  period_after  period_change notes  \\\n",
       "0   1-A02      1       A4           21.5     21.633333       0.029167  <NA>   \n",
       "3   1-A03      1       A7           21.7     21.666667       0.062500  <NA>   \n",
       "\n",
       "     compound_name concentration compound_set  \n",
       "0  Clindamycin·HCl          1 uM          fda  \n",
       "3        Felbamate          1 uM          fda  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fda_df = pandas.concat([\n",
    "    read_fda_spreadsheet(\"1 uM\"),\n",
    "    read_fda_spreadsheet(\"10 uM\"),\n",
    "])\n",
    "fda_df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read IDC compounds\n",
    "\n",
    "> the International Drug Collection (IDC) containing 311\n",
    "compounds was purchased from MicroSource Discovery Systems, Inc.\n",
    "(Namiki Shoji Co., Ltd.)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_idc_spreadsheet(concentration):\n",
    "    concentration_to_cols = {\"1 uM\": \"A:F\", \"10 uM\": \"G:L\"}\n",
    "    renamer = {\n",
    "        \"period\": \"period_before\",\n",
    "        \"AVG\": \"period_after\",\n",
    "        f\"IDC {concentration}\": \"period_change\",\n",
    "        \"Name\": \"compound_name\",\n",
    "    }\n",
    "    df = (\n",
    "        pandas.read_excel(\n",
    "            io=\"data/tamai-screen/IDC_Period_24-120.xlsx\",\n",
    "            sheet_name=\"1uM_10uM_Period_Graphs\",\n",
    "            usecols=f\"{concentration_to_cols[concentration]},P\",\n",
    "            mangle_dupe_cols=True,\n",
    "        )\n",
    "        .pipe(undo_mangle_dupe_cols)\n",
    "        .rename(columns=renamer)\n",
    "        .rename(columns=lambda x: \"notes\" if x.startswith(\"Unnamed:\") else x)\n",
    "        .assign(concentration = concentration)\n",
    "        .dropna(subset=[\"period_after\"])\n",
    "        .assign(compound_set=\"idc\")\n",
    "        .convert_dtypes()\n",
    "    )\n",
    "    return df"
   ]
  },
  {
   "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>plate</th>\n",
       "      <th>well</th>\n",
       "      <th>period_before</th>\n",
       "      <th>period_after</th>\n",
       "      <th>period_change</th>\n",
       "      <th>notes</th>\n",
       "      <th>compound_name</th>\n",
       "      <th>concentration</th>\n",
       "      <th>compound_set</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A4</td>\n",
       "      <td>21.9</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.01875</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>SULFACARBAMIDE</td>\n",
       "      <td>1 uM</td>\n",
       "      <td>idc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>A7</td>\n",
       "      <td>21.7</td>\n",
       "      <td>21.9</td>\n",
       "      <td>-0.08125</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>GALLIC ACID</td>\n",
       "      <td>1 uM</td>\n",
       "      <td>idc</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   plate well  period_before  period_after  period_change notes  \\\n",
       "0      1   A4           21.9          22.0        0.01875  <NA>   \n",
       "3      1   A7           21.7          21.9       -0.08125  <NA>   \n",
       "\n",
       "    compound_name concentration compound_set  \n",
       "0  SULFACARBAMIDE          1 uM          idc  \n",
       "3     GALLIC ACID          1 uM          idc  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idc_df = pandas.concat([\n",
    "    read_idc_spreadsheet(\"1 uM\"),\n",
    "    read_idc_spreadsheet(\"10 uM\"),\n",
    "])\n",
    "idc_df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine compound sets"
   ]
  },
  {
   "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>compound_name</th>\n",
       "      <th>compound_set</th>\n",
       "      <th>concentration</th>\n",
       "      <th>period_before</th>\n",
       "      <th>period_change</th>\n",
       "      <th>plate</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Clindamycin·HCl</td>\n",
       "      <td>fda</td>\n",
       "      <td>1 uM</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.029167</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Felbamate</td>\n",
       "      <td>fda</td>\n",
       "      <td>1 uM</td>\n",
       "      <td>21.7</td>\n",
       "      <td>0.062500</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cyclosporine A</td>\n",
       "      <td>fda</td>\n",
       "      <td>1 uM</td>\n",
       "      <td>21.6</td>\n",
       "      <td>0.095833</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Donepezil·HCl</td>\n",
       "      <td>fda</td>\n",
       "      <td>1 uM</td>\n",
       "      <td>21.4</td>\n",
       "      <td>-0.170833</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Lincomycin·HCl</td>\n",
       "      <td>fda</td>\n",
       "      <td>1 uM</td>\n",
       "      <td>21.5</td>\n",
       "      <td>-0.004167</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>945</th>\n",
       "      <td>METICRANE</td>\n",
       "      <td>idc</td>\n",
       "      <td>10 uM</td>\n",
       "      <td>21.5</td>\n",
       "      <td>-0.183333</td>\n",
       "      <td>6</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>948</th>\n",
       "      <td>MEPARFYLON</td>\n",
       "      <td>idc</td>\n",
       "      <td>10 uM</td>\n",
       "      <td>21.4</td>\n",
       "      <td>-0.116667</td>\n",
       "      <td>6</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>951</th>\n",
       "      <td>PICONOL</td>\n",
       "      <td>idc</td>\n",
       "      <td>10 uM</td>\n",
       "      <td>21.6</td>\n",
       "      <td>0.016667</td>\n",
       "      <td>6</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>954</th>\n",
       "      <td>MENBUTONE</td>\n",
       "      <td>idc</td>\n",
       "      <td>10 uM</td>\n",
       "      <td>21.6</td>\n",
       "      <td>0.016667</td>\n",
       "      <td>6</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>957</th>\n",
       "      <td>HYDROXYPROGESTERONE</td>\n",
       "      <td>idc</td>\n",
       "      <td>10 uM</td>\n",
       "      <td>24.2</td>\n",
       "      <td>26.316667</td>\n",
       "      <td>6</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2240 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           compound_name compound_set concentration  period_before  \\\n",
       "0        Clindamycin·HCl          fda          1 uM           21.5   \n",
       "3              Felbamate          fda          1 uM           21.7   \n",
       "6         Cyclosporine A          fda          1 uM           21.6   \n",
       "9          Donepezil·HCl          fda          1 uM           21.4   \n",
       "12        Lincomycin·HCl          fda          1 uM           21.5   \n",
       "..                   ...          ...           ...            ...   \n",
       "945            METICRANE          idc         10 uM           21.5   \n",
       "948           MEPARFYLON          idc         10 uM           21.4   \n",
       "951              PICONOL          idc         10 uM           21.6   \n",
       "954            MENBUTONE          idc         10 uM           21.6   \n",
       "957  HYDROXYPROGESTERONE          idc         10 uM           24.2   \n",
       "\n",
       "     period_change  plate notes  \n",
       "0         0.029167      1  <NA>  \n",
       "3         0.062500      1  <NA>  \n",
       "6         0.095833      1  <NA>  \n",
       "9        -0.170833      1  <NA>  \n",
       "12       -0.004167      1  <NA>  \n",
       "..             ...    ...   ...  \n",
       "945      -0.183333      6  <NA>  \n",
       "948      -0.116667      6  <NA>  \n",
       "951       0.016667      6  <NA>  \n",
       "954       0.016667      6  <NA>  \n",
       "957      26.316667      6  <NA>  \n",
       "\n",
       "[2240 rows x 7 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "screen_df = (\n",
    "    pandas.concat([fda_df, idc_df])\n",
    "    .convert_dtypes()\n",
    "    .reindex(columns=[\n",
    "        \"compound_name\",\n",
    "        \"compound_set\",\n",
    "        \"concentration\",\n",
    "        \"period_before\",\n",
    "        \"period_change\",\n",
    "        \"plate\",\n",
    "        \"notes\",\n",
    "    ])\n",
    ")\n",
    "screen_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "screen_df.to_csv(\"data/tamai-screen/combined-results.tsv\", sep=\"\\t\", index=False, float_format=\"%.4g\")"
   ]
  }
 ],
 "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.8.2"
  },
  "mimetype": "text/x-python",
  "name": "python",
  "npconvert_exporter": "python",
  "pygments_lexer": "ipython3",
  "version": 3
 },
 "nbformat": 4,
 "nbformat_minor": 4
}