PDF to Excel with Tabula.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install tabula-py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tabula\n",
    "import pandas as pd\n",
    "import itertools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_column_list(dataframe_obj):\n",
    "    '''\n",
    "    Puts all the column names from a list of dataframes into a list\n",
    "    \n",
    "    input: list of dataframes\n",
    "    output: lists of column names from all the dataframes\n",
    "    '''\n",
    "    columns = [list(dataframe_obj[i].columns) for i in range(len(dataframe_obj))]\n",
    "    return columns\n",
    "\n",
    "def get_series_list(dataframe_obj, columns_list):\n",
    "    '''\n",
    "    Turns all the columns in each of the dataframes to series and puts them all in a list\n",
    "    \n",
    "    input: list of dataframes and the column list from get_column_list\n",
    "    output: list of panda series objects\n",
    "    '''\n",
    "    series_list = [dataframe_obj[i] for i in columns_list]\n",
    "    series_list2 = [series_list[i].dropna() for i in range(len(series_list))]\n",
    "    return series_list2\n",
    "\n",
    "def append_columns(filtered_columns, to_series):\n",
    "    '''\n",
    "    Appends items to a pandas series object\n",
    "    \n",
    "    input: items to append and an object to append them to\n",
    "    output: a series object with the appended items\n",
    "    '''\n",
    "    to_append = pd.Series(filtered_columns) \n",
    "    return to_series.append(to_append, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Will output a list of dataframes (1 for each table in the pdf) BUT\n",
    "1) The table has lost nearly all its organization\n",
    "\n",
    "2) Some of the column names should actually be row values\n",
    "\n",
    "3) NaN values litter the output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df = tabula.read_pdf(r\"C:\\Users\\rayde\\Downloads\\2020-2021-Approved-Stock-List-20200926.pdf\", pages = 'all', lattice = False, multiple_tables=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get the list of columns in each of the dataframes \n",
    "columns_list = get_column_list(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Because Tabula outputs messy data for this pdf, we will need to: \n",
    "    1. Turn each dataframe into a list of pandas series objects to remove the columns (We only want one column)\n",
    "    2. Use pd.concat to rebuild the series objects into pandas objects with only one column\n",
    "\n",
    "##### The cell below turns each dataframe into a list of series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "series_list = [get_series_list(df[x], columns_list[x]) for x in range(len(df))]\n",
    "series_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Run pd.concat on each list of series and then again on the output\n",
    "4. Once we run pd.concat for the second time, we will have a dataframe with only one column \n",
    "\n",
    "##### The cell below turns our list of list of pandas series objects into one pandas object with one column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                      Co\n",
       "1     Acorn International\n",
       "2                     Inc\n",
       "3          Amazon.com Inc\n",
       "4          American Eagle\n",
       "             ...         \n",
       "30             Utilities:\n",
       "31     Telecommunications\n",
       "32             Utilities:\n",
       "33     Telecommunications\n",
       "34             Utilities:\n",
       "Length: 2534, dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = list(map(lambda x: pd.concat(series_list[x], ignore_index=True), range(len(series_list))))\n",
    "data = pd.concat(data)\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Two filters\n",
    "3. Filter the series by length<7 because the tickers all have less than seven characters (Some of the tickers are quite long)\n",
    "4. Filter by words we don't want that are less than seven characters in length.\n",
    "\n",
    "##### The cell below filters data by character length and words less than 7 characters that we don't want in our list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32     ATV\n",
       "33    AMZN\n",
       "34     AEO\n",
       "35    APTV\n",
       "36     AVP\n",
       "      ... \n",
       "2      PLC\n",
       "8      UU.\n",
       "9       VZ\n",
       "10     VOD\n",
       "11     VOD\n",
       "Length: 406, dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bad_words = [\"Retail\", \"Media\", \"Foods\", \"Nasdaq\", \"EQUITY\", \"Cement\", \"Copper\", \"ENERGY\", \"Inc\", \"Co\", \"STYLE\", \"TICKER\", \"Balcão\"]\n",
    "filtered_data = data[(data.str.len()<7) & (data.isin(bad_words)==False)]\n",
    "filtered_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Tabula took some of the tickers from the PDF file and assigned them to column names. \n",
    "We want to make sure we don't leave any of the tickers out. So, we also need to: \n",
    "5. Filter the column list by bad words and length less than 7 \n",
    "6. Append to the filtered_data pandas series object.\n",
    "\n",
    "##### The cell below uses itertools to unzip the list of lists of column names into one list and then filters it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "Unzips the column list and puts all the column names into one list (versus having a list of lists)\n",
    "'''\n",
    "\n",
    "columns_one_list = list(itertools.chain.from_iterable(columns_list)) #itertools will turn the list of list into one list\n",
    "columns_list_filtered = [x for x in columns_one_list if (len(x) < 7) & (x not in bad_words)] #Filter by less than 7 and bad words\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#append the columns to the filtered_data_series\n",
    "final_lists = append_columns(columns_list_filtered, filtered_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        ATV\n",
       "1       AMZN\n",
       "2        AEO\n",
       "3       APTV\n",
       "4        AVP\n",
       "       ...  \n",
       "423    RAIL3\n",
       "424     ADBE\n",
       "425     ANET\n",
       "426      AEP\n",
       "427    EGIE3\n",
       "Length: 428, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_lists"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### There are duplicates in our pandas series\n",
    "7. Remove duplicate values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "404    VOD\n",
       "405    VOD\n",
       "dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_lists[final_lists=='VOD']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#We need to drop duplicates\n",
    "final_lists = final_lists.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        ATV\n",
       "1       AMZN\n",
       "2        AEO\n",
       "3       APTV\n",
       "4        AVP\n",
       "       ...  \n",
       "423    RAIL3\n",
       "424     ADBE\n",
       "425     ANET\n",
       "426      AEP\n",
       "427    EGIE3\n",
       "Length: 346, dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_lists.to_excel('stocklist.xlsx')"
   ]
  },
  {
   "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.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}