assignment3_1.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "kernelspec": {
      "name": "python36",
      "display_name": "Python 3.6 with Spark",
      "language": "python3"
    },
    "language_info": {
      "mimetype": "text/x-python",
      "nbconvert_exporter": "python",
      "name": "python",
      "pygments_lexer": "ipython3",
      "version": "3.6.8",
      "file_extension": ".py",
      "codemirror_mode": {
        "version": 3,
        "name": "ipython"
      }
    },
    "colab": {
      "name": "assignment3.1.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "accelerator": "GPU"
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Blackman9t/Advanced-Data-Science/blob/master/assignment3_1.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "H-D3tb0j66X5",
        "colab_type": "text"
      },
      "source": [
        "# Assignment 3\n",
        "\n",
        "Welcome to Assignment 3. This will be even more fun. Now we will calculate statistical measures. \n",
        "\n",
        "## You only have to pass 4 out of 7 functions\n",
        "\n",
        "Just make sure you hit the play button on each cell from top to down. There are seven functions you have to implement. Please also make sure than on each change on a function you hit the play button again on the corresponding cell to make it available to the rest of this notebook."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2sCrYZwN66X8",
        "colab_type": "text"
      },
      "source": [
        "All functions can be implemented using DataFrames, ApacheSparkSQL or RDDs. We are only interested in the result. You are given the reference to the data frame in the \"df\" parameter and in case you want to use SQL just use the \"spark\" parameter which is a reference to the global SparkSession object. Finally if you want to use RDDs just use \"df.rdd\" for obtaining a reference to the underlying RDD object. But we discurage using RDD at this point in time.\n",
        "\n",
        "Let's start with the first function. Please calculate the minimal temperature for the test data set you have created. We've provided a little skeleton for you in case you want to use SQL. Everything can be implemented using SQL only if you like."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BKoI9DfmDx9B",
        "colab_type": "code",
        "outputId": "9c17c116-0cc0-4ef2-9e56-7f95db7d7d9c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "# Let's point Colaboratory to our Google Drive\n",
        "\n",
        "from google.colab import drive\n",
        "drive.mount('/content/gdrive')"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Drive already mounted at /content/gdrive; to attempt to forcibly remount, call drive.mount(\"/content/gdrive\", force_remount=True).\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EM0DNoaG66X9",
        "colab_type": "text"
      },
      "source": [
        "Now it is time to grab a PARQUET file and create a dataframe out of it. Using SparkSQL you can handle it like a database. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "W-2UV3EF66X-",
        "colab_type": "code",
        "outputId": "f3eea1f5-f1ed-4623-b419-ee8996df114e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 353
        }
      },
      "source": [
        "!wget https://github.com/IBM/coursera/blob/master/coursera_ds/washing.parquet?raw=true\n",
        "!mv washing.parquet?raw=true washing.parquet"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2020-01-14 14:28:02--  https://github.com/IBM/coursera/blob/master/coursera_ds/washing.parquet?raw=true\n",
            "Resolving github.com (github.com)... 140.82.118.4\n",
            "Connecting to github.com (github.com)|140.82.118.4|:443... connected.\n",
            "HTTP request sent, awaiting response... 302 Found\n",
            "Location: https://github.com/IBM/coursera/raw/master/coursera_ds/washing.parquet [following]\n",
            "--2020-01-14 14:28:03--  https://github.com/IBM/coursera/raw/master/coursera_ds/washing.parquet\n",
            "Reusing existing connection to github.com:443.\n",
            "HTTP request sent, awaiting response... 302 Found\n",
            "Location: https://raw.githubusercontent.com/IBM/coursera/master/coursera_ds/washing.parquet [following]\n",
            "--2020-01-14 14:28:03--  https://raw.githubusercontent.com/IBM/coursera/master/coursera_ds/washing.parquet\n",
            "Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 151.101.0.133, 151.101.64.133, 151.101.128.133, ...\n",
            "Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|151.101.0.133|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 112048 (109K) [application/octet-stream]\n",
            "Saving to: ‘washing.parquet?raw=true’\n",
            "\n",
            "washing.parquet?raw 100%[===================>] 109.42K  --.-KB/s    in 0.01s   \n",
            "\n",
            "2020-01-14 14:28:03 (10.4 MB/s) - ‘washing.parquet?raw=true’ saved [112048/112048]\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Urbt5rF7CCrg",
        "colab_type": "text"
      },
      "source": [
        "### Install spark-related dependencies in Colab"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KTSAyrvx76QC",
        "colab_type": "code",
        "outputId": "5129ae3e-475a-43b7-ba3a-32cd99fc0423",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 50
        }
      },
      "source": [
        "!apt-get install openjdk-8-jdk-headless -qq > /dev/null\n",
        "!wget -q http://apache.osuosl.org/spark/spark-2.4.4/spark-2.4.4-bin-hadoop2.7.tgz\n",
        "!tar xf spark-2.4.4-bin-hadoop2.7.tgz\n",
        "\n",
        "!pip install -q findspark\n",
        "!pip install pyspark\n",
        "# Set up required environment variables\n",
        "\n",
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = \"/content/spark-2.4.4-bin-hadoop2.7\""
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Requirement already satisfied: pyspark in /usr/local/lib/python3.6/dist-packages (2.4.4)\n",
            "Requirement already satisfied: py4j==0.10.7 in /usr/local/lib/python3.6/dist-packages (from pyspark) (0.10.7)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "u7gRxStkCND9",
        "colab_type": "text"
      },
      "source": [
        "### Next, we set up a SparkContext connection"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HlUi9rEc_yHA",
        "colab_type": "code",
        "outputId": "3df99996-8568-4b9a-a367-98eb168787a9",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "from pyspark import SparkConf, SparkContext\n",
        "try:\n",
        "    conf = SparkConf().setMaster(\"local\").setAppName(\"My App\")\n",
        "    sc = SparkContext(conf = conf)\n",
        "    print('SparkContext Initialised Successfully!')\n",
        "except Exception as e:\n",
        "    print(e)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Cannot run multiple SparkContexts at once; existing SparkContext(app=My App, master=local) created by __init__ at <ipython-input-23-adef17ed6060>:3 \n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OzkdoX4SCWDz",
        "colab_type": "text"
      },
      "source": [
        "**Let's view the connection**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "L5oJOk0oAwwp",
        "colab_type": "code",
        "outputId": "f2a59df5-4f8e-4c5d-eb75-f495d3c394b2",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 191
        }
      },
      "source": [
        "sc"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "        <div>\n",
              "            <p><b>SparkContext</b></p>\n",
              "\n",
              "            <p><a href=\"http://ce8976e66c76:4040\">Spark UI</a></p>\n",
              "\n",
              "            <dl>\n",
              "              <dt>Version</dt>\n",
              "                <dd><code>v2.4.4</code></dd>\n",
              "              <dt>Master</dt>\n",
              "                <dd><code>local</code></dd>\n",
              "              <dt>AppName</dt>\n",
              "                <dd><code>My App</code></dd>\n",
              "            </dl>\n",
              "        </div>\n",
              "        "
            ],
            "text/plain": [
              "<SparkContext master=local appName=My App>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 187
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mTCeEWX7CbKj",
        "colab_type": "text"
      },
      "source": [
        "### Next we set up a Spark Session named spark"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7XwuF28ZAwqN",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName('My App').getOrCreate()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bEXYbJgwBUPN",
        "colab_type": "code",
        "outputId": "b216445c-3d9f-4053-a515-66de7cbc00df",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 214
        }
      },
      "source": [
        "spark"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "            <div>\n",
              "                <p><b>SparkSession - in-memory</b></p>\n",
              "                \n",
              "        <div>\n",
              "            <p><b>SparkContext</b></p>\n",
              "\n",
              "            <p><a href=\"http://ce8976e66c76:4040\">Spark UI</a></p>\n",
              "\n",
              "            <dl>\n",
              "              <dt>Version</dt>\n",
              "                <dd><code>v2.4.4</code></dd>\n",
              "              <dt>Master</dt>\n",
              "                <dd><code>local</code></dd>\n",
              "              <dt>AppName</dt>\n",
              "                <dd><code>My App</code></dd>\n",
              "            </dl>\n",
              "        </div>\n",
              "        \n",
              "            </div>\n",
              "        "
            ],
            "text/plain": [
              "<pyspark.sql.session.SparkSession at 0x7f5e0a7f6cc0>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 189
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UiUke-D2CjMx",
        "colab_type": "text"
      },
      "source": [
        "### Let's now read the washing df into a Spark DataFrame so we can view it"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HkuxQnNk66YB",
        "colab_type": "code",
        "outputId": "bd6a9711-affa-4502-b0ef-88b7d6f0f7e6",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 454
        }
      },
      "source": [
        "df = spark.read.parquet('washing.parquet')\n",
        "df.createOrReplaceTempView('washing')\n",
        "df.show()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------------+--------------------+-----+--------+----------+---------+--------+-----+-----------+-------------+-------+\n",
            "|                 _id|                _rev|count|flowrate|fluidlevel|frequency|hardness|speed|temperature|           ts|voltage|\n",
            "+--------------------+--------------------+-----+--------+----------+---------+--------+-----+-----------+-------------+-------+\n",
            "|0d86485d0f88d1f9d...|1-57940679fb8a713...|    4|      11|acceptable|     null|      77| null|        100|1547808723923|   null|\n",
            "|0d86485d0f88d1f9d...|1-15ff3a0b304d789...|    2|    null|      null|     null|    null| 1046|       null|1547808729917|   null|\n",
            "|0d86485d0f88d1f9d...|1-97c2742b68c7b07...|    4|    null|      null|       71|    null| null|       null|1547808731918|    236|\n",
            "|0d86485d0f88d1f9d...|1-eefb903dbe45746...|   19|      11|acceptable|     null|      75| null|         86|1547808738999|   null|\n",
            "|0d86485d0f88d1f9d...|1-5f68b4c72813c25...|    7|    null|      null|       75|    null| null|       null|1547808740927|    235|\n",
            "|0d86485d0f88d1f9d...|1-cd4b6c57ddbe77e...|    5|    null|      null|     null|    null| 1014|       null|1547808744923|   null|\n",
            "|0d86485d0f88d1f9d...|1-a35b25b5bf43aaf...|   32|      11|acceptable|     null|      73| null|         84|1547808752028|   null|\n",
            "|0d86485d0f88d1f9d...|1-b717f7289a8476d...|   48|      11|acceptable|     null|      79| null|         84|1547808768065|   null|\n",
            "|0d86485d0f88d1f9d...|1-c2f1f8fcf178b2f...|   18|    null|      null|       73|    null| null|       null|1547808773944|    228|\n",
            "|0d86485d0f88d1f9d...|1-15033dd9eebb4a8...|   59|      11|acceptable|     null|      72| null|         96|1547808779093|   null|\n",
            "|0d86485d0f88d1f9d...|1-753dae825f9a6c2...|   62|      11|acceptable|     null|      73| null|         88|1547808782113|   null|\n",
            "|0d86485d0f88d1f9d...|1-b168089f44f03f0...|   13|    null|      null|     null|    null| 1097|       null|1547808784940|   null|\n",
            "|0d86485d0f88d1f9d...|1-403b687c6be0dea...|   23|    null|      null|       80|    null| null|       null|1547808788955|    236|\n",
            "|0d86485d0f88d1f9d...|1-195551e0455a24b...|   72|      11|acceptable|     null|      77| null|         87|1547808792134|   null|\n",
            "|0d86485d0f88d1f9d...|1-060a39fc6c2ddee...|   26|    null|      null|       62|    null| null|       null|1547808797959|    233|\n",
            "|0d86485d0f88d1f9d...|1-2234514bffee465...|   27|    null|      null|       61|    null| null|       null|1547808800960|    226|\n",
            "|0d86485d0f88d1f9d...|1-4265898bb401db0...|   82|      11|acceptable|     null|      79| null|         96|1547808802154|   null|\n",
            "|0d86485d0f88d1f9d...|1-2fbf7ca9a0425a0...|   94|      11|acceptable|     null|      73| null|         90|1547808814186|   null|\n",
            "|0d86485d0f88d1f9d...|1-203c0ee6d7fbd21...|   97|      11|acceptable|     null|      77| null|         88|1547808817190|   null|\n",
            "|0d86485d0f88d1f9d...|1-47e1965db94fcab...|  104|      11|acceptable|     null|      75| null|         80|1547808824198|   null|\n",
            "+--------------------+--------------------+-----+--------+----------+---------+--------+-----+-----------+-------------+-------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Czjw8OM1Cxcx",
        "colab_type": "text"
      },
      "source": [
        "**Let's confirm the washing table is in the spark session tables catalog**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WehSaHsD66YD",
        "colab_type": "code",
        "outputId": "aa79d68d-6ec7-4fa3-c10b-af9d8fc95acc",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "spark.catalog.listTables()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[Table(name='washing', database=None, description=None, tableType='TEMPORARY', isTemporary=True)]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 191
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YvaCtPI366YF",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def minTemperature(df,spark):\n",
        "    #TODO Please enter your code here, you are not required to use the template code below\n",
        "    #some reference: https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrame\n",
        "    return spark.sql(\"SELECT min(temperature) as mintemp from washing\").first().mintemp"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Pp0X8Zbj66YH",
        "colab_type": "text"
      },
      "source": [
        "Please now do the same for the mean of the temperature"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QTpW1Fq466YH",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def meanTemperature(df,spark):\n",
        "    #TODO Please enter your code here, you are not required to use the template code below\n",
        "    #some reference: https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrame\n",
        "    return spark.sql(\"SELECT mean(temperature) as meantemp from washing\").first().meantemp"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UTUECMmr66YK",
        "colab_type": "text"
      },
      "source": [
        "Please now do the same for the maximum of the temperature"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Tq9612pa66YL",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def maxTemperature(df,spark):\n",
        "    #TODO Please enter your code here, you are not required to use the template code below\n",
        "    #some reference: https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrame\n",
        "    return spark.sql(\"SELECT max(temperature) as maxtemp from washing\").first().maxtemp"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UIYnZrQ366YN",
        "colab_type": "text"
      },
      "source": [
        "Please now do the same for the standard deviation of the temperature"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fLLeLPlw66YN",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def sdTemperature(df,spark):\n",
        "    from pyspark.sql.functions import stddev\n",
        "    #TODO Please enter your code here, you are not required to use the template code below\n",
        "    #some reference: https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrame\n",
        "    #https://spark.apache.org/docs/2.3.0/api/sql/\n",
        "\n",
        "    # Let's fill the NAN values in the temperature column to 0\n",
        "    stats = df.select(stddev(col('temperature')).alias('std')).collect()\n",
        "    std = stats[0]['std']\n",
        "\n",
        "    #return spark.sql(\"SELECT STDEV([temperature]) as sdtemp from washing\").first().sdtemp\n",
        "    return std"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GAmp2MI166YP",
        "colab_type": "text"
      },
      "source": [
        "Please now do the same for the skew of the temperature. Since the SQL statement for this is a bit more complicated we've provided a skeleton for you. You have to insert custom code at four positions in order to make the function work. Alternatively you can also remove everything and implement if on your own. Note that we are making use of two previously defined functions, so please make sure they are correct. Also note that we are making use of python's string formatting capabilitis where the results of the two function calls to \"meanTemperature\" and \"sdTemperature\" are inserted at the \"%s\" symbols in the SQL string."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3mbh83UVXfJl",
        "colab_type": "code",
        "outputId": "eed74f0e-5a6e-40c0-f25d-e66392a12ce1",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "df.count()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "2058"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 196
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0rJHcySv66YQ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def skewTemperature(df,spark):\n",
        "    return spark.sql(\"\"\"\n",
        "SELECT \n",
        "    (\n",
        "        1/2058.0\n",
        "    ) *\n",
        "    SUM (\n",
        "        POWER(washing.temperature - %s,3)/POWER(%s,3)\n",
        "    )\n",
        "\n",
        "as sktemperature from washing\n",
        "                    \"\"\" %(meanTemperature(df,spark),sdTemperature(df,spark))).first().sktemperature"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WlpF-Tg2WjUt",
        "colab_type": "code",
        "outputId": "dec34eda-9a65-4035-8d43-5475f4547dc6",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "skew_temperature = skewTemperature(df,spark)\n",
        "print(skew_temperature)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "0.006781944683746945\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pUF1uFBA66YR",
        "colab_type": "text"
      },
      "source": [
        "Kurtosis is the 4th statistical moment, so if you are smart you can make use of the code for skew which is the 3rd statistical moment. Actually only two things are different."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "h1EXFWCh66YS",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def kurtosisTemperature(df,spark):    \n",
        "        return spark.sql(\"\"\"\n",
        "SELECT \n",
        "    (\n",
        "        1/2058.0\n",
        "    ) *\n",
        "    SUM (\n",
        "        POWER(washing.temperature-%s,4)/POWER(%s,4)\n",
        "    )\n",
        "as ktemperature from washing\n",
        "                    \"\"\" %(meanTemperature(df,spark),sdTemperature(df,spark))).first().ktemperature\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Hxcmnf8m66YU",
        "colab_type": "text"
      },
      "source": [
        "Just a hint. This can be solved easily using SQL as well, but as shown in the lecture also using RDDs."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DMnH-uSa66YU",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def correlationTemperatureHardness(df,spark):\n",
        "    #TODO Please enter your code here, you are not required to use the template code below\n",
        "    #some reference: https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrame\n",
        "    #https://spark.apache.org/docs/2.3.0/api/sql/\n",
        "    return spark.sql(\"SELECT corr(temperature,hardness) as temperaturehardness from washing\").first().temperaturehardness"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "R5bHtzqU66YY",
        "colab_type": "text"
      },
      "source": [
        "Now let's test the functions you've implemented"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yFo7ARy-66YZ",
        "colab_type": "code",
        "outputId": "af44b4f3-b8c4-45dc-e692-9d7f38ada933",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "min_temperature = minTemperature(df,spark)\n",
        "print(min_temperature)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "80\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PhdR63Yg66Yb",
        "colab_type": "code",
        "outputId": "df46f682-ec03-4946-d14b-a90ff0b11037",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "mean_temperature = meanTemperature(df,spark)\n",
        "print(mean_temperature)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "90.03800298062593\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PxQuudBR66Yi",
        "colab_type": "code",
        "outputId": "abf23c4a-0ed5-42ed-d316-071cb4413af6",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "max_temperature = maxTemperature(df,spark)\n",
        "print(max_temperature)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "100\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ea6tSWPz66Yj",
        "colab_type": "code",
        "outputId": "2b1af39b-2297-467e-c93b-fccbd1626a82",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "sd_temperature = sdTemperature(df,spark)\n",
        "print(sd_temperature)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "6.1007610586219725\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gVk-LxMBOOnN",
        "colab_type": "code",
        "outputId": "ad6d040c-6579-448d-f696-ae9157ad2fb0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "# To check the standard deviation of the temperature column using pandas dataframe\n",
        "pdf = df.toPandas()\n",
        "pdf.temperature.fillna(0, inplace=True)\n",
        "print(pdf.loc[:,\"temperature\"].std())"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "43.17809317646435\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZnTiTcNC66Yl",
        "colab_type": "code",
        "outputId": "661f7819-dac3-4cd7-e4f1-18667d2d4f33",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "skew_temperature = skewTemperature(df,spark)\n",
        "print(skew_temperature)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "0.006781944683746945\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lliSiLyh66Yn",
        "colab_type": "code",
        "outputId": "78bdb6c4-6c04-4235-c3f2-1e4a07ef5998",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "kurtosis_temperature = kurtosisTemperature(df,spark)\n",
        "print(kurtosis_temperature)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "1.1566504689277801\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Dbw_yN2k66Yo",
        "colab_type": "code",
        "outputId": "e6f21e22-8253-437f-c704-b670d54f4da2",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "correlation_temperature = correlationTemperatureHardness(df,spark)\n",
        "print(correlation_temperature)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "0.017754069047296324\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P4CPlbUA66Yr",
        "colab_type": "text"
      },
      "source": [
        "Congratulations, you are done, please submit this notebook to the grader. \n",
        "We have to install a little library in order to submit to coursera first.\n",
        "\n",
        "Then, please provide your email address and obtain a submission token on the grader’s submission page in coursera, then execute the subsequent cells\n",
        "\n",
        "### Note: We've changed the grader in this assignment and will do so for the others soon since it gives less errors\n",
        "This means you can directly submit your solutions from this notebook"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WjFm60h366Yu",
        "colab_type": "code",
        "outputId": "0a8be0d1-6b2c-46af-834f-843f8ed73b07",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 202
        }
      },
      "source": [
        "!rm -f rklib.py\n",
        "!wget https://raw.githubusercontent.com/IBM/coursera/master/rklib.py"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2020-01-14 14:29:19--  https://raw.githubusercontent.com/IBM/coursera/master/rklib.py\n",
            "Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 151.101.0.133, 151.101.64.133, 151.101.128.133, ...\n",
            "Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|151.101.0.133|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 2540 (2.5K) [text/plain]\n",
            "Saving to: ‘rklib.py’\n",
            "\n",
            "\rrklib.py              0%[                    ]       0  --.-KB/s               \rrklib.py            100%[===================>]   2.48K  --.-KB/s    in 0s      \n",
            "\n",
            "2020-01-14 14:29:19 (82.2 MB/s) - ‘rklib.py’ saved [2540/2540]\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dSUXBcuN66Yx",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "from rklib import submitAll\n",
        "import json\n",
        "\n",
        "key = \"Suy4biHNEeimFQ479R3GjA\"\n",
        "email = 'sisokels@gmail.com'\n",
        "token = 'PgHloDdn6XIpW8PB'"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jzNZA3HW66Yz",
        "colab_type": "code",
        "outputId": "6df0cd2b-e821-4131-ea6c-15f01e433772",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 84
        }
      },
      "source": [
        "parts_data = {}\n",
        "parts_data[\"FWMEL\"] = json.dumps(min_temperature)\n",
        "parts_data[\"3n3TK\"] = json.dumps(mean_temperature)\n",
        "parts_data[\"KD3By\"] = json.dumps(max_temperature)\n",
        "parts_data[\"06Zie\"] = json.dumps(sd_temperature)\n",
        "parts_data[\"Qc8bI\"] = json.dumps(skew_temperature)\n",
        "parts_data[\"LoqQi\"] = json.dumps(kurtosis_temperature)\n",
        "parts_data[\"ehNGV\"] = json.dumps(correlation_temperature)\n",
        "\n",
        "\n",
        "\n",
        "submitAll(email, token, key, parts_data)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Submission successful, please check on the coursera grader page for the status\n",
            "-------------------------\n",
            "{\"elements\":[{\"itemId\":\"TzU1P\",\"id\":\"sUpST4RAEeawAApvKZgcCQ~TzU1P~QSFoGTbaEeqXJhJz7JxdzQ\",\"courseId\":\"sUpST4RAEeawAApvKZgcCQ\"}],\"paging\":{},\"linked\":{}}\n",
            "-------------------------\n"
          ],
          "name": "stdout"
        }
      ]
    }
  ]
}