Ribbon_Data_Eng__Tim_Frazer.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Ribbon_Data_Eng_ Tim Frazer.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "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/Timfrazer/try_git/blob/master/Ribbon_Data_Eng__Tim_Frazer.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xVEk-V5lZvH8"
      },
      "source": [
        "#@title\n",
        "import requests\n",
        "import csv\n",
        "import pandas as pd\n",
        "from requests.auth import HTTPBasicAuth \n",
        "import json\n",
        "\n",
        "auth = HTTPBasicAuth('', '')\n"
      ],
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FgK__ZW0Z-0U"
      },
      "source": [
        "# Ribbon Data Eng Interview\n",
        "# SimplyRets is the API endpoint we use for MLS Listing data\n",
        "\n",
        "# You can query up to 25 records at a time, ordered by mlsId, which is\n",
        "# monotonically increasing\n",
        "\n",
        "# When you pass a lastId field into the simplyRets url, you will receive listings with\n",
        "# that ID as the highest mlsId\n",
        "\n",
        "# 1. Please create a data pipeline that will run every hour that will ingest all records modified within the past week\n",
        "# to a csv.\n",
        "\n",
        "# 2. Please create a job that will take the outputs of these recently modified rets records and deduplicates them, so we\n",
        "# have a record of the most up to date listing information per listing.\n",
        "\n",
        "# 3. [Optional] How would you write this ingestion job in a Scala/Spark/Pyspark environment? \n",
        "\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iqWAZydOaDUr"
      },
      "source": [
        "url = f\"https://api.simplyrets.com/properties?vendor=fmls&limit=25&lastId=99999999\"\n",
        "# documentation: https://docs.simplyrets.com/api/index.html#/Listings/get_properties"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_-tk-iQHbIwU"
      },
      "source": [
        "import requests\n",
        "import pprint\n",
        "import pandas as pd\n",
        "import json\n",
        "from datetime import datetime, timedelta, time\n",
        "\n",
        "\n",
        "#response = requests.get(url, \n",
        "#            auth = auth) \n",
        "\n",
        "\n",
        "\n",
        "#print(response.json())\n"
      ],
      "execution_count": 31,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aAb2TfJUbVTj",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 649
        },
        "outputId": "d4016c66-4cdb-4ffd-9e08-418450e59f8d"
      },
      "source": [
        "df = pd.DataFrame(response.json())\n",
        "\n",
        "df.head()\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<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>privateRemarks</th>\n",
              "      <th>showingContactName</th>\n",
              "      <th>property</th>\n",
              "      <th>mlsId</th>\n",
              "      <th>showingContactPhone</th>\n",
              "      <th>terms</th>\n",
              "      <th>showingInstructions</th>\n",
              "      <th>office</th>\n",
              "      <th>leaseTerm</th>\n",
              "      <th>disclaimer</th>\n",
              "      <th>specialListingConditions</th>\n",
              "      <th>originalListPrice</th>\n",
              "      <th>address</th>\n",
              "      <th>agreement</th>\n",
              "      <th>listDate</th>\n",
              "      <th>agent</th>\n",
              "      <th>modified</th>\n",
              "      <th>school</th>\n",
              "      <th>photos</th>\n",
              "      <th>listPrice</th>\n",
              "      <th>internetAddressDisplay</th>\n",
              "      <th>listingId</th>\n",
              "      <th>mls</th>\n",
              "      <th>internetEntireListingDisplay</th>\n",
              "      <th>geo</th>\n",
              "      <th>tax</th>\n",
              "      <th>coAgent</th>\n",
              "      <th>sales</th>\n",
              "      <th>ownership</th>\n",
              "      <th>leaseType</th>\n",
              "      <th>virtualTourUrl</th>\n",
              "      <th>remarks</th>\n",
              "      <th>association</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'roof': 'Shingle', 'cooling': 'Attic Fan,Cent...</td>\n",
              "      <td>33184409</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'contact': {'email': None, 'office': '678-352...</td>\n",
              "      <td>None</td>\n",
              "      <td>This information is believed to be accurate, b...</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>{'crossStreet': None, 'state': 'GA', 'country'...</td>\n",
              "      <td>None</td>\n",
              "      <td>2017-12-01T00:00:00Z</td>\n",
              "      <td>{'officeMlsId': 'BHHS28', 'lastName': 'Cook', ...</td>\n",
              "      <td>2020-04-09T05:26:39.353Z</td>\n",
              "      <td>{'middleSchool': 'Hopewell', 'highSchool': 'Ca...</td>\n",
              "      <td>[https://s3-us-west-2.amazonaws.com/cdn.simply...</td>\n",
              "      <td>740000</td>\n",
              "      <td>None</td>\n",
              "      <td>5942056</td>\n",
              "      <td>{'status': 'Pending', 'area': None, 'daysOnMar...</td>\n",
              "      <td>None</td>\n",
              "      <td>{'county': 'Fulton', 'lat': 34.077682, 'lng': ...</td>\n",
              "      <td>{'taxYear': 2017, 'taxAnnualAmount': 450, 'id'...</td>\n",
              "      <td>None</td>\n",
              "      <td>{'closeDate': None, 'office': {'contact': None...</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>Avalon Developer D&amp;G Development , LLC in part...</td>\n",
              "      <td>{'frequency': None, 'fee': 270, 'name': None, ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'roof': 'Wood', 'cooling': 'Ceiling Fan(s),Ce...</td>\n",
              "      <td>36437673</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'contact': {'email': None, 'office': '770-475...</td>\n",
              "      <td>None</td>\n",
              "      <td>This information is believed to be accurate, b...</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>{'crossStreet': None, 'state': 'GA', 'country'...</td>\n",
              "      <td>None</td>\n",
              "      <td>2018-02-21T05:00:00Z</td>\n",
              "      <td>{'officeMlsId': 'BHHS14', 'lastName': 'A RENFR...</td>\n",
              "      <td>2021-05-10T14:39:58.217Z</td>\n",
              "      <td>{'middleSchool': 'Banks County', 'highSchool':...</td>\n",
              "      <td>[https://s3-us-west-2.amazonaws.com/cdn.simply...</td>\n",
              "      <td>6000000</td>\n",
              "      <td>True</td>\n",
              "      <td>5968311</td>\n",
              "      <td>{'status': 'Active', 'area': None, 'daysOnMark...</td>\n",
              "      <td>None</td>\n",
              "      <td>{'county': 'Banks', 'lat': 34.443084, 'lng': -...</td>\n",
              "      <td>{'taxYear': 2019, 'taxAnnualAmount': 51017, 'i...</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>Glass Apple Farm is situated on 150 acres over...</td>\n",
              "      <td>{'frequency': None, 'fee': 0, 'name': None, 'a...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'roof': 'Wood', 'cooling': 'Ceiling Fan(s),Ce...</td>\n",
              "      <td>36464940</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'contact': {'email': None, 'office': '770-475...</td>\n",
              "      <td>None</td>\n",
              "      <td>This information is believed to be accurate, b...</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>{'crossStreet': None, 'state': 'GA', 'country'...</td>\n",
              "      <td>None</td>\n",
              "      <td>2018-02-22T05:00:00Z</td>\n",
              "      <td>{'officeMlsId': 'BHHS14', 'lastName': 'A RENFR...</td>\n",
              "      <td>2021-05-10T14:38:01.563Z</td>\n",
              "      <td>{'middleSchool': 'Banks County', 'highSchool':...</td>\n",
              "      <td>[https://s3-us-west-2.amazonaws.com/cdn.simply...</td>\n",
              "      <td>12750000</td>\n",
              "      <td>True</td>\n",
              "      <td>5968860</td>\n",
              "      <td>{'status': 'Active', 'area': None, 'daysOnMark...</td>\n",
              "      <td>None</td>\n",
              "      <td>{'county': 'Banks', 'lat': 34.443084, 'lng': -...</td>\n",
              "      <td>{'taxYear': 2019, 'taxAnnualAmount': 51017, 'i...</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>Glass Apple Farm is situated on 1,123+acres ov...</td>\n",
              "      <td>{'frequency': None, 'fee': 0, 'name': None, 'a...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'roof': 'Shingle', 'cooling': 'Zoned', 'style...</td>\n",
              "      <td>67818620</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'contact': {'email': None, 'office': '770-844...</td>\n",
              "      <td>None</td>\n",
              "      <td>This information is believed to be accurate, b...</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>{'crossStreet': None, 'state': 'GA', 'country'...</td>\n",
              "      <td>None</td>\n",
              "      <td>2019-05-01T04:00:00Z</td>\n",
              "      <td>{'officeMlsId': 'BHHS17', 'lastName': 'Davis',...</td>\n",
              "      <td>2021-06-01T17:40:50.36Z</td>\n",
              "      <td>{'middleSchool': 'White County', 'highSchool':...</td>\n",
              "      <td>[https://s3-us-west-2.amazonaws.com/cdn.simply...</td>\n",
              "      <td>1990000</td>\n",
              "      <td>True</td>\n",
              "      <td>6545594</td>\n",
              "      <td>{'status': 'Active', 'area': None, 'daysOnMark...</td>\n",
              "      <td>None</td>\n",
              "      <td>{'county': 'White', 'lat': 34.626673, 'lng': -...</td>\n",
              "      <td>{'taxYear': 2018, 'taxAnnualAmount': 5939, 'id...</td>\n",
              "      <td>{'officeMlsId': None, 'lastName': 'Davis', 'co...</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>http://142goldflumeway.relahq.com/?mls</td>\n",
              "      <td>*Photos 3, 4, and 5 are a Visual Rendering for...</td>\n",
              "      <td>{'frequency': None, 'fee': 1000, 'name': None,...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'roof': 'Slate', 'cooling': 'Central Air,Zone...</td>\n",
              "      <td>71140934</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'contact': {'email': None, 'office': '404-537...</td>\n",
              "      <td>None</td>\n",
              "      <td>This information is believed to be accurate, b...</td>\n",
              "      <td>None</td>\n",
              "      <td>NaN</td>\n",
              "      <td>{'crossStreet': None, 'state': 'GA', 'country'...</td>\n",
              "      <td>None</td>\n",
              "      <td>2019-06-15T04:00:00Z</td>\n",
              "      <td>{'officeMlsId': 'BHHS05', 'lastName': 'JOHNSTO...</td>\n",
              "      <td>2021-06-08T15:44:15.74Z</td>\n",
              "      <td>{'middleSchool': 'South Hall', 'highSchool': '...</td>\n",
              "      <td>[https://s3-us-west-2.amazonaws.com/cdn.simply...</td>\n",
              "      <td>15500000</td>\n",
              "      <td>True</td>\n",
              "      <td>6570560</td>\n",
              "      <td>{'status': 'Active', 'area': None, 'daysOnMark...</td>\n",
              "      <td>None</td>\n",
              "      <td>{'county': 'Hall', 'lat': 34.175059, 'lng': -8...</td>\n",
              "      <td>{'taxYear': 2018, 'taxAnnualAmount': 13707, 'i...</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>https://tours.goldlensmedia.com/1722124?idx=1</td>\n",
              "      <td>Rare and pristine 446 acres of rolling hills, ...</td>\n",
              "      <td>{'frequency': None, 'fee': None, 'name': None,...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  privateRemarks  ...                                        association\n",
              "0           None  ...  {'frequency': None, 'fee': 270, 'name': None, ...\n",
              "1           None  ...  {'frequency': None, 'fee': 0, 'name': None, 'a...\n",
              "2           None  ...  {'frequency': None, 'fee': 0, 'name': None, 'a...\n",
              "3           None  ...  {'frequency': None, 'fee': 1000, 'name': None,...\n",
              "4           None  ...  {'frequency': None, 'fee': None, 'name': None,...\n",
              "\n",
              "[5 rows x 33 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IeGjxRLTboP3"
      },
      "source": [
        "def get_listings(): #vendor\n",
        "  url = f\"https://api.simplyrets.com/properties?vendor=fmls&limit=25&lastId=99999999\"\n",
        "  try:\n",
        "    response = requests.get(url,auth = auth)\n",
        "  except:\n",
        "    pass\n",
        "  return response.json()\n",
        "\n",
        "\n",
        "def ingest_all_rets_records_for_vendor(vendorId, lookbackPeriodDays):\n",
        "  #lookback periuod is 1 week = 7 days \n",
        "  resp = get_listings()\n",
        "  for record in resp:\n",
        "    week = datetime.now() - timedelta(days=7)\n",
        "    datetime.strftime(record['modified'])\n",
        "    time.strptime(time_string[, format])\n",
        "    #r = datetime(record['modified']) # convert to datetime \n",
        "    if r >= week:\n",
        "      print(record)\n",
        "  \"\"\" This job will run hourly.  Output is a csv with all recently modified rets records.\n",
        "  \"\"\"\n",
        "  ## orderd based on modified\n",
        "\n",
        "  # mls id 000-\n",
        "  # speficy the lastid method \n",
        "  return\n",
        "\n",
        "\n",
        "def write_csv(record):\n",
        "      with open(\"listing.csv\", newline=\"\") as f:\n",
        "        csv.writer(record)\n",
        "\n",
        "def scheduler():\n",
        "  pass\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Xk8AHG5kHcdq",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 283
        },
        "outputId": "d4a5aa35-40a1-4115-af41-863d34e70b96"
      },
      "source": [
        "def ingest_all_rets_records_for_vendor(vendorId='fmls',lookbackPeriodDays=7)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "error",
          "ename": "TypeError",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-25-2419c5f600da>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mingest_all_rets_records_for_vendor\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
            "\u001b[0;32m<ipython-input-24-9b201454655c>\u001b[0m in \u001b[0;36mingest_all_rets_records_for_vendor\u001b[0;34m()\u001b[0m\n\u001b[1;32m     15\u001b[0m   \u001b[0;32mfor\u001b[0m \u001b[0mrecord\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mresp\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     16\u001b[0m     \u001b[0mtoday\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnow\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 17\u001b[0;31m     \u001b[0mr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrecord\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'modified'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     18\u001b[0m     \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mr\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     19\u001b[0m   \"\"\" This job will run hourly.  Output is a csv with all recently modified rets records.\n",
            "\u001b[0;31mTypeError\u001b[0m: an integer is required (got type str)"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "a45uSv__J446",
        "outputId": "adc69529-6d4b-4fd9-ac59-95b4c64a34b3"
      },
      "source": [
        "week = datetime.now() - timedelta(days=7)\n",
        "week"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "datetime.datetime(2021, 6, 15, 19, 42, 3, 68423)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9nCrPxUQNJX9"
      },
      "source": [
        "today = datetime.now()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BwOSLfXbOCv_",
        "outputId": "d3a2b62a-6e4d-4a0f-b483-71f6f61ef7cf"
      },
      "source": [
        "today"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "datetime.datetime(2021, 6, 22, 19, 35, 11, 802072)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uedQyNYlODoF"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}