WV Production _version3 (Fall 2019).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# West Virginia Production dataset - WVGES version\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Production data from WVGES"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "See website <http://www.wvgs.wvnet.edu/www/datastat/devshales.htm>  for lots of resources.  This includes\n",
    "an excel sheet with lots of metadata on each unconventional well. We use that here to filter out conventional wells and for lat/lon, as well as monthly production.\n",
    "Note that the main data file (WVGES Marcellus Wells.xlsx) appears to only include whole years.  The current year (2019) is not included.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Steps to update data\n",
    "\n",
    "- Start by making a copy of this jupyter notebook and make changes to the copy (and keep original separate).\n",
    "- You only need one file from the <WVGES web site>(http://www.wvgs.wvnet.edu/www/datastat/devshales.htm).  Download the \"WVGES Marcellus Wells.xlsx\" file.  Because the file is **read-only**, you will have to open it with a program like Excel or OpenOffice and save it as another name. (For me, I use OpenOffice so I cannot save it in the same xlsx format, but xls works too.)\n",
    "- Move that copy into the /sources directory.\n",
    "- in the Imports section of code below, change the name of the 'inputdatafn' file to match the file you just moved.\n",
    "- in the Make Output section of the below, change the suffix of the output filename to reflect the current date.\n",
    "- That should be it: run this notebook and the output files will be in the out directory. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# python/pandas preamble\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import zipfile, os\n",
    "\n",
    "datadir = './sources/'\n",
    "outdir = './out/'\n",
    "inputdatafn = datadir+'Copy of WVGES Marcellus Wells.xls'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "klst = ['API','YEAR','G_JAN','G_FEB','G_MAR','G_APR','G_MAY','G_JUN','G_JUL','G_AUG','G_OCT','G_NOV','G_DEC',\n",
    "        'O_JAN','O_FEB','O_MAR','O_APR','O_MAY','O_JUN','O_JUL','O_AUG','O_OCT','O_NOV','O_DEC',\n",
    "        'NGL_JAN','NGL_FEB','NGL_MAR','NGL_APR','NGL_MAY','NGL_JUN','NGL_JUL','NGL_AUG','NGL_OCT','NGL_NOV','NGL_DEC',]\n",
    "prod = pd.read_excel(inputdatafn,sheet_name='WV Marcellus Prod',\n",
    "                     usecols=klst, skiprows=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The dataset has some records back to 1979 for working wells that are fracked in the early 2000's.  Presumably, the back records are included to demonstrate the difference between conventional and non-conventional.   I am leaving off years before 2002 (the earliest spud date) to keep the dataset from being swamped with empty cells."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>API</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>G_JAN</th>\n",
       "      <th>G_FEB</th>\n",
       "      <th>G_MAR</th>\n",
       "      <th>G_APR</th>\n",
       "      <th>G_MAY</th>\n",
       "      <th>G_JUN</th>\n",
       "      <th>G_JUL</th>\n",
       "      <th>G_AUG</th>\n",
       "      <th>...</th>\n",
       "      <th>NGL_FEB</th>\n",
       "      <th>NGL_MAR</th>\n",
       "      <th>NGL_APR</th>\n",
       "      <th>NGL_MAY</th>\n",
       "      <th>NGL_JUN</th>\n",
       "      <th>NGL_JUL</th>\n",
       "      <th>NGL_AUG</th>\n",
       "      <th>NGL_OCT</th>\n",
       "      <th>NGL_NOV</th>\n",
       "      <th>NGL_DEC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4700102885</td>\n",
       "      <td>2008</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>956.0</td>\n",
       "      <td>1082.0</td>\n",
       "      <td>1397.0</td>\n",
       "      <td>1158.0</td>\n",
       "      <td>1179.0</td>\n",
       "      <td>1013.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4700102885</td>\n",
       "      <td>2009</td>\n",
       "      <td>758.0</td>\n",
       "      <td>1104.0</td>\n",
       "      <td>774.0</td>\n",
       "      <td>881.0</td>\n",
       "      <td>437.0</td>\n",
       "      <td>711.0</td>\n",
       "      <td>408.0</td>\n",
       "      <td>346.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4700102885</td>\n",
       "      <td>2010</td>\n",
       "      <td>1797.0</td>\n",
       "      <td>1385.0</td>\n",
       "      <td>1270.0</td>\n",
       "      <td>1024.0</td>\n",
       "      <td>899.0</td>\n",
       "      <td>1170.0</td>\n",
       "      <td>952.0</td>\n",
       "      <td>747.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4700102885</td>\n",
       "      <td>2011</td>\n",
       "      <td>748.0</td>\n",
       "      <td>717.0</td>\n",
       "      <td>617.0</td>\n",
       "      <td>590.0</td>\n",
       "      <td>590.0</td>\n",
       "      <td>689.0</td>\n",
       "      <td>636.0</td>\n",
       "      <td>512.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4700102885</td>\n",
       "      <td>2012</td>\n",
       "      <td>589.0</td>\n",
       "      <td>636.0</td>\n",
       "      <td>719.0</td>\n",
       "      <td>602.0</td>\n",
       "      <td>677.0</td>\n",
       "      <td>433.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>259.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          API  YEAR   G_JAN   G_FEB   G_MAR   G_APR   G_MAY   G_JUN   G_JUL  \\\n",
       "0  4700102885  2008     0.0     0.0   956.0  1082.0  1397.0  1158.0  1179.0   \n",
       "1  4700102885  2009   758.0  1104.0   774.0   881.0   437.0   711.0   408.0   \n",
       "2  4700102885  2010  1797.0  1385.0  1270.0  1024.0   899.0  1170.0   952.0   \n",
       "3  4700102885  2011   748.0   717.0   617.0   590.0   590.0   689.0   636.0   \n",
       "4  4700102885  2012   589.0   636.0   719.0   602.0   677.0   433.0   583.0   \n",
       "\n",
       "    G_AUG  ...  NGL_FEB  NGL_MAR  NGL_APR  NGL_MAY  NGL_JUN  NGL_JUL  NGL_AUG  \\\n",
       "0  1013.0  ...      NaN      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "1   346.0  ...      NaN      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "2   747.0  ...      NaN      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "3   512.0  ...      NaN      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "4   259.0  ...      NaN      NaN      NaN      NaN      NaN      NaN      NaN   \n",
       "\n",
       "   NGL_OCT  NGL_NOV  NGL_DEC  \n",
       "0      NaN      NaN      NaN  \n",
       "1      NaN      NaN      NaN  \n",
       "2      NaN      NaN      NaN  \n",
       "3      NaN      NaN      NaN  \n",
       "4      NaN      NaN      NaN  \n",
       "\n",
       "[5 rows x 35 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prod = prod[prod.YEAR>2001]\n",
    "prod.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>API</th>\n",
       "      <th>G_JAN</th>\n",
       "      <th>G_FEB</th>\n",
       "      <th>G_MAR</th>\n",
       "      <th>G_APR</th>\n",
       "      <th>G_MAY</th>\n",
       "      <th>G_JUN</th>\n",
       "      <th>G_JUL</th>\n",
       "      <th>G_AUG</th>\n",
       "      <th>G_OCT</th>\n",
       "      <th>...</th>\n",
       "      <th>NGL_FEB</th>\n",
       "      <th>NGL_MAR</th>\n",
       "      <th>NGL_APR</th>\n",
       "      <th>NGL_MAY</th>\n",
       "      <th>NGL_JUN</th>\n",
       "      <th>NGL_JUL</th>\n",
       "      <th>NGL_AUG</th>\n",
       "      <th>NGL_OCT</th>\n",
       "      <th>NGL_NOV</th>\n",
       "      <th>NGL_DEC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YEAR</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2002</th>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005</th>\n",
       "      <td>126</td>\n",
       "      <td>126</td>\n",
       "      <td>126</td>\n",
       "      <td>126</td>\n",
       "      <td>126</td>\n",
       "      <td>126</td>\n",
       "      <td>126</td>\n",
       "      <td>126</td>\n",
       "      <td>126</td>\n",
       "      <td>126</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006</th>\n",
       "      <td>380</td>\n",
       "      <td>380</td>\n",
       "      <td>380</td>\n",
       "      <td>380</td>\n",
       "      <td>380</td>\n",
       "      <td>380</td>\n",
       "      <td>380</td>\n",
       "      <td>380</td>\n",
       "      <td>380</td>\n",
       "      <td>380</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007</th>\n",
       "      <td>734</td>\n",
       "      <td>734</td>\n",
       "      <td>734</td>\n",
       "      <td>734</td>\n",
       "      <td>734</td>\n",
       "      <td>734</td>\n",
       "      <td>734</td>\n",
       "      <td>734</td>\n",
       "      <td>734</td>\n",
       "      <td>734</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>1218</td>\n",
       "      <td>1218</td>\n",
       "      <td>1218</td>\n",
       "      <td>1218</td>\n",
       "      <td>1218</td>\n",
       "      <td>1218</td>\n",
       "      <td>1218</td>\n",
       "      <td>1218</td>\n",
       "      <td>1218</td>\n",
       "      <td>1218</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>1378</td>\n",
       "      <td>1378</td>\n",
       "      <td>1378</td>\n",
       "      <td>1378</td>\n",
       "      <td>1378</td>\n",
       "      <td>1378</td>\n",
       "      <td>1378</td>\n",
       "      <td>1378</td>\n",
       "      <td>1378</td>\n",
       "      <td>1378</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>1563</td>\n",
       "      <td>1563</td>\n",
       "      <td>1563</td>\n",
       "      <td>1563</td>\n",
       "      <td>1563</td>\n",
       "      <td>1563</td>\n",
       "      <td>1563</td>\n",
       "      <td>1563</td>\n",
       "      <td>1563</td>\n",
       "      <td>1563</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>1802</td>\n",
       "      <td>1802</td>\n",
       "      <td>1802</td>\n",
       "      <td>1802</td>\n",
       "      <td>1802</td>\n",
       "      <td>1802</td>\n",
       "      <td>1802</td>\n",
       "      <td>1802</td>\n",
       "      <td>1802</td>\n",
       "      <td>1802</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>2087</td>\n",
       "      <td>2087</td>\n",
       "      <td>2087</td>\n",
       "      <td>2087</td>\n",
       "      <td>2087</td>\n",
       "      <td>2087</td>\n",
       "      <td>2087</td>\n",
       "      <td>2087</td>\n",
       "      <td>2087</td>\n",
       "      <td>2087</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>...</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "      <td>2476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>...</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "      <td>2895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>...</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "      <td>3069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>3309</td>\n",
       "      <td>3309</td>\n",
       "      <td>3309</td>\n",
       "      <td>3309</td>\n",
       "      <td>3309</td>\n",
       "      <td>3309</td>\n",
       "      <td>3309</td>\n",
       "      <td>3309</td>\n",
       "      <td>3309</td>\n",
       "      <td>3309</td>\n",
       "      <td>...</td>\n",
       "      <td>1026</td>\n",
       "      <td>1026</td>\n",
       "      <td>1026</td>\n",
       "      <td>1026</td>\n",
       "      <td>1026</td>\n",
       "      <td>1026</td>\n",
       "      <td>1026</td>\n",
       "      <td>1026</td>\n",
       "      <td>1026</td>\n",
       "      <td>1026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>3652</td>\n",
       "      <td>3652</td>\n",
       "      <td>3652</td>\n",
       "      <td>3652</td>\n",
       "      <td>3652</td>\n",
       "      <td>3652</td>\n",
       "      <td>3652</td>\n",
       "      <td>3652</td>\n",
       "      <td>3652</td>\n",
       "      <td>3652</td>\n",
       "      <td>...</td>\n",
       "      <td>244</td>\n",
       "      <td>242</td>\n",
       "      <td>240</td>\n",
       "      <td>267</td>\n",
       "      <td>257</td>\n",
       "      <td>275</td>\n",
       "      <td>348</td>\n",
       "      <td>380</td>\n",
       "      <td>388</td>\n",
       "      <td>393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>...</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "      <td>3918</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       API  G_JAN  G_FEB  G_MAR  G_APR  G_MAY  G_JUN  G_JUL  G_AUG  G_OCT  \\\n",
       "YEAR                                                                        \n",
       "2002    53     53     53     53     53     53     53     53     53     53   \n",
       "2003    55     55     55     55     55     55     55     55     55     55   \n",
       "2004    58     58     58     58     58     58     58     58     58     58   \n",
       "2005   126    126    126    126    126    126    126    126    126    126   \n",
       "2006   380    380    380    380    380    380    380    380    380    380   \n",
       "2007   734    734    734    734    734    734    734    734    734    734   \n",
       "2008  1218   1218   1218   1218   1218   1218   1218   1218   1218   1218   \n",
       "2009  1378   1378   1378   1378   1378   1378   1378   1378   1378   1378   \n",
       "2010  1563   1563   1563   1563   1563   1563   1563   1563   1563   1563   \n",
       "2011  1802   1802   1802   1802   1802   1802   1802   1802   1802   1802   \n",
       "2012  2087   2087   2087   2087   2087   2087   2087   2087   2087   2087   \n",
       "2013  2476   2476   2476   2476   2476   2476   2476   2476   2476   2476   \n",
       "2014  2895   2895   2895   2895   2895   2895   2895   2895   2895   2895   \n",
       "2015  3069   3069   3069   3069   3069   3069   3069   3069   3069   3069   \n",
       "2016  3309   3309   3309   3309   3309   3309   3309   3309   3309   3309   \n",
       "2017  3652   3652   3652   3652   3652   3652   3652   3652   3652   3652   \n",
       "2018  3918   3918   3918   3918   3918   3918   3918   3918   3918   3918   \n",
       "\n",
       "      ...  NGL_FEB  NGL_MAR  NGL_APR  NGL_MAY  NGL_JUN  NGL_JUL  NGL_AUG  \\\n",
       "YEAR  ...                                                                  \n",
       "2002  ...        0        0        0        0        0        0        0   \n",
       "2003  ...        0        0        0        0        0        0        0   \n",
       "2004  ...        0        0        0        0        0        0        0   \n",
       "2005  ...        0        0        0        0        0        0        0   \n",
       "2006  ...        0        0        0        0        0        0        0   \n",
       "2007  ...        0        0        0        0        0        0        0   \n",
       "2008  ...        0        0        0        0        0        0        0   \n",
       "2009  ...        0        0        0        0        0        0        0   \n",
       "2010  ...        0        0        0        0        0        0        0   \n",
       "2011  ...        0        0        0        0        0        0        0   \n",
       "2012  ...        0        0        0        0        0        0        0   \n",
       "2013  ...     2476     2476     2476     2476     2476     2476     2476   \n",
       "2014  ...     2895     2895     2895     2895     2895     2895     2895   \n",
       "2015  ...     3069     3069     3069     3069     3069     3069     3069   \n",
       "2016  ...     1026     1026     1026     1026     1026     1026     1026   \n",
       "2017  ...      244      242      240      267      257      275      348   \n",
       "2018  ...     3918     3918     3918     3918     3918     3918     3918   \n",
       "\n",
       "      NGL_OCT  NGL_NOV  NGL_DEC  \n",
       "YEAR                             \n",
       "2002        0        0        0  \n",
       "2003        0        0        0  \n",
       "2004        0        0        0  \n",
       "2005        0        0        0  \n",
       "2006        0        0        0  \n",
       "2007        0        0        0  \n",
       "2008        0        0        0  \n",
       "2009        0        0        0  \n",
       "2010        0        0        0  \n",
       "2011        0        0        0  \n",
       "2012        0        0        0  \n",
       "2013     2476     2476     2476  \n",
       "2014     2895     2895     2895  \n",
       "2015     3069     3069     3069  \n",
       "2016     1026     1026     1026  \n",
       "2017      380      388      393  \n",
       "2018     3918     3918     3918  \n",
       "\n",
       "[17 rows x 34 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prod.groupby(\"YEAR\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>API</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "      <th>typ</th>\n",
       "      <th>yrmo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>941155</th>\n",
       "      <td>4710903065</td>\n",
       "      <td>2018</td>\n",
       "      <td>O_JUN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>OIL</td>\n",
       "      <td>Y2018M06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>941156</th>\n",
       "      <td>4710903065</td>\n",
       "      <td>2018</td>\n",
       "      <td>O_MAR</td>\n",
       "      <td>0.0</td>\n",
       "      <td>OIL</td>\n",
       "      <td>Y2018M03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>941157</th>\n",
       "      <td>4710903065</td>\n",
       "      <td>2018</td>\n",
       "      <td>O_MAY</td>\n",
       "      <td>0.0</td>\n",
       "      <td>OIL</td>\n",
       "      <td>Y2018M05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>941158</th>\n",
       "      <td>4710903065</td>\n",
       "      <td>2018</td>\n",
       "      <td>O_NOV</td>\n",
       "      <td>0.0</td>\n",
       "      <td>OIL</td>\n",
       "      <td>Y2018M11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>941159</th>\n",
       "      <td>4710903065</td>\n",
       "      <td>2018</td>\n",
       "      <td>O_OCT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>OIL</td>\n",
       "      <td>Y2018M10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               API  YEAR variable  value  typ      yrmo\n",
       "941155  4710903065  2018    O_JUN    0.0  OIL  Y2018M06\n",
       "941156  4710903065  2018    O_MAR    0.0  OIL  Y2018M03\n",
       "941157  4710903065  2018    O_MAY    0.0  OIL  Y2018M05\n",
       "941158  4710903065  2018    O_NOV    0.0  OIL  Y2018M11\n",
       "941159  4710903065  2018    O_OCT    0.0  OIL  Y2018M10"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xlate = {'JAN':'01', 'FEB':'02', 'MAR':'03', 'APR':'04', 'MAY':'05', 'JUN':'06',\n",
    "         'JUL':'07', 'AUG':'08', 'SEP':'09', 'OCT':'10', 'NOV':'11', 'DEC':'12'}\n",
    "\n",
    "def getColName(row):\n",
    "    yr = str(row.YEAR)\n",
    "    mo = xlate[row.variable[-3:]]\n",
    "    return f'Y{yr}M{mo}'\n",
    "    \n",
    "\n",
    "mprod = prod.melt(id_vars=['API','YEAR'])\n",
    "mprod = mprod.groupby(['API','YEAR','variable'],as_index=False)['value'].max()\n",
    "\n",
    "mprod['typ'] = np.where(mprod.variable.str[0]=='G','GAS',np.nan)\n",
    "mprod.typ = np.where(mprod.variable.str[0]=='O','OIL',mprod.typ)\n",
    "mprod.typ = np.where(mprod.variable.str[0]=='N','NGL',mprod.typ)\n",
    "mprod['yrmo'] = mprod.apply(lambda x: getColName(x),axis=1)\n",
    "mprod.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#mprod.to_pickle(outdir+'ver2.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Metadata from WVGES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>API</th>\n",
       "      <th>CountyName</th>\n",
       "      <th>OperatorName</th>\n",
       "      <th>Surf_Long</th>\n",
       "      <th>Surf_Lat</th>\n",
       "      <th>Spud Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4700102885</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>Seneca-Upshur Petroleum Corp.</td>\n",
       "      <td>-79.973910</td>\n",
       "      <td>39.008158</td>\n",
       "      <td>8/13/2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4700102981</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>EXCO - North Coast Energy Eastern, Inc.</td>\n",
       "      <td>-80.108525</td>\n",
       "      <td>39.045433</td>\n",
       "      <td>8/30/2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4700102985</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>Dominion Exploration &amp; Production</td>\n",
       "      <td>-80.020647</td>\n",
       "      <td>39.204180</td>\n",
       "      <td>7/30/2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4700102994</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>Petroleum Development Corp.</td>\n",
       "      <td>-80.016268</td>\n",
       "      <td>39.185397</td>\n",
       "      <td>1/17/2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4700103013</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>Petroleum Development Corp.</td>\n",
       "      <td>-80.170851</td>\n",
       "      <td>39.232015</td>\n",
       "      <td>7/15/2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          API CountyName                             OperatorName  Surf_Long  \\\n",
       "0  4700102885    Barbour            Seneca-Upshur Petroleum Corp. -79.973910   \n",
       "1  4700102981    Barbour  EXCO - North Coast Energy Eastern, Inc. -80.108525   \n",
       "2  4700102985    Barbour        Dominion Exploration & Production -80.020647   \n",
       "3  4700102994    Barbour              Petroleum Development Corp. -80.016268   \n",
       "4  4700103013    Barbour              Petroleum Development Corp. -80.170851   \n",
       "\n",
       "    Surf_Lat  Spud Date  \n",
       "0  39.008158  8/13/2007  \n",
       "1  39.045433  8/30/2008  \n",
       "2  39.204180  7/30/2009  \n",
       "3  39.185397  1/17/2009  \n",
       "4  39.232015  7/15/2009  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "klst = ['API Number','County Name','Operator at Completion',\n",
    "        'Surface Loc Longitude','Surface Loc Latitude','Spud Date']\n",
    "meta = pd.read_excel(inputdatafn,sheet_name='Completed Marcellus Wells',\n",
    "                     usecols=klst, skiprows=1)\n",
    "meta.columns = ['API','CountyName','OperatorName',\n",
    "        'Surf_Long','Surf_Lat','Spud Date']\n",
    "\n",
    "meta.head()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Len meta: 4035, unique API: 4019\n"
     ]
    }
   ],
   "source": [
    "print(f'Len meta: {len(meta)}, unique API: {len(meta.API.unique())}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  Need to take most recent meta record:\n",
    "meta = meta.groupby('API',as_index=False).last()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Len meta: 4019, unique API: 4019\n"
     ]
    }
   ],
   "source": [
    "print(f'Len meta: {len(meta)}, unique API: {len(meta.API.unique())}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Make the output files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def makeFile(data,prodName):\n",
    "    mprod = data[data.typ==prodName].filter(['API','yrmo','value'],axis=1)\n",
    "    pivot = mprod.pivot(index='API',columns='yrmo')\n",
    "    pivot.columns = pivot.columns.get_level_values(1)\n",
    "    #pivot.head()\n",
    "    mg = pd.merge(meta,pivot,on='API',how='right',validate='1:1')\n",
    "    #mg.head()\n",
    "    mg.to_excel(outdir+'WV_'+prodName+'_production_through_Oct_2019.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "makeFile(mprod,'GAS')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "makeFile(mprod,'OIL')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "makeFile(mprod,'NGL')"
   ]
  }
 ],
 "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.7.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}