FTU-workflow.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "213248b9",
   "metadata": {},
   "source": [
    "### Workflow\n",
    "\n",
    "Figma diagram: https://www.figma.com/file/PFKdJO3HTIcM9g4nmVONCT/HRA-Data-Explorer?type=whiteboard&node-id=1%3A108&t=XGoN91s7IKbyZ7xk-1\n",
    "\n",
    "Below is the information about the diagram:\n",
    "\n",
    "1. Dataset Collections:\n",
    "The process begins with RUI Registered Tissue datasets (553), a collection of tissue datasets registered in a specific repository or database.\n",
    "These datasets are linked to specific Organs and Anatomical Structures.\n",
    "\n",
    "2. Dataset Processing:\n",
    "The datasets are processed to produce Anatomical Structures/Cell type information of 2D FTUs. - 193\n",
    "There is a decision point or a step to possibly sort these structures, which leads to the extraction of a Cell Summary, including cell type biomarker gene expression data that maps to the FTU specific cell types.\n",
    "    \n",
    "    1. Compare FTU's cell type information between Azimuth, PopV and Celltypist. (Azimuth > celltypist > popv)\n",
    "    2. Get the list of dataset for FTU.\n",
    "        1. Cell summary from the atlas-as-cell-summaries.jsonld\n",
    "        2. Add organ data from the cell annotation crosswalk files\n",
    "    3. Any dataset that is been shared among two FTUs\n",
    "        1. Remove \n",
    "\n",
    "3. Cell Type Mapping:\n",
    "list of 2D FTUs are used to map Cell Types, leading to a set of inquiries on how to best extract data from the datasets used in Step 1 and 2\n",
    "\n",
    "4. Vasculature Mapping\n",
    "\n",
    "5. cell type info:\n",
    "   1. Exclusively within FTU:\n",
    "      | FTU                       | Cell Types                                                                                      |\n",
    "        |---------------------------|--------------------------------------------------------------------------------------------------|\n",
    "        | Cortical Collecting Duct  | Kidney collecting duct beta-intercalated cell, Kidney collecting duct alpha-intercalated cell, Kidney cortex collecting duct principal cell |\n",
    "        | Outer Medullary Collecting Duct | Kidney outer medulla collecting duct intercalated cell, Kidney outer medulla collecting duct principal cell |\n",
    "        | Inner Medullary Collecting Duct | Kidney inner medulla collecting duct principal cell                                          |\n",
    "        | Thick Ascending Limb      | Kidney loop of Henle thick ascending limb epithelial cell                                        |\n",
    "        | Descending Thin Limb      | Kidney loop of Henle thin descending limb epithelial cell                                        |\n",
    "        | Ascending Thin Limb       | Kidney loop of Henle thin ascending limb epithelial cell                                         |\n",
    "        | Nephron                   | Cortical collecting duct, Proximal straight tubule, Proximal convoluted tubule segment 2, Proximal convoluted tubule segment 1, Epithelial cell of proximal tubule segment 1, Distal convoluted tubule, Kidney distal convoluted tubule epithelial cell, Renal connecting tubule, Kidney connecting tubule epithelial cell, Macula densa epithelial cell, Thick ascending limb of loop of Henle, Loop of Henle ascending limb thin segment, Descending thin limb, Kidney inner medulla collecting duct, Renal Corpuscle, Kidney loop of Henle thin ascending limb epithelial cell, Kidney loop of Henle thin descending limb epithelial cell, Kidney loop of Henle thick ascending limb epithelial cell, Kidney cortex collecting duct principal cell, Kidney outer medulla collecting duct principal cell |\n",
    "        | Renal Corpuscle           | Glomerular mesangial cell, Glomerular visceral epithelial cell, Epithelial cell of proximal tubule, Glomerular capillary endothelial cell, Efferent arteriole endothelial cell, Parietal epithelial cell, Afferent arteriole endothelial cell, Macula densa epithelial cell |\n",
    "        | Crypt Of Lieberkuhn       | Large intestine crypt goblet cell, Enterocyte of epithelium of large intestine, Neuroendocrine cell, Intestinal crypt stem cell of large intestine, Intestinal tuft cell |\n",
    "        | Liver Lobule              | Hepatic oval stem cell, Endothelial cell of hepatic sinusoid, Hepatic stellate cell, Kupffer Cell, Hepatocyte |\n",
    "        | Bronchial Submucosal Gland | Airway submucosal gland duct ciliated cell, Airway submucosal gland duct basal cell, Mucus secreting cell of bronchus submucosal gland, Airway submucosal gland collecting duct epithelial cell, Serous secreting cell of bronchus submucosal gland, Myoepithelial cell of bronchus submucosal gland |\n",
    "        | Pulmonary Alveolus        | Type II pneumocyte, Type I pneumocyte, Alveolar type 1 fibroblast cell, Secondary crest myofibroblast |\n",
    "        | Intercalated Duct         | Epithelial cell of exocrine pancreas, Pancreatic centro-acinar cell, Intercalated duct of pancreas |\n",
    "        | Acinus                    | Pancreatic acinar cell, Pancreatic goblet cell, Epithelial cell of exocrine pancreas, Pancreatic centro-acinar cell, Intercalated duct of pancreas |\n",
    "        | Islet of Langerhans       | Pancreatic A cell, Type B pancreatic cell, Pancreatic D cell, Pancreatic PP cell |\n",
    "        | Prostate Glandular Acinus | Luminal cell of prostate epithelium, Basal cell of prostate epithelium |\n",
    "        | Dermal Papilla            | Skin fibroblast |\n",
    "        | Epidermal Ridge           | Prickle cell, Epidermal Langerhans cell, Basal cell of epidermis, Merkel cell, Melanocyte of skin |\n",
    "        | Villus                    | Tuft cell of small intestine, Enterocyte, Duodenal goblet cell |\n",
    "        | Red Pulp                  | CD14-positive, CD16-low monocyte, Follicular dendritic cell, Plasmablast, Neutrophil, Splenic tingible body macrophage, Splenic marginal zone macrophage, Splenic red pulp macrophage, Dendritic cell, human, Hematopoietic stem cell, Reticular cell of splenic sinusoid |\n",
    "        | White Pulp                | Splenic white pulp macrophage, Smooth muscle cell, Splenic endothelial cell, Stromal cell, Reticular cell of splenic cord, CD14-positive, CD16-low monocyte, Follicular dendritic cell, Plasmablast, Neutrophil |\n",
    "        | Thymus Lobule             | Double-positive, alpha-beta thymocyte, Thymus subcapsular epithelium, Thymic plasmacytoid dendritic cell, Thymic cortical macrophage, Subcapsular cortical epithelial cell, Beige adipocyte, Medullary thymic epithelial cell type 1, Hassall's corpuscle, Corticomedullary thymic epithelial cell, Thymic medullary macrophage |\n",
    "\n",
    "   2. Shared between FTUs\n",
    "   \n",
    "      | Cell Type                               | FTU 1                          | FTU 2                          |\n",
    "      |-----------------------------------------|--------------------------------|--------------------------------|\n",
    "        | Peritubular capillary endothelial cell  | Cortical Collecting Duct       | Outer Medullary Collecting Duct |\n",
    "        | Peritubular capillary endothelial cell  | Inner Medullary Collecting Duct | Thick Ascending Limb           |\n",
    "        | Macula densa epithelial cell            | Nephron                        | Renal Corpuscle                |\n",
    "        | Epithelial cell of exocrine pancreas    | Intercalated Duct              | Acinus                         |\n",
    "        | Pancreatic centro-acinar cell           | Intercalated Duct              | Acinus                         |\n",
    "        | Intercalated duct of pancreas           | Intercalated Duct              | Acinus                         |\n",
    "        | CD14-positive, CD16-low monocyte        | Red Pulp                       | White Pulp                     |\n",
    "        | Follicular dendritic cell               | Red Pulp                       | White Pulp                     |\n",
    "        | Plasmablast                             | Red Pulp                       | White Pulp                     |\n",
    "        | Neutrophil                              | Red Pulp                       | White Pulp                     |\n",
    "\n",
    "   \n",
    "\n",
    "   3. Shared between FTU and surrounding tissues:\n",
    "      | FTU                       | Cell Types                                                                                     |\n",
    "        |---------------------------|-------------------------------------------------------------------------------------------------------|\n",
    "        | Cortical Collecting Duct  | Peritubular capillary endothelial cell                                                                |\n",
    "        | Outer Medullary Collecting Duct | Peritubular capillary endothelial cell                                                          |\n",
    "        | Inner Medullary Collecting Duct | Peritubular capillary endothelial cell                                                          |\n",
    "        | Thick Ascending Limb      | Peritubular capillary endothelial cell                                                                |\n",
    "        | Descending Thin Limb      | Vasa recta descending limb cell                                                                       |\n",
    "        | Ascending Thin Limb       | Vasa recta ascending limb cell                                                                        |\n",
    "        | Liver Lobule              | Hepatic portal fibroblast, Blood vessel smooth muscle cell, Endothelial cell of artery, Stratified cuboidal epithelial cell, Columnar/cuboidal epithelial cell, Vein endothelial cell |\n",
    "        | Pulmonary Alveolus        | Lung pericyte, Alveolar capillary type 1 endothelial cell                                             |\n",
    "        | Islet of Langerhans       | Capillary endothelial cell, Macrophage                                                                |\n",
    "        | Prostate Glandular Acinus | Fibroblast of subepithelial connective tissue of prostatic gland, Smooth muscle cell of prostate, Fibroblast of connective tissue of prostate, Prostate gland microvascular endothelial cell, Vascular associated smooth muscle cell, Pericyte |\n",
    "        | Dermal Papilla            | Endothelial cell                                                                                      |\n",
    "        | Villus                    | Natural killer cell, Fibroblast, Mast cell, Myofibroblast, Plasma cell, Dendritic cell, B cell, Eosinophil, Innate lymphoid cell, Endocrine cell, Neutrophil |\n",
    "        \n",
    "        \n",
    "Source section:  JSON LD file:\n",
    "\n",
    "1. JSON LD file will have information about the AS volume, cell annotation tool information, demographics and sex infromation "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0147567f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "import json\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from sklearn.manifold import TSNE\n",
    "import numpy as np\n",
    "import plotly.graph_objects as go"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12725dc9",
   "metadata": {},
   "source": [
    "### Load Reference files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2868fb05",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Local paths to save the downloaded files\n",
    "popv_local_path = 'ref_data/popv.csv'\n",
    "celltypist_local_path = 'ref_data/celltypist.csv'\n",
    "azimuth_local_path = 'ref_data/azimuth.csv'\n",
    "sankey_local_path = 'ref_data/sankey.csv'\n",
    "ftu_cell_count_path = 'ref_data/FTU Cell Count Table - Cell_Type_Count.csv'\n",
    "\n",
    "# Load the files\n",
    "popv = pd.read_csv(popv_local_path)\n",
    "celltypist = pd.read_csv(celltypist_local_path)\n",
    "azimuth = pd.read_csv(azimuth_local_path)\n",
    "sankey = pd.read_csv(sankey_local_path)\n",
    "ftu_cell_count = pd.read_csv(ftu_cell_count_path)\n",
    "\n",
    "# Load the JSON file\n",
    "atlas_enriched_dataset_graph_path = 'ref_data/atlas-enriched-dataset-graph.jsonld'\n",
    "atlas_as_cell_summaries_path = 'ref_data/atlas-as-cell-summaries.jsonld'\n",
    "\n",
    "with open(atlas_as_cell_summaries_path, 'r') as file:\n",
    "    json_data_summary = json.load(file)\n",
    "\n",
    "with open(atlas_enriched_dataset_graph_path, 'r') as file:\n",
    "    json_data_enriched = json.load(file)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0c450b3",
   "metadata": {},
   "source": [
    "### Get the Cell annotation tool information for cell types in FTUs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1dcafd84",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the mapping dictionary\n",
    "organ_to_uberon = {\n",
    "    'Kidney': 'http://purl.obolibrary.org/obo/UBERON_0002113',\n",
    "    'Lung': 'http://purl.obolibrary.org/obo/UBERON_0002048',\n",
    "    'Pancreas': 'http://purl.obolibrary.org/obo/UBERON_0001264',\n",
    "    'Liver': 'http://purl.obolibrary.org/obo/UBERON_0002107',\n",
    "    'Skin': 'http://purl.obolibrary.org/obo/UBERON_0002097',\n",
    "    'Small Intestine': 'http://purl.obolibrary.org/obo/UBERON_0002108',\n",
    "    'Prostate': 'http://purl.obolibrary.org/obo/UBERON_0002367',\n",
    "    'Thymus': 'http://purl.obolibrary.org/obo/UBERON_0002370',\n",
    "    'Spleen': 'http://purl.obolibrary.org/obo/UBERON_0002106',\n",
    "    'Large Intestine': 'http://purl.obolibrary.org/obo/UBERON_0000059'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "2c6e289e-f7cc-4249-b514-8b662b052654",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array(['UBERON:0000178', 'UBERON:0004537', 'UBERON:0002371',\n",
       "        'UBERON:0000970', 'UBERON:0000948', 'UBERON:0000059',\n",
       "        'UBERON:0002107', 'UBERON:0002048', 'UBERON:0000029',\n",
       "        'UBERON:0000079', 'UBERON:0001911', 'UBERON:0002509',\n",
       "        'UBERON:0001264', 'UBERON:0002367', 'UBERON:0001004',\n",
       "        'UBERON:0002097', 'UBERON:0002108', 'UBERON:0002106',\n",
       "        'UBERON:0002370', 'UBERON:0003126', 'UBERON:0001255',\n",
       "        'UBERON:0000995'], dtype=object),\n",
       " array(['UBERON:0000178', 'UBERON:0002371', 'UBERON:0000948',\n",
       "        'UBERON:0002421', 'UBERON:0000160', 'UBERON:0002113',\n",
       "        'UBERON:0002107', 'UBERON:0002048', 'UBERON:0000029',\n",
       "        'UBERON:0001264', 'UBERON:0001134', 'UBERON:0002106',\n",
       "        'UBERON:0002097'], dtype=object),\n",
       " array(['UBERON:0000948', 'UBERON:0002113', 'UBERON:0002048',\n",
       "        'UBERON:0002107', 'UBERON:0000178', 'UBERON:0001264',\n",
       "        'UBERON:0002371', 'UBERON:0001347', 'UBERON:0002373'], dtype=object))"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "popv['Organ_ID'].unique(), celltypist['Organ_ID'].unique(), azimuth['Organ_ID'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "54f80184-7dd2-4a06-9d73-5bdd21b8c860",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "({'UBERON:0001264',\n",
       "  'UBERON:0002048',\n",
       "  'UBERON:0002097',\n",
       "  'UBERON:0002106',\n",
       "  'UBERON:0002107',\n",
       "  'UBERON:0002108',\n",
       "  'UBERON:0002367',\n",
       "  'UBERON:0002370',\n",
       "  'UBERON:0002371'},\n",
       " {'UBERON:0001264',\n",
       "  'UBERON:0002048',\n",
       "  'UBERON:0002097',\n",
       "  'UBERON:0002106',\n",
       "  'UBERON:0002107',\n",
       "  'UBERON:0002113',\n",
       "  'UBERON:0002371'},\n",
       " {'UBERON:0001264',\n",
       "  'UBERON:0002048',\n",
       "  'UBERON:0002107',\n",
       "  'UBERON:0002113',\n",
       "  'UBERON:0002371'})"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract the UBERON IDs from the organ_to_uberon dictionary\n",
    "organ_to_uberon_ids = set([v.split('/')[-1].replace('_', ':') for v in organ_to_uberon.values()])\n",
    "\n",
    "# Extract the unique Organ_IDs from popv, celltypist, and azimuth\n",
    "popv_ids = set(popv['Organ_ID'].unique())\n",
    "\n",
    "celltypist_ids = set(celltypist['Organ_ID'].unique())\n",
    "\n",
    "azimuth_ids = set(azimuth['Organ_ID'].unique())\n",
    "\n",
    "# Find common organ IDs between organ_to_uberon and the different datasets\n",
    "common_popv = organ_to_uberon_ids.intersection(popv_ids)\n",
    "common_celltypist = organ_to_uberon_ids.intersection(celltypist_ids)\n",
    "common_azimuth = organ_to_uberon_ids.intersection(azimuth_ids)\n",
    "\n",
    "common_popv, common_celltypist, common_azimuth\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "069e7c05-31b6-49ae-926b-5e6a721129da",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract unique Organ_Level and Organ_ID from each file\n",
    "popv_unique = popv[['Organ_Level', 'Organ_ID']].drop_duplicates()\n",
    "celltypist_unique = celltypist[['Organ_Level', 'Organ_ID']].drop_duplicates()\n",
    "azimuth_unique = azimuth[['Organ_Level', 'Organ_ID']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "7476a6e8-a4d3-4806-a96a-65c30fdb0121",
   "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>Organ_Level</th>\n",
       "      <th>Organ_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>blood</td>\n",
       "      <td>UBERON:0000178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>blood vasculature</td>\n",
       "      <td>UBERON:0004537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>bone marrow</td>\n",
       "      <td>UBERON:0002371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>eye</td>\n",
       "      <td>UBERON:0000970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>heart</td>\n",
       "      <td>UBERON:0000948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>large intestine</td>\n",
       "      <td>UBERON:0000059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>liver</td>\n",
       "      <td>UBERON:0002107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>lung</td>\n",
       "      <td>UBERON:0002048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>lymph node</td>\n",
       "      <td>UBERON:0000029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>male reproductive system</td>\n",
       "      <td>UBERON:0000079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>mammary gland</td>\n",
       "      <td>UBERON:0001911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>mesenteric lymph node</td>\n",
       "      <td>UBERON:0002509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>pancreas</td>\n",
       "      <td>UBERON:0001264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>prostate gland</td>\n",
       "      <td>UBERON:0002367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>respiratory system</td>\n",
       "      <td>UBERON:0001004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277</th>\n",
       "      <td>skin</td>\n",
       "      <td>UBERON:0002097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>small intestine</td>\n",
       "      <td>UBERON:0002108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>314</th>\n",
       "      <td>spleen</td>\n",
       "      <td>UBERON:0002106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336</th>\n",
       "      <td>thymus</td>\n",
       "      <td>UBERON:0002370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>trachea</td>\n",
       "      <td>UBERON:0003126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>375</th>\n",
       "      <td>urinary bladder</td>\n",
       "      <td>UBERON:0001255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>389</th>\n",
       "      <td>uterus</td>\n",
       "      <td>UBERON:0000995</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Organ_Level        Organ_ID\n",
       "0                       blood  UBERON:0000178\n",
       "18          blood vasculature  UBERON:0004537\n",
       "32                bone marrow  UBERON:0002371\n",
       "46                        eye  UBERON:0000970\n",
       "73                      heart  UBERON:0000948\n",
       "79            large intestine  UBERON:0000059\n",
       "95                      liver  UBERON:0002107\n",
       "107                      lung  UBERON:0002048\n",
       "143                lymph node  UBERON:0000029\n",
       "165  male reproductive system  UBERON:0000079\n",
       "178             mammary gland  UBERON:0001911\n",
       "192     mesenteric lymph node  UBERON:0002509\n",
       "214                  pancreas  UBERON:0001264\n",
       "228            prostate gland  UBERON:0002367\n",
       "241        respiratory system  UBERON:0001004\n",
       "277                      skin  UBERON:0002097\n",
       "298           small intestine  UBERON:0002108\n",
       "314                    spleen  UBERON:0002106\n",
       "336                    thymus  UBERON:0002370\n",
       "357                   trachea  UBERON:0003126\n",
       "375           urinary bladder  UBERON:0001255\n",
       "389                    uterus  UBERON:0000995"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "popv_unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "e9d3c2f8-813f-4d27-b8a9-11a6409c2afb",
   "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>Organ_Level</th>\n",
       "      <th>Organ_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>blood_L1</td>\n",
       "      <td>UBERON:0000178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>bone marrow_L1</td>\n",
       "      <td>UBERON:0002371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>heart_L1</td>\n",
       "      <td>UBERON:0000948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>hippocampus_L1</td>\n",
       "      <td>UBERON:0002421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>intestine_L1</td>\n",
       "      <td>UBERON:0000160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>kidney_L1</td>\n",
       "      <td>UBERON:0002113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>liver_L1</td>\n",
       "      <td>UBERON:0002107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>lung_L1</td>\n",
       "      <td>UBERON:0002048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297</th>\n",
       "      <td>lymph node_L1</td>\n",
       "      <td>UBERON:0000029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>pancreas_L1</td>\n",
       "      <td>UBERON:0001264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343</th>\n",
       "      <td>skeletal muscle_L1</td>\n",
       "      <td>UBERON:0001134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365</th>\n",
       "      <td>spleen_L1</td>\n",
       "      <td>UBERON:0002106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td>Adult_Human_Skin_pkl</td>\n",
       "      <td>UBERON:0002097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>433</th>\n",
       "      <td>Healthy_Human_Liver_pkl</td>\n",
       "      <td>UBERON:0002107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450</th>\n",
       "      <td>Adult_Human_PancreaticIslet_pkl</td>\n",
       "      <td>UBERON:0001264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>462</th>\n",
       "      <td>Human_Lung_Atlas_pkl</td>\n",
       "      <td>UBERON:0002048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523</th>\n",
       "      <td>Healthy_Adult_Heart_pkl</td>\n",
       "      <td>UBERON:0000948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>598</th>\n",
       "      <td>Human_AdultAged_Hippocampus_pkl</td>\n",
       "      <td>UBERON:0002421</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Organ_Level        Organ_ID\n",
       "0                           blood_L1  UBERON:0000178\n",
       "27                    bone marrow_L1  UBERON:0002371\n",
       "72                          heart_L1  UBERON:0000948\n",
       "98                    hippocampus_L1  UBERON:0002421\n",
       "134                     intestine_L1  UBERON:0000160\n",
       "178                        kidney_L1  UBERON:0002113\n",
       "219                         liver_L1  UBERON:0002107\n",
       "248                          lung_L1  UBERON:0002048\n",
       "297                    lymph node_L1  UBERON:0000029\n",
       "329                      pancreas_L1  UBERON:0001264\n",
       "343               skeletal muscle_L1  UBERON:0001134\n",
       "365                        spleen_L1  UBERON:0002106\n",
       "399             Adult_Human_Skin_pkl  UBERON:0002097\n",
       "433          Healthy_Human_Liver_pkl  UBERON:0002107\n",
       "450  Adult_Human_PancreaticIslet_pkl  UBERON:0001264\n",
       "462             Human_Lung_Atlas_pkl  UBERON:0002048\n",
       "523          Healthy_Adult_Heart_pkl  UBERON:0000948\n",
       "598  Human_AdultAged_Hippocampus_pkl  UBERON:0002421"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "celltypist_unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "b8692305-8fa4-4a75-95aa-5823c1c4438d",
   "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>Organ_Level</th>\n",
       "      <th>Organ_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Heart_L2</td>\n",
       "      <td>UBERON:0000948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Kidney_L3</td>\n",
       "      <td>UBERON:0002113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Lung_v2_finest_level</td>\n",
       "      <td>UBERON:0002048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>Liver_L2</td>\n",
       "      <td>UBERON:0002107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>Liver_L1</td>\n",
       "      <td>UBERON:0002107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>Heart_L1</td>\n",
       "      <td>UBERON:0000948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>Kidney_L1</td>\n",
       "      <td>UBERON:0002113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>Human_PBMC_L3</td>\n",
       "      <td>UBERON:0000178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>Human_PBMC_L1</td>\n",
       "      <td>UBERON:0000178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>Kidney_L2</td>\n",
       "      <td>UBERON:0002113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>Lung_v2_L1</td>\n",
       "      <td>UBERON:0002048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>302</th>\n",
       "      <td>Lung_v2_L2</td>\n",
       "      <td>UBERON:0002048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312</th>\n",
       "      <td>Lung_v2_L3</td>\n",
       "      <td>UBERON:0002048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336</th>\n",
       "      <td>Lung_v2_L4</td>\n",
       "      <td>UBERON:0002048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>373</th>\n",
       "      <td>Lung_v2_L5</td>\n",
       "      <td>UBERON:0002048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>387</th>\n",
       "      <td>Pancreas_L1</td>\n",
       "      <td>UBERON:0001264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>400</th>\n",
       "      <td>Human_PBMC_L2</td>\n",
       "      <td>UBERON:0000178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>430</th>\n",
       "      <td>Bone_marrow_L1</td>\n",
       "      <td>UBERON:0002371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>438</th>\n",
       "      <td>Bone_marrow_L2</td>\n",
       "      <td>UBERON:0002371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>478</th>\n",
       "      <td>Adipose_L1</td>\n",
       "      <td>UBERON:0001347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>494</th>\n",
       "      <td>Adipose_L2</td>\n",
       "      <td>UBERON:0001347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>539</th>\n",
       "      <td>Tonsil_v2_L2</td>\n",
       "      <td>UBERON:0002373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>656</th>\n",
       "      <td>Tonsil_v2_L1</td>\n",
       "      <td>UBERON:0002373</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Organ_Level        Organ_ID\n",
       "0                Heart_L2  UBERON:0000948\n",
       "21              Kidney_L3  UBERON:0002113\n",
       "70   Lung_v2_finest_level  UBERON:0002048\n",
       "128              Liver_L2  UBERON:0002107\n",
       "151              Liver_L1  UBERON:0002107\n",
       "159              Heart_L1  UBERON:0000948\n",
       "172             Kidney_L1  UBERON:0002113\n",
       "188         Human_PBMC_L3  UBERON:0000178\n",
       "245         Human_PBMC_L1  UBERON:0000178\n",
       "252             Kidney_L2  UBERON:0002113\n",
       "298            Lung_v2_L1  UBERON:0002048\n",
       "302            Lung_v2_L2  UBERON:0002048\n",
       "312            Lung_v2_L3  UBERON:0002048\n",
       "336            Lung_v2_L4  UBERON:0002048\n",
       "373            Lung_v2_L5  UBERON:0002048\n",
       "387           Pancreas_L1  UBERON:0001264\n",
       "400         Human_PBMC_L2  UBERON:0000178\n",
       "430        Bone_marrow_L1  UBERON:0002371\n",
       "438        Bone_marrow_L2  UBERON:0002371\n",
       "478            Adipose_L1  UBERON:0001347\n",
       "494            Adipose_L2  UBERON:0001347\n",
       "539          Tonsil_v2_L2  UBERON:0002373\n",
       "656          Tonsil_v2_L1  UBERON:0002373"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "azimuth_unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "87df8ae0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Organ                    FTU Label in Uberon FTU ID in Uberon       CL_ID  \\\n",
      "0  Kidney               Cortical Collecting Duct   UBERON:0004203  CL:1000715   \n",
      "1  Kidney               Cortical Collecting Duct   UBERON:0004203  CL:4030015   \n",
      "2  Kidney        Outer Medullary Collecting Duct   UBERON:0004204  CL:1000717   \n",
      "3  Kidney  Thick Ascending Limb Of Loop Of Henle   UBERON:0001291  CL:1001033   \n",
      "4  Kidney       Descending Limb of Loop of Henle   UBERON:0001289  CL:1001285   \n",
      "\n",
      "                                            CT Label  azimuth  celltypist  \\\n",
      "0    kidney cortex collecting duct intercalated cell        1           0   \n",
      "1     kidney collecting duct alpha-intercalated cell        0           1   \n",
      "2  kidney outer medulla collecting duct intercala...        1           0   \n",
      "3             peritubular capillary endothelial cell        1           1   \n",
      "4                    vasa recta descending limb cell        1           1   \n",
      "\n",
      "   popv  \n",
      "0     0  \n",
      "1     0  \n",
      "2     0  \n",
      "3     0  \n",
      "4     0  \n"
     ]
    }
   ],
   "source": [
    "# Rename 'CT ID in CL' to 'CL_ID' for consistency\n",
    "ftu_cell_count = ftu_cell_count.rename(columns={'CT ID in CL': 'CL_ID'})\n",
    "ftu_cell_count = ftu_cell_count.rename(columns={'CT Label in CL': 'CL_Label_FTU'})\n",
    "\n",
    "# Filter FTU cell count to include only rows where `CT ID in CL` contains `CL`\n",
    "ftu_cell_count_filtered = ftu_cell_count[ftu_cell_count['CL_ID'].str.contains('CL', na=False)]\n",
    "\n",
    "# Select necessary columns from ftu_cell_count\n",
    "ftu_cell_count_filtered = ftu_cell_count_filtered[['Organ', 'FTU Label in Uberon', 'FTU ID in Uberon', 'CL_ID', 'CL_Label_FTU']]\n",
    "\n",
    "# Merge the dataframes based on 'CL_ID'\n",
    "merged_df = ftu_cell_count_filtered.merge(azimuth[['CL_ID', 'CL_Label']], on='CL_ID', how='left', suffixes=('', '_azimuth'))\n",
    "merged_df = merged_df.rename(columns={'CL_Label': 'CL_Label_azimuth'})\n",
    "\n",
    "merged_df = merged_df.merge(celltypist[['CL_ID', 'CL_Label']], on='CL_ID', how='left', suffixes=('', '_celltypist'))\n",
    "merged_df = merged_df.rename(columns={'CL_Label': 'CL_Label_celltypist'})\n",
    "\n",
    "merged_df = merged_df.merge(popv[['CL_ID', 'CL_Label']], on='CL_ID', how='left', suffixes=('', '_popv'))\n",
    "merged_df = merged_df.rename(columns={'CL_Label': 'CL_Label_popv'})\n",
    "\n",
    "# Populating the columns azimuth, celltypist, popv with 1 if there is a match, otherwise 0\n",
    "merged_df['azimuth'] = merged_df['CL_Label_azimuth'].notna().astype(int)\n",
    "merged_df['celltypist'] = merged_df['CL_Label_celltypist'].notna().astype(int)\n",
    "merged_df['popv'] = merged_df['CL_Label_popv'].notna().astype(int)\n",
    "\n",
    "# Renaming 'CT Label in CL' to 'CT Label'\n",
    "merged_df = merged_df.rename(columns={'CL_Label_FTU': 'CT Label'})\n",
    "\n",
    "# Selecting columns for final output\n",
    "final_columns = ['Organ', 'FTU Label in Uberon', 'FTU ID in Uberon', 'CL_ID', 'CT Label', 'azimuth', 'celltypist', 'popv']\n",
    "ftu_ct_ann_info_df = merged_df[final_columns]\n",
    "\n",
    "# Removing any duplicates\n",
    "ftu_ct_ann_info_az_df = ftu_ct_ann_info_df.drop_duplicates( \n",
    "  subset = ['CL_ID', 'azimuth'],keep = 'last').reset_index(drop = True)\n",
    "\n",
    "ftu_ct_ann_info_ct_df = ftu_ct_ann_info_df.drop_duplicates( \n",
    "  subset = ['CL_ID', 'celltypist'],keep = 'last').reset_index(drop = True)\n",
    "\n",
    "ftu_ct_ann_info_pv_df = ftu_ct_ann_info_df.drop_duplicates( \n",
    "  subset = ['CL_ID', 'popv'],keep = 'last').reset_index(drop = True)\n",
    "\n",
    "# Final creation of dataframe\n",
    "ftu_ct_ann_info_df = ftu_ct_ann_info_pv_df\n",
    "\n",
    "# Save the final dataframe to a CSV file\n",
    "output_path = 'output/FTU-CT-AnnTool-info.csv'\n",
    "ftu_ct_ann_info_pv_df.to_csv(output_path, index=False)\n",
    "\n",
    "# Display the first few rows of the final dataframe\n",
    "print(ftu_ct_ann_info_pv_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "6c48006a",
   "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>Organ</th>\n",
       "      <th>FTU Label in Uberon</th>\n",
       "      <th>FTU ID in Uberon</th>\n",
       "      <th>CL_ID</th>\n",
       "      <th>CL_Label_FTU</th>\n",
       "      <th>popv</th>\n",
       "      <th>celltypist_L1</th>\n",
       "      <th>celltypist_pkl</th>\n",
       "      <th>azimuth_L4</th>\n",
       "      <th>azimuth_L2</th>\n",
       "      <th>azimuth_finest</th>\n",
       "      <th>azimuth_L3</th>\n",
       "      <th>azimuth_L5</th>\n",
       "      <th>azimuth_L1</th>\n",
       "      <th>azimuth_level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kidney</td>\n",
       "      <td>Cortical Collecting Duct</td>\n",
       "      <td>UBERON:0004203</td>\n",
       "      <td>CL:1000715</td>\n",
       "      <td>kidney cortex collecting duct intercalated cell</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kidney</td>\n",
       "      <td>Cortical Collecting Duct</td>\n",
       "      <td>UBERON:0004203</td>\n",
       "      <td>CL:4030015</td>\n",
       "      <td>kidney collecting duct alpha-intercalated cell</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Kidney</td>\n",
       "      <td>Cortical Collecting Duct</td>\n",
       "      <td>UBERON:0004203</td>\n",
       "      <td>CL:1001033</td>\n",
       "      <td>peritubular capillary endothelial cell</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Kidney</td>\n",
       "      <td>Cortical Collecting Duct</td>\n",
       "      <td>UBERON:0004203</td>\n",
       "      <td>CL:1000714</td>\n",
       "      <td>kidney cortex collecting duct principal cell</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kidney</td>\n",
       "      <td>Outer Medullary Collecting Duct</td>\n",
       "      <td>UBERON:0004204</td>\n",
       "      <td>CL:1000717</td>\n",
       "      <td>kidney outer medulla collecting duct intercala...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Organ              FTU Label in Uberon FTU ID in Uberon       CL_ID  \\\n",
       "0  Kidney         Cortical Collecting Duct   UBERON:0004203  CL:1000715   \n",
       "1  Kidney         Cortical Collecting Duct   UBERON:0004203  CL:4030015   \n",
       "2  Kidney         Cortical Collecting Duct   UBERON:0004203  CL:1001033   \n",
       "3  Kidney         Cortical Collecting Duct   UBERON:0004203  CL:1000714   \n",
       "4  Kidney  Outer Medullary Collecting Duct   UBERON:0004204  CL:1000717   \n",
       "\n",
       "                                        CL_Label_FTU  popv  celltypist_L1  \\\n",
       "0    kidney cortex collecting duct intercalated cell     0              0   \n",
       "1     kidney collecting duct alpha-intercalated cell     0              0   \n",
       "2             peritubular capillary endothelial cell     0              0   \n",
       "3       kidney cortex collecting duct principal cell     0              0   \n",
       "4  kidney outer medulla collecting duct intercala...     0              0   \n",
       "\n",
       "   celltypist_pkl  azimuth_L4  azimuth_L2  azimuth_finest  azimuth_L3  \\\n",
       "0               0           0           1               0           1   \n",
       "1               0           0           0               0           0   \n",
       "2               0           0           1               0           1   \n",
       "3               0           0           1               0           1   \n",
       "4               0           0           1               0           1   \n",
       "\n",
       "   azimuth_L5  azimuth_L1  azimuth_level  \n",
       "0           0           0            NaN  \n",
       "1           0           0            NaN  \n",
       "2           0           0            NaN  \n",
       "3           0           0            NaN  \n",
       "4           0           0            NaN  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Map the organ names in FTU cell count table to UBERON IDs\n",
    "ftu_cell_count_filtered.loc[:, 'Organ_ID'] = ftu_cell_count_filtered['Organ'].map(organ_to_uberon)\n",
    "\n",
    "# Prepare a common structure to hold the results\n",
    "ann_level_info = []\n",
    "\n",
    "# Function to dynamically generate match info for celltypist and azimuth\n",
    "def generate_match_info(tool_name, tool_data):\n",
    "    levels = set()\n",
    "    for organ_level in tool_data['Organ_Level']:\n",
    "        if 'level' in organ_level.lower():\n",
    "            level = organ_level.split('_')[-2]\n",
    "        else:\n",
    "            level = organ_level.split('_')[-1]\n",
    "        levels.add(level)\n",
    "    \n",
    "    match_info = {f'{tool_name}_{level}': 0 for level in levels}\n",
    "    return match_info, levels\n",
    "\n",
    "# Dynamically generate the match info for celltypist and azimuth\n",
    "celltypist_info, celltypist_levels = generate_match_info('celltypist', celltypist)\n",
    "azimuth_info, azimuth_levels = generate_match_info('azimuth', azimuth)\n",
    "\n",
    "# Iterate over the filtered FTU cell count data\n",
    "for idx, ftu_row in ftu_cell_count_filtered.iterrows():\n",
    "    organ_name = ftu_row['Organ']\n",
    "    \n",
    "    ftu_label = ftu_row['FTU Label in Uberon']\n",
    "    ftu_id = ftu_row['FTU ID in Uberon']\n",
    "    cl_id = ftu_row['CL_ID']\n",
    "    cl_label = ftu_row['CL_Label_FTU']\n",
    "    \n",
    "    # Initialize match columns\n",
    "    match_info = {\n",
    "        'Organ': ftu_row['Organ'],\n",
    "        'FTU Label in Uberon': ftu_label,\n",
    "        'FTU ID in Uberon': ftu_id,\n",
    "        'CL_ID': cl_id,\n",
    "        'CL_Label_FTU': cl_label,\n",
    "        'popv': 0\n",
    "    }\n",
    "    match_info.update({key: 0 for key in celltypist_info.keys()})\n",
    "    match_info.update({key: 0 for key in azimuth_info.keys()})\n",
    "    \n",
    "    # Check in celltypist based on organ name in Organ_Level\n",
    "    celltypist_matches = celltypist[celltypist['Organ_Level'].str.contains(organ_name)]\n",
    "    celltypist_matches = celltypist_matches[celltypist_matches['CL_ID'] == cl_id]  # Match CL_ID\n",
    "    for _, ct_match in celltypist_matches.iterrows():\n",
    "        level = ct_match['Organ_Level'].split('_')[-1]  # Extract the level number\n",
    "        match_info[f'celltypist_{level}'] = 1\n",
    "\n",
    "    # Check in azimuth based on organ name in Organ_Level\n",
    "    azimuth_matches = azimuth[azimuth['Organ_Level'].str.contains(organ_name)]\n",
    "    azimuth_matches = azimuth_matches[azimuth_matches['CL_ID'] == cl_id]  # Match CL_ID\n",
    "    for _, az_match in azimuth_matches.iterrows():\n",
    "        level = az_match['Organ_Level'].split('_')[-1]  # Extract the level number\n",
    "        match_info[f'azimuth_{level}'] = 1\n",
    "\n",
    "    # Check in popv based on CL_ID and organ name in Organ_Level\n",
    "    popv_matches = popv[popv['Organ_Level'].str.contains(organ_name)]\n",
    "    popv_matches = popv_matches[popv_matches['CL_ID'] == cl_id]  # Match CL_ID\n",
    "    if not popv_matches.empty:\n",
    "        match_info['popv'] = 1\n",
    "\n",
    "    # Append to results\n",
    "    ann_level_info.append(match_info)\n",
    "\n",
    "# Create a dataframe from the results\n",
    "ann_level_df = pd.DataFrame(ann_level_info)\n",
    "\n",
    "# Display the first few rows of the resulting dataframe\n",
    "ann_level_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "32858122",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "File saved to output/FTU-CT-AnnTool-level-info.csv\n"
     ]
    }
   ],
   "source": [
    "# Save the result to a CSV file\n",
    "output_path = 'output/FTU-CT-AnnTool-level-info.csv'\n",
    "ann_level_df.to_csv(output_path, index=False)\n",
    "\n",
    "print(f\"File saved to {output_path}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "05fda6ae",
   "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>Organ</th>\n",
       "      <th>FTU Label in Uberon</th>\n",
       "      <th>FTU ID in Uberon</th>\n",
       "      <th>CL_ID</th>\n",
       "      <th>CL_Label_FTU</th>\n",
       "      <th>popv</th>\n",
       "      <th>celltypist_L1</th>\n",
       "      <th>celltypist_pkl</th>\n",
       "      <th>azimuth_L4</th>\n",
       "      <th>azimuth_L2</th>\n",
       "      <th>azimuth_finest</th>\n",
       "      <th>azimuth_L3</th>\n",
       "      <th>azimuth_L5</th>\n",
       "      <th>azimuth_L1</th>\n",
       "      <th>azimuth_level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Organ, FTU Label in Uberon, FTU ID in Uberon, CL_ID, CL_Label_FTU, popv, celltypist_L1, celltypist_pkl, azimuth_L4, azimuth_L2, azimuth_finest, azimuth_L3, azimuth_L5, azimuth_L1, azimuth_level]\n",
       "Index: []"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ann_level_df[ann_level_df.duplicated()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75444f91",
   "metadata": {},
   "source": [
    "### Retrieve the dataset information pertaining to FTU cell types."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb30015c",
   "metadata": {},
   "source": [
    "#### Understanding the structure, content, and formatting of the JSON files namely, atlas-as-cell-summaries.jsonld and atlas-enriched-dataset-graph.jsonld."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "35dec1cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to extract structure and keys of the JSON\n",
    "def extract_structure(data, level=0):\n",
    "    structure = {}\n",
    "    if isinstance(data, dict):\n",
    "        for key, value in data.items():\n",
    "            structure[key] = extract_structure(value, level + 1)\n",
    "    elif isinstance(data, list) and len(data) > 0:\n",
    "        structure = [extract_structure(data[0], level + 1)]\n",
    "    else:\n",
    "        structure = None\n",
    "    return structure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "c2fac17f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'@context': {'CL': {'@id': None, '@prefix': None},\n",
       "  'ASCTB-TEMP': {'@id': None, '@prefix': None},\n",
       "  'ctpop': {'@id': None, '@prefix': None},\n",
       "  'as_3d_id': {'@type': None},\n",
       "  'as_id': {'@type': None},\n",
       "  'all_collisions': {'@id': None},\n",
       "  'collision_source': {'@reverse': None, '@type': None},\n",
       "  'collisions': {'@id': None},\n",
       "  'corridor_source': {'@reverse': None, '@type': None},\n",
       "  'corridor': {'@id': None},\n",
       "  'aggregated_cell_source': {'@id': None},\n",
       "  'summaries': {'@id': None},\n",
       "  'cell_source': {'@reverse': None, '@type': None},\n",
       "  'aggregated_summaries': {'@id': None},\n",
       "  'annotation_method': {'@id': None},\n",
       "  'summary': {'@id': None},\n",
       "  'cell_id': {'@type': None},\n",
       "  'count': {'@id': None},\n",
       "  'percentage': {'@id': None},\n",
       "  'cell_count': {'@id': None, '@type': None},\n",
       "  'gene_count': {'@id': None, '@type': None},\n",
       "  'organ_id': {'@type': None},\n",
       "  'cell_source_a': {'@type': None},\n",
       "  'cell_source_b': {'@type': None},\n",
       "  'entity_a': {'@type': None},\n",
       "  'entity_b': {'@type': None},\n",
       "  '@base': None,\n",
       "  '@vocab': None,\n",
       "  'ccf': None,\n",
       "  'rdfs': None,\n",
       "  'dcterms': None,\n",
       "  'label': None,\n",
       "  'description': None,\n",
       "  'link': None,\n",
       "  'sex': None,\n",
       "  'age': None,\n",
       "  'bmi': None,\n",
       "  'consortium_name': None,\n",
       "  'provider_name': None,\n",
       "  'provider_uuid': None,\n",
       "  'donor': {'@id': None, '@type': None},\n",
       "  'samples': {'@reverse': None},\n",
       "  'sections': {'@id': None, '@type': None},\n",
       "  'datasets': {'@id': None, '@type': None},\n",
       "  'sample_type': None,\n",
       "  'section_count': None,\n",
       "  'section_size': None,\n",
       "  'section_units': None,\n",
       "  'section_number': None,\n",
       "  'rui_location': {'@id': None, '@type': None},\n",
       "  'ccf_annotations': {'@id': None, '@type': None, '@container': None},\n",
       "  'representation_of': {'@id': None, '@type': None},\n",
       "  'reference_organ': {'@id': None, '@type': None},\n",
       "  'extraction_set_for': {'@id': None, '@type': None},\n",
       "  'extraction_set': {'@id': None, '@type': None},\n",
       "  'organ_owner_sex': None,\n",
       "  'side': None,\n",
       "  'rui_rank': None,\n",
       "  'slice_thickness': None,\n",
       "  'slice_count': None,\n",
       "  'object': {'@id': None, '@type': None},\n",
       "  'creation_date': None,\n",
       "  'updated_date': None,\n",
       "  'creator': None,\n",
       "  'creator_first_name': None,\n",
       "  'creator_last_name': None,\n",
       "  'placement': {'@reverse': None},\n",
       "  'placement_date': None,\n",
       "  'rotation_order': None,\n",
       "  'dimension_units': None,\n",
       "  'rotation_units': None,\n",
       "  'scaling_units': None,\n",
       "  'translation_units': None,\n",
       "  'source': {'@id': None, '@type': None},\n",
       "  'target': {'@id': None, '@type': None},\n",
       "  'x_rotation': None,\n",
       "  'y_rotation': None,\n",
       "  'z_rotation': None,\n",
       "  'x_scaling': None,\n",
       "  'y_scaling': None,\n",
       "  'z_scaling': None,\n",
       "  'x_translation': None,\n",
       "  'y_translation': None,\n",
       "  'z_translation': None,\n",
       "  'x_dimension': None,\n",
       "  'y_dimension': None,\n",
       "  'z_dimension': None,\n",
       "  'ontology_terms': {'@id': None, '@type': None},\n",
       "  'technology': None,\n",
       "  'thumbnail': None,\n",
       "  'file': None,\n",
       "  'file_format': None,\n",
       "  'file_subpath': None},\n",
       " '@graph': [{'@type': None,\n",
       "   'sex': None,\n",
       "   'cell_source': None,\n",
       "   'cell_source_label': None,\n",
       "   'annotation_method': None,\n",
       "   'aggregated_summary_count': None,\n",
       "   'aggregated_summaries': [{'aggregated_cell_source': None,\n",
       "     'percentage': None}],\n",
       "   'modality': None,\n",
       "   'summary': [{'@type': None,\n",
       "     'cell_id': None,\n",
       "     'cell_label': None,\n",
       "     'count': None,\n",
       "     'percentage': None}]}]}"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract structure\n",
    "json_structure_cell_summaries = extract_structure(json_data_summary)\n",
    "json_structure_cell_summaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "6a1d76ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'@context': {'CL': {'@id': None, '@prefix': None},\n",
       "  'ASCTB-TEMP': {'@id': None, '@prefix': None},\n",
       "  'ctpop': {'@id': None, '@prefix': None},\n",
       "  'as_3d_id': {'@type': None},\n",
       "  'as_id': {'@type': None},\n",
       "  'all_collisions': {'@id': None},\n",
       "  'collision_source': {'@reverse': None, '@type': None},\n",
       "  'collisions': {'@id': None},\n",
       "  'corridor_source': {'@reverse': None, '@type': None},\n",
       "  'corridor': {'@id': None},\n",
       "  'aggregated_cell_source': {'@id': None},\n",
       "  'summaries': {'@id': None},\n",
       "  'cell_source': {'@reverse': None, '@type': None},\n",
       "  'aggregated_summaries': {'@id': None},\n",
       "  'annotation_method': {'@id': None},\n",
       "  'summary': {'@id': None},\n",
       "  'cell_id': {'@type': None},\n",
       "  'count': {'@id': None},\n",
       "  'percentage': {'@id': None},\n",
       "  'cell_count': {'@id': None, '@type': None},\n",
       "  'gene_count': {'@id': None, '@type': None},\n",
       "  'organ_id': {'@type': None},\n",
       "  'cell_source_a': {'@type': None},\n",
       "  'cell_source_b': {'@type': None},\n",
       "  'entity_a': {'@type': None},\n",
       "  'entity_b': {'@type': None},\n",
       "  '@base': None,\n",
       "  '@vocab': None,\n",
       "  'ccf': None,\n",
       "  'rdfs': None,\n",
       "  'dcterms': None,\n",
       "  'label': None,\n",
       "  'description': None,\n",
       "  'link': None,\n",
       "  'sex': None,\n",
       "  'age': None,\n",
       "  'bmi': None,\n",
       "  'consortium_name': None,\n",
       "  'provider_name': None,\n",
       "  'provider_uuid': None,\n",
       "  'donor': {'@id': None, '@type': None},\n",
       "  'samples': {'@reverse': None},\n",
       "  'sections': {'@id': None, '@type': None},\n",
       "  'datasets': {'@id': None, '@type': None},\n",
       "  'sample_type': None,\n",
       "  'section_count': None,\n",
       "  'section_size': None,\n",
       "  'section_units': None,\n",
       "  'section_number': None,\n",
       "  'rui_location': {'@id': None, '@type': None},\n",
       "  'ccf_annotations': {'@id': None, '@type': None, '@container': None},\n",
       "  'representation_of': {'@id': None, '@type': None},\n",
       "  'reference_organ': {'@id': None, '@type': None},\n",
       "  'extraction_set_for': {'@id': None, '@type': None},\n",
       "  'extraction_set': {'@id': None, '@type': None},\n",
       "  'organ_owner_sex': None,\n",
       "  'side': None,\n",
       "  'rui_rank': None,\n",
       "  'slice_thickness': None,\n",
       "  'slice_count': None,\n",
       "  'object': {'@id': None, '@type': None},\n",
       "  'creation_date': None,\n",
       "  'updated_date': None,\n",
       "  'creator': None,\n",
       "  'creator_first_name': None,\n",
       "  'creator_last_name': None,\n",
       "  'placement': {'@reverse': None},\n",
       "  'placement_date': None,\n",
       "  'rotation_order': None,\n",
       "  'dimension_units': None,\n",
       "  'rotation_units': None,\n",
       "  'scaling_units': None,\n",
       "  'translation_units': None,\n",
       "  'source': {'@id': None, '@type': None},\n",
       "  'target': {'@id': None, '@type': None},\n",
       "  'x_rotation': None,\n",
       "  'y_rotation': None,\n",
       "  'z_rotation': None,\n",
       "  'x_scaling': None,\n",
       "  'y_scaling': None,\n",
       "  'z_scaling': None,\n",
       "  'x_translation': None,\n",
       "  'y_translation': None,\n",
       "  'z_translation': None,\n",
       "  'x_dimension': None,\n",
       "  'y_dimension': None,\n",
       "  'z_dimension': None,\n",
       "  'ontology_terms': {'@id': None, '@type': None},\n",
       "  'technology': None,\n",
       "  'thumbnail': None,\n",
       "  'file': None,\n",
       "  'file_format': None,\n",
       "  'file_subpath': None},\n",
       " '@graph': [{'@id': None,\n",
       "   '@type': None,\n",
       "   'samples': [{'@id': None,\n",
       "     '@type': None,\n",
       "     'datasets': None,\n",
       "     'sections': [{'@id': None,\n",
       "       '@type': None,\n",
       "       'datasets': [{'@id': None,\n",
       "         '@type': None,\n",
       "         'link': None,\n",
       "         'technology': None,\n",
       "         'cell_count': None,\n",
       "         'gene_count': None,\n",
       "         'organ_id': None,\n",
       "         'label': None,\n",
       "         'description': None,\n",
       "         'thumbnail': None,\n",
       "         'summaries': [{'@type': None,\n",
       "           'annotation_method': None,\n",
       "           'modality': None,\n",
       "           'summary': [{'cell_id': None,\n",
       "             'cell_label': None,\n",
       "             'gene_expr': [{'gene_id': None,\n",
       "               'gene_label': None,\n",
       "               'ensembl_id': None,\n",
       "               'mean_gene_expr_value': None}],\n",
       "             'count': None,\n",
       "             '@type': None,\n",
       "             'percentage': None}],\n",
       "           'sex': None}]}],\n",
       "       'sample_type': None,\n",
       "       'link': None,\n",
       "       'label': None,\n",
       "       'description': None}],\n",
       "     'sample_type': None,\n",
       "     'link': None,\n",
       "     'label': None,\n",
       "     'description': None,\n",
       "     'rui_location': {'@context': None,\n",
       "      '@id': None,\n",
       "      '@type': None,\n",
       "      'ccf_annotations': [None],\n",
       "      'creation_date': None,\n",
       "      'creator': None,\n",
       "      'creator_first_name': None,\n",
       "      'creator_last_name': None,\n",
       "      'dimension_units': None,\n",
       "      'placement': {'@context': None,\n",
       "       '@id': None,\n",
       "       '@type': None,\n",
       "       'placement_date': None,\n",
       "       'rotation_order': None,\n",
       "       'rotation_units': None,\n",
       "       'scaling_units': None,\n",
       "       'target': None,\n",
       "       'translation_units': None,\n",
       "       'x_rotation': None,\n",
       "       'x_scaling': None,\n",
       "       'x_translation': None,\n",
       "       'y_rotation': None,\n",
       "       'y_scaling': None,\n",
       "       'y_translation': None,\n",
       "       'z_rotation': None,\n",
       "       'z_scaling': None,\n",
       "       'z_translation': None},\n",
       "      'x_dimension': None,\n",
       "      'y_dimension': None,\n",
       "      'z_dimension': None,\n",
       "      'all_collisions': [{'@type': None,\n",
       "        'collision_method': None,\n",
       "        'collisions': [{'@type': None,\n",
       "          'reference_organ': None,\n",
       "          'as_3d_id': None,\n",
       "          'as_id': None,\n",
       "          'as_label': None,\n",
       "          'as_volume': None,\n",
       "          'percentage': None}]}],\n",
       "      'corridor': {'@type': None, 'file_format': None, 'file': None},\n",
       "      'summaries': [{'@type': None,\n",
       "        'sex': None,\n",
       "        'annotation_method': None,\n",
       "        'aggregated_summary_count': None,\n",
       "        'aggregated_summaries': [{'aggregated_cell_source': None,\n",
       "          'percentage': None}],\n",
       "        'modality': None,\n",
       "        'summary': [{'@type': None,\n",
       "          'cell_id': None,\n",
       "          'cell_label': None,\n",
       "          'count': None,\n",
       "          'percentage': None}]}]}}],\n",
       "   'consortium_name': None,\n",
       "   'provider_name': None,\n",
       "   'provider_uuid': None,\n",
       "   'age': None,\n",
       "   'sex': None,\n",
       "   'bmi': None,\n",
       "   'race': None,\n",
       "   'link': None,\n",
       "   'label': None,\n",
       "   'description': None}]}"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract structure\n",
    "json_structure_enriched_dataset_graph = extract_structure(json_data_enriched)\n",
    "json_structure_enriched_dataset_graph"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a910b375",
   "metadata": {},
   "source": [
    "### Filter the registered dataset from the pool of dataset logged in HRApop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "cde44bee",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset_ids = sankey[sankey['is_atlas_dataset'] == True]['unique_dataset_id'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "24fa22e2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of dataset that are registered in HRApop :  553\n"
     ]
    }
   ],
   "source": [
    "print('The number of dataset that are registered in HRApop : ', len(dataset_ids))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35e19574",
   "metadata": {},
   "source": [
    "#### Extract Source, CT and dataset information from as-cell-summaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "f3e91c01",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      cell_id            cell_label annotation_method            modality  \\\n",
      "0  CL:0009080  intestinal tuft cell              popv  sc_transcriptomics   \n",
      "1  CL:0000057            fibroblast              popv  sc_transcriptomics   \n",
      "2  CL:0000775            neutrophil              popv  sc_transcriptomics   \n",
      "3  CL:0000775            neutrophil              popv  sc_transcriptomics   \n",
      "4  CL:0000775            neutrophil              popv  sc_transcriptomics   \n",
      "\n",
      "  cell_source_label     sex  \\\n",
      "0           jejunum  Female   \n",
      "1           jejunum  Female   \n",
      "2           jejunum  Female   \n",
      "3           jejunum  Female   \n",
      "4           jejunum  Female   \n",
      "\n",
      "                                            datasets  #datasets  \n",
      "0  [{'aggregated_cell_source': 'https://entity.ap...          2  \n",
      "1  [{'aggregated_cell_source': 'https://entity.ap...          2  \n",
      "2  [{'aggregated_cell_source': 'https://entity.ap...          2  \n",
      "3  [{'aggregated_cell_source': 'https://entity.ap...          2  \n",
      "4  [{'aggregated_cell_source': 'https://entity.ap...          2  \n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/p7/kpn2wtl53w9bjt1kf0s_jfh40000gq/T/ipykernel_38176/4123233818.py:16: FutureWarning: Series.fillna with 'method' is deprecated and will raise in a future version. Use obj.ffill() or obj.bfill() instead.\n",
      "  merged_json_df['modality'] = merged_json_df['modality'].fillna(method='ffill')\n",
      "/var/folders/p7/kpn2wtl53w9bjt1kf0s_jfh40000gq/T/ipykernel_38176/4123233818.py:53: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  CTs_with_datasets_df['#datasets'] = CTs_with_datasets_df['datasets'].apply(lambda x: len(x) if isinstance(x, list) else 0)\n"
     ]
    }
   ],
   "source": [
    "# Extract the relevant data from the JSON file\n",
    "json_graph = json_data_summary['@graph']\n",
    "\n",
    "# Convert the JSON graph to a DataFrame for easier manipulation\n",
    "json_df = pd.json_normalize(json_graph)\n",
    "\n",
    "# Ensure modality is expanded correctly\n",
    "expanded_json_df = json_df.explode('summary').reset_index(drop=True)\n",
    "expanded_json_df['modality'] = json_df.explode('modality').reset_index(drop=True)['modality']\n",
    "\n",
    "# Normalize the summary column and merge with expanded_json_df\n",
    "summary_df = pd.json_normalize(expanded_json_df['summary'])\n",
    "merged_json_df = pd.concat([expanded_json_df.drop(columns=['summary']), summary_df], axis=1)\n",
    "\n",
    "# Ensure correct modality assignment\n",
    "merged_json_df['modality'] = merged_json_df['modality'].fillna(method='ffill')\n",
    "\n",
    "# Extract relevant columns from the CSV data\n",
    "ftu_relevant_columns = ftu_cell_count[['Organ', 'FTU Label in Uberon', 'FTU ID in Uberon','CL_ID', 'CL_Label_FTU']]\n",
    "\n",
    "# Merge the CSV and JSON DataFrames based on matching cell_id\n",
    "merged_data = pd.merge(\n",
    "    merged_json_df,\n",
    "    ftu_relevant_columns,\n",
    "    left_on='cell_id',\n",
    "    right_on='CL_ID',\n",
    "    how='inner'\n",
    ")\n",
    "\n",
    "# Create the final DataFrame with the desired columns\n",
    "CTs_with_datasets_df = merged_data[[\n",
    "    'cell_id',\n",
    "    'cell_label',\n",
    "    'annotation_method',\n",
    "    'modality',\n",
    "    'cell_source_label',\n",
    "    'sex',\n",
    "    'aggregated_summaries'\n",
    "]]\n",
    "\n",
    "# Rename the columns as specified\n",
    "CTs_with_datasets_df.columns = [\n",
    "    'cell_id',\n",
    "    'cell_label',\n",
    "    'annotation_method',\n",
    "    'modality',\n",
    "    'cell_source_label',\n",
    "    'sex',\n",
    "    'datasets'\n",
    "]\n",
    "\n",
    "# Add the new column \"#datasets\"\n",
    "CTs_with_datasets_df['#datasets'] = CTs_with_datasets_df['datasets'].apply(lambda x: len(x) if isinstance(x, list) else 0)\n",
    "\n",
    "# Check for null values in the 'modality' column again\n",
    "null_modality_entries_after_correction = CTs_with_datasets_df[CTs_with_datasets_df['modality'].isnull()]\n",
    "\n",
    "# Display the first few rows of the final DataFrame\n",
    "print(CTs_with_datasets_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "2ab4caa8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Final CSV saved to: output/CTs-with-datasets-info.csv\n"
     ]
    }
   ],
   "source": [
    "# Save the final DataFrame to a CSV file\n",
    "final_csv_path = 'output/CTs-with-datasets-info.csv'\n",
    "CTs_with_datasets_df.to_csv(final_csv_path, index=False)\n",
    "print(f\"Final CSV saved to: {final_csv_path}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "5019c964",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['sc_transcriptomics'], dtype=object)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CTs_with_datasets_df['modality'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "86501894",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "115"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CTs_with_datasets_df['cell_label'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "3cefe2fc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The number of dataset that are registered in cell summary :  0\n"
     ]
    }
   ],
   "source": [
    "print('The number of dataset that are registered in cell summary : ',len(dataset))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "e3ba822a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Difference in dataset: 553\n"
     ]
    }
   ],
   "source": [
    "difference_ds = set(dataset_ids) - dataset\n",
    "print(\"Difference in dataset:\", len(difference_ds))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "46a20d12",
   "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>portal</th>\n",
       "      <th>study_paper</th>\n",
       "      <th>doi</th>\n",
       "      <th>lead_author</th>\n",
       "      <th>is_azimuth_reference</th>\n",
       "      <th>donor_id</th>\n",
       "      <th>donor_sex</th>\n",
       "      <th>donor_age</th>\n",
       "      <th>donor_development_stage</th>\n",
       "      <th>donor_race</th>\n",
       "      <th>...</th>\n",
       "      <th>unique_dataset_id</th>\n",
       "      <th>link_to_h5ad_file</th>\n",
       "      <th>sc_transcriptomics_or_sc_proteomics</th>\n",
       "      <th>cell_type_annotation_tool</th>\n",
       "      <th>omap_id</th>\n",
       "      <th>number_of_cells_total</th>\n",
       "      <th>number_of_unique_cell_types</th>\n",
       "      <th>hubmap_dataset_publication_status</th>\n",
       "      <th>is_rui_registered</th>\n",
       "      <th>is_atlas_dataset</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3262</th>\n",
       "      <td>NHLBI/LungMap</td>\n",
       "      <td>LungMAP — Human data from a broad age healthy ...</td>\n",
       "      <td>https://doi.org/10.7554/eLife.62522</td>\n",
       "      <td>Allen Wang</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D032_Donor</td>\n",
       "      <td>Male</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>https://api.cellxgene.cziscience.com/dp/v1/col...</td>\n",
       "      <td>https://data-browser.lungmap.net/explore/proje...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>azimuth</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14910.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3263</th>\n",
       "      <td>NHLBI/LungMap</td>\n",
       "      <td>LungMAP — Human data from a broad age healthy ...</td>\n",
       "      <td>https://doi.org/10.7554/eLife.62522</td>\n",
       "      <td>Allen Wang</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D032_Donor</td>\n",
       "      <td>Male</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>https://api.cellxgene.cziscience.com/dp/v1/col...</td>\n",
       "      <td>https://data-browser.lungmap.net/explore/proje...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>azimuth</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14910.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3264</th>\n",
       "      <td>NHLBI/LungMap</td>\n",
       "      <td>LungMAP — Human data from a broad age healthy ...</td>\n",
       "      <td>https://doi.org/10.7554/eLife.62522</td>\n",
       "      <td>Allen Wang</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D032_Donor</td>\n",
       "      <td>Male</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>https://api.cellxgene.cziscience.com/dp/v1/col...</td>\n",
       "      <td>https://data-browser.lungmap.net/explore/proje...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>celltypist</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14910.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3265</th>\n",
       "      <td>NHLBI/LungMap</td>\n",
       "      <td>LungMAP — Human data from a broad age healthy ...</td>\n",
       "      <td>https://doi.org/10.7554/eLife.62522</td>\n",
       "      <td>Allen Wang</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D032_Donor</td>\n",
       "      <td>Male</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>https://api.cellxgene.cziscience.com/dp/v1/col...</td>\n",
       "      <td>https://data-browser.lungmap.net/explore/proje...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>celltypist</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14910.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3266</th>\n",
       "      <td>NHLBI/LungMap</td>\n",
       "      <td>LungMAP — Human data from a broad age healthy ...</td>\n",
       "      <td>https://doi.org/10.7554/eLife.62522</td>\n",
       "      <td>Allen Wang</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D032_Donor</td>\n",
       "      <td>Male</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>https://api.cellxgene.cziscience.com/dp/v1/col...</td>\n",
       "      <td>https://data-browser.lungmap.net/explore/proje...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>popv</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14910.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15076</th>\n",
       "      <td>HuBMAP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Male</td>\n",
       "      <td>37.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>White</td>\n",
       "      <td>...</td>\n",
       "      <td>https://entity.api.hubmapconsortium.org/entiti...</td>\n",
       "      <td>https://portal.hubmapconsortium.org/browse/dat...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>celltypist</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10600.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15077</th>\n",
       "      <td>HuBMAP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Male</td>\n",
       "      <td>37.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>White</td>\n",
       "      <td>...</td>\n",
       "      <td>https://entity.api.hubmapconsortium.org/entiti...</td>\n",
       "      <td>https://portal.hubmapconsortium.org/browse/dat...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>popv</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10600.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15080</th>\n",
       "      <td>HuBMAP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Male</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>White</td>\n",
       "      <td>...</td>\n",
       "      <td>https://entity.api.hubmapconsortium.org/entiti...</td>\n",
       "      <td>https://portal.hubmapconsortium.org/browse/dat...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>popv</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22996.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15167</th>\n",
       "      <td>SenNet</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>https://entity.api.sennetconsortium.org/entiti...</td>\n",
       "      <td>https://data.sennetconsortium.org/dataset?uuid...</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>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15317</th>\n",
       "      <td>SenNet</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>https://entity.api.sennetconsortium.org/entiti...</td>\n",
       "      <td>https://data.sennetconsortium.org/dataset?uuid...</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>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1156 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              portal                                        study_paper  \\\n",
       "3262   NHLBI/LungMap  LungMAP — Human data from a broad age healthy ...   \n",
       "3263   NHLBI/LungMap  LungMAP — Human data from a broad age healthy ...   \n",
       "3264   NHLBI/LungMap  LungMAP — Human data from a broad age healthy ...   \n",
       "3265   NHLBI/LungMap  LungMAP — Human data from a broad age healthy ...   \n",
       "3266   NHLBI/LungMap  LungMAP — Human data from a broad age healthy ...   \n",
       "...              ...                                                ...   \n",
       "15076         HuBMAP                                                NaN   \n",
       "15077         HuBMAP                                                NaN   \n",
       "15080         HuBMAP                                                NaN   \n",
       "15167         SenNet                                                NaN   \n",
       "15317         SenNet                                                NaN   \n",
       "\n",
       "                                       doi lead_author  is_azimuth_reference  \\\n",
       "3262   https://doi.org/10.7554/eLife.62522  Allen Wang                   NaN   \n",
       "3263   https://doi.org/10.7554/eLife.62522  Allen Wang                   NaN   \n",
       "3264   https://doi.org/10.7554/eLife.62522  Allen Wang                   NaN   \n",
       "3265   https://doi.org/10.7554/eLife.62522  Allen Wang                   NaN   \n",
       "3266   https://doi.org/10.7554/eLife.62522  Allen Wang                   NaN   \n",
       "...                                    ...         ...                   ...   \n",
       "15076                                  NaN         NaN                   NaN   \n",
       "15077                                  NaN         NaN                   NaN   \n",
       "15080                                  NaN         NaN                   NaN   \n",
       "15167                                  NaN         NaN                   NaN   \n",
       "15317                                  NaN         NaN                   NaN   \n",
       "\n",
       "         donor_id donor_sex  donor_age  donor_development_stage donor_race  \\\n",
       "3262   D032_Donor      Male        3.0                      NaN        NaN   \n",
       "3263   D032_Donor      Male        3.0                      NaN        NaN   \n",
       "3264   D032_Donor      Male        3.0                      NaN        NaN   \n",
       "3265   D032_Donor      Male        3.0                      NaN        NaN   \n",
       "3266   D032_Donor      Male        3.0                      NaN        NaN   \n",
       "...           ...       ...        ...                      ...        ...   \n",
       "15076         NaN      Male       37.0                      NaN      White   \n",
       "15077         NaN      Male       37.0                      NaN      White   \n",
       "15080         NaN      Male       18.0                      NaN      White   \n",
       "15167         NaN   Unknown        NaN                      NaN        NaN   \n",
       "15317         NaN   Unknown        NaN                      NaN        NaN   \n",
       "\n",
       "       ...                                  unique_dataset_id  \\\n",
       "3262   ...  https://api.cellxgene.cziscience.com/dp/v1/col...   \n",
       "3263   ...  https://api.cellxgene.cziscience.com/dp/v1/col...   \n",
       "3264   ...  https://api.cellxgene.cziscience.com/dp/v1/col...   \n",
       "3265   ...  https://api.cellxgene.cziscience.com/dp/v1/col...   \n",
       "3266   ...  https://api.cellxgene.cziscience.com/dp/v1/col...   \n",
       "...    ...                                                ...   \n",
       "15076  ...  https://entity.api.hubmapconsortium.org/entiti...   \n",
       "15077  ...  https://entity.api.hubmapconsortium.org/entiti...   \n",
       "15080  ...  https://entity.api.hubmapconsortium.org/entiti...   \n",
       "15167  ...  https://entity.api.sennetconsortium.org/entiti...   \n",
       "15317  ...  https://entity.api.sennetconsortium.org/entiti...   \n",
       "\n",
       "                                       link_to_h5ad_file  \\\n",
       "3262   https://data-browser.lungmap.net/explore/proje...   \n",
       "3263   https://data-browser.lungmap.net/explore/proje...   \n",
       "3264   https://data-browser.lungmap.net/explore/proje...   \n",
       "3265   https://data-browser.lungmap.net/explore/proje...   \n",
       "3266   https://data-browser.lungmap.net/explore/proje...   \n",
       "...                                                  ...   \n",
       "15076  https://portal.hubmapconsortium.org/browse/dat...   \n",
       "15077  https://portal.hubmapconsortium.org/browse/dat...   \n",
       "15080  https://portal.hubmapconsortium.org/browse/dat...   \n",
       "15167  https://data.sennetconsortium.org/dataset?uuid...   \n",
       "15317  https://data.sennetconsortium.org/dataset?uuid...   \n",
       "\n",
       "      sc_transcriptomics_or_sc_proteomics cell_type_annotation_tool  omap_id  \\\n",
       "3262                                  NaN                   azimuth      NaN   \n",
       "3263                                  NaN                   azimuth      NaN   \n",
       "3264                                  NaN                celltypist      NaN   \n",
       "3265                                  NaN                celltypist      NaN   \n",
       "3266                                  NaN                      popv      NaN   \n",
       "...                                   ...                       ...      ...   \n",
       "15076                                 NaN                celltypist      NaN   \n",
       "15077                                 NaN                      popv      NaN   \n",
       "15080                                 NaN                      popv      NaN   \n",
       "15167                                 NaN                       NaN      NaN   \n",
       "15317                                 NaN                       NaN      NaN   \n",
       "\n",
       "       number_of_cells_total  number_of_unique_cell_types  \\\n",
       "3262                 14910.0                         64.0   \n",
       "3263                 14910.0                         64.0   \n",
       "3264                 14910.0                         64.0   \n",
       "3265                 14910.0                         64.0   \n",
       "3266                 14910.0                         64.0   \n",
       "...                      ...                          ...   \n",
       "15076                10600.0                        129.0   \n",
       "15077                10600.0                        129.0   \n",
       "15080                22996.0                         14.0   \n",
       "15167                    NaN                          NaN   \n",
       "15317                    NaN                          NaN   \n",
       "\n",
       "      hubmap_dataset_publication_status is_rui_registered is_atlas_dataset  \n",
       "3262                                NaN              True             True  \n",
       "3263                                NaN              True             True  \n",
       "3264                                NaN              True             True  \n",
       "3265                                NaN              True             True  \n",
       "3266                                NaN              True             True  \n",
       "...                                 ...               ...              ...  \n",
       "15076                               NaN              True             True  \n",
       "15077                               NaN              True             True  \n",
       "15080                               NaN              True             True  \n",
       "15167                               NaN              True             True  \n",
       "15317                               NaN              True             True  \n",
       "\n",
       "[1156 rows x 32 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Print the dataset information that is not present in cell_summary\n",
    "ds_not_in_cell_summary = sankey[sankey['unique_dataset_id'].isin(difference_ds)]\n",
    "ds_not_in_cell_summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "8de08783",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cell_type_annotation_tool\n",
       "azimuth          483\n",
       "popv             313\n",
       "celltypist       281\n",
       "sc_proteomics     77\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ds_not_in_cell_summary['cell_type_annotation_tool'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "79a8e109",
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_not_in_cell_summary.to_csv('output/dataset-in-sankey-not-in-cell-summary.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c4f1e97",
   "metadata": {},
   "source": [
    "#### Extract gene information for idenfied CT - dataset combination from cell-summary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "88ab5535",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Empty DataFrame\n",
      "Columns: [organ_id, organ_name, reference_organ, cell_id, cell_label, annotation_method, as_label, sex, dataset, gene_id, gene_label, ensembl_id, mean_gene_expr_value]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "# Define the columns for the enriched dataframe\n",
    "columns = [\n",
    "    'organ_id', 'organ_name', 'reference_organ', 'cell_id', 'cell_label', 'annotation_method', \n",
    "    'as_label', 'sex', 'dataset', 'gene_id', \n",
    "    'gene_label', 'ensembl_id', 'mean_gene_expr_value'\n",
    "]\n",
    "enriched_v1_df = pd.DataFrame(columns=columns)\n",
    "\n",
    "# Function to extract and match data based on cell_id, annotation_method and aggregated_summary\n",
    "def extract_data(csv_row, json_data):\n",
    "    matches = []\n",
    "    cell_id = csv_row['cell_id']\n",
    "    annotation_method = csv_row['annotation_method']\n",
    "    aggregated_summaries = csv_row['datasets']  # Assuming this is a list of datasets\n",
    "    organ_name = 'unknown'\n",
    "    for entry in json_data[\"@graph\"]:\n",
    "        for sample in entry.get('samples', []):\n",
    "            rui_location = sample.get('rui_location', {})\n",
    "            all_collisions = rui_location.get('all_collisions', [])\n",
    "            for collision in all_collisions:\n",
    "                collisions = collision.get('collisions', [])\n",
    "                for col in collisions:\n",
    "                    reference_organ = col.get('reference_organ', 'unknown')\n",
    "                    as_label = col.get('as_label', 'unknown')\n",
    "                    for section in sample.get('sections', []):\n",
    "                        for dataset in section.get('datasets', []):\n",
    "                            summaries = dataset.get('summaries', [])\n",
    "                            organ_id = dataset.get('organ_id', 'unknown')\n",
    "                            dataset_id = dataset.get('@id', '')  # Extract the dataset id\n",
    "                            if not isinstance(dataset_id, str):\n",
    "                                dataset_id = str(dataset_id)  # Ensure dataset_id is a string\n",
    "\n",
    "                            for summary in summaries:\n",
    "                                if summary.get('annotation_method') == annotation_method:\n",
    "                                    for summ in summary['summary']:\n",
    "                                        if isinstance(summ, dict) and summ['cell_id'] == cell_id:\n",
    "                                            for aggregated_summary in aggregated_summaries:\n",
    "                                                # Convert aggregated_summary to a string if it's not already\n",
    "                                                if not isinstance(aggregated_summary, str):\n",
    "                                                    aggregated_summary = str(aggregated_summary)\n",
    "                                                    \n",
    "                                                # Check if aggregated_summary is part of dataset_id\n",
    "                                                if aggregated_summary in dataset_id:\n",
    "                                                    for gene_expr in summ.get('gene_expr', []):\n",
    "                                                        if isinstance(gene_expr, dict):  # Ensure gene_expr is a dictionary\n",
    "                                                            new_row = {\n",
    "                                                                'organ_id': organ_id,\n",
    "                                                                'organ_name': organ_name,\n",
    "                                                                'reference_organ': reference_organ.split('#')[-1].replace('VHF', '').replace('VHM', ''),                                                                \n",
    "                                                                'cell_id': cell_id,\n",
    "                                                                'cell_label': summ['cell_label'],\n",
    "                                                                'annotation_method': annotation_method,\n",
    "                                                                'as_label': as_label,\n",
    "                                                                'sex': csv_row['sex'],\n",
    "                                                                'dataset': aggregated_summary,\n",
    "                                                                'gene_id': gene_expr.get('gene_id'),\n",
    "                                                                'gene_label': gene_expr.get('gene_label'),\n",
    "                                                                'ensembl_id': gene_expr.get('ensembl_id'),\n",
    "                                                                'mean_gene_expr_value': gene_expr.get('mean_gene_expr_value'),\n",
    "                                                                \n",
    "                                                            }\n",
    "                                                            matches.append(new_row)\n",
    "    return matches\n",
    "\n",
    "# Iterate over the CTs with dataset csv file and extract matched data\n",
    "for index, row in CTs_with_datasets_df.iterrows():\n",
    "    if row.get('cell_id'):  # Filter data by cell id\n",
    "        matches = extract_data(row, json_data_enriched)\n",
    "        if matches:\n",
    "            enriched_v1_df = pd.concat([enriched_v1_df, pd.DataFrame(matches)], ignore_index=True)\n",
    "\n",
    "# Display the new dataframe\n",
    "print(enriched_v1_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27db8a1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "enriched_v1_df[enriched_v1_df.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d87848d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "enriched_v1_df = enriched_v1_df.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "871029c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "enriched_v1_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1324d15c",
   "metadata": {},
   "outputs": [],
   "source": [
    "enriched_v1_df['dataset'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d072e72c",
   "metadata": {},
   "outputs": [],
   "source": [
    "cell_source_label = set(enriched_v1_df['as_label'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45858df4",
   "metadata": {},
   "outputs": [],
   "source": [
    "cell_source_label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae988328",
   "metadata": {},
   "outputs": [],
   "source": [
    "enriched_v1_df['reference_organ'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41a21a3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "enriched_v1_df[enriched_v1_df['reference_organ'] == 'UrinaryBladder'][['cell_label', 'as_label']]['cell_label'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa26968d",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_enriched_v1_df = enriched_v1_df[enriched_v1_df['reference_organ'] == 'UrinaryBladder']\n",
    "aggregated_summaries = filtered_enriched_v1_df['dataset']\n",
    "\n",
    "# Display the aggregated_summaries\n",
    "print(aggregated_summaries.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2135de86",
   "metadata": {},
   "outputs": [],
   "source": [
    "# revmove reference organ for which there is no FTU\n",
    "organ_no_ftu = ['LeftUreter', 'Heart', 'HeartV1.1', 'UrinaryBladder']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7e1642c",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_enriched_v1_df = enriched_v1_df[~enriched_v1_df['reference_organ'].isin(organ_no_ftu)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1278131",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_enriched_v1_df['reference_organ'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c5a2993",
   "metadata": {},
   "source": [
    "#### Extract gene information for dataset in HRApop from enriched-dataset-graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "52474ba0",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/p7/kpn2wtl53w9bjt1kf0s_jfh40000gq/T/ipykernel_38176/2313816232.py:74: FutureWarning: The behavior of DataFrame concatenation with empty or all-NA entries is deprecated. In a future version, this will no longer exclude empty or all-NA columns when determining the result dtypes. To retain the old behavior, exclude the relevant entries before the concat operation.\n",
      "  enriched_v2_df = pd.concat([enriched_v2_df, pd.DataFrame(matches)], ignore_index=True)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                        organ_id  reference_organ     cell_id  \\\n",
      "0  http://purl.obolibrary.org/obo/UBERON_0002113  RightKidneyV1.1  CL:1000715   \n",
      "1  http://purl.obolibrary.org/obo/UBERON_0002113  RightKidneyV1.1  CL:1000715   \n",
      "2  http://purl.obolibrary.org/obo/UBERON_0002113  RightKidneyV1.1  CL:1000715   \n",
      "3  http://purl.obolibrary.org/obo/UBERON_0002113  RightKidneyV1.1  CL:1000715   \n",
      "4  http://purl.obolibrary.org/obo/UBERON_0002113  RightKidneyV1.1  CL:1000715   \n",
      "\n",
      "                                     cell_label annotation_method  \\\n",
      "0  Cortical Collecting Duct Intercalated Type A           azimuth   \n",
      "1  Cortical Collecting Duct Intercalated Type A           azimuth   \n",
      "2  Cortical Collecting Duct Intercalated Type A           azimuth   \n",
      "3  Cortical Collecting Duct Intercalated Type A           azimuth   \n",
      "4  Cortical Collecting Duct Intercalated Type A           azimuth   \n",
      "\n",
      "                 as_label     sex                       race    bmi x_value  \\\n",
      "0  outer cortex of kidney  Female  Black or African American  53.06       3   \n",
      "1  outer cortex of kidney  Female  Black or African American  53.06       3   \n",
      "2  outer cortex of kidney  Female  Black or African American  53.06       3   \n",
      "3  outer cortex of kidney  Female  Black or African American  53.06       3   \n",
      "4  outer cortex of kidney  Female  Black or African American  53.06       3   \n",
      "\n",
      "  y_value z_value                                               link  \\\n",
      "0       5       2  https://portal.hubmapconsortium.org/browse/dat...   \n",
      "1       5       2  https://portal.hubmapconsortium.org/browse/dat...   \n",
      "2       5       2  https://portal.hubmapconsortium.org/browse/dat...   \n",
      "3       5       2  https://portal.hubmapconsortium.org/browse/dat...   \n",
      "4       5       2  https://portal.hubmapconsortium.org/browse/dat...   \n",
      "\n",
      "                                             dataset     gene_id gene_label  \\\n",
      "0  https://entity.api.hubmapconsortium.org/entiti...  HGNC:17438       CLNK   \n",
      "1  https://entity.api.hubmapconsortium.org/entiti...    HGNC:946      MAGI1   \n",
      "2  https://entity.api.hubmapconsortium.org/entiti...  HGNC:27295       FYB2   \n",
      "3  https://entity.api.hubmapconsortium.org/entiti...  HGNC:53807  MUC20-OT1   \n",
      "4  https://entity.api.hubmapconsortium.org/entiti...  HGNC:25308    TMEM117   \n",
      "\n",
      "           ensembl_id  mean_gene_expr_value  \n",
      "0  ENSG00000109684.15             18.478542  \n",
      "1  ENSG00000151276.23             43.448765  \n",
      "2  ENSG00000187889.13              6.035665  \n",
      "3   ENSG00000242086.8             16.502508  \n",
      "4  ENSG00000139173.10             10.167935  \n"
     ]
    }
   ],
   "source": [
    "# Create an empty dataframe with the specified columns\n",
    "columns = [\n",
    "    'organ_id', 'reference_organ', 'cell_id', 'cell_label', 'annotation_method', \n",
    "    'as_label', 'sex', 'race', 'bmi', 'x_value', 'y_value', 'z_value', 'link', 'dataset', 'gene_id', \n",
    "    'gene_label', 'ensembl_id', 'mean_gene_expr_value'\n",
    "]\n",
    "\n",
    "enriched_v2_df = pd.DataFrame(columns=columns)\n",
    "\n",
    "# Function to extract and match data based on cell_id and annotation_method\n",
    "def extract_data(csv_row, json_data):\n",
    "    matches = []\n",
    "    cell_id = csv_row['CL_ID']\n",
    "    organ_name = 'unknown'\n",
    "    for entry in json_data[\"@graph\"]:\n",
    "        age = entry.get('age', 'unknown')\n",
    "        race = entry.get('race', 'unknown')\n",
    "        bmi = entry.get('bmi', 'unknown')       \n",
    "        for sample in entry.get('samples', []):            \n",
    "            rui_location = sample.get('rui_location', {})\n",
    "            all_collisions = rui_location.get('all_collisions', [])\n",
    "            x_value = rui_location.get('x_dimension', 'unknown')\n",
    "            y_value = rui_location.get('y_dimension', 'unknown')\n",
    "            z_value = rui_location.get('z_dimension', 'unknown')\n",
    "            for collision in all_collisions:\n",
    "                collisions = collision.get('collisions', [])\n",
    "                for col in collisions:\n",
    "                    reference_organ = col.get('reference_organ', 'unknown')\n",
    "                    as_label = col.get('as_label', 'unknown')\n",
    "                    as_volume = col.get('as_volumne', 'unknown')\n",
    "                    for section in sample.get('sections', []):\n",
    "                        for dataset in section.get('datasets', []):\n",
    "                            aggregated_summary = dataset.get('@id', [])\n",
    "                            if aggregated_summary in dataset_ids:\n",
    "                                link = dataset.get('link', [])\n",
    "                                organ_id = dataset.get('organ_id', 'unknown')\n",
    "                                summaries = dataset.get('summaries', [])                            \n",
    "                                for summary in summaries:\n",
    "                                    ann_method = summary.get('annotation_method', [])\n",
    "                                    sex = summary.get('sex', [])\n",
    "                                    for summ in summary['summary']:                                    \n",
    "                                        if summ['cell_id'] == cell_id:\n",
    "                                            for gene_expr in summ.get('gene_expr', []):\n",
    "                                                if isinstance(gene_expr, dict):  # Ensure gene_expr is a dictionary\n",
    "                                                    new_row = {\n",
    "                                                        'organ_id': organ_id,\n",
    "                                                        'reference_organ': reference_organ.split('#')[-1].replace('VHF', '').replace('VHM', ''),                                                                \n",
    "                                                        'cell_id': cell_id,\n",
    "                                                        'cell_label': summ['cell_label'],\n",
    "                                                        'annotation_method': ann_method,\n",
    "                                                        'as_label': as_label,\n",
    "                                                        'sex': sex,\n",
    "                                                        'race': race,\n",
    "                                                        'bmi': bmi,\n",
    "                                                        'x_value': x_value,\n",
    "                                                        'y_value': y_value,\n",
    "                                                        'z_value': z_value,\n",
    "                                                        'link' : link,\n",
    "                                                        'dataset': aggregated_summary,                                                        \n",
    "                                                        'gene_id': gene_expr.get('gene_id'),\n",
    "                                                        'gene_label': gene_expr.get('gene_label'),\n",
    "                                                        'ensembl_id': gene_expr.get('ensembl_id'),\n",
    "                                                        'mean_gene_expr_value': gene_expr.get('mean_gene_expr_value'),\n",
    "                                                                \n",
    "                                                    }\n",
    "                                                    matches.append(new_row)\n",
    "    return matches\n",
    "\n",
    "# Iterate over the CSV data and extract matched data\n",
    "for index, row in ftu_cell_count.iterrows():\n",
    "    if row['CL_ID']:  # Filter data by cell_id\n",
    "        matches = extract_data(row, json_data_enriched)\n",
    "        if matches:\n",
    "            enriched_v2_df = pd.concat([enriched_v2_df, pd.DataFrame(matches)], ignore_index=True)\n",
    "\n",
    "# Display the new dataframe\n",
    "print(enriched_v2_df.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "a544c722",
   "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>organ_id</th>\n",
       "      <th>reference_organ</th>\n",
       "      <th>cell_id</th>\n",
       "      <th>cell_label</th>\n",
       "      <th>annotation_method</th>\n",
       "      <th>as_label</th>\n",
       "      <th>sex</th>\n",
       "      <th>race</th>\n",
       "      <th>bmi</th>\n",
       "      <th>x_value</th>\n",
       "      <th>y_value</th>\n",
       "      <th>z_value</th>\n",
       "      <th>link</th>\n",
       "      <th>dataset</th>\n",
       "      <th>gene_id</th>\n",
       "      <th>gene_label</th>\n",
       "      <th>ensembl_id</th>\n",
       "      <th>mean_gene_expr_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15430</th>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0002113</td>\n",
       "      <td>RightKidneyV1.1</td>\n",
       "      <td>CL:1000718</td>\n",
       "      <td>Inner Medullary Collecting Duct</td>\n",
       "      <td>azimuth</td>\n",
       "      <td>renal pyramid</td>\n",
       "      <td>Female</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>53.06</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>https://portal.hubmapconsortium.org/browse/dat...</td>\n",
       "      <td>https://entity.api.hubmapconsortium.org/entiti...</td>\n",
       "      <td>HGNC:29665</td>\n",
       "      <td>MALAT1</td>\n",
       "      <td>ENSG00000251562.8</td>\n",
       "      <td>2165.678467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15431</th>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0002113</td>\n",
       "      <td>RightKidneyV1.1</td>\n",
       "      <td>CL:1000718</td>\n",
       "      <td>Inner Medullary Collecting Duct</td>\n",
       "      <td>azimuth</td>\n",
       "      <td>renal pyramid</td>\n",
       "      <td>Female</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>53.06</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>https://portal.hubmapconsortium.org/browse/dat...</td>\n",
       "      <td>https://entity.api.hubmapconsortium.org/entiti...</td>\n",
       "      <td>HGNC:18708</td>\n",
       "      <td>GRIP1</td>\n",
       "      <td>ENSG00000155974.12</td>\n",
       "      <td>30.792019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15432</th>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0002113</td>\n",
       "      <td>RightKidneyV1.1</td>\n",
       "      <td>CL:1000718</td>\n",
       "      <td>Inner Medullary Collecting Duct</td>\n",
       "      <td>azimuth</td>\n",
       "      <td>renal pyramid</td>\n",
       "      <td>Female</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>53.06</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>https://portal.hubmapconsortium.org/browse/dat...</td>\n",
       "      <td>https://entity.api.hubmapconsortium.org/entiti...</td>\n",
       "      <td>HGNC:494</td>\n",
       "      <td>ANK3</td>\n",
       "      <td>ENSG00000151150.22</td>\n",
       "      <td>54.934525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15433</th>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0002113</td>\n",
       "      <td>RightKidneyV1.1</td>\n",
       "      <td>CL:1000718</td>\n",
       "      <td>Inner Medullary Collecting Duct</td>\n",
       "      <td>azimuth</td>\n",
       "      <td>renal pyramid</td>\n",
       "      <td>Female</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>53.06</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>https://portal.hubmapconsortium.org/browse/dat...</td>\n",
       "      <td>https://entity.api.hubmapconsortium.org/entiti...</td>\n",
       "      <td>HGNC:26197</td>\n",
       "      <td>TOP6BL</td>\n",
       "      <td>ENSG00000173715.18</td>\n",
       "      <td>20.063810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15434</th>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0002113</td>\n",
       "      <td>RightKidneyV1.1</td>\n",
       "      <td>CL:1000718</td>\n",
       "      <td>Inner Medullary Collecting Duct</td>\n",
       "      <td>azimuth</td>\n",
       "      <td>renal pyramid</td>\n",
       "      <td>Female</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>53.06</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>https://portal.hubmapconsortium.org/browse/dat...</td>\n",
       "      <td>https://entity.api.hubmapconsortium.org/entiti...</td>\n",
       "      <td>HGNC:18610</td>\n",
       "      <td>KSR2</td>\n",
       "      <td>ENSG00000171435.14</td>\n",
       "      <td>27.016384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54455</th>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0001255</td>\n",
       "      <td>UrinaryBladder</td>\n",
       "      <td>CL:0000084</td>\n",
       "      <td>T cell</td>\n",
       "      <td>popv</td>\n",
       "      <td>fundus of urinary bladder</td>\n",
       "      <td>Female</td>\n",
       "      <td>White</td>\n",
       "      <td>unknown</td>\n",
       "      <td>15</td>\n",
       "      <td>17</td>\n",
       "      <td>5</td>\n",
       "      <td>https://portal.hubmapconsortium.org/browse/dat...</td>\n",
       "      <td>https://entity.api.hubmapconsortium.org/entiti...</td>\n",
       "      <td>ASCTB-TEMP:bcl11b</td>\n",
       "      <td>BCL11B</td>\n",
       "      <td>BCL11B</td>\n",
       "      <td>1.208149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54456</th>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0001255</td>\n",
       "      <td>UrinaryBladder</td>\n",
       "      <td>CL:0000084</td>\n",
       "      <td>T cell</td>\n",
       "      <td>popv</td>\n",
       "      <td>fundus of urinary bladder</td>\n",
       "      <td>Female</td>\n",
       "      <td>White</td>\n",
       "      <td>unknown</td>\n",
       "      <td>15</td>\n",
       "      <td>17</td>\n",
       "      <td>5</td>\n",
       "      <td>https://portal.hubmapconsortium.org/browse/dat...</td>\n",
       "      <td>https://entity.api.hubmapconsortium.org/entiti...</td>\n",
       "      <td>ASCTB-TEMP:spock2</td>\n",
       "      <td>SPOCK2</td>\n",
       "      <td>SPOCK2</td>\n",
       "      <td>0.922378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54457</th>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0001255</td>\n",
       "      <td>UrinaryBladder</td>\n",
       "      <td>CL:0000084</td>\n",
       "      <td>T cell</td>\n",
       "      <td>popv</td>\n",
       "      <td>fundus of urinary bladder</td>\n",
       "      <td>Female</td>\n",
       "      <td>White</td>\n",
       "      <td>unknown</td>\n",
       "      <td>15</td>\n",
       "      <td>17</td>\n",
       "      <td>5</td>\n",
       "      <td>https://portal.hubmapconsortium.org/browse/dat...</td>\n",
       "      <td>https://entity.api.hubmapconsortium.org/entiti...</td>\n",
       "      <td>ASCTB-TEMP:sytl3</td>\n",
       "      <td>SYTL3</td>\n",
       "      <td>SYTL3</td>\n",
       "      <td>1.291063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54458</th>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0001255</td>\n",
       "      <td>UrinaryBladder</td>\n",
       "      <td>CL:0000084</td>\n",
       "      <td>T cell</td>\n",
       "      <td>popv</td>\n",
       "      <td>fundus of urinary bladder</td>\n",
       "      <td>Female</td>\n",
       "      <td>White</td>\n",
       "      <td>unknown</td>\n",
       "      <td>15</td>\n",
       "      <td>17</td>\n",
       "      <td>5</td>\n",
       "      <td>https://portal.hubmapconsortium.org/browse/dat...</td>\n",
       "      <td>https://entity.api.hubmapconsortium.org/entiti...</td>\n",
       "      <td>ASCTB-TEMP:fth1</td>\n",
       "      <td>FTH1</td>\n",
       "      <td>FTH1</td>\n",
       "      <td>3.309898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54459</th>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0001255</td>\n",
       "      <td>UrinaryBladder</td>\n",
       "      <td>CL:0000084</td>\n",
       "      <td>T cell</td>\n",
       "      <td>popv</td>\n",
       "      <td>fundus of urinary bladder</td>\n",
       "      <td>Female</td>\n",
       "      <td>White</td>\n",
       "      <td>unknown</td>\n",
       "      <td>15</td>\n",
       "      <td>17</td>\n",
       "      <td>5</td>\n",
       "      <td>https://portal.hubmapconsortium.org/browse/dat...</td>\n",
       "      <td>https://entity.api.hubmapconsortium.org/entiti...</td>\n",
       "      <td>ASCTB-TEMP:ccnd3</td>\n",
       "      <td>CCND3</td>\n",
       "      <td>CCND3</td>\n",
       "      <td>1.627521</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9950 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            organ_id  reference_organ  \\\n",
       "15430  http://purl.obolibrary.org/obo/UBERON_0002113  RightKidneyV1.1   \n",
       "15431  http://purl.obolibrary.org/obo/UBERON_0002113  RightKidneyV1.1   \n",
       "15432  http://purl.obolibrary.org/obo/UBERON_0002113  RightKidneyV1.1   \n",
       "15433  http://purl.obolibrary.org/obo/UBERON_0002113  RightKidneyV1.1   \n",
       "15434  http://purl.obolibrary.org/obo/UBERON_0002113  RightKidneyV1.1   \n",
       "...                                              ...              ...   \n",
       "54455  http://purl.obolibrary.org/obo/UBERON_0001255   UrinaryBladder   \n",
       "54456  http://purl.obolibrary.org/obo/UBERON_0001255   UrinaryBladder   \n",
       "54457  http://purl.obolibrary.org/obo/UBERON_0001255   UrinaryBladder   \n",
       "54458  http://purl.obolibrary.org/obo/UBERON_0001255   UrinaryBladder   \n",
       "54459  http://purl.obolibrary.org/obo/UBERON_0001255   UrinaryBladder   \n",
       "\n",
       "          cell_id                       cell_label annotation_method  \\\n",
       "15430  CL:1000718  Inner Medullary Collecting Duct           azimuth   \n",
       "15431  CL:1000718  Inner Medullary Collecting Duct           azimuth   \n",
       "15432  CL:1000718  Inner Medullary Collecting Duct           azimuth   \n",
       "15433  CL:1000718  Inner Medullary Collecting Duct           azimuth   \n",
       "15434  CL:1000718  Inner Medullary Collecting Duct           azimuth   \n",
       "...           ...                              ...               ...   \n",
       "54455  CL:0000084                           T cell              popv   \n",
       "54456  CL:0000084                           T cell              popv   \n",
       "54457  CL:0000084                           T cell              popv   \n",
       "54458  CL:0000084                           T cell              popv   \n",
       "54459  CL:0000084                           T cell              popv   \n",
       "\n",
       "                        as_label     sex                       race      bmi  \\\n",
       "15430              renal pyramid  Female  Black or African American    53.06   \n",
       "15431              renal pyramid  Female  Black or African American    53.06   \n",
       "15432              renal pyramid  Female  Black or African American    53.06   \n",
       "15433              renal pyramid  Female  Black or African American    53.06   \n",
       "15434              renal pyramid  Female  Black or African American    53.06   \n",
       "...                          ...     ...                        ...      ...   \n",
       "54455  fundus of urinary bladder  Female                      White  unknown   \n",
       "54456  fundus of urinary bladder  Female                      White  unknown   \n",
       "54457  fundus of urinary bladder  Female                      White  unknown   \n",
       "54458  fundus of urinary bladder  Female                      White  unknown   \n",
       "54459  fundus of urinary bladder  Female                      White  unknown   \n",
       "\n",
       "      x_value y_value z_value  \\\n",
       "15430       3       5       2   \n",
       "15431       3       5       2   \n",
       "15432       3       5       2   \n",
       "15433       3       5       2   \n",
       "15434       3       5       2   \n",
       "...       ...     ...     ...   \n",
       "54455      15      17       5   \n",
       "54456      15      17       5   \n",
       "54457      15      17       5   \n",
       "54458      15      17       5   \n",
       "54459      15      17       5   \n",
       "\n",
       "                                                    link  \\\n",
       "15430  https://portal.hubmapconsortium.org/browse/dat...   \n",
       "15431  https://portal.hubmapconsortium.org/browse/dat...   \n",
       "15432  https://portal.hubmapconsortium.org/browse/dat...   \n",
       "15433  https://portal.hubmapconsortium.org/browse/dat...   \n",
       "15434  https://portal.hubmapconsortium.org/browse/dat...   \n",
       "...                                                  ...   \n",
       "54455  https://portal.hubmapconsortium.org/browse/dat...   \n",
       "54456  https://portal.hubmapconsortium.org/browse/dat...   \n",
       "54457  https://portal.hubmapconsortium.org/browse/dat...   \n",
       "54458  https://portal.hubmapconsortium.org/browse/dat...   \n",
       "54459  https://portal.hubmapconsortium.org/browse/dat...   \n",
       "\n",
       "                                                 dataset            gene_id  \\\n",
       "15430  https://entity.api.hubmapconsortium.org/entiti...         HGNC:29665   \n",
       "15431  https://entity.api.hubmapconsortium.org/entiti...         HGNC:18708   \n",
       "15432  https://entity.api.hubmapconsortium.org/entiti...           HGNC:494   \n",
       "15433  https://entity.api.hubmapconsortium.org/entiti...         HGNC:26197   \n",
       "15434  https://entity.api.hubmapconsortium.org/entiti...         HGNC:18610   \n",
       "...                                                  ...                ...   \n",
       "54455  https://entity.api.hubmapconsortium.org/entiti...  ASCTB-TEMP:bcl11b   \n",
       "54456  https://entity.api.hubmapconsortium.org/entiti...  ASCTB-TEMP:spock2   \n",
       "54457  https://entity.api.hubmapconsortium.org/entiti...   ASCTB-TEMP:sytl3   \n",
       "54458  https://entity.api.hubmapconsortium.org/entiti...    ASCTB-TEMP:fth1   \n",
       "54459  https://entity.api.hubmapconsortium.org/entiti...   ASCTB-TEMP:ccnd3   \n",
       "\n",
       "      gene_label          ensembl_id  mean_gene_expr_value  \n",
       "15430     MALAT1   ENSG00000251562.8           2165.678467  \n",
       "15431      GRIP1  ENSG00000155974.12             30.792019  \n",
       "15432       ANK3  ENSG00000151150.22             54.934525  \n",
       "15433     TOP6BL  ENSG00000173715.18             20.063810  \n",
       "15434       KSR2  ENSG00000171435.14             27.016384  \n",
       "...          ...                 ...                   ...  \n",
       "54455     BCL11B              BCL11B              1.208149  \n",
       "54456     SPOCK2              SPOCK2              0.922378  \n",
       "54457      SYTL3               SYTL3              1.291063  \n",
       "54458       FTH1                FTH1              3.309898  \n",
       "54459      CCND3               CCND3              1.627521  \n",
       "\n",
       "[9950 rows x 18 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "enriched_v2_df[enriched_v2_df.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "88d61a05",
   "metadata": {},
   "outputs": [],
   "source": [
    "enriched_v2_df = enriched_v2_df.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "68986f82",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(44570, 18)"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "enriched_v2_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "aa5c1d4b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['RightKidneyV1.1', 'RightKidney', 'LeftUreter', 'LeftKidneyV1.1',\n",
       "       'LeftKidney', 'Colon', 'Lung', 'Heart', 'HeartV1.1',\n",
       "       'UrinaryBladder', 'SmallIntestine'], dtype=object)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "enriched_v2_df['reference_organ'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f4585ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "enriched_v2_df['dataset'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19c6f1f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# revmove reference organ for which there is no FTU\n",
    "organ_no_ftu = ['LeftUreter', 'Heart', 'HeartV1.1', 'UrinaryBladder']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc408548",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_enriched_v2_df = enriched_v2_df[~enriched_v2_df['reference_organ'].isin(organ_no_ftu)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29cb5eea",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_enriched_v2_df['reference_organ'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fec69c06",
   "metadata": {},
   "source": [
    "##### Compare version 1 and version 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9e50e2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "print('The shape of the dataframe of version 1', filtered_enriched_v1_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd9fdef5",
   "metadata": {},
   "outputs": [],
   "source": [
    "print('The shape of the dataframe of version 1', filtered_enriched_v2_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ffe2d59",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a dataframe for comparison\n",
    "comparison_df = pd.DataFrame({\n",
    "    'In DF1': filtered_enriched_v1_df['dataset'].isin(filtered_enriched_v2_df['dataset']),\n",
    "    'In DF2': filtered_enriched_v2_df['dataset'].isin(filtered_enriched_v1_df['dataset'])\n",
    "})\n",
    "\n",
    "# Add columns to indicate presence in df1 and df2\n",
    "comparison_df['In DF1'] = comparison_df['In DF1'].map({True: 'Yes', False: 'No'})\n",
    "comparison_df['In DF2'] = comparison_df['In DF2'].map({True: 'Yes', False: 'No'})\n",
    "\n",
    "# Display the comparison dataframe\n",
    "print(comparison_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "593eb115",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find rows where the value is in DF1 but not in DF2\n",
    "in_df1_not_df2 = comparison_df[(comparison_df['In DF1'] == 'Yes') & (comparison_df['In DF2'].isna())]\n",
    "\n",
    "print(in_df1_not_df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76854d3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find rows where the value is in DF2 but not in DF1\n",
    "in_df2_not_df1 = comparison_df[(comparison_df['In DF2'] == 'Yes') & (comparison_df['In DF1'].isna())]\n",
    "\n",
    "print(in_df2_not_df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d03732b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Combine the differences into one DataFrame\n",
    "differences_df = pd.concat([in_df1_not_df2, in_df2_not_df1])\n",
    "\n",
    "print(differences_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a8dc26d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Count the number of differences\n",
    "num_differences = differences_df.shape[0]\n",
    "\n",
    "print(f\"Total number of differences: {num_differences}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ebe0a23",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Common columns to compare\n",
    "common_columns = ['organ_id', 'reference_organ', 'cell_id', 'cell_label',\n",
    "                  'annotation_method', 'as_label', 'sex', 'dataset', 'gene_id',\n",
    "                  'gene_label', 'ensembl_id', 'mean_gene_expr_value']\n",
    "\n",
    "# Merging dataframes on common columns\n",
    "merged_df = pd.merge(filtered_enriched_v1_df[common_columns], filtered_enriched_v2_df[common_columns], \n",
    "                     on=common_columns, how='outer', indicator=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4617aabb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Identifying matching and non-matching rows\n",
    "matching_rows = merged_df[merged_df['_merge'] == 'both']\n",
    "non_matching_rows_v1 = merged_df[merged_df['_merge'] == 'left_only']\n",
    "non_matching_rows_v2 = merged_df[merged_df['_merge'] == 'right_only']\n",
    "\n",
    "print(\"Matching rows:\")\n",
    "print(matching_rows.shape)\n",
    "\n",
    "print(\"\\nNon-matching rows in v1:\")\n",
    "print(non_matching_rows_v1.shape)\n",
    "\n",
    "print(\"\\nNon-matching rows in v2:\")\n",
    "print(non_matching_rows_v2.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bdfdfd9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "matching_rows['cell_label'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f01a04ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "non_matching_rows_v2[non_matching_rows_v2['as_label'].isin(non_matching_rows_v2['as_label'].unique())]['dataset'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b256111",
   "metadata": {},
   "outputs": [],
   "source": [
    "non_matching_rows_v1[non_matching_rows_v1['as_label'].isin(non_matching_rows_v1['as_label'].unique())]['dataset'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96cf8223",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_enriched_v2_df['dataset'].isin(filtered_enriched_v1_df['dataset']).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c00acfcd",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_enriched_v2_df['cell_label'].isin(filtered_enriched_v1_df['cell_label']).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5324a20b",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_enriched_v2_df['cell_label'].isin(filtered_enriched_v1_df['cell_label']).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b44a6a13",
   "metadata": {},
   "outputs": [],
   "source": [
    "enriched_v2_df.to_csv('output/CTs-with-datasets-with-gene-information-w-heart-UB.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb96f3e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_enriched_v2_df.to_csv('output/CTs-with-datasets-with-gene-information-wo-heart-UB.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa094179",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get count of cells per cell_label per organ_name per dataset\n",
    "cell_counts = filtered_enriched_v2_df.groupby(['dataset', 'reference_organ', 'cell_label']).size().reset_index(name='cell_count')\n",
    "cell_counts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a2a64b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the results to a CSV file\n",
    "cell_counts.to_csv('output/cell_counts_per_cell_label_per_organ_per_dataset.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54b46188",
   "metadata": {},
   "source": [
    "##### Sanity check of the organ_id, cell_source_label and cell_ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13cfb719",
   "metadata": {},
   "outputs": [],
   "source": [
    "CTs_with_datasets_df['cell_source_label'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51d587b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "enriched_v2_df['as_label'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7059ff04",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_enriched_v2_df['as_label'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88a3ca83",
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = ['AS_Label', 'Unique CTs Count']\n",
    "as_label_CTs_info = pd.DataFrame(columns=columns)\n",
    "\n",
    "# Calculate the number of unique cell types for each AS_Label\n",
    "for label in list(filtered_enriched_v2_df['as_label'].unique()):\n",
    "    unique_cts_count = filtered_enriched_v2_df[filtered_enriched_v2_df['as_label'] == label]['cell_label'].nunique()\n",
    "    row = pd.DataFrame({'AS_Label': [label], 'Unique CTs Count': [unique_cts_count]})\n",
    "    as_label_CTs_info = pd.concat([as_label_CTs_info, row], ignore_index=True)\n",
    "\n",
    "as_label_CTs_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ed88380",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plotting the distribution with counts displayed on the bars\n",
    "plt.figure(figsize=(10, 6))\n",
    "bars = plt.bar(as_label_CTs_info['AS_Label'], as_label_CTs_info['Unique CTs Count'], color='skyblue')\n",
    "\n",
    "# Adding the count labels on top of each bar\n",
    "for bar in bars:\n",
    "    height = bar.get_height()\n",
    "    plt.text(bar.get_x() + bar.get_width() / 2, height, str(height), ha='center', va='bottom')\n",
    "\n",
    "plt.xlabel('AS_Label')\n",
    "plt.ylabel('Number of Unique Cell Types')\n",
    "plt.title('Distribution of Unique Cell Types per AS_Label')\n",
    "plt.xticks(rotation=45, ha='right')\n",
    "plt.tight_layout()\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6616dbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_enriched_v2_df['reference_organ'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e19c2280",
   "metadata": {},
   "outputs": [],
   "source": [
    "difference_labels = set(CTs_with_datasets_df['cell_source_label'].unique()) - set(enriched_v1_df['as_label'])\n",
    "print(\"Difference in cell_source_label:\")\n",
    "print(difference_labels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1576271",
   "metadata": {},
   "outputs": [],
   "source": [
    "difference_source = set(CTs_with_datasets_df['cell_source_label'].unique()) - set(enriched_v2_df['as_label'])\n",
    "print(\"Difference in cell_source_label:\")\n",
    "print(difference_source)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21a10d0a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract cell_label and AS_label information from the JSON-LD data for source that are not found in enriched dataset\n",
    "extracted_info = []\n",
    "label_info = []\n",
    "for label in difference_source:\n",
    "    for entry in json_data_enriched:\n",
    "        for entry in json_data_enriched[\"@graph\"]:\n",
    "            for sample in entry.get('samples', []):\n",
    "                rui_location = sample.get('rui_location', {})\n",
    "                all_collisions = rui_location.get('all_collisions', [])\n",
    "                for collision in all_collisions:\n",
    "                    collisions = collision.get('collisions', [])\n",
    "                    for col in collisions:\n",
    "                        as_label = col.get('as_label', 'unknown')\n",
    "                        if as_label == label:\n",
    "                            for section in sample.get('sections', []):\n",
    "                                for dataset in section.get('datasets', []):\n",
    "                                    summaries = dataset.get('summaries', [])                            \n",
    "                                    for summary in summaries:\n",
    "                                        for summ in summary['summary']:\n",
    "                                            extracted_info.append({'as_label': as_label,\n",
    "                                                            'cell_label': summ.get('cell_label', 'N/A')\n",
    "                                                         })\n",
    "\n",
    "# Convert the extracted information into a DataFrame for better visualization\n",
    "df_extracted_info = pd.DataFrame(extracted_info)\n",
    "\n",
    "df_extracted_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "862f19ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_extracted_info['as_label'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7850cb5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_extracted_info['cell_label'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63f059a9",
   "metadata": {},
   "source": [
    "#### AS_Label to FTU mapping - Long table\n",
    "\n",
    "\n",
    "| Cell Source Label                              | Corresponding FTU                  |\n",
    "|------------------------------------------------|------------------------------------|\n",
    "| renal pyramid                                  | Nephron, Inner Medullary Collecting Duct, Thick Ascending Limb, Ascending Thin Limb, Descending Thin Limb, Outer Medullary Collecting Duct    |\n",
    "| outer cortex of kidney                         | Nephron, Renal Corpuscle, Cortical Collecting Duct, Outer Medullary Collecting Duct    |\n",
    "| renal column                                   | Nephron, cortical collecting duct, Descending Thin Limb, Thick Ascending Limb, Ascending Thin Limb, Renal Corpuscle    |\n",
    "| renal papilla                                  | Not applicable to listed FTUs    |\n",
    "| left ureter                                    | Not applicable to listed FTUs      |\n",
    "| hilum of kidney                                | Not applicable to listed FTUs      |\n",
    "| kidney capsule                                 | Not applicable to listed FTUs      |\n",
    "| descending colon                               | Crypt Of Lieberkuhn of large intestine |\n",
    "| sigmoid colon                                  | Crypt Of Lieberkuhn of large intestine |\n",
    "| rectum                                         | Crypt Of Lieberkuhn of large intestine |\n",
    "| ascending colon                                | Crypt Of Lieberkuhn of large intestine |\n",
    "| transverse colon                               | Crypt Of Lieberkuhn of large intestine |\n",
    "| Right Medial Bronchopulmonary Segment          | Bronchial Submucosal Gland, Pulmonary Alveolus |\n",
    "| Right Lateral Bronchopulmonary Segment         | Bronchial Submucosal Gland, Pulmonary Alveolus |\n",
    "| gastric impression of liver                    | Liver Lobule                       |\n",
    "| diaphragmatic surface of liver                 | Liver Lobule                       |\n",
    "| capsule of the liver                           | Liver Lobule                       |\n",
    "| jejunum                                        | Villus                             |\n",
    "| superior part of duodenum                      | Villus                             |\n",
    "| descending part of duodenum                    | Villus                             |\n",
    "| ascending part of duodenum                     | Villus                             |\n",
    "| horizontal part of duodenum                    | Villus                             |\n",
    "| distal part of ileum                           | Villus                             |\n",
    "| ileum                                          | Villus                             |\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8a055ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "cell_source_to_ftu_mapping = {\n",
    "    \"renal pyramid\": [\"Nephron\", \"Inner Medullary Collecting Duct\", \"Thick Ascending Limb Of Loop Of Henle\", \"Loop Of Henle Ascending Limb Thin Segment\", \"Descending Limb of Loop of Henle\", \"Outer Medullary Collecting Duct\"],\n",
    "    \"outer cortex of kidney\": [\"Nephron\", \"Renal Corpuscle\", \"Cortical Collecting Duct\", \"Outer Medullary Collecting Duct\"],\n",
    "    \"renal column\": [\"Nephron\", \"Cortical Collecting Duct\", \"Descending Limb of Loop of Henle\", \"Thick Ascending Limb Of Loop Of Henle\", \"Loop Of Henle Ascending Limb Thin Segment\", \"Renal Corpuscle\"],\n",
    "    \"renal papilla\": None, # Not applicable to listed FTUs\n",
    "    \"hilum of kidney\": None,  # Not applicable to listed FTUs\n",
    "    \"kidney capsule\": None,  # Not applicable to listed FTUs\n",
    "    \"descending colon\": [\"Crypt Of Lieberkuhn of large intestine\"],\n",
    "    \"sigmoid colon\": [\"Crypt Of Lieberkuhn of large intestine\"],\n",
    "    \"rectum\": [\"Crypt Of Lieberkuhn of large intestine\"],\n",
    "    \"ascending colon\": [\"Crypt Of Lieberkuhn of large intestine\"],\n",
    "    \"transverse colon\": [\"Crypt Of Lieberkuhn of large intestine\"],\n",
    "    \"Right Medial Bronchopulmonary Segment\": [\"Bronchial Submucosal Gland\", \"Alveolus of Lung\"],\n",
    "    \"Right Lateral Bronchopulmonary Segment\": [\"Bronchial Submucosal Gland\", \"Alveolus of Lung\"],\n",
    "    \"gastric impression of liver\": [\"Liver Lobule\"],\n",
    "    \"diaphragmatic surface of liver\": [\"Liver Lobule\"],\n",
    "    \"capsule of the liver\": [\"Liver Lobule\"],\n",
    "    \"jejunum\": [\"Villus\"],\n",
    "    \"superior part of duodenum\": [\"Villus\"],\n",
    "    \"descending part of duodenum\": [\"Villus\"],\n",
    "    \"ascending part of duodenum\": [\"Villus\"],\n",
    "    \"horizontal part of duodenum\": [\"Villus\"],\n",
    "    \"distal part of ileum\": [\"Villus\"],\n",
    "    \"ileum\": [\"Villus\"]\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c9f56f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "cell_source_with_no_ftu = [key for key, value in cell_source_to_ftu_mapping.items() if value is None]\n",
    "cell_source_with_no_ftu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c965b5fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_v1_enriched_v2_df = filtered_enriched_v2_df[~filtered_enriched_v2_df['as_label'].isin(cell_source_with_no_ftu)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2633a076",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(filtered_v1_enriched_v2_df.shape, filtered_enriched_v2_df.shape, filtered_enriched_v1_df.shape, CTs_with_datasets_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "064682a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "villus = filtered_v1_enriched_v2_df[filtered_v1_enriched_v2_df['reference_organ'] == 'SmallIntestine']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a45c0396",
   "metadata": {},
   "outputs": [],
   "source": [
    "gene_counts = villus.groupby(['reference_organ', 'cell_label', 'dataset'])['gene_id'].count().reset_index()\n",
    "\n",
    "# Renaming the column for clarity\n",
    "gene_counts.rename(columns={'gene_id': 'gene_count'}, inplace=True)\n",
    "\n",
    "# Simplify the x-axis labels by removing URLs and focusing on cell types and datasets\n",
    "gene_counts['simple_label'] = gene_counts['cell_label'] + ' (' + gene_counts['dataset'].str.split('/').str[-1] + ')'\n",
    "\n",
    "# Plotting with Seaborn to improve presentation\n",
    "plt.figure(figsize=(14, 8))\n",
    "sns.barplot(x='simple_label', y='gene_count', hue='reference_organ', data=gene_counts, dodge=False)\n",
    "\n",
    "plt.xlabel('Cell Type (Dataset)')\n",
    "plt.ylabel('Number of Genes')\n",
    "plt.title('Number of Genes per Cell Type per Dataset per Reference Organ')\n",
    "plt.xticks(rotation=90)\n",
    "plt.legend(title='Reference Organ', bbox_to_anchor=(1.05, 1), loc='upper left')\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1269f175",
   "metadata": {},
   "outputs": [],
   "source": [
    "genes_per_cell_type = villus.groupby('cell_label')['gene_id'].nunique().reset_index()\n",
    "genes_per_cell_type.rename(columns={'gene_id': 'unique_gene_count'}, inplace=True)\n",
    "\n",
    "# Plotting the number of genes for each cell type\n",
    "plt.figure(figsize=(14, 8))\n",
    "sns.barplot(x='cell_label', y='unique_gene_count', data=genes_per_cell_type, color='skyblue')\n",
    "\n",
    "plt.xlabel('Cell Type')\n",
    "plt.ylabel('Number of Unique Genes')\n",
    "plt.title('Number of Unique Genes per Cell Type')\n",
    "plt.xticks(rotation=90)\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c9961b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(14, 8))\n",
    "sns.boxplot(x='cell_label', y='mean_gene_expr_value', hue='reference_organ', data=villus)\n",
    "\n",
    "plt.xlabel('Cell Type')\n",
    "plt.ylabel('Mean Gene Expression Value')\n",
    "plt.title('Distribution of Mean Gene Expression Values per Cell Type Grouped by Reference Organ')\n",
    "plt.xticks(rotation=90)\n",
    "plt.legend(title='Reference Organ', bbox_to_anchor=(1.05, 1), loc='upper left')\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "206e64e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a box plot\n",
    "plt.figure(figsize=(12, 8))\n",
    "sns.boxplot(x='as_label', y='mean_gene_expr_value', hue='reference_organ', data=filtered_v1_enriched_v2_df)\n",
    "\n",
    "# Customize the plot\n",
    "plt.title('Mean Expression Value per Cell Type per FTU Name per Annotation Method')\n",
    "plt.xlabel('as_label')\n",
    "plt.ylabel('Mean Expression Value')\n",
    "plt.xticks(rotation=90)\n",
    "plt.legend(title='Organ', bbox_to_anchor=(1, 1), loc='upper left')\n",
    "\n",
    "# Save the plot to a file\n",
    "#output_file_path = 'output/vasculature/mean-expression-box-plot-per-CT-per-FTU-per-AnnMethod.png'\n",
    "plt.tight_layout()\n",
    "#plt.savefig(output_file_path)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35c85776",
   "metadata": {},
   "outputs": [],
   "source": [
    "def map_ftu(row):\n",
    "    as_label = row['as_label']\n",
    "    cell_id = row['cell_id']\n",
    "    organ_id = row['organ_id']  # Using 'reference_organ' instead of 'organ_id'\n",
    "\n",
    "    ftu_options = cell_source_to_ftu_mapping.get(as_label, None)\n",
    "\n",
    "    if ftu_options is None:\n",
    "        return None\n",
    "\n",
    "    # If only one FTU, return it\n",
    "    if len(ftu_options) == 1:\n",
    "        return ftu_options[0]\n",
    "\n",
    "    # Otherwise, match based on cell_id and organ\n",
    "    filtered_ftu = ftu_cell_count_filtered[(ftu_cell_count_filtered['CL_ID'] == cell_id) & (ftu_cell_count_filtered['Organ_ID'].str.lower() == organ_id.lower())]\n",
    "    if not filtered_ftu.empty:\n",
    "        matching_ftu = filtered_ftu[filtered_ftu['FTU Label in Uberon'].isin(ftu_options)]\n",
    "        if not matching_ftu.empty:\n",
    "            return matching_ftu['FTU Label in Uberon'].values[0]\n",
    "    \n",
    "    return None\n",
    "\n",
    "# Applying the corrected mapping function to create the new \"FTU\" column\n",
    "filtered_v1_enriched_v2_df['FTU'] = filtered_v1_enriched_v2_df.apply(map_ftu, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41f5af47",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_v1_enriched_v2_df['FTU'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c2ed445",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_v1_enriched_v2_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fae4fd61",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_v1_enriched_v2_df[filtered_v1_enriched_v2_df['FTU'] == None]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7d67f35",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_v1_enriched_v2_df['FTU'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5aa5fbdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rearrange the columns\n",
    "desired_order = ['reference_organ', 'organ_id', 'as_label', 'sex', 'race', 'bmi', 'annotation_method', 'FTU',\n",
    "                 'cell_id', 'cell_label', 'gene_id', 'gene_label', 'ensembl_id', 'mean_gene_expr_value', 'link',\n",
    "                 'dataset', 'x_value','y_value', 'z_value']\n",
    "\n",
    "# Check if all desired columns are in the dataframe\n",
    "existing_columns = [col for col in desired_order if col in filtered_v1_enriched_v2_df.columns]\n",
    "\n",
    "# Rearrange columns in the dataframe\n",
    "filtered_v1_enriched_v2_df_rearranged = filtered_v1_enriched_v2_df[existing_columns]\n",
    "filtered_v1_enriched_v2_df_rearranged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e757364d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the combined dataframe to a CSV file\n",
    "output_path = 'output/FTU-CTs-with-datasets-with-gene-information-wo-heart-UB.csv'\n",
    "filtered_v1_enriched_v2_df_rearranged.to_csv(output_path, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3635b640",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge the two dataframes based on 'unique_dataset_id' from sankey_df and 'dataset' from ftu_cts_df\n",
    "sankey_for_ftu = pd.merge(sankey, filtered_v1_enriched_v2_df_rearranged, left_on='unique_dataset_id', right_on='dataset', how='inner')\n",
    "\n",
    "# Define the columns to retain in the new file\n",
    "columns_to_retain = [\n",
    "    'portal', 'study_paper', 'doi', 'lead_author', 'is_azimuth_reference',\n",
    "    'donor_id', 'donor_sex', 'donor_age', 'donor_development_stage', 'donor_race',\n",
    "    'donor_bmi', 'organ_name', 'organ_name_glb_file', 'tissue_block_id', \n",
    "    'tissue_block_volume', 'collisions_bb', 'collisions_mesh',\n",
    "    'list_of_colliding_anatomical_structures_bb', \n",
    "    'list_of_colliding_anatomical_structures_mesh', \n",
    "    'list_of_colliding_anatomical_structures_bb_ids', \n",
    "    'list_of_colliding_anatomical_structures_mesh_ids', 'FTU', 'dataset_id', \n",
    "    'unique_dataset_id', 'link_to_h5ad_file', 'sc_transcriptomics_or_sc_proteomics', \n",
    "    'cell_type_annotation_tool', 'omap_id', 'number_of_cells_total', \n",
    "    'number_of_unique_cell_types', 'hubmap_dataset_publication_status', \n",
    "    'is_rui_registered', 'is_atlas_dataset'\n",
    "]\n",
    "\n",
    "# Filter the merged dataframe to keep only the necessary columns\n",
    "filtered_sankey_for_ftu = sankey_for_ftu[columns_to_retain]\n",
    "filtered_sankey_for_ftu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d743abc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_sankey_for_ftu.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "912bd6b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = filtered_sankey_for_ftu.dtypes\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68431441",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check for duplicates in the new dataset\n",
    "duplicates = filtered_sankey_for_ftu[filtered_sankey_for_ftu.duplicated()]\n",
    "duplicates.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02781f84",
   "metadata": {},
   "outputs": [],
   "source": [
    "deduplicated_df = filtered_sankey_for_ftu.drop_duplicates()\n",
    "deduplicated_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf772b3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "deduplicated_df.to_csv('output/sankey_for_FTU.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c5c21e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = filtered_v1_enriched_v2_df\n",
    "\n",
    "# 1. Distribution of FTUs across organs\n",
    "plt.figure(figsize=(12, 6))\n",
    "sns.countplot(y='FTU', data=data, order=data['FTU'].value_counts().index)\n",
    "plt.title('Distribution of FTUs Across the Dataset')\n",
    "plt.xlabel('Count')\n",
    "plt.ylabel('FTU')\n",
    "plt.show()\n",
    "\n",
    "# 2. Distribution of gene expression values\n",
    "plt.figure(figsize=(12, 6))\n",
    "sns.boxplot(x='reference_organ', y='mean_gene_expr_value', data=data)\n",
    "plt.title('Distribution of Gene Expression Values Across Reference Organs')\n",
    "plt.xlabel('Reference Organ')\n",
    "plt.ylabel('Mean Gene Expression Value')\n",
    "plt.xticks(rotation=45, ha='right')\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "# 3. Number of unique cell types per organ\n",
    "unique_cell_types = data.groupby('reference_organ')['cell_label'].nunique().reset_index()\n",
    "plt.figure(figsize=(12, 6))\n",
    "sns.barplot(x='reference_organ', y='cell_label', data=unique_cell_types)\n",
    "plt.title('Number of Unique Cell Types per Reference Organ')\n",
    "plt.xlabel('Reference Organ')\n",
    "plt.ylabel('Number of Unique Cell Types')\n",
    "plt.xticks(rotation=45, ha='right')\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "# 4. Scatter plot of gene expression vs. FTU\n",
    "plt.figure(figsize=(12, 6))\n",
    "sns.scatterplot(x='FTU', y='mean_gene_expr_value', data=data, hue='reference_organ')\n",
    "plt.title('Gene Expression Values Across FTUs')\n",
    "plt.xlabel('FTU')\n",
    "plt.ylabel('Mean Gene Expression Value')\n",
    "plt.xticks(rotation=45, ha='right')\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "# Number of genes per cell type per dataset per FTU\n",
    "genes_per_cell_type_ftu = data.groupby(['FTU', 'cell_label', 'dataset'])['gene_label'].nunique().reset_index()\n",
    "genes_per_cell_type_ftu.rename(columns={'gene_label': 'unique_gene_count'}, inplace=True)\n",
    "\n",
    "\n",
    "# 5. Box Plot: Number of genes per cell type per dataset per FTU\n",
    "plt.figure(figsize=(14, 8))\n",
    "sns.boxplot(x='FTU', y='unique_gene_count', data=genes_per_cell_type_ftu)\n",
    "plt.title('Number of Genes per Cell Type per Dataset per FTU')\n",
    "plt.xlabel('FTU')\n",
    "plt.ylabel('Number of Unique Genes')\n",
    "plt.xticks(rotation=45, ha='right')\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "# Number of datasets per FTU\n",
    "datasets_per_ftu = data.groupby('FTU')['dataset'].nunique().reset_index()\n",
    "datasets_per_ftu.rename(columns={'dataset': 'unique_dataset_count'}, inplace=True)\n",
    "\n",
    "# 6. Violin Plot: Number of datasets per FTU\n",
    "plt.figure(figsize=(14, 8))\n",
    "sns.violinplot(x='FTU', y='unique_dataset_count', data=datasets_per_ftu)\n",
    "plt.title('Distribution of Datasets per FTU')\n",
    "plt.xlabel('FTU')\n",
    "plt.ylabel('Number of Datasets')\n",
    "plt.xticks(rotation=45, ha='right')\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "# Number of genes per FTU per annotation method\n",
    "genes_per_ftu_annotation = data.groupby(['FTU', 'annotation_method'])['gene_label'].nunique().reset_index()\n",
    "genes_per_ftu_annotation.rename(columns={'gene_label': 'unique_gene_count'}, inplace=True)\n",
    "\n",
    "# 7. Box Plot: Number of genes per FTU per annotation method\n",
    "plt.figure(figsize=(14, 8))\n",
    "sns.boxplot(x='FTU', y='unique_gene_count', hue='annotation_method', data=genes_per_ftu_annotation)\n",
    "plt.title('Number of Genes per FTU per Annotation Method')\n",
    "plt.xlabel('FTU')\n",
    "plt.ylabel('Number of Unique Genes')\n",
    "plt.xticks(rotation=45, ha='right')\n",
    "plt.legend(title='Annotation Method', bbox_to_anchor=(1.05, 1), loc='upper left')\n",
    "plt.tight_layout()\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ffd8652",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 8. Violin Plot: Distribution of gene expression values per cell type\n",
    "plt.figure(figsize=(14, 8))\n",
    "sns.violinplot(x='cell_label', y='mean_gene_expr_value', data=data, inner='box', scale='width')\n",
    "plt.title('Distribution of Gene Expression Values per Cell Type')\n",
    "plt.xlabel('Cell Type')\n",
    "plt.ylabel('Mean Gene Expression Value')\n",
    "plt.xticks(rotation=90)\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "# 9. Heatmap: Gene expression across different FTUs and cell types\n",
    "plt.figure(figsize=(14, 8))\n",
    "heatmap_data = data.pivot_table(index='FTU', columns='cell_label', values='mean_gene_expr_value', aggfunc='mean')\n",
    "sns.heatmap(heatmap_data, cmap='viridis', linewidths=0.5)\n",
    "plt.title('Heatmap of Mean Gene Expression Across FTUs and Cell Types')\n",
    "plt.xlabel('Cell Type')\n",
    "plt.ylabel('FTU')\n",
    "plt.xticks(rotation=90)\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0998f9f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare the data\n",
    "sankey_data = data.groupby(['annotation_method', 'FTU', 'cell_label', 'gene_label']).size().reset_index(name='count')\n",
    "\n",
    "# Create labels for the nodes\n",
    "all_labels = list(sankey_data['annotation_method'].unique()) + \\\n",
    "             list(sankey_data['FTU'].unique()) + \\\n",
    "             list(sankey_data['cell_label'].unique()) + \\\n",
    "             list(sankey_data['gene_label'].unique())\n",
    "\n",
    "label_map = {label: i for i, label in enumerate(all_labels)}\n",
    "\n",
    "# Define the source, target, and values for the Sankey diagram\n",
    "sources = sankey_data['annotation_method'].map(label_map).tolist() + \\\n",
    "          sankey_data['FTU'].map(label_map).tolist() + \\\n",
    "          sankey_data['cell_label'].map(label_map).tolist()\n",
    "\n",
    "targets = sankey_data['FTU'].map(label_map).tolist() + \\\n",
    "          sankey_data['cell_label'].map(label_map).tolist() + \\\n",
    "          sankey_data['gene_label'].map(label_map).tolist()\n",
    "\n",
    "values = sankey_data['count'].tolist() * 3  # repeating the counts for each layer\n",
    "\n",
    "# Create the Sankey diagram\n",
    "sankey_fig = go.Figure(go.Sankey(\n",
    "    node=dict(\n",
    "        pad=15,\n",
    "        thickness=20,\n",
    "        line=dict(color=\"black\", width=0.5),\n",
    "        label=all_labels\n",
    "    ),\n",
    "    link=dict(\n",
    "        source=sources,\n",
    "        target=targets,\n",
    "        value=values\n",
    "    )\n",
    "))\n",
    "\n",
    "sankey_fig.update_layout(title_text=\"Detailed Sankey Diagram: Annotation Method to FTU, Cell Type, Gene Label\", font_size=10)\n",
    "sankey_fig.write_image(\"sankey_diagram_high_res.png\", width=1920, height=1080)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d2a7b9b",
   "metadata": {},
   "source": [
    "### Organ and CTann level information as per CTann crosswalk files "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61970c26",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the unique organ levels for each file\n",
    "azimuth_organs = azimuth['Organ_Level'].unique().tolist()\n",
    "celltypist_organs = celltypist['Organ_Level'].unique().tolist()\n",
    "popv_organs = popv['Organ_Level'].unique().tolist()\n",
    "\n",
    "print(\"Azimuth Organs:\", azimuth_organs)\n",
    "print()\n",
    "print(\"Celltypist Organs:\", celltypist_organs)\n",
    "print()\n",
    "print(\"Popv Organs:\", popv_organs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aaa4754d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename the column in final_output_df\n",
    "CTs_with_datasets_df.rename(columns={'cell_id': 'CL_ID'}, inplace=True)\n",
    "\n",
    "# Merge with azimuth_df\n",
    "final_output_azimuth = CTs_with_datasets_df[CTs_with_datasets_df['annotation_method'] == 'azimuth'].merge(\n",
    "    azimuth[['CL_ID', 'Organ_Level']], on='CL_ID', how='left')\n",
    "\n",
    "# Merge with celltypist_df\n",
    "final_output_celltypist = CTs_with_datasets_df[CTs_with_datasets_df['annotation_method'] == 'celltypist'].merge(\n",
    "    celltypist[['CL_ID', 'Organ_Level']], on='CL_ID', how='left')\n",
    "\n",
    "# Merge with popv_df\n",
    "final_output_popv = CTs_with_datasets_df[CTs_with_datasets_df['annotation_method'] == 'popv'].merge(\n",
    "    popv[['CL_ID', 'Organ_Level']], on='CL_ID', how='left')\n",
    "\n",
    "# Combine all the dataframes\n",
    "final_output_combined = pd.concat([final_output_azimuth, final_output_celltypist, final_output_popv], ignore_index=True)\n",
    "\n",
    "# Define the organ levels for each annotation method\n",
    "azimuth_organs = ['Kidney_L3', 'Lung_v2_finest_level', 'Liver_L2', 'Liver_L1', 'Kidney_L1',  'Kidney_L2', \n",
    "                  'Lung_v2_L1', 'Lung_v2_L2', 'Lung_v2_L3', 'Lung_v2_L4', 'Lung_v2_L5', 'Pancreas_L1']\n",
    "celltypist_organs = ['intestine_L1', 'kidney_L1', 'liver_L1', 'lung_L1', 'pancreas_L1', 'spleen_L1', \n",
    "                     'Adult_Human_Skin_pkl', 'Healthy_Human_Liver_pkl', 'Adult_Human_PancreaticIslet_pkl', \n",
    "                     'Human_Lung_Atlas_pkl']\n",
    "popv_organs = ['large intestine', 'liver', 'lung', 'male reproductive system', 'pancreas', 'prostate gland', \n",
    "               'respiratory system', 'skin', 'small intestine', 'spleen', 'thymus']\n",
    "\n",
    "# Filter the combined dataframe for each annotation method and their corresponding organ levels\n",
    "filtered_azimuth = final_output_combined[(final_output_combined['annotation_method'] == 'azimuth') & \n",
    "                                         (final_output_combined['Organ_Level'].isin(azimuth_organs))]\n",
    "\n",
    "filtered_celltypist = final_output_combined[(final_output_combined['annotation_method'] == 'celltypist') & \n",
    "                                            (final_output_combined['Organ_Level'].isin(celltypist_organs))]\n",
    "\n",
    "filtered_popv = final_output_combined[(final_output_combined['annotation_method'] == 'popv') & \n",
    "                                      (final_output_combined['Organ_Level'].isin(popv_organs))]\n",
    "\n",
    "# Combine the filtered dataframes\n",
    "final_filtered_combined = pd.concat([filtered_azimuth, filtered_celltypist, filtered_popv], ignore_index=True)\n",
    "\n",
    "final_filtered_combined.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0028f3fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the combined dataframe to a CSV file\n",
    "output_path = 'output/CTs-with-datasets-with-organ.csv'\n",
    "final_filtered_combined.to_csv(output_path, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c71c047",
   "metadata": {},
   "source": [
    "###  Extract gene information for Cell types in vasculature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f761ed7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Provided CT Labels and CT IDs\n",
    "ct_labels_ids = {\n",
    "    \"glomerular capillary endothelial cell\": \"CL:1001005\",\n",
    "    \"efferent arteriole endothelial cell\": \"CL:1001099\",\n",
    "    \"afferent arteriole endothelial cell\": \"CL:1001096\",\n",
    "    \"peritubular capillary endothelial cell\": \"CL:1001033\",\n",
    "    \"vasa recta ascending limb cell\": \"CL:1001131\",\n",
    "    \"vasa recta descending limb cell\": \"CL:1001285\",\n",
    "    \"alveolar capillary type 1 endothelial cell\": \"CL:4028002\",\n",
    "    \"capillary endothelial cell\": \"CL:0002144\",\n",
    "    \"blood vessel smooth muscle cell\": \"CL:0019018\",\n",
    "    \"endothelial cell of artery\": \"CL:1000413\",\n",
    "    \"vein endothelial cell\": \"CL:0002543\",\n",
    "    \"endothelial cell of hepatic sinusoid\": \"CL:1000398\",\n",
    "    \"prostate gland microvascular endothelial cell\": \"CL:2000059\",\n",
    "    \"blood vessel endothelial cell\": \"CL:0000071\",\n",
    "    \"splenic endothelial cell\": \"CL:2000053\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ca9410e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Identifying the structure within the \"@graph\" key to process accordingly\n",
    "graph_data = json_data_enriched[\"@graph\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88dc22be",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Grouping ensembl_ids by cell_id and adding cell_label\n",
    "grouped_matches = {}\n",
    "for item in graph_data:\n",
    "    samples = item.get(\"samples\", [])\n",
    "    for sample in samples:\n",
    "        sections = sample.get(\"sections\", [])\n",
    "        for section in sections:\n",
    "            datasets = section.get(\"datasets\", [])\n",
    "            for dataset in datasets:\n",
    "                summaries = dataset.get(\"summaries\", [])\n",
    "                for summary in summaries:\n",
    "                    sum_details = summary.get(\"summary\", [])\n",
    "                    for sum_detail in sum_details:\n",
    "                        cell_id = sum_detail.get(\"cell_id\")\n",
    "                        gene_expr_list = sum_detail.get(\"gene_expr\", [])\n",
    "                        if cell_id in ct_labels_ids.values():\n",
    "                            if isinstance(gene_expr_list, list):\n",
    "                                for gene_expr in gene_expr_list:\n",
    "                                    if isinstance(gene_expr, dict):\n",
    "                                        ensembl_id = gene_expr.get(\"ensembl_id\")\n",
    "                                        if cell_id not in grouped_matches:\n",
    "                                            grouped_matches[cell_id] = {\"cell_label\": \"\", \"ensembl_ids\": []}\n",
    "                                        grouped_matches[cell_id][\"cell_label\"] = [label for label, id in ct_labels_ids.items() if id == cell_id][0]\n",
    "                                        grouped_matches[cell_id][\"ensembl_ids\"].append(ensembl_id)\n",
    "\n",
    "# Preparing the final dataframe\n",
    "final_matches = []\n",
    "for cell_id, details in grouped_matches.items():\n",
    "    final_matches.append({\n",
    "        \"cell_id\": cell_id,\n",
    "        \"cell_label\": details[\"cell_label\"],\n",
    "        \"ensembl_ids\": \", \".join(details[\"ensembl_ids\"])\n",
    "    })\n",
    "\n",
    "final_matches_df = pd.DataFrame(final_matches)\n",
    "final_matches_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5581f87",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Displaying the final dataframe\n",
    "final_matches_df.to_csv('output/vasculature/Biomarker_for_Vasculature_CTs.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f66ff2e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_matches_df['cell_id'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cd987cc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Grouping ensembl_ids by cell_id and organ_id, adding cell_label and mean_gene_expr_value\n",
    "grouped_matches = {}\n",
    "for item in graph_data:\n",
    "    samples = item.get(\"samples\", [])\n",
    "    for sample in samples:\n",
    "        sections = sample.get(\"sections\", [])\n",
    "        for section in sections:\n",
    "            datasets = section.get(\"datasets\", [])\n",
    "            for dataset in datasets:\n",
    "                summaries = dataset.get(\"summaries\", [])               \n",
    "                for summary in summaries:\n",
    "                    annotation_method = summary.get(\"annotation_method\", \"Unknown\")\n",
    "                    sum_details = summary.get(\"summary\", [])\n",
    "                    for sum_detail in sum_details:\n",
    "                        cell_id = sum_detail.get(\"cell_id\")\n",
    "                        gene_expr_list = sum_detail.get(\"gene_expr\", [])\n",
    "                        organ_id = dataset.get(\"organ_id\", \"Unknown\")\n",
    "                        if cell_id in ct_labels_ids.values():\n",
    "                            if isinstance(gene_expr_list, list) and gene_expr_list:\n",
    "                                for gene_expr in gene_expr_list:\n",
    "                                    if isinstance(gene_expr, dict):\n",
    "                                        ensembl_id = gene_expr.get(\"ensembl_id\")\n",
    "                                        mean_expr_value = gene_expr.get(\"mean_gene_expr_value\")\n",
    "                                        key = (cell_id, organ_id, annotation_method)\n",
    "                                        if key not in grouped_matches:\n",
    "                                            grouped_matches[key] = {\"cell_label\": \"\", \"ensembl_ids\": [], \"mean_expr_values\": []}\n",
    "                                        grouped_matches[key][\"cell_label\"] = [label for label, id in ct_labels_ids.items() if id == cell_id][0]\n",
    "                                        grouped_matches[key][\"ensembl_ids\"].append(ensembl_id)\n",
    "                                        grouped_matches[key][\"mean_expr_values\"].append(mean_expr_value)\n",
    "                            else:\n",
    "                                # Handle entries with empty gene_expr lists\n",
    "                                key = (cell_id, organ_id, annotation_method)\n",
    "                                if key not in grouped_matches:\n",
    "                                    grouped_matches[key] = {\"cell_label\": \"\", \"ensembl_ids\": [], \"mean_expr_values\": []}\n",
    "                                grouped_matches[key][\"cell_label\"] = [label for label, id in ct_labels_ids.items() if id == cell_id][0]\n",
    "\n",
    "# Preparing the final dataframe\n",
    "final_matches = []\n",
    "for (cell_id, organ_id, annotation_method), details in grouped_matches.items():\n",
    "    final_matches.append({\n",
    "        \"cell_id\": cell_id,\n",
    "        \"cell_label\": details[\"cell_label\"],\n",
    "        \"organ_id\": organ_id,\n",
    "        \"annotation_method\": annotation_method,\n",
    "        \"ensembl_ids\": \", \".join(details[\"ensembl_ids\"]),\n",
    "        \"mean_expr_values\": \", \".join(map(str, details[\"mean_expr_values\"]))\n",
    "    })\n",
    "\n",
    "final_matches_df = pd.DataFrame(final_matches)\n",
    "\n",
    "# Expanding the dataframe to have each row for each ensembl_id\n",
    "expanded_matches = []\n",
    "for _, row in final_matches_df.iterrows():\n",
    "    cell_id = row[\"cell_id\"]\n",
    "    cell_label = row[\"cell_label\"]\n",
    "    organ_id = row[\"organ_id\"]\n",
    "    annotation_method = row[\"annotation_method\"]\n",
    "    ensembl_ids = row[\"ensembl_ids\"].split(\", \")\n",
    "    mean_expr_values = row[\"mean_expr_values\"].split(\", \")\n",
    "    \n",
    "    for ensembl_id, mean_expr_value in zip(ensembl_ids, mean_expr_values):\n",
    "        try:\n",
    "            mean_expr_value_float = float(mean_expr_value)\n",
    "        except ValueError:\n",
    "            mean_expr_value_float = None  # Handle non-numeric values\n",
    "        expanded_matches.append({\n",
    "            \"cell_id\": cell_id,\n",
    "            \"cell_label\": cell_label,\n",
    "            \"organ_id\": organ_id,\n",
    "            \"annotation_method\": annotation_method,\n",
    "            \"ensembl_id\": ensembl_id,\n",
    "            \"mean_expr_value\": mean_expr_value_float\n",
    "        })\n",
    "\n",
    "expanded_matches_df = pd.DataFrame(expanded_matches)\n",
    "\n",
    "# Displaying the expanded dataframe\n",
    "expanded_matches_df.head()  # Displaying only the first few rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f122e1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract unique organ IDs from the expanded dataframe\n",
    "unique_organ_ids = expanded_matches_df[\"organ_id\"].unique()\n",
    "\n",
    "unique_organ_ids_list = unique_organ_ids.tolist()\n",
    "unique_organ_ids_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42f8cbe0",
   "metadata": {},
   "outputs": [],
   "source": [
    "organ_id_to_organ_name = {\n",
    "         'http://purl.obolibrary.org/obo/UBERON_0002113' : 'Kidney',\n",
    "         'http://purl.obolibrary.org/obo/UBERON_0000948' : 'heart',\n",
    "         'http://purl.obolibrary.org/obo/UBERON_0001255': 'urinary bladder',\n",
    "         'http://purl.obolibrary.org/obo/UBERON_0002048' : 'lung',\n",
    "         'http://purl.obolibrary.org/obo/UBERON_0002107' : 'liver'\n",
    "        }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fac496df",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Adding a new column to the dataframe\n",
    "expanded_matches_df['organ_name'] = expanded_matches_df['organ_id'].map(organ_id_to_organ_name)\n",
    "\n",
    "# Display the first few rows of the updated dataframe\n",
    "expanded_matches_df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6781270",
   "metadata": {},
   "outputs": [],
   "source": [
    "expanded_matches_df['organ_name'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e47f431",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select and clean up relevant columns from FTU dataframe\n",
    "ftu_cleaned_df = ftu_cell_count[['Organ', 'CL_Label_FTU', 'FTU Label in Uberon']].dropna().drop_duplicates()\n",
    "\n",
    "# Ensure unique mapping by dropping duplicates based on 'CT Label in CL'\n",
    "ftu_unique_df = ftu_cleaned_df.drop_duplicates(subset=['CL_Label_FTU'])\n",
    "\n",
    "# Perform the match and add the new column for FTU name\n",
    "expanded_matches_df['FTU_name'] = expanded_matches_df['cell_label'].map(\n",
    "    ftu_unique_df.set_index('CL_Label_FTU')['FTU Label in Uberon']\n",
    ")\n",
    "\n",
    "# Adding the organ name from FTU data\n",
    "expanded_matches_df['Organ_name_FTU'] = expanded_matches_df['cell_label'].map(\n",
    "    ftu_unique_df.set_index('CL_Label_FTU')['Organ']\n",
    ")\n",
    "\n",
    "# Rearrange columns as per the requirement\n",
    "expanded_matches_df = expanded_matches_df[[\n",
    "    'organ_name', 'Organ_name_FTU','organ_id', 'annotation_method', 'FTU_name', 'cell_id', 'cell_label', 'ensembl_id', 'mean_expr_value'\n",
    "]]\n",
    "\n",
    "# Rename columns for consistency\n",
    "expanded_matches_df.rename(columns={\n",
    "    'organ_name': 'organ_name',\n",
    "    'Organ_name_FTU': 'organ_name_in_FTU'\n",
    "}, inplace=True)\n",
    "\n",
    "# Display the first few rows of the updated dataframe\n",
    "expanded_matches_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e832f20b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop rows where organ_name is 'heart' or 'urinary bladder'\n",
    "matches_df_filtered = expanded_matches_df[~expanded_matches_df['organ_name'].isin(['heart', 'urinary bladder'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49b6a286",
   "metadata": {},
   "outputs": [],
   "source": [
    "matches_df_filtered.to_csv('output/vasculature/gene_information-for-vasculature-CTs-1.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2f6a109",
   "metadata": {},
   "outputs": [],
   "source": [
    "matches_df_filtered['FTU_name'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e63d4534",
   "metadata": {},
   "source": [
    "#### Generate box plot images for unique cell label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "991025b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create individual box plots for each cell_label\n",
    "unique_cell_labels = matches_df_filtered['cell_label'].unique()\n",
    "\n",
    "for cell_label in unique_cell_labels:\n",
    "    cell_label_data = matches_df_filtered[matches_df_filtered['cell_label'] == cell_label]\n",
    "    \n",
    "    if len(cell_label_data) > 1:  # Ensure there is enough data to plot\n",
    "        plt.figure(figsize=(8, 6))\n",
    "        sns.boxplot(data=cell_label_data, y='mean_expr_value', color='skyblue')\n",
    "        \n",
    "        # Customize the box plot\n",
    "        plt.xticks([])\n",
    "        plt.title(f'Box Plot of Mean Expression Values for {cell_label}')\n",
    "        plt.xlabel('Cell Label')\n",
    "        plt.ylabel('Mean Expression Value')\n",
    "        \n",
    "        # Adding additional visual elements\n",
    "        plt.axhline(y=cell_label_data['mean_expr_value'].median(), color='red', linestyle='-', label='Median')\n",
    "        plt.legend()\n",
    "\n",
    "        # Save each plot as an image file\n",
    "        individual_box_plot_file_path = f'output/vasculature/figures/box_plot_mean_expr_value_{cell_label.replace(\" \", \"_\")}.png'\n",
    "        plt.tight_layout()\n",
    "        plt.savefig(individual_box_plot_file_path)\n",
    "        plt.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "325ef524",
   "metadata": {},
   "source": [
    "#### Generate box plot images for unique cell label per annotation method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a5e9368",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_annotation_methods = matches_df_filtered['annotation_method'].explode().unique()\n",
    "unique_annotation_methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba53ba84",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_annotation_methods = matches_df_filtered['annotation_method'].explode().unique()\n",
    "\n",
    "for cell_label in unique_cell_labels:\n",
    "    for annotation_method in unique_annotation_methods:\n",
    "        cell_label_data = matches_df_filtered[\n",
    "            (matches_df_filtered['cell_label'] == cell_label) &\n",
    "            (matches_df_filtered['annotation_method'] == annotation_method)\n",
    "        ]\n",
    "        \n",
    "        if len(cell_label_data) > 1:  # Ensure there is enough data to plot\n",
    "            plt.figure(figsize=(8, 6))\n",
    "            sns.boxplot(data=cell_label_data, y='mean_expr_value', color='skyblue')\n",
    "            \n",
    "            # Customize the box plot\n",
    "            plt.xticks([])\n",
    "            plt.title(f'Box Plot of Mean Expression Values for {cell_label} ({annotation_method})')\n",
    "            plt.xlabel('Cell Label')\n",
    "            plt.ylabel('Mean Expression Value')\n",
    "            \n",
    "            # Adding additional visual elements\n",
    "            plt.axhline(y=cell_label_data['mean_expr_value'].median(), color='red', linestyle='-', label='Median')\n",
    "            plt.legend()\n",
    "\n",
    "            # Save each plot as an image file\n",
    "            individual_box_plot_file_path = f'output/vasculature/figures/box_plot_mean_expr_value_{cell_label.replace(\" \", \"_\")}_{annotation_method.replace(\" \", \"_\")}.png'\n",
    "            plt.tight_layout()\n",
    "            plt.savefig(individual_box_plot_file_path)\n",
    "            plt.close()\n",
    "\n",
    "# List of generated plot file paths\n",
    "plot_file_paths = [f'output/vasculature/figures/box_plot_mean_expr_value_{cell_label.replace(\" \", \"_\")}_{annotation_method.replace(\" \", \"_\")}.png'\n",
    "                   for cell_label in unique_cell_labels\n",
    "                   for annotation_method in unique_annotation_methods\n",
    "                   if len(matches_df_filtered[(matches_df_filtered['cell_label'] == cell_label) & (matches_df_filtered['annotation_method'] == annotation_method)]) > 1]\n",
    "\n",
    "plot_file_paths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ee47041",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a box plot\n",
    "plt.figure(figsize=(12, 8))\n",
    "sns.boxplot(x='FTU_name', y='mean_expr_value', hue='annotation_method', data=matches_df_filtered)\n",
    "\n",
    "# Customize the plot\n",
    "plt.title('Mean Expression Value per Cell Type per FTU Name per Annotation Method')\n",
    "plt.xlabel('FTU Name')\n",
    "plt.ylabel('Mean Expression Value')\n",
    "plt.xticks(rotation=45)\n",
    "plt.legend(title='Annotation Method', bbox_to_anchor=(1.05, 1), loc='upper left')\n",
    "\n",
    "# Save the plot to a file\n",
    "output_file_path = 'output/vasculature/mean-expression-box-plot-per-CT-per-FTU-per-AnnMethod.png'\n",
    "plt.tight_layout()\n",
    "plt.savefig(output_file_path)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b2b705b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a box plot\n",
    "plt.figure(figsize=(12, 8))\n",
    "sns.boxplot(x='FTU_name', y='mean_expr_value', hue='cell_label', data=matches_df_filtered)\n",
    "\n",
    "# Customize the plot\n",
    "plt.title('Mean Expression Value per Cell Type per FTU Name')\n",
    "plt.xlabel('FTU Name')\n",
    "plt.ylabel('Mean Expression Value')\n",
    "plt.xticks(rotation=45)\n",
    "plt.legend(title='Cell Type', bbox_to_anchor=(1.05, 1), loc='upper left')\n",
    "\n",
    "# Save the plot to a file\n",
    "output_file_path = 'output/vasculature/mean_expression_box_plot_per_cell_type.png'\n",
    "plt.tight_layout()\n",
    "plt.savefig(output_file_path)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c4c0159",
   "metadata": {},
   "source": [
    "### Understand the JSON files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7fa9dfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to extract as_label, link, and cell_id from the JSON data\n",
    "def extract_all_entries(json_data):\n",
    "    result = []\n",
    "    for entry in json_data['@graph']:\n",
    "        for sample in entry.get('samples', []):\n",
    "            rui_location = sample.get('rui_location', {})\n",
    "            all_collisions = rui_location.get('all_collisions', [])\n",
    "            for collision in all_collisions:\n",
    "                collisions = collision.get('collisions', [])\n",
    "                for col in collisions:\n",
    "                    as_label = col.get('as_label', 'unknown')\n",
    "                    for section in sample.get('sections', []):\n",
    "                        for dataset in section.get('datasets', []):\n",
    "                            link = dataset.get('link', 'unknown')\n",
    "                            for summary in dataset.get('summaries', []):\n",
    "                                for summary_item in summary.get('summary', []):\n",
    "                                    cell_id = summary_item.get('cell_id', 'unknown')\n",
    "                                    cell_label = summary_item.get('cell_label', 'unknown')\n",
    "                                    new_row = {\n",
    "                                        'as_label': as_label,\n",
    "                                        'link': link,\n",
    "                                        'cell_label': cell_label,\n",
    "                                        'cell_id': cell_id\n",
    "                                    }\n",
    "                                    result.append(new_row)\n",
    "    return result\n",
    "\n",
    "# Extract all entries\n",
    "extracted_data_enriched = extract_all_entries(json_data_enriched)\n",
    "\n",
    "# Convert extracted data to a DataFrame\n",
    "extracted_df_enriched = pd.DataFrame(extracted_data_enriched)\n",
    "\n",
    "# Display the DataFrame's head\n",
    "print(extracted_df_enriched.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "856e5185",
   "metadata": {},
   "outputs": [],
   "source": [
    "extracted_df_enriched.to_csv('output/sanity_check/Dataset-source-cell-enriched.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06aa4c46",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract cell IDs from the summary section\n",
    "def extract_cell_ids(data):\n",
    "    cell_ids = []\n",
    "    for item in data['@graph']:\n",
    "        samples = item.get(\"samples\", [])\n",
    "        for sample in samples:\n",
    "            sections = sample.get(\"sections\", [])\n",
    "            for section in sections:\n",
    "                datasets = section.get(\"datasets\", [])\n",
    "                for dataset in datasets:\n",
    "                    summaries = dataset.get(\"summaries\", [])               \n",
    "                    for summary in summaries:\n",
    "                        sum_details = summary.get(\"summary\", [])\n",
    "                        for sum_detail in sum_details:\n",
    "                            cell_id = sum_detail.get(\"cell_id\")\n",
    "                            cell_ids.append(cell_id)\n",
    "    return cell_ids\n",
    "\n",
    "cell_ids = extract_cell_ids(json_data_enriched)\n",
    "print(f\"Cell IDs from the file: {len(cell_ids)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f68d0427",
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_cell_ids_summaries(data):\n",
    "    cell_ids = []\n",
    "    for item in data['@graph']:\n",
    "        summaries = item.get('summary', [])\n",
    "        for summary in summaries:\n",
    "            for detail in summary:\n",
    "                if 'cell_id' in detail:\n",
    "                    cell_ids.append(summary['cell_id'])\n",
    "    return cell_ids\n",
    "\n",
    "cell_ids_summaries = extract_cell_ids_summaries(json_data_summary)\n",
    "print(f\"Cell IDs from the file: {len(cell_ids_summaries)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5c361dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to extract aggregated_summaries, cell_source_label, cell_label, and cell_id from the JSON data\n",
    "graph_data = json_data_summary.get('@graph', [])\n",
    "\n",
    "extracted_data_summary = []\n",
    "\n",
    "for item in graph_data:\n",
    "    if 'summary' in item:\n",
    "        for summary in item['summary']:\n",
    "            for summary in summaries:\n",
    "                if 'cell_id' in summary and 'cell_label' in summary:\n",
    "                    for aggregated_summary in aggregated_summaries:\n",
    "                        extracted_entry = {\n",
    "                    'cell_source_label': cell_source_label,\n",
    "                    'aggregated_summary': aggregated_summary,\n",
    "                    'cell_label': summary.get('cell_label'),\n",
    "                    'cell_id': summary.get('cell_id')\n",
    "                        }\n",
    "                        extracted_data_summary.append(extracted_entry)\n",
    "\n",
    "# Convert extracted data to a DataFrame\n",
    "extracted_df_summary = pd.DataFrame(extracted_data_summary)\n",
    "\n",
    "# Display the DataFrame's head\n",
    "print(extracted_df_summary.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d935e027",
   "metadata": {},
   "outputs": [],
   "source": [
    "extracted_df_summary.to_csv('output/sanity_check/Dataset-source-cell-summary.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70f31ab4",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_df['dataset'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "901cfbbd",
   "metadata": {},
   "outputs": [],
   "source": [
    "latest_filtered_cts_df = filtered_v1_enriched_v2_df_rearranged.copy()\n",
    "\n",
    "# Extract the relevant part of the reference_organ (after '#VHF')\n",
    "latest_filtered_cts_df['reference_organ_clean'] = latest_filtered_cts_df['reference_organ'].apply(lambda x: x.split('#')[-1])\n",
    "\n",
    "# Remove 'VHF' or 'VHM' from the 'reference_organ_clean' field\n",
    "latest_filtered_cts_df['reference_organ_clean'] = latest_filtered_cts_df['reference_organ_clean'].str.replace('VHF', '').str.replace('VHM', '')\n",
    "\n",
    "# Create a new column combining reference_organ_clean and as_label\n",
    "latest_filtered_cts_df['y_label_clean'] = latest_filtered_cts_df['reference_organ_clean'] + \"_\" + latest_filtered_cts_df['as_label']\n",
    "\n",
    "# Count the number of unique datasets per reference_organ + as_label combination for each annotation_method\n",
    "latest_dataset_counts = latest_filtered_cts_df.groupby(['annotation_method', 'y_label_clean', 'sex']).dataset.nunique().reset_index()\n",
    "latest_dataset_counts = latest_dataset_counts.rename(columns={'dataset': 'unique_dataset_count'})\n",
    "\n",
    "# Debug: Check the unique dataset counts\n",
    "print(latest_dataset_counts.head())\n",
    "\n",
    "# Combine the male and female dataframes and create a new column for sex\n",
    "latest_dataset_counts_combined = latest_dataset_counts.copy()\n",
    "latest_dataset_counts_combined['sex_label'] = latest_dataset_counts_combined['sex']\n",
    "\n",
    "# Define a custom color palette for better visualization\n",
    "custom_palette = sns.color_palette(\"tab10\")\n",
    "\n",
    "# Set figure size and fonts for better readability\n",
    "plt.figure(figsize=(14, 10))\n",
    "\n",
    "g_combined = sns.catplot(\n",
    "    data=latest_dataset_counts_combined,\n",
    "    x=\"unique_dataset_count\", y=\"y_label_clean\", hue=\"sex_label\",\n",
    "    col=\"annotation_method\", kind=\"bar\",\n",
    "    height=8, aspect=1.5, col_wrap=1,\n",
    "    palette=custom_palette\n",
    ")\n",
    "\n",
    "g_combined.set_axis_labels(\"Number of Unique Datasets\", \"\")\n",
    "g_combined.set_titles(\"{col_name}\")\n",
    "g_combined.fig.suptitle(\"Number of Unique Datasets by Annotation Method and Sex\", y=1.02)\n",
    "g_combined.set_xticklabels(fontsize=10)\n",
    "g_combined.set_yticklabels(fontsize=10)\n",
    "g_combined.set_xlabels(\"Number of Unique Datasets\", fontsize=12)\n",
    "g_combined.set_ylabels(\"\", fontsize=12)\n",
    "\n",
    "# Annotate the bars with the number of unique datasets, handling NaN values\n",
    "for ax in g_combined.axes.flatten():\n",
    "    for c in ax.containers:\n",
    "        labels = [f'{int(v.get_width()):.0f}' if not pd.isna(v.get_width()) else '' for v in c]\n",
    "        ax.bar_label(c, labels=labels, label_type='edge', fontsize=10)\n",
    "\n",
    "# Save the plot as a high-resolution image\n",
    "g_combined.savefig(\"output/figures/Datasets-AS-Annotation-Sex.png\", dpi=300)\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "650305ca",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "plt.figure(figsize=(10, 6))\n",
    "\n",
    "sns.violinplot(\n",
    "    data=latest_dataset_counts_combined,\n",
    "    x=\"unique_dataset_count\", y=\"annotation_method\", hue=\"sex_label\",\n",
    "    split=True, inner=\"quart\", palette=custom_palette\n",
    ")\n",
    "\n",
    "plt.title(\"Distribution of Unique Datasets by Annotation Method and Sex\")\n",
    "plt.xlabel(\"Count\")\n",
    "plt.ylabel(\"Annotation Method\")\n",
    "plt.xlim(0, 50)\n",
    "plt.legend(title=\"Sex\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69524614",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_sexes_popv = filtered_v1_enriched_v2_df_rearranged[filtered_v1_enriched_v2_df_rearranged['annotation_method'] == 'popv']['sex'].unique()\n",
    "print(unique_sexes_popv)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e3c4b8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_sexes_azimuth = filtered_v1_enriched_v2_df_rearranged[filtered_v1_enriched_v2_df_rearranged['annotation_method'] == 'azimuth']['sex'].unique()\n",
    "print(unique_sexes_azimuth)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23e710e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_sexes_celltypist = filtered_v1_enriched_v2_df_rearranged[filtered_v1_enriched_v2_df_rearranged['annotation_method'] == 'celltypist']['sex'].unique()\n",
    "print(unique_sexes_celltypist)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "1b10de54",
   "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>as_label</th>\n",
       "      <th>modality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>diaphragmatic surface of liver</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Interlobar adipose tissue of right mammary gland</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>fundus of urinary bladder</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Right Medial Bronchopulmonary Segment</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>horizontal part of duodenum</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>central zone of prostate</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>gastric impression of liver</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>jejunum</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>descending part of duodenum</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>renal pyramid</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>skin of body</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>kidney capsule</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>renal papilla</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Spatial entity of male fundus of urinary bladd...</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>left cardiac atrium</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>distal part of ileum</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>caecum</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>jejunum</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>descending part of duodenum</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>heart left ventricle</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>ileum</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>rectum</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>skin of body</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>right cardiac atrium</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Lateral segmental bronchus</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Spatial entity of male fundus of urinary bladd...</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>ascending colon</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>renal column</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>distal part of ileum</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>caecum</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>rectum</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>ileum</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>ascending colon</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>ascending part of duodenum</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>hilum of spleen</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>interventricular septum</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Right Lateral Bronchopulmonary Segment</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>hilum of kidney</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Left apical bronchopulmonary segment</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>peripheral zone of prostate</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>sigmoid colon</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>diaphragmatic surface of spleen</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>outer cortex of kidney</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>descending colon</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>ascending part of duodenum</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>transverse colon</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>capsule of the liver</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>superior part of duodenum</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>sigmoid colon</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>heart right ventricle</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Posteromedial head of posterior papillary musc...</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>horizontal part of duodenum</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>descending colon</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Spatial entity of male skin</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>transverse colon</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>left ureter</td>\n",
       "      <td>sc_transcriptomics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>superior part of duodenum</td>\n",
       "      <td>sc_proteomics</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             as_label            modality\n",
       "0                      diaphragmatic surface of liver  sc_transcriptomics\n",
       "1    Interlobar adipose tissue of right mammary gland  sc_transcriptomics\n",
       "2                           fundus of urinary bladder  sc_transcriptomics\n",
       "3               Right Medial Bronchopulmonary Segment  sc_transcriptomics\n",
       "4                         horizontal part of duodenum       sc_proteomics\n",
       "5                            central zone of prostate  sc_transcriptomics\n",
       "6                         gastric impression of liver  sc_transcriptomics\n",
       "7                                             jejunum  sc_transcriptomics\n",
       "8                         descending part of duodenum  sc_transcriptomics\n",
       "9                                       renal pyramid  sc_transcriptomics\n",
       "10                                       skin of body  sc_transcriptomics\n",
       "11                                     kidney capsule  sc_transcriptomics\n",
       "12                                      renal papilla  sc_transcriptomics\n",
       "13  Spatial entity of male fundus of urinary bladd...  sc_transcriptomics\n",
       "14                                left cardiac atrium  sc_transcriptomics\n",
       "15                               distal part of ileum  sc_transcriptomics\n",
       "16                                             caecum  sc_transcriptomics\n",
       "17                                            jejunum       sc_proteomics\n",
       "18                        descending part of duodenum       sc_proteomics\n",
       "19                               heart left ventricle  sc_transcriptomics\n",
       "20                                              ileum  sc_transcriptomics\n",
       "21                                             rectum  sc_transcriptomics\n",
       "22                                       skin of body       sc_proteomics\n",
       "23                               right cardiac atrium  sc_transcriptomics\n",
       "24                         Lateral segmental bronchus  sc_transcriptomics\n",
       "25  Spatial entity of male fundus of urinary bladd...  sc_transcriptomics\n",
       "26                                    ascending colon  sc_transcriptomics\n",
       "27                                       renal column  sc_transcriptomics\n",
       "28                               distal part of ileum       sc_proteomics\n",
       "29                                             caecum       sc_proteomics\n",
       "30                                             rectum       sc_proteomics\n",
       "31                                              ileum       sc_proteomics\n",
       "32                                    ascending colon       sc_proteomics\n",
       "33                         ascending part of duodenum  sc_transcriptomics\n",
       "34                                    hilum of spleen  sc_transcriptomics\n",
       "35                            interventricular septum  sc_transcriptomics\n",
       "36             Right Lateral Bronchopulmonary Segment  sc_transcriptomics\n",
       "37                                    hilum of kidney  sc_transcriptomics\n",
       "38               Left apical bronchopulmonary segment  sc_transcriptomics\n",
       "39                        peripheral zone of prostate  sc_transcriptomics\n",
       "40                                      sigmoid colon  sc_transcriptomics\n",
       "41                    diaphragmatic surface of spleen  sc_transcriptomics\n",
       "42                             outer cortex of kidney  sc_transcriptomics\n",
       "43                                   descending colon  sc_transcriptomics\n",
       "44                         ascending part of duodenum       sc_proteomics\n",
       "45                                   transverse colon  sc_transcriptomics\n",
       "46                               capsule of the liver  sc_transcriptomics\n",
       "47                          superior part of duodenum  sc_transcriptomics\n",
       "48                                      sigmoid colon       sc_proteomics\n",
       "49                              heart right ventricle  sc_transcriptomics\n",
       "50  Posteromedial head of posterior papillary musc...  sc_transcriptomics\n",
       "51                        horizontal part of duodenum  sc_transcriptomics\n",
       "52                                   descending colon       sc_proteomics\n",
       "53                        Spatial entity of male skin       sc_proteomics\n",
       "54                                   transverse colon       sc_proteomics\n",
       "55                                        left ureter  sc_transcriptomics\n",
       "56                          superior part of duodenum       sc_proteomics"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Initialize a set to collect unique (as_label, modality) pairs\n",
    "as_label_modality_pairs = set()\n",
    "\n",
    "# Traverse the JSON structure to find all \"as_label\" and \"modality\" pairs\n",
    "for graph in json_data_enriched.get('@graph', []):\n",
    "    if 'samples' in graph:\n",
    "        for sample in graph['samples']:\n",
    "            if 'rui_location' in sample:\n",
    "                rui_location = sample['rui_location']\n",
    "                # Check for summaries in rui_location\n",
    "                if 'summaries' in rui_location:\n",
    "                    for summary in rui_location['summaries']:\n",
    "                        modality = summary.get('modality')\n",
    "                        if 'all_collisions' in rui_location:\n",
    "                            for collision in rui_location['all_collisions']:\n",
    "                                if 'collisions' in collision:\n",
    "                                    for col in collision['collisions']:\n",
    "                                        as_label = col.get('as_label')\n",
    "                                        if as_label and modality:\n",
    "                                            as_label_modality_pairs.add((as_label, modality))\n",
    "\n",
    "# Convert to list for easy viewing and display the results\n",
    "as_label_modality_list = list(as_label_modality_pairs)\n",
    "\n",
    "import pandas as pd\n",
    "df = pd.DataFrame(as_label_modality_list, columns=[\"as_label\", \"modality\"])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f04ab72-8410-4a41-8acc-5e068bf96e70",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (hra)",
   "language": "python",
   "name": "hra"
  },
  "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}