{ "cells": [ { "cell_type": "markdown", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "# COVID-19 - unvaccinated high risk patients\n", "\n", "Analysis of FHIR source data using a [Pathling FHIR server](https://pathling.csiro.au/docs/server).\n", "\n", "This query counts patients, grouped by whether they have received a COVID-19 vaccination and whether they are high-risk based upon a number of factors (CKD, heart disease, BMI)." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "import json\n", "from http.client import HTTPConnection\n", "from time import time\n", "from urllib.parse import urlencode\n", "\n", "import pandas as pd\n", "\n", "start = time()\n", "pathling_conn = HTTPConnection('localhost', 8080)" ] }, { "cell_type": "markdown", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "## Load the data\n", "\n", "First we load the NDJSON files into the Pathling server.\n", "\n", "Provide each NDJSON file as a parameter to the [import operation](https://pathling.csiro.au/docs/server/operations/import), which will read each file and persist it as a Parquet table in the configured warehouse location." ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "text/plain": [ "{'resourceType': 'OperationOutcome',\n", " 'issue': [{'severity': 'information',\n", " 'code': 'informational',\n", " 'diagnostics': 'Data import completed successfully'}]}" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import_start = time()\n", "resources = ['Patient', 'Immunization', 'Condition', 'Observation']\n", "parameters = {\n", " 'resourceType': 'Parameters',\n", " 'parameter': [\n", " {\n", " 'name': 'source',\n", " 'part': [\n", " {'name': 'resourceType', 'valueCode': resource},\n", " {'name': 'url',\n", " 'valueUrl': f'file:///Users/gri306/Library/CloudStorage/OneDrive-CSIRO/Data/synthea/paper-md/fhir/{resource}.ndjson'}\n", " ]\n", " } for resource in resources\n", " ]\n", "}\n", "\n", "pathling_conn.request('POST', '/fhir/$import', json.dumps(parameters),\n", " headers={'Content-Type': 'application/fhir+json'})\n", "response = pathling_conn.getresponse()\n", "json_string = response.read()\n", "\n", "parsed = json.loads(json_string)\n", "parsed" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Import completed in: 32.009 s\n" ] } ], "source": [ "print(f\"Import completed in: {time() - import_start:.3f} s\")" ] }, { "cell_type": "markdown", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "## Run the aggregate query\n", "\n", "Next we compose an query to the [aggregate operation](https://pathling.csiro.au/docs/server/operations/aggregate) to count the number of patients by vaccination status and high-risk status.\n", "\n", "We use a FHIR value set containing CVX codes to identify COVID-19 vaccinations.\n", "\n", "Subsumption queries are used for chronic kidney disease and diabetes, while an ECL expression is used to identify heart disease." ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "aggregate_start = time()\n", "params = [\n", " # AGGREGATIONS\n", " # Number of patients\n", " ('aggregation', 'count()'),\n", " # GROUPINGS\n", " # Vaccinated against COVID-19\n", " ('grouping',\n", " \"reverseResolve(Immunization.patient).vaccineCode.memberOf('https://aehrc.csiro.au/fhir/ValueSet/covid-19-vaccines') contains true\"),\n", " # High risk\n", " ('grouping',\n", " # Chronic kidney disease\n", " \"reverseResolve(Condition.subject).code.subsumedBy(http://snomed.info/sct|709044004) contains true \"\n", " \"or \"\n", " # Heart disease - << 49601007|Cardiovascular disease| : << 363698007|Finding site| = << 80891009|Structure of heart|\n", " \"reverseResolve(Condition.subject).code.memberOf('http://snomed.info/sct?fhir_vs=ecl/%3C%3C%2049601007%20%3A%20%3C%3C%20363698007%20%3D%20%3C%3C%2080891009%20') contains true \"\n", " \"or \"\n", " # BMI > 30\n", " \"reverseResolve(Observation.subject).where(code.coding contains http://loinc.org|39156-5||'Body Mass Index').valueQuantity\"\n", " \".where(system = 'http://unitsofmeasure.org').where(code = 'kg/m2').where(value > 30).empty().not()\"),\n", " # FILTERS\n", " # Age 18-60\n", " ('filter', 'birthDate < @2004-07-30 and birthDate > @1962-07-30')\n", "]\n", "url = f'/fhir/Patient/$aggregate?{urlencode(params)}'\n", "\n", "pathling_conn.request('GET', url)\n", "response = pathling_conn.getresponse()\n", "json_string = response.read()" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "text/plain": [ "{'resourceType': 'Parameters',\n", " 'parameter': [{'name': 'grouping',\n", " 'part': [{'name': 'label', 'valueBoolean': True},\n", " {'name': 'label', 'valueBoolean': False},\n", " {'name': 'result', 'valueUnsignedInt': 266},\n", " {'name': 'drillDown',\n", " 'valueString': \"(reverseResolve(Immunization.patient).vaccineCode.memberOf('https://aehrc.csiro.au/fhir/ValueSet/covid-19-vaccines') contains true) and ((reverseResolve(Condition.subject).code.subsumedBy(http://snomed.info/sct|709044004) contains true or reverseResolve(Condition.subject).code.memberOf('http://snomed.info/sct?fhir_vs=ecl/%3C%3C%2049601007%20%3A%20%3C%3C%20363698007%20%3D%20%3C%3C%2080891009%20') contains true or reverseResolve(Observation.subject).where($this.code.coding contains http://loinc.org|39156-5||'Body Mass Index').valueQuantity.where($this.system = 'http://unitsofmeasure.org').where($this.code = 'kg/m2').where($this.value > 30).empty().not()) = false) and (birthDate < @2004-07-30 and birthDate > @1962-07-30)\"}]},\n", " {'name': 'grouping',\n", " 'part': [{'name': 'label', 'valueBoolean': True},\n", " {'name': 'label', 'valueBoolean': True},\n", " {'name': 'result', 'valueUnsignedInt': 215},\n", " {'name': 'drillDown',\n", " 'valueString': \"(reverseResolve(Immunization.patient).vaccineCode.memberOf('https://aehrc.csiro.au/fhir/ValueSet/covid-19-vaccines') contains true) and (reverseResolve(Condition.subject).code.subsumedBy(http://snomed.info/sct|709044004) contains true or reverseResolve(Condition.subject).code.memberOf('http://snomed.info/sct?fhir_vs=ecl/%3C%3C%2049601007%20%3A%20%3C%3C%20363698007%20%3D%20%3C%3C%2080891009%20') contains true or reverseResolve(Observation.subject).where($this.code.coding contains http://loinc.org|39156-5||'Body Mass Index').valueQuantity.where($this.system = 'http://unitsofmeasure.org').where($this.code = 'kg/m2').where($this.value > 30).empty().not()) and (birthDate < @2004-07-30 and birthDate > @1962-07-30)\"}]},\n", " {'name': 'grouping',\n", " 'part': [{'name': 'label', 'valueBoolean': False},\n", " {'name': 'label', 'valueBoolean': False},\n", " {'name': 'result', 'valueUnsignedInt': 81},\n", " {'name': 'drillDown',\n", " 'valueString': \"((reverseResolve(Immunization.patient).vaccineCode.memberOf('https://aehrc.csiro.au/fhir/ValueSet/covid-19-vaccines') contains true) = false) and ((reverseResolve(Condition.subject).code.subsumedBy(http://snomed.info/sct|709044004) contains true or reverseResolve(Condition.subject).code.memberOf('http://snomed.info/sct?fhir_vs=ecl/%3C%3C%2049601007%20%3A%20%3C%3C%20363698007%20%3D%20%3C%3C%2080891009%20') contains true or reverseResolve(Observation.subject).where($this.code.coding contains http://loinc.org|39156-5||'Body Mass Index').valueQuantity.where($this.system = 'http://unitsofmeasure.org').where($this.code = 'kg/m2').where($this.value > 30).empty().not()) = false) and (birthDate < @2004-07-30 and birthDate > @1962-07-30)\"}]},\n", " {'name': 'grouping',\n", " 'part': [{'name': 'label', 'valueBoolean': False},\n", " {'name': 'label', 'valueBoolean': True},\n", " {'name': 'result', 'valueUnsignedInt': 85},\n", " {'name': 'drillDown',\n", " 'valueString': \"((reverseResolve(Immunization.patient).vaccineCode.memberOf('https://aehrc.csiro.au/fhir/ValueSet/covid-19-vaccines') contains true) = false) and (reverseResolve(Condition.subject).code.subsumedBy(http://snomed.info/sct|709044004) contains true or reverseResolve(Condition.subject).code.memberOf('http://snomed.info/sct?fhir_vs=ecl/%3C%3C%2049601007%20%3A%20%3C%3C%20363698007%20%3D%20%3C%3C%2080891009%20') contains true or reverseResolve(Observation.subject).where($this.code.coding contains http://loinc.org|39156-5||'Body Mass Index').valueQuantity.where($this.system = 'http://unitsofmeasure.org').where($this.code = 'kg/m2').where($this.value > 30).empty().not()) and (birthDate < @2004-07-30 and birthDate > @1962-07-30)\"}]}]}" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "parsed = json.loads(json_string)\n", "parsed" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Aggregate completed in: 20.256 s\n" ] } ], "source": [ "print(f\"Aggregate completed in: {time() - aggregate_start:.3f} s\")" ] }, { "cell_type": "markdown", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "Extract the values from the response and put them into a Pandas dataframe." ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "def get_value(name, value, i):\n", " return [\n", " [\n", " part[value]\n", " for part\n", " in parameter['part']\n", " if part['name'] == name\n", " ][i]\n", " for parameter\n", " in parsed['parameter']\n", " if parameter['name'] == 'grouping'\n", " ]" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Vaccinated against COVID-19High riskNumber of patients
0TrueFalse266
1TrueTrue215
2FalseFalse81
3FalseTrue85
\n", "
" ], "text/plain": [ " Vaccinated against COVID-19 High risk Number of patients\n", "0 True False 266\n", "1 True True 215\n", "2 False False 81\n", "3 False True 85" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data = {\n", " 'Vaccinated against COVID-19': get_value('label', 'valueBoolean', 0),\n", " 'High risk': get_value('label', 'valueBoolean', 1),\n", " 'Number of patients': get_value('result', 'valueUnsignedInt', 0)\n", "}\n", "df = pd.DataFrame(data=data)\n", "df" ] }, { "cell_type": "markdown", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "## List the high risk unvaccinated patients\n", "\n", "Finally, we use the [extract operation](https://pathling.csiro.au/docs/server/operations/extract) to list out the patients that are high-risk and have not been vaccinated, along with the specific risk factors that were identified for each patient." ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [], "source": [ "extract_start = time()\n", "params = [\n", " # COLUMNS\n", " # Family name\n", " ('column', 'name.first().family'),\n", " # Given name\n", " ('column', 'name.first().given.first()'),\n", " # Phone number\n", " ('column', \"telecom.where(system = 'phone').first().value\"),\n", " # Chronic kidney disease\n", " ('column',\n", " 'reverseResolve(Condition.subject).code.subsumedBy(http://snomed.info/sct|709044004) contains true'),\n", " # Heart disease\n", " ('column',\n", " \"reverseResolve(Condition.subject).code.memberOf('http://snomed.info/sct?fhir_vs=ecl/%3C%3C%2049601007%20%3A%20%3C%3C%20363698007%20%3D%20%3C%3C%2080891009%20') contains true\"),\n", " # BMI > 30\n", " ('column',\n", " \"reverseResolve(Observation.subject).where(code.coding contains http://loinc.org|39156-5||'Body Mass Index').valueQuantity\"\n", " \".where(system = 'http://unitsofmeasure.org').where(code = 'kg/m2').where(value > 30).empty().not()\"),\n", " # FILTERS\n", " # Not vaccinated against COVID-19\n", " ('filter',\n", " \"(reverseResolve(Immunization.patient).vaccineCode.memberOf('https://aehrc.csiro.au/fhir/ValueSet/covid-19-vaccines') contains true).not()\"),\n", " # High risk\n", " ('filter',\n", " \"reverseResolve(Condition.subject).code.subsumedBy(http://snomed.info/sct|709044004) contains true \"\n", " \"or \"\n", " \"reverseResolve(Condition.subject).code.memberOf('http://snomed.info/sct?fhir_vs=ecl/%3C%3C%2049601007%20%3A%20%3C%3C%20363698007%20%3D%20%3C%3C%2080891009%20') contains true \"\n", " \"or \"\n", " \"reverseResolve(Observation.subject).where(code.coding contains http://loinc.org|39156-5||'Body Mass Index').valueQuantity\"\n", " \".where(system = 'http://unitsofmeasure.org').where(code = 'kg/m2').where(value > 30).empty().not()\"),\n", " # Age 18-60\n", " ('filter', 'birthDate < @2004-07-30 and birthDate > @1962-07-30')\n", "]\n", "url = f'/fhir/Patient/$extract?{urlencode(params)}'\n", "\n", "pathling_conn.request('GET', url)\n", "response = pathling_conn.getresponse()\n", "json_string = response.read()" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "text/plain": [ "{'resourceType': 'Parameters',\n", " 'parameter': [{'name': 'url',\n", " 'valueUrl': 'http://localhost:8080/fhir/$result?id=xbUwZ0obgdvBKqCf'}]}" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "parsed = json.loads(json_string)\n", "parsed" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Extract completed in: 38.177 s\n" ] } ], "source": [ "print(f\"Extract completed in: {time() - extract_start:.3f} s\")" ] }, { "cell_type": "markdown", "metadata": { "pycharm": { "name": "#%% md\n" } }, "source": [ "Get the result URL out of the extract response, download it and use it to populate a Pandas data frame.\n", "\n", "Sort the data frame by patient name before display, so that our result is deterministic." ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Family nameGiven namePhone numberChronic kidney diseaseHeart diseaseBMI > 30
38Abernathy524Kathline630555-746-7353TrueFalseTrue
29Bartell116Rhett759555-257-6514FalseFalseTrue
75Bashirian201Aldo414555-300-9051FalseFalseTrue
42Beahan375Neva514555-809-1747FalseFalseTrue
11Bednar518Chase54555-812-1196FalseFalseTrue
.....................
33Wintheiser220Georgene966555-630-3157FalseFalseTrue
7Wunsch504Domenic627555-973-4643FalseTrueTrue
13Wyman904Joey457555-833-6044FalseFalseTrue
76Zulauf375Elvin140555-925-7111FalseTrueFalse
43Zulauf375Kaitlin600555-859-1355FalseFalseTrue
\n", "

85 rows × 6 columns

\n", "
" ], "text/plain": [ " Family name Given name Phone number Chronic kidney disease \\\n", "38 Abernathy524 Kathline630 555-746-7353 True \n", "29 Bartell116 Rhett759 555-257-6514 False \n", "75 Bashirian201 Aldo414 555-300-9051 False \n", "42 Beahan375 Neva514 555-809-1747 False \n", "11 Bednar518 Chase54 555-812-1196 False \n", ".. ... ... ... ... \n", "33 Wintheiser220 Georgene966 555-630-3157 False \n", "7 Wunsch504 Domenic627 555-973-4643 False \n", "13 Wyman904 Joey457 555-833-6044 False \n", "76 Zulauf375 Elvin140 555-925-7111 False \n", "43 Zulauf375 Kaitlin600 555-859-1355 False \n", "\n", " Heart disease BMI > 30 \n", "38 False True \n", "29 False True \n", "75 False True \n", "42 False True \n", "11 False True \n", ".. ... ... \n", "33 False True \n", "7 True True \n", "13 False True \n", "76 True False \n", "43 False True \n", "\n", "[85 rows x 6 columns]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result_url = [\n", " parameter['valueUrl']\n", " for parameter\n", " in parsed['parameter']\n", " if parameter['name'] == 'url'\n", "][0]\n", "result_df = pd.read_csv(result_url, names=['Family name', 'Given name', 'Phone number',\n", " 'Chronic kidney disease', 'Heart disease', 'BMI > 30'])\n", "result_df = result_df.sort_values(['Family name', 'Given name'])\n", "result_df" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "pycharm": { "name": "#%%\n" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Total execution time: 90.490 s\n" ] } ], "source": [ "print(f\"Total execution time: {time() - start:.3f} s\")\n" ] } ], "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.10.4" } }, "nbformat": 4, "nbformat_minor": 1 }