unclaimed_property_search.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "unclaimed-property-search.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wD9NzbrFVc7h",
        "colab_type": "text"
      },
      "source": [
        "# Unclaimed Property Records Lookup for California\n",
        "\n",
        "```\n",
        "Author: Amirhossein Kiani ( kiani [at] cs.stanford.edu )\n",
        "Date Last Updated: Feb 12, 2020\n",
        "Repository: https://github.com/akiani/linkedin-money\n",
        "```\n",
        "\n",
        "\n",
        "Thanks to the [escheat episode](https://www.npr.org/2020/01/24/799345159/episode-967-escheat-show) from Planet Money, I learned that there is $150+ of money awaiting me on the California Unclained Property website! \n",
        "\n",
        "So I quickly started searching my friends. But then I noticed I could search ALL my friends. Given that I am not Facebook anymore, LinkedIn is the only large social network that I currently have. So I wrote this script to get this done. \n",
        "\n",
        "You can do the same by running this Google Colab notebook and following these steps:\n",
        "\n",
        "## Step 1: Download Your Connections\n",
        "\n",
        "To do this:\n",
        "1. Go to [here](https://www.linkedin.com/psettings/member-data)\n",
        "2. Click on `Getting a copy of your data`\n",
        "3. Under the `\"Want something in particular?...` click on `Connections`\n",
        "4. Click `Download...`\n",
        "5. Wait for about 10 minutes and go back to the same page were the download will be available for you to get as a ZIP file.\n",
        "6. Decompress the ZIP file on your laptop (it should result in a file called `Connections.csv`\n",
        "7. In Google colab, click on the Folder icon on the left sidebar and then drag the CSV file there\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2qm9IUE1Vseo",
        "colab_type": "text"
      },
      "source": [
        "## Step 2: Download Escheat Records\n",
        "The most up-to-date unclaimed property records for California are freely available.\n",
        "\n",
        "You can run the below cell to get these in Google Colab (this is around 3GB in size so it'll take about 30 min)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5jrKN3SjVrrp",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "! wget https://dpupd.sco.ca.gov/01_From_0_To_Below_10.zip\n",
        "! wget https://dpupd.sco.ca.gov/02_From_10_To_Below_100.zip\n",
        "! wget https://dpupd.sco.ca.gov/03_From_100_To_Below_500.zip"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pN_BUBasbuq4",
        "colab_type": "text"
      },
      "source": [
        "These records are stored in ZIP files which each contain a large number of CSV files. Run the below cell to deflate the ZIP files so we can search them!"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Rw5-BaxAW5Ch",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "! unzip 01_From_0_To_Below_10.zip\n",
        "! unzip 02_From_10_To_Below_100.zip\n",
        "! unzip 03_From_100_To_Below_500.zip"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "R6uCvH1n0zlJ",
        "colab_type": "text"
      },
      "source": [
        "Use a bit of data wrangling to get find the intersection of your LinkedIn friends and the records:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vDy-Wv6Kb9t4",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import pandas as pd\n",
        "import glob"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9G4HlSmsetBA",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# read LinkedIn connections\n",
        "connections = pd.read_csv(\"Connections.csv\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yDCM_cUUewZ1",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# create full names to be searched for\n",
        "friends = []\n",
        "for i, connection in connections.iterrows():\n",
        "  first_name = connection[\"First Name\"]\n",
        "  last_name = connection[\"Last Name\"]\n",
        "  try:\n",
        "    friends.append(first_name.upper() + \" \" + last_name.upper())\n",
        "  except:\n",
        "    # ignore NaN records\n",
        "    pass\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "92K-xegcew3-",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# go through each file in the records and print out potential matches\n",
        "\n",
        "record_files = glob.glob(\"/content/*From*/*.csv\")\n",
        "\n",
        "results = pd.DataFrame()\n",
        "\n",
        "for i, csv_file in enumerate(record_files):\n",
        "  print(\"\\nanalyzing file %i of %i\\n\" % (i+1, len(record_files)))\n",
        "  try:\n",
        "    claims = pd.read_csv(csv_file, encoding=\"cp1252\", low_memory=False)\n",
        "  except:\n",
        "    # deal with different encodings in the records...\n",
        "    claims = pd.read_csv(csv_file, encoding=\"cp850\", low_memory=False)\n",
        "  properties_found = claims[claims[\"OWNER_NAME\"].isin(friends)]\n",
        "  results = pd.concat([results, properties_found])\n",
        "  if len(properties_found) == 0:\n",
        "    print(\"no match found.\")\n",
        "  else:\n",
        "    print(\"matches found:\")\n",
        "    for _, row in properties_found.iterrows():\n",
        "      try:\n",
        "        print(\"\\t %s, $%.2f, %s, %s\" %(\n",
        "            row[\"OWNER_NAME\"].title(),\n",
        "            row[\"CASH_REPORTED\"], \n",
        "            row[\"OWNER_CITY\"].title(), \n",
        "            row[\"HOLDER_NAME\"].title()))\n",
        "      except:\n",
        "        print(\"Error in rendering row\", row)\n",
        "        pass"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IW0neJRw1Pr_",
        "colab_type": "text"
      },
      "source": [
        "# Analyzing the results"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WgOyIkcefGoc",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# print the total recovered cash $$$\n",
        "results[\"CASH_REPORTED\"].sum()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RKAxo5ARp-aG",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# only get the records with <5 matches to ignore super common names\n",
        "less_than_5_match = pd.DataFrame()\n",
        "\n",
        "for friend in friends:\n",
        "  matches = results[results[\"OWNER_NAME\"] == friend]\n",
        "  if len(matches) > 0 and len(matches) < 5:\n",
        "    less_than_5_match = pd.concat([less_than_5_match, matches])"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "i0bCM5ClqXVH",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "less_than_5_match[\"CASH_REPORTED\"].sum()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iRce9shhrAr6",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# print only the amounts records in <5 matches that are >$50\n",
        "threshold = 50\n",
        "\n",
        "total = 0\n",
        "\n",
        "for i, row in less_than_5_match.iterrows():\n",
        "  if row[\"CASH_REPORTED\"] > threshold:\n",
        "    total += row[\"CASH_REPORTED\"]\n",
        "    try:\n",
        "      print(\"%s, $%.2f, %s, %s\" %(\n",
        "        row[\"OWNER_NAME\"].title(),\n",
        "        row[\"CASH_REPORTED\"], \n",
        "        row[\"OWNER_CITY\"].title(), \n",
        "        row[\"HOLDER_NAME\"].title()))\n",
        "    except:\n",
        "      print(\"%s, $%.2f\" %(\n",
        "        row[\"OWNER_NAME\"].title(),\n",
        "          row[\"CASH_REPORTED\"]))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TU6WysKlwCHw",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "total"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "My3LEwTq1j3y",
        "colab_type": "text"
      },
      "source": [
        "Generating the visualizatons in the blog post:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "k65x4-xFrcGz",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# aggregate on holders over the whole results set\n",
        "holders = pd.DataFrame()\n",
        "\n",
        "for holder in results[\"HOLDER_NAME\"].unique():\n",
        "  results_for_holder = results[results[\"HOLDER_NAME\"]== holder]\n",
        "  holders = holders.append({\"Holder\": holder, \"Sum\": sum(\n",
        "      results_for_holder[\"CASH_REPORTED\"]), \"Records\": len(\n",
        "          results_for_holder)}, ignore_index=True )"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "y_TWskKtymaV",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# aggregate on the owners over the whole results set\n",
        "owners = pd.DataFrame()\n",
        "\n",
        "for owner in results[\"OWNER_NAME\"].unique():\n",
        "  results_for_owner = results[results[\"OWNER_NAME\"]== owner]\n",
        "  owners = owners.append({\"Owner\": owner, \"Sum\": sum(\n",
        "      results_for_owner[\"CASH_REPORTED\"]), \"Records\": len(\n",
        "          results_for_owner)}, ignore_index=True )"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tcZzp0LmucFN",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import plotly.express as px"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "G2d1Z9gaurnC",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "fig = px.bar(holders.sort_values(\n",
        "    \"Sum\", ascending=False).head(20), x=\"Holder\", y=\"Sum\", template=\"ggplot2\")\n",
        "fig.update_layout(xaxis_tickangle=-45)\n",
        "fig.update_layout(\n",
        "    title=\"Top 20 Holder Companies for Lost Properties\",\n",
        "    xaxis_title=\"Sum in Dollars\",\n",
        "    yaxis_title=\"Holder\",\n",
        ")\n",
        "\n",
        "fig.show()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GX-oaHaTu2ve",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "fig = px.histogram(owners, x=\"Sum\", template=\"ggplot2\")\n",
        "fig.update_layout(xaxis_tickangle=-45)\n",
        "fig.update_layout(\n",
        "    title=\"Distribution Recovered Cash for LinkedIn Friends\",\n",
        "    xaxis_title=\"Dollar Amount\",\n",
        "    yaxis_title=\"Holder\",\n",
        ")\n",
        "\n",
        "fig.show()"
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}