assignment3.1.py.ipynb

{
    "nbformat_minor": 1, 
    "cells": [
        {
            "source": "# Assignment 3\n\nWelcome to Assignment 3. This will be even more fun. Now we will calculate statistical measures on the test data you have created.\n\nYOU ARE NOT ALLOWED TO USE ANY OTHER 3RD PARTY LIBRARIES LIKE PANDAS. PLEASE ONLY MODIFY CONTENT INSIDE THE FUNCTION SKELETONS\nPlease read why: https://www.coursera.org/learn/exploring-visualizing-iot-data/discussions/weeks/3/threads/skjCbNgeEeapeQ5W6suLkA\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.\nPlease also make sure to only implement the function bodies and DON'T add any additional code outside functions since this might confuse the autograder.\n\nSo the function below is used to make it easy for you to create a data frame from a cloudant data frame using the so called \"DataSource\" which is some sort of a plugin which allows ApacheSpark to use different data sources.\n", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "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. \n\nLet'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. You can use this skeleton for all subsequent functions. Everything can be implemented using SQL only if you like.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 1, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Waiting for a Spark session to start...\nSpark Initialization Done! ApplicationId = app-20190218071915-0001\nKERNEL_ID = e46e0e48-9b47-4b6d-8dd9-749c327b943c\n"
                }
            ], 
            "source": "def minTemperature(df,spark):\n    return spark.sql(\"SELECT min(temperature) as mintemp from washing\").first().mintemp"
        }, 
        {
            "source": "Please now do the same for the mean of the temperature", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 2, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "def meanHardness(df,spark):\n    return spark.sql(\"SELECT mean(hardness) as meanhard from washing\").first().meanhard"
        }, 
        {
            "execution_count": 3, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "def meanTemperature(df,spark):\n    return spark.sql(\"SELECT mean(temperature) as meantemp from washing\").first().meantemp"
        }, 
        {
            "source": "Please now do the same for the maximum of the temperature", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 19, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "def maxTemperature(df,spark):\n    return spark.sql(\"SELECT max(temperature) as maxtemp from washing\").first().maxtemp"
        }, 
        {
            "source": "Please now do the same for the standard deviation of the temperature", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 21, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "def sdTemperature(df,spark):\n    return spark.sql(\"SELECT std(temperature) as stdtemp from washing\").first().stdtemp"
        }, 
        {
            "execution_count": null, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": ""
        }, 
        {
            "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 position 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": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 31, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "def skewTemperature(df,spark):    \n    return spark.sql(\"\"\"\nSELECT \n    (\n        1/count(temperature)\n    ) *\n    SUM (\n        POWER(temperature -%s,3)/POWER(%s,3)\n    )\n\nas skewtemp from washing\n                    \"\"\" %(meanTemperature(df,spark),sdTemperature(df,spark))).first().skewtemp"
        }, 
        {
            "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": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 33, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "def kurtosisTemperature(df,spark):    \n     return spark.sql(\"\"\"\nSELECT \n    (\n        1/count(temperature)\n    ) *\n    SUM (\n        POWER(temperature -%s,4)/POWER(%s,4)\n    )\n\nas kurttemp from washing\n                    \"\"\" %(meanTemperature(df,spark),sdTemperature(df,spark))).first().kurttemp"
        }, 
        {
            "source": "Just a hint. This can be solved easily using SQL as well, but as shown in the lecture also using RDDs.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 38, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [], 
            "source": "def correlationTemperatureHardness(df,spark):\n    return spark.sql(\"\"\"\nSELECT \n    ((\n        1/count(temperature)\n    ) *\n    SUM (\n        (temperature - %s) * (hardness- %s)\n    )) /(variance (temperature) * variance (hardness))\n\nas corrtemphard from washing\n                    \"\"\" %(meanTemperature(df,spark),meanHardness(df,spark))).first().corrtemphard"
        }, 
        {
            "source": "### PLEASE DON'T REMOVE THIS BLOCK - THE FOLLOWING CODE IS NOT GRADED\n#axx\n### PLEASE DON'T REMOVE THIS BLOCK - THE FOLLOWING CODE IS NOT GRADED", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "source": "Now it is time to connect to the object store and read a PARQUET file and create a dataframe out of it. We've created that data for you already. Using SparkSQL you can handle it like a database.", 
            "cell_type": "markdown", 
            "metadata": {}
        }, 
        {
            "execution_count": 8, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Collecting ibmos2spark\n  Downloading https://files.pythonhosted.org/packages/c6/81/1edb24382edef1ca636e87972b2da286b8271a586c728a21f916d3cd76cd/ibmos2spark-1.0.1-py2.py3-none-any.whl\n\u001b[31mpyspark 2.3.0 requires py4j==0.10.6, which is not installed.\u001b[0m\n\u001b[31mtensorflow 1.3.0 requires tensorflow-tensorboard<0.2.0,>=0.1.0, which is not installed.\u001b[0m\nInstalling collected packages: ibmos2spark\nSuccessfully installed ibmos2spark-1.0.1\n"
                }
            ], 
            "source": "!pip install ibmos2spark"
        }, 
        {
            "execution_count": 1, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "output_type": "stream", 
                    "name": "stdout", 
                    "text": "Waiting for a Spark session to start...\nSpark Initialization Done! ApplicationId = app-20190218081132-0002\nKERNEL_ID = e46e0e48-9b47-4b6d-8dd9-749c327b943c\n+--------------------+--------------------+-----+--------+----------+---------+--------+-----+-----------+-------------+-------+\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+--------------------+--------------------+-----+--------+----------+---------+--------+-----+-----------+-------------+-------+\nonly showing top 20 rows\n\n"
                }
            ], 
            "source": "# The code was removed by Watson Studio for sharing."
        }, 
        {
            "execution_count": 16, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 16, 
                    "metadata": {}, 
                    "data": {
                        "text/plain": "80"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "minTemperature(df,spark)"
        }, 
        {
            "execution_count": 18, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 18, 
                    "metadata": {}, 
                    "data": {
                        "text/plain": "90.03800298062593"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "meanTemperature(df,spark)"
        }, 
        {
            "execution_count": 20, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 20, 
                    "metadata": {}, 
                    "data": {
                        "text/plain": "100"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "maxTemperature(df,spark)"
        }, 
        {
            "execution_count": 22, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 22, 
                    "metadata": {}, 
                    "data": {
                        "text/plain": "6.1007610586219725"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "sdTemperature(df,spark)"
        }, 
        {
            "execution_count": 32, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 32, 
                    "metadata": {}, 
                    "data": {
                        "text/plain": "0.010398374583336334"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "skewTemperature(df,spark)"
        }, 
        {
            "execution_count": 34, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 34, 
                    "metadata": {}, 
                    "data": {
                        "text/plain": "1.773427150876985"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "kurtosisTemperature(df,spark)"
        }, 
        {
            "execution_count": 7, 
            "cell_type": "code", 
            "metadata": {}, 
            "outputs": [
                {
                    "execution_count": 7, 
                    "metadata": {}, 
                    "data": {
                        "text/plain": "0.00013362503098007886"
                    }, 
                    "output_type": "execute_result"
                }
            ], 
            "source": "correlationTemperatureHardness(df,spark)"
        }, 
        {
            "source": "Congratulations, you are done, please download this notebook as python file using the export function and submit is to the gader using the filename \"assignment3.1.py\"", 
            "cell_type": "markdown", 
            "metadata": {}
        }
    ], 
    "metadata": {
        "kernelspec": {
            "display_name": "Python 3.5 with Spark", 
            "name": "python3", 
            "language": "python3"
        }, 
        "language_info": {
            "mimetype": "text/x-python", 
            "nbconvert_exporter": "python", 
            "version": "3.5.4", 
            "name": "python", 
            "file_extension": ".py", 
            "pygments_lexer": "ipython3", 
            "codemirror_mode": {
                "version": 3, 
                "name": "ipython"
            }
        }
    }, 
    "nbformat": 4
}