{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Setting up the dependencies" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": true }, "outputs": [], "source": [ "import tabula as tbl\n", "import pandas as pd\n", "import copy\n", "from IPython.display import display, HTML\n", "import time\n", "from pyPdf import PdfFileReader" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Reading the file configuration parameters" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": true }, "outputs": [], "source": [ "fileName = \"Input Files/41-Peenya.pdf\"\n", "numberOfPages = 331 #NOTE: Pages are NOT 0-indexed\n", "expectedNumCols = 17" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "collapsed": false }, "outputs": [], "source": [ "with open(fileName,'rb') as file:\n", " pdf = PdfFileReader(file)\n", " numberOfPages = pdf.getNumPages()\n", " " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Parsing the PDF files " ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "collapsed": false, "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "There appears to be a discrepancy with the table on page 1. Ignoring this page\n" ] }, { "ename": "IOError", "evalue": "[Errno 13] Permission denied: 'Output CSVs/41-Peenya.csv'", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mIOError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m()\u001b[0m\n\u001b[1;32m 90\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m 91\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m---> 92\u001b[0;31m \u001b[0mfileTable\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"Output CSVs/41-Peenya.csv\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 93\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m 94\u001b[0m \u001b[0mendTime\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mtime\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtime\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n", "\u001b[0;32mC:\\Users\\Admin\\Anaconda2\\lib\\site-packages\\pandas\\core\\frame.pyc\u001b[0m in \u001b[0;36mto_csv\u001b[0;34m(self, path_or_buf, sep, na_rep, float_format, columns, header, index, index_label, mode, encoding, compression, quoting, quotechar, line_terminator, chunksize, tupleize_cols, date_format, doublequote, escapechar, decimal)\u001b[0m\n\u001b[1;32m 1381\u001b[0m \u001b[0mdoublequote\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdoublequote\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m 1382\u001b[0m escapechar=escapechar, decimal=decimal)\n\u001b[0;32m-> 1383\u001b[0;31m \u001b[0mformatter\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msave\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 1384\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m 1385\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mpath_or_buf\u001b[0m \u001b[1;32mis\u001b[0m \u001b[0mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n", "\u001b[0;32mC:\\Users\\Admin\\Anaconda2\\lib\\site-packages\\pandas\\formats\\format.pyc\u001b[0m in \u001b[0;36msave\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 1458\u001b[0m f = _get_handle(self.path_or_buf, self.mode,\n\u001b[1;32m 1459\u001b[0m \u001b[0mencoding\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mencoding\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1460\u001b[0;31m compression=self.compression)\n\u001b[0m\u001b[1;32m 1461\u001b[0m \u001b[0mclose\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mTrue\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m 1462\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n", "\u001b[0;32mC:\\Users\\Admin\\Anaconda2\\lib\\site-packages\\pandas\\io\\common.pyc\u001b[0m in \u001b[0;36m_get_handle\u001b[0;34m(path, mode, encoding, compression, memory_map)\u001b[0m\n\u001b[1;32m 332\u001b[0m \u001b[0mf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpath\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmode\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrors\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'replace'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m 333\u001b[0m \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m--> 334\u001b[0;31m \u001b[0mf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpath\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmode\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 335\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m 336\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mmemory_map\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mhasattr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'fileno'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n", "\u001b[0;31mIOError\u001b[0m: [Errno 13] Permission denied: 'Output CSVs/41-Peenya.csv'" ] } ], "source": [ "startTime = time.time()\n", "\n", "fileTable = pd.DataFrame()\n", "\n", "#SETTING UP ITERATOR TO ITERATE THROUGH EACH PAGE\n", "for counter in range(0, numberOfPages):\n", " \n", " try:\n", "\n", " #READING THE RAW TABLE/S ON THE PAGE\n", " pageNo = counter+1\n", " pageRawTable = tbl.read_pdf(fileName, pages = pageNo, spreadsheet = True)\n", " pageRawTable = pageRawTable.replace(\"\\\\r\", \"\", regex = True) #TREATING NEW LINE ESCAPE CHARACTERS\n", " numRows = len(pageRawTable.index)\n", " numCols = len(pageRawTable.columns)\n", "\n", "\n", " #HANDLING CONDITION WHERE EXTRA COLUMN MIGHT EXIST DUE TO DATA DISCREPANCIES\n", " if numCols != expectedNumCols:\n", " print \"There appears to be a discrepancy with the table on page \" + str(pageNo) + \". Ignoring this page\"\n", " continue\n", "\n", " #REMOVING THE LAST TWO ROWS, IN CASE THE PAGE IS THE LAST PAGE \n", " if pageNo == numberOfPages:\n", " pageRawTable.drop([numRows-2, numRows-1], inplace = True, axis = 0)\n", "\n", " numRows = len(pageRawTable.index)\n", " numCols = len(pageRawTable.columns) \n", "\n", " #SETTING UP THE INDICES FOR ROWS WITH APP NO AND ROWS WITH AMT\n", " appRowIndices = range(0, numRows, 2)\n", " amtRowIndices = range(1, numRows, 2) #NOTE: WE EXCLUDE THE LAST ROW, AS THE VALUE POSITIONS ARE DIFFERENT IN IT\n", " lastRowIndex = numRows - 1\n", "\n", " #TREATING DATAFRAME TO REMOVE COLUMN CONTAINING THE VALUES \"APP NO\" AND \"AMT\"\n", " pageRawTable.iloc[lastRowIndex] = pageRawTable.iloc[lastRowIndex].shift(6, axis = 0) #CORRECTING OFFSET IN LAST ROW\n", " pageRawTable.drop(\"Unnamed: 6\", axis = 1, inplace = True)\n", "\n", " #SETTING UP DATAFRAMES CONTAINING APP_NO AND AMT\n", " appNoDataFrame = copy.deepcopy(pageRawTable.iloc[appRowIndices, :])\n", " appNoDataFrame = appNoDataFrame.reset_index()\n", " amtDataFrame = copy.deepcopy(pageRawTable.iloc[amtRowIndices, :])\n", " amtDataFrame = amtDataFrame.reset_index()\n", " appNoDataFrame.drop(\"index\", axis = 1, inplace = True)\n", " amtDataFrame.drop(\"index\", axis = 1, inplace = True) \n", "\n", " amtDataFrame.ix[:, 0:6] = appNoDataFrame.ix[:, 0:6] \n", "\n", " #RENAMING COLUMNS BEFORE CONVERTING TO WIDE AND LONG FORM\n", " appNoDataFrame.columns = [\"Sl No\", \"New PID\", \"Old PID\", \"Khata / Survey\", \"Owner Name\", \"Property Address\",\n", " \"2008\", \"2009\", \"2010\", \"2011\",\"2012\", \"2013\", \"2014\", \"2015\", \"2016\", \"2017\"]\n", "\n", " amtDataFrame.columns = [\"Sl No\", \"New PID\", \"Old PID\", \"Khata / Survey\", \"Owner Name\", \"Property Address\",\n", " \"2008\", \"2009\", \"2010\", \"2011\",\"2012\", \"2013\", \"2014\", \"2015\", \"2016\", \"2017\"] \n", "\n", " #CONVERTING APP_NO DATAFRAME FROM WIDE TO LONG FORMAT\n", " appNoDataFrame = pd.melt(appNoDataFrame, \n", " id_vars = [\"Sl No\", \"New PID\", \"Old PID\", \"Khata / Survey\", \n", " \"Owner Name\", \"Property Address\"], \n", " value_vars = [\"2008\", \"2009\", \"2010\", \"2011\", \"2012\", \"2013\", \"2014\", \n", " \"2015\", \"2016\", \"2017\"])\n", "\n", " #RENAMING THE NEW COLUMNS IN APP_NO DATAFRAME\n", " appNoDataFrame = appNoDataFrame.rename(index = str, columns = {\"variable\": \"year\", \"value\": \"app_no\"}) \n", "\n", " #CONVERTING AMT DATAFRAME FROM WIDE TO LONG FORMAT\n", " amtDataFrame = pd.melt(amtDataFrame, \n", " id_vars = [\"Sl No\", \"New PID\", \"Old PID\", \"Khata / Survey\", \n", " \"Owner Name\", \"Property Address\"], \n", " value_vars = [\"2008\", \"2009\", \"2010\", \"2011\", \"2012\", \"2013\", \"2014\", \n", " \"2015\", \"2016\", \"2017\"])\n", "\n", " #RENAMING THE NEW COLUMNS IN AMT DATAFRAME\n", " amtDataFrame = amtDataFrame.rename(index = str, columns = {\"variable\": \"year\", \"value\": \"amt\"}) \n", "\n", " #MERGING THE TWO DATAFRAMES TO GET A CONSOLIDATED FRAME FOR THE PAGE\n", " processedPageTable = appNoDataFrame.merge(amtDataFrame[[\"Sl No\", \"year\", \"amt\"]], on = [\"Sl No\", \"year\"], how = \"left\")\n", "\n", " processedPageTable.to_csv(\"Output CSVs/41-Peenya_\" + str(pageNo) + \".csv\")\n", "\n", " #APPENDING THE PROCESSED DATAFRAME TO THE OVERALL FILE DATAFRAME\n", " if len(fileTable.index) == 0:\n", " fileTable = processedPageTable\n", " else:\n", " fileTable = fileTable.append(processedPageTable)\n", " \n", " except:\n", " print \"There appears to be a discrepancy with the table on page \" + str(pageNo) + \". Ignoring this page\"\n", " continue\n", " \n", " \n", "fileTable.to_csv(\"Output CSVs/41-Peenya.csv\")\n", "\n", "endTime = time.time()\n", "difference = (endTime - startTime)/60\n", "\n", "print \"Execution time is: \" + str(difference) + \" minutes\"" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "%run:\n", " Run the named file inside IPython as a program.\n", "\n", "Usage::\n", "\n", " %run [-n -i -e -G]\n", " [( -t [-N] | -d [-b] | -p [profile options] )]\n", " ( -m mod | file ) [args]\n", "\n", "Parameters after the filename are passed as command-line arguments to\n", "the program (put in sys.argv). Then, control returns to IPython's\n", "prompt.\n", "\n", "This is similar to running at a system prompt ``python file args``,\n", "but with the advantage of giving you IPython's tracebacks, and of\n", "loading all variables into your interactive namespace for further use\n", "(unless -p is used, see below).\n", "\n", "The file is executed in a namespace initially consisting only of\n", "``__name__=='__main__'`` and sys.argv constructed as indicated. It thus\n", "sees its environment as if it were being run as a stand-alone program\n", "(except for sharing global objects such as previously imported\n", "modules). But after execution, the IPython interactive namespace gets\n", "updated with all variables defined in the program (except for __name__\n", "and sys.argv). This allows for very convenient loading of code for\n", "interactive work, while giving each program a 'clean sheet' to run in.\n", "\n", "Arguments are expanded using shell-like glob match. Patterns\n", "'*', '?', '[seq]' and '[!seq]' can be used. Additionally,\n", "tilde '~' will be expanded into user's home directory. Unlike\n", "real shells, quotation does not suppress expansions. Use\n", "*two* back slashes (e.g. ``\\\\*``) to suppress expansions.\n", "To completely disable these expansions, you can use -G flag.\n", "\n", "Options:\n", "\n", "-n\n", " __name__ is NOT set to '__main__', but to the running file's name\n", " without extension (as python does under import). This allows running\n", " scripts and reloading the definitions in them without calling code\n", " protected by an ``if __name__ == \"__main__\"`` clause.\n", "\n", "-i\n", " run the file in IPython's namespace instead of an empty one. This\n", " is useful if you are experimenting with code written in a text editor\n", " which depends on variables defined interactively.\n", "\n", "-e\n", " ignore sys.exit() calls or SystemExit exceptions in the script\n", " being run. This is particularly useful if IPython is being used to\n", " run unittests, which always exit with a sys.exit() call. In such\n", " cases you are interested in the output of the test results, not in\n", " seeing a traceback of the unittest module.\n", "\n", "-t\n", " print timing information at the end of the run. IPython will give\n", " you an estimated CPU time consumption for your script, which under\n", " Unix uses the resource module to avoid the wraparound problems of\n", " time.clock(). Under Unix, an estimate of time spent on system tasks\n", " is also given (for Windows platforms this is reported as 0.0).\n", "\n", "If -t is given, an additional ``-N`` option can be given, where \n", "must be an integer indicating how many times you want the script to\n", "run. The final timing report will include total and per run results.\n", "\n", "For example (testing the script uniq_stable.py)::\n", "\n", " In [1]: run -t uniq_stable\n", "\n", " IPython CPU timings (estimated):\n", " User : 0.19597 s.\n", " System: 0.0 s.\n", "\n", " In [2]: run -t -N5 uniq_stable\n", "\n", " IPython CPU timings (estimated):\n", " Total runs performed: 5\n", " Times : Total Per run\n", " User : 0.910862 s, 0.1821724 s.\n", " System: 0.0 s, 0.0 s.\n", "\n", "-d\n", " run your program under the control of pdb, the Python debugger.\n", " This allows you to execute your program step by step, watch variables,\n", " etc. Internally, what IPython does is similar to calling::\n", "\n", " pdb.run('execfile(\"YOURFILENAME\")')\n", "\n", " with a breakpoint set on line 1 of your file. You can change the line\n", " number for this automatic breakpoint to be by using the -bN option\n", " (where N must be an integer). For example::\n", "\n", " %run -d -b40 myscript\n", "\n", " will set the first breakpoint at line 40 in myscript.py. Note that\n", " the first breakpoint must be set on a line which actually does\n", " something (not a comment or docstring) for it to stop execution.\n", "\n", " Or you can specify a breakpoint in a different file::\n", "\n", " %run -d -b myotherfile.py:20 myscript\n", "\n", " When the pdb debugger starts, you will see a (Pdb) prompt. You must\n", " first enter 'c' (without quotes) to start execution up to the first\n", " breakpoint.\n", "\n", " Entering 'help' gives information about the use of the debugger. You\n", " can easily see pdb's full documentation with \"import pdb;pdb.help()\"\n", " at a prompt.\n", "\n", "-p\n", " run program under the control of the Python profiler module (which\n", " prints a detailed report of execution times, function calls, etc).\n", "\n", " You can pass other options after -p which affect the behavior of the\n", " profiler itself. See the docs for %prun for details.\n", "\n", " In this mode, the program's variables do NOT propagate back to the\n", " IPython interactive namespace (because they remain in the namespace\n", " where the profiler executes them).\n", "\n", " Internally this triggers a call to %prun, see its documentation for\n", " details on the options available specifically for profiling.\n", "\n", "There is one special usage for which the text above doesn't apply:\n", "if the filename ends with .ipy[nb], the file is run as ipython script,\n", "just as if the commands were written on IPython prompt.\n", "\n", "-m\n", " specify module name to load instead of script path. Similar to\n", " the -m option for the python interpreter. Use this option last if you\n", " want to combine with other %run options. Unlike the python interpreter\n", " only source modules are allowed no .pyc or .pyo files.\n", " For example::\n", "\n", " %run -m example\n", "\n", " will run the example module.\n", "\n", "-G\n", " disable shell-like glob expansion of arguments.\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\Admin\\Anaconda2\\lib\\site-packages\\IPython\\core\\magics\\execution.py:614: UserWarning: you must provide at least a filename.\n", " warn('you must provide at least a filename.')\n" ] } ], "source": [ "run\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 2", "language": "python", "name": "python2" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 2 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython2", "version": "2.7.13" } }, "nbformat": 4, "nbformat_minor": 2 }