Dataframes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Building Data Frames\n",
    "\n",
    "**Contents**\n",
    "1. Cases\n",
    "2. People\n",
    "3. Acts/Bills\n",
    "4. Speeches\n",
    "5. Keywords? (maybe add to cases)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import regex as re\n",
    "import numpy as np\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv(r\"C:\\Users\\Avena Cheng\\Desktop\\Liberating Archives\\data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {
    "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>Unnamed: 0</th>\n",
       "      <th>Title</th>\n",
       "      <th>Case_ID</th>\n",
       "      <th>Case_No</th>\n",
       "      <th>Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Justices</th>\n",
       "      <th>Appearances</th>\n",
       "      <th>People</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>14 Penn Plaza LLC v. Pyett</td>\n",
       "      <td>1</td>\n",
       "      <td>No. 07-581</td>\n",
       "      <td>December 1, 2008</td>\n",
       "      <td>2008</td>\n",
       "      <td>['JUSTICE ALITO', 'JUSTICE BREYER', 'JUSTICE G...</td>\n",
       "      <td>PAUL SALVATORE, ESQ., New York, N.Y.; on behal...</td>\n",
       "      <td>PAUL SALVATORE, DAVID C. FREDERICK, CURTIS E. ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Abbott v. Perez</td>\n",
       "      <td>2</td>\n",
       "      <td>No. 17-586</td>\n",
       "      <td>April 24, 2018</td>\n",
       "      <td>2018</td>\n",
       "      <td>['JUSTICE ALITO', 'JUSTICE BREYER', 'JUSTICE G...</td>\n",
       "      <td>SCOTT A. KELLER, Solicitor General of Texas, ...</td>\n",
       "      <td>SCOTT A. KELLER, EDWIN S. KNEEDLER, MAX RENEA ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Abbott v. United States</td>\n",
       "      <td>3</td>\n",
       "      <td>No. 09-479</td>\n",
       "      <td>October 4, 2010</td>\n",
       "      <td>2010</td>\n",
       "      <td>['JUSTICE ALITO', 'JUSTICE BREYER', 'JUSTICE G...</td>\n",
       "      <td>DAVID L. HORAN, ESQ., Dallas, Texas; on behalf...</td>\n",
       "      <td>DAVID L. HORAN, JAMES E. RYAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Abdul-Kabir v. Quarterman</td>\n",
       "      <td>4</td>\n",
       "      <td>No. 05-11284</td>\n",
       "      <td>January 17, 2006</td>\n",
       "      <td>2006</td>\n",
       "      <td>['JUSTICE ALITO', 'JUSTICE BREYER', 'JUSTICE G...</td>\n",
       "      <td>ROBERT C. OWEN, ESQ., Austin, Tex.; on behalf...</td>\n",
       "      <td>ROBERT C. OWEN, EDWARD L. MARSHALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Abdur_Rahman v. Bell</td>\n",
       "      <td>5</td>\n",
       "      <td>No. 01-9094</td>\n",
       "      <td>November 6, 2002</td>\n",
       "      <td>2002</td>\n",
       "      <td>['JUSTICE REHNQUIST']</td>\n",
       "      <td>JAMES S. LIEBMAN, ESQ., New York, New York; on...</td>\n",
       "      <td>JAMES S. LIEBMAN, PAUL G. SUMMERS, PAUL J. ZID...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0                       Title  Case_ID       Case_No  \\\n",
       "0           0  14 Penn Plaza LLC v. Pyett        1    No. 07-581   \n",
       "1           1             Abbott v. Perez        2    No. 17-586   \n",
       "2           2     Abbott v. United States        3    No. 09-479   \n",
       "3           3   Abdul-Kabir v. Quarterman        4  No. 05-11284   \n",
       "4           4        Abdur_Rahman v. Bell        5   No. 01-9094   \n",
       "\n",
       "                Date  Year                                           Justices  \\\n",
       "0   December 1, 2008  2008  ['JUSTICE ALITO', 'JUSTICE BREYER', 'JUSTICE G...   \n",
       "1     April 24, 2018  2018  ['JUSTICE ALITO', 'JUSTICE BREYER', 'JUSTICE G...   \n",
       "2    October 4, 2010  2010  ['JUSTICE ALITO', 'JUSTICE BREYER', 'JUSTICE G...   \n",
       "3   January 17, 2006  2006  ['JUSTICE ALITO', 'JUSTICE BREYER', 'JUSTICE G...   \n",
       "4   November 6, 2002  2002                              ['JUSTICE REHNQUIST']   \n",
       "\n",
       "                                         Appearances  \\\n",
       "0  PAUL SALVATORE, ESQ., New York, N.Y.; on behal...   \n",
       "1   SCOTT A. KELLER, Solicitor General of Texas, ...   \n",
       "2  DAVID L. HORAN, ESQ., Dallas, Texas; on behalf...   \n",
       "3   ROBERT C. OWEN, ESQ., Austin, Tex.; on behalf...   \n",
       "4  JAMES S. LIEBMAN, ESQ., New York, New York; on...   \n",
       "\n",
       "                                              People  \n",
       "0  PAUL SALVATORE, DAVID C. FREDERICK, CURTIS E. ...  \n",
       "1  SCOTT A. KELLER, EDWIN S. KNEEDLER, MAX RENEA ...  \n",
       "2                      DAVID L. HORAN, JAMES E. RYAN  \n",
       "3                 ROBERT C. OWEN, EDWARD L. MARSHALL  \n",
       "4  JAMES S. LIEBMAN, PAUL G. SUMMERS, PAUL J. ZID...  "
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cases DB\n",
    "\n",
    "**Columns:**\n",
    "* Case ID\n",
    "* Title\n",
    "* Case No ...notice there's some issues with this you should probably fix. \n",
    "* Date\n",
    "* Year\n",
    "* Month\n",
    "* Day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data.drop('Unnamed: 0',axis=1)\n",
    "data['Date']=pd.to_datetime(data[['Date']].stack()).unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "month = []\n",
    "day = []\n",
    "for i in range(len(data)):\n",
    "    month += [data['Date'][i].month]\n",
    "    day += [data['Date'][i].day]\n",
    "data['Month'] = month\n",
    "data['Day'] = day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "case_df = data[['Title','Case_ID','Case_No','Date','Year','Month','Day']]\n",
    "new_cols = [i.lower() for i in case_df.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [],
   "source": [
    "case_df.columns = new_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "case_df['date'] = case_df['date'].dt.normalize()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1302"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(case_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [],
   "source": [
    "keywords = pd.read_csv(r'C:\\Users\\Avena Cheng\\Desktop\\Liberating Archives\\supreme-court-transcripts\\keywords.csv').drop('Unnamed: 0',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [],
   "source": [
    "case_df = pd.merge(case_df,keywords)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [],
   "source": [
    "case_df.to_csv('cases.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {
    "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>title</th>\n",
       "      <th>case_id</th>\n",
       "      <th>case_no</th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14 Penn Plaza LLC v. Pyett</td>\n",
       "      <td>1</td>\n",
       "      <td>No. 07-581</td>\n",
       "      <td>2008-12-01</td>\n",
       "      <td>2008</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Abbott v. Perez</td>\n",
       "      <td>2</td>\n",
       "      <td>No. 17-586</td>\n",
       "      <td>2018-04-24</td>\n",
       "      <td>2018</td>\n",
       "      <td>4</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Abbott v. United States</td>\n",
       "      <td>3</td>\n",
       "      <td>No. 09-479</td>\n",
       "      <td>2010-10-04</td>\n",
       "      <td>2010</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Abdul-Kabir v. Quarterman</td>\n",
       "      <td>4</td>\n",
       "      <td>No. 05-11284</td>\n",
       "      <td>2006-01-17</td>\n",
       "      <td>2006</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Abdur_Rahman v. Bell</td>\n",
       "      <td>5</td>\n",
       "      <td>No. 01-9094</td>\n",
       "      <td>2002-11-06</td>\n",
       "      <td>2002</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        title  case_id       case_no       date  year  month  \\\n",
       "0  14 Penn Plaza LLC v. Pyett        1    No. 07-581 2008-12-01  2008     12   \n",
       "1             Abbott v. Perez        2    No. 17-586 2018-04-24  2018      4   \n",
       "2     Abbott v. United States        3    No. 09-479 2010-10-04  2010     10   \n",
       "3   Abdul-Kabir v. Quarterman        4  No. 05-11284 2006-01-17  2006      1   \n",
       "4        Abdur_Rahman v. Bell        5   No. 01-9094 2002-11-06  2002     11   \n",
       "\n",
       "   day  \n",
       "0    1  \n",
       "1   24  \n",
       "2    4  \n",
       "3   17  \n",
       "4    6  "
      ]
     },
     "execution_count": 225,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "case_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [],
   "source": [
    "#case_df['date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "con = sqlite3.connect(\"database.db\")\n",
    "\n",
    "# Write the new DataFrame to a new SQLite table\n",
    "case_df.to_sql(\"cases\", con, if_exists=\"replace\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "## not really useful\n",
    "#roles = []\n",
    "#for i in range(len(data)):\n",
    "#    role = re.findall('[A-Z\\s\\.\\,]+[A-Z][a-z]+\\.',data[['Appearances']].iloc[i].values[0])\n",
    "#    roles += role"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1302"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(data['Title']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## People DB\n",
    "\n",
    "**Columns:**\n",
    "* Person_ID\n",
    "* Name\n",
    "* Possibly role"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'PAUL SALVATORE, ESQ., New York, N.Y.; on behalf of the Petitioners. DAVID C. FREDERICK, ESQ., Washington, D.C.; on behalf of the Respondents. CURTIS E. GANNON, ESQ., Assistant to the Solicitor General, Department of Justice, Washington, D.C.; on behalf of the United States, as amicus curiae, supporting the Respondents. '"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[['Appearances']].iloc[0].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[' ROBERT DIGGES, JR., ESQ., Alexandria, Virginia; on behalf of the Petitioners . th Street, NW Suite ']],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['Title'] == 'American Trucking Assns., Inc. v. Michigan Pub. Serv. Comm_n'][[\"Appearances\"]].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "# check for errors\n",
    "for i in range(1302):\n",
    "    lst = data['People'].iloc[i]\n",
    "    if \"P R O C E E D I N G S\" in lst:\n",
    "        print(data.iloc[i]['Title'])\n",
    "    if \"ARGUMENT\" in lst:\n",
    "        print(data.iloc[i]['Title'])\n",
    "    if \"JUSTCE\" in lst:\n",
    "        print(data.iloc[i]['Title'])\n",
    "    if lst == \"NW S\":\n",
    "        print(data.iloc[i]['Title'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'PAUL SALVATORE, DAVID C. FREDERICK, CURTIS E. GANNON'"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['People'].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(1302):\n",
    "    lst = data['People'].iloc[i]\n",
    "    for j in lst:\n",
    "        if j == \"ANN O\":\n",
    "            print(lst)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [],
   "source": [
    "everyone = []\n",
    "for i in range(1302):\n",
    "    lst = data['People'].iloc[i]\n",
    "    lst = re.findall('[A-Z]+\\s[A-Z\\.\\s]*[\\'*A-Za-z\\s*]+',data['People'].iloc[i])\n",
    "    for i in lst:\n",
    "        if \"NW S\" in i:\n",
    "            continue\n",
    "        if \"JUSTICE\" in i:\n",
    "            continue\n",
    "        everyone.append(i) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "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>Title</th>\n",
       "      <th>People</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14 Penn Plaza LLC v. Pyett</td>\n",
       "      <td>PAUL SALVATORE, DAVID C. FREDERICK, CURTIS E. ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Abbott v. Perez</td>\n",
       "      <td>SCOTT A. KELLER, EDWIN S. KNEEDLER, MAX RENEA ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Abbott v. United States</td>\n",
       "      <td>DAVID L. HORAN, JAMES E. RYAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Abdul-Kabir v. Quarterman</td>\n",
       "      <td>ROBERT C. OWEN, EDWARD L. MARSHALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Abdur_Rahman v. Bell</td>\n",
       "      <td>JAMES S. LIEBMAN, PAUL G. SUMMERS, PAUL J. ZID...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Title  \\\n",
       "0  14 Penn Plaza LLC v. Pyett   \n",
       "1             Abbott v. Perez   \n",
       "2     Abbott v. United States   \n",
       "3   Abdul-Kabir v. Quarterman   \n",
       "4        Abdur_Rahman v. Bell   \n",
       "\n",
       "                                              People  \n",
       "0  PAUL SALVATORE, DAVID C. FREDERICK, CURTIS E. ...  \n",
       "1  SCOTT A. KELLER, EDWIN S. KNEEDLER, MAX RENEA ...  \n",
       "2                      DAVID L. HORAN, JAMES E. RYAN  \n",
       "3                 ROBERT C. OWEN, EDWARD L. MARSHALL  \n",
       "4  JAMES S. LIEBMAN, PAUL G. SUMMERS, PAUL J. ZID...  "
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[['Title','People']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The set being empty is a good thing. Means there are no weird names that have just a one letter last name\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tgt = \", \".join(everyone)\n",
    "print('The set being empty is a good thing. Means there are no weird names that have just a one letter last name')\n",
    "set(re.findall(\"[A-Z]+\\s[A-Z],\",tgt))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_justices =['GORSUCH','PHILLIPS','SCALIA','ROBERTS','KAVANAUGH','GINSBURG','BREYER','KENNEDY',\n",
    "               'ALITO','STEVENS','KAGAN','REHNQUIST','THOMAS','SOTOMAYOR','SOUTER','BENNETT']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#re.findall('[A-Z]+\\s[A-Z\\.\\s]*[A-Z]+',data[['People']].iloc[0][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [],
   "source": [
    "people = all_justices+everyone\n",
    "#people"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### People Dataframe\n",
    "\n",
    "#### This won't be used for the actual website. It was just helpful for cretaing the speeches dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [],
   "source": [
    "people_df = pd.DataFrame({'Name':[],'Case':[]})\n",
    "for i in range(1302):\n",
    "    l = data['People'].iloc[i]\n",
    "    title = data['Title'].iloc[i]\n",
    "    l = re.findall('[A-Z]+\\s[A-Z\\.\\s]*[\\'*A-Za-z\\s*]+',data['People'].iloc[i])\n",
    "    for j in l:\n",
    "        if \"NW S\" in j:\n",
    "            continue\n",
    "        if \"JUSTICE\" in j:\n",
    "            continue\n",
    "        people_df = people_df.append(pd.DataFrame({'Name':[j],'Case':[title]}))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3275"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(people_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [],
   "source": [
    "j = {'A':0,'B':1,'C':2}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "j.get('A')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {},
   "outputs": [],
   "source": [
    "lk = [0,0,0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 198,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(lk))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [],
   "source": [
    "outputs = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [],
   "source": [
    "outputs += ['A']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['A']"
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outputs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {
    "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>Name</th>\n",
       "      <th>Case</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PAUL SALVATORE</td>\n",
       "      <td>14 Penn Plaza LLC v. Pyett</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DAVID C. FREDERICK</td>\n",
       "      <td>14 Penn Plaza LLC v. Pyett</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CURTIS E. GANNON</td>\n",
       "      <td>14 Penn Plaza LLC v. Pyett</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SCOTT A. KELLER</td>\n",
       "      <td>Abbott v. Perez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EDWIN S. KNEEDLER</td>\n",
       "      <td>Abbott v. Perez</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Name                        Case\n",
       "0      PAUL SALVATORE  14 Penn Plaza LLC v. Pyett\n",
       "0  DAVID C. FREDERICK  14 Penn Plaza LLC v. Pyett\n",
       "0    CURTIS E. GANNON  14 Penn Plaza LLC v. Pyett\n",
       "0     SCOTT A. KELLER             Abbott v. Perez\n",
       "0   EDWIN S. KNEEDLER             Abbott v. Perez"
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "people_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "#people_df.to_csv(r\"C:\\Users\\Avena Cheng\\Desktop\\Liberating Archives\\people.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the new DataFrame to a new SQLite table\n",
    "#people_df.to_sql(\"people\", con, if_exists=\"replace\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Keywords/Acts&Bills Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [],
   "source": [
    "keywords = pd.read_csv('frequencies.csv').drop(\"Unnamed: 0\",axis=1)\n",
    "keywords = keywords.rename({'titles':'Case'},axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Abuelhawa v. United States'"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "keywords['Case'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}