2023 Biohackathon Europe - Whyqd conversion of ISA to GEO.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "1f6be14b-31de-41a8-b3dc-9b40acb866d5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-11-02 15:53:41\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2023-11-02 16:53:43,797\tINFO util.py:159 -- Missing packages: ['ipywidgets']. Run `pip install -U ipywidgets`, then restart the notebook server for rich notebook output.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.97 s, sys: 1.15 s, total: 3.12 s\n",
      "Wall time: 2.33 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "###################################################################################################\n",
    "### IMPORTS\n",
    "###################################################################################################\n",
    "from time import gmtime, strftime\n",
    "print(strftime(\"%Y-%m-%d %H:%M:%S\", gmtime()))\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "import whyqd as qd\n",
    "import modin.pandas as pd\n",
    "from pathlib import Path\n",
    "import re\n",
    "import random"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "582ba1aa-273e-40cb-a37e-f82944dc4db9",
   "metadata": {},
   "outputs": [],
   "source": [
    "DIRECTORY = \"/mnt/c/Users/turuk/Documents/GitHub/biohackathon-assets/test/data/bucket/\"\n",
    "FILENAMES = [\"isa.study.xlsx\", \"isa.assay.xlsx\"]\n",
    "SCHEMA = \"geo-format.SCHEMA\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b179021c-3403-4d5e-8329-55ff89320344",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2023-11-02 16:53:48,096\tWARNING services.py:1889 -- WARNING: The object store is using /tmp instead of /dev/shm because /dev/shm has only 4145565696 bytes available. This will harm performance! You may be able to free up space by deleting files in /dev/shm. If you are inside a Docker container, you can increase /dev/shm size by passing '--shm-size=6.15gb' to 'docker run' (or add it to the run_options list in a Ray cluster config). Make sure to set this to more than 30% of available RAM.\n",
      "2023-11-02 16:53:49,343\tINFO worker.py:1642 -- Started a local Ray instance.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2.92 s, sys: 483 ms, total: 3.41 s\n",
      "Wall time: 10.4 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "###################################################################################################\n",
    "### 1. IMPORT SOURCE BUCKET OF FILES / TABS\n",
    "###################################################################################################\n",
    "# 1. Loop bucket and extract all tables\n",
    "# 2. Test is a process: fields 'Source Name', 'Sample Name', 'Raw Data File' 'Derived Data File'\n",
    "# 3. Sort into list of df_processes and df_other\n",
    "# , skip_blank_lines=True, set(['Source Name', 'Sample Name', 'Raw Data File' 'Derived Data File']) in set(df.columns)\n",
    "\n",
    "# Special extra is identifying key-value terms only available in the process data files\n",
    "special_other_terms = [\n",
    "    {\n",
    "        \"source\": \"Parameter [Genome reference sequence]\",\n",
    "        \"destination\": \"genome build/assembly\",\n",
    "        \"value\": \"\",\n",
    "    },\n",
    "    {\n",
    "        \"source\": \"Parameter [Processed data file format]\",\n",
    "        \"destination\": \"processed data files format and content\",\n",
    "        \"value\": \"\",\n",
    "    }\n",
    "]\n",
    "\n",
    "df_processes = []\n",
    "df_other = []\n",
    "for FILENAME in FILENAMES:\n",
    "    datasource = qd.DataSourceDefinition()\n",
    "    DATASOURCE_PATH = Path(DIRECTORY) / FILENAME\n",
    "    MIMETYPE = \"XLSX\"\n",
    "    datasource.derive_model(source=DATASOURCE_PATH, mimetype=MIMETYPE, keep_default_na=False) #, skip_blank_lines=True)\n",
    "    for ds in datasource.get:\n",
    "        ds_in = qd.DataSourceDefinition(source=ds)\n",
    "        df_in = ds_in.get_data()\n",
    "        df_in.dropna(how=\"all\")\n",
    "        if any([c.startswith(\"Unnamed:\") for c in df_in.columns]):\n",
    "            headers = df_in.iloc[0].values\n",
    "            df_in.columns = headers\n",
    "            df_in.drop(index=0, axis=0, inplace=True)\n",
    "        if all([any([c.startswith(\"Input [\") for c in df_in.columns]), any([c.startswith(\"Output [\") for c in df_in.columns])]):\n",
    "            df_processes.append(df_in)\n",
    "            # Deal with the specials\n",
    "            for special in special_other_terms:\n",
    "                if special[\"source\"] in df_in.columns:\n",
    "                    special[\"value\"] = df_in[special[\"source\"]].iloc[0]                        \n",
    "        else:\n",
    "            df_other.append(df_in)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f58c62c3-9f0a-4a7a-be13-583b0492421b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2, 4)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df_in.loc[1, \"Source Name\"], df_in.loc[1, \"Sample Name\"]\n",
    "len(df_other), len(df_processes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "fe9bc6da-cd5e-4255-ba6a-64449a1dbf11",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'source': 'Parameter [Genome reference sequence]',\n",
       "  'destination': 'genome build/assembly',\n",
       "  'value': 'CCGAGTGGTA, GATTTAACGG'},\n",
       " {'source': 'Parameter [Processed data file format]',\n",
       "  'destination': 'processed data files format and content',\n",
       "  'value': 'mzML format'}]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "special_other_terms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "8a5ad097-4fcc-4e59-bf1c-c909dc0cf059",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'index': 4, 'inputValue': 'c', 'outputValue': 'e'},\n",
       " {'index': 5, 'inputValue': 'c', 'outputValue': 'd'},\n",
       " {'index': 1, 'inputValue': 'a', 'outputValue': 'b'},\n",
       " {'index': 3, 'inputValue': 'b', 'outputValue': 'f'},\n",
       " {'index': 0, 'inputValue': 'z', 'outputValue': 'a'},\n",
       " {'index': 7, 'inputValue': 'f', 'outputValue': 'h'},\n",
       " {'index': 2, 'inputValue': 'b', 'outputValue': 'c'},\n",
       " {'index': 8, 'inputValue': 'h', 'outputValue': 'i'},\n",
       " {'index': 6, 'inputValue': 'f', 'outputValue': 'g'}]"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sample data to emulate a branching tree structure for the ISA files, to be used in the recursive function\n",
    "\n",
    "processes = [\n",
    "    {\n",
    "        \"index\": 0,\n",
    "        \"inputValue\": \"z\",\n",
    "        \"outputValue\": \"a\"\n",
    "    },\n",
    "    {\n",
    "        \"index\": 1,\n",
    "        \"inputValue\": \"a\",\n",
    "        \"outputValue\": \"b\"\n",
    "    },\n",
    "    {\n",
    "        \"index\": 2,\n",
    "        \"inputValue\": \"b\",\n",
    "        \"outputValue\": \"c\"\n",
    "    },\n",
    "    {\n",
    "        \"index\": 3,\n",
    "        \"inputValue\": \"b\",\n",
    "        \"outputValue\": \"f\"\n",
    "    },\n",
    "    {\n",
    "        \"index\": 4,\n",
    "        \"inputValue\": \"c\",\n",
    "        \"outputValue\": \"e\"\n",
    "    },\n",
    "    {\n",
    "        \"index\": 5,\n",
    "        \"inputValue\": \"c\",\n",
    "        \"outputValue\": \"d\"\n",
    "    },\n",
    "    {\n",
    "        \"index\": 6,\n",
    "        \"inputValue\": \"f\",\n",
    "        \"outputValue\": \"g\"\n",
    "    },\n",
    "    {\n",
    "        \"index\": 7,\n",
    "        \"inputValue\": \"f\",\n",
    "        \"outputValue\": \"h\"\n",
    "    },\n",
    "    {\n",
    "        \"index\": 8,\n",
    "        \"inputValue\": \"h\",\n",
    "        \"outputValue\": \"i\"\n",
    "    },\n",
    "]\n",
    "random.shuffle(processes)\n",
    "processes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e740e9a4-02c3-4b28-a788-4d2ebf98fb76",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3 ordered [0, 1, 2, 3]\n",
      "CPU times: user 120 ms, sys: 58.1 ms, total: 178 ms\n",
      "Wall time: 191 ms\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[[{'index': 0,\n",
       "   'input': 'Input [Source Name]',\n",
       "   'inputValue': '001_uncult_8°',\n",
       "   'output': 'Output [Sample Name]',\n",
       "   'outputValue': '001-007_uncult_8°_son'},\n",
       "  {'index': 1,\n",
       "   'input': 'Input [Sample Name]',\n",
       "   'inputValue': '001-007_uncult_8°_son',\n",
       "   'output': 'Output [Sample Name]',\n",
       "   'outputValue': '001-007_uncult_8°_ext'},\n",
       "  {'index': 2,\n",
       "   'input': 'Input [Sample Name]',\n",
       "   'inputValue': '001-007_uncult_8°_ext',\n",
       "   'output': 'Output [Raw Data File]',\n",
       "   'outputValue': '20210913_1558_001.fastq'},\n",
       "  {'index': 3,\n",
       "   'input': 'Input [Raw Data File]',\n",
       "   'inputValue': '20210913_1558_001.fastq',\n",
       "   'output': 'Output [Derived Data File]',\n",
       "   'outputValue': '20210913_1558_001.mzml'}]]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "###################################################################################################\n",
    "### 2. ORGANISE df_processes AND MERGE\n",
    "###################################################################################################\n",
    "# 1. Organise source -> sample -> raw -> derived\n",
    "#    Going to use array of dicts with [source, sample, raw, derived]\n",
    "# 2. Merge on the order and columns\n",
    "# NOTE: this does not yet work for complex trees ... needs review and validation\n",
    "\n",
    "def get_process_list(df_processes: list[pd.DataFrame]):\n",
    "    processes = []\n",
    "    for index, df_in in enumerate(df_processes):\n",
    "        input = next(c for c in df_in.columns if c.startswith(\"Input [\"))\n",
    "        output = next(c for c in df_in.columns if c.startswith(\"Output [\"))\n",
    "        term = {\n",
    "            \"index\": index,\n",
    "            \"input\": input,\n",
    "            \"inputValue\": df_in.loc[1, input],\n",
    "            \"output\": output,\n",
    "            \"outputValue\": df_in.loc[1, output]\n",
    "        }\n",
    "        processes.append(term)\n",
    "    return processes\n",
    "\n",
    "def get_root_process(processes: list[dict]):\n",
    "    roots = []\n",
    "    for prospect in processes:\n",
    "        root = prospect\n",
    "        for process in processes:\n",
    "            if prospect[\"index\"] == process[\"index\"]:\n",
    "                continue\n",
    "            if prospect[\"inputValue\"] == process[\"outputValue\"]:\n",
    "                root = None\n",
    "                break\n",
    "        if root:\n",
    "            roots.append(root)\n",
    "    return roots\n",
    "\n",
    "\n",
    "def reduce_process_list(root: dict, processes: list[dict]):\n",
    "    return [p for p in processes if p[\"index\"] != root[\"index\"]]\n",
    "\n",
    "def order_processes(\n",
    "    *, \n",
    "    processes: list[dict] = [], \n",
    "    ordered: list[dict] = [], \n",
    "    ordered_processes: list[list[dict]] = [], \n",
    "    done: set[int] = set(),\n",
    "):\n",
    "    roots = get_root_process(processes=processes)\n",
    "    set_order = False\n",
    "    if not ordered:\n",
    "        set_order = True\n",
    "    for root in roots:\n",
    "        if set_order:\n",
    "            ordered = [root]\n",
    "        processes = reduce_process_list(root=root, processes=processes)\n",
    "        # if not processes:\n",
    "        #     print(root)\n",
    "        #     ordered_processes.append(ordered)\n",
    "        for process in processes:\n",
    "            if root[\"outputValue\"] == process[\"inputValue\"]:\n",
    "                ordered.append(process)\n",
    "                if not any([process[\"outputValue\"] == p[\"inputValue\"] for p in processes]) and not process[\"index\"] in done:\n",
    "                    ordered_processes.append(ordered)\n",
    "                    done.add(process[\"index\"])\n",
    "                    print(process[\"index\"], \"ordered\", [p[\"index\"] for p in ordered])\n",
    "                else:\n",
    "                    ordered_processes = order_processes(processes=processes, ordered=ordered, ordered_processes=ordered_processes, done=done)\n",
    "    return ordered_processes\n",
    "\n",
    "processes = get_process_list(df_processes=df_processes)\n",
    "ordered_processes = order_processes(processes=processes)\n",
    "ordered_processes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a599736f-ca73-49e5-9332-83a58d934c11",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5.53 s, sys: 245 ms, total: 5.78 s\n",
      "Wall time: 8.26 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "###################################################################################################\n",
    "### 2. Perform the merge\n",
    "###################################################################################################\n",
    "\n",
    "def get_merge_dataframe(df_processes: list[pd.DataFrame], ordered_processes: list[list[dict]]):\n",
    "    chunks = []\n",
    "    for processes in ordered_processes:\n",
    "        root_process = processes[0]\n",
    "        df_chunk = df_processes[root_process[\"index\"]]\n",
    "        for process in processes[1:]:\n",
    "            root_on = [c for c in df_chunk.columns if c.startswith(root_process[\"output\"])]\n",
    "            if len(root_on) > 1:\n",
    "                root_process[\"output\"] = root_on[-1]\n",
    "            try:\n",
    "                df_chunk = df_chunk.merge(\n",
    "                    df_processes[process[\"index\"]], \n",
    "                    left_on=root_process[\"output\"], \n",
    "                    right_on=process[\"input\"],\n",
    "                )\n",
    "            except:\n",
    "                return df_chunk\n",
    "                \n",
    "            root_process = process\n",
    "        return df_chunk\n",
    "        chunks.append(df_chunk)\n",
    "    return pd.concat(chunks)\n",
    "\n",
    "DIRECTORY = \"/mnt/c/Users/turuk/Documents/GitHub/biohackathon-assets/test/data/\"\n",
    "FILENAME = \"biohackathon.xlsx\"\n",
    "DATASOURCE_PATH = Path(DIRECTORY) / FILENAME\n",
    "\n",
    "df = get_merge_dataframe(df_processes=df_processes, ordered_processes=ordered_processes)\n",
    "df = df.drop_duplicates()\n",
    "df.to_excel(DATASOURCE_PATH, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9a978ec9-ebc2-4cad-b193-894c1cc744b9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5.23 s, sys: 115 ms, total: 5.34 s\n",
      "Wall time: 5.56 s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "###################################################################################################\n",
    "### 3. Import data for crosswalk\n",
    "###################################################################################################\n",
    "\n",
    "DIRECTORY = \"/mnt/c/Users/turuk/Documents/GitHub/biohackathon-assets/test/data/\"\n",
    "FILENAME = \"biohackathon.xlsx\"\n",
    "SCHEMA = \"geo-format.SCHEMA\"\n",
    "DATASOURCE_PATH = Path(DIRECTORY) / FILENAME\n",
    "MIMETYPE = \"XLSX\"\n",
    "\n",
    "datasource = qd.DataSourceDefinition()\n",
    "datasource.derive_model(source=DATASOURCE_PATH, mimetype=MIMETYPE)\n",
    "\n",
    "###################################################################################################\n",
    "### 4. Derive source schema\n",
    "###################################################################################################\n",
    "\n",
    "#schema_source = qd.SchemaDefinition(source=datasource.get.dict())\n",
    "schema_source = qd.SchemaDefinition()\n",
    "schema_source.derive_model(data=datasource.get)\n",
    "# Get script 'title'\n",
    "prospects = [field.name for field in schema_source.fields.get_all() if field.name.startswith(\"Output [Sample Name]\")]\n",
    "prospects.sort()\n",
    "title = prospects[-1]\n",
    "SCRIPTS = [\n",
    "    f\"SELECT > 'library name' < ['{title}']\",\n",
    "    f\"RENAME > 'title' < ['{title}']\",\n",
    "    \"RENAME > 'organism' < ['Characteristic [organism]']\",\n",
    "    \"RENAME > 'cell line' < ['Characteristic [cell line]']\",\n",
    "]\n",
    "ignore = []\n",
    "# Get 'cell type'\n",
    "if schema_source.fields.get(name='Characteristics [Sample type]'):\n",
    "    SCRIPTS.append(\"RENAME > 'cell type' < ['Characteristics [Sample type]']\")\n",
    "    ignore.append('Characteristics [Sample type]')\n",
    "elif schema_source.fields.get(name='Characteristic [Sample type]'):\n",
    "    SCRIPTS.append(\"RENAME > 'cell type' < ['Characteristic [Sample type]']\")\n",
    "    ignore.append('Characteristic [Sample type]')\n",
    "# Get 'library strategy'\n",
    "if schema_source.fields.get(name='Characteristic [library strategy]'):\n",
    "    schema_source.fields.set_categories(name='Characteristic [library strategy]', terms=[\"paired-end\", \"single-end\"])\n",
    "    SCRIPTS.append(\"CATEGORISE  > 'single or paired-end'::'single' < 'Characteristic [library strategy]'::['single-end']\")\n",
    "    SCRIPTS.append(\"CATEGORISE  > 'single or paired-end'::'paired-end' < 'Characteristic [library strategy]'::['paired-end']\")\n",
    "    ignore.append('Characteristic [library strategy]')\n",
    "# And the rest of the standards ['Component [instrument model]', 'Output [Raw Data File]', 'Output [Derived Data File]']\n",
    "if schema_source.fields.get(name='Component [instrument model]'):\n",
    "    SCRIPTS.append(\"RENAME > 'instrument model' < ['Component [instrument model]']\")\n",
    "    ignore.append('Component [instrument model]')\n",
    "if schema_source.fields.get(name='Output [Raw Data File]'):\n",
    "    SCRIPTS.append(\"RENAME > 'processed data file' < ['Output [Raw Data File]']\")\n",
    "    ignore.append('Output [Raw Data File]')\n",
    "if schema_source.fields.get(name='Output [Derived Data File]'):\n",
    "    SCRIPTS.append(\"RENAME > 'raw data file' < ['Output [Derived Data File]']\")\n",
    "    ignore.append('Output [Derived Data File]')\n",
    "schema_source.save(directory=Path(DIRECTORY), filename=\"biohackathon-source.SCHEMA\", created_by=\"Gavin Chait\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "14b1916d-07a7-4f6e-943d-2aa8ded0d978",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 18.6 ms, sys: 1.32 ms, total: 19.9 ms\n",
      "Wall time: 21.9 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "###################################################################################################\n",
    "### 5. Update destination schema and cover crosswalk\n",
    "###################################################################################################\n",
    "\n",
    "def get_new_destination_characteristics(fields: list[str], ignore: list[str] = []) -> list[dict]:\n",
    "    characteristics = []\n",
    "    for field in fields:\n",
    "        if field.startswith(\"Characteristic\") and field not in ignore:\n",
    "            destination = re.search('\\[(.+?)\\]', field)\n",
    "            if destination:\n",
    "                term = {\n",
    "                    \"source\": field,\n",
    "                    \"destination\": destination.group(1)\n",
    "                }\n",
    "                characteristics.append(term)\n",
    "    return characteristics\n",
    "\n",
    "fields = [field.name for field in schema_source.fields.get_all() if field.name.startswith(\"Characteristic\") and field not in ignore]\n",
    "new_terms = get_new_destination_characteristics(fields=fields, ignore=ignore)\n",
    "\n",
    "schema_destination = qd.SchemaDefinition(source=Path(DIRECTORY) / SCHEMA)\n",
    "for term in new_terms:\n",
    "    if not schema_destination.fields.get(name=term[\"destination\"]):\n",
    "        field = {\n",
    "            \"name\": term[\"destination\"],\n",
    "            \"type\": \"string\"\n",
    "        }\n",
    "        schema_destination.fields.add(term=field)\n",
    "        script = f\"RENAME > '{term['destination']}' < ['{term['source']}']\"\n",
    "        SCRIPTS.append(script)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "604e51a2-e77d-488a-ad3f-ec0f443fe118",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[\"SELECT > 'library name' < ['Output [Sample Name]_y']\",\n",
       " \"RENAME > 'title' < ['Output [Sample Name]_y']\",\n",
       " \"RENAME > 'organism' < ['Characteristic [organism]']\",\n",
       " \"RENAME > 'cell line' < ['Characteristic [cell line]']\",\n",
       " \"RENAME > 'cell type' < ['Characteristics [Sample type]']\",\n",
       " \"CATEGORISE  > 'single or paired-end'::'single' < 'Characteristic [library strategy]'::['single-end']\",\n",
       " \"CATEGORISE  > 'single or paired-end'::'paired-end' < 'Characteristic [library strategy]'::['paired-end']\",\n",
       " \"RENAME > 'instrument model' < ['Component [instrument model]']\",\n",
       " \"RENAME > 'processed data file' < ['Output [Raw Data File]']\",\n",
       " \"RENAME > 'raw data file' < ['Output [Derived Data File]']\",\n",
       " \"RENAME > 'Biological replicate' < ['Characteristics [Biological replicate]']\",\n",
       " \"RENAME > 'Isolate' < ['Characteristics [Isolate]']\",\n",
       " \"RENAME > 'Cultivar' < ['Characteristics [Cultivar]']\",\n",
       " \"RENAME > 'Ecotype' < ['Characteristics [Ecotype]']\",\n",
       " \"RENAME > 'Genotype' < ['Characteristics [Genotype]']\",\n",
       " \"RENAME > 'population' < ['Characteristics [population]']\",\n",
       " \"RENAME > 'Organism part' < ['Characteristics [Organism part]']\",\n",
       " \"RENAME > 'Cell line' < ['Characteristics [Cell line]']\",\n",
       " \"RENAME > 'Cell type' < ['Characteristics [Cell type]']\",\n",
       " \"RENAME > 'age' < ['Characteristics [age]']\",\n",
       " \"RENAME > 'Developmental Stage' < ['Characteristics [Developmental Stage]']\",\n",
       " \"RENAME > 'Plant disease' < ['Characteristics [Plant disease]']\",\n",
       " \"RENAME > 'Plant disease stage' < ['Characteristics [Plant disease stage]']\",\n",
       " \"RENAME > 'Phenotype' < ['Characteristics [Phenotype]']\",\n",
       " \"RENAME > 'whole plant size' < ['Characteristics [whole plant size]']\",\n",
       " \"RENAME > 'study type' < ['Characteristics [study type]']\",\n",
       " \"RENAME > 'plant growth medium exposure' < ['Characteristics [plant growth medium exposure]']\",\n",
       " \"RENAME > 'growth plot design' < ['Characteristics [growth plot design]']\",\n",
       " \"RENAME > 'Growth day length' < ['Characteristics [Growth day length]']\",\n",
       " \"RENAME > 'light intensity exposure' < ['Characteristics [light intensity exposure]']\",\n",
       " \"RENAME > 'Humidity Day' < ['Characteristics [Humidity Day]']\",\n",
       " \"RENAME > 'Humidity Night' < ['Characteristics [Humidity Night]']\",\n",
       " \"RENAME > 'Temperature Day' < ['Characteristics [Temperature Day]']\",\n",
       " \"RENAME > 'Temperature Night' < ['Characteristics [Temperature Night]']\",\n",
       " \"RENAME > 'watering exposure' < ['Characteristics [watering exposure]']\",\n",
       " \"RENAME > 'plant nutrient exposure' < ['Characteristics [plant nutrient exposure]']\",\n",
       " \"RENAME > 'abiotic plant exposure' < ['Characteristics [abiotic plant exposure]']\",\n",
       " \"RENAME > 'biotic plant exposure' < ['Characteristics [biotic plant exposure]']\",\n",
       " \"RENAME > 'Geographic Area' < ['Characteristics [Geographic Area]']\",\n",
       " \"RENAME > 'Sample Collection Date' < ['Characteristics [Sample Collection Date]']\",\n",
       " \"RENAME > 'Sample Collected By' < ['Characteristics [Sample Collected By]']\",\n",
       " \"RENAME > 'Time point' < ['Characteristics [Time point]']\"]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SCRIPTS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c3ce0383-38a2-441b-b1f1-093e5fbac022",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 9.15 s, sys: 946 ms, total: 10.1 s\n",
      "Wall time: 9.06 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "###################################################################################################\n",
    "### 6. Create crosswalk and perform transform\n",
    "###################################################################################################\n",
    "\n",
    "# Crosswalk\n",
    "crosswalk = qd.CrosswalkDefinition()\n",
    "crosswalk.set(schema_source=schema_source.get.dict(), schema_destination=schema_destination.get.dict())\n",
    "crosswalk.actions.add_multi(terms=SCRIPTS)\n",
    "# Transform\n",
    "transform = qd.TransformDefinition(crosswalk=crosswalk, data_source=datasource.get)\n",
    "transform.process()\n",
    "transform.save(directory=DIRECTORY, filename=\"biohackathon-out\", mimetype=\"XLSX\")\n",
    "# Deduplicate in file\n",
    "df = transform.data\n",
    "df = df.drop_duplicates()\n",
    "df.to_excel(Path(transform.model.dataDestination.path), index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c028672f-391d-4dec-8e57-7969832d1fed",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5.13 s, sys: 2.77 s, total: 7.9 s\n",
      "Wall time: 5.79 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "###################################################################################################\n",
    "### 7. Create output file\n",
    "###################################################################################################\n",
    "\n",
    "# Get the distinct fields as lists\n",
    "df_output = pd.DataFrame()\n",
    "max_lengths = {}\n",
    "rows = []\n",
    "for library, df_group in df.groupby('library name'):\n",
    "    row = []\n",
    "    for c in df_group.columns:\n",
    "        terms = df_group[c].unique()\n",
    "        if len(terms) == 1 and pd.isnull(terms[0]):\n",
    "            continue\n",
    "        row.append({\n",
    "            c: terms\n",
    "        })\n",
    "        if max_lengths.get(c, 0) <= len(terms):\n",
    "            max_lengths[c] = len(terms)\n",
    "    rows.append(row)\n",
    "# Process into a headerless dataframe\n",
    "chunks = []\n",
    "# Headers\n",
    "data = []\n",
    "processed = []\n",
    "raw = []\n",
    "for field, value in max_lengths.items():\n",
    "    dv = [field] * value\n",
    "    if field == \"processed data file\":\n",
    "        processed = dv\n",
    "    elif field == \"raw data file\":\n",
    "        raw = dv\n",
    "    else:\n",
    "        data.extend(dv)\n",
    "data.extend(processed)\n",
    "data.extend(raw)\n",
    "chunks.append(pd.DataFrame(data=[data]))\n",
    "# Rows\n",
    "for row in rows:\n",
    "    data = []\n",
    "    processed = []\n",
    "    raw = []\n",
    "    for term in row:\n",
    "        for field, values in term.items():\n",
    "            max = max_lengths.get(field, 1)\n",
    "            dv = list(values) + [\"\"] * (max - len(values))\n",
    "            if field == \"processed data file\":\n",
    "                processed = dv\n",
    "            elif field == \"raw data file\":\n",
    "                raw = dv\n",
    "            else:\n",
    "                data.extend(dv)\n",
    "    data.extend(processed)\n",
    "    data.extend(raw)\n",
    "    chunks.append(pd.DataFrame(data=[data]))\n",
    "dfo = pd.concat(chunks)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "da8c7019-e3a6-42b9-b7f3-29dfc81aca6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "other_terms = [\n",
    "    {\n",
    "        \"source\": \"Study Title\",\n",
    "        \"destination\": \"title\",\n",
    "        \"value\": \"\",\n",
    "    },\n",
    "    {\n",
    "        \"source\": \"Study Description\",\n",
    "        \"destination\": \"summary (abstract)\",\n",
    "        \"value\": \"\",\n",
    "    },\n",
    "    {\n",
    "        \"source\": \"Study Design Type\",\n",
    "        \"destination\": \"experimental design\",\n",
    "        \"value\": \"\",\n",
    "    },\n",
    "]\n",
    "\n",
    "contributors = []\n",
    "contributors_prefered = [\n",
    "    {\n",
    "        \"first\": \"Assay Person First Name\",\n",
    "        \"last\": \"Assay Person Last Name\",\n",
    "        \"mid\": \"Assay Person Mid Initials\",\n",
    "    },\n",
    "    {\n",
    "        \"first\": \"Study Person First Name\",\n",
    "        \"last\": \"Study Person Last Name\",\n",
    "        \"mid\": \"Study Person Mid Initials\",\n",
    "    }\n",
    "]\n",
    "    \n",
    "\n",
    "for df_o in df_other:\n",
    "    for other in other_terms:\n",
    "        # general\n",
    "        index = list(df_o[df_o[df_o.columns[0]] == other[\"source\"]].index)\n",
    "        if index:\n",
    "            for k, v in df_o.iloc[index[0]-1].items():\n",
    "                if k == other[\"source\"]:\n",
    "                    continue\n",
    "                other[\"value\"] = v\n",
    "    for contr in contributors_prefered:\n",
    "        # contributors\n",
    "        index = list(df_o[df_o[df_o.columns[0]] == contr[\"last\"]].index)\n",
    "        if index:\n",
    "            lasts = [t for t in df_o.iloc[index[0]-1].to_list() if t != contr[\"last\"]]\n",
    "            firsts = [t for t in df_o.iloc[index[0]].to_list() if t != contr[\"first\"]]\n",
    "            mids = [t for t in df_o.iloc[index[0]+1].to_list() if t != contr[\"mid\"]]\n",
    "            contributors.extend([\", \".join([c for c in [t, m, l] if c]) for t, m, l in zip(firsts, mids, lasts)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "f45f6a12-6272-4cd7-9fdd-7175cdfedc63",
   "metadata": {},
   "outputs": [],
   "source": [
    "chunks = []\n",
    "# STUDY\n",
    "chunks.append(pd.DataFrame(data=[[\"STUDY\"]]))\n",
    "for other in other_terms:\n",
    "    data = [other[\"destination\"], other[\"value\"]]\n",
    "    chunks.append(pd.DataFrame(data=[data]))\n",
    "contributors.sort()\n",
    "for contr in contributors:\n",
    "    data = [\"contributor\", contr]\n",
    "    chunks.append(pd.DataFrame(data=[data]))\n",
    "chunks.append(pd.DataFrame(data=[[\"\"]]))\n",
    "# SAMPLES\n",
    "chunks.append(pd.DataFrame(data=[[\"SAMPLES\"]]))\n",
    "chunks.append(dfo)\n",
    "chunks.append(pd.DataFrame(data=[[\"\"]]))\n",
    "# PROTOCOL\n",
    "chunks.append(pd.DataFrame(data=[[\"PROTOCOL\"]]))\n",
    "for other in special_other_terms:\n",
    "    data = [other[\"destination\"], other[\"value\"]]\n",
    "    chunks.append(pd.DataFrame(data=[data]))\n",
    "df_output = pd.concat(chunks).fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "68c2fe1a-b345-41ed-ac90-101a9640d275",
   "metadata": {},
   "outputs": [],
   "source": [
    "DIRECTORY = \"/mnt/c/Users/turuk/Documents/GitHub/biohackathon-assets/test/data/\"\n",
    "FILENAME = \"biohackathon-output.xlsx\"\n",
    "DATASOURCE_PATH = Path(DIRECTORY) / FILENAME\n",
    "df_output.to_excel(DATASOURCE_PATH, index=False, header=False)"
   ]
  }
 ],
 "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.9.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}