xlsx_to_yml.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "caring-indonesia",
   "metadata": {
    "kernel": "SoS"
   },
   "source": [
    "# Converting between yml and xlsx meta-data formats\n",
    "\n",
    "**Author: Gao Wang**\n",
    "\n",
    "This notebook contains a pipeline to convert between xlsx and yaml formats for phenotype meta data.\n",
    "\n",
    "Featuers will be implemented on need basis (as our thoughts on meta-data evolves) and will be documented in the section below.\n",
    "\n",
    "## Example usage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "adopted-isolation",
   "metadata": {
    "kernel": "Bash"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO: Running \u001b[32mdefault\u001b[0m: \n",
      "INFO: \u001b[32mdefault\u001b[0m is \u001b[32mcompleted\u001b[0m.\n",
      "INFO: \u001b[32mdefault\u001b[0m output:   \u001b[32mFGC_metadata_v4_template.yml\u001b[0m\n",
      "INFO: Workflow default (ID=w9997ae7f947c9a21) is executed successfully with 1 completed step.\n"
     ]
    }
   ],
   "source": [
    "sos run xlsx_to_yml.ipynb --xlsx-file FGC_metadata_v4_template.xlsx"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "minus-welsh",
   "metadata": {
    "kernel": "Bash"
   },
   "source": [
    "## Implementation details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "adult-jackson",
   "metadata": {
    "kernel": "SoS"
   },
   "outputs": [],
   "source": [
    "[global]\n",
    "parameter: xlsx_file = str\n",
    "    \n",
    "[default]\n",
    "depends: Py_Module(\"openpyxl\"), Py_Module(\"numpyencoder\")\n",
    "input: xlsx_file\n",
    "output: f\"{_input:n}.yml\"\n",
    "python: expand = \"${ }\"\n",
    "    import pandas as pd\n",
    "    df = pd.read_excel(${_input:r}, sheet_name=None, dtype=str, engine=\"openpyxl\")\n",
    "    values = dict([(sheet, dict([(x.split(\":\")[1].strip(), df[sheet][x][0]) for x in df[sheet].columns]))\n",
    "              for sheet in df])\n",
    "    # need to dump to plain JSON first in order to properly YAML format it\n",
    "    import json\n",
    "    from numpyencoder import NumpyEncoder\n",
    "    json.dump(values, open(${_output:r}, \"w\"), indent=4, sort_keys=False,\n",
    "              separators=(', ', ': '), ensure_ascii=False,\n",
    "              cls=NumpyEncoder)\n",
    "    import yaml\n",
    "    values = json.load(open(${_output:r}))\n",
    "    yaml.safe_dump(values, open(${_output:r}, \"w\"), \n",
    "               allow_unicode=True, \n",
    "               default_flow_style=False, \n",
    "               indent=4, \n",
    "               sort_keys=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "SoS",
   "language": "sos",
   "name": "sos"
  },
  "language_info": {
   "codemirror_mode": "sos",
   "file_extension": ".sos",
   "mimetype": "text/x-sos",
   "name": "sos",
   "nbconvert_exporter": "sos_notebook.converter.SoS_Exporter",
   "pygments_lexer": "sos"
  },
  "sos": {
   "kernels": [
    [
     "Bash",
     "bash",
     "Bash",
     "#E6EEFF",
     ""
    ],
    [
     "SoS",
     "sos",
     "",
     "",
     "sos"
    ]
   ],
   "version": "0.22.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}