PySpark_DataManipulation.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lihDEjAhyqca"
      },
      "source": [
        "# Spark 101 - Data Manipulation using Spark\n",
        "<hr size=\"5\"/>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "o9t08dFEeA9W"
      },
      "source": [
        "## **1. Running Spark in Colab**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e8dsXbdzzHDc"
      },
      "source": [
        "### 1.1 Initialize Spark"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "anLHk85lyQ5G",
        "outputId": "e1b1f057-ac20-4a87-a1bb-8e910fe3dd22"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Collecting pyspark\n",
            "  Downloading pyspark-3.4.1.tar.gz (310.8 MB)\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m310.8/310.8 MB\u001b[0m \u001b[31m3.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h  Preparing metadata (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "Requirement already satisfied: py4j==0.10.9.7 in /usr/local/lib/python3.10/dist-packages (from pyspark) (0.10.9.7)\n",
            "Building wheels for collected packages: pyspark\n",
            "  Building wheel for pyspark (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for pyspark: filename=pyspark-3.4.1-py2.py3-none-any.whl size=311285398 sha256=5377e2774d9b2079e8a446797edb3f0cd65734b9f8bdeb07ff2c99f7b9ac184b\n",
            "  Stored in directory: /root/.cache/pip/wheels/0d/77/a3/ff2f74cc9ab41f8f594dabf0579c2a7c6de920d584206e0834\n",
            "Successfully built pyspark\n",
            "Installing collected packages: pyspark\n",
            "Successfully installed pyspark-3.4.1\n"
          ]
        }
      ],
      "source": [
        "!pip install pyspark"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rt3m6PxECGIY",
        "outputId": "698a4c0b-6fc9-4178-fb0b-9df581684e60"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Collecting findspark\n",
            "  Downloading findspark-2.0.1-py2.py3-none-any.whl (4.4 kB)\n",
            "Installing collected packages: findspark\n",
            "Successfully installed findspark-2.0.1\n"
          ]
        }
      ],
      "source": [
        "!pip install findspark"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "gJ6HCddh0rBI"
      },
      "outputs": [],
      "source": [
        "import findspark\n",
        "findspark.init()\n",
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.master(\"local[*]\").getOrCreate()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "77fvmo9F0szJ",
        "outputId": "abdb4099-e584-451e-f3fc-4cc075531564"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+-----+\n",
            "|hello|\n",
            "+-----+\n",
            "|spark|\n",
            "+-----+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Check if spark is initialized\n",
        "df = spark.sql('''select 'Sucesso total, estamos online!' as hello''')\n",
        "df.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {
        "id": "QWbHuaova0PH"
      },
      "outputs": [],
      "source": [
        "# Import spark libraries\n",
        "from pyspark.sql import Row, DataFrame\n",
        "from pyspark.sql.types import StringType, StructType, StructField, IntegerType\n",
        "from pyspark.sql.functions import col, expr, lit, substring, concat, concat_ws, when, coalesce\n",
        "from pyspark.sql import functions as F # for more sql functions\n",
        "from functools import reduce"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tDLaLZjzzpWg"
      },
      "source": [
        "# Data manipulation using spark"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1Ql57n4Iz4kX",
        "outputId": "ed890508-2f5c-4de4-c1f7-c0f067821804"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "df.count  : 561\n",
            "df.col ct : 6\n",
            "df.columns: ['Bank Name', 'City', 'ST', 'CERT', 'Acquiring Institution', 'Closing Date']\n"
          ]
        }
      ],
      "source": [
        "df = spark.read.csv('banklist.csv', sep = ',', inferSchema = True, header = True)\n",
        "\n",
        "print('df.count  :', df.count())\n",
        "print('df.col ct :', len(df.columns))\n",
        "print('df.columns:', df.columns)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lviT9J99TMmr"
      },
      "source": [
        "### **3. Using SQL in spark**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Jce3V6YyT_tm",
        "outputId": "3b1360dc-bbf9-4584-f501-f66baf608816"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------------------------+-------------+------------+\n",
            "|Bank Name                       |City         |Closing Date|\n",
            "+--------------------------------+-------------+------------+\n",
            "|The First State Bank            |Barboursville|3-Apr-20    |\n",
            "|Ericson State Bank              |Ericson      |14-Feb-20   |\n",
            "|City National Bank of New Jersey|Newark       |1-Nov-19    |\n",
            "|Resolute Bank                   |Maumee       |25-Oct-19   |\n",
            "+--------------------------------+-------------+------------+\n",
            "only showing top 4 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df.createOrReplaceTempView(\"banklist\")\n",
        "\n",
        "df_check = spark.sql('''select `Bank Name`, City, `Closing Date` from banklist''')\n",
        "df_check.show(4, truncate=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "A_h4nezoVoMP"
      },
      "source": [
        "## **4 Dataframe Basic Operations**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NLoqnwVgtucp"
      },
      "source": [
        "### 4.1 Describe dataframe"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LJSOwS4Eturw",
        "outputId": "196643d7-35c5-4393-9ee0-95962684f637"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+-------+--------------------+-------+----+-----------------+---------------------+------------+\n",
            "|summary|           Bank Name|   City|  ST|             CERT|Acquiring Institution|Closing Date|\n",
            "+-------+--------------------+-------+----+-----------------+---------------------+------------+\n",
            "|  count|                 561|    561| 561|              561|                  561|         561|\n",
            "|   mean|                null|   null|null|31685.68449197861|                 null|        null|\n",
            "| stddev|                null|   null|null|16446.65659309965|                 null|        null|\n",
            "|    min|1st American Stat...|Acworth|  AL|               91|      1st United Bank|    1-Aug-08|\n",
            "|    max|               ebank|Wyoming|  WY|            58701|  Your Community Bank|    9-Sep-11|\n",
            "+-------+--------------------+-------+----+-----------------+---------------------+------------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df.describe().show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FwVXN5fZn3Jz",
        "outputId": "8a0fa666-647a-4939-934a-8460e06c5447"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+-------+-------+----+\n",
            "|summary|   City|  ST|\n",
            "+-------+-------+----+\n",
            "|  count|    561| 561|\n",
            "|   mean|   null|null|\n",
            "| stddev|   null|null|\n",
            "|    min|Acworth|  AL|\n",
            "|    max|Wyoming|  WY|\n",
            "+-------+-------+----+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df.describe('City', 'ST').show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RDht33CnhVL5"
      },
      "source": [
        "### 4.2 Counts, Columns and Schema"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "O63DzXi1VoaN",
        "outputId": "0961aac2-74f3-4a70-c281-4ed74d575053"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "df.count\t\t: 561\n",
            "df.columns\t: ['Bank Name', 'City', 'ST', 'CERT', 'Acquiring Institution', 'Closing Date']\n",
            "df dtypes\t: [('Bank Name', 'string'), ('City', 'string'), ('ST', 'string'), ('CERT', 'int'), ('Acquiring Institution', 'string'), ('Closing Date', 'string')]\n",
            "df schema 1: StructType([StructField('Bank Name', StringType(), True), StructField('City', StringType(), True), StructField('ST', StringType(), True), StructField('CERT', IntegerType(), True), StructField('Acquiring Institution', StringType(), True), StructField('Closing Date', StringType(), True)])\n"
          ]
        }
      ],
      "source": [
        "print('df.count\t\t:', df.count())\n",
        "print('df.columns\t:', df.columns)\n",
        "print('df dtypes\t:', df.dtypes)\n",
        "print('df schema 1:', df.schema)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "AeG5OcIq1vfg",
        "outputId": "97ffca11-b0e7-4887-c7ed-a8bd9e2ff5b2"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "df schema 1:\n",
            "root\n",
            " |-- Bank Name: string (nullable = true)\n",
            " |-- City: string (nullable = true)\n",
            " |-- ST: string (nullable = true)\n",
            " |-- CERT: integer (nullable = true)\n",
            " |-- Acquiring Institution: string (nullable = true)\n",
            " |-- Closing Date: string (nullable = true)\n",
            "\n"
          ]
        }
      ],
      "source": [
        "print('df schema 1:')\n",
        "df.printSchema()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OSHkkGVBhd-F"
      },
      "source": [
        "### 4.3 Remove Duplicates"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "CcqzJ65IV0Fa",
        "outputId": "3b77c35f-4010-4a6a-b409-c764a7000d2f"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "df.count\t\t: 561\n",
            "df.columns\t: ['Bank Name', 'City', 'ST', 'CERT', 'Acquiring Institution', 'Closing Date']\n"
          ]
        }
      ],
      "source": [
        "df = df.dropDuplicates()\n",
        "print('df.count\t\t:', df.count())\n",
        "print('df.columns\t:', df.columns)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "olIfm2KIicOn"
      },
      "source": [
        "### 4.4 Select specific columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3wBMsReAWSUF",
        "outputId": "2d75977f-77d8-4a3f-cb70-780a8ccfd05d"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------------+--------+\n",
            "|           Bank Name|    City|\n",
            "+--------------------+--------+\n",
            "| First Bank of Idaho| Ketchum|\n",
            "|Amcore Bank, Nati...|Rockford|\n",
            "+--------------------+--------+\n",
            "only showing top 2 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df2 = df.select(*['Bank Name', 'City'])\n",
        "df2.show(2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WMvFVdP_oK7W"
      },
      "source": [
        "### 4.5 Select multiple columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 19,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fS3tpP6qoLVu",
        "outputId": "73bcacce-8016-4ff8-bae0-0ce89be7f5cd"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+---------------------+--------+------------+--------------------+\n",
            "|Acquiring Institution|    City|Closing Date|           Bank Name|\n",
            "+---------------------+--------+------------+--------------------+\n",
            "|      U.S. Bank, N.A.| Ketchum|   24-Apr-09| First Bank of Idaho|\n",
            "|          Harris N.A.|Rockford|   23-Apr-10|Amcore Bank, Nati...|\n",
            "+---------------------+--------+------------+--------------------+\n",
            "only showing top 2 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "col_l = list(set(df.columns)  - {'CERT','ST'})\n",
        "df2 = df.select(*col_l)\n",
        "df2.show(2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Zfc5uWaoi_R2"
      },
      "source": [
        "### 4.6 Rename columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PdOkLYzlisvy",
        "outputId": "5172404f-8533-4a28-bb9c-4cdb0576280d"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------------+--------+-----+-----+---------------+------------+\n",
            "|           bank_name|    City|state| cert|acq_institution|closing_date|\n",
            "+--------------------+--------+-----+-----+---------------+------------+\n",
            "| First Bank of Idaho| Ketchum|   ID|34396|U.S. Bank, N.A.|   24-Apr-09|\n",
            "|Amcore Bank, Nati...|Rockford|   IL| 3735|    Harris N.A.|   23-Apr-10|\n",
            "+--------------------+--------+-----+-----+---------------+------------+\n",
            "only showing top 2 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df2 = df \\\n",
        "  .withColumnRenamed('Bank Name'            , 'bank_name') \\\n",
        "  .withColumnRenamed('Acquiring Institution', 'acq_institution') \\\n",
        "  .withColumnRenamed('Closing Date'         , 'closing_date') \\\n",
        "  .withColumnRenamed('ST'                   , 'state') \\\n",
        "  .withColumnRenamed('CERT'                 , 'cert') #\\\n",
        "\n",
        "df2.show(2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "veAUD18tl6oG"
      },
      "source": [
        "### 4.7 Rename columns using loop"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0gnKDBPjjpka",
        "outputId": "d72eb7fa-e2cc-48c2-c487-f5eff71b6982"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------------+--------+---+-----+---------------------+------------+\n",
            "|           Bank_Name|    City| ST| CERT|Acquiring_Institution|Closing_Date|\n",
            "+--------------------+--------+---+-----+---------------------+------------+\n",
            "| First Bank of Idaho| Ketchum| ID|34396|      U.S. Bank, N.A.|   24-Apr-09|\n",
            "|Amcore Bank, Nati...|Rockford| IL| 3735|          Harris N.A.|   23-Apr-10|\n",
            "+--------------------+--------+---+-----+---------------------+------------+\n",
            "only showing top 2 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "rename_expr = [col(column).alias(column.replace(' ', '_')) for column in df.columns]\n",
        "\n",
        "df2 = df.select(*rename_expr)\n",
        "df2.show(2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "momxuupJnqPd"
      },
      "source": [
        "### 4.8 Add columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "uDKwdHg5l_nm",
        "outputId": "523ec74f-d6c1-43d2-bd6c-a1ff1b7dde78"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------------+--------+---+-----+---------------------+------------+-----+\n",
            "|           Bank Name|    City| ST| CERT|Acquiring Institution|Closing Date|state|\n",
            "+--------------------+--------+---+-----+---------------------+------------+-----+\n",
            "| First Bank of Idaho| Ketchum| ID|34396|      U.S. Bank, N.A.|   24-Apr-09|   ID|\n",
            "|Amcore Bank, Nati...|Rockford| IL| 3735|          Harris N.A.|   23-Apr-10|   IL|\n",
            "+--------------------+--------+---+-----+---------------------+------------+-----+\n",
            "only showing top 2 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df2 = df.withColumn('state', col('ST'))\n",
        "df2.show(2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EjkXUUpvou7B"
      },
      "source": [
        "### 4.9 Add constant column"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0-gSou1GnpBd",
        "outputId": "50611507-a1ac-4799-f9dd-35582acfd4ea"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------------+--------+---+-----+---------------------+------------+-------+\n",
            "|           Bank Name|    City| ST| CERT|Acquiring Institution|Closing Date|country|\n",
            "+--------------------+--------+---+-----+---------------------+------------+-------+\n",
            "| First Bank of Idaho| Ketchum| ID|34396|      U.S. Bank, N.A.|   24-Apr-09|     US|\n",
            "|Amcore Bank, Nati...|Rockford| IL| 3735|          Harris N.A.|   23-Apr-10|     US|\n",
            "+--------------------+--------+---+-----+---------------------+------------+-------+\n",
            "only showing top 2 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df2 = df.withColumn('country', lit('US'))\n",
        "df2.show(2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wx7fAtmqZxSH"
      },
      "source": [
        "### 4.10 Drop columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 24,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fAKuWe7nZxmc",
        "outputId": "77278d54-efe0-4c4f-989f-ad3a4f701fea"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------------+--------+---+---------------------+------------+\n",
            "|           Bank Name|    City| ST|Acquiring Institution|Closing Date|\n",
            "+--------------------+--------+---+---------------------+------------+\n",
            "| First Bank of Idaho| Ketchum| ID|      U.S. Bank, N.A.|   24-Apr-09|\n",
            "|Amcore Bank, Nati...|Rockford| IL|          Harris N.A.|   23-Apr-10|\n",
            "+--------------------+--------+---+---------------------+------------+\n",
            "only showing top 2 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df2 = df.drop('CERT')\n",
        "df2.show(2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AG7tuEZpaGll"
      },
      "source": [
        "### 4.11 Drop multiple columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 25,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "56E1uJPQpZJH",
        "outputId": "8c3d1e2e-f984-44a5-d6fa-9aa0a013febf"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------------+--------+---------------------+------------+\n",
            "|           Bank Name|    City|Acquiring Institution|Closing Date|\n",
            "+--------------------+--------+---------------------+------------+\n",
            "| First Bank of Idaho| Ketchum|      U.S. Bank, N.A.|   24-Apr-09|\n",
            "|Amcore Bank, Nati...|Rockford|          Harris N.A.|   23-Apr-10|\n",
            "+--------------------+--------+---------------------+------------+\n",
            "only showing top 2 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df2 = df.drop(*['CERT','ST'])\n",
        "df2.show(2)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 26,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WuVEE3SqaFvC",
        "outputId": "9b4c70e2-8812-4282-fdc9-84c0dd435bf3"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------------+--------+---------------------+------------+\n",
            "|           Bank Name|    City|Acquiring Institution|Closing Date|\n",
            "+--------------------+--------+---------------------+------------+\n",
            "| First Bank of Idaho| Ketchum|      U.S. Bank, N.A.|   24-Apr-09|\n",
            "|Amcore Bank, Nati...|Rockford|          Harris N.A.|   23-Apr-10|\n",
            "+--------------------+--------+---------------------+------------+\n",
            "only showing top 2 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df2 = reduce(DataFrame.drop, ['CERT','ST'], df)\n",
        "df2.show(2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PhuwX3AwbRiu"
      },
      "source": [
        "### 4.12 Filter data"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 27,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "yeA5BOpJbD5O",
        "outputId": "95398d26-e396-4b64-fae1-feb8a3b56386"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "df.count  : 561\n",
            "df2.count : 4\n",
            "df3.count : 9\n",
            "df4.count : 73\n"
          ]
        }
      ],
      "source": [
        "# Equal to values\n",
        "df2 = df.where(df['ST'] == 'NE')\n",
        "\n",
        "# Between values\n",
        "df3 = df.where(df['CERT'].between('1000','2000'))\n",
        "\n",
        "# Is inside multiple values\n",
        "df4 = df.where(df['ST'].isin('NE','IL'))\n",
        "\n",
        "print('df.count  :', df.count())\n",
        "print('df2.count :', df2.count())\n",
        "print('df3.count :', df3.count())\n",
        "print('df4.count :', df4.count())\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 28,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "k0fL-hmR7XAU",
        "outputId": "0b6c1a97-fc04-48b9-b810-105bfd34df70"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\n",
            "df2 sample below\n",
            "+-------------------+---------+---+-----+---------------------+------------+\n",
            "|          Bank Name|     City| ST| CERT|Acquiring Institution|Closing Date|\n",
            "+-------------------+---------+---+-----+---------------------+------------+\n",
            "|       TierOne Bank|  Lincoln| NE|29341|   Great Western Bank|    4-Jun-10|\n",
            "|Sherman County Bank|Loup City| NE| 5431|        Heritage Bank|   13-Feb-09|\n",
            "+-------------------+---------+---+-----+---------------------+------------+\n",
            "only showing top 2 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Equal to values\n",
        "df2 = df.where(df['ST'] == 'NE')\n",
        "\n",
        "print('\\ndf2 sample below')\n",
        "df2.show(2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WvyiT6Virnk0"
      },
      "source": [
        "### 4.13 Filter data using logical operators"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 29,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "d2jx8gV3q70-",
        "outputId": "6ae8b94b-6658-4bbf-bba8-4821dc291c1f"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+------------------+-------+---+-----+---------------------+------------+\n",
            "|         Bank Name|   City| ST| CERT|Acquiring Institution|Closing Date|\n",
            "+------------------+-------+---+-----+---------------------+------------+\n",
            "|Ericson State Bank|Ericson| NE|18265| Farmers and Merch...|   14-Feb-20|\n",
            "+------------------+-------+---+-----+---------------------+------------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "df2 = df.where((df['ST'] == 'NE') & (df['City'] == 'Ericson'))\n",
        "df2.show(3)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bImSbNBFcsNl"
      },
      "source": [
        "### 4.14 Cast datatypes"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 30,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5kUFFW4FcsNp",
        "outputId": "36c86cf4-0c22-45ca-ed38-bbe129e5e560"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "==================================================\n",
            "Pre cast\n",
            "root\n",
            " |-- Bank Name: string (nullable = true)\n",
            " |-- City: string (nullable = true)\n",
            " |-- ST: string (nullable = true)\n",
            " |-- CERT: integer (nullable = true)\n",
            " |-- Acquiring Institution: string (nullable = true)\n",
            " |-- Closing Date: string (nullable = true)\n",
            "\n",
            "None\n",
            "==================================================\n",
            "Post cast\n",
            "root\n",
            " |-- Bank Name: string (nullable = true)\n",
            " |-- City: string (nullable = true)\n",
            " |-- ST: string (nullable = true)\n",
            " |-- CERT: integer (nullable = true)\n",
            " |-- Acquiring Institution: string (nullable = true)\n",
            " |-- Closing Date: string (nullable = true)\n",
            " |-- CERT_str1: string (nullable = true)\n",
            " |-- CERT_str2: string (nullable = true)\n",
            "\n",
            "None\n"
          ]
        }
      ],
      "source": [
        "print('='*50)\n",
        "print('Pre cast')\n",
        "print(df.printSchema())\n",
        "\n",
        "df2 = df \\\n",
        ".withColumn('CERT_str1', df['CERT'].cast('string')) \\\n",
        ".withColumn('CERT_str2', df['CERT'].cast(StringType())) #\\\n",
        "\n",
        "print('='*50)\n",
        "print('Post cast')\n",
        "print(df2.printSchema())\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BOAWTJcIVLrs"
      },
      "source": [
        "### 4.15 Replace values in dataframe"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 31,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vJkLrgi-VMqM",
        "outputId": "c9c09051-10ef-4234-d2e9-37a7cc202d87"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------------+--------+---+-----+---------------------+------------+\n",
            "|           Bank Name|    City| ST| CERT|Acquiring Institution|Closing Date|\n",
            "+--------------------+--------+---+-----+---------------------+------------+\n",
            "| First Bank of Idaho| Ketchum| ID|34396|      U.S. Bank, N.A.|   24-Apr-09|\n",
            "|Amcore Bank, Nati...|Rockford| IL| 3735|          Harris N.A.|   23-Apr-10|\n",
            "+--------------------+--------+---+-----+---------------------+------------+\n",
            "only showing top 2 rows\n",
            "\n",
            "Replace 7 in the above dataframe with 17 at all instances\n",
            "+--------------------+--------+---+-----+---------------------+------------+\n",
            "|           Bank Name|    City| ST| CERT|Acquiring Institution|Closing Date|\n",
            "+--------------------+--------+---+-----+---------------------+------------+\n",
            "| First Bank of Idaho| Ketchum| ID|34396|      U.S. Bank, N.A.|   24-Apr-09|\n",
            "|Amcore Bank, Nati...|Rockford| IL| 3735|          Harris N.A.|   23-Apr-10|\n",
            "+--------------------+--------+---+-----+---------------------+------------+\n",
            "only showing top 2 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Pre replace\n",
        "df.show(2)\n",
        "\n",
        "# Post replace\n",
        "print('Replace 7 in the above dataframe with 17 at all instances')\n",
        "df.na.replace(7,17).show(2)"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}