Q1 Most Influential Attorneys.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load libraries\n",
    "import pandas as pd\n",
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect(\"./hcap.sqlite\")\n",
    "\n",
    "# Attorneys without case information (non-repated attorney names)\n",
    "attorneys = pd.read_sql(\"SELECT * FROM attorneys\", conn)\n",
    "\n",
    "# Attorneys joined with their ID and case ID\n",
    "attscases = pd.read_sql(\"SELECT * FROM attorneys AS att INNER JOIN attorney_cases AS attc ON att.id = attorney_id\", conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "tags": [
     "outputPrepend"
    ]
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "attorney_id\n6070     5564\n14916    3551\n13517    2413\ndtype: int64"
     },
     "metadata": {},
     "execution_count": 2
    }
   ],
   "source": [
    "\n",
    "## Attorney involved in the most cases (govt. and private)?\n",
    "\n",
    "attcases = attscases.groupby([\"attorney_id\"])\n",
    "topatt = attcases.size().sort_values(ascending=False).head(3)\n",
    "topatt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "Top Attorney ID:  6070\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "        id             names\n6069  6070  william j. scott",
      "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>id</th>\n      <th>names</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>6069</th>\n      <td>6070</td>\n      <td>william j. scott</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 3
    }
   ],
   "source": [
    "# Find the actual top attorney\n",
    "topatt_id = topatt.index.tolist()[0]\n",
    "print(\"Top Attorney ID: \", topatt_id)\n",
    "attorneys[attorneys[\"id\"] == topatt_id]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "\\begin{tabular}{lrl}\n\\toprule\n{} &    id &             names \\\\\n\\midrule\n6069 &  6070 &  william j. scott \\\\\n\\bottomrule\n\\end{tabular}\n\n"
    }
   ],
   "source": [
    "\n",
    "print(attorneys[attorneys[\"id\"] == topatt_id].to_latex())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "                               id  attorney_id  case_id  party  title\nnames            party_type                                          \nwilliam j. scott government  5556         5556     5556   5556   5556\nbernard carey    government  3550         3550     3550   3550   3550\njames j. doherty private     2413         2413     2413   2413   2413\nneil f. hartigan government  2171         2171     2171   2171   2171\nrichard m. daley government  2029         2029     2029   2029   2029",
      "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></th>\n      <th>id</th>\n      <th>attorney_id</th>\n      <th>case_id</th>\n      <th>party</th>\n      <th>title</th>\n    </tr>\n    <tr>\n      <th>names</th>\n      <th>party_type</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>william j. scott</th>\n      <th>government</th>\n      <td>5556</td>\n      <td>5556</td>\n      <td>5556</td>\n      <td>5556</td>\n      <td>5556</td>\n    </tr>\n    <tr>\n      <th>bernard carey</th>\n      <th>government</th>\n      <td>3550</td>\n      <td>3550</td>\n      <td>3550</td>\n      <td>3550</td>\n      <td>3550</td>\n    </tr>\n    <tr>\n      <th>james j. doherty</th>\n      <th>private</th>\n      <td>2413</td>\n      <td>2413</td>\n      <td>2413</td>\n      <td>2413</td>\n      <td>2413</td>\n    </tr>\n    <tr>\n      <th>neil f. hartigan</th>\n      <th>government</th>\n      <td>2171</td>\n      <td>2171</td>\n      <td>2171</td>\n      <td>2171</td>\n      <td>2171</td>\n    </tr>\n    <tr>\n      <th>richard m. daley</th>\n      <th>government</th>\n      <td>2029</td>\n      <td>2029</td>\n      <td>2029</td>\n      <td>2029</td>\n      <td>2029</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "\n",
    "## Attorney involved in the most cases for private sector and for govt?\n",
    "\n",
    "## First group and count\n",
    "attcases_pvtgovt = attscases.groupby([\"names\", \"party_type\"])\n",
    "attcases_pg_cnt = attcases_pvtgovt.count().sort_values(\"case_id\", ascending=False).head(5)\n",
    "attcases_pg_cnt\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "                               id  attorney_id  case_id  party  title\nnames            party_type                                          \nwilliam j. scott government  5556         5556     5556   5556   5556\njames j. doherty private     2413         2413     2413   2413   2413",
      "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></th>\n      <th>id</th>\n      <th>attorney_id</th>\n      <th>case_id</th>\n      <th>party</th>\n      <th>title</th>\n    </tr>\n    <tr>\n      <th>names</th>\n      <th>party_type</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>william j. scott</th>\n      <th>government</th>\n      <td>5556</td>\n      <td>5556</td>\n      <td>5556</td>\n      <td>5556</td>\n      <td>5556</td>\n    </tr>\n    <tr>\n      <th>james j. doherty</th>\n      <th>private</th>\n      <td>2413</td>\n      <td>2413</td>\n      <td>2413</td>\n      <td>2413</td>\n      <td>2413</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "## Then obtain the maximums for each party type:\n",
    "govt_and_pvt_atts = attcases_pg_cnt.loc[attcases_pg_cnt.groupby(level='party_type')['id'].idxmax()]\n",
    "govt_and_pvt_atts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "\\begin{tabular}{llrrrrr}\n\\toprule\n                 &         &    id &  attorney\\_id &  case\\_id &  party &  title \\\\\nnames & party\\_type &       &              &          &        &        \\\\\n\\midrule\nwilliam j. scott & government &  5556 &         5556 &     5556 &   5556 &   5556 \\\\\njames j. doherty & private &  2413 &         2413 &     2413 &   2413 &   2413 \\\\\n\\bottomrule\n\\end{tabular}\n\n"
    }
   ],
   "source": [
    "print(govt_and_pvt_atts.to_latex())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "error",
     "ename": "NameError",
     "evalue": "name 'atts_ids' is not defined",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-8-6413bb66b0fa>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mattorneys\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mattorneys\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"id\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0misin\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0matts_ids\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'atts_ids' is not defined"
     ]
    }
   ],
   "source": [
    "attorneys[attorneys[\"id\"].isin(atts_ids)]"
   ]
  }
 ],
 "metadata": {
  "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.8.2-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python38264bit382pyenv05fc67cdd8c348f9a384c10c4419353e",
   "display_name": "Python 3.8.2 64-bit ('3.8.2': pyenv)"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}