scripts/02_analyze_ls_pwnage.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8ebf3dd8-cfe3-4a08-a8d0-36a24f8e53cd",
   "metadata": {},
   "source": [
    "### How Many Indian MP Accounts Have Been Hacked?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "bfb5287b-2d82-4a20-b9f6-26756bad843c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import json\n",
    "import logging\n",
    "import glob"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "803a10f1-53c4-4adf-8f81-4563d7d0110b",
   "metadata": {},
   "source": [
    "### Concat HIBP Data\n",
    "\n",
    "Ignore the empty files. We will impute 0s for those emails. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "caa4a824-b05a-4f31-914a-1f95085e27d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import glob\n",
    "import logging\n",
    "import pandas as pd\n",
    "\n",
    "# Set up logging\n",
    "logging.basicConfig(filename='empty_return.log', level=logging.ERROR)\n",
    "\n",
    "# Get a list of JSON files\n",
    "json_files = glob.glob('pwned/*.json')\n",
    "\n",
    "# List to store DataFrames\n",
    "df_list = []\n",
    "\n",
    "# Iterate over the JSON files\n",
    "for file in json_files:\n",
    "    try:\n",
    "        df = pd.read_json(file)\n",
    "        # Append DataFrame to the list\n",
    "        file_name = file.rsplit('/', 1)[-1].rstrip('.json')\n",
    "        df['file_name'] = file_name\n",
    "        df_list.append(df)\n",
    "    except Exception as e:\n",
    "        # Log the error\n",
    "        logging.error(f\"Error processing file: {file}. Error message: {str(e)}\")\n",
    "        continue\n",
    "\n",
    "# Concatenate DataFrames\n",
    "df_hibp = pd.concat(df_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ee27c56a-3383-4a5e-95a5-68f9dc0e8889",
   "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>Name</th>\n",
       "      <th>email</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PDL</td>\n",
       "      <td>profhay@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LinkedIn</td>\n",
       "      <td>profhay@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Netlog</td>\n",
       "      <td>profhay@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>YouveBeenScraped</td>\n",
       "      <td>profhay@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>OnlinerSpambot</td>\n",
       "      <td>vidyasagar@sansad.nic.i</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>db8151dd</td>\n",
       "      <td>nishikant.dubey.mp@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>IndiaMART</td>\n",
       "      <td>nishikant.dubey.mp@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Twitter200M</td>\n",
       "      <td>nishikant.dubey.mp@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>db8151dd</td>\n",
       "      <td>kalraj.mishra@sansad.nic.i</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>db8151dd</td>\n",
       "      <td>ks.tanwar@sansad.nic.i</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2117 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                Name                         email\n",
       "0                PDL             profhay@gmail.com\n",
       "1           LinkedIn             profhay@gmail.com\n",
       "2             Netlog             profhay@gmail.com\n",
       "3   YouveBeenScraped             profhay@gmail.com\n",
       "0     OnlinerSpambot       vidyasagar@sansad.nic.i\n",
       "..               ...                           ...\n",
       "1           db8151dd  nishikant.dubey.mp@gmail.com\n",
       "2          IndiaMART  nishikant.dubey.mp@gmail.com\n",
       "3        Twitter200M  nishikant.dubey.mp@gmail.com\n",
       "0           db8151dd    kalraj.mishra@sansad.nic.i\n",
       "0           db8151dd        ks.tanwar@sansad.nic.i\n",
       "\n",
       "[2117 rows x 2 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_hibp.rename(columns={'file_name': 'email'}, inplace = True)\n",
    "df_hibp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "32c363a3-b336-4250-ac35-51e7c91d01c7",
   "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>email</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>38ashokroad@gmail.com</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>97gambhirgautam@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>KULDEEPSINGHBISHNOI@gmail.com</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a.hussain@sansad.nic.i</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>926</th>\n",
       "      <td>yashvirsansad@gmail.com</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>927</th>\n",
       "      <td>yashwant.singh19@sansad.nic.i</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>928</th>\n",
       "      <td>ys.avinash@gmail.com</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>929</th>\n",
       "      <td>ysinha2005@hotmail.com</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>930</th>\n",
       "      <td>ysinha@sansad.nic.i</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>931 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             email  count\n",
       "0            38ashokroad@gmail.com      5\n",
       "1        97gambhirgautam@gmail.com      1\n",
       "2    KULDEEPSINGHBISHNOI@gmail.com      7\n",
       "3                             None      8\n",
       "4           a.hussain@sansad.nic.i      2\n",
       "..                             ...    ...\n",
       "926        yashvirsansad@gmail.com      1\n",
       "927  yashwant.singh19@sansad.nic.i      1\n",
       "928           ys.avinash@gmail.com      2\n",
       "929         ysinha2005@hotmail.com      5\n",
       "930            ysinha@sansad.nic.i      2\n",
       "\n",
       "[931 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_hibp_sum = df_hibp.groupby('email').size().rename('count', inplace=True).reset_index()\n",
    "df_hibp_sum"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "408dd480-036c-468f-a7d2-43cb5690a681",
   "metadata": {},
   "source": [
    "### Get all pols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "87118940-3a60-4007-bf81-cd9a846d5ce9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ls_17.json\n",
      "ls_16.json\n",
      "ls_13.json\n",
      "ls_14.json\n",
      "ls_15.json\n",
      "ls_12.json\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(3196, 34)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_list = []\n",
    "\n",
    "for filename in os.listdir(\"../daughters/ls_data/\"):\n",
    "    print(filename)\n",
    "    with open(os.path.join(\"../daughters/ls_data/\", filename), \"r\") as f:\n",
    "            data = json.load(f)\n",
    "    df = pd.DataFrame(data['membersDtoList'])\n",
    "    df['ls'] = filename[3:-5]\n",
    "    df_list.append(df)\n",
    "\n",
    "df_pols = pd.concat(df_list)\n",
    "df_pols = df_pols.reset_index()\n",
    "df_pols.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb68b265-e66d-4f8f-a9db-3b9363c2ba8b",
   "metadata": {},
   "source": [
    "### Convert to long form to get each email in a separate row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ba2c82a0-06ca-4131-97ba-f2d40755b167",
   "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>index</th>\n",
       "      <th>mpsno</th>\n",
       "      <th>initial</th>\n",
       "      <th>firstName</th>\n",
       "      <th>lastName</th>\n",
       "      <th>gender</th>\n",
       "      <th>partyFname</th>\n",
       "      <th>partySname</th>\n",
       "      <th>stateName</th>\n",
       "      <th>constName</th>\n",
       "      <th>...</th>\n",
       "      <th>imageUrl</th>\n",
       "      <th>profileUrl</th>\n",
       "      <th>dob</th>\n",
       "      <th>numberOfSons</th>\n",
       "      <th>numberOfDaughters</th>\n",
       "      <th>qualification</th>\n",
       "      <th>freedom</th>\n",
       "      <th>profession2</th>\n",
       "      <th>categoryCode</th>\n",
       "      <th>ls</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>344</td>\n",
       "      <td>Shri</td>\n",
       "      <td></td>\n",
       "      <td>A. Raja</td>\n",
       "      <td>Male</td>\n",
       "      <td>Dravida Munnetra Kazhagam</td>\n",
       "      <td>DMK</td>\n",
       "      <td>Tamil Nadu                                    ...</td>\n",
       "      <td>Nilgiris</td>\n",
       "      <td>...</td>\n",
       "      <td>https://sansad.in/getFile/mpimage/photo/344.jp...</td>\n",
       "      <td>None</td>\n",
       "      <td>10/05/1963</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Post Graduate</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>(SC)</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>344</td>\n",
       "      <td>Shri</td>\n",
       "      <td></td>\n",
       "      <td>A. Raja</td>\n",
       "      <td>Male</td>\n",
       "      <td>Dravida Munnetra Kazhagam</td>\n",
       "      <td>DMK</td>\n",
       "      <td>Tamil Nadu                                    ...</td>\n",
       "      <td>Nilgiris</td>\n",
       "      <td>...</td>\n",
       "      <td>https://sansad.in/getFile/mpimage/photo/344.jp...</td>\n",
       "      <td>None</td>\n",
       "      <td>10/05/1963</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Post Graduate</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>(SC)</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5175</td>\n",
       "      <td>Shri</td>\n",
       "      <td>Narayana Swamy</td>\n",
       "      <td>Abbaiah</td>\n",
       "      <td>Male</td>\n",
       "      <td>Bharatiya Janata Party</td>\n",
       "      <td>BJP</td>\n",
       "      <td>Karnataka                                     ...</td>\n",
       "      <td>Chitradurga</td>\n",
       "      <td>...</td>\n",
       "      <td>https://sansad.in/getFile/mpimage/photo/5175.j...</td>\n",
       "      <td>None</td>\n",
       "      <td>16/05/1957</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Graduate</td>\n",
       "      <td>N</td>\n",
       "      <td>Social Worker                                 ...</td>\n",
       "      <td>(SC)</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5175</td>\n",
       "      <td>Shri</td>\n",
       "      <td>Narayana Swamy</td>\n",
       "      <td>Abbaiah</td>\n",
       "      <td>Male</td>\n",
       "      <td>Bharatiya Janata Party</td>\n",
       "      <td>BJP</td>\n",
       "      <td>Karnataka                                     ...</td>\n",
       "      <td>Chitradurga</td>\n",
       "      <td>...</td>\n",
       "      <td>https://sansad.in/getFile/mpimage/photo/5175.j...</td>\n",
       "      <td>None</td>\n",
       "      <td>16/05/1957</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Graduate</td>\n",
       "      <td>N</td>\n",
       "      <td>Social Worker                                 ...</td>\n",
       "      <td>(SC)</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2654</td>\n",
       "      <td>Dr.</td>\n",
       "      <td>Farooq</td>\n",
       "      <td>Abdullah</td>\n",
       "      <td>Male</td>\n",
       "      <td>Jammu and Kashmir National Conference</td>\n",
       "      <td>J&amp;KNC</td>\n",
       "      <td>Jammu and Kashmir                             ...</td>\n",
       "      <td>Srinagar</td>\n",
       "      <td>...</td>\n",
       "      <td>https://sansad.in/getFile/mpimage/photo/2654.j...</td>\n",
       "      <td>None</td>\n",
       "      <td>21/10/1937</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Professional Graduate</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3191</th>\n",
       "      <td>524</td>\n",
       "      <td>3953</td>\n",
       "      <td>Shri</td>\n",
       "      <td>Sita Ram</td>\n",
       "      <td>Yadav</td>\n",
       "      <td>Male</td>\n",
       "      <td>Rashtriya Janata Dal</td>\n",
       "      <td>RJD</td>\n",
       "      <td>Bihar                                         ...</td>\n",
       "      <td>Sitamarhi                                     ...</td>\n",
       "      <td>...</td>\n",
       "      <td>https://sansad.in/getFile/mpimage/photo/3953.j...</td>\n",
       "      <td>None</td>\n",
       "      <td>05/01/1946</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Graduate</td>\n",
       "      <td>N</td>\n",
       "      <td>Advocate, Social Worker                       ...</td>\n",
       "      <td></td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3192</th>\n",
       "      <td>525</td>\n",
       "      <td>3955</td>\n",
       "      <td>Shri</td>\n",
       "      <td>Surendra Prasad</td>\n",
       "      <td>Yadav</td>\n",
       "      <td>Male</td>\n",
       "      <td>Rashtriya Janata Dal</td>\n",
       "      <td>RJD</td>\n",
       "      <td>Bihar                                         ...</td>\n",
       "      <td>Jhanjharpur                                   ...</td>\n",
       "      <td>...</td>\n",
       "      <td>https://sansad.in/getFile/writereaddata/biodat...</td>\n",
       "      <td>https://lssapi.nic.in/MemberProfile/biodata_1_...</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3193</th>\n",
       "      <td>526</td>\n",
       "      <td>4002</td>\n",
       "      <td>Dr.</td>\n",
       "      <td>Surendra Prasad</td>\n",
       "      <td>Yadav</td>\n",
       "      <td>Male</td>\n",
       "      <td>Rashtriya Janata Dal</td>\n",
       "      <td>RJD</td>\n",
       "      <td>Bihar                                         ...</td>\n",
       "      <td>Jahanabad                                     ...</td>\n",
       "      <td>...</td>\n",
       "      <td>https://sansad.in/getFile/writereaddata/biodat...</td>\n",
       "      <td>https://lssapi.nic.in/MemberProfile/biodata_1_...</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3194</th>\n",
       "      <td>527</td>\n",
       "      <td>533</td>\n",
       "      <td>Shri</td>\n",
       "      <td>Kinjarapu</td>\n",
       "      <td>Yerrannaidu</td>\n",
       "      <td>Male</td>\n",
       "      <td>Telugu Desam Party</td>\n",
       "      <td>TDP</td>\n",
       "      <td>Andhra Pradesh                                ...</td>\n",
       "      <td>Srikakulam                                    ...</td>\n",
       "      <td>...</td>\n",
       "      <td>https://sansad.in/getFile/mpimage/photo/533.jp...</td>\n",
       "      <td>None</td>\n",
       "      <td>23/02/1957</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Graduate</td>\n",
       "      <td>N</td>\n",
       "      <td>Advocate                                      ...</td>\n",
       "      <td></td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3195</th>\n",
       "      <td>528</td>\n",
       "      <td>534</td>\n",
       "      <td>Shri</td>\n",
       "      <td>Mahboob</td>\n",
       "      <td>Zahedi</td>\n",
       "      <td>Male</td>\n",
       "      <td>Communist Party of India (Marxist)</td>\n",
       "      <td>CPI(M)</td>\n",
       "      <td>West Bengal                                   ...</td>\n",
       "      <td>Katwa                                         ...</td>\n",
       "      <td>...</td>\n",
       "      <td>https://sansad.in/getFile/mpimage/photo/534.jp...</td>\n",
       "      <td>None</td>\n",
       "      <td>02/02/1929</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Matric</td>\n",
       "      <td>N</td>\n",
       "      <td>Agriculturist                                 ...</td>\n",
       "      <td></td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4448 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      index  mpsno initial        firstName     lastName gender  \\\n",
       "0         0    344    Shri                       A. Raja   Male   \n",
       "0         0    344    Shri                       A. Raja   Male   \n",
       "1         1   5175    Shri   Narayana Swamy      Abbaiah   Male   \n",
       "1         1   5175    Shri   Narayana Swamy      Abbaiah   Male   \n",
       "2         2   2654     Dr.           Farooq     Abdullah   Male   \n",
       "...     ...    ...     ...              ...          ...    ...   \n",
       "3191    524   3953    Shri         Sita Ram        Yadav   Male   \n",
       "3192    525   3955    Shri  Surendra Prasad        Yadav   Male   \n",
       "3193    526   4002     Dr.  Surendra Prasad        Yadav   Male   \n",
       "3194    527    533    Shri        Kinjarapu  Yerrannaidu   Male   \n",
       "3195    528    534    Shri          Mahboob       Zahedi   Male   \n",
       "\n",
       "                                 partyFname partySname  \\\n",
       "0                 Dravida Munnetra Kazhagam        DMK   \n",
       "0                 Dravida Munnetra Kazhagam        DMK   \n",
       "1                    Bharatiya Janata Party        BJP   \n",
       "1                    Bharatiya Janata Party        BJP   \n",
       "2     Jammu and Kashmir National Conference      J&KNC   \n",
       "...                                     ...        ...   \n",
       "3191                   Rashtriya Janata Dal        RJD   \n",
       "3192                   Rashtriya Janata Dal        RJD   \n",
       "3193                   Rashtriya Janata Dal        RJD   \n",
       "3194                     Telugu Desam Party        TDP   \n",
       "3195     Communist Party of India (Marxist)     CPI(M)   \n",
       "\n",
       "                                              stateName  \\\n",
       "0     Tamil Nadu                                    ...   \n",
       "0     Tamil Nadu                                    ...   \n",
       "1     Karnataka                                     ...   \n",
       "1     Karnataka                                     ...   \n",
       "2     Jammu and Kashmir                             ...   \n",
       "...                                                 ...   \n",
       "3191  Bihar                                         ...   \n",
       "3192  Bihar                                         ...   \n",
       "3193  Bihar                                         ...   \n",
       "3194  Andhra Pradesh                                ...   \n",
       "3195  West Bengal                                   ...   \n",
       "\n",
       "                                              constName  ...  \\\n",
       "0                                              Nilgiris  ...   \n",
       "0                                              Nilgiris  ...   \n",
       "1                                           Chitradurga  ...   \n",
       "1                                           Chitradurga  ...   \n",
       "2                                              Srinagar  ...   \n",
       "...                                                 ...  ...   \n",
       "3191  Sitamarhi                                     ...  ...   \n",
       "3192  Jhanjharpur                                   ...  ...   \n",
       "3193  Jahanabad                                     ...  ...   \n",
       "3194  Srikakulam                                    ...  ...   \n",
       "3195  Katwa                                         ...  ...   \n",
       "\n",
       "                                               imageUrl  \\\n",
       "0     https://sansad.in/getFile/mpimage/photo/344.jp...   \n",
       "0     https://sansad.in/getFile/mpimage/photo/344.jp...   \n",
       "1     https://sansad.in/getFile/mpimage/photo/5175.j...   \n",
       "1     https://sansad.in/getFile/mpimage/photo/5175.j...   \n",
       "2     https://sansad.in/getFile/mpimage/photo/2654.j...   \n",
       "...                                                 ...   \n",
       "3191  https://sansad.in/getFile/mpimage/photo/3953.j...   \n",
       "3192  https://sansad.in/getFile/writereaddata/biodat...   \n",
       "3193  https://sansad.in/getFile/writereaddata/biodat...   \n",
       "3194  https://sansad.in/getFile/mpimage/photo/533.jp...   \n",
       "3195  https://sansad.in/getFile/mpimage/photo/534.jp...   \n",
       "\n",
       "                                             profileUrl         dob  \\\n",
       "0                                                  None  10/05/1963   \n",
       "0                                                  None  10/05/1963   \n",
       "1                                                  None  16/05/1957   \n",
       "1                                                  None  16/05/1957   \n",
       "2                                                  None  21/10/1937   \n",
       "...                                                 ...         ...   \n",
       "3191                                               None  05/01/1946   \n",
       "3192  https://lssapi.nic.in/MemberProfile/biodata_1_...        None   \n",
       "3193  https://lssapi.nic.in/MemberProfile/biodata_1_...        None   \n",
       "3194                                               None  23/02/1957   \n",
       "3195                                               None  02/02/1929   \n",
       "\n",
       "     numberOfSons numberOfDaughters                             qualification  \\\n",
       "0             0.0               1.0  Post Graduate                              \n",
       "0             0.0               1.0  Post Graduate                              \n",
       "1             0.0               3.0                                  Graduate   \n",
       "1             0.0               3.0                                  Graduate   \n",
       "2             1.0               3.0                     Professional Graduate   \n",
       "...           ...               ...                                       ...   \n",
       "3191          3.0               2.0  Graduate                                   \n",
       "3192          NaN               NaN                                      None   \n",
       "3193          NaN               NaN                                      None   \n",
       "3194          1.0               1.0  Graduate                                   \n",
       "3195          2.0               0.0  Matric                                     \n",
       "\n",
       "     freedom                                        profession2 categoryCode  \\\n",
       "0          N                                                ...        (SC)    \n",
       "0          N                                                ...        (SC)    \n",
       "1          N  Social Worker                                 ...        (SC)    \n",
       "1          N  Social Worker                                 ...        (SC)    \n",
       "2          N                                                ...                \n",
       "...      ...                                                ...          ...   \n",
       "3191       N  Advocate, Social Worker                       ...                \n",
       "3192    None                                                ...         None   \n",
       "3193    None                                                ...         None   \n",
       "3194       N  Advocate                                      ...                \n",
       "3195       N  Agriculturist                                 ...                \n",
       "\n",
       "      ls  \n",
       "0     17  \n",
       "0     17  \n",
       "1     17  \n",
       "1     17  \n",
       "2     17  \n",
       "...   ..  \n",
       "3191  12  \n",
       "3192  12  \n",
       "3193  12  \n",
       "3194  12  \n",
       "3195  12  \n",
       "\n",
       "[4448 rows x 34 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pols = df_pols.explode('email')\n",
    "df_pols['email'] = df_pols['email'].str.split(r'</br>')\n",
    "df_pols = df_pols.explode('email')\n",
    "df_pols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cae4486b-a91e-4926-ab7d-22e6de20cce9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2023"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pols['email'] = df_pols['email'].str.strip()\n",
    "df_pols['email_fix'] = df_pols['email'].str.replace('\\[at\\]', '@', regex=True).str.replace('\\[dot\\]', '.', regex=True)\n",
    "df_pols['email_fix'] = df_pols['email_fix'].str.extract(r'([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,})')\n",
    "df_pols['email_fix'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1bdc1e3b-e81c-4c36-bdc0-8b6317c19263",
   "metadata": {},
   "source": [
    "### Remove rows with Nan emails (assumptions ~ missing at random or don't use emails)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3af1a949-0898-4b20-8a7a-6408b9a8a508",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3554, 35)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pols = df_pols[~df_pols['email_fix'].isna()]\n",
    "df_pols.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9caaa49b-b09c-4548-982f-8ee23b3d67d5",
   "metadata": {},
   "source": [
    "### Join HIBP to pol data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "151e4e8f-3325-4a28-95ec-c39cfdf77992",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5052, 37)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_joined = df_pols.merge(df_hibp, left_on='email_fix', right_on = 'email', how='left')\n",
    "df_joined.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c46856c-82e3-42d2-98f4-4fca8061edfb",
   "metadata": {},
   "source": [
    "### Join HIBP metadata on breaches to above"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "56d0dd3f-257f-4e09-906e-e7bb3d788b36",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5052, 52)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "breaches = pd.read_json('breaches.json')\n",
    "fin_df = pd.merge(df_joined, breaches, on='Name', how = 'left')\n",
    "fin_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "184bede8-0c25-4d0e-bf26-b35a7d7e9bf3",
   "metadata": {},
   "source": [
    "### Distribution of the Number of Breaches by Email ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f4b82123-aa8c-4084-8047-c065b079df77",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    2023.000000\n",
       "mean        0.639150\n",
       "std         1.836354\n",
       "min         0.000000\n",
       "25%         0.000000\n",
       "50%         0.000000\n",
       "75%         0.000000\n",
       "max        20.000000\n",
       "Name: Name, dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_df.groupby('email_fix')['Name'].nunique().describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69ae5382-4da2-4633-a390-e019f2bfd9d1",
   "metadata": {},
   "source": [
    "### Distribution of Number of Breaches by Politician"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b185e852-a44a-4d47-a4a5-c7d811dae9ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2315,)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Total Number of Politicians\n",
    "fin_df.groupby(['ls', 'index']).size().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "1d9ba12a-7101-4956-9b80-2a1c3363e519",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    2315.000000\n",
       "mean        0.957235\n",
       "std         2.236529\n",
       "min         0.000000\n",
       "25%         0.000000\n",
       "50%         0.000000\n",
       "75%         1.000000\n",
       "max        20.000000\n",
       "Name: Name, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_df.groupby(['ls', 'index'])['Name'].nunique().reset_index()['Name'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "85911bd2-60a6-4fec-a8a3-32b7e7547e05",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.3105831533477322"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(fin_df.groupby(['ls', 'index'])['Name'].nunique().reset_index()['Name'] > 0).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9339a8e-02cf-47fe-9065-4ce2260e4e18",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### Serious Dataclasses Breached \n",
    "\n",
    "Either data that will help you hack your other accounts, e.g., Mother's Maiden Name, or sensitive personal information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "e7e75c0e-616f-4696-9261-066de9c8f847",
   "metadata": {},
   "outputs": [],
   "source": [
    "serious_dataclasses = [\"Audio recordings\",\n",
    "\"Auth tokens\",\n",
    "\"Bank account numbers\",\n",
    "\"Biometric data\",\n",
    "\"Browsing histories\",\n",
    "\"Chat logs\",\n",
    "\"Credit card CVV\",\n",
    "\"Credit cards\",\n",
    "\"Credit status information\",\n",
    "\"Drinking habits\",\n",
    "\"Driver's licenses\",\n",
    "\"Drug habits\",\n",
    "\"Email messages\",\n",
    "\"Encrypted keys\",\n",
    "\"Government issued IDs\",\n",
    "\"Health insurance information\",\n",
    "\"Historical passwords\",\n",
    "\"HIV statuses\",\n",
    "\"Login histories\",\n",
    "\"MAC addresses\",\n",
    "\"Mothers maiden names\",\n",
    "\"Nationalities\",\n",
    "\"Partial credit card data\",\n",
    "\"Partial dates of birth\",\n",
    "\"Passport numbers\",\n",
    "\"Password hints\",\n",
    "\"Passwords\",\n",
    "\"Personal health data\",\n",
    "\"Photos\",\n",
    "\"PINs\",\n",
    "\"Places of birth\",\n",
    "\"Private messages\",\n",
    "\"Security questions and answers\",\n",
    "\"Sexual fetishes\",\n",
    "\"Sexual orientations\",\n",
    "\"SMS messages\",\n",
    "\"Social security numbers\",\n",
    "\"Taxation records\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "63da47e8-ed5d-4e96-9ba6-0c941eb23d1e",
   "metadata": {},
   "outputs": [],
   "source": [
    "fin_df['serious'] = fin_df['DataClasses']\\\n",
    "                  .apply(lambda x: \n",
    "                        any(string in x for string in serious_dataclasses) \n",
    "                         if isinstance(x, list) else False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "02323ee7-9842-4896-b1ab-ab71750fc5ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "fin_df['serious_n'] = fin_df['serious']*(~fin_df['Name'].isna())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "d52d2151-9816-453d-9c65-f16a21c7853d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    2315.000000\n",
       "mean        0.514903\n",
       "std         1.379288\n",
       "min         0.000000\n",
       "25%         0.000000\n",
       "50%         0.000000\n",
       "75%         0.000000\n",
       "max        14.000000\n",
       "Name: serious_n, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fin_df.groupby(['ls', 'index'])['serious_n'].sum().reset_index()['serious_n'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "7af7cf26-7fe6-46fd-a103-ec1eacbee45e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.2203023758099352"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(fin_df.groupby(['ls', 'index'])['serious_n'].sum().reset_index()['serious_n'] > 0).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f028d00c-f584-464d-860a-35be4fed6c1a",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}