posco.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# POSCO\n",
    "\n",
    "Cases in India related to the POSCO act. A exploratory data analysis of child specific legal activity tracker."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[0m\u001b[01;32mmh_pocso_processed_01.csv\u001b[0m*  \u001b[34;42mNashik\u001b[0m/  \u001b[01;32mposco.ipynb\u001b[0m*  \u001b[34;42mPune\u001b[0m/  \u001b[34;42mThane\u001b[0m/\r\n"
     ]
    }
   ],
   "source": [
    "%ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib\n",
    "import seaborn as sb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\n",
    "    'mh_pocso_processed_01.csv',\n",
    "    encoding='utf-8',\n",
    "    parse_dates=['date_of_decision', 'date_last_list', 'date_of_filing']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Columns\n",
    "\n",
    "- **cino:** \n",
    "- **disp_nature:** nature of disposal\n",
    "- **fil_year:** the year in which the case was filed\n",
    "- **jcode:**\n",
    "- **date_of_decision:**\n",
    "- **reg_year:** the year in which the case was registered\n",
    "- **transfer_est_name:**\n",
    "- **lower_court_dec_dt:**\n",
    "- **fir_year:** the year in which the FIR was registered\n",
    "- **pet_adv:**\n",
    "- **type_name:** the type of case (categorical)\n",
    "- **res_name:**\n",
    "- **petNameAdd:**\n",
    "- **subordinateCourtInfoStr:**\n",
    "- **lower_court_code:**\n",
    "- **res_adv:**\n",
    "- **petparty_name:**\n",
    "- **purpose_next:**\n",
    "- **est_code:**\n",
    "- **state_name:**\n",
    "- **date_last_list:** the date on which the last hearing was made for this case\n",
    "- **court_no:**\n",
    "- **reg_no:**\n",
    "- **dt_regis:**\n",
    "- **date_of_filing:** the full date on which the case was filed\n",
    "- **purpose_name:** \n",
    "- **fir_no:** the FIR number\n",
    "- **police_st_code:**\n",
    "- **district_code:**\n",
    "- **courtno:**\n",
    "- **case_no:**\n",
    "- **desgname:**\n",
    "- **pet_name:** Petitioner's name\n",
    "- **resparty_name:**\n",
    "- **fir_details:** Usually containing the name of the person who registered the FIR\n",
    "- **court_name:**\n",
    "- **district_name:**\n",
    "- **police_station:**\n",
    "- **police_station_id:**\n",
    "- **lon:** Longitude of the police station location\n",
    "- **lat:** Latitude of the police station location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4353, 42)"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['cino', 'disp_nature', 'fil_year', 'jcode', 'date_of_decision',\n",
       "       'reg_year', 'transfer_est_name', 'lower_court_dec_dt', 'fir_year',\n",
       "       'pet_adv', 'type_name', 'res_name', 'petNameAdd',\n",
       "       'subordinateCourtInfoStr', 'lower_court_code', 'res_adv',\n",
       "       'petparty_name', 'purpose_next', 'est_code', 'state_name',\n",
       "       'date_last_list', 'court_no', 'reg_no', 'dt_regis', 'date_of_filing',\n",
       "       'purpose_name', 'fir_no', 'police_st_code', 'district_code', 'courtno',\n",
       "       'case_no', 'desgname', 'pet_name', 'resparty_name', 'fir_details',\n",
       "       'court_name', 'district_name', 'police_station', 'police_station_id',\n",
       "       'lon', 'lat', 'geojson'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cino                               object\n",
       "disp_nature                         int64\n",
       "fil_year                            int64\n",
       "jcode                             float64\n",
       "date_of_decision           datetime64[ns]\n",
       "reg_year                            int64\n",
       "transfer_est_name                  object\n",
       "lower_court_dec_dt                  int64\n",
       "fir_year                            int64\n",
       "pet_adv                            object\n",
       "type_name                          object\n",
       "res_name                           object\n",
       "petNameAdd                         object\n",
       "subordinateCourtInfoStr            object\n",
       "lower_court_code                    int64\n",
       "res_adv                            object\n",
       "petparty_name                      object\n",
       "purpose_next                        int64\n",
       "est_code                           object\n",
       "state_name                         object\n",
       "date_last_list             datetime64[ns]\n",
       "court_no                            int64\n",
       "reg_no                              int64\n",
       "dt_regis                           object\n",
       "date_of_filing             datetime64[ns]\n",
       "purpose_name                       object\n",
       "fir_no                            float64\n",
       "police_st_code                      int64\n",
       "district_code                       int64\n",
       "courtno                           float64\n",
       "case_no                             int64\n",
       "desgname                           object\n",
       "pet_name                           object\n",
       "resparty_name                      object\n",
       "fir_details                        object\n",
       "court_name                         object\n",
       "district_name                      object\n",
       "police_station                     object\n",
       "police_station_id                   int64\n",
       "lon                               float64\n",
       "lat                               float64\n",
       "geojson                            object\n",
       "dtype: object"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.purpose"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "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>purpose_name</th>\n",
       "      <th>purpose_next</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Unready Board</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Appearance</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Evidence Part Heard</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Filing of Say on Exh___Unready</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Defence Evidence</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Compliance</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Compliance</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Unready Board</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Order</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Evidence Part Heard</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Hearing</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Order</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4323</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4324</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4325</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4326</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4327</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4328</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4329</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4330</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4331</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4332</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4333</th>\n",
       "      <td>Order on Exh</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4334</th>\n",
       "      <td>Filing of Say on Exh___Unready</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4335</th>\n",
       "      <td>Appearance</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4336</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4337</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4338</th>\n",
       "      <td>Appearance</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4339</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4340</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4341</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4342</th>\n",
       "      <td>Filing of Say on Exh___Ready</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4343</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4344</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4345</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4346</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4347</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4348</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4349</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4350</th>\n",
       "      <td>Appearance</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4351</th>\n",
       "      <td>Charge</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4352</th>\n",
       "      <td>Filing of Say on Exh___Unready</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4353 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        purpose_name  purpose_next\n",
       "0                             Charge             8\n",
       "1                      Unready Board            78\n",
       "2                         Appearance            67\n",
       "3                             Charge             8\n",
       "4                Evidence Part Heard             3\n",
       "5                             Charge             8\n",
       "6                             Charge             8\n",
       "7                             Charge             8\n",
       "8     Filing of Say on Exh___Unready            27\n",
       "9                   Defence Evidence            30\n",
       "10                        Compliance            41\n",
       "11                            Charge             8\n",
       "12                            Charge             8\n",
       "13                            Charge             8\n",
       "14                            Charge             8\n",
       "15                            Charge             8\n",
       "16                            Charge             8\n",
       "17                            Charge             8\n",
       "18                            Charge             8\n",
       "19                            Charge             8\n",
       "20                        Compliance            41\n",
       "21                     Unready Board            78\n",
       "22                            Charge             8\n",
       "23                            Charge             8\n",
       "24                             Order            22\n",
       "25                            Charge             8\n",
       "26               Evidence Part Heard             3\n",
       "27                           Hearing             4\n",
       "28                            Charge             8\n",
       "29                             Order            22\n",
       "...                              ...           ...\n",
       "4323                          Charge             8\n",
       "4324                          Charge             8\n",
       "4325                          Charge             8\n",
       "4326                          Charge             8\n",
       "4327                          Charge             8\n",
       "4328                          Charge             8\n",
       "4329                          Charge             8\n",
       "4330                          Charge             8\n",
       "4331                          Charge             8\n",
       "4332                          Charge             8\n",
       "4333                    Order on Exh            23\n",
       "4334  Filing of Say on Exh___Unready            27\n",
       "4335                      Appearance            67\n",
       "4336                          Charge             8\n",
       "4337                          Charge             8\n",
       "4338                      Appearance            67\n",
       "4339                          Charge             8\n",
       "4340                          Charge             8\n",
       "4341                          Charge             8\n",
       "4342    Filing of Say on Exh___Ready            26\n",
       "4343                          Charge             8\n",
       "4344                          Charge             8\n",
       "4345                          Charge             8\n",
       "4346                          Charge             8\n",
       "4347                          Charge             8\n",
       "4348                          Charge             8\n",
       "4349                          Charge             8\n",
       "4350                      Appearance            67\n",
       "4351                          Charge             8\n",
       "4352  Filing of Say on Exh___Unready            27\n",
       "\n",
       "[4353 rows x 2 columns]"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['purpose_name', 'purpose_next']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Court establishment-wise cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DISTRICT AND SESSIONS COURT PUNE MAHARASHTRA       1744\n",
       "DISTRICT AND SESSIONS COURT, THANE                 1257\n",
       "ADDL.DIST.  SESSIONS COURT KALYAN                   658\n",
       "ADDITIONAL DISTRICT AND SESSIONS COURT BARAMATI     341\n",
       "ADDL. DISTRICT COURT, KHED                          190\n",
       "DISTRICT AND SESSIONS COURT NASHIK.                 158\n",
       "J.M.F.C.COURT PUNE MAHARASHTRA                        2\n",
       "CIVIL COURT VADAGAON MAVAL                            1\n",
       "CRIMINAL COURTS BARAMATI DIST PUNE                    1\n",
       "J.M.F.C. CANTONMENT COURT PUNE M.S.                   1\n",
       "Name: court_name, dtype: int64"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.court_name.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4353, 41)"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## District-wise cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Pune      2280\n",
       "Thane     1915\n",
       "Nashik     158\n",
       "Name: district_name, dtype: int64"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.district_name.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Count of types of cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Spl.Case Child Prot.    3360\n",
       "Atro.Spl.Case            645\n",
       "Sessions Case            184\n",
       "Spl.Case                  59\n",
       "Cri.Bail Appln.           54\n",
       "Spl.M.A. Child Prot.      35\n",
       "Spl.Case ACB               4\n",
       "R.C.C.                     4\n",
       "Cri.M.A.                   3\n",
       "Cri.Appeal                 3\n",
       "S.C.C.                     1\n",
       "PWDVA Appeal               1\n",
       "Name: type_name, dtype: int64"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.type_name.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "type_name             disp_nature\n",
       "Spl.Case Child Prot.  0              3360\n",
       "Atro.Spl.Case         0               645\n",
       "Sessions Case         0               184\n",
       "Spl.Case              0                59\n",
       "Cri.Bail Appln.       0                54\n",
       "Spl.M.A. Child Prot.  0                35\n",
       "Spl.Case ACB          0                 4\n",
       "R.C.C.                0                 4\n",
       "Cri.M.A.              0                 3\n",
       "Cri.Appeal            0                 3\n",
       "S.C.C.                0                 1\n",
       "PWDVA Appeal          0                 1\n",
       "Name: disp_nature, dtype: int64"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('type_name')['disp_nature'].value_counts().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of police stations across all cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Yerwada Police Station     111\n",
       "Kondwa Police Station      100\n",
       "Hadapsar Police Station     95\n",
       "Mumbra Police Station       80\n",
       "Kashmira Police Station     67\n",
       "Name: police_station, dtype: int64"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.police_station.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['cino', 'disp_nature', 'fil_year', 'jcode', 'date_of_decision',\n",
       "       'reg_year', 'transfer_est_name', 'lower_court_dec_dt', 'fir_year',\n",
       "       'pet_adv', 'type_name', 'res_name', 'petNameAdd',\n",
       "       'subordinateCourtInfoStr', 'lower_court_code', 'res_adv',\n",
       "       'petparty_name', 'purpose_next', 'est_code', 'state_name',\n",
       "       'date_last_list', 'court_no', 'reg_no', 'dt_regis', 'date_of_filing',\n",
       "       'purpose_name', 'fir_no', 'police_st_code', 'district_code', 'courtno',\n",
       "       'case_no', 'desgname', 'pet_name', 'resparty_name', 'fir_details',\n",
       "       'court_name', 'district_name', 'police_station', 'police_station_id',\n",
       "       'lon', 'lat'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hearings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [],
   "source": [
    "hearings_df = pd.read_csv(\n",
    "    'mh_pocso_case_history_02.csv',\n",
    "    encoding='utf-8'\n",
    ")\n",
    "hearings_df['Hearing Date'] = pd.to_datetime(hearings_df['Hearing Date'], dayfirst=True)\n",
    "hearings_df['Business On Date'] = pd.to_datetime(hearings_df['Business On Date'], dayfirst=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [],
   "source": [
    "hearings_df['diff_days'] = hearings_df['Hearing Date'] - hearings_df['Business On Date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(94828, 6)\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>Judge</th>\n",
       "      <th>Business On Date</th>\n",
       "      <th>Hearing Date</th>\n",
       "      <th>Purpose of hearing</th>\n",
       "      <th>cino</th>\n",
       "      <th>diff_days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE</td>\n",
       "      <td>2017-01-13</td>\n",
       "      <td>2017-02-03</td>\n",
       "      <td>Unready Board</td>\n",
       "      <td>MHNS010000282017</td>\n",
       "      <td>21 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE</td>\n",
       "      <td>2017-02-03</td>\n",
       "      <td>2017-02-22</td>\n",
       "      <td>Charge</td>\n",
       "      <td>MHNS010000282017</td>\n",
       "      <td>19 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE</td>\n",
       "      <td>2017-02-22</td>\n",
       "      <td>2017-03-08</td>\n",
       "      <td>Charge</td>\n",
       "      <td>MHNS010000282017</td>\n",
       "      <td>14 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE</td>\n",
       "      <td>2017-03-08</td>\n",
       "      <td>2017-04-07</td>\n",
       "      <td>Charge</td>\n",
       "      <td>MHNS010000282017</td>\n",
       "      <td>30 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DISTRICT JUDGE-9 AND ADDL.SESSIONS JUDGE</td>\n",
       "      <td>2017-04-07</td>\n",
       "      <td>2017-05-06</td>\n",
       "      <td>Charge</td>\n",
       "      <td>MHNS010000282017</td>\n",
       "      <td>29 days</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       Judge Business On Date Hearing Date  \\\n",
       "0  DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE       2017-01-13   2017-02-03   \n",
       "1  DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE       2017-02-03   2017-02-22   \n",
       "2  DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE       2017-02-22   2017-03-08   \n",
       "3  DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE       2017-03-08   2017-04-07   \n",
       "4   DISTRICT JUDGE-9 AND ADDL.SESSIONS JUDGE       2017-04-07   2017-05-06   \n",
       "\n",
       "  Purpose of hearing              cino diff_days  \n",
       "0      Unready Board  MHNS010000282017   21 days  \n",
       "1             Charge  MHNS010000282017   19 days  \n",
       "2             Charge  MHNS010000282017   14 days  \n",
       "3             Charge  MHNS010000282017   30 days  \n",
       "4             Charge  MHNS010000282017   29 days  "
      ]
     },
     "execution_count": 230,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(hearings_df.shape)\n",
    "hearings_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Types of hearings and their count across all cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Charge                           33945\n",
       "Hearing                          13406\n",
       "Appearance                        9038\n",
       "Evidence Part Heard               8262\n",
       "Evidence                          5533\n",
       "Production of Accused             4869\n",
       "N.B.W._Unready                    3070\n",
       "N.B.W._Ready                      2261\n",
       "Say / Hearing on Exh____Ready     1707\n",
       "Argument on Exh.____Unready       1636\n",
       "Name: Purpose of hearing, dtype: int64"
      ]
     },
     "execution_count": 231,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hearings_df['Purpose of hearing'].value_counts().head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of days between the first and the last hearing per case"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cino\n",
       "MHPU010053482013   2155 days\n",
       "MHPU010049732013   2120 days\n",
       "MHTH010046122013   2094 days\n",
       "MHPU010064562013   2067 days\n",
       "MHPU010065112013   2058 days\n",
       "Name: diff_days, dtype: timedelta64[ns]"
      ]
     },
     "execution_count": 232,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hearings_df.groupby('cino')['diff_days'].sum().sort_values(ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of hearings per case"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MHTH010073092013    141\n",
       "MHPU010119862013    139\n",
       "MHTH010004752014    133\n",
       "MHTH010032952014    128\n",
       "MHTH010036282014    118\n",
       "Name: cino, dtype: int64"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hearings_df.cino.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged = pd.merge(df, hearings_df, on=['cino'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "district_name  disp_nature\n",
       "Nashik         0               2011\n",
       "Pune           0              51636\n",
       "Thane          0              41267\n",
       "Name: disp_nature, dtype: int64"
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.groupby('district_name')['disp_nature'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "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>Judge</th>\n",
       "      <th>Business On Date</th>\n",
       "      <th>Hearing Date</th>\n",
       "      <th>Purpose of hearing</th>\n",
       "      <th>cino</th>\n",
       "      <th>diff_days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE</td>\n",
       "      <td>2017-01-13</td>\n",
       "      <td>2017-02-03</td>\n",
       "      <td>Unready Board</td>\n",
       "      <td>MHNS010000282017</td>\n",
       "      <td>21 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE</td>\n",
       "      <td>2017-02-03</td>\n",
       "      <td>2017-02-22</td>\n",
       "      <td>Charge</td>\n",
       "      <td>MHNS010000282017</td>\n",
       "      <td>19 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE</td>\n",
       "      <td>2017-02-22</td>\n",
       "      <td>2017-03-08</td>\n",
       "      <td>Charge</td>\n",
       "      <td>MHNS010000282017</td>\n",
       "      <td>14 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE</td>\n",
       "      <td>2017-03-08</td>\n",
       "      <td>2017-04-07</td>\n",
       "      <td>Charge</td>\n",
       "      <td>MHNS010000282017</td>\n",
       "      <td>30 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DISTRICT JUDGE-9 AND ADDL.SESSIONS JUDGE</td>\n",
       "      <td>2017-04-07</td>\n",
       "      <td>2017-05-06</td>\n",
       "      <td>Charge</td>\n",
       "      <td>MHNS010000282017</td>\n",
       "      <td>29 days</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       Judge Business On Date Hearing Date  \\\n",
       "0  DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE       2017-01-13   2017-02-03   \n",
       "1  DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE       2017-02-03   2017-02-22   \n",
       "2  DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE       2017-02-22   2017-03-08   \n",
       "3  DISTRICT JUDGE-1 AND ADDL. SESSIONS JUDGE       2017-03-08   2017-04-07   \n",
       "4   DISTRICT JUDGE-9 AND ADDL.SESSIONS JUDGE       2017-04-07   2017-05-06   \n",
       "\n",
       "  Purpose of hearing              cino diff_days  \n",
       "0      Unready Board  MHNS010000282017   21 days  \n",
       "1             Charge  MHNS010000282017   19 days  \n",
       "2             Charge  MHNS010000282017   14 days  \n",
       "3             Charge  MHNS010000282017   30 days  \n",
       "4             Charge  MHNS010000282017   29 days  "
      ]
     },
     "execution_count": 234,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hearings_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "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>cino</th>\n",
       "      <th>purpose_name</th>\n",
       "      <th>Hearing Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>61722</th>\n",
       "      <td>MHTH060035902018</td>\n",
       "      <td>Charge</td>\n",
       "      <td>2019-12-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84647</th>\n",
       "      <td>MHTH010059452018</td>\n",
       "      <td>List of Witness</td>\n",
       "      <td>2019-12-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53801</th>\n",
       "      <td>MHTH060000532016</td>\n",
       "      <td>Charge</td>\n",
       "      <td>2019-12-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66468</th>\n",
       "      <td>MHTH010009392018</td>\n",
       "      <td>Charge</td>\n",
       "      <td>2019-12-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70499</th>\n",
       "      <td>MHTH010020602014</td>\n",
       "      <td>N.B.W._Unready</td>\n",
       "      <td>2019-12-04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   cino     purpose_name Hearing Date\n",
       "61722  MHTH060035902018           Charge   2019-12-07\n",
       "84647  MHTH010059452018  List of Witness   2019-12-04\n",
       "53801  MHTH060000532016           Charge   2019-12-04\n",
       "66468  MHTH010009392018           Charge   2019-12-04\n",
       "70499  MHTH010020602014   N.B.W._Unready   2019-12-04"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged[['cino', 'purpose_name', 'Hearing Date']].sort_values(by='Hearing Date', ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Judge-wise days spent based on hearing type (`purpose_name`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Judge                                          purpose_name                  \n",
       "3RD JT.C.J.J.D.J.M.F.C. BARAMATI.              N.B.W._Unready                     121 days\n",
       "4th CJJD AND JMFC VADGAON MAVAL                Charge                             106 days\n",
       "4th JT. C.J.J.D. AND J.M.F.C. BARAMATI.        N.B.W._Unready                     774 days\n",
       "6th C.J.J.D.  J.M.F.C. BARAMATI                N.B.W._Unready                     259 days\n",
       "AD-HOC D.J.-3 AND ASSTT.SESSIONS JUDGE         Charge                             -95 days\n",
       "                                               Hearing                             27 days\n",
       "AD-HOC DISTRICT JUDGE-1 ADDL.SESS.JUDGE        Charge                             -83 days\n",
       "                                               Hearing                            -82 days\n",
       "ADDL. DISTRICT JUDGE, KHED.                    Arguments                         1822 days\n",
       "                                               Charge                           35362 days\n",
       "                                               Evidence Part Heard              19361 days\n",
       "                                               Hearing                          16242 days\n",
       "                                               Judgment                          2472 days\n",
       "                                               N.B.W._Ready                      1260 days\n",
       "                                               Statement U/sec.313 Cr.P.C.       1330 days\n",
       "                                               Unready Board                        5 days\n",
       "ADHOC D.J. 1 AND ADJ BARAMATI DISTRICT PUNE.   Appearance                         819 days\n",
       "                                               Argument on Exh.____Unready        582 days\n",
       "                                               Arguments                          631 days\n",
       "                                               Awaiting Summons                    22 days\n",
       "                                               B.W._Ready                         204 days\n",
       "                                               B.W._Unready                       230 days\n",
       "                                               Charge                            6957 days\n",
       "                                               Evidence                            88 days\n",
       "                                               Evidence Part Heard               1327 days\n",
       "                                               Formal Hearing                    -176 days\n",
       "                                               Hearing                           8988 days\n",
       "                                               N.B.W._Ready                       925 days\n",
       "                                               N.B.W._Unready                    2087 days\n",
       "                                               Notice_Unready                    1013 days\n",
       "                                                                                   ...    \n",
       "PRINCIPAL DISTRICT  AND SESSIONS JUDGE PUNE    Hearing                           6695 days\n",
       "                                               N.B.W._Ready                      1776 days\n",
       "                                               N.B.W._Unready                     261 days\n",
       "                                               Statement U/sec.313 Cr.P.C.        409 days\n",
       "PRINCIPAL DISTRICT AND SESSIONS JUDGE, NASHIK  Appearance                         891 days\n",
       "                                               Argument on Exh.____Ready          297 days\n",
       "                                               Argument on Exh.____Unready        149 days\n",
       "                                               Arguments                          347 days\n",
       "                                               Awaiting Summons                   148 days\n",
       "                                               B.W._Ready                          88 days\n",
       "                                               Charge                           14878 days\n",
       "                                               Defence Evidence                    77 days\n",
       "                                               Documents                          156 days\n",
       "                                               Evidence Part Heard               1278 days\n",
       "                                               Filing of Say on Exh___Ready        67 days\n",
       "                                               Filing of Say on Exh___Unready     107 days\n",
       "                                               Hearing                           3259 days\n",
       "                                               N.B.W._Ready                       839 days\n",
       "                                               N.B.W._Unready                      56 days\n",
       "                                               Order                              795 days\n",
       "                                               Order on Exh                       531 days\n",
       "                                               Say / Hearing on Exh____Ready      123 days\n",
       "Principal District and  Sessions Judge Thane   Hearing                            143 days\n",
       "VACANT COURT 31052015                          Arguments                           78 days\n",
       "                                               Evidence                            26 days\n",
       "                                               Evidence Part Heard                 86 days\n",
       "                                               Filing of Say on Exh___Ready       263 days\n",
       "                                               Hearing                            359 days\n",
       "                                               N.B.W._Ready                       544 days\n",
       "                                               N.B.W._Unready                     261 days\n",
       "Name: diff_days, Length: 1061, dtype: timedelta64[ns]"
      ]
     },
     "execution_count": 245,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.groupby(['Judge', 'purpose_name'])['diff_days'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of unique cases handled by judges (max 10 & min 20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Judge\n",
       "District Judge-7 and Additonal Sessions Judge, Thane    959\n",
       "District Judge-8 Addl. Sessions JudgeThane              696\n",
       "District Judge-1  Addl.Sessions JudgeThane              472\n",
       "District Judge - 5  Addl. Sessions Judge Thane          448\n",
       "District Judge -6   Addl. Sessions Judge Thane          426\n",
       "District Judge-9 and Addl. Sessions Judge Thane         387\n",
       "Ad-hoc Dist. Judge-2 and Addl. Sessions Judge Thane     371\n",
       "Ad-hoc Dist.Judge 1  Addl. Sessions JudgeThane          369\n",
       "DISTRICT JUDGE -13  ADDL. SESSIONS JUDGE PUNE           342\n",
       "DISTRICT JUDGE - 1 AND  ADDL. SESSIONS JUDGE PUNE       330\n",
       "Name: cino, dtype: int64"
      ]
     },
     "execution_count": 255,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.groupby('Judge')['cino'].nunique().sort_values(ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Judge\n",
       "ADHOC DISTRICT JUDGE-3 BARAMATI DISTRICT PUNE.          6\n",
       "ADHOC DIST. JUDGE-3 AND  ASST.SESSIONS JUDGE PUNE       6\n",
       "DISTRICT JUDGE-9 AND ASSITT SESSJUDGE                   5\n",
       "District Judge 2 and Addl.Sessions Judge,Kalyan         5\n",
       "DISTRICT JUDGE - 1 ADDL. SESSIONS JUDGE PUNE            4\n",
       "DISTRICT JUDGE-3 AND ADDL.SESS.JUDGE                    3\n",
       "DISTRICT JUDGE-2 AND ADDL.SESSIONS JUDGE                3\n",
       "AD-HOC DISTRICT JUDGE-1 ADDL.SESS.JUDGE                 2\n",
       "DISTRICT JUDGE-24 AND ADDL. SESSIONS JUDGE PUNE         2\n",
       "AD-HOC D.J.-3 AND ASSTT.SESSIONS JUDGE                  2\n",
       "4th CJJD AND JMFC VADGAON MAVAL                         1\n",
       "4th JT. C.J.J.D. AND J.M.F.C. BARAMATI.                 1\n",
       "6th C.J.J.D.  J.M.F.C. BARAMATI                         1\n",
       "Principal District and  Sessions Judge Thane            1\n",
       "Jt.C.J.J.D. AND J.M.F.C. COURT VADGAON MAVAL            1\n",
       "JT. JUDICIAL MAGISTRATE F. C.                           1\n",
       "JOINT CIVIL JUDGE J.D. AND JMFC PUNE.                   1\n",
       "CHIEF JUDICIAL MAGISTRATE PUNE                          1\n",
       "Ad-hoc Dist.Judge 2  and Asst. Sessions Judge, Thane    1\n",
       "3RD JT.C.J.J.D.J.M.F.C. BARAMATI.                       1\n",
       "Name: cino, dtype: int64"
      ]
     },
     "execution_count": 254,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.groupby('Judge')['cino'].nunique().sort_values(ascending=False).tail(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "State of Maharashtra                                                   624\n",
       "State OF Maharashtra                                                    66\n",
       "State Of Maharashtra                                                    54\n",
       "The State of Maharashtra Kondhwa P Stn                                  52\n",
       "The State of Maharashtra Hadapsar P Stn                                 45\n",
       "The State of Maharashtra Yerwada P Stn                                  45\n",
       "Kolshewadi Police Station                                               41\n",
       "Hilline Police Station                                                  35\n",
       "Manpada Police Station                                                  35\n",
       "Ambarnath Police Station                                                34\n",
       "Vitthalvadi Police Station                                              34\n",
       "Central Police Station                                                  33\n",
       "Khadakpada Police Station Kalyan                                        30\n",
       "The State of Maharashtra Wakad P Stn                                    29\n",
       "Badlapur Police Station                                                 27\n",
       "State Of Maharashtra/Khed Police Station                                26\n",
       "State of Maharashtra Yerwada P Stn                                      25\n",
       "State Of Maharashtra/Chakan Police Station                              25\n",
       "State of Maharashtra Kondhwa P Stn                                      25\n",
       "The State of Maharashtra Shirur P Stn                                   24\n",
       "Shivaji Nagar Police Station                                            22\n",
       "The State of Maharashtra Bharati Vidyapith P Stn                        22\n",
       "State of Maharashtra Hadapsar P Stn                                     21\n",
       "The State of Maharashtra Lonikand P Stn                                 21\n",
       "The State of Maharashtra Nigdi P Stn                                    21\n",
       "Ulhasnagar Police Station                                               21\n",
       "State Ambernath Police Station                                          20\n",
       "The State of Maharashtra Pimpri P Stn                                   20\n",
       "The State of Maharashtra MIDC Bhosari P Stn                             19\n",
       "The State of Maharashtra Faraskhana P Stn                               19\n",
       "                                                                      ... \n",
       "The State  Mumbra Plc. Stn. Dist. Thane                                  1\n",
       "The State of Maharashtra Wanwadi P.S.                                    1\n",
       "State of Maharashtra through Sanpada Police Station, Navi Mumbai         1\n",
       "The State Mira Rd. Police Stn. Dist. Thane                               1\n",
       "State Dombivali police station                                           1\n",
       "State of MaharashtraChakan Police Station                                1\n",
       "Sagar Hona Utale                                                         1\n",
       "The State of Maharashtra Khadaki Po Stn                                  1\n",
       "State of Maharashtra etc 1                                               1\n",
       "The State of Maharashtra Turbe MIDC Police Station Dist Thane            1\n",
       "Mukim Bhuresab Shaikh                                                    1\n",
       "The State of Maharashtra Wadak P Stn                                     1\n",
       "Kasara Police Station                                                    1\n",
       "State of Maharashtra Sangavi P Stn                                       1\n",
       "Najish Abugalib Shaikh                                                   1\n",
       "The State of Maharashtra Velha  P Stn.                                   1\n",
       "The State of Maharashtra Sangvi  P.Stn.                                  1\n",
       "State  of Maharashtra                                                    1\n",
       "The State of Maharashtra Sinhagad Road P.Stn.                            1\n",
       "The State of Maharashtra Wanawadi PStn                                   1\n",
       "The State of MaharashtraThe State of Maharashtra MIDC Bhosari P Stn      1\n",
       "The State of Maharashtra  Lashkar P Stn                                  1\n",
       "The State of Maharashtra MIDC Bhosari P  Stn                             1\n",
       "The State of Maharashtra Warje Malwadi P.S.                              1\n",
       "The State of Maharashtra Dattawdi P Stn                                  1\n",
       "State Of Maharashtra/Narayangaon Police Statioin                         1\n",
       "State of Maharashtra Through Padgha Police Station                       1\n",
       "The State Nerul Police Station, Dist. Thane                              1\n",
       "The State Kasarvadvli Pol. Stn. Dist. Thane                              1\n",
       "State of Maharashtra Wagle Estate Plc stn. Thane                         1\n",
       "Name: petparty_name, Length: 1109, dtype: int64"
      ]
     },
     "execution_count": 257,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.petparty_name.value_counts()"
   ]
  },
  {
   "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
}