pre-process-FTU-data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "47063ba6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import zipfile\n",
    "import os\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "eea3cb25",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the dataset\n",
    "ftu_file_path = 'ref_data/FTU Cell Count Table - Cell_Type_Count.csv'\n",
    "ftu_data = pd.read_csv(ftu_file_path)\n",
    "\n",
    "# Filter the data where 'CT ID in CL' starts with 'CL'\n",
    "ftu_data_clean = ftu_data.dropna(subset=['CT ID in CL'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7993f6df",
   "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>CT Label in CL</th>\n",
       "      <th>CT ID in CL</th>\n",
       "      <th>CT Label in 2D Object</th>\n",
       "      <th>#AS</th>\n",
       "      <th>#CT</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>kidney cortex collecting duct intercalated cell</td>\n",
       "      <td>CL:1000715</td>\n",
       "      <td>Cortical Collecting Duct Intercalated Cell Type B</td>\n",
       "      <td>0</td>\n",
       "      <td>2</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>kidney collecting duct alpha-intercalated cell</td>\n",
       "      <td>CL:4030015</td>\n",
       "      <td>Cortical Collecting Duct Intercalated Cell Type A</td>\n",
       "      <td>0</td>\n",
       "      <td>4</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>peritubular capillary endothelial cell</td>\n",
       "      <td>CL:1001033</td>\n",
       "      <td>Endothelium Peritubular Capillary</td>\n",
       "      <td>0</td>\n",
       "      <td>5</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>kidney cortex collecting duct principal cell</td>\n",
       "      <td>CL:1000714</td>\n",
       "      <td>Cortical Collecting Duct Principal Cell</td>\n",
       "      <td>0</td>\n",
       "      <td>9</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>kidney outer medulla collecting duct intercala...</td>\n",
       "      <td>CL:1000717</td>\n",
       "      <td>Outer Medullary Collecting Duct Intercalated C...</td>\n",
       "      <td>0</td>\n",
       "      <td>5</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>Thymus</td>\n",
       "      <td>Thymus Lobule</td>\n",
       "      <td>UBERON:0002125</td>\n",
       "      <td>memory B cell</td>\n",
       "      <td>CL:0000787</td>\n",
       "      <td>B Cell</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>Thymus</td>\n",
       "      <td>Thymus Lobule</td>\n",
       "      <td>UBERON:0002125</td>\n",
       "      <td>corticomedullary thymic epithelial cell</td>\n",
       "      <td>CL:0009070</td>\n",
       "      <td>Corticomedullary Thymic Epithelial Cell</td>\n",
       "      <td>0</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>Thymus</td>\n",
       "      <td>Thymus Lobule</td>\n",
       "      <td>UBERON:0002125</td>\n",
       "      <td>leukocyte</td>\n",
       "      <td>CL:0000738</td>\n",
       "      <td>White Blood Cell</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>Thymus</td>\n",
       "      <td>Thymus Lobule</td>\n",
       "      <td>UBERON:0002125</td>\n",
       "      <td>thymic medullary macrophage</td>\n",
       "      <td>CL:0000882</td>\n",
       "      <td>Thymic Medullary Macrophage</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>Thymus</td>\n",
       "      <td>Thymus Lobule</td>\n",
       "      <td>UBERON:0002125</td>\n",
       "      <td>naive B cell</td>\n",
       "      <td>CL:0000788</td>\n",
       "      <td>B Cell</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>132 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Organ              FTU Label in Uberon FTU ID in Uberon  \\\n",
       "0    Kidney         Cortical Collecting Duct   UBERON:0004203   \n",
       "1    Kidney         Cortical Collecting Duct   UBERON:0004203   \n",
       "2    Kidney         Cortical Collecting Duct   UBERON:0004203   \n",
       "3    Kidney         Cortical Collecting Duct   UBERON:0004203   \n",
       "4    Kidney  Outer Medullary Collecting Duct   UBERON:0004204   \n",
       "..      ...                              ...              ...   \n",
       "149  Thymus                    Thymus Lobule   UBERON:0002125   \n",
       "150  Thymus                    Thymus Lobule   UBERON:0002125   \n",
       "151  Thymus                    Thymus Lobule   UBERON:0002125   \n",
       "152  Thymus                    Thymus Lobule   UBERON:0002125   \n",
       "153  Thymus                    Thymus Lobule   UBERON:0002125   \n",
       "\n",
       "                                        CT Label in CL CT ID in CL  \\\n",
       "0      kidney cortex collecting duct intercalated cell  CL:1000715   \n",
       "1       kidney collecting duct alpha-intercalated cell  CL:4030015   \n",
       "2               peritubular capillary endothelial cell  CL:1001033   \n",
       "3         kidney cortex collecting duct principal cell  CL:1000714   \n",
       "4    kidney outer medulla collecting duct intercala...  CL:1000717   \n",
       "..                                                 ...         ...   \n",
       "149                                      memory B cell  CL:0000787   \n",
       "150            corticomedullary thymic epithelial cell  CL:0009070   \n",
       "151                                          leukocyte  CL:0000738   \n",
       "152                        thymic medullary macrophage  CL:0000882   \n",
       "153                                       naive B cell  CL:0000788   \n",
       "\n",
       "                                 CT Label in 2D Object  #AS  #CT  \n",
       "0    Cortical Collecting Duct Intercalated Cell Type B    0    2  \n",
       "1    Cortical Collecting Duct Intercalated Cell Type A    0    4  \n",
       "2                    Endothelium Peritubular Capillary    0    5  \n",
       "3              Cortical Collecting Duct Principal Cell    0    9  \n",
       "4    Outer Medullary Collecting Duct Intercalated C...    0    5  \n",
       "..                                                 ...  ...  ...  \n",
       "149                                             B Cell    0    8  \n",
       "150            Corticomedullary Thymic Epithelial Cell    0   45  \n",
       "151                                   White Blood Cell    0   10  \n",
       "152                        Thymic Medullary Macrophage    0    4  \n",
       "153                                             B Cell    0    8  \n",
       "\n",
       "[132 rows x 8 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_ftu_data = ftu_data_clean[ftu_data_clean['CT ID in CL'].str.startswith('CL')]\n",
    "filtered_ftu_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "98ce56ad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "114"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_ftu_data['CT ID in CL'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "495e1bb0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Kidney', 'Large Intestine', 'Liver', 'Lung', 'Pancreas',\n",
       "       'Prostate', 'Skin', 'Small Intestine', 'Spleen', 'Thymus'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 4: Extract unique organs from the FTU data\n",
    "unique_organs = filtered_ftu_data['Organ'].unique()\n",
    "unique_organs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c4514c98",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processing data for organ: Kidney\n",
      "Processing data for organ: Large Intestine\n",
      "Processing data for organ: Lung\n",
      "Processing data for organ: Pancreas\n",
      "Processing data for organ: Prostate\n",
      "Processing data for organ: Skin\n",
      "Processing data for organ: Small Intestine\n",
      "Processing data for organ: Spleen\n",
      "Processing data for organ: Thymus\n",
      "Processing data for organ: Liver\n"
     ]
    },
    {
     "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>FTU Partonomy</th>\n",
       "      <th>Cell Type</th>\n",
       "      <th>Cell ID</th>\n",
       "      <th>Biomarker - Gene</th>\n",
       "      <th>Biomarker - Protein</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>kidney--&gt;renal collecting system--&gt;collecting ...</td>\n",
       "      <td>kidney cortex collecting duct intercalated cell</td>\n",
       "      <td>CL:1000715</td>\n",
       "      <td>SLC26A7;SLC4A1;KIT;AQP6</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>kidney--&gt;renal collecting system--&gt;collecting ...</td>\n",
       "      <td>kidney cortex collecting duct intercalated cell</td>\n",
       "      <td>CL:1000715</td>\n",
       "      <td>SLC4A9;SLC26A4</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>kidney--&gt;renal collecting system--&gt;collecting ...</td>\n",
       "      <td>kidney collecting duct alpha-intercalated cell</td>\n",
       "      <td>CL:4030015</td>\n",
       "      <td>SLC4A1;SLC26A7;TMEM213;KIT;AQP6</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>kidney--&gt;kidney vasculature--&gt;kidney capillary...</td>\n",
       "      <td>peritubular capillary endothelial cell</td>\n",
       "      <td>CL:1001033</td>\n",
       "      <td>RAMP3;PLVAP;DNASE1L3</td>\n",
       "      <td>CD31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>kidney--&gt;cortex of kidney--&gt;medullary ray--&gt;co...</td>\n",
       "      <td>kidney cortex collecting duct principal cell</td>\n",
       "      <td>CL:1000714</td>\n",
       "      <td>AQP2;AQP3;FXYD4</td>\n",
       "      <td>AQP2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       FTU Partonomy  \\\n",
       "0  kidney-->renal collecting system-->collecting ...   \n",
       "1  kidney-->renal collecting system-->collecting ...   \n",
       "2  kidney-->renal collecting system-->collecting ...   \n",
       "3  kidney-->kidney vasculature-->kidney capillary...   \n",
       "4  kidney-->cortex of kidney-->medullary ray-->co...   \n",
       "\n",
       "                                         Cell Type     Cell ID  \\\n",
       "0  kidney cortex collecting duct intercalated cell  CL:1000715   \n",
       "1  kidney cortex collecting duct intercalated cell  CL:1000715   \n",
       "2   kidney collecting duct alpha-intercalated cell  CL:4030015   \n",
       "3           peritubular capillary endothelial cell  CL:1001033   \n",
       "4     kidney cortex collecting duct principal cell  CL:1000714   \n",
       "\n",
       "                  Biomarker - Gene Biomarker - Protein  \n",
       "0          SLC26A7;SLC4A1;KIT;AQP6                      \n",
       "1                   SLC4A9;SLC26A4                      \n",
       "2  SLC4A1;SLC26A7;TMEM213;KIT;AQP6                      \n",
       "3             RAMP3;PLVAP;DNASE1L3                CD31  \n",
       "4                  AQP2;AQP3;FXYD4                AQP2  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "organ_files = [\n",
    "    'ref_data/ASCT_B/Kidney_v1.5 - Kidney_v1.5.csv',\n",
    "    'ref_data/ASCT_B/Large_Intestine_v1.3 - Large_Intestine_v1.3.csv',\n",
    "    'ref_data/ASCT_B/Lung_v1.5 - Lung_v1.5.csv',\n",
    "    'ref_data/ASCT_B/Pancreas_v1.3 - Pancreas_v1.3.csv',\n",
    "    'ref_data/ASCT_B/Prostate_v1.1 - Prostate_v1.1.csv',\n",
    "    'ref_data/ASCT_B/Skin_v1.3 - Skin_v1.3.csv',\n",
    "    'ref_data/ASCT_B/Small_Intestine_v1.1 - Small_Intestine_v1.1.csv',\n",
    "    'ref_data/ASCT_B/Spleen_v1.3 - Spleen_v1.3.csv',\n",
    "    'ref_data/ASCT_B/Thymus_v1.4 - Thymus_v1.4.csv',\n",
    "    'ref_data/ASCT_B/Liver_v1.3 - Liver_v1.3.csv'\n",
    "]\n",
    "\n",
    "# Initialize an empty list to store the rows for the final CSV\n",
    "refined_rows = []\n",
    "\n",
    "# Iterate through each organ file\n",
    "for organ_file in organ_files:\n",
    "    organ_name = str(organ_file).split('/')[-1].split(' - ')[0].split('_v')[0].replace(\"_\", \" \")\n",
    "    print(f'Processing data for organ: {organ_name}')\n",
    "    organ_df = pd.read_csv(organ_file)\n",
    "    \n",
    "    # Strip any trailing spaces from the relevant columns\n",
    "    if 'CT/1/ID' in organ_df.columns:\n",
    "        organ_df['CT/1/ID'] = organ_df['CT/1/ID'].astype(str).str.strip()\n",
    "    if 'CT/2/ID' in organ_df.columns:\n",
    "        organ_df['CT/2/ID'] = organ_df['CT/2/ID'].astype(str).str.strip()\n",
    "    \n",
    "    # Normalize the organ name in the FTU data to match the organ file name format\n",
    "    organ_specific_ftu_data = filtered_ftu_data[filtered_ftu_data['Organ'] == organ_name]\n",
    "\n",
    "    # Iterate through each row in the organ-specific FTU data\n",
    "    for _, row in organ_specific_ftu_data.iterrows():\n",
    "        ct_id = row['CT ID in CL'].replace(' ', '')\n",
    "        \n",
    "        # Initialize an empty dataframe for matching rows\n",
    "        matching_rows = pd.DataFrame()\n",
    "        \n",
    "        # Check if 'CT/1/ID' and 'CT/2/ID' columns exist in the organ dataframe and match the CT ID\n",
    "        if 'CT/1/ID' in organ_df.columns:\n",
    "            matching_rows = pd.concat([matching_rows, organ_df[organ_df['CT/1/ID'] == ct_id]], ignore_index=True)\n",
    "        if 'CT/2/ID' in organ_df.columns:\n",
    "            matching_rows = pd.concat([matching_rows, organ_df[organ_df['CT/2/ID'] == ct_id]], ignore_index=True)\n",
    "        \n",
    "        # Iterate through matching rows and extract required data\n",
    "        for _, match in matching_rows.iterrows():\n",
    "            # Concatenate the AS parts with '-->'\n",
    "            as_parts = []\n",
    "            for i in range(1, 7):\n",
    "                as_label_col = f'AS/{i}/LABEL'\n",
    "                if as_label_col in match and pd.notna(match[as_label_col]):\n",
    "                    as_parts.append(match[as_label_col])\n",
    "            ftu_partonomy = '-->'.join(as_parts)\n",
    "            \n",
    "            # Determine the correct cell ID and label\n",
    "            cell_id = match['CT/1/ID'] if 'CT/1/ID' in match and match['CT/1/ID'] == ct_id else (match['CT/2/ID'] if 'CT/2/ID' in match and match['CT/2/ID'] == ct_id else \"\")\n",
    "            cell_label = match['CT/1'] if 'CT/1/ID' in match and match['CT/1/ID'] == ct_id else (match['CT/2'] if 'CT/2/ID' in match and match['CT/2/ID'] == ct_id else \"\")\n",
    "            \n",
    "            # Concatenate gene biomarkers\n",
    "            bgene = ';'.join([match[f'BGene/{i}'] for i in range(1, 9) if f'BGene/{i}' in match and pd.notna(match[f'BGene/{i}'])])\n",
    "            \n",
    "            # Concatenate protein biomarkers\n",
    "            bprotein = ';'.join([match[f'BProtein/{i}'] for i in range(1, 5) if f'BProtein/{i}' in match and pd.notna(match[f'BProtein/{i}'])])\n",
    "            \n",
    "            # Create a row for the final CSV\n",
    "            refined_row = [\n",
    "                ftu_partonomy,  # FTU Partonomy\n",
    "                row['CT Label in CL'],  # Cell Type\n",
    "                cell_id,        # Cell ID\n",
    "                bgene,          # Biomarker - Gene\n",
    "                bprotein        # Biomarker - Protein\n",
    "            ]\n",
    "            \n",
    "            refined_rows.append(refined_row)\n",
    "\n",
    "# Convert the list of rows into a DataFrame\n",
    "ftu_partonomy_content = pd.DataFrame(refined_rows, columns=[\n",
    "    'FTU Partonomy', 'Cell Type', 'Cell ID', 'Biomarker - Gene', 'Biomarker - Protein'\n",
    "])\n",
    "\n",
    "# Display the first few rows of the refined DataFrame\n",
    "ftu_partonomy_content.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f1495968",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(340, 5)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ftu_partonomy_content.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5887c643",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "({'CL:0000084', 'CL:0000738', 'CL:0000787', 'CL:0002144'}, set())"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the unique CT IDs from the refined data and filtered FTU data\n",
    "refined_ct_ids = set(ftu_partonomy_content['Cell ID'].unique())\n",
    "filtered_ct_ids = set(filtered_ftu_data['CT ID in CL'].unique())\n",
    "\n",
    "# Find the difference\n",
    "# CT IDs present in filtered FTU data but not in refined FTU partonomy data\n",
    "ct_ids_in_filtered_not_in_refined = filtered_ct_ids - refined_ct_ids\n",
    "\n",
    "# CT IDs present in refined FTU partonomy data but not in filtered FTU data\n",
    "ct_ids_in_refined_not_in_filtered = refined_ct_ids - filtered_ct_ids\n",
    "\n",
    "# Display the results\n",
    "ct_ids_in_filtered_not_in_refined, ct_ids_in_refined_not_in_filtered"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "55dcf001",
   "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>FTU_partonomy</th>\n",
       "      <th>cell_type</th>\n",
       "      <th>CT_ID</th>\n",
       "      <th>CT_label</th>\n",
       "      <th>biomarker-gene</th>\n",
       "      <th>biomarker-protein</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>kidney--&gt;cortex of kidney--&gt;outer cortex of ki...</td>\n",
       "      <td>podocyte</td>\n",
       "      <td>CL:0000653</td>\n",
       "      <td>Podocyte</td>\n",
       "      <td>NPHS2;PODXL;NPHS1;PTPRQ</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>kidney--&gt;cortex of kidney--&gt;juxtamedullary cor...</td>\n",
       "      <td>glomerular visceral epithelial cell</td>\n",
       "      <td>CL:0000653</td>\n",
       "      <td>Podocyte</td>\n",
       "      <td>NPHS2;PODXL;NPHS1;PTPRQ</td>\n",
       "      <td>PODXL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>kidney--&gt;cortex of kidney--&gt;medullary ray--&gt;Co...</td>\n",
       "      <td>kidney cortex collecting duct principal cell</td>\n",
       "      <td>CL:1000714</td>\n",
       "      <td>Cortical Collecting Duct Principal Cell</td>\n",
       "      <td>AQP2;AQP3;FXYD4</td>\n",
       "      <td>AQP2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>kidney--&gt;cortex of kidney--&gt;medullary ray--&gt;Co...</td>\n",
       "      <td>kidney cortex collecting duct principal cell</td>\n",
       "      <td>CL:1000714</td>\n",
       "      <td>Cortical Collecting Duct Principal Cell</td>\n",
       "      <td>AQP2;AQP3;FXYD4</td>\n",
       "      <td>AQP2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>kidney--&gt;renal medulla--&gt;inner medulla of kidn...</td>\n",
       "      <td>vasa recta descending limb cell</td>\n",
       "      <td>CL:1001285</td>\n",
       "      <td>Descending Vasa Recta Endothelial Cell</td>\n",
       "      <td>SERPINE2;TM4SF1;PALMD;ADAMTS6</td>\n",
       "      <td>UMOD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       FTU_partonomy  \\\n",
       "0  kidney-->cortex of kidney-->outer cortex of ki...   \n",
       "1  kidney-->cortex of kidney-->juxtamedullary cor...   \n",
       "2  kidney-->cortex of kidney-->medullary ray-->Co...   \n",
       "3  kidney-->cortex of kidney-->medullary ray-->Co...   \n",
       "4  kidney-->renal medulla-->inner medulla of kidn...   \n",
       "\n",
       "                                      cell_type       CT_ID  \\\n",
       "0                                      podocyte  CL:0000653   \n",
       "1           glomerular visceral epithelial cell  CL:0000653   \n",
       "2  kidney cortex collecting duct principal cell  CL:1000714   \n",
       "3  kidney cortex collecting duct principal cell  CL:1000714   \n",
       "4               vasa recta descending limb cell  CL:1001285   \n",
       "\n",
       "                                  CT_label                 biomarker-gene  \\\n",
       "0                                 Podocyte        NPHS2;PODXL;NPHS1;PTPRQ   \n",
       "1                                 Podocyte        NPHS2;PODXL;NPHS1;PTPRQ   \n",
       "2  Cortical Collecting Duct Principal Cell                AQP2;AQP3;FXYD4   \n",
       "3  Cortical Collecting Duct Principal Cell                AQP2;AQP3;FXYD4   \n",
       "4   Descending Vasa Recta Endothelial Cell  SERPINE2;TM4SF1;PALMD;ADAMTS6   \n",
       "\n",
       "  biomarker-protein  \n",
       "0                    \n",
       "1             PODXL  \n",
       "2              AQP2  \n",
       "3              AQP2  \n",
       "4              UMOD  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "# Specify the encoding to avoid UnicodeDecodeError\n",
    "with open('ref_data/hra-asctb-all.v2.1.json', 'r', encoding='utf-8') as json_file:\n",
    "    json_data = json.load(json_file)\n",
    "\n",
    "# Step 4: Extract unique organs from the FTU data\n",
    "unique_organs = filtered_ftu_data['Organ'].unique()\n",
    "\n",
    "# Initialize a list to store the refined rows\n",
    "refined_rows_ct_only = []\n",
    "\n",
    "# Step 5: Process only the matching organs from the JSON data\n",
    "# Step 5: Process only the matching organs from the JSON data\n",
    "for organ in unique_organs:\n",
    "    organ_key = organ.lower().replace(\" \", \"-\")  # Adjust the organ name to match JSON keys\n",
    "    if organ_key in json_data:\n",
    "        organ_data_list = json_data[organ_key].get('data', [])\n",
    "        \n",
    "        # Subset the filtered FTU data for the current organ\n",
    "        organ_specific_ftu_data = filtered_ftu_data[filtered_ftu_data['Organ'] == organ]\n",
    "\n",
    "        # Process each entry in the organ data list\n",
    "        for entry in organ_data_list:\n",
    "            # Process each cell type in the entry\n",
    "            for cell_type_info in entry['cell_types']:\n",
    "                cell_id = cell_type_info.get('id', '').replace(' ', '').strip()\n",
    "                cell_type = cell_type_info.get('rdfs_label', '').strip()\n",
    "                cell_label = cell_type_info.get('name', '').strip()\n",
    "\n",
    "                # Match with organ-specific filtered FTU data\n",
    "                for _, ftu_row in organ_specific_ftu_data.iterrows():\n",
    "                    if cell_id == ftu_row['CT ID in CL'].replace(' ', '').strip():\n",
    "                        # Build the FTU Partonomy using all AS labels\n",
    "                        anatomical_structures = [as_entry['name'] for as_entry in entry['anatomical_structures'] if 'name' in as_entry]\n",
    "                        ftu_partonomy = '-->'.join(anatomical_structures)\n",
    "                                \n",
    "                        # Concatenate gene biomarkers\n",
    "                        bgene = ';'.join([bm['name'].strip() for bm in entry['biomarkers_gene'] if bm.get('b_type') == 'gene'])\n",
    "\n",
    "                        # Concatenate protein biomarkers\n",
    "                        bprotein = ';'.join([bm['name'].strip() for bm in entry['biomarkers_protein'] if bm.get('b_type') == 'protein'])\n",
    "\n",
    "                        # Create the refined row\n",
    "                        refined_row = [\n",
    "                            ftu_partonomy,  # FTU Partonomy\n",
    "                            cell_type,  #  Cell label assigned to the term from the source ontology (ie uberon)\n",
    "                            cell_id,        # CT ID\n",
    "                            cell_label,     # CT Label\n",
    "                            bgene,          # Biomarker - Gene\n",
    "                            bprotein        # Biomarker - Protein\n",
    "                        ]\n",
    "                                \n",
    "                        refined_rows_ct_only.append(refined_row)\n",
    "\n",
    "# Convert the list of rows into a DataFrame\n",
    "refined_ftu_partonomy_data_ct_only = pd.DataFrame(refined_rows_ct_only, columns=[\n",
    "    'FTU_partonomy', 'cell_type', 'CT_ID', 'CT_label', 'biomarker-gene', 'biomarker-protein'\n",
    "])\n",
    "\n",
    "refined_ftu_partonomy_data_ct_only.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "6024c216",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(340, 6)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refined_ftu_partonomy_data_ct_only.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "c43c6f46",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "({'CL:0000084', 'CL:0000738', 'CL:0000787', 'CL:0002144'}, set())"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the unique CT IDs from the refined data and filtered FTU data\n",
    "refined_ct_ids = set(refined_ftu_partonomy_data_ct_only['CT_ID'].unique())\n",
    "filtered_ct_ids = set(filtered_ftu_data['CT ID in CL'].unique())\n",
    "\n",
    "# Find the difference\n",
    "# CT IDs present in filtered FTU data but not in refined FTU partonomy data\n",
    "ct_ids_in_filtered_not_in_refined = filtered_ct_ids - refined_ct_ids\n",
    "\n",
    "# CT IDs present in refined FTU partonomy data but not in filtered FTU data\n",
    "ct_ids_in_refined_not_in_filtered = refined_ct_ids - filtered_ct_ids\n",
    "\n",
    "# Display the results\n",
    "ct_ids_in_filtered_not_in_refined, ct_ids_in_refined_not_in_filtered"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "80c84acb",
   "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>FTU Partonomy</th>\n",
       "      <th>Cell Type</th>\n",
       "      <th>Cell ID</th>\n",
       "      <th>Biomarker - Gene</th>\n",
       "      <th>Biomarker - Protein</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [FTU Partonomy, Cell Type, Cell ID, Biomarker - Gene, Biomarker - Protein]\n",
       "Index: []"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filter the filtered_ftu_data DataFrame to get rows where 'CT ID in CL' is in ct_ids_in_filtered_not_in_refined\n",
    "data_filtered_not_in_refined = ftu_partonomy_content[ftu_partonomy_content['Cell ID'].isin(ct_ids_in_filtered_not_in_refined)]\n",
    "\n",
    "# Display the resulting DataFrame\n",
    "data_filtered_not_in_refined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "adf6e418",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(110, 110)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ftu_partonomy_content['Cell ID'].nunique(), refined_ftu_partonomy_data_ct_only['CT_ID'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "a5ceb201",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CL:0000084', 'CL:0000738', 'CL:0000787', 'CL:0002144'}"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff = set(filtered_ftu_data['CT ID in CL']) - set(ftu_partonomy_content['Cell ID'])\n",
    "diff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "a8a72e9f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'CL:0000084', 'CL:0000738', 'CL:0000787', 'CL:0002144'}"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff = set(filtered_ftu_data['CT ID in CL']) - set(refined_ftu_partonomy_data_ct_only['CT_ID'])\n",
    "diff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "c1b95358",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Separate the values in \"Biomarker - Gene\" and \"Biomarker - Protein\" columns into individual rows\n",
    "gene_df = refined_ftu_partonomy_data_ct_only[['FTU_partonomy', 'cell_type', 'CT_ID', 'CT_label', 'biomarker-gene']].copy()\n",
    "protein_df = refined_ftu_partonomy_data_ct_only[['FTU_partonomy', 'cell_type', 'CT_ID', 'CT_label', 'biomarker-protein']].copy()\n",
    "\n",
    "# Rename the columns for consistency\n",
    "gene_df = gene_df.rename(columns={'biomarker-gene': 'biomarker'})\n",
    "protein_df = protein_df.rename(columns={'biomarker-protein': 'biomarker'})\n",
    "\n",
    "# Add the Biomarker type column\n",
    "gene_df['biomarker_type'] = 'gene'\n",
    "protein_df['biomarker_type'] = 'protein'\n",
    "\n",
    "# Concatenate the dataframes\n",
    "combined_df = pd.concat([gene_df, protein_df], ignore_index=True)\n",
    "\n",
    "# Perform the merge to add the \"FTU\" information based on matching \"CT ID\" and \"CT ID in CL\"\n",
    "final_df_with_ftu = pd.merge(combined_df, filtered_ftu_data[['CT ID in CL', 'FTU Label in Uberon']], \n",
    "                             left_on='CT_ID', right_on='CT ID in CL', how='left')\n",
    "\n",
    "# Rename the 'FTU Label in Uberon' to 'FTU' to match the request\n",
    "final_df_with_ftu = final_df_with_ftu.rename(columns={'FTU Label in Uberon': 'FTU'})\n",
    "\n",
    "# Drop the redundant 'CT ID in CL' column\n",
    "final_df_with_ftu = final_df_with_ftu.drop(columns=['CT ID in CL'])\n",
    "\n",
    "# Drop rows where \"Biomarker\" is NaN\n",
    "final_refined_ftu_partonomy_data_ct_only = final_df_with_ftu.dropna(subset=['biomarker'])\n",
    "\n",
    "# Split Biomarker column values by \";\" and explode to get separate rows\n",
    "final_refined_ftu_partonomy_data_ct_only['biomarker'] = final_refined_ftu_partonomy_data_ct_only['biomarker'].str.split(';')\n",
    "final_refined_ftu_partonomy_data_ct_only = final_refined_ftu_partonomy_data_ct_only.explode('biomarker').reset_index(drop=True)\n",
    "# Reorder the columns as requested\n",
    "final_refined_ftu_partonomy_data_ct_only = final_refined_ftu_partonomy_data_ct_only[['FTU', 'FTU_partonomy', 'cell_type', 'CT_ID', 'CT_label', 'biomarker', 'biomarker_type']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "599624e3",
   "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>FTU</th>\n",
       "      <th>FTU_partonomy</th>\n",
       "      <th>cell_type</th>\n",
       "      <th>CT_ID</th>\n",
       "      <th>CT_label</th>\n",
       "      <th>biomarker</th>\n",
       "      <th>biomarker_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Renal Corpuscle</td>\n",
       "      <td>kidney--&gt;cortex of kidney--&gt;outer cortex of ki...</td>\n",
       "      <td>podocyte</td>\n",
       "      <td>CL:0000653</td>\n",
       "      <td>Podocyte</td>\n",
       "      <td>NPHS2</td>\n",
       "      <td>gene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Renal Corpuscle</td>\n",
       "      <td>kidney--&gt;cortex of kidney--&gt;outer cortex of ki...</td>\n",
       "      <td>podocyte</td>\n",
       "      <td>CL:0000653</td>\n",
       "      <td>Podocyte</td>\n",
       "      <td>PODXL</td>\n",
       "      <td>gene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Renal Corpuscle</td>\n",
       "      <td>kidney--&gt;cortex of kidney--&gt;outer cortex of ki...</td>\n",
       "      <td>podocyte</td>\n",
       "      <td>CL:0000653</td>\n",
       "      <td>Podocyte</td>\n",
       "      <td>NPHS1</td>\n",
       "      <td>gene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Renal Corpuscle</td>\n",
       "      <td>kidney--&gt;cortex of kidney--&gt;outer cortex of ki...</td>\n",
       "      <td>podocyte</td>\n",
       "      <td>CL:0000653</td>\n",
       "      <td>Podocyte</td>\n",
       "      <td>PTPRQ</td>\n",
       "      <td>gene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Renal Corpuscle</td>\n",
       "      <td>kidney--&gt;cortex of kidney--&gt;juxtamedullary cor...</td>\n",
       "      <td>glomerular visceral epithelial cell</td>\n",
       "      <td>CL:0000653</td>\n",
       "      <td>Podocyte</td>\n",
       "      <td>NPHS2</td>\n",
       "      <td>gene</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               FTU                                      FTU_partonomy  \\\n",
       "0  Renal Corpuscle  kidney-->cortex of kidney-->outer cortex of ki...   \n",
       "1  Renal Corpuscle  kidney-->cortex of kidney-->outer cortex of ki...   \n",
       "2  Renal Corpuscle  kidney-->cortex of kidney-->outer cortex of ki...   \n",
       "3  Renal Corpuscle  kidney-->cortex of kidney-->outer cortex of ki...   \n",
       "4  Renal Corpuscle  kidney-->cortex of kidney-->juxtamedullary cor...   \n",
       "\n",
       "                             cell_type       CT_ID  CT_label biomarker  \\\n",
       "0                             podocyte  CL:0000653  Podocyte     NPHS2   \n",
       "1                             podocyte  CL:0000653  Podocyte     PODXL   \n",
       "2                             podocyte  CL:0000653  Podocyte     NPHS1   \n",
       "3                             podocyte  CL:0000653  Podocyte     PTPRQ   \n",
       "4  glomerular visceral epithelial cell  CL:0000653  Podocyte     NPHS2   \n",
       "\n",
       "  biomarker_type  \n",
       "0           gene  \n",
       "1           gene  \n",
       "2           gene  \n",
       "3           gene  \n",
       "4           gene  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_refined_ftu_partonomy_data_ct_only.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "386dae1e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "113"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_refined_ftu_partonomy_data_ct_only['cell_type'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "a2c9c425",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2918, 7)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_refined_ftu_partonomy_data_ct_only.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "983c6d68",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Cortical Collecting Duct Principal Cell',\n",
       "       'Inner Medullary Collecting Duct Cell',\n",
       "       'Descending Thin Limb Cell', 'Ascending Thin Limb Cell',\n",
       "       'Thick Ascending Limb Cell', 'Macula Densa cell',\n",
       "       'Outer Medullary Collecting Duct Principal Cell',\n",
       "       'Peritubular Capillary Endothelial Cell', 'absorptive', 'goblet',\n",
       "       'airway submucosal gland collecting duct epithelial cell',\n",
       "       'Ductal epithelial cell', 'Centroacinar cell', 'PP cell',\n",
       "       'Macrophage', 'Monocytes', 'Plasmablast', 'Neutrophil',\n",
       "       'Follicular Dendritic Cell (FDC)', 'blood endothelial cell',\n",
       "       'Kupffer cell'], dtype=object)"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for duplicates in the new dataset\n",
    "duplicates = final_refined_ftu_partonomy_data_ct_only[final_refined_ftu_partonomy_data_ct_only.duplicated()]\n",
    "duplicates['CT_label'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "2f144c17",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2183, 7)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deduplicated_df = final_refined_ftu_partonomy_data_ct_only.drop_duplicates()\n",
    "deduplicated_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "01e2f281",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deduplicated_df['FTU'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "206930ff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Final CSV file saved.\n"
     ]
    }
   ],
   "source": [
    "# Save the DataFrames to a CSV file\n",
    "output_path_files = 'output/trios/asctb_ftu-partonomy-ct-info.csv'\n",
    "output_path_json = 'output/trios/asctb_ftu-partonomy-ct-info_v1.csv'\n",
    "output_path_json_sep_biomarker = 'output/trios/asctb_ftu-partonomy-ct-info_v2.csv'\n",
    "\n",
    "ftu_partonomy_content.to_csv(output_path_files, index=False, sep='\\t')\n",
    "refined_ftu_partonomy_data_ct_only.to_csv(output_path_json, index=False, sep='\\t')\n",
    "deduplicated_df.to_csv(output_path_json_sep_biomarker, index=False, sep='\\t')\n",
    "\n",
    "print(f\"Final CSV file saved.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "d09f6f4e",
   "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>ftu</th>\n",
       "      <th>table</th>\n",
       "      <th>ftu_label</th>\n",
       "      <th>as_label</th>\n",
       "      <th>ct_label</th>\n",
       "      <th>bm_label</th>\n",
       "      <th>ftu_as</th>\n",
       "      <th>as</th>\n",
       "      <th>ct</th>\n",
       "      <th>bm</th>\n",
       "      <th>bmType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://purl.humanatlas.io/2d-ftu/kidney-ascen...</td>\n",
       "      <td>https://purl.humanatlas.io/asct-b/kidney</td>\n",
       "      <td>kidney ascending thin loop of henle</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0004193</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://purl.humanatlas.io/2d-ftu/kidney-corti...</td>\n",
       "      <td>https://purl.humanatlas.io/asct-b/kidney</td>\n",
       "      <td>kidney cortical collecting duct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0004203</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://purl.humanatlas.io/2d-ftu/kidney-desce...</td>\n",
       "      <td>https://purl.humanatlas.io/asct-b/kidney</td>\n",
       "      <td>kidney descending thin loop of henle</td>\n",
       "      <td>descending thin limb of loop of Henle</td>\n",
       "      <td>Descending Thin Limb Cell</td>\n",
       "      <td>SPP1</td>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0001289</td>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0005096</td>\n",
       "      <td>http://purl.obolibrary.org/obo/CL_1001111</td>\n",
       "      <td>http://identifiers.org/hgnc/11255</td>\n",
       "      <td>gene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://purl.humanatlas.io/2d-ftu/kidney-desce...</td>\n",
       "      <td>https://purl.humanatlas.io/asct-b/kidney</td>\n",
       "      <td>kidney descending thin loop of henle</td>\n",
       "      <td>descending thin limb of loop of Henle</td>\n",
       "      <td>Descending Thin Limb Cell</td>\n",
       "      <td>CDH1</td>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0001289</td>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0005096</td>\n",
       "      <td>http://purl.obolibrary.org/obo/CL_1001111</td>\n",
       "      <td>http://identifiers.org/hgnc/1748</td>\n",
       "      <td>gene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://purl.humanatlas.io/2d-ftu/kidney-desce...</td>\n",
       "      <td>https://purl.humanatlas.io/asct-b/kidney</td>\n",
       "      <td>kidney descending thin loop of henle</td>\n",
       "      <td>descending thin limb of loop of Henle</td>\n",
       "      <td>Descending Thin Limb Cell</td>\n",
       "      <td>CRYAB</td>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0001289</td>\n",
       "      <td>http://purl.obolibrary.org/obo/UBERON_0005096</td>\n",
       "      <td>http://purl.obolibrary.org/obo/CL_1001111</td>\n",
       "      <td>http://identifiers.org/hgnc/2389</td>\n",
       "      <td>gene</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 ftu  \\\n",
       "0  https://purl.humanatlas.io/2d-ftu/kidney-ascen...   \n",
       "1  https://purl.humanatlas.io/2d-ftu/kidney-corti...   \n",
       "2  https://purl.humanatlas.io/2d-ftu/kidney-desce...   \n",
       "3  https://purl.humanatlas.io/2d-ftu/kidney-desce...   \n",
       "4  https://purl.humanatlas.io/2d-ftu/kidney-desce...   \n",
       "\n",
       "                                      table  \\\n",
       "0  https://purl.humanatlas.io/asct-b/kidney   \n",
       "1  https://purl.humanatlas.io/asct-b/kidney   \n",
       "2  https://purl.humanatlas.io/asct-b/kidney   \n",
       "3  https://purl.humanatlas.io/asct-b/kidney   \n",
       "4  https://purl.humanatlas.io/asct-b/kidney   \n",
       "\n",
       "                              ftu_label  \\\n",
       "0   kidney ascending thin loop of henle   \n",
       "1       kidney cortical collecting duct   \n",
       "2  kidney descending thin loop of henle   \n",
       "3  kidney descending thin loop of henle   \n",
       "4  kidney descending thin loop of henle   \n",
       "\n",
       "                                as_label                   ct_label bm_label  \\\n",
       "0                                    NaN                        NaN      NaN   \n",
       "1                                    NaN                        NaN      NaN   \n",
       "2  descending thin limb of loop of Henle  Descending Thin Limb Cell     SPP1   \n",
       "3  descending thin limb of loop of Henle  Descending Thin Limb Cell     CDH1   \n",
       "4  descending thin limb of loop of Henle  Descending Thin Limb Cell    CRYAB   \n",
       "\n",
       "                                          ftu_as  \\\n",
       "0  http://purl.obolibrary.org/obo/UBERON_0004193   \n",
       "1  http://purl.obolibrary.org/obo/UBERON_0004203   \n",
       "2  http://purl.obolibrary.org/obo/UBERON_0001289   \n",
       "3  http://purl.obolibrary.org/obo/UBERON_0001289   \n",
       "4  http://purl.obolibrary.org/obo/UBERON_0001289   \n",
       "\n",
       "                                              as  \\\n",
       "0                                            NaN   \n",
       "1                                            NaN   \n",
       "2  http://purl.obolibrary.org/obo/UBERON_0005096   \n",
       "3  http://purl.obolibrary.org/obo/UBERON_0005096   \n",
       "4  http://purl.obolibrary.org/obo/UBERON_0005096   \n",
       "\n",
       "                                          ct  \\\n",
       "0                                        NaN   \n",
       "1                                        NaN   \n",
       "2  http://purl.obolibrary.org/obo/CL_1001111   \n",
       "3  http://purl.obolibrary.org/obo/CL_1001111   \n",
       "4  http://purl.obolibrary.org/obo/CL_1001111   \n",
       "\n",
       "                                  bm bmType  \n",
       "0                                NaN    NaN  \n",
       "1                                NaN    NaN  \n",
       "2  http://identifiers.org/hgnc/11255   gene  \n",
       "3   http://identifiers.org/hgnc/1748   gene  \n",
       "4   http://identifiers.org/hgnc/2389   gene  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load the dataset\n",
    "ftu_trios_by_bruce = 'ref_data/asctb-trios-by-ftu.csv'\n",
    "ftu_trios_by_bruce_data = pd.read_csv(ftu_trios_by_bruce)\n",
    "ftu_trios_by_bruce_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "932c0519",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(757, 10)"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Clean the 'ct' column in the asctb_trios_df by removing \"http://purl.obolibrary.org/obo/\" and replacing \"_\" with \":\"\n",
    "ftu_trios_by_bruce_data['ct_clean'] = ftu_trios_by_bruce_data['ct'].str.replace(\"http://purl.obolibrary.org/obo/\", \"\").str.replace(\"_\", \":\", regex=False)\n",
    "\n",
    "# Clean the 'table' column by making 'Organ' lowercase and replacing spaces with underscores before comparing\n",
    "ftu_trios_by_bruce_data['table_clean'] = ftu_trios_by_bruce_data['table'].str.lower().str.replace(\"https://purl.humanatlas.io/asct-b/\", \"\").str.replace(\" \", \"_\")\n",
    "filtered_ftu_data.loc[:, 'organ_clean'] = filtered_ftu_data['Organ'].str.lower().str.replace(\" \", \"_\")\n",
    "\n",
    "# Perform the merge again based on the cleaned columns\n",
    "merged_df_clean = pd.merge(\n",
    "    ftu_trios_by_bruce_data, \n",
    "    filtered_ftu_data, \n",
    "    left_on=['ct_clean', 'table_clean'], \n",
    "    right_on=['CT ID in CL', 'organ_clean'], \n",
    "    how='inner'\n",
    ")\n",
    "\n",
    "final_df_from_trios_ftu_data = merged_df_clean[['ftu', 'ftu_label', 'as_label', 'ct_label', 'bm_label', 'ftu_as', 'as', 'ct', 'bm', 'bmType']]\n",
    "final_df_from_trios_ftu_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "c869adab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array(['kidney descending thin loop of henle', 'kidney nephron',\n",
       "        'kidney renal corpuscle', 'kidney thick ascending loop of henle',\n",
       "        'liver liver lobule', 'lung pulmonary alveolus', 'spleen red pulp',\n",
       "        'spleen white pulp', 'thymus thymus lobule'], dtype=object),\n",
       " array(['Descending Thin Limb Cell', 'Podocyte',\n",
       "        'Parietal Epithelial Cell', 'Mesangial Cell',\n",
       "        'Glomerular Capillary Endothelial Cell',\n",
       "        'Proximal Tubule Epithelial Cell', 'Ascending Thin Limb Cell',\n",
       "        'Connecting Tubule Cell', 'Distal Convoluted Tubule Cell',\n",
       "        'Thick Ascending Limb Cell', 'Macula Densa cell',\n",
       "        'Proximal Tubule Epithelial Cell Segment 1', 'hepatic oval cell',\n",
       "        'quiescent hepatic stellate cell', 'endothelial cell',\n",
       "        'hepatocyte', 'liver sinusoid endothelial cell', 'Kupffer cell',\n",
       "        'cuboidal cell (Small cholangiocytes are cuboidal)',\n",
       "        'smooth muscle cell', 'portal fibroblast',\n",
       "        'columnar cell (larger cholangiocytes are more columnar) has ciliae',\n",
       "        'alveolar type 1 fibroblast cell', 'lung pericyte',\n",
       "        'CAP1 general capillary gCap', 'alveolar type I cell',\n",
       "        'alveolar type II cell', 'Splenic endothelial cell',\n",
       "        'Splenic Stromal Cell (non-FRC/non-FDC)', 'Macrophage',\n",
       "        'Hematopoetic Stem Cell', 'Red Pulp Reticular Cell', 'Plasmablast',\n",
       "        'Monocytes', 'Dendritic Cell', 'Follicular Dendritic Cell (FDC)',\n",
       "        'Tingible Body Macrophage', 'Splenic Smooth Muscle Cell',\n",
       "        'blood endothelial cell', 'plasmacytoid dendritic cell',\n",
       "        'double positive thymocyte', 'beige adipocyte',\n",
       "        'corticomedullary thymic epithelial cell', 'naive B cell',\n",
       "        'macrophage', 'medullary thymic epithelial cell type 1',\n",
       "        'subcapsular cortical epithelial cell'], dtype=object))"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df_from_trios_ftu_data['ftu_label'].unique(), final_df_from_trios_ftu_data['ct_label'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "55c68419",
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df_from_trios_ftu_data.to_csv('output/trios/ftu_trios_from_ftu_asctb_trios.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4c92f90",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}