Code/Transform.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "id": "9cb2a19a",
      "metadata": {
        "id": "9cb2a19a"
      },
      "source": [
        "# Laden der Brustkrebsdaten in die SQLite-Datenbank"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "4cff46c2",
      "metadata": {
        "id": "4cff46c2"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Fuenfgeld/DMA2022DataProjectB/blob/main/Code/Transform.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "6d97e6aa",
      "metadata": {
        "id": "6d97e6aa"
      },
      "source": [
        "In diesem Notebook werden die Brustkrebsdaten in die Datenbank geladen und danach für die Analyse wird ein neues Datenmodell erstellt."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "kZcljh3B8rh8",
      "metadata": {
        "id": "kZcljh3B8rh8"
      },
      "outputs": [],
      "source": [
        "#Löschen aller Variablen\n",
        "%reset -f "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "deee0a2f",
      "metadata": {
        "id": "deee0a2f"
      },
      "outputs": [],
      "source": [
        "#Laden Libraries\n",
        "import sqlite3\n",
        "import pandas as pd\n",
        "import os\n",
        "from google.colab import drive"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "3KV2Ff719nt_",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3KV2Ff719nt_",
        "outputId": "4d2e41c6-31cf-476e-f2e4-c289e9017d02"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "sys.version_info(major=3, minor=7, micro=13, releaselevel='final', serial=0)"
            ]
          },
          "metadata": {},
          "execution_count": 34
        }
      ],
      "source": [
        "#Version \n",
        "import sys\n",
        "sys.version_info"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "kRPEnpQc9q0S",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kRPEnpQc9q0S",
        "outputId": "0b78e5f7-d6ae-428a-c8ac-d5b7bfaf7ead"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(2, 6, 0)"
            ]
          },
          "metadata": {},
          "execution_count": 35
        }
      ],
      "source": [
        "sqlite3.version_info"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "sCqdVhdp9xAh",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "sCqdVhdp9xAh",
        "outputId": "34e85365-b704-451d-cafc-2f6a0d7f3ba8"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'1.3.5'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 36
        }
      ],
      "source": [
        "pd.__version__"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "9666c8df",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "9666c8df",
        "outputId": "95d6ce3c-bd0d-42d9-9a25-1d18d6e7d979"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/gdrive/\n"
          ]
        }
      ],
      "source": [
        "#Verbindung zu GoogleDrive\n",
        "drive.mount('/content/gdrive/', force_remount=True)\n"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#%cd gdrive/Shareddrives/'Studie Breast Cancer'/\n",
        "%cd gdrive/Shareddrives/'Studie Breast Cancer'/TestFolder\n",
        "%ls"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "CJl2AypFaACq",
        "outputId": "e788fdc7-0f03-4ca6-c9bb-72da7cfe01f0"
      },
      "id": "CJl2AypFaACq",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "/content/gdrive/Shareddrives/Studie Breast Cancer/TestFolder\n",
            "conditions.csv    patients.csv    sqliteDatabase.py\n",
            "medications.csv   procedures.csv  sqliteDatabaseUnitTest.py\n",
            "observations.csv  \u001b[0m\u001b[01;34m__pycache__\u001b[0m/    Transform.ipynb\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!python3 sqliteDatabase.py\n",
        "!python3 sqliteDatabaseUnitTest.py\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wT0baXLlabug",
        "outputId": "0251a176-a800-46db-a109-85f9356fdb99"
      },
      "id": "wT0baXLlabug",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "...\n",
            "----------------------------------------------------------------------\n",
            "Ran 3 tests in 3.164s\n",
            "\n",
            "OK\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%cd ..\n",
        "%cd ..\n",
        "%cd .."
      ],
      "metadata": {
        "id": "781h8ZVfd4tL",
        "outputId": "177cf665-d949-49dd-aee6-db856bf384bd",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "id": "781h8ZVfd4tL",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "/content/gdrive/Shareddrives/Studie Breast Cancer\n",
            "/content/gdrive/Shareddrives\n",
            "/content/gdrive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "J-x1VneoElR1",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "J-x1VneoElR1",
        "outputId": "06755097-8281-46e6-8967-e928c97a920a"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "careplans.csv\t disease.csv\t      observations.csv\t  payers.csv\n",
            "careplans.xlsx\t disease.xlsx\t      observations.xlsx   payer_transitions.csv\n",
            "conditions.csv\t encounters.csv       organizations.csv   procedures.csv\n",
            "conditions.xlsx  imaging_studies.csv  organizations.xlsx  providers.csv\n",
            "devices.csv\t immunizations.csv    patients.csv\t  supplies.csv\n",
            "devices.xlsx\t medications.csv      patients.xlsx\n"
          ]
        }
      ],
      "source": [
        "# Liste mit Tabellen \n",
        "!ls \"/content/gdrive/Shareddrives/Studie Breast Cancer/breast_cancer/\""
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%cd ..\n",
        "%ls\n"
      ],
      "metadata": {
        "id": "nMOLb2TKgWqE",
        "outputId": "9bd6da26-afec-41ab-e16d-70e6a56ba3ea",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "id": "nMOLb2TKgWqE",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "/content\n",
            "\u001b[0m\u001b[01;34mgdrive\u001b[0m/  \u001b[01;34msample_data\u001b[0m/\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "a9deab3b",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "a9deab3b",
        "outputId": "9eb65ed3-60a3-459d-8b49-c456f30811d5"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Datenbank wurde erfolgreich geöffnet\n"
          ]
        }
      ],
      "source": [
        "# Verbindung zur Datenbank\n",
        "conn = sqlite3.connect('/content/gdrive/Shareddrives/Studie Breast Cancer/BreastCancerDB.db')\n",
        "cursor = conn.cursor()\n",
        "print(\"Datenbank wurde erfolgreich geöffnet\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "3c50fe52",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3c50fe52",
        "outputId": "c32ea542-3965-4826-c4a9-919259353416"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7f3fc94b05e0>"
            ]
          },
          "metadata": {},
          "execution_count": 45
        }
      ],
      "source": [
        "# Löschen Tabellen wenn sie schon exestieren\n",
        "cursor.execute(\"DROP TABLE IF EXISTS patients\")\n",
        "cursor.execute(\"DROP TABLE IF EXISTS conditions\")\n",
        "cursor.execute(\"DROP TABLE IF EXISTS medications\")\n",
        "cursor.execute(\"DROP TABLE IF EXISTS observations\")\n",
        "cursor.execute(\"DROP TABLE IF EXISTS procedures\")\n",
        "cursor.execute(\"DROP TABLE IF EXISTS patient_careplans\")\n",
        "cursor.execute(\"DROP TABLE IF EXISTS patients_new\")"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "A-PWJCMUcQu1"
      },
      "id": "A-PWJCMUcQu1",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "03415042",
      "metadata": {
        "id": "03415042"
      },
      "source": [
        "Mit dem folgenden Code werden die Daten aus den CSV.-Dateien in die DataFrames von Pandas eingelesen."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "b81ff71b",
      "metadata": {
        "id": "b81ff71b"
      },
      "outputs": [],
      "source": [
        "# Laden Daten \n",
        "patients = pd.read_csv('gdrive/Shareddrives/Studie Breast Cancer/breast_cancer/patients.csv', sep=\",\")\n",
        "conditions = pd.read_csv( 'gdrive/Shareddrives/Studie Breast Cancer/breast_cancer/conditions.csv', sep=\",\")\n",
        "medications = pd.read_csv('gdrive/Shareddrives/Studie Breast Cancer/breast_cancer/medications.csv', sep=\",\")\n",
        "observations = pd.read_csv('gdrive/Shareddrives/Studie Breast Cancer/breast_cancer/observations.csv', sep=\",\")\n",
        "procedures = pd.read_csv('gdrive/Shareddrives/Studie Breast Cancer/breast_cancer/procedures.csv', sep=\",\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "96a425ff",
      "metadata": {
        "id": "96a425ff"
      },
      "source": [
        "Die Tabellen enthalten die gleichen Namen. Den Zusammenhang zwischen den Daten ist mit dem folgenden ER-Diagramm visualisiert:  "
      ]
    },
    {
      "cell_type": "markdown",
      "id": "4e6e24c3",
      "metadata": {
        "id": "4e6e24c3"
      },
      "source": [
        "<img src=https://raw.githubusercontent.com/Fuenfgeld/DMA2022DataProjectB/main/Daten_schema/Brustkrebsdaten.png>"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "16ab4380",
      "metadata": {
        "id": "16ab4380"
      },
      "source": [
        "Für die Daten werden neue Tabellen mit entsprechenden Attributen in der Datenbank eingelegt."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f92ef57f",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "f92ef57f",
        "outputId": "f862bcd1-8d15-4d0f-dcdd-a9e134f3e015"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7f3fc94b05e0>"
            ]
          },
          "metadata": {},
          "execution_count": 47
        }
      ],
      "source": [
        "# Tabellen erstellen\n",
        "cursor.execute('''\n",
        "\t\tCREATE TABLE IF NOT EXISTS patients (\n",
        "\t\t\tId nvarchar(36) primary key,\n",
        "            BIRTHDATE Date,\n",
        "            DEATHDATE Date,\n",
        "            SSN nvarchar(50),\n",
        "            DRIVERS nvarchar(50),\n",
        "            PASSPORT nvarchar(10),\n",
        "            PREFIX nvarchar(4),\n",
        "            FIRST nvarchar(50),\n",
        "            LAST nvarchar(50),\n",
        "            SUFFIX nvarchar(10),\n",
        "            MAIDEN nvarchar(50),\n",
        "            MARITAL nvarchar(1),\n",
        "            RACE nvarchar(10),\n",
        "            ETHNICITY nvarchar(50),\n",
        "            GENDER nvarchar(1),\n",
        "            BIRTHPLACE nvarchar(50),\n",
        "            ADDRESS nvarchar(50),\n",
        "            CITY nvarchar(50),\n",
        "            STATE nvarchar(50),\n",
        "            COUNTY nvarchar(50),\n",
        "            ZIP nvarchar(4),\n",
        "            LAT nvarchar(10),\n",
        "            LON nvarchar(10),\n",
        "            HEALTHCARE_EXPENSES float,\n",
        "            HEALTHCARE_COVERAGE float\n",
        "          \n",
        "\t\t\t)\n",
        "             ''')\n",
        "\n",
        "cursor.execute('''\n",
        "\t\tCREATE TABLE IF NOT EXISTS conditions (\n",
        "            START Date,\n",
        "            STOP Date,\n",
        "            PATIENT nvarchar(36),\n",
        "            ENCOUNTER nvarchar(50),\n",
        "            DESCRIPTION nvarchar(256),\n",
        "            CODE int,\n",
        "            foreign key(PATIENT) references patients(Id) \n",
        "\t\t\t)\n",
        "               ''')\n",
        "\n",
        "cursor.execute('''\n",
        "\t\tCREATE TABLE IF NOT EXISTS medications(\n",
        "            START nvarchar(20),\n",
        "            STOP nvarchar(20),\n",
        "            PATIENT nvarchar(36),\n",
        "            PAYER nvarchar(50),\n",
        "            ENCOUNTER nvarchar(50),\n",
        "            CODE int,\n",
        "            DESCRIPTION nvarchar(256),\n",
        "            BASE_COST float,\n",
        "            PAYER_COVERAGE float,\n",
        "            DISPENSES int,\n",
        "            TOTALCOST float,\n",
        "            REASONCODE float,\n",
        "            REASONDESCRIPTION nvarchar(50),\n",
        "            foreign key(PATIENT) references patients(Id) \n",
        "\t\t\t)\n",
        "               ''')\n",
        "\n",
        "cursor.execute('''\n",
        "\t\tCREATE TABLE IF NOT EXISTS observations (\n",
        "            DATE nvarchar(20),\n",
        "            PATIENT nvarchar(36),\n",
        "            ENCOUNTER nvarchar(50),\n",
        "            CODE int,\n",
        "            DESCRIPTION nvarchar(256),\n",
        "            VALUE float,\n",
        "            UNITS nvarchar(10),\n",
        "            TYPE nvarchar(10),\n",
        "            foreign key(PATIENT) references patients(Id) \n",
        "\t\t\t)\n",
        "               ''')\n",
        "\n",
        "cursor.execute('''\n",
        "\t\tCREATE TABLE IF NOT EXISTS procedures(\n",
        "            DATE nvarchar(20),\n",
        "            PATIENT nvarchar(36),\n",
        "            ENCOUNTER nvarchar(50),\n",
        "            CODE int,\n",
        "            DESCRIPTION nvarchar(50),\n",
        "            BASE_COST float,\n",
        "            REASONCODE float,\n",
        "            REASONDESCRIPTION nvarchar(256),\n",
        "            foreign key(PATIENT) references patients(Id) \n",
        "\t\t\t)\n",
        "               ''')"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "8a37c414",
      "metadata": {
        "id": "8a37c414"
      },
      "source": [
        "Die erstellten Tabellen werden mit Daten aus Dataframes eingefügt."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "70726a76",
      "metadata": {
        "id": "70726a76"
      },
      "outputs": [],
      "source": [
        "# Einfügen der Daten in die erstellten Tabellen\n",
        "patients.to_sql('patients', conn, if_exists='append', index=False)\n",
        "conditions.to_sql('conditions', conn, if_exists='append', index=False)\n",
        "medications.to_sql('medications', conn, if_exists='append', index=False)\n",
        "observations.to_sql('observations', conn, if_exists='append', index=False)\n",
        "procedures.to_sql('procedures', conn, if_exists='append', index=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "052ef0c8",
      "metadata": {
        "id": "052ef0c8"
      },
      "source": [
        "# Sternschema erstellen"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "6296687f",
      "metadata": {
        "id": "6296687f"
      },
      "source": [
        "Wir benötigen eine Faktentabelle für unser Sternschema. Die Tabellen patients, medications, observations und procedures werden als Dimensionstabelle für das Sternschema genommen. Die neue Tabelle erhalten den Namen mit einem Präfix _new und sie werden mit den Daten aus der alten Tabellen befüllt."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "c4bdf1d4",
      "metadata": {
        "id": "c4bdf1d4"
      },
      "source": [
        "## Dimensionstabellen "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "37216db9",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "37216db9",
        "outputId": "03fba133-b7e6-4867-a327-1fafb0e5171e"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7f3fc94b05e0>"
            ]
          },
          "metadata": {},
          "execution_count": 49
        }
      ],
      "source": [
        "# Dimensionstabellen\n",
        "cursor.execute('''\n",
        "\t\tCREATE TABLE IF NOT EXISTS patients_new(\n",
        "\t\t\tId nvarchar(36) primary key,\n",
        "            BIRTHDATE_YEAR Date,\n",
        "            DEATHDATE Date,\n",
        "            SSN nvarchar(50),\n",
        "            SUFFIX nvarchar(10),\n",
        "            MAIDEN nvarchar(50),\n",
        "            MARITAL nvarchar(1),\n",
        "            RACE nvarchar(10),\n",
        "            ETHNICITY nvarchar(50),\n",
        "            GENDER nvarchar(1),\n",
        "            BIRTHPLACE nvarchar(50),\n",
        "            CITY nvarchar(50),\n",
        "            STATE nvarchar(50),\n",
        "            COUNTY nvarchar(50),\n",
        "            ZIP nvarchar(4),\n",
        "            HEALTHCARE_EXPENSES float,\n",
        "            HEALTHCARE_COVERAGE float\n",
        "          \n",
        "\t\t\t)\n",
        "             ''')\n",
        "\n",
        "cursor.execute('''\n",
        "\t\tCREATE TABLE IF NOT EXISTS conditions_new (\n",
        "            CONDITIONS_ID INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            START Date,\n",
        "            STOP Date,\n",
        "            PATIENT nvarchar(36),\n",
        "            ENCOUNTER nvarchar(50),\n",
        "            DESCRIPTION nvarchar(256),\n",
        "            CODE int,\n",
        "            foreign key(PATIENT) references patients_new(Id) \n",
        "\t\t\t)\n",
        "               ''')             \n",
        "\n",
        "cursor.execute('''\n",
        "\t\tCREATE TABLE IF NOT EXISTS observations_new (\n",
        "            OBSERVATIONS_ID INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            DATE nvarchar(20),\n",
        "            PATIENT nvarchar(36),\n",
        "            ENCOUNTER nvarchar(50),\n",
        "            CODE int,\n",
        "            DESCRIPTION nvarchar(256),\n",
        "            VALUE float,\n",
        "            UNITS nvarchar(10),\n",
        "            foreign key(PATIENT) references patients_new(Id) \n",
        "\t\t\t)\n",
        "               ''')  \n",
        "\n",
        "cursor.execute('''\n",
        "\t\tCREATE TABLE IF NOT EXISTS procedures_new(\n",
        "            PROCEDURES_ID INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            DATE nvarchar(20),\n",
        "            PATIENT nvarchar(36),\n",
        "            ENCOUNTER nvarchar(50),\n",
        "            CODE int,\n",
        "            DESCRIPTION nvarchar(50),\n",
        "            BASE_COST float,\n",
        "            REASONCODE float,\n",
        "            REASONDESCRIPTION nvarchar(256),\n",
        "            foreign key(PATIENT) references patients_new(Id) \n",
        "\t\t\t)\n",
        "               ''') \n",
        "\n",
        "cursor.execute('''\n",
        "\t\tCREATE TABLE IF NOT EXISTS medications_new (\n",
        "            MEDICATIONS_ID INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "            START nvarchar(20),\n",
        "            STOP nvarchar(20),\n",
        "            PATIENT nvarchar(36),\n",
        "            PAYER nvarchar(50),\n",
        "            ENCOUNTER nvarchar(50),\n",
        "            CODE int,\n",
        "            DESCRIPTION nvarchar(256),\n",
        "            BASE_COST float,\n",
        "            PAYER_COVERAGE float,\n",
        "            DISPENSES int,\n",
        "            TOTALCOST float,\n",
        "            REASONCODE float,\n",
        "            REASONDESCRIPTION nvarchar(50),\n",
        "            foreign key(PATIENT) references patients_new(Id) \n",
        "\t\t\t)\n",
        "               ''')                        \n",
        "\n",
        "# Faktentabelle \n",
        "cursor.execute('''\n",
        "\t\tCREATE TABLE IF NOT EXISTS patient_careplans (\n",
        "            CAREPLAN_ID INTEGER PRIMARY KEY AUTOINCREMENT,\n",
        "\t\t\tPATIENT_ID nvarchar(36),\n",
        "            CONDITIONS_ID int,\n",
        "            CARE_TYPE nvarchar(20),\n",
        "            TABLE_ID int,\n",
        "            foreign key(PATIENT_ID) references patients_new(Id) \n",
        "            foreign key(CONDITIONS_ID) references conditions_new(CONDITIONS_ID)\n",
        "\t\t\t)\n",
        "             ''')             "
      ]
    },
    {
      "cell_type": "markdown",
      "id": "86dea1c1",
      "metadata": {
        "id": "86dea1c1"
      },
      "source": [
        "In dem nächsten Abschnitt werden die Daten aus den alten in die neuen Tabellen eingefügt."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "8c1376ec",
      "metadata": {
        "id": "8c1376ec"
      },
      "outputs": [],
      "source": [
        "cursor.execute('''\n",
        "    INSERT INTO patients_new(Id, BIRTHDATE_YEAR, DEATHDATE,SSN,SUFFIX,MAIDEN,MARITAL,RACE,ETHNICITY,GENDER,BIRTHPLACE,\n",
        "    CITY,STATE, COUNTY,ZIP,HEALTHCARE_EXPENSES, HEALTHCARE_COVERAGE)\n",
        "    SELECT  Id, strftime('%Y', BIRTHDATE) as BIRTHDATE_YEAR, DEATHDATE,SSN,SUFFIX,MAIDEN,MARITAL,RACE,ETHNICITY,GENDER,BIRTHPLACE,\n",
        "    CITY,STATE, COUNTY,ZIP, HEALTHCARE_EXPENSES, HEALTHCARE_COVERAGE \n",
        "    FROM patients\n",
        "    ''') \n",
        "\n",
        "           \n",
        "             \n",
        "conn.commit()\n",
        "cursor.execute(''' SELECT * FROM patients_new;''')\n",
        "records = cursor.fetchall()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "0552baad",
      "metadata": {
        "id": "0552baad"
      },
      "outputs": [],
      "source": [
        "cursor.execute('''\n",
        "    INSERT INTO conditions_new(START,STOP, PATIENT, ENCOUNTER,DESCRIPTION,CODE)\n",
        "    SELECT START,STOP, PATIENT, ENCOUNTER,DESCRIPTION,CODE\n",
        "    FROM conditions\n",
        "    ''')\n",
        "conn.commit()\n",
        "cursor.execute(''' SELECT * FROM conditions_new;''')\n",
        "records = cursor.fetchall()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "021b8ec8",
      "metadata": {
        "id": "021b8ec8"
      },
      "outputs": [],
      "source": [
        "cursor.execute('''\n",
        "    INSERT INTO observations_new(DATE, PATIENT, ENCOUNTER,CODE, DESCRIPTION, UNITS)\n",
        "    SELECT DATE, PATIENT, ENCOUNTER,CODE, DESCRIPTION, UNITS\n",
        "    FROM observations\n",
        "    ''')\n",
        "\n",
        "conn.commit()\n",
        "cursor.execute(''' SELECT * FROM observations_new;''')\n",
        "records = cursor.fetchall()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "ae32557c",
      "metadata": {
        "id": "ae32557c"
      },
      "outputs": [],
      "source": [
        "cursor.execute('''\n",
        "    INSERT INTO procedures_new(DATE, PATIENT, ENCOUNTER,CODE, DESCRIPTION, BASE_COST, REASONCODE,REASONDESCRIPTION )\n",
        "    SELECT DATE, PATIENT, ENCOUNTER,CODE, DESCRIPTION, BASE_COST, REASONCODE,REASONDESCRIPTION\n",
        "    FROM procedures\n",
        "    ''')\n",
        "\n",
        "conn.commit()\n",
        "cursor.execute(''' SELECT * FROM procedures_new;''')\n",
        "records = cursor.fetchall()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "1f2d8201",
      "metadata": {
        "id": "1f2d8201"
      },
      "outputs": [],
      "source": [
        "cursor.execute('''\n",
        "    INSERT INTO medications_new(START, STOP, PATIENT, PAYER, ENCOUNTER, CODE, DESCRIPTION, BASE_COST, PAYER_COVERAGE, DISPENSES, TOTALCOST, REASONCODE,REASONDESCRIPTION )\n",
        "    SELECT START, STOP, PATIENT, PAYER, ENCOUNTER, CODE, DESCRIPTION, BASE_COST, PAYER_COVERAGE, DISPENSES, TOTALCOST, REASONCODE,REASONDESCRIPTION \n",
        "    FROM medications\n",
        "    ''')\n",
        "conn.commit()\n",
        "cursor.execute(''' SELECT * FROM medications_new;''')\n",
        "records = cursor.fetchall()\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "9a0a8d36",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "9a0a8d36",
        "outputId": "14c4e5bd-4f00-40b4-dc44-1ae363032aa0"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "                                        Id  BIRTHDATE_YEAR DEATHDATE  \\\n",
            "0     d2061cc7-bee0-0e6c-3ac4-15c197c474e0            1956      None   \n",
            "1     073d8e80-ff90-1c8d-57e4-29bfca52c87f            1964      None   \n",
            "2     e1ff7e68-4097-9faf-514d-e4cfcfdf252e            1998      None   \n",
            "3     a0f679cc-875f-dd72-ed13-9ca863ec6cf3            2004      None   \n",
            "4     e4166a9c-f7c5-bef7-ea43-b96281a2d586            2006      None   \n",
            "...                                    ...             ...       ...   \n",
            "1014  2a41a25a-bb37-04db-4bd6-5a52474e309a            1991      None   \n",
            "1015  a8bea34f-a8d3-f5e2-93d1-5e2e52307251            1952      None   \n",
            "1016  8e1abff8-cf2e-ed15-1e93-753b6f2578ea            2002      None   \n",
            "1017  f98799f2-c4da-761d-f3ac-5fac8e8fc8bc            1979      None   \n",
            "1018  4d6c5c28-0195-8486-66bc-787fb1bc4099            1917      None   \n",
            "\n",
            "              SSN SUFFIX         MAIDEN MARITAL   RACE    ETHNICITY GENDER  \\\n",
            "0     999-51-6528   None           None       M  white  nonhispanic      M   \n",
            "1     999-90-4728   None  Williamson769       M  white  nonhispanic      F   \n",
            "2     999-82-3645   None           None    None  white  nonhispanic      F   \n",
            "3     999-93-6431   None           None    None  asian  nonhispanic      M   \n",
            "4     999-11-8697   None           None    None  black     hispanic      M   \n",
            "...           ...    ...            ...     ...    ...          ...    ...   \n",
            "1014  999-69-4089   None           None       M  white  nonhispanic      M   \n",
            "1015  999-94-5072   None   Cummerata161       M  white  nonhispanic      F   \n",
            "1016  999-24-8062   None           None    None  white  nonhispanic      F   \n",
            "1017  999-30-4844   None           None       M  asian  nonhispanic      M   \n",
            "1018  999-59-1679   None           None       S  white  nonhispanic      F   \n",
            "\n",
            "                            BIRTHPLACE              CITY          STATE  \\\n",
            "0         Worcester  Massachusetts  US         Cambridge  Massachusetts   \n",
            "1        Somerville  Massachusetts  US  North Brookfield  Massachusetts   \n",
            "2      Williamstown  Massachusetts  US         Worcester  Massachusetts   \n",
            "3     New Braintree  Massachusetts  US          Weymouth  Massachusetts   \n",
            "4            Canton  Massachusetts  US         Worcester  Massachusetts   \n",
            "...                                ...               ...            ...   \n",
            "1014     Greenfield  Massachusetts  US           Ipswich  Massachusetts   \n",
            "1015     Leominster  Massachusetts  US          Plymouth  Massachusetts   \n",
            "1016         Groton  Massachusetts  US         Westfield  Massachusetts   \n",
            "1017      Cambridge  Massachusetts  US            Boston  Massachusetts   \n",
            "1018           Lynn  Massachusetts  US            Ludlow  Massachusetts   \n",
            "\n",
            "                COUNTY     ZIP  HEALTHCARE_EXPENSES  HEALTHCARE_COVERAGE  \n",
            "0     Middlesex County  2140.0           1475230.00              4244.64  \n",
            "1     Worcester County  1535.0           1489125.00              4016.36  \n",
            "2     Worcester County  1545.0             57142.44              2103.20  \n",
            "3       Norfolk County    None            408520.00              2709.00  \n",
            "4     Worcester County  1609.0            320400.00              2970.68  \n",
            "...                ...     ...                  ...                  ...  \n",
            "1014      Essex County    None            711495.00              2508.96  \n",
            "1015   Plymouth County    None           1574295.00              4567.12  \n",
            "1016    Hampden County  1086.0             55660.00              2827.32  \n",
            "1017    Suffolk County  2115.0            845600.00              2120.60  \n",
            "1018    Hampden County    None           1525140.00             13607.51  \n",
            "\n",
            "[1019 rows x 17 columns]\n"
          ]
        }
      ],
      "source": [
        "# Ausgabe der daten aus der neuen Tablle patients_new\n",
        "print(pd.read_sql_query('''\n",
        "     SELECT *\n",
        "     FROM patients_new\n",
        "     ''', conn))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "e5bf8fed",
      "metadata": {
        "id": "e5bf8fed"
      },
      "source": [
        "## Faktentabelle"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "7acc71a7",
      "metadata": {
        "id": "7acc71a7"
      },
      "source": [
        "Mithilfe von Union und Join wird die Faktentabelle für das Datenmodell erstellt. Die Referenz zu jedem Primär-Schluss jeder Dimensionstabelle wird in die Faktentabelle gespeichert.\n",
        "Zuerst wird die Tabellen patients_new und conditions_new mithilfe von Join verbunden. Das Ergebnis wird mit Tabellen medications_new, observations_new und procedures_new getrennt voneinander verbunden. Alle Ergebnisse werden anschließend mit Unions zusammengefügt. Die Faktentabelle ist mit dem folgenden ER-Diagramm abgebildet. "
      ]
    },
    {
      "cell_type": "markdown",
      "id": "90b2aa2f",
      "metadata": {
        "id": "90b2aa2f"
      },
      "source": [
        "<img src=https://raw.githubusercontent.com/Fuenfgeld/DMA2022DataProjectB/main/Daten_schema/Sternschema.png>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "0ecb1805",
      "metadata": {
        "id": "0ecb1805"
      },
      "outputs": [],
      "source": [
        "## Faktentabelle\n",
        "cursor.execute('''\n",
        "    INSERT INTO patient_careplans(PATIENT_ID, CONDITIONS_ID, CARE_TYPE, TABLE_ID)\n",
        "    SELECT \n",
        "    patients_new.Id, CONDITIONS_ID,  'Medications' as CARE_TYPE, medications_new.MEDICATIONS_ID\n",
        "    FROM patients_new\n",
        "    INNER JOIN \n",
        "    conditions_new on patients_new.Id = conditions_new.PATIENT\n",
        "    INNER JOIN\n",
        "    medications_new ON patients_new.Id = medications_new.PATIENT\n",
        "    \n",
        "    UNION\n",
        "\n",
        "    SELECT \n",
        "    patients_new.Id, CONDITIONS_ID,  'Observations' as CARE_TYPE, observations_new.OBSERVATIONS_ID\n",
        "    FROM patients_new\n",
        "    INNER JOIN \n",
        "    conditions_new on patients_new.Id = conditions_new.PATIENT\n",
        "    INNER JOIN\n",
        "    observations_new ON patients_new.Id =  observations_new.PATIENT\n",
        "\n",
        "    UNION\n",
        "\n",
        "    SELECT \n",
        "    patients_new.Id, CONDITIONS_ID,  'Procedures' as CARE_TYPE, procedures_new.PROCEDURES_ID\n",
        "    FROM patients_new\n",
        "    INNER JOIN \n",
        "    conditions_new on patients_new.Id = conditions_new.PATIENT\n",
        "    INNER JOIN\n",
        "    procedures_new ON patients_new.Id =  procedures_new.PATIENT\n",
        "\n",
        "    ''')\n",
        "\n",
        "conn.commit()\n",
        "cursor.execute(''' SELECT * FROM patient_careplans;''')\n",
        "records = cursor.fetchall()"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "d930544c",
      "metadata": {
        "id": "d930544c"
      },
      "source": [
        "Ausgabe der Daten aus der Faktentabelle:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "ac58baf0",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ac58baf0",
        "outputId": "65d382fa-73ad-4dff-ef9d-850df65ec13b"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "      CAREPLAN_ID                            PATIENT_ID  CONDITIONS_ID  \\\n",
            "0               1  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "1               2  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "2               3  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "3               4  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "4               5  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "...           ...                                   ...            ...   \n",
            "8265         8266  fd6d5aae-f012-0e00-99bc-6668b73bf4f3             68   \n",
            "8266         8267  fd6d5aae-f012-0e00-99bc-6668b73bf4f3             68   \n",
            "8267         8268  fd6d5aae-f012-0e00-99bc-6668b73bf4f3             68   \n",
            "8268         8269  fd6d5aae-f012-0e00-99bc-6668b73bf4f3             68   \n",
            "8269         8270  fd6d5aae-f012-0e00-99bc-6668b73bf4f3             68   \n",
            "\n",
            "         CARE_TYPE  TABLE_ID  \n",
            "0     Observations      7190  \n",
            "1     Observations      7191  \n",
            "2     Observations      7192  \n",
            "3     Observations      7193  \n",
            "4     Observations      7194  \n",
            "...            ...       ...  \n",
            "8265  Observations      8872  \n",
            "8266  Observations      8873  \n",
            "8267  Observations      8874  \n",
            "8268  Observations      8875  \n",
            "8269  Observations      8876  \n",
            "\n",
            "[8270 rows x 5 columns]\n"
          ]
        }
      ],
      "source": [
        "print(pd.read_sql_query('''\n",
        "     SELECT *\n",
        "     FROM patient_careplans\n",
        "     ''', conn))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "80454c4c",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "80454c4c",
        "outputId": "3f1bd4f0-80d8-45e3-ecef-aac2dc0e29c5"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "   CAREPLAN_ID                            PATIENT_ID  CONDITIONS_ID  \\\n",
            "0            1  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "1            2  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "2            3  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "3            4  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "4            5  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "5            6  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "6            7  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "7            8  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "8            9  009121bf-a672-8942-443e-85e18a33f766             51   \n",
            "\n",
            "      CARE_TYPE  TABLE_ID  \n",
            "0  Observations      7190  \n",
            "1  Observations      7191  \n",
            "2  Observations      7192  \n",
            "3  Observations      7193  \n",
            "4  Observations      7194  \n",
            "5  Observations      7195  \n",
            "6  Observations      7196  \n",
            "7  Observations      7197  \n",
            "8  Observations      7198  \n"
          ]
        }
      ],
      "source": [
        "print(pd.read_sql_query('''\n",
        "     SELECT *\n",
        "     FROM patient_careplans\n",
        "     WHERE CAREPLAN_ID<10\n",
        "     ''', conn))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "c0db7533",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "c0db7533",
        "outputId": "132119eb-97cd-43a1-c829-9f021c48ff51"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "      CARE_TYPE\n",
            "0   Medications\n",
            "1  Observations\n",
            "2    Procedures\n"
          ]
        }
      ],
      "source": [
        "print(pd.read_sql_query('''\n",
        "     SELECT CARE_TYPE\n",
        "     FROM patient_careplans\n",
        "     GROUP BY CARE_TYPE\n",
        "     ''', conn))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "3285801a",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3285801a",
        "outputId": "39847ead-4235-45ea-a71d-71054502d012"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "                               PATIENT_ID    CARE_TYPE\n",
            "0    02db7f22-8617-0cf7-fa10-d820d596a81a  Medications\n",
            "1    02db7f22-8617-0cf7-fa10-d820d596a81a  Medications\n",
            "2    02db7f22-8617-0cf7-fa10-d820d596a81a  Medications\n",
            "3    02db7f22-8617-0cf7-fa10-d820d596a81a  Medications\n",
            "4    037404e1-0c87-534d-0fe2-e21ef20640f6  Medications\n",
            "..                                    ...          ...\n",
            "798  f4b7d173-38fd-e173-9242-aca2651c6b10  Medications\n",
            "799  f5a6e3b3-781d-d800-d4c3-1782e39258e8  Medications\n",
            "800  f5a6e3b3-781d-d800-d4c3-1782e39258e8  Medications\n",
            "801  f5a6e3b3-781d-d800-d4c3-1782e39258e8  Medications\n",
            "802  f5a6e3b3-781d-d800-d4c3-1782e39258e8  Medications\n",
            "\n",
            "[803 rows x 2 columns]\n"
          ]
        }
      ],
      "source": [
        "print(pd.read_sql_query('''\n",
        "     SELECT PATIENT_ID, CARE_TYPE\n",
        "     FROM patient_careplans\n",
        "     WHERE CARE_TYPE = 'Medications'\n",
        "     ''', conn))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "Zke1n0YUvHcv",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Zke1n0YUvHcv",
        "outputId": "637c92d7-99dd-4f53-e0fb-66a01e4502f1"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "                 name\n",
            "0          conditions\n",
            "1      conditions_new\n",
            "2         medications\n",
            "3     medications_new\n",
            "4        observations\n",
            "5    observations_new\n",
            "6   patient_careplans\n",
            "7            patients\n",
            "8        patients_new\n",
            "9          procedures\n",
            "10     procedures_new\n",
            "11    sqlite_sequence\n"
          ]
        }
      ],
      "source": [
        "print(pd.read_sql_query('''\n",
        "SELECT name FROM sqlite_master\n",
        "WHERE type='table'\n",
        "ORDER BY name;\n",
        "     ''', conn))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "ac2eb56a",
      "metadata": {
        "id": "ac2eb56a"
      },
      "outputs": [],
      "source": [
        "conn.close()"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [],
      "name": "Transform.ipynb",
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "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.8.8"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}