ArcGIS Hubs Scanner PY.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8ead2357",
   "metadata": {},
   "source": [
    "# ArcGIS Hubs Scanner NoteBook Format\n",
    "\n",
    "> Original created/imported by Alexander Danielson (ardumn) Feburary 20th, 2023"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fd9303e",
   "metadata": {},
   "source": [
    "## Import Modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "728e05a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\"\"\"\n",
    "Original created on Wed Mar 15 09:18:12 2017\n",
    "Edited Dec 28 2018; January 8, 2019\n",
    "@author: kerni016\n",
    "\n",
    "Updated July 28, 2020\n",
    "Updated by Yijing Zhou @YijingZhou33\n",
    "\n",
    "Updated October 6, 2020\n",
    "Updated by Ziying Cheng @Ziiiiing\n",
    "\n",
    "Updated February 16, 2021\n",
    "Updated by Yijing Zhou @YijingZhou33\n",
    "-- populating spatial coverage based on bounding boxes\n",
    "\n",
    "Updated February 24, 2021\n",
    "Updated by Yijing Zhou @YijingZhou33\n",
    "-- Handling download link errors for newly added items\n",
    "\n",
    "Updated May 13, 2021\n",
    "Updated by Ziying Cheng @Ziiiiing\n",
    "-- Updating 'Genre' field\n",
    "\n",
    "Updated May 13, 2021\n",
    "Updated by Ziying Cheng @Ziiiiing\n",
    "-- Updating the csv report for retired items\n",
    "\n",
    "Updated Dec 31, 2021\n",
    "Updated by Ziying Cheng @Ziiiiing\n",
    "-- Updating the Provider, Member Of and Is Part Of fields\n",
    "\n",
    "Updated Apr 17, 2022\n",
    "Updated by Ziying Cheng @Ziiiiing\n",
    "-- Updating the Theme, Duplicates, Title\n",
    "\n",
    "Changed February 1, 2023\n",
    "@karenmajewicz\n",
    "removes complex functions in order to run more regularly\n",
    "\n",
    "\"\"\"\n",
    "# Need to define directory path (containing arcPortals.csv, folder \"jsons\" and \"reports\"), and list of fields desired in the printed report\n",
    "# The script currently prints one combined report - one of new items\n",
    "# The script also prints a status report giving the total number of resources in the portal\n",
    "\n",
    "import json\n",
    "import csv\n",
    "from ssl import AlertDescription\n",
    "import urllib\n",
    "import urllib.request\n",
    "from urllib.parse import urlparse, parse_qs\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",
   "id": "1d2b3b86",
   "metadata": {},
   "source": [
    "## Directory Setup Steps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "04f68242",
   "metadata": {},
   "outputs": [],
   "source": [
    "######################################\n",
    "\n",
    "# SET UP YOUR PATHS\n",
    "\n",
    "# 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/Thenewsguy/Documents/GitHub/arcgis-hubs'\n",
    "\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', 'Title Source', 'Resource Class',\n",
    "              'Theme', 'Keyword', 'Date Issued', 'Temporal Coverage', 'Date Range', 'Spatial Coverage',\n",
    "              'Bounding Box', 'Resource Type', 'Format', 'Information', 'Download', 'MapServer',\n",
    "              'FeatureServer', 'ImageServer', 'ID', 'Identifier', 'Provider', 'Code', 'Member Of', 'Is Part Of', 'Rights',\n",
    "              'Accrual Method', 'Date Accessioned', 'Access Rights']\n",
    "\n",
    "# list of fields to use for the deletedItems report\n",
    "delFieldsReport = ['ID', 'document[b1g_dateRetired_s]', 'document[b1g_status_s]', 'document[publication_state]']\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', '14': 'New Jersey', '99': 'Esri'}\n",
    "#######################################\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56a02e6b",
   "metadata": {},
   "source": [
    "## Cleans HTML Tags, Coverts Metadata from dictionary to Two CSVs (allNewItems and allNewDeletedItems)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2655364f",
   "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\n",
    "\n",
    "# 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",
    "\n",
    "\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 generates two csv files (allNewItems & allDeletedItems)\n",
    "\n",
    "\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)\n",
    "\n",
    "# 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",
    "\n",
    "\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\n",
    "\n",
    "\n",
    "def getTitles(data):\n",
    "    json_titles = {}\n",
    "    for x in range(len(data[\"dataset\"])):\n",
    "        json_titles[x] = data[\"dataset\"][x][\"title\"]\n",
    "    return json_titles\n",
    "\n",
    "'''Auto-generate Title field be like alternativeTitle [titleSource(place name)] {year if exist in alternative title}'''\n",
    "def format_title(alternativeTitle, titleSource):\n",
    "    # find if year exist in alternativeTitle\n",
    "    year = ''  \n",
    "    year_range = re.findall(r'(\\d{4})-(\\d{4})', alternativeTitle)\n",
    "#     single_year = re.match(r'.*([1-3][0-9]{3})', alternativeTitle)  \n",
    "    single_year = re.match(r'.*(17\\d{2}|18\\d{2}|19\\d{2}|20\\d{2})', alternativeTitle)    \n",
    "    if year_range:   # if a 'yyyy-yyyy' exists\n",
    "        year = '-'.join(year_range[0])\n",
    "        alternativeTitle = alternativeTitle.replace(year, '').strip().rstrip(',')\n",
    "    elif single_year:  # or if a 'yyyy' exists\n",
    "        year = single_year.group(1)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "        alternativeTitle = alternativeTitle.replace(year, '').strip().rstrip(',')\n",
    "        \n",
    "    title = alternativeTitle + ' [{}]'.format(titleSource)\n",
    "    \n",
    "    if year:\n",
    "        title += ' {' + year +'}'\n",
    "        \n",
    "    return title"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c6be182",
   "metadata": {},
   "source": [
    "## Same processes as above cell, except compares JSONs for duplicates and iterates within the folder and portals to produce clean IDs and circumvent broken links."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c88a0be4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "08b-42003 https://openac-alcogis.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 08b-42003\n",
      "04b-24003 https://maps.aacounty.org//api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 04b-24003\n",
      "10b-55003 https://data-ashlandcountywi.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55003\n",
      "11b-39009 https://data-athgis.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 11b-39009\n",
      "05b-27011 https://data-bigstonecounty.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27011\n",
      "04b-24009 https://calvert-county-open-data-calvertgis.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 04b-24009\n",
      "10b-55025-01 https://data-carpc.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55025-01\n",
      "04b-24013 https://data-carrollco-md.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 04b-24013\n",
      "05b-27019 http://data-carver.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27019\n",
      "08b-42027 http://gisdata-centrecountygov.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 08b-42027\n",
      "08b-42029 https://chester-county-s-gis-hub-chesco.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 08b-42029\n",
      "05b-27023 https://data-chippewa.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27023\n",
      "13c-02 https://data-cityofgi.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 13c-02\n",
      "10c-03 https://data-cityofmadison.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10c-03\n",
      "05c-02 https://information.stpaul.gov/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05c-02\n",
      "05b-27027 https://data-claycountymn.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27027\n",
      "12b-17031 https://hub-cookcountyil.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 12b-17031\n",
      "08b-42039 https://share-open-data-crawfordcountypa.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 08b-42039\n",
      "11b-39035 https://data-cuyahoga.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 11b-39035\n",
      "08b-42043 https://data-dauphinco.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 08b-42043\n",
      "11b-39041 https://gisdata-delco.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 11b-39041\n",
      "04f-01 https://dvrpc-dvrpcgis.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 04f-01\n",
      "07c-01 https://data.detroitmi.gov/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 07c-01\n",
      "10b-55025 https://gis-countyofdane.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "12b-17043 https://gisdata-dupage.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 12b-17043\n",
      "10b-55035-01 https://hub-eccounty.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55035-01\n",
      "01b-18163 https://dev-evansvilleapc.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 01b-18163\n",
      "11b-39049 https://auditor-fca.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 11b-39049\n",
      "04b-24021 https://gis-fcgmd.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 04b-24021\n",
      "10b-55125-01 https://data-vilas.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55125-01\n",
      "11a-01 https://ogrip-geohio.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 11a-01\n",
      "05b-27051 https://hub-co-grant-mn-us.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27051\n",
      "05b-27053 https://gis-hennepin.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27053\n",
      "09a-04 https://www.indianamap.org/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 09a-04\n",
      "03a-03 https://geodata.iowa.gov/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 03a-03\n",
      "10b-55059 https://dataportal.kenoshacounty.org/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55059\n",
      "12b-17097 https://data-lakecountyil.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 12b-17097\n",
      "10b-55021 https://opendata-cclid.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55021\n",
      "13c-01 https://data2-lincolnne.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 13c-01\n",
      "03b-19113 http://opendata-linncounty-gis.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 03b-19113\n",
      "11b-39093 https://data-loraingis.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 11b-39093\n",
      "https://www.arcgis.com/home/item.html?id=e437e02b56a34eb7b61ba27bf3619d86\n",
      "https://www.arcgis.com/home/item.html?id=c72420e6b9a1492983a95d8a7ad3bbc1\n",
      "https://www.arcgis.com/home/item.html?id=ef328a063edc4d2f95f37016c0507a61\n",
      "https://www.arcgis.com/home/item.html?id=efc4a10dd4f14a42a98c8998313e9e3d\n",
      "04a-01 http://data.imap.maryland.gov/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 04a-01\n",
      "02b-17113 http://www.mcgis.org//api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 02b-17113\n",
      "06a-02 https://gis-midnr.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 06a-02\n",
      "10b-55079 https://gis-mclio.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55079\n",
      "08b-42091 https://data-montcopa.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 08b-42091\n",
      "13a-04 https://data-outdoornebraska.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 13a-04\n",
      "13a-01 https://www.nebraskamap.gov/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 13a-01\n",
      "14a-02 https://gisdata-njdep.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 14a-02\n",
      "14a-01 https://njogis-newjersey.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 14a-01\n",
      "06b-26125 http://accessoakland.oakgov.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 06b-26125\n",
      "13b-31055 https://data-dogis.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 13b-31055\n",
      "04c-01 https://opendata.dc.gov/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 04c-01\n",
      "05c-01 https://opendata.minneapolismn.gov/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05c-01\n",
      "01c-02 http://data.indy.gov/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 01c-02\n",
      "12c-02 https://opioid-awareness-peoriacountygis.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 12c-02\n",
      "10b-55087 https://data-ocgis.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55087\n",
      "08a-02 https://newdata-dcnr.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 08a-02\n",
      "12b-17143 https://data-peoriacountygis.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 12b-17143\n",
      "08c-01-2 http://data-phl.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 08c-01-2\n",
      "10b-55093 https://data-piercecounty-wi.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55093\n",
      "05b-27119 https://hub-pcg.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27119\n",
      "05b-27121 https://hub-popecounty.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27121\n",
      "10b-55101 http://data.racinecounty.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55101\n",
      "05b-27123 https://data-ramseygis.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27123\n",
      "05b-27129 https://hub-renvilleco.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27129\n",
      "05b-27137 https://open-data-slcgis.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27137\n",
      "13b-31153 https://data2-sarpy.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 13b-31153\n",
      "10b-55111 https://data-saukgis.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55111\n",
      "06f-01 http://maps-semcog.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 06f-01\n",
      "09c-01 http://data-southbend.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 09c-01\n",
      "10b-55109 https://gis-scccdd.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55109\n",
      "11b-39151 http://opendata.starkcountyohio.gov/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 11b-39151\n",
      "06a-01 http://gis-michigan.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 06a-01\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "05b-27145 https://stearns-county-gis-stearns.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27145\n",
      "11b-39153 https://data-summitgis.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 11b-39153\n",
      "07d-02 https://mbgna-umich.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 07d-02\n",
      "12d-03 http://library-uchicago.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 12d-03\n",
      "07b-26161 https://data-washtenaw.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 07b-26161\n",
      "10b-55133 https://data-waukeshacounty.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55133\n",
      "10b-55135 https://data2017-04-05t135915451z-waupacacounty.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55135\n",
      "10b-55137 https://data-waushara.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55137\n",
      "05b-27167 https://hub-wilkinco.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27167\n",
      "10c-04 https://rapidsdata-wisconsinrapids.opendata.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10c-04\n",
      "10b-55141 https://opendata.woodcogis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 10b-55141\n",
      "05b-27173 https://hub-yellowmedicine.hub.arcgis.com/api/feed/dcat-us/1.1.json\n",
      "There is no comparison json for 05b-27173\n"
     ]
    }
   ],
   "source": [
    "# function that returns a dictionary of selected metadata elements into a dictionary of new items (newItemDict) 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",
    "#ALTERNATIVE TITLE\n",
    "        alternativeTitle = \"\"\n",
    "        try:\n",
    "            alternativeTitle = cleanData(newdata[\"dataset\"][y]['title'])\n",
    "        except:\n",
    "            alternativeTitle = newdata[\"dataset\"][y]['title']\n",
    "            \n",
    "#DESCRIPTION\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",
    "        elif description == \"{{description}}\":\n",
    "            description = description.replace(\"{{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",
    " #CREATOR\n",
    "        creator = newdata[\"dataset\"][y][\"publisher\"]\n",
    "        for pub in creator.values():\n",
    "            try:\n",
    "                creator = pub.replace(u\"\\u2019\", \"'\")\n",
    "            except:\n",
    "                creator = pub\n",
    "\n",
    "\n",
    "# DISTRIBUTION\n",
    "\n",
    "        format_types = []\n",
    "        resourceClass = \"\"\n",
    "        formatElement = \"\"\n",
    "        downloadURL = \"\"\n",
    "        resourceType = \"\"\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",
    "                    resourceClass = \"Datasets|Web services\"\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",
    "                    resourceType = \"Vector data\"\n",
    "\n",
    "                # If the Rest API is based on an ImageServer, change genre, type, and format to relate to imagery\n",
    "                if dictionary[\"title\"] == \"ArcGIS GeoService\":\n",
    "                    if 'accessURL' in dictionary.keys():\n",
    "                        webService = dictionary['accessURL']\n",
    "\n",
    "                        if webService.rsplit('/', 1)[-1] == 'ImageServer':\n",
    "                            resourceClass = \"Imagery|Web services\"\n",
    "                            formatElement = 'Imagery'\n",
    "                            resourceType = \"Satellite imagery\"\n",
    "                    else:\n",
    "                        resourceClass = \"\"\n",
    "                        formatElement = \"\"\n",
    "                        downloadURL = \"\"\n",
    "\n",
    "            # If the distribution section of the metadata is not structured in a typical way\n",
    "            except:\n",
    "                resourceClass = \"\"\n",
    "                formatElement = \"\"\n",
    "                downloadURL = \"\"\n",
    "                continue\n",
    "\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",
    "        theme = \"\"\n",
    "        keyword = newdata[\"dataset\"][y][\"keyword\"]\n",
    "        keyword_list = []\n",
    "        keyword_list = '|'.join(keyword).replace(' ', '')\n",
    "\n",
    "        dateIssued = cleanData(newdata[\"dataset\"][y]['issued']).split('T', 1)[0] \n",
    "        temporalCoverage = \"\"\n",
    "        dateRange = \"\"\n",
    "\n",
    "        information = cleanData(newdata[\"dataset\"][y]['landingPage'])\n",
    "        \n",
    "        try:\n",
    "            rights = cleanData(newdata[\"dataset\"][y]['license'])\n",
    "        except:\n",
    "            rights = \"\"   \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",
    "# GET CLEAN IDENTIFIER\n",
    "        slug = identifier.split('=', 1)[-1].replace(\"&sublayer=\", \"_\")\n",
    "        querystring = parse_qs(urlparse(identifier).query)\n",
    "        identifier_new = \"https://hub.arcgis.com/datasets/\" + \"\" + querystring[\"id\"][0]\n",
    "\n",
    "        # auto-generate Title as alternativeTitle [titleSource] {YEAR if it exists in alternativeTitle}\n",
    "        title = format_title(alternativeTitle, titleSource)\n",
    "        # auto-generate Temporal Coverage and Date Range\n",
    "        if re.search(r\"\\{(.*?)\\}\", title):     # if title has {YYYY} or {YYYY-YYYY}\n",
    "            temporalCoverage = re.search(r\"\\{(.*?)\\}\", title).group(1)\n",
    "            dateRange = temporalCoverage[:4] + '-' + temporalCoverage[-4:]\n",
    "        else:\n",
    "            temporalCoverage = 'Continually updated resource'\n",
    "\n",
    "        # if 'LiDAR' exists in Title or Description, add it to Resource Type\n",
    "        if 'LiDAR' in title or 'LiDAR' in description:\n",
    "            resourceType = 'LiDAR'\n",
    "        if 'imagery' in title or 'imagery' in description or 'imagery' in keyword_list:\n",
    "            resourceClass = 'Imagery'\n",
    "\n",
    "        metadataList = [title, alternativeTitle, description, language, creator, titleSource,\n",
    "                        resourceClass, theme, keyword_list, dateIssued, temporalCoverage,\n",
    "                        dateRange, spatialCoverage, bbox, resourceType,\n",
    "                        formatElement, information, downloadURL, mapServer, featureServer,\n",
    "                        imageServer, slug, identifier_new, provider, portalName, memberOf, isPartOf, rights,\n",
    "                        accrualMethod, dateAccessioned, accessRights]\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\n",
    "\n",
    "\n",
    "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', 'provider', '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['ID']\n",
    "        url = row['Identifier']\n",
    "        provider = row['Title']\n",
    "        titleSource = row['Publisher']\n",
    "        spatialCoverage = row['Spatial Coverage']\n",
    "        isPartOf  = row['ID']\n",
    "        memberOf = row['Member Of']\n",
    "        print(portalName, url)\n",
    "        accrualMethod = \"ArcGIS Hub\"\n",
    "        dateAccessioned = time.strftime('%Y-%m-%d')\n",
    "        accessRights = \"Public\"\n",
    "        language = \"eng\"\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",
    "        if dates:\n",
    "            PreviousActionDate = max(dates)\n",
    "        else:  # for brand new portals\n",
    "            PreviousActionDate='00000000'\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",
    "            # UPDATE: Makes a list of dataset title in the older json\n",
    "            older_titles = getTitles(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",
    "            # UPDATE\n",
    "            newjson_titles = {}\n",
    "            newitem_ids = {}\n",
    "            for y in range(len(newdata[\"dataset\"])):\n",
    "                identifier = newdata[\"dataset\"][y][\"identifier\"]\n",
    "                title = newdata[\"dataset\"][y][\"title\"]\n",
    "                newjson_titles[y] = title\n",
    "                if title not in older_titles.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",
    "\n",
    "            # UPDATE: 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",
    "                title = older_data[\"dataset\"][z][\"title\"]\n",
    "                if title not in newjson_titles.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\"] == \"ArcGIS GeoService\":  # TODO:UPDATE HERE\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",
    "                        deletedItemDict[slug] = [slug, time.strftime('%Y-%m-%d'), \"Inactive\", \"['unpublished']\"]\n",
    "\n",
    "            All_Deleted_Items.append(deletedItemDict)\n",
    "\n",
    "            # collects information for the status report\n",
    "            status_metalist = [len(newjson_titles), len(\n",
    "                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",
   "id": "f878621a",
   "metadata": {},
   "source": [
    "## Finally, produces two CSVs from allNewItems and allDeletedItems from harvest and populates with Spatial Coverage and Bounding Box Coordinates based on Portal Location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3682737c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "--------------------- Congrats! ╰(￣▽￣)╯ --------------------\n",
      "\n"
     ]
    }
   ],
   "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 + \\\n",
    "    \"/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)\n",
    "\n",
    "\n",
    "# ---------- Populating Spatial Coverage -----------\n",
    "\n",
    "\"\"\" set file path \"\"\"\n",
    "# df_csv = pd.read_csv(newItemsReport, encoding='unicode_escape')\n",
    "df_csv = pd.read_csv(newItemsReport)\n",
    "\n",
    "\"\"\" split csv file if necessary \"\"\"\n",
    "# if records come from Esri, the spatial coverage is considered as United States\n",
    "df_esri = df_csv[df_csv['Title Source'] == 'Esri'].reset_index(drop=True)\n",
    "df_csv = df_csv[df_csv['Title Source'] != 'Esri'].reset_index(drop=True)\n",
    "\n",
    "\n",
    "\"\"\" split state from column 'Title Source' \"\"\"\n",
    "# -----------------------------------------\n",
    "# The portal code is the main indicator:\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\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()]\n",
    "\n",
    "\"\"\" create bounding boxes for csv file \"\"\"\n",
    "\n",
    "\n",
    "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(\n",
    "        row.minX, row.minY, row.maxX, row.maxY) if str(row['Bounding Box']) != 'nan' else None, axis=1)\n",
    "    df['Roundcoords'] = df.apply(lambda row: ', '.join(\n",
    "        [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(\n",
    "        columns=['State', 'Coordinates'])\n",
    "\n",
    "    return [df_clean, df_wrongcoords]\n",
    "\n",
    "\n",
    "df_csvlist = format_coordinates(df_csv, 'ID')\n",
    "df_clean = df_csvlist[0]\n",
    "df_wrongcoords = df_csvlist[1]\n",
    "\n",
    "\n",
    "df_newitems = pd.read_csv(newItemsReport)\n",
    "df_newitems.to_csv(newItemsReport, index=False)\n",
    "\n",
    "\n",
    "print(\"\\n--------------------- Congrats! ╰(￣▽￣)╯ --------------------\\n\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}