> For the complete documentation index, see [llms.txt](https://dnanexus.gitbook.io/uk-biobank-rap/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dnanexus.gitbook.io/uk-biobank-rap/working-on-the-research-analysis-platform/accessing-data/accessing-phenotypic-data.md). # Accessing phenotype data Learn how to export selected phenotypic fields into a TSV or CSV file, for easy browsing and analysis. If you've worked with UK Biobank data prior to using the Research Analysis Platform, you may be aware that UK Biobank distributes the main tabular dataset in a large encoded file with the extension `.enc_ukb`. To work with the dataset, you first convert this file to TSV or CSV format. On the Research Analysis Platform (RAP), this dataset is dispensed into your project as a database, in [Parquet format](https://archive.apache.org/dist/spark/docs/3.5.2/sql-data-sources-parquet.html). There are a few approaches to access and extract this tabular phenotype data on the RAP. ## Manually using Cohort Browser This approach requires you to manually add columns (data-fields) of interest to the table one-by-one. Therefore, this approach is suitable when you have a small number of fields. ### Select Fields of Interest in the Cohort Browser Start by navigating to your project and clicking on the name of the dispensed dataset. The Cohort Browser will launch. In the Cohort Browser, 1. Open the **Data Preview** tab 2. Click the "grid" icon at the right end of the **Participant ID** header row. Then click **Add Columns**. The **Add Columns to Table** dialog will open 3. Navigate to any field, either directly or via search. Once you've found the field you're looking for, click **Add** **as Column** 4. Continue locating the fields you're interested in, and adding them as columns. Note that as you add additional fields as columns, you do not have to wait for the **Data Preview** to finish loading. 5. Once you've finished, close the dialog by clicking the **X** to the right of the **Add Column to Table** title. In the **Data Preview** tab, you'll see the first few rows of the data. 6. In the upper right corner of the screen, click **Views**, then click **Save View**. Enter a name for the view, then save it. ### Create a TSV or CSV File Using Table Exporter Now convert your saved view into a TSV or CSV file, using the Table Exporter app. Navigate back to your project and click the **Start Analysis** button in the upper right corner of the screen. In the **Start New Analysis** dialog, select the [Table Exporter app](https://ukbiobank.dnanexus.com/app/table-exporter), then click **Run Selected**. Note that if this is the first time you've run Table Exporter, you'll be prompted to install it first. #### Select Input Within the Table Exporter app, open the **Analysis Inputs** tab on the right side of the screen. Then click the **Dataset or Cohort or Dashboard** tile: ![](https://1330403369-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MJSGqCj05ksPveoRMWe%2F-MeRlwu5ifGKRLj3Szqz%2F-MeRmMaR4Nw2NJE-yR7E%2Fimage.png?alt=media\&token=f2f9b857-9238-4910-9786-2eecc23b3314) A modal window will open. Select the view that you created and saved in the Cohort Browser. ### Configuring Output Options Within the **Options** section, configure your output options. In the **Output File Name** field, enter a filename prefix. In the **Output File Format** field, select "CSV" or "TSV." You may find it easier to work with a TSV file downstream, because the values in certain fields contain commas, complicating the parsing of a CSV file. In the **Coding Option** field, select "RAW" so that you can work with the original UK Biobank data, as you would get them from the Biobank. (For example, in the **Sex** field, you will see the coded value "0" rather than "Female.") In the **Header Style** field, select "UKB-FORMAT" to get headers that match the original UK Biobank format (e.g. 123-4.5). ![](https://1330403369-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MJSGqCj05ksPveoRMWe%2F-MeRlwu5ifGKRLj3Szqz%2F-MeRnjF-ZUx6T6qvmPgf%2Fimage.png?alt=media\&token=c0c6ce01-0117-4673-8114-9cc7e9be5d0f) ### Launching the Table Exporter App and Viewing the Converted File Click **Start Analysis**. Once the conversion finishes and the file is ready, you will be notified via email. To access the file, either return to your project, or click the link in the email. ## Programatically Alternatively, to extract a large amount of data-fields programatically using the following approach: ### Using Table Exporter Step 1. Get the file containing all available data-fields in your Dataset by running the following command on your local terminal (please make sure that dx-toolkit is [installed](https://documentation.dnanexus.com/downloads#installing-the-python-sdk-and-command-line-tools) on your machine) or on a [ttyd](https://ukbiobank.dnanexus.com/app/ttyd) terminal: ``` dx extract_dataset project-xxxx:record-yyyy -ddd --delimiter "," # where project-xxxx:record-yyyy is ID of your Dataset ``` The above command will generate 3 \*.csv files, and the *\*.dataset.data\_dictionary.csv* file contains full information about all available data-fields (the field names are in the second column called `name`).
Step 2. Collect all data-field names of interest, and write them into a *field\_name.txt* file. The file should have 1 column, and each line has only 1 data-field name. *The file above should contain only data-fields in the same Entity (e.g. "participant", "olink\_instance\_0", etc). Data–fields in different Entities should be written in separate files.* The value to use for `Entity` can be found in the first column of the *.dataset.data\_dictionary.csv* file called `entity` (Note: this is not the same as `entity_title` found in the *entity\_dictionary.csv* file)\ \ Step 3. Upload the *field\_name.txt* file to the RAP project: ``` dx upload field_name.txt --destination project-xxxx:/path/to/destination/ ``` Step 4. Use [Table Exporter](https://ukbiobank.dnanexus.com/app/table-exporter) app to extract the data. {% hint style="warning" %} Users usually experience problems when extracting the "eid" field without Entity specification, so do not forget to specify the corresponding Entity when extracting the "eid" field. {% endhint %}
For more information about how to use the Table exporter application, see the [DNAnexus documentation page](https://documentation.dnanexus.com/developer/apps/developing-spark-apps/table-exporter-application). ### Using JupyterLab Step 1. Get the file containing all available data-fields in your Dataset by running the following command: ```python import dxpy import subprocess # Automatically discover dispensed dataset ID and load the dataset dispensed_dataset_id = dxpy.find_one_data_object(typename='Dataset', name='app*.dataset', folder='/', name_mode='glob')['id'] # Get project ID project_id = dxpy.find_one_project()["id"] dataset = (':').join([project_id, dispensed_dataset_id]) cmd = ["dx", "extract_dataset", dataset, "-ddd", "--delimiter", ","] subprocess.check_call(cmd) ``` The above command will generate 3 \*.csv files, and the *\*.dataset.data\_dictionary.csv* file contains full information about all available data-fields (the field names are in the second column). Step 2. Collect all data-field names of interest, and write them into a variable *field\_names\_protein*. Here is an example collecting all proteins.
```python import os import glob path = os.getcwd() data_dict_csv = glob.glob(os.path.join(path, "*.data_dictionary.csv"))[0] data_dict_df = pd.read_csv(data_dict_csv) data_dict_df.head() field_names = list( data_dict_df.loc[data_dict_df["entity"] == "olink_instance_0", "name"].values ) field_names_str = [f"olink_instance_0.{f}" for f in field_names] field_names_protein = ",".join(field_names_str) ``` Step 3. Extract the data using `dx extract_dataset` command. ```python cmd = ['dx', 'extract_dataset', dataset, '--fields', field_names_protein, '--delimiter', ',', '--output', , ] subprocess.check_call(cmd) ``` Depending on the number of fields you're trying to grab, you may need to use a Spark environment - for example, by querying it from inside a Spark JupyterLab session. *We recommend using a Spark instance if you are attempting to extract more than 30 fields.* Your Step 3 will instead look like the following: ```python import pyspark config = pyspark.SparkConf().setAll([('spark.kryoserializer.buffer.max', '128'),('spark.sql.execution.arrow.pyspark.enabled','true')]) sc = pyspark.SparkContext(conf=config) spark = pyspark.sql.SparkSession(sc) cmd = [ "dx", "extract_dataset", dataset, "--fields", field_names_protein, "--delimiter", ",", "--output", "extracted_data.sql", "--sql", ] subprocess.check_call(cmd) with open('extracted_data.sql', 'r') as file: retrieve_sql = file.read() temp_df = spark.sql(retrieve_sql.strip(";")) pdf = temp_df.toPandas() pdf.to_csv('extracted_data.tsv', sep='\t', index=False) ``` ### Using Spark JupyterLab Apache Spark is a modern, scalable framework for parallel processing of big data. To analyze tabular data using Spark in JupyterLab, you first need to launch JupyterLab in a Spark cluster configuration. For information on how to use HAIL with Jupyterlab, see example notebooks [here](https://documentation.dnanexus.com/science/using-hail-to-analyze-genomic-data). When launching JupyterLab instance: * Click **Spark Cluster** under **Cluster Configuration**. * Select an instance type and number of nodes. This will affect how powerful the Spark cluster will be. The default settings allow for casual interrogation of the data. If you will be running complex queries or analyzing a large amount of data in memory, you may need to select a larger instance type. To increase parallelization efficiency and reduce processing time, you may need to select more nodes. To begin, import relevant Spark and DNAnexus libraries, and instantiate a Spark context and Spark session at the very top of your notebook, as shown below. ```python import dxpy import pyspark config = pyspark.SparkConf().setAll([('spark.kryoserializer.buffer.max', '128'),('spark.sql.execution.arrow.pyspark.enabled','true')]) sc = pyspark.SparkContext(conf=config) spark = pyspark.sql.SparkSession(sc) ``` {% hint style="info" %} Ensure that your Spark session is only initialized once per JupyterLab session. If you try to evaluate this cell multiple times (for example, by selecting "Run All Cells" to rerun a notebook after it's already run, or by opening and running multiple notebooks in the same JupyterLab session), you may encounter errors or your notebook may hang. If that happens, you may need to restart the specific notebook's kernel. As a best practice, shut down the kernel of any notebook you are not using, before running a second notebook in the same session. {% endhint %} ### Accessing the Database Directly Using SQL * To evaluate SQL, you can use the `spark.sql("...")` function, which returns a Spark DataFrame. * You can view the contents of a DataFrame (in full width) by calling `.show(truncate=False)` on it. The following example lists the tables in the database: ```python spark.sql("USE " + dispensed_database_name) spark.sql("SHOW TABLES").show(truncate=False) ``` ### Database Tables The database contains the following tables: | Table name | Description | | ----------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `participant_0001, ..., participant_9999` | These tables contain the main UK Biobank participant data. Each participant is represented as one row, and each data-field is represented as one or more columns. For scalability reasons, the data-fields are horizontally split across multiple tables, starting from table participant\_0001 (which contains the first few hundred columns for all participants), followed by participant\_0002 (which contains the next few hundred columns), etc. The exact number of tables depends on how many data-fields your application is approved for. | | `hesin` | Hospitalization records. This table is only included if your application is approved for data-field #41259. | | `hesin_critical` | Hospital critical care records. This table is only included if your application is approved for data-field #41290. | | `hesin_delivery` | Hospital delivery records. This table is only included if your application is approved for data-field #41264. | | `hesin_diag` | Hospital diagnosis records. This table is only included if your application is approved for data-field #41234. | | `hesin_maternity` | Hospital maternity records. This table is only included if your application is approved for data-field #41261. | | `hesin_oper` | Hospital operation records. This table is only included if your application is approved for data-field #41149. | | `hesin_psych` | Hospital psychiatric records. This table is only included if your application is approved for data-field #41289. | | `death` | Death records. This table is only included if your application is approved for data-field #40023. | | `death_cause` | Death cause records. This table is only included if your application is approved for data-field #40023. | | `gp_clinical` | GP clinical event records. This table is only included if your application is approved for data-field #42040. | | `gp_registrations` | GP registration records. This table is only included if your application is approved for data-field #42038. | | `gp_scripts` | GP prescription records. This table is only included if your application is approved for data-field #42039. | | `covid19_tpp_gp_clinical` | GP clinical event records (COVID TPP). This table is only included if your application is approved for data-field #40101. | | `covid19_tpp_gp_scripts` | GP prescription records (COVID TPP). This table is only included if your application is approved for data-field #40102. | | `covid19_emis_gp_clinical` | GP clinical event records (COVID EMIS). This table is only included if your application is approved for data-field #40103. | | `covid19_emis_gp_scripts` | GP prescription records (COVID EMIS). This table is only included if your application is approved for data-field #40104. | | `covid19_result_england` | COVID19 Test Result Record (England). This table is only included if your application is approved for data-field #40100. | | `covid19_result_scotland` | COVID19 Test Result Record (Scotland). This table is only included if your application is approved for data-field #40100. | | `covid19_result_wales` | COVID19 Test Result Record (Wales). This table is only included if your application is approved for data-field #40100. | | `covid19_vaccination` | COVID-19 vaccination data. This table is only included if your application is approved for data-field #32040. | | `olink_instance_0` | Olink NPX values for the instance 0 visit. This table is only included if your application is approved for data-field #30900. For scalability reasons, the protein columns are horizontally split across multiple tables, starting from table `olink_instance_0_0001` (which contains the first few hundred columns for all participants), followed by `olink_instance_0_0002` (which contains the next few hundred columns), etc. The splitting applies to the `olink_instance_2` and `olink_instance_3` tables mentioned below as well. | | `olink_instance_2` | Olink NPX values for the instance 2 visit. This table is only included if your application is approved for data-field #30900. | | `olink_instance_3` | Olink NPX values for the instance 3 visit. This table is only included if your application is approved for data-field #30900. | | `omop_condition_era` | OMOP Condition Era. This table is only included if your application is approved for data-field #20142. | | `omop_condition_occurrence` | OMOP Condition Occurrence. This table is only included if your application is approved for data-field #20142. | | `omop_death` | OMOP Death. This table is only included if your application is approved for data-field #20142. | | `omop_device_exposure` | OMOP Device Exposure. This table is only included if your application is approved for data-field #20142. | | `omop_dose_era` | OMOP Dose Era. This table is only included if your application is approved for data-field #20142. | | `omop_drug_era` | OMOP Drug Era. This table is only included if your application is approved for data-field #20142. | | `omop_drug_exposure` | OMOP Drug Exposure. This table is only included if your application is approved for data-field #20142. | | `omop_measurement` | OMOP Measurement. This table is only included if your application is approved for data-field #20142. | | `omop_note` | OMOP Note. This table is only included if your application is approved for data-field #20142. | | `omop_observation` | OMOP Observation. This table is only included if your application is approved for data-field #20142. | | `omop_observation_period` | OMOP Observation Period. This table is only included if your application is approved for data-field #20142. | | `omop_person` | OMOP Person. This table is only included if your application is approved for data-field #20142. | | `omop_procedure_occurrence` | OMOP Procedure Occurrence. This table is only included if your application is approved for data-field #20142. | | `omop_specimen` | OMOP Specimen. This table is only included if your application is approved for data-field #20142. | | `omop_visit_detail` | OMOP Visit Detail. This table is only included if your application is approved for data-field #20142. | | `omop_visit_occurrence` | OMOP Visit Occurrence. This table is only included if your application is approved for data-field #20142. | {% hint style="info" %} When listing tables in SQL, you may notice each table appearing twice, using a regular name and a versioned name, such as`"gp_clinical"`and`"gp_clinical_v4_0_9b7a7f3"`. This naming scheme is part of the system's architecture, supporting data refreshes and participant withdrawals. The "regularly named" table is actually a SQL VIEW pointing to the versioned table. When data is updated, the VIEW is switched to point to a new versioned table, and the old versioned table is deleted. Due to this behavior, please make sure to always use the regularly named tables - such as `"gp_clinical"` - because the versioned tables do not persist over time. {% endhint %} {% hint style="info" %} If your access application has been approved for Data-field 23146, 23148, and/or 23157 you will also see the following tables: Allele\_23146, allele\_23148, allele\_23157, annotation\_23146, annotation\_23148, annotation\_23157, assay\_eid\_map\_23146, assay\_eid\_map\_23148, assay\_eid\_map\_23157, genotype\_23146, genotype\_23148, genotype\_23157, pheno\_assay\_23146\_link, rsid\_lookup\_r81\_23146, pheno\_assay\_23146\_link, rsid\_lookup\_r81\_23148, pheno\_assay\_23157\_link, and rsid\_lookup\_r81\_23157. \ These tables contain limited information about alleles and genotypes, transcribed into SQL from the pVCF files of Data-field 23146 and/or 23148 and/or 23157 (along with added annotations). These tables are used by the Cohort Browser in the creation of the "GENOMICS" tab. They have not been optimized for direct SQL querying, and their schema and conventions are subject to change. For this reason, it is not recommended to access these tables on your own but to access the bulk files instead. {% endhint %} ### Database Columns For the main UK Biobank participant tables, the column-naming convention is generally as follows: `p_i_a` However, the following additional rules apply: * If a field is not instanced, the `_i` piece is skipped altogether. * If a field is not arrayed, the `_a` piece is skipped altogether. * If a field is arrayed due to being multi-select, the field is converted into a single column of type "embedded array", and the `_a` piece is skipped altogether. Examples**:** * Age at recruitment: `p21022` * Date of attending assessment centre: `p53_i0`, `p53_i1`, ... * Diagnoses - ICD10 (converted into embedded array): `p41270` For all other tables - such as hospital records, GP records, death records, and COVID-19 records - the column names are identical to what UK Biobank provides in its Showcase. For more information on the columns of these tables, consult [Resource #138483](https://biobank.ndph.ox.ac.uk/ukb/refer.cgi?id=138483) (hospital records), [Resource #591](https://biobank.ndph.ox.ac.uk/ukb/refer.cgi?id=591) (GP records), [Resource #115559](https://biobank.ndph.ox.ac.uk/ukb/refer.cgi?id=115559) (death records), [Resource #3151](https://biobank.ndph.ox.ac.uk/ukb/refer.cgi?id=3151) (COVID-19 GP records), or [Resource #1758](https://biobank.ndph.ox.ac.uk/ukb/refer.cgi?id=1758) (COVID-19 test results). ### Tips for Using SQL The main participant data is horizontally split into multiple tables, and you may find that SQL is less than suitable for querying those tables directly. To access main participant data, consider using the [dataset construct as discussed below](/uk-biobank-rap/getting-started/data-structure.md#accessing-the-dataset-using-python). For linked health care tables, it is easier to use SQL directly to extract data as a Spark DataFrame. The following example retrieves all GP records related to serum HDL cholesterol levels. ```python # Assumes you have already done: spark.sql("USE " + dispensed_database_name) df = spark.sql("SELECT * FROM gp_clinical WHERE read_2 = '44P5.00' OR read_3 = '44P5.'") ``` {% hint style="info" %} Spark DataFrames are lazy-evaluated. In the code block above, the command will return right away, assigning the variable `df` without executing the query. The query is only evaluated when needed, potentially with additional transformations. For example, typing `df.count()` later will evaluate an equivalent `SELECT COUNT(*)`... {% endhint %} For a list of all DataFrame functions, consult the [PySpark DataFrame Documentation](https://archive.apache.org/dist/spark/docs/3.5.2/api/python/reference/pyspark.sql/dataframe.html). ## Troubleshooting For general tips on troubleshooting, see [guide](https://dnanexus.gitbook.io/uk-biobank-rap/working-on-the-research-analysis-platform/troubleshooting-guide). | Issue | Example error message | What to do | | ------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Data not exported | `Warning: Out of memory` | Try to adjust the instance type to use one with more memory/storage and re-run your table exporter query. Alternatively, you could try using the dx extract\_dataset command within spark Jupyterlab. Example code [here](https://github.com/dnanexus/UKB_RAP/blob/main/proteomics/0_extract_phenotype_protein_data.ipynb). | | | `Invalid characters found in field names on line number(s) 1, 2, 3, 4, 5, 6, 7, 9, 10, 11, 12, 13,...` |

Check that you provided your inputs correctly following the documentation.


Note: If you don’t provide an entity value, then by default Table exporter will use the “participant” entity table

| | | `Failed to export data: An error occurred while calling o305.csv. : org.apache.spark.SparkException: Job aborted` | Make sure you specified the **entity** to use | | Export participant id (EID) | |

By default the participant identifier (EID) is no longer extracted.

In the Table exporter app you’ll need to add “eid” to the File containing Field Names parameter as well as specify entity parameter in the Advanced Options. Entity refers to the entity table from which we are extracting data from - e.g. “participant” or “olink\_instance\_0”


Alternatively, if using dx extract\_dataset command, then you’ll need to specify \.eid as one of the field names in your query. See example.

| | Are there spaces in the input argument "output" (Example: "physical activity")? | `table_exporter.py: error: unrecognized arguments: activity` | Remove or Replace spaces with underscore. Example: “physical\_activity” | | Is there a file containing a list of field names for the proteomics dataset? | | All protein fields can be found [here](https://github.com/dnanexus/UKB_RAP/blob/main/proteomics/field_names.txt). |