AMIA_Exercise5-Covariates_and_BMI.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Covariates and BMI\n",
    "\n",
    "This notebook combines the demographics and PCs we created earlier, then adds BMI as a final covariate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "library(tidyverse)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load existing data\n",
    "\n",
    "First we will load our PCs and Demographics files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "demo = read_csv(\"demo.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pcs = read_csv(\"pcs.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine and save the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cov_in = inner_join(demo, pcs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "write_csv(cov_in,\"covariates.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "system2(\"gsutil\",c(\"cp\",\"covariates.csv\",\"$WORKSPACE_BUCKET/\"), stdout=T,stderr=T)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create BMI Data\n",
    "\n",
    "Next, we will get Program Physical Measurements BMI data. We'll configure our BQ retrieval again, then select the Measurement Source Concept IDs AoU uses for PM calculated BMI."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "library(bigrquery)  # BigQuery R client.\n",
    "\n",
    "## BigQuery setup.\n",
    "BILLING_PROJECT_ID <- Sys.getenv('GOOGLE_PROJECT')\n",
    "# Get the BigQuery curated dataset for the current workspace context.\n",
    "CDR <- Sys.getenv('WORKSPACE_CDR')\n",
    "# Bucket\n",
    "WORKSPACE_BUCKET <- Sys.getenv('WORKSPACE_BUCKET')\n",
    "\n",
    "#Query\n",
    "bq <- function(query) {bq_table_download(bq_project_query(\n",
    "    BILLING_PROJECT_ID, page_size = 25000,\n",
    "    query=query, default_dataset = CDR ))\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get the All of Us calculated BMI from enrollment\n",
    "bmi_pm = bq(\"select person_id, min(value_as_number) BMI\n",
    "from \n",
    "measurement join measurement_ext using (measurement_id)\n",
    "where measurement_source_concept_id=903124 and value_as_number is not null\n",
    "group by person_id\n",
    "\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dim(bmi_pm)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bmi_pm %>% transmute(across(-person_id, \\(x) sample(x))) %>% head()\n",
    "#Uncomment for the original table view\n",
    "#head(bmi_pm)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge all of our covariates and save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cov_out = inner_join(bmi_pm, cov_in)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cov_out %>% transmute(across(-person_id, \\(x) sample(x))) %>% head()\n",
    "#Uncomment for the original table view\n",
    "#head(cov_out)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write the file and save in the workspace bucket"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "write_csv(cov_out, \"covariates.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "system2(\"gsutil\",c(\"cp\", \"covariates.csv\",\"$WORKSPACE_BUCKET/\"), stdout=T,stderr=T)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.2.2"
  },
  "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": 2
}