tsk_20181208_hosp_count_no_id.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-1544281057288)\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@752c4a04\n"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018-12-08 15:58:02 WARN  Utils:66 - Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.debug.maxToStringFields' in SparkEnv.conf.\n",
      "root\n",
      " |-- ForskriverLopeNr: string (nullable = true)\n",
      " |-- ForskriverFodtAr: string (nullable = true)\n",
      " |-- ForskriverKjonn: string (nullable = true)\n",
      " |-- ForskriverUtenID: string (nullable = true)\n",
      " |-- PasientLopeNr: string (nullable = true)\n",
      " |-- PasientFodtAr: string (nullable = true)\n",
      " |-- PasientKjonn: string (nullable = true)\n",
      " |-- PasientBostedFylkeNr: string (nullable = true)\n",
      " |-- PasientBostedFylkeNavn: string (nullable = true)\n",
      " |-- PasientDodsAr: string (nullable = true)\n",
      " |-- PasientDodsMnd: string (nullable = true)\n",
      " |-- PasientUtenID: string (nullable = true)\n",
      " |-- Diff_UtleveringDato: string (nullable = true)\n",
      " |-- UtleveringsAar: string (nullable = true)\n",
      " |-- UtleveringsDato: string (nullable = true)\n",
      " |-- OrdinasjonAntallPakninger: string (nullable = true)\n",
      " |-- OrdinasjonAntallDDD: string (nullable = true)\n",
      " |-- Kategori: string (nullable = true)\n",
      " |-- KategoriNr: string (nullable = true)\n",
      " |-- Hjemmel: string (nullable = true)\n",
      " |-- HjemmelNr: string (nullable = true)\n",
      " |-- RefusjonKodeICDNr: string (nullable = true)\n",
      " |-- RefusjonKodeICPCNr: string (nullable = true)\n",
      " |-- VareNr: string (nullable = true)\n",
      " |-- VareNavn: string (nullable = true)\n",
      " |-- ATCKode: string (nullable = true)\n",
      " |-- ATCKodeDDDVerdi: string (nullable = true)\n",
      " |-- ATCKodeDDDEnhet: string (nullable = true)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "rawHospitalization: org.apache.spark.sql.Dataset[org.apache.spark.sql.Row] = [ForskriverLopeNr: string, ForskriverFodtAr: string ... 26 more fields]\n"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val rawHospitalization = spark.read\n",
    "    .option(\"header\",\"true\")\n",
    "    .option(\"delimiter\",\";\")\n",
    "    .csv(\"datasets/hospitalization\")\n",
    "    .cache()\n",
    "rawHospitalization.printSchema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |--  PasientLopeNr: string (nullable = true)\n",
      " |-- PasientFodtAr: string (nullable = true)\n",
      " |-- PasientKjonn: string (nullable = true)\n",
      " |-- PasientUtenID: string (nullable = true)\n",
      " |-- UtleveringsDato: string (nullable = true)\n",
      " |-- ATCKode: string (nullable = true)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "path: String = datasets/prescription\n",
       "raw: org.apache.spark.sql.Dataset[org.apache.spark.sql.Row] = [ PasientLopeNr: string, PasientFodtAr: string ... 4 more fields]\n"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "val path = \"datasets/prescription\"\n",
    "val raw= spark.read.option(\"delimiter\",\";\").option(\"header\",\"true\").csv(path).cache\n",
    "raw.printSchema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+\n",
      "|PasientUtenID|\n",
      "+-------------+\n",
      "|            0|\n",
      "|         null|\n",
      "+-------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rawHospitalization.select($\"PasientUtenID\").distinct.show"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+\n",
      "|PasientUtenID|\n",
      "+-------------+\n",
      "|            0|\n",
      "|            1|\n",
      "+-------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "raw.select($\"PasientUtenID\").distinct.show"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+--------+\n",
      "|PasientUtenID|   count|\n",
      "+-------------+--------+\n",
      "|            0|61925946|\n",
      "|         null|    4367|\n",
      "+-------------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rawHospitalization.select($\"PasientUtenID\").groupBy($\"PasientUtenID\").count.show"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+---------+\n",
      "|PasientUtenID|    count|\n",
      "+-------------+---------+\n",
      "|            0|370164691|\n",
      "|            1|  4740225|\n",
      "+-------------+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "raw.select($\"PasientUtenID\").groupBy($\"PasientUtenID\").count.show"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Count total prescriptions per set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "res8: Long = 374904916\n"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw.count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "res9: Long = 61930313\n"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rawHospitalization.count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+------------------+\n",
      "|RefusjonKodeICDNr|RefusjonKodeICPCNr|\n",
      "+-----------------+------------------+\n",
      "|              Z21|              null|\n",
      "|             null|               X76|\n",
      "|              -30|              null|\n",
      "|            M31.3|              null|\n",
      "|              Q61|              null|\n",
      "|              F25|              null|\n",
      "|              G12|              null|\n",
      "|              D81|              null|\n",
      "|              F32|              null|\n",
      "|              F21|              null|\n",
      "|              D66|              null|\n",
      "|             null|               -71|\n",
      "|              -71|              null|\n",
      "|            C54.1|              null|\n",
      "|              C22|              null|\n",
      "|              I01|              null|\n",
      "|             null|               P72|\n",
      "|              C78|              null|\n",
      "|              C24|              null|\n",
      "|              I21|              null|\n",
      "+-----------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rawHospitalization.where(\"Diff_UtleveringDato is not null\")\n",
    "    .select($\"RefusjonKodeICDNr\", $\"RefusjonKodeICPCNr\")\n",
    "    .distinct.show"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+------------------+\n",
      "|RefusjonKodeICDNr|RefusjonKodeICPCNr|\n",
      "+-----------------+------------------+\n",
      "|              -30|              null|\n",
      "|             null|               X76|\n",
      "|            M31.3|              null|\n",
      "|              G12|              null|\n",
      "|              F25|              null|\n",
      "|              F32|              null|\n",
      "|              D81|              null|\n",
      "|              F21|              null|\n",
      "|              D66|              null|\n",
      "|             null|               -71|\n",
      "|              -71|              null|\n",
      "|            C54.1|              null|\n",
      "|              C22|              null|\n",
      "|              I01|              null|\n",
      "|             null|               P72|\n",
      "|              C78|              null|\n",
      "|              C95|              null|\n",
      "|              C24|              null|\n",
      "|              I21|              null|\n",
      "|             null|               R95|\n",
      "+-----------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "rawHospitalization.where(\"Diff_UtleveringDato is null\")\n",
    "    .select($\"RefusjonKodeICDNr\", $\"RefusjonKodeICPCNr\")\n",
    "    .distinct.show"
   ]
  },
  {
   "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
}