flat_dcp_to_tier1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from metadata_dict import (dcp_to_tier1_mapping, tier1, kidney_core_alignment)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "bionetwork = \"gut/\"\n",
    "dcp_path = \"./dcp_spreadsheets/\" + bionetwork\n",
    "flat_path = \"./flat_dcp/\" + bionetwork\n",
    "tier1_path = \"./tier1_output/\" + bionetwork\n",
    "file_name = \"ImmuneLandscapeccRCC_metadata_30-01-2023.xlsx\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "fde434df656943bd9c9c70b74cceea61",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Dropdown(description='Report Entity:', options=('HumanMouseEntericNervousSystem_metadata_06-12-2024.xlsx',), v…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import ipywidgets as widgets\n",
    "from IPython.display import display\n",
    "file_name_dropdown = widgets.Dropdown(\n",
    "    options= os.listdir(path=dcp_path),\n",
    "    # value=file_name or 'ImmuneLandscapeccRCC_metadata_30-01-2023.xlsx',\n",
    "    description='Report Entity:',\n",
    "    disabled=False,\n",
    ")\n",
    "display(file_name_dropdown)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "HumanMouseEntericNervousSystem_metadata_06-12-2024.xlsx\n"
     ]
    }
   ],
   "source": [
    "file_name = file_name_dropdown.value\n",
    "print(file_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "dcp_spreadsheet = pd.read_excel(os.path.join(dcp_path, file_name), sheet_name=None, skiprows= [0,1,2,4])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uns tab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "project_info = dcp_spreadsheet['Project']\n",
    "contributors = pd.read_excel(os.path.join(dcp_path, file_name), sheet_name='Project - Contributors', skiprows= [0,1,2,4])\n",
    "\n",
    "\n",
    "uns = {\n",
    "    \"title\": project_info.iloc[0]['project.project_core.project_title'], \n",
    "    # select the last listed contributor that is corresponding\n",
    "    \"study_pi\": contributors['project.contributors.name']\\\n",
    "                    [max(\\\n",
    "                        contributors[contributors['project.contributors.corresponding_contributor'].isin([True, 'yes'])]\\\n",
    "                            .index)]\n",
    "    }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "uns = pd.DataFrame(uns, index=[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Obs tab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "flat_biomaterial = pd.read_csv(os.path.join(flat_path, file_name.replace(\".xlsx\", \"_flat_biomaterial.csv\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "flat_tier1 = flat_biomaterial.rename(columns=dcp_to_tier1_mapping)\\\n",
    "    .drop(columns=[col for col in flat_biomaterial if col not in dcp_to_tier1_mapping.keys()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/cg/kjj0b2v92rx2c4s4d0wc5by80000gp/T/ipykernel_35697/3495121067.py:4: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value 'surgical donor' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  flat_tier1.loc[(flat_tier1['sample_source_alive'] == 'yes'), 'sample_source'] = 'surgical donor'\n"
     ]
    }
   ],
   "source": [
    "if 'sample_source_alive' in flat_tier1.keys():\n",
    "    flat_tier1['sample_source'] = np.nan\n",
    "    # fill living with surgical and deceaced with post-mortem, and if there is transplant donor information overwrite\n",
    "    flat_tier1.loc[(flat_tier1['sample_source_alive'] == 'yes'), 'sample_source'] = 'surgical donor'\n",
    "    flat_tier1.loc[(flat_tier1['sample_source_alive'] == 'no'), 'sample_source'] = 'postmortem donor'\n",
    "    \n",
    "    if  'sample_source_organ' in flat_tier1.keys():\n",
    "        flat_tier1.loc[(flat_tier1['sample_source_organ'] == 'yes'), 'sample_source'] = 'organ donor'\n",
    "        flat_tier1.drop(columns=['sample_source_organ'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "if 'sex_ontology_term_id' in flat_tier1.keys():\n",
    "    sex_ontology = {\n",
    "        'sex_ontology_term_id':\n",
    "            {\n",
    "                'female': 'PATO:0000383',\n",
    "                'male': 'PATO:0000384'\n",
    "            }           \n",
    "    }\n",
    "    flat_tier1.replace(sex_ontology, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def library_to_tissue_type(library_id, dcp_spreadsheet):\n",
    "    tissue_type_dict = {\n",
    "        'specimen_from_organism.biomaterial_core.biomaterial_id': 'tissue',\n",
    "        'cell_line.biomaterial_core.biomaterial_id': 'cell culture',\n",
    "        'organoid.biomaterial_core.biomaterial_id': 'organoid'\n",
    "    }\n",
    "    row = dcp_spreadsheet['Cell suspension'][dcp_spreadsheet['Cell suspension']['cell_suspension.biomaterial_core.biomaterial_id'] == library_id]\n",
    "    tissue_type = [tissue_type for dcp_type, tissue_type in tissue_type_dict.items() if dcp_type in row.columns and not any(row[dcp_type].isna())]\n",
    "    if len(tissue_type) > 1:\n",
    "        raise ValueError(f'Multiple input biomaterials for {library_id}')\n",
    "    return tissue_type[0]\n",
    "\n",
    "flat_tier1['tissue_type'] = flat_tier1['library_id'].apply(lambda x: library_to_tissue_type(x, dcp_spreadsheet))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'Series' object has no attribute 'age_unit'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m/var/folders/cg/kjj0b2v92rx2c4s4d0wc5by80000gp/T/ipykernel_35697/2504894844.py\u001b[0m in \u001b[0;36m?\u001b[0;34m()\u001b[0m\n\u001b[1;32m     57\u001b[0m     \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Age of donors not provided\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     58\u001b[0m \u001b[0;32melif\u001b[0m \u001b[0mflat_tier1\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'organism_ontology_term_id'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0misin\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m9606\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0many\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     59\u001b[0m     flat_tier1['development_stage_ontology_term_id'] = \\\n\u001b[1;32m     60\u001b[0m         flat_tier1[flat_tier1['organism_ontology_term_id'] == 9606]\\\n\u001b[0;32m---> 61\u001b[0;31m             .apply(lambda x: convert_to_years(x.age, x.age_unit), axis=1)\\\n\u001b[0m\u001b[1;32m     62\u001b[0m             .dropna()\\\n\u001b[1;32m     63\u001b[0m             \u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mage_to_dev\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     64\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/Desktop/dcp_to_tier1/.venv/lib/python3.12/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m?\u001b[0;34m(self, func, axis, raw, result_type, args, by_row, engine, engine_kwargs, **kwargs)\u001b[0m\n\u001b[1;32m  10357\u001b[0m             \u001b[0mengine_kwargs\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mengine_kwargs\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m  10358\u001b[0m             \u001b[0margs\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m  10359\u001b[0m             \u001b[0mkwargs\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m  10360\u001b[0m         \u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m> 10361\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mop\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__finalize__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"apply\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/Desktop/dcp_to_tier1/.venv/lib/python3.12/site-packages/pandas/core/apply.py\u001b[0m in \u001b[0;36m?\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    912\u001b[0m         \u001b[0;31m# raw\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    913\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraw\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    914\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply_raw\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mengine_kwargs\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine_kwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    915\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 916\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply_standard\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/Desktop/dcp_to_tier1/.venv/lib/python3.12/site-packages/pandas/core/apply.py\u001b[0m in \u001b[0;36m?\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1061\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mapply_standard\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1062\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m\"python\"\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1063\u001b[0;31m             \u001b[0mresults\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mres_index\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply_series_generator\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1064\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1065\u001b[0m             \u001b[0mresults\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mres_index\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mapply_series_numba\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1066\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/Desktop/dcp_to_tier1/.venv/lib/python3.12/site-packages/pandas/core/apply.py\u001b[0m in \u001b[0;36m?\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1077\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1078\u001b[0m         \u001b[0;32mwith\u001b[0m \u001b[0moption_context\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"mode.chained_assignment\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1079\u001b[0m             \u001b[0;32mfor\u001b[0m \u001b[0mi\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mv\u001b[0m \u001b[0;32min\u001b[0m \u001b[0menumerate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mseries_gen\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1080\u001b[0m                 \u001b[0;31m# ignore SettingWithCopy here in case the user mutates\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1081\u001b[0;31m                 \u001b[0mresults\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mi\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfunc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mv\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1082\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresults\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mi\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mABCSeries\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1083\u001b[0m                     \u001b[0;31m# If we have a view on v, we need to make a copy because\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1084\u001b[0m                     \u001b[0;31m#  series_generator will swap out the underlying data\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/var/folders/cg/kjj0b2v92rx2c4s4d0wc5by80000gp/T/ipykernel_35697/2504894844.py\u001b[0m in \u001b[0;36m?\u001b[0;34m(x)\u001b[0m\n\u001b[0;32m---> 61\u001b[0;31m             .apply(lambda x: convert_to_years(x.age, x.age_unit), axis=1)\\\n\u001b[0m\u001b[1;32m     62\u001b[0m             .dropna()\\\n\u001b[1;32m     63\u001b[0m             \u001b[0;34m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mage_to_dev\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/Desktop/dcp_to_tier1/.venv/lib/python3.12/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36m?\u001b[0;34m(self, name)\u001b[0m\n\u001b[1;32m   6292\u001b[0m             \u001b[0;32mand\u001b[0m \u001b[0mname\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_accessors\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   6293\u001b[0m             \u001b[0;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_info_axis\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_can_hold_identifiers_and_holds_name\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   6294\u001b[0m         \u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   6295\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 6296\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mobject\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__getattribute__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m: 'Series' object has no attribute 'age_unit'"
     ]
    }
   ],
   "source": [
    "age_to_dev_dict = {\n",
    "    (0, 1): 'HsapDv:0000261',\n",
    "    (1, 4): 'HsapDv:0000265',\n",
    "    (5, 14): 'HsapDv:0000264',\n",
    "    (15, 19): 'HsapDv:0000268',\n",
    "    (20, 29): 'HsapDv:0000237',\n",
    "    (30, 39): 'HsapDv:0000238',\n",
    "    (40, 49): 'HsapDv:0000239',\n",
    "    (50, 59): 'HsapDv:0000240',\n",
    "    (60, 69): 'HsapDv:0000241',\n",
    "    (70, 79): 'HsapDv:0000242',\n",
    "    (80, 89): 'HsapDv:0000243'\n",
    "}\n",
    "\n",
    "def convert_to_years(age, age_unit):\n",
    "    if isinstance(age, str) and '-' in age:\n",
    "        if age_unit == 'year':\n",
    "            return age\n",
    "        else:\n",
    "            print(\"Can't convert range to years\")\n",
    "            return age\n",
    "    age_to_years = {\n",
    "    'year': 1,\n",
    "    'month': 12,\n",
    "    'day': 365\n",
    "    }\n",
    "    try:\n",
    "        return int(age) / age_to_years[age_unit]\n",
    "    except ValueError:\n",
    "        print(\"Age \" + str(age) + \" is not a number\")\n",
    "\n",
    "def age_to_dev(age, age_to_dev_dict=age_to_dev_dict):\n",
    "    # TODO add a way to record the following options\n",
    "    # Unknown = unknown\n",
    "    # Embryonic stage = A term from the set of Carnegie stages 1-23 = (up to 8 weeks after conception; e.g. HsapDv:0000003)\n",
    "    # Fetal development = A term from the set of 9 to 38 week post-fertilization human stages = (9 weeks after conception and before birth; e.g. HsapDv:0000046)\n",
    "    if isinstance(age, str) and '-' in age:\n",
    "        age = [int(age) for age in age.split('-')]\n",
    "        for age_range, label in age_to_dev_dict.items():\n",
    "            if age_range[0] <= age[0] <= age_range[1] and \\\n",
    "                    age_range[0] <= age[1] <= age_range[1]:\n",
    "                return label\n",
    "            elif age_range[0] <= np.mean(age) <= age_range[1]:\n",
    "                print(f\"Given range {age} overlaps the acceptable ranges.\",\n",
    "                       f\"We will use the mean age {np.mean(age)} to assing to range.\")\n",
    "                return label\n",
    "    elif isinstance(age, int) or isinstance(age, float) or (isinstance(age, str) and age.isdigit()):\n",
    "        age = float(age) if isinstance(age, str) else age\n",
    "        for age_range, label in age_to_dev_dict.items():\n",
    "            if age_range[0] <= age <= age_range[1]:\n",
    "                return label\n",
    "    print(f\"Age {age} could not be mapped to accepted ranges {['-'.join(map(str, age)) for age in age_to_dev_dict.keys()]}\")\n",
    "    return str(age[0]) + '-' + str(age[1])\n",
    "\n",
    "\n",
    "if 'age' not in flat_tier1.keys():\n",
    "    print(\"Age of donors not provided\")\n",
    "elif flat_tier1['organism_ontology_term_id'].isin([9606]).any():\n",
    "    flat_tier1['development_stage_ontology_term_id'] = \\\n",
    "        flat_tier1[flat_tier1['organism_ontology_term_id'] == 9606]\\\n",
    "            .apply(lambda x: convert_to_years(x.age, x.age_unit), axis=1)\\\n",
    "            .dropna()\\\n",
    "            .apply(age_to_dev)\n",
    "else:\n",
    "    print(\"Not human organism. Please convert the age to developomental stage ontology manually.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For this dataset the preservation and storage method has not been submitted, but it is mentioned in the article that the samples were snap frozen with liquid nitrogen & stored at -80C\n",
    "if 'sample_preservation_method' not in flat_tier1.keys() \\\n",
    "    and file_name == 'ImmuneLandscapeccRCC_metadata_30-01-2023.xlsx':\n",
    "        flat_tier1['sample_preservation_method'] = 'frozen in liquid nitrogen'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# change names to be in align with CELLxGENE schema\n",
    "suspension_type_dict = {\n",
    "    'suspension_type':\n",
    "    {\n",
    "        'single cell': 'cell',\n",
    "        'single nucleus': 'nucleus'\n",
    "    }\n",
    "}\n",
    "flat_tier1.replace(suspension_type_dict, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "cell_enrichment_dict = {\n",
    "    'cell_enrichment':\n",
    "    {\n",
    "        'DAPI-': 'na',\n",
    "        np.nan: 'na'\n",
    "    }\n",
    "}\n",
    "if 'cell_enrichment' in flat_tier1 and \\\n",
    "    any(flat_tier1['cell_enrichment'].isin(cell_enrichment_dict.values())):\n",
    "        print(flat_tier1['cell_enrichment'].unique())\n",
    "flat_tier1.replace(cell_enrichment_dict, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Almost every dataset we have wrangled is primary data. No way to automate this one as DCP does not record this information\n",
    "flat_tier1['is_primary_data'] = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'HumanMouseEntericNervousSystem'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[21], line 4\u001b[0m\n\u001b[1;32m      2\u001b[0m     flat_tier1[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124malignment_software\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m flat_tier1[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124malignment_software\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39mastype(\u001b[38;5;28mstr\u001b[39m) \u001b[38;5;241m+\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m \u001b[39m\u001b[38;5;124m\"\u001b[39m \u001b[38;5;241m+\u001b[39m flat_tier1[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124malignment_software_version\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39mastype(\u001b[38;5;28mstr\u001b[39m)\n\u001b[1;32m      3\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m----> 4\u001b[0m     flat_tier1[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124malignment_software\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m \u001b[43mkidney_core_alignment\u001b[49m\u001b[43m[\u001b[49m\u001b[43mfile_name\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43msplit\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m_metadata\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;241;43m0\u001b[39;49m\u001b[43m]\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mreplace\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m.xlsx\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m]\u001b[49m\n\u001b[1;32m      5\u001b[0m     \u001b[38;5;66;03m# this field was not in DCP schema when we wrangled this dataset, therefore we add it manually\u001b[39;00m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'HumanMouseEntericNervousSystem'"
     ]
    }
   ],
   "source": [
    "if 'alignment_software' in flat_tier1.keys() and 'alignment_software_version' in flat_tier1.keys():\n",
    "    flat_tier1['alignment_software'] = flat_tier1['alignment_software'].astype(str) + \" \" + flat_tier1['alignment_software_version'].astype(str)\n",
    "else:\n",
    "    flat_tier1['alignment_software'] = kidney_core_alignment[file_name.split('_metadata')[0].replace('.xlsx', '')]\n",
    "    # this field was not in DCP schema when we wrangled this dataset, therefore we add it manually"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "flat_tier1['organism_ontology_term_id'] = 'NCBITaxon:' + flat_tier1['organism_ontology_term_id'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If we have more detailed UBERON ontology, use that instead\n",
    "if 'tissue_ontology_term_id_parts' in flat_tier1:\n",
    "    flat_tier1['tissue_free_text'] = flat_tier1['tissue_free_text_parts']\n",
    "    flat_tier1['tissue_ontology_term_id'] = flat_tier1['tissue_ontology_term_id_parts']\n",
    "    flat_tier1['tissue_free_label_text'] = flat_tier1['tissue_free_text_label_parts']\n",
    "\n",
    "# If text is identical to ontology label, then no need to include _free_text field\n",
    "flat_tier1['tissue_free_text'] = flat_tier1.apply(\\\n",
    "    lambda row: '' if row['tissue_free_text_label'] == row['tissue_free_text'] \\\n",
    "        else row['tissue_free_text'], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manual inspection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Are these the library IDs that are listed in the repository? Or just a generic name?\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>library_id</th>\n",
       "      <th>library_id_repository_name</th>\n",
       "      <th>library_id_repository_description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S101_mouse_colon_cell_suspension_plate</td>\n",
       "      <td>S101 mouse colon plate cell suspension</td>\n",
       "      <td>mouse colon cell suspension - plate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S100_mouse_colon_cell_suspension_plate</td>\n",
       "      <td>S100 mouse colon plate cell suspension</td>\n",
       "      <td>mouse colon cell suspension - plate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S102_mouse_colon_cell_suspension_plate</td>\n",
       "      <td>S102 mouse colon plate cell suspension</td>\n",
       "      <td>mouse colon cell suspension - plate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S40_mouse_colon_cell_suspension_plate</td>\n",
       "      <td>S40 mouse colon plate cell suspension</td>\n",
       "      <td>mouse colon cell suspension - plate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>S38_mouse_colon_cell_suspension_plate</td>\n",
       "      <td>S38 mouse colon plate cell suspension</td>\n",
       "      <td>mouse colon cell suspension - plate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>293</th>\n",
       "      <td>S61_human_colon_cell_suspension</td>\n",
       "      <td>S61 human colon sample cell suspension</td>\n",
       "      <td>human colon cell suspension - droplet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>294</th>\n",
       "      <td>S62_human_colon_cell_suspension</td>\n",
       "      <td>S62 human colon sample cell suspension</td>\n",
       "      <td>human colon cell suspension - droplet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295</th>\n",
       "      <td>S59_human_colon_cell_suspension</td>\n",
       "      <td>S59 human colon sample cell suspension</td>\n",
       "      <td>human colon cell suspension - droplet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>S15_human_colon_cell_suspension</td>\n",
       "      <td>S15 human colon sample cell suspension</td>\n",
       "      <td>human colon cell suspension - droplet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297</th>\n",
       "      <td>S58_human_colon_cell_suspension</td>\n",
       "      <td>S58 human colon sample cell suspension</td>\n",
       "      <td>human colon cell suspension - droplet</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>174 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 library_id  \\\n",
       "0    S101_mouse_colon_cell_suspension_plate   \n",
       "2    S100_mouse_colon_cell_suspension_plate   \n",
       "4    S102_mouse_colon_cell_suspension_plate   \n",
       "6     S40_mouse_colon_cell_suspension_plate   \n",
       "8     S38_mouse_colon_cell_suspension_plate   \n",
       "..                                      ...   \n",
       "293         S61_human_colon_cell_suspension   \n",
       "294         S62_human_colon_cell_suspension   \n",
       "295         S59_human_colon_cell_suspension   \n",
       "296         S15_human_colon_cell_suspension   \n",
       "297         S58_human_colon_cell_suspension   \n",
       "\n",
       "                 library_id_repository_name  \\\n",
       "0    S101 mouse colon plate cell suspension   \n",
       "2    S100 mouse colon plate cell suspension   \n",
       "4    S102 mouse colon plate cell suspension   \n",
       "6     S40 mouse colon plate cell suspension   \n",
       "8     S38 mouse colon plate cell suspension   \n",
       "..                                      ...   \n",
       "293  S61 human colon sample cell suspension   \n",
       "294  S62 human colon sample cell suspension   \n",
       "295  S59 human colon sample cell suspension   \n",
       "296  S15 human colon sample cell suspension   \n",
       "297  S58 human colon sample cell suspension   \n",
       "\n",
       "         library_id_repository_description  \n",
       "0      mouse colon cell suspension - plate  \n",
       "2      mouse colon cell suspension - plate  \n",
       "4      mouse colon cell suspension - plate  \n",
       "6      mouse colon cell suspension - plate  \n",
       "8      mouse colon cell suspension - plate  \n",
       "..                                     ...  \n",
       "293  human colon cell suspension - droplet  \n",
       "294  human colon cell suspension - droplet  \n",
       "295  human colon cell suspension - droplet  \n",
       "296  human colon cell suspension - droplet  \n",
       "297  human colon cell suspension - droplet  \n",
       "\n",
       "[174 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "if 'library_id_repository' not in flat_tier1.keys():\n",
    "    print(\"Are these the library IDs that are listed in the repository? Or just a generic name?\")\n",
    "    display(flat_tier1[['library_id', 'library_id_repository_name', 'library_id_repository_description']].drop_duplicates())\n",
    "if False:\n",
    "    # del flat_tier1['library_id_repository']\n",
    "    flat_tier1['library_id_repository'] = flat_tier1['library_id_repository_name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# if we have pooled diseases, we would need to select one. by default select the first and print what was not selected\n",
    "if 'disease_ontology_term_id' in flat_tier1.keys():\n",
    "    unique_diseases = flat_tier1['disease_ontology_term_id'].str.split(\"\\\\|\\\\|\", expand=True, n=1).drop_duplicates().dropna()\n",
    "    if unique_diseases.shape[1] > 1:\n",
    "        selected_disease = \", \".join(np.unique(unique_diseases[0]))\n",
    "        unselected_diseases = \" and \".join(unique_diseases[1])\n",
    "        print(f\"From pooled diseases, we will use {selected_disease}, instead of {unselected_diseases}\")\n",
    "\n",
    "        flat_tier1['disease_ontology_term_id'] = flat_tier1['disease_ontology_term_id'].str.split(\"\\\\|\\\\|\").str[0]\n",
    "\n",
    "    if False:\n",
    "        flat_tier1.loc[(flat_tier1['disease_ontology_term_id'] == 'POOLED DISEASES'), 'disease_ontology_term_id'] = 'SELECTED DISEASE'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/cg/kjj0b2v92rx2c4s4d0wc5by80000gp/T/ipykernel_35697/2755595383.py:4: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value 'healthy' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  flat_tier1.loc[(flat_tier1['sampled_site_condition_donor'] == 'normal') & \\\n"
     ]
    }
   ],
   "source": [
    "# diseased donor and healthy specimen does not mean adjacent every time. This needs to be inspected manually\n",
    "if 'sampled_site_condition_donor' in flat_tier1.keys() and 'sampled_site_condition_specimen' in flat_tier1.keys():\n",
    "    flat_tier1['sampled_site_condition'] = np.nan\n",
    "    flat_tier1.loc[(flat_tier1['sampled_site_condition_donor'] == 'normal') & \\\n",
    "                (flat_tier1['sampled_site_condition_specimen'] == 'normal'), 'sampled_site_condition'] = 'healthy'\n",
    "    flat_tier1.loc[(flat_tier1['sampled_site_condition_donor'] != 'normal') & \\\n",
    "                (flat_tier1['sampled_site_condition_specimen'] == 'normal'), 'sampled_site_condition'] = 'adjacent'\n",
    "    flat_tier1.loc[(flat_tier1['sampled_site_condition_specimen'] != 'normal'), 'sampled_site_condition'] = 'diseased'\n",
    "\n",
    "    if any(flat_tier1['sampled_site_condition'] == 'adjacent'):\n",
    "        print(\"Please investigate if diseases of donor could be diseases in specimen,\",\n",
    "            \"in order to define healthy or adjacent sampled_site_condition\")\n",
    "        display(flat_tier1.loc[flat_tier1['sampled_site_condition'] == 'adjacent', \\\n",
    "                            ['sampled_site_condition', 'sampled_site_condition_donor', 'sampled_site_condition_specimen', 'tissue_free_text']])\n",
    "\n",
    "    if False:\n",
    "        flat_tier1.loc[(flat_tier1['sampled_site_condition_donor'] == 'TYPE NOT ADJACENT DISEASE') & \\\n",
    "                    (flat_tier1['sampled_site_condition_specimen'] == 'normal'), 'sampled_site_condition'] = 'healthy'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "if 'manner_of_death' not in flat_tier1.keys():\n",
    "    flat_tier1['manner_of_death'] = 'unknown'\n",
    "flat_tier1['manner_of_death'] = flat_tier1['manner_of_death'].apply(str)\n",
    "\n",
    "if 'sample_source_alive' in flat_tier1.keys():\n",
    "    flat_tier1.loc[(flat_tier1['sample_source_alive'] == 'yes'), 'manner_of_death'] = 'not applicable'\n",
    "if 'manner_of_death_string' in flat_tier1.keys() and \\\n",
    "    not flat_tier1['manner_of_death'].isin(['0', '1', '2', '3', '4', 'unknown']).any():\n",
    "    print(\"We have the following cause of death values. Could we convert that in hardy scale?\")\n",
    "    display(flat_tier1.loc[(pd.notna(flat_tier1['manner_of_death_string']),'manner_of_death_string')].unique())\n",
    "\n",
    "if False:\n",
    "    flat_tier1.loc[(flat_tier1['manner_of_death_string'] == 'SELECTED CAUSE OF DEATH'), 'manner_of_death'] = 'HARDY SCALE VALUE'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Group by sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Template is at sample level, therefore all library level fields will be collapsed with comma\n",
    "def collapse_values(series):\n",
    "    return \", \".join(series.unique().astype(str))\n",
    "\n",
    "flat_tier1_sample = flat_tier1.groupby('sample_id').agg(collapse_values)\n",
    "\n",
    "# keep only tier 1 fields, remove helper fields\n",
    "flat_tier1_sample = flat_tier1_sample.reset_index().reindex(columns=tier1['obs'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Required fields filled by 59.0%\n",
      "Optional fields filled by 13.0%\n"
     ]
    }
   ],
   "source": [
    "from metadata_dict import tier1_required\n",
    "\n",
    "req_count = sum([col in tier1_required['MUST'] for col in flat_tier1.columns])\n",
    "req_percent = round(req_count/len(tier1_required['MUST']),2) * 100\n",
    "opt_count = sum([col in tier1_required['RECOMMENDED'] for col in flat_tier1.columns])\n",
    "opt_percent = round(opt_count/len(tier1_required['RECOMMENDED']),2) * 100\n",
    "\n",
    "pd.DataFrame({'File name': [file_name], 'Required filled': [req_count], 'Required Percentage': [req_percent], \\\n",
    "              'Optional filled': [opt_count], 'Optional Percentage': [opt_percent]})\\\n",
    "                .to_csv('filled_values.csv', mode='a', index=False, header=False)\n",
    "\n",
    "print(f\"Required fields filled by {req_percent}%\")\n",
    "print(f\"Optional fields filled by {opt_percent}%\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export to xlsx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "tier1_output = tier1_path + file_name.replace(\" \", \"_\").replace('.xlsx', \"_Tier1.xlsx\")\n",
    "! cp 'HCA_Tier_1_Technical_Metadata_template_v0.1.xlsx' $tier1_output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "tier1_spreadsheet = {}\n",
    "tier1_spreadsheet['Tier 1_uns'] = uns\n",
    "tier1_spreadsheet['Tier 1_obs'] = flat_tier1_sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "excel_dict = pd.read_excel(tier1_output, header=1, sheet_name=None)\n",
    "for sheet_name, excel_df in excel_dict.items():\n",
    "    if sheet_name in tier1_spreadsheet:\n",
    "        py_df = tier1_spreadsheet[sheet_name]\n",
    "        excel_dict[sheet_name] = pd.concat([excel_df, py_df])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(tier1_output) as writer:\n",
    "    for tab in excel_dict:\n",
    "        excel_dict[tab].to_excel(writer, sheet_name=tab, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "tier1_spreadsheet['Tier 1_uns'].to_csv(os.path.join(tier1_path, file_name.replace('.xlsx', '_tier1_uns.csv')))\n",
    "tier1_spreadsheet['Tier 1_obs'].to_csv(os.path.join(tier1_path, file_name.replace('.xlsx', '_tier1_obs.csv')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.12.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}