patient-journey.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ynPe6RLRWSKd"
   },
   "source": [
    "# Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "09qvRMrxye7l"
   },
   "source": [
    "Import our usual suspects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "BOfc_-mllcUE"
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from graphdatascience import GraphDataScience\n",
    "from neo4j import GraphDatabase, RoutingControl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "pa61u1jfyk3t"
   },
   "source": [
    "Register for a sandbox and create an empty sandbox  https://sandbox.neo4j.com"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "CHR_0lmElZ-R"
   },
   "outputs": [],
   "source": [
    "# Capture connection string and auth info\n",
    "connectionUrl = 'neo4j://localhost:7687'\n",
    "username = 'neo4j'\n",
    "password = 'test1234'\n",
    "database = 'neo4j'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver = GraphDatabase.driver(\n",
    "    connectionUrl, \n",
    "    auth=(username, password)\n",
    ")\n",
    "driver.verify_connectivity()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For sandbox, connect like this (with ip address and password for yor sandbox )\n",
    "# driver = GraphDatabase.driver(\n",
    "#   \"bolt://35.175.243.212:7687\",\n",
    "#   auth=(\"neo4j\", \"sections-ladders-respects\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Utility\n",
    "def split_dataframe(df, chunk_size = 50_000): \n",
    "    chunks = list()\n",
    "    num_chunks = len(df) // chunk_size + 1\n",
    "    for i in range(num_chunks):\n",
    "        chunks.append(df[i*chunk_size:(i+1)*chunk_size])\n",
    "    return chunks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0OMlYdxHWZLx"
   },
   "source": [
    "# Graph creation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Source"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "synthea_data_dir = \"~/import/synthea_data_csv/csv1000/\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "cdTfdAyV2ZaR"
   },
   "outputs": [],
   "source": [
    "schema_statements = [\n",
    "    'create constraint patientNumber if not exists for (n:Patient) require (n.id) is node key',\n",
    "    'create constraint payerId if not exists for (n:Payer) require (n.id) is node key',\n",
    "    'create constraint encounterId if not exists for (n:Encounter) require (n.id) is node key',\n",
    "    'create constraint conditionCode if not exists for (n:Condition) require (n.code) is node key',\n",
    "    'create constraint providerId if not exists for (n:Provider) require (n.id) is node key',\n",
    "    'create constraint observation if not exists for (n:Observation) require (n.code) is node key',\n",
    "    'create constraint organization if not exists for (n:Organization) require (n.id) is node key',\n",
    "    'create constraint drug if not exists for (n:Drug) require (n.code) is node key',\n",
    "    'create constraint careplan if not exists for (n:CarePlan) require (n.id) is node key',\n",
    "    'create constraint reaction if not exists for (n:Reaction) require (n.id) is node key',\n",
    "    'create constraint device if not exists for (n:Device) require (n.code) is node key',\n",
    "    'create constraint speciality if not exists for (n:Speciality) require (n.name) is node key',\n",
    "    'create constraint conditionDescription if not exists for (n:ConditionDescription) require (n.text) is node key'\n",
    "]\n",
    "for statement in schema_statements:\n",
    "    driver.execute_query(\n",
    "        statement,\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE\n",
    "    )\n",
    "\n",
    "# Fetch all constraints\n",
    "schema_result_df  = driver.execute_query(\n",
    "    'show constraints',\n",
    "    database_=database,\n",
    "    routing_=RoutingControl.READ,\n",
    "    result_transformer_= lambda r: r.to_df()\n",
    ")\n",
    "schema_result_df.head(100)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "LbsQlwvwku0b"
   },
   "outputs": [],
   "source": [
    "df_patients = pd.read_csv(synthea_data_dir + 'patients.csv', delimiter=',').replace({np.nan: None})\n",
    "df_patients.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "uduojIopm0qV"
   },
   "outputs": [],
   "source": [
    "for chunk in split_dataframe(df_patients):\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        ''' \n",
    "            unwind $rows as row\n",
    "            merge (p:Patient{id:row['Id']})\n",
    "            set \n",
    "            p.first = row['FIRST'],\n",
    "            p.last = row['LAST'],\n",
    "            p.birthdate = Date(row['BIRTHDATE']),\n",
    "            p.birthplace = row['BIRTHPLACE'],\n",
    "            p.deathdate = Date(row['DEATHDATE']),\n",
    "            p.ethnicity = row['ETHNICITY'],\n",
    "            p.gender = row['GENDER'],\n",
    "            p.prefix = row['PREFIX'],\n",
    "            p.race = row['RACE'],\n",
    "            p.address = row['ADDRESS'],\n",
    "            p.state = row['STATE'],\n",
    "            p.city = row['CITY'],\n",
    "            p.county = row['COUNTY'],\n",
    "            p.drivers = row['DRIVERS'],\n",
    "            p.healthcare_coverage = toFloat(row['HEALTHCARE_COVERAGE']),\n",
    "            p.healthcare_expenses = toFloat(row['HEALTHCARE_EXPENSES']),\n",
    "            p.latitude = toFloat(row['LAT']),\n",
    "            p.longitude = toFloat(row['LON']),\n",
    "            p.location = point({latitude:toFloat(row['LAT']),longitude: toFloat(row['LON'])}),\n",
    "            p.martial = row['MARITAL']\n",
    "            return count(*) as rows_processed\n",
    "        ''',\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE,\n",
    "        rows = chunk.to_dict('records')\n",
    "    )\n",
    "    print(summary.counters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_payers = pd.read_csv(synthea_data_dir + 'payers.csv', delimiter=',').replace({np.nan: None})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chunk in split_dataframe(df_payers):\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        ''' \n",
    "            unwind $rows as row\n",
    "            merge (p:Payer{id:row['Id']})\n",
    "            set p.name = row['NAME'],\n",
    "                p.address = row['ADDRESS'],\n",
    "                p.city = row['CITY'],\n",
    "                p.zip = row['ZIP'],\n",
    "                p.state = row['STATE_HEADQUARTERED']\n",
    "            return count(*) as rows_processed\n",
    "        ''',\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE,\n",
    "        rows = chunk.to_dict('records')\n",
    "    )\n",
    "    print(summary.counters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_encounters = pd.read_csv(synthea_data_dir + 'encounters.csv', delimiter=',').replace({np.nan: None})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_encounters.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chunk in split_dataframe(df_encounters):\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        ''' \n",
    "            unwind $rows as row\n",
    "            merge (e:Encounter {id:row['Id']})\n",
    "            set e.code = row['CODE'],\n",
    "            e.description = row['DESCRIPTION'],\n",
    "            e.class = row['ENCOUNTERCLASS'],\n",
    "            e.start = datetime(row['START']),\n",
    "            e.baseCost = toFloat(row['BASE_ENCOUNTER_COST']),\n",
    "            e.claimCost = toFloat(row['TOTAL_CLAIM_COST']),\n",
    "            e.coveredAmount = toFloat(row['PAYER_COVERAGE']),\n",
    "            e.isEnd = false,\n",
    "            e.end = datetime(row['STOP'])\n",
    "            merge (p:Patient {id:row['PATIENT']})\n",
    "            merge (p)-[:HAS_ENCOUNTER]->(e)\n",
    "            merge (pr:Provider {id: row['PROVIDER']})\n",
    "            merge (e)-[:HAS_PROVIDER]->(pr)\n",
    "            merge (o:Organization {id:row['ORGANIZATION']})\n",
    "            merge (e)-[:AT_ORGANIZATION]->(o)\n",
    "            with e,row\n",
    "            match (pa:Payer {id:row['PAYER']})\n",
    "            merge (e)-[:HAS_PAYER]->(pa)\n",
    "            return count(*) as rows_processed\n",
    "        ''',\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE,\n",
    "        rows = chunk.to_dict('records')\n",
    "    )\n",
    "    print(summary.counters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_providers = pd.read_csv(synthea_data_dir + 'providers.csv', delimiter=',').replace({np.nan: None})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chunk in split_dataframe(df_providers):\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        ''' \n",
    "            unwind $rows as row\n",
    "            merge (p:Provider {id: row['Id']})\n",
    "            set p.name = row['NAME'],\n",
    "                p.address = row['ADDRESS'],\n",
    "                p.location = point({latitude:toFloat(row['LAT']), longitude:toFloat(row['LON'])})\n",
    "            merge (s:Speciality {name: row['SPECIALITY']})\n",
    "            merge (p)-[:HAS_SPECIALITY]->(s)\n",
    "            merge (o:Organization {id: row['ORGANIZATION']})\n",
    "            merge (p)-[:BELONGS_TO]->(o)\n",
    "            return count(*) as rows_processed\n",
    "        ''',\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE,\n",
    "        rows = chunk.to_dict('records')\n",
    "    )\n",
    "    print(summary.counters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_payer_transitions = pd.read_csv(synthea_data_dir + 'payer_transitions.csv', delimiter=',').replace({np.nan: None})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chunk in split_dataframe(df_payer_transitions):\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        ''' \n",
    "            unwind $rows as row\n",
    "            match (p:Patient {id:row['PATIENT']})\n",
    "            merge (payer:Payer {id:row['PAYER']})\n",
    "            merge (p)-[s:INSURANCE_START]->(payer)\n",
    "                set s.year=toInteger(row['START_YEAR'])\n",
    "            merge (p)-[e:INSURANCE_END]->(payer)\n",
    "                set e.year=toInteger(row['END_YEAR'])\n",
    "            return count(*) as rows_processed\n",
    "        ''',\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE,\n",
    "        rows = chunk.to_dict('records')\n",
    "    )\n",
    "    print(summary.counters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_allergies = pd.read_csv(synthea_data_dir + 'allergies.csv', delimiter=',').replace({np.nan: None})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chunk in split_dataframe(df_allergies):\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        ''' \n",
    "            unwind $rows as row\n",
    "            match (p:Patient {id:row['PATIENT']})\n",
    "            merge (a:Allergy {code: row['CODE']})\n",
    "                set a.description = row['DESCRIPTION'],\n",
    "                    a.type = row['TYPE'],\n",
    "                    a.category = row['CATEGORY'],\n",
    "                    a.system = row['SYSTEM']\n",
    "            merge (e:Encounter {id:row['ENCOUNTER']})\n",
    "            merge (p)-[:HAS_ENCOUNTER]->(e)\n",
    "            merge (p)-[:HAS_ALLERGY]->(a)\n",
    "            merge (e)-[r:ALLERGY_DETECTED]->(a)\n",
    "                set r.start = datetime(row['START'])\n",
    "            with p,a,e,r,row\n",
    "            where row['REACTION1'] is not null and row['REACTION1'] <> ''\n",
    "            merge (r1:Reaction {id: row['REACTION1']})\n",
    "                set r1.description = row['DESCRIPTION1']\n",
    "            merge (p)-[rr:HAS_REACTION]->(r1)\n",
    "                set rr.severity = row['SEVERITY1']\n",
    "            merge (a)-[:CAUSES_REACTION]->(r1)\n",
    "            with p,a,e,r,row\n",
    "            where row['REACTION2'] is not null and row['REACTION2'] <> ''\n",
    "            merge (r2:Reaction {id: row['REACTION2']})\n",
    "                set r2.description = row['DESCRIPTION2']\n",
    "            merge (p)-[rrr:HAS_REACTION]->(r2)\n",
    "                set rrr.severity = row['SEVERITY2']\n",
    "            merge (a)-[:CAUSES_REACTION]->(r2)\n",
    "            with p,a,e,r,row\n",
    "            where row['STOP'] is not null and row['STOP'] <> ''\n",
    "                set r.isEnd = True,\n",
    "                    r.stop = datetime(row['STOP'])\n",
    "            return count(*) as rows_processed\n",
    "        ''',\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE,\n",
    "        rows = chunk.to_dict('records')\n",
    "    )\n",
    "    print(summary.counters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_conditions = pd.read_csv(synthea_data_dir + 'conditions.csv', delimiter=',').replace({np.nan: None})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chunk in split_dataframe(df_conditions):\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        ''' \n",
    "            unwind $rows as row\n",
    "            match (p:Patient {id:row['PATIENT']})\n",
    "            merge (c:Condition {code:row['CODE']})\n",
    "                set c.description  = row['DESCRIPTION'],\n",
    "                    c.start = datetime(row['START']),\n",
    "                    c.code = row['CODE'],\n",
    "                    c.isEnd = false\n",
    "            merge (e:Encounter {id:row['ENCOUNTER']})\n",
    "            merge (p)-[:HAS_ENCOUNTER]->(e)\n",
    "            merge (e)-[:HAS_CONDITION]->(c)\n",
    "            with p,c,e,row\n",
    "                where row['STOP'] is not null and row['STOP'] <> ''\n",
    "                    set c.stop = row['STOP'], c.isEnd = true\n",
    "            return count(*) as rows_processed\n",
    "        ''',\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE,\n",
    "        rows = chunk.to_dict('records')\n",
    "    )\n",
    "    print(summary.counters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_medications = pd.read_csv(synthea_data_dir + 'medications.csv', delimiter=',').replace({np.nan: None})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chunk in split_dataframe(df_medications):\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        ''' \n",
    "            unwind $rows as row\n",
    "            merge (p:Patient {id:row['PATIENT']})\n",
    "            merge (e:Encounter {id:row['ENCOUNTER']})\n",
    "            merge (d:Drug {code:row['CODE']})\n",
    "                set d.description = row['DESCRIPTION'],\n",
    "                    d.basecost = row['BASE_COST'],\n",
    "                    d.totalcost = row['TOTALCOST'],\n",
    "                    d.isEnd = false,\n",
    "                    d.start = datetime(row['START'])\n",
    "            merge (p)-[:HAS_ENCOUNTER]->(e)\n",
    "            merge (e)-[:HAS_DRUG]->(d)\n",
    "            with p,d,e,row\n",
    "            where row['STOP'] is not null and row['STOP'] <> ''\n",
    "                set d.stop = datetime(row['STOP']), d.isEnd = true\n",
    "            return count(*) as rows_processed\n",
    "        ''',\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE,\n",
    "        rows = chunk.to_dict('records')\n",
    "    )\n",
    "    print(summary.counters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_procedures = pd.read_csv(synthea_data_dir + 'procedures.csv', delimiter=',').replace({np.nan: None})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chunk in split_dataframe(df_procedures):\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        ''' \n",
    "            unwind $rows as row\n",
    "            merge (p:Patient {id:row['PATIENT']})\n",
    "            merge (r:Procedure {code:row['CODE']})\n",
    "                set r.description=row['DESCRIPTION']\n",
    "            merge (pe:Encounter {id:row['ENCOUNTER'], isEnd: false})\n",
    "                on create\n",
    "                set pe.date=datetime(row['START']), pe.code=row['CODE']\n",
    "                on match\n",
    "                set pe.code=row['CODE']\n",
    "            merge (p)-[:HAS_ENCOUNTER]->(pe)\n",
    "            merge (pe)-[:HAS_PROCEDURE]->(r)\n",
    "            return count(*) as rows_processed\n",
    "        ''',\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE,\n",
    "        rows = chunk.to_dict('records')\n",
    "    )\n",
    "    print(summary.counters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_observations = pd.read_csv(synthea_data_dir + 'observations.csv', delimiter=',').replace({np.nan: None})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_observations[df_observations['ENCOUNTER'].isnull()].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_observations=df_observations.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chunk in split_dataframe(df_observations):\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        ''' \n",
    "            unwind $rows as row\n",
    "            match (p:Patient {id:row['PATIENT']})\n",
    "            merge (oe:Encounter {id:row['ENCOUNTER']})\n",
    "            merge (ob:Observation{code:row['CODE']})\n",
    "            set ob.description = row['DESCRIPTION'],\n",
    "                ob.type = row['TYPE'],\n",
    "                ob.units = row['UNTIS'],\n",
    "                ob.category = row['CATEGORY'],\n",
    "                ob.type = row['TYPE']\n",
    "            merge (oe)-[r:HAS_OBSERVATION]->(ob)\n",
    "            set r.value = row['VALUE'], \n",
    "                r.date = datetime(row['DATE']),\n",
    "                r.unit = row['UNITS']\n",
    "            return count(*) as rows_processed\n",
    "        ''',\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE,\n",
    "        rows = chunk.to_dict('records')\n",
    "    )\n",
    "    print(summary.counters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_careplans = pd.read_csv(synthea_data_dir + 'careplans.csv', delimiter=',').replace({np.nan: None})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chunk in split_dataframe(df_careplans):\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        ''' \n",
    "            unwind $rows as row\n",
    "            match (p:Patient {id:row['PATIENT']})\n",
    "            merge (c:CarePlan {id:row['Id']})\n",
    "            set c.description = row['DESCRIPTION'],\n",
    "                c.reasoncode = row['REASONCODE'],\n",
    "                c.code = row['CODE'],\n",
    "                c.start = datetime(row['START']),\n",
    "                c.isEnd = false\n",
    "            merge (e:Encounter {id:row['ENCOUNTER']})\n",
    "            merge (p)-[:HAS_ENCOUNTER]->(e)\n",
    "            merge (e)-[:HAS_CARE_PLAN]->(c)\n",
    "            with p,c, row\n",
    "            where row['STOP'] is not null and row['STOP'] <> '' \n",
    "            set c.end = datetime(row['STOP']),\n",
    "                c.isEnd = true\n",
    "            return count(*) as rows_processed\n",
    "        ''',\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE,\n",
    "        rows = chunk.to_dict('records')\n",
    "    )\n",
    "    print(summary.counters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_organizations = pd.read_csv(synthea_data_dir + 'organizations.csv', delimiter=',').replace({np.nan: None})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chunk in split_dataframe(df_organizations):\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        ''' \n",
    "            unwind $rows as row\n",
    "            merge (o:Organization {id:row['Id']})\n",
    "            set o.name = row['NAME'],\n",
    "                o.address = row['ADDRESS'],\n",
    "                o.city = row['CITY'],\n",
    "                o.location = point({latitude:toFloat(row['LAT']), longitude:toFloat(row['LON'])})\n",
    "            return count(*) as rows_processed\n",
    "        ''',\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE,\n",
    "        rows = chunk.to_dict('records')\n",
    "    )\n",
    "    print(summary.counters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_devices = pd.read_csv(synthea_data_dir + 'devices.csv', delimiter=',').replace({np.nan: None})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for chunk in split_dataframe(df_devices):\n",
    "    records, summary, keys = driver.execute_query(\n",
    "        ''' \n",
    "            unwind $rows as row\n",
    "            merge (d:Device {code:row['CODE']})\n",
    "                set d.description = row['DESCRIPTION']\n",
    "            merge (e:Encounter {id:row['ENCOUNTER']})\n",
    "            merge (e)-[:DEVICE_USED]->(d)\n",
    "            return count(*) as rows_processed\n",
    "        ''',\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE,\n",
    "        rows = chunk.to_dict('records')\n",
    "    )\n",
    "    print(summary.counters)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Enrich graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create linked list between encounters in chronlolgical order\n",
    "with driver.session(database=database) as session:\n",
    "    session.run(\n",
    "        ''' \n",
    "            match (p:Patient)\n",
    "            call (p) { \n",
    "                match (p)-[:HAS_ENCOUNTER]->(e:Encounter)\n",
    "                with p,e ORDER BY e.start\n",
    "                with p, collect(e) as encounters\n",
    "                call apoc.nodes.link( encounters , \"NEXT\")\n",
    "                with p, head(encounters) as first, last(encounters) as last\n",
    "                merge (p)-[:FIRST]->(first)\n",
    "                merge (p)-[:LAST]->(last)\n",
    "                set last.isEnd = True,\n",
    "                first.isStart = True\n",
    "            } in transactions of 5_000 rows\n",
    "        '''\n",
    "    ).consume()\n",
    "    session.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drug paired with condition\n",
    "with driver.session(database=database) as session:\n",
    "    session.run(\n",
    "        ''' \n",
    "            match (c:Condition)<-[:HAS_CONDITION]-(e:Encounter)-[:HAS_DRUG]->(d:Drug)\n",
    "            with c, count(*) as total_pairs\n",
    "            set c.total_drug_pairings = total_pairs;\n",
    "        '''\n",
    "    ).consume()\n",
    "    session.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create condition descriptions\n",
    "with driver.session(database=database) as session:\n",
    "    session.run(\n",
    "        ''' \n",
    "            match (c:Condition)\n",
    "            with distinct c.description as text\n",
    "            merge (:ConditionDescription {text: text})\n",
    "        '''\n",
    "    ).consume()\n",
    "    session.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Consecutive conditions network\n",
    "with driver.session(database=database) as session:\n",
    "    session.run(\n",
    "        ''' \n",
    "            match (c:Condition)<--(e:Encounter)-[:NEXT*0..1]->(e2:Encounter)-->(c2:Condition)\n",
    "            with c.description as desc1, c2.description as desc2, count(*) as count\n",
    "            match (n1:ConditionDescription{text: desc1}), (n2:ConditionDescription{text: desc2})\n",
    "            merge (n1)-[r:NEXT]->(n2)\n",
    "                set r.amount = count\n",
    "        '''\n",
    "    ).consume()\n",
    "    session.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add additional indexes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "index_statements = [\n",
    "    'create point index patientLocation if not exists for (n:Patient) on (n.location)',\n",
    "    'create point index providerLocation if not exists for (n:Provider) on (n.location)',\n",
    "    'create point index organizationLocation if not exists for (n:Organization) on (n.location)',\n",
    "    'create text  index patient_index_name if not exists for (n:Patient) on (n.id)',\n",
    "    'create text  index encounterDate_name if not exists for (n:Encounter) on (n.date)',\n",
    "    'create text  index encounterIsEnd_name if not exists for (n:Encounter) on (n.isEnd)',\n",
    "    'create text  index organization_name if not exists for (n:Organization) on (n.id)',\n",
    "    'create text  index drug_name if not exists for (n:Drug) on (n.code)',\n",
    "    'create text  index carePlan_name if not exists for (n:CarePlan) on (n.id)',\n",
    "    'create text  index speciality_name if not exists for (n:Speciality) on (n.name)',\n",
    "    'create text  index allergy_name if not exists for (n:Allergy) on (n.code)',\n",
    "    'create text  index procedure_name if not exists for (n:Procedure) on (n.code)',\n",
    "    'create text  index observation_name if not exists for (n:Observation) on (n.code)',\n",
    "    'create text  index device_name if not exists for (n:Device) on (n.code)'\n",
    "]\n",
    "for statement in index_statements:\n",
    "    driver.execute_query(\n",
    "        statement,\n",
    "        database_=database,\n",
    "        routing_=RoutingControl.WRITE\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rZo7Gln2jJcF"
   },
   "source": [
    "# Basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "70-xbOfgjQsY"
   },
   "outputs": [],
   "source": [
    "# Node count\n",
    "driver.execute_query(\n",
    "    ''' \n",
    "    match (n)\n",
    "    return labels(n) as labels, count(*) as count\n",
    "    ''',\n",
    "    database_=database,\n",
    "    routing_=RoutingControl.READ,\n",
    "    result_transformer_= lambda r: r.to_df()\n",
    ").head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "volCOjn_jjm3"
   },
   "outputs": [],
   "source": [
    "# Relationship count\n",
    "driver.execute_query(\n",
    "    ''' \n",
    "    match ()-[r]->()\n",
    "    return type(r) as type, count(*) as count\n",
    "    ''',\n",
    "    database_=database,\n",
    "    routing_=RoutingControl.READ,\n",
    "    result_transformer_= lambda r: r.to_df()\n",
    ").head(30)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "kx1UHN87Kfup"
   },
   "source": [
    "# Graph Data Science"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "kulQKQj63Zb_"
   },
   "source": [
    "Let's get this party started"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gds = GraphDataScience(connectionUrl, auth=(username, password))\n",
    "gds.set_database(database)\n",
    "gds.version()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "BDt3N5vPALJS"
   },
   "outputs": [],
   "source": [
    "G, res = gds.graph.project(\n",
    "    \"patient_allergies\",                    # Graph name\n",
    "    [\"Patient\", \"Allergy\",\"Reaction\"],      # Node projection\n",
    "    [\"HAS_REACTION\", \"HAS_ALLERGY\"]         # Relationship projection\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7rBfNJ6A9m65"
   },
   "outputs": [],
   "source": [
    "res\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gds.nodeSimilarity.stats(G)['similarityDistribution']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gds.nodeSimilarity.write(G,\n",
    "                         writeRelationshipType='SIMLAR_ALLERGIC_REACTION',\n",
    "                         writeProperty='sim_score',\n",
    "                         similarityCutoff=0.7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "G.drop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove symetric relationships\n",
    "gds.run_cypher('''\n",
    "  match (a:Patient)-[r:SIMLAR_ALLERGIC_REACTION]->(b:Patient) \n",
    "    where exists { (b)-[:SIMLAR_ALLERGIC_REACTION]->(a) }\n",
    "    and   a<>b\n",
    "  delete r\n",
    "''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Neodash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Backup to file\n",
    "# dashboard_json = driver.execute_query(\n",
    "#     '''\n",
    "#     match (n:`_Neodash_Dashboard`) \n",
    "#     return n{.*} as dashboard limit 1\n",
    "#     ''',\n",
    "#     database_=database,\n",
    "#     routing_=RoutingControl.READ,\n",
    "#     result_transformer_= lambda r: r.single(strict=True).get('dashboard')\n",
    "# )\n",
    "# del dashboard_json['date']\n",
    "\n",
    "# with open('patient-journey-dashboard.json', 'w') as file:\n",
    "#     json.dump(dashboard_json, file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Restore from file and deploy\n",
    "dashboard_from_file = {}\n",
    "with open('patient-journey-dashboard.json') as file:\n",
    "    dashboard_from_file = json.load(file)\n",
    "\n",
    "driver.execute_query(\n",
    "    '''\n",
    "    merge (n:`_Neodash_Dashboard`{uuid:$data.uuid})\n",
    "    set n += $data,\n",
    "        n.date = datetime() \n",
    "    return true\n",
    "    ''',\n",
    "    data = dashboard_from_file,\n",
    "    database_=database,\n",
    "    routing_=RoutingControl.WRITE,\n",
    "    result_transformer_= lambda r: r.single(strict=True).get('dashboard')\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Bloom"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Search phrase: Prediabetes to diabetes\n",
    "```cypher\n",
    "match path=(pd:Condition{description:\"Prediabetes\"})<-[:HAS_CONDITION]-(e1)(\n",
    "    (f)-[n:NEXT]->(t)\n",
    "    ){1,10}\n",
    "(e2)-[:HAS_CONDITION]->(d:Condition{description:\"Diabetes\"})\n",
    "return path limit 6\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save for later"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```cypher\n",
    "match pattern1=(p1:Patient)-[:HAS_ALLERGY]-(a)<-[:HAS_ALLERGY]-(p2:Patient), \n",
    "      pattern2=(p1)-[:HAS_REACTION]->(r)<-[:HAS_REACTION]-(p2)\n",
    "with p1,p2, count(distinct r) as shared_reactions, count(distinct a) as shared_allergies, collect(pattern1) as path1, collect(pattern2) as path2 \n",
    "return * order by shared_reactions desc limit 1\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "provenance": [],
   "toc_visible": true
  },
  "kernelspec": {
   "display_name": "notebooks",
   "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.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}