harvest.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 1: Introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This demonstration is the Jupyter Notebook version of ***harvest*** script, which is used to keep track of changes to the publicly available geoportal data. Due to the instability of data source, we re-check the geoportals monthly to capture both newly added and broken links. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 2: Preparation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To run this script you need:\n",
    "* a csv with five columns (portalName, URL, provenance, publisher, and spatialCoverage) with details about ESRI open data portals to be checked for new records\n",
    "* directory path (containing arcPortals.csv, folder \"jsons\" and \"reports\")\n",
    "* list of fields desired in the printed report\n",
    "\n",
    "The script currently prints three reports:\n",
    "* two combined reports - one of new items and one with deleted items.\n",
    "* a status report giving the total number of resources in the portal, as well as the numbers of added and deleted items."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Part 3: Let's get started!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    ">Original created on Wed Mar 15 09:18:12 2017<br>\n",
    ">Edited Dec 28 2018; January 8, 2019; Dec 26-31, 2019; Jan 22-29 2020<br>\n",
    ">@author: kerni016\n",
    "\n",
    ">Updated July 28, 2020<br>\n",
    ">Updated by Yijing Zhou @YijingZhou33\n",
    "\n",
    ">Updated October 6, 2020<br>\n",
    ">Updated by Ziying Cheng @Ziiiiing\n",
    "\n",
    ">Updated October 22, 2020<br>\n",
    ">Updated by Ziying Cheng @Ziiiiing\n",
    "\n",
    ">Updated February 16, 2021<br>\n",
    ">Updated by Yijing Zhou @YijingZhou33<br>\n",
    ">-- Populating spatial coverage based on bounding boxes\n",
    "\n",
    ">Updated February 24, 2021<br>\n",
    ">Updated by Yijing Zhou @YijingZhou33<br>\n",
    ">-- Handling download link errors for newly added items\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1: Import modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import sys\n",
    "#!conda install --yes --prefix {sys.prefix} geopandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "import json\n",
    "import csv\n",
    "import urllib\n",
    "import urllib.request\n",
    "import os\n",
    "from html.parser import HTMLParser\n",
    "import decimal\n",
    "import re\n",
    "import time\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "from shapely.geometry import box\n",
    "import numpy as np\n",
    "from itertools import chain\n",
    "from itertools import repeat\n",
    "from functools import reduce\n",
    "import requests"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2: Manual items to change!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## names of the main directory containing folders named \"Jsons\" and \"Reports\"\n",
    "## Windows:\n",
    "## directory = r'D:\\Library RA\\dcat-metadata'\n",
    "## MAC or Linux:\n",
    "directory = r'/Users/majew030/GitHub/dcat-metadata/'\n",
    "\n",
    "## csv file contaning portal list\n",
    "portalFile = 'arcPortals.csv'\n",
    "\n",
    "## list of metadata fields from the DCAT json schema for open data portals desired in the final report\n",
    "fieldnames = ['Title', 'Alternative Title', 'Description', 'Language', 'Creator', 'Publisher', 'Genre',\n",
    "              'Subject', 'Keyword', 'Date Issued', 'Temporal Coverage', 'Date Range', 'Solr Year', 'Spatial Coverage',\n",
    "              'Bounding Box', 'Type', 'Geometry Type', 'Format', 'Information', 'Download', 'MapServer',\n",
    "              'FeatureServer', 'ImageServer', 'Slug', 'Identifier', 'Provenance', 'Code', 'Is Part Of', 'Status',\n",
    "              'Accrual Method', 'Date Accessioned', 'Rights', 'Access Rights', 'Suppressed', 'Child']\n",
    "\n",
    "## list of fields to use for the deletedItems report\n",
    "delFieldsReport = ['identifier', 'landingPage', 'portalName']\n",
    "\n",
    "## list of fields to use for the portal status report\n",
    "statusFieldsReport = ['portalName', 'total', 'new_items', 'deleted_items']\n",
    "\n",
    "## dictionary using partial portal code to find out where the data portal belongs\n",
    "statedict = {'01': 'Indiana', '02': 'Illinois', '03': 'Iowa', '04': 'Maryland', '04c-01': 'District of Columbia', \n",
    "             '04f-01': '04f-01', '05': 'Minnesota', '06': 'Michigan', '07': 'Michigan', '08': 'Pennsylvania', \n",
    "             '09': 'Indiana', '10': 'Wisconsin', '11': 'Ohio', '12': 'Illinois', '13': 'Nebraska', '99': 'Esri'}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 3: Clean up JSON file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## function to removes html tags from text\n",
    "class MLStripper(HTMLParser):\n",
    "    def __init__(self):\n",
    "        self.reset()\n",
    "        self.strict = False\n",
    "        self.convert_charrefs = True\n",
    "        self.fed = []\n",
    "\n",
    "    def handle_data(self, d):\n",
    "        self.fed.append(d)\n",
    "\n",
    "    def get_data(self):\n",
    "        return ''.join(self.fed)\n",
    "\n",
    "\n",
    "def strip_tags(html):\n",
    "    s = MLStripper()\n",
    "    s.feed(html)\n",
    "    return s.get_data()\n",
    "\n",
    "\n",
    "def cleanData(value):\n",
    "    fieldvalue = strip_tags(value)\n",
    "    return fieldvalue"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4: Export metadata as csv files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## function that prints metadata elements from the dictionary to a csv file (portal_status_report) \n",
    "## with as specified fields list as the header row. \n",
    "def printReport(report, dictionary, fields):\n",
    "    with open(report, 'w', newline='', encoding='utf-8') as outfile:\n",
    "        csvout = csv.writer(outfile)\n",
    "        csvout.writerow(fields)\n",
    "        for keys in dictionary:\n",
    "            allvalues = dictionary[keys]\n",
    "            csvout.writerow(allvalues)\n",
    "\n",
    "## Similar to the function above but generate two csv files (allNewItems & allDeletedItems)            \n",
    "def printItemReport(report, fields, dictionary):\n",
    "    with open(report, 'w', newline='', encoding='utf-8') as outfile:\n",
    "        csvout = csv.writer(outfile)\n",
    "        csvout.writerow(fields)\n",
    "        for portal in dictionary:\n",
    "            for keys in portal:\n",
    "                allvalues = portal[keys]\n",
    "                csvout.writerow(allvalues)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 5: Create a identifier dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## function that creates a dictionary with the position of a record in the data portal DCAT metadata json as the key \n",
    "## and the identifier as the value. \n",
    "def getIdentifiers(data):\n",
    "    json_ids = {}\n",
    "    for x in range(len(data[\"dataset\"])):\n",
    "        json_ids[x] = data[\"dataset\"][x][\"identifier\"]\n",
    "    return json_ids"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 6: Build up GeoBlacklight metadata schema "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## function that returns a dictionary of selected metadata elements into a dictionary of new items (newItemDict) \n",
    "## for each new item in a data portal. \n",
    "## This includes blank fields '' for columns that will be filled in manually later. \n",
    "def metadataNewItems(newdata, newitem_ids):\n",
    "    newItemDict = {}\n",
    "    # y = position of the dataset in the DCAT metadata json, v = landing page URLs\n",
    "    for y, v in newitem_ids.items():\n",
    "        identifier = v\n",
    "        metadata = []\n",
    "\n",
    "        title = \"\"\n",
    "        alternativeTitle = \"\"\n",
    "        try:\n",
    "            alternativeTitle = cleanData(newdata[\"dataset\"][y]['title'])\n",
    "        except:\n",
    "            alternativeTitle = newdata[\"dataset\"][y]['title']\n",
    "\n",
    "        description = cleanData(newdata[\"dataset\"][y]['description'])\n",
    "        ## Remove newline, whitespace, defalut description and replace singe quote, double quote\n",
    "        if description == \"{{default.description}}\":\n",
    "            description = description.replace(\"{{default.description}}\", \"\")\n",
    "        else:\n",
    "            description = re.sub(r'[\\n]+|[\\r\\n]+', ' ',\n",
    "                                 description, flags=re.S)\n",
    "            description = re.sub(r'\\s{2,}', ' ', description)\n",
    "            description = description.replace(u\"\\u2019\", \"'\").replace(u\"\\u201c\", \"\\\"\").replace(u\"\\u201d\", \"\\\"\").replace(\n",
    "                u\"\\u00a0\", \"\").replace(u\"\\u00b7\", \"\").replace(u\"\\u2022\", \"\").replace(u\"\\u2013\", \"-\").replace(u\"\\u200b\", \"\")\n",
    "\n",
    "        language = \"English\"\n",
    "\n",
    "        creator = newdata[\"dataset\"][y][\"publisher\"]\n",
    "        for pub in creator.values():\n",
    "            creator = pub.replace(u\"\\u2019\", \"'\")\n",
    "\n",
    "        format_types = []\n",
    "        genre = \"\"\n",
    "        formatElement = \"\"\n",
    "        typeElement = \"\"\n",
    "        downloadURL = \"\"\n",
    "        geometryType = \"\"\n",
    "        webService = \"\"\n",
    "\n",
    "        distribution = newdata[\"dataset\"][y][\"distribution\"]\n",
    "        for dictionary in distribution:\n",
    "            try:\n",
    "                ## If one of the distributions is a shapefile, change genre/format and get the downloadURL\n",
    "                format_types.append(dictionary[\"title\"])\n",
    "                if dictionary[\"title\"] == \"Shapefile\":\n",
    "                    genre = \"Geospatial data\"\n",
    "                    formatElement = \"Shapefile\"\n",
    "                    if 'downloadURL' in dictionary.keys():\n",
    "                        downloadURL = dictionary[\"downloadURL\"].split('?')[0]\n",
    "                    else:\n",
    "                        downloadURL = dictionary[\"accessURL\"].split('?')[0]\n",
    "\n",
    "                    geometryType = \"Vector\"\n",
    "\n",
    "                ## If the Rest API is based on an ImageServer, change genre, type, and format to relate to imagery\n",
    "                if dictionary[\"title\"] == \"Esri Rest API\":\n",
    "                    if 'accessURL' in dictionary.keys():\n",
    "                        webService = dictionary['accessURL']\n",
    "\n",
    "                        if webService.rsplit('/', 1)[-1] == 'ImageServer':\n",
    "                            genre = \"Aerial imagery\"\n",
    "                            formatElement = 'Imagery'\n",
    "                            typeElement = 'Image|Service'\n",
    "                            geometryType = \"Image\"\n",
    "                    else:\n",
    "                        genre = \"\"\n",
    "                        formatElement = \"\"\n",
    "                        typeElement = \"\"\n",
    "                        downloadURL = \"\"\n",
    "\n",
    "            # If the distribution section of the metadata is not structured in a typical way\n",
    "            except:\n",
    "                genre = \"\"\n",
    "                formatElement = \"\"\n",
    "                typeElement = \"\"\n",
    "                downloadURL = \"\"\n",
    "\n",
    "                continue\n",
    "\n",
    "        # If the item has both a Shapefile and Esri Rest API format, change type\n",
    "        if \"Esri Rest API\" in format_types:\n",
    "            if \"Shapefile\" in format_types:\n",
    "                typeElement = \"Dataset|Service\"\n",
    "\n",
    "        try:\n",
    "            bboxList = []\n",
    "            bbox = ''\n",
    "            spatial = cleanData(newdata[\"dataset\"][y]['spatial'])\n",
    "            typeDmal = decimal.Decimal\n",
    "            fix4 = typeDmal(\"0.0001\")\n",
    "            for coord in spatial.split(\",\"):\n",
    "                coordFix = typeDmal(coord).quantize(fix4)\n",
    "                bboxList.append(str(coordFix))\n",
    "            bbox = ','.join(bboxList)\n",
    "        except:\n",
    "            spatial = \"\"\n",
    "\n",
    "        subject = \"\"\n",
    "        keyword = newdata[\"dataset\"][y][\"keyword\"]\n",
    "        keyword_list = []\n",
    "        keyword_list = '|'.join(keyword).replace(' ', '')\n",
    "\n",
    "        dateIssued = cleanData(newdata[\"dataset\"][y]['issued'])\n",
    "        temporalCoverage = \"\"\n",
    "        dateRange = \"\"\n",
    "        solrYear = \"\"\n",
    "\n",
    "        information = cleanData(newdata[\"dataset\"][y]['landingPage'])\n",
    "\n",
    "        featureServer = \"\"\n",
    "        mapServer = \"\"\n",
    "        imageServer = \"\"\n",
    "\n",
    "        try:\n",
    "            if \"FeatureServer\" in webService:\n",
    "                featureServer = webService\n",
    "            if \"MapServer\" in webService:\n",
    "                mapServer = webService\n",
    "            if \"ImageServer\" in webService:\n",
    "                imageServer = webService\n",
    "        except:\n",
    "            print(identifier)\n",
    "\n",
    "        slug = identifier.rsplit('/', 1)[-1]\n",
    "        identifier_new = \"https://hub.arcgis.com/datasets/\" + slug\n",
    "        isPartOf = portalName\n",
    "\n",
    "        status = \"Active\"\n",
    "        accuralMethod = \"ArcGIS Hub\"\n",
    "        dateAccessioned = \"\"\n",
    "\n",
    "        rights = \"Public\"\n",
    "        accessRights = \"\"\n",
    "        suppressed = \"FALSE\"\n",
    "        child = \"FALSE\"\n",
    "\n",
    "        metadataList = [title, alternativeTitle, description, language, creator, publisher,\n",
    "                        genre, subject, keyword_list, dateIssued, temporalCoverage,\n",
    "                        dateRange, solrYear, spatialCoverage, bbox, typeElement, geometryType,\n",
    "                        formatElement, information, downloadURL, mapServer, featureServer,\n",
    "                        imageServer, slug, identifier_new, provenance, portalName, isPartOf, status,\n",
    "                        accuralMethod, dateAccessioned, rights, accessRights, suppressed, child]\n",
    "\n",
    "        ## deletes data portols except genere = 'Geospatial data' or 'Aerial imagery'\n",
    "        for i in range(len(metadataList)):\n",
    "            if metadataList[6] != \"\":\n",
    "                metadata.append(metadataList[i])\n",
    "\n",
    "        newItemDict[slug] = metadata\n",
    "\n",
    "        for k in list(newItemDict.keys()):\n",
    "            if not newItemDict[k]:\n",
    "                del newItemDict[k]\n",
    "\n",
    "    return newItemDict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 7: Check deleted and newly added links"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "All_New_Items = []\n",
    "All_Deleted_Items = []\n",
    "Status_Report = {}\n",
    "\n",
    "## Generate the current local time with the format like 'YYYYMMDD' and save to the variable named 'ActionDate'\n",
    "ActionDate = time.strftime('%Y%m%d')\n",
    "\n",
    "## List all files in the 'jsons' folder under the current directory and store file names in the 'filenames' list\n",
    "filenames = os.listdir('jsons')\n",
    "\n",
    "## Open a list of portals and urls ending in /data.json from input CSV\n",
    "## using column headers 'portalName', 'URL', 'provenance', 'SpatialCoverage'\n",
    "with open(portalFile, newline='', encoding='utf-8') as f:\n",
    "    reader = csv.DictReader(f)\n",
    "    for row in reader:\n",
    "        ## Read in values from the portals list to be used within the script or as part of the metadata report\n",
    "        portalName = row['portalName']\n",
    "        url = row['URL']\n",
    "        provenance = row['provenance']\n",
    "        publisher = row['publisher']\n",
    "        spatialCoverage = ''\n",
    "        print(portalName, url)\n",
    "\n",
    "        ## For each open data portal in the csv list...\n",
    "        ## create an empty list to extract all previous action dates only from file names\n",
    "        dates = []\n",
    "\n",
    "        ## loop over all file names in 'filenames' list and find the json files for the selected portal\n",
    "        ## extract the previous action dates only from these files and store in the 'dates' list\n",
    "        for filename in filenames:\n",
    "            if filename.startswith(portalName):\n",
    "                ## format of filename is 'portalName_YYYYMMDD.json'\n",
    "                ## 'YYYYMMDD' is located from index -13(included) to index -5(excluded)\n",
    "                dates.append(filename[-13:-5])\n",
    "\n",
    "        ## remove action date from previous dates if any\n",
    "        ## in case the script is run several times in one single day\n",
    "        ## so the actionDate JSONs can overwrite those generated earlier on the same day \n",
    "        if ActionDate in dates:\n",
    "            dates.remove(ActionDate)\n",
    "\n",
    "        ## find the latest action date from the 'dates' list\n",
    "        PreviousActionDate = max(dates)\n",
    "\n",
    "        ## renames file paths based on portalName and manually provided dates\n",
    "        oldjson = directory + \\\n",
    "            '/jsons/%s_%s.json' % (portalName, PreviousActionDate)\n",
    "        newjson = directory + '/jsons/%s_%s.json' % (portalName, ActionDate)\n",
    "\n",
    "        ## if newjson already exists, do not need to request again\n",
    "        if os.path.exists(newjson):\n",
    "            with open(newjson, 'r') as fr:\n",
    "                newdata = json.load(fr)\n",
    "        else:\n",
    "            response = urllib.request.urlopen(url)\n",
    "            ## check if data portal URL is broken\n",
    "            if response.headers['content-type'] != 'application/json; charset=utf-8':\n",
    "                print(\"\\n--------------------- Data portal URL does not exist --------------------\\n\",\n",
    "                      portalName, url,  \"\\n--------------------------------------------------------------------------\\n\")\n",
    "                continue\n",
    "            else:\n",
    "                newdata = json.load(response)\n",
    "\n",
    "            ## Saves a copy of the json to be used for the next round of comparison/reporting\n",
    "            with open(newjson, 'w', encoding='utf-8') as outfile:\n",
    "                json.dump(newdata, outfile)\n",
    "\n",
    "        ## collects information about number of resources (total, new, and old) in each portal\n",
    "        status_metadata = []\n",
    "        status_metadata.append(portalName)\n",
    "\n",
    "        ## Opens older copy of data/json downloaded from the specified Esri Open Data Portal.\n",
    "        ## If this file does not exist, treats every item in the portal as new.\n",
    "        if os.path.exists(oldjson):\n",
    "            with open(oldjson) as data_file:\n",
    "                older_data = json.load(data_file)\n",
    "\n",
    "            ## Makes a list of dataset identifiers in the older json\n",
    "            older_ids = getIdentifiers(older_data)\n",
    "\n",
    "            ## compares identifiers in the older json harvest of the data portal with identifiers in the new json,\n",
    "            ## creating dictionaries with\n",
    "            ## 1) a complete list of new json identifiers\n",
    "            ## 2) a list of just the items that appear in the new json but not the older one\n",
    "            newjson_ids = {}\n",
    "            newitem_ids = {}\n",
    "\n",
    "            for y in range(len(newdata[\"dataset\"])):\n",
    "                identifier = newdata[\"dataset\"][y][\"identifier\"]\n",
    "                newjson_ids[y] = identifier\n",
    "                if identifier not in older_ids.values():\n",
    "                    newitem_ids[y] = identifier\n",
    "\n",
    "            ## Creates a dictionary of metadata elements for each new data portal item.\n",
    "            ## Includes an option to print a csv report of new items for each data portal.\n",
    "            ## Puts dictionary of identifiers (key), metadata elements (values) for each data portal into a list\n",
    "            ## (to be used printing the combined report)\n",
    "            ## i.e. [portal1{identifier:[metadataElement1, metadataElement2, ... ],\n",
    "            ## portal2{identifier:[metadataElement1, metadataElement2, ... ], ...}]\n",
    "            All_New_Items.append(metadataNewItems(newdata, newitem_ids))\n",
    "\n",
    "            ## Compares identifiers in the older json to the list of identifiers from the newer json.\n",
    "            ## If the record no longer exists, adds selected fields into a dictionary of deleted items (deletedItemDict)\n",
    "            deletedItemDict = {}\n",
    "            \n",
    "            ## check deleted item's landing page, if it is broken, delete it\n",
    "            for z in range(len(older_data[\"dataset\"])):\n",
    "                identifier = older_data[\"dataset\"][z][\"identifier\"]\n",
    "                if identifier not in newjson_ids.values():\n",
    "                    distribution = older_data[\"dataset\"][z][\"distribution\"]\n",
    "                    for dictionary in distribution:\n",
    "                        if dictionary[\"title\"] == \"Shapefile\":\n",
    "                            slug = identifier.rsplit('/', 1)[-1]\n",
    "                        elif dictionary[\"title\"] == \"Esri Rest API\":\n",
    "                            if 'accessURL' in dictionary.keys():\n",
    "                                webService = dictionary['accessURL']\n",
    "                                if webService.rsplit('/', 1)[-1] == 'ImageServer':\n",
    "                                    slug = identifier.rsplit('/', 1)[-1]\n",
    "                        else: \n",
    "                            slug = ''\n",
    "                            \n",
    "                    ## only include records whose download link is either Shapefile or ImageServer\n",
    "                    if len(slug):                       \n",
    "                        del_metadata = []\n",
    "                        del_metalist = [slug, identifier, portalName]\n",
    "                        for value in del_metalist:\n",
    "                            del_metadata.append(value)\n",
    "                        deletedItemDict[identifier] = del_metadata\n",
    "\n",
    "            All_Deleted_Items.append(deletedItemDict)\n",
    "            \n",
    "            ## collects information for the status report\n",
    "            status_metalist = [len(newjson_ids), len(newitem_ids), len(deletedItemDict)]\n",
    "            for value in status_metalist:\n",
    "                status_metadata.append(value)\n",
    "\n",
    "        ## if there is no older json for comparions....\n",
    "        else:\n",
    "            print(\"There is no comparison json for %s\" % (portalName))\n",
    "            ## Makes a list of dataset identifiers in the new json\n",
    "            newjson_ids = getIdentifiers(newdata)\n",
    "\n",
    "            All_New_Items.append(metadataNewItems(newdata, newjson_ids))\n",
    "\n",
    "            ## collects information for the status report\n",
    "            status_metalist = [len(newjson_ids), len(newjson_ids), '0']\n",
    "            for value in status_metalist:\n",
    "                status_metadata.append(value)\n",
    "\n",
    "        Status_Report[portalName] = status_metadata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 8: Print three csv files (new items, deleted items, status report)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## prints two csv spreadsheets with all items that are new or deleted since the last time the data portals were harvested\n",
    "newItemsReport = directory + \"/reports/allNewItems_%s.csv\" % (ActionDate)\n",
    "printItemReport(newItemsReport, fieldnames, All_New_Items)\n",
    "\n",
    "delItemsReport = directory + \"/reports/allDeletedItems_%s.csv\" % (ActionDate)\n",
    "printItemReport(delItemsReport, delFieldsReport, All_Deleted_Items)\n",
    "\n",
    "reportStatus = directory + \\\n",
    "\"/reports/portal_status_report_%s.csv\" % (ActionDate)\n",
    "printReport(reportStatus, Status_Report, statusFieldsReport)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 9: Check if link is valid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_csv = pd.read_csv(newItemsReport, encoding='unicode_escape')\n",
    "\n",
    "print(\"\\n--------------------- Check if link is valid --------------------\\n\")\n",
    "\n",
    "def check_url(df, timeout):\n",
    "    totalcount = len(df.index)\n",
    "    countnotshp = countprivate = countok = count404 = count500 = countothercode = countconnection = counttimeout = 0\n",
    "    start_time = time.time()\n",
    "    filesize = download = imageserver = None\n",
    "    filesizelist, downloadlist, imageserverlist, oklist, checkagainlist = ([] for i in range(5))\n",
    "    for _, row in df.iterrows():\n",
    "        slug = row['Slug']\n",
    "        ## access the download link\n",
    "        if row['Format'] == 'Imagery':\n",
    "            url = row['ImageServer']\n",
    "        else:\n",
    "            url = row['Download']\n",
    "        try:\n",
    "            ## set timeout to avoid waiting for the server to response forever             \n",
    "            response = requests.get(url, timeout = timeout, proxies = urllib.request.getproxies())\n",
    "            response.raise_for_status()\n",
    "            ## vector data: check if it is a shapefile  \n",
    "            ## only keep the data source url\n",
    "            if 'content-type' in response.headers and response.headers['content-type'] == 'application/json; charset=utf-8':\n",
    "                countnotshp += 1\n",
    "                oklist.append(slug)\n",
    "                print(f'{slug}: Not a shapefile')\n",
    "            ## imagery Data: check if we could access ImageServer\n",
    "            ## only keep the data source url\n",
    "            elif 'Cache-Control' in response.headers and response.headers['Cache-Control'] == 'private':\n",
    "                countprivate += 1\n",
    "                oklist.append(slug)\n",
    "                print(f'{slug}: Could not access ImageServer')  \n",
    "            else:\n",
    "                ## if records with both vaild data source page and download link\n",
    "                ## query the file size and keep both links\n",
    "                if 'content-length' in response.headers:\n",
    "                    filesize = str(round(int(response.headers['content-length']) / 1000000, 4)) + ' MB'\n",
    "                if row['Download'] is not None:\n",
    "                    download = row['Download']\n",
    "                if row['ImageServer'] is not None:\n",
    "                    imageserver = row['ImageServer']\n",
    "                countok += 1\n",
    "                oklist.append(slug)\n",
    "                print(f'{slug}: Success')\n",
    "        ## check HTTP error: 404 (not found) or 500 (server error)       \n",
    "        except requests.exceptions.HTTPError as errh:\n",
    "            ## 404 error: drop this record\n",
    "            if errh.response.status_code == 404:\n",
    "                count404 += 1\n",
    "            ## 500 error: only keeps data source url    \n",
    "            elif errh.response.status_code == 500:\n",
    "                count500 += 1\n",
    "                oklist.append(slug)\n",
    "            ## other HTTP errors: only keeps data source url\n",
    "            else:\n",
    "                countothercode += 1\n",
    "            print (f'{slug}: {errh}')\n",
    "        ## check Connection error: need to be double-checked by increasing the timeout or even manually open it    \n",
    "        except requests.exceptions.ConnectionError as errc:\n",
    "            download = row['Download']\n",
    "            imageserver = row['ImageServer']\n",
    "            countconnection += 1\n",
    "            checkagainlist.append(slug)\n",
    "            print (f'{slug}: {errc}')\n",
    "        ## check Timeout error: need to be double-checked by increasing the timeout or even manually open it \n",
    "        except requests.exceptions.Timeout as errt:\n",
    "            download = row['Download']\n",
    "            imageserver = row['ImageServer']            \n",
    "            counttimeout += 1\n",
    "            checkagainlist.append(slug)\n",
    "            print (f'{slug}: {errt}')\n",
    "        \n",
    "        filesizelist.append(filesize)\n",
    "        downloadlist.append(download)\n",
    "        imageserverlist.append(imageserver)\n",
    "    \n",
    "    df['FileSize'] = filesizelist    \n",
    "    df['Download'] = downloadlist \n",
    "    df['ImageServer'] = imageserverlist \n",
    "    \n",
    "    errordict = {'OK': countok, 'Not a shapefile': countnotshp, 'Timeout Error': counttimeout,\n",
    "                 'Could not access ImageServer': countprivate, '404 Not Found': count404, \n",
    "                 '500 Internal Server Error': count500, 'Other HTTP Errors': countothercode, \n",
    "                 'Connection Errors': countconnection}     \n",
    "    msglist = [f'{k}: {v}, {round(v/totalcount * 100.0, 2)}%' for k, v in errordict.items()]\n",
    "    print('\\n---------- %s seconds ----------' % round((time.time() - start_time), 0), \n",
    "          '\\n\\n---------- Error Summary ----------', \n",
    "          '\\nAll records: %s' % totalcount)\n",
    "    for msg in msglist:\n",
    "        print(msg)\n",
    "    \n",
    "    ## records with runtime error need to be double-checked\n",
    "    return [df[df['Slug'].isin(oklist)], df[df['Slug'].isin(checkagainlist)]]\n",
    "\n",
    "df_total = check_url(df_csv, 3)\n",
    "df_ok = df_total[0].reset_index(drop = True)\n",
    "df_checkagain = df_total[1].reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## set the timeout as 10 seconds\n",
    "## if there still exists any records, manually check the download link to see if it works\n",
    "if len(df_checkagain.index):\n",
    "    df_checkagain = check_url(df_checkagain, 10)\n",
    "    df_checkok = df_checkagain[0].reset_index(drop = True)\n",
    "    df_manualcheck = df_checkagain[1].reset_index(drop = True)\n",
    "    df_manualcheck['Title'] = 'Manually check this link!'\n",
    "    df_csv = pd.concat([df_ok, df_checkok, df_manualcheck]).reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 10: Split csv file if necessary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## if records come from Esri, the spatial coverage is considered as United States\n",
    "df_esri = df_csv[df_csv['Publisher'] == 'Esri'].reset_index(drop=True)\n",
    "df_csv = df_csv[df_csv['Publisher'] != 'Esri'].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 11: Split state from column 'Publisher'\n",
    "The portal code is the main indicator: <br>\n",
    "- 01 - Indiana\n",
    "- 02 - Illinois\n",
    "- 03 - Iowa\n",
    "- 04 - Maryland\n",
    "- 04c-01 - District of Columbia\n",
    "- 04f-01 - Delaware, Philadelphia, Maryland, New Jersey\n",
    "- 05 - Minnesota\n",
    "- 06 - Michigan\n",
    "- 07 - Michigan\n",
    "- 08 - Pennsylvania\n",
    "- 09 - Indiana\n",
    "- 10 - Wisconsin\n",
    "- 11 - Ohio\n",
    "- 12 - Illinois\n",
    "- 13 - Nebraska\n",
    "- 99 - Esri"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\\n--------------------- Populating spatial coverage based on bounding box --------------------\\n\")\n",
    "\n",
    "df_csv['State'] = [statedict[row['Code']] if row['Code'] in statedict.keys(\n",
    "                    ) else statedict[row['Code'][0:2]] for _, row in df_csv.iterrows()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 12: Create bounding boxes for csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def format_coordinates(df, identifier):\n",
    "    ## create regular bouding box coordinate pairs and round them to 2 decimal places\n",
    "    ## manually generates the buffering zone\n",
    "    df = pd.concat([df, df['Bounding Box'].str.split(',', expand=True).astype(float).round(2)], axis=1).rename(\n",
    "        columns={0:'minX', 1:'minY', 2:'maxX', 3:'maxY'})\n",
    "    \n",
    "    ## check if there exists wrong coordinates and drop them\n",
    "    coordslist = ['minX', 'minY', 'maxX', 'maxY']\n",
    "    idlist = []\n",
    "    for _, row in df.iterrows():\n",
    "        for coord in coordslist:\n",
    "            ## e.g. [-180.0000,-90.0000,180.0000,90.0000]             \n",
    "            if abs(row[coord]) == 0 or abs(row[coord]) == 180:\n",
    "                idlist.append(row[identifier])\n",
    "        if (row.maxX - row.minX) > 10 or (row.maxY - row.minY) > 10:\n",
    "            idlist.append(row[identifier])\n",
    "    \n",
    "    ## create bounding box\n",
    "    df['Coordinates'] = df.apply(lambda row: box(row.minX, row.minY, row.maxX, row.maxY), axis=1)\n",
    "    df['Roundcoords'] = df.apply(lambda row: ', '.join([str(i) for i in [row.minX, row.minY, row.maxX, row.maxY]]), axis=1)\n",
    "    \n",
    "    ## clean up unnecessary columns\n",
    "    df = df.drop(columns = coordslist).reset_index(drop = True)\n",
    "    \n",
    "    df_clean = df[~df[identifier].isin(idlist)]\n",
    "    ## remove records with wrong coordinates into a new dataframe\n",
    "    df_wrongcoords = df[df[identifier].isin(idlist)].drop(columns = ['State', 'Coordinates'])\n",
    "    \n",
    "    return [df_clean, df_wrongcoords]\n",
    "\n",
    "df_csvlist = format_coordinates(df_csv, 'Slug')\n",
    "df_clean = df_csvlist[0]\n",
    "# df_wrongcoords = df_csvlist[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 13: Convert csv and GeoJSON file into dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_rawdata = gpd.GeoDataFrame(df_clean, geometry = df_clean['Coordinates'])\n",
    "gdf_rawdata.crs = 'EPSG:4326'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 14: Split dataframe and convert them into dictionary "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## e.g.\n",
    "## splitdict = {'Minnesota': {'Roundcoords 1': df_1, 'Roundcoords 2': df_2}, \n",
    "##              'Michigan':  {'Roundcoords 3': df_3, ...}, \n",
    "##               ...}\n",
    "\n",
    "splitdict = {}\n",
    "for state in list(gdf_rawdata['State'].unique()):\n",
    "    gdf_slice = gdf_rawdata[gdf_rawdata['State'] == state]\n",
    "    if state:\n",
    "        coordsdict = {}\n",
    "        for coord in list(gdf_slice['Roundcoords'].unique()):\n",
    "            coordsdict[coord] = gdf_slice[gdf_slice['Roundcoords'] == coord].drop(columns = ['State', 'Roundcoords'])\n",
    "        splitdict[state] = coordsdict\n",
    "    else:\n",
    "        sluglist = gdf_slice['Code'].unique()\n",
    "        print(\"Can't find the bounding box file: \", sluglist)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "**<a href='https://geopandas.org/reference/geopandas.sjoin.html#geopandas-sjoin'>`geopandas.sjoin`</a>** provides the following the criteria used to match rows:\n",
    "- intersects \n",
    "- within\n",
    "- contains"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 15: Perform spatial join on each record"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def split_placename(df, level):\n",
    "    formatlist = []\n",
    "    for _, row in df.iterrows():\n",
    "        ## e.g. 'Baltimore County, Baltimore City'\n",
    "        ## --> ['Baltimore County&Maryland', 'Baltimore City&Maryland']\n",
    "        if row[level] != 'nan':\n",
    "            placelist = row[level].split(', ')\n",
    "            formatname = ', '.join([(i + '&' + row['State']) for i in placelist])  \n",
    "        ## e.g. 'nan'\n",
    "        ## --> ['nan']\n",
    "        else:\n",
    "            formatname = 'nan'\n",
    "        formatlist.append(formatname)\n",
    "    return formatlist\n",
    "\n",
    "\"\"\" Spatial Join: both city and county bounding box files \"\"\"\n",
    "def city_and_county_sjoin(gdf_rawdata, op, state):\n",
    "    bboxpath = os.path.join('geojsons', state, f'{state}_City_bbox.json')\n",
    "    gdf_basemap = gpd.read_file(bboxpath)\n",
    "    ## spatial join\n",
    "    df_merged = gpd.sjoin(gdf_rawdata, gdf_basemap, op = op, how = 'left')[['City', 'County', 'State']].astype(str)\n",
    "    ## merge column 'City', 'County' into one column 'op'\n",
    "    df_merged['City'] = split_placename(df_merged, 'City')\n",
    "    df_merged['County'] = split_placename(df_merged, 'County')\n",
    "    df_merged[op] = df_merged[['City', 'County']].agg(', '.join, axis=1).replace('nan, nan', 'nan')\n",
    "    ## convert placename into list\n",
    "    oplist = df_merged[op].astype(str).values.tolist()\n",
    "    return oplist\n",
    "\n",
    "\"\"\" Spatial Join: either city or county bounding box file \"\"\"\n",
    "def city_or_county_sjoin(gdf_rawdata, op, state, level):\n",
    "    bboxpath = os.path.join('geojsons', state, f'{state}_{level}_bbox.json')\n",
    "    gdf_basemap = gpd.read_file(bboxpath)\n",
    "    ## spatial join\n",
    "    df_merged = gpd.sjoin(gdf_rawdata, gdf_basemap, op = op, how = 'left')[[level, 'State']].astype(str)\n",
    "    ## merge column level and 'State' into one column 'op'\n",
    "    df_merged[op] = df_merged.apply(lambda row: (row[level] + '&' + row['State']) if str(row[level]\n",
    "                                                ) != 'nan' else 'nan', axis = 1)\n",
    "    ## convert placename into list\n",
    "    oplist = df_merged[op].astype(str).values.tolist()\n",
    "    return oplist"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 16: Remove duplicates and 'nan' from place name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_nan(row):\n",
    "    ## e.g. ['nan', 'Minneapolis, Minnesota', 'Hennepin County, Minnesota', 'Hennepin County, Minnesota']\n",
    "    ## remove 'nan' and duplicates from list: ['Minneapolis, Minnesota, 'Hennepin County, Minnesota']\n",
    "    nonan = list(filter(lambda x: x != 'nan', row))\n",
    "    nodups = list(set(', '.join(nonan).split(', ')))\n",
    "    result = [i.replace('&', ', ') for i in nodups]\n",
    "    return result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 17: Fetch the proper join bouding box files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "operations = ['intersects', 'within', 'contains']\n",
    "def spatial_join(gdf_rawdata, state, length):\n",
    "    oplist = []\n",
    "    for op in operations:\n",
    "        bboxpath = os.path.join('geojsons', state, f'{state}_City_bbox.json')\n",
    "        \n",
    "        ## Disteict of Columbia doesn't have county boudning box file\n",
    "        if state == 'District of Columbia':\n",
    "            columbia = city_or_county_sjoin(gdf_rawdata, op, state, 'City')\n",
    "            pnamelist = remove_nan(columbia)\n",
    "        \n",
    "        ## check if there exists bounding box files\n",
    "        elif os.path.isfile(bboxpath):\n",
    "            city_county_state_list = city_and_county_sjoin(gdf_rawdata, op, state)\n",
    "            county_state_list = city_or_county_sjoin(gdf_rawdata, op, state, 'County')\n",
    "            pnamelist = city_county_state_list + county_state_list\n",
    "            pnamelist = remove_nan(pnamelist)\n",
    "       \n",
    "        ## missing bounding box file    \n",
    "        else: \n",
    "            print('Missing city bounding box file: ', state)\n",
    "            continue \n",
    "        \n",
    "        oplist.append(pnamelist)\n",
    "    ## duplicate placename list for all rows with the same bounding box    \n",
    "    allopslist = list(repeat(oplist, length))\n",
    "    ## ultimately it returns a dataframe with placename related to matching operation\n",
    "    ## e.g. dataframe = {'intersects', 'within', 'contains'}\n",
    "    df_match = pd.DataFrame.from_records(allopslist, columns = operations)\n",
    "    return df_match"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 18: Merge place names generated by three matching operations to raw data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mergeddf = []\n",
    "## loop through splitdict based on key 'State'\n",
    "for state, gdfdict in splitdict.items():\n",
    "    ## loop through records based on key 'Bounding Box'\n",
    "    for coord, gdf_split in gdfdict.items():\n",
    "        length = int(len(gdf_split))\n",
    "        df_comparison = spatial_join(gdf_split.iloc[[0]], state, length)\n",
    "        ## append dataframe {'intersects', 'within', 'contains'} to raw data\n",
    "        gdf_sjoin = pd.concat([gdf_split.reset_index(drop=True), df_comparison.reset_index(drop=True)], axis = 1)\n",
    "        mergeddf.append(gdf_sjoin)\n",
    "\n",
    "## merge all dataframes with different bounding boxes into one\n",
    "gdf_merged = reduce(lambda left, right: left.append(right), mergeddf).reset_index(drop = True)\n",
    "\n",
    "## replace [''] with None\n",
    "for op in operations:\n",
    "    gdf_merged[op] = gdf_merged[op].apply(lambda row: None if row == [''] else row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 19: Format spatial coverage based on GBL Metadata Template"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## e.g. ['Camden County, New Jersey', 'Delaware County, Pennsylvania', 'Philadelphia County, Pennsylvania']\n",
    "def format_placename(colname):\n",
    "    inv_map = {}\n",
    "    plist = []\n",
    "\n",
    "    ## {'Camden County': 'New Jersey', 'Delaware County': 'Pennsylvania', 'Philadelphia County': 'Pennsylvania'}\n",
    "    namedict = dict(item.split(', ') for item in colname)\n",
    "\n",
    "    ## {'New Jersey': ['Camden County'], 'Pennsylvania': ['Delaware County', 'Philadelphia County']}\n",
    "    for k, v in namedict.items():\n",
    "        inv_map[v] = inv_map.get(v, []) + [k] \n",
    "    \n",
    "    ## ['Camden County, New Jersey|New Jersey', 'Delaware County, Pennsylvania|Philadelphia County, Pennsylvania|Pennsylvania']\n",
    "    for k, v in inv_map.items():\n",
    "        pname = [elem + ', ' + k for elem in v]\n",
    "        pname.append(k)\n",
    "        plist.append('|'.join(pname))\n",
    "\n",
    "    ## Camden County, New Jersey|New Jersey|Delaware County, Pennsylvania|Philadelphia County, Pennsylvania|Pennsylvania\n",
    "    return '|'.join(plist)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 20: Select spatial coverage based on operaions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def populate_placename(df, identifier):\n",
    "    placenamelist = []\n",
    "    for _, row in df.iterrows():\n",
    "        if row['contains'] is None:\n",
    "            if row['intersects'] is None: \n",
    "                placename = ''\n",
    "            elif row['within'] is None:\n",
    "                placename = format_placename(row['intersects']) \n",
    "            else: \n",
    "                placename = format_placename(row['within']) \n",
    "        else:\n",
    "            placename = format_placename(row['contains']) \n",
    "        placenamelist.append(placename)\n",
    "    df['Spatial Coverage'] = placenamelist\n",
    "    return df.drop(columns = ['intersects', 'within', 'contains', 'Coordinates', 'geometry'])\n",
    "\n",
    "df_bbox = populate_placename(gdf_merged, 'Slug')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 21: Write to csv file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## check if there exists data portal from Esri\n",
    "if len(df_esri):\n",
    "    df_esri['Spatial Coverage'] = 'United States'\n",
    "    \n",
    "dflist = [df_esri, df_bbox]\n",
    "df_final = pd.concat(filter(len, dflist), ignore_index=True)\n",
    "\n",
    "df_final.to_csv(os.path.join('reports', newItemsReport), index = False)\n",
    "\n",
    "print(\"\\n--------------------- Congrats! ╰(￣▽￣)╯ --------------------\\n\")"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}