scripts/bihar_to_csv.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>filesize</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>pdf_path</th>\n",
       "      <th>meta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6028561</td>\n",
       "      <td>2020-07-28T22:24:16Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4045548</td>\n",
       "      <td>2020-07-28T22:25:18Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3842485</td>\n",
       "      <td>2020-07-28T22:26:14Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4718095</td>\n",
       "      <td>2020-07-28T22:26:06Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3745353</td>\n",
       "      <td>2020-07-28T22:24:17Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72718</th>\n",
       "      <td>2592820</td>\n",
       "      <td>2020-07-24T06:13:09Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72719</th>\n",
       "      <td>4000473</td>\n",
       "      <td>2020-07-24T06:14:17Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72720</th>\n",
       "      <td>4907926</td>\n",
       "      <td>2020-07-24T06:12:54Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72721</th>\n",
       "      <td>4358269</td>\n",
       "      <td>2020-07-24T06:14:17Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72722</th>\n",
       "      <td>4106424</td>\n",
       "      <td>2020-07-24T06:13:43Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72723 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       filesize             timestamp  \\\n",
       "0       6028561  2020-07-28T22:24:16Z   \n",
       "1       4045548  2020-07-28T22:25:18Z   \n",
       "2       3842485  2020-07-28T22:26:14Z   \n",
       "3       4718095  2020-07-28T22:26:06Z   \n",
       "4       3745353  2020-07-28T22:24:17Z   \n",
       "...         ...                   ...   \n",
       "72718   2592820  2020-07-24T06:13:09Z   \n",
       "72719   4000473  2020-07-24T06:14:17Z   \n",
       "72720   4907926  2020-07-24T06:12:54Z   \n",
       "72721   4358269  2020-07-24T06:14:17Z   \n",
       "72722   4106424  2020-07-24T06:13:43Z   \n",
       "\n",
       "                                                pdf_path              meta  \n",
       "0      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "1      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "2      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "3      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "4      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "...                                                  ...               ...  \n",
       "72718  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "72719  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "72720  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "72721  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "72722  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "\n",
       "[72723 rows x 4 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# gsutil -u in-electoral-rolls ls -al gs://in-electoral-rolls-2020/bihar > list3.txt\n",
    "# FIXME: ignore last line, it's summary\n",
    "df = pd.read_csv('../metadata_and_log_files/list3.txt', header=None, delimiter='\\s+', nrows=72723, engine='python')\n",
    "df.columns = ['filesize', 'timestamp', 'pdf_path', 'meta']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check smallest file size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>filesize</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>pdf_path</th>\n",
       "      <th>meta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>59103</th>\n",
       "      <td>108614</td>\n",
       "      <td>2020-07-26T19:24:41Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14604</th>\n",
       "      <td>110280</td>\n",
       "      <td>2020-07-31T05:43:10Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14524</th>\n",
       "      <td>110280</td>\n",
       "      <td>2020-07-31T05:42:58Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14518</th>\n",
       "      <td>110280</td>\n",
       "      <td>2020-07-31T05:43:36Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14487</th>\n",
       "      <td>110280</td>\n",
       "      <td>2020-07-31T05:42:54Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15026</th>\n",
       "      <td>6603236</td>\n",
       "      <td>2020-07-31T08:03:34Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1241</th>\n",
       "      <td>6610269</td>\n",
       "      <td>2020-07-29T02:36:16Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64914</th>\n",
       "      <td>6619014</td>\n",
       "      <td>2020-07-27T17:15:31Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62938</th>\n",
       "      <td>6669693</td>\n",
       "      <td>2020-07-23T23:05:02Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64910</th>\n",
       "      <td>6701848</td>\n",
       "      <td>2020-07-27T17:14:43Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72723 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       filesize             timestamp  \\\n",
       "59103    108614  2020-07-26T19:24:41Z   \n",
       "14604    110280  2020-07-31T05:43:10Z   \n",
       "14524    110280  2020-07-31T05:42:58Z   \n",
       "14518    110280  2020-07-31T05:43:36Z   \n",
       "14487    110280  2020-07-31T05:42:54Z   \n",
       "...         ...                   ...   \n",
       "15026   6603236  2020-07-31T08:03:34Z   \n",
       "1241    6610269  2020-07-29T02:36:16Z   \n",
       "64914   6619014  2020-07-27T17:15:31Z   \n",
       "62938   6669693  2020-07-23T23:05:02Z   \n",
       "64910   6701848  2020-07-27T17:14:43Z   \n",
       "\n",
       "                                                pdf_path              meta  \n",
       "59103  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "14604  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "14524  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "14518  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "14487  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "...                                                  ...               ...  \n",
       "15026  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "1241   gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "64914  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "62938  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "64910  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1  \n",
       "\n",
       "[72723 rows x 4 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(['filesize'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_ac_part_no(r):\n",
    "    for m in re.finditer('FinalRoll_ACNo_(\\d+)PartNo_(\\d+)\\.pdf', r.pdf_path):\n",
    "        ac = int(m.group(1))\n",
    "        part = int(m.group(2))\n",
    "        return ac, part\n",
    "    print(r.pdf_path)\n",
    "    return None, None\n",
    "\n",
    "df[['ac_no', 'part_no']] = df.apply(lambda r: get_ac_part_no(r), axis=1, result_type='expand')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>filesize</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>pdf_path</th>\n",
       "      <th>meta</th>\n",
       "      <th>ac_no</th>\n",
       "      <th>part_no</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6028561</td>\n",
       "      <td>2020-07-28T22:24:16Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4045548</td>\n",
       "      <td>2020-07-28T22:25:18Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>100</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3842485</td>\n",
       "      <td>2020-07-28T22:26:14Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4718095</td>\n",
       "      <td>2020-07-28T22:26:06Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>100</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3745353</td>\n",
       "      <td>2020-07-28T22:24:17Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>100</td>\n",
       "      <td>102</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72718</th>\n",
       "      <td>2592820</td>\n",
       "      <td>2020-07-24T06:13:09Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>9</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72719</th>\n",
       "      <td>4000473</td>\n",
       "      <td>2020-07-24T06:14:17Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>9</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72720</th>\n",
       "      <td>4907926</td>\n",
       "      <td>2020-07-24T06:12:54Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>9</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72721</th>\n",
       "      <td>4358269</td>\n",
       "      <td>2020-07-24T06:14:17Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>9</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72722</th>\n",
       "      <td>4106424</td>\n",
       "      <td>2020-07-24T06:13:43Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>9</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72723 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       filesize             timestamp  \\\n",
       "0       6028561  2020-07-28T22:24:16Z   \n",
       "1       4045548  2020-07-28T22:25:18Z   \n",
       "2       3842485  2020-07-28T22:26:14Z   \n",
       "3       4718095  2020-07-28T22:26:06Z   \n",
       "4       3745353  2020-07-28T22:24:17Z   \n",
       "...         ...                   ...   \n",
       "72718   2592820  2020-07-24T06:13:09Z   \n",
       "72719   4000473  2020-07-24T06:14:17Z   \n",
       "72720   4907926  2020-07-24T06:12:54Z   \n",
       "72721   4358269  2020-07-24T06:14:17Z   \n",
       "72722   4106424  2020-07-24T06:13:43Z   \n",
       "\n",
       "                                                pdf_path              meta  \\\n",
       "0      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "1      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "2      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "3      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "4      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "...                                                  ...               ...   \n",
       "72718  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "72719  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "72720  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "72721  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "72722  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "\n",
       "       ac_no  part_no  \n",
       "0        100        1  \n",
       "1        100       10  \n",
       "2        100      100  \n",
       "3        100      101  \n",
       "4        100      102  \n",
       "...      ...      ...  \n",
       "72718      9       95  \n",
       "72719      9       96  \n",
       "72720      9       97  \n",
       "72721      9       98  \n",
       "72722      9       99  \n",
       "\n",
       "[72723 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>filesize</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>pdf_path</th>\n",
       "      <th>meta</th>\n",
       "      <th>ac_no</th>\n",
       "      <th>part_no</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33055</td>\n",
       "      <td>4683775</td>\n",
       "      <td>2020-07-31T12:57:49Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>33166</td>\n",
       "      <td>3401423</td>\n",
       "      <td>2020-07-23T07:47:26Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>33277</td>\n",
       "      <td>3442217</td>\n",
       "      <td>2020-07-20T13:11:04Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>33319</td>\n",
       "      <td>3648596</td>\n",
       "      <td>2020-07-20T13:11:10Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>33330</td>\n",
       "      <td>3097803</td>\n",
       "      <td>2020-07-20T13:11:16Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72718</th>\n",
       "      <td>48156</td>\n",
       "      <td>4230033</td>\n",
       "      <td>2020-08-05T14:28:11Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>243</td>\n",
       "      <td>317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72719</th>\n",
       "      <td>48157</td>\n",
       "      <td>3393267</td>\n",
       "      <td>2020-08-05T14:27:56Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>243</td>\n",
       "      <td>318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72720</th>\n",
       "      <td>48158</td>\n",
       "      <td>3957013</td>\n",
       "      <td>2020-08-05T14:26:38Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>243</td>\n",
       "      <td>319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72721</th>\n",
       "      <td>48160</td>\n",
       "      <td>4497632</td>\n",
       "      <td>2020-08-06T02:53:29Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>243</td>\n",
       "      <td>320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72722</th>\n",
       "      <td>48161</td>\n",
       "      <td>3404653</td>\n",
       "      <td>2020-08-06T02:53:29Z</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>metageneration=1</td>\n",
       "      <td>243</td>\n",
       "      <td>321</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72723 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       index  filesize             timestamp  \\\n",
       "0      33055   4683775  2020-07-31T12:57:49Z   \n",
       "1      33166   3401423  2020-07-23T07:47:26Z   \n",
       "2      33277   3442217  2020-07-20T13:11:04Z   \n",
       "3      33319   3648596  2020-07-20T13:11:10Z   \n",
       "4      33330   3097803  2020-07-20T13:11:16Z   \n",
       "...      ...       ...                   ...   \n",
       "72718  48156   4230033  2020-08-05T14:28:11Z   \n",
       "72719  48157   3393267  2020-08-05T14:27:56Z   \n",
       "72720  48158   3957013  2020-08-05T14:26:38Z   \n",
       "72721  48160   4497632  2020-08-06T02:53:29Z   \n",
       "72722  48161   3404653  2020-08-06T02:53:29Z   \n",
       "\n",
       "                                                pdf_path              meta  \\\n",
       "0      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "1      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "2      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "3      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "4      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "...                                                  ...               ...   \n",
       "72718  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "72719  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "72720  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "72721  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "72722  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...  metageneration=1   \n",
       "\n",
       "       ac_no  part_no  \n",
       "0          1        1  \n",
       "1          1        2  \n",
       "2          1        3  \n",
       "3          1        4  \n",
       "4          1        5  \n",
       "...      ...      ...  \n",
       "72718    243      317  \n",
       "72719    243      318  \n",
       "72720    243      319  \n",
       "72721    243      320  \n",
       "72722    243      321  \n",
       "\n",
       "[72723 rows x 7 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.sort_values(['ac_no', 'part_no']).reset_index()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>ac_no</th>\n",
       "      <th>part_no</th>\n",
       "      <th>assembly_segment</th>\n",
       "      <th>part_number</th>\n",
       "      <th>pdf_path</th>\n",
       "      <th>filesize</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1 - वाल्मीकिनगर</td>\n",
       "      <td>1 - उत्क्रमित मध्य विद्यालय, ठाढ़ी</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>4683775</td>\n",
       "      <td>2020-07-31T12:57:49Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1 - वाल्मीकिनगर</td>\n",
       "      <td>2 - वन विकास भवन कोतरहा पूर्वी भाग</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>3401423</td>\n",
       "      <td>2020-07-23T07:47:26Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1 - वाल्मीकिनगर</td>\n",
       "      <td>3 - वन विकास भवन कोतरहा पश्चिम भाग</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>3442217</td>\n",
       "      <td>2020-07-20T13:11:04Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1 - वाल्मीकिनगर</td>\n",
       "      <td>4 - उत्क्रमित मध्य विद्यालय लक्ष्मीपुर पूर्वी भाग</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>3648596</td>\n",
       "      <td>2020-07-20T13:11:10Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1 - वाल्मीकिनगर</td>\n",
       "      <td>5 - उत्क्रमित मध्य विद्यालय लक्ष्मीपुर पश्चिम भाग</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>3097803</td>\n",
       "      <td>2020-07-20T13:11:16Z</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72718</th>\n",
       "      <td>243</td>\n",
       "      <td>317</td>\n",
       "      <td>243 - चकाई</td>\n",
       "      <td>317 - पंचायत भवन दुल्लमपुर दांया भाग</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>4230033</td>\n",
       "      <td>2020-08-05T14:28:11Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72719</th>\n",
       "      <td>243</td>\n",
       "      <td>318</td>\n",
       "      <td>243 - चकाई</td>\n",
       "      <td>318 - पंचायत भवन दुलामपुर बांया भाग</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>3393267</td>\n",
       "      <td>2020-08-05T14:27:56Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72720</th>\n",
       "      <td>243</td>\n",
       "      <td>319</td>\n",
       "      <td>243 - चकाई</td>\n",
       "      <td>319 - प्राथमिक विद्यालय चितरडीह</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>3957013</td>\n",
       "      <td>2020-08-05T14:26:38Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72721</th>\n",
       "      <td>243</td>\n",
       "      <td>320</td>\n",
       "      <td>243 - चकाई</td>\n",
       "      <td>320 - वन विभाग  निरिक्षण भवन प्रतापपुर</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>4497632</td>\n",
       "      <td>2020-08-06T02:53:29Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72722</th>\n",
       "      <td>243</td>\n",
       "      <td>321</td>\n",
       "      <td>243 - चकाई</td>\n",
       "      <td>321 - वन विभाग निरीक्षण भवन प्रतापपुर बांया भाग</td>\n",
       "      <td>gs://in-electoral-rolls-2020/bihar/FinalRoll_A...</td>\n",
       "      <td>3404653</td>\n",
       "      <td>2020-08-06T02:53:29Z</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72723 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       ac_no  part_no assembly_segment  \\\n",
       "0          1        1  1 - वाल्मीकिनगर   \n",
       "1          1        2  1 - वाल्मीकिनगर   \n",
       "2          1        3  1 - वाल्मीकिनगर   \n",
       "3          1        4  1 - वाल्मीकिनगर   \n",
       "4          1        5  1 - वाल्मीकिनगर   \n",
       "...      ...      ...              ...   \n",
       "72718    243      317       243 - चकाई   \n",
       "72719    243      318       243 - चकाई   \n",
       "72720    243      319       243 - चकाई   \n",
       "72721    243      320       243 - चकाई   \n",
       "72722    243      321       243 - चकाई   \n",
       "\n",
       "                                             part_number  \\\n",
       "0                     1 - उत्क्रमित मध्य विद्यालय, ठाढ़ी   \n",
       "1                     2 - वन विकास भवन कोतरहा पूर्वी भाग   \n",
       "2                     3 - वन विकास भवन कोतरहा पश्चिम भाग   \n",
       "3      4 - उत्क्रमित मध्य विद्यालय लक्ष्मीपुर पूर्वी भाग   \n",
       "4      5 - उत्क्रमित मध्य विद्यालय लक्ष्मीपुर पश्चिम भाग   \n",
       "...                                                  ...   \n",
       "72718               317 - पंचायत भवन दुल्लमपुर दांया भाग   \n",
       "72719                318 - पंचायत भवन दुलामपुर बांया भाग   \n",
       "72720                    319 - प्राथमिक विद्यालय चितरडीह   \n",
       "72721             320 - वन विभाग  निरिक्षण भवन प्रतापपुर   \n",
       "72722    321 - वन विभाग निरीक्षण भवन प्रतापपुर बांया भाग   \n",
       "\n",
       "                                                pdf_path  filesize  \\\n",
       "0      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...   4683775   \n",
       "1      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...   3401423   \n",
       "2      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...   3442217   \n",
       "3      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...   3648596   \n",
       "4      gs://in-electoral-rolls-2020/bihar/FinalRoll_A...   3097803   \n",
       "...                                                  ...       ...   \n",
       "72718  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...   4230033   \n",
       "72719  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...   3393267   \n",
       "72720  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...   3957013   \n",
       "72721  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...   4497632   \n",
       "72722  gs://in-electoral-rolls-2020/bihar/FinalRoll_A...   3404653   \n",
       "\n",
       "                  timestamp  \n",
       "0      2020-07-31T12:57:49Z  \n",
       "1      2020-07-23T07:47:26Z  \n",
       "2      2020-07-20T13:11:04Z  \n",
       "3      2020-07-20T13:11:10Z  \n",
       "4      2020-07-20T13:11:16Z  \n",
       "...                     ...  \n",
       "72718  2020-08-05T14:28:11Z  \n",
       "72719  2020-08-05T14:27:56Z  \n",
       "72720  2020-08-05T14:26:38Z  \n",
       "72721  2020-08-06T02:53:29Z  \n",
       "72722  2020-08-06T02:53:29Z  \n",
       "\n",
       "[72723 rows x 7 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mdf = pd.read_csv('../metadata_and_log_files/metadata.csv')\n",
    "mdf = mdf.join(df)\n",
    "odf = mdf[['ac_no', 'part_no', 'assembly_segment', 'part_number', 'pdf_path', 'filesize', 'timestamp']]\n",
    "odf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "odf.to_csv('../metadata_and_log_files/bihar.csv', index=False)"
   ]
  }
 ],
 "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}