Create_Onboarding_Demog_table.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# OnBoarding Demographics Data Prep"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-08T20:51:18.756789Z",
     "start_time": "2018-11-08T20:51:16.206741Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "UPGRADE AVAILABLE\n",
      "\n",
      "A more recent version of the Synapse Client (2.0.0) is available. Your version (1.9.4) can be upgraded by typing:\n",
      "    pip install --upgrade synapseclient\n",
      "\n",
      "Python Synapse Client version 2.0.0 release notes\n",
      "\n",
      "https://python-docs.synapse.org/build/html/news.html\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Welcome, Sean Deering!\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import datetime as dt\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "import synapseclient\n",
    "from synapseclient import Activity, Schema, Table, as_table_columns\n",
    "\n",
    "InteractiveShell.ast_node_interactivity = 'all'\n",
    "syn = synapseclient.Synapse()\n",
    "syn.login()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Raw Demographics Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-08T20:51:03.792459Z",
     "start_time": "2018-11-08T20:50:50.988008Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>participantId</th>\n",
       "      <th>patientWeightPounds</th>\n",
       "      <th>patientBiologicalSex</th>\n",
       "      <th>patientHeightInches</th>\n",
       "      <th>patientWakeUpTime</th>\n",
       "      <th>patientCurrentAge</th>\n",
       "      <th>patientGoSleepTime</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6c9018a1-6e62-477e-a5b5-36145bf4ea51</td>\n",
       "      <td>170.0</td>\n",
       "      <td>Male</td>\n",
       "      <td>71.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-02-27T22:04:59-05:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>e1d8427f-924b-4bd1-a08f-421c1805a405</td>\n",
       "      <td>177.0</td>\n",
       "      <td>Male</td>\n",
       "      <td>68.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-03-10T10:24:26-06:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3d6c0442-9150-4974-8257-3a94461c8790</td>\n",
       "      <td>190.0</td>\n",
       "      <td>Male</td>\n",
       "      <td>75.0</td>\n",
       "      <td>2016-07-21T08:00:00-07:00</td>\n",
       "      <td>27.0</td>\n",
       "      <td>2016-07-21T22:30:00-07:00</td>\n",
       "      <td>2016-07-21T14:44:06-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a12e2ca0-2cf4-498f-a51d-1644630511ec</td>\n",
       "      <td>177.0</td>\n",
       "      <td>Female</td>\n",
       "      <td>64.0</td>\n",
       "      <td>2016-07-21T08:00:00-04:00</td>\n",
       "      <td>19.0</td>\n",
       "      <td>2016-07-21T00:00:00-04:00</td>\n",
       "      <td>2016-07-21T20:29:50-04:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          participantId  patientWeightPounds  \\\n",
       "0  6c9018a1-6e62-477e-a5b5-36145bf4ea51                170.0   \n",
       "1  e1d8427f-924b-4bd1-a08f-421c1805a405                177.0   \n",
       "2  3d6c0442-9150-4974-8257-3a94461c8790                190.0   \n",
       "3  a12e2ca0-2cf4-498f-a51d-1644630511ec                177.0   \n",
       "\n",
       "  patientBiologicalSex  patientHeightInches          patientWakeUpTime  \\\n",
       "0                 Male                 71.0                        NaN   \n",
       "1                 Male                 68.0                        NaN   \n",
       "2                 Male                 75.0  2016-07-21T08:00:00-07:00   \n",
       "3               Female                 64.0  2016-07-21T08:00:00-04:00   \n",
       "\n",
       "   patientCurrentAge         patientGoSleepTime                  timestamp  \n",
       "0               40.0                        NaN  2016-02-27T22:04:59-05:00  \n",
       "1               39.0                        NaN  2016-03-10T10:24:26-06:00  \n",
       "2               27.0  2016-07-21T22:30:00-07:00  2016-07-21T14:44:06-07:00  \n",
       "3               19.0  2016-07-21T00:00:00-04:00  2016-07-21T20:29:50-04:00  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "demog_raw_id = 'syn7115683'\n",
    "demog = pd.read_csv(syn.get(demog_raw_id).path)\n",
    "demog.head(n=4)\n",
    "\n",
    "# drop columns (lots of missing data due to App bug)\n",
    "demog = demog.drop(columns=['patientWakeUpTime', 'patientGoSleepTime'])\n",
    "\n",
    "### Replace white space with NaN\n",
    "demog = demog.replace(r'^\\s*$', np.nan, regex=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Process Demographics Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-11-08T20:51:04.422718Z",
     "start_time": "2018-11-08T20:51:03.852456Z"
    },
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "demog = demog.rename(columns={'patientWeightPounds':'weight_pounds',\n",
    "                              'patientBiologicalSex':'gender',\n",
    "                              'patientHeightInches':'height_inches',\n",
    "                              'patientCurrentAge':'age_years'})\n",
    "\n",
    "demog.height_inches = demog.height_inches.round(2)\n",
    "demog.weight_pounds = demog.weight_pounds.round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filter based on Age "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "##################################################\n",
      " Uploading file to Synapse storage \n",
      "##################################################\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "File(versionLabel=u'29', createdOn=u'2020-04-09T22:44:11.245Z', modifiedBy=u'3334346', cacheDir='', files=['SleepHealth_underage_participants_tobeExcluded.tsv'], concreteType=u'org.sagebionetworks.repo.model.FileEntity', dataFileHandleId=u'52068772', _file_handle={u'contentType': u'text/tab-separated-values', u'createdBy': u'3334346', u'createdOn': u'2020-04-10T16:05:42.000Z', u'contentMd5': u'229035449a1cd9c67288f94f2e71b821', u'isPreview': False, u'fileName': u'SleepHealth_underage_participants_tobeExcluded.tsv', u'etag': u'1f268792-39b4-4e7a-91e8-b56db8559b53', u'bucketName': u'proddata.sagebase.org', u'key': u'3334346/6ef9f0b7-07e3-438f-a8a6-b458a99e02a8/SleepHealth_underage_participants_tobeExcluded.tsv', u'id': u'52068772', u'previewId': u'52068773', u'externalURL': None, u'storageLocationId': 1, u'concreteType': u'org.sagebionetworks.repo.model.file.S3FileHandle', u'contentSize': 22014}, path='SleepHealth_underage_participants_tobeExcluded.tsv', synapseStore=True, id=u'syn21905452', createdBy=u'2166046', modifiedOn=u'2020-07-20T17:26:09.070Z', name=u'SleepHealth_underage_participants_tobeExcluded.tsv', parentId=u'syn7066726', etag=u'6d9bacc1-1264-4c23-9d00-2c6f986c8807', versionNumber=29)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "underage_participants = demog[demog.age_years < 18]\n",
    "underage_participants.to_csv('SleepHealth_underage_participants_tobeExcluded.tsv',\n",
    "                            index=False, sep=\"\\t\")\n",
    "syn.store(synapseclient.File(\"SleepHealth_underage_participants_tobeExcluded.tsv\", parentId = 'syn7066726'),\n",
    "         executed = 'https://github.com/apratap/SleepHealth_Data_Release/blob/master/Create_Onboarding_Demog_table.ipynb',\n",
    "         used = \"syn7115683\")\n",
    "\n",
    "#remove participants under 18\n",
    "demog = demog[~demog.participantId.isin(underage_participants.participantId)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove test accounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_accounts = pd.read_excel(syn.get('syn21958537').path)\n",
    "\n",
    "demog = demog[~demog.participantId.isin(test_accounts.participantId)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Censor height_inches"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.010      0.0\n",
       "0.050     60.0\n",
       "0.500     69.0\n",
       "0.600     70.0\n",
       "0.800     72.0\n",
       "0.850     73.0\n",
       "0.900     73.0\n",
       "0.950     75.0\n",
       "0.970     75.0\n",
       "0.990     77.0\n",
       "0.999     83.0\n",
       "1.000    107.0\n",
       "Name: height_inches, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "count    13921.000000\n",
       "mean        65.764433\n",
       "std         14.392243\n",
       "min          0.000000\n",
       "25%         66.000000\n",
       "50%         69.000000\n",
       "75%         71.000000\n",
       "max        107.000000\n",
       "Name: height_inches, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "demog.height_inches.quantile([.01, .05, .5, .6, .8, .85, .90, .95, .97, 0.99, 0.999, 1])\n",
    "demog.height_inches.describe()\n",
    "\n",
    "## If the height is < 60 in or > 78 in, exclude data (based on Asthma Data Descriptor, also to protect people with unique features))\n",
    "demog.height_inches[ (demog.height_inches < 60) | (demog.height_inches > 78) ] = 'CENSORED'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Censor weight_pounds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.010    0.000000e+00\n",
       "0.050    0.000000e+00\n",
       "0.500    1.800000e+02\n",
       "0.600    1.940000e+02\n",
       "0.800    2.260000e+02\n",
       "0.850    2.400000e+02\n",
       "0.900    2.580000e+02\n",
       "0.950    2.850000e+02\n",
       "0.970    3.080000e+02\n",
       "0.990    3.540000e+02\n",
       "0.999    5.090800e+02\n",
       "1.000    1.877018e+12\n",
       "Name: weight_pounds, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "count    1.392100e+04\n",
       "mean     1.348338e+08\n",
       "std      1.590865e+10\n",
       "min      0.000000e+00\n",
       "25%      1.500000e+02\n",
       "50%      1.800000e+02\n",
       "75%      2.160000e+02\n",
       "max      1.877018e+12\n",
       "Name: weight_pounds, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "demog.weight_pounds.quantile([.01, .05,  .5, .6, .8, .85, .90, .95, .97, 0.99, 0.999, 1])\n",
    "demog.weight_pounds.describe()\n",
    "\n",
    "## If the weight is less than 80 or above 350, exclude (choices based on thresholds from Asthma Data Descriptor).\n",
    "demog.weight_pounds[ (demog.weight_pounds < 80) |  (demog.weight_pounds > 350) ] = 'CENSORED'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Internal & External Copies of the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8130, 6)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#download the data \n",
    "sharing_info = pd.read_excel(syn.get('syn21958546').path)\n",
    "healthCodes_with_broadsharing = sharing_info[sharing_info.sharing == 'all_qualified_researchers']\n",
    "EXTERNAL_DEMO_DATA = demog[demog.participantId.isin(healthCodes_with_broadsharing['participant id'])]\n",
    "EXTERNAL_DEMO_DATA.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of unique participants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7250"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(EXTERNAL_DEMO_DATA.participantId.unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Total number of observations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8130"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(EXTERNAL_DEMO_DATA)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### External - Upload to Synapse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<synapseclient.table.CsvFileTable at 0x112801e90>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SH_EXTERNAL_PROJECT = 'syn18492837'\n",
    "table_schema_external = Schema(name='Onboarding Demographics',\n",
    "                               columns=as_table_columns(EXTERNAL_DEMO_DATA),\n",
    "                               parent=SH_EXTERNAL_PROJECT)\n",
    "demo_synTable_external = syn.store(Table(table_schema_external,EXTERNAL_DEMO_DATA))\n",
    "demo_synTable_external"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Internal - Upload to Synapse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "SH_INTERNAL_PROJECT = 'syn7066726'\n",
    "table_schema_internal = Schema(name='Participant Demographics Internal',\n",
    "                               columns=as_table_columns(demog), \n",
    "                               parent=SH_INTERNAL_PROJECT)\n",
    "demo_synTable_internal = syn.store(Table(table_schema_internal,demog))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Set Provenance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'createdBy': u'3334346',\n",
       " u'createdOn': u'2020-07-20T17:27:18.595Z',\n",
       " u'description': u'Process and convert raw data to table format',\n",
       " u'etag': u'95b27feb-3464-4ca9-b904-780b244b604c',\n",
       " u'id': u'10282138',\n",
       " u'modifiedBy': u'3334346',\n",
       " u'modifiedOn': u'2020-07-20T17:27:18.595Z',\n",
       " u'name': u'Onboarding Demographics data curation',\n",
       " u'used': [{u'concreteType': u'org.sagebionetworks.repo.model.provenance.UsedEntity',\n",
       "   u'reference': {u'targetId': u'syn7115683', u'targetVersionNumber': 913},\n",
       "   u'wasExecuted': False},\n",
       "  {u'concreteType': u'org.sagebionetworks.repo.model.provenance.UsedURL',\n",
       "   u'name': u'https://github.com/apratap/SleepHealth_Data_Release/blob/master/Create_Onboarding_Demog_table.ipynb',\n",
       "   u'url': u'https://github.com/apratap/SleepHealth_Data_Release/blob/master/Create_Onboarding_Demog_table.ipynb',\n",
       "   u'wasExecuted': True}]}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "{u'createdBy': u'3334346',\n",
       " u'createdOn': u'2020-07-20T17:27:24.429Z',\n",
       " u'description': u'Process and convert raw data to table format',\n",
       " u'etag': u'c438b512-b766-4082-97de-6ae565b14936',\n",
       " u'id': u'10282139',\n",
       " u'modifiedBy': u'3334346',\n",
       " u'modifiedOn': u'2020-07-20T17:27:24.429Z',\n",
       " u'name': u'Onboarding Demographics data curation',\n",
       " u'used': [{u'concreteType': u'org.sagebionetworks.repo.model.provenance.UsedEntity',\n",
       "   u'reference': {u'targetId': u'syn7115683', u'targetVersionNumber': 913},\n",
       "   u'wasExecuted': False},\n",
       "  {u'concreteType': u'org.sagebionetworks.repo.model.provenance.UsedURL',\n",
       "   u'name': u'https://github.com/apratap/SleepHealth_Data_Release/blob/master/Create_Onboarding_Demog_table.ipynb',\n",
       "   u'url': u'https://github.com/apratap/SleepHealth_Data_Release/blob/master/Create_Onboarding_Demog_table.ipynb',\n",
       "   u'wasExecuted': True}]}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "activity=Activity(name= 'Onboarding Demographics data curation', \n",
    "                  description='Process and convert raw data to table format', \n",
    "                  used=demog_raw_id, \n",
    "                  executed='https://github.com/apratap/SleepHealth_Data_Release/blob/master/Create_Onboarding_Demog_table.ipynb')\n",
    "#Internal \n",
    "syn.setProvenance(demo_synTable_internal, activity)\n",
    "\n",
    "#External\n",
    "syn.setProvenance(demo_synTable_external, activity)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.17"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}