fsa_approved_premises-v2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of FSA approved food establishments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <span style=\"color:red\">Please remember to restart and clear outputs before saving and pushing to GitHub repository.</span>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overview\n",
    "This workbook contains code that retrieves the slaughthouse details from FSA monthly updates of approved premises. The code opens each monthly file consecutively and compares with data that exists in the database. If the name of the slaughthouse name is unchanged, the details that are retained are the latest versions. However, if the slaughterhouse name has changed then the original details are retained and the new data is added. The data is than uploaded to a localhost MySQL database as an example of how data can be used.\n",
    "\n",
    "The monthly updates of approved food establishments are produced by FSA and are available at: https://www.food.gov.uk/business-guidance/approved-food-establishments#list-of-approved-food-establishments.\n",
    "\n",
    "Processing of FSA monthly updates of approved food establishments is covered by Open Government Licence v3 (see: http://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/).\n",
    "\n",
    "### Version history\n",
    "#### May 2020\n",
    "Original version of ```fsa_approved_premises-v2.ipynb```. The code only retains details of appno premises where 'slaughterhouse' field equals 'yes'. Attempts to determine whether the name of the abattoir has changed are based on calculating the Jaccard Distance.\n",
    "\n",
    "#### 17 Nov 2020\n",
    "Some abattoir data refers to an appno that is not included in the extracted data. In all cases, the slaughterhouse field was set to NULL but the list of 'All Activities' contained the phrase 'Game Handling Establishment (Wild Game)'. Code edited so that all the extracted of premises included those abattoirs where slaughterhouse field equalled 'yes' or allactivities field contained 'game handling establishment \\(wild game\\)'. Also needed to edit MySQL data to all slaughterhouse field to contain NULL.\n",
    "\n",
    "#### 20 Nov 2020\n",
    "On some occasions, it may be beneficial to have a list of just the latest appno details, rather than a record of all the occasions when abattoirs change owners. This modification downloads the data from the 'abattoir' table and retains only the latest details for each appno entry. The output is saved as csv and pkl files called ```phjLatestAppnoDetails.csv``` and ```phjLatestAppnoDetails.pkl```.\n",
    "\n",
    "#### 14 Jan 2022\n",
    "Starting in Jan 2018, the naming convention for monthly update of approved premises took the format ```approved-food-establishments-as-at-1-MONTH-YEAR.csv```. This continued until Sept 2021. However, thereafter the naming convention became a bit more random with:\n",
    "1. ```ApprovedPremisesOct21.csv```\n",
    "2. ```APMSNov21.csv```\n",
    "3. ```Approved+establishments+01-12-2021.csv```\n",
    "4. ```ApprovedPremisesJan22.csv```\n",
    "\n",
    "When the naming convention was consistent, the list of filenames could be created from month and year values. However, with different naming conventions, the filenames needed to be extracted from the directory and arranged into time order. Also, as a result of the different name formats, the month and year details extracted from the filenames were different from original version. This resulted in errors when running the code. Therefore, additional code was required to convert extracted month and year details to the same format as would have been extracted from a filename in the original format. These edits were completed 16 Feb 2022.\n",
    "\n",
    "#### 03 Mar 2023\n",
    "Latest updates of approved establishments files were of several different naming conventions. It was necessary to add regexes to be able to extract information (namely month and year) from the filename.\n",
    "\n",
    "Also, two files (namely, ApprovedFoodEstAug22.csv and ApprovedFoodEstOct22.csv) did not contain column headings on row zero; instead, column headings were on row 1. Added code to read column headings from row 1 for these two files, otherwise read column headings from row 0.\n",
    "\n",
    "Some of the latest files cause an error when being imported into Pandas dataframe (`UnicodeDecodeError: 'utf-8' codec can't decode byte 0x92 in position 7: invalid start byte`). Those files were likely to be encoded using `cp1252`. In cp1252, the 0x92 character is a curly apostrophy and occurs in names such as 'Pilgrim's Pride' (appno 5091). As a result, for affected files, the encoding needs to be set to cp1252.\n",
    "\n",
    "#### 25 Sep 2023\n",
    "Added ISO-3166 country codes to phjCountryDF dataframe, just for completeness.\n",
    "\n",
    "Edited filename regex to match names of format ApprovedFoodEstablishmentsApr23."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import required libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import regex\n",
    "import collections\n",
    "import getpass\n",
    "from pathlib import Path\n",
    "import os\n",
    "import calendar\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import nltk\n",
    "#nltk.set_proxy(\"enter-proxy:port\") # ...if needed\n",
    "nltk.download('punkt')\n",
    "import epydemiology as epy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "import sqlalchemy\n",
    "#engine = sqlalchemy.create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define functions that will combine lists of FSA approved premises\n",
    "List needs to order files in chronological order so that the most recent file represents the most up-to-date information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def phjUpdateData(phjOriginalDF,\n",
    "                  phjNewDF,\n",
    "                  phjColsOfInterestList,\n",
    "                  phjIDColName = 'id',\n",
    "                  phjYrColName = 'yr',\n",
    "                  phjMthColName = 'mth',\n",
    "                  phjGrpColName = 'grp',\n",
    "                  phjNameColName = 'name',\n",
    "                  phjCountColName = 'count',\n",
    "                  phjTokColName = 'tokens',\n",
    "                  phjPrevTokColName = 'prevtokens',\n",
    "                  phjJaccardColName = 'jd',\n",
    "                  phjMatchColName = 'match',\n",
    "                  phjPrintResults = False):\n",
    "    \n",
    "    # Other things to do:\n",
    "    # • Check that cols of interest appear in both dataframes\n",
    "    # • Check that year and month columns already appear in original DF\n",
    "    # • Check no duplicates in phjGrpColName columns\n",
    "    \n",
    "\n",
    "    # Concatenate data frames and sort by appno, year and month to ensure\n",
    "    # earliest rows occur as first row in each group.\n",
    "    phjConcatDF = pd.concat([phjOriginalDF,phjNewDF],sort = False).sort_values(by = [phjGrpColName,\n",
    "                                                                                     phjYrColName,\n",
    "                                                                                     phjMthColName])\n",
    "\n",
    "    # Reset index to make sure no duplicated values in index\n",
    "    phjConcatDF = phjConcatDF.reset_index(drop = True)\n",
    "\n",
    "    # Add count column to indicate how many rows in each groupby group\n",
    "    phjConcatDF[phjCountColName] = phjConcatDF.groupby(phjGrpColName)[phjGrpColName].transform('count')\n",
    "    \n",
    "    if phjPrintResults == True:\n",
    "        print('Concatenated dataframe\\n======================')\n",
    "        print(phjConcatDF)\n",
    "        print('\\n')\n",
    "\n",
    "        print('Groups that appear only once (i.e. occur only in either original or new dataframes')\n",
    "        print('==================================================================================')\n",
    "        print(phjConcatDF.loc[phjConcatDF[phjCountColName] < 2,:])\n",
    "        print('\\n')\n",
    "\n",
    "        print('Groups that appear more than twice (i.e. already two occurrences of group in database')\n",
    "        print('=====================================================================================')\n",
    "        print(phjConcatDF.loc[phjConcatDF[phjCountColName] > 2,:])\n",
    "        print('\\n')\n",
    "\n",
    "    # Create mask indicating the first rows of any groups with more than two rows, leaving the last\n",
    "    # two rows only for further processing.\n",
    "    # Suggestion for this method given as answer by Nick Becker in response to question at:\n",
    "    # https://stackoverflow.com/questions/59761547/create-mask-to-identify-final-two-rows-in-groups-in-pandas-dataframe\n",
    "    phjLast2RowsMask = phjConcatDF.index.isin(phjConcatDF.groupby(phjGrpColName).tail(2).index)\n",
    "\n",
    "    # Add columns of tokens and tokens from previous rows\n",
    "    phjLast2RowsDF = phjAddColOfTokens(phjDF = phjConcatDF[phjLast2RowsMask],\n",
    "                                       phjNameColName = phjNameColName,\n",
    "                                       phjTokColName = phjTokColName,\n",
    "                                       phjPrevTokColName = phjPrevTokColName,\n",
    "                                       phjGrpColName = phjGrpColName,\n",
    "                                       phjYrColName = phjYrColName,\n",
    "                                       phjMthColName = phjMthColName,\n",
    "                                       phjPrintResults = phjPrintResults)\n",
    "    \n",
    "    if phjPrintResults == True:\n",
    "        print('Token columns added (only last 2 rows of each group retained)\\n===================')\n",
    "        print(phjLast2RowsDF)\n",
    "        print('\\n')\n",
    "    \n",
    "    # Compare tokens and previous tokens using Jaccard distance\n",
    "    phjLast2RowsDF = phjCompareTokCols(phjDF = phjLast2RowsDF,\n",
    "                                       phjGrpColName = phjGrpColName,\n",
    "                                       phjTokColName = phjTokColName,\n",
    "                                       phjPrevTokColName = phjPrevTokColName,\n",
    "                                       phjJaccardColName = phjJaccardColName,\n",
    "                                       phjPrintResults = phjPrintResults)\n",
    "     \n",
    "    # Define names that have changed\n",
    "    phjLast2RowsDF = phjDefineChangedNames(phjDF = phjLast2RowsDF,\n",
    "                                           phjGrpColName = phjGrpColName,\n",
    "                                           phjNameColName = phjNameColName,\n",
    "                                           phjJaccardColName = phjJaccardColName,\n",
    "                                           phjMatchColName = phjMatchColName,\n",
    "                                           phjCutOffValue = 0.5,\n",
    "                                           phjPrintResults = phjPrintResults)\n",
    "    \n",
    "    # Remove unchanged names\n",
    "    phjLast2RowsDF = phjRemoveUnchanged(phjDF = phjLast2RowsDF,\n",
    "                                        phjIDColName = phjIDColName,\n",
    "                                        phjGrpColName = phjGrpColName,\n",
    "                                        phjMatchColName = phjMatchColName,\n",
    "                                        phjCountColName = phjCountColName,\n",
    "                                        phjPrintResults = phjPrintResults)\n",
    "    \n",
    "    # Re-attach the first rows from groups with more than 2 rows\n",
    "    phjConcatDF = phjConcatDF[~phjLast2RowsMask].append(phjLast2RowsDF,\n",
    "                                                        ignore_index = False,\n",
    "                                                        sort = False)\n",
    "    \n",
    "    if phjPrintResults == True:\n",
    "        print('Recombined dataframes\\n=====================')\n",
    "        print(phjConcatDF)\n",
    "        print('\\n')\n",
    "\n",
    "    # Update id column\n",
    "    phjConcatDF = phjUpdateID(phjDF = phjConcatDF,\n",
    "                              phjIDColName = phjIDColName,\n",
    "                              phjYrColName = phjYrColName,\n",
    "                              phjMthColName = phjMthColName,\n",
    "                              phjPrintResults = phjPrintResults)\n",
    "    \n",
    "    # Some final corrections...\n",
    "    phjConcatDF[phjIDColName] = phjConcatDF[phjIDColName].astype('int')\n",
    "    phjConcatDF[phjYrColName] = phjConcatDF[phjYrColName].astype('int')\n",
    "    phjConcatDF[phjMthColName] = phjConcatDF[phjMthColName].astype('int')\n",
    "    phjConcatDF = phjConcatDF.reset_index(drop = True)\n",
    "    phjConcatDF = phjConcatDF.sort_values(by = [phjGrpColName,phjYrColName,phjMthColName])\n",
    "    \n",
    "    if phjPrintResults == True:\n",
    "        print('Final returned dataframe\\n===================')\n",
    "        print(phjConcatDF)\n",
    "        print('\\n')\n",
    "\n",
    "        \n",
    "    return phjConcatDF\n",
    "\n",
    "\n",
    "\n",
    "# This function does some preprocessing to ensure only data of interest is retained and that minor\n",
    "# anomalies in data entry are addressed (e.g. upper and lower case, punction marks, white space, etc.)\n",
    "# Function edited to allow functionality to be applied to an empty dataframe consisting of column\n",
    "# headers only by setting defaults for some function arguments to None so that no attempt is made to\n",
    "# edit contents of columns.\n",
    "# Added function argument to delete columns that have been renamed (i.e. postcode, country)\n",
    "def phjPreProcess(phjDF,\n",
    "                  phjCountryDF = None,\n",
    "                  phjCountryMissCode = None,\n",
    "                  phjColsOfInterestList = None,\n",
    "                  phjDeleteOrigCols = True,\n",
    "                  phjPrintResults = False):\n",
    "    \n",
    "    # Convert column names to lowercase, remove spaces, underscores and '???' characters ... just for consistency\n",
    "    # (For some reason, some files (e.g. Dec2019) used underscores and the AppNo column in Dec 2019 was called '???AppNo')\n",
    "    phjDF.columns = phjDF.columns.str.replace(' ','').str.replace('_','').str.replace('\\?\\?\\?','').str.lower()\n",
    "\n",
    "    # Retain only the columns of interest\n",
    "    if phjColsOfInterestList is not None:\n",
    "        phjDF = phjDF[phjColsOfInterestList].copy()\n",
    "    \n",
    "    # Only retain slaughterhouse approved premises and premises\n",
    "    # where 'allactivities' column contains 'game handling establishment (wild game)'\n",
    "    phjDF['slaughterhouse'] = phjDF['slaughterhouse'].str.lower()\n",
    "    phjDF['allactivities'] = phjDF['allactivities'].str.lower()\n",
    "    phjDF = phjDF.loc[((phjDF['slaughterhouse'] == 'yes') |\n",
    "                       (phjDF['allactivities'].str.contains('game handling establishment \\(wild game\\)'))),:].copy()\n",
    "    \n",
    "    # Remove whitespace from front and back of trading name column\n",
    "    phjDF['tradingname'] = phjDF['tradingname'].str.strip()\n",
    "    \n",
    "    # Format the appno column to make uppercase and remove white space\n",
    "    phjDF['appno'] = phjDF['appno'].str.upper().str.replace('[^\\w]','')\n",
    "    \n",
    "    # Add a column that contains the trading name but in lower case and with all punctuation\n",
    "    # and spaces removed. Also, '&' is converted to 'and', 'ltd' to 'limited' and\n",
    "    # 'Bros' to 'Brothers'. This ensures that subtle variations in the way the TradingName\n",
    "    # is written won't be misinterpreted as a different company.\n",
    "    # Actually probably not needed and not used - but leave for the moment\n",
    "    phjDF['tradingname_lcase'] = phjDF['tradingname'].str.replace('&','and').str.replace('[Ll]td','limited').str.replace('[Bb]ros','Brothers').str.lower().str.replace('[^\\w]','')\n",
    "\n",
    "    # Add column with postcode formatted to 7 characters\n",
    "    phjDF = epy.phjPostcodeFormat7(phjDF = phjDF,\n",
    "                                   phjPostcodeVarName = 'postcode',\n",
    "                                   phjPostcodeCheckVarName = None,\n",
    "                                   phjPostcode7VarName = 'postcode7',\n",
    "                                   phjPrintResults = False)\n",
    "\n",
    "    # Display all countries contained in file\n",
    "    phjDF['country'].value_counts()\n",
    "    \n",
    "    # Replace country names with country codes defined in phjCountryDF\n",
    "    if phjCountryDF is not None:\n",
    "        phjDF = pd.merge(phjDF,\n",
    "                         phjCountryDF,\n",
    "                         on = \"country\",\n",
    "                         how = 'left')\n",
    "    \n",
    "    if phjCountryMissCode is not None:\n",
    "        phjDF['country_id'] = phjDF['country_id'].fillna(phjCountryMissCode)\n",
    "    \n",
    "    # Remove original postcode and country columns\n",
    "    if phjDeleteOrigCols == True:\n",
    "        phjDF = phjDF[[col for col in list(phjDF.columns) if col not in ['postcode','country']]].copy()\n",
    "\n",
    "    return phjDF\n",
    "\n",
    "\n",
    "\n",
    "# Combine two dataframes ensuring that if there are any repeated rows, only the latest version is retained.\n",
    "\n",
    "def phjRetrieveOrigData():\n",
    "    \n",
    "    return None\n",
    "\n",
    "\n",
    "\n",
    "# Following function flattens a list that contains lists providing the nested lists are\n",
    "# only 1 level deep.\n",
    "def phjFlattenList(phjList):\n",
    "\n",
    "    flatlist = []\n",
    "\n",
    "    for sublist in phjList:\n",
    "        if isinstance(sublist,list):\n",
    "            for item in sublist:\n",
    "                flatlist.append(item)\n",
    "        else:\n",
    "            flatlist.append(sublist)\n",
    "\n",
    "    return flatlist\n",
    "\n",
    "\n",
    "\n",
    "def phjAddColOfTokens(phjDF,\n",
    "                      phjNameColName,\n",
    "                      phjTokColName,\n",
    "                      phjPrevTokColName,\n",
    "                      phjGrpColName,\n",
    "                      phjYrColName,\n",
    "                      phjMthColName,\n",
    "                      phjPrintResults = False):\n",
    "    \n",
    "    # Create a column of tokens found in the name column\n",
    "\n",
    "    phjStopList = ['(',')','/','&',',','-','–','—','and','the','limited']\n",
    "\n",
    "    phjReplaceDict = {'t/a':'ta',\n",
    "                      'ltd':'limited',\n",
    "                      'bros':'brothers',\n",
    "                      '&':'and',\n",
    "                      '2sisters':[2,'sisters']}\n",
    "    \n",
    "    # Sort data based on grouping variable and date variables\n",
    "    phjDF = phjDF.sort_values(by = [phjGrpColName,\n",
    "                                    phjYrColName,\n",
    "                                    phjMthColName])\n",
    "    \n",
    "    # Remove items from phjStopList and replace any items in list using a dictionary lookup\n",
    "    # (The dictionary replacements may result in some lists-within-a-list scenarios; the function\n",
    "    # phjFlattenList() flattens lists providing the nested lists are only 1 deep.)\n",
    "    # The replace() functions convert hyphens and n and m dashes to spaces.\n",
    "    phjDF[phjTokColName] = phjDF[phjNameColName].apply(lambda x: [tok for tok in phjFlattenList([phjReplaceDict.get(i,i) for i in nltk.word_tokenize(x.lower().replace('-',' ').replace('–',' ').replace('—',' '))]) if tok not in phjStopList])\n",
    "\n",
    "\n",
    "    # Create a new column with tokens offset by 1 place so new column contains the\n",
    "    # tokens from the previous row\n",
    "    phjDF[phjPrevTokColName] = phjDF[phjTokColName].shift(1)\n",
    "    \n",
    "    return phjDF\n",
    "\n",
    "\n",
    "\n",
    "def phjCompareTokCols(phjDF,\n",
    "                      phjGrpColName,\n",
    "                      phjTokColName,\n",
    "                      phjPrevTokColName,\n",
    "                      phjJaccardColName,\n",
    "                      phjPrintResults = False):\n",
    "\n",
    "    # Need to check that index does not include duplicate values (because next line makes changes\n",
    "    # based on index position)\n",
    "    if len(list(phjDF.index)) == len(set(list(phjDF.index))):\n",
    "\n",
    "        # For each group (groupby appno), the first row can't be compared with the preceding\n",
    "        # row. Therefore, remove the tokens from the preceding row. The method for doing this\n",
    "        # was given by EdChum - Reinstate Monica at\n",
    "        # https://stackoverflow.com/questions/46242488/change-first-element-of-each-group-in-pandas-dataframe\n",
    "        phjDF.loc[phjDF.groupby(phjGrpColName)[phjPrevTokColName].head(1).index, phjPrevTokColName] = np.nan\n",
    "\n",
    "        # In groups with two rows, compare tokens with previous tokens using NLTK's Jaccard Distance\n",
    "        # (Only calculate Jaccard distance if the prevtokens column contains a list i.e. don't try to calculate\n",
    "        # difference if the prevtokens column contains a NaN value)\n",
    "        phjDF[phjJaccardColName] = phjDF.apply(lambda x: nltk.jaccard_distance(set(x[phjTokColName]),set(x[phjPrevTokColName])) if isinstance(x[phjPrevTokColName],list) else np.nan,axis = 1)\n",
    "\n",
    "        if phjPrintResults == True:\n",
    "            print('Token columns compared\\n======================')\n",
    "            print(phjDF)\n",
    "            print('\\n')\n",
    "    \n",
    "    else:\n",
    "        print(\"Index values not unique\")\n",
    "        phjDF = None\n",
    "        \n",
    "    return phjDF\n",
    "\n",
    "\n",
    "\n",
    "def phjDefineChangedNames(phjDF,\n",
    "                          phjGrpColName,\n",
    "                          phjNameColName,\n",
    "                          phjJaccardColName,\n",
    "                          phjMatchColName,\n",
    "                          phjCutOffValue = 0.5,\n",
    "                          phjPrintResults = False):\n",
    "    \n",
    "    # Get a list of the indexes of rows where Jaccard distance is greater than 0\n",
    "    phAlteredNamesIndexList = list(phjDF.loc[phjDF['jd'].gt(0),[phjGrpColName,phjNameColName,phjJaccardColName]].index)\n",
    "\n",
    "    # Edit to include index of immediately preceding row\n",
    "    phAlteredNamesIndexList = phAlteredNamesIndexList + [i-1 for i in phAlteredNamesIndexList]\n",
    "\n",
    "    # Sort list to ensure consecutive index numbers are adjacent to each other\n",
    "    phAlteredNamesIndexList.sort()\n",
    "\n",
    "    # Provide a cut-off to interpret Jaccard distance\n",
    "    phjDF[phjMatchColName] = phjDF['jd'].lt(phjCutOffValue)\n",
    "    phjDF[phjMatchColName] = phjDF[phjMatchColName].replace({1:'yes',0:'no'})\n",
    "\n",
    "    phjDF.loc[phjDF[phjJaccardColName].isnull(),phjMatchColName] = np.nan\n",
    "\n",
    "    if phjPrintResults == True:\n",
    "        print('Altered rows of data\\n====================')\n",
    "        print(phjDF.loc[phAlteredNamesIndexList,:])\n",
    "        print('\\n')\n",
    "        \n",
    "    return phjDF\n",
    "\n",
    "\n",
    "\n",
    "def phjRemoveUnchanged(phjDF,\n",
    "                       phjIDColName,\n",
    "                       phjGrpColName,\n",
    "                       phjMatchColName,\n",
    "                       phjCountColName,\n",
    "                       phjPrintResults = False):\n",
    "    \n",
    "    # If name has not changed then keep only the last row in the group (but ensure the same id number is retained).\n",
    "    # If the trading name has changed then keep both rows in the group. The suggested approach to produce the\n",
    "    # above was given as an answer by Erfin at:\n",
    "    # https://stackoverflow.com/questions/59568154/updating-a-pandas-dataframe-with-new-data-whilst-retaining-existing-id-number\n",
    "\n",
    "    mask_yes = phjDF[phjMatchColName].eq('yes') # array with True for rows with 'yes'\n",
    "    mask_no = phjDF[phjMatchColName].eq('no')   # array with True for rows with 'no'\n",
    "    mask_single = phjDF[phjCountColName].eq(1)  # array with True for rows in single-row groups\n",
    "\n",
    "\n",
    "    # if the row is 'yes', get the shifted id, else the original id\n",
    "    phjDF[phjIDColName] = np.where(mask_yes, phjDF[phjIDColName].shift(), phjDF[phjIDColName]) \n",
    "\n",
    "    # if a group has 'no' mark all rows as True so we can keep the whole group\n",
    "    mask = phjDF.assign(indicator=mask_no).groupby(phjGrpColName)['indicator'].transform('any')\n",
    "\n",
    "    # filter on groups with 'no' or only the row 'yes'\n",
    "    phjDF = phjDF[mask | mask_yes | mask_single]\n",
    "\n",
    "    if phjPrintResults == True:\n",
    "        print('Updated dataframe\\n=================')\n",
    "        print(phjDF)\n",
    "        print('\\n')\n",
    "\n",
    "    return phjDF\n",
    "\n",
    "\n",
    "\n",
    "def phjUpdateID(phjDF,\n",
    "                phjIDColName = 'id',\n",
    "                phjYrColName = 'yr',\n",
    "                phjMthColName = 'mth',\n",
    "                phjPrintResults = False):\n",
    "    \n",
    "    # Sort dataframe by ID and date columns, ensuring that NaNs are left at the end\n",
    "    phjDF = phjDF.sort_values(by = [phjIDColName,phjYrColName,phjMthColName],\n",
    "                              na_position = 'last')\n",
    "    \n",
    "    # Create a list of new ID values that follow-on from those ID values that already exist.\n",
    "    # But first, need to check whether the max() value is null (which can happen if the dataframe is empty).\n",
    "    if pd.isnull(phjDF[phjIDColName].max()):\n",
    "        phjNewIDs = list(range(1,\n",
    "                         len(phjDF.loc[phjDF[phjIDColName].isnull(),:]) + 1))\n",
    "    else:\n",
    "        phjNewIDs = list(range(phjDF[phjIDColName].max().astype(int) + 1,\n",
    "                               phjDF[phjIDColName].max().astype(int) + len(phjDF.loc[phjDF[phjIDColName].isnull(),:]) + 1))\n",
    "\n",
    "    # Where ID values are NaN, replace with range of new IDs\n",
    "    phjDF.loc[phjDF[phjIDColName].isnull(),[phjIDColName]] = phjNewIDs\n",
    "\n",
    "    if phjPrintResults == True:\n",
    "        print('Updated ID values\\n=================')\n",
    "        print(phjDF)\n",
    "        print('\\n')\n",
    "    \n",
    "    return phjDF"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define miscellaneous functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def phjGenMonthLookupDict(phjPrintResults = False):\n",
    "    \"\"\"\n",
    "    # Create a lookup table of month names and numbers\n",
    "    # ------------------------------------------------\n",
    "    # Use 'calendar' library to create a dictionary of month names and abbreviations to numbers as described at:\n",
    "    # https://stackoverflow.com/questions/3418050/month-name-to-month-number-and-vice-versa-in-python\n",
    "    #\n",
    "    # Multiple dictionaries can be joined using dict.update() method as described at:\n",
    "    # https://stackoverflow.com/questions/8930915/append-a-dictionary-to-a-dictionary\n",
    "    \"\"\"\n",
    "    phjMonthLUTDict = {}\n",
    "    # Add month names\n",
    "    phjMonthLUTDict.update({month.lower(): index for index, month in enumerate(calendar.month_name) if month})\n",
    "    # Add month abbreviations\n",
    "    phjMonthLUTDict.update({month.lower(): index for index, month in enumerate(calendar.month_abbr) if month})\n",
    "    # Add additional 4-letter abbreviation for sept\n",
    "    phjMonthLUTDict.update({'sept':9})\n",
    "    # Add month numbers (with leading zero)\n",
    "    phjMonthLUTDict.update({str(index).zfill(2): index for index, month in enumerate(calendar.month_name) if month})\n",
    "    # Add month numbers\n",
    "    phjMonthLUTDict.update({str(index): index for index, month in enumerate(calendar.month_name) if month})\n",
    "    \n",
    "    if phjPrintResults == True:\n",
    "        print('Dictionary of month lookup values')\n",
    "        print('---------------------------------')\n",
    "        print(phjMonthLUTDict)\n",
    "    \n",
    "    return phjMonthLUTDict\n",
    "\n",
    "\n",
    "\n",
    "def phjGenRevMonthLookupDict(phjPrintResults = False):\n",
    "    \"\"\"\n",
    "    # Create a dictionary to convert month number to month name in lowercase\n",
    "    \"\"\"\n",
    "    phjRevMonthLUTDict = {index: month.lower() for index, month in enumerate(calendar.month_name) if month}\n",
    "    \n",
    "    if phjPrintResults == True:\n",
    "        print('Dictionary of reversed month lookup values')\n",
    "        print('------------------------------------------')\n",
    "        print(phjRevMonthLUTDict)\n",
    "        \n",
    "    return phjRevMonthLUTDict\n",
    "\n",
    "\n",
    "\n",
    "def phjGenMonthList(phjPrintResults = False):\n",
    "    # Create list of month names (lowercase) in order\n",
    "    phjMonthList = [month.lower() for index, month in enumerate(calendar.month_name) if month]\n",
    "    \n",
    "    if phjPrintResults == True:\n",
    "        print(phjMonthList)\n",
    "        \n",
    "    return phjMonthList"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def phjCheckColumnsInMultipleDataframes(phjColsList,\n",
    "                                        phjColsOfInterestList,\n",
    "                                        phjPrintResults = False):\n",
    "    \n",
    "    # Check that the columns of interest is a subset of each list in\n",
    "    # phjColsList; return True if columns of interest occur in ALL lists\n",
    "    \n",
    "    # To do\n",
    "    #     Check phjColsList is a list of lists\n",
    "    #     Check phjColsOfInterestList is a list\n",
    "    \n",
    "    phjResults = []\n",
    "    for l in phjColsList:\n",
    "        phjResults = phjResults + [set(phjColsOfInterestList).issubset(list(l))]\n",
    "        \n",
    "    if phjPrintResults == True:\n",
    "        print('Columns of interest occur in following lists')\n",
    "        print('--------------------------------------------')\n",
    "        print(phjResults)\n",
    "        \n",
    "    return all(phjResults)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example of using phjCheckColumnsInMultipleDataframes() function\n",
    "phjVar = phjCheckColumnsInMultipleDataframes(phjColsList = [['a','b','c','d','e'],\n",
    "                                                            ['b','c','d','e','f'],\n",
    "                                                            ['a','b','c','d','e','f'],\n",
    "                                                            ['b','c','d','e','f','g']],\n",
    "                                             phjColsOfInterestList = ['b','c','d','e'],\n",
    "                                             phjPrintResults = True)\n",
    "\n",
    "print('All columns of interest present in all files: {}'.format(phjVar))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Testing with hypothetical data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Define missing codes\n",
    "phjMissingCodesDict = {'phjMissValueStr':'missing',\n",
    "                       'phjCPHCountyMissCode':999,\n",
    "                       'phjCountryMissCode':99,\n",
    "                       'phjAnimSrcMissCode':400,\n",
    "                       'phjSppGrpMissCode':99,\n",
    "                       'phjOffCCIRMissCode':999,\n",
    "                       'phjInspectionTypeMissCode':785}\n",
    "\n",
    "# Define country codes\n",
    "# Include ISO-3166 codes for consistency\n",
    "# ISO-3166 numeric codes 900 to 999 are reserved for in-house user definitions\n",
    "phjCountryDF = pd.DataFrame({'country_id':[1,2,3,4,5,6,7,8,phjMissingCodesDict['phjCountryMissCode']],\n",
    "                             'country':['England','Wales','Scotland','Northern Ireland','Guernsey','Jersey','Isle of Man','Ireland','missing'],\n",
    "                             'iso3166-1-alpha-2':['GB','GB','GB','GB','GG','JE','IM','IE','missing'],\n",
    "                             'iso3166-1-alpha-3':['GBR','GBR','GBR','GBR','GGY','JEY','IMN','IRL','missing'],\n",
    "                             'iso3166-1-numeric':[826,826,826,826,831,832,833,372,999],\n",
    "                             'iso3166-2':['GB-ENG','GB-WLS','GB-SCT','GB-NIR','NA','NA','NA','NA','missing'],\n",
    "                             'iso3166':['GB-ENG','GB-WLS','GB-SCT','GB-NIR','GGY','JEY','IMN','IE','missing']\n",
    "                            })\n",
    "\n",
    "print('Country codes')\n",
    "print('=============')\n",
    "print(phjCountryDF)\n",
    "print('\\n')\n",
    "print(phjCountryDF.dtypes)\n",
    "print('\\n')\n",
    "\n",
    "phjColsOfInterestList = ['premisesnameid',\n",
    "                         'appno',\n",
    "                         'tradingname',\n",
    "                         'country',\n",
    "                         'yr',\n",
    "                         'mth',\n",
    "                         'allactivities',\n",
    "                         'slaughterhouse',\n",
    "                         'postcode']\n",
    "\n",
    "# Original dataframe should include a column of premises ID\n",
    "# N.B. Spaces, underscores and ? will be removed from column names in pre-processing\n",
    "origDF = pd.DataFrame({'premisesname_id':[1,2,3,4,5,6,11,12,13,7,8,9,10],\n",
    "                       'appno':['123','226','321','356','403','556','556','556','556','598','612','663','785'],\n",
    "                       'trading_name':['Smith and Jones','ABC Ltd','Stuff Galore Ltd','Quality Food','Pure Heaven','XYZ Foods','ABC Foods','LMN Foods','AAA Foods','Fry Foods','Farm Foods','Good Eggs Limited','Farm to Fork Food'],\n",
    "                       'country':['England','England','England','England','Wales','Scotland','Scotland','Scotland','Scotland','Wales','England','England','Northern Ireland'],\n",
    "                       'yr':[2018,2018,2018,2018,2018,2018,2018,2018,2018,2018,2018,2018,2018],\n",
    "                       'mth':[4,4,4,4,4,1,2,3,4,4,4,4,4],\n",
    "                       'all_activities':[np.nan,np.nan,np.nan,np.nan,'game handling establishment (wild game)',np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan],\n",
    "                       'slaughterhouse':['yes','yes','yes','yes','no','yes','yes','yes','yes','yes','yes','yes','yes'],\n",
    "                       'postcode':['np45df','ab123df','w14sd','cd49gs','eg349dg','h118gs','h118gs','h118gs','h118gs','j28gd','h89ad','p98gs','ts19yu']})\n",
    "\n",
    "# Preprocess the original file\n",
    "origDF = phjPreProcess(phjDF = origDF,\n",
    "                       phjCountryDF = phjCountryDF[['country_id','country','iso3166']],\n",
    "                       phjCountryMissCode = phjMissingCodesDict['phjCountryMissCode'],\n",
    "                       phjColsOfInterestList = phjColsOfInterestList)\n",
    "\n",
    "print('Original dataframe (post processing)\\n==================')\n",
    "print(origDF)\n",
    "print('\\n')\n",
    "\n",
    "\n",
    "\n",
    "# New dataframe should have a column of premises ID but should be NaN initially\n",
    "# N.B. Spaces, underscores and ? will be removed from column names in pre-processing\n",
    "newDF = pd.DataFrame({'premisesname_id':[np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan],\n",
    "                      'appno':['123','226','356','403','556','598','663','785','444'],\n",
    "                      'trading_name':['Smith and Jones Bros','2sisters ABC Ltd','Completely Different','Pure Heaven','AAA Foods','Fry & Sons Foods','Good Eggs Ltd','Farm-2-Fork Food','Food Heaven'],\n",
    "                      'country':['England','England','England','Wales','Scotland','England','England','Northern Ireland','Wales'],\n",
    "                      'yr':[2018,2018,2018,2018,2018,2018,2018,2018,2018],\n",
    "                      'mth':[5,5,5,5,5,5,5,5,5],\n",
    "                      'all_activities':[np.nan,np.nan,np.nan,np.nan,'game handling establishment (wild game)',np.nan,np.nan,np.nan,np.nan],\n",
    "                      'slaughterhouse':['yes','yes','yes','yes','no','yes','yes','yes','yes'],\n",
    "                      'postcode':['np45df','ab123df','w14sd','eg349dg','h118gs','j28gd','p98gs','ts19yu','xy987ab']})\n",
    "\n",
    "# Preprocess the new file\n",
    "newDF = phjPreProcess(phjDF = newDF,\n",
    "                      phjCountryDF = phjCountryDF[['country_id','country','iso3166']],\n",
    "                      phjCountryMissCode = phjMissingCodesDict['phjCountryMissCode'],\n",
    "                      phjColsOfInterestList = phjColsOfInterestList)\n",
    "\n",
    "print('Newer dataframe (post processing)\\n===============')\n",
    "print(newDF)\n",
    "print('\\n')\n",
    "\n",
    "\n",
    "\n",
    "phjCombinedDF = phjUpdateData(phjOriginalDF = origDF,\n",
    "                              phjNewDF = newDF,\n",
    "                              phjColsOfInterestList = phjColsOfInterestList,\n",
    "                              phjIDColName = 'premisesnameid',\n",
    "                              phjYrColName = 'yr',\n",
    "                              phjMthColName = 'mth',\n",
    "                              phjGrpColName = 'appno',\n",
    "                              phjNameColName = 'tradingname',\n",
    "                              phjCountColName = 'count',\n",
    "                              phjTokColName = 'tokens',\n",
    "                              phjPrevTokColName = 'prevtokens',\n",
    "                              phjJaccardColName = 'jd',\n",
    "                              phjMatchColName = 'samename',\n",
    "                              phjPrintResults = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Test with two files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Path to directory\n",
    "phjPath = './monthly_approved_premises'\n",
    "\n",
    "# List of files\n",
    "phjFilesList = [\"approved-food-establishments-as-at-1-january-2018.csv\",\n",
    "                \"approved-food-establishments-as-at-1-december-2019.csv\"]\n",
    "\n",
    "# Define missing codes\n",
    "phjMissingCodesDict = {'phjMissValueStr':'missing',\n",
    "                       'phjCPHCountyMissCode':999,\n",
    "                       'phjCountryMissCode':999,\n",
    "                       'phjAnimSrcMissCode':999,\n",
    "                       'phjSppGrpMissCode':999,\n",
    "                       'phjOffCCIRMissCode':999,\n",
    "                       'phjInspectionTypeMissCode':999}\n",
    "\n",
    "# Define country codes\n",
    "phjCountryDF = pd.DataFrame({'country_id':[1,2,3,4,5,6,7,8,phjMissingCodesDict['phjCountryMissCode']],\n",
    "                             'country':['England','Wales','Scotland','Northern Ireland','Guernsey','Jersey','Isle of Man','Ireland','missing'],\n",
    "                             'iso3166-1-alpha-2':['GB','GB','GB','GB','GG','JE','IM','IE','missing'],\n",
    "                             'iso3166-1-alpha-3':['GBR','GBR','GBR','GBR','GGY','JEY','IMN','IRL','missing'],\n",
    "                             'iso3166-1-numeric':[826,826,826,826,831,832,833,372,999],\n",
    "                             'iso3166-2':['GB-ENG','GB-WLS','GB-SCT','GB-NIR','NA','NA','NA','NA','missing'],\n",
    "                             'iso3166':['GB-ENG','GB-WLS','GB-SCT','GB-NIR','GGY','JEY','IMN','IE','missing']\n",
    "                            })\n",
    "\n",
    "print('Country codes')\n",
    "print('=============')\n",
    "print(phjCountryDF)\n",
    "print('\\n')\n",
    "print(phjCountryDF.dtypes)\n",
    "print('\\n')\n",
    "\n",
    "phjColsOfInterestList = ['appno',\n",
    "                         'tradingname',\n",
    "                         'town',\n",
    "                         'postcode',\n",
    "                         'country',\n",
    "                         'allactivities',\n",
    "                         'slaughterhouse',\n",
    "                         'x',\n",
    "                         'y']\n",
    "\n",
    "# Original dataframe should include a column of premises ID\n",
    "# Read csv file\n",
    "origDF = pd.read_csv(Path('/'.join([phjPath,phjFilesList[0]])))\n",
    "\n",
    "print('origDF column dtypes')\n",
    "print('--------------------')\n",
    "with pd.option_context('display.max_rows', None, 'display.max_columns', None):\n",
    "    print(origDF.dtypes)\n",
    "    print('\\n')\n",
    "\n",
    "# Preprocess the original file\n",
    "origDF = phjPreProcess(phjDF = origDF,\n",
    "                       phjCountryDF = phjCountryDF[['country_id','country','iso3166']],\n",
    "                       phjCountryMissCode = phjMissingCodesDict['phjCountryMissCode'],\n",
    "                       phjColsOfInterestList = phjColsOfInterestList)\n",
    "\n",
    "origDF['id'] = range(1,len(origDF.index)+1)\n",
    "origDF = origDF[['id'] + [i for i in origDF.columns if i not in ['id']]]\n",
    "origDF['yr'] = 2018\n",
    "origDF['mth'] = 1\n",
    "\n",
    "print('Original dataframe (post processing)\\n==================')\n",
    "print(origDF.sort_values(by = ['appno','id']))\n",
    "print('\\n')\n",
    "\n",
    "\n",
    "# New dataframe should have a column of premises ID but should be NaN initially\n",
    "newDF = pd.read_csv(Path('/'.join([phjPath,phjFilesList[1]])))\n",
    "\n",
    "print('newDF column dtypes')\n",
    "print('-------------------')\n",
    "with pd.option_context('display.max_rows', None, 'display.max_columns', None):\n",
    "    print(newDF.dtypes)\n",
    "    print('\\n')\n",
    "\n",
    "\n",
    "# Preprocess the new file\n",
    "newDF = phjPreProcess(phjDF = newDF,\n",
    "                      phjCountryDF = phjCountryDF[['country_id','country','iso3166']],\n",
    "                      phjCountryMissCode = phjMissingCodesDict['phjCountryMissCode'],\n",
    "                      phjColsOfInterestList = phjColsOfInterestList)\n",
    "\n",
    "newDF['id'] = np.nan\n",
    "newDF = newDF[['id'] + [i for i in newDF.columns if i not in ['id']]]\n",
    "newDF['yr'] = 2019\n",
    "newDF['mth'] = 12\n",
    "\n",
    "print('Newer dataframe (post processing)\\n===============')\n",
    "print(newDF.sort_values(by = ['appno','id']))\n",
    "print('\\n')\n",
    "\n",
    "\n",
    "phjCombinedDF = phjUpdateData(phjOriginalDF = origDF,\n",
    "                              phjNewDF = newDF,\n",
    "                              phjColsOfInterestList = phjColsOfInterestList,\n",
    "                              phjIDColName = 'id',\n",
    "                              phjYrColName = 'yr',\n",
    "                              phjMthColName = 'mth',\n",
    "                              phjGrpColName = 'appno',\n",
    "                              phjNameColName = 'tradingname',\n",
    "                              phjCountColName = 'count',\n",
    "                              phjTokColName = 'tokens',\n",
    "                              phjPrevTokColName = 'prevtokens',\n",
    "                              phjJaccardColName = 'jd',\n",
    "                              phjMatchColName = 'samename',\n",
    "                              phjPrintResults = True)\n",
    "\n",
    "print(phjCombinedDF.sort_values(by = ['appno','id']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Testing with real data files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define additional required data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create list of years to include\n",
    "phjYearList = [2018,2019,2020,2021,2022,2023]\n",
    "print('Year list')\n",
    "print('---------')\n",
    "print(phjYearList)\n",
    "print('\\n')\n",
    "\n",
    "# Create a dictionary lookup to convert months to number\n",
    "phjMonthLUTDict = phjGenMonthLookupDict()\n",
    "print('Month lookup dict')\n",
    "print('-----------------')\n",
    "print(phjMonthLUTDict)\n",
    "print('\\n')\n",
    "\n",
    "# Create a dictionary lookup to convert number to month name\n",
    "phjRevMonthLUTDict = phjGenRevMonthLookupDict()\n",
    "print('Month number lookup dict')\n",
    "print('------------------------')\n",
    "print(phjRevMonthLUTDict)\n",
    "print('\\n')\n",
    "\n",
    "# Create a list of lowercase month names (in order)\n",
    "phjMonthList = phjGenMonthList()\n",
    "print('Month list')\n",
    "print('----------')\n",
    "print(phjMonthList)\n",
    "print('\\n')\n",
    "\n",
    "# Define missing codes\n",
    "phjMissingCodesDict = {'phjMissValueStr':'missing',\n",
    "                       'phjCPHCountyMissCode':999,\n",
    "                       'phjCountryMissCode':999,\n",
    "                       'phjAnimSrcMissCode':999,\n",
    "                       'phjSppGrpMissCode':999,\n",
    "                       'phjOffCCIRMissCode':999,\n",
    "                       'phjInspectionTypeMissCode':999}\n",
    "print('Missing codes')\n",
    "print('-------------')\n",
    "print(phjMissingCodesDict)\n",
    "print('\\n')\n",
    "\n",
    "# Define country codes dataframe\n",
    "phjCountryDF = pd.DataFrame({'country_id':[1,2,3,4,5,6,7,8,phjMissingCodesDict['phjCountryMissCode']],\n",
    "                             'country':['England','Wales','Scotland','Northern Ireland','Guernsey','Jersey','Isle of Man','Ireland','missing'],\n",
    "                             'iso3166-1-alpha-2':['GB','GB','GB','GB','GG','JE','IM','IE','missing'],\n",
    "                             'iso3166-1-alpha-3':['GBR','GBR','GBR','GBR','GGY','JEY','IMN','IRL','missing'],\n",
    "                             'iso3166-1-numeric':[826,826,826,826,831,832,833,372,999],\n",
    "                             'iso3166-2':['GB-ENG','GB-WLS','GB-SCT','GB-NIR','NA','NA','NA','NA','missing'],\n",
    "                             'iso3166':['GB-ENG','GB-WLS','GB-SCT','GB-NIR','GGY','JEY','IMN','IE','missing']\n",
    "                            })\n",
    "\n",
    "print('Country codes')\n",
    "print('=============')\n",
    "print(phjCountryDF)\n",
    "print('\\n')\n",
    "print(phjCountryDF.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Run real datafiles and add to MySQL database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Make connection to database where data will be retrieved and stored\n",
    "# Instructions for creating sql engine taken from: https://docs.sqlalchemy.org/en/13/dialects/mysql.html\n",
    "phjUsername = input('Enter username: ')\n",
    "phjPassword = getpass.getpass('Enter password: ')\n",
    "\n",
    "phjSQLEng = sqlalchemy.create_engine('mysql+pymysql://{0}:{1}@localhost/ccir_data'.format(phjUsername,phjPassword))\n",
    "\n",
    "# Delete contents of table first (don't use 'replace' because it changes the structure of the table)\n",
    "with phjSQLEng.begin() as conn:     \n",
    "    conn.execute('DROP TABLE IF EXISTS `appno`')\n",
    "    \n",
    "#    conn.execute(\"\"\"CREATE TABLE IF NOT EXISTS `ccir_data`.`appno` (\n",
    "#                      `id` INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,\n",
    "#                      `appno` VARCHAR(12) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' NOT NULL DEFAULT '',\n",
    "#                      `yr` INT(4) UNSIGNED NOT NULL,\n",
    "#                      `mth` INT(4) UNSIGNED NOT NULL,\n",
    "#                      `abattoir_id` INT(4) UNSIGNED NOT NULL,\n",
    "#                    PRIMARY KEY (`id`),\n",
    "#                    UNIQUE INDEX `appno-yr-mth_UNIQUE` (`appno` ASC, `yr` ASC, `mth` ASC) VISIBLE)\n",
    "#                    ENGINE = InnoDB\n",
    "#                    AUTO_INCREMENT = 1\n",
    "#                    DEFAULT CHARACTER SET = utf8mb4\n",
    "#                    COLLATE = utf8mb4_0900_ai_ci\n",
    "#                    \"\"\")\n",
    "\n",
    "    conn.execute(\"\"\"CREATE TABLE IF NOT EXISTS `ccir_data`.`appno` (\n",
    "                      `appno` VARCHAR(12) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' NOT NULL DEFAULT '',\n",
    "                      `yr` INT(4) UNSIGNED NOT NULL,\n",
    "                      `mth` INT(4) UNSIGNED NOT NULL,\n",
    "                      `abattoir_id` INT(4) UNSIGNED NOT NULL,\n",
    "                      PRIMARY KEY (`appno`,`yr`,`mth`))\n",
    "                    ENGINE = InnoDB\n",
    "                    DEFAULT CHARACTER SET = utf8mb4\n",
    "                    COLLATE = utf8mb4_0900_ai_ci\n",
    "                    \"\"\")\n",
    "\n",
    "\n",
    "    conn.execute('DELETE FROM `abattoir`')\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define regex to extract mth and yr from filenames\n",
    "# -------------------------------------------------\n",
    "# Filenames take one of the following formats:\n",
    "#   1. approved-food-establishments-as-at-1-january-2018.csv\n",
    "#   2. ApprovedPremisesOct21.csv\n",
    "#   3. APMSNov21.csv\n",
    "#   4. Approved+establishments+01-12-2021.csv\n",
    "#   5. Approved_establishments01-12-2022.csv (modified regex for above format to match)\n",
    "#   6. ApprovedFoodEstAug22.csv\n",
    "#   7. ApprovedPremisesEandWMay22.csv\n",
    "#   8. ApprovedFoodEstablishmentsApr23\n",
    "\n",
    "\n",
    "# Define a list of regexes that will match each filename format and extract month and year.\n",
    "# If a new naming format is used in future, add an appropriate regex with named groups to\n",
    "# extract 'mth' and 'yr' information from filename.\n",
    "# Python PyPi regex module supports a branch reset feature which allows the same group name to be used in each alternative regex\n",
    "# N.B. There is a typo in Approved+establsihments+01-12-2021.csv which is accounted for in the regex\n",
    "phjFilenameRegexList = ['(?:^approved-food-establishments-as-at-1-(?P<mth>[A-Za-z]{3,9})-(?P<yr>\\d{4})\\.csv$)',\n",
    "                        '(?:^ApprovedPremises(?:EandW)?(?P<mth>[A-Za-z]{3,9})(?P<yr>\\d{2,4})\\.csv$)',\n",
    "                        '(?:^APMS(?P<mth>[A-Za-z]{3})(?P<yr>\\d{2})\\.csv$)',\n",
    "                        '(?:^Approved[+_]{1}establ[is]{2}hments\\+?01-(?P<mth>\\d{2})-(?P<yr>\\d{4})\\.csv$)',\n",
    "                        '(?:^ApprovedFoodEst(?P<mth>[A-Za-z]{3,9})(?P<yr>\\d{2,4})\\.csv$)',\n",
    "                        '(?:^ApprovedFoodEstablishments(?P<mth>[A-Za-z]{3})(?P<yr>\\d{2,4})\\.csv$)']\n",
    "\n",
    "# Join regexes in list to form single regex separated by |\n",
    "phjCombRegex = '|'.join(phjFilenameRegexList)\n",
    "print('Combined regex')\n",
    "print('--------------')\n",
    "print(phjCombRegex)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example of date extraction from filename\n",
    "phjTempName = 'ApprovedPremisesOct21.csv'\n",
    "phjTempExtrDate = regex.compile(phjCombRegex).search(phjTempName).groupdict()\n",
    "phjTempExtrMth = phjTempExtrDate['mth'].lower()\n",
    "print(phjTempExtrMth)\n",
    "print(phjTempExtrDate['yr'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Retrieve list of filenames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Files listing FSA approved premises taken from:\n",
    "# https://data.food.gov.uk/catalog/datasets/1e61736a-2a1a-4c6a-b8b1-e45912ebc8e3\n",
    "\n",
    "# Path to directory\n",
    "phjPath = './monthly_approved_premises'\n",
    "\n",
    "# List of filenames can be created when all filenames follow the same naming convention.\n",
    "# However, when names vary, it is necessary to retrieve actual filenames that exist in\n",
    "# a directory.\n",
    "#phjFilesList = [\"approved-food-establishments-as-at-1-{}-{}.csv\".format(mth,yr) for yr in phjYearList for mth in phjMonthList]\n",
    "\n",
    "# Retrieve filenames contained in directory\n",
    "# -----------------------------------------\n",
    "# Get files (and directories) in folder if name ends with '.csv' and store results in a Pandas dataframe.\n",
    "# N.B. There is a typo in the filename Approved+establsihments+01-12-2021.csv\n",
    "phjFilenamesDF = pd.DataFrame({'filename':[f for f in os.listdir(phjPath) if f[-4:] == '.csv']})\n",
    "\n",
    "print('Retrieved filenames')\n",
    "print('-------------------')\n",
    "with pd.option_context('display.max_colwidth', 60):\n",
    "    print(phjFilenamesDF)\n",
    "print('\\n')   \n",
    "\n",
    "\n",
    "# Extract mth and yr from filename (as dicts)\n",
    "# --------------------------------\n",
    "\n",
    "# N.B. Pandas expects a re object not a regex object.\n",
    "# Need to define own method and use .apply() as described at:\n",
    "# https://stackoverflow.com/questions/61485358/using-regex-module-with-pandas-series\n",
    "#\n",
    "# import pandas as pd\n",
    "# import regex\n",
    "#\n",
    "# to_test = pd.Series([ 'yes' , 'no' , 'yes' ])\n",
    "#\n",
    "# def regex_contains(s,rx):\n",
    "#     return bool(rx.search(s))\n",
    "#\n",
    "# classifier = regex.compile(r\"yes\")\n",
    "# to_test.apply(regex_contains, args=(classifier,))\n",
    "\n",
    "# Useful comments on defining methods given at:\n",
    "# https://stackoverflow.com/questions/12182744/python-pandas-apply-a-function-with-arguments-to-a-series\n",
    "\n",
    "# Discussion of the origin of the 'P' in (?P<aaa<) can be found at:\n",
    "# https://stackoverflow.com/questions/10059673/named-regular-expression-group-pgroup-nameregexp-what-does-p-stand-for\n",
    "    \n",
    "\n",
    "# Start by defining method to extract groups from filename using phjFilenameRegexList\n",
    "def phjFilenameRegex(s,rx):\n",
    "    return rx.search(s).groupdict()\n",
    "\n",
    "phjFilenamesDF['date_extract'] = phjFilenamesDF['filename'].apply(phjFilenameRegex, args=(regex.compile(phjCombRegex),))\n",
    "\n",
    "print('Dataframe with extracted date components')\n",
    "print('----------------------------------------')\n",
    "with pd.option_context('display.max_rows', 6, 'display.max_columns', None):\n",
    "    print(phjFilenamesDF)\n",
    "print('\\n')\n",
    "\n",
    "# Convert dict keys to column headings in dataframe\n",
    "# -------------------------------------------------\n",
    "# Converting a series of dicts to a dataframe can be achieved using df['b'].apply(pd.Series)\n",
    "# as described in answer by joris at:\n",
    "# https://stackoverflow.com/questions/38231591/split-explode-a-column-of-dictionaries-into-separate-columns-with-pandas\n",
    "# On the same page there are other answers that are, apparently, faster but .apply() works\n",
    "# well for this requirement.\n",
    "phjFilenamesDF = pd.concat([phjFilenamesDF, phjFilenamesDF['date_extract'].apply(pd.Series)], axis=1)\n",
    "\n",
    "print('Dataframe with date columns')\n",
    "print('---------------------------')\n",
    "with pd.option_context('display.max_rows', 6, 'display.max_columns', None):\n",
    "    print(phjFilenamesDF)\n",
    "print('\\n')\n",
    "\n",
    "\n",
    "# Convert extracted mth and yr to month and year (and day) columns to use to create dates\n",
    "# ---------------------------------------------------------------------------------------\n",
    "# Map 'mth' column (converted to lowercase) to month number by mapping to the phjMonthLUTDict\n",
    "phjFilenamesDF['month'] = phjFilenamesDF['mth'].str.lower().map(phjMonthLUTDict).fillna('missing')\n",
    "\n",
    "# Convert 2-digit year to 4-digit year\n",
    "phjFilenamesDF['year'] = phjFilenamesDF['yr'].where(phjFilenamesDF['yr'].astype('int') >= 2000,\n",
    "                                                    phjFilenamesDF['yr'].astype('int') + 2000,\n",
    "                                                    axis = 0).astype('int')\n",
    "\n",
    "\n",
    "# Converting columns to datetime requires minimum of 'year', 'month' and 'day' columns as described at:\n",
    "# https://stackoverflow.com/questions/19350806/how-to-convert-columns-into-one-datetime-column-in-pandas\n",
    "phjFilenamesDF['day'] = 1\n",
    "phjFilenamesDF['date'] = pd.to_datetime(phjFilenamesDF[['year', 'month','day']])\n",
    "\n",
    "phjFilenamesDF = phjFilenamesDF.sort_values('date').reset_index(drop = True)\n",
    "\n",
    "print('Dataframe with filenames sorted by date')\n",
    "print('---------------------------------------')\n",
    "with pd.option_context('display.max_rows', 6, 'display.max_columns', None):\n",
    "    print(phjFilenamesDF)\n",
    "print('\\n')\n",
    "\n",
    "# Produce list of filenames in date order\n",
    "# ---------------------------------------\n",
    "phjFilesList = list(phjFilenamesDF['filename'])\n",
    "\n",
    "print('List of date-ordered filenames')\n",
    "print('------------------------------')\n",
    "print(phjFilesList)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check that each file contains all the columns of interest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Columns to retain in data\n",
    "phjColsOfInterestList  = ['appno',\n",
    "                          'tradingname',\n",
    "                          'town',\n",
    "                          'postcode',\n",
    "                          'country',\n",
    "                          'allactivities',\n",
    "                          'slaughterhouse',\n",
    "                          'x',\n",
    "                          'y']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The CSV files for Aug 2022 (ApprovedFoodEstAug22) and Oct 2022 (ApprovedFoodEstOct22) - interestingly the only\n",
    "# only two files with names in that format - have no values in row 0; the column headings are in row 1.\n",
    "# Define list of files with column headings in row 1; other files assumed to have column headings in row 0.\n",
    "phjHeaderRow1Files = ['ApprovedFoodEstAug22.csv',\n",
    "                      'ApprovedFoodEstOct22.csv']\n",
    "\n",
    "# The CSV files for May 2022 (ApprovedPremisesEandWMay22.csv), Aug 2022 (ApprovedFoodEstAug22) and\n",
    "# Oct 2022 (ApprovedFoodEstOct22) fail to import correctly into Pandas due an error:\n",
    "# UnicodeDecodeError: 'utf-8' codec can't decode byte 0x92 in position 7: invalid start byte\n",
    "# In these cases, need to set the encoding to cp1252 to read the csv.\n",
    "phjCp1252EncodeFiles = ['ApprovedPremisesEandWMay22.csv',\n",
    "                        'ApprovedFoodEstAug22.csv',\n",
    "                        'ApprovedFoodEstOct22.csv']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Create a list of column headings for each file (i.e. a list of lists)\n",
    "# ----------------------------------------------\n",
    "phjColHeadingsList = []\n",
    "\n",
    "# Could read just the header row of the CSV files using csv library (without using Pandas)\n",
    "# as described in answer by Tyler at:\n",
    "# https://stackoverflow.com/questions/24962908/how-can-i-read-only-the-header-column-of-a-csv-file-using-python\n",
    "\n",
    "#import csv    \n",
    "#\n",
    "#for f in phjFilesList:\n",
    "#    with open('/'.join([phjPath,f]), 'r') as infile:\n",
    "#        phjReader = csv.DictReader(infile)\n",
    "#        phjFieldnames = phjReader.fieldnames\n",
    "#        print(phjFieldnames)\n",
    "\n",
    "# However, the column headers need to be edited to ensure consistency between each file as described\n",
    "# in the phjPreProcessing() function. Therefore, instead, read CSV into Pandas dataframe but without\n",
    "# reading in any rows (use header=0 and set index_col=False)\n",
    "for f in phjFilesList:\n",
    "    if f in phjHeaderRow1Files:\n",
    "        phjTestDF = pd.read_csv('/'.join([phjPath,f]), index_col=False, header=1, nrows=0)\n",
    "    else:\n",
    "        phjTestDF = pd.read_csv('/'.join([phjPath,f]), index_col=False, header=0, nrows=0)\n",
    "        \n",
    "    phjTestDF = phjPreProcess(phjDF = phjTestDF,\n",
    "                              phjDeleteOrigCols = False)\n",
    "    \n",
    "    # Add list of column headings to phjColHeadingsList list\n",
    "    # (N.B. Lists are mutable. Using myList = myList.append(newList) won't work.)\n",
    "    phjColHeadingsList.append(list(phjTestDF.columns))\n",
    "    \n",
    "#print(phjColHeadingsList)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check that all the files contain all the columns of interest\n",
    "if phjCheckColumnsInMultipleDataframes(phjColsList = phjColHeadingsList,\n",
    "                                       phjColsOfInterestList = phjColsOfInterestList,\n",
    "                                       phjPrintResults = False):\n",
    "    print('All files contain all the columns of interest')\n",
    "else:\n",
    "    print('Not all files contain all the columns of interest')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Upload data to database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Make connection to database where data will be retrieved and stored\n",
    "# Instructions for creating sql engine taken from: https://docs.sqlalchemy.org/en/13/dialects/mysql.html\n",
    "phjUsername = input('Enter username: ')\n",
    "phjPassword = getpass.getpass('Enter password: ')\n",
    "\n",
    "phjSQLEng = sqlalchemy.create_engine('mysql+pymysql://{0}:{1}@localhost/ccir_data'.format(phjUsername,phjPassword))\n",
    "\n",
    "phjRenameYrMthDict = {'last_yr':'yr',\n",
    "                      'last_mth':'mth'}\n",
    "\n",
    "for phjFilename in phjFilesList:\n",
    "    \n",
    "    print('Filename: {}'.format(phjFilename))\n",
    "    \n",
    "    # Retrieve data from database to act as original data\n",
    "    # ---------------------------------------------------\n",
    "    # Make connection to database where data will be retrieved and stored\n",
    "    #phjConn = epy.phjConnectToDatabase('mysql')\n",
    "    \n",
    "    try:\n",
    "        fsaAppno1DF = pd.read_sql('SELECT * from `abattoir`', con=phjSQLEng)\n",
    "        \n",
    "        # The columns in the database are 'latest_yr' and 'latest_mth' but this needs to\n",
    "        # be changed to 'yr' and 'mth' respectively to enable dataframe to be combined with\n",
    "        # new data file.\n",
    "        fsaAppno1DF = fsaAppno1DF.rename(columns = phjRenameYrMthDict)\n",
    "    \n",
    "    except pd.io.sql.DatabaseError as e:\n",
    "        print('\\nA DatabaseError occurred.')\n",
    "        print(e)\n",
    "        fsaAppno1DF = None\n",
    "        break\n",
    "    \n",
    "    print('First dataframe (retrieved from DB)\\n===============')\n",
    "    print(fsaAppno1DF)\n",
    "    print('\\n')\n",
    "\n",
    "    # Retrieve next file in list to act as latest data\n",
    "    # ------------------------------------------------\n",
    "    try:\n",
    "        # Read csv file; some files have the column names on row 1 (rather than row 0) and\n",
    "        # some files need to have encoding set to cp1252. The files in each category are\n",
    "        # defined in variables phjHeaderRow1Files and phjCp1252EncodeFiles.\n",
    "        if phjFilename in phjHeaderRow1Files:\n",
    "            if phjFilename in phjCp1252EncodeFiles:\n",
    "                fsaAppno2DF = pd.read_csv(Path('/'.join([phjPath,phjFilename])),\n",
    "                                          header = 1,\n",
    "                                          encoding = 'cp1252')\n",
    "            else:\n",
    "                fsaAppno2DF = pd.read_csv(Path('/'.join([phjPath,phjFilename])),\n",
    "                                          header = 1)\n",
    "        else:\n",
    "            if phjFilename in phjCp1252EncodeFiles:\n",
    "                fsaAppno2DF = pd.read_csv(Path('/'.join([phjPath,phjFilename])),\n",
    "                                          header = 0,\n",
    "                                          encoding = 'cp1252')\n",
    "            else:\n",
    "                fsaAppno2DF = pd.read_csv(Path('/'.join([phjPath,phjFilename])),\n",
    "                                          header = 0)\n",
    "\n",
    "            \n",
    "            \n",
    "        # Preprocess the new file\n",
    "        fsaAppno2DF = phjPreProcess(phjDF = fsaAppno2DF,\n",
    "                                    phjCountryDF = phjCountryDF[['country_id','country','iso3166']],\n",
    "                                    phjCountryMissCode = phjMissingCodesDict['phjCountryMissCode'],\n",
    "                                    phjColsOfInterestList = phjColsOfInterestList)\n",
    "\n",
    "        # Add id column and move it to front\n",
    "        fsaAppno2DF['id'] = np.nan\n",
    "        fsaAppno2DF = fsaAppno2DF[['id'] + [i for i in fsaAppno2DF.columns if i not in ['id']]]\n",
    "\n",
    "        # Add yr and mth columns\n",
    "        # Extract year and month from filename and add to dataframe\n",
    "        # Originally used re library to extract regex named groups from\n",
    "        # regex that matched filename. This worked fine when filename always\n",
    "        # had same name format. However, when filenames became more variable\n",
    "        # it was necessary to use regex library because multiple regexes\n",
    "        # can be defined with the same named groups listed in each option.\n",
    "        # This is a klunky piece of code. The original name format of files\n",
    "        # included month name (in full). Subsequent code was written with\n",
    "        # this assumption. However, more recent filenames contain month\n",
    "        # in any one of a number of formats. This code takes extracted month value\n",
    "        # converts to a number and then converts number back to full month name.\n",
    "        #phjExtrDate = re.search('as-at-1-(?P<mth>.*)-(?P<yr>.*).csv',phjFilename)\n",
    "        phjExtrDate = regex.compile(phjCombRegex).search(phjFilename).groupdict()\n",
    "        \n",
    "        # Check if extracted mth value exists in the month lookup dictionary and, if\n",
    "        # so, replace phjExtrDate['mth'] value with full month name. This is achieved\n",
    "        # by converting extracted month value to a number and converting the number\n",
    "        # back to a full month name.\n",
    "        if phjExtrDate['mth'].lower() in phjMonthLUTDict:\n",
    "            phjExtrDate['mth'] = phjRevMonthLUTDict[phjMonthLUTDict[phjExtrDate['mth'].lower()]]\n",
    "        \n",
    "        # Convert 2-digit year to 4-digit year\n",
    "        if int(phjExtrDate['yr']) < 100:\n",
    "            phjExtrDate['yr'] = 2000 + int(phjExtrDate['yr'])\n",
    "        \n",
    "        # Add columns to dataframe\n",
    "        fsaAppno2DF['yr']  = int(phjExtrDate['yr'])\n",
    "        fsaAppno2DF['mth'] = int(phjMonthLUTDict[phjExtrDate['mth']])\n",
    "        \n",
    "        \n",
    "        print('Second dataframe (post processing)\\n================')\n",
    "        print(fsaAppno2DF)\n",
    "        print('\\n')\n",
    "    \n",
    "    except FileNotFoundError as e:\n",
    "        print('There was error opening the file {}.'.format(phjFilename))\n",
    "        print(e)\n",
    "        print('\\n')\n",
    "            \n",
    "        fsaAppno2DF = None\n",
    "        \n",
    "    \n",
    "    if (fsaAppno2DF is not None) & (fsaAppno2DF is not None):\n",
    "        \n",
    "        # Combine dataframes\n",
    "        # ------------------\n",
    "        phjCombinedDF = phjUpdateData(phjOriginalDF = fsaAppno1DF,\n",
    "                                      phjNewDF = fsaAppno2DF,\n",
    "                                      phjColsOfInterestList = phjColsOfInterestList,\n",
    "                                      phjIDColName = 'id',\n",
    "                                      phjYrColName = 'yr',\n",
    "                                      phjMthColName = 'mth',\n",
    "                                      phjGrpColName = 'appno',\n",
    "                                      phjNameColName = 'tradingname',\n",
    "                                      phjCountColName = 'count',\n",
    "                                      phjTokColName = 'tokens',\n",
    "                                      phjPrevTokColName = 'prevtokens',\n",
    "                                      phjJaccardColName = 'jd',\n",
    "                                      phjMatchColName = 'samename',\n",
    "                                      phjPrintResults = False)\n",
    "        \n",
    "        print('Combined dataframe (post processing)\\n================')\n",
    "        print(phjCombinedDF)\n",
    "        print('\\n')\n",
    "\n",
    "\n",
    "        # Write data to 'abattoir' table\n",
    "        # ------------------------------\n",
    "        \n",
    "        # The 'abattoir' table defines an id number for each incarnation of an abattoir. For abattoirs that\n",
    "        # have only minor changes in details, the same id number will be retained but the latest version\n",
    "        # of an abattoir's metadata will be retained. If an abattoir name changes considerably (e.g. the\n",
    "        # abattoir is bought by another company) then a new id number will be created.\n",
    "        \n",
    "        # The columns that need to be written back to the database are inlucded in the phjColsOfInterestList\n",
    "        # but with 'country' replaced by 'country_id' and 'postcode' replaced by 'postcode7'.\n",
    "        # Before writing back to the database, the 'yr' and 'mth' columns must be renamed to 'latest_yr' and\n",
    "        # latest 'latest_mth'; this is done by reversing the key:value pairs in the phjRenameYrMthDict dictionary.\n",
    "        # For code to replace the items within a list (i.e. 'country' to 'country_id' etc.) see:\n",
    "        # https://stackoverflow.com/questions/53294611/pandas-to-sql-changing-datatype-in-database-table\n",
    "        \n",
    "        # Delete contents of table first (don't use 'replace' because it changes the structure of the table)\n",
    "        with phjSQLEng.begin() as conn:     \n",
    "            conn.execute('DELETE FROM `abattoir`')\n",
    "        \n",
    "        # Then append data in dataframe (to empty database)\n",
    "        phjColNameRepl = {'country':'country_id',\n",
    "                          'postcode':'postcode7'}\n",
    "\n",
    "        phjCombinedDF[[phjColNameRepl.get(i,i) for i in ['id']+phjColsOfInterestList+['yr','mth']]].rename(columns = {v:k for k,v in phjRenameYrMthDict.items()}).to_sql(con = phjSQLEng,\n",
    "                                                                                                                                                                         name = 'abattoir',\n",
    "                                                                                                                                                                         if_exists = 'append',\n",
    "                                                                                                                                                                         index = False,\n",
    "                                                                                                                                                                         dtype = {'id':sqlalchemy.types.INTEGER(),\n",
    "                                                                                                                                                                                  'appno':sqlalchemy.types.VARCHAR(length=12),\n",
    "                                                                                                                                                                                  'tradingname':sqlalchemy.types.VARCHAR(length=254),\n",
    "                                                                                                                                                                                  'town':sqlalchemy.types.VARCHAR(length=254),\n",
    "                                                                                                                                                                                  'country_id':sqlalchemy.types.INTEGER(),\n",
    "                                                                                                                                                                                  'allactivities':sqlalchemy.types.VARCHAR(length=512),\n",
    "                                                                                                                                                                                  'slaughterhouse':sqlalchemy.types.VARCHAR(length=12),\n",
    "                                                                                                                                                                                  'x':sqlalchemy.types.FLOAT(),\n",
    "                                                                                                                                                                                  'y':sqlalchemy.types.FLOAT(),\n",
    "                                                                                                                                                                                  'latest_yr':sqlalchemy.types.INTEGER(),\n",
    "                                                                                                                                                                                  'latest_mth':sqlalchemy.types.INTEGER()})\n",
    "        \n",
    "        # Write data to 'appno' table\n",
    "        # ===========================\n",
    "        # This table links the appno for any given month and year with the name of the abattoir on that\n",
    "        # particular occasion using the 'abattoir_id' field. The only data that needs to be retained is\n",
    "        # the information relating to the yr and mth of the current field being processed.\n",
    "        # N.B. The appno table is set to have autoincrement for id column. Rows are added to the table\n",
    "        # for each new file. The table is not cleared beforehand. If the table needs to be deleted\n",
    "        # manually, the following SQL is report to be used to reset the autoincrement or to delete.\n",
    "        # However, in MySQL, autoincrement cannot be set to value less than current value. Instead, a\n",
    "        # constant value could be subtracted from the id column but this does not help if table is empty.\n",
    "        #\n",
    "        # DELETE FROM `appno`;\n",
    "        # ALTER TABLE `appno` AUTO_INCREMENT = 1;\n",
    "        #\n",
    "        # SET @phjMinID = (SELECT min(`id`) FROM `appno`) + 1;\n",
    "        # UPDATE `appno` set `id`=`id`- @phjMinID;\n",
    "        #\n",
    "        # Best option is perhaps to delete and recreate the table in SQL:\n",
    "        #\n",
    "        # DROP TABLE IF EXISTS `appno`;\n",
    "        #\n",
    "        # CREATE TABLE `appno` (\n",
    "        #   `id` int(4) unsigned NOT NULL AUTO_INCREMENT,\n",
    "        #   `appno` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',\n",
    "        #   `yr` int(4) unsigned NOT NULL,\n",
    "        #   `mth` int(2) unsigned NOT NULL,\n",
    "        #   `abattoir_id` int(4) unsigned NOT NULL,\n",
    "        #   PRIMARY KEY (`id`)\n",
    "        # ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;\n",
    "        \n",
    "        print('Combined appno dataframe')\n",
    "        print('------------------------')\n",
    "        \n",
    "        print(phjCombinedDF.loc[(phjCombinedDF['yr'] == int(phjExtrDate['yr'])) & (phjCombinedDF['mth'] == int(phjMonthLUTDict[phjExtrDate['mth']])),['appno','yr','mth','id']])\n",
    "\n",
    "\n",
    "        phjCombinedDF.loc[(phjCombinedDF['yr'] == int(phjExtrDate['yr'])) & (phjCombinedDF['mth'] == int(phjMonthLUTDict[phjExtrDate['mth']])),['appno','yr','mth','id']].rename(columns = {'id':'abattoir_id'}).to_sql(  con = phjSQLEng,\n",
    "                                                                                                                                                                                                                name = 'appno',\n",
    "                                                                                                                                                                                                                if_exists = 'append',\n",
    "                                                                                                                                                                                                                index = False,\n",
    "                                                                                                                                                                                                                dtype = {'appno':sqlalchemy.types.VARCHAR(length=12),\n",
    "                                                                                                                                                                                                                         'yr':sqlalchemy.types.INTEGER(),\n",
    "                                                                                                                                                                                                                         'mth':sqlalchemy.types.INTEGER(),\n",
    "                                                                                                                                                                                                                         'abattoir_id':sqlalchemy.types.INTEGER()})\n",
    "\n",
    "        \n",
    "print('All done')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retrieve data from database and pickle it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Path to directory\n",
    "phjPklPath = './outputs'\n",
    "\n",
    "# Define queries\n",
    "phjAppnoQuery = \"SELECT * FROM `appno`\"\n",
    "phjAbattoirQuery = \"SELECT * FROM `abattoir`\"\n",
    "\n",
    "# Retrieve Appno data\n",
    "# ===================\n",
    "phjRetrievedAppnoDF = pd.read_sql(phjAppnoQuery,\n",
    "                                  con = phjSQLEng)\n",
    "\n",
    "print('Retrieved Appno data from database')\n",
    "print('----------------------------------')\n",
    "print(phjRetrievedAppnoDF)\n",
    "print(phjRetrievedAppnoDF.dtypes)\n",
    "print('\\n')\n",
    "\n",
    "# Export as pickle and csv\n",
    "phjRetrievedAppnoDF.to_pickle(Path('/'.join([phjPklPath,'phjLatestAppno.pkl'])))\n",
    "phjRetrievedAppnoDF.to_csv(Path('/'.join([phjPklPath,'phjLatestAppno.csv'])),\n",
    "                           header = True,\n",
    "                           index = False)\n",
    "\n",
    "\n",
    "# Retrieve Abattoir data\n",
    "# ======================\n",
    "phjRetrievedAbattoirDF = pd.read_sql(phjAbattoirQuery,\n",
    "                                     con = phjSQLEng)\n",
    "\n",
    "print('Retrieved Abattoir data from database')\n",
    "print('-------------------------------------')\n",
    "print(phjRetrievedAbattoirDF)\n",
    "print(phjRetrievedAbattoirDF.dtypes)\n",
    "\n",
    "# Export as pickle and csv\n",
    "phjRetrievedAbattoirDF.to_pickle(Path('/'.join([phjPklPath,'phjLatestAbattoir.pkl'])))\n",
    "phjRetrievedAbattoirDF.to_csv(Path('/'.join([phjPklPath,'phjLatestAbattoir.csv'])),\n",
    "                              header = True,\n",
    "                              index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test reading pickle\n",
    "phjRetrievedPklAppnoDF = pd.read_pickle(Path('/'.join([phjPklPath,'phjLatestAppno.pkl'])))\n",
    "\n",
    "print('Retrieved pickled Appno data')\n",
    "print('----------------------------')\n",
    "print(phjRetrievedPklAppnoDF)\n",
    "print(phjRetrievedPklAppnoDF.dtypes)\n",
    "print('\\n')\n",
    "\n",
    "phjRetrievedPklAbattoirDF = pd.read_pickle(Path('/'.join([phjPklPath,'phjLatestAbattoir.pkl'])))\n",
    "\n",
    "print('Retrieved pickled Abattoir data')\n",
    "print('-------------------------------')\n",
    "print(phjRetrievedPklAbattoirDF)\n",
    "print(phjRetrievedPklAbattoirDF.dtypes)\n",
    "print('\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## List of latest abattoir data entries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On some occasions, it may be beneficial to have a list of just the latest appno details, rather than a record of all the occasions when abattoirs change owners. The following code downloads the data from the 'abattoir' table and retains only the latest details for each appno entry."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use data retrieved from database (see above)\n",
    "print(phjRetrievedAbattoirDF)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Number of unique appno values\n",
    "print('Number of unique Appno values: {}'.format(len(phjRetrievedAbattoirDF['appno'].unique())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "phjLatestAppnoDetailsDF = phjRetrievedAbattoirDF.sort_values(['appno','last_yr','last_mth'])\n",
    "\n",
    "phjLatestAppnoDetailsDF = phjLatestAppnoDetailsDF.groupby('appno').agg('last').reset_index(drop = False)\n",
    "\n",
    "print(phjLatestAppnoDetailsDF)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Path to directory for pickles is already defined above\n",
    "#phjPklPath = ./outputs\n",
    "\n",
    "# Export as pickle and csv\n",
    "phjLatestAppnoDetailsDF.to_pickle(Path('/'.join([phjPklPath,'phjLatestAppnoDetails.pkl'])))\n",
    "phjLatestAppnoDetailsDF.to_csv(Path('/'.join([phjPklPath,'phjLatestAppnoDetails.csv'])),\n",
    "                              header = True,\n",
    "                              index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <span style=\"color:red\">Please remember to restart and clear outputs before saving and pushing to GitHub repository.</span>"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}