edatt_stats.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyPuxyYU7F8RY0PpUOs42EWK",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/daleelenteny/NWI_Workflow/blob/main/edatt_stats.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "plC4ped3hUFh"
      },
      "outputs": [],
      "source": [
        "import arcpy\n",
        "from arcgis.gis import GIS\n",
        "import arcgis\n",
        "import arcpy\n",
        "import pandas as pd\n",
        "import string\n",
        "from arcgis.features import GeoAccessor, GeoSeriesAccessor, FeatureLayerCollection\n",
        "from arcgis.mapping import WebMap\n",
        "import arcgis.geometry\n",
        "from arcgis.geometry import Point\n",
        "\n",
        "pd.options.display.float_format = '{:,}'.format\n",
        "\n",
        "agol_url = \"https://monroemi.maps.arcgis.com/home/index.html\"\n",
        "portal_url = \"https://gis.mocogeo.org/portal/home/\"\n",
        "portal_id_url = \"https://gis.mocogeo.org/portal/home/item.html?id=\"\n",
        "agol_id_url = \"https://monroemi.maps.arcgis.com/home/item.html?id=\"\n",
        "agol_name = \"username\"\n",
        "portal_name = \"password\"\n",
        "agol_password = \"username\"\n",
        "portal_password = \"password\"\n",
        "\n",
        "# Log in to AGOL and Portal\n",
        "agol = GIS(url=agol_url, username=agol_name, password=agol_password)\n",
        "portal = GIS(url=portal_url, username=portal_name, password=portal_password, profile = 'Dale', verify_cert = False)\n",
        "\n",
        "# Dependencies\n",
        "block_groups = r\"https://gis.mocogeo.org/server/rest/services/Hosted/Education_Attainment_Data_Tool_Geodatabase_FLC_/FeatureServer/1\"\n",
        "parcels = r\"https://gis.mocogeo.org/server/rest/services/Hosted/Education_Attainment_Data_Tool_Geodatabase_FLC_/FeatureServer/2\"\n",
        "ea_mp = portal.content.get(\"63bdb898e2c2437097e715d583a2b4dd\")\n",
        "\n",
        "def ScriptTool(parcel_input, search_distances):\n",
        "# Identify parcel and block group layers\n",
        "    flc = portal.content.search(\"7bdaa7ac55e5404490f16d2ae9744bcf\")[0]\n",
        "    for f in flc.layers:\n",
        "        if f.properties.name == \"Parcels\":\n",
        "            parcels = f.url\n",
        "        if f.properties.name == \"Block_Groups\":\n",
        "            block_groups = f.url\n",
        "### Part One: Create integer list (radii) of search distance inputs\n",
        "    arcpy.SetProgressor(\"step\", \"Creating list of search distances...\", 0, 7, 1)\n",
        "    arcpy.AddMessage(\"Creating list of search distances...\")\n",
        "    radii = []\n",
        "    for sd_int in search_distances.split(\";\"):\n",
        "        sd_int = int(str(sd_int))\n",
        "        radii.append(sd_int)\n",
        "\n",
        "### Part Two: Create polygon list of buffers around the specified point\n",
        "    arcpy.SetProgressor(\"step\", \"Creating a polygon for each search area...\", 0, 7, 1)\n",
        "    arcpy.AddMessage(\"Creating a polygon for each search area\")\n",
        "    buffers = []\n",
        "    polygon_jsons = []\n",
        "    buffer_jsons = []\n",
        "    sr = arcpy.Describe(parcels).spatialReference\n",
        "    with arcpy.da.SearchCursor(parcels, [\"x\", \"y\", \"parcelid\"]) as cursor:\n",
        "        for row in cursor:\n",
        "            if row[2] == parcel_input:\n",
        "                x = row[0]\n",
        "                y = row[1]\n",
        "                pt = arcpy.Point(x, y)\n",
        "                pg = arcpy.PointGeometry(pt, sr)\n",
        "                for r in radii:\n",
        "                    meters = r * 1609.34                                                                           #################CLIPPIN####################\n",
        "                    poly = pg.buffer(meters)\n",
        "                    poly\n",
        "                    buffers.append(poly)\n",
        "                    polygon_jsons.append(poly.JSON)\n",
        "\n",
        "    for b in buffers:\n",
        "        buffer_jsons.append(b.JSON)\n",
        "\n",
        "### Part Three: Create list of block group selections\n",
        "    arcpy.SetProgressor(\"step\", \"Selecting census block group data...\", 0, 7, 1)\n",
        "    arcpy.AddMessage(\"Selecting census block group data...\")\n",
        "    bg_areas = []\n",
        "    for polygon in buffers:\n",
        "        bg_selection, bg_multiple, count = arcpy.management.SelectLayerByLocation(block_groups, \"INTERSECT\", polygon)\n",
        "        #print(bg_selection, bg_multiple, count)\n",
        "        bg_areas.append(bg_selection)                                                                            ######## At this point an error has occured with the first bg selection\n",
        "\n",
        "### Part Four: Create sum_fields list variable\n",
        "    arcpy.SetProgressor(\"Calculating data...\", 0, 7, 1)\n",
        "    arcpy.AddMessage(\"Calculating data...\")\n",
        "    sum_fields = []\n",
        "    block_groups = flc.layers[1]\n",
        "    for field in block_groups.properties.fields:\n",
        "        if field.editable == True:\n",
        "            field.type = \"esriFieldTypeInteger\"\n",
        "            sum_fields.append(field.name)\n",
        "    sum_fields.remove(\"fips\")\n",
        "\n",
        "### Part Five: Create field dictionary that will be used to populate dataframe. All values will be 0 at this point\n",
        "    field_dict = {}\n",
        "    col_names = []\n",
        "    for field in sum_fields:\n",
        "        field_dict[field] = 0\n",
        "        col_names.append(field)\n",
        "    # I think these will be important?\n",
        "    row_count = 0\n",
        "    tract_count = 0\n",
        "    key_list = []\n",
        "    value_list = []\n",
        "\n",
        "### Part Six: Algorithm to populate dictionary\n",
        "    df_dict = {}\n",
        "    for bg_selection in bg_areas:\n",
        "        tract_count += 1\n",
        "        result = arcpy.GetCount_management(bg_selection)\n",
        "        ftr_count = int(result.getOutput(0))\n",
        "        for fd_key, fd_value in field_dict.items():\n",
        "            with arcpy.da.SearchCursor(bg_selection, fd_key) as rows:\n",
        "                for row in rows:\n",
        "                    row_count += 1\n",
        "                    row = str(row)\n",
        "                    row = row.replace(\",\", \"\").replace(\"(\", \"\").replace(\")\", \"\").replace(\"'\", \"\")\n",
        "                    row = int(row)\n",
        "                    field_dict[fd_key] += row\n",
        "        for key, value in field_dict.items():\n",
        "            if key not in key_list:\n",
        "                key_list.append(key)\n",
        "                df_dict[key] = []\n",
        "            if key in key_list and df_dict[key] == ():\n",
        "                df_dict[key] = value\n",
        "            if key in key_list and df_dict[key] != ():\n",
        "                df_dict[key].append(value)\n",
        "\n",
        "### Part Seven: Dictionary into dataframe\n",
        "    df = pd.DataFrame.from_dict(df_dict)\n",
        "    df.insert(0, \"Search_Radius_in_Miles\", radii)\n",
        "    col_names.remove(\"total_population\")\n",
        "    merge_dict = {}\n",
        "    for col in col_names:\n",
        "        merge_dict[col] = \"perc_{}\".format(col)\n",
        "        loc = df.columns.get_loc(col) + 1\n",
        "        df.insert(loc, \"perc_{}\".format(col), 0)\n",
        "        num = df[col].astype(int)\n",
        "        den = df[\"total_population\"].astype(float)\n",
        "        perc = ((num/den)*100)\n",
        "        perc = perc.round(decimals=2)\n",
        "        df[\"perc_{}\".format(col)] = perc\n",
        "        df = df.round(2)\n",
        "\n",
        "### Part Eight: Dataframe to Spatial Dataframe to feature layer\n",
        "    arcpy.SetProgressor(\"step\", \"Exporting dataframe to feature layer...\", 0, 7, 1)\n",
        "    arcpy.AddMessage(\"Exporting dataframe to feature layer...\")\n",
        "\n",
        "    # Insert commas\n",
        "    df = df.applymap('{:,.2f}'.format)                                                                              # This is where I will format the numbers\n",
        "\n",
        "    # Insert geometry column\n",
        "    df[\"SHAPE\"] = buffer_jsons\n",
        "    sdf = GeoAccessor.from_df(df, geometry_column = \"SHAPE\")\n",
        "    #sdf.set_index(\"Search_Radius_in_Miles\", inplace = True)\n",
        "    # Define sdf properties\n",
        "    title = \"Output_Data\"\n",
        "    gis = portal\n",
        "    tags = \"edatt\"\n",
        "    folder = \"Education Attainment Outputs\"                                                                          # Will sharing this folder be important?\n",
        "    sanitize_columns = True\n",
        "    service_name = \"sdf\"\n",
        "    # Export to feature layer\n",
        "    source_sdf_fl = sdf.spatial.to_featurelayer(title, gis, sanitize_columns)\n",
        "    source_url = source_sdf_fl.url\n",
        "\n",
        "### Part Nine: Edit field aliases\n",
        "    arcpy.SetProgressor(\"step\", \"Cleaning up data...\", 0, 7, 1)\n",
        "    arcpy.AddMessage(\"Cleaning up data...\")\n",
        "    # Identify layer to work on\n",
        "    l = source_sdf_fl.layers[0]\n",
        "    # This is the key/property I will use\n",
        "    orig_di = l.properties['drawingInfo']\n",
        "\n",
        "    # Construct an alias dictionary\n",
        "    alias_dict = {}\n",
        "    for field in l.properties.fields:\n",
        "        if field.editable == True:\n",
        "            # Phrase-level replacements\n",
        "            orig_name = field.name\n",
        "            new_name = orig_name.replace(\"_s_\", \"'s \")\n",
        "            new_name = new_name.replace(\"_\", \" \")\n",
        "            new_name = new_name.replace(\"perc\", \"Percentage with\")\n",
        "            new_name = new_name.replace(\"ged\", \"GED\")\n",
        "            # Word-level replacements\n",
        "            phrase = \"\"\n",
        "            # Adding in appropriate phrase\n",
        "            abs_names = [\"Percentage\", \"search\", \"total\", \"Adults 25+\"]\n",
        "            if new_name.split(\" \")[0] not in abs_names:\n",
        "                new_name = new_name.replace(new_name, \"Adults 25+ with \" + new_name)\n",
        "            # Capitalizing words\n",
        "            for word in new_name.split(\" \"):\n",
        "                if word != \"with\" and word != \"or\":\n",
        "                    word = word.replace(word, word[0].upper() + word[1:])\n",
        "                if len(phrase) > 0:\n",
        "                    phrase = phrase + \" \" + word\n",
        "                else:\n",
        "                    phrase = word\n",
        "            # Define dictionary\n",
        "            alias_dict[orig_name] = phrase\n",
        "    # Identify layer to work on\n",
        "    #l = source_sdf_fl.layers[0]\n",
        "    # Create an empty list of new fields\n",
        "    # Iteratively add to it based on properties within the dictionaries\n",
        "    new_fields = []\n",
        "    for a in l.properties['fields']:\n",
        "        if a['editable'] == False:\n",
        "            new_fields.append(a)\n",
        "        if a['editable'] == True:\n",
        "            for key, val in alias_dict.items():\n",
        "                if a['alias'] == key:\n",
        "                    a['alias'] = val\n",
        "                    new_fields.append(a)\n",
        "\n",
        "### Part Ten: Update drawing info\n",
        "    # Palette\n",
        "    dark_blue = [35, 97, 146, 150, 1.0]\n",
        "    pale_blue = [164, 190, 208, 150]\n",
        "    gray = [227, 233, 237, 150]\n",
        "    teal = [128, 224, 214, 150]\n",
        "    # Preset symbols\n",
        "    outline = {'color': dark_blue, 'width':'2', 'type':'esriSLS', 'style':'esriSLSLongDash'}\n",
        "    # Identify layer and start with drawingInfo, work from there\n",
        "    l = source_sdf_fl.layers[0]\n",
        "    orig_di = l.properties['drawingInfo']\n",
        "    # Create a copy of it\n",
        "    new_di = orig_di\n",
        "    # Newly define as a simple renderer\n",
        "    new_di['renderer']['type'] = 'simple'\n",
        "    new_di['renderer']['symbol'] = {'color':pale_blue, 'type':'esriSFS', 'style':'esriSFSSolid', 'outline':outline}\n",
        "    # Update definition\n",
        "    l.manager.update_definition({'drawingInfo':new_di})\n",
        "\n",
        "### Part Eleven: Add layer to map and configure map\n",
        "    arcpy.SetProgressor(\"step\", \"Adding layer to map...\", 0, 7, 1)\n",
        "    # Add layer to map\n",
        "    mp = portal.content.get(\"63bdb898e2c2437097e715d583a2b4dd\")\n",
        "    ea_mp = WebMap(mp)\n",
        "    for l in ea_mp.layers:\n",
        "        if l.title != \"Education Attainment  Data Tool Geodatabase FLC  - Parcels\" and l.title != \"Ontario\" and l.title != \"Monroe County Boundary\":\n",
        "            ea_mp.remove_layer(l)\n",
        "            ea_mp.update()\n",
        "    ea_mp.add_layer(source_sdf_fl, options = {'opacity':0.65})\n",
        "    ea_mp.update()\n",
        "    # Change the output name\n",
        "    # This is how I identify the random output name\n",
        "    output_name = source_sdf_fl.layers[0].properties.name\n",
        "    # This changes the title of the layer - I just need to find a way to obtain the id so it does this every time\n",
        "    mp = portal.content.get(\"63bdb898e2c2437097e715d583a2b4dd\")\n",
        "    ea_mp = WebMap(mp)\n",
        "    for l in ea_mp.layers:\n",
        "        if l['title'] == output_name:\n",
        "            l['title'] = \"Output Data\"\n",
        "            # Now this edits the popups\n",
        "            output_data = l\n",
        "            output_data.popupInfo['title'] = \"Search Radius in Miles: {search_radius_in_miles}\"\n",
        "            for popup in output_data.popupInfo['fieldInfos']:\n",
        "                if popup['isEditable'] == True:\n",
        "                    popup['visible'] = True\n",
        "                else:\n",
        "                    popup['visible'] = False\n",
        "                ea_mp.update()\n",
        "\n",
        "    # Define extent dictionary\n",
        "    # Assign extent values based on dictionary\n",
        "    extent_dict = {}\n",
        "    extent_dict['xmin'] = source_sdf_fl.extent[0][0]\n",
        "    extent_dict['ymin'] = source_sdf_fl.extent[0][1]\n",
        "    extent_dict['xmax'] = source_sdf_fl.extent[1][0]\n",
        "    extent_dict['ymax'] = source_sdf_fl.extent[1][1]\n",
        "    # Assign extent values based on dictionary\n",
        "    for k, v in extent_dict.items():\n",
        "        ea_mp.definition['initialState']['viewpoint']['targetGeometry'][k] = v\n",
        "    ea_mp.update()\n",
        "\n",
        "    # Finish\n",
        "    return radii, buffers, poly, polygon_jsons, buffer_jsons, bg_areas, poly, sum_fields, col_names, field_dict, df_dict, merge_dict, df, sdf, source_sdf_fl, source_url, new_fields, l, output_data, orig_di, ea_mp\n",
        "\n",
        "# This is used to execute code if the file was run but not imported\n",
        "if __name__ == '__main__':\n",
        "    # Tool parameter accessed with GetParameter or GetParameterAsText\n",
        "    parcel_input = arcpy.GetParameterAsText(0)\n",
        "    search_distances = arcpy.GetParameterAsText(1)\n",
        "\n",
        "radii, buffers, poly, polygon_jsons, buffer_jsons, bg_areas, poly, sum_fields, col_names, field_dict, df_dict, merge_dict, df, sdf, source_sdf_fl, source_url, new_fields, l, output_data, orig_di, ea_mp = ScriptTool(parcel_input, search_distances)\n",
        "\n",
        "# This is used to execute code if the file was run but not imported\n",
        "if __name__ == '__main__':\n",
        "    # Tool parameter accessed with GetParameter or GetParameterAsText\n",
        "    parcel_input = arcpy.GetParameterAsText(0)\n",
        "    search_distances = arcpy.GetParameterAsText(1)\n",
        "\n",
        "ScriptTool(parcel_input, search_distances)"
      ]
    }
  ]
}