Home_Sales.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "a_KW73O2e3dw",
        "outputId": "4ea13bda-ed84-4013-ed2c-b47e9c4210a3"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Hit:1 http://archive.ubuntu.com/ubuntu jammy InRelease\n",
            "Get:2 http://archive.ubuntu.com/ubuntu jammy-updates InRelease [119 kB]\n",
            "Hit:3 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu2204/x86_64  InRelease\n",
            "Hit:4 http://archive.ubuntu.com/ubuntu jammy-backports InRelease\n",
            "Get:5 http://security.ubuntu.com/ubuntu jammy-security InRelease [110 kB]\n",
            "Get:6 https://cloud.r-project.org/bin/linux/ubuntu jammy-cran40/ InRelease [3,626 B]\n",
            "Get:7 http://archive.ubuntu.com/ubuntu jammy-updates/main amd64 Packages [1,920 kB]\n",
            "Get:8 http://archive.ubuntu.com/ubuntu jammy-updates/universe amd64 Packages [1,358 kB]\n",
            "Hit:9 https://ppa.launchpadcontent.net/c2d4u.team/c2d4u4.0+/ubuntu jammy InRelease\n",
            "Hit:10 https://ppa.launchpadcontent.net/deadsnakes/ppa/ubuntu jammy InRelease\n",
            "Hit:11 https://ppa.launchpadcontent.net/graphics-drivers/ppa/ubuntu jammy InRelease\n",
            "Hit:12 https://ppa.launchpadcontent.net/ubuntugis/ppa/ubuntu jammy InRelease\n",
            "Fetched 3,510 kB in 3s (1,320 kB/s)\n",
            "Reading package lists... Done\n"
          ]
        }
      ],
      "source": [
        "import os\n",
        "# Find the latest version of spark 3.x  from http://www.apache.org/dist/spark/ and enter as the spark version\n",
        "# For example:\n",
        "# spark_version = 'spark-3.4.0'\n",
        "spark_version = 'spark-3.5.1'\n",
        "os.environ['SPARK_VERSION']=spark_version\n",
        "\n",
        "# Install Spark and Java\n",
        "!apt-get update\n",
        "!apt-get install openjdk-11-jdk-headless -qq > /dev/null\n",
        "!wget -q http://www.apache.org/dist/spark/$SPARK_VERSION/$SPARK_VERSION-bin-hadoop3.tgz\n",
        "!tar xf $SPARK_VERSION-bin-hadoop3.tgz\n",
        "!pip install -q findspark\n",
        "\n",
        "# Set Environment Variables\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-11-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = f\"/content/{spark_version}-bin-hadoop3\"\n",
        "\n",
        "# Start a SparkSession\n",
        "import findspark\n",
        "findspark.init()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "2XbWNf1Te5fM"
      },
      "outputs": [],
      "source": [
        "# Import packages\n",
        "from pyspark.sql import SparkSession\n",
        "import time\n",
        "\n",
        "# Create a SparkSession\n",
        "spark = SparkSession.builder.appName(\"SparkSQL\").getOrCreate()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "id": "wOJqxG_RPSwp",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d9659376-208f-4700-ecdf-4015c02ad728"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------------+----------+----------+------+--------+---------+-----------+--------+------+----------+----+\n",
            "|                  id|      date|date_built| price|bedrooms|bathrooms|sqft_living|sqft_lot|floors|waterfront|view|\n",
            "+--------------------+----------+----------+------+--------+---------+-----------+--------+------+----------+----+\n",
            "|f8a53099-ba1c-47d...|2022-04-08|      2016|936923|       4|        3|       3167|   11733|     2|         1|  76|\n",
            "|7530a2d8-1ae3-451...|2021-06-13|      2013|379628|       2|        2|       2235|   14384|     1|         0|  23|\n",
            "|43de979c-0bf0-4c9...|2019-04-12|      2014|417866|       2|        2|       2127|   10575|     2|         0|   0|\n",
            "|b672c137-b88c-48b...|2019-10-16|      2016|239895|       2|        2|       1631|   11149|     2|         0|   0|\n",
            "|e0726d4d-d595-407...|2022-01-08|      2017|424418|       3|        2|       2249|   13878|     2|         0|   4|\n",
            "|5aa00529-0533-46b...|2019-01-30|      2017|218712|       2|        3|       1965|   14375|     2|         0|   7|\n",
            "|131492a1-72e2-4a8...|2020-02-08|      2017|419199|       2|        3|       2062|    8876|     2|         0|   6|\n",
            "|8d54a71b-c520-44e...|2019-07-21|      2010|323956|       2|        3|       1506|   11816|     1|         0|  25|\n",
            "|e81aacfe-17fe-46b...|2020-06-16|      2016|181925|       3|        3|       2137|   11709|     2|         0|  22|\n",
            "|2ed8d509-7372-46d...|2021-08-06|      2015|258710|       3|        3|       1918|    9666|     1|         0|  25|\n",
            "|f876d86f-3c9f-42b...|2019-02-27|      2011|167864|       3|        3|       2471|   13924|     2|         0|  15|\n",
            "|0a2bd445-8508-4d8...|2021-12-30|      2014|337527|       2|        3|       1926|   12556|     1|         0|  23|\n",
            "|941bad30-eb49-4a7...|2020-05-09|      2015|229896|       3|        3|       2197|    8641|     1|         0|   3|\n",
            "|dd61eb34-6589-4c0...|2021-07-25|      2016|210247|       3|        2|       1672|   11986|     2|         0|  28|\n",
            "|f1e4cef7-d151-439...|2019-02-01|      2011|398667|       2|        3|       2331|   11356|     1|         0|   7|\n",
            "|ea620c7b-c2f7-4c6...|2021-05-31|      2011|437958|       3|        3|       2356|   11052|     1|         0|  26|\n",
            "|f233cb41-6f33-4b0...|2021-07-18|      2016|437375|       4|        3|       1704|   11721|     2|         0|  34|\n",
            "|c797ca12-52cd-4b1...|2019-06-08|      2015|288650|       2|        3|       2100|   10419|     2|         0|   7|\n",
            "|0cfe57f3-28c2-472...|2019-10-04|      2015|308313|       3|        3|       1960|    9453|     2|         0|   2|\n",
            "|4566cd2a-ac6e-435...|2019-07-15|      2016|177541|       3|        3|       2130|   10517|     2|         0|  25|\n",
            "+--------------------+----------+----------+------+--------+---------+-----------+--------+------+----------+----+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# 1. Read in the AWS S3 bucket into a DataFrame.\n",
        "from pyspark import SparkFiles\n",
        "url = \"https://2u-data-curriculum-team.s3.amazonaws.com/dataviz-classroom/v1.2/22-big-data/home_sales_revised.csv\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.csv(SparkFiles.get(\"home_sales_revised.csv\"), sep=\",\", header=True)\n",
        "\n",
        "df.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "RoljcJ7WPpnm"
      },
      "outputs": [],
      "source": [
        "# 2. Create a temporary view of the DataFrame.\n",
        "df.createOrReplaceTempView('home_sales')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "L6fkwOeOmqvq",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "ec3026ce-1a2e-4183-ed3e-e73bd2f59c85"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+-------------+\n",
            "|year|average_price|\n",
            "+----+-------------+\n",
            "|2019|     300263.7|\n",
            "|2020|    298353.78|\n",
            "|2021|    301819.44|\n",
            "|2022|    296363.88|\n",
            "+----+-------------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# 3. What is the average price for a four bedroom house sold per year, rounded to two decimal places?\n",
        "spark.sql(\n",
        "    \"\"\"\n",
        "    SELECT EXTRACT(YEAR FROM date) AS year,\n",
        "    ROUND(AVG(price), 2) as average_price\n",
        "    FROM home_sales\n",
        "    WHERE bedrooms == 4\n",
        "    GROUP BY year\n",
        "    ORDER BY year\n",
        "    \"\"\"\n",
        ").show()\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {
        "id": "l8p_tUS8h8it",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "658a5168-e982-4f6b-ffed-d9c44a1dbd41"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-------------+\n",
            "|date_built|average_price|\n",
            "+----------+-------------+\n",
            "|      2010|    292859.62|\n",
            "|      2011|    291117.47|\n",
            "|      2012|    293683.19|\n",
            "|      2013|    295962.27|\n",
            "|      2014|    290852.27|\n",
            "|      2015|     288770.3|\n",
            "|      2016|    290555.07|\n",
            "|      2017|    292676.79|\n",
            "+----------+-------------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# 4. What is the average price of a home for each year the home was built,\n",
        "# that have 3 bedrooms and 3 bathrooms, rounded to two decimal places?\n",
        "spark.sql(\n",
        "    \"\"\"\n",
        "    SELECT\n",
        "    date_built,\n",
        "    ROUND(AVG(price), 2) as average_price\n",
        "    FROM home_sales\n",
        "    WHERE bedrooms == 3 and bathrooms == 3\n",
        "    GROUP BY date_built\n",
        "    ORDER BY date_built\n",
        "    \"\"\"\n",
        ").show()\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {
        "id": "Y-Eytz64liDU",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "2c29e545-78bd-4a88-a1c9-b93771f7966d"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-------------+\n",
            "|date_built|average_price|\n",
            "+----------+-------------+\n",
            "|      2010|    285010.22|\n",
            "|      2011|    276553.81|\n",
            "|      2012|    307539.97|\n",
            "|      2013|    303676.79|\n",
            "|      2014|    298264.72|\n",
            "|      2015|    297609.97|\n",
            "|      2016|     293965.1|\n",
            "|      2017|    280317.58|\n",
            "+----------+-------------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# 5. What is the average price of a home for each year the home was built,\n",
        "# that have 3 bedrooms, 3 bathrooms, with two floors,\n",
        "# and are greater than or equal to 2,000 square feet, rounded to two decimal places?\n",
        "spark.sql(\n",
        "    \"\"\"\n",
        "    SELECT\n",
        "    date_built,\n",
        "    ROUND(AVG(price), 2) as average_price\n",
        "    FROM home_sales\n",
        "    WHERE bedrooms == 3 AND bathrooms == 3 AND floors == 2 AND sqft_living >= 2000\n",
        "    GROUP BY date_built\n",
        "    ORDER BY date_built\n",
        "    \"\"\"\n",
        ").show()\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GUrfgOX1pCRd",
        "outputId": "d83b041f-64db-4692-a6a3-f412768eca55"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+-------------+\n",
            "|view|average_price|\n",
            "+----+-------------+\n",
            "|  99|   1061201.42|\n",
            "|  98|   1053739.33|\n",
            "|  97|   1129040.15|\n",
            "|  96|   1017815.92|\n",
            "|  95|    1054325.6|\n",
            "|  94|    1033536.2|\n",
            "|  93|   1026006.06|\n",
            "|  92|    970402.55|\n",
            "|  91|   1137372.73|\n",
            "|  90|   1062654.16|\n",
            "|  89|   1107839.15|\n",
            "|  88|   1031719.35|\n",
            "|  87|    1072285.2|\n",
            "|  86|   1070444.25|\n",
            "|  85|   1056336.74|\n",
            "|  84|   1117233.13|\n",
            "|  83|   1033965.93|\n",
            "|  82|    1063498.0|\n",
            "|  81|   1053472.79|\n",
            "|  80|    991767.38|\n",
            "+----+-------------+\n",
            "only showing top 20 rows\n",
            "\n",
            "--- 1.2482097148895264 seconds ---\n"
          ]
        }
      ],
      "source": [
        "# 6. What is the average price of a home per \"view\" rating, rounded to two decimal places,\n",
        "# having an average home price greater than or equal to $350,000? Order by descending view rating.\n",
        "# Although this is a small dataset, determine the run time for this query.\n",
        "\n",
        "start_time = time.time()\n",
        "\n",
        "spark.sql(\n",
        "    \"\"\"\n",
        "    SELECT\n",
        "    view,\n",
        "    ROUND(AVG(price), 2) as average_price\n",
        "    FROM home_sales\n",
        "    GROUP BY view\n",
        "    HAVING AVG(price) >= 350000\n",
        "    ORDER BY view desc\n",
        "    \"\"\"\n",
        ").show()\n",
        "\n",
        "\n",
        "print(\"--- %s seconds ---\" % (time.time() - start_time))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {
        "id": "KAhk3ZD2tFy8",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "6de1452e-4829-4e23-8c63-bdb6fd05f953"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[]"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ],
      "source": [
        "# 7. Cache the the temporary table home_sales.\n",
        "spark.sql('cache table home_sales')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {
        "id": "4opVhbvxtL-i",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e01f449c-4f57-4b4a-c306-e5f1b1410849"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "True"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ],
      "source": [
        "# 8. Check if the table is cached.\n",
        "spark.catalog.isCached('home_sales')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5GnL46lwTSEk",
        "outputId": "7165f86c-cfec-49c0-80f1-cad745e43b7c"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+-------------+\n",
            "|view|average_price|\n",
            "+----+-------------+\n",
            "|  99|   1061201.42|\n",
            "|  98|   1053739.33|\n",
            "|  97|   1129040.15|\n",
            "|  96|   1017815.92|\n",
            "|  95|    1054325.6|\n",
            "|  94|    1033536.2|\n",
            "|  93|   1026006.06|\n",
            "|  92|    970402.55|\n",
            "|  91|   1137372.73|\n",
            "|  90|   1062654.16|\n",
            "|  89|   1107839.15|\n",
            "|  88|   1031719.35|\n",
            "|  87|    1072285.2|\n",
            "|  86|   1070444.25|\n",
            "|  85|   1056336.74|\n",
            "|  84|   1117233.13|\n",
            "|  83|   1033965.93|\n",
            "|  82|    1063498.0|\n",
            "|  81|   1053472.79|\n",
            "|  80|    991767.38|\n",
            "+----+-------------+\n",
            "only showing top 20 rows\n",
            "\n",
            "--- 0.529306173324585 seconds ---\n"
          ]
        }
      ],
      "source": [
        "# 9. Using the cached data, run the last query above, that calculates\n",
        "# the average price of a home per \"view\" rating, rounded to two decimal places,\n",
        "# having an average home price greater than or equal to $350,000.\n",
        "# Determine the runtime and compare it to the uncached runtime.\n",
        "\n",
        "start_time = time.time()\n",
        "\n",
        "spark.sql(\n",
        "    \"\"\"\n",
        "    SELECT\n",
        "    view,\n",
        "    ROUND(AVG(price), 2) as average_price\n",
        "    FROM home_sales\n",
        "    GROUP BY view\n",
        "    HAVING AVG(price) >= 350000\n",
        "    ORDER BY view desc\n",
        "    \"\"\"\n",
        ").show()\n",
        "\n",
        "print(\"--- %s seconds ---\" % (time.time() - start_time))\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 12,
      "metadata": {
        "id": "Qm12WN9isHBR"
      },
      "outputs": [],
      "source": [
        "# 10. Partition by the \"date_built\" field on the formatted parquet home sales data\n",
        "df.write.partitionBy(\"date_built\").mode(\"overwrite\").parquet(\"date_built_partitioned\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {
        "id": "AZ7BgY61sRqY"
      },
      "outputs": [],
      "source": [
        "# 11. Read the parquet formatted data.\n",
        "p_df_p=spark.read.parquet('date_built_partitioned')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "metadata": {
        "id": "J6MJkHfvVcvh"
      },
      "outputs": [],
      "source": [
        "# 12. Create a temporary table for the parquet data.\n",
        "p_df_p.createOrReplaceTempView('p_home_sales_p')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "G_Vhb52rU1Sn",
        "outputId": "d053b855-4040-41dd-c5cd-c0186e0e6b36"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----+-------------+\n",
            "|view|average_price|\n",
            "+----+-------------+\n",
            "|  99|   1061201.42|\n",
            "|  98|   1053739.33|\n",
            "|  97|   1129040.15|\n",
            "|  96|   1017815.92|\n",
            "|  95|    1054325.6|\n",
            "|  94|    1033536.2|\n",
            "|  93|   1026006.06|\n",
            "|  92|    970402.55|\n",
            "|  91|   1137372.73|\n",
            "|  90|   1062654.16|\n",
            "|  89|   1107839.15|\n",
            "|  88|   1031719.35|\n",
            "|  87|    1072285.2|\n",
            "|  86|   1070444.25|\n",
            "|  85|   1056336.74|\n",
            "|  84|   1117233.13|\n",
            "|  83|   1033965.93|\n",
            "|  82|    1063498.0|\n",
            "|  81|   1053472.79|\n",
            "|  80|    991767.38|\n",
            "+----+-------------+\n",
            "only showing top 20 rows\n",
            "\n",
            "--- 0.9826006889343262 seconds ---\n"
          ]
        }
      ],
      "source": [
        "# 13. Using the parquet DataFrame, run the last query above, that calculates\n",
        "# the average price of a home per \"view\" rating, rounded to two decimal places,\n",
        "# having an average home price greater than or equal to $350,000.\n",
        "# Determine the runtime and compare it to the cached runtime.\n",
        "\n",
        "start_time = time.time()\n",
        "\n",
        "spark.sql(\"\"\"\n",
        "    SELECT\n",
        "    view,\n",
        "    ROUND(AVG(price), 2) as average_price\n",
        "    FROM p_home_sales_p\n",
        "    GROUP BY view\n",
        "    HAVING AVG(price) >= 350000\n",
        "    ORDER BY view desc\n",
        "    \"\"\"\n",
        ").show()\n",
        "\n",
        "print(\"--- %s seconds ---\" % (time.time() - start_time))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "metadata": {
        "id": "hjjYzQGjtbq8",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b3fd0a6b-5e7d-4409-a9f6-18ed46123c4d"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[]"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ],
      "source": [
        "# 14. Uncache the home_sales temporary table.\n",
        "spark.sql(\"uncache table home_sales\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "metadata": {
        "id": "Sy9NBvO7tlmm",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "07f9d985-9703-4a82-80e5-ced61b760321"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "False"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ],
      "source": [
        "# 15. Check if the home_sales is no longer cached\n",
        "spark.catalog.isCached('home_sales')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Si-BNruRUGK3"
      },
      "outputs": [],
      "source": []
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "gpuClass": "standard",
    "kernelspec": {
      "display_name": "Python 3 (ipykernel)",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.10.4"
    },
    "nteract": {
      "version": "0.28.0"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}