xvhelper

Note: this package was recently named dxhelper. It has been renamed to xvhelper to reflect that it is not software that is released by DNAnexus product.

The goal of xvhelper is to provide an tidy interface to extracting and decoding raw values from exported Apollo datasets, whether they are exported from dx extract_dataset or through a SparkSQL query.

Given the data dictionary and the codings files, it will decode the categorical values from the raw data.

Installation

You can install the development version of xvhelper from GitHub with:

install.packages("remotes")
remotes::install_github("laderast/xvhelper")

Note about example data

All examples are shown with synthetic data. These are not actual patients from UKB RAP.

Read in CSV file or SQL generated data.frame

{xvhelper} works with both the CSV files that is generated from dx extract_dataset or the data.frame that is generated from running the Spark SQL query.

If you need to load the CSV file in, readr::read_csv is recommended:

cohort <- readr::read_csv("my_dataset.csv", show_col_types=FALSE)

Build coding dictionary

We’ll need two data.frames: a coding_dict and a data_dict. These are data.frames from the .coding.csv and data_dictionary.csv files that are generated with the -ddd (dump dataset dictionary) option of dx extract_dataset. We read them in using readr::read_csv.

coding_dict <- readr::read_csv("my_dataset.coding.csv", show_col_types=FALSE)
data_dict <- readr::read_csv("my_dataset.data_dictionary.csv" , show_col_types=FALSE)

Then we’ll build a coding dictionary by combining coding_dict and data_dict.

merged_code <- merge_coding_data_dict(coding_dict, data_dict)

head(merged_code)
#> # A tibble: 6 × 9
#>   title        ent_field entity name  coding_name code  meaning is_sparse_coding
#>   <chr>        <glue>    <chr>  <chr> <chr>       <chr> <chr>   <chr>           
#> 1 Coffee cons… particip… parti… p100… data_codin… 0     No      <NA>            
#> 2 Coffee cons… particip… parti… p100… data_codin… 1     Yes     <NA>            
#> 3 Coffee cons… particip… parti… p100… data_codin… 0     No      <NA>            
#> 4 Coffee cons… particip… parti… p100… data_codin… 1     Yes     <NA>            
#> 5 Sex of baby  particip… parti… p412… data_codin… 9     Not sp… <NA>            
#> 6 Sex of baby  particip… parti… p412… data_codin… 3     Indete… <NA>            
#> # ℹ 1 more variable: is_multi_select <chr>

Decoding Integer Categories of Apollo Datasets

Categorical data is returned by dx extract_dataset as the integer representation. To see the actual values, they must be decoded from the codings.csv that is generated from dx extract_dataset.

Data fields that have multiple categories must also be parsed and decoded. If the data is a sparse field, it is a combined categorical/numerical field.

Show Raw Cohort File

Here’s a cohort that was returned by dx extract_dataset. Note that there are multiple columns encoded as categories. We need to decode these values.

knitr::kable(head(cohort[,c(1:5,14)]))

Decode Columns

We can decode the integer values into the actual values using decode_categories and our merged_code object:

decoded_cohort <- cohort |>
  decode_df(merged_code)

knitr::kable(head(decoded_cohort[,c(1:5,14)]))

Decode Column Names

Optionally, you can also decode the column names in the table to be R-friendly column names with the actual field titles:

final_cohort <- decoded_cohort |>
  decode_column_names(merged_code)

knitr::kable(head(final_cohort[,c(1:5,14)]))

If you want the original field names with no modifications, you can set the argument r_clean_names to FALSE:

final_cohort2 <- decoded_cohort |>
  decode_column_names(merged_code, r_clean_names=FALSE)

knitr::kable(head(final_cohort2[,c(1:5,14)]))

Separating out single and multi column decodes

You can have a little more control if you use the two decode_ functions, decode_single() and decode_multi_purrr():

decoded_cohort <- mydf[1:10,] |>
  decode_single(merged_code) |>
  decode_multi_purrr(merged_code) |>
  decode_column_names(merged_code)

knitr::kable(decoded_cohort)

Multi-valued columns

Multivalued columns are returned as a pipe (|) delimited string (this is to avoid issues with commas within categorical values):

knitr::kable(decoded_cohort[1:10,"diagnoses_main_icd10"])

If we want to split these out, we can use tidyr::separate_longer_delim():

decoded_cohort |>
  dplyr::select(participant_eid, diagnoses_main_icd10) |>
  tidyr::separate_longer_delim(diagnoses_main_icd10, delim="|") |>
  knitr::kable()

New (7/24/23) Incremental Decoding of Very Large Data Frames

One thing to note is that decoding a large dataset (for UKB Pheno data, this can be over 500K rows) takes time. The new function decode_multi_large_df() will split the dataset into a number of data frames, the size of which is specified by df_size, which is then processed one data frame at a time, with a progress bar.

This is not necessarily faster, but it does give the user a sense of progress.

Note that the default value for df_size is 1000. We’re only changing it here because the example data is smaller.

decoded_cohort <- mydf[1:10,] |>
  decode_single(merged_code) |>
  decode_multi_large_df(merged_code, df_size = 2) |>
  decode_column_names(merged_code)

knitr::kable(decoded_cohort)

Returned tables from SparkSQL

If you use dbGetQuery() to fetch your table from Spark, multi-value columns will be returned as list-columns. decode_df will return a pipe-delimited string for these columns as well.

tibble::tibble(mydf)
#> # A tibble: 100 × 11
#>    participant.eid participant.p21022 participant.p1508_i0 participant.p1508_i1
#>    <chr>                        <dbl>                <dbl>                <dbl>
#>  1 sample_100_116                  43                    3                   NA
#>  2 sample_100_142                  60                    3                   NA
#>  3 sample_100_285                  53                    3                   NA
#>  4 sample_100_290                  62                    2                   NA
#>  5 sample_100_304                  67                    3                   NA
#>  6 sample_100_328                  61                    1                   NA
#>  7 sample_100_348                  66                    3                   NA
#>  8 sample_100_36                   56                    3                    3
#>  9 sample_100_362                  55                    2                   NA
#> 10 sample_100_384                  53                    1                   NA
#> # ℹ 90 more rows
#> # ℹ 7 more variables: participant.p1508_i2 <dbl>, participant.p41202 <list>,
#> #   participant.p31 <dbl>, participant.p41226 <list>,
#> #   participant.p3159_i0 <dbl>, participant.p3159_i1 <dbl>,
#> #   participant.p3159_i2 <dbl>

from_db <- mydf |> 
  decode_df(merged_code)

knitr::kable(head(from_db))

New (7/28/23) Extract data using dx-toolkit

xvhelper now contains a number of functions that help you extract and download pheno data using the dx-toolkit from your project. It does this by using reticulate to call the python dxpy package and using system calls to dx extract_dataset.

These functions are designed to work within RStudio (on UKB RAP) or JupyterLab (on DNAnexus/UKB RAP).

See the article for more info.

New (8/3/23) Run Table Exporter

There is now the ability to run Table Exporter if your extract_data() query fails. See the article

Caveats/Limitations

extract_data() is based on dx extract_dataset from the dx-toolkit. There are some limitations with queries, since it uses a shared resource called the Thrift server to extract data. You may get an error if you try to extract more than ~15 fields at once.

See the above article about Running Table Exporter.