00-data-preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 00 Data Preparation\n",
    "author: SheltonXiao (Tong Xiao)    \n",
    "2023/2/12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from tqdm import trange\n",
    "from IPython.display import clear_output"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## import data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Time-series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_list = os.listdir(r\"data/raw/dataAllFields/\")\n",
    "injection = [each for each in file_list if \"Injection\" in each]\n",
    "production = [each for each in file_list if \"Production\" in each]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Injection Well Information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 33403/33403 [17:00<00:00, 32.74it/s]\n"
     ]
    }
   ],
   "source": [
    "info = pd.DataFrame()\n",
    "for i in trange(len(injection)):\n",
    "    each = injection[i]\n",
    "    sub_info = pd.read_excel(r\"data/raw/dataAllFields/\"+each,nrows=1,index_col=0,parse_dates=False).dropna(how = \"all\",axis = 0)\n",
    "    info = pd.concat([info,sub_info],axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    os.mkdir(r\"data/processed\")\n",
    "except:\n",
    "    pass\n",
    "info.to_csv(os.path.join(r\"data/processed\",\"well_injection_info.csv\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Injection Well Time-series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|█████████▉| 33403/33404 [18:32<00:00, 30.04it/s]\n"
     ]
    },
    {
     "ename": "PermissionError",
     "evalue": "[Errno 13] Permission denied: 'data/raw/dataAllFields/~$Well_Injection_API_05920869.xlsx'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mPermissionError\u001b[0m                           Traceback (most recent call last)",
      "\u001b[1;32me:\\Document\\github\\blended-learning\\AIEnergy\\01-data-pre-processing.ipynb Cell 10\u001b[0m in \u001b[0;36m<cell line: 2>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      <a href='vscode-notebook-cell:/e%3A/Document/github/blended-learning/AIEnergy/01-data-pre-processing.ipynb#X16sZmlsZQ%3D%3D?line=1'>2</a>\u001b[0m \u001b[39mfor\u001b[39;00m i \u001b[39min\u001b[39;00m trange(\u001b[39mlen\u001b[39m(injection)):\n\u001b[0;32m      <a href='vscode-notebook-cell:/e%3A/Document/github/blended-learning/AIEnergy/01-data-pre-processing.ipynb#X16sZmlsZQ%3D%3D?line=2'>3</a>\u001b[0m     each \u001b[39m=\u001b[39m injection[i]\n\u001b[1;32m----> <a href='vscode-notebook-cell:/e%3A/Document/github/blended-learning/AIEnergy/01-data-pre-processing.ipynb#X16sZmlsZQ%3D%3D?line=3'>4</a>\u001b[0m     data \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39;49mread_excel(\u001b[39mr\u001b[39;49m\u001b[39m\"\u001b[39;49m\u001b[39mdata/raw/dataAllFields/\u001b[39;49m\u001b[39m\"\u001b[39;49m\u001b[39m+\u001b[39;49meach,skiprows\u001b[39m=\u001b[39;49m[\u001b[39m0\u001b[39;49m,\u001b[39m1\u001b[39;49m,\u001b[39m2\u001b[39;49m],parse_dates\u001b[39m=\u001b[39;49m\u001b[39mTrue\u001b[39;49;00m)\u001b[39m.\u001b[39mdropna(how \u001b[39m=\u001b[39m \u001b[39m\"\u001b[39m\u001b[39mall\u001b[39m\u001b[39m\"\u001b[39m,axis \u001b[39m=\u001b[39m \u001b[39m1\u001b[39m)\n\u001b[0;32m      <a href='vscode-notebook-cell:/e%3A/Document/github/blended-learning/AIEnergy/01-data-pre-processing.ipynb#X16sZmlsZQ%3D%3D?line=4'>5</a>\u001b[0m     \u001b[39mif\u001b[39;00m \u001b[39mlen\u001b[39m(data) \u001b[39m<\u001b[39m \u001b[39m1\u001b[39m:\n\u001b[0;32m      <a href='vscode-notebook-cell:/e%3A/Document/github/blended-learning/AIEnergy/01-data-pre-processing.ipynb#X16sZmlsZQ%3D%3D?line=5'>6</a>\u001b[0m         \u001b[39mcontinue\u001b[39;00m\n",
      "File \u001b[1;32md:\\ProgramData\\Miniconda3\\envs\\gis\\lib\\site-packages\\pandas\\util\\_decorators.py:311\u001b[0m, in \u001b[0;36mdeprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    305\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mlen\u001b[39m(args) \u001b[39m>\u001b[39m num_allow_args:\n\u001b[0;32m    306\u001b[0m     warnings\u001b[39m.\u001b[39mwarn(\n\u001b[0;32m    307\u001b[0m         msg\u001b[39m.\u001b[39mformat(arguments\u001b[39m=\u001b[39marguments),\n\u001b[0;32m    308\u001b[0m         \u001b[39mFutureWarning\u001b[39;00m,\n\u001b[0;32m    309\u001b[0m         stacklevel\u001b[39m=\u001b[39mstacklevel,\n\u001b[0;32m    310\u001b[0m     )\n\u001b[1;32m--> 311\u001b[0m \u001b[39mreturn\u001b[39;00m func(\u001b[39m*\u001b[39;49margs, \u001b[39m*\u001b[39;49m\u001b[39m*\u001b[39;49mkwargs)\n",
      "File \u001b[1;32md:\\ProgramData\\Miniconda3\\envs\\gis\\lib\\site-packages\\pandas\\io\\excel\\_base.py:457\u001b[0m, in \u001b[0;36mread_excel\u001b[1;34m(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, decimal, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)\u001b[0m\n\u001b[0;32m    455\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39misinstance\u001b[39m(io, ExcelFile):\n\u001b[0;32m    456\u001b[0m     should_close \u001b[39m=\u001b[39m \u001b[39mTrue\u001b[39;00m\n\u001b[1;32m--> 457\u001b[0m     io \u001b[39m=\u001b[39m ExcelFile(io, storage_options\u001b[39m=\u001b[39;49mstorage_options, engine\u001b[39m=\u001b[39;49mengine)\n\u001b[0;32m    458\u001b[0m \u001b[39melif\u001b[39;00m engine \u001b[39mand\u001b[39;00m engine \u001b[39m!=\u001b[39m io\u001b[39m.\u001b[39mengine:\n\u001b[0;32m    459\u001b[0m     \u001b[39mraise\u001b[39;00m \u001b[39mValueError\u001b[39;00m(\n\u001b[0;32m    460\u001b[0m         \u001b[39m\"\u001b[39m\u001b[39mEngine should not be specified when passing \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[0;32m    461\u001b[0m         \u001b[39m\"\u001b[39m\u001b[39man ExcelFile - ExcelFile already has the engine set\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[0;32m    462\u001b[0m     )\n",
      "File \u001b[1;32md:\\ProgramData\\Miniconda3\\envs\\gis\\lib\\site-packages\\pandas\\io\\excel\\_base.py:1376\u001b[0m, in \u001b[0;36mExcelFile.__init__\u001b[1;34m(self, path_or_buffer, engine, storage_options)\u001b[0m\n\u001b[0;32m   1374\u001b[0m     ext \u001b[39m=\u001b[39m \u001b[39m\"\u001b[39m\u001b[39mxls\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[0;32m   1375\u001b[0m \u001b[39melse\u001b[39;00m:\n\u001b[1;32m-> 1376\u001b[0m     ext \u001b[39m=\u001b[39m inspect_excel_format(\n\u001b[0;32m   1377\u001b[0m         content_or_path\u001b[39m=\u001b[39;49mpath_or_buffer, storage_options\u001b[39m=\u001b[39;49mstorage_options\n\u001b[0;32m   1378\u001b[0m     )\n\u001b[0;32m   1379\u001b[0m     \u001b[39mif\u001b[39;00m ext \u001b[39mis\u001b[39;00m \u001b[39mNone\u001b[39;00m:\n\u001b[0;32m   1380\u001b[0m         \u001b[39mraise\u001b[39;00m \u001b[39mValueError\u001b[39;00m(\n\u001b[0;32m   1381\u001b[0m             \u001b[39m\"\u001b[39m\u001b[39mExcel file format cannot be determined, you must specify \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[0;32m   1382\u001b[0m             \u001b[39m\"\u001b[39m\u001b[39man engine manually.\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[0;32m   1383\u001b[0m         )\n",
      "File \u001b[1;32md:\\ProgramData\\Miniconda3\\envs\\gis\\lib\\site-packages\\pandas\\io\\excel\\_base.py:1250\u001b[0m, in \u001b[0;36minspect_excel_format\u001b[1;34m(content_or_path, storage_options)\u001b[0m\n\u001b[0;32m   1247\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39misinstance\u001b[39m(content_or_path, \u001b[39mbytes\u001b[39m):\n\u001b[0;32m   1248\u001b[0m     content_or_path \u001b[39m=\u001b[39m BytesIO(content_or_path)\n\u001b[1;32m-> 1250\u001b[0m \u001b[39mwith\u001b[39;00m get_handle(\n\u001b[0;32m   1251\u001b[0m     content_or_path, \u001b[39m\"\u001b[39;49m\u001b[39mrb\u001b[39;49m\u001b[39m\"\u001b[39;49m, storage_options\u001b[39m=\u001b[39;49mstorage_options, is_text\u001b[39m=\u001b[39;49m\u001b[39mFalse\u001b[39;49;00m\n\u001b[0;32m   1252\u001b[0m ) \u001b[39mas\u001b[39;00m handle:\n\u001b[0;32m   1253\u001b[0m     stream \u001b[39m=\u001b[39m handle\u001b[39m.\u001b[39mhandle\n\u001b[0;32m   1254\u001b[0m     stream\u001b[39m.\u001b[39mseek(\u001b[39m0\u001b[39m)\n",
      "File \u001b[1;32md:\\ProgramData\\Miniconda3\\envs\\gis\\lib\\site-packages\\pandas\\io\\common.py:795\u001b[0m, in \u001b[0;36mget_handle\u001b[1;34m(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)\u001b[0m\n\u001b[0;32m    786\u001b[0m         handle \u001b[39m=\u001b[39m \u001b[39mopen\u001b[39m(\n\u001b[0;32m    787\u001b[0m             handle,\n\u001b[0;32m    788\u001b[0m             ioargs\u001b[39m.\u001b[39mmode,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m    791\u001b[0m             newline\u001b[39m=\u001b[39m\u001b[39m\"\u001b[39m\u001b[39m\"\u001b[39m,\n\u001b[0;32m    792\u001b[0m         )\n\u001b[0;32m    793\u001b[0m     \u001b[39melse\u001b[39;00m:\n\u001b[0;32m    794\u001b[0m         \u001b[39m# Binary mode\u001b[39;00m\n\u001b[1;32m--> 795\u001b[0m         handle \u001b[39m=\u001b[39m \u001b[39mopen\u001b[39;49m(handle, ioargs\u001b[39m.\u001b[39;49mmode)\n\u001b[0;32m    796\u001b[0m     handles\u001b[39m.\u001b[39mappend(handle)\n\u001b[0;32m    798\u001b[0m \u001b[39m# Convert BytesIO or file objects passed with an encoding\u001b[39;00m\n",
      "\u001b[1;31mPermissionError\u001b[0m: [Errno 13] Permission denied: 'data/raw/dataAllFields/~$Well_Injection_API_05920869.xlsx'"
     ]
    }
   ],
   "source": [
    "ts = pd.DataFrame()\n",
    "for i in trange(len(injection)):\n",
    "    each = injection[i]\n",
    "    data = pd.read_excel(r\"data/raw/dataAllFields/\"+each,skiprows=[0,1,2],parse_dates=True).dropna(how = \"all\",axis = 1)\n",
    "    if len(data) < 1:\n",
    "        continue\n",
    "    data = data[[\"Total\" not in each for each in data[\"Injection Date\"]]]\n",
    "    data[\"Year\"] = [int(each.split(\"-\")[1]) for each in data[\"Injection Date\"]]\n",
    "    data[\"Month\"] = [each.split(\"-\")[0] for each in data[\"Injection Date\"]]\n",
    "    ts = pd.concat([ts,data],axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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 Number</th>\n",
       "      <th>Injection Date</th>\n",
       "      <th>Water or Steam Injected (bbl)</th>\n",
       "      <th>Days Well Injected</th>\n",
       "      <th>Surface Injection Pressure</th>\n",
       "      <th>Source of Water</th>\n",
       "      <th>Kind of Water</th>\n",
       "      <th>PWT Status</th>\n",
       "      <th>Well Type</th>\n",
       "      <th>Status</th>\n",
       "      <th>Pool Code</th>\n",
       "      <th>Reported Date</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Gas or Air Injected (Mcf)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3700007</td>\n",
       "      <td>Dec-1979</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>P</td>\n",
       "      <td>WF</td>\n",
       "      <td>5.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1979-12-01</td>\n",
       "      <td>1979</td>\n",
       "      <td>Dec</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3700007</td>\n",
       "      <td>Nov-1979</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>P</td>\n",
       "      <td>WF</td>\n",
       "      <td>5.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1979-11-01</td>\n",
       "      <td>1979</td>\n",
       "      <td>Nov</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3700007</td>\n",
       "      <td>Oct-1979</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>P</td>\n",
       "      <td>WF</td>\n",
       "      <td>5.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1979-10-01</td>\n",
       "      <td>1979</td>\n",
       "      <td>Oct</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3700007</td>\n",
       "      <td>Sep-1979</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>P</td>\n",
       "      <td>WF</td>\n",
       "      <td>5.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1979-09-01</td>\n",
       "      <td>1979</td>\n",
       "      <td>Sep</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3700007</td>\n",
       "      <td>Aug-1979</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>P</td>\n",
       "      <td>WF</td>\n",
       "      <td>5.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1979-08-01</td>\n",
       "      <td>1979</td>\n",
       "      <td>Aug</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   API Number Injection Date  Water or Steam Injected (bbl)  \\\n",
       "1     3700007       Dec-1979                            NaN   \n",
       "2     3700007       Nov-1979                            NaN   \n",
       "3     3700007       Oct-1979                            NaN   \n",
       "4     3700007       Sep-1979                            NaN   \n",
       "5     3700007       Aug-1979                            0.0   \n",
       "\n",
       "   Days Well Injected  Surface Injection Pressure Source of Water  \\\n",
       "1                 NaN                         NaN             NaN   \n",
       "2                 NaN                         NaN             NaN   \n",
       "3                 NaN                         NaN             NaN   \n",
       "4                 NaN                         NaN             NaN   \n",
       "5                 0.0                         0.0             1.0   \n",
       "\n",
       "  Kind of Water PWT Status Well Type  Status  Pool Code Reported Date  Year  \\\n",
       "1           NaN          P        WF     5.0       20.0    1979-12-01  1979   \n",
       "2           NaN          P        WF     5.0       20.0    1979-11-01  1979   \n",
       "3           NaN          P        WF     5.0       20.0    1979-10-01  1979   \n",
       "4           NaN          P        WF     5.0       20.0    1979-09-01  1979   \n",
       "5           1.0          P        WF     5.0       20.0    1979-08-01  1979   \n",
       "\n",
       "  Month  Gas or Air Injected (Mcf)  \n",
       "1   Dec                        NaN  \n",
       "2   Nov                        NaN  \n",
       "3   Oct                        NaN  \n",
       "4   Sep                        NaN  \n",
       "5   Aug                        NaN  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts.to_csv(os.path.join(r\"data/processed\",\"well_injection_data.csv\"),index = None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts[ts[\"Days Well Injected\"]>0].to_csv(os.path.join(r\"data/processed\",\"well_injection_data_nonzero.csv\"),index = None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Production Well Information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 33403/33403 [34:48<00:00, 16.00it/s]  \n"
     ]
    }
   ],
   "source": [
    "info_p = pd.DataFrame()\n",
    "for i in trange(len(production)):\n",
    "    each = production[i]\n",
    "    sub_info = pd.read_excel(r\"data/raw/dataAllFields/\"+each,nrows=1,index_col=0,parse_dates=False).dropna(how = \"all\",axis = 0).dropna(how = \"all\",axis = 1)\n",
    "    info_p = pd.concat([info_p,sub_info],axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>Operator Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Field Name</th>\n",
       "      <th>Lease Name</th>\n",
       "      <th>Well #</th>\n",
       "      <th>Area Name</th>\n",
       "      <th>Area Code</th>\n",
       "      <th>District #</th>\n",
       "      <th>Section</th>\n",
       "      <th>Township</th>\n",
       "      <th>Range</th>\n",
       "      <th>Base Meridian</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Unnamed: 15</th>\n",
       "      <th>Unnamed: 16</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>API #</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>2500001</th>\n",
       "      <td>Mike Barkett</td>\n",
       "      <td>Imperial</td>\n",
       "      <td>Any Field</td>\n",
       "      <td>Barkett</td>\n",
       "      <td>2</td>\n",
       "      <td>Any Area</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>17S</td>\n",
       "      <td>11E</td>\n",
       "      <td>SB</td>\n",
       "      <td>32.661605</td>\n",
       "      <td>-115.885164</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2500002</th>\n",
       "      <td>Clarence E. Harrison</td>\n",
       "      <td>Imperial</td>\n",
       "      <td>Any Field</td>\n",
       "      <td>Yuha</td>\n",
       "      <td>1</td>\n",
       "      <td>Any Area</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>17S</td>\n",
       "      <td>11E</td>\n",
       "      <td>SB</td>\n",
       "      <td>32.661568</td>\n",
       "      <td>-115.873528</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2500003</th>\n",
       "      <td>Petrodynamics Assoc.</td>\n",
       "      <td>Imperial</td>\n",
       "      <td>Any Field</td>\n",
       "      <td>Straw</td>\n",
       "      <td>1</td>\n",
       "      <td>Any Area</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>17S</td>\n",
       "      <td>10E</td>\n",
       "      <td>SB</td>\n",
       "      <td>32.703601</td>\n",
       "      <td>-115.936421</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2500004</th>\n",
       "      <td>Sardi Oil Co.</td>\n",
       "      <td>Imperial</td>\n",
       "      <td>Any Field</td>\n",
       "      <td>Biff</td>\n",
       "      <td>1</td>\n",
       "      <td>Any Area</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>12S</td>\n",
       "      <td>13E</td>\n",
       "      <td>SB</td>\n",
       "      <td>33.111987</td>\n",
       "      <td>-115.571866</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2500005</th>\n",
       "      <td>Ajax Oil and Development Co.</td>\n",
       "      <td>Imperial</td>\n",
       "      <td>Any Field</td>\n",
       "      <td>U.S.L. Phyllis</td>\n",
       "      <td>1</td>\n",
       "      <td>Any Area</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>13S</td>\n",
       "      <td>17E</td>\n",
       "      <td>SB</td>\n",
       "      <td>33.068021</td>\n",
       "      <td>-115.187679</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3714787</th>\n",
       "      <td>BreitBurn Operating LP</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>Santa Fe Springs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>486-G</td>\n",
       "      <td>Any Area</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>03S</td>\n",
       "      <td>11W</td>\n",
       "      <td>SB</td>\n",
       "      <td>33.942218</td>\n",
       "      <td>-118.065484</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3714788</th>\n",
       "      <td>Blanton, Gendron &amp; Cook</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>Santa Fe Springs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56</td>\n",
       "      <td>Any Area</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>03S</td>\n",
       "      <td>12W</td>\n",
       "      <td>SB</td>\n",
       "      <td>33.936671</td>\n",
       "      <td>-118.086564</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3714789</th>\n",
       "      <td>Block Oil Co.</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>Santa Fe Springs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>Any Area</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>03S</td>\n",
       "      <td>11W</td>\n",
       "      <td>SB</td>\n",
       "      <td>33.940659</td>\n",
       "      <td>-118.070689</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3714790</th>\n",
       "      <td>Harry Briggs</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>Santa Fe Springs</td>\n",
       "      <td>Dallugge</td>\n",
       "      <td>9</td>\n",
       "      <td>Any Area</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>02S</td>\n",
       "      <td>11W</td>\n",
       "      <td>SB</td>\n",
       "      <td>33.950167</td>\n",
       "      <td>-118.076053</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3714791</th>\n",
       "      <td>Jack Brown</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>Santa Fe Springs</td>\n",
       "      <td>White Star</td>\n",
       "      <td>1</td>\n",
       "      <td>Any Area</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>03S</td>\n",
       "      <td>11W</td>\n",
       "      <td>SB</td>\n",
       "      <td>33.940096</td>\n",
       "      <td>-118.069159</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11325 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Operator Name  County Name        Field Name  \\\n",
       "API #                                                                  \n",
       "2500001                  Mike Barkett     Imperial         Any Field   \n",
       "2500002          Clarence E. Harrison     Imperial         Any Field   \n",
       "2500003          Petrodynamics Assoc.     Imperial         Any Field   \n",
       "2500004                 Sardi Oil Co.     Imperial         Any Field   \n",
       "2500005  Ajax Oil and Development Co.     Imperial         Any Field   \n",
       "...                               ...          ...               ...   \n",
       "3714787        BreitBurn Operating LP  Los Angeles  Santa Fe Springs   \n",
       "3714788       Blanton, Gendron & Cook  Los Angeles  Santa Fe Springs   \n",
       "3714789                 Block Oil Co.  Los Angeles  Santa Fe Springs   \n",
       "3714790                  Harry Briggs  Los Angeles  Santa Fe Springs   \n",
       "3714791                    Jack Brown  Los Angeles  Santa Fe Springs   \n",
       "\n",
       "             Lease Name Well # Area Name  Area Code  District #  Section  \\\n",
       "API #                                                                      \n",
       "2500001         Barkett      2  Any Area          0           1       20   \n",
       "2500002            Yuha      1  Any Area          0           1       20   \n",
       "2500003           Straw      1  Any Area          0           1        2   \n",
       "2500004            Biff      1  Any Area          0           1       24   \n",
       "2500005  U.S.L. Phyllis      1  Any Area          0           1        2   \n",
       "...                 ...    ...       ...        ...         ...      ...   \n",
       "3714787             NaN  486-G  Any Area          0           1        6   \n",
       "3714788             NaN     56  Any Area          0           1        1   \n",
       "3714789             NaN      2  Any Area          0           1        6   \n",
       "3714790        Dallugge      9  Any Area          0           1       31   \n",
       "3714791      White Star      1  Any Area          0           1        6   \n",
       "\n",
       "        Township Range Base Meridian   Latitude   Longitude  Unnamed: 15  \\\n",
       "API #                                                                      \n",
       "2500001      17S   11E            SB  32.661605 -115.885164          NaN   \n",
       "2500002      17S   11E            SB  32.661568 -115.873528          NaN   \n",
       "2500003      17S   10E            SB  32.703601 -115.936421          NaN   \n",
       "2500004      12S   13E            SB  33.111987 -115.571866          NaN   \n",
       "2500005      13S   17E            SB  33.068021 -115.187679          NaN   \n",
       "...          ...   ...           ...        ...         ...          ...   \n",
       "3714787      03S   11W            SB  33.942218 -118.065484          NaN   \n",
       "3714788      03S   12W            SB  33.936671 -118.086564          NaN   \n",
       "3714789      03S   11W            SB  33.940659 -118.070689          NaN   \n",
       "3714790      02S   11W            SB  33.950167 -118.076053          NaN   \n",
       "3714791      03S   11W            SB  33.940096 -118.069159          NaN   \n",
       "\n",
       "         Unnamed: 16  \n",
       "API #                 \n",
       "2500001          NaN  \n",
       "2500002          NaN  \n",
       "2500003          NaN  \n",
       "2500004          NaN  \n",
       "2500005          NaN  \n",
       "...              ...  \n",
       "3714787          NaN  \n",
       "3714788          NaN  \n",
       "3714789          NaN  \n",
       "3714790          NaN  \n",
       "3714791          NaN  \n",
       "\n",
       "[11325 rows x 16 columns]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "info_p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "info_p.to_csv(os.path.join(r\"data/processed\",\"well_production_info.csv\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Production Well Time-series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 33403/33403 [1:23:44<00:00,  6.65it/s]\n"
     ]
    }
   ],
   "source": [
    "ts_p = pd.DataFrame()\n",
    "for i in trange(len(production)):\n",
    "    each = production[i]\n",
    "    data = pd.read_excel(r\"data/raw/dataAllFields/\"+each,skiprows=[0,1,2],parse_dates=True).dropna(how = \"all\",axis = 1)\n",
    "    if len(data) < 1:\n",
    "        continue\n",
    "    data = data[[\"Total\" not in each for each in data[\"Production Date\"]]]\n",
    "    data[\"Year\"] = [int(each.split(\"-\")[1]) for each in data[\"Production Date\"]]\n",
    "    data[\"Month\"] = [each.split(\"-\")[0] for each in data[\"Production Date\"]]\n",
    "    ts_p = pd.concat([ts_p,data],axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_p.to_csv(os.path.join(r\"data/processed\",\"well_production_data.csv\"),index = None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_p[ts_p[\"Days Well Produced\"]>0].to_csv(os.path.join(r\"data/processed\",\"well_production_data_nonzero.csv\"),index = None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Earthquake Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 92/92 [00:25<00:00,  3.63it/s]\n"
     ]
    }
   ],
   "source": [
    "file_list = os.listdir(r\"data/raw/SCEC_DC/\")\n",
    "eq_file = [each for each in file_list if \"catalog\" in each]\n",
    "\n",
    "eq_data = pd.DataFrame()\n",
    "for i in trange(len(eq_file)):\n",
    "    each = eq_file[i]\n",
    "    df = pd.read_csv(r\"data/raw/SCEC_DC/\"+each,delim_whitespace=True,skiprows=list(range(9))).dropna(how=\"all\",axis = 1).iloc[:-1,:]\n",
    "    try:\n",
    "        df[\"Time\"] = df[\"#YYY/MM/DD\"]+\" \"+df[\"HH:mm:SS.ss\"]\n",
    "        df[\"Time\"] = pd.to_datetime(df[\"Time\"])\n",
    "    except:\n",
    "        # :60\n",
    "        df[\"Time\"] = df[\"#YYY/MM/DD\"]+\" \"+[each[:-5] for each in df[\"HH:mm:SS.ss\"]]+\"00\"\n",
    "        df[\"Time\"] = pd.to_datetime(df[\"Time\"])\n",
    "    eq_data = pd.concat([eq_data,df],axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "eq_data[\"Year\"] = [each.year for each in eq_data[\"Time\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "eq_data.to_csv(os.path.join(r\"data/processed\",\"earthquake.csv\"),index=None)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "gis",
   "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.8.0"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "d103d41b802a72e76d399106087f29da0436761a8849760a0c5ee13d62eef649"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}