PySparkSQL_Stock_Price_Prediction/Stock_Prices_using_Spark_SQL.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d99g2KpE4vZ0"
      },
      "source": [
        "# **Stock Prices using Spark SQL**"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "! pip install pyspark"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "iXUgy9yZ5Wvp",
        "outputId": "6b9e480c-488e-409b-c318-f519e0808132"
      },
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting pyspark\n",
            "  Downloading pyspark-3.2.0.tar.gz (281.3 MB)\n",
            "\u001b[K     |████████████████████████████████| 281.3 MB 41 kB/s \n",
            "\u001b[?25hCollecting py4j==0.10.9.2\n",
            "  Downloading py4j-0.10.9.2-py2.py3-none-any.whl (198 kB)\n",
            "\u001b[K     |████████████████████████████████| 198 kB 41.9 MB/s \n",
            "\u001b[?25hBuilding wheels for collected packages: pyspark\n",
            "  Building wheel for pyspark (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for pyspark: filename=pyspark-3.2.0-py2.py3-none-any.whl size=281805912 sha256=a6c53104c0c026a8c907b368e66bf4e605889452a8b82a01574a486960fb1c90\n",
            "  Stored in directory: /root/.cache/pip/wheels/0b/de/d2/9be5d59d7331c6c2a7c1b6d1a4f463ce107332b1ecd4e80718\n",
            "Successfully built pyspark\n",
            "Installing collected packages: py4j, pyspark\n",
            "Successfully installed py4j-0.10.9.2 pyspark-3.2.0\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "745Q1GWO4vZ_"
      },
      "outputs": [],
      "source": [
        "import pyspark\n",
        "from pyspark import SparkContext\n",
        "\n",
        "sc = SparkContext.getOrCreate()"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "sc"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 196
        },
        "id": "1F6bA4Zx5r1h",
        "outputId": "4189eaec-3d21-4698-8f0d-d71715effd42"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "        <div>\n",
              "            <p><b>SparkContext</b></p>\n",
              "\n",
              "            <p><a href=\"http://47909734d637:4040\">Spark UI</a></p>\n",
              "\n",
              "            <dl>\n",
              "              <dt>Version</dt>\n",
              "                <dd><code>v3.2.0</code></dd>\n",
              "              <dt>Master</dt>\n",
              "                <dd><code>local[*]</code></dd>\n",
              "              <dt>AppName</dt>\n",
              "                <dd><code>pyspark-shell</code></dd>\n",
              "            </dl>\n",
              "        </div>\n",
              "        "
            ],
            "text/plain": [
              "<SparkContext master=local[*] appName=pyspark-shell>"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "n2lWzjta4vaA",
        "outputId": "c0cd3061-3104-435e-b18c-9aa2650534c1"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "pyspark.context.SparkContext"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ],
      "source": [
        "type(sc)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "id": "eNxq4deR4vaA"
      },
      "outputs": [],
      "source": [
        "data = [1, 2, 3, 4, 5]\n",
        "distData = sc.parallelize(data)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jLgJ-J994vaC",
        "outputId": "6669ed86-2ca9-4490-fb94-51b384f439c6"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/pyspark/sql/context.py:79: FutureWarning: Deprecated in 3.0.0. Use SparkSession.builder.getOrCreate() instead.\n",
            "  FutureWarning\n"
          ]
        }
      ],
      "source": [
        "from pyspark.sql import SQLContext\n",
        "\n",
        "sqlContext = SQLContext(sc)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "k-b3sJbg4vaD",
        "outputId": "5396e44e-a4f4-48c7-efca-065d1174eaea"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[Row(name='Alice', age=1)]"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ],
      "source": [
        "l = [('Alice', 1)]\n",
        "df = sqlContext.createDataFrame(l, ['name', 'age'])\n",
        "df.collect()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "gh42iM_D4vaE",
        "outputId": "27cc2630-3bfe-4030-b89d-b54f9b80ad9a"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['Date,Open,High,Low,Close,AdjClose,Volume',\n",
              " '2019-07-15,248.000000,254.419998,244.860001,253.500000,253.500000,11000100',\n",
              " '2019-07-16,249.300003,253.529999,247.929993,252.380005,252.380005,8149000',\n",
              " '2019-07-17,255.669998,258.309998,253.350006,254.860001,254.860001,9764700',\n",
              " '2019-07-18,255.050003,255.750000,251.889999,253.539993,253.539993,4764500']"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ],
      "source": [
        "data_file = 'TSLA.csv'\n",
        "raw_rdd = sc.textFile(data_file).cache()\n",
        "raw_rdd.take(5)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kBGaXtsb4vaF",
        "outputId": "9e2257d1-a9c9-4ff1-c017-ca19dc663367"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "pyspark.rdd.RDD"
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ],
      "source": [
        "type(raw_rdd)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ct894Wap4vaG",
        "outputId": "4fb90d01-9058-488c-ce95-f972d1022fb0"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[['Date', 'Open', 'High', 'Low', 'Close', 'AdjClose', 'Volume'], ['2019-07-15', '248.000000', '254.419998', '244.860001', '253.500000', '253.500000', '11000100']]\n",
            "<class 'pyspark.rdd.PipelinedRDD'>\n"
          ]
        }
      ],
      "source": [
        "csv_rdd = raw_rdd.map(lambda row: row.split(\",\"))\n",
        "print(csv_rdd.take(2))\n",
        "print(type(csv_rdd))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FVzdCF9S4vaH",
        "outputId": "92a44518-7b4a-4888-dfbf-7229ae8eb4fc"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "7"
            ]
          },
          "metadata": {},
          "execution_count": 15
        }
      ],
      "source": [
        "len(csv_rdd.take(1)[0])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "metadata": {
        "id": "9vhm4krP4vaI"
      },
      "outputs": [],
      "source": [
        "df = sqlContext.read.load(data_file, \n",
        "                      format='com.databricks.spark.csv', \n",
        "                      header='true', \n",
        "                      inferSchema='true')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0IC2OI684vaI",
        "outputId": "f18cbd55-9e29-4b09-eafb-d3731a755acb"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "253"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ],
      "source": [
        "df.count()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "T_YXhd6y4vaJ",
        "outputId": "1c5e59c2-7cf3-47aa-fa59-c1bb7e3af37a"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[Row(Date='2019-07-15', Open=248.0, High=254.419998, Low=244.860001, Close=253.5, AdjClose=253.5, Volume=11000100),\n",
              " Row(Date='2019-07-16', Open=249.300003, High=253.529999, Low=247.929993, Close=252.380005, AdjClose=252.380005, Volume=8149000),\n",
              " Row(Date='2019-07-17', Open=255.669998, High=258.309998, Low=253.350006, Close=254.860001, AdjClose=254.860001, Volume=9764700),\n",
              " Row(Date='2019-07-18', Open=255.050003, High=255.75, Low=251.889999, Close=253.539993, AdjClose=253.539993, Volume=4764500),\n",
              " Row(Date='2019-07-19', Open=255.690002, High=259.959991, Low=254.619995, Close=258.179993, AdjClose=258.179993, Volume=7048400)]"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ],
      "source": [
        "df.take(5)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 19,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Vz8aYi2T4vaK",
        "outputId": "6667ceb3-7c5a-4316-8c8c-33c7077d4582"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- Date: string (nullable = true)\n",
            " |-- Open: double (nullable = true)\n",
            " |-- High: double (nullable = true)\n",
            " |-- Low: double (nullable = true)\n",
            " |-- Close: double (nullable = true)\n",
            " |-- AdjClose: double (nullable = true)\n",
            " |-- Volume: integer (nullable = true)\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df.printSchema()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "zDg-a-6D4vaK",
        "outputId": "bcb0a0e8-22f2-4b9d-8662-08d76894e2c0"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-80b5811b-a5a4-483f-b37b-4b91f006a4e4\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Date</th>\n",
              "      <th>Open</th>\n",
              "      <th>High</th>\n",
              "      <th>Low</th>\n",
              "      <th>Close</th>\n",
              "      <th>AdjClose</th>\n",
              "      <th>Volume</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2019-07-15</td>\n",
              "      <td>248.000000</td>\n",
              "      <td>254.419998</td>\n",
              "      <td>244.860001</td>\n",
              "      <td>253.500000</td>\n",
              "      <td>253.500000</td>\n",
              "      <td>11000100</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2019-07-16</td>\n",
              "      <td>249.300003</td>\n",
              "      <td>253.529999</td>\n",
              "      <td>247.929993</td>\n",
              "      <td>252.380005</td>\n",
              "      <td>252.380005</td>\n",
              "      <td>8149000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2019-07-17</td>\n",
              "      <td>255.669998</td>\n",
              "      <td>258.309998</td>\n",
              "      <td>253.350006</td>\n",
              "      <td>254.860001</td>\n",
              "      <td>254.860001</td>\n",
              "      <td>9764700</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2019-07-18</td>\n",
              "      <td>255.050003</td>\n",
              "      <td>255.750000</td>\n",
              "      <td>251.889999</td>\n",
              "      <td>253.539993</td>\n",
              "      <td>253.539993</td>\n",
              "      <td>4764500</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2019-07-19</td>\n",
              "      <td>255.690002</td>\n",
              "      <td>259.959991</td>\n",
              "      <td>254.619995</td>\n",
              "      <td>258.179993</td>\n",
              "      <td>258.179993</td>\n",
              "      <td>7048400</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-80b5811b-a5a4-483f-b37b-4b91f006a4e4')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-80b5811b-a5a4-483f-b37b-4b91f006a4e4 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-80b5811b-a5a4-483f-b37b-4b91f006a4e4');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "         Date        Open        High  ...       Close    AdjClose    Volume\n",
              "0  2019-07-15  248.000000  254.419998  ...  253.500000  253.500000  11000100\n",
              "1  2019-07-16  249.300003  253.529999  ...  252.380005  252.380005   8149000\n",
              "2  2019-07-17  255.669998  258.309998  ...  254.860001  254.860001   9764700\n",
              "3  2019-07-18  255.050003  255.750000  ...  253.539993  253.539993   4764500\n",
              "4  2019-07-19  255.690002  259.959991  ...  258.179993  258.179993   7048400\n",
              "\n",
              "[5 rows x 7 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ],
      "source": [
        "import pandas\n",
        "df.toPandas().head(5)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "metadata": {
        "id": "sqCVOsDU4vaL"
      },
      "outputs": [],
      "source": [
        "from pyspark.sql.types import *\n",
        "schema = StructType([\n",
        "   StructField(\"date\", StringType(), True),\n",
        "   StructField(\"openprice\", IntegerType(), True),\n",
        "   StructField(\"highprice\", IntegerType(), True),\n",
        "   StructField(\"lowprice\", IntegerType(), True),\n",
        "   StructField(\"closeprice\", IntegerType(), True),\n",
        "   StructField(\"volume\", IntegerType(), True),\n",
        "   StructField(\"adjcloseprice\", IntegerType(), True)])\n",
        "\n",
        "\n",
        "df2 = sqlContext.read.load(data_file, \n",
        "                      format='com.databricks.spark.csv', \n",
        "                      header='true', \n",
        "                      schema=schema)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "IsOfSS6g4vaL",
        "outputId": "1e1de293-dc5f-46f7-ddba-bd16d9a1b0dd"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[Row(date='2019-07-15', openprice=None, highprice=None, lowprice=None, closeprice=None, volume=None, adjcloseprice=11000100),\n",
              " Row(date='2019-07-16', openprice=None, highprice=None, lowprice=None, closeprice=None, volume=None, adjcloseprice=8149000)]"
            ]
          },
          "metadata": {},
          "execution_count": 22
        }
      ],
      "source": [
        "df2.take(2)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rHtIdFhF4vaM",
        "outputId": "7d7acafe-af8e-4333-ae2d-cb35ea162028"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- date: string (nullable = true)\n",
            " |-- openprice: integer (nullable = true)\n",
            " |-- highprice: integer (nullable = true)\n",
            " |-- lowprice: integer (nullable = true)\n",
            " |-- closeprice: integer (nullable = true)\n",
            " |-- volume: integer (nullable = true)\n",
            " |-- adjcloseprice: integer (nullable = true)\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df2.printSchema()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 24,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "u33LjkQa4vaM",
        "outputId": "4ff8cdf3-6ae7-4368-b9c6-4a6d294d7ce7"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "pyspark.sql.dataframe.DataFrame"
            ]
          },
          "metadata": {},
          "execution_count": 24
        }
      ],
      "source": [
        "type(df)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 25,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Yo-b_FAe4vaM",
        "outputId": "07c2f7ba-cf28-432e-cd5f-f63e6522ed16"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+----------+----------+----------+----------+----------+--------+\n",
            "|      Date|      Open|      High|       Low|     Close|  AdjClose|  Volume|\n",
            "+----------+----------+----------+----------+----------+----------+--------+\n",
            "|2019-07-15|     248.0|254.419998|244.860001|     253.5|     253.5|11000100|\n",
            "|2019-07-16|249.300003|253.529999|247.929993|252.380005|252.380005| 8149000|\n",
            "|2019-07-17|255.669998|258.309998|253.350006|254.860001|254.860001| 9764700|\n",
            "|2019-07-18|255.050003|    255.75|251.889999|253.539993|253.539993| 4764500|\n",
            "|2019-07-19|255.690002|259.959991|254.619995|258.179993|258.179993| 7048400|\n",
            "+----------+----------+----------+----------+----------+----------+--------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df.show(5)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 26,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "O8c7sMX74vaN",
        "outputId": "691591d5-3fe1-4501-f221-b0834b78c929"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+----------+----------+----------+----------+----------+--------+\n",
            "|      Date|      Open|      High|       Low|     Close|  AdjClose|  Volume|\n",
            "+----------+----------+----------+----------+----------+----------+--------+\n",
            "|2019-07-15|     248.0|254.419998|244.860001|     253.5|     253.5|11000100|\n",
            "|2019-07-16|249.300003|253.529999|247.929993|252.380005|252.380005| 8149000|\n",
            "|2019-07-17|255.669998|258.309998|253.350006|254.860001|254.860001| 9764700|\n",
            "|2019-07-18|255.050003|    255.75|251.889999|253.539993|253.539993| 4764500|\n",
            "|2019-07-19|255.690002|259.959991|254.619995|258.179993|258.179993| 7048400|\n",
            "|2019-07-22|    258.75|262.149994|254.190002|255.679993|255.679993| 6842400|\n",
            "|2019-07-23|256.709991|260.480011|     254.5|260.170013|260.170013| 5023100|\n",
            "|2019-07-24|259.170013|266.070007|258.160004|264.880005|264.880005|11072800|\n",
            "|2019-07-25|     233.5|     234.5|225.550003|228.820007|228.820007|22418300|\n",
            "|2019-07-26|226.919998|230.259995|    222.25|228.039993|228.039993|10027700|\n",
            "|2019-07-29|227.089996|235.940002|226.029999|235.770004|235.770004| 9273300|\n",
            "|2019-07-30|232.899994|243.360001|232.179993|242.259995|242.259995| 8109000|\n",
            "|2019-07-31|     243.0|246.679993|236.649994|241.610001|241.610001| 9178200|\n",
            "|2019-08-01|242.649994|244.509995|231.770004|233.850006|233.850006| 8259500|\n",
            "|2019-08-02|231.350006|236.270004|229.229996|234.339996|234.339996| 6136500|\n",
            "|2019-08-05|229.600006|231.369995|225.779999|228.320007|228.320007| 7028300|\n",
            "|2019-08-06|231.880005|     232.5|    225.75|    230.75|    230.75| 5564200|\n",
            "|2019-08-07|     226.5|233.570007|225.800003|233.419998|233.419998| 4776500|\n",
            "|2019-08-08|234.449997|239.800003|232.649994|238.300003|238.300003| 5274300|\n",
            "|2019-08-09|236.050003|238.960007|233.809998|235.009995|235.009995| 3898200|\n",
            "+----------+----------+----------+----------+----------+----------+--------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "tesla_data_file = 'TSLA.csv'\n",
        "\n",
        "tesla_df = sqlContext.read.load(tesla_data_file, \n",
        "                      format='com.databricks.spark.csv', \n",
        "                      header='true', \n",
        "                      inferSchema='true')\n",
        "\n",
        "tesla_df.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 27,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "W7s62_iP4vaN",
        "outputId": "9393ee83-8845-4540-9a8e-874f74f838a1"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-----------+-----------+-----------+-----------+-----------+-------+\n",
            "|      Date|       Open|       High|        Low|      Close|   AdjClose| Volume|\n",
            "+----------+-----------+-----------+-----------+-----------+-----------+-------+\n",
            "|2019-07-15|1146.859985|1150.819946|1139.400024|1150.339966|1150.339966| 903800|\n",
            "|2019-07-16|     1146.0|1158.579956|     1145.0|1153.579956|1153.579956|1238800|\n",
            "|2019-07-17|1150.969971|1158.359985| 1145.77002|1146.349976|1146.349976|1170000|\n",
            "|2019-07-18| 1141.73999| 1147.60498| 1132.72998|1146.329956|1146.329956|1291300|\n",
            "|2019-07-19|1148.189941|1151.140015|1129.619995|1130.099976|1130.099976|1647200|\n",
            "|2019-07-22|1133.449951|    1139.25| 1124.23999|1138.069946|1138.069946|1301500|\n",
            "|2019-07-23|     1144.0|1146.900024|1131.800049|1146.209961|1146.209961|1093700|\n",
            "|2019-07-24|1131.900024|     1144.0| 1126.98999|1137.810059|1137.810059|1589800|\n",
            "|2019-07-25|1137.819946|1141.699951|1120.920044|1132.119995|1132.119995|2209800|\n",
            "|2019-07-26|1224.040039|1265.550049|     1224.0|1250.410034|1250.410034|4805800|\n",
            "|2019-07-29|1241.050049|1247.369995| 1228.22998|1239.410034|1239.410034|2223700|\n",
            "|2019-07-30|1225.410034|1234.869995|1223.300049|1225.140015|1225.140015|1453300|\n",
            "|2019-07-31|     1223.0|     1234.0|1207.764038|1216.680054|1216.680054|1725500|\n",
            "|2019-08-01|1214.030029|1234.109985|1205.719971| 1209.01001| 1209.01001|1698500|\n",
            "|2019-08-02| 1200.73999|1206.900024|1188.939941| 1193.98999| 1193.98999|1645100|\n",
            "|2019-08-05|1170.040039| 1175.23999|1140.140015|1152.319946|1152.319946|2597500|\n",
            "|2019-08-06|1163.310059|1179.959961|     1160.0|1169.949951|1169.949951|1709400|\n",
            "|2019-08-07|     1156.0|1178.444946|1149.624023| 1173.98999| 1173.98999|1444300|\n",
            "|2019-08-08|1182.829956| 1205.01001| 1173.02002|1204.800049|1204.800049|1468000|\n",
            "|2019-08-09| 1197.98999|1203.880005|1183.603027| 1188.01001| 1188.01001|1065700|\n",
            "+----------+-----------+-----------+-----------+-----------+-----------+-------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "google_data_file = 'GOOG.csv'\n",
        "\n",
        "google_df = sqlContext.read.load(google_data_file, \n",
        "                      format='com.databricks.spark.csv', \n",
        "                      header='true', \n",
        "                      inferSchema='true')\n",
        "\n",
        "google_df.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 28,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "mZr1FGRH4vaN",
        "outputId": "acbc70ee-1bc8-41c8-8be6-8d1bdc7d7cd4"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-----------+-----------+-----------+-----------+-----------+-------+\n",
            "|      Date|       Open|       High|        Low|      Close|   AdjClose| Volume|\n",
            "+----------+-----------+-----------+-----------+-----------+-----------+-------+\n",
            "|2019-07-15|2021.400024|2022.900024|2001.550049| 2020.98999| 2020.98999|2981300|\n",
            "|2019-07-16|2010.579956|2026.319946|2001.219971|2009.900024|2009.900024|2618200|\n",
            "|2019-07-17|2007.050049|     2012.0|1992.030029|1992.030029|1992.030029|2558800|\n",
            "|2019-07-18| 1980.01001|     1987.5|1951.550049|1977.900024|1977.900024|3504300|\n",
            "|2019-07-19|1991.209961|     1996.0| 1962.22998| 1964.52002| 1964.52002|3185600|\n",
            "|2019-07-22|1971.140015|     1989.0| 1958.26001|1985.630005|1985.630005|2900000|\n",
            "|2019-07-23| 1995.98999|1997.790039|1973.130005| 1994.48999| 1994.48999|2703500|\n",
            "|2019-07-24|1969.300049|2001.300049|1965.869995|2000.810059|2000.810059|2631300|\n",
            "|2019-07-25|     2001.0|2001.199951|1972.719971|1973.819946|1973.819946|4136500|\n",
            "|2019-07-26|     1942.0|1950.900024| 1924.51001|1943.050049|1943.050049|4927100|\n",
            "|2019-07-29|     1930.0| 1932.22998|1890.540039|1912.449951|1912.449951|4493200|\n",
            "|2019-07-30|1891.119995|1909.890015| 1883.47998|1898.530029|1898.530029|2910900|\n",
            "|2019-07-31|1898.109985|1899.550049|1849.439941|1866.780029|1866.780029|4470700|\n",
            "|2019-08-01|1871.719971|1897.920044| 1844.01001|1855.319946|1855.319946|4713300|\n",
            "|2019-08-02|1845.069946|1846.359985| 1808.02002| 1823.23999| 1823.23999|4956200|\n",
            "|2019-08-05|1770.219971|1788.670044|1748.780029|1765.130005|1765.130005|6058200|\n",
            "|2019-08-06| 1792.22998| 1793.77002|1753.400024|1787.829956|1787.829956|5070300|\n",
            "|2019-08-07| 1773.98999|1798.930054|     1757.0|1793.400024|1793.400024|4526900|\n",
            "|2019-08-08|     1806.0| 1834.26001|1798.109985|1832.890015|1832.890015|3701200|\n",
            "|2019-08-09|1828.949951|1831.089966|1802.219971|1807.579956|1807.579956|2879800|\n",
            "+----------+-----------+-----------+-----------+-----------+-----------+-------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "amazon_data_file = 'AMZN.csv'\n",
        "\n",
        "amazon_df = sqlContext.read.load(amazon_data_file, \n",
        "                      format='com.databricks.spark.csv', \n",
        "                      header='true', \n",
        "                      inferSchema='true')\n",
        "\n",
        "amazon_df.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 29,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rhoV8ncp4vaO",
        "outputId": "9901c12d-6508-4fc4-ed38-2d7d8c944e51"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+------------------+\n",
            "|year|     avg(AdjClose)|\n",
            "+----+------------------+\n",
            "|2019|1800.6161329411755|\n",
            "|2020| 2236.414478798507|\n",
            "+----+------------------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "import datetime\n",
        "from pyspark.sql.functions import year, month, dayofmonth\n",
        "\n",
        "# Average closing price per year for AMZN\n",
        "# stocksDF.select(year($\"dt\").alias(\"yr\"), $\"adjcloseprice\").groupBy(\"yr\").avg(\"adjcloseprice\").orderBy(desc(\"yr\")).show\n",
        "\n",
        "amazon_df.select(year(\"Date\").alias(\"year\"), \"AdjClose\").groupby(\"year\").avg(\"AdjClose\").sort(\"year\").show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 30,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "VP2vFHWW4vaO",
        "outputId": "453651d2-75a8-4faa-95ab-41c7deffe2d9"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+-----+------------------+\n",
            "|year|month|     avg(AdjClose)|\n",
            "+----+-----+------------------+\n",
            "|2019|    7|1964.6846265384618|\n",
            "|2019|    8|1793.6027220909093|\n",
            "|2019|    9|     1799.12099615|\n",
            "|2019|   10|1752.3317498695653|\n",
            "|2019|   11|      1774.2939941|\n",
            "|2019|   12|1785.7728446190476|\n",
            "|2020|    1|1884.2376128571425|\n",
            "|2020|    2|2066.1752672631574|\n",
            "|2020|    3|1872.3104358636365|\n",
            "|2020|    4|2228.7052408571426|\n",
            "|2020|    5|2394.1840209499996|\n",
            "|2020|    6|      2613.5454545|\n",
            "|2020|    7| 3053.100016222222|\n",
            "+----+-----+------------------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Compute the average closing price per month for apc\n",
        "# stocksDF.select(year($\"dt\").alias(\"yr\"),month($\"dt\").alias(\"mo\"), $\"adjcloseprice\")\n",
        "# .groupBy(\"yr\",\"mo\").agg(avg(\"adjcloseprice\")).orderBy(desc(\"yr\"),desc(\"mo\")).show\n",
        "\n",
        "amazon_df.select(year(\"Date\").alias(\"year\"),\n",
        "                month(\"Date\").alias(\"month\"),\n",
        "                \"AdjClose\").groupby(\"year\", \"month\").avg(\"AdjClose\").sort(\"year\", \"month\").show()"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "amazon_df.select(year(\"Date\").alias(\"year\"),\n",
        "                month(\"Date\").alias(\"month\"),\n",
        "                \"AdjClose\").groupby(\"year\", \"month\").avg(\"AdjClose\").sort(\"year\", \"month\").explain(True)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-Un1lE3K-FsM",
        "outputId": "d65bedd7-753b-4c4e-f750-19e21d8f2e31"
      },
      "execution_count": 32,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "== Parsed Logical Plan ==\n",
            "'Sort ['year ASC NULLS FIRST, 'month ASC NULLS FIRST], true\n",
            "+- Aggregate [year#396, month#397], [year#396, month#397, avg(AdjClose#270) AS avg(AdjClose)#405]\n",
            "   +- Project [year(cast(Date#265 as date)) AS year#396, month(cast(Date#265 as date)) AS month#397, AdjClose#270]\n",
            "      +- Relation [Date#265,Open#266,High#267,Low#268,Close#269,AdjClose#270,Volume#271] csv\n",
            "\n",
            "== Analyzed Logical Plan ==\n",
            "year: int, month: int, avg(AdjClose): double\n",
            "Sort [year#396 ASC NULLS FIRST, month#397 ASC NULLS FIRST], true\n",
            "+- Aggregate [year#396, month#397], [year#396, month#397, avg(AdjClose#270) AS avg(AdjClose)#405]\n",
            "   +- Project [year(cast(Date#265 as date)) AS year#396, month(cast(Date#265 as date)) AS month#397, AdjClose#270]\n",
            "      +- Relation [Date#265,Open#266,High#267,Low#268,Close#269,AdjClose#270,Volume#271] csv\n",
            "\n",
            "== Optimized Logical Plan ==\n",
            "Sort [year#396 ASC NULLS FIRST, month#397 ASC NULLS FIRST], true\n",
            "+- Aggregate [year#396, month#397], [year#396, month#397, avg(AdjClose#270) AS avg(AdjClose)#405]\n",
            "   +- Project [year(cast(Date#265 as date)) AS year#396, month(cast(Date#265 as date)) AS month#397, AdjClose#270]\n",
            "      +- Relation [Date#265,Open#266,High#267,Low#268,Close#269,AdjClose#270,Volume#271] csv\n",
            "\n",
            "== Physical Plan ==\n",
            "AdaptiveSparkPlan isFinalPlan=false\n",
            "+- Sort [year#396 ASC NULLS FIRST, month#397 ASC NULLS FIRST], true, 0\n",
            "   +- Exchange rangepartitioning(year#396 ASC NULLS FIRST, month#397 ASC NULLS FIRST, 200), ENSURE_REQUIREMENTS, [id=#319]\n",
            "      +- HashAggregate(keys=[year#396, month#397], functions=[avg(AdjClose#270)], output=[year#396, month#397, avg(AdjClose)#405])\n",
            "         +- Exchange hashpartitioning(year#396, month#397, 200), ENSURE_REQUIREMENTS, [id=#316]\n",
            "            +- HashAggregate(keys=[year#396, month#397], functions=[partial_avg(AdjClose#270)], output=[year#396, month#397, sum#411, count#412L])\n",
            "               +- Project [year(cast(Date#265 as date)) AS year#396, month(cast(Date#265 as date)) AS month#397, AdjClose#270]\n",
            "                  +- FileScan csv [Date#265,AdjClose#270] Batched: false, DataFilters: [], Format: CSV, Location: InMemoryFileIndex(1 paths)[file:/content/AMZN.csv], PartitionFilters: [], PushedFilters: [], ReadSchema: struct<Date:string,AdjClose:double>\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 33,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "l1Hwg8y14vaP",
        "outputId": "a1e6d831-21f0-4eee-88c1-b112382da420"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/pyspark/sql/dataframe.py:140: FutureWarning: Deprecated in 2.0, use createOrReplaceTempView instead.\n",
            "  FutureWarning\n"
          ]
        }
      ],
      "source": [
        "# register the DataFrames as  temp views\n",
        "\n",
        "amazon_df.registerTempTable(\"amazon_stocks\")\n",
        "google_df.registerTempTable(\"google_stocks\")\n",
        "tesla_df.registerTempTable(\"tesla_stocks\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 35,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qQQICHof4vaP",
        "outputId": "9f3bcba6-e973-4783-aa4b-08fe402d637e"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-----------+-----------+-----------+-----------+-----------+-------+\n",
            "|      Date|       Open|       High|        Low|      Close|   AdjClose| Volume|\n",
            "+----------+-----------+-----------+-----------+-----------+-----------+-------+\n",
            "|2019-07-15|2021.400024|2022.900024|2001.550049| 2020.98999| 2020.98999|2981300|\n",
            "|2019-07-16|2010.579956|2026.319946|2001.219971|2009.900024|2009.900024|2618200|\n",
            "|2019-07-17|2007.050049|     2012.0|1992.030029|1992.030029|1992.030029|2558800|\n",
            "|2019-07-18| 1980.01001|     1987.5|1951.550049|1977.900024|1977.900024|3504300|\n",
            "|2019-07-19|1991.209961|     1996.0| 1962.22998| 1964.52002| 1964.52002|3185600|\n",
            "+----------+-----------+-----------+-----------+-----------+-----------+-------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "sqlContext.sql(\"SELECT * FROM amazon_stocks\").show(5)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 36,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "46uVME514vaP",
        "outputId": "2d52e2eb-2283-41ed-df68-276c62c0b82c"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+---+------------------+\n",
            "|  yr| mo|     avg(AdjClose)|\n",
            "+----+---+------------------+\n",
            "|2019| 10|1752.3317498695653|\n",
            "|2020|  6|      2613.5454545|\n",
            "|2020|  3|1872.3104358636365|\n",
            "|2019|  8|1793.6027220909093|\n",
            "|2020|  4|2228.7052408571426|\n",
            "|2020|  1|1884.2376128571425|\n",
            "|2019|  9|     1799.12099615|\n",
            "|2019| 12|1785.7728446190476|\n",
            "|2020|  7| 3053.100016222222|\n",
            "|2020|  2|2066.1752672631574|\n",
            "|2019|  7|1964.6846265384618|\n",
            "|2019| 11|      1774.2939941|\n",
            "|2020|  5|2394.1840209499996|\n",
            "+----+---+------------------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Calculate and display the average closing price per month for XOM ordered by year,month \n",
        "\n",
        "sqlContext.sql(\"\"\"SELECT year(amazon_stocks.Date) as yr, month(amazon_stocks.Date) as mo, avg(amazon_stocks.AdjClose) from amazon_stocks group By year(amazon_stocks.Date), month(amazon_stocks.Date)\"\"\").show()\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 37,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "lCRqqAzd4vaQ",
        "outputId": "f6edf4bf-b56c-4eea-8dd0-e0db658adae2"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-----------+-----------+------------------+\n",
            "|      Date|       Open|      Close|            spydif|\n",
            "+----------+-----------+-----------+------------------+\n",
            "|2019-07-16|     1146.0|1153.579956| 7.579956000000038|\n",
            "|2019-07-17|1150.969971|1146.349976| 4.619995000000017|\n",
            "|2019-07-18| 1141.73999|1146.329956| 4.589966000000004|\n",
            "|2019-07-19|1148.189941|1130.099976| 18.08996500000012|\n",
            "|2019-07-22|1133.449951|1138.069946| 4.619995000000017|\n",
            "|2019-07-24|1131.900024|1137.810059|  5.91003499999988|\n",
            "|2019-07-25|1137.819946|1132.119995|5.6999510000000555|\n",
            "|2019-07-26|1224.040039|1250.410034|26.369995000000017|\n",
            "|2019-07-31|     1223.0|1216.680054| 6.319946000000073|\n",
            "|2019-08-01|1214.030029| 1209.01001|5.0200190000000475|\n",
            "|2019-08-02| 1200.73999| 1193.98999|              6.75|\n",
            "|2019-08-05|1170.040039|1152.319946|17.720092999999906|\n",
            "|2019-08-06|1163.310059|1169.949951| 6.639892000000145|\n",
            "|2019-08-07|     1156.0| 1173.98999|17.989990000000034|\n",
            "|2019-08-08|1182.829956|1204.800049|21.970092999999906|\n",
            "|2019-08-09| 1197.98999| 1188.01001| 9.979980000000069|\n",
            "|2019-08-12|1179.209961|1174.709961|               4.5|\n",
            "|2019-08-13|1171.459961| 1197.27002| 25.81005899999991|\n",
            "|2019-08-14|1176.310059|1164.290039|12.020019999999931|\n",
            "|2019-08-19|1190.089966|1198.449951| 8.359985000000052|\n",
            "+----------+-----------+-----------+------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# When did the closing price for SPY go up or down by more than 2 dollars?\n",
        "\n",
        "sqlContext.sql(\"SELECT google_stocks.Date, google_stocks.Open, google_stocks.Close, abs(google_stocks.Close - google_stocks.Open) as spydif FROM google_stocks WHERE abs(google_stocks.Close - google_stocks.Open) > 4 \").show()\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 38,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "UVuggW114vaQ",
        "outputId": "fb166235-7d2a-4f93-866c-ec77962ba265"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+-------------+-------------+\n",
            "|  yr|max(AdjClose)|min(AdjClose)|\n",
            "+----+-------------+-------------+\n",
            "|2019|   430.940002|   211.399994|\n",
            "|2020|  1544.650024|   361.220001|\n",
            "+----+-------------+-------------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "#What was the max, min closing price for SPY and XOM by Year?</font> \n",
        "\n",
        "sqlContext.sql(\"SELECT year(tesla_stocks.Date) as yr, max(tesla_stocks.AdjClose), min(tesla_stocks.AdjClose) FROM tesla_stocks group By year(tesla_stocks.Date)\").show()\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4XN0c9Wd4vaQ",
        "outputId": "a163cd44-f544-4b2d-984a-13a7314eb86b"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "== Physical Plan ==\n",
            "*(2) HashAggregate(keys=[year(cast(Date#133 as date))#524], functions=[max(AdjClose#138), min(AdjClose#138)])\n",
            "+- Exchange hashpartitioning(year(cast(Date#133 as date))#524, 200), true, [id=#324]\n",
            "   +- *(1) HashAggregate(keys=[year(cast(Date#133 as date)) AS year(cast(Date#133 as date))#524], functions=[partial_max(AdjClose#138), partial_min(AdjClose#138)])\n",
            "      +- FileScan csv [Date#133,AdjClose#138] Batched: false, DataFilters: [], Format: CSV, Location: InMemoryFileIndex[file:/home/jovyan/work/TSLA.csv], PartitionFilters: [], PushedFilters: [], ReadSchema: struct<Date:string,AdjClose:double>\n",
            "\n",
            "\n"
          ]
        }
      ],
      "source": [
        "sqlContext.sql(\"SELECT year(tesla_stocks.Date) as yr, max(tesla_stocks.AdjClose), min(tesla_stocks.AdjClose) FROM tesla_stocks group By year(tesla_stocks.Date)\").explain()\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 39,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-91mGTAm4vaR",
        "outputId": "8e60c0ea-a894-41e7-bfb3-06536fbbeb95"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+----------+-----------+-----------+\n",
            "|      Date|teslaclose|amazonclose|googleclose|\n",
            "+----------+----------+-----------+-----------+\n",
            "|2019-07-15|     253.5| 2020.98999|1150.339966|\n",
            "|2019-07-16|252.380005|2009.900024|1153.579956|\n",
            "|2019-07-17|254.860001|1992.030029|1146.349976|\n",
            "|2019-07-18|253.539993|1977.900024|1146.329956|\n",
            "|2019-07-19|258.179993| 1964.52002|1130.099976|\n",
            "|2019-07-22|255.679993|1985.630005|1138.069946|\n",
            "|2019-07-23|260.170013| 1994.48999|1146.209961|\n",
            "|2019-07-24|264.880005|2000.810059|1137.810059|\n",
            "|2019-07-25|228.820007|1973.819946|1132.119995|\n",
            "|2019-07-26|228.039993|1943.050049|1250.410034|\n",
            "|2019-07-29|235.770004|1912.449951|1239.410034|\n",
            "|2019-07-30|242.259995|1898.530029|1225.140015|\n",
            "|2019-07-31|241.610001|1866.780029|1216.680054|\n",
            "|2019-08-01|233.850006|1855.319946| 1209.01001|\n",
            "|2019-08-02|234.339996| 1823.23999| 1193.98999|\n",
            "|2019-08-05|228.320007|1765.130005|1152.319946|\n",
            "|2019-08-06|    230.75|1787.829956|1169.949951|\n",
            "|2019-08-07|233.419998|1793.400024| 1173.98999|\n",
            "|2019-08-08|238.300003|1832.890015|1204.800049|\n",
            "|2019-08-09|235.009995|1807.579956| 1188.01001|\n",
            "+----------+----------+-----------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/pyspark/sql/dataframe.py:140: FutureWarning: Deprecated in 2.0, use createOrReplaceTempView instead.\n",
            "  FutureWarning\n"
          ]
        }
      ],
      "source": [
        "# Join all stock closing prices in order to compare\n",
        "\n",
        "joinclose=sqlContext.sql(\"SELECT tesla_stocks.Date, tesla_stocks.AdjClose as teslaclose, amazon_stocks.AdjClose as amazonclose, google_stocks.AdjClose as googleclose from tesla_stocks join google_stocks on tesla_stocks.Date = google_stocks.Date join amazon_stocks on tesla_stocks.Date = amazon_stocks.Date\").cache()\n",
        "\n",
        "joinclose.show()\n",
        "\n",
        "joinclose.registerTempTable(\"joinclose\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 40,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "S-1UBNlW4vaR",
        "outputId": "48f58007-190f-4166-e131-171bf26490d2"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+------------------+------------------+------------------+\n",
            "|  yr|        teslaclose|       amazonclose|       googleclose|\n",
            "+----+------------------+------------------+------------------+\n",
            "|2019|283.62126070588243|1800.6161329411755|1245.3833654621849|\n",
            "|2020| 761.8206739179104| 2236.414478798507|1362.8286906865671|\n",
            "+----+------------------+------------------+------------------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "sqlContext.sql(\"SELECT year(joinclose.Date) as yr, avg(joinclose.teslaclose) as teslaclose, avg(joinclose.amazonclose) as amazonclose, avg(joinclose.googleclose) as googleclose from joinclose group By year(joinclose.Date) order by year(joinclose.Date)\").show()\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## **Saving to Parquet File**"
      ],
      "metadata": {
        "id": "83iL2EJs_4FH"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 41,
      "metadata": {
        "id": "ztUztWRu4vaR"
      },
      "outputs": [],
      "source": [
        "joinclose.write.format(\"parquet\").save(\"joinstocks.parquet\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 42,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WE9clue74vaS",
        "outputId": "5d5d4ea2-6dbf-45e6-c454-c4145dbba21f"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+----------+-----------+-----------+\n",
            "|      Date|teslaclose|amazonclose|googleclose|\n",
            "+----------+----------+-----------+-----------+\n",
            "|2019-07-15|     253.5| 2020.98999|1150.339966|\n",
            "|2019-07-16|252.380005|2009.900024|1153.579956|\n",
            "|2019-07-17|254.860001|1992.030029|1146.349976|\n",
            "|2019-07-18|253.539993|1977.900024|1146.329956|\n",
            "|2019-07-19|258.179993| 1964.52002|1130.099976|\n",
            "|2019-07-22|255.679993|1985.630005|1138.069946|\n",
            "|2019-07-23|260.170013| 1994.48999|1146.209961|\n",
            "|2019-07-24|264.880005|2000.810059|1137.810059|\n",
            "|2019-07-25|228.820007|1973.819946|1132.119995|\n",
            "|2019-07-26|228.039993|1943.050049|1250.410034|\n",
            "|2019-07-29|235.770004|1912.449951|1239.410034|\n",
            "|2019-07-30|242.259995|1898.530029|1225.140015|\n",
            "|2019-07-31|241.610001|1866.780029|1216.680054|\n",
            "|2019-08-01|233.850006|1855.319946| 1209.01001|\n",
            "|2019-08-02|234.339996| 1823.23999| 1193.98999|\n",
            "|2019-08-05|228.320007|1765.130005|1152.319946|\n",
            "|2019-08-06|    230.75|1787.829956|1169.949951|\n",
            "|2019-08-07|233.419998|1793.400024| 1173.98999|\n",
            "|2019-08-08|238.300003|1832.890015|1204.800049|\n",
            "|2019-08-09|235.009995|1807.579956| 1188.01001|\n",
            "+----------+----------+-----------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "final_df = sqlContext.read.parquet(\"joinstocks.parquet\")\n",
        "final_df.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 43,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0vZokehz4vaS",
        "outputId": "19874293-1caf-4cf6-8739-878a1d357529"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- Date: string (nullable = true)\n",
            " |-- teslaclose: double (nullable = true)\n",
            " |-- amazonclose: double (nullable = true)\n",
            " |-- googleclose: double (nullable = true)\n",
            "\n"
          ]
        }
      ],
      "source": [
        "final_df.printSchema()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 44,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Hi1P3H-B4vaS",
        "outputId": "2200da79-1b79-4c3b-e939-1c01d3856f45"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+-----+------------------+------------------+------------------+\n",
            "|year|month|   avg(teslaclose)|  avg(amazonclose)|  avg(googleclose)|\n",
            "+----+-----+------------------+------------------+------------------+\n",
            "|2019|    7|248.43769253846148|1964.6846265384618|1170.1961483076923|\n",
            "|2019|    8|225.10272704545451|1793.6027220909093|1180.6868120454546|\n",
            "|2019|    9|237.26149830000003|     1799.12099615|     1220.83952035|\n",
            "|2019|   10| 266.3547840434783|1752.3317498695653|1232.7117442608696|\n",
            "|2019|   11|338.30000000000007|      1774.2939941|     1304.27899165|\n",
            "|2019|   12| 377.6947631904762|1785.7728446190476|1340.8676351904762|\n",
            "|2020|    1| 528.6590503809524|1884.2376128571425|1436.6537968571424|\n",
            "|2020|    2| 797.4468415263159|2066.1752672631574|1464.1105184736841|\n",
            "|2020|    3| 559.1013613181818|1872.3104358636365|1188.3940984545457|\n",
            "|2020|    4| 663.5985761428572|2228.7052408571426|1234.1404797142854|\n",
            "|2020|    5|      799.42549745|2394.1840209499996|1381.1137511999998|\n",
            "|2020|    6| 963.5422779545456|      2613.5454545|1431.0477184545452|\n",
            "|2020|    7|1378.7111273333333| 3053.100016222222|1496.0299885555555|\n",
            "+----+-----+------------------+------------------+------------------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Average of each by month\n",
        "final_df.select(year(\"Date\").alias(\"year\"), \n",
        "                 month(\"Date\").alias(\"month\"), \n",
        "                 \"teslaclose\", \"amazonclose\", \n",
        "                 \"googleclose\").groupby(\"year\", \n",
        "                                        \"month\").avg(\"teslaclose\", \n",
        "                                                     \"amazonclose\", \n",
        "                                                     \"googleclose\").sort(\"year\", \n",
        "                                                                         \"month\").show()\n",
        "                "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 45,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_Ins2IdX4vaS",
        "outputId": "b544a782-08b3-4ed5-b086-2f15416d9f64"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "== Physical Plan ==\n",
            "AdaptiveSparkPlan isFinalPlan=false\n",
            "+- Sort [year#1048 ASC NULLS FIRST, month#1049 ASC NULLS FIRST], true, 0\n",
            "   +- Exchange rangepartitioning(year#1048 ASC NULLS FIRST, month#1049 ASC NULLS FIRST, 200), ENSURE_REQUIREMENTS, [id=#721]\n",
            "      +- HashAggregate(keys=[year#1048, month#1049], functions=[avg(teslaclose#956), avg(amazonclose#957), avg(googleclose#958)])\n",
            "         +- Exchange hashpartitioning(year#1048, month#1049, 200), ENSURE_REQUIREMENTS, [id=#718]\n",
            "            +- HashAggregate(keys=[year#1048, month#1049], functions=[partial_avg(teslaclose#956), partial_avg(amazonclose#957), partial_avg(googleclose#958)])\n",
            "               +- Project [year(cast(Date#955 as date)) AS year#1048, month(cast(Date#955 as date)) AS month#1049, teslaclose#956, amazonclose#957, googleclose#958]\n",
            "                  +- FileScan parquet [Date#955,teslaclose#956,amazonclose#957,googleclose#958] Batched: true, DataFilters: [], Format: Parquet, Location: InMemoryFileIndex(1 paths)[file:/content/joinstocks.parquet], PartitionFilters: [], PushedFilters: [], ReadSchema: struct<Date:string,teslaclose:double,amazonclose:double,googleclose:double>\n",
            "\n",
            "\n"
          ]
        }
      ],
      "source": [
        "final_df.select(year(\"Date\").alias(\"year\"), \n",
        "                 month(\"Date\").alias(\"month\"), \n",
        "                 \"teslaclose\", \"amazonclose\", \n",
        "                 \"googleclose\").groupby(\"year\", \n",
        "                                        \"month\").avg(\"teslaclose\", \n",
        "                                                     \"amazonclose\", \n",
        "                                                     \"googleclose\").sort(\"year\", \n",
        "                                                                         \"month\").explain()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "5etd5o8r4vaS"
      },
      "outputs": [],
      "source": [
        ""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": true,
        "id": "vgPUFTiu4vaT"
      },
      "outputs": [],
      "source": [
        ""
      ]
    }
  ],
  "metadata": {
    "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.4"
    },
    "colab": {
      "name": "SparkSQL.ipynb",
      "provenance": [],
      "collapsed_sections": []
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}