EQIPAnalysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import arcpy and set workspace\n",
    "import arcpy\n",
    "from arcpy import env\n",
    "env.workspace=\"H:/UEP 235/Final_Project/EQIPAnalysis/EQIPAnalysis.gdb\"\n",
    "env.addOutputsToMap = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 6:51:13 PM\",\"Adding County_State_ID to FederalCodes_Counties...\",\"Succeeded at Monday, December 18, 2023 6:51:17 PM (Elapsed Time: 3.63 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'FederalCodes_Counties'>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Gathering state names for each county \n",
    "arcpy.management.MakeFeatureLayer(\n",
    "    \"H:/UEP 235/Final_Project/Federal Codes/Gazetteer_National_GDB/Gazetteer_National_GDB.gdb/Topical_Gazetteer/FederalCodes\",\n",
    "    \"FederalCodesCounties\",\n",
    "    \"Census_Class_Code LIKE 'H%' OR Census_Class_Code='C7'\"\n",
    ")\n",
    "arcpy.conversion.ExportFeatures(\n",
    "    in_features=\"FederalCodesCounties\",\n",
    "    out_features=\"FederalCodes_Counties\",\n",
    "    use_field_alias_as_name=\"NOT_USE_ALIAS\",\n",
    "    field_mapping='feature_id \"Feature_ID\" true true false 4 Long 0 0,First,#,FederalCodesCounties,feature_id,-1,-1;feature_name \"Feature_Name\" true true false 120 Text 0 0,First,#,FederalCodesCounties,feature_name,0,120;feature_class \"Feature_Class\" true true false 50 Text 0 0,First,#,FederalCodesCounties,feature_class,0,50;state_name \"State_Name\" true true false 100 Text 0 0,First,#,FederalCodesCounties,state_name,0,100;state_numeric \"State_Numeric\" true true false 2 Text 0 0,First,#,FederalCodesCounties,state_numeric,0,2;county_name \"County_Name\" true true false 100 Text 0 0,First,#,FederalCodesCounties,county_name,0,100;county_numeric \"County_Numeric\" true true false 5 Text 0 0,First,#,FederalCodesCounties,county_numeric,0,5',\n",
    ")\n",
    "arcpy.management.CalculateField(\n",
    "    \"FederalCodes_Counties\",\n",
    "    \"County_State_ID\",\n",
    "    \"!state_numeric!!county_numeric!\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 6:51:44 PM\",\"Succeeded at Monday, December 18, 2023 6:52:06 PM (Elapsed Time: 21.81 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'H:\\\\UEP 235\\\\Final_Project\\\\EQIPAnalysis\\\\EQIPAnalysis.gdb\\\\Boundary_Polygons\\\\AllCountiesWStates'>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adding state names to county shapefile\n",
    "arcpy.management.AddJoin(\n",
    "    in_layer_or_view=\"AllCounties2022\",\n",
    "    in_field=\"GEOID\",\n",
    "    join_table=\"FederalCodes_Counties\",\n",
    "    join_field=\"County_State_ID\",\n",
    "    join_type=\"KEEP_COMMON\",\n",
    "    index_join_fields=\"INDEX_JOIN_FIELDS\"\n",
    ")\n",
    "arcpy.management.CopyFeatures(\n",
    "    \"AllCounties2022_Layer\",\n",
    "    \"H:/UEP 235/Final_Project/EQIPAnalysis/EQIPAnalysis.gdb/Boundary_Polygons/AllCountiesWStates\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 6:52:20 PM\",\"Succeeded at Monday, December 18, 2023 6:52:31 PM (Elapsed Time: 11.40 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'H:\\\\UEP 235\\\\Final_Project\\\\EQIPAnalysis\\\\EQIPAnalysis.gdb\\\\Boundary_Polygons\\\\ContiguousUSCounties'>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Extract contiguous US counties\n",
    "arcpy.analysis.Select(\n",
    "    \"AllCountiesWStates\",\n",
    "    \"H:/UEP 235/Final_Project/EQIPAnalysis/EQIPAnalysis.gdb/Boundary_Polygons/ContiguousUSCounties\",\n",
    "    \"AllCounties2022_STATEFP NOT IN ('11', '69', '60', '66', '15', '02', '72', '78','!',' ')\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 6:52:47 PM\",\"Succeeded at Monday, December 18, 2023 6:52:48 PM (Elapsed Time: 0.03 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'ContiguousUSCounties_lyr'>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Fix St. vs St issues\n",
    "fc=\"ContiguousUSCounties_lyr\"\n",
    "fields=['AllCounties2022_NAME']\n",
    "\n",
    "arcpy.management.MakeFeatureLayer(\n",
    "    \"ContiguousUSCounties\",\n",
    "    \"ContiguousUSCounties_lyr\"\n",
    ")\n",
    "arcpy.management.SelectLayerByAttribute(\n",
    "    in_layer_or_view=\"ContiguousUSCounties_lyr\",\n",
    "    selection_type=\"NEW_SELECTION\",\n",
    "    where_clause=\"AllCounties2022_NAME LIKE 'St.%'\",\n",
    ")\n",
    "with arcpy.da.UpdateCursor(fc, fields) as cursor:\n",
    "    for row in cursor:\n",
    "        name=row[0]\n",
    "        row[0] = name[0:2]+' '+name[4:]\n",
    "        cursor.updateRow(row) \n",
    "        \n",
    "arcpy.management.SelectLayerByAttribute(\n",
    "    in_layer_or_view=\"ContiguousUSCounties_lyr\",\n",
    "    selection_type=\"CLEAR_SELECTION\",\n",
    ")\n",
    "\n",
    "#Fix 's vs. s issues\n",
    "fc=\"ContiguousUSCounties_lyr\"\n",
    "fields=['AllCounties2022_NAME']\n",
    "arcpy.management.MakeFeatureLayer(\n",
    "    \"ContiguousUSCounties\",\n",
    "    \"ContiguousUSCounties_lyr\"\n",
    ")\n",
    "arcpy.management.SelectLayerByAttribute(\n",
    "    in_layer_or_view=\"ContiguousUSCounties_lyr\",\n",
    "    selection_type=\"NEW_SELECTION\",\n",
    "    where_clause=\"AllCounties2022_NAME LIKE '%''s'\",\n",
    ")\n",
    "with arcpy.da.UpdateCursor(fc, fields) as cursor:\n",
    "    for row in cursor:\n",
    "        name=row[0]\n",
    "        row[0] = name[:-2]+name[-1:]\n",
    "        cursor.updateRow(row) \n",
    "\n",
    "arcpy.management.SelectLayerByAttribute(\n",
    "    in_layer_or_view=\"ContiguousUSCounties_lyr\",\n",
    "    selection_type=\"CLEAR_SELECTION\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 6:53:25 PM\",\"Succeeded at Monday, December 18, 2023 6:53:33 PM (Elapsed Time: 7.73 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'H:/UEP 235/Final_Project/EQIPAnalysis/EQIPAnalysis.gdb\\\\EQIPContracts'>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create unique text ID for states and counties together\n",
    "arcpy.management.CalculateField(\n",
    "    in_table=\"ContiguousUSCounties\",\n",
    "    field=\"County_States\",\n",
    "    expression='Concatenate($feature.AllCounties2022_NAME,\", \",$feature.FederalCodes_Counties_state_name)',\n",
    "    expression_type=\"ARCADE\",\n",
    "    code_block=\"\"\"# Joins two or more fields into a single string value\n",
    "                # More calculator examples at esriurl.com/CalculatorExamples\n",
    "                def ConcatenateFields(*args):\n",
    "                sep = \"-\"\n",
    "                return sep.join([i for i in args if i])\"\"\",\n",
    "    field_type=\"TEXT\",\n",
    "    enforce_domains=\"NO_ENFORCE_DOMAINS\"\n",
    ")\n",
    "arcpy.management.CalculateField(\n",
    "    \"EQIPContracts\",\n",
    "    \"County_States\",\n",
    "    expression='Concatenate($feature.County,\", \",$feature.State)',\n",
    "    expression_type=\"ARCADE\",\n",
    "    code_block=\"\"\"# Joins two or more fields into a single string value\n",
    "                # More calculator examples at esriurl.com/CalculatorExamples\n",
    "                def ConcatenateFields(*args):\n",
    "                sep = \"-\"\n",
    "                return sep.join([i for i in args if i])\"\"\",\n",
    "    field_type=\"TEXT\",\n",
    "    enforce_domains=\"NO_ENFORCE_DOMAINS\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:20:20 PM\",\"Succeeded at Monday, December 18, 2023 7:20:23 PM (Elapsed Time: 3.54 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'ContiguousUSCounties_lyr'>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Join EQIP contract data onto US counties\n",
    "arcpy.management.AddJoin(\n",
    "    in_layer_or_view=\"ContiguousUSCounties_lyr\",\n",
    "    in_field=\"County_States\",\n",
    "    join_table=\"EQIPContracts\",\n",
    "    join_field=\"County_States\",\n",
    "    join_type=\"KEEP_COMMON\",\n",
    "    index_join_fields=\"INDEX_JOIN_FIELDS\"\n",
    ")\n",
    "arcpy.management.CopyFeatures(\n",
    "    \"ContiguousUSCounties_lyr\",\n",
    "    \"H:/UEP 235/Final_Project/EQIPAnalysis/EQIPAnalysis.gdb/Boundary_Polygons/CountiesWContracts\"\n",
    ")\n",
    "arcpy.management.RemoveJoin(\n",
    "    \"ContiguousUSCounties_lyr\",\n",
    "    \"EQIPContracts\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:20:24 PM\",\"Adding FiscalYear to CountiesWContracts...\",\"Succeeded at Monday, December 18, 2023 7:20:49 PM (Elapsed Time: 24.59 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'H:/UEP 235/Final_Project/EQIPAnalysis/EQIPAnalysis.gdb/Boundary_Polygons/CountiesWContracts'>"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create date field for space time cube\n",
    "arcpy.management.ConvertTimeField(\n",
    "    in_table=\"H:/UEP 235/Final_Project/EQIPAnalysis/EQIPAnalysis.gdb/Boundary_Polygons/CountiesWContracts\",\n",
    "    input_time_field=\"EQIPContracts_FY\",\n",
    "    input_time_format=\"yyyy\",\n",
    "    output_time_field=\"FiscalYear\",\n",
    "    output_time_type=\"DATE\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:21:24 PM\",\"Succeeded at Monday, December 18, 2023 7:21:24 PM (Elapsed Time: 0.08 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'CountiesWContracts_lyr'>"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Filling in  suppressed values with zeros\n",
    "arcpy.management.MakeFeatureLayer(\n",
    "    \"CountiesWContracts\",\n",
    "    \"CountiesWContracts_lyr\"\n",
    ")\n",
    "arcpy.management.SelectLayerByAttribute(\n",
    "    in_layer_or_view=\"CountiesWContracts_lyr\",\n",
    "    selection_type=\"NEW_SELECTION\",\n",
    "    where_clause=\"EQIPContracts_Suppressed = 'TRUE'\",\n",
    ")\n",
    "arcpy.management.CalculateField(\n",
    "    \"CountiesWContracts_lyr\",\n",
    "    \"EQIPContracts_Contract_count\",\n",
    "    \"0\",\n",
    ")\n",
    "arcpy.management.SelectLayerByAttribute(\n",
    "    in_layer_or_view=\"CountiesWContracts_lyr\",\n",
    "    selection_type=\"CLEAR_SELECTION\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:21:52 PM\",\"Succeeded at Monday, December 18, 2023 7:21:52 PM (Elapsed Time: 0.06 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'CountiesWContracts_lyr'>"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arcpy.management.SelectLayerByAttribute(\n",
    "    in_layer_or_view=\"CountiesWContracts_lyr\",\n",
    "    selection_type=\"NEW_SELECTION\",\n",
    "    where_clause=\"EQIPContracts_Suppressed = 'TRUE'\",\n",
    ")\n",
    "arcpy.management.CalculateField(\n",
    "    \"CountiesWContracts_lyr\",\n",
    "    \"EQIPContracts_Acres_Contracted\",\n",
    "    \"0\",\n",
    ")\n",
    "arcpy.management.SelectLayerByAttribute(\n",
    "    in_layer_or_view=\"CountiesWContracts_lyr\",\n",
    "    selection_type=\"CLEAR_SELECTION\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:22:20 PM\",\"Succeeded at Monday, December 18, 2023 7:22:20 PM (Elapsed Time: 0.11 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'CountiesWContracts_lyr'>"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arcpy.management.SelectLayerByAttribute(\n",
    "    in_layer_or_view=\"CountiesWContracts_lyr\",\n",
    "    selection_type=\"NEW_SELECTION\",\n",
    "    where_clause=\"EQIPContracts_Suppressed = 'TRUE'\",\n",
    ")\n",
    "arcpy.management.CalculateField(\n",
    "    \"CountiesWContracts_lyr\",\n",
    "    \"EQIPContracts_Dollars_Obligated\",\n",
    "    \"0\",\n",
    ")\n",
    "arcpy.management.SelectLayerByAttribute(\n",
    "    in_layer_or_view=\"CountiesWContracts_lyr\",\n",
    "    selection_type=\"CLEAR_SELECTION\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:23:00 PM\",[{\"element\":\"table\",\"data\":[[{\"data\":\"Input feature time extent\",\"prop\":{\"rowspan\":2}},\"2014-01-01 00:00:00\"],[{\"data\":\"to 2023-01-01 00:00:00\",\"prop\":{\"text-align\":\"right\"}}],[\"\",\"\"],[\"Number of time steps\",\"10\"],[\"Time step interval\",\"1 year\"],[\"Time step alignment\",\"End\"],[\"\",\"\"],[\"First time step temporal bias\",\"0.00%\"],[{\"data\":\"First time step interval\",\"prop\":{\"rowspan\":4}},\"after\"],[{\"data\":\"2013-01-01 00:00:00\",\"prop\":{\"text-align\":\"right\"}}],[{\"data\":\"to on or before\",\"prop\":{\"text-align\":\"right\"}}],[{\"data\":\"2014-01-01 00:00:00\",\"prop\":{\"text-align\":\"right\"}}],[\"\",\"\"],[\"Last time step temporal bias\",\"0.00%\"],[{\"data\":\"Last time step interval\",\"prop\":{\"rowspan\":4}},\"after\"],[{\"data\":\"2022-01-01 00:00:00\",\"prop\":{\"text-align\":\"right\"}}],[{\"data\":\"to on or before\",\"prop\":{\"text-align\":\"right\"}}],[{\"data\":\"2023-01-01 00:00:00\",\"prop\":{\"text-align\":\"right\"}}],[\"\",\"\"],[\"Coordinate System\",\"NAD 1983 2011 Contiguous USA Albers\"],[\"Cube extent across space\",\"(coordinates in meters)\"],[\"Min X\",\"-2361582.2779\"],[\"Min Y\",\"310896.2075\"],[\"Max X\",\"2263786.1897\"],[\"Max Y\",\"3177425.2132\"]],\"elementProps\":{\"striped\":\"true\",\"noHeader\":true,\"title\":\"Space Time Cube Characteristics\",\"0\":{\"align\":\"left\",\"pad\":\"0px\",\"wrap\":true},\"1\":{\"align\":\"right\",\"pad\":\"0px\",\"wrap\":true}}}],[{\"element\":\"table\",\"data\":[[\"Locations\",\"3038\"],[\"% of locations with estimated observations\",\"22.58\"],[\"- Total number\",\"686\"],[\"Total observations\",\"30380\"],[\"% of all observations that were estimated\",\"4.98\"],[\"- Total number\",\"1514\"]],\"elementProps\":{\"striped\":\"true\",\"noHeader\":true,\"0\":{\"align\":\"left\",\"pad\":\"0px\",\"wrap\":true},\"1\":{\"align\":\"right\",\"pad\":\"0px\",\"wrap\":true}}}],[{\"element\":\"table\",\"data\":[[\"Trend direction\",\"Not Significant\"],[\"Trend statistic\",\"-1.2522\"],[\"Trend p-value\",\"0.2105\"]],\"elementProps\":{\"striped\":\"true\",\"noHeader\":true,\"title\":\"Overall Data Trend - EQIPCONTRACTS_CONTRACT_COUNT_NONE_ZEROS\",\"0\":{\"align\":\"left\",\"pad\":\"0px\",\"wrap\":true},\"1\":{\"align\":\"right\",\"pad\":\"0px\",\"wrap\":true}}}],[{\"element\":\"table\",\"data\":[[\"Trend direction\",\"Not Significant\"],[\"Trend statistic\",\"-0.7155\"],[\"Trend p-value\",\"0.4743\"]],\"elementProps\":{\"striped\":\"true\",\"noHeader\":true,\"title\":\"Overall Data Trend - EQIPCONTRACTS_ACRES_CONTRACTED_NONE_ZEROS\",\"0\":{\"align\":\"left\",\"pad\":\"0px\",\"wrap\":true},\"1\":{\"align\":\"right\",\"pad\":\"0px\",\"wrap\":true}}}],[{\"element\":\"table\",\"data\":[[\"Trend direction\",\"     Increasing\"],[\"Trend statistic\",\"2.3255\"],[\"Trend p-value\",\"0.0200\"]],\"elementProps\":{\"striped\":\"true\",\"noHeader\":true,\"title\":\"Overall Data Trend - EQIPCONTRACTS_DOLLARS_OBLIGATED_NONE_ZEROS\",\"0\":{\"align\":\"left\",\"pad\":\"0px\",\"wrap\":true},\"1\":{\"align\":\"right\",\"pad\":\"0px\",\"wrap\":true}}}],\"Succeeded at Monday, December 18, 2023 7:28:13 PM (Elapsed Time: 5 minutes 13 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'H:\\\\UEP 235\\\\Final_Project\\\\EQIPAnalysis\\\\EQIPContractsCube.nc'>"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create space time cube\n",
    "arcpy.stpm.CreateSpaceTimeCubeDefinedLocations(\n",
    "    in_features=\"CountiesWContracts\",\n",
    "    output_cube=\"H:/UEP 235/Final_Project/EQIPAnalysis/EQIPContractsCube.nc\",\n",
    "    location_id=\"ContiguousUSCounties_AllCounties2022_GEOID\",\n",
    "    temporal_aggregation=\"NO_TEMPORAL_AGGREGATION\",\n",
    "    time_field=\"FiscalYear\",\n",
    "    time_step_interval=\"1 Years\",\n",
    "    time_step_alignment=\"END_TIME\",\n",
    "    reference_time=None,\n",
    "    variables=\"EQIPContracts_Contract_count ZEROS;EQIPContracts_Acres_Contracted ZEROS;EQIPContracts_Dollars_Obligated ZEROS\",\n",
    "    summary_fields=None,\n",
    "    in_related_table=None,\n",
    "    related_location_id=None\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:29:06 PM\",\"WARNING 110020: The default Neighborhood Distance is 192524.746402 meters.\",\"\",[{\"element\":\"table\",\"data\":[[\"Time step interval\",\"1 year\"],[\"\",\"\"],[\"Shape Type\",\"Polygon\"],[\"\",\"\"],[\"First time step temporal bias\",\"0.00%\"],[{\"data\":\"First time step interval\",\"prop\":{\"rowspan\":4}},\"after\"],[{\"data\":\"2013-01-01 00:00:00\",\"prop\":{\"text-align\":\"right\"}}],[{\"data\":\"to on or before\",\"prop\":{\"text-align\":\"right\"}}],[{\"data\":\"2014-01-01 00:00:00\",\"prop\":{\"text-align\":\"right\"}}],[\"\",\"\"],[\"Last time step temporal bias\",\"0.00%\"],[{\"data\":\"Last time step interval\",\"prop\":{\"rowspan\":4}},\"after\"],[{\"data\":\"2022-01-01 00:00:00\",\"prop\":{\"text-align\":\"right\"}}],[{\"data\":\"to on or before\",\"prop\":{\"text-align\":\"right\"}}],[{\"data\":\"2023-01-01 00:00:00\",\"prop\":{\"text-align\":\"right\"}}],[\"\",\"\"],[\"Number of time steps\",\"10\"],[\"Number of locations analyzed\",\"3038\"],[\"Number of space time bins analyzed\",\"30380\"]],\"elementProps\":{\"striped\":\"true\",\"noHeader\":true,\"title\":\"Input Space Time Cube Details\",\"0\":{\"align\":\"left\",\"pad\":\"0px\",\"wrap\":true},\"1\":{\"align\":\"right\",\"pad\":\"0px\",\"wrap\":true}}}],[{\"element\":\"table\",\"data\":[[\"Neighborhood distance\",\"192524.746402 meters\"],[\"Neighborhood time step intervals\",\"1 (spanning 1 year)\"]],\"elementProps\":{\"striped\":\"true\",\"noHeader\":true,\"title\":\"Analysis Details\",\"0\":{\"align\":\"left\",\"pad\":\"0px\",\"wrap\":true},\"1\":{\"align\":\"right\",\"pad\":\"0px\",\"wrap\":true}}}],[{\"element\":\"table\",\"data\":[[\"\",\"HOT\",\"COLD\"],[\"New\",\"17\",\"6\"],[\"Consecutive\",\"278\",\"28\"],[\"Intensifying\",\"75\",\"15\"],[\"Persistent\",\"73\",\"114\"],[\"Diminishing\",\"2\",\"285\"],[\"Sporadic\",\"89\",\"317\"],[\"Oscillating\",\"22\",\"2\"],[\"Historical\",\"0\",\"0\"]],\"elementProps\":{\"striped\":\"true\",\"footnote\":[\"All locations with hot or cold spot trends: 1323 of 3038\"],\"title\":\"Summary of Results\",\"0\":{\"align\":\"left\",\"pad\":\"0px\",\"wrap\":true},\"1\":{\"align\":\"right\",\"pad\":\"0px\",\"wrap\":true},\"2\":{\"align\":\"right\",\"pad\":\"0px\",\"wrap\":true}}}],{\"element\":\"h5\",\"data\":\"Category Definitions\"},{\"element\":\"content\",\"data\":\"Last time step is hot:\"},{\"element\":\"unorderedlist\",\"data\":[\"New: the most recent time step interval is hot for the first time\",\"Consecutive: a single uninterrupted run of hot time step intervals, comprised of less than 90% of all intervals\",\"Intensifying: at least 90% of the time step intervals are hot, and becoming hotter over time\",\"Persistent: at least 90% of the time step intervals are hot, with no trend up or down\",\"Diminishing: at least 90% of the time step intervals are hot, and becoming less hot over time\",\"Sporadic: some of the time step intervals are hot\",\"Oscillating: some of the time step intervals are hot, some are cold\"]},{\"element\":\"content\",\"data\":\"Last time step is not hot:\"},{\"element\":\"unorderedlist\",\"data\":[\"Historical: at least 90% of the time step intervals are hot, but the most recent time step interval is not\"]},{\"element\":\"content\",\"data\":\"Last time step is cold:\"},{\"element\":\"unorderedlist\",\"data\":[\"New: the most recent time step interval is cold for the first time\",\"Consecutive: a single uninterrupted run of cold time step intervals, comprised of less than 90% of all\",\"Intensifying: at least 90% of the time step intervals are cold, and becoming colder over time\",\"Persistent: at least 90% of the time step intervals are cold, with no trend up or down\",\"Diminishing: at least 90% of the time step intervals are cold, and becoming less cold over time intervals\",\"Sporadic: some of the time step intervals are cold\",\"Oscillating: some of the time step intervals are cold, some are hot\"]},{\"element\":\"content\",\"data\":\"Last time step is not cold:\"},{\"element\":\"unorderedlist\",\"data\":[\"Historical: at least 90% of the time step intervals are cold, but the most recent time step interval is not\"]},\"\\n\\n\\n\\n\\n\\n\\n\\n\\n\",\"Succeeded at Monday, December 18, 2023 7:29:28 PM (Elapsed Time: 21.96 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'H:/UEP 235/Final_Project/EQIPAnalysis/EQIPAnalysis.gdb\\\\HotspotResults\\\\Hotspots_dollars'>"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Perform emerging hot spot analysis on each of the 3 variables of interest\n",
    "arcpy.stpm.EmergingHotSpotAnalysis(\n",
    "    \"H:/UEP 235/Final_Project/EQIPAnalysis/EQIPContractsCube.nc\",\n",
    "    \"EQIPCONTRACTS_CONTRACT_COUNT_NONE_ZEROS\",\n",
    "    \"HotspotResults/Hotspots_contracts\",\n",
    ")\n",
    "arcpy.stpm.EmergingHotSpotAnalysis(\n",
    "    \"H:/UEP 235/Final_Project/EQIPAnalysis/EQIPContractsCube.nc\",\n",
    "    \"EQIPCONTRACTS_ACRES_CONTRACTED_NONE_ZEROS\",\n",
    "    \"HotspotResults/Hotspots_acreage\",\n",
    ")\n",
    "arcpy.stpm.EmergingHotSpotAnalysis(\n",
    "    \"H:/UEP 235/Final_Project/EQIPAnalysis/EQIPContractsCube.nc\",\n",
    "    \"EQIPCONTRACTS_DOLLARS_OBLIGATED_NONE_ZEROS\",\n",
    "    \"HotspotResults/Hotspots_dollars\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:38:25 PM\",\"Succeeded at Monday, December 18, 2023 7:38:25 PM (Elapsed Time: 0.36 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'States_lyr'>"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Add state boundaries to map\n",
    "arcpy.management.MakeFeatureLayer(\n",
    "    \"Boundary_Polygons/States\",\n",
    "    \"States_lyr\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:39:44 PM\",\"Succeeded at Monday, December 18, 2023 7:40:13 PM (Elapsed Time: 29.24 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'H:\\\\UEP 235\\\\Final_Project\\\\EQIPAnalysis\\\\EQIPAnalysis.gdb\\\\MeanCenters\\\\Contracts'>"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Mean Center Calculations for each variable over time\n",
    "arcpy.stats.MeanCenter(\n",
    "    Input_Feature_Class=r\"H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\Boundary_Polygons\\CountiesWContracts\",\n",
    "    Output_Feature_Class=r\"H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\MeanCenters\\Acreage\",\n",
    "    Weight_Field=\"EQIPContracts_Acres_Contracted\",\n",
    "    Case_Field=\"EQIPContracts_FY\",\n",
    ")\n",
    "arcpy.stats.MeanCenter(\n",
    "    Input_Feature_Class=r\"H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\Boundary_Polygons\\CountiesWContracts\",\n",
    "    Output_Feature_Class=r\"H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\MeanCenters\\Dollars\",\n",
    "    Weight_Field=\"EQIPContracts_Dollars_Obligated\",\n",
    "    Case_Field=\"EQIPContracts_FY\",\n",
    ")\n",
    "arcpy.stats.MeanCenter(\n",
    "    Input_Feature_Class=r\"H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\Boundary_Polygons\\CountiesWContracts\",\n",
    "    Output_Feature_Class=r\"H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\MeanCenters\\Contracts\",\n",
    "    Weight_Field=\"EQIPContracts_Contract_count\",\n",
    "    Case_Field=\"EQIPContracts_FY\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:41:22 PM\",\"Reading Features...\",\"Cracking Features...\",\"Assembling Features...\",\"Succeeded at Monday, December 18, 2023 7:41:54 PM (Elapsed Time: 32.20 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'H:/UEP 235/Final_Project/EQIPAnalysis/EQIPAnalysis.gdb\\\\HotspotResults\\\\Hotspots_dollars_CLIP'>"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Cleaning up county boundaries using a coastline file and clip\n",
    "arcpy.analysis.Clip(\n",
    "    \"HotspotResults/Hotspots_contracts\",\n",
    "    \"Boundary_Polygons/CountieswCoast\",\n",
    "    \"HotspotResults/Hotspots_contracts_CLIP\"\n",
    ")\n",
    "arcpy.analysis.Clip(\n",
    "    \"HotspotResults/Hotspots_acreage\",\n",
    "    \"Boundary_Polygons/CountieswCoast\",\n",
    "    \"HotspotResults/Hotspots_acreage_CLIP\"\n",
    ")\n",
    "arcpy.analysis.Clip(\n",
    "    \"HotspotResults/Hotspots_dollars\",\n",
    "    \"Boundary_Polygons/CountieswCoast\",\n",
    "    \"HotspotResults/Hotspots_dollars_CLIP\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:43:12 PM\",\"Sorting Attributes...\",\"Dissolving...\",\"Succeeded at Monday, December 18, 2023 7:43:38 PM (Elapsed Time: 26.33 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'H:/UEP 235/Final_Project/EQIPAnalysis/EQIPAnalysis.gdb\\\\HotspotResults\\\\AllDissolved'>"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Combining cluster maps from contracts, dollars, and acreage into one map\n",
    "arcpy.analysis.Intersect(\n",
    "    in_features=r\"'H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\HotspotResults\\Hotspots_acreage_CLIP' #;'H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\HotspotResults\\Hotspots_contracts_CLIP' #;'H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\HotspotResults\\Hotspots_dollars_CLIP' #\",\n",
    "    out_feature_class=r\"H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\HotspotResults\\Hotspots_All\",\n",
    "    join_attributes=\"ALL\"\n",
    ")\n",
    "arcpy.management.CalculateField(\n",
    "    in_table=\"Hotspots_All\",\n",
    "    field=\"HC_1\",\n",
    "    expression=\"reclass(!CATEGORY!)\",\n",
    "    expression_type=\"PYTHON3\",\n",
    "    code_block=\"\"\"def reclass(CATEGORY):\n",
    "    if (CATEGORY > 2)and(CATEGORY < 7):\n",
    "        return 1\n",
    "    elif (CATEGORY < -2)and(CATEGORY > -7):\n",
    "        return -1\n",
    "    else:\n",
    "        return 0\"\"\",\n",
    "    field_type=\"DOUBLE\",\n",
    ")\n",
    "arcpy.management.CalculateField(\n",
    "    in_table=\"Hotspots_All\",\n",
    "    field=\"HC_2\",\n",
    "    expression=\"reclass(!CATEGORY_1!)\",\n",
    "    expression_type=\"PYTHON3\",\n",
    "    code_block=\"\"\"def reclass(CATEGORY_1):\n",
    "    if (CATEGORY_1 > 2)and(CATEGORY_1 < 7):\n",
    "        return 1\n",
    "    elif (CATEGORY_1 < -2)and(CATEGORY_1 > -7):\n",
    "        return -1\n",
    "    else:\n",
    "        return 0\"\"\",\n",
    "    field_type=\"DOUBLE\",\n",
    ")\n",
    "arcpy.management.CalculateField(\n",
    "    in_table=\"Hotspots_All\",\n",
    "    field=\"HC_3\",\n",
    "    expression=\"reclass(!CATEGORY_12!)\",\n",
    "    expression_type=\"PYTHON3\",\n",
    "    code_block=\"\"\"def reclass(CATEGORY_12):\n",
    "    if (CATEGORY_12 > 2)and(CATEGORY_12 < 7):\n",
    "        return 1\n",
    "    elif (CATEGORY_12 < -2)and(CATEGORY_12 > -7):\n",
    "        return -1\n",
    "    else:\n",
    "        return 0\"\"\",\n",
    "    field_type=\"DOUBLE\",\n",
    ")\n",
    "arcpy.management.CalculateField(\n",
    "    \"Hotspots_All\",\n",
    "    \"HC_Add\",\n",
    "    expression=\"reclass(!HC_1!+!HC_2!+!HC_3!)\",\n",
    "    expression_type=\"PYTHON3\",\n",
    "    code_block=\"\"\"def reclass(sum):\n",
    "    if (sum > 0):\n",
    "        return 1\n",
    "    elif (sum<0):\n",
    "        return -1\n",
    "    else:\n",
    "        return 0\"\"\",\n",
    "    field_type=\"DOUBLE\",\n",
    ")\n",
    "arcpy.management.Dissolve(\n",
    "    \"Hotspots_All\",\n",
    "    \"HotspotResults/AllDissolved\",\n",
    "    \"HC_Add\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:55:20 PM\",\"Succeeded at Monday, December 18, 2023 7:55:23 PM (Elapsed Time: 3.49 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'ContiguousUSCounties_lyr'>"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Creating farm resource regions map\n",
    "arcpy.management.CalculateField(\n",
    "    \"FarmResourceRegions\",\n",
    "    \"FIPS_Fixed\",\n",
    "    expression=\"reclass(!FIPS!)\",\n",
    "    expression_type=\"PYTHON3\",\n",
    "    code_block=\"\"\"def reclass(FIPS):\n",
    "    if (FIPS<10000):\n",
    "        return \"0\"+str(FIPS)\n",
    "    else:\n",
    "        return FIPS\"\"\",\n",
    "    field_type=\"TEXT\",\n",
    ")\n",
    "arcpy.management.AddJoin(\n",
    "    in_layer_or_view=\"ContiguousUSCounties_lyr\",\n",
    "    in_field=\"AllCounties2022_GEOID\",\n",
    "    join_table=\"FarmResourceRegions\",\n",
    "    join_field=\"FIPS_Fixed\",\n",
    "    join_type=\"KEEP_COMMON\",\n",
    "    index_join_fields=\"INDEX_JOIN_FIELDS\"\n",
    ")\n",
    "arcpy.analysis.Clip(\n",
    "    \"ContiguousUSCounties_lyr\",\n",
    "    \"Boundary_Polygons/CountieswCoast\",\n",
    "    \"FarmResourceRegions/Counties\"\n",
    ")\n",
    "arcpy.management.Dissolve(\n",
    "    \"FarmResourceRegions/Counties\",\n",
    "    \"FarmResourceRegions/Regions\",\n",
    "    \"FarmResourceRegions_Region\"\n",
    ")\n",
    "arcpy.management.RemoveJoin(\n",
    "    \"ContiguousUSCounties_lyr\",\n",
    "    \"FarmResourceRegions\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:57:39 PM\",\"Succeeded at Monday, December 18, 2023 7:57:42 PM (Elapsed Time: 2.77 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'ContiguousUSCounties_lyr'>"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Creating farm crop data map\n",
    "arcpy.management.CalculateField(\n",
    "    \"crop_Farms\",\n",
    "    \"ANSI_GEOID\",\n",
    "    expression=\"fix(!state_ANSI!,!County_ANSI!)\",\n",
    "    expression_type=\"PYTHON3\",\n",
    "    code_block=\"\"\"def fix(state,county):\n",
    "    if (state<10):\n",
    "        if (county<10):\n",
    "            return \"0\"+str(state)+\"00\"+str(county)\n",
    "        elif (county>10)and(county<100):\n",
    "            return \"0\"+str(state)+\"0\"+str(county)\n",
    "        else:\n",
    "            return \"0\"+str(state)+str(county)\n",
    "    else:\n",
    "        if (county<10):\n",
    "            return str(state)+\"00\"+str(county)\n",
    "        elif (county>10)and(county<100):\n",
    "            return str(state)+\"0\"+str(county)\n",
    "        else:\n",
    "            return str(state)+str(county)\"\"\",\n",
    "    field_type=\"TEXT\",\n",
    ")\n",
    "arcpy.management.CalculateField(\n",
    "    \"total_Farms\",\n",
    "    \"ANSI_GEOID\",\n",
    "    expression=\"fix(!state_ANSI!,!County_ANSI!)\",\n",
    "    expression_type=\"PYTHON3\",\n",
    "    code_block=\"\"\"def fix(state,county):\n",
    "    if (state<10):\n",
    "        if (county<10):\n",
    "            return \"0\"+str(state)+\"00\"+str(county)\n",
    "        elif (county>10)and(county<100):\n",
    "            return \"0\"+str(state)+\"0\"+str(county)\n",
    "        else:\n",
    "            return \"0\"+str(state)+str(county)\n",
    "    else:\n",
    "        if (county<10):\n",
    "            return str(state)+\"00\"+str(county)\n",
    "        elif (county>10)and(county<100):\n",
    "            return str(state)+\"0\"+str(county)\n",
    "        else:\n",
    "            return str(state)+str(county)\"\"\",\n",
    "    field_type=\"TEXT\",\n",
    ")\n",
    "arcpy.management.AddJoin(\n",
    "    in_layer_or_view=\"ContiguousUSCounties_lyr\",\n",
    "    in_field=\"AllCounties2022_GEOID\",\n",
    "    join_table=\"crop_Farms\",\n",
    "    join_field=\"ANSI_GEOID\",\n",
    "    join_type=\"KEEP_COMMON\",\n",
    "    index_join_fields=\"INDEX_JOIN_FIELDS\"\n",
    ")\n",
    "arcpy.analysis.Clip(\n",
    "    \"ContiguousUSCounties_lyr\",\n",
    "    \"Boundary_Polygons/CountieswCoast\",\n",
    "    \"Boundary_Polygons/CountiesWCropData\"\n",
    ")\n",
    "arcpy.management.RemoveJoin(\n",
    "    \"ContiguousUSCounties_lyr\",\n",
    "    \"crop_Farms\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:58:48 PM\",\"Adding Percent_Crop to CountiesWCropandFarmData...\",\"Succeeded at Monday, December 18, 2023 7:59:03 PM (Elapsed Time: 14.91 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'CountiesWCropandFarmData'>"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Creating farm crop data map (cont.)\n",
    "arcpy.management.AddJoin(\n",
    "    in_layer_or_view=\"CountiesWCropData\",\n",
    "    in_field=\"ContiguousUSCounties_AllCounties2022_GEOID\",\n",
    "    join_table=\"total_Farms\",\n",
    "    join_field=\"ANSI_GEOID\",\n",
    "    join_type=\"KEEP_COMMON\",\n",
    "    index_join_fields=\"INDEX_JOIN_FIELDS\"\n",
    ")\n",
    "arcpy.analysis.Clip(\n",
    "    \"CountiesWCropData\",\n",
    "    \"Boundary_Polygons/CountieswCoast\",\n",
    "    \"Boundary_Polygons/CountiesWCropandFarmData\"\n",
    ")\n",
    "arcpy.management.RemoveJoin(\n",
    "    \"CountiesWCropData\",\n",
    "    \"total_Farms\"\n",
    ")\n",
    "arcpy.management.CalculateField(\n",
    "    \"CountiesWCropandFarmData\",\n",
    "    \"Percent_Crop\",\n",
    "    expression=\"!CountiesWCropData_crop_Farms_Crop_Farms!/!total_Farms_Total_Farms!*100\",\n",
    "    field_type=\"DOUBLE\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n",
      "Traceback (most recent call last):\n",
      "  File \"<expression>\", line 1, in <module>\n",
      "  File \"<string>\", line 3, in fix\n",
      "TypeError: '<' not supported between instances of 'NoneType' and 'int'\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:59:09 PM\",\"WARNING 002858: Certain rows set to NULL due to error while evaluating python expression:   File \\\"\\u003cstring\\u003e\\\", line 3, in fix\",\"Succeeded at Monday, December 18, 2023 7:59:11 PM (Elapsed Time: 2.02 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'H:/UEP 235/Final_Project/EQIPAnalysis/EQIPAnalysis.gdb\\\\Size_Average'>"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Fixing ANSI codes in net cash farm income table and average farm size table\n",
    "arcpy.management.CalculateField(\n",
    "    \"NCFI_Average\",\n",
    "    \"ANSI_GEOID\",\n",
    "    expression=\"fix(!state_ANSI!,!county_ANSI!)\",\n",
    "    expression_type=\"PYTHON3\",\n",
    "    code_block=\"\"\"def fix(state,county):\n",
    "    if (state<10):\n",
    "        if (county<10):\n",
    "            return \"0\"+str(state)+\"00\"+str(county)\n",
    "        elif (county>10)and(county<100):\n",
    "            return \"0\"+str(state)+\"0\"+str(county)\n",
    "        else:\n",
    "            return \"0\"+str(state)+str(county)\n",
    "    else:\n",
    "        if (county<10):\n",
    "            return str(state)+\"00\"+str(county)\n",
    "        elif (county>10)and(county<100):\n",
    "            return str(state)+\"0\"+str(county)\n",
    "        else:\n",
    "            return str(state)+str(county)\"\"\",\n",
    "    field_type=\"TEXT\",\n",
    ")\n",
    "arcpy.management.CalculateField(\n",
    "    \"Size_Average\",\n",
    "    \"ANSI_GEOID\",\n",
    "    expression=\"fix(!state_ANSI!,!county_ANSI!)\",\n",
    "    expression_type=\"PYTHON3\",\n",
    "    code_block=\"\"\"def fix(state,county):\n",
    "    if (state<10):\n",
    "        if (county<10):\n",
    "            return \"0\"+str(state)+\"00\"+str(county)\n",
    "        elif (county>10)and(county<100):\n",
    "            return \"0\"+str(state)+\"0\"+str(county)\n",
    "        else:\n",
    "            return \"0\"+str(state)+str(county)\n",
    "    else:\n",
    "        if (county<10):\n",
    "            return str(state)+\"00\"+str(county)\n",
    "        elif (county>10)and(county<100):\n",
    "            return str(state)+\"0\"+str(county)\n",
    "        else:\n",
    "            return str(state)+str(county)\"\"\",\n",
    "    field_type=\"TEXT\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 7:59:35 PM\",\"Reading Features...\",\"Cracking Features...\",\"Assembling Features...\",\"Succeeded at Monday, December 18, 2023 8:00:10 PM (Elapsed Time: 34.78 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'H:/UEP 235/Final_Project/EQIPAnalysis/EQIPAnalysis.gdb\\\\Boundary_Polygons\\\\CountiesWCropandFarmData_Hotspots'>"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Adding farm data to hotspot data\n",
    "arcpy.management.AddJoin(\n",
    "    in_layer_or_view=\"Hotspots_All\",\n",
    "    in_field=\"LOC_LABEL\",\n",
    "    join_table=\"CountiesWCropandFarmData\",\n",
    "    join_field=\"CountiesWCropData_ContiguousUSCounties_AllCounties2022_GEOID\",\n",
    "    join_type=\"KEEP_COMMON\",\n",
    "    index_join_fields=\"INDEX_JOIN_FIELDS\"\n",
    ")\n",
    "arcpy.analysis.Clip(\n",
    "    \"Hotspots_All\",\n",
    "    \"Boundary_Polygons/CountieswCoast\",\n",
    "    \"Boundary_Polygons/CountiesWCropandFarmData_Hotspots\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 8:01:59 PM\",\"Succeeded at Monday, December 18, 2023 8:02:06 PM (Elapsed Time: 6.85 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'CountiesWCropandFarmData_Hotspots_NCFI'>"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Adding average farm size and average income to hotspot data\n",
    "arcpy.management.AddJoin(\n",
    "    in_layer_or_view=\"CountiesWCropandFarmData_Hotspots\",\n",
    "    in_field=\"Hotspots_All_LOC_LABEL\",\n",
    "    join_table=\"NCFI_Average\",\n",
    "    join_field=\"ANSI_GEOID\",\n",
    "    join_type=\"KEEP_COMMON\",\n",
    "    index_join_fields=\"INDEX_JOIN_FIELDS\"\n",
    ")\n",
    "arcpy.analysis.Clip(\n",
    "    \"CountiesWCropandFarmData_Hotspots\",\n",
    "    \"Boundary_Polygons/CountieswCoast\",\n",
    "    \"Boundary_Polygons/CountiesWCropandFarmData_Hotspots_NCFI\"\n",
    ")\n",
    "arcpy.management.RemoveJoin(\n",
    "    \"CountiesWCropandFarmData_Hotspots\",\n",
    "    \"NCFI_Average\"\n",
    ")\n",
    "arcpy.management.AddJoin(\n",
    "    in_layer_or_view=\"CountiesWCropandFarmData_Hotspots_NCFI\",\n",
    "    in_field=\"CountiesWCropandFarmData_Hotspots_Hotspots_All_LOC_LABEL\",\n",
    "    join_table=\"Size_Average\",\n",
    "    join_field=\"ANSI_GEOID\",\n",
    "    join_type=\"KEEP_COMMON\",\n",
    "    index_join_fields=\"INDEX_JOIN_FIELDS\"\n",
    ")\n",
    "arcpy.analysis.Clip(\n",
    "    \"CountiesWCropandFarmData_Hotspots_NCFI\",\n",
    "    \"Boundary_Polygons/CountieswCoast\",\n",
    "    \"Boundary_Polygons/CountiesWCropandFarmData_Hotspots_NCFIandSize\"\n",
    ")\n",
    "arcpy.management.RemoveJoin(\n",
    "    \"CountiesWCropandFarmData_Hotspots_NCFI\",\n",
    "    \"Size_Average\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 8:10:52 PM\",\"WARNING 003108: Input fields contained null value(s).  Null value(s) skipped.\",\"Succeeded at Monday, December 18, 2023 8:10:57 PM (Elapsed Time: 4.49 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'H:\\\\UEP 235\\\\Final_Project\\\\EQIPAnalysis\\\\EQIPAnalysis.gdb\\\\Overall_Hotspot_Statistics'>"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Summary statistics for farm data by overall cluster type\n",
    "arcpy.analysis.Statistics(\n",
    "    in_table=\"CountiesWCropandFarmData_Hotspots_NCFIandSize\",\n",
    "    out_table=r\"H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\Overall_Hotspot_Statistics\",\n",
    "    statistics_fields=\"Size_Average_Size_Avg MEAN;CountiesWCropandFarmData_Hotspots_NCFI_CountiesWCropandFarmDa_91 MEAN;CountiesWCropandFarmData_Hotspots_NCFI_NCFI_Average_NCFI_Avg MEAN;CountiesWCropandFarmData_Hotspots_NCFI_CountiesWCropandFarmDa_94 MEAN\",\n",
    "    case_field=\"CountiesWCropandFarmData_Hotspots_NCFI_CountiesWCropandFarmDa_50\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Extracting overall hot and cold spot results by farm resource region\n",
    "for r in range(1,10):\n",
    "    arcpy.analysis.Select(\n",
    "        \"FarmResourceRegions/Regions\",\n",
    "        \"FarmResourceRegions/Region\"+str(r),\n",
    "        where_clause=\"FarmResourceRegions_Region = \"+str(r)\n",
    "    )\n",
    "    arcpy.analysis.Intersect(\n",
    "        [\"HotspotResults/Hotspots_All\",\"FarmResourceRegions/Region\"+str(r)],\n",
    "        \"HotspotsbyRegion/HotspotsRegion\"+str(r)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class='gpresult'><h2>Messages</h2><div id='messages' data-messages='[\"Start Time: Monday, December 18, 2023 8:16:23 PM\",\"Succeeded at Monday, December 18, 2023 8:16:26 PM (Elapsed Time: 2.72 seconds)\"]' data-show='true'><div id = 'default' /></div></div>"
      ],
      "text/plain": [
       "<Result 'H:\\\\UEP 235\\\\Final_Project\\\\EQIPAnalysis\\\\Counties_Stats_byReg.csv'>"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Calculating perecent of counties in each region that are hot or cold\n",
    "arcpy.analysis.Intersect(\n",
    "    [\"HotspotResults/Hotspots_All\",\"FarmResourceRegions/Regions\"],\n",
    "    \"HotspotsbyRegion/HotspotsRegion\"\n",
    ")\n",
    "arcpy.analysis.Statistics(\n",
    "    in_table=r\"H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\HotspotsbyRegion\\HotspotsRegion\",\n",
    "    out_table=r\"H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\Counties_Stats_by_Region\",\n",
    "    statistics_fields=\"LOCATION COUNT\",\n",
    "    case_field=\"HC_Add;FarmResourceRegions_Region\",\n",
    ")\n",
    "arcpy.conversion.ExportTable(\n",
    "    in_table=r\"H:\\UEP 235\\Final_Project\\EQIPAnalysis\\EQIPAnalysis.gdb\\Counties_Stats_by_Region\",\n",
    "    out_table=r\"H:\\UEP 235\\Final_Project\\EQIPAnalysis\\Counties_Stats_byReg.csv\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ArcGISPro",
   "language": "Python",
   "name": "python3"
  },
  "language_info": {
   "file_extension": ".py",
   "name": "python",
   "version": "3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}