FracFocusPostgithub.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import pickle\n",
    "import geopandas as gpd\n",
    "from datetime import date\n",
    "from datetime import datetime\n",
    "import pyodbc\n",
    "import pandas.io.sql as psql\n",
    "\n",
    "#Set viewing options in Jupyter\n",
    "np.set_printoptions(threshold=np.inf)\n",
    "pd.set_option('display.max_columns', 999)\n",
    "pd.options.display.float_format = '{:.5f}'.format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Pull the SQL database into Python and use the query they have already built for you from the download page.\n",
    "\n",
    "con = pyodbc.connect(driver='{SQL Server Native Client 11.0}',  #<-Make sure your driver matches whatever you put in here\n",
    "                      server='LAPTOP-XXXXXXXX\\SQLEXPRESS', #<-Find this at the opening screen of MS SQL Server Mgmt. Studio\n",
    "                      database='FracFocusRegistry', #<-If you made no changes on import of the FF db backup, this is it.\n",
    "                      trusted_connection='yes') \n",
    "cursor = con.cursor()\n",
    "\n",
    "sql = \"\"\"SELECT * From FracFocusRegistry.dbo.RegistryUpload ru Join FracFocusRegistry.[dbo].[RegistryUploadIngredients] ri\n",
    "\n",
    "on ru.pkey=ri.pKeyDisclosure \n",
    "\n",
    "Left JOIN FracFocusRegistry.dbo.RegistryUploadPurpose rp\n",
    "\n",
    "on ri.pKeyPurpose = rp.pKey\n",
    "\n",
    "Where (ri.pKeyPurpose is null or ru.pkey = ri.pKeyDisclosure);\"\"\"\n",
    "\n",
    "#    you can specify states and counties by adding something like this example:  \n",
    "\n",
    "#    StateNumber = (30) AND (CountyNumber = 25 OR CountyNumber = 15)\n",
    "\n",
    "#    in this spot of the query: Where (ri.pKeyPurpose is null or ru.pkey = ri.pKeyDisclosure) --put code here-- ;\"\"\" \n",
    "\n",
    "FFNMdata = pd.read_sql(sql, con)\n",
    "con.close()\n",
    "FFNMdata.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#With the way the SQL query FracFocus has set up, you can run into an issue with duplicate column names. Fix it with this.\n",
    "\n",
    "def df_columnrename(df):\n",
    "    df_columns = df.columns\n",
    "    new_columns = []\n",
    "    for item in df_columns:\n",
    "        counter = 0\n",
    "        newitem = item\n",
    "        while newitem in new_columns:\n",
    "            counter += 1\n",
    "            newitem = \"{}_{}\".format(item, counter)\n",
    "        new_columns.append(newitem)\n",
    "    df.columns = new_columns\n",
    "    return df\n",
    "\n",
    "#Apply the column rename function to the dataframe\n",
    "FFNMdata = df_columnrename(FFNMdata)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#There are \\n(newline) and \\t(tab) tags on some entries. For uniformity, capitalize the ingredients (or everything), \n",
    "#get rid of nan's, and get rid of those tags with this cell.\n",
    "\n",
    "FFNMdata['IngredientName'] = FFNMdata['IngredientName'].str.upper()\n",
    "FFNMdata['IngredientName'] = FFNMdata['IngredientName'].replace('\\n','', regex=True)\n",
    "FFNMdata['IngredientName'] = FFNMdata['IngredientName'].replace('\\t','', regex=True)\n",
    "\n",
    "FFNMdata['PercentHFJob'] = FFNMdata['PercentHFJob'].fillna(0.0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Number of wells in database\n",
    "\n",
    "APIvals = np.unique(FFNMdata['APINumber'])\n",
    "print(\"Database Wellcount:\", len(APIvals))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#PercentHFJob will give you frac compositions, and there are many entry errors throughout the database in this column.\n",
    "#We will check for sums equaling, or close to, 1.0, 100.0, and an arbitrary amount I thought may be present below.\n",
    "\n",
    "Sumcheck = FFNMdata.groupby('APINumber')['PercentHFJob'].sum()\n",
    "Sumcheck = pd.DataFrame(Sumcheck)\n",
    "Sumcheck.reset_index(inplace=True)\n",
    "Sumcheck.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Number of wells with a 1% tolerance\")\n",
    "DecimalSumdf = Sumcheck[(Sumcheck['PercentHFJob']>=.99) & (Sumcheck['PercentHFJob']<1.01)]\n",
    "WholeSumdf = Sumcheck[(Sumcheck['PercentHFJob']>=99.0) & (Sumcheck['PercentHFJob']<=101.0)]\n",
    "TooMuchSumdf = Sumcheck[(Sumcheck['PercentHFJob']>=110)]\n",
    "print(\"Number of decimal# compositions:\", len(DecimalSumdf))\n",
    "print(\"Number of whole# compositions:\", len(WholeSumdf))\n",
    "print(\"Number of way too much compositions:\", len(TooMuchSumdf))\n",
    "print(\"Total count:\", len(DecimalSumdf) + len(WholeSumdf) + len(TooMuchSumdf))\n",
    "print(\"Total good for use:\", len(DecimalSumdf) + len(WholeSumdf))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Number of wells with a 5% tolerance\")\n",
    "DecimalSumdf = Sumcheck[(Sumcheck['PercentHFJob']>=.90) & (Sumcheck['PercentHFJob']<1.1)]\n",
    "WholeSumdf = Sumcheck[(Sumcheck['PercentHFJob']>=90.0) & (Sumcheck['PercentHFJob']<=110.0)]\n",
    "TooMuchSumdf = Sumcheck[(Sumcheck['PercentHFJob']>110.0)]\n",
    "print(\"Number of decimal# compositions:\", len(DecimalSumdf))\n",
    "print(\"Number of whole# compositions:\", len(WholeSumdf))\n",
    "print(\"Number of way too much compositions:\", len(TooMuchSumdf))\n",
    "print(\"Total count:\", len(DecimalSumdf) + len(WholeSumdf) + len(TooMuchSumdf))\n",
    "print(\"Total good for use:\", len(DecimalSumdf) + len(WholeSumdf))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Number of wells that are exactly 1, 100, or 110 (the 110 is just a guess)\")\n",
    "DecimalSumdf = Sumcheck[(Sumcheck['PercentHFJob']==1.0)]\n",
    "WholeSumdf = Sumcheck[(Sumcheck['PercentHFJob']== 100.0)]\n",
    "TooMuchSumdf = Sumcheck[(Sumcheck['PercentHFJob']== 110.0)]\n",
    "print(\"Number of decimal# compositions:\", len(DecimalSumdf))\n",
    "print(\"Number of whole# compositions:\", len(WholeSumdf))\n",
    "print(\"Number of way too much compositions:\", len(TooMuchSumdf))\n",
    "print(\"Total count:\", len(DecimalSumdf) + len(WholeSumdf) + len(TooMuchSumdf))\n",
    "print(\"Total good for use:\", len(DecimalSumdf) + len(WholeSumdf))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#In the interest of sticking to realistic numbers, let's use the 99 - 101 range in terms of a sum cut off.\n",
    "#Granted, there are notes telling you that sums may add to more than 100%, I would rather just stick\n",
    "#with a tighter tolerance for this post.\n",
    "\n",
    "#You can increase/decrease your usable well count by doing more cleaning and inspection yourself.\n",
    "\n",
    "DecimalSumdf = Sumcheck[(Sumcheck['PercentHFJob']>=.99) & (Sumcheck['PercentHFJob']<1.01)]\n",
    "WholeSumdf = Sumcheck[(Sumcheck['PercentHFJob']>=99.0) & (Sumcheck['PercentHFJob']<=101.0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Convert decimal percent wells to whole number percentages before combining the filtered data sets\n",
    "DecimalConvert = FFNMdata[FFNMdata[\"APINumber\"].isin(DecimalSumdf['APINumber'])]\n",
    "\n",
    "DecimalConvert['PercentHFJob_fix'] = [(DecimalConvert['PercentHFJob'].iloc[x])*100.0 if \n",
    "                                      (DecimalConvert['PercentHFJob'].iloc[x] <= 1.0) \n",
    "                                      else DecimalConvert['PercentHFJob'].iloc[x] for x in range(0,len(DecimalConvert))]\n",
    "\n",
    "DecimalConvert = DecimalConvert.drop(['PercentHFJob', 'PercentHFJob_1'], axis=1)\n",
    "DecimalConvert = DecimalConvert.rename(columns={\"PercentHFJob_fix\":'PercentHFJob'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "WholeValuedf = FFNMdata[FFNMdata[\"APINumber\"].isin(WholeSumdf['APINumber'])]\n",
    "WholeValuedf = WholeValuedf.drop(['PercentHFJob_1'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Put the converted decimal to whole number and original whole number dataframes back together\n",
    "\n",
    "TotalUsedf = pd.concat([DecimalConvert, WholeValuedf], axis = 0)\n",
    "TotalUsedf = TotalUsedf.sort_values(by = \"APINumber\")\n",
    "APIvals = np.unique(TotalUsedf['APINumber'])\n",
    "print(\"Well count for db filter:\", len(APIvals), \"wells\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Create a dataframe for water volumes that has only the columns you need\n",
    "\n",
    "FFwatervol = TotalUsedf[[\"APINumber\", \"WellName\", \"TotalBaseWaterVolume\", \"OperatorName\", \"StateName\", \"StateNumber\", \n",
    "                         \"CountyName\", \"CountyNumber\", \"JobEndDate\", \"Latitude\", \"Longitude\", \"Projection\"]]\n",
    "FFwatervol = FFwatervol.sort_values(by = [\"APINumber\", \"TotalBaseWaterVolume\"], ascending=True)\n",
    "FFwatervol = FFwatervol.drop_duplicates(subset='APINumber', keep='last', inplace=False)\n",
    "FFwatervol = FFwatervol.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a dataframe for propant amounts to then apply the filter list below\n",
    "\n",
    "FFPropvol = TotalUsedf[[\"APINumber\", \"IngredientName\", \"PercentHFJob\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Polylactic acid is used as a diverter, but it looked a little suspect when going through the database, so I included it\n",
    "#If it doesn't matter, the numbers will be inconsequential in considering the mass of the proppant.\n",
    "\n",
    "#You can also just do a text search to break these out, but I already do various lists for looking at other aspects of\n",
    "#what is in the frac mixtures, so a cut and paste is much easier - for me.\n",
    "\n",
    "Proppantlist = [\"  CRYSTALLINE SILICA, QUARTZ    \", \n",
    "\"  CRYSTALLINE SILICA: QUARTZ (SIO2)\",\n",
    "\" CRYSTALLINE SILICA  QUARTZ \", \n",
    "\" CRYSTALLINE SILICA (QUARTS) \", \n",
    "\" CRYSTALLINE SILICA QUARTZ\", \n",
    "\" CRYSTALLINE SILICA: QUARTZ (SIO2)\", \n",
    "\" CRYSTALLINE SILICA: QUARTZ (SIO2) CRYSTALLINE SILICA: QUARTZ (SIO2)\",\n",
    "\" INTERMEDIATE & HIGH STRENGTH PROPPANTS (ALL SIZES) \", \n",
    "\" ORGANICALLY TREATED CLAY\", \n",
    "\" SILICA, CRYSTALLINE - QUARTZ \", \n",
    "\" SILICA, CRYSTALLINE QUARTZ\", \n",
    "\" ULEXITE\", \n",
    "\"CRYSTALLINE SILICA (QUARTZ)\", '\"CRYSTALLINE SILICA QUARTZ\"',\n",
    "'\"CRYSTALLINE SILICA: QUARTZ (SIO2) CRYSTALLINE SILICA: QUARTZ (SIO2)\"', \n",
    "'\"CRYSTALLINE SILICA: QUARTZ (SIO2)\"', \n",
    "'\"CRYSTALLINE SILLICA: QUARTZCRYSTALLINE SILLICA: QUARTZ\"', \n",
    "'\"SILICA, CRYSTALLINE- QUARTS QUARTZ QUARTZ\"', \n",
    "'\"ULEXITE\"', \n",
    "'\"ULEXITEULEXITEULEXITE\"',\n",
    "\"10 MESH STANDARD POWDER\", \n",
    "\"12/20 SAND\", \n",
    "\"12-20 MESH BRADY SAND_OKLAHOMA\", \n",
    "\"12-20 MESH OTTAWA SD., TX\", \n",
    "\"12-40 MESH OTTAWA SD.,TX\", \n",
    "\"14/40 FRACBLACK HT\", \n",
    "\"16/20 EL PROP\", \n",
    "\"16/30 SAND\", \n",
    "\"16-30 MESH BRADY SAND_OKLAHOMA\", \n",
    "\"20/40 ECONOPROP\", \n",
    "\"20/40 MG LITE\", \n",
    "\"20/40 OTTAWA SAND\", \n",
    "\"20/40 PC\", \n",
    "\"20/40 PRC\", \n",
    "\"20/40 SINTERLITE\", \n",
    "\"20/40 SUPER LC\", \n",
    "\"20/40 TG\", \n",
    "\"20/40 WHITE\", \n",
    "\"20-40 MESH OTTAWA SAND_TX\", \n",
    "\"20-40 MESH OTTAWA SD TX\", \n",
    "\"30/50 PPC\", \n",
    "\"30/50 SAND\", \n",
    "\"30/50 SUPER LC\", \n",
    "\"30/50 TEMPERED HS\", \n",
    "\"30/50 TG\", \n",
    "\"30/50 WHITE\", \n",
    "\"33-40\", \n",
    "\"35# SIROCCO\", \n",
    "\"40/70 NORTHERN WHITE SAND\", \n",
    "\"40/70 PPC\", \n",
    "\"40/70 PREFERRED SAND\", \n",
    "\"40/70 TEMPERED HS\", \n",
    "\"40/70 WHITE\", \n",
    "\"40/70 WHITE SAND\", \n",
    "\"40/80 HYDROPROP\", \n",
    "\"AA-400 (ALUMINUM OXIDE)\", \n",
    "\"ALUMINATE (AL(OH)63-) (OC-6-11)- MAGNESIUM CARBONATE HYDROXIDE (2:6:1:4)\", \n",
    "\"ALUMINION OXIDE\", \n",
    "\"ALUMINIUM OXIDE\", \n",
    "\"ALUMINIUM SILICATE\", \n",
    "\"ALUMINUIUM OXIDE\", \n",
    "\"ALUMINUM NEEDLES\", \n",
    "\"ALUMINUM OXIDE\", \n",
    "\"ALUMINUM OXIDE \", \n",
    "\"ALUMINUM OXIDE / CORUNDUM\", \n",
    "\"ALUMINUM OXIDE CORUNDUM\", \n",
    "\"ALUMINUM OXIDE CORUNDUM 35-65\", \n",
    "\"ALUMINUM OXIDE, CORUNDUM\", \n",
    "\"ALUMINUM OXIDE/CORUNDUM\", \n",
    "\"ALUMINUM OXIDE/CURUNDUM\", \n",
    "\"ALUMINUM SILCATE/MULITE\", \n",
    "\"ALUMINUM SILICATE\", \n",
    "\"ALUMINUM SILICATE (MULLITE)\", \n",
    "\"ALUMINUM SILICATE / MULLITE\", \n",
    "\"ALUMINUM SILICATE MULITE\", \n",
    "\"ALUMINUM SILICATE MULLITE\", \n",
    "\"ALUMINUM SILICATE MULLITE 35-65\", \n",
    "\"ALUMINUM SILICATE, MULITE\", \n",
    "\"ALUMINUM SILICATE/ MULLITE\", \n",
    "\"ALUMINUM SILICATE/MULITE\", \n",
    "\"ALUMINUM SILICATE/MULLITE\", \n",
    "\"ALUMIUM OXIDE\", \n",
    "\"AMORPHOUS SILICA\", \n",
    "\"AMORPHOUS SILICA (GLASS)\", \n",
    "\"AMORPHOUS SILICA (LIQUIFIED)\", \n",
    "\"AMORPHOUS SILICA/ETHYLENE GLYCOL\", \n",
    "\"AMORPHOUS SILISCA\", \n",
    "\"AMORPHUS SILICA\", \n",
    "\"BAUXITE\", \n",
    "\"BAUXITE (AL2O3.XH2O), CALCINED\", \n",
    "\"BAUXITE (AL2O3.XH2O),CALCINED\", \n",
    "\"BAUXITE CALCINED\", \n",
    "\"BAUXITE, CALCINED\", \n",
    "\"CALCINED BAUXITE\", \n",
    "\"CALCINED KAOLIN (MULLITE)\",\n",
    "\"CARBAMIDE\", \n",
    "\"CERAMIC\", \n",
    "\"CERAMIC BEADS\", \n",
    "\"CERAMIC MATERIAL & WARES CHEMICALS \", \n",
    "\"CERAMIC MATERIAL & WARES, CHEMICALS\", \n",
    "\"CERAMIC MATERIAL & WARES, CHEMICALS \", \n",
    "\"CERAMIC MATERIAL AND WARES CHEMCIAL\", \n",
    "\"CERAMIC MATERIAL AND WARES CHEMICAL\", \n",
    "\"CERAMIC MATERIAL AND WARES, CHEMCIAL\", \n",
    "\"CERAMIC MATERIAL AND WARES, CHEMICAL\", \n",
    "\"CERAMIC MATERIALS\", \n",
    "\"CERAMIC MATERIALS AND WARES\", \n",
    "\"CERAMIC MATERIALS AND WARES CHEMCICALS\", \n",
    "\"CERAMIC MATERIALS AND WARES CHEMICAL\", \n",
    "\"CERAMIC MATERIALS AND WARES CHEMICALS\", \n",
    "\"CERAMIC MATERIALS AND WARES CHEMICALS\", \n",
    "\"CERAMIC MATERIALS AND WARES, CHEMCICALS\", \n",
    "\"CERAMIC MATERIALS AND WARES, CHEMICAL\", \n",
    "\"CERAMIC MATERIALS AND WARES, CHEMICALS\", \n",
    "\"CERAMIC MATERIAS AND WARES CHEMICALS\", \n",
    "\"CERAMIC MATERIAS AND WARES, CHEMICALS\", \n",
    "\"CERAMIC MATERICLAS AND WARES CHEMICALS\", \n",
    "\"CERAMIC MATERICLAS AND WARES, CHEMICALS\", \n",
    "\"CERAMIC MATIERIALS AND WARES \", \n",
    "\"CERAMIC MICROSPHERE\", \n",
    "\"CERAMIC MICROSPHERES\", \n",
    "\"CERAMIC MICROSPHERES/GLUTARALDEHYDE\", \n",
    "\"CERAMIC MIX\", \n",
    "\"CERAMIC PROPANT\", \n",
    "\"CERAMIC PROPPANT\", \n",
    "\"CERAMIC PROPPANT PROPRIETARY\", \n",
    "\"CERAMICM MATERIALS AND WARES CHEMICALS\", \n",
    "\"CERAMICM MATERIALS AND WARES, CHEMICALS\", \n",
    "\"CERAMICS MATERIALS AND WARES, CHEMICALS\", \n",
    "\"CHRISTOBALITE\", \n",
    "\"CHROMITE SAND\", \n",
    "\"CHRYSTALLINE SILICA\", \n",
    "\"CHRYSTALLINE SILICA, QUARTZ\", \n",
    "\"CHRYSTALLINE SILICA: CRISTOBALITE\", \n",
    "\"CHRYSTALLINE SILICA: CRISTOBALITE \", \n",
    "\"CHRYSTALLINE SILICA: QUARTZ\", \n",
    "\"CHRYSTALLINE SILICA:CRISTOBALITE \", \n",
    "\"CORRUNDUM\", \n",
    "\"CORUNDIUM\", \n",
    "\"CORUNDUM\", \n",
    "\"CORUNDUM (AI203)\", \n",
    "\"CORUNDUM (ALUMINUM OXIDE)\", \n",
    "\"CRHYSTALLINE SILICA, QUARTZ\", \n",
    "\"CRISTALLINE SILICA\", \n",
    "\"CRISTALLINE SILICA QUARTZ\", \n",
    "\"CRISTALLINE SILICA, QUARTZ\", \n",
    "\"CRISTALLINE SILLICA: QUARTZ (SIO2)\", \n",
    "\"CRISTOBALITE\", \n",
    "\"CRSTALLINE SILICA (QUARTZ)\", \n",
    "\"CRSTALLINE SILICA, QUARTZ\", \n",
    "\"CRYS4808-60-7TALLINE SIO2\", \n",
    "\"CRYSTAILLINE SILICA: QUARTZ\", \n",
    "\"CRYSTALILINE SILICA: QUARTZ (SIO2)\", \n",
    "\"CRYSTALINE\", \n",
    "\"CRYSTALINE  SILICA, SIO2\", \n",
    "\"CRYSTALINE SAILICA\", \n",
    "\"CRYSTALINE SILCA, SIO2\", \n",
    "\"CRYSTALINE SILICA\", \n",
    "\"CRYSTALINE SILICA \", \n",
    "\"CRYSTALINE SILICA - QUARTZ\", \n",
    "\"CRYSTALINE SILICA (QUARTZ)\", \n",
    "\"CRYSTALINE SILICA (SIO2)\", \n",
    "\"CRYSTALINE SILICA 100 MESH\", \n",
    "\"CRYSTALINE SILICA 40/70\", \n",
    "\"CRYSTALINE SILICA IN THE FORM OF QUARTZ\", \n",
    "\"CRYSTALINE SILICA QUARTZ\", \n",
    "\"CRYSTALINE SILICA QUARTZ\", \n",
    "\"CRYSTALINE SILICA RESPIRABLE POWDER\", \n",
    "\"CRYSTALINE SILICA SIO2\", \n",
    "\"CRYSTALINE SILICA, CRISTOBALITE\", \n",
    "\"CRYSTALINE SILICA, QUARTZ\", \n",
    "\"CRYSTALINE SILICA, QUARTZ \", \n",
    "\"CRYSTALINE SILICA, QUARTZ, PHENOL-FORMALDEHYDE NOVOLAK RESIN 9003-35-4, HEXAMEHTYLENETETRAMINE 100-97-0\", \n",
    "\"CRYSTALINE SILICA, QUARTZ, PHENOL-FORMALDEHYDE NOVOLAK RESIN9003-35-4, HEXAMETHYLENETETRAMINE 100-97-0\", \n",
    "\"CRYSTALINE SILICA, RESPIRABLE POWDER\", \n",
    "\"CRYSTALINE SILICA, SIO2\", \n",
    "\"CRYSTALINE SILICA: CRISTOBALITE\", \n",
    "\"CRYSTALINE SILICA: QUARTZ\", \n",
    "\"CRYSTALINE SILICA: QUARTZ (SIO2)\", \n",
    "\"CRYSTALINE SILLICA\", \n",
    "\"CRYSTALINE SILLICA IN FORM OF QUARTZ\", \n",
    "\"CRYSTALIONE SILICA, QUARTZ\", \n",
    "\"CRYSTALLIE SILICA QUARTZ\", \n",
    "\"CRYSTALLIE SILICA, QUARTZ\", \n",
    "\"CRYSTALLIINE SILICA\", \n",
    "\"CRYSTALLIINE SILICA: QUARTZ\", \n",
    "\"CRYSTALLILNE SILICA\", \n",
    "\"CRYSTALLILNE SILICA: QUARTZ (SIO2)\", \n",
    "\"CRYSTALLIME SILICA\", \n",
    "\"CRYSTALLIN SILICA (QUARTZ)\", \n",
    "\"CRYSTALLIN SILICA QUARTZ\", \n",
    "\"CRYSTALLIN SILICA, CRISTOBALITE\", \n",
    "\"CRYSTALLIN SILICA, QUARTZ\", \n",
    "\"CRYSTALLIN SILICA: QUARTZ\", \n",
    "\"CRYSTALLINE\", \n",
    "\"CRYSTALLINE \", \n",
    "\"CRYSTALLINE CRISTOBALITE\", \n",
    "\"CRYSTALLINE POWDER\", \n",
    "\"CRYSTALLINE POWDWER\", \n",
    "\"CRYSTALLINE QUARTZ\", \n",
    "\"CRYSTALLINE QUARTZ (SILICA)\", \n",
    "\"CRYSTALLINE QUARTZ SILICA\", \n",
    "\"CRYSTALLINE QUARTZ, SILICA\", \n",
    "\"CRYSTALLINE SALT\", \n",
    "\"CRYSTALLINE SI02\", \n",
    "\"CRYSTALLINE SIICA\", \n",
    "\"CRYSTALLINE SIILICA: QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILCA\", \n",
    "\"CRYSTALLINE SILCA (QUARTZ)\", \n",
    "\"CRYSTALLINE SILCA: QUARTZ\", \n",
    "\"CRYSTALLINE SILIC, CRISTOBALITE\", \n",
    "\"CRYSTALLINE SILICA\", \n",
    "\"CRYSTALLINE SILICA \", \n",
    "\"CRYSTALLINE SILICA  (QUARTZ)\", \n",
    "\"CRYSTALLINE SILICA  QUARTZ\", \n",
    "\"CRYSTALLINE SILICA - QUARTZ\", \n",
    "\"CRYSTALLINE SILICA (<0.02%)\", \n",
    "\"CRYSTALLINE SILICA (CRISOBALITE)\", \n",
    "\"CRYSTALLINE SILICA (CRISTOBALITE)\", \n",
    "\"CRYSTALLINE SILICA (CRISTOBALOITE)\", \n",
    "\"CRYSTALLINE SILICA (CRITOBALITE\", \n",
    "\"CRYSTALLINE SILICA (IMPURITY)\", \n",
    "\"CRYSTALLINE SILICA (IN FORM OF QUARTZ)\", \n",
    "\"CRYSTALLINE SILICA (IN THE FORM OF QUARTZ)\", \n",
    "\"CRYSTALLINE SILICA (QUARTS)\", \n",
    "\"CRYSTALLINE SILICA (QUARTX)\", \n",
    "\"CRYSTALLINE SILICA (QUARTZ\", \n",
    "\"CRYSTALLINE SILICA (QUARTZ SAND SILICON DIOXIDE)\", \n",
    "\"CRYSTALLINE SILICA (QUARTZ SAND, SILICON DIOXIDE)\", \n",
    "\"CRYSTALLINE SILICA (QUARTZ)\", \n",
    "\"CRYSTALLINE SILICA (QUARTZ) \", \n",
    "\"CRYSTALLINE SILICA (QUARTZ) (SIO2)\", \n",
    "\"CRYSTALLINE SILICA (QUARTZ) SIO2\", \n",
    "\"CRYSTALLINE SILICA (SILICON DIOXIDE)\", \n",
    "\"CRYSTALLINE SILICA (SIO2)\", \n",
    "\"CRYSTALLINE SILICA : QUARTZ (SI02)\", \n",
    "\"CRYSTALLINE SILICA : QUARTZ (SIC02)\", \n",
    "\"CRYSTALLINE SILICA : QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILICA ALKYL\", \n",
    "\"CRYSTALLINE SILICA CRISTOBALITE\", \n",
    "\"CRYSTALLINE SILICA CRISTOBALLITE\", \n",
    "\"CRYSTALLINE SILICA IN FORM OF QUARTZ\", \n",
    "\"CRYSTALLINE SILICA IN THE FORM OF QUARTZ\", \n",
    "\"CRYSTALLINE SILICA IN THE FORM OF QUARTZ \", \n",
    "\"CRYSTALLINE SILICA IN THE FORM OF QUARZ\", \n",
    "\"CRYSTALLINE SILICA IN THE FORM OR QUARTZ\", \n",
    "\"CRYSTALLINE SILICA IN THE FORMOF QUARTZ\", \n",
    "\"CRYSTALLINE SILICA QUART\", \n",
    "\"CRYSTALLINE SILICA QUARTS\", \n",
    "\"CRYSTALLINE SILICA QUARTZ\", \n",
    "\"CRYSTALLINE SILICA QUARTZ\", \n",
    "\"CRYSTALLINE SILICA QUARTZ\", \n",
    "\"CRYSTALLINE SILICA QUARTZ\", \n",
    "\"CRYSTALLINE SILICA QUARTZ \", \n",
    "\"CRYSTALLINE SILICA QUARTZ \", \n",
    "\"CRYSTALLINE SILICA QUARTZ (SI02)\", \n",
    "\"CRYSTALLINE SILICA QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILICA QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILICA QUARTZ (SIO2) \", \n",
    "\"CRYSTALLINE SILICA QUARTZ(SIO2)\", \n",
    "\"CRYSTALLINE SILICA QUARTZ: SIO2\", \n",
    "\"CRYSTALLINE SILICA QUARZ \", \n",
    "\"CRYSTALLINE SILICA QUATRTS\", \n",
    "\"CRYSTALLINE SILICA QUATZ\", \n",
    "\"CRYSTALLINE SILICA RESPIRABLE POWDER\", \n",
    "\"CRYSTALLINE SILICA SIO2\", \n",
    "\"CRYSTALLINE SILICA        \",\n",
    "\"CRYSTALLINE SILICA(QUARTZ)\", \n",
    "\"CRYSTALLINE SILICA, (QUARTZ)\", \n",
    "\"CRYSTALLINE SILICA, CRISTOBALITE\", \n",
    "\"CRYSTALLINE SILICA, QARTZ\", \n",
    "\"CRYSTALLINE SILICA, QUART\", \n",
    "\"CRYSTALLINE SILICA, QUARTAZ\", \n",
    "\"CRYSTALLINE SILICA, QUARTS\", \n",
    "\"CRYSTALLINE SILICA, QUARTZ\", \n",
    "\"CRYSTALLINE SILICA, QUARTZ \", \n",
    "\"CRYSTALLINE SILICA, QUARTZ (SI02)\", \n",
    "\"CRYSTALLINE SILICA, QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILICA, QUARTZ\",\n",
    "\"CRYSTALLINE SILICA, QUARTZÂ Â \", \n",
    "\"CRYSTALLINE SILICA, QUATRTS\", \n",
    "\"CRYSTALLINE SILICA, QUATZ\", \n",
    "\"CRYSTALLINE SILICA, QWUARTZ\", \n",
    "\"CRYSTALLINE SILICA, RESPIRABLE POWDER\", \n",
    "\"CRYSTALLINE SILICA, SIO2\", \n",
    "\"CRYSTALLINE SILICA,QUARTZ\", \n",
    "\"CRYSTALLINE SILICA,QURTZ\", \n",
    "\"CRYSTALLINE SILICA. QUARTZ\", \n",
    "\"CRYSTALLINE SILICA:  CRISTOBALITE\", \n",
    "\"CRYSTALLINE SILICA:  QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILICA:  QUARTZ(SIO2)\", \n",
    "\"CRYSTALLINE SILICA: (SIO2)\", \n",
    "\"CRYSTALLINE SILICA: CHRISTOBALITE\", \n",
    "\"CRYSTALLINE SILICA: CRISTOBALITE\", \n",
    "\"CRYSTALLINE SILICA: CRISTOLBALITE\", \n",
    "\"CRYSTALLINE SILICA: QUART (SIO2)\", \n",
    "\"CRYSTALLINE SILICA: QUARTS\", \n",
    "\"CRYSTALLINE SILICA: QUARTS (SI02)\", \n",
    "\"CRYSTALLINE SILICA: QUARTS (SIO2)\", \n",
    "\"CRYSTALLINE SILICA: QUARTZ\", \n",
    "\"CRYSTALLINE SILICA: QUARTZ \", \n",
    "\"CRYSTALLINE SILICA: QUARTZ (SI02)\", \n",
    "\"CRYSTALLINE SILICA: QUARTZ (SIO2\", \n",
    "\"CRYSTALLINE SILICA: QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILICA: QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILICA: QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILICA: QUARTZ (SIO2) \", \n",
    "\"CRYSTALLINE SILICA: QUARTZ (SIO2)\",\n",
    "\"CRYSTALLINE SILICA: QUARTZ(SIO2)\", \n",
    "\"CRYSTALLINE SILICA: QUATRZ (SIO2)\", \n",
    "\"CRYSTALLINE SILICA:CRISTOBALITE\",\n",
    "\"CRYSTALLINE SILICA:QUARTZ\", \n",
    "\"CRYSTALLINE SILICA:QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILICA; QUARTZ\", \n",
    "\"CRYSTALLINE SILICA;QUARTZ SAND,SILICON DIOXIDE\", \n",
    "\"CRYSTALLINE SILICAM, QUARTZ\", \n",
    "\"CRYSTALLINE SILICAM, QUARTZ \", \n",
    "\"CRYSTALLINE SILICAQUARTZ\", \n",
    "\"CRYSTALLINE SILICIA QUARTZ\", \n",
    "\"CRYSTALLINE SILICIA, QUARTS\", \n",
    "\"CRYSTALLINE SILICIA, SIO2\", \n",
    "\"CRYSTALLINE SILICIA: QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILICON DIOXIDE\", \n",
    "\"CRYSTALLINE SILLICA\", \n",
    "\"CRYSTALLINE SILLICA \", \n",
    "\"CRYSTALLINE SILLICA (QUARTZ)\", \n",
    "\"CRYSTALLINE SILLICA (SILICA SAND)\", \n",
    "\"CRYSTALLINE SILLICA IN FORM OF QUARTZ\", \n",
    "\"CRYSTALLINE SILLICA QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILLICA, QUARTZ\", \n",
    "\"CRYSTALLINE SILLICA, QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILLICA: QUARTZ\", \n",
    "\"CRYSTALLINE SILLICA: QUARTZ (SIO2)\", \n",
    "\"CRYSTALLINE SILLICA: QUARTZCRYSTALLINE SILLICA: QUARTZ\",\n",
    "\"CRYSTALLINE SIO2\", \n",
    "\"CRYSTALLINE SITICA\", \n",
    "\"CRYSTALLINE SLICA (QUARTZ)\", \n",
    "\"CRYSTALLINE SLICA QUARTZ\", \n",
    "\"CRYSTALLINE SLICA, QUARTZ\", \n",
    "\"CRYSTALLINE SLILCA\", \n",
    "\"CRYSTALLINE SLILICA (QUARTZ)\", \n",
    "\"CRYSTALLINE, SILICA, QUARTZ\", \n",
    "\"CRYSTALLING SILICA, QUARTZ\", \n",
    "\"CRYSTALLLINE SILICA (QUARTZ)\", \n",
    "\"CRYSTALLLINE SILICA QUARTZ\", \n",
    "\"CRYSTALLLINE SILICA QUARTZ (SIO2)\", \n",
    "\"CRYSTALLLINE SILICA, QUARTZ\", \n",
    "\"CRYSTALLLINE SILICA, QUARTZ (SIO2)\", \n",
    "\"CRYSTIALLINE SILICA\", \n",
    "\"CRYSTIALLINE SILICA: CRISTOBALITE\", \n",
    "\"CRYSTIALLINIE SILICA, QUARTZ\", \n",
    "\"CRYSTILLINE SILICA, QUARTZ\", \n",
    "\"CRYSTLLINE SILICA, QUARTZ\", \n",
    "\"CRYTALINE SILICA, CRISTOBALITE\", \n",
    "\"CRYTALLIN SILICA QURATZ\", \n",
    "\"CRYTALLIN SILICA, QURATZ\", \n",
    "\"CRYTALLINE SILICA\", \n",
    "\"CRYTALLINE SILICA (QUARTZ)\", \n",
    "\"CRYTALLINE SILICA, QUARTZ\", \n",
    "\"CRYTALLINE SILICA, SIO2\", \n",
    "\"CS-210PB\", \n",
    "\"CTYSTALLINE SILICA\", \n",
    "\"CTYSTALLINE SILICA\", \n",
    "\"CURED  RESIN\", \n",
    "\"CURED ACRLIC RESIN\", \n",
    "\"CURED ACRLYIC RESIN\", \n",
    "\"CURED ACRYLIC  RESIN\", \n",
    "\"CURED ACRYLIC RESIC\", \n",
    "\"CURED ACRYLIC RESIN\", \n",
    "\"CURED ACRYLIC RESIN \", \n",
    "\"CURED ACRYLIC RESIN MIXTURE\", \n",
    "\"CURED ACRYLIC RESINQ \", \n",
    "\"CURED ACRYLIC SAND\", \n",
    "\"CURED ACYLIC RESIN\", \n",
    "\"CURED PHENOLIC RESIN\", \n",
    "\"CURED PHNOLIC RESIN\", \n",
    "\"CURED RESIN\", \n",
    "\"CURED RESIN \", \n",
    "\"CURED SCRYLIC RESIN\", \n",
    "\"CURED SILICONE RUBBER-POLYDIMETHYLSILOXANE\", \n",
    "\"CURED URETHANE RESIN\", \n",
    "\"CURRED ACRYLIC RESIN\", \n",
    "\"CYRSTALINE SILCA\", \n",
    "\"CYRSTALINE SILICA\", \n",
    "\"CYRSTALLIN SILICA: QUARTZ (SIO2)\", \n",
    "\"CYRSTALLINE SILICA\", \n",
    "\"CYRSTALLINE SILICA QUARTZ\", \n",
    "\"CYRSTALLINE SILICA QUARTZ (SIO2)\", \n",
    "\"CYRSTALLINE SILICA, QUARTZ\", \n",
    "\"CYRSTALLINE SILICA, QUARTZ (SIO2)\", \n",
    "\"CYRSTALLINE SILICA: QUARTZ (SIO2)\", \n",
    "\"CYRTALLINE SILICA QUARTZ\", \n",
    "\"CYSTALLINE SALICA, SIO2\", \n",
    "\"CYSTALLINE SILICA\", \n",
    "\"CYSTALLINE SILICA (QUARTZ)\", \n",
    "\"CYSTALLINE SILICA QUARTZ\", \n",
    "\"CYSTALLINE SILICA, (QUARTZ)\", \n",
    "\"CYSTALLINE SILICA, QUARTZ\", \n",
    "\"CYSTALLINE SILICA: QUARTZ\", \n",
    "\"CYSTALLINE SILICA; QUARTZ\", \n",
    "\"FRAC SAND\", \n",
    "\"FUMED SILICA\", \n",
    "\"GLASS FIBERS\", \n",
    "\"GLASS OXIDE\", \n",
    "\"GLASS OXIDE; GLASS\", \n",
    "\"GLASS, OXIDE\", \n",
    "\"GLASSY CALCIUM MAGNESIUM PHOSPHATE\", \n",
    "\"GLASSY CALSIUM MAGNESIUM PHOSPHATE\", \n",
    "\"ILLITE\", \n",
    "\"ILMENITE\", \n",
    "\"IRON OXIDE\", \n",
    "\"IRON OXIDE \", \n",
    "\"IRON OXIDE (COLORANT)\", \n",
    "\"IRON OXIDE (FUME)\", \n",
    "\"LIMESTONE\", \n",
    "\"MICA\", \n",
    "\"MODIFIED BENTONITE\", \n",
    "\"MODIFIED BENTONITE (ORGANOPHILLIC CLAY)\", \n",
    "\"MODIFIED BENTONITE CLAY\", \n",
    "\"MUILLITE\", \n",
    "\"MULITE\", \n",
    "\"MULITIE\", \n",
    "\"MULLITE\", \n",
    "\"MULLITE \", \n",
    "\"MULLITE (ALUMINASILICATE)\", \n",
    "\"MULLITE (ALUMINUM SILICATE)\", \n",
    "\"MULLITE (AMMONIUM SILICATE)\", \n",
    "\"NON- CRYSTALLINE SILICA (IMPURITY)\", \n",
    "\"NON-CRYSTALLINE SILICA (IMPURITY)\", \n",
    "\"NON-CRYSTALLINE SUBSTANCE\", \n",
    "\"OSYALKYLATED RESINS\", \n",
    "\"OTHER INORGANIC MAGNESIUM COMPOUND\", \n",
    "\"OTHER INORGANIC MAGNESIUM CPD\", \n",
    "\"OXIDE POLYMER \", \n",
    "\"OXIDES OF SILICA, IRON, ALUMINUM\", \n",
    "\"P/F NOVOLAK RESIN\", \n",
    "\"P/F RESIN\", \n",
    "\"POLACTIDE  RESIN\", \n",
    "\"POLACTIDE RESIN\", \n",
    "\"POLIYLACTIDE RESIN\", \n",
    "\"POLLACTIDE RESIN\", \n",
    "\"POLLYLACTIDE RESIN\", \n",
    "\"POLULACTIDE RESIN\", \n",
    "\"POLYACETIDE RESIN\", \n",
    "\"POLYACITE RESIN\", \n",
    "\"POLYACITIDE RESIN\", \n",
    "\"POLYACITIDE RESIN \", \n",
    "\"POLYACTDE RESIN\", \n",
    "\"POLYACTIDE\", \n",
    "\"POLYACTIDE RESIGN\", \n",
    "\"POLYACTIDE RESIN\", \n",
    "\"POLYACTIDE RESIN \", \n",
    "\"POLYACTIDE RESUN\", \n",
    "\"POLYACTITE RESIN\", \n",
    "\"POLYACTITIDE RESIN\", \n",
    "\"POLYALACTIDE RESIN\", \n",
    "\"POLYCITE RESIN\", \n",
    "\"POLYERETHANE RESIN\", \n",
    "\"POLYEURETHANE RESIN\", \n",
    "\"POLYLACIDITE RESIN\", \n",
    "\"POLYLACITE RESIN\", \n",
    "\"POLYLACITIDE RESIN\", \n",
    "\"POLYLACTACTIDE RESIN\", \n",
    "\"POLYLACTIC RESIN\", \n",
    "\"POLYLACTICDE RESIN\", \n",
    "\"POLYLACTICIDE RESIN\", \n",
    "\"POLYLACTID RESIN\", \n",
    "\"POLYLACTIDE\", \n",
    "\"POLYLACTIDE  RESIN\", \n",
    "\"POLYLACTIDE RESEN\", \n",
    "\"POLYLACTIDE RESID\", \n",
    "\"POLYLACTIDE RESIGN\", \n",
    "\"POLYLACTIDE RESIN\", \n",
    "\"POLYLACTIDE RESIN \", \n",
    "\"POLYLACTIDEE RESIN\", \n",
    "\"POLYLACTITE RESIN\", \n",
    "\"POLYLACTIVE RESIN\", \n",
    "\"POLYLASTIDE RESIN\", \n",
    "\"PREMIUM WHITE 40/70 PROPPANT\", \n",
    "\"PROPRIETARY SILICA\", \n",
    "\"PROPRIETARY SILICA ORGANIC POLYMER\", \n",
    "\"QUARTS (SIO2)\", \n",
    "\"QUARTZ\", \n",
    "\"QUARTZ  (CRYSTALLINE SILICA)\", \n",
    "\"QUARTZ - SILICA\", \n",
    "\"QUARTZ (CRYSTALLINE SILICA\", \n",
    "\"QUARTZ (CRYSTALLINE SILICA)\", \n",
    "\"QUARTZ (CRYSTILLANE SILICA)\", \n",
    "\"QUARTZ (SI02)\", \n",
    "\"QUARTZ (SI02) (CRYSTALLINE SILICA)\", \n",
    "\"QUARTZ (SILICA)\", \n",
    "\"QUARTZ (SIO2)\", \n",
    "\"QUARTZ (SIO2) \", \n",
    "\"QUARTZ (SIO2)  (CRYSTALLINE SILICA)\", \n",
    "\"QUARTZ (SIO2) (CRYSTALLINE SILICA\", \n",
    "\"QUARTZ (SIO2) (CRYSTALLINE SILICA)\", \n",
    "\"QUARTZ (SIO2) (CRYSTALLINE SILICA) \", \n",
    "\"QUARTZ (SIO2) (CRYSTASLLINE SILICA)\", \n",
    "\"QUARTZ (SIO2) (CYRSTALLINE SILICA)\", \n",
    "\"QUARTZ (SIO2) CRYSTALLINE SILLICA\", \n",
    "\"QUARTZ (SIO2)(CRYSTALLINE SILICA)\", \n",
    "\"QUARTZ CRYSTALLINE SILICA\", \n",
    "\"QUARTZ CRYSTALLINE SILICA \", \n",
    "\"QUARTZ -SILICA\", \n",
    "\"QUARTZ(SIO2)\", \n",
    "\"QUARTZ, CRYSTALLINE SILICA\", \n",
    "\"QUARTZ, CRYSTALLINE SILICA \", \n",
    "\"QUARTZ, CRYSTALLINE SILLICA\", \n",
    "\"QUARTZ, SIO2\", \n",
    "\"RESIN\", \n",
    "\"RESIN A\", \n",
    "\"RESIN COATED CRYSTALLINE SILICA \", \n",
    "\"RESIN COATED CRYSTALLINE SILICA (QUARTZ)\", \n",
    "\"RESIN COATED FRACTURING PROPPANT\", \n",
    "\"RESIN COATED PROPPANT\", \n",
    "\"RESIN COATED SILICA FRACTURING PROPPANT\", \n",
    "\"RESIN COMPOUND\", \n",
    "\"RESPIRABLE CRYSTALLING SILICA (QUARTS)\", \n",
    "\"RYSTALLINE SILICA\", \n",
    "\"RYSTALLINE SILICA, QUARTZ\", \n",
    "\"SAND\", \n",
    "\"SAND - CRYSTALINE SILICA\", \n",
    "\"SAND - CRYSTALLINE SILICA\", \n",
    "\"SAND (CRYSTALLINE SILICA)\", \n",
    "\"SAND 20/40 OTTOWA WHITE\", \n",
    "\"SAND-PREMIUM WHITE\", \n",
    "\"SI\", \n",
    "\"SI-2 BLEND\", \n",
    "\"SICLA QUARTZ\", \n",
    "\"SIICIA QUARTZ\", \n",
    "\"SIICIA, QUARTZ\", \n",
    "\"SIILICA AMORPHOUS PRECIPITATED AND GEL\", \n",
    "\"SIILICA, AMORPHOUS PRECIPITATED AND GEL\", \n",
    "\"SILCA\", \n",
    "\"SILCA, CRYSTALLINE-QUARTZ\", \n",
    "\"SILCON DIOXIDE\", \n",
    "\"SILCON DIOXIDE (SILICA SAND)\", \n",
    "\"SILCON DIOXIDE AMORPHOUS AS GLASS\", \n",
    "\"SILCON DIOXIDE, (AMORPHOUS AS GLASS)\", \n",
    "\"SILCON DIOXIDE, AMORPHOUS AS GLASS\", \n",
    "\"SILICA\", \n",
    "\"SILICA\", \n",
    "\"SILICA \", \n",
    "\"SILICA - CRYSTALLINE QUARTZ\", \n",
    "\"SILICA - CRYSTALLINE, QUARTZ\", \n",
    "\"SILICA (CRYSTALINE)\", \n",
    "\"SILICA (QUARTZ) SIO2\", \n",
    "\"SILICA , CRYSTALLINE - QUARTZ\", \n",
    "\"SILICA , CRYSTALLINE QUARTZ\", \n",
    "\"SILICA , CRYSTALLINE-QUARTZ\", \n",
    "\"SILICA AMORPHOUS\", \n",
    "\"SILICA AMORPHOUS - FUMED\", \n",
    "\"SILICA AMORPHOUS FUMED CRYSTAL FREE\", \n",
    "\"SILICA AMORPHOUS FUMED CRYSTALLINE - FREE\", \n",
    "\"SILICA AMORPHOUS FUSED\", \n",
    "\"SILICA AMORPHOUS PRECIPIDATED AND GEL\", \n",
    "\"SILICA AMORPHOUS PRECIPITATE\", \n",
    "\"SILICA AMORPHOUS PRECIPITATED\", \n",
    "\"SILICA AMORPHOUS PRECIPITATED AND GEL\", \n",
    "\"SILICA AMORPHOUS-FUMED\", \n",
    "\"SILICA CHRYSTALINE CRISTOBALITE\", \n",
    "\"SILICA CRISTOBALITE\", \n",
    "\"SILICA CRYATALLINE QUARTZ\", \n",
    "\"SILICA CRYSALLINE QUARTZ\", \n",
    "\"SILICA CRYSLLINE QUARTZ\", \n",
    "\"SILICA CRYSTALINE QUARTZ\", \n",
    "\"SILICA CRYSTALINE-CRISTOBALITE\", \n",
    "\"SILICA CRYSTALINE-QUARTZ\", \n",
    "\"SILICA CRYSTALLINE\", \n",
    "\"SILICA CRYSTALLINE - CRISTABOLITE\", \n",
    "\"SILICA CRYSTALLINE - QUARTZ\", \n",
    "\"SILICA CRYSTALLINE (QUARTZ)\", \n",
    "\"SILICA CRYSTALLINE CRISTOBALITE\", \n",
    "\"SILICA CRYSTALLINE -CRISTOBALITE\", \n",
    "\"SILICA CRYSTALLINE QUARTZ\", \n",
    "\"SILICA CRYSTALLINE QUARTZ\", \n",
    "\"SILICA CRYSTALLINE -QUARTZ\", \n",
    "\"SILICA CRYSTALLINE- QUARTZ\", \n",
    "\"SILICA CRYSTALLINE-CRISTBALITE\", \n",
    "\"SILICA CRYSTALLINE-CRISTOBALIT\", \n",
    "\"SILICA CRYSTALLINE-CRISTOBALITE\", \n",
    "\"SILICA CRYSTALLINE-CRISTOBALLITE\", \n",
    "\"SILICA CRYSTALLINE-CRISTOBALT\", \n",
    "\"SILICA CRYSTALLINE-CRITOBALITE\", \n",
    "\"SILICA CRYSTALLINE-QUARTZ\", \n",
    "\"SILICA CRYSTALLLINE-CRISTOBALITE\", \n",
    "\"SILICA CRYSTANLLLINE\", \n",
    "\"SILICA CYSTALLINE - CRISTOBALITE\", \n",
    "\"SILICA CYSTALLINE - QUARTZ\", \n",
    "\"SILICA CYSTALLINE CRISTOBALITE\", \n",
    "\"SILICA CYSTALLINE-QUARTZ\", \n",
    "\"SILICA DIOXIDE\", \n",
    "\"SILICA DISPERSION\", \n",
    "\"SILICA ENHANCED FLAKES WITH ACIDIC RESIDUAL\", \n",
    "\"SILICA GEL\", \n",
    "\"SILICA GEL, PPTD., CRYST.-FREE\", \n",
    "\"SILICA IN FORM OF QUARTZ\", \n",
    "\"SILICA ORGANIC POLYMER\", \n",
    "\"SILICA QUARTS\", \n",
    "\"SILICA QUARTZ\", \n",
    "\"SILICA QUARTZ\", \n",
    "\"SILICA QUARTZ SI02\", \n",
    "\"SILICA QUARTZ SIO2\", \n",
    "\"SILICA QUARTZ SIO2\", \n",
    "\"SILICA QUARTZ SIO2\", \n",
    "\"SILICA QUARTZ, SIO2\", \n",
    "\"SILICA QUARTZ, SIO2 \", \n",
    "\"SILICA QUARTZ, SIO2/PHENOLIC RESIN/HEXAMETHYLENETETRAMINE\", \n",
    "\"SILICA QUARTZ, SIO5\", \n",
    "\"SILICA QUARTZ/ SIO2\", \n",
    "\"SILICA SAND\", \n",
    "\"SILICA SAND \", \n",
    "\"SILICA SAND (QUARTZ)\", \n",
    "\"SILICA SAND(QUARTZ)\", \n",
    "\"SILICA SANDS (QUARTZ)\", \n",
    "\"SILICA SUBSTATE\", \n",
    "\"SILICA SUBSTITUTE WITH BONDED COATINGS\", \n",
    "\"SILICA SUBSTRAIT\", \n",
    "\"SILICA SUBSTRAT\", \n",
    "\"SILICA SUBSTRATE\", \n",
    "\"SILICA SUBSTRATTE\", \n",
    "\"SILICA SUBTRATE\", \n",
    "\"SILICA, AMORPHOUS\", \n",
    "\"SILICA, AMORPHOUS - FUMED\", \n",
    "\"SILICA, AMORPHOUS - FUMED \", \n",
    "\"SILICA, AMORPHOUS- FUMED\", \n",
    "\"SILICA, AMORPHOUS FUSED\", \n",
    "\"SILICA, AMORPHOUS PRECIPIDATED AND GEL\", \n",
    "\"SILICA, AMORPHOUS PRECIPITATE\", \n",
    "\"SILICA, AMORPHOUS PRECIPITATED\", \n",
    "\"SILICA, AMORPHOUS PRECIPITATED AND GEL\", \n",
    "\"SILICA, AMORPHOUS, FUMED, CRYSTAL FREE\", \n",
    "\"SILICA, AMORPHOUS, FUMED, CRYSTALLINE - FREE\", \n",
    "\"SILICA, AMORPHOUS-FUMED\", \n",
    "\"SILICA, AMPORHOUS - FUMED\", \n",
    "\"SILICA, CRISTOBALITE\", \n",
    "\"SILICA, CRYATALINE-QUARTZ\", \n",
    "\"SILICA, CRYATALLINE-QUARTZ\", \n",
    "\"SILICA, CRYATALLINE-QUARTZ \", \n",
    "\"SILICA, CRYSTALINE QUARTZ\", \n",
    "\"SILICA, CRYSTALINE, QUARTZ\", \n",
    "\"SILICA, CRYSTALINE-QUARTZ\", \n",
    "\"SILICA, CRYSTALLINE\", \n",
    "\"SILICA, CRYSTALLINE - QUARTZ\", \n",
    "\"SILICA, CRYSTALLINE - QUARTZ \", \n",
    "\"SILICA, CRYSTALLINE (CRISTOBALITE)\", \n",
    "\"SILICA, CRYSTALLINE (QUARTZ)\", \n",
    "\"SILICA, CRYSTALLINE- QUARTSQUARTZQUARTZ\",\n",
    "\"SILICA, CRYSTALLINE QUARTZ\", \n",
    "\"SILICA, CRYSTALLINE- QUARTZ\", \n",
    "\"SILICA, CRYSTALLINE, QUARTZ\", \n",
    "\"SILICA, CRYSTALLINE-/QUARTZ\", \n",
    "\"SILICA, CRYSTALLINE-CRISTOBALITE\", \n",
    "\"SILICA, CRYSTALLINE-QUARTZ\", \n",
    "\"SILICA, CRYTALLINE QUARTZ\", \n",
    "\"SILICA, CYSTALLINE - QUARTZ\", \n",
    "\"SILICA, CYSTALLINE-QUARTZ\", \n",
    "\"SILICA, QUART, SIO2\", \n",
    "\"SILICA, QUARTZ\", \n",
    "\"SILICA, QUARTZ SIO2\", \n",
    "\"SILICA, QUARTZ, S102\", \n",
    "\"SILICA, QUARTZ, SIO2\", \n",
    "\"SILICA, QUARTZ,SIO2\", \n",
    "\"SILICA,CRYSTALLINE QUARTZ\", \n",
    "\"SILICA,QUARTZ\", \n",
    "\"SILICA/CRYSTALLINE QUARTZ\", \n",
    "\"SILICA/QUARTZ/SIO2\", \n",
    "\"SILICA-CRYSTALLINE QUARTZ\", \n",
    "\"SILICA-CRYSTALLINE, QUARTZ\", \n",
    "\"SILICA-CRYSTALLINE-CRISTOBALITE\", \n",
    "\"SILICAN CRYSTALLINE-CRISTOBALITE\", \n",
    "\"SILICATE MINERALS - TS\", \n",
    "\"SILICIA CRYSTALINE-CRISTOBALITE\", \n",
    "\"SILICIA CRYSTALLINE QUARTZ\", \n",
    "\"SILICIA, CRYSTALINE QUARTZ\", \n",
    "\"SILICIA, CRYSTALLINE QUARTZ\", \n",
    "\"SILICON\", \n",
    "\"SILICON  DIOXIDE\", \n",
    "\"SILICON DIOXIDE\", \n",
    "\"SILICON DIOXIDE \", \n",
    "\"SILICON DIOXIDE (AMORPHOUS AS GLASS)\", \n",
    "\"SILICON DIOXIDE (AMORPHOUS AS GLASS)\", \n",
    "\"SILICON DIOXIDE (AMORPHOUS)\", \n",
    "\"SILICON DIOXIDE (CRYSTALLINE)\", \n",
    "\"SILICON DIOXIDE (QUARTZ)\", \n",
    "\"SILICON DIOXIDE (SILICA SAND)\", \n",
    "\"SILICON DIOXIDE (SILICIA SAND)\", \n",
    "\"SILICON DIOXIDE (SIO2)\", \n",
    "\"SILICON DIOXIDE AMORPHOUS AS GLASS\", \n",
    "\"SILICON DIOXIDE CHEMICALLY PREPARED\", \n",
    "\"SILICON DIOXIDE IN THE FORM OF CRYSTALLINE SILICA\", \n",
    "\"SILICON DIOXIDE IN THE FORM OF CRYSTALLINE SILICA (QUARTZ)\", \n",
    "\"SILICON DIOXIDE(AMORPHOUS AS GLASS)\", \n",
    "\"SILICON DIOXIDE(AMORPHOUS AS GLASS)\", \n",
    "\"SILICON DIOXIDE, (AMORPHOUS AS GLASS)\", \n",
    "\"SILICON DIOXIDE, AMORPHOUS - FUMED\", \n",
    "\"SILICON DIOXIDE, AMORPHOUS AS GLASS\", \n",
    "\"SILICON DIOXIDE, CHEMICALLY PREPARED\", \n",
    "\"SILICON DIOXIDE, QUARTZ\", \n",
    "\"SILICON DIOXIDE,(AMORPHOUS AS GLASS)\", \n",
    "\"SILICON DIOXIDE. (AMPORPHOUS AS GLASS)\", \n",
    "\"SILICON DIOXIDE...\", \n",
    "\"SILICON DIOXODE\", \n",
    "\"SILICON DIXIDE, (AMORPHOUS AS GLASS)\", \n",
    "\"SILICONE DIOXIDE\", \n",
    "\"SILICONE DIOXIDE \", \n",
    "\"SILICONE DIOXIDE, (AMORPHOUS AS GLASS)\", \n",
    "\"SILLICA AMORPHOUS PRECIPITATED AND GEL\", \n",
    "\"SILLICA CRYSTALLINE QUARTZ\", \n",
    "\"SILLICA, AMORPHOUS PRECIPITATED AND GEL\", \n",
    "\"SILLICA, CRYSTALLINE QUARTZ\", \n",
    "\"SILLICA, QUARTZ, SIO2\", \n",
    "\"SILLICON DIOXIDE\", \n",
    "\"SINTERED BAUXITE\", \n",
    "\"SIO2\", \n",
    "\"SYNTHETIC AMORPHOUS METAL SILICATE - TS\", \n",
    "\"SYNTHETIC AMORPHOUS SILICA (CRYSTALLINE SILICA-FREE)\", \n",
    "\"UEXITE\", \n",
    "\"ULEXIE\", \n",
    "\"ULEXITE\", \n",
    "\"ULEXITE\", \n",
    "\"ULEXITE \", \n",
    "\"ULEXITE - TS\", \n",
    "\"ULEXITE (BORATE SALT)\", \n",
    "\"ULEXITE CALCINED\", \n",
    "\"ULEXITE MINERAL\", \n",
    "\"ULEXITE MINERAL \", \n",
    "\"ULEXITE POWDER\", \n",
    "\"ULEXITE POWER\", \n",
    "\"ULEXITEULEXITEULEXITE\", \n",
    "\"ULEXITE, CALCINED\", \n",
    "\"ULIXITE MINERAL\", \n",
    "\"ULLEXITE\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "FFPropvol = FFPropvol[FFPropvol[\"IngredientName\"].isin(Proppantlist)]\n",
    "FFPropvol = FFPropvol[[\"APINumber\", \"PercentHFJob\"]]\n",
    "FFPropvol['ProppantPerc'] = FFPropvol[\"PercentHFJob\"].groupby(FFPropvol['APINumber']).transform('sum')\n",
    "FFPropvol = FFPropvol.drop_duplicates(subset=\"APINumber\", keep='first', inplace=False)\n",
    "FFPropvol = FFPropvol.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Put together your water volume/general info and propant dataframes\n",
    "FFCalcFrame = pd.merge(FFwatervol, FFPropvol, on='APINumber')\n",
    "\n",
    "#Clean up Kern County format\n",
    "FFCalcFrame['CountyName'] = FFCalcFrame['CountyName'].replace(' - 30','', regex=True)\n",
    "\n",
    "#Capitalize all Operators\n",
    "FFCalcFrame['OperatorName'] = FFCalcFrame['OperatorName'].str.upper()\n",
    "\n",
    "#Add a year column to break the data out by a timeframe\n",
    "FFCalcFrame['FracYear'] = FFCalcFrame['JobEndDate'].dt.year.astype(int)\n",
    "\n",
    "#Drop PercentHFJob column\n",
    "FFCalcFrame = FFCalcFrame.drop(['PercentHFJob'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get a gist of how much Proppant is being used by percent in your \"cleaner\" dataframe.\n",
    "#Some numbers are still running high, but we will clean that up in a bit.\n",
    "\n",
    "plt.hist(FFCalcFrame['ProppantPerc'], bins = 10, range = (0,100));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "FFCalcFrame[\"FracFluid_Weight_Pct\"] = 100-FFCalcFrame[\"ProppantPerc\"]\n",
    "FFCalcFrame[\"TBV_Water_Weight\"] = FFCalcFrame[\"TotalBaseWaterVolume\"]*8.34\n",
    "FFCalcFrame[\"Total_Frac_Weight_(lbs.)\"] = FFCalcFrame[\"TBV_Water_Weight\"]/(FFCalcFrame[\"FracFluid_Weight_Pct\"]/100.0)\n",
    "FFCalcFrame[\"Prop_Weight_(lbs.)\"] = FFCalcFrame[\"Total_Frac_Weight_(lbs.)\"]*(FFCalcFrame[\"ProppantPerc\"]/100.0)\n",
    "FFCalcFrame[\"FracFluidWeight_(lbs.)\"] = FFCalcFrame[\"TBV_Water_Weight\"]*(FFCalcFrame[\"FracFluid_Weight_Pct\"]/100.0)\n",
    "FFCalcFrame[\"Frac_Fluid_Barrels_(bbls)\"] = FFCalcFrame[\"TotalBaseWaterVolume\"]/42.0\n",
    "FFCalcFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "FFCalcFrame.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns; sns.set(style=\"ticks\", color_codes=True)\n",
    "\n",
    "FFCalcFrameNum = FFCalcFrame[['TotalBaseWaterVolume','TBV_Water_Weight', 'Prop_Weight_(lbs.)', 'FracFluidWeight_(lbs.)', 'Total_Frac_Weight_(lbs.)']]\n",
    "FFpairplt = sns.pairplot(FFCalcFrameNum)\n",
    "FFpairplt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-Looks like there may be some crazy numbers in the data set, so let's cut them.\n",
    "\n",
    "-We show below one way to statistically set where outliers start occuring by using the IQR and subtracting and adding 1.5 times the IQR from the respective quartiles\n",
    "\n",
    "-We know there are a lot of high prop/fluid wells out there (some with 50 million lbs of sand), and in a data set running from 2009 to present, there will be some definite huge swings on values.\n",
    "\n",
    "-It would be easier to make some calls on this if you had lateral lengths, but you can fix this up if you narrow your \n",
    "project down to a state or 2 and get that lateral data for the calculation. \n",
    "\n",
    "-So, from 0 to 23,238,640 gallons on total base water volume is the range."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Example of how to use IQR in Python\n",
    "\n",
    "#Get the interquartile range on Total Base Water Volume\n",
    "q75, q25 = np.percentile(FFCalcFrame['TotalBaseWaterVolume'], [75 ,25])\n",
    "TBV_iqr = q75 - q25\n",
    "\n",
    "#Use the iqr to set bounds on where to call outliers\n",
    "TBV_LB = q25 - (1.5*TBV_iqr)\n",
    "TBV_UB = q75 + (1.5*TBV_iqr)\n",
    "print(\"IQR:\",TBV_iqr, \"Lower Bound:\", TBV_LB, \"Upper Bound:\", TBV_UB)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "FFCalcFrame2 = FFCalcFrame[FFCalcFrame['TotalBaseWaterVolume'] <= TBV_UB]\n",
    "FFCalcFrame2.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "FFCalcFrameNum2 = FFCalcFrame2[['TotalBaseWaterVolume','TBV_Water_Weight', 'Prop_Weight_(lbs.)', 'FracFluidWeight_(lbs.)', 'Total_Frac_Weight_(lbs.)']]\n",
    "FFpairplt = sns.pairplot(FFCalcFrameNum2)\n",
    "FFpairplt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Much better, but that ProppantPerc, and therefore proppant weight, is still insanely high.  Do the same thing as before using the IQR."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PWPq75, PWPq25 = np.percentile(FFCalcFrame['Prop_Weight_(lbs.)'], [75 ,25])\n",
    "PWP_iqr = PWPq75 - PWPq25\n",
    "\n",
    "PWP_LB = q25 - (1.5*PWP_iqr)\n",
    "PWP_UB = q75 + (1.5*PWP_iqr)\n",
    "print(\"IQR:\",PWP_iqr, \"Lower Bound:\", PWP_LB, \"Upper Bound:\", PWP_UB)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "FFCalcFrame3 = FFCalcFrame2[FFCalcFrame2['Prop_Weight_(lbs.)'] <= PWP_UB]\n",
    "FFCalcFrame3.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "FFCalcFrameNum3 = FFCalcFrame3[['TotalBaseWaterVolume','TBV_Water_Weight', 'Prop_Weight_(lbs.)', 'FracFluidWeight_(lbs.)', 'Total_Frac_Weight_(lbs.)']]\n",
    "FFpairplt = sns.pairplot(FFCalcFrameNum3)\n",
    "FFpairplt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "FFCalcFrame3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}