UKB_notebooks/ukb-rap-pheno-basic.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# UK Biobank Research Analysis Platform - Basic data extraction\n",
    "\n",
    "(_Disclaimer: This notebook is delivered 'As-Is'. Notwithstanding anything to the contrary, DNAnexus will have no warranty, support, liability or other obligations with respect to Materials provided hereunder. The [MIT License](https://github.com/dnanexus/OpenBio/blob/master/LICENSE.md) applies to this notebook._)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prologue"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This prologue has been created for consistency across notebooks, and to improve notebook portability across projects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import packages\n",
    "import pyspark\n",
    "import dxpy\n",
    "import dxdata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dxdata.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Spark initialization (Done only once; do not rerun this cell unless you select Kernel -> Restart kernel).\n",
    "sc = pyspark.SparkContext()\n",
    "spark = pyspark.sql.SparkSession(sc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Automatically discover dispensed database name and dataset id\n",
    "dispensed_database = dxpy.find_one_data_object(\n",
    "    classname='database', \n",
    "    name='app*', \n",
    "    folder='/', \n",
    "    name_mode='glob', \n",
    "    describe=True)\n",
    "dispensed_database_name = dispensed_database['describe']['name']\n",
    "\n",
    "dispensed_dataset = dxpy.find_one_data_object(\n",
    "    typename='Dataset', \n",
    "    name='app*.dataset', \n",
    "    folder='/', \n",
    "    name_mode='glob')\n",
    "dispensed_dataset_id = dispensed_dataset['id']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Access dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = dxdata.load_dataset(id=dispensed_dataset_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dataset 'entities' are virtual tables linked to one another.\n",
    "\n",
    "The main entity is 'participant' and corresponds to most pheno fields. Additional entities correspond to linked health care data.\n",
    "Entities starting with 'hesin' are for hospital records; entities starting with 'gp' are for GP records, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset.entities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessing the main 'participant' entity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "participant = dataset['participant']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Constructing field names, given UKB showcase field id, instance id, and array id\n",
    "\n",
    "For the main participant data, the Platform uses field names with the following convention:\n",
    "\n",
    "|Type of field|Syntax for field name|Example|\n",
    "|:------------|---------------------|-------|\n",
    "|Neither instanced nor arrayed|`p<FIELD-ID>`|`p31`|\n",
    "|Instanced but not arrayed|`p<FIELD-ID>_i<INSTANCE-ID>`|`p40005_i0`|\n",
    "|Arrayed but not instanced|`p<FIELD-ID>_a<ARRAY-ID>`|`p41262_a0`|\n",
    "|Instanced and arrayed|`p<FIELD-ID>_i<INSTANCE-ID>_a<ARRAY-ID>`|`p93_i0_a0`|\n",
    "\n",
    "Lastly, the participant id field itself (EID) is named `eid`\n",
    "\n",
    "If you know exactly the field names you want to work with, put them in a string array (we will see later how to use that):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "field_names = ['eid', 'p31', 'p21022', 'p40005_i0', 'p93_i0_a0']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Looking up fields, given UKB showcase field id\n",
    "\n",
    "If you know the field id but you are not sure if it is instanced or arrayed, and want to grab all instances/arrays (if any), use these:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Returns all field objects for a given UKB showcase field id\n",
    "\n",
    "def fields_for_id(field_id):\n",
    "    from distutils.version import LooseVersion\n",
    "    field_id = str(field_id)\n",
    "    fields = participant.find_fields(name_regex=r'^p{}(_i\\d+)?(_a\\d+)?$'.format(field_id))\n",
    "    return sorted(fields, key=lambda f: LooseVersion(f.name))\n",
    "\n",
    "# Returns all field names for a given UKB showcase field id\n",
    "\n",
    "def field_names_for_id(field_id):\n",
    "    return [f.name for f in fields_for_id(field_id)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Examples:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Participant sex\n",
    "field_names_for_id('31')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Age when attending assessment centre has multiple instances (visits) \n",
    "field_names_for_id('21003')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pulse rate has multiple instances and array indices (measured twice in each visit)\n",
    "field_names_for_id('102')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Obtain field name for all instances and arrays for each field_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "field_ids = ['21003', '102']\n",
    "# sum flattens list of lists\n",
    "sum([field_names_for_id(field_id) for field_id in field_ids], []) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Looking up fields by title keyword\n",
    "\n",
    "If you remember part of the field title, use these:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Returns all field objects for a given title keyword\n",
    "\n",
    "def fields_by_title_keyword(keyword):\n",
    "    from distutils.version import LooseVersion\n",
    "    fields = list(participant.find_fields(lambda f: keyword.lower() in f.title.lower()))\n",
    "    return sorted(fields, key=lambda f: LooseVersion(f.name))\n",
    "\n",
    "# Returns all field names for a given title keyword\n",
    "\n",
    "def field_names_by_title_keyword(keyword):\n",
    "    return [f.name for f in fields_by_title_keyword(keyword)]\n",
    "\n",
    "# Returns all field titles for a given title keyword\n",
    "\n",
    "def field_titles_by_title_keyword(keyword):\n",
    "    return [f.title for f in fields_by_title_keyword(keyword)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "field_titles_by_title_keyword('standing height')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "field_names_by_title_keyword('standing height')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### You can mix and match these methods to end up with a list of field names of interest:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "field_names = ['eid', 'p31', 'p21022'] \\\n",
    "    + field_names_for_id('41262') \\\n",
    "    + field_names_by_title_keyword('standing height')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "field_names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grabbing fields into a Spark DataFrame\n",
    "\n",
    "The `participant.retrieve_fields` function can be used to construct a Spark DataFrame of the given fields.\n",
    "\n",
    "By default, this retrieves data as encoded by UK Biobank. For example, field p31 (participant sex) will be returned as an integer column with values of 0 and 1. To receive decoded values, supply the `coding_values='replace'` argument.\n",
    "\n",
    "For more information, see [Tips for Retrieving Fields](https://dnanexus.gitbook.io/uk-biobank-rap/getting-started/working-with-ukb-data#tips-for-retrieving-fields) in the documentation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = participant.retrieve_fields(names=field_names, engine=dxdata.connect())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# See the first five entries as a Spark DataFrame:\n",
    "df.show(5, truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# See the first five entries as a Pandas DataFrame:\n",
    "df.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filtering spark dataframes\n",
    "\n",
    "Spark dataframes can be filtered using the syntax: `df.filter(expression)`\n",
    "\n",
    "The expression can be either :\n",
    "\n",
    "* a string expression, built using SQL field names (e.g. `p31`) and SQL operators (e.g. `=`, `NOT`, `OR`, `AND`)\n",
    "  * example: `'(p21022 >= 50) AND (p31 = 0)'`\n",
    "  \n",
    "\n",
    "* a Python expression, built using Python object fields (e.g. `df.p31`) and Python operators (e.g. `==`, `!`, `|`, `&`)\n",
    "  * example: `(df.p21022 >= 50) & (df.p31 == 0)`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example: Participants above 50 years old and female"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Using SQL syntax\n",
    "df.filter('(p21022 >= 50) AND (p31 = 0)').count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Using Python syntax\n",
    "df.filter((df.p21022 >= 50) & (df.p31 == 0)).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting more information about fields\n",
    "\n",
    "Field objects (such as those returned by `fields_for_id` and `fields_by_title_keyword` above, or via the `participant[<field_name>]` syntax) provide additional information such as codings and units."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  Working with codings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "participant['p31'].coding"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "participant['p31'].coding.codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def field_codes_by_keyword(field_name, keyword):\n",
    "    return dict([(k,v) for (k,v) in participant[field_name].coding.codes.items() if keyword.lower() in v.lower()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "field_codes_by_keyword('p31', 'female')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "field_codes_by_keyword('p41202', 'obesity')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Additional field information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get link to UKB documentation page\n",
    "participant['p31'].linkout"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get field units\n",
    "participant['p21022'].units"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving as CSV file\n",
    "df.toPandas().to_csv('results.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving as TSV file\n",
    "df.toPandas().to_csv('results.tsv', sep='\\t', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving as DTA file (Stata)\n",
    "df.toPandas().astype(str).replace('None|NaN|nan', '.', regex=True).to_stata('results.dta')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Writing results back to the project"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "dx upload results.tsv --dest / "
   ]
  }
 ],
 "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.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}