# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: 'Python 3.6.7 64-bit (''energyanalytics'': conda)'
#     language: python
#     name: python36764bitenergyanalyticsconda420549f17c0a492fa1ad380efb26bb87
# ---

# +
import pandas as pd
import datetime
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

import missingno as msno 

import re
# -

originalDF = pd.read_excel('UT Completion and Sequencing.xlsx')

# ## Cleaning Data nan values

# +
## Compl. Type has a couple nan values
originalDF['Compl. Type'] = originalDF['Compl. Type'].replace("No Data", np.nan).replace("unknown, probably hybrid", np.nan).replace("Not indicated",np.nan)


# -

DatabaseDF = originalDF

# ## Using Missingno to visualize incomplete Data

msno.matrix(DatabaseDF)

# Our data appears to be relatively complete at first glace. The only variables that have noticibly many missing values are : Stages, Best# Mo BOPD, Compl. Type, and Fluid Type from DI.
#
# It should be noted however that for Stages we are not sure if 0 should be interpreted as a NaN value

msno.heatmap(DatabaseDF)

# I wasn't expecting this to give meaningfull information, since really we don't care why the data isn't present. It is interesting to note that higher Best# Mo BOPD does not allways gaurentee the existence of lower Month BOPD, which means that there are some interesting holes in our data.

# That's kind of all that Missingno can do, if I'm being perfectly honest its not super usefull for us

# ## Variable comparabilities

# + tags=["outputPrepend"]
## 2 variable comparison 


colList = list(DatabaseDF.columns.values)
totalEntries = len(DatabaseDF)

Heatmap = []

for column in colList:
    rowList = []
    for row in colList:
        missing = len((DatabaseDF[(DatabaseDF[column].isna()) | DatabaseDF[row].isna()]))
        percentAvailable = 1 - (missing/totalEntries)
        rowList.append(percentAvailable)
    
    Heatmap.append(rowList)

mask = np.zeros_like(Heatmap)
mask[np.triu_indices_from(mask)] = True


heatmapDF = pd.DataFrame(Heatmap, columns = colList, index = colList)

with sns.axes_style("white"):
    plt.subplots(figsize = (15,10))
    sns.heatmap(heatmapDF, annot = True, mask = mask, square = True)



# -

# This table shows us what percent of total entries have both values, which gives us an idea of what values we will be able to compare. For the most part most 2 value pairs are present within the entire dataset except for the best Mo production, those are the main soucre of error.

# ## Compl. Type Repurposing

# +
def completionTypeParser(string, stages):
    if (string == "nan"): return np.nan, np.nan, np.nan, np.nan, np.nan, np.nan

    if (stages == "nan"): stages = 0

    integers = list(map(int, re.findall(r'\d+',string)))
    
    integerCount = len(integers)

    typeString = ''
    typeCount = [np.nan] * 5

    if (integerCount == 0):
        ## need to determine if uncountable

        if "and" not in string:
            ## it is uncountable
            if "orts" in string: 
                typeString = "Frac Ports"
                typeCount[2] = stages
            elif "Sleeves" in string:
                typeString = "Sleeves"
                typeCount[0] = stages
            elif "&" in string:
                typeString = "P & P"
                typeCount[1] = stages
            elif "CT" in string:
                typeString = "CT"
                typeCount[4] = stages
            elif "Tubing" in string:
                typeString = "CT"
                typeCount[4] = stages
            else:
                typeString = string
        else:
            typeString = "Sleeves and P & P"

    elif (integerCount == 2):
        ## Determine which to set

        ## Need to determine if this is worth checking
        ##if (sum(integers) != stages):
            ##print(string , "     ", stages)
            ##raise Exception("Stages should equal sum of completions")

        if "&" in string:
            ##P&P type
            if "eeve" in string:
                typeString = "Sleeves and P & P"
                typeCount[0] = integers[0]
                typeCount[1] = integers[1]
            elif "eater" in string:
                typeString = "Repeater Ports and P & P"
                typeCount[3] = integers[0]
                typeCount[1] = integers[1]
            elif "ort" in string:
                typeString = "Frac Ports and P & P"
                typeCount[2] = integers[0]
                typeCount[1] = integers[1]
            else:
                typeString = "P & P and CT"
                typeCount[1] = integers[0]
                typeCount[4] = integers[1]
        else:
            ##not P&P type
            typeString = "Frac Ports and Repeater Ports"
            typeCount[2] = integers[0]
            typeCount[3] = integers[1]
    else:
        raise Exception("String should contain either two or no numbers")
    
    return typeString, typeCount[0], typeCount[1], typeCount[2], typeCount[3], typeCount[4]
    


# +
completionTypeData = []

for index, row in DatabaseDF.iterrows():
    parseResults = completionTypeParser(str(row['Compl. Type']), row['Stages'])
    completionTypeData.append(parseResults);
    
completionTypeDF = pd.DataFrame(completionTypeData, columns= ["Completion Type", "Sleeves", "P&P", "Frac Ports", "Repeater Ports", "CT"])

DatabaseDF = DatabaseDF.join(completionTypeDF)
# -

DatabaseDF.head()

# ## Adding the Year the well was drilled

# Adding a column recording the year from the date for potentially future clustering of the data based upon the year the well was drilled.

DatabaseDF["Year Drilled"] = DatabaseDF["Date Fracd"].dt.year

DatabaseDF.head()

# ## Exporting to CSV

DatabaseDF.to_csv('CleanedDataset.csv')

StarcoderdataJupyterScriptsDedupFiltered