csv-to-db.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Instructions for building field maps\n",
    "\n",
    "1. Begin with a blank template like:\n",
    "\n",
    "```python\n",
    "# Oregon\n",
    "state = fieldmaps.State('OR')\n",
    "\n",
    "layout = {\n",
    "    'api_field': '',\n",
    "    'date_field': '',\n",
    "    'lat_field': '',\n",
    "    'lon_field': '',\n",
    "    'status_field': '',\n",
    "    'status_map': None,\n",
    "    'type_field': '',\n",
    "    'type_map': None\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, )\n",
    "wells.write_to_db()\n",
    "```\n",
    "\n",
    "  * The API and Date fields can be strings or lists of strings (which will be evaluated in the order given).\n",
    "  * If the date is in JavaScript epoch time, add `webdate = True` to the state object declaration\n",
    "2. Fill in the fields (except status_map and type_map) from the provided list\n",
    "3. Change the second to last line to: `wells = fieldmaps.Dataset(state, layout)`\n",
    "4. Overwrite the status_map and type_map lines with the output, and fill in the appropriate values\n",
    "\n",
    "A (likely incomplete) list of valid types:\n",
    "well_types = {'OIL',\n",
    "    'GAS',\n",
    "    'OILANDGAS',\n",
    "    'INJECTION',\n",
    "    'STORAGE',\n",
    "    'DISPOSAL',\n",
    "    'SERVICE',\n",
    "    'DRY HOLE',\n",
    "    'OBSERVATION',\n",
    "    'TEST',\n",
    "    'WATER',\n",
    "    'OTHER',\n",
    "    'UNKNOWN'}\n",
    "well_statuses = {'ACTIVE', 'INACTIVE', 'A', 'PA', 'TA', 'SI', 'DRY', 'ORPHAN', 'PERMITTED', 'CANCELLED', 'UNKNOWN'} # PA: plugged and abandoned, TA: temporarily abandoned\n",
    "well_categories = {'CONVENTIONAL','UNCONVENTIONAL','FRAC','CBM','SERVICE','TEST','STORAGE','WATER','OTHER','UNKNOWN'}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# module that imports CSVs and processes to DB\n",
    "import os, errno, array, csv, math, re\n",
    "from datetime import datetime\n",
    "import dateutil.parser\n",
    "import importlib\n",
    "import psycopg2\n",
    "import fieldmaps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Alabama\n",
    "import fieldmaps.al\n",
    "state = fieldmaps.State('AL', webdate = True)\n",
    "well_source_url = 'http://www.gsa.state.al.us/ogb/results.aspx?api=%s&permit=%s'\n",
    "state.source_url = 'http://www.gsa.state.al.us/ogb/db_main.html'\n",
    "state.description = \"\"\"\n",
    "Data from the Geological Survey of Alabama State Oil and Gas Board\n",
    "\"\"\"\n",
    "state.write_to_db()\n",
    "\n",
    "dataset = fieldmaps.Dataset(state, fieldmaps.al.layout)\n",
    "db_result = dataset.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Alaska\n",
    "import fieldmaps.ak\n",
    "state = fieldmaps.State('AK')\n",
    "state.projection = 4267 # NAD27\n",
    "state.source_url = 'http://aogweb.state.ak.us/DataMiner3/Forms/WellList.aspx'\n",
    "state.description = \"\"\"\n",
    "Data from the Alaska Oil and Gas Conservation Commission\n",
    "\"\"\"\n",
    "state.write_to_db()\n",
    "\n",
    "dataset = fieldmaps.Dataset(state, fieldmaps.ak.layout)\n",
    "dataset.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Arkansas\n",
    "import fieldmaps.ar\n",
    "state = fieldmaps.State('AR')\n",
    "state.source_url = 'http://www.noggateway.org/reports'\n",
    "state.description = \"Data from the National Oil and Gas Gateway\"\n",
    "state.write_to_db()\n",
    "\n",
    "state.projection = 4269 # NAD83\n",
    "\n",
    "dataset = fieldmaps.Dataset(state, fieldmaps.ar.layout)\n",
    "dataset.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Arizona\n",
    "import fieldmaps.az\n",
    "state = fieldmaps.State('AZ')\n",
    "state.source_url = 'http://services.azgs.az.gov/arcgis/rest/services/aasggeothermal/AZWellHeaders/MapServer/0'\n",
    "state.description = \"\"\"\n",
    "Data from the Arizona Oil and Gas Conservation Commission ArcGIS map at http://ogviewer.azdeq.gov/.\n",
    "\n",
    "Dates do not seem to be available from this source.\n",
    "\"\"\"\n",
    "state.date_function = lambda x: None\n",
    "state.write_to_db()\n",
    "\n",
    "dataset = fieldmaps.Dataset(state, fieldmaps.az.layout)\n",
    "db_result = dataset.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# California\n",
    "import fieldmaps.ca\n",
    "state = fieldmaps.State('CA', filename = 'california-data.csv', prepend_api = True, webdate = True)\n",
    "state.source_url = 'http://spatialservices.conservation.ca.gov/arcgis/rest/services/DOMS/Wells/MapServer/0'\n",
    "state.description = \"\"\"California Division of Oil, Gas, & Geothermal Resources \n",
    "at http://www.conservation.ca.gov/dog. Type codes: ftp://ftp.consrv.ca.gov/pub/oil/GIS/ReadMe.pdf\"\"\"\n",
    "state.write_to_db()\n",
    "\n",
    "dataset = fieldmaps.Dataset(state, fieldmaps.ca.layout)\n",
    "dataset.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Colorado\n",
    "import fieldmaps.co\n",
    "state = fieldmaps.State('CO')\n",
    "\n",
    "wells = fieldmaps.Dataset(state, fieldmaps.co.layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connecticut - No Oil or Gas Drilling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delaware - No Oil or Gas Drilling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# District of Columbia - No Oil or Gas Drilling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Florida\n",
    "state = fieldmaps.State('FL', webdate = True,  prepend_api = True)\n",
    "import fieldmaps.fl\n",
    "\n",
    "wells = fieldmaps.Dataset(state, fieldmaps.fl.layout, quiet = True)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Georgia - No oil or gas activity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Hawaii - No oil or gas activity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Idaho\n",
    "state = fieldmaps.State('ID')\n",
    "# import fieldmaps.id\n",
    "state.load_data()\n",
    "layout = {\n",
    "    'api_field': 'API',\n",
    "    'date_field': 'Date',\n",
    "    'lat_field': 'lattitudeWGS84',\n",
    "    'lon_field': 'longitudeWGS84',\n",
    "    'status_field': 'Status',\n",
    "    'status_map': {\n",
    "        'Not Drilled - Permit Expired': 'CANCELLED',\n",
    "        'Permitted Well': 'PERMITTED',\n",
    "        'Expired Permit': 'CANCELLED',\n",
    "        'Plugged and Abandoned': 'PA',\n",
    "        'Shut In': 'SI',\n",
    "        'Not Drilled': 'PERMITTED',\n",
    "        'Drilling': 'ACTIVE',\n",
    "        'Producing': 'ACTIVE',\n",
    "        'Completed': 'ACTIVE',\n",
    "    },\n",
    "    'type_field': 'Well Type',\n",
    "    'type_map': {\n",
    "        '': 'UNKNOWN',\n",
    "        'Expired Location': 'UNKNOWN',\n",
    "        'Oil Well': 'OIL',\n",
    "        'Oil & Gas': 'OILANDGAS',\n",
    "        'Gas Well': 'GAS',\n",
    "        'Dry Hole': 'DRY HOLE',\n",
    "    }\n",
    "}\n",
    "wells = fieldmaps.Dataset(state, layout, quiet = True)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Illinois\n",
    "state = fieldmaps.State('IL', webdate = True, filename = 'il-data-old.csv')\n",
    "import fieldmaps.il\n",
    "wells = fieldmaps.Dataset(state, fieldmaps.il.layout, quiet = True)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Indiana\n",
    "state = fieldmaps.State('IN', prepend_api = True)\n",
    "import fieldmaps.indiana\n",
    "wells = fieldmaps.Dataset(state, fieldmaps.indiana.layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Iowa - No oil or Gas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Kansas\n",
    "state = fieldmaps.State('KS', webdate = True)\n",
    "state.projection = 4269 # NAD83\n",
    "\n",
    "import fieldmaps.ks\n",
    "layout = fieldmaps.ks.layout\n",
    "wells = fieldmaps.Dataset(state, layout, quiet = True)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Kentucky\n",
    "state = fieldmaps.State('KY')\n",
    "state.projection = 4267 # NAD27\n",
    "\n",
    "import fieldmaps.ky\n",
    "wells = fieldmaps.Dataset(state, fieldmaps.ky.layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Louisiana\n",
    "\n",
    "state = fieldmaps.State('LA', webdate = True)\n",
    "import fieldmaps.la\n",
    "\n",
    "wells = fieldmaps.Dataset(state, fieldmaps.la.layout, quiet = True)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Maine - No Oil or Gas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Maryland - gas only\n",
    "state = fieldmaps.State('MD', prepend_api = True)\n",
    "layout = {\n",
    "    'api_field': 'Id',\n",
    "    'date_field': 'Date_drill',\n",
    "    'lat_field': 'Y',\n",
    "    'lon_field': 'X',\n",
    "    'status_field': 'Status',\n",
    "    'status_map': {\n",
    "        '?': 'UNKNOWN',\n",
    "        'Abandoned': 'A',\n",
    "        'Active': 'ACTIVE',\n",
    "        'Plugged': 'PA',\n",
    "        'Plugged?': 'PA',\n",
    "        'plugged': 'PA',\n",
    "    },\n",
    "    'type_field': 'Status',\n",
    "    'type_map': {\n",
    "        '?': 'GAS',\n",
    "        'Abandoned': 'GAS',\n",
    "        'Active': 'GAS',\n",
    "        'Plugged': 'GAS',\n",
    "        'Plugged?': 'GAS',\n",
    "        'plugged': 'GAS',\n",
    "    },\n",
    "}\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Massachusetts - No oil or gas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Michigan\n",
    "# note - these are bottom hole locations. Getting surface locations\n",
    "# and joining dates would take some work\n",
    "state = fieldmaps.State('MI', webdate = True)\n",
    "layout = {\n",
    "    'api_field': 'api_wellno',\n",
    "    'date_field': ['PermDate', 'PlugDate'],\n",
    "    'lat_field': 'bh_lat',\n",
    "    'lon_field': 'bh_long',\n",
    "    'status_field': 'well_stat',\n",
    "    'status_map': {\n",
    "        'Orphan': 'ORPHAN',\n",
    "        'Pilot': 'UNKNOWN',\n",
    "        'Plugged Back': 'PA',\n",
    "        'Plugging Approved': 'PA',\n",
    "        'Plugging Completed': 'PA',\n",
    "        'Producing': 'ACTIVE',\n",
    "        'Shut_In': 'SI',\n",
    "        'Suspended Operations': 'INACTIVE',\n",
    "        'Temporarily Abandoned': 'TA',\n",
    "        'Terminated Permit': 'CANCELLED',\n",
    "        'Unknown': 'UNKNOWN',\n",
    "        'Well Completed': 'ACTIVE',\n",
    "    },\n",
    "    'type_field': 'well_type',\n",
    "    'type_map': {\n",
    "        'GAS': 'GAS',\n",
    "        'GC': 'GAS',\n",
    "        'OIL': 'OIL',\n",
    "    },\n",
    "}\n",
    "\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Minnesota - no oil or gas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Mississippi\n",
    "state = fieldmaps.State('MS')\n",
    "layout = {\n",
    "   'date_field': ['SPUD DATE', 'FIRST COMPLETION DATE', 'FIRST PRODUCTION DATE'],\n",
    "    'api_field': 'API NUMBER',\n",
    "    'type_field': 'STANDARD WELL TYPE DESCRIPTION',\n",
    "    'status_field': 'STANDARD WELL STATUS DESCRIPTION',\n",
    "    'lat_field': 'WELLHEAD LATITUDE, DECIMAL DEGREES',\n",
    "    'lon_field': 'WELLHEAD LONGITUDE, DECIMAL DEGREES',\n",
    "    'status_map': {\n",
    "        'Unassigned': 'UNKNOWN',\n",
    "        'Abandoned': 'A',\n",
    "        'Abandoned:Plugged': 'PA',\n",
    "        'Abandoned:Dry Hole': 'A',\n",
    "        'Active:Producing': 'ACTIVE',\n",
    "        'Permit:Active': 'ACTIVE',\n",
    "        'Inactive:Temporarily Abandoned': 'TA',\n",
    "        'Permit:Cancelled Expired': 'CANCELLED',\n",
    "        'Active:Completed': 'ACTIVE',\n",
    "        'Active': 'ACTIVE',\n",
    "        'Inactive': 'A',\n",
    "        'Active:Injecting': 'ACTIVE',\n",
    "    },\n",
    "    'type_map': {\n",
    "        'Other': 'OTHER',\n",
    "        'Gas': 'GAS',\n",
    "        'Unassigned': 'OTHER',\n",
    "        'Oil': 'OIL',\n",
    "        'Observation Monitor Strat Test': 'TEST',\n",
    "        'Gas Storage': 'STORAGE',\n",
    "        'Water Supply': 'WATER',\n",
    "        'Permit': 'UNKNOWN',\n",
    "        'Underground Injection Control:Class 2 Disposal': 'INJECTION',\n",
    "        'Dry Hole': 'DRY HOLE',\n",
    "        'Underground Injection Control:Class 2 Enhanced Recovery': 'INJECTION',\n",
    "        'Underground Injection Control:Class 1': 'INJECTION',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Missouri\n",
    "state = fieldmaps.State('MO', strip_whitespace = ['Well Status', 'Well Type'], prepend_api = True)\n",
    "#import fieldmaps.ks\n",
    "layout = {\n",
    "    'api_field': 'API Number',\n",
    "    'date_field': ['Spud Date','Well Status Date'],\n",
    "    'lat_field': 'Well Latitude Decimal',\n",
    "    'lon_field': 'Well Longitude Decimal',\n",
    "    'status_field': 'Well Status',\n",
    "    'status_map': {\n",
    "        'Abandoned': 'A',\n",
    "        'Abandoned, Known Location and Verified': 'A',\n",
    "        'Abandoned, No evidence of existence/ Unable to find': 'A',\n",
    "        'Abandoned, Unknown Location': 'A',\n",
    "        'Active Well': 'ACTIVE',\n",
    "        'Inactive Ð Initial 90 day window': 'TA',\n",
    "        'Plugged - Approved': 'PA',\n",
    "        'Proposed Well (Associated with permit application only)': 'UNKNOWN',\n",
    "        'Shut in - Complete': 'SI',\n",
    "        'Shut in - Extended': 'SI',\n",
    "        'Shut in - Incomplete': 'SI',\n",
    "        'Temporarily Abandoned(Idle)': 'TA',\n",
    "        'Under Construction': 'PERMITTED',\n",
    "        'Unknown Well Status': 'UNKNOWN',\n",
    "        'Water Well Conversion': 'UNKNOWN',\n",
    "    },\n",
    "    'type_field': 'Well Type',\n",
    "    'type_map': {\n",
    "        'Dry Hole': 'DRY HOLE',\n",
    "        'Gas Storage Wells': 'STORAGE',\n",
    "        'Gas(Coalbed Methane)': 'GAS',\n",
    "        'Gas(Convertional, Commercial)': 'GAS',\n",
    "        'Gas(Private Use)': 'GAS',\n",
    "        'Horizontal Oil Well': 'OIL',\n",
    "        'Injection(Class II Disposal)': 'INJECTION',\n",
    "        'Injection(Class II EOR)': 'INJECTION',\n",
    "        'Observation': 'OBSERVATION',\n",
    "        'Oil': 'OIL',\n",
    "        'Stratigraphic Test': 'TEST',\n",
    "        'Stratigraphic Test, None Oil/Gas Related(has geophysical log)': 'TEST',\n",
    "        'Unknown Well Type': 'UNKNOWN',\n",
    "        'Water Well': 'WATER',\n",
    "        'Water Well, None Oil/Gas Related(has geophysical log)': 'WATER',\n",
    "    },\n",
    "}\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Montana\n",
    "state = fieldmaps.State('MT')\n",
    "#import fieldmaps.ks\n",
    "layout = {\n",
    "   'date_field': 'Dt_Cmp',\n",
    "    'api_field': 'API #',\n",
    "    'type_field': 'Well_Type',\n",
    "    'status_field': 'Wl_Status',\n",
    "    'lat_field': 'Wh_Lat',\n",
    "    'lon_field': 'Wh_Long',\n",
    "    'status_map': {\n",
    "        'Abandoned': 'A',\n",
    "        'Abandoned - Unapproved': 'A',\n",
    "        'Active Injection': 'ACTIVE',\n",
    "        'Completed': 'ACTIVE',\n",
    "        'Domestic': 'ACTIVE',\n",
    "        'Expired Permit': 'CANCELLED',\n",
    "        'Expired, Not Released': 'CANCELLED',\n",
    "        'Not Completed': 'PERMITTED',\n",
    "        'Other': 'UNKNOWN',\n",
    "        'P&A - Approved': 'PA',\n",
    "        'Permit to Drill': 'PERMITTED',\n",
    "        'Permitted Injection Well': 'PERMITTED',\n",
    "        'Producing': 'ACTIVE',\n",
    "        'Revoked Inj. Permit': 'CANCELLED',\n",
    "        'Shut In': 'SI',\n",
    "        'Spudded': 'ACTIVE',\n",
    "        'Temporarily Abandoned': 'TA',\n",
    "        'Unknown': 'UNKNOWN',\n",
    "        'Water Well, Completed': 'ACTIVE',\n",
    "        'Water Well, Released': 'ACTIVE',\n",
    "    },\n",
    "    'type_map': {\n",
    "        'Class V Injection': 'INJECTION',\n",
    "        'Coal Bed Methane': 'GAS',\n",
    "        'Domestic Water': 'WATER',\n",
    "        'Dry Hole': 'DRY HOLE',\n",
    "        'Expired Location': 'UNKNOWN',\n",
    "        'Gas': 'GAS',\n",
    "        'Gas Storage': 'STORAGE',\n",
    "        'Injection - Disposal': 'DISPOSAL',\n",
    "        'Injection, EOR': 'INJECTION',\n",
    "        'Injection, Indian Lands': 'INJECTION',\n",
    "        'Monitor/Observation': 'OBSERVATION',\n",
    "        'Oil': 'OIL',\n",
    "        'Oil & Gas': 'OILANDGAS',\n",
    "        'Stratigraphic Test': 'TEST',\n",
    "        'Unknown': 'UNKNOWN',\n",
    "        'Water Source': 'WATER',\n",
    "    },\n",
    "}\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Nebraska\n",
    "state = fieldmaps.State('NE')\n",
    "\n",
    "layout = {\n",
    "   'date_field': ['SPUD DATE', 'FIRST COMPLETION DATE', 'FIRST PRODUCTION DATE'],\n",
    "    'api_field': 'API NUMBER',\n",
    "    'type_field': 'STANDARD WELL TYPE DESCRIPTION',\n",
    "    'status_field': 'STANDARD WELL STATUS DESCRIPTION',\n",
    "    'lat_field': 'WELLHEAD LATITUDE, DECIMAL DEGREES',\n",
    "    'lon_field': 'WELLHEAD LONGITUDE, DECIMAL DEGREES',\n",
    "    'status_map': {\n",
    "        'Abandoned': 'A',\n",
    "        'Abandoned:Dry Hole': 'A',\n",
    "        'Abandoned:Plugged': 'PA',\n",
    "        'Active': 'ACTIVE',\n",
    "        'Active:Injecting': 'ACTIVE',\n",
    "        'Active:Producing': 'ACTIVE',\n",
    "        'Inactive': 'A',\n",
    "        'Inactive:Shut In': 'SI',\n",
    "        'Inactive:Temporarily Abandoned': 'TA',\n",
    "        'Permit:Active': 'ACTIVE',\n",
    "        'Permit:Cancelled Expired': 'CANCELLED',\n",
    "        'Unassigned': 'UNKNOWN',\n",
    "        'Unknown': 'UNKNOWN',\n",
    "    },\n",
    "    'type_map': {\n",
    "        'Abandoned': 'UNKNOWN',\n",
    "        'Dry Hole': 'DRY HOLE',\n",
    "        'Gas': 'GAS',\n",
    "        'Gas Storage': 'STORAGE',\n",
    "        'Observation Monitor Strat Test': 'TEST',\n",
    "        'Oil': 'OIL',\n",
    "        'Other': 'OTHER',\n",
    "        'Permit': 'UNKNOWN',\n",
    "        'Unassigned': 'UNKNOWN',\n",
    "        'Underground Injection Control:Class 1': 'INJECTION',\n",
    "        'Underground Injection Control:Class 2': 'INJECTION',\n",
    "        'Underground Injection Control:Class 2 Disposal': 'DISPOSAL',\n",
    "        'Underground Injection Control:Class 2 Enhanced Recovery': 'INJECTION',\n",
    "        'Water Supply': 'WATER',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Nevada\n",
    "state = fieldmaps.State('NV') # strip_whitespace = ['Well Status', 'Well Type']\n",
    "state.projection = 26910\n",
    "layout = {\n",
    "    'api_field': 'API',\n",
    "    'date_field': 'COMPL_DATE',\n",
    "    'lat_field': 'APROX_UTMN',\n",
    "    'lon_field': 'APROX_UTME',\n",
    "    'status_field': 'STATUS',\n",
    "    'status_map': {\n",
    "        ' ': 'UNKNOWN',\n",
    "        'A': 'A',\n",
    "        'Abandoned': 'A',\n",
    "        'Cancelled': 'CANCELLED',\n",
    "        'D & A': 'A',\n",
    "        'D & A (?)': 'A',\n",
    "        'D & A ?': 'A',\n",
    "        'D&A': 'A',\n",
    "        'D&A(?)': 'A',\n",
    "        'D&A,Water well': 'A',\n",
    "        'Drilled': 'ACTIVE',\n",
    "        'Drilled?': 'ACTIVE',\n",
    "        'Expired': 'CANCELLED',\n",
    "        'Geothermal': 'ACTIVE',\n",
    "        'Geothermal Wel': 'ACTIVE',\n",
    "        'Injection': 'ACTIVE',\n",
    "        'J & A': 'A',\n",
    "        'J & A ?': 'A',\n",
    "        'J&A ?': 'A',\n",
    "        'Lease closed': 'CANCELLED',\n",
    "        'Never Drilled': 'CANCELLED',\n",
    "        'Never drilled': 'CANCELLED',\n",
    "        'Never drilled?': 'CANCELLED',\n",
    "        'P & A': 'PA',\n",
    "        'P & A (?)': 'PA',\n",
    "        'P & A 7/17/95': 'PA',\n",
    "        'P & A 7/27/95': 'PA',\n",
    "        'P&A': 'PA',\n",
    "        'P&A (Producer)': 'PA',\n",
    "        'Permit Revoked': 'CANCELLED',\n",
    "        'Prod?Inject': 'ACTIVE',\n",
    "        'Producer': 'ACTIVE',\n",
    "        'Producer(?)': 'ACTIVE',\n",
    "        'Shut In': 'SI',\n",
    "        'Shut in': 'SI',\n",
    "        'Suspended': 'TA',\n",
    "        'TA': 'TA',\n",
    "        'TA ?': 'TA',\n",
    "        'Temp. Shut In': 'TA',\n",
    "        'Testing': 'ACTIVE',\n",
    "        'Water Disposal': 'ACTIVE',\n",
    "        'Water Well': 'ACTIVE',\n",
    "        'Water well': 'ACTIVE',\n",
    "        'drilled': 'ACTIVE',\n",
    "        'injection': 'ACTIVE',\n",
    "        'injection well': 'ACTIVE',\n",
    "        'producer': 'ACTIVE',\n",
    "        're-entry': 'ACTIVE',\n",
    "        'water well': 'ACTIVE',\n",
    "    },\n",
    "    'type_field': 'SHOW',\n",
    "    'type_map': {\n",
    "        ' ': 'UNKNOWN',\n",
    "        '?': 'UNKNOWN',\n",
    "        'GAS': 'GAS',\n",
    "        'GAS  OIL': 'OILANDGAS',\n",
    "        'GAS  WATER': 'GAS',\n",
    "        'GAS WATER': 'GAS',\n",
    "        'OIL': 'OIL',\n",
    "        'OIL   GAS': 'OILANDGAS',\n",
    "        'OIL  GAS': 'OILANDGAS',\n",
    "        'OIL  GAS  WATER': 'OILANDGAS',\n",
    "        'OIL  WATER': 'OIL',\n",
    "        'OIL  WATER  GAS': 'OILANDGAS',\n",
    "        'OIL  `': 'OIL',\n",
    "        'OIL GAS': 'OIL',\n",
    "        'OIL GAS WATER': 'OILANDGAS',\n",
    "        'OIL WATER': 'OIL',\n",
    "        'OIL, WATER': 'OIL',\n",
    "        'OIL?': 'OIL',\n",
    "        'Oil': 'OIL',\n",
    "        'WATER': 'WATER',\n",
    "        'WATER  GAS': 'GAS',\n",
    "        'WATER  HELIUM G': 'OTHER',\n",
    "        'WATER  OIL': 'OIL',\n",
    "        'WATER  OIL GAS': 'OILANDGAS',\n",
    "        'Water': 'WATER',\n",
    "        'weak oil': 'OIL',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# New Hampshire - no oil or gas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# New Jersey - no oil or gas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# New Mexico\n",
    "# I think the data is actually in WGS84 b/c that's how I requested\n",
    "# it from the NM ArcGIS server\n",
    "# status map: ftp://164.64.106.6/Public/OCD/OCD%20GIS%20Data/Metadata/NM_Wells_Metadata.pdf\n",
    "state = fieldmaps.State('NM')\n",
    "layout = {\n",
    "    'api_field': 'API',\n",
    "    'date_field': 'spud_date',\n",
    "    'lat_field': 'latitude',\n",
    "    'lon_field': 'longitude',\n",
    "    'status_field': 'status',\n",
    "    'status_map': {\n",
    "        '0': 'UNKNOWN',\n",
    "        'A': 'ACTIVE',\n",
    "        'C': 'CANCELLED',\n",
    "        'D': 'A',\n",
    "        'E': 'CANCELLED',\n",
    "        'H': 'A',\n",
    "        'N': 'PERMITTED',\n",
    "        'P': 'PA',\n",
    "        'Q': 'PA',\n",
    "        'S': 'SI',\n",
    "        'T': 'TA',\n",
    "        'X': 'PERMITTED',\n",
    "        'Z': 'TA',\n",
    "    },\n",
    "    'type_field': 'well_type',\n",
    "    'type_map': {\n",
    "        'C': 'OILANDGAS',\n",
    "        'G': 'GAS',\n",
    "        'I': 'INJECTION',\n",
    "        'M': 'OTHER',\n",
    "        'O': 'OIL',\n",
    "        'S': 'DISPOSAL',\n",
    "        'W': 'WATER',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# New York\n",
    "state = fieldmaps.State('NY', webdate = True)\n",
    "state.projection = 4269 # NAD83\n",
    "# STATUS CODES LOOKUP https://www.dec.ny.gov/energy/30438.html\n",
    "layout = {\n",
    "    'api_field': 'API_WELLNO',\n",
    "    'date_field': ['DATE_SPUDDED', 'DATE_PERMIT_APPLICATION', 'DATE_WELL_COMPLETED'],\n",
    "    'lat_field': 'SURFACE_LATITUDE',\n",
    "    'lon_field': 'SURFACE_LONGITUDE',\n",
    "    'status_field': 'WELL_STATUS',\n",
    "    'status_map': {\n",
    "        'AC': 'ACTIVE',\n",
    "        'AR': 'UNKNOWN',\n",
    "        'CA': 'CANCELLED',\n",
    "        'CO': 'ACTIVE',\n",
    "        'Confidential': 'ACTIVE',\n",
    "        'DC': 'ACTIVE',\n",
    "        'DD': 'ACTIVE',\n",
    "        'DG': 'ACTIVE',\n",
    "        'EX': 'CANCELLED',\n",
    "        'IN': 'INACTIVE',\n",
    "        'NR': 'UNKNOWN',\n",
    "        'PA': 'PA',\n",
    "        'PB': 'PA',\n",
    "        'PI': 'PERMITTED',\n",
    "        'PM': 'PA',\n",
    "        'RE': 'CANCELLED',\n",
    "        'SI': 'SI',\n",
    "        'TA': 'TA',\n",
    "        'TR': 'UNKNOWN',\n",
    "        'UL': 'UNKNOWN',\n",
    "        'UM': 'UNKNOWN',\n",
    "        'UN': 'UNKNOWN',\n",
    "        'VP': 'CANCELLED',\n",
    "    },\n",
    "    'type_field': 'WELL_TYPE',\n",
    "    'type_map': {\n",
    "        'BR': 'WATER',\n",
    "        'Confidential': 'UNKNOWN',\n",
    "        'DH': 'DRY HOLE',\n",
    "        'DS': 'DISPOSAL',\n",
    "        'DW': 'DRY HOLE',\n",
    "        'GD': 'GAS',\n",
    "        'GE': 'GAS',\n",
    "        'GW': 'GAS',\n",
    "        'IW': 'INJECTION',\n",
    "        'LP': 'STORAGE',\n",
    "        'MB': 'OBSERVATION',\n",
    "        'MM': 'OBSERVATION',\n",
    "        'MS': 'OBSERVATION',\n",
    "        'NL': 'OTHER',\n",
    "        'OD': 'OIL',\n",
    "        'OE': 'OIL',\n",
    "        'OW': 'OIL',\n",
    "        'SG': 'TEST',\n",
    "        'ST': 'STORAGE',\n",
    "        'TH': 'OTHER',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# North Carolina - no oil or gas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# North Dakota\n",
    "state = fieldmaps.State('ND')\n",
    "# code definitions: https://www.dmr.nd.gov/oilgas/codehelp.asp\n",
    "layout = {\n",
    "    'api_field': 'api',\n",
    "    'date_field': 'spud_date',\n",
    "    'lat_field': 'latitude',\n",
    "    'lon_field': 'longitude',\n",
    "    'status_field': 'status',\n",
    "    'status_map': {\n",
    "        'A': 'ACTIVE',\n",
    "        'AB': 'A',\n",
    "        'Confidential': 'ACTIVE',\n",
    "        'DRL': 'ACTIVE',\n",
    "        'DRY': 'DRY',\n",
    "        'EXP': 'CANCELLED',\n",
    "        'IA': 'INACTIVE',\n",
    "        'IAW': 'INACTIVE',\n",
    "        'LOC': 'PERMITTED',\n",
    "        'LOCR': 'PERMITTED',\n",
    "        'NC': 'PERMITTED',\n",
    "        'NCW': 'PERMITTED',\n",
    "        'PA': 'PA',\n",
    "        'PNC': 'CANCELLED',\n",
    "        'PNS': 'CANCELLED',\n",
    "        'TA': 'TA',\n",
    "        'TAO': 'TA',\n",
    "        'TASC': 'TA',\n",
    "        'TATD': 'TA',\n",
    "    },\n",
    "    'type_field': 'well_type',\n",
    "    'type_map': {\n",
    "        'AGD': 'DISPOSAL',\n",
    "        'AI': 'INJECTION',\n",
    "        'CBM': 'GAS',\n",
    "        'Confidential': 'UNKNOWN',\n",
    "        'DF': 'INJECTION',\n",
    "        'DFP': 'INJECTION',\n",
    "        'GASC': 'GAS',\n",
    "        'GASD': 'GAS',\n",
    "        'GASN': 'GAS',\n",
    "        'GI': 'INJECTION',\n",
    "        'GS': 'STORAGE',\n",
    "        'INJP': 'INJECTION',\n",
    "        'OG': 'OILANDGAS',\n",
    "        'ST': 'TEST',\n",
    "        'SWD': 'DISPOSAL',\n",
    "        'WI': 'INJECTION',\n",
    "        'WS': 'WATER',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ohio\n",
    "state = fieldmaps.State('OH')\n",
    "\n",
    "layout = {\n",
    "    'api_field': 'api',\n",
    "    'date_field': ['commenced', 'completed', 'issued'],\n",
    "    'lat_field': 'WH_LAT',\n",
    "    'lon_field': 'WH_LONG',\n",
    "    'status_field': 'WL_STATUS_DESC',\n",
    "    'status_map': {\n",
    "        'Active Injection': 'ACTIVE',\n",
    "        'Cancelled': 'CANCELLED',\n",
    "        'Domestic Well': 'ACTIVE',\n",
    "        'Drilled, Inactive': 'INACTIVE',\n",
    "        'Drilling': 'ACTIVE',\n",
    "        'Dry and Abandoned': 'A',\n",
    "        'Exempt Mississippian Well': 'UNKNOWN',\n",
    "        'Field Inspected, Well Not Found': 'UNKNOWN',\n",
    "        'Final Restoration': 'PA',\n",
    "        'Historical Production Well': 'UNKNOWN',\n",
    "        'Lost Hole': 'UNKNOWN',\n",
    "        'Not Drilled': 'CANCELLED',\n",
    "        'Orphan Well - Pending': 'ORPHAN',\n",
    "        'Orphan Well - Ready': 'ORPHAN',\n",
    "        'Plugged Back': 'PA',\n",
    "        'Plugged and Abandoned': 'PA',\n",
    "        'Producing': 'ACTIVE',\n",
    "        'Replugged Well': 'PA',\n",
    "        'Shut in': 'SI',\n",
    "        'Storage Well': 'ACTIVE',\n",
    "        'Temporary Inactive Well Status 1st Year': 'INACTIVE',\n",
    "        'Temporary Inactive Well Status 2nd Year': 'INACTIVE',\n",
    "        'Temporary Inactive Well Status 3rd Year': 'INACTIVE',\n",
    "        'Unknown status': 'UNKNOWN',\n",
    "        'Well Drilled': 'ACTIVE',\n",
    "        'Well Permitted': 'PERMITTED',\n",
    "    },\n",
    "    'type_field': 'MapSymbol_DESC',\n",
    "    'type_map': {\n",
    "        'Brine for dust control': 'WATER',\n",
    "        'Coalbed methane': 'GAS',\n",
    "        'Dry hole': 'DRY HOLE',\n",
    "        'Dry hole with gas show': 'GAS',\n",
    "        'Dry hole with oil and gas show': 'GAS',\n",
    "        'Dry hole with oil show': 'OIL',\n",
    "        'Expired permit': 'UNKNOWN',\n",
    "        'Gas': 'GAS',\n",
    "        'Gas and oil show': 'OILANDGAS',\n",
    "        'Gas show': 'GAS',\n",
    "        'Gas storage': 'GAS',\n",
    "        'Gas with oil show': 'OILANDGAS',\n",
    "        'Injection': 'INJECTION',\n",
    "        'Lost hole': 'UNKNOWN',\n",
    "        'Observation': 'OBSERVATION',\n",
    "        'Oil': 'OIL',\n",
    "        'Oil and gas': 'OILANDGAS',\n",
    "        'Oil and gas converted to water': 'WATER',\n",
    "        'Oil show': 'OIL',\n",
    "        'Oil with gas show': 'OILANDGAS',\n",
    "        'Permitted location': 'UNKNOWN',\n",
    "        'Plugged brine for dust control': 'WATER',\n",
    "        'Plugged gas': 'GAS',\n",
    "        'Plugged gas with oil show': 'OILANDGAS',\n",
    "        'Plugged injection': 'INJECTION',\n",
    "        'Plugged oil': 'OIL',\n",
    "        'Plugged oil and gas': 'OILANDGAS',\n",
    "        'Plugged oil with gas show': 'OILANDGAS',\n",
    "        'Plugged water supply': 'WATER',\n",
    "        'Plugged with gas show': 'GAS',\n",
    "        'Plugged with oil show': 'OIL',\n",
    "        'Radioactive tools lost in hole': 'UNKNOWN',\n",
    "        'Solution mining': 'UNKNOWN',\n",
    "        'Stratigraphy test': 'TEST',\n",
    "        'Unknown status': 'UNKNOWN',\n",
    "        'Water supply': 'WATER',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Oklahoma\n",
    "# about half of Oklahoma's older wells are in TRS coordinates. I know\n",
    "# once upon a time I had a function to convert from TRS to decimal degrees\n",
    "# for the time being, these are getting ignored.\n",
    "# also, unclear if this dataset includes the reservation or not.\n",
    "state = fieldmaps.State('OK')\n",
    "\n",
    "layout = {\n",
    "   'date_field': ['SPUD DATE', 'FIRST COMPLETION DATE', 'FIRST PRODUCTION DATE'],\n",
    "    'api_field': 'API NUMBER',\n",
    "    'type_field': 'STANDARD WELL TYPE DESCRIPTION',\n",
    "    'status_field': 'STANDARD WELL STATUS DESCRIPTION',\n",
    "    'lat_field': 'WELLHEAD LATITUDE, DECIMAL DEGREES',\n",
    "    'lon_field': 'WELLHEAD LONGITUDE, DECIMAL DEGREES',\n",
    "    'status_map': {\n",
    "        'Abandoned:Plugged': 'PA',\n",
    "        'Active': 'ACTIVE',\n",
    "        'Active:Completed': 'ACTIVE',\n",
    "        'Inactive:Temporarily Abandoned': 'TA',\n",
    "        'Permit:Active': 'ACTIVE',\n",
    "        'Permit:Cancelled Expired': 'CANCELLED',\n",
    "        'Unassigned': 'UNKNOWN',\n",
    "        'Unknown': 'UNKNOWN',\n",
    "    },\n",
    "    'type_map': {\n",
    "        'Abandoned': 'UNKNOWN',\n",
    "        'Dry Hole': 'DRY HOLE',\n",
    "        'Gas': 'GAS',\n",
    "        'Gas Storage': 'STORAGE',\n",
    "        'Oil': 'OIL',\n",
    "        'Oil and Gas': 'OILANDGAS',\n",
    "        'Other': 'OTHER',\n",
    "        'Unassigned': 'UNKNOWN',\n",
    "        'Underground Injection Control:Class 2': 'INJECTION',\n",
    "        'Underground Injection Control:Class 2 Disposal': 'DISPOSAL',\n",
    "        'Underground Injection Control:Class 2 Enhanced Recovery': 'INJECTION',\n",
    "        'Unknown': 'UNKNOWN',\n",
    "        'Water Supply': 'WATER',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Oregon\n",
    "state = fieldmaps.State('OR')\n",
    "\n",
    "layout = {\n",
    "    'api_field': 'PermitID',\n",
    "    'date_field': 'ApplicationDate',\n",
    "    'lat_field': 'Latitude',\n",
    "    'lon_field': 'Longitude',\n",
    "    'status_field': 'Status',\n",
    "    'status_map': {\n",
    "        'Application': 'UNKNOWN',\n",
    "        'Application Withdrawn': 'CANCELLED',\n",
    "        'Cancelled': 'CANCELLED',\n",
    "        'Closed': 'A',\n",
    "        'Denied': 'CANCELLED',\n",
    "        'Extended to 11/24/83': 'UNKNOWN',\n",
    "        'Extended to 5/28/93': 'UNKNOWN',\n",
    "        'Extended to 7/25/87': 'UNKNOWN',\n",
    "        'Permitted': 'PERMITTED',\n",
    "        'Terminated': 'A',\n",
    "        'Unknown': 'UNKNOWN',\n",
    "        'Withdrawn': 'CANCELLED',\n",
    "    },\n",
    "    'type_field': 'WellType',\n",
    "    'type_map': {\n",
    "        'Dry Hole': 'DRY HOLE',\n",
    "        'Exploration': 'TEST',\n",
    "        'Former producer - plugged': 'OIL',\n",
    "        'Gas Injection/Withdrawal': 'INJECTION',\n",
    "        'Gas Injection/Withdrawl': 'INJECTION',\n",
    "        'Information': 'OBSERVATION',\n",
    "        'Monitoring': 'OBSERVATION',\n",
    "        'Never produced - plugged ': 'OIL',\n",
    "        'Not Drilled': 'UNKNOWN',\n",
    "        'Not drilled': 'UNKNOWN',\n",
    "        'Observation': 'OBSERVATION',\n",
    "        'Observation well': 'OBSERVATION',\n",
    "        'Producer': 'OIL',\n",
    "        'Water Disposal': 'WATER',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pennsylvania\n",
    "state = fieldmaps.State('PA')\n",
    "state.source_url = 'http://www.depreportingservices.state.pa.us/ReportServer/Pages/ReportViewer.aspx?/Oil_Gas/Spud_External_Data'\n",
    "state.description = \"\"\"\n",
    "Data downloaded in CSV form based on setting SPUD BEGIN DATE = 1/1/1800 and SPUD END DATE = 12/31/2099\n",
    "Represents 142,788, of which 110,000 have dates wells.\n",
    "\"\"\"\n",
    "state.write_to_db()\n",
    "\n",
    "layout = {\n",
    "    'date_field': 'SPUD_DATE',\n",
    "    'api_field': 'API',\n",
    "    'type_field': 'WELL_CODE_DESC',\n",
    "    'status_field': 'WELL_STATUS',\n",
    "    'category_field': 'UNCONVENTIONAL',\n",
    "    'lat_field': 'LATITUDE',\n",
    "    'lon_field': 'LONGITUDE',\n",
    "    'description_layout': '\\n'.join(['County: %s',\n",
    "                             'Operator Name: %s',\n",
    "                             'OGO No: %s',\n",
    "                             'Municipality: %s',\n",
    "                             'Farm Name: %s',\n",
    "                             'Well Configuration: %s'\n",
    "                            ]),\n",
    "    'description_fields': ['COUNTY', 'OPERATOR', 'OGO_NUM', 'MUNICIPALITY', 'FARM_NAME', 'CONFIGURATION'],\n",
    "    'type_map': {\n",
    "        'UNDETERMINED': 'OTHER',\n",
    "        'MULTIPLE WELL BORE TYPE': 'OTHER',\n",
    "        'OIL': 'OIL',\n",
    "        'OBSERVATION': 'OBSERVATION',\n",
    "        'DRY HOLE': 'DRY HOLE',\n",
    "        'COALBED METHANE': 'GAS',\n",
    "        'GAS': 'GAS',\n",
    "        'COMB. OIL&GAS': 'OILANDGAS',\n",
    "        'STORAGE WELL': 'STORAGE',\n",
    "        'WASTE DISPOSAL': 'DISPOSAL',\n",
    "        'INJECTION': 'INJECTION',\n",
    "        'TEST WELL': 'TEST',\n",
    "    },\n",
    "\n",
    "    'status_map': {\n",
    "        'DEP Abandoned List': 'ORPHAN',\n",
    "        'Plugged OG Well': 'PA',\n",
    "        'DEP Orphan List': 'ORPHAN',\n",
    "        'DEP Plugged': 'PA',\n",
    "        'Regulatory Inactive Status': 'UNKNOWN',\n",
    "        'Operator Reported Not Drilled': 'CANCELLED',\n",
    "        'Proposed But Never Materialized': 'CANCELLED',\n",
    "        'Active': 'ACTIVE',\n",
    "        'Abandoned': 'PA',\n",
    "    },\n",
    "\n",
    "    'category_map': {\n",
    "        'Yes': 'UNCONVENTIONAL',\n",
    "        'No': 'CONVENTIONAL',\n",
    "    },\n",
    "\n",
    "}\n",
    "\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rhode Island - no oil or gas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# South Carolina - No oil or gas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# South Dakota\n",
    "state = fieldmaps.State('SD')\n",
    "state.projection = 4269 # NAD83\n",
    "layout = {\n",
    "    'api_field': 'API Number',\n",
    "    'date_field': ['Spud Date', 'Date Permit Issued'],\n",
    "    'lat_field': 'Latitude (GCS83)',\n",
    "    'lon_field': 'Longitude (GCS83)',\n",
    "    'status_field': 'Administrative Status',\n",
    "    'status_map': {\n",
    "        'Abandoned-Not Regulated': 'A',\n",
    "        'Converted-Domestic Gas': 'ACTIVE',\n",
    "        'Converted-Fresh Water Supply': 'ACTIVE',\n",
    "        'Converted-Water Supply-Injection': 'ACTIVE',\n",
    "        'Injecting': 'ACTIVE',\n",
    "        'Never drilled': 'CANCELLED',\n",
    "        'Not Spudded': 'PERMITTED',\n",
    "        'Plugged and Abandoned': 'PA',\n",
    "        'Producing': 'ACTIVE',\n",
    "        'Shut In': 'SI',\n",
    "        'Temporarily Abandoned': 'TA',\n",
    "    },\n",
    "    'type_field': 'Permitted Well Type',\n",
    "    'type_map': {\n",
    "        'Gas': 'GAS',\n",
    "        'Injection': 'INJECTION',\n",
    "        'Injection-Horizontal': 'INJECTION',\n",
    "        'Oil': 'OIL',\n",
    "        'Oil and Gas Exploration': 'TEST',\n",
    "        'Oil-Horizontal': 'OIL',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Tennessee\n",
    "state = fieldmaps.State('TN')\n",
    "\n",
    "state.description = \"\"\"\n",
    "Tennessee's Department of Environment & Conservation does not \n",
    "doesn't make a lot of details available (no spud date, well status). The dates recorded are the \n",
    "Permit Date. Status is set to unknown for all wells. Per their website, \n",
    "<i>\"This minimal permit information is intended to allow the public to easily track the number and \n",
    "locations of oil and gas well permits issued. For more detailed information about a specific well, \n",
    "contact the Oil and Gas program staff at 615-687-7109 . Permit files can be accessed by visiting \n",
    "the Oil and Gas Section at the Nashville Environmental Field Office in Nashville, TN 37243.\"</i>\n",
    "\"\"\"\n",
    "state.source_url = 'http://environment-online.state.tn.us:8080/pls/enf_reports/f?p=9034:34300:0::NO:::'\n",
    "state.write_to_db()\n",
    "\n",
    "layout = {\n",
    "    'date_field': 'Permit Date',\n",
    "    'api_field': 'API No',\n",
    "    'type_field': 'Purpose af Well',\n",
    "    'status_field': 'Purpose af Well',\n",
    "    'lat_field': 'Latitude',\n",
    "    'lon_field': 'Longitude',\n",
    "    'description_layout': '\\n'.join(['County: %s',\n",
    "                             'Operator Name: %s',\n",
    "                             'Permit No: %s',\n",
    "                             'Well Name and Number: %s',\n",
    "                             'EFO Name: %s'\n",
    "                            ]),\n",
    "    'description_fields': ['County', 'Operator Name', 'Permit No', 'Well Name and Number', 'EFO Name'],\n",
    "    'type_map': {\n",
    "        'Gas': 'GAS',\n",
    "        'Mineral Core Test': 'TEST',\n",
    "        'NCG - Domestic Use': 'GAS',\n",
    "        'Oil': 'OIL',\n",
    "        'Oil And Gas': 'OILANDGAS',\n",
    "    },\n",
    "    'status_map': {\n",
    "        'Gas': 'ACTIVE',\n",
    "        'Mineral Core Test': 'UNKNOWN',\n",
    "        'NCG - Domestic Use': 'ACTIVE',\n",
    "        'Oil': 'ACTIVE',\n",
    "        'Oil And Gas': 'ACTIVE',\n",
    "    },\n",
    "}\n",
    "\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1462802 of 1517493 rows loaded\n",
      "first item: [{'state': 'TX', 'api': '42421114', 'lon': -102.12224025, 'lat': 36.45300986, 'date': '', 'type': 'UNKNOWN', 'status': 'PERMITTED', 'category': None, 'description': None}]\n",
      "loaded 1462802 of 1462802 records into the database\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1462802"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Texas\n",
    "# ug - scraped permits, only came away with 1m of 2m spud dates\n",
    "# and now for some reason I don't understand only half of Texas's wells are loading.\n",
    "state = fieldmaps.State('TX', prepend_api = True)\n",
    "state.projection = 4269 # NAD83\n",
    "layout = {\n",
    "    'api_field': 'API',\n",
    "    'date_field': ['Approved Date', 'Submitted Date'],\n",
    "    'lat_field': 'GIS_LAT83',\n",
    "    'lon_field': 'GIS_LONG83',\n",
    "    'status_field': 'GIS_SYMBOL_DESCRIPTION',\n",
    "    'status_map': {\n",
    "        'Brine Mining': 'ACTIVE',\n",
    "        'Brine Mining / Oil': 'ACTIVE',\n",
    "        'Brine Mining / Oil / Gas': 'ACTIVE',\n",
    "        'Canceled / Abandoned Location': 'CANCELLED',\n",
    "        'Core Test': 'ACTIVE',\n",
    "        'Dry Hole': 'DRY',\n",
    "        'Gas Well': 'ACTIVE',\n",
    "        'Geothermal Well': 'ACTIVE',\n",
    "        'Injection / Disposal': 'ACTIVE',\n",
    "        'Injection / Disposal from Gas': 'ACTIVE',\n",
    "        'Injection / Disposal from Oil': 'ACTIVE',\n",
    "        'Injection / Disposal from Oil/Gas': 'ACTIVE',\n",
    "        'Injection/Disposal from Brine Mining': 'ACTIVE',\n",
    "        'Injection/Disposal from Brine Mining / Gas': 'ACTIVE',\n",
    "        'Injection/Disposal from Storage': 'ACTIVE',\n",
    "        'Injection/Disposal from Storage/Brine Mining': 'ACTIVE',\n",
    "        'Injection/Disposal from Storage/Gas': 'ACTIVE',\n",
    "        'Injection/Disposal from Storage/Oil': 'ACTIVE',\n",
    "        'Observation Well': 'ACTIVE',\n",
    "        'Observation from Gas': 'ACTIVE',\n",
    "        'Observation from Oil': 'ACTIVE',\n",
    "        'Observation from Oil / Gas': 'ACTIVE',\n",
    "        'Observation from Storage': 'ACTIVE',\n",
    "        'Observation from Storage/Gas': 'ACTIVE',\n",
    "        'Observation from Storage/Oil': 'ACTIVE',\n",
    "        'Oil Well': 'ACTIVE',\n",
    "        'Oil/Gas Well': 'ACTIVE',\n",
    "        'Permitted Location': 'PERMITTED',\n",
    "        'Plugged Brine Mining': 'PA',\n",
    "        'Plugged Gas Well': 'PA',\n",
    "        'Plugged Oil / Gas': 'PA',\n",
    "        'Plugged Oil Well': 'PA',\n",
    "        'Plugged Storage': 'PA',\n",
    "        'Plugged Storage/Brine Mining': 'PA',\n",
    "        'Plugged Storage/Gas': 'PA',\n",
    "        'Plugged Storage/Oil': 'PA',\n",
    "        'Service': 'ACTIVE',\n",
    "        'Service from Brine Mining': 'ACTIVE',\n",
    "        'Service from Oil': 'ACTIVE',\n",
    "        'Service from Oil / Gas': 'ACTIVE',\n",
    "        'Service from Storage/Gas': 'ACTIVE',\n",
    "        'Shut-In Gas': 'SI',\n",
    "        'Shut-In Oil': 'SI',\n",
    "        'Storage Well': 'ACTIVE',\n",
    "        'Storage from Gas': 'ACTIVE',\n",
    "        'Storage from Oil': 'ACTIVE',\n",
    "        'Storage from Oil/Gas': 'ACTIVE',\n",
    "        'Storage/Brine Mining': 'ACTIVE',\n",
    "        'Sulfur Core Test': 'ACTIVE',\n",
    "        'Water Supply Well': 'ACTIVE',\n",
    "        'Water Supply from Gas': 'ACTIVE',\n",
    "        'Water Supply from Oil': 'ACTIVE',\n",
    "        'Water Supply from Oil/Gas': 'ACTIVE',\n",
    "    },\n",
    "    'type_field': 'GIS_SYMBOL_DESCRIPTION',\n",
    "    'type_map': {\n",
    "        'Brine Mining': 'OTHER',\n",
    "        'Brine Mining / Oil': 'OIL',\n",
    "        'Brine Mining / Oil / Gas': 'OILANDGAS',\n",
    "        'Canceled / Abandoned Location': 'UNKNOWN',\n",
    "        'Core Test': 'TEST',\n",
    "        'Dry Hole': 'DRY HOLE',\n",
    "        'Gas Well': 'GAS',\n",
    "        'Geothermal Well': 'OTHER',\n",
    "        'Injection / Disposal': 'DISPOSAL',\n",
    "        'Injection / Disposal from Gas': 'DISPOSAL',\n",
    "        'Injection / Disposal from Oil': 'DISPOSAL',\n",
    "        'Injection / Disposal from Oil/Gas': 'DISPOSAL',\n",
    "        'Injection/Disposal from Brine Mining': 'DISPOSAL',\n",
    "        'Injection/Disposal from Brine Mining / Gas': 'DISPOSAL',\n",
    "        'Injection/Disposal from Storage': 'DISPOSAL',\n",
    "        'Injection/Disposal from Storage/Brine Mining': 'DISPOSAL',\n",
    "        'Injection/Disposal from Storage/Gas': 'DISPOSAL',\n",
    "        'Injection/Disposal from Storage/Oil': 'DISPOSAL',\n",
    "        'Observation Well': 'OBSERVATION',\n",
    "        'Observation from Gas': 'OBSERVATION',\n",
    "        'Observation from Oil': 'OBSERVATION',\n",
    "        'Observation from Oil / Gas': 'OBSERVATION',\n",
    "        'Observation from Storage': 'OBSERVATION',\n",
    "        'Observation from Storage/Gas': 'OBSERVATION',\n",
    "        'Observation from Storage/Oil': 'OBSERVATION',\n",
    "        'Oil Well': 'OIL',\n",
    "        'Oil/Gas Well': 'OILANDGAS',\n",
    "        'Permitted Location': 'UNKNOWN',\n",
    "        'Plugged Brine Mining': 'UNKNOWN',\n",
    "        'Plugged Gas Well': 'GAS',\n",
    "        'Plugged Oil / Gas': 'OILANDGAS',\n",
    "        'Plugged Oil Well': 'OIL',\n",
    "        'Plugged Storage': 'STORAGE',\n",
    "        'Plugged Storage/Brine Mining': 'STORAGE',\n",
    "        'Plugged Storage/Gas': 'GAS',\n",
    "        'Plugged Storage/Oil': 'OIL',\n",
    "        'Service': 'SERVICE',\n",
    "        'Service from Brine Mining': 'SERVICE',\n",
    "        'Service from Oil': 'SERVICE',\n",
    "        'Service from Oil / Gas': 'SERVICE',\n",
    "        'Service from Storage/Gas': 'SERVICE',\n",
    "        'Shut-In Gas': 'GAS',\n",
    "        'Shut-In Oil': 'OIL',\n",
    "        'Storage Well': 'STORAGE',\n",
    "        'Storage from Gas': 'STORAGE',\n",
    "        'Storage from Oil': 'STORAGE',\n",
    "        'Storage from Oil/Gas': 'STORAGE',\n",
    "        'Storage/Brine Mining': 'STORAGE',\n",
    "        'Sulfur Core Test': 'TEST',\n",
    "        'Water Supply Well': 'WATER',\n",
    "        'Water Supply from Gas': 'WATER',\n",
    "        'Water Supply from Oil': 'WATER',\n",
    "        'Water Supply from Oil/Gas': 'WATER',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "importlib.reload(fieldmaps)\n",
    "\n",
    "layout = {\n",
    "    'api_field': '',\n",
    "    'date_field': '',\n",
    "    'lat_field': '',\n",
    "    'lon_field': '',\n",
    "    'status_field': '',\n",
    "    'status_map': None,\n",
    "    'type_field': '',\n",
    "    'type_map': None\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Utah\n",
    "state = fieldmaps.State('UT')\n",
    "# type and status codes: https://gis.utah.gov/data/energy/oil-gas/\n",
    "layout = {\n",
    "   'date_field': 'OrigComplD',\n",
    "    'api_field': 'API',\n",
    "    'type_field': 'WellType',\n",
    "    'status_field': 'WellStatus',\n",
    "    'lat_field': 'Latitude',\n",
    "    'lon_field': 'Longitude',\n",
    "    'status_map': {\n",
    "        'A': 'ACTIVE',\n",
    "        'APD': 'PERMITTED',\n",
    "        'DRL': 'ACTIVE',\n",
    "        'I': 'INACTIVE',\n",
    "        'LA': 'A',\n",
    "        'NEW': 'UNKNOWN',\n",
    "        'OPS': 'UNKNOWN',\n",
    "        'P': 'ACTIVE',\n",
    "        'PA': 'PA',\n",
    "        'RET': 'CANCELLED',\n",
    "        'S': 'SI',\n",
    "        'SAI': 'SI',\n",
    "        'TA': 'TA',\n",
    "    },\n",
    "    'type_map': {\n",
    "        'CD': 'UNKNOWN',\n",
    "        'D': 'DRY HOLE',\n",
    "        'GI': 'INJECTION',\n",
    "        'GS': 'STORAGE',\n",
    "        'GW': 'GAS',\n",
    "        'HE': 'OTHER',\n",
    "        'NA': 'UNKNOWN',\n",
    "        'OW': 'OIL',\n",
    "        'TW': 'TEST',\n",
    "        'WD': 'DISPOSAL',\n",
    "        'WI': 'INJECTION',\n",
    "        'WS': 'WATER',\n",
    "        'tw': 'TEST',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Vermont - no oil or gas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Virginia\n",
    "state = fieldmaps.State('VA', prepend_api = True, webdate = True)\n",
    "\n",
    "layout = {\n",
    "    'api_field': 'FiNo',\n",
    "    'date_field': ['FiWellComp', 'FiStatDt'],\n",
    "    'lat_field': 'Y',\n",
    "    'lon_field': 'X',\n",
    "    'status_field': 'TblPsDesc',\n",
    "    'status_map': {\n",
    "        'Completed': 'ACTIVE',\n",
    "        'Drilled/Waiting Completion/PL': 'ACTIVE',\n",
    "        'Drilling': 'ACTIVE',\n",
    "        'Not Connected': 'UNKNOWN',\n",
    "        'Orphaned Well': 'ORPHAN',\n",
    "        'Other': 'UNKNOWN',\n",
    "        'Partial Plug': 'A',\n",
    "        'Plugged/Abandoned': 'PA',\n",
    "        'Plugging': 'PA',\n",
    "        'Producing': 'ACTIVE',\n",
    "        'Released': 'UNKNOWN',\n",
    "        'STABILIZED/OTHER': 'UNKNOWN',\n",
    "        'STABILIZED/PRODUCING': 'ACTIVE',\n",
    "        'Shut In': 'SI',\n",
    "    },\n",
    "    'type_field': 'TblOpDesc',\n",
    "    'type_map': {\n",
    "        'Coal Bed': 'GAS',\n",
    "        'Coalbed/Pipeline': 'UNKNOWN',\n",
    "        'Conversion': 'UNKNOWN',\n",
    "        'Conversion to Water Well': 'WATER',\n",
    "        'Conversion/Pipeline': 'UNKNOWN',\n",
    "        'Exploratory': 'TEST',\n",
    "        'Gas': 'GAS',\n",
    "        'Gas/CB Dual Completion': 'GAS',\n",
    "        'Gas/Coalbed with Pipeline': 'GAS',\n",
    "        'Gas/Pipeline': 'GAS',\n",
    "        'Geophysical': 'UNKNOWN',\n",
    "        'Horizontal Coalbed': 'GAS',\n",
    "        'Horizontal Coalbed W/PL': 'GAS',\n",
    "        'Horizontal Gas': 'GAS',\n",
    "        'Horizontal Gas W/PL': 'GAS',\n",
    "        'Oil': 'OIL',\n",
    "        'Service Well': 'SERVICE',\n",
    "        'Underground Storage': 'STORAGE',\n",
    "        'Waste Disposal': 'DISPOSAL',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Washington\n",
    "state = fieldmaps.State('WA', webdate = True)\n",
    "\n",
    "layout = {\n",
    "    'api_field': 'API_NUMBER',\n",
    "    'date_field': ['DRILL_START_DATE', 'PERMIT_DATE'],\n",
    "    'lat_field': 'LATITUDE_DECIMAL_DEGREES',\n",
    "    'lon_field': 'LONGITUDE_DECIMAL_DEGREES',\n",
    "    'status_field': 'WELL_STATUS',\n",
    "    'status_map': {\n",
    "        'Completed': 'ACTIVE',\n",
    "        'Completed, plugged, and abandoned': 'PA',\n",
    "        'Converted to water well': 'ACTIVE',\n",
    "        'Drilled': 'ACTIVE',\n",
    "        'Plugged and abandoned': 'PA',\n",
    "    },\n",
    "    'type_field': 'HYDROCARBON_SHOW',\n",
    "    'type_map': {\n",
    "        'gas': 'GAS',\n",
    "        'gas, oil': 'OILANDGAS',\n",
    "        'oil': 'OIL',\n",
    "    },\n",
    "\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# West Virginia\n",
    "state = fieldmaps.State('WV', webdate = True)\n",
    "\n",
    "layout = {\n",
    "    'api_field': 'api',\n",
    "    'date_field': ['CompDate','IssueDate'],\n",
    "    'lat_field': 'y',\n",
    "    'lon_field': 'x',\n",
    "    'status_field': 'WellStatus',\n",
    "    'status_map': {\n",
    "        'Abandoned Well': 'A',\n",
    "        'Active Well': 'ACTIVE',\n",
    "        'Drilling': 'ACTIVE',\n",
    "        'Future Use': 'UNKNOWN',\n",
    "        'Never Drilled': 'CANCELLED',\n",
    "        'Never Issued': 'CANCELLED',\n",
    "        'Permit Application': 'UNKNOWN',\n",
    "        'Permit Issued': 'PERMITTED',\n",
    "        'Plugged': 'PA',\n",
    "        'Shutin': 'SI',\n",
    "    },\n",
    "    'type_field': 'WellUse',\n",
    "    'type_map': {\n",
    "        'Brine Disposal': 'DISPOSAL',\n",
    "        'CBM - Articulate': 'GAS',\n",
    "        'EOR Production Well': 'UNKNOWN',\n",
    "        'Fluid Injection': 'INJECTION',\n",
    "        'Gas Production': 'GAS',\n",
    "        'House Gas ': 'GAS',\n",
    "        'Not Available': 'UNKNOWN',\n",
    "        'Observation': 'OBSERVATION',\n",
    "        'Oil Production': 'OIL',\n",
    "        'Production': 'OIL', # NOT SURE ABOUT THIS ONE\n",
    "        'Solution Mining': 'UNKNOWN',\n",
    "        'Storage': 'STORAGE',\n",
    "        'Unknown': 'UNKNOWN',\n",
    "        'Vent': 'UNKNOWN',\n",
    "    },\n",
    "}\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Wisconsin - no oil or gas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Wyoming\n",
    "state = fieldmaps.State('WY')\n",
    "# codes: http://wsgs.maps.arcgis.com/apps/webappviewer/index.html?id=3f7ab99343c34bd3ac5ae6ac8c04d95a\n",
    "layout = {\n",
    "    'api_field': 'APINO',\n",
    "    'date_field': ['FIRSTSPUD', 'FIRSTCOMP'],\n",
    "    'lat_field': 'LAT',\n",
    "    'lon_field': 'LON',\n",
    "    'status_field': 'STATUS',\n",
    "    'status_map': {\n",
    "        'AI': 'ACTIVE',\n",
    "        'AP': 'PERMITTED',\n",
    "        'D': 'ACTIVE',\n",
    "        'DH': 'DRY',\n",
    "        'DP': 'ACTIVE',\n",
    "        'DR': 'ACTIVE',\n",
    "        'FL': 'ACTIVE',\n",
    "        'GL': 'ACTIVE',\n",
    "        'GS': 'ACTIVE',\n",
    "        'M': 'ACTIVE',\n",
    "        'MW': 'ACTIVE',\n",
    "        'ND': 'CANCELLED',\n",
    "        'NI': 'A',\n",
    "        'NR': 'UNKNOWN',\n",
    "        'PA': 'PA',\n",
    "        'PG': 'ORPHAN',\n",
    "        'PH': 'ACTIVE',\n",
    "        'PL': 'ACTIVE',\n",
    "        'PO': 'ACTIVE',\n",
    "        'PR': 'ACTIVE',\n",
    "        'PS': 'ACTIVE',\n",
    "        'SI': 'SI',\n",
    "        'SO': 'SI',\n",
    "        'SP': 'SI',\n",
    "        'SR': 'SI',\n",
    "        'TA': 'TA',\n",
    "        'WP': 'UNKNOWN',\n",
    "        'si': 'SI',\n",
    "    },\n",
    "    'type_field': 'WELL_CLASS',\n",
    "    'type_map': {\n",
    "        '01': 'DISPOSAL',\n",
    "        '05': 'DISPOSAL',\n",
    "        '1': 'DISPOSAL',\n",
    "        'CB': 'GAS',\n",
    "        'D': 'DISPOSAL',\n",
    "        'DO': 'DISPOSAL',\n",
    "        'G': 'GAS',\n",
    "        'GO': 'GAS',\n",
    "        'GS': 'STORAGE',\n",
    "        'I': 'INJECTION',\n",
    "        'IO': 'INJECTION',\n",
    "        'LW': 'WATER',\n",
    "        'M': 'OBSERVATION',\n",
    "        'MO': 'OBSERVATION',\n",
    "        'MW': 'OBSERVATION',\n",
    "        'NA': 'UNKNOWN',\n",
    "        'O': 'OIL',\n",
    "        'OO': 'OIL',\n",
    "        'S': 'WATER',\n",
    "        'ST': 'TEST',\n",
    "        'WS': 'WATER',\n",
    "    },\n",
    "}\n",
    "#import fieldmaps.ks\n",
    "wells = fieldmaps.Dataset(state, layout)\n",
    "wells.write_to_db()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Alaska Offshore\n",
    "state = State('AK1')\n",
    "# Pacific Coast Offshore\n",
    "state = State('CA1')\n",
    "# Northern Gulf of Mexico\n",
    "state = State('TX1')\n",
    "# Atlantic Coast Offshore\n",
    "state = State('DC1')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}