01-prepare-data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "870322c1-64a9-484a-a880-7ea42dd0769b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sweetviz as sv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fda4baed-9cae-480f-b776-3878670e1f06",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\n",
    "    'data/raw/IHME_PHMRC_VA_DATA_ADULT_Y2013M09D11_0.csv',\n",
    "    dtype={\n",
    "        'g1_06d': str,\n",
    "        'g2_03ad': str,\n",
    "        'g2_03dd': str,\n",
    "        'g2_03ed': str,\n",
    "        'g5_03d': str,\n",
    "        'a2_09_2b': str\n",
    "    }\n",
    ")\n",
    "\n",
    "# remove columns that begin with \"word_\"\n",
    "# these are BOW features from the open narratives\n",
    "df = df[[c for c in df.columns if not c.startswith('word_')]]\n",
    "\n",
    "# remove columns that begin with \"c_\"\n",
    "# these are child related questions\n",
    "df = df[[c for c in df.columns if not c.startswith('c_')]]\n",
    "\n",
    "# map \"don't know\" to a numeric value to keep the dtype as integer\n",
    "def convert_dont_know_to_int(x):\n",
    "    if isinstance(x, str):\n",
    "        if (x.strip() == \"Don't Know\"):\n",
    "            return -1\n",
    "        else:\n",
    "            return pd.to_numeric(x)\n",
    "    else:\n",
    "        return x\n",
    "\n",
    "integer_columns = [\n",
    "    'g1_01d',\n",
    "    'g1_01y',\n",
    "    'g1_06d',\n",
    "    'g1_06y',\n",
    "    'g1_07a',\n",
    "    'g1_07b',\n",
    "    'g1_07c',\n",
    "    'g1_10',\n",
    "    'g2_03ad', # Date of first interview attempt [day]\n",
    "    'g2_03ay', # Date of first interview attempt [year]\n",
    "    'g2_03bd', # Date and time arranged for second interview attempt [day]\n",
    "    'g2_03by', # Date and time arranged for second interview attempt [year]\n",
    "    'g2_03cd', # Date and time arranged for third interview attempt [day]\n",
    "    'g2_03cy', # Date and time arranged for third interview attempt [year]\n",
    "    'g2_03dd', # Date of interview [day]\n",
    "    'g2_03dy', # Date of interview [year]\n",
    "    'g2_03ed',\n",
    "    'g2_03ey',\n",
    "    'g2_03fd',\n",
    "    'g2_03fy',\n",
    "    'g5_01d', # When was the deceased born? [day]\n",
    "    'g5_01y', # When was the deceased born? [year]\n",
    "    'g5_03d', # When did he/she die? [day]\n",
    "    'g5_03y', # When did he/she die? [year]\n",
    "    'g5_04a', # What was the last known age of the deceased? [years]\n",
    "    'g5_04c', # What was the last known age of the deceased? [days]\n",
    "]\n",
    "\n",
    "for c in integer_columns:\n",
    "    df[c] = df[c].map(convert_dont_know_to_int).astype('Int64')\n",
    "\n",
    "print('Consent:')\n",
    "print(df['g3_01'].value_counts())\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "213c7010-eeed-4ae9-b165-795764fe7252",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "## Load in the free-text data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f684e06-044c-4bb8-a7ab-068df6f62ff6",
   "metadata": {},
   "outputs": [],
   "source": [
    "text_codebook = pd.read_excel('data/raw/PHMRC_VAI_redacted_free_text.xlsx', sheet_name=0)\n",
    "text = pd.read_excel('data/raw/PHMRC_VAI_redacted_free_text.xlsx', sheet_name=1)\n",
    "\n",
    "# subselect to the adult module\n",
    "text = text.loc[text['module'] == 'Adult']\n",
    "\n",
    "# newid is only unique when scoped by adult/child/neonate\n",
    "newid_multiple_row = text['newid'].value_counts()\n",
    "newid_multiple_row = newid_multiple_row.index[newid_multiple_row>1]\n",
    "assert len(newid_multiple_row) == 0, 'found multiple row for newid, check only adults included'\n",
    "\n",
    "print(text.shape)\n",
    "text.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f02742f-c019-41df-9e42-f4a665c55882",
   "metadata": {},
   "source": [
    "## Apply exclusions / data cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48a14e97-609c-4eb4-9cff-830202cd6dc0",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f'{df.shape[0]} rows in primary dataframe.')\n",
    "dff = df.merge(text, how='left', on=['newid', 'module'], suffixes=('', '_text'))\n",
    "\n",
    "print(f'{dff.shape[0]} rows after merge.')\n",
    "dff['age_years'] = dff['age_years'].astype('Int64')\n",
    "\n",
    "# compare columns\n",
    "idx = dff['open_response'].isnull()\n",
    "print(f'  {idx.sum()} rows missing text data.')\n",
    "dff = dff.loc[~idx]\n",
    "\n",
    "idx = (dff['g5_06a'].isnull()) | (dff['g5_06a'] == 'Unknown')\n",
    "print(f'  {idx.sum()} rows missing education data.')\n",
    "dff = dff.loc[~idx]\n",
    "\n",
    "for c in ['site', 'gs_text34']:\n",
    "    idx = (dff[c] == dff[f'{c}_text']) & (dff[f'{c}_text'].notnull())\n",
    "    print(f'{idx.sum()} / {dff[f\"{c}_text\"].notnull().sum()} rows match for {c}.')\n",
    "\n",
    "# subselect data\n",
    "columns = [\n",
    "    'site', # Site\n",
    "    'newid', # Study ID\n",
    "    # free-text columns\n",
    "    'sex',\n",
    "    'age_years',\n",
    "    'age_months',\n",
    "    'age_days',\n",
    "    'gs_text34',\n",
    "    'open_response',\n",
    "    \n",
    "    # the diagnosis vars listed in the codebook look inconsistent with the dataframe\n",
    "    # ... so we are ignoring them, but the df cols are listed below\n",
    "    # 'gs_code34', 'gs_text34', 'va34'\n",
    "    # 'gs_code46', 'gs_text46', 'va46', \n",
    "    # 'gs_code55', 'gs_text55', 'va55',\n",
    "    \n",
    "    # for reference, we expected the following based on the codebook:\n",
    "    # 'gs_diagnosis', # Gold Standard Diagnosis Code\n",
    "    # 'gs_comorbid1', # Gold Standard Comorbid Conditions 1\n",
    "    # 'gs_comorbid2', # Gold Standard Comorbid Conditions 2\n",
    "    # 'gs_level', # Gold Standard Diagnosis Level\n",
    "    \n",
    "    # questionnaire\n",
    "    'g1_01d', # Date of birth [day]\n",
    "    'g1_01m', # Date of birth [month]\n",
    "    'g1_01y', # Date of birth [year]\n",
    "    'g1_05', # Sex of deceased\n",
    "    'g1_06d', # Date of death [day]\n",
    "    'g1_06m', # Date of death [month]\n",
    "    'g1_06y', # Date of death [year]\n",
    "    'g1_07a', # Last known age of the deceased [years]\n",
    "    'g1_07b', # Last known age of the deceased [months]\n",
    "    'g1_07c', # Last known age of the deceased [days]\n",
    "    'g1_08', # Marital status of deceased\n",
    "    'g1_09', # Last known level of education completed\n",
    "    'g1_10', # Number of years of education completed\n",
    "    'g2_01', # Language of interview\n",
    "    'g2_02', # Interviewer ID number\n",
    "    'g2_03ad', # Date of first interview attempt [day]\n",
    "    'g2_03am', # Date of first interview attempt [month]\n",
    "    'g2_03ay', # Date of first interview attempt [year]\n",
    "    'g2_03bd', # Date and time arranged for second interview attempt [day]\n",
    "    'g2_03bm', # Date and time arranged for second interview attempt [month]\n",
    "    'g2_03by', # Date and time arranged for second interview attempt [year]\n",
    "    'g2_03cd', # Date and time arranged for third interview attempt [day]\n",
    "    'g2_03cm', # Date and time arranged for third interview attempt [month]\n",
    "    'g2_03cy', # Date and time arranged for third interview attempt [year]\n",
    "    'g2_03dd', # Date of interview [day]\n",
    "    'g2_03dm', # Date of interview [month]\n",
    "    'g2_03dy', # Date of interview [year]\n",
    "    'g3_01', # Did respondent give consent?\n",
    "    'g4_02', # What is the sex of the respondent?\n",
    "    'g4_03a', # What is your respondent's relationship to the deceased?\n",
    "    'g4_03b', # Other relationship specified\n",
    "    'g4_04', # How old are you?\n",
    "    'g4_05', # # \"What was the last level of school you attended?\" -> respondent education level\n",
    "    'g5_01d', # When was the deceased born? [day]\n",
    "    'g5_01m', # When was the deceased born? [month]\n",
    "    'g5_01y', # When was the deceased born? [year]\n",
    "    'g5_02', # What was the sex of the deceased?\n",
    "    'g5_03d', # When did he/she die? [day]\n",
    "    'g5_03m', # When did he/she die? [month]\n",
    "    'g5_03y', # When did he/she die? [year]\n",
    "    'g5_04a', # What was the last known age of the deceased? [years]\n",
    "    'g5_04b', # What was the last known age of the deceased? [months]\n",
    "    'g5_04c', # What was the last known age of the deceased? [days]\n",
    "    \n",
    "    # outcome vars\n",
    "    'g5_06a', # What was the last known level of education of deceased?\n",
    "    'g5_06b', # Number of years of education completed\n",
    "]\n",
    "\n",
    "dff = dff[columns]\n",
    "\n",
    "# apply mapping for sex from codebook\n",
    "# 1 \"Male\" 2 \"Female\" 8 \"Refused to Answer\" 9 \"Don't Know\"\n",
    "dff['sex'] = dff['sex'].map({1: 'Male', 2: 'Female', 8: 'Refused to Answer', 9: \"Don't Know\"})\n",
    "\n",
    "# replace ages of 999 with -1\n",
    "dff.loc[dff['age_years'] == 999, 'age_years'] = -1\n",
    "\n",
    "# 19 rows were discrepant by sex after merging, so were deleted\n",
    "def print_discrepency(dff, var1, var2, mapping=None):\n",
    "    \"\"\"transform allows mapping vars in var1\"\"\"\n",
    "    v1 = dff[var1].copy()\n",
    "    v2 = dff[var2].copy()\n",
    "    if mapping is not None:\n",
    "        v1 = v1.map(mapping)\n",
    "    \n",
    "    print(f'=== {var1} vs {var2} ===')\n",
    "    idx1 = v1.notnull()\n",
    "    idx2 = v2.notnull()\n",
    "    print(f'  {var1}: {idx1.sum()} rows, {dff.loc[idx1, var1].nunique()} unique values.')\n",
    "    print(f'  {var2}: {idx2.sum()} rows, {dff.loc[idx2, var2].nunique()} unique values.')\n",
    "    idx = idx1 & idx2\n",
    "    print(f'  Overlap: {idx.sum()} rows, with {(v1[idx] != v2[idx]).sum()} mismatches.')\n",
    "\n",
    "print_discrepency(dff, 'sex', 'g5_02')\n",
    "print_discrepency(dff, 'age_years', 'g5_04a')\n",
    "\n",
    "# 1408 rows were discrepant by age but were clearly correlated when analysed by scatter plot, so we chose to keep age from the gold standard dataset.\n",
    "# Highest educational level was discrepant for 1977 rows between what was reported by the interviewee and what was formally recorded in the decedent’s record;\n",
    "# in these cases we selected the highest educational 103 level between the two.\n",
    "dff.set_index('newid', inplace=True)\n",
    "print(dff.shape)\n",
    "dff.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19776ba6-4baa-4437-83a7-3bb12232de07",
   "metadata": {},
   "source": [
    "## Output to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97b8d43d-26ae-43b3-b60c-4fdff9d85135",
   "metadata": {},
   "outputs": [],
   "source": [
    "dff.to_csv('data/processed/merged_dataset.csv')"
   ]
  }
 ],
 "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}