examples/datasets.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7067d845-9121-47c1-8bc2-65db8f4d7004",
   "metadata": {},
   "source": [
    "# Senzing + Neo4j: The datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78939f8c-3e2c-49b0-b447-213cccfd5ea6",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-15T19:36:02.310335Z",
     "iopub.status.busy": "2024-03-15T19:36:02.303320Z",
     "iopub.status.idle": "2024-03-15T19:36:02.352513Z",
     "shell.execute_reply": "2024-03-15T19:36:02.348131Z",
     "shell.execute_reply.started": "2024-03-15T19:36:02.310230Z"
    }
   },
   "source": [
    "## Set up the Python environment"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea7cc699-a3f7-4768-966a-a7fd77b825c4",
   "metadata": {},
   "source": [
    "First, we need to import the Python library dependencies which are required for the code we'll be running."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c55880de-2f5f-425d-bdfc-6a9cd19f9777",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:30.991414Z",
     "iopub.status.busy": "2024-04-12T03:52:30.990738Z",
     "iopub.status.idle": "2024-04-12T03:52:33.200937Z",
     "shell.execute_reply": "2024-04-12T03:52:33.198399Z",
     "shell.execute_reply.started": "2024-04-12T03:52:30.991379Z"
    }
   },
   "outputs": [],
   "source": [
    "import json\n",
    "import os\n",
    "import pathlib\n",
    "import sys\n",
    "import typing\n",
    "\n",
    "from graphdatascience import GraphDataScience\n",
    "from icecream import ic\n",
    "import dotenv\n",
    "import pandas as pd\n",
    "import watermark\n",
    "\n",
    "%load_ext watermark"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11331aa0-be80-4de3-bed3-24beb12a57bc",
   "metadata": {},
   "source": [
    "Show a \"watermark\" of which versions are being used for system componenents and library dependencies. This may help in case you need to troubleshoot the dependencies on your system, e.g., if there's some conflict during installation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e6a97a2f-e4c4-4086-9dec-bfbf680fd9b9",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:33.218547Z",
     "iopub.status.busy": "2024-04-12T03:52:33.203944Z",
     "iopub.status.idle": "2024-04-12T03:52:33.293449Z",
     "shell.execute_reply": "2024-04-12T03:52:33.291464Z",
     "shell.execute_reply.started": "2024-04-12T03:52:33.218509Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Last updated: 2024-04-11T20:52:33.221585-07:00\n",
      "\n",
      "Python implementation: CPython\n",
      "Python version       : 3.11.0\n",
      "IPython version      : 8.23.0\n",
      "\n",
      "Compiler    : Clang 13.0.0 (clang-1300.0.29.30)\n",
      "OS          : Darwin\n",
      "Release     : 21.6.0\n",
      "Machine     : x86_64\n",
      "Processor   : i386\n",
      "CPU cores   : 8\n",
      "Architecture: 64bit\n",
      "\n",
      "watermark: 2.4.3\n",
      "json     : 2.0.9\n",
      "pandas   : 2.2.1\n",
      "sys      : 3.11.0 (v3.11.0:deaf509e8f, Oct 24 2022, 14:43:23) [Clang 13.0.0 (clang-1300.0.29.30)]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "%watermark\n",
    "%watermark --iversions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db109f9d-8f09-44c5-9117-5481bf3ade78",
   "metadata": {},
   "source": [
    "## Examine the input datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48eaa952-04e5-4370-a06e-2bed638da850",
   "metadata": {},
   "source": [
    "We will use three datasets which describe businesses (names, addresses, etc.) within the Las Vegas metropolitan area:\n",
    "\n",
    "  - SafeGraph: `Places of Interest` (POI)\n",
    "  - US Dept of Labor: `Wage and Hour Compliance Action Data` (WHISARD)\n",
    "  - US Small Business Admin: `PPP Loans over $150K` (PPP)\n",
    "\n",
    "Snapshots of these datasets are available to download from: \n",
    "<https://senzing.com/get-erkg-tutorial-evaluation-data/>\n",
    "\n",
    "For the sake of brevity, we've shortened their file names to: `poi.json`, `dol.json`, `ppp.json`, respectively\n",
    "\n",
    "We'll keep all of the columns for each dataset even though we're only using a few in this tutorial. Let's define a utility function to show a subset of columns in a Pandas `DataFrame` object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "baa8e1ea-d76d-4346-b4ab-436ad800620e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:33.302519Z",
     "iopub.status.busy": "2024-04-12T03:52:33.299520Z",
     "iopub.status.idle": "2024-04-12T03:52:33.336379Z",
     "shell.execute_reply": "2024-04-12T03:52:33.328489Z",
     "shell.execute_reply.started": "2024-04-12T03:52:33.302474Z"
    }
   },
   "outputs": [],
   "source": [
    "def sample_df (\n",
    "    df: pd.DataFrame,\n",
    "    cols_keep: typing.Set[ typing.Any ],\n",
    "    ) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "Remove all but the specified columns from a copy of the given Pandas dataframe.\n",
    "https://stackoverflow.com/a/51285940/1698443\n",
    "    \"\"\"\n",
    "    diff: typing.Set[ typing.Any ] = set(df.columns) - cols_keep\n",
    "    \n",
    "    return df.drop(\n",
    "        diff,\n",
    "        axis = 1,\n",
    "        inplace = False,\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "50342b7a-b0d3-4b19-be69-e596a0dada70",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-15T18:27:12.719784Z",
     "iopub.status.busy": "2024-03-15T18:27:12.718521Z",
     "iopub.status.idle": "2024-03-15T18:27:12.783427Z",
     "shell.execute_reply": "2024-03-15T18:27:12.781099Z",
     "shell.execute_reply.started": "2024-03-15T18:27:12.719691Z"
    }
   },
   "source": [
    "### Load the SafeGraph Places dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "843500f6-d486-4f84-af2b-5bbdae81f91f",
   "metadata": {},
   "source": [
    "Load the `Places of Interest` (POI) dataset for Las Vegas, from SafeGraph:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "eee34144-9970-4654-a86b-e947ed8b1538",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:33.347963Z",
     "iopub.status.busy": "2024-04-12T03:52:33.338793Z",
     "iopub.status.idle": "2024-04-12T03:52:35.700953Z",
     "shell.execute_reply": "2024-04-12T03:52:35.700200Z",
     "shell.execute_reply.started": "2024-04-12T03:52:33.347842Z"
    }
   },
   "outputs": [],
   "source": [
    "poi_path: pathlib.Path = pathlib.Path(\"../lv_data\") / \"poi.json\"\n",
    "\n",
    "df_poi: pd.DataFrame = pd.DataFrame.from_dict(\n",
    "    [ json.loads(line) for line in poi_path.open(encoding = \"utf-8\") ],\n",
    ")\n",
    "\n",
    "df_poi = df_poi.astype(str)\n",
    "\n",
    "df_poi[\"name\"] = df_poi[\"LOCATION_NAME_ORG\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a4967177-4c95-4044-9fc8-199b3cfb4b78",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:35.702459Z",
     "iopub.status.busy": "2024-04-12T03:52:35.702153Z",
     "iopub.status.idle": "2024-04-12T03:52:35.763678Z",
     "shell.execute_reply": "2024-04-12T03:52:35.763041Z",
     "shell.execute_reply.started": "2024-04-12T03:52:35.702438Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATA_SOURCE</th>\n",
       "      <th>RECORD_ID</th>\n",
       "      <th>RECORD_TYPE</th>\n",
       "      <th>PLACEKEY</th>\n",
       "      <th>REL_ANCHOR_DOMAIN</th>\n",
       "      <th>REL_ANCHOR_KEY</th>\n",
       "      <th>LOCATION_NAME_ORG</th>\n",
       "      <th>BRANDS</th>\n",
       "      <th>TOP_CATEGORY</th>\n",
       "      <th>SUB_CATEGORY</th>\n",
       "      <th>...</th>\n",
       "      <th>PHONE_NUMBER</th>\n",
       "      <th>BUSINESS_ADDR_COUNTRY</th>\n",
       "      <th>BUSINESS_ADDR_FULL</th>\n",
       "      <th>MAILING_VERIFIED_STATUS</th>\n",
       "      <th>REL_POINTER_DOMAIN</th>\n",
       "      <th>REL_POINTER_KEY</th>\n",
       "      <th>REL_POINTER_ROLE</th>\n",
       "      <th>OPENED_ON</th>\n",
       "      <th>IS_INTERSECTION</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SAFEGRAPH</td>\n",
       "      <td>225-222@5yv-j92-tn5</td>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>225-222@5yv-j92-tn5</td>\n",
       "      <td>PLACEKEY</td>\n",
       "      <td>225-222@5yv-j92-tn5</td>\n",
       "      <td>Cantwell Michelle L Atty</td>\n",
       "      <td>[]</td>\n",
       "      <td>Legal Services</td>\n",
       "      <td>Offices of Lawyers</td>\n",
       "      <td>...</td>\n",
       "      <td>+17023627800</td>\n",
       "      <td>US</td>\n",
       "      <td>3320 W Sahara Ave Las Vegas NV 89102-3223</td>\n",
       "      <td>VERIFIED_PREMISE</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>Cantwell Michelle L Atty</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SAFEGRAPH</td>\n",
       "      <td>226-222@5yv-hmm-whq</td>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>226-222@5yv-hmm-whq</td>\n",
       "      <td>PLACEKEY</td>\n",
       "      <td>226-222@5yv-hmm-whq</td>\n",
       "      <td>Pieology Pizzeria</td>\n",
       "      <td>[ { \"safegraph_brand_id\": \"SG_BRAND_f372f9969f...</td>\n",
       "      <td>Restaurants and Other Eating Places</td>\n",
       "      <td>Limited-Service Restaurants</td>\n",
       "      <td>...</td>\n",
       "      <td>+17023314454</td>\n",
       "      <td>US</td>\n",
       "      <td>10965 Lavender Hill Dr Ste 130 Las Vegas NV 89...</td>\n",
       "      <td>VERIFIED_DELIVERY_POINT</td>\n",
       "      <td>PLACEKEY</td>\n",
       "      <td>zzw-223@5yv-hkm-rc5</td>\n",
       "      <td>PARENT</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>Pieology Pizzeria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SAFEGRAPH</td>\n",
       "      <td>22s-222@5yv-jbz-jgk</td>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>22s-222@5yv-jbz-jgk</td>\n",
       "      <td>PLACEKEY</td>\n",
       "      <td>22s-222@5yv-jbz-jgk</td>\n",
       "      <td>Ellen Peneyra</td>\n",
       "      <td>[]</td>\n",
       "      <td>Offices of Other Health Practitioners</td>\n",
       "      <td>Offices of All Other Miscellaneous Health Prac...</td>\n",
       "      <td>...</td>\n",
       "      <td>+17027397716</td>\n",
       "      <td>US</td>\n",
       "      <td>2275 Renaissance Dr Ste D Las Vegas NV 89119-6797</td>\n",
       "      <td>VERIFIED_DELIVERY_POINT</td>\n",
       "      <td>PLACEKEY</td>\n",
       "      <td>22g-222@5yv-jbz-h89</td>\n",
       "      <td>PARENT</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>Ellen Peneyra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SAFEGRAPH</td>\n",
       "      <td>28m-222@5yv-hmp-v4v</td>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>28m-222@5yv-hmp-v4v</td>\n",
       "      <td>PLACEKEY</td>\n",
       "      <td>28m-222@5yv-hmp-v4v</td>\n",
       "      <td>Stanford Jackson</td>\n",
       "      <td>[]</td>\n",
       "      <td>Offices of Other Health Practitioners</td>\n",
       "      <td>Offices of All Other Miscellaneous Health Prac...</td>\n",
       "      <td>...</td>\n",
       "      <td>+17025623569</td>\n",
       "      <td>US</td>\n",
       "      <td>8321 W Sahara Ave Apt 2007 Las Vegas NV 89117-...</td>\n",
       "      <td>VERIFIED_DELIVERY_POINT</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>Stanford Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SAFEGRAPH</td>\n",
       "      <td>228-232@5yv-hts-bp9</td>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>228-232@5yv-hts-bp9</td>\n",
       "      <td>PLACEKEY</td>\n",
       "      <td>228-232@5yv-hts-bp9</td>\n",
       "      <td>MS. Jazlyn James</td>\n",
       "      <td>[]</td>\n",
       "      <td>Offices of Physicians</td>\n",
       "      <td>Offices of Physicians, Mental Health Specialists</td>\n",
       "      <td>...</td>\n",
       "      <td>+17029008666</td>\n",
       "      <td>US</td>\n",
       "      <td>5550 Painted Mirage Rd Ste 320 Las Vegas NV 89...</td>\n",
       "      <td>VERIFIED_DELIVERY_POINT</td>\n",
       "      <td>PLACEKEY</td>\n",
       "      <td>223-23b@5yv-hts-bp9</td>\n",
       "      <td>PARENT</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>MS. Jazlyn James</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  DATA_SOURCE            RECORD_ID   RECORD_TYPE             PLACEKEY  \\\n",
       "0   SAFEGRAPH  225-222@5yv-j92-tn5  ORGANIZATION  225-222@5yv-j92-tn5   \n",
       "1   SAFEGRAPH  226-222@5yv-hmm-whq  ORGANIZATION  226-222@5yv-hmm-whq   \n",
       "2   SAFEGRAPH  22s-222@5yv-jbz-jgk  ORGANIZATION  22s-222@5yv-jbz-jgk   \n",
       "3   SAFEGRAPH  28m-222@5yv-hmp-v4v  ORGANIZATION  28m-222@5yv-hmp-v4v   \n",
       "4   SAFEGRAPH  228-232@5yv-hts-bp9  ORGANIZATION  228-232@5yv-hts-bp9   \n",
       "\n",
       "  REL_ANCHOR_DOMAIN       REL_ANCHOR_KEY         LOCATION_NAME_ORG  \\\n",
       "0          PLACEKEY  225-222@5yv-j92-tn5  Cantwell Michelle L Atty   \n",
       "1          PLACEKEY  226-222@5yv-hmm-whq         Pieology Pizzeria   \n",
       "2          PLACEKEY  22s-222@5yv-jbz-jgk             Ellen Peneyra   \n",
       "3          PLACEKEY  28m-222@5yv-hmp-v4v          Stanford Jackson   \n",
       "4          PLACEKEY  228-232@5yv-hts-bp9          MS. Jazlyn James   \n",
       "\n",
       "                                              BRANDS  \\\n",
       "0                                                 []   \n",
       "1  [ { \"safegraph_brand_id\": \"SG_BRAND_f372f9969f...   \n",
       "2                                                 []   \n",
       "3                                                 []   \n",
       "4                                                 []   \n",
       "\n",
       "                            TOP_CATEGORY  \\\n",
       "0                         Legal Services   \n",
       "1    Restaurants and Other Eating Places   \n",
       "2  Offices of Other Health Practitioners   \n",
       "3  Offices of Other Health Practitioners   \n",
       "4                  Offices of Physicians   \n",
       "\n",
       "                                        SUB_CATEGORY  ...  PHONE_NUMBER  \\\n",
       "0                                 Offices of Lawyers  ...  +17023627800   \n",
       "1                        Limited-Service Restaurants  ...  +17023314454   \n",
       "2  Offices of All Other Miscellaneous Health Prac...  ...  +17027397716   \n",
       "3  Offices of All Other Miscellaneous Health Prac...  ...  +17025623569   \n",
       "4   Offices of Physicians, Mental Health Specialists  ...  +17029008666   \n",
       "\n",
       "  BUSINESS_ADDR_COUNTRY                                 BUSINESS_ADDR_FULL  \\\n",
       "0                    US          3320 W Sahara Ave Las Vegas NV 89102-3223   \n",
       "1                    US  10965 Lavender Hill Dr Ste 130 Las Vegas NV 89...   \n",
       "2                    US  2275 Renaissance Dr Ste D Las Vegas NV 89119-6797   \n",
       "3                    US  8321 W Sahara Ave Apt 2007 Las Vegas NV 89117-...   \n",
       "4                    US  5550 Painted Mirage Rd Ste 320 Las Vegas NV 89...   \n",
       "\n",
       "   MAILING_VERIFIED_STATUS REL_POINTER_DOMAIN      REL_POINTER_KEY  \\\n",
       "0         VERIFIED_PREMISE                nan                  nan   \n",
       "1  VERIFIED_DELIVERY_POINT           PLACEKEY  zzw-223@5yv-hkm-rc5   \n",
       "2  VERIFIED_DELIVERY_POINT           PLACEKEY  22g-222@5yv-jbz-h89   \n",
       "3  VERIFIED_DELIVERY_POINT                nan                  nan   \n",
       "4  VERIFIED_DELIVERY_POINT           PLACEKEY  223-23b@5yv-hts-bp9   \n",
       "\n",
       "  REL_POINTER_ROLE OPENED_ON IS_INTERSECTION                      name  \n",
       "0              nan       nan             nan  Cantwell Michelle L Atty  \n",
       "1           PARENT       nan             nan         Pieology Pizzeria  \n",
       "2           PARENT       nan             nan             Ellen Peneyra  \n",
       "3              nan       nan             nan          Stanford Jackson  \n",
       "4           PARENT       nan             nan          MS. Jazlyn James  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_poi.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c6bdea3-4aa9-4848-9499-cd11b8269f98",
   "metadata": {},
   "source": [
    "Take a look at the column names. The `\"DATA_SOURCE\"`, `\"RECORD_ID\"`, `\"RECORD_TYPE\"` columns are needed by Senzing to identify unique records,  then any related to names or addresses will get used during _entity resolution_."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e5842998-f755-4b39-bf25-98b63e6a255d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:35.777193Z",
     "iopub.status.busy": "2024-04-12T03:52:35.776714Z",
     "iopub.status.idle": "2024-04-12T03:52:35.782304Z",
     "shell.execute_reply": "2024-04-12T03:52:35.781672Z",
     "shell.execute_reply.started": "2024-04-12T03:52:35.777175Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['DATA_SOURCE', 'RECORD_ID', 'RECORD_TYPE', 'PLACEKEY',\n",
       "       'REL_ANCHOR_DOMAIN', 'REL_ANCHOR_KEY', 'LOCATION_NAME_ORG', 'BRANDS',\n",
       "       'TOP_CATEGORY', 'SUB_CATEGORY', 'NAICS_CODE', 'BUSINESS_GEO_LATITUDE',\n",
       "       'BUSINESS_GEO_LONGITUDE', 'CATEGORY_TAGS', 'CLOSED_ON',\n",
       "       'TRACKING_CLOSED_SINCE', 'PHONE_NUMBER', 'BUSINESS_ADDR_COUNTRY',\n",
       "       'BUSINESS_ADDR_FULL', 'MAILING_VERIFIED_STATUS', 'REL_POINTER_DOMAIN',\n",
       "       'REL_POINTER_KEY', 'REL_POINTER_ROLE', 'OPENED_ON', 'IS_INTERSECTION',\n",
       "       'name'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_poi.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "52fd2d42-e978-4ed1-b5b3-1f871ec4c736",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:35.783465Z",
     "iopub.status.busy": "2024-04-12T03:52:35.783216Z",
     "iopub.status.idle": "2024-04-12T03:52:35.832754Z",
     "shell.execute_reply": "2024-04-12T03:52:35.830932Z",
     "shell.execute_reply.started": "2024-04-12T03:52:35.783447Z"
    }
   },
   "outputs": [],
   "source": [
    "df = sample_df(\n",
    "    df_poi,\n",
    "    set([\n",
    "        \"RECORD_ID\",\n",
    "        \"name\",\n",
    "        \"SUB_CATEGORY\",\n",
    "        \"BUSINESS_GEO_LATITUDE\",\n",
    "        \"BUSINESS_GEO_LONGITUDE\",\n",
    "        \"BUSINESS_ADDR_FULL\",\n",
    "    ]),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a5fd479b-ec61-4659-a993-a8fc32a5a97c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:35.834397Z",
     "iopub.status.busy": "2024-04-12T03:52:35.833915Z",
     "iopub.status.idle": "2024-04-12T03:52:35.846337Z",
     "shell.execute_reply": "2024-04-12T03:52:35.845398Z",
     "shell.execute_reply.started": "2024-04-12T03:52:35.834367Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RECORD_ID</th>\n",
       "      <th>SUB_CATEGORY</th>\n",
       "      <th>BUSINESS_GEO_LATITUDE</th>\n",
       "      <th>BUSINESS_GEO_LONGITUDE</th>\n",
       "      <th>BUSINESS_ADDR_FULL</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>225-222@5yv-j92-tn5</td>\n",
       "      <td>Offices of Lawyers</td>\n",
       "      <td>36.145647</td>\n",
       "      <td>-115.186399</td>\n",
       "      <td>3320 W Sahara Ave Las Vegas NV 89102-3223</td>\n",
       "      <td>Cantwell Michelle L Atty</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>226-222@5yv-hmm-whq</td>\n",
       "      <td>Limited-Service Restaurants</td>\n",
       "      <td>36.144906</td>\n",
       "      <td>-115.332644</td>\n",
       "      <td>10965 Lavender Hill Dr Ste 130 Las Vegas NV 89...</td>\n",
       "      <td>Pieology Pizzeria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22s-222@5yv-jbz-jgk</td>\n",
       "      <td>Offices of All Other Miscellaneous Health Prac...</td>\n",
       "      <td>36.10289</td>\n",
       "      <td>-115.121807</td>\n",
       "      <td>2275 Renaissance Dr Ste D Las Vegas NV 89119-6797</td>\n",
       "      <td>Ellen Peneyra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28m-222@5yv-hmp-v4v</td>\n",
       "      <td>Offices of All Other Miscellaneous Health Prac...</td>\n",
       "      <td>36.143386</td>\n",
       "      <td>-115.272694</td>\n",
       "      <td>8321 W Sahara Ave Apt 2007 Las Vegas NV 89117-...</td>\n",
       "      <td>Stanford Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>228-232@5yv-hts-bp9</td>\n",
       "      <td>Offices of Physicians, Mental Health Specialists</td>\n",
       "      <td>36.261833</td>\n",
       "      <td>-115.255012</td>\n",
       "      <td>5550 Painted Mirage Rd Ste 320 Las Vegas NV 89...</td>\n",
       "      <td>MS. Jazlyn James</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             RECORD_ID                                       SUB_CATEGORY  \\\n",
       "0  225-222@5yv-j92-tn5                                 Offices of Lawyers   \n",
       "1  226-222@5yv-hmm-whq                        Limited-Service Restaurants   \n",
       "2  22s-222@5yv-jbz-jgk  Offices of All Other Miscellaneous Health Prac...   \n",
       "3  28m-222@5yv-hmp-v4v  Offices of All Other Miscellaneous Health Prac...   \n",
       "4  228-232@5yv-hts-bp9   Offices of Physicians, Mental Health Specialists   \n",
       "\n",
       "  BUSINESS_GEO_LATITUDE BUSINESS_GEO_LONGITUDE  \\\n",
       "0             36.145647            -115.186399   \n",
       "1             36.144906            -115.332644   \n",
       "2              36.10289            -115.121807   \n",
       "3             36.143386            -115.272694   \n",
       "4             36.261833            -115.255012   \n",
       "\n",
       "                                  BUSINESS_ADDR_FULL                      name  \n",
       "0          3320 W Sahara Ave Las Vegas NV 89102-3223  Cantwell Michelle L Atty  \n",
       "1  10965 Lavender Hill Dr Ste 130 Las Vegas NV 89...         Pieology Pizzeria  \n",
       "2  2275 Renaissance Dr Ste D Las Vegas NV 89119-6797             Ellen Peneyra  \n",
       "3  8321 W Sahara Ave Apt 2007 Las Vegas NV 89117-...          Stanford Jackson  \n",
       "4  5550 Painted Mirage Rd Ste 320 Las Vegas NV 89...          MS. Jazlyn James  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "00474aab-6b33-44ae-b6cf-db390aa2e2ee",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:35.847510Z",
     "iopub.status.busy": "2024-04-12T03:52:35.847331Z",
     "iopub.status.idle": "2024-04-12T03:52:36.237838Z",
     "shell.execute_reply": "2024-04-12T03:52:36.237122Z",
     "shell.execute_reply.started": "2024-04-12T03:52:35.847488Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RECORD_ID</th>\n",
       "      <th>SUB_CATEGORY</th>\n",
       "      <th>BUSINESS_GEO_LATITUDE</th>\n",
       "      <th>BUSINESS_GEO_LONGITUDE</th>\n",
       "      <th>BUSINESS_ADDR_FULL</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>79946</td>\n",
       "      <td>79946</td>\n",
       "      <td>79946</td>\n",
       "      <td>79946</td>\n",
       "      <td>79946</td>\n",
       "      <td>79946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "      <td>12145</td>\n",
       "      <td>11</td>\n",
       "      <td>15</td>\n",
       "      <td>2692</td>\n",
       "      <td>435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>79946</td>\n",
       "      <td>388</td>\n",
       "      <td>57686</td>\n",
       "      <td>60708</td>\n",
       "      <td>31831</td>\n",
       "      <td>67555</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       RECORD_ID SUB_CATEGORY BUSINESS_GEO_LATITUDE BUSINESS_GEO_LONGITUDE  \\\n",
       "count      79946        79946                 79946                  79946   \n",
       "freq           1        12145                    11                     15   \n",
       "unique     79946          388                 57686                  60708   \n",
       "\n",
       "       BUSINESS_ADDR_FULL   name  \n",
       "count               79946  79946  \n",
       "freq                 2692    435  \n",
       "unique              31831  67555  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe(include = \"all\").loc[[ \"count\", \"freq\", \"unique\", ]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b31d2eb4-3c9a-40e4-805f-f2da6b6885b9",
   "metadata": {},
   "source": [
    "### Load the DoL WHISARD dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17419ba0-c66c-489c-bfd4-0fc5f5ea7f13",
   "metadata": {},
   "source": [
    "Load the `Wage and Hour Compliance Action Data` (WHISARD) from the US Department of Labor:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "57501d05-56c6-4b23-92fd-1872332f74a4",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:36.239457Z",
     "iopub.status.busy": "2024-04-12T03:52:36.239232Z",
     "iopub.status.idle": "2024-04-12T03:52:36.418688Z",
     "shell.execute_reply": "2024-04-12T03:52:36.417952Z",
     "shell.execute_reply.started": "2024-04-12T03:52:36.239438Z"
    }
   },
   "outputs": [],
   "source": [
    "dol_path: pathlib.Path = pathlib.Path(\"../lv_data\") / \"dol.json\"\n",
    "\n",
    "df_dol: pd.DataFrame = pd.DataFrame.from_dict(\n",
    "    [ json.loads(line) for line in dol_path.open(encoding = \"utf-8\") ],\n",
    ")\n",
    "\n",
    "df_dol = df_dol.astype(str)\n",
    "df_dol = df_dol.fillna({ \"case_violtn_cnt\": \"0\" })\n",
    "\n",
    "df_dol[\"name\"] = df_dol[\"BUSINESS_NAME_ORG\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f0b38fbf-808d-4bd9-a0d4-4d52a2cfe26c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:36.420284Z",
     "iopub.status.busy": "2024-04-12T03:52:36.419802Z",
     "iopub.status.idle": "2024-04-12T03:52:36.438135Z",
     "shell.execute_reply": "2024-04-12T03:52:36.437477Z",
     "shell.execute_reply.started": "2024-04-12T03:52:36.420263Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RECORD_TYPE</th>\n",
       "      <th>DATA_SOURCE</th>\n",
       "      <th>RECORD_ID</th>\n",
       "      <th>case_id</th>\n",
       "      <th>BUSINESS_NAME_ORG</th>\n",
       "      <th>LEGAL_NAME_ORG</th>\n",
       "      <th>BUSINESS_ADDR_LINE1</th>\n",
       "      <th>BUSINESS_ADDR_CITY</th>\n",
       "      <th>BUSINESS_ADDR_STATE</th>\n",
       "      <th>BUSINESS_ADDR_POSTAL_CODE</th>\n",
       "      <th>...</th>\n",
       "      <th>flsa_smwsl_ee_atp_cnt</th>\n",
       "      <th>eev_violtn_cnt</th>\n",
       "      <th>h2b_violtn_cnt</th>\n",
       "      <th>h2b_bw_atp_amt</th>\n",
       "      <th>h2b_ee_atp_cnt</th>\n",
       "      <th>sraw_violtn_cnt</th>\n",
       "      <th>sraw_bw_atp_amt</th>\n",
       "      <th>sraw_ee_atp_cnt</th>\n",
       "      <th>ld_dt</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>DoL_WHISARD</td>\n",
       "      <td>53</td>\n",
       "      <td>1658108</td>\n",
       "      <td>Fabulous Freddy's (Trailwood)</td>\n",
       "      <td>Fabulous LLC</td>\n",
       "      <td>9611 Trail Wood Drive</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>NV</td>\n",
       "      <td>89134</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-04-01 01:00:03 EDT</td>\n",
       "      <td>Fabulous Freddy's (Trailwood)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>DoL_WHISARD</td>\n",
       "      <td>165</td>\n",
       "      <td>1419689</td>\n",
       "      <td>Boulder Station Hotel &amp; Casino</td>\n",
       "      <td>Boulder Station, Inc.</td>\n",
       "      <td>4111 Boulder Hwy</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>NV</td>\n",
       "      <td>89121</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-02-20 01:00:06 EST</td>\n",
       "      <td>Boulder Station Hotel &amp; Casino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>DoL_WHISARD</td>\n",
       "      <td>178</td>\n",
       "      <td>1424856</td>\n",
       "      <td>MSI Landscaping</td>\n",
       "      <td>MIST Systems International, Inc.</td>\n",
       "      <td>4820 Quality Court, #B</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>NV</td>\n",
       "      <td>89103</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-02-20 01:00:06 EST</td>\n",
       "      <td>MSI Landscaping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>DoL_WHISARD</td>\n",
       "      <td>196</td>\n",
       "      <td>1668849</td>\n",
       "      <td>Pastime Pools</td>\n",
       "      <td>Pastime Pools</td>\n",
       "      <td>731 Memory Ln</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>NV</td>\n",
       "      <td>89110</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-02-20 01:00:06 EST</td>\n",
       "      <td>Pastime Pools</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>DoL_WHISARD</td>\n",
       "      <td>491</td>\n",
       "      <td>1602023</td>\n",
       "      <td>Steven Michaels</td>\n",
       "      <td>Steven Michaels</td>\n",
       "      <td>4108 Autum St</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>NV</td>\n",
       "      <td>89120</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-02-20 01:00:06 EST</td>\n",
       "      <td>Steven Michaels</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 114 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    RECORD_TYPE  DATA_SOURCE RECORD_ID  case_id  \\\n",
       "0  ORGANIZATION  DoL_WHISARD        53  1658108   \n",
       "1  ORGANIZATION  DoL_WHISARD       165  1419689   \n",
       "2  ORGANIZATION  DoL_WHISARD       178  1424856   \n",
       "3  ORGANIZATION  DoL_WHISARD       196  1668849   \n",
       "4  ORGANIZATION  DoL_WHISARD       491  1602023   \n",
       "\n",
       "                BUSINESS_NAME_ORG                    LEGAL_NAME_ORG  \\\n",
       "0   Fabulous Freddy's (Trailwood)                      Fabulous LLC   \n",
       "1  Boulder Station Hotel & Casino             Boulder Station, Inc.   \n",
       "2                 MSI Landscaping  MIST Systems International, Inc.   \n",
       "3                   Pastime Pools                     Pastime Pools   \n",
       "4                 Steven Michaels                   Steven Michaels   \n",
       "\n",
       "      BUSINESS_ADDR_LINE1 BUSINESS_ADDR_CITY BUSINESS_ADDR_STATE  \\\n",
       "0   9611 Trail Wood Drive          Las Vegas                  NV   \n",
       "1        4111 Boulder Hwy          Las Vegas                  NV   \n",
       "2  4820 Quality Court, #B          Las Vegas                  NV   \n",
       "3           731 Memory Ln          Las Vegas                  NV   \n",
       "4           4108 Autum St          Las Vegas                  NV   \n",
       "\n",
       "  BUSINESS_ADDR_POSTAL_CODE  ... flsa_smwsl_ee_atp_cnt eev_violtn_cnt  \\\n",
       "0                     89134  ...                     0              0   \n",
       "1                     89121  ...                     0              0   \n",
       "2                     89103  ...                     0              0   \n",
       "3                     89110  ...                     0              0   \n",
       "4                     89120  ...                     0              0   \n",
       "\n",
       "  h2b_violtn_cnt h2b_bw_atp_amt h2b_ee_atp_cnt sraw_violtn_cnt  \\\n",
       "0              0            0.0              0               0   \n",
       "1              0            0.0              0               0   \n",
       "2              0            0.0              0               0   \n",
       "3              0            0.0              0               0   \n",
       "4              0            0.0              0               0   \n",
       "\n",
       "  sraw_bw_atp_amt sraw_ee_atp_cnt                    ld_dt  \\\n",
       "0             0.0               0  2015-04-01 01:00:03 EDT   \n",
       "1             0.0               0  2015-02-20 01:00:06 EST   \n",
       "2             0.0               0  2015-02-20 01:00:06 EST   \n",
       "3             0.0               0  2015-02-20 01:00:06 EST   \n",
       "4             0.0               0  2015-02-20 01:00:06 EST   \n",
       "\n",
       "                             name  \n",
       "0   Fabulous Freddy's (Trailwood)  \n",
       "1  Boulder Station Hotel & Casino  \n",
       "2                 MSI Landscaping  \n",
       "3                   Pastime Pools  \n",
       "4                 Steven Michaels  \n",
       "\n",
       "[5 rows x 114 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dol.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "eaa7f8e7-1258-4a97-9013-302eec352cde",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:36.439538Z",
     "iopub.status.busy": "2024-04-12T03:52:36.439299Z",
     "iopub.status.idle": "2024-04-12T03:52:36.444657Z",
     "shell.execute_reply": "2024-04-12T03:52:36.443999Z",
     "shell.execute_reply.started": "2024-04-12T03:52:36.439520Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['RECORD_TYPE', 'DATA_SOURCE', 'RECORD_ID', 'case_id',\n",
       "       'BUSINESS_NAME_ORG', 'LEGAL_NAME_ORG', 'BUSINESS_ADDR_LINE1',\n",
       "       'BUSINESS_ADDR_CITY', 'BUSINESS_ADDR_STATE',\n",
       "       'BUSINESS_ADDR_POSTAL_CODE',\n",
       "       ...\n",
       "       'flsa_smwsl_ee_atp_cnt', 'eev_violtn_cnt', 'h2b_violtn_cnt',\n",
       "       'h2b_bw_atp_amt', 'h2b_ee_atp_cnt', 'sraw_violtn_cnt',\n",
       "       'sraw_bw_atp_amt', 'sraw_ee_atp_cnt', 'ld_dt', 'name'],\n",
       "      dtype='object', length=114)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dol.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "888df916-f2b4-4a0f-af38-196a0b46af7f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:36.445595Z",
     "iopub.status.busy": "2024-04-12T03:52:36.445431Z",
     "iopub.status.idle": "2024-04-12T03:52:36.450351Z",
     "shell.execute_reply": "2024-04-12T03:52:36.449547Z",
     "shell.execute_reply.started": "2024-04-12T03:52:36.445581Z"
    }
   },
   "outputs": [],
   "source": [
    "df = sample_df(\n",
    "    df_dol,\n",
    "    set([\n",
    "        \"RECORD_ID\",\n",
    "        \"name\",\n",
    "        \"BUSINESS_ADDR_LINE1\",\n",
    "        \"naics_code_description\",\n",
    "        \"case_violtn_cnt\",\n",
    "    ]),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "0600b1f6-0e12-435d-9c62-97f6b471225a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:36.451450Z",
     "iopub.status.busy": "2024-04-12T03:52:36.451172Z",
     "iopub.status.idle": "2024-04-12T03:52:36.460716Z",
     "shell.execute_reply": "2024-04-12T03:52:36.459232Z",
     "shell.execute_reply.started": "2024-04-12T03:52:36.451420Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RECORD_ID</th>\n",
       "      <th>BUSINESS_ADDR_LINE1</th>\n",
       "      <th>naics_code_description</th>\n",
       "      <th>case_violtn_cnt</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>53</td>\n",
       "      <td>9611 Trail Wood Drive</td>\n",
       "      <td>Car Washes</td>\n",
       "      <td>0</td>\n",
       "      <td>Fabulous Freddy's (Trailwood)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>165</td>\n",
       "      <td>4111 Boulder Hwy</td>\n",
       "      <td>Casino Hotels</td>\n",
       "      <td>1</td>\n",
       "      <td>Boulder Station Hotel &amp; Casino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>178</td>\n",
       "      <td>4820 Quality Court, #B</td>\n",
       "      <td>Landscaping Services</td>\n",
       "      <td>14</td>\n",
       "      <td>MSI Landscaping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>196</td>\n",
       "      <td>731 Memory Ln</td>\n",
       "      <td>All Other Specialty Trade Contractors</td>\n",
       "      <td>0</td>\n",
       "      <td>Pastime Pools</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>491</td>\n",
       "      <td>4108 Autum St</td>\n",
       "      <td>Private Households</td>\n",
       "      <td>2</td>\n",
       "      <td>Steven Michaels</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  RECORD_ID     BUSINESS_ADDR_LINE1                 naics_code_description  \\\n",
       "0        53   9611 Trail Wood Drive                             Car Washes   \n",
       "1       165        4111 Boulder Hwy                          Casino Hotels   \n",
       "2       178  4820 Quality Court, #B                   Landscaping Services   \n",
       "3       196           731 Memory Ln  All Other Specialty Trade Contractors   \n",
       "4       491           4108 Autum St                     Private Households   \n",
       "\n",
       "  case_violtn_cnt                            name  \n",
       "0               0   Fabulous Freddy's (Trailwood)  \n",
       "1               1  Boulder Station Hotel & Casino  \n",
       "2              14                 MSI Landscaping  \n",
       "3               0                   Pastime Pools  \n",
       "4               2                 Steven Michaels  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "e6306158-76ca-46df-afb9-8846f3fb11cd",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:36.463097Z",
     "iopub.status.busy": "2024-04-12T03:52:36.462724Z",
     "iopub.status.idle": "2024-04-12T03:52:36.489004Z",
     "shell.execute_reply": "2024-04-12T03:52:36.488255Z",
     "shell.execute_reply.started": "2024-04-12T03:52:36.463079Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RECORD_ID</th>\n",
       "      <th>BUSINESS_ADDR_LINE1</th>\n",
       "      <th>naics_code_description</th>\n",
       "      <th>case_violtn_cnt</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1554</td>\n",
       "      <td>1554</td>\n",
       "      <td>1554</td>\n",
       "      <td>1554</td>\n",
       "      <td>1554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>143</td>\n",
       "      <td>413</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>1554</td>\n",
       "      <td>1476</td>\n",
       "      <td>388</td>\n",
       "      <td>160</td>\n",
       "      <td>1435</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       RECORD_ID BUSINESS_ADDR_LINE1 naics_code_description case_violtn_cnt  \\\n",
       "count       1554                1554                   1554            1554   \n",
       "freq           1                   4                    143             413   \n",
       "unique      1554                1476                    388             160   \n",
       "\n",
       "        name  \n",
       "count   1554  \n",
       "freq      10  \n",
       "unique  1435  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe(include = \"all\").loc[[ \"count\", \"freq\", \"unique\", ]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf98bf73-d0ae-4620-a8b4-b2d8f168e731",
   "metadata": {},
   "source": [
    "### Load the PPP Loans dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a378837-47e3-491c-9de3-335250b3b0e1",
   "metadata": {},
   "source": [
    "Load the `PPP Loans over $150K` (PPP) from the US Small Business Administration:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "525eb7a0-b977-41fb-9711-f4032107b6d3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:36.490307Z",
     "iopub.status.busy": "2024-04-12T03:52:36.490125Z",
     "iopub.status.idle": "2024-04-12T03:52:36.553075Z",
     "shell.execute_reply": "2024-04-12T03:52:36.551338Z",
     "shell.execute_reply.started": "2024-04-12T03:52:36.490291Z"
    }
   },
   "outputs": [],
   "source": [
    "ppp_path: pathlib.Path = pathlib.Path(\"../lv_data\") / \"ppp.json\"\n",
    "\n",
    "df_ppp: pd.DataFrame = pd.DataFrame.from_dict(\n",
    "    [ json.loads(line) for line in ppp_path.open(encoding = \"utf-8\") ],\n",
    ")\n",
    "\n",
    "df_ppp = df_ppp.astype(str)\n",
    "\n",
    "df_ppp[\"name\"] = df_ppp[\"BUSINESS_NAME_ORG\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "332ec9d1-3602-4753-9260-2e05584a6956",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:36.555153Z",
     "iopub.status.busy": "2024-04-12T03:52:36.554831Z",
     "iopub.status.idle": "2024-04-12T03:52:36.571460Z",
     "shell.execute_reply": "2024-04-12T03:52:36.570170Z",
     "shell.execute_reply.started": "2024-04-12T03:52:36.555132Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RECORD_TYPE</th>\n",
       "      <th>DATA_SOURCE</th>\n",
       "      <th>RECORD_ID</th>\n",
       "      <th>Loan_Range</th>\n",
       "      <th>BUSINESS_NAME_ORG</th>\n",
       "      <th>BUSINESS_ADDR_LINE1</th>\n",
       "      <th>BUSINESS_ADDR_CITY</th>\n",
       "      <th>BUSINESS_ADDR_STATE</th>\n",
       "      <th>BUSINESS_ADDR_POSTAL_CODE</th>\n",
       "      <th>NAICS_Code</th>\n",
       "      <th>Business_Type</th>\n",
       "      <th>OwnedByRaceEthnicity</th>\n",
       "      <th>OwnedBy</th>\n",
       "      <th>OwnedByVeteran</th>\n",
       "      <th>NonProfit</th>\n",
       "      <th>JobsReported</th>\n",
       "      <th>DateApproved</th>\n",
       "      <th>Lender</th>\n",
       "      <th>CD</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>PPP_LOANS</td>\n",
       "      <td>7017</td>\n",
       "      <td>c $1-2 million</td>\n",
       "      <td>INFINITY HOSPICE CARE OF LAS VEGAS, LLC</td>\n",
       "      <td>5110 N 40TH ST STE 107</td>\n",
       "      <td>PHOENIX</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85018</td>\n",
       "      <td>623110</td>\n",
       "      <td>Limited  Liability Company(LLC)</td>\n",
       "      <td>Unanswered</td>\n",
       "      <td>Male Owned</td>\n",
       "      <td>Unanswered</td>\n",
       "      <td></td>\n",
       "      <td>137</td>\n",
       "      <td>05/01/2020</td>\n",
       "      <td>JPMorgan Chase Bank, National Association</td>\n",
       "      <td>AZ-09</td>\n",
       "      <td>INFINITY HOSPICE CARE OF LAS VEGAS, LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>PPP_LOANS</td>\n",
       "      <td>7018</td>\n",
       "      <td>d $350,000-1 million</td>\n",
       "      <td>CLUB TATTOO LAS VEGAS LLC</td>\n",
       "      <td>1839 s. almaschool rd. ste 230</td>\n",
       "      <td>MESA</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85210</td>\n",
       "      <td>812199</td>\n",
       "      <td>Limited  Liability Company(LLC)</td>\n",
       "      <td>Unanswered</td>\n",
       "      <td>Male Owned</td>\n",
       "      <td>Non-Veteran</td>\n",
       "      <td></td>\n",
       "      <td>15</td>\n",
       "      <td>05/01/2020</td>\n",
       "      <td>JPMorgan Chase Bank, National Association</td>\n",
       "      <td>AZ-09</td>\n",
       "      <td>CLUB TATTOO LAS VEGAS LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>PPP_LOANS</td>\n",
       "      <td>7021</td>\n",
       "      <td>d $350,000-1 million</td>\n",
       "      <td>LAS VEGAS LABOR, LLC</td>\n",
       "      <td>10265 W Camelback Rd, Ste 104</td>\n",
       "      <td>PHOENIX</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85037</td>\n",
       "      <td>111421</td>\n",
       "      <td>Limited  Liability Company(LLC)</td>\n",
       "      <td>Unanswered</td>\n",
       "      <td>Unanswered</td>\n",
       "      <td>Unanswered</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>04/07/2020</td>\n",
       "      <td>UMB Bank, National Association</td>\n",
       "      <td>AZ-03</td>\n",
       "      <td>LAS VEGAS LABOR, LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>PPP_LOANS</td>\n",
       "      <td>7022</td>\n",
       "      <td>e $150,000-350,000</td>\n",
       "      <td>FIRST CUP PARTNERS LAS VEGAS II, LLC</td>\n",
       "      <td>106 S Kyrene Rd, Ste 2</td>\n",
       "      <td>CHANDLER</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85226</td>\n",
       "      <td>722511</td>\n",
       "      <td>Limited  Liability Company(LLC)</td>\n",
       "      <td>Unanswered</td>\n",
       "      <td>Male Owned</td>\n",
       "      <td>Non-Veteran</td>\n",
       "      <td></td>\n",
       "      <td>105</td>\n",
       "      <td>04/16/2020</td>\n",
       "      <td>JPMorgan Chase Bank, National Association</td>\n",
       "      <td>AZ-09</td>\n",
       "      <td>FIRST CUP PARTNERS LAS VEGAS II, LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORGANIZATION</td>\n",
       "      <td>PPP_LOANS</td>\n",
       "      <td>7023</td>\n",
       "      <td>e $150,000-350,000</td>\n",
       "      <td>FIRST CUP PARTNERS LAS VEGAS, LLC</td>\n",
       "      <td>2121 W Chandler Blvd Ste 106</td>\n",
       "      <td>Chandler</td>\n",
       "      <td>AZ</td>\n",
       "      <td>85224</td>\n",
       "      <td></td>\n",
       "      <td>Limited  Liability Company(LLC)</td>\n",
       "      <td>Unanswered</td>\n",
       "      <td>Male Owned</td>\n",
       "      <td>Non-Veteran</td>\n",
       "      <td></td>\n",
       "      <td>124</td>\n",
       "      <td>05/01/2020</td>\n",
       "      <td>JPMorgan Chase Bank, National Association</td>\n",
       "      <td>AZ-09</td>\n",
       "      <td>FIRST CUP PARTNERS LAS VEGAS, LLC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    RECORD_TYPE DATA_SOURCE RECORD_ID            Loan_Range  \\\n",
       "0  ORGANIZATION   PPP_LOANS      7017        c $1-2 million   \n",
       "1  ORGANIZATION   PPP_LOANS      7018  d $350,000-1 million   \n",
       "2  ORGANIZATION   PPP_LOANS      7021  d $350,000-1 million   \n",
       "3  ORGANIZATION   PPP_LOANS      7022    e $150,000-350,000   \n",
       "4  ORGANIZATION   PPP_LOANS      7023    e $150,000-350,000   \n",
       "\n",
       "                         BUSINESS_NAME_ORG             BUSINESS_ADDR_LINE1  \\\n",
       "0  INFINITY HOSPICE CARE OF LAS VEGAS, LLC          5110 N 40TH ST STE 107   \n",
       "1                CLUB TATTOO LAS VEGAS LLC  1839 s. almaschool rd. ste 230   \n",
       "2                     LAS VEGAS LABOR, LLC   10265 W Camelback Rd, Ste 104   \n",
       "3     FIRST CUP PARTNERS LAS VEGAS II, LLC          106 S Kyrene Rd, Ste 2   \n",
       "4        FIRST CUP PARTNERS LAS VEGAS, LLC    2121 W Chandler Blvd Ste 106   \n",
       "\n",
       "  BUSINESS_ADDR_CITY BUSINESS_ADDR_STATE BUSINESS_ADDR_POSTAL_CODE NAICS_Code  \\\n",
       "0            PHOENIX                  AZ                     85018     623110   \n",
       "1               MESA                  AZ                     85210     812199   \n",
       "2            PHOENIX                  AZ                     85037     111421   \n",
       "3           CHANDLER                  AZ                     85226     722511   \n",
       "4           Chandler                  AZ                     85224              \n",
       "\n",
       "                     Business_Type OwnedByRaceEthnicity     OwnedBy  \\\n",
       "0  Limited  Liability Company(LLC)           Unanswered  Male Owned   \n",
       "1  Limited  Liability Company(LLC)           Unanswered  Male Owned   \n",
       "2  Limited  Liability Company(LLC)           Unanswered  Unanswered   \n",
       "3  Limited  Liability Company(LLC)           Unanswered  Male Owned   \n",
       "4  Limited  Liability Company(LLC)           Unanswered  Male Owned   \n",
       "\n",
       "  OwnedByVeteran NonProfit JobsReported DateApproved  \\\n",
       "0     Unanswered                    137   05/01/2020   \n",
       "1    Non-Veteran                     15   05/01/2020   \n",
       "2     Unanswered                      0   04/07/2020   \n",
       "3    Non-Veteran                    105   04/16/2020   \n",
       "4    Non-Veteran                    124   05/01/2020   \n",
       "\n",
       "                                      Lender     CD  \\\n",
       "0  JPMorgan Chase Bank, National Association  AZ-09   \n",
       "1  JPMorgan Chase Bank, National Association  AZ-09   \n",
       "2             UMB Bank, National Association  AZ-03   \n",
       "3  JPMorgan Chase Bank, National Association  AZ-09   \n",
       "4  JPMorgan Chase Bank, National Association  AZ-09   \n",
       "\n",
       "                                      name  \n",
       "0  INFINITY HOSPICE CARE OF LAS VEGAS, LLC  \n",
       "1                CLUB TATTOO LAS VEGAS LLC  \n",
       "2                     LAS VEGAS LABOR, LLC  \n",
       "3     FIRST CUP PARTNERS LAS VEGAS II, LLC  \n",
       "4        FIRST CUP PARTNERS LAS VEGAS, LLC  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ppp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "c1c37135-2391-46b8-91d0-84bef2809769",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:36.572742Z",
     "iopub.status.busy": "2024-04-12T03:52:36.572490Z",
     "iopub.status.idle": "2024-04-12T03:52:36.577905Z",
     "shell.execute_reply": "2024-04-12T03:52:36.577022Z",
     "shell.execute_reply.started": "2024-04-12T03:52:36.572721Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['RECORD_TYPE', 'DATA_SOURCE', 'RECORD_ID', 'Loan_Range',\n",
       "       'BUSINESS_NAME_ORG', 'BUSINESS_ADDR_LINE1', 'BUSINESS_ADDR_CITY',\n",
       "       'BUSINESS_ADDR_STATE', 'BUSINESS_ADDR_POSTAL_CODE', 'NAICS_Code',\n",
       "       'Business_Type', 'OwnedByRaceEthnicity', 'OwnedBy', 'OwnedByVeteran',\n",
       "       'NonProfit', 'JobsReported', 'DateApproved', 'Lender', 'CD', 'name'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ppp.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "66a784be-ce89-4f43-9877-81d4d1380a26",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:36.579283Z",
     "iopub.status.busy": "2024-04-12T03:52:36.579053Z",
     "iopub.status.idle": "2024-04-12T03:52:36.585540Z",
     "shell.execute_reply": "2024-04-12T03:52:36.584196Z",
     "shell.execute_reply.started": "2024-04-12T03:52:36.579239Z"
    }
   },
   "outputs": [],
   "source": [
    "df = sample_df(\n",
    "    df_ppp,\n",
    "    set([\n",
    "        \"RECORD_ID\",\n",
    "        \"name\",\n",
    "        \"BUSINESS_ADDR_LINE1\",\n",
    "        \"Business_Type\",\n",
    "        \"JobsReported\",\n",
    "    ]),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "86bb0474-20ea-4036-ba17-9b31fd4fe7be",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:36.590577Z",
     "iopub.status.busy": "2024-04-12T03:52:36.590216Z",
     "iopub.status.idle": "2024-04-12T03:52:36.610213Z",
     "shell.execute_reply": "2024-04-12T03:52:36.609225Z",
     "shell.execute_reply.started": "2024-04-12T03:52:36.590556Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RECORD_ID</th>\n",
       "      <th>BUSINESS_ADDR_LINE1</th>\n",
       "      <th>Business_Type</th>\n",
       "      <th>JobsReported</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7017</td>\n",
       "      <td>5110 N 40TH ST STE 107</td>\n",
       "      <td>Limited  Liability Company(LLC)</td>\n",
       "      <td>137</td>\n",
       "      <td>INFINITY HOSPICE CARE OF LAS VEGAS, LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7018</td>\n",
       "      <td>1839 s. almaschool rd. ste 230</td>\n",
       "      <td>Limited  Liability Company(LLC)</td>\n",
       "      <td>15</td>\n",
       "      <td>CLUB TATTOO LAS VEGAS LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7021</td>\n",
       "      <td>10265 W Camelback Rd, Ste 104</td>\n",
       "      <td>Limited  Liability Company(LLC)</td>\n",
       "      <td>0</td>\n",
       "      <td>LAS VEGAS LABOR, LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7022</td>\n",
       "      <td>106 S Kyrene Rd, Ste 2</td>\n",
       "      <td>Limited  Liability Company(LLC)</td>\n",
       "      <td>105</td>\n",
       "      <td>FIRST CUP PARTNERS LAS VEGAS II, LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7023</td>\n",
       "      <td>2121 W Chandler Blvd Ste 106</td>\n",
       "      <td>Limited  Liability Company(LLC)</td>\n",
       "      <td>124</td>\n",
       "      <td>FIRST CUP PARTNERS LAS VEGAS, LLC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  RECORD_ID             BUSINESS_ADDR_LINE1                    Business_Type  \\\n",
       "0      7017          5110 N 40TH ST STE 107  Limited  Liability Company(LLC)   \n",
       "1      7018  1839 s. almaschool rd. ste 230  Limited  Liability Company(LLC)   \n",
       "2      7021   10265 W Camelback Rd, Ste 104  Limited  Liability Company(LLC)   \n",
       "3      7022          106 S Kyrene Rd, Ste 2  Limited  Liability Company(LLC)   \n",
       "4      7023    2121 W Chandler Blvd Ste 106  Limited  Liability Company(LLC)   \n",
       "\n",
       "  JobsReported                                     name  \n",
       "0          137  INFINITY HOSPICE CARE OF LAS VEGAS, LLC  \n",
       "1           15                CLUB TATTOO LAS VEGAS LLC  \n",
       "2            0                     LAS VEGAS LABOR, LLC  \n",
       "3          105     FIRST CUP PARTNERS LAS VEGAS II, LLC  \n",
       "4          124        FIRST CUP PARTNERS LAS VEGAS, LLC  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "70ffd1ac-5e7e-44e9-b1ea-a1a1d48e22f8",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:36.619995Z",
     "iopub.status.busy": "2024-04-12T03:52:36.618793Z",
     "iopub.status.idle": "2024-04-12T03:52:36.647153Z",
     "shell.execute_reply": "2024-04-12T03:52:36.646520Z",
     "shell.execute_reply.started": "2024-04-12T03:52:36.619960Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>RECORD_ID</th>\n",
       "      <th>BUSINESS_ADDR_LINE1</th>\n",
       "      <th>Business_Type</th>\n",
       "      <th>JobsReported</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3488</td>\n",
       "      <td>3488</td>\n",
       "      <td>3488</td>\n",
       "      <td>3488</td>\n",
       "      <td>3488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>1532</td>\n",
       "      <td>241</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>3488</td>\n",
       "      <td>3293</td>\n",
       "      <td>14</td>\n",
       "      <td>288</td>\n",
       "      <td>3481</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       RECORD_ID BUSINESS_ADDR_LINE1 Business_Type JobsReported  name\n",
       "count       3488                3488          3488         3488  3488\n",
       "freq           1                  20          1532          241     2\n",
       "unique      3488                3293            14          288  3481"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe(include = \"all\").loc[[ \"count\", \"freq\", \"unique\", ]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5704e747-2d67-4167-8f2b-672ab0649f97",
   "metadata": {},
   "source": [
    "## Connect the GDS library to Neo4j Desktop"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f13953d-8e49-41e3-b948-1fcde751a9a7",
   "metadata": {},
   "source": [
    "Set up a GDS connection using our credentials for Neo4j Desktop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "1bc0cacc-5534-4535-ab7b-1790f0c45e78",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:36.648901Z",
     "iopub.status.busy": "2024-04-12T03:52:36.648415Z",
     "iopub.status.idle": "2024-04-12T03:52:37.070642Z",
     "shell.execute_reply": "2024-04-12T03:52:37.070126Z",
     "shell.execute_reply.started": "2024-04-12T03:52:36.648882Z"
    }
   },
   "outputs": [],
   "source": [
    "dotenv.load_dotenv(dotenv.find_dotenv())\n",
    "\n",
    "bolt_uri: str = os.environ.get(\"NEO4J_BOLT\")\n",
    "database: str = os.environ.get(\"NEO4J_DBMS\")\n",
    "username: str = os.environ.get(\"NEO4J_USER\")\n",
    "password: str = os.environ.get(\"NEO4J_PASS\")\n",
    "\n",
    "gds:GraphDataScience = GraphDataScience(\n",
    "    bolt_uri,\n",
    "    auth = ( username, password, ),\n",
    "    database = database,\n",
    "    aura_ds = False,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "081a3490-da99-42bd-a092-5ece0cabd64f",
   "metadata": {},
   "source": [
    "Before we begin adding structure to our graph, we'll create a [_constraint_](https://neo4j.com/docs/cypher-manual/current/constraints/) to ensure the uniqueness of records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "272065de-d911-4f72-bb1a-519011299c7e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:37.071844Z",
     "iopub.status.busy": "2024-04-12T03:52:37.071565Z",
     "iopub.status.idle": "2024-04-12T03:52:37.535499Z",
     "shell.execute_reply": "2024-04-12T03:52:37.532133Z",
     "shell.execute_reply.started": "2024-04-12T03:52:37.071826Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\"\"\"\n",
    "DROP CONSTRAINT `record_node_key` IF EXISTS\n",
    "\"\"\")\n",
    "\n",
    "gds.run_cypher(\"\"\"\n",
    "CREATE CONSTRAINT `record_node_key` IF NOT EXISTS\n",
    "  FOR (rec:Record)\n",
    "  REQUIRE rec.uid IS NODE KEY\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7162c630-b755-4b56-b83d-4d2e27e3049b",
   "metadata": {},
   "source": [
    "If you get a `Failed to write data to connection...` error, ignore it.\n",
    "These are spurious."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4efc5c48-9e03-4700-8435-325744991d76",
   "metadata": {},
   "source": [
    "## Load records into the KG"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af2f9e52-e221-4707-8992-adb1fdc684db",
   "metadata": {},
   "source": [
    "Define utility functions used for loading the graph data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "67482f89-7d4e-43e5-89ce-eb7f1cd08da4",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:37.538868Z",
     "iopub.status.busy": "2024-04-12T03:52:37.538334Z",
     "iopub.status.idle": "2024-04-12T03:52:37.546203Z",
     "shell.execute_reply": "2024-04-12T03:52:37.545358Z",
     "shell.execute_reply.started": "2024-04-12T03:52:37.538841Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_property_keys (\n",
    "    df: pd.DataFrame,\n",
    "    ) -> typing.List[ str ]:\n",
    "    \"\"\"\n",
    "Convert the column names from the given Pandas dataframe into Cypher property names.\n",
    "    \"\"\"\n",
    "    return [\n",
    "        name.lower().replace(\" \", \"_\")\n",
    "        for name in df.columns.values.tolist()\n",
    "    ]\n",
    "\n",
    "\n",
    "def safe_value (\n",
    "    obj: typing.Any,\n",
    "    ) -> typing.Any:\n",
    "    \"\"\"\n",
    "Escape double quotes within string values.\n",
    "    \"\"\"\n",
    "    if pd.isna(obj):\n",
    "        return None\n",
    "\n",
    "    if isinstance(obj, str):\n",
    "        return obj.replace('\"', \"'\")\n",
    "\n",
    "    return obj\n",
    "\n",
    "\n",
    "def convert_row (\n",
    "    row: pd.Series,\n",
    "    keys: typing.List[ str ],\n",
    "    ) -> dict:\n",
    "    \"\"\"\n",
    "Transform one row from a Pandas DataFrame of input data into\n",
    "node data ready to load into our graph.\n",
    "    \"\"\"\n",
    "    safe_vals: list = [ safe_value(v) for v in row.tolist() ]\n",
    "    params: dict = dict(zip(keys, safe_vals))\n",
    "    params[\"uid\"] = params[\"data_source\"].upper() + \".\" + str(params[\"record_id\"])\n",
    "\n",
    "    return params\n",
    "\n",
    "\n",
    "def convert_df (   \n",
    "    df: pd.DataFrame,\n",
    "    ) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "Transform a Pandas DataFrame of input data into a DataFrame\n",
    "of node data ready to load into our graph.\n",
    "    \"\"\"\n",
    "    keys: typing.List[ str ] = get_property_keys(df)\n",
    "\n",
    "    return pd.DataFrame([\n",
    "        convert_row(row, keys)\n",
    "        for _, row in df.iterrows()\n",
    "    ])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52078df7-a024-4a64-876a-0367014ef236",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "The Cypher clause [`UNWIND`](https://neo4j.com/docs/cypher-cheat-sheet/5/neo4j-community/#_unwind) turns a converted `DataFrame` (e.g., the `$rows` parameter) into individual rows.\n",
    "Combined with a sub-query this functions like a batch load, and is much faster than loading rows by iterating over them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "7a6992d6-a119-40c1-ae75-fa43cc869daa",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:37.547713Z",
     "iopub.status.busy": "2024-04-12T03:52:37.547381Z",
     "iopub.status.idle": "2024-04-12T03:52:37.553521Z",
     "shell.execute_reply": "2024-04-12T03:52:37.552688Z",
     "shell.execute_reply.started": "2024-04-12T03:52:37.547662Z"
    }
   },
   "outputs": [],
   "source": [
    "unwind_query: str = \"\"\"\n",
    "UNWIND $rows AS row\n",
    "CALL {\n",
    "  WITH row\n",
    "  MERGE (rec:Record {uid: row.uid})\n",
    "  ON CREATE \n",
    "    SET rec += row\n",
    "} IN TRANSACTIONS OF 10000 ROWS\n",
    "    \"\"\"\n",
    "\n",
    "def load_records (\n",
    "    gds: GraphDataScience,\n",
    "    df: pd.DataFrame,\n",
    "    ) -> None:\n",
    "    \"\"\"\n",
    "Load one Pandas DataFrame of node data into Neo4j\n",
    "    \"\"\"\n",
    "    gds.run_cypher(\n",
    "        unwind_query,\n",
    "        {\"rows\": convert_df(df).to_dict(orient = \"records\")},\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "4dd7cc33-db96-40ec-8ba6-bd8c1f48d0ac",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:52:37.555957Z",
     "iopub.status.busy": "2024-04-12T03:52:37.555335Z",
     "iopub.status.idle": "2024-04-12T03:53:13.088956Z",
     "shell.execute_reply": "2024-04-12T03:53:13.086126Z",
     "shell.execute_reply.started": "2024-04-12T03:52:37.555931Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "load_records(gds, df_poi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "8b947887-ff23-49ce-910b-fb71aa161028",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:53:13.096295Z",
     "iopub.status.busy": "2024-04-12T03:53:13.094169Z",
     "iopub.status.idle": "2024-04-12T03:53:18.208793Z",
     "shell.execute_reply": "2024-04-12T03:53:18.207548Z",
     "shell.execute_reply.started": "2024-04-12T03:53:13.096245Z"
    }
   },
   "outputs": [],
   "source": [
    "load_records(gds, df_dol)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "4b1d4de3-f38b-43e5-804c-095f27c14a45",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:53:18.214886Z",
     "iopub.status.busy": "2024-04-12T03:53:18.214546Z",
     "iopub.status.idle": "2024-04-12T03:53:19.528606Z",
     "shell.execute_reply": "2024-04-12T03:53:19.527320Z",
     "shell.execute_reply.started": "2024-04-12T03:53:18.214867Z"
    }
   },
   "outputs": [],
   "source": [
    "load_records(gds, df_ppp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "abd5ff98-d768-4f5c-b345-7d8279da7237",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-04-12T03:53:19.536762Z",
     "iopub.status.busy": "2024-04-12T03:53:19.535314Z",
     "iopub.status.idle": "2024-04-12T03:53:19.818982Z",
     "shell.execute_reply": "2024-04-12T03:53:19.817695Z",
     "shell.execute_reply.started": "2024-04-12T03:53:19.536729Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COUNT(rec.uid)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>84988</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   COUNT(rec.uid)\n",
       "0           84988"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gds.run_cypher(\n",
    "    \"\"\"\n",
    "MATCH (rec:Record)\n",
    "RETURN COUNT(rec.uid)\n",
    "    \"\"\"\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}