PySparkCrashCourse.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyN8Ht51BnHcqupzFqFZ9Cey",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ShubhamJagtap2000/Spark-Python/blob/main/PySparkCrashCourse.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 219
        },
        "id": "d6_tT8n0Kihf",
        "outputId": "038e3f97-d1d9-4e5f-c71d-7494e099962d"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<pyspark.sql.session.SparkSession at 0x791ed0f9a050>"
            ],
            "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://2a77417eb2a8:4040\">Spark UI</a></p>\n",
              "\n",
              "            <dl>\n",
              "              <dt>Version</dt>\n",
              "                <dd><code>v3.1.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",
              "        "
            ]
          },
          "metadata": {},
          "execution_count": 1
        }
      ],
      "source": [
        "!apt-get install openjdk-8-jdk-headless -qq > /dev/null\n",
        "!wget -q http://archive.apache.org/dist/spark/spark-3.1.1/spark-3.1.1-bin-hadoop3.2.tgz\n",
        "!tar xf spark-3.1.1-bin-hadoop3.2.tgz\n",
        "!pip install -q findspark\n",
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = \"/content/spark-3.1.1-bin-hadoop3.2\"\n",
        "import findspark\n",
        "findspark.init()\n",
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.master(\"local[*]\").getOrCreate()\n",
        "spark"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "mydata = spark.read.format(\"csv\").option(\"header\", \"true\").load(\"original.csv\")"
      ],
      "metadata": {
        "id": "YI5Sj-UmKrvs"
      },
      "execution_count": 9,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "mydata.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8J3Xxbk_LtN_",
        "outputId": "7b2ef9fd-9e0c-4ef1-f0c3-73255706a509"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+\n",
            "| id|first_name| last_name|gender|           City|            JobTitle|   Salary|  Latitude|  Longitude|\n",
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+\n",
            "|  1|   Melinde| Shilburne|Female|      Nowa Ruda| Assistant Professor|$57438.18|50.5774075| 16.4967184|\n",
            "|  2|  Kimberly|Von Welden|Female|         Bulgan|       Programmer II|$62846.60|48.8231572|103.5218199|\n",
            "|  3|    Alvera|  Di Boldi|Female|           null|                null|$57576.52|39.9947462|116.3397725|\n",
            "|  4|   Shannon| O'Griffin|  Male|  Divnomorskoye|Budget/Accounting...|$61489.23|44.5047212| 38.1300171|\n",
            "|  5|  Sherwood|   Macieja|  Male|      Mytishchi|            VP Sales|$63863.09|      null| 37.6489954|\n",
            "|  6|     Maris|      Folk|Female|Kinsealy-Drinan|      Civil Engineer|$30101.16|53.4266145| -6.1644997|\n",
            "|  7|     Masha|    Divers|Female|         Dachun|                null|$25090.87| 24.879416| 118.930111|\n",
            "|  8|   Goddart|     Flear|  Male|      Trélissac|Desktop Support T...|$46116.36|45.1905186|  0.7423124|\n",
            "|  9|      Roth|O'Cannavan|  Male|         Heitan|VP Product Manage...|$73697.10| 32.027934| 106.657113|\n",
            "| 10|      Bran|   Trahear|  Male|       Arbeláez|Mechanical System...|$68098.42|  4.272793| -74.416014|\n",
            "| 11|    Kylynn|   Lockart|Female|       El Cardo|Nuclear Power Eng...|$13604.63|     -5.85|-79.8833329|\n",
            "| 12|       Rey|    Meharg|Female|    Wangqingtuo|Systems Administr...|$73423.70| 39.172378| 116.931607|\n",
            "| 13|      Kerr|    Braden|  Male|      Sułkowice|Compensation Analyst|$33432.99|49.8151822| 19.3771749|\n",
            "| 14|    Mickie| Whanstall|  Male|    Springfield|Assistant Media P...|$50838.53|42.1014803|-72.5766759|\n",
            "| 15|    Kaspar|     Pally|  Male|         Chrást|  Analyst Programmer|$40163.03|49.7923299| 13.4915324|\n",
            "| 16|    Norbie|    Gwyllt|  Male|         Xijiao|              Editor|$32492.73|43.4945737|  5.8978018|\n",
            "| 17|    Claude|    Briant|Female|      Mieścisko|Research Assistan...|$51862.48|52.7441662| 17.3278637|\n",
            "| 18|     Thain|    Habbon|  Male| Foros do Trapo|     Design Engineer|$42135.67| 38.696249| -8.7098337|\n",
            "| 19|  Tiffanie|  Pattison|Female|    Jabungsisir|Senior Financial ...|$91925.08|-7.7232567|113.4686802|\n",
            "| 20|    Ettore|  Gerriets|  Male|          Pedra| Staff Accountant IV|$73921.33|40.7172049| -8.3625148|\n",
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql.functions import *"
      ],
      "metadata": {
        "id": "nbB8mdNzNXPf"
      },
      "execution_count": 11,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "mydata2 = mydata.withColumn(\"clean_city\", when(mydata.City.isNull(), \"Unknown\").otherwise(mydata.City))"
      ],
      "metadata": {
        "id": "kmWE0zXxNj83"
      },
      "execution_count": 14,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "mydata2.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ByXVEApVN02d",
        "outputId": "38fa7273-251f-45a9-e1dc-7e592b02e170"
      },
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+\n",
            "| id|first_name| last_name|gender|           City|            JobTitle|   Salary|  Latitude|  Longitude|     clean_city|\n",
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+\n",
            "|  1|   Melinde| Shilburne|Female|      Nowa Ruda| Assistant Professor|$57438.18|50.5774075| 16.4967184|      Nowa Ruda|\n",
            "|  2|  Kimberly|Von Welden|Female|         Bulgan|       Programmer II|$62846.60|48.8231572|103.5218199|         Bulgan|\n",
            "|  3|    Alvera|  Di Boldi|Female|           null|                null|$57576.52|39.9947462|116.3397725|        Unknown|\n",
            "|  4|   Shannon| O'Griffin|  Male|  Divnomorskoye|Budget/Accounting...|$61489.23|44.5047212| 38.1300171|  Divnomorskoye|\n",
            "|  5|  Sherwood|   Macieja|  Male|      Mytishchi|            VP Sales|$63863.09|      null| 37.6489954|      Mytishchi|\n",
            "|  6|     Maris|      Folk|Female|Kinsealy-Drinan|      Civil Engineer|$30101.16|53.4266145| -6.1644997|Kinsealy-Drinan|\n",
            "|  7|     Masha|    Divers|Female|         Dachun|                null|$25090.87| 24.879416| 118.930111|         Dachun|\n",
            "|  8|   Goddart|     Flear|  Male|      Trélissac|Desktop Support T...|$46116.36|45.1905186|  0.7423124|      Trélissac|\n",
            "|  9|      Roth|O'Cannavan|  Male|         Heitan|VP Product Manage...|$73697.10| 32.027934| 106.657113|         Heitan|\n",
            "| 10|      Bran|   Trahear|  Male|       Arbeláez|Mechanical System...|$68098.42|  4.272793| -74.416014|       Arbeláez|\n",
            "| 11|    Kylynn|   Lockart|Female|       El Cardo|Nuclear Power Eng...|$13604.63|     -5.85|-79.8833329|       El Cardo|\n",
            "| 12|       Rey|    Meharg|Female|    Wangqingtuo|Systems Administr...|$73423.70| 39.172378| 116.931607|    Wangqingtuo|\n",
            "| 13|      Kerr|    Braden|  Male|      Sułkowice|Compensation Analyst|$33432.99|49.8151822| 19.3771749|      Sułkowice|\n",
            "| 14|    Mickie| Whanstall|  Male|    Springfield|Assistant Media P...|$50838.53|42.1014803|-72.5766759|    Springfield|\n",
            "| 15|    Kaspar|     Pally|  Male|         Chrást|  Analyst Programmer|$40163.03|49.7923299| 13.4915324|         Chrást|\n",
            "| 16|    Norbie|    Gwyllt|  Male|         Xijiao|              Editor|$32492.73|43.4945737|  5.8978018|         Xijiao|\n",
            "| 17|    Claude|    Briant|Female|      Mieścisko|Research Assistan...|$51862.48|52.7441662| 17.3278637|      Mieścisko|\n",
            "| 18|     Thain|    Habbon|  Male| Foros do Trapo|     Design Engineer|$42135.67| 38.696249| -8.7098337| Foros do Trapo|\n",
            "| 19|  Tiffanie|  Pattison|Female|    Jabungsisir|Senior Financial ...|$91925.08|-7.7232567|113.4686802|    Jabungsisir|\n",
            "| 20|    Ettore|  Gerriets|  Male|          Pedra| Staff Accountant IV|$73921.33|40.7172049| -8.3625148|          Pedra|\n",
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Overwrite\n",
        "mydata2 = mydata2.filter(mydata2.JobTitle.isNotNull())"
      ],
      "metadata": {
        "id": "tv8dEf3sOPT1"
      },
      "execution_count": 17,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "mydata2.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "eaIkLLJmOprD",
        "outputId": "7269f0ef-cea7-4691-93b2-6b188fd6ea6a"
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+\n",
            "| id|first_name| last_name|gender|           City|            JobTitle|   Salary|  Latitude|  Longitude|     clean_city|\n",
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+\n",
            "|  1|   Melinde| Shilburne|Female|      Nowa Ruda| Assistant Professor|$57438.18|50.5774075| 16.4967184|      Nowa Ruda|\n",
            "|  2|  Kimberly|Von Welden|Female|         Bulgan|       Programmer II|$62846.60|48.8231572|103.5218199|         Bulgan|\n",
            "|  4|   Shannon| O'Griffin|  Male|  Divnomorskoye|Budget/Accounting...|$61489.23|44.5047212| 38.1300171|  Divnomorskoye|\n",
            "|  5|  Sherwood|   Macieja|  Male|      Mytishchi|            VP Sales|$63863.09|      null| 37.6489954|      Mytishchi|\n",
            "|  6|     Maris|      Folk|Female|Kinsealy-Drinan|      Civil Engineer|$30101.16|53.4266145| -6.1644997|Kinsealy-Drinan|\n",
            "|  8|   Goddart|     Flear|  Male|      Trélissac|Desktop Support T...|$46116.36|45.1905186|  0.7423124|      Trélissac|\n",
            "|  9|      Roth|O'Cannavan|  Male|         Heitan|VP Product Manage...|$73697.10| 32.027934| 106.657113|         Heitan|\n",
            "| 10|      Bran|   Trahear|  Male|       Arbeláez|Mechanical System...|$68098.42|  4.272793| -74.416014|       Arbeláez|\n",
            "| 11|    Kylynn|   Lockart|Female|       El Cardo|Nuclear Power Eng...|$13604.63|     -5.85|-79.8833329|       El Cardo|\n",
            "| 12|       Rey|    Meharg|Female|    Wangqingtuo|Systems Administr...|$73423.70| 39.172378| 116.931607|    Wangqingtuo|\n",
            "| 13|      Kerr|    Braden|  Male|      Sułkowice|Compensation Analyst|$33432.99|49.8151822| 19.3771749|      Sułkowice|\n",
            "| 14|    Mickie| Whanstall|  Male|    Springfield|Assistant Media P...|$50838.53|42.1014803|-72.5766759|    Springfield|\n",
            "| 15|    Kaspar|     Pally|  Male|         Chrást|  Analyst Programmer|$40163.03|49.7923299| 13.4915324|         Chrást|\n",
            "| 16|    Norbie|    Gwyllt|  Male|         Xijiao|              Editor|$32492.73|43.4945737|  5.8978018|         Xijiao|\n",
            "| 17|    Claude|    Briant|Female|      Mieścisko|Research Assistan...|$51862.48|52.7441662| 17.3278637|      Mieścisko|\n",
            "| 18|     Thain|    Habbon|  Male| Foros do Trapo|     Design Engineer|$42135.67| 38.696249| -8.7098337| Foros do Trapo|\n",
            "| 19|  Tiffanie|  Pattison|Female|    Jabungsisir|Senior Financial ...|$91925.08|-7.7232567|113.4686802|    Jabungsisir|\n",
            "| 20|    Ettore|  Gerriets|  Male|          Pedra| Staff Accountant IV|$73921.33|40.7172049| -8.3625148|          Pedra|\n",
            "| 21|      Alon|  Chasteau|  Male|          Xin’e|     Web Developer I|$62755.85|  49.16291|  127.98658|          Xin’e|\n",
            "| 22|   Guthrey|    Johnke|  Male|         Xijiao|         Developer I|$13708.03|40.7576842| 23.1342184|         Xijiao|\n",
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "mydata2 = mydata2.withColumn(\"clean_salary\", mydata2.Salary.substr(2, 100).cast('float'))"
      ],
      "metadata": {
        "id": "oKaWF0vQOrxF"
      },
      "execution_count": 20,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "mydata2.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1yf1rynvPJIf",
        "outputId": "0635c8a5-b599-4025-ff54-aa0ea6abc4de"
      },
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+------------+\n",
            "| id|first_name| last_name|gender|           City|            JobTitle|   Salary|  Latitude|  Longitude|     clean_city|clean_salary|\n",
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+------------+\n",
            "|  1|   Melinde| Shilburne|Female|      Nowa Ruda| Assistant Professor|$57438.18|50.5774075| 16.4967184|      Nowa Ruda|    57438.18|\n",
            "|  2|  Kimberly|Von Welden|Female|         Bulgan|       Programmer II|$62846.60|48.8231572|103.5218199|         Bulgan|     62846.6|\n",
            "|  4|   Shannon| O'Griffin|  Male|  Divnomorskoye|Budget/Accounting...|$61489.23|44.5047212| 38.1300171|  Divnomorskoye|    61489.23|\n",
            "|  5|  Sherwood|   Macieja|  Male|      Mytishchi|            VP Sales|$63863.09|      null| 37.6489954|      Mytishchi|    63863.09|\n",
            "|  6|     Maris|      Folk|Female|Kinsealy-Drinan|      Civil Engineer|$30101.16|53.4266145| -6.1644997|Kinsealy-Drinan|    30101.16|\n",
            "|  8|   Goddart|     Flear|  Male|      Trélissac|Desktop Support T...|$46116.36|45.1905186|  0.7423124|      Trélissac|    46116.36|\n",
            "|  9|      Roth|O'Cannavan|  Male|         Heitan|VP Product Manage...|$73697.10| 32.027934| 106.657113|         Heitan|     73697.1|\n",
            "| 10|      Bran|   Trahear|  Male|       Arbeláez|Mechanical System...|$68098.42|  4.272793| -74.416014|       Arbeláez|    68098.42|\n",
            "| 11|    Kylynn|   Lockart|Female|       El Cardo|Nuclear Power Eng...|$13604.63|     -5.85|-79.8833329|       El Cardo|    13604.63|\n",
            "| 12|       Rey|    Meharg|Female|    Wangqingtuo|Systems Administr...|$73423.70| 39.172378| 116.931607|    Wangqingtuo|     73423.7|\n",
            "| 13|      Kerr|    Braden|  Male|      Sułkowice|Compensation Analyst|$33432.99|49.8151822| 19.3771749|      Sułkowice|    33432.99|\n",
            "| 14|    Mickie| Whanstall|  Male|    Springfield|Assistant Media P...|$50838.53|42.1014803|-72.5766759|    Springfield|    50838.53|\n",
            "| 15|    Kaspar|     Pally|  Male|         Chrást|  Analyst Programmer|$40163.03|49.7923299| 13.4915324|         Chrást|    40163.03|\n",
            "| 16|    Norbie|    Gwyllt|  Male|         Xijiao|              Editor|$32492.73|43.4945737|  5.8978018|         Xijiao|    32492.73|\n",
            "| 17|    Claude|    Briant|Female|      Mieścisko|Research Assistan...|$51862.48|52.7441662| 17.3278637|      Mieścisko|    51862.48|\n",
            "| 18|     Thain|    Habbon|  Male| Foros do Trapo|     Design Engineer|$42135.67| 38.696249| -8.7098337| Foros do Trapo|    42135.67|\n",
            "| 19|  Tiffanie|  Pattison|Female|    Jabungsisir|Senior Financial ...|$91925.08|-7.7232567|113.4686802|    Jabungsisir|    91925.08|\n",
            "| 20|    Ettore|  Gerriets|  Male|          Pedra| Staff Accountant IV|$73921.33|40.7172049| -8.3625148|          Pedra|    73921.33|\n",
            "| 21|      Alon|  Chasteau|  Male|          Xin’e|     Web Developer I|$62755.85|  49.16291|  127.98658|          Xin’e|    62755.85|\n",
            "| 22|   Guthrey|    Johnke|  Male|         Xijiao|         Developer I|$13708.03|40.7576842| 23.1342184|         Xijiao|    13708.03|\n",
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "mean = mydata2.groupby().avg('clean_salary')"
      ],
      "metadata": {
        "id": "091fg486PPQc"
      },
      "execution_count": 22,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "mean.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ytquoDIaPXqN",
        "outputId": "a48ee462-5326-4043-d163-a0cf6618885f"
      },
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------------+\n",
            "|avg(clean_salary)|\n",
            "+-----------------+\n",
            "|55516.32088199837|\n",
            "+-----------------+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "mean = mydata2.groupBy().avg('clean_salary').take(1)[0][0]"
      ],
      "metadata": {
        "id": "RsLw-bVKPY6D"
      },
      "execution_count": 26,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "print(mean)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "q4TdCYerPgKS",
        "outputId": "e6d8597a-a4ed-422e-f0bf-53ae8a69c22a"
      },
      "execution_count": 28,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "55516.32088199837\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql.functions import lit\n",
        "mydata2 = mydata2.withColumn('new_salary', when(mydata2.clean_salary.isNull(), lit(mean)).otherwise(mydata2.clean_salary))"
      ],
      "metadata": {
        "id": "153M9b7-PhXv"
      },
      "execution_count": 30,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "mydata2.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "NpAOZDDyQGul",
        "outputId": "b6cdedbd-2037-4a6b-98bb-544d2c345aa6"
      },
      "execution_count": 31,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+------------+----------------+\n",
            "| id|first_name| last_name|gender|           City|            JobTitle|   Salary|  Latitude|  Longitude|     clean_city|clean_salary|      new_salary|\n",
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+------------+----------------+\n",
            "|  1|   Melinde| Shilburne|Female|      Nowa Ruda| Assistant Professor|$57438.18|50.5774075| 16.4967184|      Nowa Ruda|    57438.18|   57438.1796875|\n",
            "|  2|  Kimberly|Von Welden|Female|         Bulgan|       Programmer II|$62846.60|48.8231572|103.5218199|         Bulgan|     62846.6|   62846.6015625|\n",
            "|  4|   Shannon| O'Griffin|  Male|  Divnomorskoye|Budget/Accounting...|$61489.23|44.5047212| 38.1300171|  Divnomorskoye|    61489.23|  61489.23046875|\n",
            "|  5|  Sherwood|   Macieja|  Male|      Mytishchi|            VP Sales|$63863.09|      null| 37.6489954|      Mytishchi|    63863.09|  63863.08984375|\n",
            "|  6|     Maris|      Folk|Female|Kinsealy-Drinan|      Civil Engineer|$30101.16|53.4266145| -6.1644997|Kinsealy-Drinan|    30101.16|  30101.16015625|\n",
            "|  8|   Goddart|     Flear|  Male|      Trélissac|Desktop Support T...|$46116.36|45.1905186|  0.7423124|      Trélissac|    46116.36|    46116.359375|\n",
            "|  9|      Roth|O'Cannavan|  Male|         Heitan|VP Product Manage...|$73697.10| 32.027934| 106.657113|         Heitan|     73697.1|   73697.1015625|\n",
            "| 10|      Bran|   Trahear|  Male|       Arbeláez|Mechanical System...|$68098.42|  4.272793| -74.416014|       Arbeláez|    68098.42|    68098.421875|\n",
            "| 11|    Kylynn|   Lockart|Female|       El Cardo|Nuclear Power Eng...|$13604.63|     -5.85|-79.8833329|       El Cardo|    13604.63|13604.6298828125|\n",
            "| 12|       Rey|    Meharg|Female|    Wangqingtuo|Systems Administr...|$73423.70| 39.172378| 116.931607|    Wangqingtuo|     73423.7|    73423.703125|\n",
            "| 13|      Kerr|    Braden|  Male|      Sułkowice|Compensation Analyst|$33432.99|49.8151822| 19.3771749|      Sułkowice|    33432.99|  33432.98828125|\n",
            "| 14|    Mickie| Whanstall|  Male|    Springfield|Assistant Media P...|$50838.53|42.1014803|-72.5766759|    Springfield|    50838.53|     50838.53125|\n",
            "| 15|    Kaspar|     Pally|  Male|         Chrást|  Analyst Programmer|$40163.03|49.7923299| 13.4915324|         Chrást|    40163.03|     40163.03125|\n",
            "| 16|    Norbie|    Gwyllt|  Male|         Xijiao|              Editor|$32492.73|43.4945737|  5.8978018|         Xijiao|    32492.73|  32492.73046875|\n",
            "| 17|    Claude|    Briant|Female|      Mieścisko|Research Assistan...|$51862.48|52.7441662| 17.3278637|      Mieścisko|    51862.48|  51862.48046875|\n",
            "| 18|     Thain|    Habbon|  Male| Foros do Trapo|     Design Engineer|$42135.67| 38.696249| -8.7098337| Foros do Trapo|    42135.67|    42135.671875|\n",
            "| 19|  Tiffanie|  Pattison|Female|    Jabungsisir|Senior Financial ...|$91925.08|-7.7232567|113.4686802|    Jabungsisir|    91925.08|    91925.078125|\n",
            "| 20|    Ettore|  Gerriets|  Male|          Pedra| Staff Accountant IV|$73921.33|40.7172049| -8.3625148|          Pedra|    73921.33|    73921.328125|\n",
            "| 21|      Alon|  Chasteau|  Male|          Xin’e|     Web Developer I|$62755.85|  49.16291|  127.98658|          Xin’e|    62755.85|   62755.8515625|\n",
            "| 22|   Guthrey|    Johnke|  Male|         Xijiao|         Developer I|$13708.03|40.7576842| 23.1342184|         Xijiao|    13708.03|13708.0302734375|\n",
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+------------+----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Median values for latitude and longitude\n",
        "import numpy as np\n",
        "\n",
        "latitude = mydata2.select('Latitude')"
      ],
      "metadata": {
        "id": "0xTfCQB1QVRz"
      },
      "execution_count": 32,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "latitude.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OE5s8GLiQsV0",
        "outputId": "6a755299-5865-4616-c418-dde8b94a3fe0"
      },
      "execution_count": 33,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+\n",
            "|  Latitude|\n",
            "+----------+\n",
            "|50.5774075|\n",
            "|48.8231572|\n",
            "|44.5047212|\n",
            "|      null|\n",
            "|53.4266145|\n",
            "|45.1905186|\n",
            "| 32.027934|\n",
            "|  4.272793|\n",
            "|     -5.85|\n",
            "| 39.172378|\n",
            "|49.8151822|\n",
            "|42.1014803|\n",
            "|49.7923299|\n",
            "|43.4945737|\n",
            "|52.7441662|\n",
            "| 38.696249|\n",
            "|-7.7232567|\n",
            "|40.7172049|\n",
            "|  49.16291|\n",
            "|40.7576842|\n",
            "+----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#filter null values\n",
        "latitude = latitude.filter(latitude.Latitude.isNotNull())"
      ],
      "metadata": {
        "id": "QPIysaPqQuua"
      },
      "execution_count": 34,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "latitude.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OrHyRoloQ8PD",
        "outputId": "8a0278d8-c082-4a53-a573-604a47689cdb"
      },
      "execution_count": 35,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+\n",
            "|  Latitude|\n",
            "+----------+\n",
            "|50.5774075|\n",
            "|48.8231572|\n",
            "|44.5047212|\n",
            "|53.4266145|\n",
            "|45.1905186|\n",
            "| 32.027934|\n",
            "|  4.272793|\n",
            "|     -5.85|\n",
            "| 39.172378|\n",
            "|49.8151822|\n",
            "|42.1014803|\n",
            "|49.7923299|\n",
            "|43.4945737|\n",
            "|52.7441662|\n",
            "| 38.696249|\n",
            "|-7.7232567|\n",
            "|40.7172049|\n",
            "|  49.16291|\n",
            "|40.7576842|\n",
            "|48.4902808|\n",
            "+----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#dataframe.field('...').select('new_field')\n",
        "latitude = latitude.withColumn(\"latitude2\", latitude.Latitude.cast('float')).select('latitude2')"
      ],
      "metadata": {
        "id": "aTZxZVhoQ-PB"
      },
      "execution_count": 38,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "latitude.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cfF4Sc-7RMO4",
        "outputId": "37659f7d-e967-412f-c81f-daf17bebf1be"
      },
      "execution_count": 39,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+\n",
            "| latitude2|\n",
            "+----------+\n",
            "| 50.577408|\n",
            "|  48.82316|\n",
            "| 44.504723|\n",
            "| 53.426613|\n",
            "| 45.190517|\n",
            "| 32.027935|\n",
            "|  4.272793|\n",
            "|     -5.85|\n",
            "|  39.17238|\n",
            "|  49.81518|\n",
            "|  42.10148|\n",
            "|  49.79233|\n",
            "| 43.494576|\n",
            "| 52.744167|\n",
            "| 38.696247|\n",
            "|-7.7232566|\n",
            "| 40.717205|\n",
            "|  49.16291|\n",
            "| 40.757683|\n",
            "|  48.49028|\n",
            "+----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "median = np.median(latitude.collect())"
      ],
      "metadata": {
        "id": "eW7JZX9xRZ6A"
      },
      "execution_count": 40,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "print(median)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "h5Jj0SFNRr3S",
        "outputId": "1d5a7632-c43c-49a4-9dcf-6265c9d1bad2"
      },
      "execution_count": 42,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "31.93397331237793\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "mydata2 = mydata2.withColumn(\"lat\", when(mydata2.Latitude.isNull(), lit(median)).otherwise(mydata2.Latitude))"
      ],
      "metadata": {
        "id": "OL1CLXa4RuOu"
      },
      "execution_count": 45,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#filtered all null values\n",
        "mydata2.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qLNRV9fpSDej",
        "outputId": "971b2140-f802-45b9-adc2-3417d17d0cc2"
      },
      "execution_count": 46,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+------------+----------------+-----------------+\n",
            "| id|first_name| last_name|gender|           City|            JobTitle|   Salary|  Latitude|  Longitude|     clean_city|clean_salary|      new_salary|              lat|\n",
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+------------+----------------+-----------------+\n",
            "|  1|   Melinde| Shilburne|Female|      Nowa Ruda| Assistant Professor|$57438.18|50.5774075| 16.4967184|      Nowa Ruda|    57438.18|   57438.1796875|       50.5774075|\n",
            "|  2|  Kimberly|Von Welden|Female|         Bulgan|       Programmer II|$62846.60|48.8231572|103.5218199|         Bulgan|     62846.6|   62846.6015625|       48.8231572|\n",
            "|  4|   Shannon| O'Griffin|  Male|  Divnomorskoye|Budget/Accounting...|$61489.23|44.5047212| 38.1300171|  Divnomorskoye|    61489.23|  61489.23046875|       44.5047212|\n",
            "|  5|  Sherwood|   Macieja|  Male|      Mytishchi|            VP Sales|$63863.09|      null| 37.6489954|      Mytishchi|    63863.09|  63863.08984375|31.93397331237793|\n",
            "|  6|     Maris|      Folk|Female|Kinsealy-Drinan|      Civil Engineer|$30101.16|53.4266145| -6.1644997|Kinsealy-Drinan|    30101.16|  30101.16015625|       53.4266145|\n",
            "|  8|   Goddart|     Flear|  Male|      Trélissac|Desktop Support T...|$46116.36|45.1905186|  0.7423124|      Trélissac|    46116.36|    46116.359375|       45.1905186|\n",
            "|  9|      Roth|O'Cannavan|  Male|         Heitan|VP Product Manage...|$73697.10| 32.027934| 106.657113|         Heitan|     73697.1|   73697.1015625|        32.027934|\n",
            "| 10|      Bran|   Trahear|  Male|       Arbeláez|Mechanical System...|$68098.42|  4.272793| -74.416014|       Arbeláez|    68098.42|    68098.421875|         4.272793|\n",
            "| 11|    Kylynn|   Lockart|Female|       El Cardo|Nuclear Power Eng...|$13604.63|     -5.85|-79.8833329|       El Cardo|    13604.63|13604.6298828125|            -5.85|\n",
            "| 12|       Rey|    Meharg|Female|    Wangqingtuo|Systems Administr...|$73423.70| 39.172378| 116.931607|    Wangqingtuo|     73423.7|    73423.703125|        39.172378|\n",
            "| 13|      Kerr|    Braden|  Male|      Sułkowice|Compensation Analyst|$33432.99|49.8151822| 19.3771749|      Sułkowice|    33432.99|  33432.98828125|       49.8151822|\n",
            "| 14|    Mickie| Whanstall|  Male|    Springfield|Assistant Media P...|$50838.53|42.1014803|-72.5766759|    Springfield|    50838.53|     50838.53125|       42.1014803|\n",
            "| 15|    Kaspar|     Pally|  Male|         Chrást|  Analyst Programmer|$40163.03|49.7923299| 13.4915324|         Chrást|    40163.03|     40163.03125|       49.7923299|\n",
            "| 16|    Norbie|    Gwyllt|  Male|         Xijiao|              Editor|$32492.73|43.4945737|  5.8978018|         Xijiao|    32492.73|  32492.73046875|       43.4945737|\n",
            "| 17|    Claude|    Briant|Female|      Mieścisko|Research Assistan...|$51862.48|52.7441662| 17.3278637|      Mieścisko|    51862.48|  51862.48046875|       52.7441662|\n",
            "| 18|     Thain|    Habbon|  Male| Foros do Trapo|     Design Engineer|$42135.67| 38.696249| -8.7098337| Foros do Trapo|    42135.67|    42135.671875|        38.696249|\n",
            "| 19|  Tiffanie|  Pattison|Female|    Jabungsisir|Senior Financial ...|$91925.08|-7.7232567|113.4686802|    Jabungsisir|    91925.08|    91925.078125|       -7.7232567|\n",
            "| 20|    Ettore|  Gerriets|  Male|          Pedra| Staff Accountant IV|$73921.33|40.7172049| -8.3625148|          Pedra|    73921.33|    73921.328125|       40.7172049|\n",
            "| 21|      Alon|  Chasteau|  Male|          Xin’e|     Web Developer I|$62755.85|  49.16291|  127.98658|          Xin’e|    62755.85|   62755.8515625|         49.16291|\n",
            "| 22|   Guthrey|    Johnke|  Male|         Xijiao|         Developer I|$13708.03|40.7576842| 23.1342184|         Xijiao|    13708.03|13708.0302734375|       40.7576842|\n",
            "+---+----------+----------+------+---------------+--------------------+---------+----------+-----------+---------------+------------+----------------+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "P_n80-KmSU78"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}