BatchData_SkipTracing_MockData.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "BatchData_SkipTracing_MockData.ipynb",
      "provenance": [],
      "authorship_tag": "ABX9TyPERSOY6Dqkkx6BD50sKQqx",
      "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/BatchData_SkipTracing_MockData.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": [
        "# Batch Data Skip Tracing Mock Data\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",
        "| 1st 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",
        "  - Signup for BatchData\n",
        "  - Create mock api key and live api key\n",
        "\n",
        "## Useful Resources\n",
        "- [Google Colab Cheat Sheet](https://towardsdatascience.com/cheat-sheet-for-google-colab-63853778c093)\n",
        "- [BatchData API docs](https://developer.batchdata.com/docs/batchdata/a45e094c668b1-property-skip-trace)\n",
        "- [Curl Convertor](https://curlconverter.com/#)"
      ]
    },
    {
      "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 # google colab specific\n",
        "import pandas as pd\n",
        "import requests\n",
        "import os\n",
        "import warnings\n",
        "\n",
        "pd.set_option('display.max_columns', None) # show all columns"
      ],
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PkdXQZp8UqcC"
      },
      "source": [
        "## <font color=\"blue\">Functions</font>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5p76TDONUsXe"
      },
      "source": [
        ""
      ],
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9B1YSUk9UsoZ"
      },
      "source": [
        "## <font color=\"blue\">Locals & Constants</font>"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "NG7gxupMcw7B",
        "outputId": "da51398b-8bce-4129-c356-5cfab77fceff"
      },
      "execution_count": 2,
      "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": [
        "# set working directory\n",
        "os.chdir('/content/drive/MyDrive/Colab Data/')\n",
        "dir = os.getcwd()\n",
        "print('Current working directory:', dir)\n",
        "\n",
        "# remove pandas warnings\n",
        "warnings.filterwarnings('ignore')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7-YcqpLdc3Ts",
        "outputId": "1a1c66ea-187c-470b-a780-913fc1826f67"
      },
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Current working directory: /content/drive/MyDrive/Colab Data\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CGCaqKj1UvIo"
      },
      "source": [
        "## <font color=\"blue\">Data</font>"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# read in api keys\n",
        "df_api_keys = pd.read_csv(dir + '/input/api_keys.csv')\n",
        "# extract api key\n",
        "batchdata_mock_api_key = df_api_keys.loc[df_api_keys['API'] == 'batchdata_mock']['KEY'].iloc[0]"
      ],
      "metadata": {
        "id": "8M0pHaEadieo"
      },
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## <font color=\"blue\">Transformations</font>"
      ],
      "metadata": {
        "id": "Q65HVqsFd0Zw"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### <font color=\"green\">1) Single Property - Skip Trace Search</font>"
      ],
      "metadata": {
        "id": "LPBBGUlXeYiD"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "url = 'https://stoplight.io/mocks/batchdata/batchdata/20349728/property/skip-trace'\n",
        "\n",
        "json_data = {\n",
        "  'requests': [\n",
        "    {\n",
        "      'propertyAddress': {\n",
        "        \"city\": \"Franklin Square\",\n",
        "        \"street\": \"1011 Rosegold St\",\n",
        "        \"state\": \"NY\",\n",
        "        \"zip\": \"11010\"\n",
        "      },\n",
        "    },\n",
        "  ],\n",
        "}\n",
        "\n",
        "headers = {\n",
        "  'Content-Type': \"application/json\",\n",
        "  'Authorization': 'Bearer ' + batchdata_mock_api_key\n",
        "}\n",
        "\n",
        "response = requests.post(url, headers=headers, json=json_data)\n",
        "response.text"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 178
        },
        "id": "NMsKPKcqUSMD",
        "outputId": "3c58c201-9316-44c9-e44a-5a684e8a3346"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'{\"status\":{\"code\":200,\"text\":\"OK\"},\"results\":{\"persons\":[{\"_id\":\"tOdljN72SozLw\",\"bankruptcy\":{},\"death\":{},\"dnc\":{},\"emails\":[{\"email\":\"john@example.net\"}],\"mailingAddress\":{},\"name\":{\"first\":\"john\",\"last\":\"dow\"},\"phoneNumbers\":[{\"number\":\"123123123\",\"carrier\":\"test carries\",\"type\":\"Mobile\",\"tested\":true,\"reachable\":true,\"score\":100},{\"number\":\"123123\",\"carrier\":\"test carries\",\"type\":\"Land Line\",\"tested\":false,\"reachable\":false,\"score\":95},{\"number\":\"123123\",\"carrier\":\"test carries.\",\"type\":\"Land Line\",\"tested\":true,\"reachable\":true,\"score\":90},{\"number\":\"123123\",\"carrier\":\"test carries\",\"type\":\"Land Line\",\"tested\":true,\"reachable\":true,\"score\":85}],\"litigator\":false,\"propertyAddress\":{\"houseNumber\":\"1011\",\"street\":\"1011 Rosegold St\",\"city\":\"Franklin Square\",\"county\":\"Nassau\",\"state\":\"NY\",\"zip\":\"11010\",\"zipPlus4\":\"2507\",\"formattedStreet\":\"Rosegold St\",\"streetNoUnit\":\"1011 Rosegold St\",\"hash\":\"b7bd4cea51b26af459febfbf822c99e2\"},\"involuntaryLien\":[],\"property\":{\"address\":{\"houseNumber\":\"1011\",\"street\":\"1011 Rosegold St\",\"city\":\"Franklin Square\",\"county\":\"Nassau\",\"state\":\"NY\",\"zip\":\"11010\",\"zipPlus4\":\"2507\",\"localities\":[\"franklin square\"],\"hash\":\"b7bd4cea51b26af459febfbf822c99e2\",\"latitude\":40.704661,\"longitude\":-73.6776615,\"countyFipsCode\":\"36059\",\"formattedStreet\":\"Rosegold St\",\"streetNoUnit\":\"1011 Rosegold St\",\"geoStatus\":\"Rooftop\"},\"owner\":{\"name\":{\"first\":\"john\",\"last\":\"doe\"},\"mailingAddress\":{\"houseNumber\":\"161\",\"street\":\"161 Sheridan Blvd\",\"city\":\"Mineola\",\"county\":\"Nassau\",\"state\":\"NY\",\"zip\":\"11501\",\"formattedStreet\":\"Sheridan Blvd\",\"streetNoUnit\":\"161 Sheridan Blvd\",\"hash\":\"8659e28750aff33159bdc0dbc484bf6f\"}},\"equity\":171469,\"equityPercent\":30.2,\"absenteeOwner\":true,\"vacant\":false,\"uspsDeliverable\":true},\"meta\":{\"matched\":true,\"error\":false}}],\"meta\":{\"apiVersion\":\"2.10.2\",\"performance\":{\"totalRequestTime\":948,\"startTime\":\"2022-04-20T11:25:00.231Z\",\"endTime\":\"2022-04-20T11:25:01.179Z\"},\"results\":{\"requestCount\":1,\"matchCount\":1,\"noMatchCount\":0,\"errorCount\":0},\"requestId\":\"1gKSoy00kQLgmyQ\"}}}'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "response.json()['results']"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7ed23hNgcEAQ",
        "outputId": "00c71523-da64-4652-fc9e-7c1e692d9d6b"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'meta': {'apiVersion': '2.10.2',\n",
              "  'performance': {'endTime': '2022-04-20T11:25:01.179Z',\n",
              "   'startTime': '2022-04-20T11:25:00.231Z',\n",
              "   'totalRequestTime': 948},\n",
              "  'requestId': '1gKSoy00kQLgmyQ',\n",
              "  'results': {'errorCount': 0,\n",
              "   'matchCount': 1,\n",
              "   'noMatchCount': 0,\n",
              "   'requestCount': 1}},\n",
              " 'persons': [{'_id': 'tOdljN72SozLw',\n",
              "   'bankruptcy': {},\n",
              "   'death': {},\n",
              "   'dnc': {},\n",
              "   'emails': [{'email': 'john@example.net'}],\n",
              "   'involuntaryLien': [],\n",
              "   'litigator': False,\n",
              "   'mailingAddress': {},\n",
              "   'meta': {'error': False, 'matched': True},\n",
              "   'name': {'first': 'john', 'last': 'dow'},\n",
              "   'phoneNumbers': [{'carrier': 'test carries',\n",
              "     'number': '123123123',\n",
              "     'reachable': True,\n",
              "     'score': 100,\n",
              "     'tested': True,\n",
              "     'type': 'Mobile'},\n",
              "    {'carrier': 'test carries',\n",
              "     'number': '123123',\n",
              "     'reachable': False,\n",
              "     'score': 95,\n",
              "     'tested': False,\n",
              "     'type': 'Land Line'},\n",
              "    {'carrier': 'test carries.',\n",
              "     'number': '123123',\n",
              "     'reachable': True,\n",
              "     'score': 90,\n",
              "     'tested': True,\n",
              "     'type': 'Land Line'},\n",
              "    {'carrier': 'test carries',\n",
              "     'number': '123123',\n",
              "     'reachable': True,\n",
              "     'score': 85,\n",
              "     'tested': True,\n",
              "     'type': 'Land Line'}],\n",
              "   'property': {'absenteeOwner': True,\n",
              "    'address': {'city': 'Franklin Square',\n",
              "     'county': 'Nassau',\n",
              "     'countyFipsCode': '36059',\n",
              "     'formattedStreet': 'Rosegold St',\n",
              "     'geoStatus': 'Rooftop',\n",
              "     'hash': 'b7bd4cea51b26af459febfbf822c99e2',\n",
              "     'houseNumber': '1011',\n",
              "     'latitude': 40.704661,\n",
              "     'localities': ['franklin square'],\n",
              "     'longitude': -73.6776615,\n",
              "     'state': 'NY',\n",
              "     'street': '1011 Rosegold St',\n",
              "     'streetNoUnit': '1011 Rosegold St',\n",
              "     'zip': '11010',\n",
              "     'zipPlus4': '2507'},\n",
              "    'equity': 171469,\n",
              "    'equityPercent': 30.2,\n",
              "    'owner': {'mailingAddress': {'city': 'Mineola',\n",
              "      'county': 'Nassau',\n",
              "      'formattedStreet': 'Sheridan Blvd',\n",
              "      'hash': '8659e28750aff33159bdc0dbc484bf6f',\n",
              "      'houseNumber': '161',\n",
              "      'state': 'NY',\n",
              "      'street': '161 Sheridan Blvd',\n",
              "      'streetNoUnit': '161 Sheridan Blvd',\n",
              "      'zip': '11501'},\n",
              "     'name': {'first': 'john', 'last': 'doe'}},\n",
              "    'uspsDeliverable': True,\n",
              "    'vacant': False},\n",
              "   'propertyAddress': {'city': 'Franklin Square',\n",
              "    'county': 'Nassau',\n",
              "    'formattedStreet': 'Rosegold St',\n",
              "    'hash': 'b7bd4cea51b26af459febfbf822c99e2',\n",
              "    'houseNumber': '1011',\n",
              "    'state': 'NY',\n",
              "    'street': '1011 Rosegold St',\n",
              "    'streetNoUnit': '1011 Rosegold St',\n",
              "    'zip': '11010',\n",
              "    'zipPlus4': '2507'}}]}"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "_df = pd.json_normalize(response.json()['results']['persons'])\n",
        "print('No of columns:', len(_df.columns))\n",
        "_df"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 215
        },
        "id": "DAHFqvD-cQB3",
        "outputId": "b39260dc-964c-4bee-cde3-2079c03a3cf0"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "No of columns: 50\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "             _id                           emails  \\\n",
              "0  tOdljN72SozLw  [{'email': 'john@example.net'}]   \n",
              "\n",
              "                                        phoneNumbers  litigator  \\\n",
              "0  [{'number': '123123123', 'carrier': 'test carr...      False   \n",
              "\n",
              "  involuntaryLien name.first name.last propertyAddress.houseNumber  \\\n",
              "0              []       john       dow                        1011   \n",
              "\n",
              "  propertyAddress.street propertyAddress.city propertyAddress.county  \\\n",
              "0       1011 Rosegold St      Franklin Square                 Nassau   \n",
              "\n",
              "  propertyAddress.state propertyAddress.zip propertyAddress.zipPlus4  \\\n",
              "0                    NY               11010                     2507   \n",
              "\n",
              "  propertyAddress.formattedStreet propertyAddress.streetNoUnit  \\\n",
              "0                     Rosegold St             1011 Rosegold St   \n",
              "\n",
              "               propertyAddress.hash property.address.houseNumber  \\\n",
              "0  b7bd4cea51b26af459febfbf822c99e2                         1011   \n",
              "\n",
              "  property.address.street property.address.city property.address.county  \\\n",
              "0        1011 Rosegold St       Franklin Square                  Nassau   \n",
              "\n",
              "  property.address.state property.address.zip property.address.zipPlus4  \\\n",
              "0                     NY                11010                      2507   \n",
              "\n",
              "  property.address.localities             property.address.hash  \\\n",
              "0           [franklin square]  b7bd4cea51b26af459febfbf822c99e2   \n",
              "\n",
              "   property.address.latitude  property.address.longitude  \\\n",
              "0                  40.704661                  -73.677661   \n",
              "\n",
              "  property.address.countyFipsCode property.address.formattedStreet  \\\n",
              "0                           36059                      Rosegold St   \n",
              "\n",
              "  property.address.streetNoUnit property.address.geoStatus  \\\n",
              "0              1011 Rosegold St                    Rooftop   \n",
              "\n",
              "  property.owner.name.first property.owner.name.last  \\\n",
              "0                      john                      doe   \n",
              "\n",
              "  property.owner.mailingAddress.houseNumber  \\\n",
              "0                                       161   \n",
              "\n",
              "  property.owner.mailingAddress.street property.owner.mailingAddress.city  \\\n",
              "0                    161 Sheridan Blvd                            Mineola   \n",
              "\n",
              "  property.owner.mailingAddress.county property.owner.mailingAddress.state  \\\n",
              "0                               Nassau                                  NY   \n",
              "\n",
              "  property.owner.mailingAddress.zip  \\\n",
              "0                             11501   \n",
              "\n",
              "  property.owner.mailingAddress.formattedStreet  \\\n",
              "0                                 Sheridan Blvd   \n",
              "\n",
              "  property.owner.mailingAddress.streetNoUnit  \\\n",
              "0                          161 Sheridan Blvd   \n",
              "\n",
              "  property.owner.mailingAddress.hash  property.equity  property.equityPercent  \\\n",
              "0   8659e28750aff33159bdc0dbc484bf6f           171469                    30.2   \n",
              "\n",
              "   property.absenteeOwner  property.vacant  property.uspsDeliverable  \\\n",
              "0                    True            False                      True   \n",
              "\n",
              "   meta.matched  meta.error  \n",
              "0          True       False  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-babb6edd-5765-4a51-b3b8-29883cb45f44\">\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>emails</th>\n",
              "      <th>phoneNumbers</th>\n",
              "      <th>litigator</th>\n",
              "      <th>involuntaryLien</th>\n",
              "      <th>name.first</th>\n",
              "      <th>name.last</th>\n",
              "      <th>propertyAddress.houseNumber</th>\n",
              "      <th>propertyAddress.street</th>\n",
              "      <th>propertyAddress.city</th>\n",
              "      <th>propertyAddress.county</th>\n",
              "      <th>propertyAddress.state</th>\n",
              "      <th>propertyAddress.zip</th>\n",
              "      <th>propertyAddress.zipPlus4</th>\n",
              "      <th>propertyAddress.formattedStreet</th>\n",
              "      <th>propertyAddress.streetNoUnit</th>\n",
              "      <th>propertyAddress.hash</th>\n",
              "      <th>property.address.houseNumber</th>\n",
              "      <th>property.address.street</th>\n",
              "      <th>property.address.city</th>\n",
              "      <th>property.address.county</th>\n",
              "      <th>property.address.state</th>\n",
              "      <th>property.address.zip</th>\n",
              "      <th>property.address.zipPlus4</th>\n",
              "      <th>property.address.localities</th>\n",
              "      <th>property.address.hash</th>\n",
              "      <th>property.address.latitude</th>\n",
              "      <th>property.address.longitude</th>\n",
              "      <th>property.address.countyFipsCode</th>\n",
              "      <th>property.address.formattedStreet</th>\n",
              "      <th>property.address.streetNoUnit</th>\n",
              "      <th>property.address.geoStatus</th>\n",
              "      <th>property.owner.name.first</th>\n",
              "      <th>property.owner.name.last</th>\n",
              "      <th>property.owner.mailingAddress.houseNumber</th>\n",
              "      <th>property.owner.mailingAddress.street</th>\n",
              "      <th>property.owner.mailingAddress.city</th>\n",
              "      <th>property.owner.mailingAddress.county</th>\n",
              "      <th>property.owner.mailingAddress.state</th>\n",
              "      <th>property.owner.mailingAddress.zip</th>\n",
              "      <th>property.owner.mailingAddress.formattedStreet</th>\n",
              "      <th>property.owner.mailingAddress.streetNoUnit</th>\n",
              "      <th>property.owner.mailingAddress.hash</th>\n",
              "      <th>property.equity</th>\n",
              "      <th>property.equityPercent</th>\n",
              "      <th>property.absenteeOwner</th>\n",
              "      <th>property.vacant</th>\n",
              "      <th>property.uspsDeliverable</th>\n",
              "      <th>meta.matched</th>\n",
              "      <th>meta.error</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>tOdljN72SozLw</td>\n",
              "      <td>[{'email': 'john@example.net'}]</td>\n",
              "      <td>[{'number': '123123123', 'carrier': 'test carr...</td>\n",
              "      <td>False</td>\n",
              "      <td>[]</td>\n",
              "      <td>john</td>\n",
              "      <td>dow</td>\n",
              "      <td>1011</td>\n",
              "      <td>1011 Rosegold St</td>\n",
              "      <td>Franklin Square</td>\n",
              "      <td>Nassau</td>\n",
              "      <td>NY</td>\n",
              "      <td>11010</td>\n",
              "      <td>2507</td>\n",
              "      <td>Rosegold St</td>\n",
              "      <td>1011 Rosegold St</td>\n",
              "      <td>b7bd4cea51b26af459febfbf822c99e2</td>\n",
              "      <td>1011</td>\n",
              "      <td>1011 Rosegold St</td>\n",
              "      <td>Franklin Square</td>\n",
              "      <td>Nassau</td>\n",
              "      <td>NY</td>\n",
              "      <td>11010</td>\n",
              "      <td>2507</td>\n",
              "      <td>[franklin square]</td>\n",
              "      <td>b7bd4cea51b26af459febfbf822c99e2</td>\n",
              "      <td>40.704661</td>\n",
              "      <td>-73.677661</td>\n",
              "      <td>36059</td>\n",
              "      <td>Rosegold St</td>\n",
              "      <td>1011 Rosegold St</td>\n",
              "      <td>Rooftop</td>\n",
              "      <td>john</td>\n",
              "      <td>doe</td>\n",
              "      <td>161</td>\n",
              "      <td>161 Sheridan Blvd</td>\n",
              "      <td>Mineola</td>\n",
              "      <td>Nassau</td>\n",
              "      <td>NY</td>\n",
              "      <td>11501</td>\n",
              "      <td>Sheridan Blvd</td>\n",
              "      <td>161 Sheridan Blvd</td>\n",
              "      <td>8659e28750aff33159bdc0dbc484bf6f</td>\n",
              "      <td>171469</td>\n",
              "      <td>30.2</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-babb6edd-5765-4a51-b3b8-29883cb45f44')\"\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-babb6edd-5765-4a51-b3b8-29883cb45f44 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-babb6edd-5765-4a51-b3b8-29883cb45f44');\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": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_phone_num = pd.DataFrame(_df[\"phoneNumbers\"].iloc[0])\n",
        "df_phone_num"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 175
        },
        "id": "y-nM-n0ck3eQ",
        "outputId": "e6ccedb6-9bc6-4b25-be68-385edc603d11"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "      number        carrier       type  tested  reachable  score\n",
              "0  123123123   test carries     Mobile    True       True    100\n",
              "1     123123   test carries  Land Line   False      False     95\n",
              "2     123123  test carries.  Land Line    True       True     90\n",
              "3     123123   test carries  Land Line    True       True     85"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-edb6c7c6-9ac3-4c21-92fc-4788235aa4e8\">\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>number</th>\n",
              "      <th>carrier</th>\n",
              "      <th>type</th>\n",
              "      <th>tested</th>\n",
              "      <th>reachable</th>\n",
              "      <th>score</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>123123123</td>\n",
              "      <td>test carries</td>\n",
              "      <td>Mobile</td>\n",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>100</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>123123</td>\n",
              "      <td>test carries</td>\n",
              "      <td>Land Line</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>95</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>123123</td>\n",
              "      <td>test carries.</td>\n",
              "      <td>Land Line</td>\n",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>90</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>123123</td>\n",
              "      <td>test carries</td>\n",
              "      <td>Land Line</td>\n",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>85</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-edb6c7c6-9ac3-4c21-92fc-4788235aa4e8')\"\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-edb6c7c6-9ac3-4c21-92fc-4788235aa4e8 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-edb6c7c6-9ac3-4c21-92fc-4788235aa4e8');\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": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# filter on valid phone numbers\n",
        "df_ph_valid = df_phone_num.loc[\n",
        "  (df_phone_num['reachable'] == True) & (df_phone_num['score'] >= 90)]\n",
        "# change phone numbers for testing purposes only\n",
        "df_ph_valid['number'] = [6027828692, 5633574823] # mock dnc phone numbers\n",
        "df_ph_valid['number'] = df_ph_valid['number'].astype(str)\n",
        "df_ph_valid"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 112
        },
        "id": "LlAE8wmwlOtj",
        "outputId": "8fc54f6b-d7f3-4349-d33c-fa054fdee2f9"
      },
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       number        carrier       type  tested  reachable  score\n",
              "0  6027828692   test carries     Mobile    True       True    100\n",
              "2  5633574823  test carries.  Land Line    True       True     90"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-96d36dea-e5c1-4a00-8499-bcccf23259b8\">\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>number</th>\n",
              "      <th>carrier</th>\n",
              "      <th>type</th>\n",
              "      <th>tested</th>\n",
              "      <th>reachable</th>\n",
              "      <th>score</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>6027828692</td>\n",
              "      <td>test carries</td>\n",
              "      <td>Mobile</td>\n",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>100</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>5633574823</td>\n",
              "      <td>test carries.</td>\n",
              "      <td>Land Line</td>\n",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>90</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-96d36dea-e5c1-4a00-8499-bcccf23259b8')\"\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-96d36dea-e5c1-4a00-8499-bcccf23259b8 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-96d36dea-e5c1-4a00-8499-bcccf23259b8');\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": "code",
      "source": [
        "url = \"https://stoplight.io/mocks/batchdata/batchdata/20349728/phone/dnc\"\n",
        "\n",
        "json_data = {\n",
        "  'requests': df_ph_valid['number'].tolist(), # pass all valid numbers\n",
        "}\n",
        "\n",
        "headers = {\n",
        "  'Content-Type': \"application/json\",\n",
        "  'Authorization': 'Bearer ' + batchdata_mock_api_key\n",
        "}\n",
        "\n",
        "dnc_response = requests.post(url, headers=headers, json=json_data)\n",
        "dnc_response.json()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bSRcIg8Km-YN",
        "outputId": "09c707f2-e002-42fd-c09a-4a91b92bc290"
      },
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'results': {'meta': {'apiVersion': '2.0',\n",
              "   'performance': {'endTime': '2021-10-05T21:18:51.009Z',\n",
              "    'startTime': '2021-10-05T21:18:50.576Z',\n",
              "    'totalRequestTime': 433},\n",
              "   'requestId': 'qTmKWCL3mDLIpmX',\n",
              "   'results': {'errorCount': 0,\n",
              "    'matchCount': 3,\n",
              "    'noMatchCount': 0,\n",
              "    'requestCount': 3}},\n",
              "  'phoneNumbers': [{'dnc': False,\n",
              "    'meta': {'error': False, 'matched': True},\n",
              "    'number': '6027828692'},\n",
              "   {'dnc': True,\n",
              "    'meta': {'error': False, 'matched': True},\n",
              "    'number': '5633574823'},\n",
              "   {'dnc': True,\n",
              "    'meta': {'error': False, 'matched': True},\n",
              "    'number': '4808602153'}]},\n",
              " 'status': {'code': 200, 'text': 'OK'}}"
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# transform JSON object to a DataFrame and select relevant columns\n",
        "df_dnc = pd.json_normalize(dnc_response.json()['results']['phoneNumbers'])[['number', 'dnc']]\n",
        "df_dnc"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 143
        },
        "id": "qdHiV7qUnw0U",
        "outputId": "2c68b2b5-3063-4663-d943-ac908d3623af"
      },
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       number    dnc\n",
              "0  6027828692  False\n",
              "1  5633574823   True\n",
              "2  4808602153   True"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-4049ba31-37fa-46d1-b926-a45057e87d1f\">\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>number</th>\n",
              "      <th>dnc</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>6027828692</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>5633574823</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>4808602153</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-4049ba31-37fa-46d1-b926-a45057e87d1f')\"\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-4049ba31-37fa-46d1-b926-a45057e87d1f 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-4049ba31-37fa-46d1-b926-a45057e87d1f');\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": [
        "# convert numbers to string type\n",
        "df_ph_valid['number'] = df_ph_valid['number'].astype(str)\n",
        "df_dnc['number'] = df_dnc['number'].astype(str)\n",
        "# merge valid and dnc tables\n",
        "df_ph_valid_dnc = pd.merge(df_ph_valid, df_dnc, how='left', on=['number'])\n",
        "df_ph_valid_dnc"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 112
        },
        "id": "UB5LR-yAofbd",
        "outputId": "ac2fb78a-8e05-457b-94cf-55b522c8089e"
      },
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       number        carrier       type  tested  reachable  score    dnc\n",
              "0  6027828692   test carries     Mobile    True       True    100  False\n",
              "1  5633574823  test carries.  Land Line    True       True     90   True"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-34062023-72e2-44be-867d-fb2dc2b0607e\">\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>number</th>\n",
              "      <th>carrier</th>\n",
              "      <th>type</th>\n",
              "      <th>tested</th>\n",
              "      <th>reachable</th>\n",
              "      <th>score</th>\n",
              "      <th>dnc</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>6027828692</td>\n",
              "      <td>test carries</td>\n",
              "      <td>Mobile</td>\n",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>100</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>5633574823</td>\n",
              "      <td>test carries.</td>\n",
              "      <td>Land Line</td>\n",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>90</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-34062023-72e2-44be-867d-fb2dc2b0607e')\"\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-34062023-72e2-44be-867d-fb2dc2b0607e 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-34062023-72e2-44be-867d-fb2dc2b0607e');\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": "code",
      "source": [
        "# filter on phone numbers that are NOT on DNC (Do Not Call) list\n",
        "df_ph_valid_dnc_fltr = df_ph_valid_dnc.loc[df_ph_valid_dnc['dnc'] == False]\n",
        "df_ph_valid_dnc_fltr"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 81
        },
        "id": "jPW0IgZBo_th",
        "outputId": "bf7b6efe-1d16-4c69-f8c2-f013a80827fc"
      },
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       number       carrier    type  tested  reachable  score    dnc\n",
              "0  6027828692  test carries  Mobile    True       True    100  False"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-69a2fd7d-ebe7-4e17-9871-bd5715a43973\">\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>number</th>\n",
              "      <th>carrier</th>\n",
              "      <th>type</th>\n",
              "      <th>tested</th>\n",
              "      <th>reachable</th>\n",
              "      <th>score</th>\n",
              "      <th>dnc</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>6027828692</td>\n",
              "      <td>test carries</td>\n",
              "      <td>Mobile</td>\n",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>100</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-69a2fd7d-ebe7-4e17-9871-bd5715a43973')\"\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-69a2fd7d-ebe7-4e17-9871-bd5715a43973 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-69a2fd7d-ebe7-4e17-9871-bd5715a43973');\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": 14
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# create copy of df\n",
        "df = _df.copy()\n",
        "\n",
        "# create phone numbers columns\n",
        "i = 1\n",
        "# itereate through list of valid numbers\n",
        "for num in df_ph_valid_dnc_fltr['number'].tolist():\n",
        "  df['phone' + str(i)] = [num] # create column\n",
        "\n",
        "# create email column\n",
        "df['emails'] = df.apply(lambda x: x['emails'][0], axis=1)\n",
        "df = pd.concat([df, df[\"emails\"].apply(pd.Series)], axis=1)\n",
        "df"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 197
        },
        "id": "G007Wx80sY3z",
        "outputId": "a8832afb-2de4-4e9c-872d-427a4c456a86"
      },
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "             _id                         emails  \\\n",
              "0  tOdljN72SozLw  {'email': 'john@example.net'}   \n",
              "\n",
              "                                        phoneNumbers  litigator  \\\n",
              "0  [{'number': '123123123', 'carrier': 'test carr...      False   \n",
              "\n",
              "  involuntaryLien name.first name.last propertyAddress.houseNumber  \\\n",
              "0              []       john       dow                        1011   \n",
              "\n",
              "  propertyAddress.street propertyAddress.city propertyAddress.county  \\\n",
              "0       1011 Rosegold St      Franklin Square                 Nassau   \n",
              "\n",
              "  propertyAddress.state propertyAddress.zip propertyAddress.zipPlus4  \\\n",
              "0                    NY               11010                     2507   \n",
              "\n",
              "  propertyAddress.formattedStreet propertyAddress.streetNoUnit  \\\n",
              "0                     Rosegold St             1011 Rosegold St   \n",
              "\n",
              "               propertyAddress.hash property.address.houseNumber  \\\n",
              "0  b7bd4cea51b26af459febfbf822c99e2                         1011   \n",
              "\n",
              "  property.address.street property.address.city property.address.county  \\\n",
              "0        1011 Rosegold St       Franklin Square                  Nassau   \n",
              "\n",
              "  property.address.state property.address.zip property.address.zipPlus4  \\\n",
              "0                     NY                11010                      2507   \n",
              "\n",
              "  property.address.localities             property.address.hash  \\\n",
              "0           [franklin square]  b7bd4cea51b26af459febfbf822c99e2   \n",
              "\n",
              "   property.address.latitude  property.address.longitude  \\\n",
              "0                  40.704661                  -73.677661   \n",
              "\n",
              "  property.address.countyFipsCode property.address.formattedStreet  \\\n",
              "0                           36059                      Rosegold St   \n",
              "\n",
              "  property.address.streetNoUnit property.address.geoStatus  \\\n",
              "0              1011 Rosegold St                    Rooftop   \n",
              "\n",
              "  property.owner.name.first property.owner.name.last  \\\n",
              "0                      john                      doe   \n",
              "\n",
              "  property.owner.mailingAddress.houseNumber  \\\n",
              "0                                       161   \n",
              "\n",
              "  property.owner.mailingAddress.street property.owner.mailingAddress.city  \\\n",
              "0                    161 Sheridan Blvd                            Mineola   \n",
              "\n",
              "  property.owner.mailingAddress.county property.owner.mailingAddress.state  \\\n",
              "0                               Nassau                                  NY   \n",
              "\n",
              "  property.owner.mailingAddress.zip  \\\n",
              "0                             11501   \n",
              "\n",
              "  property.owner.mailingAddress.formattedStreet  \\\n",
              "0                                 Sheridan Blvd   \n",
              "\n",
              "  property.owner.mailingAddress.streetNoUnit  \\\n",
              "0                          161 Sheridan Blvd   \n",
              "\n",
              "  property.owner.mailingAddress.hash  property.equity  property.equityPercent  \\\n",
              "0   8659e28750aff33159bdc0dbc484bf6f           171469                    30.2   \n",
              "\n",
              "   property.absenteeOwner  property.vacant  property.uspsDeliverable  \\\n",
              "0                    True            False                      True   \n",
              "\n",
              "   meta.matched  meta.error      phone1             email  \n",
              "0          True       False  6027828692  john@example.net  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-1b72b0bc-50ae-4e15-bacb-a9aa5ca9942e\">\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>emails</th>\n",
              "      <th>phoneNumbers</th>\n",
              "      <th>litigator</th>\n",
              "      <th>involuntaryLien</th>\n",
              "      <th>name.first</th>\n",
              "      <th>name.last</th>\n",
              "      <th>propertyAddress.houseNumber</th>\n",
              "      <th>propertyAddress.street</th>\n",
              "      <th>propertyAddress.city</th>\n",
              "      <th>propertyAddress.county</th>\n",
              "      <th>propertyAddress.state</th>\n",
              "      <th>propertyAddress.zip</th>\n",
              "      <th>propertyAddress.zipPlus4</th>\n",
              "      <th>propertyAddress.formattedStreet</th>\n",
              "      <th>propertyAddress.streetNoUnit</th>\n",
              "      <th>propertyAddress.hash</th>\n",
              "      <th>property.address.houseNumber</th>\n",
              "      <th>property.address.street</th>\n",
              "      <th>property.address.city</th>\n",
              "      <th>property.address.county</th>\n",
              "      <th>property.address.state</th>\n",
              "      <th>property.address.zip</th>\n",
              "      <th>property.address.zipPlus4</th>\n",
              "      <th>property.address.localities</th>\n",
              "      <th>property.address.hash</th>\n",
              "      <th>property.address.latitude</th>\n",
              "      <th>property.address.longitude</th>\n",
              "      <th>property.address.countyFipsCode</th>\n",
              "      <th>property.address.formattedStreet</th>\n",
              "      <th>property.address.streetNoUnit</th>\n",
              "      <th>property.address.geoStatus</th>\n",
              "      <th>property.owner.name.first</th>\n",
              "      <th>property.owner.name.last</th>\n",
              "      <th>property.owner.mailingAddress.houseNumber</th>\n",
              "      <th>property.owner.mailingAddress.street</th>\n",
              "      <th>property.owner.mailingAddress.city</th>\n",
              "      <th>property.owner.mailingAddress.county</th>\n",
              "      <th>property.owner.mailingAddress.state</th>\n",
              "      <th>property.owner.mailingAddress.zip</th>\n",
              "      <th>property.owner.mailingAddress.formattedStreet</th>\n",
              "      <th>property.owner.mailingAddress.streetNoUnit</th>\n",
              "      <th>property.owner.mailingAddress.hash</th>\n",
              "      <th>property.equity</th>\n",
              "      <th>property.equityPercent</th>\n",
              "      <th>property.absenteeOwner</th>\n",
              "      <th>property.vacant</th>\n",
              "      <th>property.uspsDeliverable</th>\n",
              "      <th>meta.matched</th>\n",
              "      <th>meta.error</th>\n",
              "      <th>phone1</th>\n",
              "      <th>email</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>tOdljN72SozLw</td>\n",
              "      <td>{'email': 'john@example.net'}</td>\n",
              "      <td>[{'number': '123123123', 'carrier': 'test carr...</td>\n",
              "      <td>False</td>\n",
              "      <td>[]</td>\n",
              "      <td>john</td>\n",
              "      <td>dow</td>\n",
              "      <td>1011</td>\n",
              "      <td>1011 Rosegold St</td>\n",
              "      <td>Franklin Square</td>\n",
              "      <td>Nassau</td>\n",
              "      <td>NY</td>\n",
              "      <td>11010</td>\n",
              "      <td>2507</td>\n",
              "      <td>Rosegold St</td>\n",
              "      <td>1011 Rosegold St</td>\n",
              "      <td>b7bd4cea51b26af459febfbf822c99e2</td>\n",
              "      <td>1011</td>\n",
              "      <td>1011 Rosegold St</td>\n",
              "      <td>Franklin Square</td>\n",
              "      <td>Nassau</td>\n",
              "      <td>NY</td>\n",
              "      <td>11010</td>\n",
              "      <td>2507</td>\n",
              "      <td>[franklin square]</td>\n",
              "      <td>b7bd4cea51b26af459febfbf822c99e2</td>\n",
              "      <td>40.704661</td>\n",
              "      <td>-73.677661</td>\n",
              "      <td>36059</td>\n",
              "      <td>Rosegold St</td>\n",
              "      <td>1011 Rosegold St</td>\n",
              "      <td>Rooftop</td>\n",
              "      <td>john</td>\n",
              "      <td>doe</td>\n",
              "      <td>161</td>\n",
              "      <td>161 Sheridan Blvd</td>\n",
              "      <td>Mineola</td>\n",
              "      <td>Nassau</td>\n",
              "      <td>NY</td>\n",
              "      <td>11501</td>\n",
              "      <td>Sheridan Blvd</td>\n",
              "      <td>161 Sheridan Blvd</td>\n",
              "      <td>8659e28750aff33159bdc0dbc484bf6f</td>\n",
              "      <td>171469</td>\n",
              "      <td>30.2</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>6027828692</td>\n",
              "      <td>john@example.net</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-1b72b0bc-50ae-4e15-bacb-a9aa5ca9942e')\"\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-1b72b0bc-50ae-4e15-bacb-a9aa5ca9942e 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-1b72b0bc-50ae-4e15-bacb-a9aa5ca9942e');\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": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "output_cols = ['name.first', 'name.last',\n",
        "       'propertyAddress.street', 'propertyAddress.city',\n",
        "       'propertyAddress.county', 'propertyAddress.state',\n",
        "       'propertyAddress.zip', 'property.equity',\n",
        "       'property.equityPercent', 'property.absenteeOwner', 'property.vacant',\n",
        "       'property.uspsDeliverable', 'phone1', 'email']\n",
        "\n",
        "df_output = df[output_cols]\n",
        "df_output"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 145
        },
        "id": "8BzWXtGkhv4v",
        "outputId": "1b8ff4a9-5c5a-46ca-f080-763946b76d49"
      },
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  name.first name.last propertyAddress.street propertyAddress.city  \\\n",
              "0       john       dow       1011 Rosegold St      Franklin Square   \n",
              "\n",
              "  propertyAddress.county propertyAddress.state propertyAddress.zip  \\\n",
              "0                 Nassau                    NY               11010   \n",
              "\n",
              "   property.equity  property.equityPercent  property.absenteeOwner  \\\n",
              "0           171469                    30.2                    True   \n",
              "\n",
              "   property.vacant  property.uspsDeliverable      phone1             email  \n",
              "0            False                      True  6027828692  john@example.net  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-17febf10-0279-43c4-9cb6-a98b99951457\">\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>name.first</th>\n",
              "      <th>name.last</th>\n",
              "      <th>propertyAddress.street</th>\n",
              "      <th>propertyAddress.city</th>\n",
              "      <th>propertyAddress.county</th>\n",
              "      <th>propertyAddress.state</th>\n",
              "      <th>propertyAddress.zip</th>\n",
              "      <th>property.equity</th>\n",
              "      <th>property.equityPercent</th>\n",
              "      <th>property.absenteeOwner</th>\n",
              "      <th>property.vacant</th>\n",
              "      <th>property.uspsDeliverable</th>\n",
              "      <th>phone1</th>\n",
              "      <th>email</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>john</td>\n",
              "      <td>dow</td>\n",
              "      <td>1011 Rosegold St</td>\n",
              "      <td>Franklin Square</td>\n",
              "      <td>Nassau</td>\n",
              "      <td>NY</td>\n",
              "      <td>11010</td>\n",
              "      <td>171469</td>\n",
              "      <td>30.2</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>6027828692</td>\n",
              "      <td>john@example.net</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-17febf10-0279-43c4-9cb6-a98b99951457')\"\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-17febf10-0279-43c4-9cb6-a98b99951457 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-17febf10-0279-43c4-9cb6-a98b99951457');\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": 17
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### <font color=\"green\">2) Multi Property - Skip Trace Search</font>"
      ],
      "metadata": {
        "id": "PMNY-ZuveoAF"
      }
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "IzkIx-ddenbs"
      },
      "execution_count": 17,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## <font color=\"blue\">Output</font>"
      ],
      "metadata": {
        "id": "-_C66GQ9gp9Z"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# download file\n",
        "df_output.to_csv('output.csv', index=False)\n",
        "files.download('output.csv')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 17
        },
        "id": "M1N4h4fevdeX",
        "outputId": "3b39c3f1-615f-46ac-b36f-70d55cd6717a"
      },
      "execution_count": 18,
      "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_bf2ddb1b-925d-4aa4-b381-35d1f0e739cc\", \"output.csv\", 360)"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_WrYHVh9Ukid"
      },
      "source": [
        "# End Notebook"
      ]
    }
  ]
}