USDA Crop Maps - Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Making USDA Crop Data Maps with <i>Python</i>!\n",
    "(inspired by Zachary Stansell's tweet: https://twitter.com/ZacharyStansell/status/1354612264679198722)\n",
    "\n",
    "Note that this Notebook is designed to be executed as a ArcGIS Online Notebook and will require modification if used locally.\n",
    "\n",
    "#### References:\n",
    "* [USDA NASS Census of Agriculture Intro](https://www.nass.usda.gov/Publications/AgCensus/2017/Full_Report/Volume_1,_Chapter_1_US/usintro.pdf)\n",
    "* [USDA NASS QuickStats API](https://quickstats.nass.usda.gov/api)\n",
    "* [ArcGIS API for Python reference](https://developers.arcgis.com/python/api-reference/arcgis.mapping.toc.html?highlight=esriclassifynaturalbreaks#renderers)\n",
    "* [Notebooks in ArcGIS Online](https://doc.arcgis.com/en/arcgis-online/get-started/get-started-with-notebooks.htm)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Setup and import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import copy\n",
    "import requests\n",
    "# import arcpy # use the Advanced runtime and import arcpy if you want the feature layer to publish using a gdb instead of shp\n",
    "import pandas as pd\n",
    "from arcgis.gis import GIS\n",
    "gis = GIS(\"home\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_key(key_name):\n",
    "    df = pd.read_csv('accts.csv')\n",
    "    key = df['KEY'][df['KEYNAME'] == key_name][0]\n",
    "    return key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_crops():\n",
    "    df = pd.read_csv('CropsList.csv')\n",
    "    croplist = df['Commodity'].tolist()\n",
    "    return croplist"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Set parameters for the data query and maps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set API key\n",
    "api_key = get_key(\"NASS API KEY\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  0. ALMONDS\n",
      "  1. APPLES\n",
      "  2. APRICOTS\n",
      "  3. AQUATIC PLANTS\n",
      "  4. ARONIA BERRIES\n",
      "  5. ARTICHOKES\n",
      "  6. ASPARAGUS\n",
      "  7. AVOCADOS\n",
      "  8. BANANAS\n",
      "  9. BARLEY\n",
      " 10. BEANS\n",
      " 11. BEDDING PLANT TOTALS\n",
      " 12. BEETS\n",
      " 13. BERRIES, OTHER\n",
      " 14. BERRY TOTALS\n",
      " 15. BLACKBERRIES\n",
      " 16. BLUEBERRIES\n",
      " 17. BOYSENBERRIES\n",
      " 18. BROCCOLI\n",
      " 19. BRUSSELS SPROUTS\n",
      " 20. BUCKWHEAT\n",
      " 21. BULBS & CORMS & RHIZOMES & TUBERS\n",
      " 22. CABBAGE\n",
      " 23. CAMELINA\n",
      " 24. CANOLA\n",
      " 25. CARROTS\n",
      " 26. CAULIFLOWER\n",
      " 27. CELERY\n",
      " 28. CHERIMOYAS\n",
      " 29. CHERRIES\n",
      " 30. CHESTNUTS\n",
      " 31. CHICKPEAS\n",
      " 32. CHICORY\n",
      " 33. CITRUS TOTALS\n",
      " 34. CITRUS, OTHER\n",
      " 35. COFFEE\n",
      " 36. CORN\n",
      " 37. COTTON\n",
      " 38. CRANBERRIES\n",
      " 39. CROP TOTALS\n",
      " 40. CROPS, OTHER\n",
      " 41. CUCUMBERS\n",
      " 42. CURRANTS\n",
      " 43. CUT CHRISTMAS TREES\n",
      " 44. CUT CHRISTMAS TREES & SHORT TERM WOODY CROPS\n",
      " 45. CUT FLOWERS & CUT CULTIVATED GREENS\n",
      " 46. DAIKON\n",
      " 47. DATES\n",
      " 48. DILL\n",
      " 49. EGGPLANT\n",
      " 50. ELDERBERRIES\n",
      " 51. EMMER & SPELT\n",
      " 52. ESCAROLE & ENDIVE\n",
      " 53. FIELD CROPS, OTHER\n",
      " 54. FIGS\n",
      " 55. FLAXSEED\n",
      " 56. FLORICULTURE TOTALS\n",
      " 57. FLORICULTURE, OTHER\n",
      " 58. FLOWER SEEDS\n",
      " 59. FLOWERING PLANTS, POTTED\n",
      " 60. FOLIAGE PLANTS\n",
      " 61. FRUIT & TREE NUT TOTALS\n",
      " 62. FRUIT TOTALS\n",
      " 63. GARLIC\n",
      " 64. GINGER ROOT\n",
      " 65. GINSENG\n",
      " 66. GRAIN\n",
      " 67. GRAPEFRUIT\n",
      " 68. GRAPES\n",
      " 69. GRASSES\n",
      " 70. GRASSES & LEGUMES TOTALS\n",
      " 71. GRASSES & LEGUMES, OTHER\n",
      " 72. GREENS\n",
      " 73. GUAR\n",
      " 74. GUAVAS\n",
      " 75. HAY\n",
      " 76. HAY & HAYLAGE\n",
      " 77. HAYLAGE\n",
      " 78. HAZELNUTS\n",
      " 79. HERBS\n",
      " 80. HOPS\n",
      " 81. HORSERADISH\n",
      " 82. HORTICULTURE TOTALS\n",
      " 83. JOJOBA\n",
      " 84. KIWIFRUIT\n",
      " 85. KUMQUATS\n",
      " 86. LEGUMES\n",
      " 87. LEMONS\n",
      " 88. LENTILS\n",
      " 89. LETTUCE\n",
      " 90. LIMES\n",
      " 91. LOGANBERRIES\n",
      " 92. MACADAMIAS\n",
      " 93. MANGOES\n",
      " 94. MAPLE SYRUP\n",
      " 95. MELONS\n",
      " 96. MILLET\n",
      " 97. MINT\n",
      " 98. MISCANTHUS\n",
      " 99. MUSHROOM SPAWN\n",
      "100. MUSHROOMS\n",
      "101. MUSTARD\n",
      "102. NECTARINES\n",
      "103. NON-CITRUS TOTALS\n",
      "104. NON-CITRUS, OTHER\n",
      "105. NURSERY TOTALS\n",
      "106. OATS\n",
      "107. OKRA\n",
      "108. OLIVES\n",
      "109. ONIONS\n",
      "110. ORANGES\n",
      "111. ORCHARDS\n",
      "112. PACKING FACILITY\n",
      "113. PAPAYAS\n",
      "114. PARSLEY\n",
      "115. PASSION FRUIT\n",
      "116. PEACHES\n",
      "117. PEANUTS\n",
      "118. PEARS\n",
      "119. PEAS\n",
      "120. PECANS\n",
      "121. PEPPERS\n",
      "122. PERSIMMONS\n",
      "123. PINEAPPLES\n",
      "124. PISTACHIOS\n",
      "125. PLUM-APRICOT HYBRIDS\n",
      "126. PLUMS\n",
      "127. PLUMS & PRUNES\n",
      "128. POMEGRANATES\n",
      "129. POPCORN\n",
      "130. POTATOES\n",
      "131. PROPAGATIVE MATERIAL\n",
      "132. PRUNES\n",
      "133. PUMPKINS\n",
      "134. RADISHES\n",
      "135. RAPESEED\n",
      "136. RASPBERRIES\n",
      "137. RHUBARB\n",
      "138. RICE\n",
      "139. RYE\n",
      "140. SAFFLOWER\n",
      "141. SESAME\n",
      "142. SHORT TERM WOODY CROPS\n",
      "143. SMALL GRAINS\n",
      "144. SOD\n",
      "145. SORGHUM\n",
      "146. SOYBEANS\n",
      "147. SPINACH\n",
      "148. SQUASH\n",
      "149. STRAWBERRIES\n",
      "150. SUGARBEETS\n",
      "151. SUGARCANE\n",
      "152. SUNFLOWER\n",
      "153. SWEET CORN\n",
      "154. SWEET POTATOES\n",
      "155. SWITCHGRASS\n",
      "156. TANGELOS\n",
      "157. TANGERINES\n",
      "158. TARO\n",
      "159. TOBACCO\n",
      "160. TOMATOES\n",
      "161. TRANSPLANTS\n",
      "162. TREE NUT TOTALS\n",
      "163. TREE NUTS, OTHER\n",
      "164. TRITICALE\n",
      "165. TURNIPS\n",
      "166. VEGETABLE SEEDS\n",
      "167. VEGETABLE TOTALS\n",
      "168. VEGETABLES, OTHER\n",
      "169. WALNUTS\n",
      "170. WATERCRESS\n",
      "171. WHEAT\n",
      "172. WILD RICE\n",
      "173. ALCOHOL COPRODUCTS\n",
      "174. CAKE & MEAL\n",
      "175. FEED GRAINS\n",
      "176. FIELD CROP TOTALS\n",
      "177. FLOUR\n",
      "178. FOOD GRAINS\n",
      "179. MILLFEED\n",
      "180. NON-CITRUS FRUIT & TREE NUTS TOTALS\n",
      "181. OIL\n",
      "182. OIL-BEARING CROPS\n",
      "183. FRUIT, OTHER\n",
      "184. PEAS & CARROTS\n",
      "185. VEGETABLES, MIXED\n",
      "186. CRUDE PINE GUM\n",
      "187. GOOSEBERRIES\n",
      "188. TEMPLES\n",
      "189. COLD STORAGE CAPACITY\n",
      "190. GRAIN STORAGE CAPACITY\n",
      "191. FIELDWORK\n",
      "192. SOIL\n",
      "193. PASTURELAND\n"
     ]
    }
   ],
   "source": [
    "# Get crop values\n",
    "crops = get_crops()\n",
    "# View crops\n",
    "for i,crop in enumerate(crops):\n",
    "    print(f'{i:3}. {crop}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'PEANUTS'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Set crop for query\n",
    "cropnum = 117 # 36 is CORN for example\n",
    "crops[cropnum] # this is the crop you'll be mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query params\n",
    "params = {\n",
    "    'commodity_desc': crops[cropnum],\n",
    "    'statisticcat_desc': 'AREA HARVESTED', # for field crops\n",
    "    #'statisticcat_desc': 'AREA BEARING', # for tree crops\n",
    "    'unit_desc':'ACRES',\n",
    "    'domain_desc':'TOTAL',\n",
    "    'domaincat_desc': 'NOT SPECIFIED',\n",
    "    'agg_level_desc': 'COUNTY',\n",
    "    'freq_desc': 'ANNUAL',\n",
    "    'year': {'start':2012,'end':2021}\n",
    "#     'state_alpha': 'MO'\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Define query functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def nass_query(baseurl,params):\n",
    "    for p in params:\n",
    "        if p != 'year':\n",
    "            baseurl += f'&{p}={params[p]}'\n",
    "        else:\n",
    "            years = range(params[p]['start'],params[p]['end']+1)\n",
    "            baseurl += ''.join([f'&year={y}' for y in years])\n",
    "    queryurl = baseurl.replace(' ','%20')\n",
    "    resp = requests.get(queryurl)\n",
    "    try:\n",
    "        resp.raise_for_status()\n",
    "    except requests.exceptions.HTTPError as e:\n",
    "        return \"Error: \" + str(e)\n",
    "    return resp\n",
    "\n",
    "def nass_params(api_key,sector_desc):\n",
    "    paramsurl = f'http://quickstats.nass.usda.gov/api/get_param_values/?key={api_key}&param={sector_desc}'\n",
    "    resp = nass_query(paramsurl,params={})\n",
    "    params = resp.json()[sector_desc]\n",
    "    return params\n",
    "\n",
    "def nass_counts(api_key,params):\n",
    "    counturl = f'http://quickstats.nass.usda.gov/api/get_counts/?key={api_key}'\n",
    "    resp = nass_query(counturl,params)\n",
    "    count = resp.json()['count']\n",
    "    return count\n",
    "\n",
    "def nass_data(api_key,params):\n",
    "    dataurl = f'http://quickstats.nass.usda.gov/api/api_GET/?key={api_key}'\n",
    "    resp = nass_query(dataurl,params)\n",
    "    d = resp.json()['data']\n",
    "    data = pd.DataFrame(d)\n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Query the USDA NASS API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2153"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# First check how many records the query will return\n",
    "record_count = nass_counts(api_key,params)\n",
    "record_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PEANUTS, AREA HARVESTED (ACRES), 2012-2021: 2153 results downloaded\n"
     ]
    }
   ],
   "source": [
    "# Then submit your query\n",
    "if record_count < 49999:\n",
    "    d = nass_data(api_key,params)\n",
    "else:\n",
    "    # query by each year separately and then concat\n",
    "    years = range(params['year']['start'],params['year']['end']+1)\n",
    "    results = []\n",
    "    for year in years:\n",
    "        new_params = copy.deepcopy(params)\n",
    "        new_params['year']['start'] = year\n",
    "        new_params['year']['end'] = year\n",
    "        ct = nass_counts(api_key,new_params)\n",
    "        if ct > 0:\n",
    "            res = nass_data(api_key,new_params)\n",
    "        results.append(res)\n",
    "    d = pd.concat(results,ignore_index=True)\n",
    "print(f\"{params['commodity_desc']}, {params['statisticcat_desc']} ({params['unit_desc']}), {params['year']['start']}-{params['year']['end']}: {d.shape[0]} results downloaded\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove commas in Value\n",
    "d['Value'] = d['Value'].str.replace(',','')\n",
    "\n",
    "# filter out withheld data (see first link in References)\n",
    "# may want to modify how this is handled since withheld data != 0\n",
    "d = d[~d['Value'].isin(['                 (D)','                 (Z)'])].copy()\n",
    "\n",
    "# change Value to numeric\n",
    "d.Value = pd.to_numeric(d.Value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>short_desc</th>\n",
       "      <th>CV (%)</th>\n",
       "      <th>source_desc</th>\n",
       "      <th>state_alpha</th>\n",
       "      <th>region_desc</th>\n",
       "      <th>class_desc</th>\n",
       "      <th>year</th>\n",
       "      <th>prodn_practice_desc</th>\n",
       "      <th>state_name</th>\n",
       "      <th>county_ansi</th>\n",
       "      <th>...</th>\n",
       "      <th>county_name</th>\n",
       "      <th>reference_period_desc</th>\n",
       "      <th>freq_desc</th>\n",
       "      <th>begin_code</th>\n",
       "      <th>util_practice_desc</th>\n",
       "      <th>load_time</th>\n",
       "      <th>end_code</th>\n",
       "      <th>zip_5</th>\n",
       "      <th>unit_desc</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PEANUTS - ACRES HARVESTED</td>\n",
       "      <td>20.7</td>\n",
       "      <td>CENSUS</td>\n",
       "      <td>AL</td>\n",
       "      <td></td>\n",
       "      <td>ALL CLASSES</td>\n",
       "      <td>2017</td>\n",
       "      <td>ALL PRODUCTION PRACTICES</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>059</td>\n",
       "      <td>...</td>\n",
       "      <td>FRANKLIN</td>\n",
       "      <td>YEAR</td>\n",
       "      <td>ANNUAL</td>\n",
       "      <td>00</td>\n",
       "      <td>ALL UTILIZATION PRACTICES</td>\n",
       "      <td>2018-02-01 00:00:00</td>\n",
       "      <td>00</td>\n",
       "      <td></td>\n",
       "      <td>ACRES</td>\n",
       "      <td>700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PEANUTS - ACRES HARVESTED</td>\n",
       "      <td>20.7</td>\n",
       "      <td>CENSUS</td>\n",
       "      <td>AL</td>\n",
       "      <td></td>\n",
       "      <td>ALL CLASSES</td>\n",
       "      <td>2017</td>\n",
       "      <td>ALL PRODUCTION PRACTICES</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>079</td>\n",
       "      <td>...</td>\n",
       "      <td>LAWRENCE</td>\n",
       "      <td>YEAR</td>\n",
       "      <td>ANNUAL</td>\n",
       "      <td>00</td>\n",
       "      <td>ALL UTILIZATION PRACTICES</td>\n",
       "      <td>2018-02-01 00:00:00</td>\n",
       "      <td>00</td>\n",
       "      <td></td>\n",
       "      <td>ACRES</td>\n",
       "      <td>4390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PEANUTS - ACRES HARVESTED</td>\n",
       "      <td>20.7</td>\n",
       "      <td>CENSUS</td>\n",
       "      <td>AL</td>\n",
       "      <td></td>\n",
       "      <td>ALL CLASSES</td>\n",
       "      <td>2017</td>\n",
       "      <td>ALL PRODUCTION PRACTICES</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>103</td>\n",
       "      <td>...</td>\n",
       "      <td>MORGAN</td>\n",
       "      <td>YEAR</td>\n",
       "      <td>ANNUAL</td>\n",
       "      <td>00</td>\n",
       "      <td>ALL UTILIZATION PRACTICES</td>\n",
       "      <td>2018-02-01 00:00:00</td>\n",
       "      <td>00</td>\n",
       "      <td></td>\n",
       "      <td>ACRES</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PEANUTS - ACRES HARVESTED</td>\n",
       "      <td>20.7</td>\n",
       "      <td>CENSUS</td>\n",
       "      <td>AL</td>\n",
       "      <td></td>\n",
       "      <td>ALL CLASSES</td>\n",
       "      <td>2017</td>\n",
       "      <td>ALL PRODUCTION PRACTICES</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>009</td>\n",
       "      <td>...</td>\n",
       "      <td>BLOUNT</td>\n",
       "      <td>YEAR</td>\n",
       "      <td>ANNUAL</td>\n",
       "      <td>00</td>\n",
       "      <td>ALL UTILIZATION PRACTICES</td>\n",
       "      <td>2018-02-01 00:00:00</td>\n",
       "      <td>00</td>\n",
       "      <td></td>\n",
       "      <td>ACRES</td>\n",
       "      <td>357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>PEANUTS - ACRES HARVESTED</td>\n",
       "      <td>39.8</td>\n",
       "      <td>CENSUS</td>\n",
       "      <td>AL</td>\n",
       "      <td></td>\n",
       "      <td>ALL CLASSES</td>\n",
       "      <td>2012</td>\n",
       "      <td>ALL PRODUCTION PRACTICES</td>\n",
       "      <td>ALABAMA</td>\n",
       "      <td>009</td>\n",
       "      <td>...</td>\n",
       "      <td>BLOUNT</td>\n",
       "      <td>YEAR</td>\n",
       "      <td>ANNUAL</td>\n",
       "      <td>00</td>\n",
       "      <td>ALL UTILIZATION PRACTICES</td>\n",
       "      <td>2012-12-31 00:00:00</td>\n",
       "      <td>00</td>\n",
       "      <td></td>\n",
       "      <td>ACRES</td>\n",
       "      <td>459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2148</th>\n",
       "      <td>PEANUTS - ACRES HARVESTED</td>\n",
       "      <td></td>\n",
       "      <td>SURVEY</td>\n",
       "      <td>VA</td>\n",
       "      <td></td>\n",
       "      <td>ALL CLASSES</td>\n",
       "      <td>2016</td>\n",
       "      <td>ALL PRODUCTION PRACTICES</td>\n",
       "      <td>VIRGINIA</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>OTHER (COMBINED) COUNTIES</td>\n",
       "      <td>YEAR</td>\n",
       "      <td>ANNUAL</td>\n",
       "      <td>00</td>\n",
       "      <td>ALL UTILIZATION PRACTICES</td>\n",
       "      <td>2018-05-10 15:00:31</td>\n",
       "      <td>00</td>\n",
       "      <td></td>\n",
       "      <td>ACRES</td>\n",
       "      <td>7100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2149</th>\n",
       "      <td>PEANUTS - ACRES HARVESTED</td>\n",
       "      <td></td>\n",
       "      <td>SURVEY</td>\n",
       "      <td>VA</td>\n",
       "      <td></td>\n",
       "      <td>ALL CLASSES</td>\n",
       "      <td>2015</td>\n",
       "      <td>ALL PRODUCTION PRACTICES</td>\n",
       "      <td>VIRGINIA</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>OTHER (COMBINED) COUNTIES</td>\n",
       "      <td>YEAR</td>\n",
       "      <td>ANNUAL</td>\n",
       "      <td>00</td>\n",
       "      <td>ALL UTILIZATION PRACTICES</td>\n",
       "      <td>2017-05-11 15:00:20</td>\n",
       "      <td>00</td>\n",
       "      <td></td>\n",
       "      <td>ACRES</td>\n",
       "      <td>1900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2150</th>\n",
       "      <td>PEANUTS - ACRES HARVESTED</td>\n",
       "      <td></td>\n",
       "      <td>SURVEY</td>\n",
       "      <td>VA</td>\n",
       "      <td></td>\n",
       "      <td>ALL CLASSES</td>\n",
       "      <td>2014</td>\n",
       "      <td>ALL PRODUCTION PRACTICES</td>\n",
       "      <td>VIRGINIA</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>OTHER (COMBINED) COUNTIES</td>\n",
       "      <td>YEAR</td>\n",
       "      <td>ANNUAL</td>\n",
       "      <td>00</td>\n",
       "      <td>ALL UTILIZATION PRACTICES</td>\n",
       "      <td>2016-05-05 15:04:45</td>\n",
       "      <td>00</td>\n",
       "      <td></td>\n",
       "      <td>ACRES</td>\n",
       "      <td>1900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2151</th>\n",
       "      <td>PEANUTS - ACRES HARVESTED</td>\n",
       "      <td></td>\n",
       "      <td>SURVEY</td>\n",
       "      <td>VA</td>\n",
       "      <td></td>\n",
       "      <td>ALL CLASSES</td>\n",
       "      <td>2013</td>\n",
       "      <td>ALL PRODUCTION PRACTICES</td>\n",
       "      <td>VIRGINIA</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>OTHER (COMBINED) COUNTIES</td>\n",
       "      <td>YEAR</td>\n",
       "      <td>ANNUAL</td>\n",
       "      <td>00</td>\n",
       "      <td>ALL UTILIZATION PRACTICES</td>\n",
       "      <td>2015-05-07 15:00:14</td>\n",
       "      <td>00</td>\n",
       "      <td></td>\n",
       "      <td>ACRES</td>\n",
       "      <td>2300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2152</th>\n",
       "      <td>PEANUTS - ACRES HARVESTED</td>\n",
       "      <td></td>\n",
       "      <td>SURVEY</td>\n",
       "      <td>VA</td>\n",
       "      <td></td>\n",
       "      <td>ALL CLASSES</td>\n",
       "      <td>2012</td>\n",
       "      <td>ALL PRODUCTION PRACTICES</td>\n",
       "      <td>VIRGINIA</td>\n",
       "      <td></td>\n",
       "      <td>...</td>\n",
       "      <td>OTHER (COMBINED) COUNTIES</td>\n",
       "      <td>YEAR</td>\n",
       "      <td>ANNUAL</td>\n",
       "      <td>00</td>\n",
       "      <td>ALL UTILIZATION PRACTICES</td>\n",
       "      <td>2015-06-04 15:00:29</td>\n",
       "      <td>00</td>\n",
       "      <td></td>\n",
       "      <td>ACRES</td>\n",
       "      <td>2500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1719 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     short_desc CV (%) source_desc state_alpha region_desc  \\\n",
       "0     PEANUTS - ACRES HARVESTED   20.7      CENSUS          AL               \n",
       "1     PEANUTS - ACRES HARVESTED   20.7      CENSUS          AL               \n",
       "3     PEANUTS - ACRES HARVESTED   20.7      CENSUS          AL               \n",
       "4     PEANUTS - ACRES HARVESTED   20.7      CENSUS          AL               \n",
       "5     PEANUTS - ACRES HARVESTED   39.8      CENSUS          AL               \n",
       "...                         ...    ...         ...         ...         ...   \n",
       "2148  PEANUTS - ACRES HARVESTED             SURVEY          VA               \n",
       "2149  PEANUTS - ACRES HARVESTED             SURVEY          VA               \n",
       "2150  PEANUTS - ACRES HARVESTED             SURVEY          VA               \n",
       "2151  PEANUTS - ACRES HARVESTED             SURVEY          VA               \n",
       "2152  PEANUTS - ACRES HARVESTED             SURVEY          VA               \n",
       "\n",
       "       class_desc  year       prodn_practice_desc state_name county_ansi  ...  \\\n",
       "0     ALL CLASSES  2017  ALL PRODUCTION PRACTICES    ALABAMA         059  ...   \n",
       "1     ALL CLASSES  2017  ALL PRODUCTION PRACTICES    ALABAMA         079  ...   \n",
       "3     ALL CLASSES  2017  ALL PRODUCTION PRACTICES    ALABAMA         103  ...   \n",
       "4     ALL CLASSES  2017  ALL PRODUCTION PRACTICES    ALABAMA         009  ...   \n",
       "5     ALL CLASSES  2012  ALL PRODUCTION PRACTICES    ALABAMA         009  ...   \n",
       "...           ...   ...                       ...        ...         ...  ...   \n",
       "2148  ALL CLASSES  2016  ALL PRODUCTION PRACTICES   VIRGINIA              ...   \n",
       "2149  ALL CLASSES  2015  ALL PRODUCTION PRACTICES   VIRGINIA              ...   \n",
       "2150  ALL CLASSES  2014  ALL PRODUCTION PRACTICES   VIRGINIA              ...   \n",
       "2151  ALL CLASSES  2013  ALL PRODUCTION PRACTICES   VIRGINIA              ...   \n",
       "2152  ALL CLASSES  2012  ALL PRODUCTION PRACTICES   VIRGINIA              ...   \n",
       "\n",
       "                    county_name reference_period_desc freq_desc begin_code  \\\n",
       "0                      FRANKLIN                  YEAR    ANNUAL         00   \n",
       "1                      LAWRENCE                  YEAR    ANNUAL         00   \n",
       "3                        MORGAN                  YEAR    ANNUAL         00   \n",
       "4                        BLOUNT                  YEAR    ANNUAL         00   \n",
       "5                        BLOUNT                  YEAR    ANNUAL         00   \n",
       "...                         ...                   ...       ...        ...   \n",
       "2148  OTHER (COMBINED) COUNTIES                  YEAR    ANNUAL         00   \n",
       "2149  OTHER (COMBINED) COUNTIES                  YEAR    ANNUAL         00   \n",
       "2150  OTHER (COMBINED) COUNTIES                  YEAR    ANNUAL         00   \n",
       "2151  OTHER (COMBINED) COUNTIES                  YEAR    ANNUAL         00   \n",
       "2152  OTHER (COMBINED) COUNTIES                  YEAR    ANNUAL         00   \n",
       "\n",
       "             util_practice_desc            load_time end_code zip_5 unit_desc  \\\n",
       "0     ALL UTILIZATION PRACTICES  2018-02-01 00:00:00       00           ACRES   \n",
       "1     ALL UTILIZATION PRACTICES  2018-02-01 00:00:00       00           ACRES   \n",
       "3     ALL UTILIZATION PRACTICES  2018-02-01 00:00:00       00           ACRES   \n",
       "4     ALL UTILIZATION PRACTICES  2018-02-01 00:00:00       00           ACRES   \n",
       "5     ALL UTILIZATION PRACTICES  2012-12-31 00:00:00       00           ACRES   \n",
       "...                         ...                  ...      ...   ...       ...   \n",
       "2148  ALL UTILIZATION PRACTICES  2018-05-10 15:00:31       00           ACRES   \n",
       "2149  ALL UTILIZATION PRACTICES  2017-05-11 15:00:20       00           ACRES   \n",
       "2150  ALL UTILIZATION PRACTICES  2016-05-05 15:04:45       00           ACRES   \n",
       "2151  ALL UTILIZATION PRACTICES  2015-05-07 15:00:14       00           ACRES   \n",
       "2152  ALL UTILIZATION PRACTICES  2015-06-04 15:00:29       00           ACRES   \n",
       "\n",
       "     Value  \n",
       "0      700  \n",
       "1     4390  \n",
       "3       34  \n",
       "4      357  \n",
       "5      459  \n",
       "...    ...  \n",
       "2148  7100  \n",
       "2149  1900  \n",
       "2150  1900  \n",
       "2151  2300  \n",
       "2152  2500  \n",
       "\n",
       "[1719 rows x 39 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['short_desc', 'CV (%)', 'source_desc', 'state_alpha', 'region_desc',\n",
       "       'class_desc', 'year', 'prodn_practice_desc', 'state_name',\n",
       "       'county_ansi', 'statisticcat_desc', 'state_ansi', 'country_code',\n",
       "       'group_desc', 'commodity_desc', 'asd_code', 'location_desc',\n",
       "       'congr_district_code', 'agg_level_desc', 'domain_desc', 'country_name',\n",
       "       'county_code', 'sector_desc', 'watershed_desc', 'watershed_code',\n",
       "       'asd_desc', 'state_fips_code', 'week_ending', 'domaincat_desc',\n",
       "       'county_name', 'reference_period_desc', 'freq_desc', 'begin_code',\n",
       "       'util_practice_desc', 'load_time', 'end_code', 'zip_5', 'unit_desc',\n",
       "       'Value'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['AL', 'AR', 'CA', 'FL', 'GA', 'HI', 'LA', 'MS', 'MO', 'NM', 'NC',\n",
       "       'OK', 'SC', 'TX', 'VA'], dtype=object)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d.state_alpha.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state_alpha</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AL</th>\n",
       "      <td>1811981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AR</th>\n",
       "      <td>52400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA</th>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FL</th>\n",
       "      <td>1391096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GA</th>\n",
       "      <td>7530041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HI</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LA</th>\n",
       "      <td>2690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MO</th>\n",
       "      <td>1293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MS</th>\n",
       "      <td>299302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NC</th>\n",
       "      <td>804435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NM</th>\n",
       "      <td>20828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OK</th>\n",
       "      <td>152776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SC</th>\n",
       "      <td>988463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TX</th>\n",
       "      <td>1544224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VA</th>\n",
       "      <td>218777</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Value\n",
       "state_alpha         \n",
       "AL           1811981\n",
       "AR             52400\n",
       "CA                80\n",
       "FL           1391096\n",
       "GA           7530041\n",
       "HI                 3\n",
       "LA              2690\n",
       "MO              1293\n",
       "MS            299302\n",
       "NC            804435\n",
       "NM             20828\n",
       "OK            152776\n",
       "SC            988463\n",
       "TX           1544224\n",
       "VA            218777"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d[['state_alpha','Value']].groupby(by=\"state_alpha\").sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Clean and process data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01003</td>\n",
       "      <td>22240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01005</td>\n",
       "      <td>2928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01009</td>\n",
       "      <td>408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01011</td>\n",
       "      <td>1118</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id  Value\n",
       "0  01001    444\n",
       "1  01003  22240\n",
       "2  01005   2928\n",
       "3  01009    408\n",
       "4  01011   1118"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove commas in Value\n",
    "d['Value'] = d['Value'].str.replace(',','')\n",
    "\n",
    "# filter out irrelevant Values\n",
    "d = d[~d['Value'].isin(['                 (D)','                 (Z)'])].copy()\n",
    "\n",
    "# change Value to numeric\n",
    "d.Value = pd.to_numeric(d.Value)\n",
    "\n",
    "# add id field (State + County FIPS)\n",
    "d['id'] = d['state_fips_code']+d['county_code']\n",
    "\n",
    "# group by this new id\n",
    "sumdf = d.groupby(['id']).mean()\n",
    "sumdf = sumdf.reset_index()[['id', 'Value']]\n",
    "\n",
    "# round the values\n",
    "sumdf['Value'] = sumdf['Value'].round().astype(int)\n",
    "\n",
    "# view final table\n",
    "sumdf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Join crop data with US counties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com/home/item.html?id=7566e0221e5646f99ea249a197116605' target='_blank'>\n",
       "                        <img src='data:image/png;base64,/9j/4AAQSkZJRgABAgAAAQABAAD/2wBDAAgGBgcGBQgHBwcJCQgKDBQNDAsLDBkSEw8UHRofHh0aHBwgJC4nICIsIxwcKDcpLDAxNDQ0Hyc5PTgyPC4zNDL/2wBDAQkJCQwLDBgNDRgyIRwhMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjL/wAARCACFAMgDASIAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSExBhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD1xuHT3JH6U6oyshKnKcHPQ0jNIq5+Q8gd+5xXkXPWsS01yw2hcZJxzTWMqgHCkd8A8Uh8xgrKUOOR78UNgkPQFVwcZ56UN9+P6n+RpqtI6hgUwfY0FZCVOU4OehovoHUeQCMEZFRTRoIJPlH3T29qVWkZmHyfKcd/QH+tDpI8bLlPmBHQ0nqthrRjj5uT9z9abGHLSEsBlugHsBSsZQCfk/WhVkXPKcnPQ0B0EkOxoyzALuOc8djSSTIyFUkQsxAA3DucUrCXehynBJ7+hH9aSXzdgyV+8vr/AHhQ76grXQ8iUgj5P1psZk27fk+XjvTv3vqn5GkiyDIGxnd2+go6h0ACUEnKc/Wlhz5fOM7m6fU0+o4wfmAbADHt68/1p2sxXuiSoo/Mxxtxubr9TSuWUDDDkgdKcqlejd89KOodCv8AvBYgjbxH+PSnMFO7PmvjuflH9BS7SLTG7gJjp7UsgZkDMSDuXC+nzDr61FtC76iBpTHGCigt1yaUhzMmWAO1ug9xTmDb0+bue3saQhvPT5v4W7e4qiRMSifgqcr6en/6/wBaSOIbnBLcYI2uQB7dac0TSNglSF5AKZ5/OmFUWYDaHyp4AHGCP8aVhpj2hVU+XeOf77ev1opgVd7f6Oe393/Gij5BfzLFMkyxVB3IJPsCDRiX++n/AHwf8aawkU7y68DGAh7496p7ErckbdtO3G7HGfWkjx5Sbc7doxn0pI2Y5DdQfTH9abFEgjGFA47cUdQtoKCFmKY4Ybh9e/8An61JUUqokTuAAQN27vTyrHHzkfQChAxI/vy/7w/9BFOZtq5wT7CoonjEY+dSe5z3pZJE2ffX7w7+4ovoFtRWZyuPKb81/wAaUSIR94D2JpfMT++v50J9wY6UANZ0LD5h+dMk8soAG53L0b/aFSj77fhTZseVycDcv/oQpPZjT1Qvlj+8/wD30aakYxnc+TyfmNLmL++P++6bGY9n3x1P8XvRpcNRY03Jku/3mH3vQkUhQK6KHf5iSfm9j/XFJGY/n+cfeP8AFQrRb5CXHDYGW7YFLSyHrdi+WWYjzXABGBx/hTlLtuG7ocdKYHh3N+8X/vuiMx4Y7xyx/i/CmhMRkAtWJZ+EJ6+1Oljwn3n+8v8AF/tCo2Mf2RvmH3D/ABe1STGPb98feX+L/aFLSxWtxWjG9Pmfqf4vY0hBSZMbmyrdT7ihjFvT5x1P8XsaN0YnTDj7rfxe4o0FqBUM5ZoQTxyQDSMSCrpHjHBzjp/nFO3KXOJdv0Ipg2uro0/y5I7cihghzNKjFtiEEgffPr9PeimgGTchnJIwRjB6dO3rRTT/AK0E1/WpLuZiAFK+5H/16ChYjccgHOBxzSbpv+ecf/fw/wCFIZJA4QomTz9//wCtTuuoWfQEQMCxLZLN/ER3I9aRkEeCC4QDn5ice9KvmqMbE6k/fPr9KbI8mxwY1woyfnPPf0qXaw9bilnZvLI2n1I6/SkCZkdXyAFH3XPvzTxH5h3TKp9FzkCmYWOSQIApZVAwO+Wot3C/YiTzHVUjAEYGSQMHn61Zyf8Ankf0pjwxBOI16jtT/Ji/55p/3yKEmgbTDBLrlMdfSn4qJoIiygxIf+AikaCINHiJAN3OFHoarVE6MkUHe/Hf+lJMP3fT+Jf/AEIUn2eD/njH/wB8CmSQQhBiKMfMvRR/eFJ3sNWuiZl+U8dvSkjX92vHYdqaLeDI/cx/98imQwQmCMmKPO0fwj0o1uGliSNeX4/i9KVRh347/wBKiS3hLyZhj+8P4R/dFOEMQZh5Sf8AfIoVwdiQZ3NTQB5z8fwr29zTBDF5jDy06D+Ee9HkxeaB5afd/uijUNB+0ecOB93096R8K65B6HouaY0cKzJlEAKt1A9RSjy1kUxopODnbgUBYXcrSIAD1PVCOxpzqPMi4/iPb/ZNMcCRkV4vlyfvYI6GmGGPzHVUjDbBtGB1+alqPQllA2DgfeXt/tCh9qshOAM/0NMVLbZ9yMAcc44ohEIAI8sHc3Ix6mn1FbQG8tpQdwHy8EHFFC7jbKSVIYDjHr+NFLUehJh35yUHYDGaYsaiWQY6qpJ7k5PNSeWPV/8Avo01Btmfr91epz3aqtqrk30YpRhnY+AfXnFQ+S3zRmV/nyeAvOfwzVgsoOCwz6ZqNmCyBsOcjHyqTSaQ02PViSQwwwxmmsgMqnnOD0OKauJJGPzAAAYORTjEDyGdSOhDZ/nT3QtmIio6ZyT/AMCNOZBtPLdP7xpkZZWkGHf5uvH90fSnsx2n5G6e3+NJWsN3uNjcPswGyB1Kkfzpz53R4x1PX6GkjY+WvyN0Hp/jSBjKEZVKjG4Fvp9aOgdST5vUfgKjlUlRl2xuXjj+8Kfh/wC8v/fP/wBemShimCwxuXoMfxD3pvYS3HMgC5Bb/vo0CNQMDIA/2jTWQB4xlsFiD8x9CaCo85B82CrH7x9RSGKka7c/Nk9fmNAQbm+9/wB9GlVQGPJ/M0kS5Ukk53MOp9TTEIEHnPy2Nq/xH1NGwecPvfd/vH1oEYMz8t91f4j6mjyx5w5b7v8AeNK2g76iMVjmQnecq3QFu4oMhZ18tSTg/fBX09qCNk6YDHKt3z3HqacSxkXAK8HqM/1o1DQad7OgdVAyfuuc9D7CkcKsuCX5XIK7iRj/APXT2D70+Zep/h9j70hLLKpI3bgQMDHv6+1IBsQUys25mbtuUKccewpwZYmK4bHX5UJH6UMqySKHjzgHG4A+lIpSJ2UIRz0VCR09hT2DcbGjtANrkgNwGGMBW+ntRQgt2UlljyWJO4c9T60UlsN7g8KiYSOqMG4bK9OOvP5U6NAryCMqAD0649uvHU/nTWDZCIr9Pmy3b/GnrxNgKVXb049aElcG3Ych+dgWUt7U+o2CiEsw+6CQfT6UIsioAzgkdciqTJaFkTI3AHcOmO/tSHywm8uwX1LGlQuy53KOSOnocetRJs8xmZckNgMF4Hb9TSY0SxABM85bkknrxRMSsMhHUKf5UeWgGcsAP9sgVGVd4Xw3DKcAjpxT1SsGl7kgLqNuwAjgYOQP5UyGMKmwsx2/L94jtUm9jz5bfp/jTFYpuJRsFvb/ABpO1wVxdv73GWxtz940kqDYOW+8v8R/vCly3mbvLbGMdR/jSSlimNpHzLycf3h70O1mCvdBIg+Q5bIYfxH6UpjHmqct0P8AEfaklD7Qdy8Mv8PuPelIfevzL0P8P/16PkHzA+WrcuQfdz/jSRKpDYLEbjjDGlTPnPkg/KvQe7U1Yo3Zy0aMd3UqDQA2REEuWj3/AC9/mxg+/wBaNsO9BHtViedhxkfhThGEmHlqifLzhaHVmmTMmPlb7oHqvrmlbyHfzFK4lXaecH7xJ9PenbXLA714/wBn/wCvTCrrIpVi5weHIHp6CnF5V58uP/vs/wCFPQWoieZIkb5UZG7G31H1o3M+xth45PT0oS2iVFUxRkgAE7RTohiJQOgFCT6g2ug3LO4KjbtyCWH/ANegSbHYPuJz/ChPb2pFkCs4Ic/N2Qn+QpUJdmKnAz/EpB6UX7BbuNSVREeJByx/1bep9qKcofyj8y9/4fc+9FS29CklqPUHlm+8f09qaRmYdfu9jjvTtg9W/wC+jTFTc+/LBRlfvHnn/wCsat9iF3AwZXZ5sm08Y+Xp+VEau8as00m7vgL179qeUyOGYe+c/wA6aIiM4lfk5PC/4UrDuNKvHHgPkbupHPJ/+vT3GyFto6DimhNxZXZmAIIzx/KkmHyhd5G4gYyORnnrRsg3Y/YzY3t3zgdKfTQpH8bH8v8ACl2g+v51SJFpr/d/EfzpqLlASWzj+8aSZcQuQWyFJHzGk3oNLUlqOYExYBwdy8j/AHhT9oBOCfzqOVDs++/3l9PUe1EtgjuJJGxKL50mC3ovYE+ntSmNty/vpPyX/CnmMEgksSOQc4pGQYzlsj/aNKw7jFj/AHz7ndvlXqcdz6YoESibALYK5xuPWpPLXcT82SMH5jTWQCRDlsnI+8fSiwXE27Zl2seVOdxJ6Ef40MmZ0yzEbW4zjuPSiSPjcpO5Rxljz7fpSqEchhvyB3Y9DRboF+okibQGVmByB1z1IHeh4zt5lcjI4OPX2FLsDOyksQAp+8fU/wCFIqhtwJbAb+8aLBcd5a/7X/fRpsca+Wv3un940oH7wjLYwCPmPvTTtXCIWLZ6Ak4+vpQAL5ivIqKpUN/Ex9B7Uqq7O5LlDnomCOnuKeI1GcFuf9o1Go/eEZbqe59qLMLoQIxiP75+/Zf8KKeqDLLlsZ/vGik4tjUkiSmRf6v/AIE3/oRo3v8A88j+YqIRqBl7dSSxJJAPU1TeoktCxRUflBCGijjB7/w/yFNeaSNlDRpz6P0HHqPejmtuLlvsPUgu+Px+tIxAmQkj7pHP4UyRpAPM2KNp4+bnGfTH9aWN1ZizMoZgBjPYZ/xpX6Dt1JQQehB+lLTWiRzlo1Y+6g1GsEQkceSmOCMoPy/T9ad2LQkj/wBWv0pJhmCQeqn+VIIyn+rYAYxgjOPpzTWZjFMGxleMgY7A/wBaOlg63RId+eq/lTHDsyplefmzj0Ip/wA/qv5Uwh/OQ5X7rdvcUPYEOJcMBlefakYOcDK8+1B3+YvK9D2pTv3Lyv5UAB3ggZXn2prKxdAxGMnpx2NKQ+5eV/KkcsrITg88AD2NJjQoUGRly2AoP3j7/wCFDDDJtwD0GRTGdXAZchmAzz0Ge/5mpPLU45bjp8xoARQ/msxUAFQOvpnp+dIiZLkk53diRSou4tktw2OtNhQGJGJbcwBPzHrQAuwed1b7v941GAwndVfbjkL/AHv8n0p207UcbmPf5u1PAUouEbA5GKVh3FjlWTIGQQcEHrUagtMQQAuTghue1BjDxo5bBX5ssc4/Wmqy/uwXAO9skHtg/wCAobfUEl0JAjeYxV8AcYOTn9aKbtkQ7fMXLnkleen168fzop/IXzJ6bJ938R/OjYP7zf8AfRpPKXbty2P941TuJWH0yVVZMMAeR1HvTUMrL99Mjg/If8aJPMCZZlIBGQEOev1pN3QJaj34T8RTiAeCMionkBXhZOo/5Zt/hSmXLKqhgSerIQKLoLMPIh/55R/98ijyIf8AnjH/AN8CnYk9V/I0jGQDOUPtgiiy7Bd9xFxG2zop5X/CmMoInOT17H/ZFPaNnGHYbcg7dvoc0SALC4AA4PFJrQaeo/5/VfyphDeYvK9D2+lOzJ/dT/vo/wCFMJk81RtToT94+3t702JCnd5i8r0PalO/eoyvQ9qaTJ5i/KnQ/wAR/wAKCZPMX5U6H+I/4Uhjjv3ryv5UjEq6FiuM/wBDQTJuX5U/76P+FI7FWQyBQuexz2PtQwQSOrKyj5sYJxz3/wDrUYh/55AfWPH9KR5cRlvKfHXPH+NP3t/zxk/8d/xoAjRwA6ohxuxkYwOlShAAAC2PqaZH85l3KRluQ3+6KRIo98mFC4Yfd+XsPShAx0ajy15PT1pI5FWJc7gAOpUimRRqu0MqkN0JHIOKcf8Aj3Qe6/8AoQoVxu1xqMZYCEdG4xgU6RhIF2ozMcNjjgd88+hNOVR5jYyBgDjj1/xpsSMDIyhVDHIJ5J4pWYXQ1lQq48kK20kEgZ/SikGGlcMXYDHQZ+o6eooqXHmKUuUs02TmJ/8AdNFFavYyW4gRSMj5d3JC8U0RjzGGWxtHG4479qKKVkO71JFUKoUdAMCkb78f1P8AI0UU2JDqidm3FRjAAP5k/wCFFFJjRKKrkBrZpWALMmenTjp+tFFEgiO84lN2BTXkIlBx0Uj88f4UUVDbLSQryFfnwDgdKja6O4Ns6e9FFTKTRUYpitdHIOzp70qzCaVEKDHPv2NFFJSbdhuKSuMMuJTaBQASRu9iM9P0qZCwWRS5YqcBj16UUVUW3f1Jkkreg4RYJIduTk9P8KWIYaUZJ+bqf90UUVpYzuNRA0Sg5Ix0phtojJgoMbe3GOaKKTSsO7TF27J/kPVedxJ/rUsf+qX6UUULcT2CL7n4n+ZoooqlsJ7n/9k=' width='200' height='133' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com/home/item.html?id=7566e0221e5646f99ea249a197116605' target='_blank'><b>USA Counties (Generalized)</b>\n",
       "                        </a>\n",
       "                        <br/>USA Counties (Generalized) provides the county boundaries of the United States in the 50 states and the District of Columbia.<img src='https://www.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by esri_dm\n",
       "                        <br/>Last Modified: June 25, 2020\n",
       "                        <br/>0 comments, 201,086,990 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"USA Counties (Generalized)\" type:Feature Layer Collection owner:esri_dm>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Item Added From Toolbar\n",
    "# Title: USA Counties (Generalized) | Type: Feature Service | Owner: esri_dm\n",
    "uscounties = gis.content.get(\"7566e0221e5646f99ea249a197116605\")\n",
    "uscounties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AGE_10_14</th>\n",
       "      <th>AGE_15_19</th>\n",
       "      <th>AGE_20_24</th>\n",
       "      <th>AGE_25_34</th>\n",
       "      <th>AGE_35_44</th>\n",
       "      <th>AGE_45_54</th>\n",
       "      <th>AGE_55_64</th>\n",
       "      <th>AGE_5_9</th>\n",
       "      <th>AGE_65_74</th>\n",
       "      <th>AGE_75_84</th>\n",
       "      <th>...</th>\n",
       "      <th>SHAPE</th>\n",
       "      <th>SQMI</th>\n",
       "      <th>STATE_FIPS</th>\n",
       "      <th>STATE_NAME</th>\n",
       "      <th>Shape_Area</th>\n",
       "      <th>Shape_Leng</th>\n",
       "      <th>Shape__Area</th>\n",
       "      <th>Shape__Length</th>\n",
       "      <th>VACANT</th>\n",
       "      <th>WHITE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>88</td>\n",
       "      <td>104</td>\n",
       "      <td>249</td>\n",
       "      <td>514</td>\n",
       "      <td>597</td>\n",
       "      <td>771</td>\n",
       "      <td>435</td>\n",
       "      <td>105</td>\n",
       "      <td>108</td>\n",
       "      <td>40</td>\n",
       "      <td>...</td>\n",
       "      <td>{\"rings\": [[[-17677357.9077045, 7719547.390490...</td>\n",
       "      <td>7036.89</td>\n",
       "      <td>02</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>2.235582</td>\n",
       "      <td>24.266646</td>\n",
       "      <td>4.886230e+10</td>\n",
       "      <td>3.299975e+06</td>\n",
       "      <td>194</td>\n",
       "      <td>660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>504</td>\n",
       "      <td>604</td>\n",
       "      <td>528</td>\n",
       "      <td>786</td>\n",
       "      <td>910</td>\n",
       "      <td>1237</td>\n",
       "      <td>1056</td>\n",
       "      <td>465</td>\n",
       "      <td>662</td>\n",
       "      <td>539</td>\n",
       "      <td>...</td>\n",
       "      <td>{\"rings\": [[[-10779852.8004753, 5981411.784141...</td>\n",
       "      <td>862.51</td>\n",
       "      <td>38</td>\n",
       "      <td>North Dakota</td>\n",
       "      <td>0.266362</td>\n",
       "      <td>2.150449</td>\n",
       "      <td>4.881826e+09</td>\n",
       "      <td>2.837745e+05</td>\n",
       "      <td>386</td>\n",
       "      <td>7809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>731</td>\n",
       "      <td>674</td>\n",
       "      <td>486</td>\n",
       "      <td>1099</td>\n",
       "      <td>1147</td>\n",
       "      <td>1840</td>\n",
       "      <td>1604</td>\n",
       "      <td>620</td>\n",
       "      <td>1064</td>\n",
       "      <td>763</td>\n",
       "      <td>...</td>\n",
       "      <td>{\"rings\": [[[-10898454.0980662, 6139525.267360...</td>\n",
       "      <td>1294.14</td>\n",
       "      <td>38</td>\n",
       "      <td>North Dakota</td>\n",
       "      <td>0.411900</td>\n",
       "      <td>3.379906</td>\n",
       "      <td>7.683728e+09</td>\n",
       "      <td>4.117952e+05</td>\n",
       "      <td>752</td>\n",
       "      <td>10391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>226</td>\n",
       "      <td>249</td>\n",
       "      <td>334</td>\n",
       "      <td>972</td>\n",
       "      <td>1137</td>\n",
       "      <td>1232</td>\n",
       "      <td>786</td>\n",
       "      <td>227</td>\n",
       "      <td>157</td>\n",
       "      <td>31</td>\n",
       "      <td>...</td>\n",
       "      <td>{\"rings\": [[[-18550860.9655057, 7080282.503091...</td>\n",
       "      <td>3597.77</td>\n",
       "      <td>02</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>0.889389</td>\n",
       "      <td>18.731621</td>\n",
       "      <td>1.831491e+10</td>\n",
       "      <td>2.455085e+06</td>\n",
       "      <td>717</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3622</td>\n",
       "      <td>4245</td>\n",
       "      <td>6458</td>\n",
       "      <td>9486</td>\n",
       "      <td>6880</td>\n",
       "      <td>7883</td>\n",
       "      <td>6374</td>\n",
       "      <td>3907</td>\n",
       "      <td>3891</td>\n",
       "      <td>2754</td>\n",
       "      <td>...</td>\n",
       "      <td>{\"rings\": [[[-11239339.6270714, 6082615.148233...</td>\n",
       "      <td>2055.85</td>\n",
       "      <td>38</td>\n",
       "      <td>North Dakota</td>\n",
       "      <td>0.640877</td>\n",
       "      <td>4.408571</td>\n",
       "      <td>1.192105e+10</td>\n",
       "      <td>5.964787e+05</td>\n",
       "      <td>1715</td>\n",
       "      <td>55718</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 61 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   AGE_10_14  AGE_15_19  AGE_20_24  AGE_25_34  AGE_35_44  AGE_45_54  \\\n",
       "0         88        104        249        514        597        771   \n",
       "1        504        604        528        786        910       1237   \n",
       "2        731        674        486       1099       1147       1840   \n",
       "3        226        249        334        972       1137       1232   \n",
       "4       3622       4245       6458       9486       6880       7883   \n",
       "\n",
       "   AGE_55_64  AGE_5_9  AGE_65_74  AGE_75_84  ...  \\\n",
       "0        435      105        108         40  ...   \n",
       "1       1056      465        662        539  ...   \n",
       "2       1604      620       1064        763  ...   \n",
       "3        786      227        157         31  ...   \n",
       "4       6374     3907       3891       2754  ...   \n",
       "\n",
       "                                               SHAPE     SQMI  STATE_FIPS  \\\n",
       "0  {\"rings\": [[[-17677357.9077045, 7719547.390490...  7036.89          02   \n",
       "1  {\"rings\": [[[-10779852.8004753, 5981411.784141...   862.51          38   \n",
       "2  {\"rings\": [[[-10898454.0980662, 6139525.267360...  1294.14          38   \n",
       "3  {\"rings\": [[[-18550860.9655057, 7080282.503091...  3597.77          02   \n",
       "4  {\"rings\": [[[-11239339.6270714, 6082615.148233...  2055.85          38   \n",
       "\n",
       "     STATE_NAME  Shape_Area  Shape_Leng   Shape__Area  Shape__Length  VACANT  \\\n",
       "0        Alaska    2.235582   24.266646  4.886230e+10   3.299975e+06     194   \n",
       "1  North Dakota    0.266362    2.150449  4.881826e+09   2.837745e+05     386   \n",
       "2  North Dakota    0.411900    3.379906  7.683728e+09   4.117952e+05     752   \n",
       "3        Alaska    0.889389   18.731621  1.831491e+10   2.455085e+06     717   \n",
       "4  North Dakota    0.640877    4.408571  1.192105e+10   5.964787e+05    1715   \n",
       "\n",
       "   WHITE  \n",
       "0    660  \n",
       "1   7809  \n",
       "2  10391  \n",
       "3   2004  \n",
       "4  55718  \n",
       "\n",
       "[5 rows x 61 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# access data from this item in a spatially-enabled data frame\n",
    "sdf = pd.DataFrame.spatial.from_layer(uscounties.layers[0])\n",
    "sdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AGE_10_14</th>\n",
       "      <th>AGE_15_19</th>\n",
       "      <th>AGE_20_24</th>\n",
       "      <th>AGE_25_34</th>\n",
       "      <th>AGE_35_44</th>\n",
       "      <th>AGE_45_54</th>\n",
       "      <th>AGE_55_64</th>\n",
       "      <th>AGE_5_9</th>\n",
       "      <th>AGE_65_74</th>\n",
       "      <th>AGE_75_84</th>\n",
       "      <th>...</th>\n",
       "      <th>Shape_Area</th>\n",
       "      <th>Shape_Leng</th>\n",
       "      <th>Shape__Area</th>\n",
       "      <th>Shape__Length</th>\n",
       "      <th>VACANT</th>\n",
       "      <th>WHITE</th>\n",
       "      <th>id</th>\n",
       "      <th>Value</th>\n",
       "      <th>ACRES</th>\n",
       "      <th>ValueNorm</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>88</td>\n",
       "      <td>104</td>\n",
       "      <td>249</td>\n",
       "      <td>514</td>\n",
       "      <td>597</td>\n",
       "      <td>771</td>\n",
       "      <td>435</td>\n",
       "      <td>105</td>\n",
       "      <td>108</td>\n",
       "      <td>40</td>\n",
       "      <td>...</td>\n",
       "      <td>2.235582</td>\n",
       "      <td>24.266646</td>\n",
       "      <td>4.886230e+10</td>\n",
       "      <td>3.299975e+06</td>\n",
       "      <td>194</td>\n",
       "      <td>660</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4503609.6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>504</td>\n",
       "      <td>604</td>\n",
       "      <td>528</td>\n",
       "      <td>786</td>\n",
       "      <td>910</td>\n",
       "      <td>1237</td>\n",
       "      <td>1056</td>\n",
       "      <td>465</td>\n",
       "      <td>662</td>\n",
       "      <td>539</td>\n",
       "      <td>...</td>\n",
       "      <td>0.266362</td>\n",
       "      <td>2.150449</td>\n",
       "      <td>4.881826e+09</td>\n",
       "      <td>2.837745e+05</td>\n",
       "      <td>386</td>\n",
       "      <td>7809</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>552006.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>731</td>\n",
       "      <td>674</td>\n",
       "      <td>486</td>\n",
       "      <td>1099</td>\n",
       "      <td>1147</td>\n",
       "      <td>1840</td>\n",
       "      <td>1604</td>\n",
       "      <td>620</td>\n",
       "      <td>1064</td>\n",
       "      <td>763</td>\n",
       "      <td>...</td>\n",
       "      <td>0.411900</td>\n",
       "      <td>3.379906</td>\n",
       "      <td>7.683728e+09</td>\n",
       "      <td>4.117952e+05</td>\n",
       "      <td>752</td>\n",
       "      <td>10391</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>828249.6</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>226</td>\n",
       "      <td>249</td>\n",
       "      <td>334</td>\n",
       "      <td>972</td>\n",
       "      <td>1137</td>\n",
       "      <td>1232</td>\n",
       "      <td>786</td>\n",
       "      <td>227</td>\n",
       "      <td>157</td>\n",
       "      <td>31</td>\n",
       "      <td>...</td>\n",
       "      <td>0.889389</td>\n",
       "      <td>18.731621</td>\n",
       "      <td>1.831491e+10</td>\n",
       "      <td>2.455085e+06</td>\n",
       "      <td>717</td>\n",
       "      <td>2004</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2302572.8</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3622</td>\n",
       "      <td>4245</td>\n",
       "      <td>6458</td>\n",
       "      <td>9486</td>\n",
       "      <td>6880</td>\n",
       "      <td>7883</td>\n",
       "      <td>6374</td>\n",
       "      <td>3907</td>\n",
       "      <td>3891</td>\n",
       "      <td>2754</td>\n",
       "      <td>...</td>\n",
       "      <td>0.640877</td>\n",
       "      <td>4.408571</td>\n",
       "      <td>1.192105e+10</td>\n",
       "      <td>5.964787e+05</td>\n",
       "      <td>1715</td>\n",
       "      <td>55718</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1315744.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 65 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   AGE_10_14  AGE_15_19  AGE_20_24  AGE_25_34  AGE_35_44  AGE_45_54  \\\n",
       "0         88        104        249        514        597        771   \n",
       "1        504        604        528        786        910       1237   \n",
       "2        731        674        486       1099       1147       1840   \n",
       "3        226        249        334        972       1137       1232   \n",
       "4       3622       4245       6458       9486       6880       7883   \n",
       "\n",
       "   AGE_55_64  AGE_5_9  AGE_65_74  AGE_75_84  ...  Shape_Area  Shape_Leng  \\\n",
       "0        435      105        108         40  ...    2.235582   24.266646   \n",
       "1       1056      465        662        539  ...    0.266362    2.150449   \n",
       "2       1604      620       1064        763  ...    0.411900    3.379906   \n",
       "3        786      227        157         31  ...    0.889389   18.731621   \n",
       "4       6374     3907       3891       2754  ...    0.640877    4.408571   \n",
       "\n",
       "    Shape__Area  Shape__Length  VACANT  WHITE   id  Value      ACRES ValueNorm  \n",
       "0  4.886230e+10   3.299975e+06     194    660  NaN    NaN  4503609.6       NaN  \n",
       "1  4.881826e+09   2.837745e+05     386   7809  NaN    NaN   552006.4       NaN  \n",
       "2  7.683728e+09   4.117952e+05     752  10391  NaN    NaN   828249.6       NaN  \n",
       "3  1.831491e+10   2.455085e+06     717   2004  NaN    NaN  2302572.8       NaN  \n",
       "4  1.192105e+10   5.964787e+05    1715  55718  NaN    NaN  1315744.0       NaN  \n",
       "\n",
       "[5 rows x 65 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge in our crop data\n",
    "cropsdf = pd.merge(sdf, sumdf, how='left', left_on='FIPS', right_on='id')\n",
    "\n",
    "# calculate total acres from sq mi\n",
    "cropsdf['ACRES'] = cropsdf['SQMI']*640\n",
    "\n",
    "# calculate crop acres per 1000 county acres\n",
    "cropsdf['ValueNorm'] = (cropsdf['Value'] / cropsdf['ACRES']) * 1000\n",
    "\n",
    "cropsdf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Publish crop layer and display as a map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com/home/item.html?id=c132218b7a334391b504d1ab76623f48' target='_blank'>\n",
       "                        <img src='http://static.arcgis.com/images/desktopapp.png' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com/home/item.html?id=c132218b7a334391b504d1ab76623f48' target='_blank'><b>PEANUTS</b>\n",
       "                        </a>\n",
       "                        <br/><img src='https://www.arcgis.com/home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by mbass@esri.com_prof_services\n",
       "                        <br/>Last Modified: February 18, 2021\n",
       "                        <br/>0 comments, 0 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"PEANUTS\" type:Feature Layer Collection owner:mbass@esri.com_prof_services>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Publish as layer in ArcGIS Online\n",
    "croplyr = cropsdf.spatial.to_featurelayer(f'{crops[cropnum]}', folder='USDA Crops')\n",
    "croplyr\n",
    "#croplyr = gis.content.get('627286d4b1c14ad28bdef63416239619')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PEANUTS\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "535312186515415c8111a119551a7ab1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "MapView(layout=Layout(height='400px', width='100%'))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div class=\"map-static-img-preview-d25878c5-aa7a-4bec-933e-a48a193cb117\"><img src=\"\"></img></div>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Display our map\n",
    "m1 = gis.map('USA',zoomlevel=4)\n",
    "m1.add_layer({\"type\":\"FeatureLayer\", \n",
    "               \"url\":croplyr.layers[0].url,\n",
    "               #\"definition_expression\" : \"STATE_NAME='Missouri'\",\n",
    "               \"renderer\":\"ClassedColorRenderer\",\n",
    "               #\"field_name\":\"Value\",\n",
    "               \"field_name\":\"ValueNorm\",\n",
    "               \"opacity\":0.7\n",
    "              })\n",
    "m1.basemap = 'dark-gray'\n",
    "#m1.legend = True\n",
    "print(f'{crops[cropnum]}')\n",
    "m1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "#### Alternative: Map from Spatially-Enabled Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "m2 = gis.map('USA',zoomlevel=4)\n",
    "m2.basemap = 'dark-gray'\n",
    "m2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "cropsdf.spatial.plot(map_widget=m2,\n",
    "                     renderer_type='c',  # for class breaks renderer\n",
    "                     method='esriClassifyQuantile',  # classification algorithm\n",
    "                     class_count=7,  # choose the number of classes\n",
    "                     col='ValueNorm',  # numeric column to classify\n",
    "                     cmap='Blues',  # color map to pick colors from for each class\n",
    "                     outline_style = 'n', # set outline to null\n",
    "                     alpha = 0.1\n",
    "               )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "For details on parameters, see [ArcGIS API for Python reference](https://developers.arcgis.com/python/api-reference/arcgis.mapping.toc.html?highlight=esriclassifynaturalbreaks#renderers)\n",
    "##### method options:\n",
    " * esriClassifyDefinedInterval\n",
    " * esriClassifyEqualInterval\n",
    " * esriClassifyGeometricalInterval\n",
    " * esriClassifyNaturalBreaks\n",
    " * esriClassifyQuantile\n",
    " * esriClassifyStandardDeviation\n",
    " * esriClassifyManual\n",
    "\n",
    "##### cmap options: \n",
    "optional string or list. This is the color scheme a user can provide if the exact color is not needed, or a user can provide a list with the color defined as: [red, green blue, alpha]. The values red, green, blue are from 0-255 and alpha is a float value from 0 - 1. The default value is ‘jet’ color scheme."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "from arcgis.mapping import display_colormaps\n",
    "display_colormaps()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "esriNotebookRuntime": {
   "notebookRuntimeName": "ArcGIS Notebook Python 3 Standard",
   "notebookRuntimeVersion": "4.0"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}