Pyspark4.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Untitled3.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 416
        },
        "id": "yg_a5yywQCZc",
        "outputId": "d0038ee6-2c88-4385-8768-bc4432b53c62"
      },
      "source": [
        "!apt-get update\r\n",
        "!apt-get install openjdk-8-jdk-headless -qq > /dev/null\r\n",
        "!wget -q htt  https://archive.apache.org/dist/spark/spark-2.3.1/spark-2.3.1-bin-hadoop2.7.tgz\r\n",
        "!tar xf spark-2.3.1-bin-hadoop2.7.tgz\r\n",
        "!pip install -q findspark\r\n",
        "\r\n",
        "import os\r\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\r\n",
        "os.environ[\"SPARK_HOME\"] = \"/content/spark-2.3.1-bin-hadoop2.7\"\r\n",
        "\r\n",
        "import findspark\r\n",
        "findspark.init()\r\n",
        "from pyspark.sql import SparkSession\r\n",
        "spark = SparkSession.builder.getOrCreate()\r\n",
        "spark"
      ],
      "execution_count": 48,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\r0% [Working]\r            \rHit:1 http://security.ubuntu.com/ubuntu bionic-security InRelease\n",
            "\r0% [Connecting to archive.ubuntu.com (91.189.88.142)] [Connected to cloud.r-pro\r0% [1 InRelease gpgv 88.7 kB] [Connecting to archive.ubuntu.com (91.189.88.142)\r                                                                               \rIgn:2 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "\r0% [1 InRelease gpgv 88.7 kB] [Waiting for headers] [Waiting for headers] [Conn\r                                                                               \rHit:3 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease\n",
            "\r0% [1 InRelease gpgv 88.7 kB] [Waiting for headers] [Connecting to ppa.launchpa\r                                                                               \rHit:4 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Ign:5 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:6 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release\n",
            "Hit:7 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Hit:8 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease\n",
            "Hit:9 http://archive.ubuntu.com/ubuntu bionic-updates InRelease\n",
            "Hit:10 http://archive.ubuntu.com/ubuntu bionic-backports InRelease\n",
            "Hit:11 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Reading package lists... Done\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "            <div>\n",
              "                <p><b>SparkSession - in-memory</b></p>\n",
              "                \n",
              "        <div>\n",
              "            <p><b>SparkContext</b></p>\n",
              "\n",
              "            <p><a href=\"http://6928fdb9d9ba:4040\">Spark UI</a></p>\n",
              "\n",
              "            <dl>\n",
              "              <dt>Version</dt>\n",
              "                <dd><code>v2.3.1</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",
              "        \n",
              "            </div>\n",
              "        "
            ],
            "text/plain": [
              "<pyspark.sql.session.SparkSession at 0x7f8abd11ae10>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 48
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ni57CZ4tuWRd"
      },
      "source": [
        "# **Show the Data**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "crlIIzVpRCTM",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "9c9c9383-82e0-4073-962c-6351ea1270e1"
      },
      "source": [
        "df=spark.read.format(\"csv\").option(\"header\",\"true\").option(\"inferschema\",\"true\").load(\"/content/Walmart.csv\");\r\n",
        "df.show()"
      ],
      "execution_count": 49,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+---------+---------+---------+---------+--------+---------+\n",
            "|      Date|     Open|     High|      Low|    Close|  Volume|Adj Close|\n",
            "+----------+---------+---------+---------+---------+--------+---------+\n",
            "|03-01-2012|59.970001|61.060001|59.869999|60.330002|12668800|52.619235|\n",
            "|04-01-2012|60.209999|60.349998|59.470001|59.709999| 9593300|52.078475|\n",
            "|05-01-2012|59.349998|59.619999|58.369999|59.419998|12768200|51.825539|\n",
            "|06-01-2012|59.419998|59.450001|58.869999|     59.0| 8069400| 51.45922|\n",
            "|09-01-2012|59.029999|59.549999|58.919998|    59.18| 6679300|51.616215|\n",
            "|10-01-2012|    59.43|59.709999|    58.98|59.040001| 6907300|51.494109|\n",
            "|11-01-2012|59.060001|59.529999|59.040001|59.400002| 6365600|51.808098|\n",
            "|12-01-2012|59.790001|     60.0|59.400002|     59.5| 7236400|51.895316|\n",
            "|13-01-2012|    59.18|59.610001|59.009998|59.540001| 7729300|51.930204|\n",
            "|17-01-2012|59.869999|60.110001|    59.52|59.849998| 8500000|52.200581|\n",
            "|18-01-2012|59.790001|60.029999|59.650002|60.009998| 5911400|52.340131|\n",
            "|19-01-2012|    59.93|    60.73|    59.75|60.610001| 9234600|52.863447|\n",
            "|20-01-2012|    60.75|    61.25|60.669998|61.009998|10378800|53.212321|\n",
            "|23-01-2012|60.810001|    60.98|60.509998|    60.91| 7134100|53.125104|\n",
            "|24-01-2012|    60.75|     62.0|    60.75|61.389999| 7362800|53.543754|\n",
            "|25-01-2012|    61.18|61.610001|61.040001|61.470001| 5915800|53.613531|\n",
            "|26-01-2012|61.799999|    61.84|    60.77|60.970001| 7436200|53.177436|\n",
            "|27-01-2012|60.860001|61.119999|60.540001|60.709999| 6287300|52.950665|\n",
            "|30-01-2012|60.470001|    61.32|60.349998|61.299999| 7636900|53.465257|\n",
            "|31-01-2012|61.529999|    61.57|60.580002|61.360001| 9761500| 53.51759|\n",
            "+----------+---------+---------+---------+---------+--------+---------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_QSNEu9tu9Iz"
      },
      "source": [
        "# Spark has understood the datatypes by itself "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "T3P-j_qztz8q",
        "outputId": "3756573c-7fb4-49e0-e0ca-31dda7b8c187"
      },
      "source": [
        "df.printSchema()"
      ],
      "execution_count": 50,
      "outputs": [
        {
          "output_type": "stream",
          "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",
            " |-- Volume: integer (nullable = true)\n",
            " |-- Adj Close: double (nullable = true)\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cjA7dBj_viTa",
        "outputId": "3c7b9d06-b81c-4501-8c02-935c78cae1ae"
      },
      "source": [
        "df.columns "
      ],
      "execution_count": 51,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 51
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vS2nHHi6xy6I",
        "outputId": "3921245b-a2db-4568-a208-920645e1b169"
      },
      "source": [
        "len(df.columns)"
      ],
      "execution_count": 52,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "7"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 52
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "VglOcwVduDzs",
        "outputId": "210f7918-ad3d-42cd-844a-e4cae86c1d78"
      },
      "source": [
        "df.describe()"
      ],
      "execution_count": 53,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[summary: string, Date: string, Open: string, High: string, Low: string, Close: string, Volume: string, Adj Close: string]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 53
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jV15zVzkvUB6",
        "outputId": "46936352-6947-47a8-ecf3-10c88e25d44d"
      },
      "source": [
        "df.describe().show()"
      ],
      "execution_count": 54,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------+----------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+\n",
            "|summary|      Date|             Open|             High|              Low|            Close|           Volume|        Adj Close|\n",
            "+-------+----------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+\n",
            "|  count|      1258|             1258|             1258|             1258|             1258|             1258|             1258|\n",
            "|   mean|      null|72.35785375357709|72.83938807631165| 71.9186009594594|72.38844998012726|8222093.481717011|67.23883848728146|\n",
            "| stddev|      null| 6.76809024470826|6.768186808159218|6.744075756255496| 6.75685916373299|  4519780.8431556|6.722609449996858|\n",
            "|    min|01-02-2012|        56.389999|        57.060001|        56.299999|        56.419998|          2094900|        50.363689|\n",
            "|    max|31-12-2015|        90.800003|        90.970001|            89.25|        90.470001|         80898100|        84.914216|\n",
            "+-------+----------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8MgFGm6DvvWJ",
        "outputId": "e5e6f8e4-44c6-48cc-a5d4-0145100d750b"
      },
      "source": [
        "df['Date']"
      ],
      "execution_count": 55,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Column<b'Date'>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 55
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "CRWmr512wTS9",
        "outputId": "02004e7c-42e1-4340-ca98-81371c770c53"
      },
      "source": [
        "type(df['Date'])"
      ],
      "execution_count": 56,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "pyspark.sql.column.Column"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 56
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3VNXMgK9wXO2",
        "outputId": "bba254ba-bf15-4106-c9c1-666ddf05d823"
      },
      "source": [
        "df.select('Date','Open').show()"
      ],
      "execution_count": 57,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+---------+\n",
            "|      Date|     Open|\n",
            "+----------+---------+\n",
            "|03-01-2012|59.970001|\n",
            "|04-01-2012|60.209999|\n",
            "|05-01-2012|59.349998|\n",
            "|06-01-2012|59.419998|\n",
            "|09-01-2012|59.029999|\n",
            "|10-01-2012|    59.43|\n",
            "|11-01-2012|59.060001|\n",
            "|12-01-2012|59.790001|\n",
            "|13-01-2012|    59.18|\n",
            "|17-01-2012|59.869999|\n",
            "|18-01-2012|59.790001|\n",
            "|19-01-2012|    59.93|\n",
            "|20-01-2012|    60.75|\n",
            "|23-01-2012|60.810001|\n",
            "|24-01-2012|    60.75|\n",
            "|25-01-2012|    61.18|\n",
            "|26-01-2012|61.799999|\n",
            "|27-01-2012|60.860001|\n",
            "|30-01-2012|60.470001|\n",
            "|31-01-2012|61.529999|\n",
            "+----------+---------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AeYmKqJZ9hDP"
      },
      "source": [
        "# **If you want to select multiple columns , you can pass them via list also**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "T2q2nWLo9gZf",
        "outputId": "338af34e-4523-43d6-efa7-fd4759cff4df"
      },
      "source": [
        "df.select(['Date','Open']).show()"
      ],
      "execution_count": 58,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+---------+\n",
            "|      Date|     Open|\n",
            "+----------+---------+\n",
            "|03-01-2012|59.970001|\n",
            "|04-01-2012|60.209999|\n",
            "|05-01-2012|59.349998|\n",
            "|06-01-2012|59.419998|\n",
            "|09-01-2012|59.029999|\n",
            "|10-01-2012|    59.43|\n",
            "|11-01-2012|59.060001|\n",
            "|12-01-2012|59.790001|\n",
            "|13-01-2012|    59.18|\n",
            "|17-01-2012|59.869999|\n",
            "|18-01-2012|59.790001|\n",
            "|19-01-2012|    59.93|\n",
            "|20-01-2012|    60.75|\n",
            "|23-01-2012|60.810001|\n",
            "|24-01-2012|    60.75|\n",
            "|25-01-2012|    61.18|\n",
            "|26-01-2012|61.799999|\n",
            "|27-01-2012|60.860001|\n",
            "|30-01-2012|60.470001|\n",
            "|31-01-2012|61.529999|\n",
            "+----------+---------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "R2P1-qZtwZok",
        "outputId": "aa4c5355-a34c-4904-95d7-89a87cedbe4d"
      },
      "source": [
        "type(df.select('Date'))"
      ],
      "execution_count": 59,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "pyspark.sql.dataframe.DataFrame"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 59
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "foXCOsu6wmfd",
        "outputId": "fb55ebda-00b9-4549-81f9-ceb09a7f5694"
      },
      "source": [
        "df.head(2)"
      ],
      "execution_count": 60,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[Row(Date='03-01-2012', Open=59.970001, High=61.060001, Low=59.869999, Close=60.330002, Volume=12668800, Adj Close=52.619235),\n",
              " Row(Date='04-01-2012', Open=60.209999, High=60.349998, Low=59.470001, Close=59.709999, Volume=9593300, Adj Close=52.078475)]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 60
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "M83WuqWBwrXS",
        "outputId": "2194989d-1926-4a9f-8e6d-e133604842ff"
      },
      "source": [
        "type(df.head(2)[0])"
      ],
      "execution_count": 61,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "pyspark.sql.types.Row"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 61
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BwAqRcxf_upo"
      },
      "source": [
        "# **Add new Column**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rPfNUibKCkPB",
        "outputId": "bded95ad-a931-4e41-ece4-f2e0aa71c7ae"
      },
      "source": [
        "ms=df.withColumn('Copy_Date',df['Date']);\r\n",
        "ms.show()"
      ],
      "execution_count": 62,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+---------+---------+---------+---------+--------+---------+----------+\n",
            "|      Date|     Open|     High|      Low|    Close|  Volume|Adj Close| Copy_Date|\n",
            "+----------+---------+---------+---------+---------+--------+---------+----------+\n",
            "|03-01-2012|59.970001|61.060001|59.869999|60.330002|12668800|52.619235|03-01-2012|\n",
            "|04-01-2012|60.209999|60.349998|59.470001|59.709999| 9593300|52.078475|04-01-2012|\n",
            "|05-01-2012|59.349998|59.619999|58.369999|59.419998|12768200|51.825539|05-01-2012|\n",
            "|06-01-2012|59.419998|59.450001|58.869999|     59.0| 8069400| 51.45922|06-01-2012|\n",
            "|09-01-2012|59.029999|59.549999|58.919998|    59.18| 6679300|51.616215|09-01-2012|\n",
            "|10-01-2012|    59.43|59.709999|    58.98|59.040001| 6907300|51.494109|10-01-2012|\n",
            "|11-01-2012|59.060001|59.529999|59.040001|59.400002| 6365600|51.808098|11-01-2012|\n",
            "|12-01-2012|59.790001|     60.0|59.400002|     59.5| 7236400|51.895316|12-01-2012|\n",
            "|13-01-2012|    59.18|59.610001|59.009998|59.540001| 7729300|51.930204|13-01-2012|\n",
            "|17-01-2012|59.869999|60.110001|    59.52|59.849998| 8500000|52.200581|17-01-2012|\n",
            "|18-01-2012|59.790001|60.029999|59.650002|60.009998| 5911400|52.340131|18-01-2012|\n",
            "|19-01-2012|    59.93|    60.73|    59.75|60.610001| 9234600|52.863447|19-01-2012|\n",
            "|20-01-2012|    60.75|    61.25|60.669998|61.009998|10378800|53.212321|20-01-2012|\n",
            "|23-01-2012|60.810001|    60.98|60.509998|    60.91| 7134100|53.125104|23-01-2012|\n",
            "|24-01-2012|    60.75|     62.0|    60.75|61.389999| 7362800|53.543754|24-01-2012|\n",
            "|25-01-2012|    61.18|61.610001|61.040001|61.470001| 5915800|53.613531|25-01-2012|\n",
            "|26-01-2012|61.799999|    61.84|    60.77|60.970001| 7436200|53.177436|26-01-2012|\n",
            "|27-01-2012|60.860001|61.119999|60.540001|60.709999| 6287300|52.950665|27-01-2012|\n",
            "|30-01-2012|60.470001|    61.32|60.349998|61.299999| 7636900|53.465257|30-01-2012|\n",
            "|31-01-2012|61.529999|    61.57|60.580002|61.360001| 9761500| 53.51759|31-01-2012|\n",
            "+----------+---------+---------+---------+---------+--------+---------+----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "j4hVG1XzS7vx"
      },
      "source": [
        "# **Add Multiple Columns at a time**\r\n",
        "\r\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "lDsEx4BvS4ym",
        "outputId": "9797624a-86fa-48df-e76e-2f8c3ff86a50"
      },
      "source": [
        "ms=df.withColumn('Copy_Date',df['Date']).withColumn('Double_Open',df['Open']*2).withColumn('Half_High',df['High']/2);\r\n",
        "ms.show()"
      ],
      "execution_count": 63,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+---------+---------+---------+---------+--------+---------+----------+-----------+----------+\n",
            "|      Date|     Open|     High|      Low|    Close|  Volume|Adj Close| Copy_Date|Double_Open| Half_High|\n",
            "+----------+---------+---------+---------+---------+--------+---------+----------+-----------+----------+\n",
            "|03-01-2012|59.970001|61.060001|59.869999|60.330002|12668800|52.619235|03-01-2012| 119.940002|30.5300005|\n",
            "|04-01-2012|60.209999|60.349998|59.470001|59.709999| 9593300|52.078475|04-01-2012| 120.419998| 30.174999|\n",
            "|05-01-2012|59.349998|59.619999|58.369999|59.419998|12768200|51.825539|05-01-2012| 118.699996|29.8099995|\n",
            "|06-01-2012|59.419998|59.450001|58.869999|     59.0| 8069400| 51.45922|06-01-2012| 118.839996|29.7250005|\n",
            "|09-01-2012|59.029999|59.549999|58.919998|    59.18| 6679300|51.616215|09-01-2012| 118.059998|29.7749995|\n",
            "|10-01-2012|    59.43|59.709999|    58.98|59.040001| 6907300|51.494109|10-01-2012|     118.86|29.8549995|\n",
            "|11-01-2012|59.060001|59.529999|59.040001|59.400002| 6365600|51.808098|11-01-2012| 118.120002|29.7649995|\n",
            "|12-01-2012|59.790001|     60.0|59.400002|     59.5| 7236400|51.895316|12-01-2012| 119.580002|      30.0|\n",
            "|13-01-2012|    59.18|59.610001|59.009998|59.540001| 7729300|51.930204|13-01-2012|     118.36|29.8050005|\n",
            "|17-01-2012|59.869999|60.110001|    59.52|59.849998| 8500000|52.200581|17-01-2012| 119.739998|30.0550005|\n",
            "|18-01-2012|59.790001|60.029999|59.650002|60.009998| 5911400|52.340131|18-01-2012| 119.580002|30.0149995|\n",
            "|19-01-2012|    59.93|    60.73|    59.75|60.610001| 9234600|52.863447|19-01-2012|     119.86|    30.365|\n",
            "|20-01-2012|    60.75|    61.25|60.669998|61.009998|10378800|53.212321|20-01-2012|      121.5|    30.625|\n",
            "|23-01-2012|60.810001|    60.98|60.509998|    60.91| 7134100|53.125104|23-01-2012| 121.620002|     30.49|\n",
            "|24-01-2012|    60.75|     62.0|    60.75|61.389999| 7362800|53.543754|24-01-2012|      121.5|      31.0|\n",
            "|25-01-2012|    61.18|61.610001|61.040001|61.470001| 5915800|53.613531|25-01-2012|     122.36|30.8050005|\n",
            "|26-01-2012|61.799999|    61.84|    60.77|60.970001| 7436200|53.177436|26-01-2012| 123.599998|     30.92|\n",
            "|27-01-2012|60.860001|61.119999|60.540001|60.709999| 6287300|52.950665|27-01-2012| 121.720002|30.5599995|\n",
            "|30-01-2012|60.470001|    61.32|60.349998|61.299999| 7636900|53.465257|30-01-2012| 120.940002|     30.66|\n",
            "|31-01-2012|61.529999|    61.57|60.580002|61.360001| 9761500| 53.51759|31-01-2012| 123.059998|    30.785|\n",
            "+----------+---------+---------+---------+---------+--------+---------+----------+-----------+----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i4HfnIJeFnUt"
      },
      "source": [
        "# **Rename Column**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bAvck-O0Dk_S",
        "outputId": "93c69397-88de-4c15-aae1-a4596c2433cb"
      },
      "source": [
        "hs=ms.withColumnRenamed(\"Copy_Date\",\"Repeated_Date\");\r\n",
        "hs.show()"
      ],
      "execution_count": 64,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+---------+---------+---------+---------+--------+---------+-------------+-----------+----------+\n",
            "|      Date|     Open|     High|      Low|    Close|  Volume|Adj Close|Repeated_Date|Double_Open| Half_High|\n",
            "+----------+---------+---------+---------+---------+--------+---------+-------------+-----------+----------+\n",
            "|03-01-2012|59.970001|61.060001|59.869999|60.330002|12668800|52.619235|   03-01-2012| 119.940002|30.5300005|\n",
            "|04-01-2012|60.209999|60.349998|59.470001|59.709999| 9593300|52.078475|   04-01-2012| 120.419998| 30.174999|\n",
            "|05-01-2012|59.349998|59.619999|58.369999|59.419998|12768200|51.825539|   05-01-2012| 118.699996|29.8099995|\n",
            "|06-01-2012|59.419998|59.450001|58.869999|     59.0| 8069400| 51.45922|   06-01-2012| 118.839996|29.7250005|\n",
            "|09-01-2012|59.029999|59.549999|58.919998|    59.18| 6679300|51.616215|   09-01-2012| 118.059998|29.7749995|\n",
            "|10-01-2012|    59.43|59.709999|    58.98|59.040001| 6907300|51.494109|   10-01-2012|     118.86|29.8549995|\n",
            "|11-01-2012|59.060001|59.529999|59.040001|59.400002| 6365600|51.808098|   11-01-2012| 118.120002|29.7649995|\n",
            "|12-01-2012|59.790001|     60.0|59.400002|     59.5| 7236400|51.895316|   12-01-2012| 119.580002|      30.0|\n",
            "|13-01-2012|    59.18|59.610001|59.009998|59.540001| 7729300|51.930204|   13-01-2012|     118.36|29.8050005|\n",
            "|17-01-2012|59.869999|60.110001|    59.52|59.849998| 8500000|52.200581|   17-01-2012| 119.739998|30.0550005|\n",
            "|18-01-2012|59.790001|60.029999|59.650002|60.009998| 5911400|52.340131|   18-01-2012| 119.580002|30.0149995|\n",
            "|19-01-2012|    59.93|    60.73|    59.75|60.610001| 9234600|52.863447|   19-01-2012|     119.86|    30.365|\n",
            "|20-01-2012|    60.75|    61.25|60.669998|61.009998|10378800|53.212321|   20-01-2012|      121.5|    30.625|\n",
            "|23-01-2012|60.810001|    60.98|60.509998|    60.91| 7134100|53.125104|   23-01-2012| 121.620002|     30.49|\n",
            "|24-01-2012|    60.75|     62.0|    60.75|61.389999| 7362800|53.543754|   24-01-2012|      121.5|      31.0|\n",
            "|25-01-2012|    61.18|61.610001|61.040001|61.470001| 5915800|53.613531|   25-01-2012|     122.36|30.8050005|\n",
            "|26-01-2012|61.799999|    61.84|    60.77|60.970001| 7436200|53.177436|   26-01-2012| 123.599998|     30.92|\n",
            "|27-01-2012|60.860001|61.119999|60.540001|60.709999| 6287300|52.950665|   27-01-2012| 121.720002|30.5599995|\n",
            "|30-01-2012|60.470001|    61.32|60.349998|61.299999| 7636900|53.465257|   30-01-2012| 120.940002|     30.66|\n",
            "|31-01-2012|61.529999|    61.57|60.580002|61.360001| 9761500| 53.51759|   31-01-2012| 123.059998|    30.785|\n",
            "+----------+---------+---------+---------+---------+--------+---------+-------------+-----------+----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4PdAQgGRTdR6"
      },
      "source": [
        "## **Rename Multiple Columns at a time**\r\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "88y0sgI2TceX",
        "outputId": "3073fe69-ce2d-44cf-94ea-724a8c16b74a"
      },
      "source": [
        "hs=ms.withColumnRenamed(\"Copy_Date\",\"Repeated_Date\").withColumnRenamed(\"Adj Close\",\"Close\");\r\n",
        "hs.show()"
      ],
      "execution_count": 65,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+---------+---------+---------+---------+--------+---------+-------------+-----------+----------+\n",
            "|      Date|     Open|     High|      Low|    Close|  Volume|    Close|Repeated_Date|Double_Open| Half_High|\n",
            "+----------+---------+---------+---------+---------+--------+---------+-------------+-----------+----------+\n",
            "|03-01-2012|59.970001|61.060001|59.869999|60.330002|12668800|52.619235|   03-01-2012| 119.940002|30.5300005|\n",
            "|04-01-2012|60.209999|60.349998|59.470001|59.709999| 9593300|52.078475|   04-01-2012| 120.419998| 30.174999|\n",
            "|05-01-2012|59.349998|59.619999|58.369999|59.419998|12768200|51.825539|   05-01-2012| 118.699996|29.8099995|\n",
            "|06-01-2012|59.419998|59.450001|58.869999|     59.0| 8069400| 51.45922|   06-01-2012| 118.839996|29.7250005|\n",
            "|09-01-2012|59.029999|59.549999|58.919998|    59.18| 6679300|51.616215|   09-01-2012| 118.059998|29.7749995|\n",
            "|10-01-2012|    59.43|59.709999|    58.98|59.040001| 6907300|51.494109|   10-01-2012|     118.86|29.8549995|\n",
            "|11-01-2012|59.060001|59.529999|59.040001|59.400002| 6365600|51.808098|   11-01-2012| 118.120002|29.7649995|\n",
            "|12-01-2012|59.790001|     60.0|59.400002|     59.5| 7236400|51.895316|   12-01-2012| 119.580002|      30.0|\n",
            "|13-01-2012|    59.18|59.610001|59.009998|59.540001| 7729300|51.930204|   13-01-2012|     118.36|29.8050005|\n",
            "|17-01-2012|59.869999|60.110001|    59.52|59.849998| 8500000|52.200581|   17-01-2012| 119.739998|30.0550005|\n",
            "|18-01-2012|59.790001|60.029999|59.650002|60.009998| 5911400|52.340131|   18-01-2012| 119.580002|30.0149995|\n",
            "|19-01-2012|    59.93|    60.73|    59.75|60.610001| 9234600|52.863447|   19-01-2012|     119.86|    30.365|\n",
            "|20-01-2012|    60.75|    61.25|60.669998|61.009998|10378800|53.212321|   20-01-2012|      121.5|    30.625|\n",
            "|23-01-2012|60.810001|    60.98|60.509998|    60.91| 7134100|53.125104|   23-01-2012| 121.620002|     30.49|\n",
            "|24-01-2012|    60.75|     62.0|    60.75|61.389999| 7362800|53.543754|   24-01-2012|      121.5|      31.0|\n",
            "|25-01-2012|    61.18|61.610001|61.040001|61.470001| 5915800|53.613531|   25-01-2012|     122.36|30.8050005|\n",
            "|26-01-2012|61.799999|    61.84|    60.77|60.970001| 7436200|53.177436|   26-01-2012| 123.599998|     30.92|\n",
            "|27-01-2012|60.860001|61.119999|60.540001|60.709999| 6287300|52.950665|   27-01-2012| 121.720002|30.5599995|\n",
            "|30-01-2012|60.470001|    61.32|60.349998|61.299999| 7636900|53.465257|   30-01-2012| 120.940002|     30.66|\n",
            "|31-01-2012|61.529999|    61.57|60.580002|61.360001| 9761500| 53.51759|   31-01-2012| 123.059998|    30.785|\n",
            "+----------+---------+---------+---------+---------+--------+---------+-------------+-----------+----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "P8so6yH1DrGO",
        "outputId": "815c2446-94de-43bf-bf69-1e2401a2ef81"
      },
      "source": [
        "ms.show(2)"
      ],
      "execution_count": 66,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+---------+---------+---------+---------+--------+---------+----------+-----------+----------+\n",
            "|      Date|     Open|     High|      Low|    Close|  Volume|Adj Close| Copy_Date|Double_Open| Half_High|\n",
            "+----------+---------+---------+---------+---------+--------+---------+----------+-----------+----------+\n",
            "|03-01-2012|59.970001|61.060001|59.869999|60.330002|12668800|52.619235|03-01-2012| 119.940002|30.5300005|\n",
            "|04-01-2012|60.209999|60.349998|59.470001|59.709999| 9593300|52.078475|04-01-2012| 120.419998| 30.174999|\n",
            "+----------+---------+---------+---------+---------+--------+---------+----------+-----------+----------+\n",
            "only showing top 2 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0s-Cs2pdFeZo"
      },
      "source": [
        "# **You can do Addition , Subtraction , Multiplication, Division etc on Columns**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "mBhjZxpGEYVo",
        "outputId": "e0fb3910-bc7e-4601-fba0-b8dda29422ea"
      },
      "source": [
        "ls=ms.withColumn(\"Double_Open\",ms['Open']-2);\r\n",
        "ls.show()"
      ],
      "execution_count": 67,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+---------+---------+---------+---------+--------+---------+----------+-----------+----------+\n",
            "|      Date|     Open|     High|      Low|    Close|  Volume|Adj Close| Copy_Date|Double_Open| Half_High|\n",
            "+----------+---------+---------+---------+---------+--------+---------+----------+-----------+----------+\n",
            "|03-01-2012|59.970001|61.060001|59.869999|60.330002|12668800|52.619235|03-01-2012|  57.970001|30.5300005|\n",
            "|04-01-2012|60.209999|60.349998|59.470001|59.709999| 9593300|52.078475|04-01-2012|  58.209999| 30.174999|\n",
            "|05-01-2012|59.349998|59.619999|58.369999|59.419998|12768200|51.825539|05-01-2012|  57.349998|29.8099995|\n",
            "|06-01-2012|59.419998|59.450001|58.869999|     59.0| 8069400| 51.45922|06-01-2012|  57.419998|29.7250005|\n",
            "|09-01-2012|59.029999|59.549999|58.919998|    59.18| 6679300|51.616215|09-01-2012|  57.029999|29.7749995|\n",
            "|10-01-2012|    59.43|59.709999|    58.98|59.040001| 6907300|51.494109|10-01-2012|      57.43|29.8549995|\n",
            "|11-01-2012|59.060001|59.529999|59.040001|59.400002| 6365600|51.808098|11-01-2012|  57.060001|29.7649995|\n",
            "|12-01-2012|59.790001|     60.0|59.400002|     59.5| 7236400|51.895316|12-01-2012|  57.790001|      30.0|\n",
            "|13-01-2012|    59.18|59.610001|59.009998|59.540001| 7729300|51.930204|13-01-2012|      57.18|29.8050005|\n",
            "|17-01-2012|59.869999|60.110001|    59.52|59.849998| 8500000|52.200581|17-01-2012|  57.869999|30.0550005|\n",
            "|18-01-2012|59.790001|60.029999|59.650002|60.009998| 5911400|52.340131|18-01-2012|  57.790001|30.0149995|\n",
            "|19-01-2012|    59.93|    60.73|    59.75|60.610001| 9234600|52.863447|19-01-2012|      57.93|    30.365|\n",
            "|20-01-2012|    60.75|    61.25|60.669998|61.009998|10378800|53.212321|20-01-2012|      58.75|    30.625|\n",
            "|23-01-2012|60.810001|    60.98|60.509998|    60.91| 7134100|53.125104|23-01-2012|  58.810001|     30.49|\n",
            "|24-01-2012|    60.75|     62.0|    60.75|61.389999| 7362800|53.543754|24-01-2012|      58.75|      31.0|\n",
            "|25-01-2012|    61.18|61.610001|61.040001|61.470001| 5915800|53.613531|25-01-2012|      59.18|30.8050005|\n",
            "|26-01-2012|61.799999|    61.84|    60.77|60.970001| 7436200|53.177436|26-01-2012|  59.799999|     30.92|\n",
            "|27-01-2012|60.860001|61.119999|60.540001|60.709999| 6287300|52.950665|27-01-2012|  58.860001|30.5599995|\n",
            "|30-01-2012|60.470001|    61.32|60.349998|61.299999| 7636900|53.465257|30-01-2012|  58.470001|     30.66|\n",
            "|31-01-2012|61.529999|    61.57|60.580002|61.360001| 9761500| 53.51759|31-01-2012|  59.529999|    30.785|\n",
            "+----------+---------+---------+---------+---------+--------+---------+----------+-----------+----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xo6tyOD0Faqt"
      },
      "source": [
        "# Working with **SQL**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Bkl7C-sCEnMX"
      },
      "source": [
        "ls.createOrReplaceTempView('Public');"
      ],
      "execution_count": 68,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4vjAF5xBE1xo",
        "outputId": "3e9b6fa6-7d85-4f7c-f4cd-c97c5b848381"
      },
      "source": [
        "lg=spark.sql(\"select * from Public where Date='03-01-2012'\");\r\n",
        "lg.show()"
      ],
      "execution_count": 69,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+---------+---------+---------+---------+--------+---------+----------+-----------+----------+\n",
            "|      Date|     Open|     High|      Low|    Close|  Volume|Adj Close| Copy_Date|Double_Open| Half_High|\n",
            "+----------+---------+---------+---------+---------+--------+---------+----------+-----------+----------+\n",
            "|03-01-2012|59.970001|61.060001|59.869999|60.330002|12668800|52.619235|03-01-2012|  57.970001|30.5300005|\n",
            "+----------+---------+---------+---------+---------+--------+---------+----------+-----------+----------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2ksoajDLpPV3"
      },
      "source": [
        "# **Filtering Data based on certain condition**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "B2J2qpV4nr4U",
        "outputId": "1bb4989e-1394-4b5e-ccd7-484975cedc9d"
      },
      "source": [
        "df.show(5)"
      ],
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+---------+---------+---------+---------+--------+---------+\n",
            "|      Date|     Open|     High|      Low|    Close|  Volume|Adj Close|\n",
            "+----------+---------+---------+---------+---------+--------+---------+\n",
            "|03-01-2012|59.970001|61.060001|59.869999|60.330002|12668800|52.619235|\n",
            "|04-01-2012|60.209999|60.349998|59.470001|59.709999| 9593300|52.078475|\n",
            "|05-01-2012|59.349998|59.619999|58.369999|59.419998|12768200|51.825539|\n",
            "|06-01-2012|59.419998|59.450001|58.869999|     59.0| 8069400| 51.45922|\n",
            "|09-01-2012|59.029999|59.549999|58.919998|    59.18| 6679300|51.616215|\n",
            "+----------+---------+---------+---------+---------+--------+---------+\n",
            "only showing top 5 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FeOw43dDt-8M",
        "outputId": "1f8200e5-7d56-414e-cc51-99ad447e3c09"
      },
      "source": [
        "df.filter(df['Close']<60).select(['Volume','Open','Close']).show()"
      ],
      "execution_count": 74,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------+---------+---------+\n",
            "|  Volume|     Open|    Close|\n",
            "+--------+---------+---------+\n",
            "| 9593300|60.209999|59.709999|\n",
            "|12768200|59.349998|59.419998|\n",
            "| 8069400|59.419998|     59.0|\n",
            "| 6679300|59.029999|    59.18|\n",
            "| 6907300|    59.43|59.040001|\n",
            "| 6365600|59.060001|59.400002|\n",
            "| 7236400|59.790001|     59.5|\n",
            "| 7729300|    59.18|59.540001|\n",
            "| 8500000|59.869999|59.849998|\n",
            "|28630200|59.580002|58.599998|\n",
            "|14880300|    58.59|58.540001|\n",
            "| 9925900|    58.75|58.790001|\n",
            "|12258800|58.700001|58.459999|\n",
            "|10761900|58.439999|    58.93|\n",
            "|11484400|    58.84|59.080002|\n",
            "|16283900|59.360001|    58.82|\n",
            "| 9848100|58.990002|59.009998|\n",
            "| 9651000|58.959999|59.400002|\n",
            "| 9057100|59.040001|58.970001|\n",
            "|14916900|59.110001|59.860001|\n",
            "+--------+---------+---------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Bp0jET0x04jU"
      },
      "source": [
        "**Filter using Multiple Condition -- & for and , | for or , ~ for not , if multiple condition present , put them inside ()**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ZA-7egBAvP_C",
        "outputId": "b47732d9-af73-4d89-aba1-c2394db89764"
      },
      "source": [
        "df.filter((df['Close']<80) & ~(df['Open']>70)).select(['Volume','Open','Close']).show()"
      ],
      "execution_count": 80,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------+---------+---------+\n",
            "|  Volume|     Open|    Close|\n",
            "+--------+---------+---------+\n",
            "|12668800|59.970001|60.330002|\n",
            "| 9593300|60.209999|59.709999|\n",
            "|12768200|59.349998|59.419998|\n",
            "| 8069400|59.419998|     59.0|\n",
            "| 6679300|59.029999|    59.18|\n",
            "| 6907300|    59.43|59.040001|\n",
            "| 6365600|59.060001|59.400002|\n",
            "| 7236400|59.790001|     59.5|\n",
            "| 7729300|    59.18|59.540001|\n",
            "| 8500000|59.869999|59.849998|\n",
            "| 5911400|59.790001|60.009998|\n",
            "| 9234600|    59.93|60.610001|\n",
            "|10378800|    60.75|61.009998|\n",
            "| 7134100|60.810001|    60.91|\n",
            "| 7362800|    60.75|61.389999|\n",
            "| 5915800|    61.18|61.470001|\n",
            "| 7436200|61.799999|60.970001|\n",
            "| 6287300|60.860001|60.709999|\n",
            "| 7636900|60.470001|61.299999|\n",
            "| 9761500|61.529999|61.360001|\n",
            "+--------+---------+---------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "duqXMjTS1GtQ"
      },
      "source": [
        "# **Equality Condition Checking**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "aGwc3x4Pv1Cw",
        "outputId": "73cc4ee6-e91a-47ac-ea21-2662f13459ef"
      },
      "source": [
        "rs=df.filter(df['Low']==59.869999);\r\n",
        "rs.show()"
      ],
      "execution_count": 82,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+---------+---------+---------+---------+--------+---------+\n",
            "|      Date|     Open|     High|      Low|    Close|  Volume|Adj Close|\n",
            "+----------+---------+---------+---------+---------+--------+---------+\n",
            "|03-01-2012|59.970001|61.060001|59.869999|60.330002|12668800|52.619235|\n",
            "|16-04-2012|59.939999|60.810001|59.869999|60.580002| 6844600| 53.19094|\n",
            "|27-11-2015|60.439999|60.689999|59.869999|59.889999| 5340900|57.295243|\n",
            "+----------+---------+---------+---------+---------+--------+---------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0L7xFnaP1Mk0"
      },
      "source": [
        "# **Collect the data as row object to work more on it**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BiAjAKPzwhs3"
      },
      "source": [
        "mg=rs.collect()#returns one list"
      ],
      "execution_count": 83,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GS5yT4_WwvN9"
      },
      "source": [
        "ls=mg[0]#take the first element of the list"
      ],
      "execution_count": 93,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ibhzEvgfxPt_",
        "outputId": "a9f96f26-2b80-4126-f2f3-5e49c5c23207"
      },
      "source": [
        "ls#it's a row object"
      ],
      "execution_count": 94,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Row(Date='03-01-2012', Open=59.970001, High=61.060001, Low=59.869999, Close=60.330002, Volume=12668800, Adj Close=52.619235)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 94
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ljeC0rusxRQr"
      },
      "source": [
        "ps=ls.asDict()#Convert to dictionary , so that we can extract data later"
      ],
      "execution_count": 95,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Un5yDwQpxUtq",
        "outputId": "b2ae35e2-2f7c-4871-ef19-bf2f545816ff"
      },
      "source": [
        "ps#print the dictionary"
      ],
      "execution_count": 96,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'Adj Close': 52.619235,\n",
              " 'Close': 60.330002,\n",
              " 'Date': '03-01-2012',\n",
              " 'High': 61.060001,\n",
              " 'Low': 59.869999,\n",
              " 'Open': 59.970001,\n",
              " 'Volume': 12668800}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 96
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "9Cx4nPB9xVTI",
        "outputId": "dc337254-ab67-4568-ee1a-984d618e7a0a"
      },
      "source": [
        "ps['Close']#Extract data from Dictionary as key value format"
      ],
      "execution_count": 97,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "60.330002"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 97
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "oNQ-7aOgVfsF",
        "outputId": "cea83dd8-f4c2-4a7f-c92a-e3d49fc9777c"
      },
      "source": [
        "df.show()"
      ],
      "execution_count": 117,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+---------+---------+---------+---------+--------+---------+\n",
            "|      Date|     Open|     High|      Low|    Close|  Volume|Adj Close|\n",
            "+----------+---------+---------+---------+---------+--------+---------+\n",
            "|03-01-2012|59.970001|61.060001|59.869999|60.330002|12668800|52.619235|\n",
            "|04-01-2012|60.209999|60.349998|59.470001|59.709999| 9593300|52.078475|\n",
            "|05-01-2012|59.349998|59.619999|58.369999|59.419998|12768200|51.825539|\n",
            "|06-01-2012|59.419998|59.450001|58.869999|     59.0| 8069400| 51.45922|\n",
            "|09-01-2012|59.029999|59.549999|58.919998|    59.18| 6679300|51.616215|\n",
            "|10-01-2012|    59.43|59.709999|    58.98|59.040001| 6907300|51.494109|\n",
            "|11-01-2012|59.060001|59.529999|59.040001|59.400002| 6365600|51.808098|\n",
            "|12-01-2012|59.790001|     60.0|59.400002|     59.5| 7236400|51.895316|\n",
            "|13-01-2012|    59.18|59.610001|59.009998|59.540001| 7729300|51.930204|\n",
            "|17-01-2012|59.869999|60.110001|    59.52|59.849998| 8500000|52.200581|\n",
            "|18-01-2012|59.790001|60.029999|59.650002|60.009998| 5911400|52.340131|\n",
            "|19-01-2012|    59.93|    60.73|    59.75|60.610001| 9234600|52.863447|\n",
            "|20-01-2012|    60.75|    61.25|60.669998|61.009998|10378800|53.212321|\n",
            "|23-01-2012|60.810001|    60.98|60.509998|    60.91| 7134100|53.125104|\n",
            "|24-01-2012|    60.75|     62.0|    60.75|61.389999| 7362800|53.543754|\n",
            "|25-01-2012|    61.18|61.610001|61.040001|61.470001| 5915800|53.613531|\n",
            "|26-01-2012|61.799999|    61.84|    60.77|60.970001| 7436200|53.177436|\n",
            "|27-01-2012|60.860001|61.119999|60.540001|60.709999| 6287300|52.950665|\n",
            "|30-01-2012|60.470001|    61.32|60.349998|61.299999| 7636900|53.465257|\n",
            "|31-01-2012|61.529999|    61.57|60.580002|61.360001| 9761500| 53.51759|\n",
            "+----------+---------+---------+---------+---------+--------+---------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "c4c5q-WAa-mH"
      },
      "source": [
        "# **DataFrame column to List**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "yBO-bo-jVjr2",
        "outputId": "20d61971-aee1-4f8a-cc41-49e2e18b3e2d"
      },
      "source": [
        "ls=df.select('Low');\r\n",
        "ls.show()"
      ],
      "execution_count": 118,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+---------+\n",
            "|      Low|\n",
            "+---------+\n",
            "|59.869999|\n",
            "|59.470001|\n",
            "|58.369999|\n",
            "|58.869999|\n",
            "|58.919998|\n",
            "|    58.98|\n",
            "|59.040001|\n",
            "|59.400002|\n",
            "|59.009998|\n",
            "|    59.52|\n",
            "|59.650002|\n",
            "|    59.75|\n",
            "|60.669998|\n",
            "|60.509998|\n",
            "|    60.75|\n",
            "|61.040001|\n",
            "|    60.77|\n",
            "|60.540001|\n",
            "|60.349998|\n",
            "|60.580002|\n",
            "+---------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PNEel13vV1KW",
        "outputId": "5eded5b0-5f84-4287-89f7-496cb244320e"
      },
      "source": [
        "type(ls)"
      ],
      "execution_count": 119,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "pyspark.sql.dataframe.DataFrame"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 119
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_EMZh_UOdLri"
      },
      "source": [
        "# **Convert the selected Column to List of Row objects**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xkWmLyO7V4ch"
      },
      "source": [
        "ms=ls.collect()"
      ],
      "execution_count": 120,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RXtP_g7edZ0b"
      },
      "source": [
        "# **Iterate in the list , convert each row to dictionary , take the value part , append in a list**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZpIzxDDrWAiF"
      },
      "source": [
        "mgs=[];\r\n",
        "for i in ms:\r\n",
        "  ps=i.asDict();\r\n",
        "  mgs.append(ps['Low']);"
      ],
      "execution_count": 122,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "L5M-jTQXdl2c"
      },
      "source": [
        "# **Now we can apply all the methods which are applicable on the list data structure to inspect the columns**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "XGi_Pr5HWXRT",
        "outputId": "f612ea13-a0f7-44a0-cffe-09bbcae9c1e5"
      },
      "source": [
        "max(mgs)"
      ],
      "execution_count": 129,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "89.25"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 129
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lx1fjY72du7q"
      },
      "source": [
        "# **We can verify the results using SQL query as well**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kKN8PVDvWY8G",
        "outputId": "6e253c67-44a1-448b-f565-91b7cde4ea19"
      },
      "source": [
        "spark.sql(\"select max(Low) from Public\").show()"
      ],
      "execution_count": 130,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------+\n",
            "|max(Low)|\n",
            "+--------+\n",
            "|   89.25|\n",
            "+--------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    }
  ]
}