FA FY 25 Budget Balances Tracking.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8e2c1091",
   "metadata": {},
   "source": [
    "# FA FY 25 Budget Balance Tracking\n",
    "\n",
    "Using the [tabula-py](https://pypi.org/project/tabula-py/) package to extract data from PDFs for campus budget analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "69c59009",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# importing packages\n",
    "import tabula \n",
    "from tabula import read_pdf\n",
    "import os\n",
    "import pandas as pd\n",
    "import string as str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c497bdd8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# setting JAVA_HOME and PATH variables\n",
    "os.environ['JAVA_HOME'] = '/Users/scipio/Documents/FA_Budget_PDFs/jdk-22.0.2.jdk/Contents/Home'\n",
    "os.environ['PATH'] = '/Users/scipio/Documents/FA_Budget_PDFs/jdk-22.0.2.jdk/Contents/Home/lib/server'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4019451d",
   "metadata": {},
   "source": [
    "## JAVA_HOME and PATH\n",
    "\n",
    "The tabula-py package is a wrapper of [tabula-java](https://github.com/tabulapdf/tabula-java) and requires java on your machine. I recommend installing [OpenJDK](https://jdk.java.net/22/) and setting the JAVA_HOME and PATH enivronment variables as demonstrated above using the os package which creates an instance of the JAVA_HOME and PATH variables. For a more permanent instance set the environment variables in the command prompt (Windows) or terminal (Mac)\n",
    "\n",
    "- The **JAVA_HOME** variable points to the installation location of OpenJDK\n",
    "- The **PATH** varible points to the location of the Java Virtual Machine (JVM DLL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6dbfb44b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing data from PDF for each campus\n",
    "df_es_aug = pd.DataFrame(tabula.read_pdf('/Users/scipio/Documents/FA_Budget_PDFs/ES 08.31.24 Budget.PDF',\n",
    "                                         pages = 'all',pandas_options = {'header': None})[0])\n",
    "\n",
    "df_ms_aug = pd.DataFrame(tabula.read_pdf('/Users/scipio/Documents/FA_Budget_PDFs/MS 08.31.24 Budget.PDF', \n",
    "                                         pages = 'all',pandas_options = {'header': None})[0])\n",
    "\n",
    "df_hs_aug = pd.DataFrame(tabula.read_pdf('/Users/scipio/Documents/FA_Budget_PDFs/HS 08.31.24 Budget.PDF',\n",
    "                                         pages = 'all',pandas_options = {'header': None})[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fefc1356",
   "metadata": {},
   "source": [
    "## Data importing protocol\n",
    "\n",
    "- Import data for the month and name df_campus_month format, e.g. df_es_sept\n",
    "- Import export from previous month\n",
    "- Clean data\n",
    "- Concat cleaned data from previous and current months\n",
    "- Export\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e9e88beb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename object\n",
    "rename = {\n",
    "    0:'Account Title',\n",
    "    1:'Delete',\n",
    "    2:'Original Bgt',\n",
    "    3:'New App/Trnsf',\n",
    "    4:'Revised Bgt',\n",
    "    5:'Expenditures',\n",
    "    6:'Encumbrances',\n",
    "    7:'Avail Balance',\n",
    "    8:'% Spent'}\n",
    "\n",
    "# renaming columns\n",
    "df_es_aug.rename(columns = rename, inplace = True)\n",
    "df_ms_aug.rename(columns = rename, inplace = True)\n",
    "df_hs_aug.rename(columns = rename, inplace = True)\n",
    "\n",
    "# data cleaning\n",
    "df_es_aug['Expend. Account #'] = df_es_aug['Account Title'].str.split(' ').str[0]\n",
    "df_ms_aug['Expend. Account #'] = df_ms_aug['Account Title'].str.split(' ').str[0]\n",
    "df_hs_aug['Expend. Account #'] = df_hs_aug['Account Title'].str.split(' ').str[0]\n",
    "\n",
    "# adding month column \n",
    "df_es_aug['Month'] = 8\n",
    "df_ms_aug['Month'] = 8\n",
    "df_hs_aug['Month'] = 8\n",
    "\n",
    "# adding campus column\n",
    "df_es_aug['Campus'] = 'ES'\n",
    "df_ms_aug['Campus'] = 'MS'\n",
    "df_hs_aug['Campus'] = 'HS'\n",
    "\n",
    "#dropping irrelevant columns\n",
    "df_es_aug.drop(labels = ['Delete','% Spent'], axis = 1, inplace = True)\n",
    "df_ms_aug.drop(labels = ['Delete','% Spent'], axis = 1, inplace = True)\n",
    "df_hs_aug.drop(labels = ['Delete','% Spent'], axis = 1, inplace = True)\n",
    "\n",
    "# data casting\n",
    "df_es_aug['Expenditures'] = df_es_aug['Expenditures'].str.replace(',','').astype('float')\n",
    "df_ms_aug['Expenditures'] = df_ms_aug['Expenditures'].str.replace(',','').astype('float')\n",
    "df_hs_aug['Expenditures'] = df_hs_aug['Expenditures'].str.replace(',','').astype('float')\n",
    "\n",
    "\n",
    "# creating column object for filtering\n",
    "columns = ['Campus','Month','Original Bgt',\n",
    "           'New App/Trnsf','Revised Bgt','Expenditures',\n",
    "           'Avail Balance']\n",
    "\n",
    "# returning relevant columns\n",
    "df_es_aug = df_es_aug[df_es_aug['Account Title']=='Grand Totals for fund:11'][columns]#--> filtering using columns\n",
    "df_ms_aug = df_ms_aug[df_ms_aug['Account Title']=='Grand Totals for fund:11'][columns]#--> filtering using columns\n",
    "df_hs_aug = df_hs_aug[df_hs_aug['Account Title']=='Grand Totals for fund:11'][columns]#--> filtering using columns\n",
    "\n",
    "# calculating MoM diff\n",
    "df_es_aug['Expense Change'] = df_es_aug['Expenditures'].diff()\n",
    "df_ms_aug['Expense Change'] = df_ms_aug['Expenditures'].diff()\n",
    "df_hs_aug['Expense Change'] = df_hs_aug['Expenditures'].diff() \n",
    "\n",
    "# concating dfs\n",
    "df_aug_agg = pd.concat([df_es_aug,df_ms_aug,df_hs_aug])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1f085b0f",
   "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>Campus</th>\n",
       "      <th>Month</th>\n",
       "      <th>Original Bgt</th>\n",
       "      <th>New App/Trnsf</th>\n",
       "      <th>Revised Bgt</th>\n",
       "      <th>Expenditures</th>\n",
       "      <th>Avail Balance</th>\n",
       "      <th>Expense Change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>ES</td>\n",
       "      <td>8</td>\n",
       "      <td>4,515,612.00</td>\n",
       "      <td>(234,870.28)</td>\n",
       "      <td>4,280,741.72</td>\n",
       "      <td>475951.86</td>\n",
       "      <td>289,619.27</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>HS</td>\n",
       "      <td>8</td>\n",
       "      <td>3,562,807.00</td>\n",
       "      <td>(202,075.00)</td>\n",
       "      <td>3,360,732.00</td>\n",
       "      <td>403213.15</td>\n",
       "      <td>226,619.06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>MS</td>\n",
       "      <td>8</td>\n",
       "      <td>2,629,211.00</td>\n",
       "      <td>(107,793.00)</td>\n",
       "      <td>2,521,418.00</td>\n",
       "      <td>255698.72</td>\n",
       "      <td>252,058.94</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Campus  Month  Original Bgt New App/Trnsf   Revised Bgt  Expenditures  \\\n",
       "37     ES      8  4,515,612.00  (234,870.28)  4,280,741.72     475951.86   \n",
       "43     HS      8  3,562,807.00  (202,075.00)  3,360,732.00     403213.15   \n",
       "41     MS      8  2,629,211.00  (107,793.00)  2,521,418.00     255698.72   \n",
       "\n",
       "   Avail Balance  Expense Change  \n",
       "37    289,619.27             NaN  \n",
       "43    226,619.06             NaN  \n",
       "41    252,058.94             NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# displaying\n",
    "df_aug_agg.sort_values(by = ['Campus','Month'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3bcc065a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# object for filtering for export\n",
    "rel_cols = ['Month','Campus','Expenditures','Expense Change','Avail Balance']\n",
    "\n",
    "#exporting data - August\n",
    "df_aug_agg[rel_cols].to_excel('/Users/scipio/Documents/FA_Budget_PDFs/Campus_Budgets_Aug.xlsx',\n",
    "                              index = False, header = True )"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}