Spark DataFrames Project Exercise.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Spark DataFrames Project Exercise "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's get some quick practice with your new Spark DataFrame skills, you will be asked some basic questions about some stock market data, in this case Walmart Stock from the years 2012-2017. This exercise will just ask a bunch of questions, unlike the future machine learning exercises, which will be a little looser and be in the form of \"Consulting Projects\", but more on that later!\n",
    "\n",
    "For now, just answer the questions and complete the tasks below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Use the walmart_stock.csv file to Answer and complete the  tasks below!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Start a simple Spark Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [
    {
     "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://DESKTOP-BJS1Q67:4040\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.5.0</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>exercise</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x173b4d12bd0>"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import format_number, max, min, corr, year, month\n",
    "\n",
    "spark = SparkSession.builder.appName('exercise').getOrCreate()\n",
    "spark"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Load the Walmart Stock CSV File, have Spark infer the data types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read.csv('walmart_stock.csv',inferSchema=True,header=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What are the column names?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close']"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close']"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What does the Schema look like?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Date: date (nullable = true)\n",
      " |-- Open: double (nullable = true)\n",
      " |-- High: double (nullable = true)\n",
      " |-- Low: double (nullable = true)\n",
      " |-- Close: double (nullable = true)\n",
      " |-- Volume: integer (nullable = true)\n",
      " |-- Adj Close: double (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Date: timestamp (nullable = true)\n",
      " |-- Open: double (nullable = true)\n",
      " |-- High: double (nullable = true)\n",
      " |-- Low: double (nullable = true)\n",
      " |-- Close: double (nullable = true)\n",
      " |-- Volume: integer (nullable = true)\n",
      " |-- Adj Close: double (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Print out the first 5 columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Row(Date=datetime.date(2012, 1, 3), Open=59.970001, High=61.060001, Low=59.869999, Close=60.330002, Volume=12668800, Adj Close=52.619234999999996)\n",
      "\n",
      "Row(Date=datetime.date(2012, 1, 4), Open=60.209998999999996, High=60.349998, Low=59.470001, Close=59.709998999999996, Volume=9593300, Adj Close=52.078475)\n",
      "\n",
      "Row(Date=datetime.date(2012, 1, 5), Open=59.349998, High=59.619999, Low=58.369999, Close=59.419998, Volume=12768200, Adj Close=51.825539)\n",
      "\n",
      "Row(Date=datetime.date(2012, 1, 6), Open=59.419998, High=59.450001, Low=58.869999, Close=59.0, Volume=8069400, Adj Close=51.45922)\n",
      "\n",
      "Row(Date=datetime.date(2012, 1, 9), Open=59.029999, High=59.549999, Low=58.919998, Close=59.18, Volume=6679300, Adj Close=51.616215000000004)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for x in df.head(5):\n",
    "    print(x)\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Row(Date=datetime.datetime(2012, 1, 3, 0, 0), Open=59.970001, High=61.060001, Low=59.869999, Close=60.330002, Volume=12668800, Adj Close=52.619234999999996)\n",
      "\n",
      "\n",
      "Row(Date=datetime.datetime(2012, 1, 4, 0, 0), Open=60.209998999999996, High=60.349998, Low=59.470001, Close=59.709998999999996, Volume=9593300, Adj Close=52.078475)\n",
      "\n",
      "\n",
      "Row(Date=datetime.datetime(2012, 1, 5, 0, 0), Open=59.349998, High=59.619999, Low=58.369999, Close=59.419998, Volume=12768200, Adj Close=51.825539)\n",
      "\n",
      "\n",
      "Row(Date=datetime.datetime(2012, 1, 6, 0, 0), Open=59.419998, High=59.450001, Low=58.869999, Close=59.0, Volume=8069400, Adj Close=51.45922)\n",
      "\n",
      "\n",
      "Row(Date=datetime.datetime(2012, 1, 9, 0, 0), Open=59.029999, High=59.549999, Low=58.919998, Close=59.18, Volume=6679300, Adj Close=51.616215000000004)\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Use describe() to learn about the DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+\n",
      "|summary|              Open|             High|              Low|            Close|           Volume|        Adj Close|\n",
      "+-------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+\n",
      "|  count|              1258|             1258|             1258|             1258|             1258|             1258|\n",
      "|   mean| 72.35785375357709|72.83938807631165| 71.9186009594594|72.38844998012726|8222093.481717011|67.23883848728146|\n",
      "| stddev|  6.76809024470826|6.768186808159218|6.744075756255496|6.756859163732991|  4519780.8431556|6.722609449996857|\n",
      "|    min|56.389998999999996|        57.060001|        56.299999|        56.419998|          2094900|        50.363689|\n",
      "|    max|         90.800003|        90.970001|            89.25|        90.470001|         80898100|84.91421600000001|\n",
      "+-------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.describe().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+\n",
      "|summary|              Open|             High|              Low|            Close|           Volume|        Adj Close|\n",
      "+-------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+\n",
      "|  count|              1258|             1258|             1258|             1258|             1258|             1258|\n",
      "|   mean| 72.35785375357709|72.83938807631165| 71.9186009594594|72.38844998012726|8222093.481717011|67.23883848728146|\n",
      "| stddev|  6.76809024470826|6.768186808159218|6.744075756255496|6.756859163732991|  4519780.8431556|6.722609449996857|\n",
      "|    min|56.389998999999996|        57.060001|        56.299999|        56.419998|          2094900|        50.363689|\n",
      "|    max|         90.800003|        90.970001|            89.25|        90.470001|         80898100|84.91421600000001|\n",
      "+-------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bonus Question!\n",
    "#### There are too many decimal places for mean and stddev in the describe() dataframe. Format the numbers to just show up to two decimal places. Pay careful attention to the datatypes that .describe() returns, we didn't cover how to do this exact formatting, but we covered something very similar. [Check this link for a hint](http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.Column.cast)\n",
    "\n",
    "If you get stuck on this, don't worry, just view the solutions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- summary: string (nullable = true)\n",
      " |-- Open: string (nullable = true)\n",
      " |-- High: string (nullable = true)\n",
      " |-- Low: string (nullable = true)\n",
      " |-- Close: string (nullable = true)\n",
      " |-- Volume: string (nullable = true)\n",
      " |-- Adj Close: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.describe().printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- summary: string (nullable = true)\n",
      " |-- Open: string (nullable = true)\n",
      " |-- High: string (nullable = true)\n",
      " |-- Low: string (nullable = true)\n",
      " |-- Close: string (nullable = true)\n",
      " |-- Volume: string (nullable = true)\n",
      " |-- Adj Close: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+\n",
      "|summary|              Open|             High|              Low|            Close|           Volume|        Adj Close|\n",
      "+-------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+\n",
      "|  count|              1258|             1258|             1258|             1258|             1258|             1258|\n",
      "|   mean| 72.35785375357709|72.83938807631165| 71.9186009594594|72.38844998012726|8222093.481717011|67.23883848728146|\n",
      "| stddev|  6.76809024470826|6.768186808159218|6.744075756255496|6.756859163732991|  4519780.8431556|6.722609449996857|\n",
      "|    min|56.389998999999996|        57.060001|        56.299999|        56.419998|          2094900|        50.363689|\n",
      "|    max|         90.800003|        90.970001|            89.25|        90.470001|         80898100|84.91421600000001|\n",
      "+-------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "described_df = df.describe()\n",
    "described_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- summary: string (nullable = true)\n",
      " |-- Open: string (nullable = true)\n",
      " |-- High: string (nullable = true)\n",
      " |-- Low: string (nullable = true)\n",
      " |-- Close: string (nullable = true)\n",
      " |-- Volume: string (nullable = true)\n",
      " |-- Adj Close: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "described_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- summary: string (nullable = true)\n",
      " |-- Open: string (nullable = true)\n",
      " |-- High: string (nullable = true)\n",
      " |-- Low: string (nullable = true)\n",
      " |-- Close: string (nullable = true)\n",
      " |-- Volume: string (nullable = true)\n",
      " |-- Adj Close: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "described_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- summary: string (nullable = true)\n",
      " |-- Open: double (nullable = true)\n",
      " |-- High: double (nullable = true)\n",
      " |-- Low: double (nullable = true)\n",
      " |-- Close: double (nullable = true)\n",
      " |-- Volume: long (nullable = true)\n",
      " |-- Adj Close: double (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "described_df = described_df.withColumn('Open',described_df[\"Open\"].cast(DoubleType()))\n",
    "described_df = described_df.withColumn('Close',described_df[\"Close\"].cast(DoubleType()))\n",
    "described_df = described_df.withColumn('Low',described_df[\"Low\"].cast(DoubleType()))\n",
    "described_df = described_df.withColumn('High',described_df[\"High\"].cast(DoubleType()))\n",
    "described_df = described_df.withColumn('Adj Close',described_df[\"Adj Close\"].cast(DoubleType()))\n",
    "described_df = described_df.withColumn('Volume',described_df[\"Volume\"].cast(LongType()))\n",
    "described_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+--------+--------+--------+--------+--------+\n",
      "|summary|    Open|    High|     Low|   Close|  Volume|\n",
      "+-------+--------+--------+--------+--------+--------+\n",
      "|  count|1,258.00|1,258.00|1,258.00|1,258.00|    1258|\n",
      "|   mean|   72.36|   72.84|   71.92|   72.39| 8222093|\n",
      "| stddev|    6.77|    6.77|    6.74|    6.76| 4519780|\n",
      "|    min|   56.39|   57.06|   56.30|   56.42| 2094900|\n",
      "|    max|   90.80|   90.97|   89.25|   90.47|80898100|\n",
      "+-------+--------+--------+--------+--------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "described_df.select([described_df['summary'],format_number('Open',2).alias('Open'),format_number('High',2).alias('High'),format_number('Low',2).alias('Low'),format_number('Close',2).alias('Close'),described_df['Volume']]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+--------+--------+--------+--------+--------+\n",
      "|summary|    Open|    High|     Low|   Close|  Volume|\n",
      "+-------+--------+--------+--------+--------+--------+\n",
      "|  count|1,258.00|1,258.00|1,258.00|1,258.00|    1258|\n",
      "|   mean|   72.36|   72.84|   71.92|   72.39| 8222093|\n",
      "| stddev|    6.77|    6.77|    6.74|    6.76| 4519781|\n",
      "|    min|   56.39|   57.06|   56.30|   56.42| 2094900|\n",
      "|    max|   90.80|   90.97|   89.25|   90.47|80898100|\n",
      "+-------+--------+--------+--------+--------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create a new dataframe with a column called HV Ratio that is the ratio of the High Price versus volume of stock traded for a day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|            HV Ratio|\n",
      "+--------------------+\n",
      "|4.819714653321546E-6|\n",
      "|6.290848613094555E-6|\n",
      "|4.669412994783916E-6|\n",
      "|7.367338463826307E-6|\n",
      "|8.915604778943901E-6|\n",
      "|8.644477436914568E-6|\n",
      "|9.351828421515645E-6|\n",
      "| 8.29141562102703E-6|\n",
      "|7.712212102001476E-6|\n",
      "|7.071764823529412E-6|\n",
      "|1.015495466386981E-5|\n",
      "|6.576354146362592...|\n",
      "| 5.90145296180676E-6|\n",
      "|8.547679455011844E-6|\n",
      "|8.420709512685392E-6|\n",
      "|1.041448341728929...|\n",
      "|8.316075414862431E-6|\n",
      "|9.721183814992126E-6|\n",
      "|8.029436027707578E-6|\n",
      "|6.307432259386365E-6|\n",
      "+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "new_df = df.withColumn('HV Ratio', df['High'] / df['Volume'])\n",
    "new_df.select('HV Ratio').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|            HV Ratio|\n",
      "+--------------------+\n",
      "|4.819714653321546E-6|\n",
      "|6.290848613094555E-6|\n",
      "|4.669412994783916E-6|\n",
      "|7.367338463826307E-6|\n",
      "|8.915604778943901E-6|\n",
      "|8.644477436914568E-6|\n",
      "|9.351828421515645E-6|\n",
      "| 8.29141562102703E-6|\n",
      "|7.712212102001476E-6|\n",
      "|7.071764823529412E-6|\n",
      "|1.015495466386981E-5|\n",
      "|6.576354146362592...|\n",
      "| 5.90145296180676E-6|\n",
      "|8.547679455011844E-6|\n",
      "|8.420709512685392E-6|\n",
      "|1.041448341728929...|\n",
      "|8.316075414862431E-6|\n",
      "|9.721183814992126E-6|\n",
      "|8.029436027707578E-6|\n",
      "|6.307432259386365E-6|\n",
      "+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What day had the Peak High in Price?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GroupedData[grouping expressions: [Date], value: [Date: date, Open: double ... 5 more fields], type: GroupBy]"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_data = df.groupby('Date')\n",
    "grouped_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2015, 1, 13)"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.select([df.Date,df.High]).orderBy(df['High'].desc()).head(1)[0]['Date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2015, 1, 13)"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.orderBy(df['High'].desc()).head(1)[0]['Date'] # Alternative solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2015, 1, 13, 0, 0)"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What is the mean of the Close column?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+\n",
      "|       avg(Close)|\n",
      "+-----------------+\n",
      "|72.38844998012726|\n",
      "+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.agg({'Close': 'mean'}).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------+\n",
      "|       avg(Close)|\n",
      "+-----------------+\n",
      "|72.38844998012726|\n",
      "+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What is the max and min of the Volume column?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-----------+\n",
      "|max(Volume)|min(Volume)|\n",
      "+-----------+-----------+\n",
      "|   80898100|    2094900|\n",
      "+-----------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select([max('Volume'),min('Volume')]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+-----------+\n",
      "|max(Volume)|min(Volume)|\n",
      "+-----------+-----------+\n",
      "|   80898100|    2094900|\n",
      "+-----------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### How many days was the Close lower than 60 dollars?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "81"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['Close'] < 60].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "81"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.filter('Close < 60').count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "81"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What percentage of the time was the High greater than 80 dollars ?\n",
    "#### In other words, (Number of Days High>80)/(Total Days in the dataset)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9.141494435612083"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['High'] > 80].count() / df.count() * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9.141494435612083"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What is the Pearson correlation between High and Volume?\n",
    "#### [Hint](http://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrameStatFunctions.corr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+\n",
      "| corr(High, Volume)|\n",
      "+-------------------+\n",
      "|-0.3384326061737161|\n",
      "+-------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(corr('High','Volume')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+\n",
      "| corr(High, Volume)|\n",
      "+-------------------+\n",
      "|-0.3384326061737161|\n",
      "+-------------------+\n",
      "\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What is the max High per year?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GroupedData[grouping expressions: [Year], value: [Date: date, Open: double ... 6 more fields], type: GroupBy]"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yearly_data = df.withColumn('Year',year(df.Date).alias('Year')).groupby('Year')\n",
    "yearly_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---------+\n",
      "|Year|max(High)|\n",
      "+----+---------+\n",
      "|2015|90.970001|\n",
      "|2013|81.370003|\n",
      "|2014|88.089996|\n",
      "|2012|77.599998|\n",
      "|2016|75.190002|\n",
      "+----+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "yearly_data.agg({'High': 'max'}).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---------+\n",
      "|Year|max(High)|\n",
      "+----+---------+\n",
      "|2015|90.970001|\n",
      "|2013|81.370003|\n",
      "|2014|88.089996|\n",
      "|2012|77.599998|\n",
      "|2016|75.190002|\n",
      "+----+---------+\n",
      "\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What is the average Close for each Calendar Month?\n",
    "#### In other words, across all the years, what is the average Close price for Jan,Feb, Mar, etc... Your result will have a value for each of these months. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+-----------------+\n",
      "|Month|       avg(Close)|\n",
      "+-----+-----------------+\n",
      "|   12|72.84792478301885|\n",
      "|    1|71.44801958415842|\n",
      "|    6| 72.4953774245283|\n",
      "|    3|71.77794377570092|\n",
      "|    5|72.30971688679247|\n",
      "|    9|72.18411785294116|\n",
      "|    4|72.97361900952382|\n",
      "|    8|73.02981855454546|\n",
      "|    7|74.43971943925233|\n",
      "|   10|71.57854545454543|\n",
      "|   11| 72.1110893069307|\n",
      "|    2|  71.306804443299|\n",
      "+-----+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "monthly_data = df.withColumn('Month',month(df.Date).alias('Month')).groupby('Month')\n",
    "monthly_data.agg({'Close': 'avg'}).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+-----------------+\n",
      "|Month|       avg(Close)|\n",
      "+-----+-----------------+\n",
      "|    1|71.44801958415842|\n",
      "|    2|  71.306804443299|\n",
      "|    3|71.77794377570092|\n",
      "|    4|72.97361900952382|\n",
      "|    5|72.30971688679247|\n",
      "|    6| 72.4953774245283|\n",
      "|    7|74.43971943925233|\n",
      "|    8|73.02981855454546|\n",
      "|    9|72.18411785294116|\n",
      "|   10|71.57854545454543|\n",
      "|   11| 72.1110893069307|\n",
      "|   12|72.84792478301885|\n",
      "+-----+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "monthly_data.agg({'Close': 'avg'}).orderBy('Month').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+-----------------+\n",
      "|Month|       avg(Close)|\n",
      "+-----+-----------------+\n",
      "|    1|71.44801958415842|\n",
      "|    2|  71.306804443299|\n",
      "|    3|71.77794377570092|\n",
      "|    4|72.97361900952382|\n",
      "|    5|72.30971688679247|\n",
      "|    6| 72.4953774245283|\n",
      "|    7|74.43971943925233|\n",
      "|    8|73.02981855454546|\n",
      "|    9|72.18411785294116|\n",
      "|   10|71.57854545454543|\n",
      "|   11| 72.1110893069307|\n",
      "|   12|72.84792478301885|\n",
      "+-----+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Great Job!"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}