READ.cellprofiler_sc_sqlite.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading Single Cell profiles into the memory\n",
    "- All the information about single cells are stored in a sqlite file for each plate\n",
    "- sqlite files are huge (up to 50 GB) and loading them to memory may cause memory errors\n",
    "\n",
    "\n",
    "#### Here are alternative ways of handling this issue:\n",
    "\n",
    "- Reading All the Single Cells of a plate\n",
    "\n",
    "- Reading random images or defind subset of the plate images \n",
    "\n",
    "- Reading a subset of wells from the plate \n",
    "\n",
    "- Reading a subset of features from the plate \n",
    "\n",
    "- Reading a subset of features and a subset of wells of a plate \n",
    "   \n",
    "- Reading a subset of objects from a subset of wells plate    \n",
    "  \n",
    "  \n",
    "** Timing Example **\n",
    "\n",
    "* SQ00015195  :  11.55 GB\n",
    "   \n",
    "   \n",
    "- Reading All the Single Cells of a plate\n",
    "\n",
    "    - python sql reader\n",
    "\n",
    "            df_p_s=readSingleCellData_sqlalch(fileName,compartments);\n",
    "                CPU times: user 23min, sys: 2min 5s, total: 25min 6s\n",
    "                Wall time: 27min 35s\n",
    "\n",
    "    - R sql reader\n",
    "    \n",
    "            df_p_s=readSingleCellData_r(fileName);  \n",
    "            (Not usable since the changes in underlying packages can not be resolved)\n",
    "\n",
    "\n",
    "- Reading random images or defind subset of the plate images\n",
    "\n",
    "        df_p_s=readSingleCellData_sqlalch_random_image_subset(fileName,50);\n",
    "            CPU times: user 14.9 s, sys: 2.77 s, total: 17.6 s\n",
    "            Wall time: 7min 21s\n",
    "\n",
    "\n",
    "- Reading a subset of wells from the plate\n",
    "\n",
    "        df_p_s=readSingleCellData_sqlalch_well_subset(fileName,wells,\"Image_Metadata_Well\");\n",
    "            CPU times: user 7.08 s, sys: 2.89 s, total: 9.97 s\n",
    "            Wall time: 9min 41s\n",
    "            \n",
    "- Reading a subset of objects from a subset of wells plate\n",
    "\n",
    "        df_p_s=readSingleCellData_sqlalch_wellAndObject_subset(fileName,wells,\"Image_Metadata_Well\",50);\n",
    "            CPU times: user 5.46 s, sys: 2.01 s, total: 7.47 s\n",
    "            Wall time: 9min 20s\n",
    "\n",
    "- Reading a subset of features from the plate\n",
    "\n",
    "        df_p_s=readSingleCellData_sqlalch_features_subset(fileName,selected_features);\n",
    "            CPU times: user 6.32 s, sys: 4.77 s, total: 11.1 s\n",
    "            Wall time: 4min 47s\n",
    "        \n",
    "- Reading a subset of features and a subset of wells of a plate\n",
    "\n",
    "        df_p_s=readSingleCellData_sqlalch_FeatureAndWell_subset(fileName,selected_features,wells);\n",
    "            CPU times: user 1.39 s, sys: 1.26 s, total: 2.65 s\n",
    "            Wall time: 2min 36s   \n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "%matplotlib notebook\n",
    "import numpy as np\n",
    "import pandas as pd \n",
    "import time\n",
    "import sys, os\n",
    "# from utils import read_data, visualize_data\n",
    "from utils.read_data import *\n",
    "from utils.visualize_data import *\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "from sqlalchemy import create_engine\n",
    "from functools import reduce\n",
    "import time\n",
    "from scipy.stats import pearsonr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example dataset:\n",
    "#     drug rep\n",
    "# meta_lincs=pd.read_csv(\"/home/ubuntu/bucket/projects/2018_04_20_Rosetta/workspace/results/synth_meta/meta_lincs_repLevel.csv\")\n",
    "rootDirDrug='/home/ubuntu/bucket/projects/2015_10_05_DrugRepurposing_AravindSubramanian_GolubLab_Broad/workspace'\n",
    "batchName='2016_04_01_a549_48hr_batch1'\n",
    "p,wells=\"SQ00015195\",[\"A13\"]\n",
    "fileName=rootDirDrug+\"/backend/\"+batchName+\"/\"+p+\"/\"+p+\".sqlite\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Check file size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQ00015195  :  11.553037312\n"
     ]
    }
   ],
   "source": [
    "sqlFileSizGB=os.stat(fileName).st_size/10e8\n",
    "print(p,' : ',sqlFileSizGB)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading All the Single Cells of a plate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 23min, sys: 2min 5s, total: 25min 6s\n",
      "Wall time: 27min 35s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# python sql reader\n",
    "compartments=[\"cells\", \"cytoplasm\", \"nuclei\"]\n",
    "# compartments=[\"Neurites\",\"CellBodies\",\"CellBodiesPlusNeurites\",\"Nuclei\",\"Cytoplasm\"]\n",
    "\n",
    "df_p_s=readSingleCellData_sqlalch(fileName,compartments);\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "# python sql reader\n",
    "compartments=[\"cells\", \"cytoplasm\", \"nuclei\"]\n",
    "# compartments=[\"Neurites\",\"CellBodies\",\"CellBodiesPlusNeurites\",\"Nuclei\",\"Cytoplasm\"]\n",
    "# R sql reader\n",
    "df_p_s=readSingleCellData_r(fileName);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading random images or defind subset of the plate images "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 14.9 s, sys: 2.77 s, total: 17.6 s\n",
      "Wall time: 7min 21s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_p_s=readSingleCellData_sqlalch_random_image_subset(fileName,50);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading a subset of wells from the plate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 7.08 s, sys: 2.89 s, total: 9.97 s\n",
      "Wall time: 9min 41s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_p_s=readSingleCellData_sqlalch_well_subset(fileName,wells,\"Image_Metadata_Well\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading a subset of objects from a subset of wells plate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time elapsed: 2.2087584575017294\n",
      "(9, 658) [109, 110, 111, 112, 113, 114, 115, 116, 117]\n",
      "time elapsed: 7.124696934223175\n",
      "CPU times: user 5.46 s, sys: 2.01 s, total: 7.47 s\n",
      "Wall time: 9min 20s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_p_s=readSingleCellData_sqlalch_wellAndObject_subset(fileName,wells,\"Image_Metadata_Well\",50);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading a subset of features from the plate "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time elapsed: 4.785013918081919  mins\n",
      "CPU times: user 6.32 s, sys: 4.77 s, total: 11.1 s\n",
      "Wall time: 4min 47s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "selected_features='Cells_Intensity_IntegratedIntensity_DNA'\n",
    "df_p_s=readSingleCellData_sqlalch_features_subset(fileName,selected_features);\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading a subset of features and a subset of wells of a plate "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time elapsed: 2.6094359000523886  mins\n",
      "CPU times: user 1.39 s, sys: 1.26 s, total: 2.65 s\n",
      "Wall time: 2min 36s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "selected_features='Cells_Intensity_IntegratedIntensity_DNA'\n",
    "wells=[\"A13\"]\n",
    "\n",
    "p,wells=\"SQ00015199\", ['P20']\n",
    "fileName=rootDirDrug+\"/backend/\"+batchName+\"/\"+p+\"/\"+p+\".sqlite\"\n",
    "df_p_s=readSingleCellData_sqlalch_FeatureAndWell_subset(fileName,selected_features,wells);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_p_s.columns.duplicated()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}