Extract_Tables.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# install requirements\n",
    "! pip install -r requirements.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import pandas as pd\n",
    "import math\n",
    "import camelot\n",
    "import dateutil"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The Notebook will show the flow for a single file and the code will be exported to a python script for more generalized solution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "filename = 'data/canopy_technical_test_input.pdf'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The file has 1 table, detected using stream method of table detection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<TableList n=1>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tables = camelot.read_pdf(filename, flavor='stream', pages='all')\n",
    "tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the table detection is based of alignment, the left aligned lines at the top are also extracted, and need seperate cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31.03.2018 - 30.04.2018</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>1</th>\n",
       "      <td>Account SG1234567-01-01-JPY01 in JPY</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>2</th>\n",
       "      <td>Booking Details</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>3</th>\n",
       "      <td>Booking Date</td>\n",
       "      <td>Txn Date</td>\n",
       "      <td>Booking Text</td>\n",
       "      <td>Value Date</td>\n",
       "      <td>Debit</td>\n",
       "      <td>Credit</td>\n",
       "      <td>Balance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31.03.2018</td>\n",
       "      <td>31.03.2018</td>\n",
       "      <td>Initial Balance</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      0           1                2  \\\n",
       "0               31.03.2018 - 30.04.2018                                \n",
       "1  Account SG1234567-01-01-JPY01 in JPY                                \n",
       "2                       Booking Details                                \n",
       "3                          Booking Date    Txn Date     Booking Text   \n",
       "4                            31.03.2018  31.03.2018  Initial Balance   \n",
       "\n",
       "            3      4       5        6  \n",
       "0                                      \n",
       "1                                      \n",
       "2                                      \n",
       "3  Value Date  Debit  Credit  Balance  \n",
       "4                                0.00  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = tables[0].df\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning\n",
    "Remove the first few rows that are not part of the table.  \n",
    "Assuming each column should have a valid header, we use the first row that has call the columns non-empty as the header"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "class NoHeaderFound(ValueError): pass\n",
    "\n",
    "def valid_header(row):\n",
    "    \"\"\"A row is valid is all the cells elements are neither nan nor empty string\"\"\"\n",
    "    for j,elem in row.items():\n",
    "        # if empty string or nan, skip. check for float before calling isnan to avoid TypeError\n",
    "        if not elem or (isinstance(elem, float) and np.isnan(elem)):\n",
    "            return False\n",
    "    return True\n",
    "\n",
    "def find_header_row(df):\n",
    "    for i,row in df.iterrows():\n",
    "        if valid_header(row): return i\n",
    "    raise NoHeaderFound('No Rows satisfy the valid header condition')\n",
    "\n",
    "h_row = find_header_row(df)\n",
    "h_row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Booking Date',\n",
       " 'Txn Date',\n",
       " 'Booking Text',\n",
       " 'Value Date',\n",
       " 'Debit',\n",
       " 'Credit',\n",
       " 'Balance']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "headers = list(df.iloc[h_row,:])\n",
    "headers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# strip the top part\n",
    "df = df.iloc[h_row+1:,:]\n",
    "# set the header\n",
    "df.columns = headers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Booking Date</th>\n",
       "      <th>Txn Date</th>\n",
       "      <th>Booking Text</th>\n",
       "      <th>Value Date</th>\n",
       "      <th>Debit</th>\n",
       "      <th>Credit</th>\n",
       "      <th>Balance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31.03.2018</td>\n",
       "      <td>31.03.2018</td>\n",
       "      <td>Initial Balance</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>01.04.2018</td>\n",
       "      <td>01.04.2018</td>\n",
       "      <td>VALUE DATED BALANCE BROUGHT FORWARD</td>\n",
       "      <td>01.04.2018</td>\n",
       "      <td></td>\n",
       "      <td>180,431,640.00</td>\n",
       "      <td>180,431,640.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>06.04.2018</td>\n",
       "      <td>06.04.2018</td>\n",
       "      <td>INTEREST-FIXED TERM LOAN</td>\n",
       "      <td>06.04.2018</td>\n",
       "      <td>472,500.00</td>\n",
       "      <td></td>\n",
       "      <td>179,959,140.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Contract No: 3001-AA18091ZN72C|Interest rate:</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.810000%|Capital: 1,000,000,000.00|Period: 16...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Booking Date    Txn Date                                       Booking Text  \\\n",
       "4   31.03.2018  31.03.2018                                    Initial Balance   \n",
       "5   01.04.2018  01.04.2018                VALUE DATED BALANCE BROUGHT FORWARD   \n",
       "6   06.04.2018  06.04.2018                           INTEREST-FIXED TERM LOAN   \n",
       "7                               Contract No: 3001-AA18091ZN72C|Interest rate:   \n",
       "8                           0.810000%|Capital: 1,000,000,000.00|Period: 16...   \n",
       "\n",
       "   Value Date       Debit          Credit         Balance  \n",
       "4                                                    0.00  \n",
       "5  01.04.2018              180,431,640.00  180,431,640.00  \n",
       "6  06.04.2018  472,500.00                  179,959,140.00  \n",
       "7                                                          \n",
       "8                                                          "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merging the rows\n",
    "\n",
    "#### Assumption\n",
    "If a cell has a value, it will always start in first line, i.e., the cells are top aligned.\n",
    "\n",
    "#### Caveat\n",
    "There should atleast be one column that cannot have multi line values. This would mostly be a date or amount column.\n",
    "This is required to determine the row seperation when all the columns have values. An empty value here is also acceptable but the value shouldn't span multiple lines.\n",
    "\n",
    "This is generally not a problem for Financial Statements since each valid entry would have atleast one of date or amount which are single line values\n",
    "\n",
    "### Conditions to find the row seperation\n",
    "* If a value is missing in 1st line of a row is found, it signals start of a new row. eg. Credit value at index 5 above\n",
    "* If all values are found in a line, determining the next line is tricky, in this case, we use the existance of single line value like Date or Amount to determine row separation  \n",
    "\n",
    "##### Note\n",
    "The above mentioned Conditions are just thoughts. As mentioned at the end of the Caveat, since Financial Statements have either of date or amount, we straight away use them to find the row separation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def is_date(candidate):\n",
    "    \"\"\"Check if a candidate can be parsed by the dateparser. Not necessarily accurate\"\"\"\n",
    "    try:\n",
    "        _ = dateutil.parser.parse(candidate)\n",
    "        return True\n",
    "    except dateutil.parser.ParserError:\n",
    "        return False\n",
    "\n",
    "def is_amount(candidate):\n",
    "    \"\"\"Is a valid amount.\n",
    "    Doesn't support european way of writing the amount\"\"\"\n",
    "    if re.match(r'^(\\d+)(,(\\d{2,3},)*\\d{3})?(\\.\\d{2})?$', candidate): return True\n",
    "    return False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find columns that have either date of amount only\n",
    "These are used as row boundries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Txn Date', 'Value Date', 'Debit', 'Credit', 'Balance']"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_single_val_cols(df):\n",
    "    \"\"\"Return column names that contain either of date or amount\"\"\"\n",
    "    cols = []\n",
    "    for col in df.columns:\n",
    "        if all(df[col].apply(lambda elem: not elem or is_date(elem) or is_amount(elem))):\n",
    "            cols.append(col)\n",
    "    return cols\n",
    "single_val_cols = get_single_val_cols(df)\n",
    "single_val_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[4, 5, 6, 10, 14, 16, 18, 24, 25, 29, 31, 33]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# index if each valid entry starting row\n",
    "starts = set()\n",
    "for col in single_val_cols:\n",
    "    starts = starts.union(df.index[df[col].astype(bool)])\n",
    "# Add an extra index for merging trailing line if any\n",
    "starts.add(max(df.index)+1)\n",
    "starts = sorted(list(starts))\n",
    "starts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Booking Date', 'Booking Text']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Columns that can span multiple lines and need merging\n",
    "multi_val_cols = [c for c in df.columns if c not in single_val_cols]\n",
    "multi_val_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Booking Date</th>\n",
       "      <th>Txn Date</th>\n",
       "      <th>Booking Text</th>\n",
       "      <th>Value Date</th>\n",
       "      <th>Debit</th>\n",
       "      <th>Credit</th>\n",
       "      <th>Balance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.810000%|Capital: 500,000,000.00|Period: 27.0...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>10.04.2018</td>\n",
       "      <td>10.04.2018</td>\n",
       "      <td>FOREX SPOT</td>\n",
       "      <td>10.04.2018</td>\n",
       "      <td></td>\n",
       "      <td>157,500.00</td>\n",
       "      <td>180,431,640.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>EUR/JPY 131.1953</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Carried forward</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>180,431,640.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Remarks</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Booking Date    Txn Date  \\\n",
       "28                            \n",
       "29   10.04.2018  10.04.2018   \n",
       "30                            \n",
       "31                            \n",
       "32      Remarks               \n",
       "\n",
       "                                         Booking Text  Value Date Debit  \\\n",
       "28  0.810000%|Capital: 500,000,000.00|Period: 27.0...                     \n",
       "29                                         FOREX SPOT  10.04.2018         \n",
       "30                                   EUR/JPY 131.1953                     \n",
       "31                                    Carried forward                     \n",
       "32                                                                        \n",
       "\n",
       "        Credit         Balance  \n",
       "28                              \n",
       "29  157,500.00  180,431,640.00  \n",
       "30                              \n",
       "31              180,431,640.00  \n",
       "32                              "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We can see that `Remarks` is being picked Up in Booking Date column, making it not qualify as date.\n",
    "# This garbage row will be trimmed just like the garbage rows at the top\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/dumbpy/applications/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3331: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    }
   ],
   "source": [
    "# Merge multi_val_cols between the row boundries\n",
    "for i,j in zip(starts[:-1],starts[1:]):\n",
    "    for m_col in multi_val_cols:\n",
    "        df[m_col][i] = \"\\n\".join([elem for elem in df[m_col].loc[i:j-1] if elem])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Drop the rows multicol text of which has been merged above them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Booking Date</th>\n",
       "      <th>Txn Date</th>\n",
       "      <th>Booking Text</th>\n",
       "      <th>Value Date</th>\n",
       "      <th>Debit</th>\n",
       "      <th>Credit</th>\n",
       "      <th>Balance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>31.03.2018</td>\n",
       "      <td>31.03.2018</td>\n",
       "      <td>Initial Balance</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01.04.2018</td>\n",
       "      <td>01.04.2018</td>\n",
       "      <td>VALUE DATED BALANCE BROUGHT FORWARD</td>\n",
       "      <td>01.04.2018</td>\n",
       "      <td></td>\n",
       "      <td>180,431,640.00</td>\n",
       "      <td>180,431,640.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06.04.2018</td>\n",
       "      <td>06.04.2018</td>\n",
       "      <td>INTEREST-FIXED TERM LOAN\\nContract No: 3001-AA...</td>\n",
       "      <td>06.04.2018</td>\n",
       "      <td>472,500.00</td>\n",
       "      <td></td>\n",
       "      <td>179,959,140.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06.04.2018</td>\n",
       "      <td>06.04.2018</td>\n",
       "      <td>INTEREST-FIXED TERM LOAN\\nContract No: 3001-AA...</td>\n",
       "      <td>06.04.2018</td>\n",
       "      <td>315,000.00</td>\n",
       "      <td></td>\n",
       "      <td>179,644,140.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06.04.2018</td>\n",
       "      <td>06.04.2018</td>\n",
       "      <td>FOREX SPOT\\nEUR/JPY 130.7271</td>\n",
       "      <td>06.04.2018</td>\n",
       "      <td></td>\n",
       "      <td>472,500.00</td>\n",
       "      <td>180,116,640.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>06.04.2018</td>\n",
       "      <td>06.04.2018</td>\n",
       "      <td>FOREX SPOT\\nEUR/JPY 130.7021</td>\n",
       "      <td>06.04.2018</td>\n",
       "      <td></td>\n",
       "      <td>315,000.00</td>\n",
       "      <td>180,431,640.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>09.04.2018</td>\n",
       "      <td>09.04.2018</td>\n",
       "      <td>INTEREST-FIXED TERM LOAN\\nContract No: 3001-AA...</td>\n",
       "      <td>09.04.2018</td>\n",
       "      <td>157,500.00</td>\n",
       "      <td></td>\n",
       "      <td>180,274,140.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>09.04.2018\\nE</td>\n",
       "      <td>09.04.2018</td>\n",
       "      <td>FOREX SPOT\\nEUR/JPY 131.1407</td>\n",
       "      <td>09.04.2018</td>\n",
       "      <td></td>\n",
       "      <td>157,500.00</td>\n",
       "      <td>180,431,640.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10.04.2018</td>\n",
       "      <td>10.04.2018</td>\n",
       "      <td>INTEREST-FIXED TERM LOAN\\nContract No: 3001-AA...</td>\n",
       "      <td>10.04.2018</td>\n",
       "      <td>157,500.00</td>\n",
       "      <td></td>\n",
       "      <td>180,274,140.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10.04.2018</td>\n",
       "      <td>10.04.2018</td>\n",
       "      <td>FOREX SPOT\\nEUR/JPY 131.1953</td>\n",
       "      <td>10.04.2018</td>\n",
       "      <td></td>\n",
       "      <td>157,500.00</td>\n",
       "      <td>180,431,640.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Remarks</td>\n",
       "      <td></td>\n",
       "      <td>Carried forward</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>180,431,640.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Booking Date    Txn Date  \\\n",
       "0      31.03.2018  31.03.2018   \n",
       "1      01.04.2018  01.04.2018   \n",
       "2      06.04.2018  06.04.2018   \n",
       "3      06.04.2018  06.04.2018   \n",
       "4      06.04.2018  06.04.2018   \n",
       "5      06.04.2018  06.04.2018   \n",
       "6      09.04.2018  09.04.2018   \n",
       "7   09.04.2018\\nE  09.04.2018   \n",
       "8      10.04.2018  10.04.2018   \n",
       "9      10.04.2018  10.04.2018   \n",
       "10        Remarks               \n",
       "\n",
       "                                         Booking Text  Value Date       Debit  \\\n",
       "0                                     Initial Balance                           \n",
       "1                 VALUE DATED BALANCE BROUGHT FORWARD  01.04.2018               \n",
       "2   INTEREST-FIXED TERM LOAN\\nContract No: 3001-AA...  06.04.2018  472,500.00   \n",
       "3   INTEREST-FIXED TERM LOAN\\nContract No: 3001-AA...  06.04.2018  315,000.00   \n",
       "4                        FOREX SPOT\\nEUR/JPY 130.7271  06.04.2018               \n",
       "5                        FOREX SPOT\\nEUR/JPY 130.7021  06.04.2018               \n",
       "6   INTEREST-FIXED TERM LOAN\\nContract No: 3001-AA...  09.04.2018  157,500.00   \n",
       "7                        FOREX SPOT\\nEUR/JPY 131.1407  09.04.2018               \n",
       "8   INTEREST-FIXED TERM LOAN\\nContract No: 3001-AA...  10.04.2018  157,500.00   \n",
       "9                        FOREX SPOT\\nEUR/JPY 131.1953  10.04.2018               \n",
       "10                                    Carried forward                           \n",
       "\n",
       "            Credit         Balance  \n",
       "0                             0.00  \n",
       "1   180,431,640.00  180,431,640.00  \n",
       "2                   179,959,140.00  \n",
       "3                   179,644,140.00  \n",
       "4       472,500.00  180,116,640.00  \n",
       "5       315,000.00  180,431,640.00  \n",
       "6                   180,274,140.00  \n",
       "7       157,500.00  180,431,640.00  \n",
       "8                   180,274,140.00  \n",
       "9       157,500.00  180,431,640.00  \n",
       "10                  180,431,640.00  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.drop(set(df.index)-set(starts))\n",
    "df = df.reset_index(drop=True)\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.6.8 64-bit ('base': conda)",
   "language": "python",
   "name": "python36864bitbaseconda5bf6fb4b7f9f47bcb90c0a908e37149f"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}