Get_Zip_Code_Rental_Data_by_County.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Get Zip Code Rental Data by County.ipynb",
      "provenance": [],
      "authorship_tag": "ABX9TyNsM2pCbPgxMoqYcToP3AaF",
      "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/analyticsariel/projects/blob/master/Get_Zip_Code_Rental_Data_by_County.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BYRys7lpU-iF"
      },
      "source": [
        "# Get Zip Code Rental Data by County\n",
        "\n",
        "## Overview\n",
        "| Detail Tag            | Information                                                                                        |\n",
        "|-----------------------|----------------------------------------------------------------------------------------------------|\n",
        "| Originally Created By | Ariel Herrera arielherrera@analyticsariel.com |\n",
        "| External References   | API |\n",
        "| Input Datasets        | Source name |\n",
        "| Output Datasets       | Source name |\n",
        "| Input Data Source     | Pandas DataFrame |\n",
        "| Output Data Source    | Pandas DataFrame |\n",
        "\n",
        "## History\n",
        "| Date         | Developed By  | Reason                                                |\n",
        "|--------------|---------------|-------------------------------------------------------|\n",
        "| 22nd Jun 2022 | Ariel Herrera | Create notebook. |\n",
        "\n",
        "## Getting Started\n",
        "1. Copy this notebook -> File -> Save a Copy in Drive\n",
        "2. Directions\n",
        "\n",
        "## Useful Resources\n",
        "- [Google Colab Cheat Sheet](https://towardsdatascience.com/cheat-sheet-for-google-colab-63853778c093)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hKgiM1tbUnbI"
      },
      "source": [
        "## <font color=\"blue\">Install Packages</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7V2OU_iGUppW"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-ZgrtjtLUi6a"
      },
      "source": [
        "## <font color=\"blue\">Imports</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7vWKHxJDUjOJ"
      },
      "source": [
        "from google.colab import drive, files # specific to Google Colab\n",
        "import pandas as pd\n",
        "from scipy.stats import zscore\n",
        "import requests\n",
        "import warnings\n",
        "from datetime import datetime\n",
        "from dateutil.relativedelta import relativedelta\n",
        "import time\n",
        "\n",
        "warnings.filterwarnings('ignore')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PkdXQZp8UqcC"
      },
      "source": [
        "## <font color=\"blue\">Functions</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5p76TDONUsXe"
      },
      "source": [
        "def get_rental_market_data(rapid_api_key, zip_code):\n",
        "\n",
        "  if len(str(zip_code)) != 5:\n",
        "    return \"Invalid Zip Code\"\n",
        "\n",
        "  url = \"https://realty-mole-property-api.p.rapidapi.com/zipCodes/{}\".format(str(zip_code))\n",
        "\n",
        "  headers = {\n",
        "    \"X-RapidAPI-Key\": rapid_api_key,\n",
        "    \"X-RapidAPI-Host\": \"realty-mole-property-api.p.rapidapi.com\"\n",
        "  }\n",
        "\n",
        "  return requests.request(\"GET\", url, headers=headers)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def move_col_to_front(df, col_name):\n",
        "  front_col = df[col_name]\n",
        "  df.drop(labels=[col_name], axis=1,inplace = True)\n",
        "  df.insert(0, col_name, front_col)\n",
        "  return df"
      ],
      "metadata": {
        "id": "8sA0Cj8JSEJI"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def get_latest_rental_data(response):\n",
        "  # transform data to pandas dataframe\n",
        "  df = pd.json_normalize(data=response.json())\n",
        "  # relevant cols\n",
        "  rent_cols = ['id', 'rentalData.averageRent', 'rentalData.minRent', 'rentalData.maxRent', 'rentalData.totalRentals', 'rentalData.detailed']\n",
        "  df = df[rent_cols]\n",
        "  return df.rename(columns={'id': 'zip_code'})\n",
        "\n",
        "def get_historical_rental_data(response):\n",
        "  # transform data to pandas dataframe\n",
        "  df_rent = pd.json_normalize(data=response.json())\n",
        "\n",
        "  \"\"\" get historical data to single dataframe \"\"\"\n",
        "  df_list = [] # create empty list\n",
        "  # get all \"detailed\" columns\n",
        "  rent_detail_hist_cols = [x for x in df_rent.columns if 'detailed' and 'history' in x]\n",
        "  # iterate through \"detailed\" columns\n",
        "  for x in rent_detail_hist_cols:\n",
        "    # get column date\n",
        "    date_str = x.split('.')[2]\n",
        "    # get column name\n",
        "    detail_col = 'rentalData.history.' + date_str + '.detailed'\n",
        "    # convert historical data to a dataframe\n",
        "    _df = pd.DataFrame(df_rent[detail_col].iloc[0])\n",
        "    # create columns\n",
        "    _df['date_str'] = date_str\n",
        "    _df['zip_code'] = zip_code\n",
        "    # append to list\n",
        "    df_list.append(_df)\n",
        "\n",
        "  \"\"\" combine \"\"\"\n",
        "  # comine sub date dataframes\n",
        "  df_detail = pd.concat(df_list)\n",
        "  # move date column to front\n",
        "  df_detail = move_col_to_front(df=df_detail, col_name='date_str')\n",
        "  df_detail = move_col_to_front(df=df_detail, col_name='zip_code')\n",
        "  # add feature for previous year\n",
        "  df_detail['prev_yr_dt'] = df_detail.apply(lambda x: (datetime.strptime(x['date_str'], '%Y-%m') - relativedelta(years=1)).strftime('%Y-%m'), axis=1)\n",
        "\n",
        "  \"\"\" merge \"\"\"\n",
        "  df_detail_prev = df_detail.copy()\n",
        "  df_detail_prev.columns = [x + '_prev_yr' for x in df_detail_prev.columns]\n",
        "  # merge\n",
        "  df_merge = pd.merge(df_detail, df_detail_prev, how='left', left_on=['prev_yr_dt', 'bedrooms'], right_on=['date_str_prev_yr', 'bedrooms_prev_yr']).drop_duplicates()\n",
        "  # identify outliers\n",
        "  df_merge['averageRent_zscore'] = df_merge[['averageRent']].apply(zscore)\n",
        "\n",
        "  \"\"\" filter \"\"\"\n",
        "  # filter\n",
        "  df_filter = df_merge.copy()\n",
        "  df_filter = df_filter.loc[(df_filter['bedrooms'] <= 4) & (df_filter['averageRent_zscore'] <= 2)]\n",
        "  df_filter = df_filter.drop(columns=['prev_yr_dt', 'date_str_prev_yr', 'prev_yr_dt_prev_yr', 'zip_code_prev_yr'])\n",
        "  # add features\n",
        "  df_filter['YoY_avg_rent'] = \\\n",
        "    (df_filter['averageRent'] - df_filter['averageRent_prev_yr']) / df_filter['averageRent_prev_yr']\n",
        "  df_filter['YoY_total_rentals'] = \\\n",
        "    (df_filter['totalRentals'] - df_filter['totalRentals_prev_yr']) / df_filter['totalRentals_prev_yr']\n",
        "  return df_filter"
      ],
      "metadata": {
        "id": "_uRVh6pfaoTr"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9B1YSUk9UsoZ"
      },
      "source": [
        "## <font color=\"blue\">Locals & Constants</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "C0x9BC1nUqO6",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b4c2a6dd-def8-4367-f035-129d602cb381"
      },
      "source": [
        "############\n",
        "# OPTIONAL #\n",
        "############\n",
        "\n",
        "# mount drive\n",
        "drive.mount('/content/drive', force_remount=False)\n",
        "\n",
        "# data location\n",
        "file_dir = '/content/drive/My Drive/Colab Data/input/' # optional"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount(\"/content/drive\", force_remount=True).\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# read in api key file\n",
        "df_api_keys = pd.read_csv(file_dir + 'api_keys.csv')\n",
        "\n",
        "# get keys\n",
        "rapid_api_key = df_api_keys.loc[df_api_keys['API'] =='rapid']['KEY'].iloc[0] # replace this with your own key"
      ],
      "metadata": {
        "id": "JcuRx2ZajiZV"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CGCaqKj1UvIo"
      },
      "source": [
        "## <font color=\"blue\">Data</font>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xAk6Sl0bUxcx"
      },
      "source": [
        "## <font color=\"blue\">Transformations</font>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### <font color=\"green\">1. Single Zip Code</font>"
      ],
      "metadata": {
        "id": "A-I-mU09SL0u"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "zip_code = '33610' # Ybor, Tampa, FL\n",
        "url = \"https://realty-mole-property-api.p.rapidapi.com/zipCodes/{}\".format(str(zip_code))\n",
        "\n",
        "headers = {\n",
        "  \"X-RapidAPI-Key\": rapid_api_key,\n",
        "  \"X-RapidAPI-Host\": \"realty-mole-property-api.p.rapidapi.com\"\n",
        "}\n",
        "\n",
        "response = requests.request(\"GET\", url, headers=headers)"
      ],
      "metadata": {
        "id": "CniZFAxDiyjw"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "response.json()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6TXQRiJakPSl",
        "outputId": "8a69e127-a209-405a-b885-ff757a0143ab"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'id': '33610',\n",
              " 'rentalData': {'averageRent': 21264.16,\n",
              "  'detailed': [{'averageRent': 118059.5,\n",
              "    'bedrooms': 0,\n",
              "    'maxRent': 469000,\n",
              "    'minRent': 769,\n",
              "    'totalRentals': 4},\n",
              "   {'averageRent': 1590.2,\n",
              "    'bedrooms': 1,\n",
              "    'maxRent': 1897,\n",
              "    'minRent': 850,\n",
              "    'totalRentals': 64},\n",
              "   {'averageRent': 1825.64,\n",
              "    'bedrooms': 2,\n",
              "    'maxRent': 3000,\n",
              "    'minRent': 985,\n",
              "    'totalRentals': 70},\n",
              "   {'averageRent': 2002.55,\n",
              "    'bedrooms': 3,\n",
              "    'maxRent': 3500,\n",
              "    'minRent': 1350,\n",
              "    'totalRentals': 96},\n",
              "   {'averageRent': 2060.08,\n",
              "    'bedrooms': 4,\n",
              "    'maxRent': 2975,\n",
              "    'minRent': 1200,\n",
              "    'totalRentals': 26},\n",
              "   {'averageRent': 2047,\n",
              "    'bedrooms': 5,\n",
              "    'maxRent': 2099,\n",
              "    'minRent': 1995,\n",
              "    'totalRentals': 2}],\n",
              "  'history': {'2020-04': {'averageRent': 1367.13,\n",
              "    'detailed': [{'averageRent': 826,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 959,\n",
              "      'minRent': 744,\n",
              "      'totalRentals': 3},\n",
              "     {'averageRent': 920,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1040,\n",
              "      'minRent': 800,\n",
              "      'totalRentals': 2},\n",
              "     {'averageRent': 1121.51,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 1450,\n",
              "      'minRent': 842,\n",
              "      'totalRentals': 35},\n",
              "     {'averageRent': 2103.31,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 49900,\n",
              "      'minRent': 950,\n",
              "      'totalRentals': 124},\n",
              "     {'averageRent': 1445.26,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2150,\n",
              "      'minRent': 1100,\n",
              "      'totalRentals': 19},\n",
              "     {'averageRent': 1786.67,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 2500,\n",
              "      'minRent': 1365,\n",
              "      'totalRentals': 3}],\n",
              "    'maxRent': 49900,\n",
              "    'minRent': 744,\n",
              "    'totalRentals': 186},\n",
              "   '2020-05': {'averageRent': 1247.24,\n",
              "    'detailed': [{'averageRent': 820.33,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 908,\n",
              "      'minRent': 775,\n",
              "      'totalRentals': 3},\n",
              "     {'averageRent': 905,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1040,\n",
              "      'minRent': 800,\n",
              "      'totalRentals': 3},\n",
              "     {'averageRent': 1199.73,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2200,\n",
              "      'minRent': 842,\n",
              "      'totalRentals': 33},\n",
              "     {'averageRent': 1356.61,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 3400,\n",
              "      'minRent': 950,\n",
              "      'totalRentals': 180},\n",
              "     {'averageRent': 1436.75,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2100,\n",
              "      'minRent': 1030,\n",
              "      'totalRentals': 20},\n",
              "     {'averageRent': 1765,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 2500,\n",
              "      'minRent': 1365,\n",
              "      'totalRentals': 4}],\n",
              "    'maxRent': 3400,\n",
              "    'minRent': 775,\n",
              "    'totalRentals': 243},\n",
              "   '2020-06': {'averageRent': 1251.74,\n",
              "    'detailed': [{'averageRent': 810.67,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 879,\n",
              "      'minRent': 775,\n",
              "      'totalRentals': 3},\n",
              "     {'averageRent': 941.25,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1050,\n",
              "      'minRent': 800,\n",
              "      'totalRentals': 4},\n",
              "     {'averageRent': 1226.43,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2200,\n",
              "      'minRent': 842,\n",
              "      'totalRentals': 42},\n",
              "     {'averageRent': 1366.33,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 3400,\n",
              "      'minRent': 950,\n",
              "      'totalRentals': 186},\n",
              "     {'averageRent': 1454.76,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2100,\n",
              "      'minRent': 1030,\n",
              "      'totalRentals': 21},\n",
              "     {'averageRent': 1711,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 2500,\n",
              "      'minRent': 1365,\n",
              "      'totalRentals': 5}],\n",
              "    'maxRent': 3400,\n",
              "    'minRent': 775,\n",
              "    'totalRentals': 261},\n",
              "   '2020-07': {'averageRent': 1259.99,\n",
              "    'detailed': [{'averageRent': 832,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 943,\n",
              "      'minRent': 775,\n",
              "      'totalRentals': 3},\n",
              "     {'averageRent': 941.25,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1050,\n",
              "      'minRent': 800,\n",
              "      'totalRentals': 4},\n",
              "     {'averageRent': 1241.95,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2200,\n",
              "      'minRent': 842,\n",
              "      'totalRentals': 41},\n",
              "     {'averageRent': 1366.01,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 3400,\n",
              "      'minRent': 950,\n",
              "      'totalRentals': 191},\n",
              "     {'averageRent': 1467.75,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2100,\n",
              "      'minRent': 1030,\n",
              "      'totalRentals': 20},\n",
              "     {'averageRent': 1711,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 2500,\n",
              "      'minRent': 1365,\n",
              "      'totalRentals': 5}],\n",
              "    'maxRent': 3400,\n",
              "    'minRent': 775,\n",
              "    'totalRentals': 264},\n",
              "   '2020-08': {'averageRent': 1214.77,\n",
              "    'detailed': [{'averageRent': 838.33,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 962,\n",
              "      'minRent': 775,\n",
              "      'totalRentals': 3},\n",
              "     {'averageRent': 808.71,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1050,\n",
              "      'minRent': 250,\n",
              "      'totalRentals': 7},\n",
              "     {'averageRent': 1281.26,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2000,\n",
              "      'minRent': 900,\n",
              "      'totalRentals': 43},\n",
              "     {'averageRent': 1368.11,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2150,\n",
              "      'minRent': 1000,\n",
              "      'totalRentals': 187},\n",
              "     {'averageRent': 1510.53,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2100,\n",
              "      'minRent': 1030,\n",
              "      'totalRentals': 17},\n",
              "     {'averageRent': 1481.67,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 1700,\n",
              "      'minRent': 1250,\n",
              "      'totalRentals': 3}],\n",
              "    'maxRent': 2150,\n",
              "    'minRent': 250,\n",
              "    'totalRentals': 260},\n",
              "   '2020-09': {'averageRent': 1236.16,\n",
              "    'detailed': [{'averageRent': 860.33,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 1003,\n",
              "      'minRent': 778,\n",
              "      'totalRentals': 3},\n",
              "     {'averageRent': 797.67,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1050,\n",
              "      'minRent': 250,\n",
              "      'totalRentals': 6},\n",
              "     {'averageRent': 1299.26,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2000,\n",
              "      'minRent': 900,\n",
              "      'totalRentals': 35},\n",
              "     {'averageRent': 1406.21,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2150,\n",
              "      'minRent': 1000,\n",
              "      'totalRentals': 101},\n",
              "     {'averageRent': 1571.81,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2000,\n",
              "      'minRent': 1150,\n",
              "      'totalRentals': 16},\n",
              "     {'averageRent': 1481.67,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 1700,\n",
              "      'minRent': 1250,\n",
              "      'totalRentals': 3}],\n",
              "    'maxRent': 2150,\n",
              "    'minRent': 250,\n",
              "    'totalRentals': 164},\n",
              "   '2020-10': {'averageRent': 1199.54,\n",
              "    'detailed': [{'averageRent': 859.67,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 1001,\n",
              "      'minRent': 778,\n",
              "      'totalRentals': 3},\n",
              "     {'averageRent': 871.5,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1040,\n",
              "      'minRent': 800,\n",
              "      'totalRentals': 4},\n",
              "     {'averageRent': 1265.76,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2000,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 33},\n",
              "     {'averageRent': 1403.61,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2150,\n",
              "      'minRent': 1000,\n",
              "      'totalRentals': 85},\n",
              "     {'averageRent': 1546.71,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2000,\n",
              "      'minRent': 1195,\n",
              "      'totalRentals': 14},\n",
              "     {'averageRent': 1250,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 1250,\n",
              "      'minRent': 1250,\n",
              "      'totalRentals': 1}],\n",
              "    'maxRent': 2150,\n",
              "    'minRent': 778,\n",
              "    'totalRentals': 140},\n",
              "   '2020-11': {'averageRent': 1216.43,\n",
              "    'detailed': [{'averageRent': 868,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 1026,\n",
              "      'minRent': 778,\n",
              "      'totalRentals': 3},\n",
              "     {'averageRent': 907.2,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1050,\n",
              "      'minRent': 800,\n",
              "      'totalRentals': 5},\n",
              "     {'averageRent': 1247.78,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2000,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 32},\n",
              "     {'averageRent': 1458.18,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2500,\n",
              "      'minRent': 1025,\n",
              "      'totalRentals': 91},\n",
              "     {'averageRent': 1567.42,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2100,\n",
              "      'minRent': 1300,\n",
              "      'totalRentals': 12},\n",
              "     {'averageRent': 1250,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 1250,\n",
              "      'minRent': 1250,\n",
              "      'totalRentals': 1}],\n",
              "    'maxRent': 2500,\n",
              "    'minRent': 778,\n",
              "    'totalRentals': 144},\n",
              "   '2020-12': {'averageRent': 1178.75,\n",
              "    'detailed': [{'averageRent': 866.33,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 1021,\n",
              "      'minRent': 778,\n",
              "      'totalRentals': 3},\n",
              "     {'averageRent': 825,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1050,\n",
              "      'minRent': 600,\n",
              "      'totalRentals': 2},\n",
              "     {'averageRent': 1245.14,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2000,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 29},\n",
              "     {'averageRent': 1449.51,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2500,\n",
              "      'minRent': 1025,\n",
              "      'totalRentals': 73},\n",
              "     {'averageRent': 1507.75,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2100,\n",
              "      'minRent': 1295,\n",
              "      'totalRentals': 16}],\n",
              "    'maxRent': 2500,\n",
              "    'minRent': 600,\n",
              "    'totalRentals': 123},\n",
              "   '2021-01': {'averageRent': 1166.08,\n",
              "    'detailed': [{'averageRent': 866.33,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 1021,\n",
              "      'minRent': 778,\n",
              "      'totalRentals': 3},\n",
              "     {'averageRent': 825,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1050,\n",
              "      'minRent': 600,\n",
              "      'totalRentals': 2},\n",
              "     {'averageRent': 1175.95,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 1600,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 20},\n",
              "     {'averageRent': 1449.54,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2500,\n",
              "      'minRent': 1025,\n",
              "      'totalRentals': 68},\n",
              "     {'averageRent': 1513.57,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2100,\n",
              "      'minRent': 1295,\n",
              "      'totalRentals': 14}],\n",
              "    'maxRent': 2500,\n",
              "    'minRent': 600,\n",
              "    'totalRentals': 107},\n",
              "   '2021-02': {'averageRent': 1248.09,\n",
              "    'detailed': [{'averageRent': 860.33,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 959,\n",
              "      'minRent': 800,\n",
              "      'totalRentals': 3},\n",
              "     {'averageRent': 750,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1050,\n",
              "      'minRent': 600,\n",
              "      'totalRentals': 3},\n",
              "     {'averageRent': 1205.29,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 1600,\n",
              "      'minRent': 800,\n",
              "      'totalRentals': 28},\n",
              "     {'averageRent': 1464.12,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2500,\n",
              "      'minRent': 1025,\n",
              "      'totalRentals': 76},\n",
              "     {'averageRent': 1513.82,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2100,\n",
              "      'minRent': 1295,\n",
              "      'totalRentals': 17},\n",
              "     {'averageRent': 1695,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 1695,\n",
              "      'minRent': 1695,\n",
              "      'totalRentals': 1}],\n",
              "    'maxRent': 2500,\n",
              "    'minRent': 600,\n",
              "    'totalRentals': 128},\n",
              "   '2021-03': {'averageRent': 1218.55,\n",
              "    'detailed': [{'averageRent': 888,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 954,\n",
              "      'minRent': 822,\n",
              "      'totalRentals': 2},\n",
              "     {'averageRent': 600,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 600,\n",
              "      'minRent': 600,\n",
              "      'totalRentals': 2},\n",
              "     {'averageRent': 1195.72,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 1600,\n",
              "      'minRent': 800,\n",
              "      'totalRentals': 25},\n",
              "     {'averageRent': 1486.07,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2700,\n",
              "      'minRent': 1025,\n",
              "      'totalRentals': 58},\n",
              "     {'averageRent': 1446.54,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 1695,\n",
              "      'minRent': 1295,\n",
              "      'totalRentals': 13},\n",
              "     {'averageRent': 1695,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 1695,\n",
              "      'minRent': 1695,\n",
              "      'totalRentals': 1}],\n",
              "    'maxRent': 2700,\n",
              "    'minRent': 600,\n",
              "    'totalRentals': 101},\n",
              "   '2021-04': {'averageRent': 1357.01,\n",
              "    'detailed': [{'averageRent': 1024.5,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 1227,\n",
              "      'minRent': 822,\n",
              "      'totalRentals': 2},\n",
              "     {'averageRent': 600,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 600,\n",
              "      'minRent': 600,\n",
              "      'totalRentals': 1},\n",
              "     {'averageRent': 1185.78,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 1600,\n",
              "      'minRent': 800,\n",
              "      'totalRentals': 18},\n",
              "     {'averageRent': 1474.38,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2700,\n",
              "      'minRent': 1025,\n",
              "      'totalRentals': 45},\n",
              "     {'averageRent': 1534.89,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 1699,\n",
              "      'minRent': 1395,\n",
              "      'totalRentals': 9},\n",
              "     {'averageRent': 2322.5,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 2950,\n",
              "      'minRent': 1695,\n",
              "      'totalRentals': 2}],\n",
              "    'maxRent': 2950,\n",
              "    'minRent': 600,\n",
              "    'totalRentals': 77},\n",
              "   '2021-05': {'averageRent': 1364.92,\n",
              "    'detailed': [{'averageRent': 1027.5,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 1233,\n",
              "      'minRent': 822,\n",
              "      'totalRentals': 2},\n",
              "     {'averageRent': 600,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 600,\n",
              "      'minRent': 600,\n",
              "      'totalRentals': 1},\n",
              "     {'averageRent': 1219.7,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 1700,\n",
              "      'minRent': 800,\n",
              "      'totalRentals': 20},\n",
              "     {'averageRent': 1479.4,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2700,\n",
              "      'minRent': 1025,\n",
              "      'totalRentals': 45},\n",
              "     {'averageRent': 1540.44,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 1699,\n",
              "      'minRent': 1395,\n",
              "      'totalRentals': 9},\n",
              "     {'averageRent': 2322.5,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 2950,\n",
              "      'minRent': 1695,\n",
              "      'totalRentals': 2}],\n",
              "    'maxRent': 2950,\n",
              "    'minRent': 600,\n",
              "    'totalRentals': 79},\n",
              "   '2021-06': {'averageRent': 1520.16,\n",
              "    'detailed': [{'averageRent': 1018,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 1214,\n",
              "      'minRent': 822,\n",
              "      'totalRentals': 2},\n",
              "     {'averageRent': 1202.78,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 1700,\n",
              "      'minRent': 800,\n",
              "      'totalRentals': 18},\n",
              "     {'averageRent': 1494.5,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2700,\n",
              "      'minRent': 1025,\n",
              "      'totalRentals': 44},\n",
              "     {'averageRent': 1563,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 1699,\n",
              "      'minRent': 1395,\n",
              "      'totalRentals': 8},\n",
              "     {'averageRent': 2322.5,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 2950,\n",
              "      'minRent': 1695,\n",
              "      'totalRentals': 2}],\n",
              "    'maxRent': 2950,\n",
              "    'minRent': 800,\n",
              "    'totalRentals': 74},\n",
              "   '2021-07': {'averageRent': 21822.66,\n",
              "    'detailed': [{'averageRent': 121367.29,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 469000,\n",
              "      'minRent': 769,\n",
              "      'totalRentals': 7},\n",
              "     {'averageRent': 1446.77,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1791,\n",
              "      'minRent': 500,\n",
              "      'totalRentals': 39},\n",
              "     {'averageRent': 1597.62,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2198,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 52},\n",
              "     {'averageRent': 1772.61,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2800,\n",
              "      'minRent': 1025,\n",
              "      'totalRentals': 84},\n",
              "     {'averageRent': 1801.67,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2295,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 27},\n",
              "     {'averageRent': 2950,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 2950,\n",
              "      'minRent': 2950,\n",
              "      'totalRentals': 1}],\n",
              "    'maxRent': 469000,\n",
              "    'minRent': 500,\n",
              "    'totalRentals': 210},\n",
              "   '2021-08': {'averageRent': 25603.46,\n",
              "    'detailed': [{'averageRent': 121367.29,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 469000,\n",
              "      'minRent': 769,\n",
              "      'totalRentals': 7},\n",
              "     {'averageRent': 1446.77,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1791,\n",
              "      'minRent': 500,\n",
              "      'totalRentals': 39},\n",
              "     {'averageRent': 1611.29,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2198,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 51},\n",
              "     {'averageRent': 1786.35,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2800,\n",
              "      'minRent': 1100,\n",
              "      'totalRentals': 81},\n",
              "     {'averageRent': 1805.62,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2295,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 26}],\n",
              "    'maxRent': 469000,\n",
              "    'minRent': 500,\n",
              "    'totalRentals': 204},\n",
              "   '2021-09': {'averageRent': 25608.82,\n",
              "    'detailed': [{'averageRent': 121367.29,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 469000,\n",
              "      'minRent': 769,\n",
              "      'totalRentals': 7},\n",
              "     {'averageRent': 1446.77,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1791,\n",
              "      'minRent': 500,\n",
              "      'totalRentals': 39},\n",
              "     {'averageRent': 1629.28,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2198,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 47},\n",
              "     {'averageRent': 1795.18,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2800,\n",
              "      'minRent': 1100,\n",
              "      'totalRentals': 79},\n",
              "     {'averageRent': 1805.62,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2295,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 26}],\n",
              "    'maxRent': 469000,\n",
              "    'minRent': 500,\n",
              "    'totalRentals': 198},\n",
              "   '2021-10': {'averageRent': 25614.98,\n",
              "    'detailed': [{'averageRent': 121367.29,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 469000,\n",
              "      'minRent': 769,\n",
              "      'totalRentals': 7},\n",
              "     {'averageRent': 1446.77,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1791,\n",
              "      'minRent': 500,\n",
              "      'totalRentals': 39},\n",
              "     {'averageRent': 1629.28,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2198,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 47},\n",
              "     {'averageRent': 1809.53,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2800,\n",
              "      'minRent': 1100,\n",
              "      'totalRentals': 76},\n",
              "     {'averageRent': 1822.04,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2295,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 25}],\n",
              "    'maxRent': 469000,\n",
              "    'minRent': 500,\n",
              "    'totalRentals': 194},\n",
              "   '2021-11': {'averageRent': 43665.04,\n",
              "    'detailed': [{'averageRent': 211509,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 469000,\n",
              "      'minRent': 769,\n",
              "      'totalRentals': 4},\n",
              "     {'averageRent': 1424.9,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1791,\n",
              "      'minRent': 500,\n",
              "      'totalRentals': 31},\n",
              "     {'averageRent': 1635.94,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2140,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 33},\n",
              "     {'averageRent': 1856.87,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2800,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 54},\n",
              "     {'averageRent': 1898.5,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2383,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 16}],\n",
              "    'maxRent': 469000,\n",
              "    'minRent': 500,\n",
              "    'totalRentals': 138},\n",
              "   '2021-12': {'averageRent': 25629.94,\n",
              "    'detailed': [{'averageRent': 121289.14,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 469000,\n",
              "      'minRent': 769,\n",
              "      'totalRentals': 7},\n",
              "     {'averageRent': 1459.78,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1791,\n",
              "      'minRent': 500,\n",
              "      'totalRentals': 36},\n",
              "     {'averageRent': 1625.02,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2198,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 43},\n",
              "     {'averageRent': 1883.46,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2800,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 59},\n",
              "     {'averageRent': 1892.28,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2295,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 18}],\n",
              "    'maxRent': 469000,\n",
              "    'minRent': 500,\n",
              "    'totalRentals': 163},\n",
              "   '2022-01': {'averageRent': 25672.71,\n",
              "    'detailed': [{'averageRent': 121306.71,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 469000,\n",
              "      'minRent': 769,\n",
              "      'totalRentals': 7},\n",
              "     {'averageRent': 1498.75,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 2104,\n",
              "      'minRent': 500,\n",
              "      'totalRentals': 44},\n",
              "     {'averageRent': 1648.42,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2166,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 55},\n",
              "     {'averageRent': 1889.61,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2800,\n",
              "      'minRent': 1100,\n",
              "      'totalRentals': 69},\n",
              "     {'averageRent': 2020.05,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 3495,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 19}],\n",
              "    'maxRent': 469000,\n",
              "    'minRent': 500,\n",
              "    'totalRentals': 194},\n",
              "   '2022-02': {'averageRent': 21732.99,\n",
              "    'detailed': [{'averageRent': 121292.29,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 469000,\n",
              "      'minRent': 769,\n",
              "      'totalRentals': 7},\n",
              "     {'averageRent': 1524.38,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 2075,\n",
              "      'minRent': 500,\n",
              "      'totalRentals': 55},\n",
              "     {'averageRent': 1670.28,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2120,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 67},\n",
              "     {'averageRent': 1918.12,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2800,\n",
              "      'minRent': 1100,\n",
              "      'totalRentals': 77},\n",
              "     {'averageRent': 1997.89,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2975,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 18},\n",
              "     {'averageRent': 1995,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 1995,\n",
              "      'minRent': 1995,\n",
              "      'totalRentals': 1}],\n",
              "    'maxRent': 469000,\n",
              "    'minRent': 500,\n",
              "    'totalRentals': 225},\n",
              "   '2022-03': {'averageRent': 21750.35,\n",
              "    'detailed': [{'averageRent': 121288.57,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 469000,\n",
              "      'minRent': 769,\n",
              "      'totalRentals': 7},\n",
              "     {'averageRent': 1497.25,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 2035,\n",
              "      'minRent': 500,\n",
              "      'totalRentals': 53},\n",
              "     {'averageRent': 1719.06,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2400,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 67},\n",
              "     {'averageRent': 1916.44,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2800,\n",
              "      'minRent': 1100,\n",
              "      'totalRentals': 77},\n",
              "     {'averageRent': 2085.8,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2975,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 15},\n",
              "     {'averageRent': 1995,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 1995,\n",
              "      'minRent': 1995,\n",
              "      'totalRentals': 1}],\n",
              "    'maxRent': 469000,\n",
              "    'minRent': 500,\n",
              "    'totalRentals': 220},\n",
              "   '2022-04': {'averageRent': 21771.82,\n",
              "    'detailed': [{'averageRent': 121285.86,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 469000,\n",
              "      'minRent': 769,\n",
              "      'totalRentals': 7},\n",
              "     {'averageRent': 1559.8,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 2035,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 55},\n",
              "     {'averageRent': 1734.15,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 2400,\n",
              "      'minRent': 875,\n",
              "      'totalRentals': 67},\n",
              "     {'averageRent': 1961.48,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2950,\n",
              "      'minRent': 1100,\n",
              "      'totalRentals': 91},\n",
              "     {'averageRent': 2094.61,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2975,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 18},\n",
              "     {'averageRent': 1995,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 1995,\n",
              "      'minRent': 1995,\n",
              "      'totalRentals': 1}],\n",
              "    'maxRent': 469000,\n",
              "    'minRent': 769,\n",
              "    'totalRentals': 239},\n",
              "   '2022-05': {'averageRent': 25126.24,\n",
              "    'detailed': [{'averageRent': 141359.17,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 469000,\n",
              "      'minRent': 769,\n",
              "      'totalRentals': 6},\n",
              "     {'averageRent': 1613.31,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 2035,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 64},\n",
              "     {'averageRent': 1796.96,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 3000,\n",
              "      'minRent': 975,\n",
              "      'totalRentals': 69},\n",
              "     {'averageRent': 1943.76,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 2950,\n",
              "      'minRent': 1350,\n",
              "      'totalRentals': 94},\n",
              "     {'averageRent': 2049.23,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2975,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 22},\n",
              "     {'averageRent': 1995,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 1995,\n",
              "      'minRent': 1995,\n",
              "      'totalRentals': 1}],\n",
              "    'maxRent': 469000,\n",
              "    'minRent': 769,\n",
              "    'totalRentals': 256},\n",
              "   '2022-06': {'averageRent': 21264.16,\n",
              "    'detailed': [{'averageRent': 118059.5,\n",
              "      'bedrooms': 0,\n",
              "      'maxRent': 469000,\n",
              "      'minRent': 769,\n",
              "      'totalRentals': 4},\n",
              "     {'averageRent': 1590.2,\n",
              "      'bedrooms': 1,\n",
              "      'maxRent': 1897,\n",
              "      'minRent': 850,\n",
              "      'totalRentals': 64},\n",
              "     {'averageRent': 1825.64,\n",
              "      'bedrooms': 2,\n",
              "      'maxRent': 3000,\n",
              "      'minRent': 985,\n",
              "      'totalRentals': 70},\n",
              "     {'averageRent': 2002.55,\n",
              "      'bedrooms': 3,\n",
              "      'maxRent': 3500,\n",
              "      'minRent': 1350,\n",
              "      'totalRentals': 96},\n",
              "     {'averageRent': 2060.08,\n",
              "      'bedrooms': 4,\n",
              "      'maxRent': 2975,\n",
              "      'minRent': 1200,\n",
              "      'totalRentals': 26},\n",
              "     {'averageRent': 2047,\n",
              "      'bedrooms': 5,\n",
              "      'maxRent': 2099,\n",
              "      'minRent': 1995,\n",
              "      'totalRentals': 2}],\n",
              "    'maxRent': 469000,\n",
              "    'minRent': 769,\n",
              "    'totalRentals': 262}},\n",
              "  'maxRent': 469000,\n",
              "  'minRent': 769,\n",
              "  'totalRentals': 262}}"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# transform data to pandas dataframe\n",
        "df_rent = pd.json_normalize(data=response.json())\n",
        "print('Num of rows:', len(df_rent))\n",
        "print('Num of cols:', len(df_rent.columns))\n",
        "df_rent.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 261
        },
        "id": "Oe8vC868kVVJ",
        "outputId": "40f06d2e-c41b-45d7-faf9-4d313509434d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Num of rows: 1\n",
            "Num of cols: 141\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "      id  rentalData.averageRent  rentalData.minRent  rentalData.maxRent  \\\n",
              "0  33610                21264.16                 769              469000   \n",
              "\n",
              "   rentalData.totalRentals                                rentalData.detailed  \\\n",
              "0                      262  [{'bedrooms': 0, 'averageRent': 118059.5, 'min...   \n",
              "\n",
              "   rentalData.history.2020-04.averageRent  rentalData.history.2020-04.minRent  \\\n",
              "0                                 1367.13                                 744   \n",
              "\n",
              "   rentalData.history.2020-04.maxRent  \\\n",
              "0                               49900   \n",
              "\n",
              "   rentalData.history.2020-04.totalRentals  ...  \\\n",
              "0                                      186  ...   \n",
              "\n",
              "  rentalData.history.2022-05.averageRent  rentalData.history.2022-05.minRent  \\\n",
              "0                               25126.24                                 769   \n",
              "\n",
              "   rentalData.history.2022-05.maxRent  \\\n",
              "0                              469000   \n",
              "\n",
              "   rentalData.history.2022-05.totalRentals  \\\n",
              "0                                      256   \n",
              "\n",
              "                 rentalData.history.2022-05.detailed  \\\n",
              "0  [{'bedrooms': 0, 'averageRent': 141359.17, 'mi...   \n",
              "\n",
              "  rentalData.history.2022-06.averageRent  rentalData.history.2022-06.minRent  \\\n",
              "0                               21264.16                                 769   \n",
              "\n",
              "   rentalData.history.2022-06.maxRent  \\\n",
              "0                              469000   \n",
              "\n",
              "   rentalData.history.2022-06.totalRentals  \\\n",
              "0                                      262   \n",
              "\n",
              "                 rentalData.history.2022-06.detailed  \n",
              "0  [{'bedrooms': 0, 'averageRent': 118059.5, 'min...  \n",
              "\n",
              "[1 rows x 141 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-215010e0-bad8-4958-9592-1cb3b867255d\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>id</th>\n",
              "      <th>rentalData.averageRent</th>\n",
              "      <th>rentalData.minRent</th>\n",
              "      <th>rentalData.maxRent</th>\n",
              "      <th>rentalData.totalRentals</th>\n",
              "      <th>rentalData.detailed</th>\n",
              "      <th>rentalData.history.2020-04.averageRent</th>\n",
              "      <th>rentalData.history.2020-04.minRent</th>\n",
              "      <th>rentalData.history.2020-04.maxRent</th>\n",
              "      <th>rentalData.history.2020-04.totalRentals</th>\n",
              "      <th>...</th>\n",
              "      <th>rentalData.history.2022-05.averageRent</th>\n",
              "      <th>rentalData.history.2022-05.minRent</th>\n",
              "      <th>rentalData.history.2022-05.maxRent</th>\n",
              "      <th>rentalData.history.2022-05.totalRentals</th>\n",
              "      <th>rentalData.history.2022-05.detailed</th>\n",
              "      <th>rentalData.history.2022-06.averageRent</th>\n",
              "      <th>rentalData.history.2022-06.minRent</th>\n",
              "      <th>rentalData.history.2022-06.maxRent</th>\n",
              "      <th>rentalData.history.2022-06.totalRentals</th>\n",
              "      <th>rentalData.history.2022-06.detailed</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>33610</td>\n",
              "      <td>21264.16</td>\n",
              "      <td>769</td>\n",
              "      <td>469000</td>\n",
              "      <td>262</td>\n",
              "      <td>[{'bedrooms': 0, 'averageRent': 118059.5, 'min...</td>\n",
              "      <td>1367.13</td>\n",
              "      <td>744</td>\n",
              "      <td>49900</td>\n",
              "      <td>186</td>\n",
              "      <td>...</td>\n",
              "      <td>25126.24</td>\n",
              "      <td>769</td>\n",
              "      <td>469000</td>\n",
              "      <td>256</td>\n",
              "      <td>[{'bedrooms': 0, 'averageRent': 141359.17, 'mi...</td>\n",
              "      <td>21264.16</td>\n",
              "      <td>769</td>\n",
              "      <td>469000</td>\n",
              "      <td>262</td>\n",
              "      <td>[{'bedrooms': 0, 'averageRent': 118059.5, 'min...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1 rows × 141 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-215010e0-bad8-4958-9592-1cb3b867255d')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-215010e0-bad8-4958-9592-1cb3b867255d button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-215010e0-bad8-4958-9592-1cb3b867255d');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Get historical rental data"
      ],
      "metadata": {
        "id": "5fBYQoq_UjUW"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_list = [] # create empty list\n",
        "# get all \"detailed\" columns\n",
        "rent_detail_hist_cols = [x for x in df_rent.columns if 'detailed' and 'history' in x]\n",
        "# iterate through \"detailed\" columns\n",
        "for x in rent_detail_hist_cols:\n",
        "  # get column date\n",
        "  date_str = x.split('.')[2]\n",
        "  # get column name\n",
        "  detail_col = 'rentalData.history.' + date_str + '.detailed'\n",
        "  # convert historical data to a dataframe\n",
        "  _df = pd.DataFrame(df_rent[detail_col].iloc[0])\n",
        "  # create columns\n",
        "  _df['date_str'] = date_str\n",
        "  _df['zip_code'] = zip_code\n",
        "  # append to list\n",
        "  df_list.append(_df)"
      ],
      "metadata": {
        "id": "88moojFWpflf"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# comine sub date dataframes\n",
        "df_detail = pd.concat(df_list)\n",
        "# move date column to front\n",
        "df_detail = move_col_to_front(df=df_detail, col_name='date_str')\n",
        "df_detail = move_col_to_front(df=df_detail, col_name='zip_code')\n",
        "# add feature for previous year\n",
        "df_detail['prev_yr_dt'] = df_detail.apply(lambda x: (datetime.strptime(x['date_str'], '%Y-%m') - relativedelta(years=1)).strftime('%Y-%m'), axis=1)\n",
        "df_detail.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "UANqCCRRTAK6",
        "outputId": "fe96f100-9d3f-49d4-82a6-0c00270e161c"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  zip_code date_str  bedrooms  averageRent  minRent  maxRent  totalRentals  \\\n",
              "0    33610  2020-04         0       826.00      744      959             3   \n",
              "1    33610  2020-04         1       920.00      800     1040             2   \n",
              "2    33610  2020-04         2      1121.51      842     1450            35   \n",
              "3    33610  2020-04         3      2103.31      950    49900           124   \n",
              "4    33610  2020-04         4      1445.26     1100     2150            19   \n",
              "\n",
              "  prev_yr_dt  \n",
              "0    2019-04  \n",
              "1    2019-04  \n",
              "2    2019-04  \n",
              "3    2019-04  \n",
              "4    2019-04  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-1816abbf-b4bd-4059-aeeb-ef7f57788420\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>zip_code</th>\n",
              "      <th>date_str</th>\n",
              "      <th>bedrooms</th>\n",
              "      <th>averageRent</th>\n",
              "      <th>minRent</th>\n",
              "      <th>maxRent</th>\n",
              "      <th>totalRentals</th>\n",
              "      <th>prev_yr_dt</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>33610</td>\n",
              "      <td>2020-04</td>\n",
              "      <td>0</td>\n",
              "      <td>826.00</td>\n",
              "      <td>744</td>\n",
              "      <td>959</td>\n",
              "      <td>3</td>\n",
              "      <td>2019-04</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>33610</td>\n",
              "      <td>2020-04</td>\n",
              "      <td>1</td>\n",
              "      <td>920.00</td>\n",
              "      <td>800</td>\n",
              "      <td>1040</td>\n",
              "      <td>2</td>\n",
              "      <td>2019-04</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>33610</td>\n",
              "      <td>2020-04</td>\n",
              "      <td>2</td>\n",
              "      <td>1121.51</td>\n",
              "      <td>842</td>\n",
              "      <td>1450</td>\n",
              "      <td>35</td>\n",
              "      <td>2019-04</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>33610</td>\n",
              "      <td>2020-04</td>\n",
              "      <td>3</td>\n",
              "      <td>2103.31</td>\n",
              "      <td>950</td>\n",
              "      <td>49900</td>\n",
              "      <td>124</td>\n",
              "      <td>2019-04</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>33610</td>\n",
              "      <td>2020-04</td>\n",
              "      <td>4</td>\n",
              "      <td>1445.26</td>\n",
              "      <td>1100</td>\n",
              "      <td>2150</td>\n",
              "      <td>19</td>\n",
              "      <td>2019-04</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-1816abbf-b4bd-4059-aeeb-ef7f57788420')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-1816abbf-b4bd-4059-aeeb-ef7f57788420 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-1816abbf-b4bd-4059-aeeb-ef7f57788420');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Filter and add features"
      ],
      "metadata": {
        "id": "D9cnzET2VeHb"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_detail_prev = df_detail.copy()\n",
        "df_detail_prev.columns = [x + '_prev_yr' for x in df_detail_prev.columns]\n",
        "# merge\n",
        "df_merge = pd.merge(df_detail, df_detail_prev, how='left', left_on=['prev_yr_dt', 'bedrooms'], right_on=['date_str_prev_yr', 'bedrooms_prev_yr']).drop_duplicates()\n",
        "# identify outliers\n",
        "df_merge['averageRent_zscore'] = df_merge[['averageRent']].apply(zscore)\n",
        "# view latest two months\n",
        "df_merge.tail(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 427
        },
        "id": "g5xhNHkRr6Ox",
        "outputId": "12b990c2-bf7f-4881-f885-13df1fec464f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "     zip_code date_str  bedrooms  averageRent  minRent  maxRent  totalRentals  \\\n",
              "2135    33610  2022-05         2      1796.96      975     3000            69   \n",
              "2140    33610  2022-05         3      1943.76     1350     2950            94   \n",
              "2145    33610  2022-05         4      2049.23     1200     2975            22   \n",
              "2150    33610  2022-05         5      1995.00     1995     1995             1   \n",
              "2275    33610  2022-06         0    118059.50      769   469000             4   \n",
              "2280    33610  2022-06         1      1590.20      850     1897            64   \n",
              "2281    33610  2022-06         2      1825.64      985     3000            70   \n",
              "2286    33610  2022-06         3      2002.55     1350     3500            96   \n",
              "2291    33610  2022-06         4      2060.08     1200     2975            26   \n",
              "2296    33610  2022-06         5      2047.00     1995     2099             2   \n",
              "\n",
              "     prev_yr_dt zip_code_prev_yr date_str_prev_yr  bedrooms_prev_yr  \\\n",
              "2135    2021-05            33610          2021-05               2.0   \n",
              "2140    2021-05            33610          2021-05               3.0   \n",
              "2145    2021-05            33610          2021-05               4.0   \n",
              "2150    2021-05            33610          2021-05               5.0   \n",
              "2275    2021-06            33610          2021-06               0.0   \n",
              "2280    2021-06              NaN              NaN               NaN   \n",
              "2281    2021-06            33610          2021-06               2.0   \n",
              "2286    2021-06            33610          2021-06               3.0   \n",
              "2291    2021-06            33610          2021-06               4.0   \n",
              "2296    2021-06            33610          2021-06               5.0   \n",
              "\n",
              "      averageRent_prev_yr  minRent_prev_yr  maxRent_prev_yr  \\\n",
              "2135              1219.70            800.0           1700.0   \n",
              "2140              1479.40           1025.0           2700.0   \n",
              "2145              1540.44           1395.0           1699.0   \n",
              "2150              2322.50           1695.0           2950.0   \n",
              "2275              1018.00            822.0           1214.0   \n",
              "2280                  NaN              NaN              NaN   \n",
              "2281              1202.78            800.0           1700.0   \n",
              "2286              1494.50           1025.0           2700.0   \n",
              "2291              1563.00           1395.0           1699.0   \n",
              "2296              2322.50           1695.0           2950.0   \n",
              "\n",
              "      totalRentals_prev_yr prev_yr_dt_prev_yr  averageRent_zscore  \n",
              "2135                  20.0            2020-05           -0.276613  \n",
              "2140                  45.0            2020-05           -0.272458  \n",
              "2145                   9.0            2020-05           -0.269473  \n",
              "2150                   2.0            2020-05           -0.271008  \n",
              "2275                   2.0            2020-06            3.014071  \n",
              "2280                   NaN                NaN           -0.282465  \n",
              "2281                  18.0            2020-06           -0.275801  \n",
              "2286                  44.0            2020-06           -0.270794  \n",
              "2291                   8.0            2020-06           -0.269166  \n",
              "2296                   2.0            2020-06           -0.269536  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-a88e0750-6cc7-442e-90f9-1a48fb53fcd4\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>zip_code</th>\n",
              "      <th>date_str</th>\n",
              "      <th>bedrooms</th>\n",
              "      <th>averageRent</th>\n",
              "      <th>minRent</th>\n",
              "      <th>maxRent</th>\n",
              "      <th>totalRentals</th>\n",
              "      <th>prev_yr_dt</th>\n",
              "      <th>zip_code_prev_yr</th>\n",
              "      <th>date_str_prev_yr</th>\n",
              "      <th>bedrooms_prev_yr</th>\n",
              "      <th>averageRent_prev_yr</th>\n",
              "      <th>minRent_prev_yr</th>\n",
              "      <th>maxRent_prev_yr</th>\n",
              "      <th>totalRentals_prev_yr</th>\n",
              "      <th>prev_yr_dt_prev_yr</th>\n",
              "      <th>averageRent_zscore</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>2135</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-05</td>\n",
              "      <td>2</td>\n",
              "      <td>1796.96</td>\n",
              "      <td>975</td>\n",
              "      <td>3000</td>\n",
              "      <td>69</td>\n",
              "      <td>2021-05</td>\n",
              "      <td>33610</td>\n",
              "      <td>2021-05</td>\n",
              "      <td>2.0</td>\n",
              "      <td>1219.70</td>\n",
              "      <td>800.0</td>\n",
              "      <td>1700.0</td>\n",
              "      <td>20.0</td>\n",
              "      <td>2020-05</td>\n",
              "      <td>-0.276613</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2140</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-05</td>\n",
              "      <td>3</td>\n",
              "      <td>1943.76</td>\n",
              "      <td>1350</td>\n",
              "      <td>2950</td>\n",
              "      <td>94</td>\n",
              "      <td>2021-05</td>\n",
              "      <td>33610</td>\n",
              "      <td>2021-05</td>\n",
              "      <td>3.0</td>\n",
              "      <td>1479.40</td>\n",
              "      <td>1025.0</td>\n",
              "      <td>2700.0</td>\n",
              "      <td>45.0</td>\n",
              "      <td>2020-05</td>\n",
              "      <td>-0.272458</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2145</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-05</td>\n",
              "      <td>4</td>\n",
              "      <td>2049.23</td>\n",
              "      <td>1200</td>\n",
              "      <td>2975</td>\n",
              "      <td>22</td>\n",
              "      <td>2021-05</td>\n",
              "      <td>33610</td>\n",
              "      <td>2021-05</td>\n",
              "      <td>4.0</td>\n",
              "      <td>1540.44</td>\n",
              "      <td>1395.0</td>\n",
              "      <td>1699.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>2020-05</td>\n",
              "      <td>-0.269473</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2150</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-05</td>\n",
              "      <td>5</td>\n",
              "      <td>1995.00</td>\n",
              "      <td>1995</td>\n",
              "      <td>1995</td>\n",
              "      <td>1</td>\n",
              "      <td>2021-05</td>\n",
              "      <td>33610</td>\n",
              "      <td>2021-05</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2322.50</td>\n",
              "      <td>1695.0</td>\n",
              "      <td>2950.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>2020-05</td>\n",
              "      <td>-0.271008</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2275</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-06</td>\n",
              "      <td>0</td>\n",
              "      <td>118059.50</td>\n",
              "      <td>769</td>\n",
              "      <td>469000</td>\n",
              "      <td>4</td>\n",
              "      <td>2021-06</td>\n",
              "      <td>33610</td>\n",
              "      <td>2021-06</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1018.00</td>\n",
              "      <td>822.0</td>\n",
              "      <td>1214.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>2020-06</td>\n",
              "      <td>3.014071</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2280</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-06</td>\n",
              "      <td>1</td>\n",
              "      <td>1590.20</td>\n",
              "      <td>850</td>\n",
              "      <td>1897</td>\n",
              "      <td>64</td>\n",
              "      <td>2021-06</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>-0.282465</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2281</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-06</td>\n",
              "      <td>2</td>\n",
              "      <td>1825.64</td>\n",
              "      <td>985</td>\n",
              "      <td>3000</td>\n",
              "      <td>70</td>\n",
              "      <td>2021-06</td>\n",
              "      <td>33610</td>\n",
              "      <td>2021-06</td>\n",
              "      <td>2.0</td>\n",
              "      <td>1202.78</td>\n",
              "      <td>800.0</td>\n",
              "      <td>1700.0</td>\n",
              "      <td>18.0</td>\n",
              "      <td>2020-06</td>\n",
              "      <td>-0.275801</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2286</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-06</td>\n",
              "      <td>3</td>\n",
              "      <td>2002.55</td>\n",
              "      <td>1350</td>\n",
              "      <td>3500</td>\n",
              "      <td>96</td>\n",
              "      <td>2021-06</td>\n",
              "      <td>33610</td>\n",
              "      <td>2021-06</td>\n",
              "      <td>3.0</td>\n",
              "      <td>1494.50</td>\n",
              "      <td>1025.0</td>\n",
              "      <td>2700.0</td>\n",
              "      <td>44.0</td>\n",
              "      <td>2020-06</td>\n",
              "      <td>-0.270794</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2291</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-06</td>\n",
              "      <td>4</td>\n",
              "      <td>2060.08</td>\n",
              "      <td>1200</td>\n",
              "      <td>2975</td>\n",
              "      <td>26</td>\n",
              "      <td>2021-06</td>\n",
              "      <td>33610</td>\n",
              "      <td>2021-06</td>\n",
              "      <td>4.0</td>\n",
              "      <td>1563.00</td>\n",
              "      <td>1395.0</td>\n",
              "      <td>1699.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>2020-06</td>\n",
              "      <td>-0.269166</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2296</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-06</td>\n",
              "      <td>5</td>\n",
              "      <td>2047.00</td>\n",
              "      <td>1995</td>\n",
              "      <td>2099</td>\n",
              "      <td>2</td>\n",
              "      <td>2021-06</td>\n",
              "      <td>33610</td>\n",
              "      <td>2021-06</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2322.50</td>\n",
              "      <td>1695.0</td>\n",
              "      <td>2950.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>2020-06</td>\n",
              "      <td>-0.269536</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-a88e0750-6cc7-442e-90f9-1a48fb53fcd4')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-a88e0750-6cc7-442e-90f9-1a48fb53fcd4 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-a88e0750-6cc7-442e-90f9-1a48fb53fcd4');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# filter\n",
        "df_filter = df_merge.copy()\n",
        "df_filter = df_filter.loc[(df_filter['bedrooms'] <= 4) & (df_filter['averageRent_zscore'] <= 2)]\n",
        "df_filter = df_filter.drop(columns=['prev_yr_dt', 'date_str_prev_yr', 'prev_yr_dt_prev_yr', 'zip_code_prev_yr'])\n",
        "# add features\n",
        "df_filter['YoY_avg_rent'] = \\\n",
        "  (df_filter['averageRent'] - df_filter['averageRent_prev_yr']) / df_filter['averageRent_prev_yr']\n",
        "df_filter['YoY_total_rentals'] = \\\n",
        "  (df_filter['totalRentals'] - df_filter['totalRentals_prev_yr']) / df_filter['totalRentals_prev_yr']\n",
        "# view latest two months\n",
        "df_filter.tail(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 427
        },
        "id": "ACG7Ei54YOQ-",
        "outputId": "2a44ebb0-0e22-42cd-f3c7-d057a71af214"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "     zip_code date_str  bedrooms  averageRent  minRent  maxRent  totalRentals  \\\n",
              "1990    33610  2022-04         3      1961.48     1100     2950            91   \n",
              "1995    33610  2022-04         4      2094.61     1200     2975            18   \n",
              "2130    33610  2022-05         1      1613.31      850     2035            64   \n",
              "2135    33610  2022-05         2      1796.96      975     3000            69   \n",
              "2140    33610  2022-05         3      1943.76     1350     2950            94   \n",
              "2145    33610  2022-05         4      2049.23     1200     2975            22   \n",
              "2280    33610  2022-06         1      1590.20      850     1897            64   \n",
              "2281    33610  2022-06         2      1825.64      985     3000            70   \n",
              "2286    33610  2022-06         3      2002.55     1350     3500            96   \n",
              "2291    33610  2022-06         4      2060.08     1200     2975            26   \n",
              "\n",
              "      bedrooms_prev_yr  averageRent_prev_yr  minRent_prev_yr  maxRent_prev_yr  \\\n",
              "1990               3.0              1474.38           1025.0           2700.0   \n",
              "1995               4.0              1534.89           1395.0           1699.0   \n",
              "2130               1.0               600.00            600.0            600.0   \n",
              "2135               2.0              1219.70            800.0           1700.0   \n",
              "2140               3.0              1479.40           1025.0           2700.0   \n",
              "2145               4.0              1540.44           1395.0           1699.0   \n",
              "2280               NaN                  NaN              NaN              NaN   \n",
              "2281               2.0              1202.78            800.0           1700.0   \n",
              "2286               3.0              1494.50           1025.0           2700.0   \n",
              "2291               4.0              1563.00           1395.0           1699.0   \n",
              "\n",
              "      totalRentals_prev_yr  averageRent_zscore  YoY_avg_rent  \\\n",
              "1990                  45.0           -0.271957      0.330376   \n",
              "1995                   9.0           -0.268188      0.364665   \n",
              "2130                   1.0           -0.281811      1.688850   \n",
              "2135                  20.0           -0.276613      0.473280   \n",
              "2140                  45.0           -0.272458      0.313884   \n",
              "2145                   9.0           -0.269473      0.330289   \n",
              "2280                   NaN           -0.282465           NaN   \n",
              "2281                  18.0           -0.275801      0.517850   \n",
              "2286                  44.0           -0.270794      0.339946   \n",
              "2291                   8.0           -0.269166      0.318029   \n",
              "\n",
              "      YoY_total_rentals  \n",
              "1990           1.022222  \n",
              "1995           1.000000  \n",
              "2130          63.000000  \n",
              "2135           2.450000  \n",
              "2140           1.088889  \n",
              "2145           1.444444  \n",
              "2280                NaN  \n",
              "2281           2.888889  \n",
              "2286           1.181818  \n",
              "2291           2.250000  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-b36c3cc6-ae0b-491c-b577-47a581a28356\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>zip_code</th>\n",
              "      <th>date_str</th>\n",
              "      <th>bedrooms</th>\n",
              "      <th>averageRent</th>\n",
              "      <th>minRent</th>\n",
              "      <th>maxRent</th>\n",
              "      <th>totalRentals</th>\n",
              "      <th>bedrooms_prev_yr</th>\n",
              "      <th>averageRent_prev_yr</th>\n",
              "      <th>minRent_prev_yr</th>\n",
              "      <th>maxRent_prev_yr</th>\n",
              "      <th>totalRentals_prev_yr</th>\n",
              "      <th>averageRent_zscore</th>\n",
              "      <th>YoY_avg_rent</th>\n",
              "      <th>YoY_total_rentals</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1990</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-04</td>\n",
              "      <td>3</td>\n",
              "      <td>1961.48</td>\n",
              "      <td>1100</td>\n",
              "      <td>2950</td>\n",
              "      <td>91</td>\n",
              "      <td>3.0</td>\n",
              "      <td>1474.38</td>\n",
              "      <td>1025.0</td>\n",
              "      <td>2700.0</td>\n",
              "      <td>45.0</td>\n",
              "      <td>-0.271957</td>\n",
              "      <td>0.330376</td>\n",
              "      <td>1.022222</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1995</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-04</td>\n",
              "      <td>4</td>\n",
              "      <td>2094.61</td>\n",
              "      <td>1200</td>\n",
              "      <td>2975</td>\n",
              "      <td>18</td>\n",
              "      <td>4.0</td>\n",
              "      <td>1534.89</td>\n",
              "      <td>1395.0</td>\n",
              "      <td>1699.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>-0.268188</td>\n",
              "      <td>0.364665</td>\n",
              "      <td>1.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2130</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-05</td>\n",
              "      <td>1</td>\n",
              "      <td>1613.31</td>\n",
              "      <td>850</td>\n",
              "      <td>2035</td>\n",
              "      <td>64</td>\n",
              "      <td>1.0</td>\n",
              "      <td>600.00</td>\n",
              "      <td>600.0</td>\n",
              "      <td>600.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>-0.281811</td>\n",
              "      <td>1.688850</td>\n",
              "      <td>63.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2135</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-05</td>\n",
              "      <td>2</td>\n",
              "      <td>1796.96</td>\n",
              "      <td>975</td>\n",
              "      <td>3000</td>\n",
              "      <td>69</td>\n",
              "      <td>2.0</td>\n",
              "      <td>1219.70</td>\n",
              "      <td>800.0</td>\n",
              "      <td>1700.0</td>\n",
              "      <td>20.0</td>\n",
              "      <td>-0.276613</td>\n",
              "      <td>0.473280</td>\n",
              "      <td>2.450000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2140</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-05</td>\n",
              "      <td>3</td>\n",
              "      <td>1943.76</td>\n",
              "      <td>1350</td>\n",
              "      <td>2950</td>\n",
              "      <td>94</td>\n",
              "      <td>3.0</td>\n",
              "      <td>1479.40</td>\n",
              "      <td>1025.0</td>\n",
              "      <td>2700.0</td>\n",
              "      <td>45.0</td>\n",
              "      <td>-0.272458</td>\n",
              "      <td>0.313884</td>\n",
              "      <td>1.088889</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2145</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-05</td>\n",
              "      <td>4</td>\n",
              "      <td>2049.23</td>\n",
              "      <td>1200</td>\n",
              "      <td>2975</td>\n",
              "      <td>22</td>\n",
              "      <td>4.0</td>\n",
              "      <td>1540.44</td>\n",
              "      <td>1395.0</td>\n",
              "      <td>1699.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>-0.269473</td>\n",
              "      <td>0.330289</td>\n",
              "      <td>1.444444</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2280</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-06</td>\n",
              "      <td>1</td>\n",
              "      <td>1590.20</td>\n",
              "      <td>850</td>\n",
              "      <td>1897</td>\n",
              "      <td>64</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>-0.282465</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2281</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-06</td>\n",
              "      <td>2</td>\n",
              "      <td>1825.64</td>\n",
              "      <td>985</td>\n",
              "      <td>3000</td>\n",
              "      <td>70</td>\n",
              "      <td>2.0</td>\n",
              "      <td>1202.78</td>\n",
              "      <td>800.0</td>\n",
              "      <td>1700.0</td>\n",
              "      <td>18.0</td>\n",
              "      <td>-0.275801</td>\n",
              "      <td>0.517850</td>\n",
              "      <td>2.888889</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2286</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-06</td>\n",
              "      <td>3</td>\n",
              "      <td>2002.55</td>\n",
              "      <td>1350</td>\n",
              "      <td>3500</td>\n",
              "      <td>96</td>\n",
              "      <td>3.0</td>\n",
              "      <td>1494.50</td>\n",
              "      <td>1025.0</td>\n",
              "      <td>2700.0</td>\n",
              "      <td>44.0</td>\n",
              "      <td>-0.270794</td>\n",
              "      <td>0.339946</td>\n",
              "      <td>1.181818</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2291</th>\n",
              "      <td>33610</td>\n",
              "      <td>2022-06</td>\n",
              "      <td>4</td>\n",
              "      <td>2060.08</td>\n",
              "      <td>1200</td>\n",
              "      <td>2975</td>\n",
              "      <td>26</td>\n",
              "      <td>4.0</td>\n",
              "      <td>1563.00</td>\n",
              "      <td>1395.0</td>\n",
              "      <td>1699.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>-0.269166</td>\n",
              "      <td>0.318029</td>\n",
              "      <td>2.250000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b36c3cc6-ae0b-491c-b577-47a581a28356')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-b36c3cc6-ae0b-491c-b577-47a581a28356 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-b36c3cc6-ae0b-491c-b577-47a581a28356');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### <font color=\"green\">2. Multiple Zip Codes - All for a county</font>"
      ],
      "metadata": {
        "id": "PsewsAZfSSXc"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Get county, zip data"
      ],
      "metadata": {
        "id": "m8lTdtxjYeW7"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "geo_data_url = 'https://raw.githubusercontent.com/scpike/us-state-county-zip/master/geo-data.csv'\n",
        "df_geo = pd.read_csv(geo_data_url)\n",
        "print('Num of rows:', len(df_geo))\n",
        "print('Num of columns:', len(df_geo.columns))\n",
        "df_geo.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 241
        },
        "id": "K5btF8uRSj-Q",
        "outputId": "e4aee2b7-f0f6-4594-cb24-1045a99b99d1"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Num of rows: 33103\n",
            "Num of columns: 6\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   state_fips    state state_abbr zipcode      county        city\n",
              "0           1  Alabama         AL   35004   St. Clair       Acmar\n",
              "1           1  Alabama         AL   35005   Jefferson  Adamsville\n",
              "2           1  Alabama         AL   35006   Jefferson       Adger\n",
              "3           1  Alabama         AL   35007      Shelby    Keystone\n",
              "4           1  Alabama         AL   35010  Tallapoosa    New site"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-806cb912-b090-4d1c-b47e-d38519f7a334\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>state_fips</th>\n",
              "      <th>state</th>\n",
              "      <th>state_abbr</th>\n",
              "      <th>zipcode</th>\n",
              "      <th>county</th>\n",
              "      <th>city</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>AL</td>\n",
              "      <td>35004</td>\n",
              "      <td>St. Clair</td>\n",
              "      <td>Acmar</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>AL</td>\n",
              "      <td>35005</td>\n",
              "      <td>Jefferson</td>\n",
              "      <td>Adamsville</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>AL</td>\n",
              "      <td>35006</td>\n",
              "      <td>Jefferson</td>\n",
              "      <td>Adger</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>AL</td>\n",
              "      <td>35007</td>\n",
              "      <td>Shelby</td>\n",
              "      <td>Keystone</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>AL</td>\n",
              "      <td>35010</td>\n",
              "      <td>Tallapoosa</td>\n",
              "      <td>New site</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-806cb912-b090-4d1c-b47e-d38519f7a334')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-806cb912-b090-4d1c-b47e-d38519f7a334 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-806cb912-b090-4d1c-b47e-d38519f7a334');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 2
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# filter on single county\n",
        "df_geo_county = df_geo.loc[(df_geo['state_abbr'] == 'FL') &\n",
        "                           (df_geo['county'] == 'Hillsborough')]\n",
        "# create feature to check if zip code is valid\n",
        "df_geo_county['valid_zip_code'] = df_geo_county.apply(lambda x: x['zipcode'].isnumeric(), axis=1)\n",
        "print('Dataset:')\n",
        "print(df_geo_county.groupby(['valid_zip_code'])['zipcode'].count())\n",
        "print(' ')\n",
        "print('Invalid zip codes:', df_geo_county.loc[df_geo_county['valid_zip_code'] == False]['zipcode'].unique())"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "oh8MHEgIkkbX",
        "outputId": "b3754ad1-7e2f-4b1b-d222-652b1df09607"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Dataset:\n",
            "valid_zip_code\n",
            "False     2\n",
            "True     45\n",
            "Name: zipcode, dtype: int64\n",
            " \n",
            "Invalid zip codes: ['335HH' '336HH']\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# filter on only valid zip codes\n",
        "df_geo_county_valid = df_geo_county.loc[df_geo_county['valid_zip_code'] == True]\n",
        "print('Num of VALID zip codes:', len(df_geo_county_valid))\n",
        "df_geo_county_valid.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 288
        },
        "id": "_BfK-uH3SfFD",
        "outputId": "aaab645e-f7b5-404d-b201-29764b6fbdab"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Num of VALID zip codes: 45\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "      state_fips    state state_abbr zipcode        county       city  \\\n",
              "5129          12  Florida         FL   33510  Hillsborough    Brandon   \n",
              "5130          12  Florida         FL   33511  Hillsborough    Brandon   \n",
              "5136          12  Florida         FL   33527  Hillsborough      Dover   \n",
              "5137          12  Florida         FL   33534  Hillsborough  Gibsonton   \n",
              "5143          12  Florida         FL   33547  Hillsborough     Lithia   \n",
              "\n",
              "      valid_zip_code  \n",
              "5129            True  \n",
              "5130            True  \n",
              "5136            True  \n",
              "5137            True  \n",
              "5143            True  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-d23b22c3-0f1c-4c84-be8d-d0b758af12c3\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>state_fips</th>\n",
              "      <th>state</th>\n",
              "      <th>state_abbr</th>\n",
              "      <th>zipcode</th>\n",
              "      <th>county</th>\n",
              "      <th>city</th>\n",
              "      <th>valid_zip_code</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>5129</th>\n",
              "      <td>12</td>\n",
              "      <td>Florida</td>\n",
              "      <td>FL</td>\n",
              "      <td>33510</td>\n",
              "      <td>Hillsborough</td>\n",
              "      <td>Brandon</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5130</th>\n",
              "      <td>12</td>\n",
              "      <td>Florida</td>\n",
              "      <td>FL</td>\n",
              "      <td>33511</td>\n",
              "      <td>Hillsborough</td>\n",
              "      <td>Brandon</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5136</th>\n",
              "      <td>12</td>\n",
              "      <td>Florida</td>\n",
              "      <td>FL</td>\n",
              "      <td>33527</td>\n",
              "      <td>Hillsborough</td>\n",
              "      <td>Dover</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5137</th>\n",
              "      <td>12</td>\n",
              "      <td>Florida</td>\n",
              "      <td>FL</td>\n",
              "      <td>33534</td>\n",
              "      <td>Hillsborough</td>\n",
              "      <td>Gibsonton</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5143</th>\n",
              "      <td>12</td>\n",
              "      <td>Florida</td>\n",
              "      <td>FL</td>\n",
              "      <td>33547</td>\n",
              "      <td>Hillsborough</td>\n",
              "      <td>Lithia</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d23b22c3-0f1c-4c84-be8d-d0b758af12c3')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-d23b22c3-0f1c-4c84-be8d-d0b758af12c3 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-d23b22c3-0f1c-4c84-be8d-d0b758af12c3');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Get data for each zip code"
      ],
      "metadata": {
        "id": "9Tp5nZEiYjmA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "_df_latest_list = []\n",
        "_df_historical_list = []\n",
        "\n",
        "# iterate through each zip code in the list\n",
        "for zip_code in df_geo_county_valid['zipcode'].tolist():\n",
        "  print('Getting data for zipcode:', zip_code)\n",
        "  \n",
        "  # for each zip code get rental data\n",
        "  response = get_rental_market_data(rapid_api_key, zip_code)\n",
        "\n",
        "  try:\n",
        "\n",
        "    # transform response\n",
        "    # 1) latest rental data\n",
        "    _df_latest = get_latest_rental_data(response)\n",
        "    _df_latest_list.append(_df_latest)\n",
        "\n",
        "    # 2) historical rental data\n",
        "    _df_historical = get_historical_rental_data(response)\n",
        "    _df_historical_list.append(_df_historical)\n",
        "\n",
        "  except:\n",
        "\n",
        "    print('**Error in retrieving data for:', zip_code)\n",
        "\n",
        "  time.sleep(1) # pause for 2 seconds (max 2 requests per second)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HulbK7etYn6N",
        "outputId": "dddf3775-5c22-43a1-c179-96d9ba6465d9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Getting data for zipcode: 33510\n",
            "Getting data for zipcode: 33511\n",
            "Getting data for zipcode: 33527\n",
            "Getting data for zipcode: 33534\n",
            "Getting data for zipcode: 33547\n",
            "Getting data for zipcode: 33549\n",
            "Getting data for zipcode: 33556\n",
            "Getting data for zipcode: 33565\n",
            "Getting data for zipcode: 33566\n",
            "Getting data for zipcode: 33567\n",
            "Getting data for zipcode: 33569\n",
            "Getting data for zipcode: 33570\n",
            "Getting data for zipcode: 33572\n",
            "Getting data for zipcode: 33573\n",
            "Getting data for zipcode: 33584\n",
            "Getting data for zipcode: 33592\n",
            "Getting data for zipcode: 33594\n",
            "Getting data for zipcode: 33598\n",
            "Getting data for zipcode: 33602\n",
            "Getting data for zipcode: 33603\n",
            "Getting data for zipcode: 33604\n",
            "Getting data for zipcode: 33605\n",
            "Getting data for zipcode: 33606\n",
            "Getting data for zipcode: 33607\n",
            "Getting data for zipcode: 33609\n",
            "Getting data for zipcode: 33610\n",
            "Getting data for zipcode: 33611\n",
            "Getting data for zipcode: 33612\n",
            "Getting data for zipcode: 33613\n",
            "Getting data for zipcode: 33614\n",
            "Getting data for zipcode: 33615\n",
            "Getting data for zipcode: 33616\n",
            "Getting data for zipcode: 33617\n",
            "Getting data for zipcode: 33618\n",
            "Getting data for zipcode: 33619\n",
            "Getting data for zipcode: 33620\n",
            "**Error in retrieving data for: 33620\n",
            "Getting data for zipcode: 33621\n",
            "Getting data for zipcode: 33624\n",
            "Getting data for zipcode: 33625\n",
            "Getting data for zipcode: 33626\n",
            "Getting data for zipcode: 33629\n",
            "Getting data for zipcode: 33634\n",
            "Getting data for zipcode: 33635\n",
            "Getting data for zipcode: 33637\n",
            "Getting data for zipcode: 33647\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# concat\n",
        "df_latest = pd.concat(_df_latest_list)\n",
        "df_latest.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 250
        },
        "id": "Fx79f7m_aOaX",
        "outputId": "0e7068e6-d318-4f66-c045-49f755220813"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  zip_code  rentalData.averageRent  rentalData.minRent  rentalData.maxRent  \\\n",
              "0    33510                 2253.22                 650                4300   \n",
              "0    33511                 2148.86                1100                4090   \n",
              "0    33527                 3901.67                2495                4950   \n",
              "0    33534                 1785.64                 800                2965   \n",
              "0    33547                 2222.97                1200                3645   \n",
              "\n",
              "   rentalData.totalRentals                                rentalData.detailed  \n",
              "0                      243  [{'bedrooms': 0, 'averageRent': 1597.33, 'minR...  \n",
              "0                      946  [{'bedrooms': 0, 'averageRent': 1573.63, 'minR...  \n",
              "0                       10  [{'bedrooms': 3, 'averageRent': 4950, 'minRent...  \n",
              "0                      120  [{'bedrooms': 0, 'averageRent': 1443.33, 'minR...  \n",
              "0                      176  [{'bedrooms': 0, 'averageRent': 1624.46, 'minR...  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-fda42ddf-770a-4ba5-b57a-8556e3531caa\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>zip_code</th>\n",
              "      <th>rentalData.averageRent</th>\n",
              "      <th>rentalData.minRent</th>\n",
              "      <th>rentalData.maxRent</th>\n",
              "      <th>rentalData.totalRentals</th>\n",
              "      <th>rentalData.detailed</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>33510</td>\n",
              "      <td>2253.22</td>\n",
              "      <td>650</td>\n",
              "      <td>4300</td>\n",
              "      <td>243</td>\n",
              "      <td>[{'bedrooms': 0, 'averageRent': 1597.33, 'minR...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>33511</td>\n",
              "      <td>2148.86</td>\n",
              "      <td>1100</td>\n",
              "      <td>4090</td>\n",
              "      <td>946</td>\n",
              "      <td>[{'bedrooms': 0, 'averageRent': 1573.63, 'minR...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>33527</td>\n",
              "      <td>3901.67</td>\n",
              "      <td>2495</td>\n",
              "      <td>4950</td>\n",
              "      <td>10</td>\n",
              "      <td>[{'bedrooms': 3, 'averageRent': 4950, 'minRent...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>33534</td>\n",
              "      <td>1785.64</td>\n",
              "      <td>800</td>\n",
              "      <td>2965</td>\n",
              "      <td>120</td>\n",
              "      <td>[{'bedrooms': 0, 'averageRent': 1443.33, 'minR...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>33547</td>\n",
              "      <td>2222.97</td>\n",
              "      <td>1200</td>\n",
              "      <td>3645</td>\n",
              "      <td>176</td>\n",
              "      <td>[{'bedrooms': 0, 'averageRent': 1624.46, 'minR...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-fda42ddf-770a-4ba5-b57a-8556e3531caa')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-fda42ddf-770a-4ba5-b57a-8556e3531caa button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-fda42ddf-770a-4ba5-b57a-8556e3531caa');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# concat\n",
        "df_historical = pd.concat(_df_historical_list)\n",
        "df_historical.groupby(['zip_code'])['bedrooms'].count().reset_index().rename(columns={'bedrooms': 'num_records'})"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "id": "jpO_A3Hja2ii",
        "outputId": "bd543842-05e0-47f8-8664-a2112a0d63a6"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   zip_code  num_records\n",
              "0     33510          135\n",
              "1     33511          135\n",
              "2     33527           72\n",
              "3     33534          125\n",
              "4     33547          120\n",
              "5     33549          129\n",
              "6     33556          126\n",
              "7     33565           68\n",
              "8     33566          107\n",
              "9     33567           78\n",
              "10    33569           79\n",
              "11    33570          121\n",
              "12    33572          106\n",
              "13    33573          130\n",
              "14    33584          122\n",
              "15    33592          101\n",
              "16    33594          132\n",
              "17    33598          102\n",
              "18    33602          135\n",
              "19    33603          135\n",
              "20    33604          128\n",
              "21    33605          132\n",
              "22    33606          130\n",
              "23    33607          134\n",
              "24    33609          135\n",
              "25    33610          122\n",
              "26    33611          135\n",
              "27    33612          135\n",
              "28    33613          135\n",
              "29    33614          134\n",
              "30    33615          123\n",
              "31    33616          133\n",
              "32    33617          135\n",
              "33    33618          135\n",
              "34    33619          135\n",
              "35    33624          135\n",
              "36    33625          124\n",
              "37    33626          134\n",
              "38    33629          132\n",
              "39    33634          135\n",
              "40    33635          119\n",
              "41    33637          135\n",
              "42    33647          135"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-102d5a30-947b-42bc-b3bb-c9dcbc598d50\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>zip_code</th>\n",
              "      <th>num_records</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>33510</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>33511</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>33527</td>\n",
              "      <td>72</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>33534</td>\n",
              "      <td>125</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>33547</td>\n",
              "      <td>120</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>33549</td>\n",
              "      <td>129</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>33556</td>\n",
              "      <td>126</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>33565</td>\n",
              "      <td>68</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>33566</td>\n",
              "      <td>107</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>33567</td>\n",
              "      <td>78</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>33569</td>\n",
              "      <td>79</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>33570</td>\n",
              "      <td>121</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>33572</td>\n",
              "      <td>106</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>33573</td>\n",
              "      <td>130</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>33584</td>\n",
              "      <td>122</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>33592</td>\n",
              "      <td>101</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>33594</td>\n",
              "      <td>132</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>33598</td>\n",
              "      <td>102</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>33602</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>33603</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20</th>\n",
              "      <td>33604</td>\n",
              "      <td>128</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>33605</td>\n",
              "      <td>132</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>22</th>\n",
              "      <td>33606</td>\n",
              "      <td>130</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>23</th>\n",
              "      <td>33607</td>\n",
              "      <td>134</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24</th>\n",
              "      <td>33609</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25</th>\n",
              "      <td>33610</td>\n",
              "      <td>122</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>26</th>\n",
              "      <td>33611</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>27</th>\n",
              "      <td>33612</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28</th>\n",
              "      <td>33613</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>29</th>\n",
              "      <td>33614</td>\n",
              "      <td>134</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>30</th>\n",
              "      <td>33615</td>\n",
              "      <td>123</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31</th>\n",
              "      <td>33616</td>\n",
              "      <td>133</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>32</th>\n",
              "      <td>33617</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>33</th>\n",
              "      <td>33618</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>34</th>\n",
              "      <td>33619</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>35</th>\n",
              "      <td>33624</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>36</th>\n",
              "      <td>33625</td>\n",
              "      <td>124</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>37</th>\n",
              "      <td>33626</td>\n",
              "      <td>134</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>38</th>\n",
              "      <td>33629</td>\n",
              "      <td>132</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39</th>\n",
              "      <td>33634</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>40</th>\n",
              "      <td>33635</td>\n",
              "      <td>119</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>41</th>\n",
              "      <td>33637</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>42</th>\n",
              "      <td>33647</td>\n",
              "      <td>135</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-102d5a30-947b-42bc-b3bb-c9dcbc598d50')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-102d5a30-947b-42bc-b3bb-c9dcbc598d50 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-102d5a30-947b-42bc-b3bb-c9dcbc598d50');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## <font color=\"blue\">Output</font>"
      ],
      "metadata": {
        "id": "mEPn5N2mbL8E"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "latest_dt_str = df_historical['date_str'].max()\n",
        "print('Latest date:', latest_dt_str)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "SScFWuePbqU7",
        "outputId": "05713b2f-2d6a-4421-dcd7-b5c7fcade163"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Latest date: 2022-06\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# download file\n",
        "df_latest.to_csv('zip_rent_analysis_latest_{}.csv'.format(latest_dt_str), index=False)\n",
        "files.download('zip_rent_analysis_latest_{}.csv'.format(latest_dt_str))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 17
        },
        "id": "0e_wZdLebNR4",
        "outputId": "38a126f7-d54d-495a-e133-f22cd737339d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "\n",
              "    async function download(id, filename, size) {\n",
              "      if (!google.colab.kernel.accessAllowed) {\n",
              "        return;\n",
              "      }\n",
              "      const div = document.createElement('div');\n",
              "      const label = document.createElement('label');\n",
              "      label.textContent = `Downloading \"${filename}\": `;\n",
              "      div.appendChild(label);\n",
              "      const progress = document.createElement('progress');\n",
              "      progress.max = size;\n",
              "      div.appendChild(progress);\n",
              "      document.body.appendChild(div);\n",
              "\n",
              "      const buffers = [];\n",
              "      let downloaded = 0;\n",
              "\n",
              "      const channel = await google.colab.kernel.comms.open(id);\n",
              "      // Send a message to notify the kernel that we're ready.\n",
              "      channel.send({})\n",
              "\n",
              "      for await (const message of channel.messages) {\n",
              "        // Send a message to notify the kernel that we're ready.\n",
              "        channel.send({})\n",
              "        if (message.buffers) {\n",
              "          for (const buffer of message.buffers) {\n",
              "            buffers.push(buffer);\n",
              "            downloaded += buffer.byteLength;\n",
              "            progress.value = downloaded;\n",
              "          }\n",
              "        }\n",
              "      }\n",
              "      const blob = new Blob(buffers, {type: 'application/binary'});\n",
              "      const a = document.createElement('a');\n",
              "      a.href = window.URL.createObjectURL(blob);\n",
              "      a.download = filename;\n",
              "      div.appendChild(a);\n",
              "      a.click();\n",
              "      div.remove();\n",
              "    }\n",
              "  "
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "download(\"download_9a85a24f-df22-4561-ac63-837768b55bef\", \"zip_rent_analysis_latest_2022-06.csv\", 24307)"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_historical_context = pd.merge(df_historical, df_geo_county_valid.rename(columns={'zipcode': 'zip_code'}), how='left', on='zip_code')\n",
        "df_historical_context.head(1)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 174
        },
        "id": "viU2UpsmhHiY",
        "outputId": "f5dd3988-eab6-405d-ece6-3abf098521a9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  zip_code date_str  bedrooms  averageRent  minRent  maxRent  totalRentals  \\\n",
              "0    33510  2020-04         0       979.25      730     1281             4   \n",
              "\n",
              "   bedrooms_prev_yr  averageRent_prev_yr  minRent_prev_yr  ...  \\\n",
              "0               NaN                  NaN              NaN  ...   \n",
              "\n",
              "   totalRentals_prev_yr  averageRent_zscore  YoY_avg_rent  YoY_total_rentals  \\\n",
              "0                   NaN           -1.217757           NaN                NaN   \n",
              "\n",
              "   state_fips    state state_abbr        county     city valid_zip_code  \n",
              "0          12  Florida         FL  Hillsborough  Brandon           True  \n",
              "\n",
              "[1 rows x 21 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-2e823ada-e7a6-48f3-bf30-6383b897626d\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>zip_code</th>\n",
              "      <th>date_str</th>\n",
              "      <th>bedrooms</th>\n",
              "      <th>averageRent</th>\n",
              "      <th>minRent</th>\n",
              "      <th>maxRent</th>\n",
              "      <th>totalRentals</th>\n",
              "      <th>bedrooms_prev_yr</th>\n",
              "      <th>averageRent_prev_yr</th>\n",
              "      <th>minRent_prev_yr</th>\n",
              "      <th>...</th>\n",
              "      <th>totalRentals_prev_yr</th>\n",
              "      <th>averageRent_zscore</th>\n",
              "      <th>YoY_avg_rent</th>\n",
              "      <th>YoY_total_rentals</th>\n",
              "      <th>state_fips</th>\n",
              "      <th>state</th>\n",
              "      <th>state_abbr</th>\n",
              "      <th>county</th>\n",
              "      <th>city</th>\n",
              "      <th>valid_zip_code</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>33510</td>\n",
              "      <td>2020-04</td>\n",
              "      <td>0</td>\n",
              "      <td>979.25</td>\n",
              "      <td>730</td>\n",
              "      <td>1281</td>\n",
              "      <td>4</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>-1.217757</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>12</td>\n",
              "      <td>Florida</td>\n",
              "      <td>FL</td>\n",
              "      <td>Hillsborough</td>\n",
              "      <td>Brandon</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1 rows × 21 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-2e823ada-e7a6-48f3-bf30-6383b897626d')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-2e823ada-e7a6-48f3-bf30-6383b897626d button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-2e823ada-e7a6-48f3-bf30-6383b897626d');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 30
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# download file\n",
        "df_historical_context.to_csv('zip_rent_analysis_historical_{}.csv'.format(latest_dt_str), index=False)\n",
        "files.download('zip_rent_analysis_historical_{}.csv'.format(latest_dt_str))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 17
        },
        "id": "zQUomzIrbNJ9",
        "outputId": "2b74d53b-ede1-4db9-d016-fcadaaa369cc"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "\n",
              "    async function download(id, filename, size) {\n",
              "      if (!google.colab.kernel.accessAllowed) {\n",
              "        return;\n",
              "      }\n",
              "      const div = document.createElement('div');\n",
              "      const label = document.createElement('label');\n",
              "      label.textContent = `Downloading \"${filename}\": `;\n",
              "      div.appendChild(label);\n",
              "      const progress = document.createElement('progress');\n",
              "      progress.max = size;\n",
              "      div.appendChild(progress);\n",
              "      document.body.appendChild(div);\n",
              "\n",
              "      const buffers = [];\n",
              "      let downloaded = 0;\n",
              "\n",
              "      const channel = await google.colab.kernel.comms.open(id);\n",
              "      // Send a message to notify the kernel that we're ready.\n",
              "      channel.send({})\n",
              "\n",
              "      for await (const message of channel.messages) {\n",
              "        // Send a message to notify the kernel that we're ready.\n",
              "        channel.send({})\n",
              "        if (message.buffers) {\n",
              "          for (const buffer of message.buffers) {\n",
              "            buffers.push(buffer);\n",
              "            downloaded += buffer.byteLength;\n",
              "            progress.value = downloaded;\n",
              "          }\n",
              "        }\n",
              "      }\n",
              "      const blob = new Blob(buffers, {type: 'application/binary'});\n",
              "      const a = document.createElement('a');\n",
              "      a.href = window.URL.createObjectURL(blob);\n",
              "      a.download = filename;\n",
              "      div.appendChild(a);\n",
              "      a.click();\n",
              "      div.remove();\n",
              "    }\n",
              "  "
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "download(\"download_0196ada9-8576-4943-9259-eb1d17e4787f\", \"zip_rent_analysis_historical_2022-06.csv\", 700151)"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_WrYHVh9Ukid"
      },
      "source": [
        "# End Notebook"
      ]
    }
  ]
}