Analisando_Base_do_AirBNB_com_spark.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Analisando Base do AirBNB com spark.ipynb",
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/OseiasBeu/Data-Science/blob/master/Analisando_Base_do_AirBNB_com_spark.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Oz_8sWI7PKEl"
      },
      "source": [
        "# instalar as dependências\n",
        "!apt-get install openjdk-8-jdk-headless -qq > /dev/null\n",
        "!wget -q https://archive.apache.org/dist/spark/spark-2.4.4/spark-2.4.4-bin-hadoop2.7.tgz\n",
        "!tar xf spark-2.4.4-bin-hadoop2.7.tgz\n",
        "!pip install -q findspark"
      ],
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RkpG11RQPbRf"
      },
      "source": [
        "# configurar as variáveis de ambiente\n",
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = \"/content/spark-2.4.4-bin-hadoop2.7\"\n",
        "\n",
        "# tornar o pyspark \"importável\"\n",
        "import findspark\n",
        "findspark.init('spark-2.4.4-bin-hadoop2.7')"
      ],
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gpJqWggpPKXO",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "ac54a84e-ba5d-4cd5-ded7-995159d23939"
      },
      "source": [
        "# iniciar uma sessão local e importar dados do Airbnb\n",
        "from pyspark.sql import SparkSession\n",
        "sc = SparkSession.builder.master('local[*]').getOrCreate()\n",
        "\n",
        "# download do http para arquivo local\n",
        "!wget --show-progress https://raw.githubusercontent.com/OseiasBeu/Data-Science/master/DataSets/listings.csv"
      ],
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2021-11-15 22:22:47--  https://raw.githubusercontent.com/OseiasBeu/Data-Science/master/DataSets/listings.csv\n",
            "Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.108.133, 185.199.109.133, 185.199.110.133, ...\n",
            "Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.108.133|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 3298987 (3.1M) [text/plain]\n",
            "Saving to: ‘listings.csv’\n",
            "\n",
            "\rlistings.csv          0%[                    ]       0  --.-KB/s               \rlistings.csv        100%[===================>]   3.15M  --.-KB/s    in 0.02s   \n",
            "\n",
            "2021-11-15 22:22:47 (146 MB/s) - ‘listings.csv’ saved [3298987/3298987]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YTOdwNWUr6_y"
      },
      "source": [
        "### Summary information and metrics for listings in Rio de Janeiro (good for visualisations).\n",
        "### 26 October, 2021\n",
        "### Rio de Janeiro"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FtcjxisBpW_U",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "79d71f5f-b3c5-430d-b1e2-c397fa5a6287"
      },
      "source": [
        "listings = sc.read.load('./listings.csv', headers=True, inferSchema=True, format='csv',sep=',')\n",
        "listings.show()"
      ],
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----+--------------------+---------+--------------------+-------------------+---------------+---------+---------+---------------+-----+--------------+-----------------+-----------+-----------------+--------------------+----------------+--------------------+-------+\n",
            "|  _c0|                 _c1|      _c2|                 _c3|                _c4|            _c5|      _c6|      _c7|            _c8|  _c9|          _c10|             _c11|       _c12|             _c13|                _c14|            _c15|                _c16|   _c17|\n",
            "+-----+--------------------+---------+--------------------+-------------------+---------------+---------+---------+---------------+-----+--------------+-----------------+-----------+-----------------+--------------------+----------------+--------------------+-------+\n",
            "|   id|                name|  host_id|           host_name|neighbourhood_group|  neighbourhood| latitude|longitude|      room_type|price|minimum_nights|number_of_reviews|last_review|reviews_per_month|calculated_host_l...|availability_365|number_of_reviews...|license|\n",
            "|17878|Very Nice 2Br in ...|    68997|            Matthias|               null|     Copacabana|-22.96599| -43.1794|Entire home/apt|  350|             5|              266| 2021-05-21|             2.07|                   1|             262|                  12|   null|\n",
            "|24480|Nice and cozy nea...|    99249|                Goya|               null|        Ipanema|-22.98405|-43.20189|Entire home/apt|  296|             3|               85| 2017-04-22|             0.85|                   1|              57|                   0|   null|\n",
            "|25026|Beautiful Modern ...|   102840|             Viviane|               null|     Copacabana|-22.97735|-43.19105|Entire home/apt|  168|             4|              238| 2019-04-27|             1.84|                   1|             300|                   0|   null|\n",
            "|35636|Cosy flat close t...|   153232|            Patricia|               null|        Ipanema|-22.98839|-43.19232|Entire home/apt|  278|             2|              181| 2019-12-06|             1.90|                   1|             179|                   0|   null|\n",
            "|35764|COPACABANA SEA BR...|   153691|Patricia Miranda ...|               null|     Copacabana|-22.98107|-43.19136|Entire home/apt|  150|             3|              373| 2019-06-15|             3.05|                   1|              59|                  34|   null|\n",
            "|48305|Bright 6bed Penth...|    70933|             Goitaca|               null|        Ipanema|-22.98591|-43.20302|Entire home/apt| 2410|             2|               86| 2021-09-30|             0.82|                   8|             334|                  15|   null|\n",
            "|48726|Rio de Janeiro Co...|   221941|                Vana|               null|     Copacabana|-22.98528|-43.19264|   Private room|  112|             3|               42| 2019-08-08|             0.87|                   2|              24|                   0|   null|\n",
            "|48901|Confortable 4BD 3...|   222884|              Marcio|               null|     Copacabana|-22.96574|-43.17514|Entire home/apt|  687|             3|                5| 2015-08-13|             0.07|                   1|              56|                   1|   null|\n",
            "|49179|Djalma Ocean View...|   224192|               David|               null|     Copacabana| -22.9791|-43.19008|Entire home/apt|  190|             3|              102| 2020-10-09|             0.96|                  34|             162|                  16|   null|\n",
            "|50294|Ipanema Beach Blo...|    70933|             Goitaca|               null|        Ipanema|-22.98584|-43.20305|Entire home/apt| 1020|             2|               74| 2021-07-07|             0.66|                   8|             334|                   1|   null|\n",
            "|50759|Casanova Residenc...|393296342|                Alex|               null|Barra da Tijuca|-23.00451|-43.30873|Entire home/apt| 3050|             1|                7| 2015-11-27|             0.10|                   2|             354|                   0|   null|\n",
            "|51703|Ocean view, block...|   238091|               Dália|               null|     Copacabana|-22.98234|-43.19164|Entire home/apt|  171|             3|              178| 2021-10-03|             1.71|                   2|             325|                  21|   null|\n",
            "|53533|4BD JOATINGA-CASA...|   249439|      Sherri & Andre|               null|            Joá|-23.00809|-43.29113|Entire home/apt| 1272|             2|               15| 2017-03-05|             0.14|                   1|             316|                   2|   null|\n",
            "|64795|Ipanema amazing 3...|    93005|              Andrea|               null|        Ipanema|-22.98169| -43.2028|Entire home/apt|  443|             3|               24| 2021-05-20|             0.25|                   1|             124|                  12|   null|\n",
            "|65546|Beach Block 3bed ...|    70933|             Goitaca|               null|        Ipanema|-22.98599|-43.20299|Entire home/apt| 1761|             2|               67| 2021-08-14|             0.65|                   8|             341|                   5|   null|\n",
            "|66574|Beach apart in Ri...|   326301|            Frederic|               null|       Flamengo|-22.92837|-43.17473|Entire home/apt|  220|             4|                1| 2016-08-24|             0.02|                   1|             283|                   0|   null|\n",
            "|66797|Studio Apart  for...|   328096|              Carlos|               null|     Copacabana| -22.9824|-43.19593|Entire home/apt|  147|             3|              219| 2021-01-31|             2.59|                   2|             270|                  21|   null|\n",
            "|68422|Copacabana Beach ...|   339264|               David|               null|     Copacabana|-22.98553|-43.18964|   Private room|  219|             4|               79| 2017-02-20|             0.64|                   1|              71|                   0|   null|\n",
            "|70080|Apart-hotel in Ip...|   319672|             Valeria|               null|        Ipanema| -22.9854|-43.19602|Entire home/apt|  297|             2|              412| 2021-09-27|             3.46|                   4|             202|                  45|   null|\n",
            "+-----+--------------------+---------+--------------------+-------------------+---------------+---------+---------+---------------+-----+--------------+-----------------+-----------+-----------------+--------------------+----------------+--------------------+-------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dcG_dEpmrgsM",
        "outputId": "20870821-02b7-4059-b1ea-c458fe178d49"
      },
      "source": [
        "type(listings)"
      ],
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "pyspark.sql.dataframe.DataFrame"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "a6K1MIirrmpf",
        "outputId": "a65059a4-2b99-4c0b-9fbc-33cf79f19982"
      },
      "source": [
        "listings.columns"
      ],
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['_c0',\n",
              " '_c1',\n",
              " '_c2',\n",
              " '_c3',\n",
              " '_c4',\n",
              " '_c5',\n",
              " '_c6',\n",
              " '_c7',\n",
              " '_c8',\n",
              " '_c9',\n",
              " '_c10',\n",
              " '_c11',\n",
              " '_c12',\n",
              " '_c13',\n",
              " '_c14',\n",
              " '_c15',\n",
              " '_c16',\n",
              " '_c17']"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "f1hpEVdCrwGz",
        "outputId": "ab6fe457-14c5-4ed8-a50e-ecad4692dc1b"
      },
      "source": [
        "listings.printSchema()"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- _c0: string (nullable = true)\n",
            " |-- _c1: string (nullable = true)\n",
            " |-- _c2: string (nullable = true)\n",
            " |-- _c3: string (nullable = true)\n",
            " |-- _c4: string (nullable = true)\n",
            " |-- _c5: string (nullable = true)\n",
            " |-- _c6: string (nullable = true)\n",
            " |-- _c7: string (nullable = true)\n",
            " |-- _c8: string (nullable = true)\n",
            " |-- _c9: string (nullable = true)\n",
            " |-- _c10: string (nullable = true)\n",
            " |-- _c11: string (nullable = true)\n",
            " |-- _c12: string (nullable = true)\n",
            " |-- _c13: string (nullable = true)\n",
            " |-- _c14: string (nullable = true)\n",
            " |-- _c15: string (nullable = true)\n",
            " |-- _c16: string (nullable = true)\n",
            " |-- _c17: string (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3VnzpGH1svJn",
        "outputId": "a92404f5-e2e0-4a06-82b0-db21c7cabaa1"
      },
      "source": [
        "from functools import reduce\n",
        "from past.builtins import xrange\n",
        "\n",
        "cols = []\n",
        "for columnName in listings.first():\n",
        "  cols.append(columnName)\n",
        "oldColumns = listings.schema.names\n",
        "\n",
        "print(f'''\n",
        "Novas colunas: {cols}\\n\n",
        "Velhas colunas: {oldColumns}\n",
        "''')\n",
        "df = reduce(lambda listings, idx: listings.withColumnRenamed(oldColumns[idx], cols[idx]), xrange(len(oldColumns)), listings)\n",
        "df.printSchema()\n",
        "df.show()"
      ],
      "execution_count": 38,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "Novas colunas: ['id', 'name', 'host_id', 'host_name', 'neighbourhood_group', 'neighbourhood', 'latitude', 'longitude', 'room_type', 'price', 'minimum_nights', 'number_of_reviews', 'last_review', 'reviews_per_month', 'calculated_host_listings_count', 'availability_365', 'number_of_reviews_ltm', 'license']\n",
            "\n",
            "Velhas colunas: ['_c0', '_c1', '_c2', '_c3', '_c4', '_c5', '_c6', '_c7', '_c8', '_c9', '_c10', '_c11', '_c12', '_c13', '_c14', '_c15', '_c16', '_c17']\n",
            "\n",
            "root\n",
            " |-- id: string (nullable = true)\n",
            " |-- name: string (nullable = true)\n",
            " |-- host_id: string (nullable = true)\n",
            " |-- host_name: string (nullable = true)\n",
            " |-- neighbourhood_group: string (nullable = true)\n",
            " |-- neighbourhood: string (nullable = true)\n",
            " |-- latitude: string (nullable = true)\n",
            " |-- longitude: string (nullable = true)\n",
            " |-- room_type: string (nullable = true)\n",
            " |-- price: string (nullable = true)\n",
            " |-- minimum_nights: string (nullable = true)\n",
            " |-- number_of_reviews: string (nullable = true)\n",
            " |-- last_review: string (nullable = true)\n",
            " |-- reviews_per_month: string (nullable = true)\n",
            " |-- calculated_host_listings_count: string (nullable = true)\n",
            " |-- availability_365: string (nullable = true)\n",
            " |-- number_of_reviews_ltm: string (nullable = true)\n",
            " |-- license: string (nullable = true)\n",
            "\n",
            "+-----+--------------------+---------+--------------------+-------------------+---------------+---------+---------+---------------+-----+--------------+-----------------+-----------+-----------------+------------------------------+----------------+---------------------+-------+\n",
            "|   id|                name|  host_id|           host_name|neighbourhood_group|  neighbourhood| latitude|longitude|      room_type|price|minimum_nights|number_of_reviews|last_review|reviews_per_month|calculated_host_listings_count|availability_365|number_of_reviews_ltm|license|\n",
            "+-----+--------------------+---------+--------------------+-------------------+---------------+---------+---------+---------------+-----+--------------+-----------------+-----------+-----------------+------------------------------+----------------+---------------------+-------+\n",
            "|   id|                name|  host_id|           host_name|neighbourhood_group|  neighbourhood| latitude|longitude|      room_type|price|minimum_nights|number_of_reviews|last_review|reviews_per_month|          calculated_host_l...|availability_365| number_of_reviews...|license|\n",
            "|17878|Very Nice 2Br in ...|    68997|            Matthias|               null|     Copacabana|-22.96599| -43.1794|Entire home/apt|  350|             5|              266| 2021-05-21|             2.07|                             1|             262|                   12|   null|\n",
            "|24480|Nice and cozy nea...|    99249|                Goya|               null|        Ipanema|-22.98405|-43.20189|Entire home/apt|  296|             3|               85| 2017-04-22|             0.85|                             1|              57|                    0|   null|\n",
            "|25026|Beautiful Modern ...|   102840|             Viviane|               null|     Copacabana|-22.97735|-43.19105|Entire home/apt|  168|             4|              238| 2019-04-27|             1.84|                             1|             300|                    0|   null|\n",
            "|35636|Cosy flat close t...|   153232|            Patricia|               null|        Ipanema|-22.98839|-43.19232|Entire home/apt|  278|             2|              181| 2019-12-06|             1.90|                             1|             179|                    0|   null|\n",
            "|35764|COPACABANA SEA BR...|   153691|Patricia Miranda ...|               null|     Copacabana|-22.98107|-43.19136|Entire home/apt|  150|             3|              373| 2019-06-15|             3.05|                             1|              59|                   34|   null|\n",
            "|48305|Bright 6bed Penth...|    70933|             Goitaca|               null|        Ipanema|-22.98591|-43.20302|Entire home/apt| 2410|             2|               86| 2021-09-30|             0.82|                             8|             334|                   15|   null|\n",
            "|48726|Rio de Janeiro Co...|   221941|                Vana|               null|     Copacabana|-22.98528|-43.19264|   Private room|  112|             3|               42| 2019-08-08|             0.87|                             2|              24|                    0|   null|\n",
            "|48901|Confortable 4BD 3...|   222884|              Marcio|               null|     Copacabana|-22.96574|-43.17514|Entire home/apt|  687|             3|                5| 2015-08-13|             0.07|                             1|              56|                    1|   null|\n",
            "|49179|Djalma Ocean View...|   224192|               David|               null|     Copacabana| -22.9791|-43.19008|Entire home/apt|  190|             3|              102| 2020-10-09|             0.96|                            34|             162|                   16|   null|\n",
            "|50294|Ipanema Beach Blo...|    70933|             Goitaca|               null|        Ipanema|-22.98584|-43.20305|Entire home/apt| 1020|             2|               74| 2021-07-07|             0.66|                             8|             334|                    1|   null|\n",
            "|50759|Casanova Residenc...|393296342|                Alex|               null|Barra da Tijuca|-23.00451|-43.30873|Entire home/apt| 3050|             1|                7| 2015-11-27|             0.10|                             2|             354|                    0|   null|\n",
            "|51703|Ocean view, block...|   238091|               Dália|               null|     Copacabana|-22.98234|-43.19164|Entire home/apt|  171|             3|              178| 2021-10-03|             1.71|                             2|             325|                   21|   null|\n",
            "|53533|4BD JOATINGA-CASA...|   249439|      Sherri & Andre|               null|            Joá|-23.00809|-43.29113|Entire home/apt| 1272|             2|               15| 2017-03-05|             0.14|                             1|             316|                    2|   null|\n",
            "|64795|Ipanema amazing 3...|    93005|              Andrea|               null|        Ipanema|-22.98169| -43.2028|Entire home/apt|  443|             3|               24| 2021-05-20|             0.25|                             1|             124|                   12|   null|\n",
            "|65546|Beach Block 3bed ...|    70933|             Goitaca|               null|        Ipanema|-22.98599|-43.20299|Entire home/apt| 1761|             2|               67| 2021-08-14|             0.65|                             8|             341|                    5|   null|\n",
            "|66574|Beach apart in Ri...|   326301|            Frederic|               null|       Flamengo|-22.92837|-43.17473|Entire home/apt|  220|             4|                1| 2016-08-24|             0.02|                             1|             283|                    0|   null|\n",
            "|66797|Studio Apart  for...|   328096|              Carlos|               null|     Copacabana| -22.9824|-43.19593|Entire home/apt|  147|             3|              219| 2021-01-31|             2.59|                             2|             270|                   21|   null|\n",
            "|68422|Copacabana Beach ...|   339264|               David|               null|     Copacabana|-22.98553|-43.18964|   Private room|  219|             4|               79| 2017-02-20|             0.64|                             1|              71|                    0|   null|\n",
            "|70080|Apart-hotel in Ip...|   319672|             Valeria|               null|        Ipanema| -22.9854|-43.19602|Entire home/apt|  297|             2|              412| 2021-09-27|             3.46|                             4|             202|                   45|   null|\n",
            "+-----+--------------------+---------+--------------------+-------------------+---------------+---------+---------+---------------+-----+--------------+-----------------+-----------+-----------------+------------------------------+----------------+---------------------+-------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "zv3DKAtStXiM",
        "outputId": "819b6d1a-9e76-4423-b7d8-ed877095826c"
      },
      "source": [
        ""
      ],
      "execution_count": 40,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "id\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RrHSR9jtyB6p"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}