20190219_general_statistics.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Intitializing Scala interpreter ..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Spark Web UI available at http://localhost:4040\n",
       "SparkContext available as 'sc' (version = 2.3.2, master = local[*], app id = local-1553468318695)\n",
       "SparkSession available as 'spark'\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "res0: org.apache.spark.sql.SparkSession = org.apache.spark.sql.SparkSession@48b2260b\n"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%run \"src/scala/register_tables.scala\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "===Prescription dataset===\n",
      "registering patients\n",
      "registering prescriptions\n",
      "registering drugs\n",
      "ok!\n",
      "===Hospitalization dataset===\n",
      "registering all\n",
      "registering patients\n",
      "registering prescriptions\n",
      "registering drugs\n",
      "ok!\n",
      "===NPR Elders===\n",
      "ok!\n",
      "+--------+--------------------------+-----------+\n",
      "|database|tableName                 |isTemporary|\n",
      "+--------+--------------------------+-----------+\n",
      "|        |elders                    |true       |\n",
      "|        |elders_drugs              |true       |\n",
      "|        |elders_patients           |true       |\n",
      "|        |elders_prescriptions      |true       |\n",
      "|        |npr_elders                |true       |\n",
      "|        |prescription_drugs        |true       |\n",
      "|        |prescription_patients     |true       |\n",
      "|        |prescription_prescriptions|true       |\n",
      "+--------+--------------------------+-----------+\n",
      "\n",
      "done!\n"
     ]
    }
   ],
   "source": [
    "register_tables(spark,\"spark-warehouse/\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NPR population: 176.817\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "176817\n",
      "172428\n",
      "(Diff ,4389)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "npr_raw: org.apache.spark.sql.DataFrame = [innmate: string, pasfylke: string ... 11 more fields]\n",
       "npr_all_count: Long = 176817\n",
       "npr_elders: org.apache.spark.sql.DataFrame = [id: string, num_hospitalizations: bigint ... 10 more fields]\n",
       "npr_elders_count: Long = 172428\n"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val npr_raw = spark.read.option(\"header\",true).csv(\"datasets/npr\")\n",
    "val npr_all_count = npr_raw.select(\"pasientlopenr\").distinct.count\n",
    "println(npr_all_count)\n",
    "\n",
    "val npr_elders = spark.sql(\"select * from npr_elders\")\n",
    "\n",
    "val npr_elders_count = npr_elders.select(\"id\").distinct.count\n",
    "println(npr_elders_count)\n",
    "\n",
    "println(\"Diff \",npr_all_count-npr_elders_count)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Elders dataset\n",
    "* Number of patients"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "elders: org.apache.spark.sql.DataFrame = [id: string, birthyear: int ... 21 more fields]\n",
       "total: Long = 765431\n",
       "hosp_ids: Long = 172428\n",
       "healthy_ids: Long = 593003\n"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val elders = spark.sql(\"select * from elders\")\n",
    "val total = elders.select(\"id\").distinct.count\n",
    "val hosp_ids = elders.where(\"diff_utleveringdato is not null\")\n",
    "    .select(\"id\").distinct.count\n",
    "val healthy_ids = elders.where(\"diff_utleveringdato is null\")\n",
    "    .select(\"id\").distinct.count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+-----+\n",
      "|birthyear|count|\n",
      "+---------+-----+\n",
      "|     2012|    5|\n",
      "|     2011|    4|\n",
      "|     2010|    1|\n",
      "|     2006|    1|\n",
      "|     2004|    1|\n",
      "+---------+-----+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "elders.select(\"id\",\"birthyear\").distinct.sort(desc(\"birthyear\"))\n",
    "    .groupBy(\"birthyear\").count.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of hospitalized patients: 172.428\n",
    "### Patients not hospitalized : 593.003\n",
    "### Total: 765.431"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# General dataset, total: 5.280.632"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "general_patients: Long = 5280632\n"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val general_patients = spark.sql(\"select * from prescription_patients\")\n",
    "    .count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prescription counts\n",
    "## Elders: 59.903.331\n",
    "## General population: 370.164.691"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "370164691\n",
      "59903331\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "general_prescription_count: Long = 370164691\n",
       "elders_prescription_count: Long = 59903331\n"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val general_prescription_count = spark.sql(\"select * from prescription_prescriptions\").select(\"drugcode\").count\n",
    "val elders_prescription_count = spark.sql(\"select * from elders_prescriptions\").select(\"drugcode\").count\n",
    "\n",
    "println(general_prescription_count)\n",
    "println(elders_prescription_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "res14: Long = 0\n"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql(\"select * from elders_prescriptions\")\n",
    "    .select(\"timestamp\")\n",
    "    .where(col(\"timestamp\") < lit(\"2010.01.01\"))\n",
    "    .count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "res15: Long = 0\n"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql(\"select * from elders_prescriptions\")\n",
    "    .select(\"timestamp\")\n",
    "    .where(col(\"timestamp\") < lit(\"2011.01.01\"))\n",
    "    .count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "res16: Long = 12594935\n"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql(\"select * from elders_prescriptions\")\n",
    "    .select(\"timestamp\")\n",
    "    .where(col(\"timestamp\") < lit(\"2012.01.01\"))\n",
    "    .count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "res17: Long = 25967931\n"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql(\"select * from elders_prescriptions\")\n",
    "    .select(\"timestamp\")\n",
    "    .where(col(\"timestamp\") < lit(\"2013.01.01\"))\n",
    "    .count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "res18: Long = 13647042\n"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql(\"select * from elders_prescriptions\")\n",
    "    .select(\"timestamp\")\n",
    "    .where(col(\"timestamp\") > lit(\"2013.01.01\"))\n",
    "    .count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "res19: Long = 0\n"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql(\"select * from elders_prescriptions\")\n",
    "    .select(\"timestamp\")\n",
    "    .where(col(\"timestamp\") > lit(\"2014.01.01\"))\n",
    "    .count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "res20: Long = 0\n"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql(\"select * from elders_prescriptions\")\n",
    "    .select(\"timestamp\")\n",
    "    .where(col(\"timestamp\") > lit(\"2015.01.01\"))\n",
    "    .count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "spylon-kernel",
   "language": "scala",
   "name": "spylon-kernel"
  },
  "language_info": {
   "codemirror_mode": "text/x-scala",
   "file_extension": ".scala",
   "help_links": [
    {
     "text": "MetaKernel Magics",
     "url": "https://github.com/calysto/metakernel/blob/master/metakernel/magics/README.md"
    }
   ],
   "mimetype": "text/x-scala",
   "name": "scala",
   "pygments_lexer": "scala",
   "version": "0.4.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}