LA 6.4 – Joining script.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "HwcNaqm8qo0R"
   },
   "source": [
    "## Joining Script\n",
    "This script brings together data from different sources into one file\n",
    "Set up the the working directory as FILE_PATH, all subsequent paths are relative to this directory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "collapsed": true,
    "id": "xm7_jYtMqo0T"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import re\n",
    "\n",
    "import settings\n",
    "FILE_PATH = settings.FILE_PATH"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "XWBfU79Iqo0X"
   },
   "source": [
    "### Import Congressional directory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "collapsed": true,
    "id": "pf0TrOLbqo0Y"
   },
   "outputs": [],
   "source": [
    "directory = pd.read_csv(FILE_PATH + \"Directory/All MoCs 105-114.csv\",  delimiter=\";\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "xGe6eHL8qo0b"
   },
   "source": [
    "### Generate Constituency Table\n",
    "First, import state names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     },
     "output_extras": [
      {}
     ]
    },
    "colab_type": "code",
    "id": "EIuytix_qo0c",
    "outputId": "5acbb83b-8fef-4f17-f776-7febd599bd51"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(514, 4)"
      ]
     },
     "execution_count": 8,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_names = pd.read_csv(FILE_PATH + \"help files/state names.csv\", delimiter=\";\")\n",
    "state_and_district = pd.DataFrame({\"district\": directory['district'], \"state.abbreviation\": directory['state.abbreviation']}).drop_duplicates()\n",
    "\n",
    "constituency = pd.merge(state_names[[\"state.abbreviation\", \"state.name\"]], state_and_district, left_on = [\"state.abbreviation\"], right_on = [\"state.abbreviation\"], how = \"inner\" )\n",
    "\n",
    "territory_values = [\"AS\", \"DC\", \"GU\", \"MP\", \"VI\"]\n",
    "territories = np.isin(constituency['state.abbreviation'], territory_values)\n",
    "constituency['district'] = np.where(territories == True, \"TERRITORY\", constituency['district'])\n",
    "constituency['district'] = np.where(constituency['district'] == 'nan',\"STATE\", constituency['district'])\n",
    "\n",
    "constituency = constituency.sort_values(\"state.abbreviation\")\n",
    "constituency['constituency.id'] = range(1,len(constituency) + 1)\n",
    "constituency.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "ZQ4I1wIXqo0h"
   },
   "source": [
    "##### Merge Constituency with Directory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     },
     "output_extras": [
      {}
     ]
    },
    "colab_type": "code",
    "id": "4XA-MUyfqo0h",
    "outputId": "aec6ec23-849b-4e2a-d347-31407c01e07c"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5311, 15)"
      ]
     },
     "execution_count": 9,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "territory_values = [\"AS\", \"DC\", \"GU\", \"MP\", \"VI\"]\n",
    "territories = np.isin(directory['state.abbreviation'], territory_values)\n",
    "directory['district'] = np.where(territories == True, \"TERRITORY\", directory['district'])\n",
    "directory['district'] = np.where(directory['district'] == 'nan',\"STATE\", directory['district'])\n",
    "\n",
    "constituency[\"helper\"] = constituency['state.abbreviation'].map(str) + \"-\" + constituency['district']\n",
    "directory[\"helper\"] = directory['state.abbreviation'].map(str) + \"-\" + directory['district']\n",
    "\n",
    "directory = pd.merge(directory, constituency[['constituency.id', 'helper']], on=[\"helper\"], right_index=True)\n",
    "del directory[\"helper\"]\n",
    "directory.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "4u6P3lINqo0l"
   },
   "source": [
    "### Generate zip codes table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "collapsed": true,
    "id": "KzVbEx-yqo0m"
   },
   "outputs": [],
   "source": [
    "def split_zip_codes(df):\n",
    "    rows = []\n",
    "    for index, row in df.iterrows():\n",
    "        zips = row[\"zip\"].split(\",\")\n",
    "        for zp in zips:\n",
    "            if len(zp) < 1 or zp == 'nan': continue\n",
    "            rows.append([row[\"congress\"], row['state.abbreviation'], row[\"district\"], zp ])\n",
    "    return pd.DataFrame(rows, columns = ['congress','state.abbreviation',\"district\", \"zip\"])   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     },
     "output_extras": [
      {},
      {}
     ]
    },
    "colab_type": "code",
    "id": "uPUz_VrIqo0o",
    "outputId": "a63fe6f3-1cb6-49ea-94c0-194866c6f4cd"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(433264, 3)\n"
     ]
    },
    {
     "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>congress</th>\n",
       "      <th>zip</th>\n",
       "      <th>constituency.id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>105</td>\n",
       "      <td>00801</td>\n",
       "      <td>485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>105</td>\n",
       "      <td>00802</td>\n",
       "      <td>485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>105</td>\n",
       "      <td>00803</td>\n",
       "      <td>485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>105</td>\n",
       "      <td>00804</td>\n",
       "      <td>485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>00820</td>\n",
       "      <td>485</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   congress    zip  constituency.id\n",
       "0       105  00801              485\n",
       "1       105  00802              485\n",
       "2       105  00803              485\n",
       "3       105  00804              485\n",
       "4       105  00820              485"
      ]
     },
     "execution_count": 11,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_codes = pd.DataFrame(\n",
    "    {\n",
    "    \"congress\": directory['congress'],\n",
    "    \"state.abbreviation\": directory['state.abbreviation'],\n",
    "    \"district\": directory['district'],\n",
    "    \"zip\": directory[\"zip\"].astype(str)\n",
    "    }\n",
    ")\n",
    "\n",
    "zip_codes.duplicated()\n",
    "zip_codes = zip_codes.drop_duplicates()\n",
    "\n",
    "territory_values = [\"AS\", \"DC\", \"GU\", \"MP\", \"VI\"]\n",
    "territories = np.isin(zip_codes['state.abbreviation'], territory_values)\n",
    "zip_codes['district'] = np.where(territories == True, \"TERRITORY\", zip_codes['district'])\n",
    "zip_codes['district'] = np.where(zip_codes['district'] == 'nan',\"STATE\", zip_codes['district'])\n",
    "\n",
    "#split up zip column\n",
    "zip_codes = split_zip_codes(zip_codes)\n",
    "zip_codes.shape\n",
    "\n",
    "zip_codes.replace('nan', np.nan, inplace=True)\n",
    "zip_codes.dropna(inplace=True)\n",
    "\n",
    "#merge with constituency \n",
    "zip_codes['helper'] = zip_codes['state.abbreviation'].map(str) + \"-\" + zip_codes['district']\n",
    "zip_codes = pd.merge(zip_codes, constituency[['constituency.id', 'helper']], on=[\"helper\"], right_index=True)\n",
    "zip_codes = zip_codes[[\"congress\", \"zip\",\"constituency.id\"]]\n",
    "zip_codes.drop_duplicates()\n",
    "print(zip_codes.shape)\n",
    "zip_codes[0:5]    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "M5n73j--qo0r"
   },
   "source": [
    "### Constituency Characteristics\n",
    "This table consists of the population density per district/state"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     },
     "output_extras": [
      {},
      {}
     ]
    },
    "colab_type": "code",
    "id": "f9Pc0CzCqo0s",
    "outputId": "912f8d64-a05e-4f01-cc97-4c7cc63e9d19"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1744, 7)\n"
     ]
    },
    {
     "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>constituency.id</th>\n",
       "      <th>congress</th>\n",
       "      <th>census.district.id</th>\n",
       "      <th>land.sqm</th>\n",
       "      <th>population.total</th>\n",
       "      <th>population.per.sqm</th>\n",
       "      <th>density.quintile</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>111</td>\n",
       "      <td>200</td>\n",
       "      <td>570640.950</td>\n",
       "      <td>713985</td>\n",
       "      <td>1.251198</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>112</td>\n",
       "      <td>200</td>\n",
       "      <td>570838.980</td>\n",
       "      <td>722718</td>\n",
       "      <td>1.266063</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>113</td>\n",
       "      <td>200</td>\n",
       "      <td>570640.950</td>\n",
       "      <td>735132</td>\n",
       "      <td>1.288257</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>114</td>\n",
       "      <td>200</td>\n",
       "      <td>570600.852</td>\n",
       "      <td>738432</td>\n",
       "      <td>1.294131</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>111</td>\n",
       "      <td>105</td>\n",
       "      <td>4466.522</td>\n",
       "      <td>719906</td>\n",
       "      <td>161.178205</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   constituency.id  congress  census.district.id    land.sqm  \\\n",
       "0                1       111                 200  570640.950   \n",
       "1                1       112                 200  570838.980   \n",
       "2                1       113                 200  570640.950   \n",
       "3                1       114                 200  570600.852   \n",
       "4                8       111                 105    4466.522   \n",
       "\n",
       "   population.total  population.per.sqm  density.quintile  \n",
       "0            713985            1.251198               1.0  \n",
       "1            722718            1.266063               1.0  \n",
       "2            735132            1.288257               1.0  \n",
       "3            738432            1.294131               1.0  \n",
       "4            719906          161.178205               2.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path = \"external data sets/ACS - MY Congressional District/Congressional District size and population.csv\"\n",
    "constituency_characteristics = pd.read_csv(FILE_PATH + path,  delimiter=\";\")\n",
    "\n",
    "constituency_characteristics[\"helper\"] = constituency_characteristics['state.abbreviation'].map(str) + \"-\" + constituency_characteristics['district'].apply(lambda x: str(float(x)) if x != \"TERRITORY\" else x )\n",
    "constituency_characteristics = pd.merge(constituency_characteristics, constituency[['constituency.id', 'helper']], on=[\"helper\"], right_index=True)\n",
    "\n",
    "\n",
    "#quantiles\n",
    "all_congresses = np.unique(constituency_characteristics['congress'])\n",
    "constituency_characteristics[\"density.quintile\"] = np.nan\n",
    "rows = []\n",
    "for congress in all_congresses:\n",
    "    subset = constituency_characteristics.loc[constituency_characteristics['congress'] == congress]\n",
    "    quintiles = subset[['congress', 'population.per.sqm']].quantile([.2,.4,.6,.8])\n",
    "    quintiles = quintiles[\"population.per.sqm\"].tolist()\n",
    "\n",
    "    constituency_characteristics.loc[(constituency_characteristics['congress'] == congress) & (constituency_characteristics['population.per.sqm'] <= quintiles[0]), 'density.quintile'] = 1\n",
    "    constituency_characteristics.loc[(constituency_characteristics['congress'] == congress) & (constituency_characteristics['population.per.sqm'] > quintiles[0]) & (constituency_characteristics['population.per.sqm'] <= quintiles[1]), 'density.quintile'] = 2\n",
    "    constituency_characteristics.loc[(constituency_characteristics['congress'] == congress) & (constituency_characteristics['population.per.sqm'] > quintiles[1]) & (constituency_characteristics['population.per.sqm'] <= quintiles[2]), 'density.quintile'] = 3\n",
    "    constituency_characteristics.loc[(constituency_characteristics['congress'] == congress) & (constituency_characteristics['population.per.sqm'] > quintiles[2]) & (constituency_characteristics['population.per.sqm'] <= quintiles[3]), 'density.quintile'] = 4\n",
    "    constituency_characteristics.loc[(constituency_characteristics['congress'] == congress) & (constituency_characteristics['population.per.sqm'] > quintiles[3]), 'density.quintile'] = 5\n",
    "\n",
    "constituency_characteristics = constituency_characteristics[[\"constituency.id\",\"congress\",\"census.district.id\",\"land.sqm\",\"population.total\",\"population.per.sqm\",\"density.quintile\"]]\n",
    "print(constituency_characteristics.shape)\n",
    "constituency_characteristics.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "PSaNBMumqo0v"
   },
   "source": [
    "### All hearings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "88Ru8UZcqo0x"
   },
   "source": [
    "##### Bag of names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     },
     "output_extras": [
      {}
     ]
    },
    "colab_type": "code",
    "id": "oZYhqpEBqo0y",
    "outputId": "84ff1bb1-7ed0-4d52-b0ac-0628e7627feb"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1474\n"
     ]
    }
   ],
   "source": [
    "bag_of_names = np.unique(directory[\"name.fnf\"].str.lower().tolist()).tolist()\n",
    "new_bag_of_names = []\n",
    "for name in bag_of_names:\n",
    "    new_bag_of_names.extend(name.split(\" \"))\n",
    "bag_of_names = list(set(new_bag_of_names))\n",
    "print(len(bag_of_names))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "6tN6v1d3qo02"
   },
   "source": [
    "#### Import json files and add them to the hearings dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     },
     "output_extras": [
      {}
     ]
    },
    "colab_type": "code",
    "id": "XkHALTxnqo02",
    "outputId": "6f975fb0-d9bf-44ca-a07a-3cae449ad8e8"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 3)\n",
      "                                                 file.name  members  text\n",
      "file.id                                                                  \n",
      "0        115th Congress (2017 - 2018)_Senate Hearings_C...      NaN   NaN\n"
     ]
    }
   ],
   "source": [
    "path = FILE_PATH + 'hearings & MODS/json'\n",
    "files_ = []\n",
    "for i, filename in enumerate(os.listdir(path)):\n",
    "    if filename.endswith('.json') and not \"_MODS\" in filename:\n",
    "        files_.append([filename, np.nan, np.nan])\n",
    "all_hearings = pd.DataFrame(files_, columns = [\"file.name\", \"members\", \"text\"]).sort_values(by=['file.name']).reset_index()\n",
    "all_hearings.index.names = [\"file.id\"]\n",
    "del all_hearings['index']\n",
    "print(all_hearings.shape)\n",
    "print(all_hearings.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Y4Bj4x5Vqo09"
   },
   "source": [
    "#### Initialize tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "collapsed": true,
    "id": "euBryWL5qo0-"
   },
   "outputs": [],
   "source": [
    "hearing        = pd.DataFrame(columns = [\"hearing.id\", \"committee.id\", \"subcommittee.id\",\"hearing.title\", \"is.appropriation\", \"is.nomination\", \"date\", \"url\", \"file\", \"extent\"])\n",
    "committee      = pd.DataFrame(columns = [\"committee.id\", \"subcommitee.id\", \"type\", \"committee.name\", \"subcommittee.name\", \"chamber\", \"congress.session\", \"committee.session\", \"help.id\"])\n",
    "related        = pd.DataFrame(columns = [\"hearing.id\", \"related.item\"])\n",
    "bill           = pd.DataFrame(columns = [\"hearing.id\", \"bill.type\", \"bill.number\", \"bill.congress\"])\n",
    "law            = pd.DataFrame(columns = [\"hearing.id\", \"law.number\", \"law.congress\"])\n",
    "us_code        = pd.DataFrame(columns = [\"hearing.id\", \"code\"])\n",
    "person         = pd.DataFrame(columns = [\"person.id\", \"full.name\", \"first.name\", \"middle.name\", \"surname\",\"honorific\",\"gpo.id\", \"bio.guide.id\"]).set_index(\"person.id\")\n",
    "attendance     = pd.DataFrame(columns = [\"hearing.id\", \"person.id\", \"role\"])\n",
    "congressmember = pd.DataFrame(columns = [\"person.id\", \"party\", \"chamber\"])\n",
    "witness        = pd.DataFrame(columns = [\"person.id\", \"full.title\", \"job.title\", \"organization\"])  \n",
    "speech         = pd.DataFrame(columns = [\"speech.id\", \"previous.speech.id\", \"subsequent.speech.id\", \"hearing.id\", \"statement.type\", \"conversation\", \"text\"])  \n",
    "speaker        = pd.DataFrame(columns = [\"speech.id\", \"person.id\", \"surname\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "0xhOz-TNqo1C"
   },
   "source": [
    "#### Loop through all the hearing files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     },
     "output_extras": [
      {}
     ]
    },
    "colab_type": "code",
    "id": "-3WRKvzsqo1D",
    "outputId": "c29a9706-b7e5-4ec8-f4f2-7a1a8f648a27"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are witnesses\n",
      "Amparo, Alex, Assistant Administrator for Recovery, Office of Response and Recovery, Federal Emergency Management Agency, U.S. Department of Homeland Security\n",
      "{'surname': 'Amparo', 'first.name': 'Alex', 'full.name': 'Alex Amparo'}\n",
      "__________\n",
      "{'full.title': 'Amparo, Alex, Assistant Administrator for Recovery, Office of Response and Recovery, Federal Emergency Management Agency, U.S. Department of Homeland Security', 'job.title': 'Assistant Administrator for Recovery', 'organization': 'Amparo, Alex, Assistant Administrator for Recovery, Office of Response and Recovery, Federal Emergency Management Agency, U.S. Department of Homeland Security'}\n"
     ]
    }
   ],
   "source": [
    "person_id, speech_id, hearing_id = 0,0,0\n",
    "\n",
    "for i, row in all_hearings.iterrows():\n",
    "\n",
    "    filename = row['file.name']\n",
    "    path =  FILE_PATH + 'hearings & MODS/json/' + filename\n",
    "    path_to_mod = path.replace(\".json\", \"_MODS.json\")\n",
    "    mods_raw = pd.read_json(path_to_mod)\n",
    "\n",
    "    \n",
    "    #COMMITTEE\n",
    "    congcommitte = mods_raw['congcommittee'] if 'congcommittee' in mods_raw else None\n",
    "    c_help_id = congcommitte['attrs'][0]['authorityid'] + \"-\" + congcommitte['attrs'][0]['congress']   \n",
    "    committee_sub_name = None\n",
    "    \n",
    "    if len(congcommitte['values']) == 1:\n",
    "        if  'subcommittee' in mods_raw and (len(mods_raw['subcommittee']['values']) == 1):\n",
    "            committee_name, committee_sub_name, _ = congcommitte['values'][0].split(\"\\n\\n\")\n",
    "            committee_sub_name = committee_sub_name.strip().replace(\"\\n\",\"\" )\n",
    "            committee_name = committee_name.strip().replace(\"\\n\",\"\" )\n",
    "        else:\n",
    "            committee_name = congcommitte['values'][0].strip().replace(\"\\n\",\"\")     \n",
    "    else: \n",
    "        committee_name = (congcommitte['values'][0]).strip().replace(\"\\n\",\"\" )\n",
    "\n",
    "    if not c_help_id in committee[\"help.id\"].values:\n",
    "        committee = committee.append(pd.DataFrame({ \n",
    "            \"committee.id\" : 1 if i == 0 else committee['committee.id'].max() + 1,\n",
    "            \"subcommitee.id\": 1 if i == 0 else committee['subcommitee.id'].max() + 1,\n",
    "            \"type\": congcommitte['attrs'][0]['type'], \n",
    "            \"committee.name\": committee_name, \n",
    "            \"subcommittee.name\": committee_sub_name,\n",
    "            \"chamber\": mods_raw['chamber']['values'],\n",
    "            \"congress.session\": congcommitte['attrs'][0]['congress'] ,\n",
    "            \"committee.session\":mods_raw['session']['values'], \n",
    "            \"help.id\": c_help_id\n",
    "        }), ignore_index=True)\n",
    "\n",
    "        \n",
    "    #HEARING\n",
    "    hearing_id += 1 \n",
    "    hearing = hearing.append(pd.DataFrame({ \n",
    "        \"hearing.id\": hearing_id,\n",
    "        \"committee.id\": int(committee.loc[committee['help.id'] == c_help_id][\"committee.id\"]),\n",
    "        \"subcommittee.id\": int(committee.loc[committee['help.id'] == c_help_id][\"subcommitee.id\"]),\n",
    "        \"hearing.title\": mods_raw['title']['values'][0],\n",
    "        \"date\": mods_raw['helddate']['values'][0],\n",
    "        \"is.appropriation\": mods_raw['isappropriation']['values'],\n",
    "        \"is.nomination\": mods_raw['isnomination']['values'],\n",
    "        \"file\": filename,\n",
    "        \"extent\": mods_raw['extent']['values'] if ('extent' in mods_raw) else None,\n",
    "        \"url\": mods_raw['url']['values'][0],\n",
    "    }), ignore_index=True)\n",
    "    \n",
    "\n",
    "    #RELATED ITEM\n",
    "    if \"relateditem\" in mods_raw and len(mods_raw[\"relateditem\"][\"values\"][0]) > 0:\n",
    "        clean_related_item = [x.replace(\"\\n\", \"\") for x in mods_raw[\"relateditem\"][\"values\"] if x != \"\\n\\n\"]\n",
    "        related = related.append(pd.DataFrame({ \n",
    "            \"hearing.id\": hearing_id,\n",
    "            \"related.item\": clean_related_item,\n",
    "        }), ignore_index=True)\n",
    "      \n",
    "    \n",
    "    #BILLS\n",
    "    if 'bill' in mods_raw and len(mods_raw[\"bill\"][\"attrs\"]) > 0:\n",
    "        for j in range(len(mods_raw[\"bill\"][\"attrs\"])):\n",
    "            bill = bill.append(pd.DataFrame({ \n",
    "                \"hearing.id\": hearing_id,\n",
    "                \"bill.type\": mods_raw[\"bill\"][\"attrs\"][j][\"type\"],\n",
    "                \"bill.number\": mods_raw[\"bill\"][\"attrs\"][j][\"number\"],\n",
    "                \"bill.congress\": [mods_raw[\"bill\"][\"attrs\"][j][\"congress\"]],\n",
    "            }), ignore_index=True)   \n",
    "     \n",
    "    \n",
    "    #LAW\n",
    "    if 'law' in mods_raw and len(mods_raw[\"law\"][\"attrs\"]) > 0:\n",
    "        for j in range(len(mods_raw[\"law\"][\"attrs\"])):\n",
    "            law = law.append(pd.DataFrame({ \n",
    "                \"hearing.id\": hearing_id,\n",
    "                \"law.number\": mods_raw[\"law\"][\"attrs\"][j][\"number\"],\n",
    "                \"law.congress\": [mods_raw[\"law\"][\"attrs\"][j][\"congress\"]],\n",
    "            }), ignore_index=True)  \n",
    "            \n",
    "\n",
    "    #US CODE\n",
    "    if \"partnumber\" in mods_raw and len(mods_raw[\"partnumber\"]) > 0:\n",
    "        us_code = us_code.append(pd.DataFrame({\n",
    "            \"hearing.id\": hearing_id,\n",
    "            \"code\": mods_raw[\"partnumber\"]['values']\n",
    "        }), ignore_index=True) \n",
    "\n",
    "    #CONGRESSMEMBER, PERSON, ATTENDANCE \n",
    "    \n",
    "    if ('congmember' in mods_raw and len(mods_raw['congmember']) > 0):\n",
    "        congmember = mods_raw['congmember']\n",
    "        all_hearings.loc[(all_hearings[\"file.name\"] == filename), \"members\"] = \"GOT 'EM\"\n",
    "        \n",
    "        persons = congmember['values']\n",
    "        for p in range(len(persons)):\n",
    "            person_id += 1\n",
    "            person_lp = {\"person.id\": int(person_id)}\n",
    "            congress_member_lp = {}\n",
    "            attendance_lp = {}\n",
    "            \n",
    "            with_of = congmember['values'][p].replace(\"\\n\", \"$\")\n",
    "            non_of = re.sub(\"Judge |Dr. |Mr. |Mrs. |Ms. |Honorable |Hon. |, MD|, Jr.| Jr.\", \"\", with_of).split(\"of\")[0]\n",
    "            non_of = re.sub(\"\\\\$\" , \"\", non_of)\n",
    "            non_of = re.sub(\",\\\\s*,\",\",\", non_of)\n",
    "            non_of = re.sub(\"^,+|,+$\",\"\", non_of)\n",
    "            non_of = non_of.strip().lower()\n",
    "            \n",
    "            if (\" Jr.\" in with_of): person_lp['honorific'] = \"Jr.\"\n",
    "            if (\"Hon.|Honorable\" in with_of): person_lp['honorific'] = \"Hon.\"\n",
    "            if (\"Judge\" in with_of): person_lp['honorific'] = \"Judge\"\n",
    "            if (\", MD\" in with_of): person_lp['honorific'] = \"MD\"\n",
    "            if (\"Dr. \" in with_of): person_lp['honorific'] = \"Dr.\"\n",
    "                \n",
    "            person_lp[\"full.name\"] = non_of\n",
    "            person_lp[\"first.name\"] = non_of.split(\" \")[0]\n",
    "            person_lp['surname'] = non_of.split(\" \")[len(non_of.split(\" \")) - 1]\n",
    "            if len(non_of.split(\" \")) > 2:\n",
    "                person_lp[\"middle.name\"] = non_of.replace(person_lp[\"first.name\"], \"\").replace(person_lp[\"surname\"], \"\").strip()\n",
    "\n",
    "            #Directory match\n",
    "            directory_match = directory.loc[\n",
    "                (directory[\"name\"].str.lower() == person_lp[\"full.name\"].lower()) & \n",
    "                (directory[\"congress\"].astype(int) == int(congcommitte['attrs'][0]['congress']))\n",
    "            ]\n",
    "            \n",
    "            if directory_match.shape[0]:\n",
    "                person_lp[\"bio.guide.id\"] = directory_match[\"bio.guide.id\"]\n",
    "                person_lp[\"gpo.id\"] = directory_match[\"gpo.id\"]\n",
    "                if not person_lp[\"gpo.id\"]:\n",
    "                    if len(congmember['attrs'][0]['authorityid']) > 0:\n",
    "                        person_lp[\"gpo.id\"] = congmember['attrs'][p]['authorityid']\n",
    "                        \n",
    "                congress_member_lp[\"party\"] = directory_match[\"party\"]\n",
    "                congress_member_lp[\"chamber\"] = directory_match[\"chamber\"]\n",
    "                congress_member_lp[\"constituency.id\"] = directory_match[\"constituency.id\"]\n",
    "                    \n",
    "            else:\n",
    "                directory_match = directory.loc[\n",
    "                    (directory[\"name.fnf\"].str.lower() == person_lp[\"full.name\"].lower()) & \n",
    "                    (directory[\"congress\"].astype(int) == int(congcommitte['attrs'][0]['congress']))\n",
    "                ]\n",
    "\n",
    "                if directory_match.shape[0]:\n",
    "                    person_lp[\"bio.guide.id\"] = directory_match[\"bio.guide.id\"]\n",
    "                    person_lp[\"gpo.id\"] = directory_match[\"gpo.id\"]\n",
    "                    if not person_lp[\"gpo.id\"]:\n",
    "                        if len(congmember['attrs'][p]['authorityid']) > 0:\n",
    "                            person_lp[\"gpo.id\"] = congmember['attrs'][0]['authorityid']\n",
    "                        \n",
    "                    congress_member_lp[\"party\"] = directory_match[\"party\"]\n",
    "                    congress_member_lp[\"chamber\"] = directory_match[\"chamber\"]\n",
    "                    congress_member_lp[\"constituency.id\"] = directory_match[\"constituency.id\"]\n",
    "                else:\n",
    "                    if \"authorityid\" in congmember['attrs'][p] and  len(congmember['attrs'][p]['authorityid']) > 0:\n",
    "                        person_lp[\"gpo.id\"] = congmember['attrs'][p]['authorityid']\n",
    "                    if \"chamber\" in congmember['attrs'][p] and len(congmember['attrs'][p]['chamber']) > 0:\n",
    "                        congress_member_lp[\"chamber\"] =  congmember['attrs'][p]['chamber']\n",
    "                    if \"party\" in congmember['attrs'][p] and len(congmember['attrs'][p]['party']) > 0:\n",
    "                        congress_member_lp[\"party\"] =  congmember['attrs'][p]['party']\n",
    "                \n",
    "            if len(congmember['attrs'][p][\"role\"]) > 0:\n",
    "                attendance_lp[\"person.id\"] = person_id\n",
    "                attendance_lp[\"hearing.id\"] = hearing_id\n",
    "                attendance_lp[\"role\"] = congmember[\"attrs\"][p][\"role\"]\n",
    "            congress_member_lp[\"person.id\"] = person_id\n",
    "            \n",
    "            #PERSON, ATTENDANCE, CONGRESSMEMBER\n",
    "            #Line 417-455 in the R code will be dealt with here \n",
    "            person = person.append(pd.DataFrame(person_lp, index=[0]), ignore_index=True)\n",
    "            attendance = attendance.append(pd.DataFrame(attendance_lp, index=[0]), ignore_index=True)\n",
    "            congressmember = congressmember.append(pd.DataFrame(congress_member_lp, index=[0]), ignore_index=True)\n",
    "\n",
    "    else:\n",
    "        all_hearings.loc[(all_hearings[\"file.name\"] == filename), \"members\"] = \"NONE\"\n",
    "\n",
    "    #WITNESS\n",
    "    if \"witness\" in mods_raw and len(mods_raw[\"witness\"][\"values\"]) > 0:\n",
    "        print(\"There are witnesses\")\n",
    "        witness_lp = {}\n",
    "        person_lp = {}\n",
    "        persons = mods_raw[\"witness\"][\"values\"]\n",
    "        style = None\n",
    "\n",
    "        for p in range(len(persons)):\n",
    "            style = 1 if int(mods_raw['congcommittee'][\"attrs\"][0][\"congress\"]) <= 107 else 3\n",
    "            witness_lp[\"full.title\"] = persons[p]\n",
    "            non_hon = re.sub(\"Judge |Dr. |Mr. |Mrs. |Ms. |Honorable |Hon. |, MD|, Jr.| Jr.\", \"\",witness_lp[\"full.title\"] )\n",
    "            non_hon = re.sub(\",\\\\s*,\",\",\", non_hon)\n",
    "            non_hon = re.sub(\"^,+|,+$\",\"\", non_hon)\n",
    "            \n",
    "            if (\" Jr.\" in witness_lp[\"full.title\"]): person_lp['honorific'] = \"Jr.\"\n",
    "            if (\"Hon.|Honorable\" in witness_lp[\"full.title\"]): person_lp['honorific'] = \"Hon.\"\n",
    "            if (\"Judge\" in witness_lp[\"full.title\"]): person_lp['honorific'] = \"Judge\"\n",
    "            if (\", MD\" in witness_lp[\"full.title\"]): person_lp['honorific'] = \"MD\"\n",
    "            if (\"Dr. \" in witness_lp[\"full.title\"]): person_lp['honorific'] = \"Dr.\"\n",
    "                \n",
    "            print(non_hon)\n",
    "            style = spaces_1 = spaces_2 = names_1 = names_2 = None\n",
    "            spaces_1 = len(non_hon.split(\", \")[0]) - len(re.sub(\",\\\\s*,\",\",\", non_hon.split(\", \")[0]))\n",
    "            q = \"\".join(non_hon.split(\", \")[0].split(\" \")).lower()\n",
    "            r = q #TODO figure this part out\n",
    "            names_1 = len(q) - len(r)\n",
    "\n",
    "            if len(non_hon.split(\", \")[0]) > 1:\n",
    "                spaces_2 = len(non_hon.split(\", \")[1]) - len(re.sub(\",\\\\s*,\",\",\", non_hon.split(\", \")[1]))\n",
    "                names_2 = None\n",
    "            else:\n",
    "                style = 3\n",
    "            \n",
    "            if spaces_1 == 0 and spaces_2 < 2: style = 1\n",
    "            if spaces_1 > 2 and  spaces_2 <= 2: style = 2\n",
    "            if \"social security administration\" in non_hon.lower(): style = 2\n",
    "            if spaces_1 in [1,2] and spaces_2 < 2:style = 3\n",
    "            \n",
    "            if style == 1:\n",
    "                person_lp['surname'] = non_hon.split(\", \")[0]\n",
    "                person_lp[\"first.name\"] = non_hon.split(\", \")[1]\n",
    "                \n",
    "                if len(person_lp[\"first.name\"].split(\" \")) > 1:\n",
    "                    person_lp[\"middle.name\"] =  person_lp[\"first.name\"].replace(person_lp[\"first.name\"].split(\" \")[0], \"\").strip()\n",
    "                    person_lp[\"first.name\"] = person_lp[\"first.name\"].split(\" \")[0]\n",
    "                    person_lp[\"full.name\"] = \" \".join([person_lp[\"first.name\"], person_lp[\"middle.name\"], person_lp[\"surname\"]])\n",
    "                else:\n",
    "                    person_lp[\"full.name\"] = \" \".join([person_lp[\"first.name\"], person_lp[\"surname\"]])\n",
    "            \n",
    "                if len(non_hon.split(\", \")) == 3:\n",
    "                    witness_lp[\"organization\"] = non_hon.split(\", \")[2]\n",
    "                    \n",
    "                if len(non_hon.split(\", \")) == 4:\n",
    "                    witness_lp[\"job.title\"] = non_hon.split(\", \")[2]\n",
    "                    witness_lp[\"organization\"] = non_hon.split(\", \")[3]\n",
    "\n",
    "                if len(witness_lp[\"full.title\"].split(\", \")) > 4:\n",
    "                    witness_lp[\"job.title\"] = witness_lp[\"full.title\"].split(\", \")[2]\n",
    "                    l = len(non_hon.split(\", \"))\n",
    "                    org=\"xyz123\"\n",
    "                    for spot in range(l):\n",
    "                        org = org + \", \" + non_hon.split(\", \")[spot]\n",
    "                    org = org.replace(\"xyz123, \", \"\")\n",
    "                    witness_lp[\"organization\"] = org\n",
    "\n",
    "                print(person_lp)\n",
    "                print(\"_\" * 10)\n",
    "                print(witness_lp)\n",
    "                break;\n",
    "            ## Unpacking style 2: organization, full name, title or honorific*, function*\n",
    "            if style == 2:\n",
    "                witness_lp[\"organization\"] = non_hon.split(\", \")[0]\n",
    "                person_lp[\"full.name\"] =  person_lp[\"full.name\"].split(\" \")[0]\n",
    "                person_lp['first.name'] = non_hon.split(\", \")[0]\n",
    "                person_lp[\"surname\"] = person_lp['first.name'].split(\" \")[0] #Buggy\n",
    "                \n",
    "                if len(person_lp[\"ful;.name\"].split(\" \") > 2):\n",
    "                    person_lp[\"middle.name\"] =  person_lp[\"full.name\"].replace(person_lp[\"first.name\"].split(\" \")[0], \"\").strip()\n",
    "                    person_lp[\"middle.name\"] = person_lp[\"middle.name\"].replace(person_lp[\"surname\"], \"\").strip()\n",
    "                \n",
    "                if len(non_hon.split(\", \")) == 3:\n",
    "                    witness_lp[\"organization\"] = non_hon.split(\", \")[2]\n",
    "                    \n",
    "                if len(non_hon.split(\", \")) > 3:\n",
    "                    l = len(non_hon.split(\", \"))\n",
    "                    org=\"xyz123\"\n",
    "                    for spot in range(l):\n",
    "                        org = org + \", \" + witness_lp[\"full.title\"].split(\", \")[spot]\n",
    "                    org = org.replace(\"xyz123, \", \"\")\n",
    "                    witness[\"job.title\"] = org\n",
    "            \n",
    "            #Unpacking style 3: fullname, function*, organization*, city*, country or territory*\n",
    "            if style == 3:\n",
    "                person_lp[\"full.name\"] = non_hon.split(\", \")[0]\n",
    "                person_lp[\"first.name\"] = person_lp[\"full.name\"] #buggy\n",
    "                person_lp[\"surname\"] = person_lp[\"full.name\"]\n",
    "                \n",
    "                if len(person_lp[\"full.name\"].split(\" \")) > 2:\n",
    "                    person_lp[\"middle.name\"] = person_lp[\"full.name\"].replace(person_lp[\"first.name\"], \"\")\n",
    "                    person_lp[\"middle.name\"] = person_lp[\"middle.name\"].replace(person_lp[\"surname\"], \"\").strip()\n",
    "                    \n",
    "                if len(non_hon.split(\", \")) == 2:\n",
    "                    witness_lp[\"organization\"] = non_hon.split(\", \")[1]\n",
    "                \n",
    "                if len(non_hon.split(\", \")) == 3:\n",
    "                    witness_lp[\"job.title\"] = non_hon.split(\", \")[1]\n",
    "                    witness_lp[\"organization\"] = non_hon.split(\", \")[2]\n",
    "                \n",
    "                if len(non_hon.split(\", \")) > 3:\n",
    "                    witness_lp[\"job.title\"] = non_hon.split(\", \")[2]\n",
    "                    l = len(non_hon.split(\", \"))\n",
    "                    org=\"xyz123\"\n",
    "                    for spot in range(l):\n",
    "                        org += org + \", \" + non_hon.split(\", \")[spot]\n",
    "                    org = org.replace(\"xyz123, \", \"\")\n",
    "                    witness_lp[\"organization\"] = org\n",
    "        \n",
    "        person_lp[\"full.name\"] = person_lp[\"full.name\"].lower()\n",
    "        person_lp[\"first.name\"] = person_lp[\"first.name\"].lower()\n",
    "        person_lp[\"middle.name\"] = person_lp[\"middle.name\"].lower()\n",
    "        person_lp[\"surname\"] = person_lp[\"surname\"].lower()\n",
    "        person_lp[\"honorific\"] = person_lp[\"honorific\"].lower()\n",
    "        \n",
    "        witness_lp[\"organization\"] = witness_lp[\"organization\"].lower()\n",
    "        witness_lp[\"job.title\"] = witness_lp[\"job.title\"].lower()\n",
    "        witness_lp[\"full.title\"] = witness_lp[\"full.title\"].lower()\n",
    "        \n",
    "        attendance_lp = {}\n",
    "        #check if person exists in Persons table\n",
    "        person_match = person.loc[(person[\"full.name\"] == person_lp[\"full.name\"])]\n",
    "        if person_match.shape[0]:\n",
    "            witness_lp[\"person.id\"] = person_match['id']\n",
    "            attendance[\"person.id\"] = person_match['id']\n",
    "            attendance[\"hearing.id\"] = hearing_id\n",
    "            attendance = attendance.append(pd.DataFrame(attendance_lp, index=[0]), ignore_index=True)\n",
    "        else:\n",
    "            person_lp[\"person_id\"] = person_id + 1\n",
    "            person = person.append(pd.DataFrame(person_lp, index=[0]), ignore_index=True)\n",
    "        witness = witness.append(pd.DataFrame(witness_lp, index=[0]), ignore_index=True)\n",
    "            \n",
    "            \n",
    "    #SPEECHES, SPEAKERS\n",
    "    speech_counter = 0\n",
    "    hearing_raw = pd.read_json(path)\n",
    "    if len(hearing_raw) > 0:\n",
    "        all_hearings.loc[(all_hearings[\"file.name\"] == filename), \"text\"] = \"GOT SOME\"\n",
    "        speech_id += 1\n",
    "        \n",
    "        for con in range(len(hearing_raw)):\n",
    "            for sp in range(len(hearing_raw[con])):\n",
    "                if not hearing_raw[con][sp]: continue\n",
    "                speech_lp = {}\n",
    "                speech_lp[\"speech.id\"] = speech_id\n",
    "                speech_lp[\"previous.speech.id\"] = speech_id - 1\n",
    "                speech_lp[\"hearing.id\"] = hearing_id\n",
    "                speech_lp[\"statement.type\"] = np.nan\n",
    "                speech_lp[\"conversation\"] = con\n",
    "                speech_lp[\"text\"] = hearing_raw[con][sp][\"speech\"]\n",
    "                speech = speech.append(pd.DataFrame(speech_lp, index=[0]), ignore_index=True)\n",
    "        \n",
    "                attendance_lp = attendance.loc[(attendance[\"hearing.id\"] == hearing_id)]\n",
    "                attendance_lp = pd.merge(attendance_lp, person[['person.id', 'surname']], on=[\"person.id\"], right_index=True)\n",
    "\n",
    "                pids = np.unique(attendance_lp.loc[(attendance_lp[\"surname\"].str.lower() == hearing_raw[con][sp][\"surname\"])][\"person.id\"]).tolist()\n",
    "                pids = \";\".join([str(x) for x in pids])\n",
    "                if not pids: pids = -999\n",
    "                if pids == \";\": pids = -222\n",
    "                    \n",
    "                speaker_lp = {}\n",
    "                speaker_lp[\"speech.id\"] = speech_id\n",
    "                speaker_lp[\"surname\"] = hearing_raw[con][sp][\"surname\"]\n",
    "                speaker_lp[\"person.id\"] = pids\n",
    "                speaker = speaker.append(pd.DataFrame(speaker_lp, index=[0]), ignore_index=True)\n",
    "    else:\n",
    "        all_hearings.loc[(all_hearings[\"file.name\"] == filename), \"text\"] = \"EMPTY\"\n",
    "    if i == 100: break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "3YbvFynjqo1G"
   },
   "source": [
    "#### Storing the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "collapsed": true,
    "id": "H5bjQQ0kqo1G"
   },
   "outputs": [],
   "source": [
    "person.to_csv(FILE_PATH + \"database/csv/person/person - the only file.csv\")\n",
    "committee.to_csv(FILE_PATH + \"database/csv/committee/committee - the only file.csv\")\n",
    "constituency.to_csv(FILE_PATH + \"database/csv/constituency/constituency - the only file.csv\")\n",
    "constituency_characteristics.to_csv(FILE_PATH + \"database/csv/constituency_characteristics/constituency_characteristics - the only file.csv\")\n",
    "zip_codes.to_csv(FILE_PATH + \"database/csv/zip_codes/zip_codes - the only file.csv\")\n",
    "congressmember.to_csv(FILE_PATH + \"database/csv/congressmember/congressmember - the only file.csv\")\n",
    "witness.to_csv(FILE_PATH + \"database/csv/witness/witness - the only file.csv\")\n",
    "speech.to_csv(FILE_PATH + \"database/csv/speech/speech - the only file.csv\")\n",
    "speaker.to_csv(FILE_PATH + \"database/csv/speaker/speaker - the only file.csv\")\n",
    "attendance.to_csv(FILE_PATH + \"database/csv/attendance/attendance - the only file.csv\")\n",
    "bill.to_csv(FILE_PATH + \"database/csv/bill/bill - the only file.csv\")\n",
    "law.to_csv(FILE_PATH + \"database/csv/law/law - the only file.csv\")\n",
    "related.to_csv(FILE_PATH + \"database/csv/related/related - the only file.csv\")\n",
    "us_code.to_csv(FILE_PATH + \"database/csv/us_code/us_code - the only file.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "collapsed": true,
    "id": "F7zcYwPBqo1K"
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "collapsed": true,
    "id": "sNeyxHLvqo1O"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "default_view": {},
   "name": "Joining Script.ipynb",
   "provenance": [],
   "version": "0.3.2",
   "views": {}
  },
  "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}