ProcessDistributionFile.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This script is meant to process only ONE MONTH'S taxes in Distribution .txt files from the Tax Commission for FISCAL YEAR 2010 ONWARD.\n",
    "\n",
    "pygsheets can be installed with `pip install pygsheets`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import date\n",
    "import os\n",
    "import pygsheets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This cell immediately below is the only one that requires changes to run for a new month."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Read in Distribution to Finance .txt files from Tax Commission\n",
    "tax_distribution_file = './DistributionFiles/2018-07- DISTRIBUTION FILE TO FINANCE.txt'\n",
    "df = pd.read_fwf(tax_distribution_file, header=None, names=['Date', 'Tax', 'Location', 'Distribution'])\n",
    "    # dtype={'Date': str, 'Tax': str, 'Location':str, 'Distribution': str}"
   ]
  },
  {
   "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>Date</th>\n",
       "      <th>Tax</th>\n",
       "      <th>Location</th>\n",
       "      <th>Distribution</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>201807SEM</td>\n",
       "      <td>ES</td>\n",
       "      <td>01000Beaver County</td>\n",
       "      <td>656221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>201807SEM</td>\n",
       "      <td>ES</td>\n",
       "      <td>03038Logan</td>\n",
       "      <td>5581794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201807SEM</td>\n",
       "      <td>ES</td>\n",
       "      <td>06000Davis County</td>\n",
       "      <td>7881883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201807SEM</td>\n",
       "      <td>ES</td>\n",
       "      <td>06004Bountiful</td>\n",
       "      <td>4393324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201807SEM</td>\n",
       "      <td>ES</td>\n",
       "      <td>06008Clearfield</td>\n",
       "      <td>2603077</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date Tax            Location  Distribution\n",
       "0  201807SEM  ES  01000Beaver County        656221\n",
       "1  201807SEM  ES          03038Logan       5581794\n",
       "2  201807SEM  ES   06000Davis County       7881883\n",
       "3  201807SEM  ES      06004Bountiful       4393324\n",
       "4  201807SEM  ES     06008Clearfield       2603077"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 993 entries, 0 to 992\n",
      "Data columns (total 4 columns):\n",
      "Date            993 non-null object\n",
      "Tax             993 non-null object\n",
      "Location        993 non-null object\n",
      "Distribution    993 non-null int64\n",
      "dtypes: int64(1), object(3)\n",
      "memory usage: 31.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Translate obscure two letter tax codes to description\n",
    "Tax code info is stored in a [spreadsheet on Drive](https://docs.google.com/spreadsheets/d/1QA13HliH3YCPgPrmeDLbmw57KCjZbwreSsXGquqmsyU/edit?usp=sharing)\n",
    "\n",
    "To authorize with the google sheets api you will need to get [oauth client credentials](http://pygsheets.readthedocs.io/en/latest/authorizing.html#oauth-credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pygsheets\n",
    "# An oauth client_secret.json is needed in the same directory as this file.\n",
    "gc = pygsheets.authorize()\n",
    "# Open  a Google spreadsheet by drive ID\n",
    "spreadsheet = gc.open_by_key('1QA13HliH3YCPgPrmeDLbmw57KCjZbwreSsXGquqmsyU')\n",
    "sheet1 = spreadsheet.worksheet_by_title(\"Sheet1\")\n",
    "# Get all values of sheet as 2d list of cells\n",
    "\n",
    "# You can get a pandas dataframe if it is easier\n",
    "sheet_dataframe = sheet1.get_as_df(start=(1, 2), end=(48, 3))\n",
    "\n",
    "# This will create a dictionary with codes as the key\n",
    "cell_matrix = sheet1.get_values(start=(2, 2), end=(48, 3), returnas='matrix')\n",
    "code_lookup = {code: name for (code, name) in cell_matrix}\n",
    "for code in code_lookup:\n",
    "    df.replace(code, code_lookup[code], inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 993 entries, 0 to 992\n",
      "Data columns (total 5 columns):\n",
      "Date            993 non-null object\n",
      "Tax             993 non-null object\n",
      "Location        993 non-null object\n",
      "Distribution    993 non-null int64\n",
      "LocationCode    993 non-null int64\n",
      "dtypes: int64(2), object(3)\n",
      "memory usage: 38.9+ KB\n"
     ]
    }
   ],
   "source": [
    "#Separate Location Code from Location; remove 'SEM' from Date\n",
    "df['LocationCode'] = df['Location'].str[:5].apply(np.int64) \n",
    "df['Location'] = df['Location'].str[5:]\n",
    "df['Date'] = df['Date'].str[:-3]\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Date                       Tax               Location  Distribution  \\\n",
      "0    201807  E911 Emergency Services           Beaver County        656221   \n",
      "1    201807  E911 Emergency Services                   Logan       5581794   \n",
      "2    201807  E911 Emergency Services            Davis County       7881883   \n",
      "3    201807  E911 Emergency Services               Bountiful       4393324   \n",
      "4    201807  E911 Emergency Services              Clearfield       2603077   \n",
      "5    201807  E911 Emergency Services                  Layton       4206227   \n",
      "6    201807  E911 Emergency Services            Emery County        831114   \n",
      "7    201807  E911 Emergency Services         Garfield County        445578   \n",
      "8    201807  E911 Emergency Services            Grand County       1334939   \n",
      "9    201807  E911 Emergency Services             Juab County       1010879   \n",
      "10   201807  E911 Emergency Services             Kane County        721723   \n",
      "11   201807  E911 Emergency Services          Millard County       1090000   \n",
      "12   201807  E911 Emergency Services             Rich County        344047   \n",
      "13   201807  E911 Emergency Services          Salt Lake City      36085652   \n",
      "14   201807  E911 Emergency Services   Salt Lake County/VECC      61262933   \n",
      "15   201807  E911 Emergency Services         San Juan County       1437708   \n",
      "16   201807  E911 Emergency Services          Sanpete County       1267336   \n",
      "17   201807  E911 Emergency Services           Summit County       3737024   \n",
      "18   201807  E911 Emergency Services           Tooele County       4658209   \n",
      "19   201807  E911 Emergency Services             Utah County      14440322   \n",
      "20   201807  E911 Emergency Services                    Orem       5623198   \n",
      "21   201807  E911 Emergency Services          Pleasant Grove       1701947   \n",
      "22   201807  E911 Emergency Services                   Provo       6284194   \n",
      "23   201807  E911 Emergency Services             Springville       1903887   \n",
      "24   201807  E911 Emergency Services          Wasatch County       1674278   \n",
      "25   201807  E911 Emergency Services              St. George       9028633   \n",
      "26   201807  E911 Emergency Services            Weber County      19779446   \n",
      "27   201807         Municipal Telecom                    Roy       2332179   \n",
      "28   201807         Municipal Telecom            Centerville       1308288   \n",
      "29   201807         Municipal Telecom                   Moab        499995   \n",
      "..      ...                       ...                    ...           ...   \n",
      "963  201807  Municipal Transient Room              Richfield        522618   \n",
      "964  201807  Municipal Transient Room                 Salina        128237   \n",
      "965  201807  Municipal Transient Room            Tooele City        350696   \n",
      "966  201807  Municipal Transient Room                 Naples         17503   \n",
      "967  201807  Municipal Transient Room                 Vernal        358758   \n",
      "968  201807  Municipal Transient Room                Ballard        237582   \n",
      "969  201807  Municipal Transient Room                   Lehi       1430485   \n",
      "970  201807  Municipal Transient Room                   Orem       1013314   \n",
      "971  201807  Municipal Transient Room                 Payson         70177   \n",
      "972  201807  Municipal Transient Room                  Provo       2029688   \n",
      "973  201807  Municipal Transient Room            Springville        404405   \n",
      "974  201807  Municipal Transient Room                  Heber        223400   \n",
      "975  201807  Municipal Transient Room                 Midway        732341   \n",
      "976  201807  Municipal Transient Room              Hurricane        885118   \n",
      "977  201807  Municipal Transient Room                  Ivins       1081475   \n",
      "978  201807  Municipal Transient Room              La Verkin        552488   \n",
      "979  201807  Municipal Transient Room              Rockville          8366   \n",
      "980  201807  Municipal Transient Room             St. George       5160473   \n",
      "981  201807  Municipal Transient Room            Santa Clara        212948   \n",
      "982  201807  Municipal Transient Room             Springdale       2390170   \n",
      "983  201807  Municipal Transient Room            Toquerville         13023   \n",
      "984  201807  Municipal Transient Room                 Virgin        368338   \n",
      "985  201807  Municipal Transient Room             Hanksville         40706   \n",
      "986  201807  Municipal Transient Room              Farr West         48565   \n",
      "987  201807  Municipal Transient Room    Marriot-Slaterville        181133   \n",
      "988  201807  Municipal Transient Room                  Ogden       1111838   \n",
      "989  201807  Municipal Transient Room              Riverdale         86682   \n",
      "990  201807  Municipal Transient Room                 Uintah         66538   \n",
      "991  201807  Municipal Transient Room             West Haven        356666   \n",
      "992  201807    Tourism Transient Room       Salt Lake County      15452694   \n",
      "\n",
      "     LocationCode  \n",
      "0            1000  \n",
      "1            3038  \n",
      "2            6000  \n",
      "3            6004  \n",
      "4            6008  \n",
      "5            6030  \n",
      "6            8000  \n",
      "7            9000  \n",
      "8           10000  \n",
      "9           12000  \n",
      "10          13000  \n",
      "11          14000  \n",
      "12          17000  \n",
      "13          18122  \n",
      "14          18999  \n",
      "15          19000  \n",
      "16          20000  \n",
      "17          22000  \n",
      "18          23000  \n",
      "19          25000  \n",
      "20          25083  \n",
      "21          25088  \n",
      "22          25090  \n",
      "23          25106  \n",
      "24          26000  \n",
      "25          27020  \n",
      "26          29000  \n",
      "27          29037  \n",
      "28           6006  \n",
      "29          10011  \n",
      "..            ...  \n",
      "963         21034  \n",
      "964         21035  \n",
      "965         23048  \n",
      "966         24014  \n",
      "967         24024  \n",
      "968         24028  \n",
      "969         25066  \n",
      "970         25083  \n",
      "971         25085  \n",
      "972         25090  \n",
      "973         25106  \n",
      "974         26008  \n",
      "975         26011  \n",
      "976         27008  \n",
      "977         27010  \n",
      "978         27011  \n",
      "979         27019  \n",
      "980         27020  \n",
      "981         27021  \n",
      "982         27023  \n",
      "983         27024  \n",
      "984         27026  \n",
      "985         28005  \n",
      "986         29012  \n",
      "987         29022  \n",
      "988         29027  \n",
      "989         29036  \n",
      "990         29043  \n",
      "991         29051  \n",
      "992         18000  \n",
      "\n",
      "[993 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 993 entries, 0 to 992\n",
      "Data columns (total 5 columns):\n",
      "Date            993 non-null object\n",
      "Tax             993 non-null object\n",
      "Location        993 non-null object\n",
      "Distribution    993 non-null object\n",
      "LocationCode    993 non-null int64\n",
      "dtypes: int64(1), object(4)\n",
      "memory usage: 38.9+ KB\n"
     ]
    },
    {
     "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>Date</th>\n",
       "      <th>Tax</th>\n",
       "      <th>Location</th>\n",
       "      <th>Dollars_Distributed</th>\n",
       "      <th>LocationCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Beaver County</td>\n",
       "      <td>6562.21</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Logan</td>\n",
       "      <td>55817.94</td>\n",
       "      <td>3038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Davis County</td>\n",
       "      <td>78818.83</td>\n",
       "      <td>6000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Bountiful</td>\n",
       "      <td>43933.24</td>\n",
       "      <td>6004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Clearfield</td>\n",
       "      <td>26030.77</td>\n",
       "      <td>6008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Layton</td>\n",
       "      <td>42062.27</td>\n",
       "      <td>6030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Emery County</td>\n",
       "      <td>8311.14</td>\n",
       "      <td>8000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Garfield County</td>\n",
       "      <td>4455.78</td>\n",
       "      <td>9000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Grand County</td>\n",
       "      <td>13349.39</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Juab County</td>\n",
       "      <td>10108.79</td>\n",
       "      <td>12000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Kane County</td>\n",
       "      <td>7217.23</td>\n",
       "      <td>13000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Millard County</td>\n",
       "      <td>10900.00</td>\n",
       "      <td>14000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Rich County</td>\n",
       "      <td>3440.47</td>\n",
       "      <td>17000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Salt Lake City</td>\n",
       "      <td>360856.52</td>\n",
       "      <td>18122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>201807</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Salt Lake County/VECC</td>\n",
       "      <td>612629.33</td>\n",
       "      <td>18999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Date                       Tax               Location  \\\n",
       "0   201807  E911 Emergency Services           Beaver County   \n",
       "1   201807  E911 Emergency Services                   Logan   \n",
       "2   201807  E911 Emergency Services            Davis County   \n",
       "3   201807  E911 Emergency Services               Bountiful   \n",
       "4   201807  E911 Emergency Services              Clearfield   \n",
       "5   201807  E911 Emergency Services                  Layton   \n",
       "6   201807  E911 Emergency Services            Emery County   \n",
       "7   201807  E911 Emergency Services         Garfield County   \n",
       "8   201807  E911 Emergency Services            Grand County   \n",
       "9   201807  E911 Emergency Services             Juab County   \n",
       "10  201807  E911 Emergency Services             Kane County   \n",
       "11  201807  E911 Emergency Services          Millard County   \n",
       "12  201807  E911 Emergency Services             Rich County   \n",
       "13  201807  E911 Emergency Services          Salt Lake City   \n",
       "14  201807  E911 Emergency Services   Salt Lake County/VECC   \n",
       "\n",
       "    Dollars_Distributed  LocationCode  \n",
       "0               6562.21          1000  \n",
       "1              55817.94          3038  \n",
       "2              78818.83          6000  \n",
       "3              43933.24          6004  \n",
       "4              26030.77          6008  \n",
       "5              42062.27          6030  \n",
       "6               8311.14          8000  \n",
       "7               4455.78          9000  \n",
       "8              13349.39         10000  \n",
       "9              10108.79         12000  \n",
       "10              7217.23         13000  \n",
       "11             10900.00         14000  \n",
       "12              3440.47         17000  \n",
       "13            360856.52         18122  \n",
       "14            612629.33         18999  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Insert decimal points into Distribution amounts; rename column\n",
    "df['Distribution'] = df['Distribution'].astype(str)\n",
    "df.info()\n",
    "df['Distribution'] = df['Distribution'].str[:-2] + \".\" + df['Distribution'].str[-2:]\n",
    "df['Distribution'] = df['Distribution'].astype(np.float64)\n",
    "df.rename(columns = {'Distribution': 'Dollars_Distributed'}, inplace=True)\n",
    "df.head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'str'>\n",
      "201807\n"
     ]
    }
   ],
   "source": [
    "#Grab a single date from Date \n",
    "year_month = df.iloc[0]['Date']\n",
    "print(type(year_month))\n",
    "print(year_month)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'int'>\n",
      "2018\n",
      "<class 'int'>\n",
      "7\n"
     ]
    }
   ],
   "source": [
    "#Extract year and month individually\n",
    "year = int(year_month[:-2])\n",
    "print(type(year))\n",
    "print(year)\n",
    "month = int(year_month[-2:])\n",
    "print(type(month))\n",
    "print(month)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Convert the Date to Calendar from Fiscal year\n",
    "    #FY 2010 month 1 = July 2009\n",
    "df.rename(columns = {'Date': 'Date_CY'}, inplace=True)\n",
    "\n",
    "if month == 1:\n",
    "    month = 7\n",
    "    year = year - 1\n",
    "elif month == 2:\n",
    "    month = 8\n",
    "    year = year - 1\n",
    "elif month == 3:\n",
    "    month = 9\n",
    "    year = year - 1\n",
    "elif month == 4:\n",
    "    month = 10\n",
    "    year = year - 1\n",
    "elif month == 5:\n",
    "    month = 11\n",
    "    year = year - 1\n",
    "elif month == 6:\n",
    "    month = 12\n",
    "    year = year - 1\n",
    "    \n",
    "elif month == 7:\n",
    "    month = 1\n",
    "elif month == 8:\n",
    "    month = 2\n",
    "elif month == 9:\n",
    "    month = 3\n",
    "elif month == 10:\n",
    "    month = 4\n",
    "elif month == 11:\n",
    "    month = 5\n",
    "elif month == 12:\n",
    "    month = 6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2018-01-01\n"
     ]
    }
   ],
   "source": [
    "#Reformat the date\n",
    "when = date(year, month, 1)\n",
    "print(when)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Apply the formatted date object to entire Date column\n",
    "df['Date_CY'] = when"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Date_CY</th>\n",
       "      <th>Tax</th>\n",
       "      <th>Location</th>\n",
       "      <th>Dollars_Distributed</th>\n",
       "      <th>LocationCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Beaver County</td>\n",
       "      <td>6562.21</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Logan</td>\n",
       "      <td>55817.94</td>\n",
       "      <td>3038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Davis County</td>\n",
       "      <td>78818.83</td>\n",
       "      <td>6000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Bountiful</td>\n",
       "      <td>43933.24</td>\n",
       "      <td>6004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Clearfield</td>\n",
       "      <td>26030.77</td>\n",
       "      <td>6008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Layton</td>\n",
       "      <td>42062.27</td>\n",
       "      <td>6030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Emery County</td>\n",
       "      <td>8311.14</td>\n",
       "      <td>8000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Garfield County</td>\n",
       "      <td>4455.78</td>\n",
       "      <td>9000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Grand County</td>\n",
       "      <td>13349.39</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>E911 Emergency Services</td>\n",
       "      <td>Juab County</td>\n",
       "      <td>10108.79</td>\n",
       "      <td>12000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Date_CY                       Tax         Location  Dollars_Distributed  \\\n",
       "0  2018-01-01  E911 Emergency Services     Beaver County              6562.21   \n",
       "1  2018-01-01  E911 Emergency Services             Logan             55817.94   \n",
       "2  2018-01-01  E911 Emergency Services      Davis County             78818.83   \n",
       "3  2018-01-01  E911 Emergency Services         Bountiful             43933.24   \n",
       "4  2018-01-01  E911 Emergency Services        Clearfield             26030.77   \n",
       "5  2018-01-01  E911 Emergency Services            Layton             42062.27   \n",
       "6  2018-01-01  E911 Emergency Services      Emery County              8311.14   \n",
       "7  2018-01-01  E911 Emergency Services   Garfield County              4455.78   \n",
       "8  2018-01-01  E911 Emergency Services      Grand County             13349.39   \n",
       "9  2018-01-01  E911 Emergency Services       Juab County             10108.79   \n",
       "\n",
       "   LocationCode  \n",
       "0          1000  \n",
       "1          3038  \n",
       "2          6000  \n",
       "3          6004  \n",
       "4          6008  \n",
       "5          6030  \n",
       "6          8000  \n",
       "7          9000  \n",
       "8         10000  \n",
       "9         12000  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-3-10a1739cb651>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mos\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpath\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexists\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'./OutputCsv'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m     \u001b[0mos\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmkdir\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'./OutputCsv'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0mdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'./OutputCsv/{}_{}_sales_taxes.csv'\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0myear\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmonth\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'df' is not defined"
     ]
    }
   ],
   "source": [
    "if not os.path.exists('./OutputCsv'):\n",
    "    os.mkdir('./OutputCsv')\n",
    "df.to_csv('./OutputCsv/{}_{}_sales_taxes.csv'.format(year, month))"
   ]
  },
  {
   "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.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}