<jupyter_start><jupyter_text>Geology and Production Data Munging First we will import pandas so we can read in the data and clean it up. The end goal is that we want a `DataFrame` with some features related to completions, geology, location, and other variables. We ultimately want to predict the first 18 months of oil production from the horizontal wells, so that will have to be in the `DataFrame` as well<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>We first read in the excel workbook using `pandas` `pd.ExcelFile` and then print out a list of the different sheet names in the workbook using `sheet_names`<jupyter_code>xls = pd.ExcelFile(r"Appendix1_DataTables.xlsx")
xls.sheet_names  # list out the names of the different sheets in the excel file<jupyter_output><empty_output><jupyter_text>From the `sheet_names` call we see that there are sheets for completions, production, oil gravity, composition, gas oil ratios, pressure and temperature. Let's use `parse` to make a `dataframe` for each sheet<jupyter_code>completions = xls.parse(1)  # make a dataframe from the completions sheet
production = xls.parse(2)  # make a dataframe from the productions sheet
gravity = xls.parse(4)  # make a dataframe from the API gravity sheet
composition = xls.parse(
    5, header=1
)  # make a dataframe from the gas composition sheet
gor = xls.parse(6)  # make a dataframe from the gas oil ratio sheet
pressure = xls.parse(7)  # make a dataframe from the pressure sheet
temperature = xls.parse(8)  # make a dataframe from the temperature sheet<jupyter_output><empty_output><jupyter_text>Now let's select which columns we want from each dataframe. Here we are going to select the `'APINO'` which is the API number or unique identifier for each well, and some features. We want to select the API number so we can merge everything into one dataframe. From the production dataframe we want `'First 18 months oil (bbl)'` which is the feature we are going to try and predict from the rest of the features. Other features we are selecting include oil gravity, well surface location, well bottom location, well type, reservoir name, gas oil ratio, pressure gradient, and reservoir temperature. We are making subsets of the original dataframes in this next cell and then combining them into a list.<jupyter_code>production = production[
    ["APINO", "First 18 months oil (bbl)"]
].copy()  # this is the feature we are going to predict
gravity = gravity[
    ["APINO", "IP oil API gravity (°)"]
].copy()  # oil gravity feature
composition = composition.drop(
    [
        "API number",
        "Surface hole latitude (NAD83)",
        "Surface hole longitude (NAD83)",
        "Well type",
        "WSGS reservoir",
    ],
    axis=1,
).copy()
# composition is the geochemistry of the oil, it's easier to just drop features for this one than select all the features
pressure = pressure[
    ["APINO", "Pressure gradient (psi/ft)"]
]  # pressure gradient for each well
temperature = temperature[
    ["APINO", "Calculated top of reservoir temperature (°F)"]
].copy()  # top of reservoir temp feature<jupyter_output><empty_output><jupyter_text>The gas oil ratio `dataframe` has some issues with mixed datatypes in that we have numbers for some rows and strings for other rows. We will clean this up in the next cell and make sure the `'Average gas-oil ratio (ft3/bbl)'` feature is composed of numbers and not mixed datatypes<jupyter_code>gor = gor[["APINO", "Average gas-oil ratio (ft3/bbl)"]].copy()
gor2 = gor[
    ~gor["Average gas-oil ratio (ft3/bbl)"].isin(["*IHS proprietary data"])
].copy()  # this one needed some thorough cleaning because of mixed data types
gor2["Average gas-oil ratio (ft3/bbl)"] = pd.to_numeric(
    gor2["Average gas-oil ratio (ft3/bbl)"]
)  # make sure all entries are numbers
dataframes = [
    completions,
    production,
    gravity,
    gor2,
    composition,
    temperature,
    pressure,
]  # this is a list of all the dataframes with the features we want to use<jupyter_output><empty_output><jupyter_text>Now let's merge the dataframes together using the list from the cell above. We do this using the reduce function https://docs.python.org/2/library/functions.htmlreduce<jupyter_code>from functools import reduce

df_final = reduce(
    lambda left, right: pd.merge(left, right, how="left", on="APINO"),
    dataframes,
)
df_final.head()  # take a quick look to see what the data looks like<jupyter_output><empty_output><jupyter_text>At first glance I see a lot of NaN values in the dataset, we don't have a complete dataset for the gas composition. This means we only have a few wells with that data. We can either impute the data using a mean or median imputation, or we can drop some of the features. To decide which features to drop we will set a threshold and if a feature is missing more than that threshold we will drop that feature from the dataframe. Here I use a 25% threshold to drop features (columns) missing more than 25% of their data.<jupyter_code>thresh = len(df_final) * 0.25
df_final.dropna(
    thresh=thresh, axis=1, inplace=True
)  # axis 1 means drop columns, inplace means to modify the dataframe
df_final.columns  # just a quick sanity check to see what features we still have left in the dataframe<jupyter_output><empty_output><jupyter_text>After thresholding and dropping some features we still have `NaN` values, and we can impute those values using the `mean` for each feature<jupyter_code>impute_df = df_final.fillna(df_final.mean())
impute_df.head()  # another check to see what the dataframe looks like<jupyter_output><empty_output><jupyter_text>How are we going to deal with the non-numeric features like `'Company'` and `'WSGS reservoir'`? We need to convert them to a numeric label using a label encoder. We will use `preprocessing.LabelEncoder` to convert categorical entries to numerical values. So to do this let's import the `preprocessing` module and fit the `LabelEncoder` to the categorical type data. We will then join the numeric `dataframe` and the categorical `dataframe` into one encoded `dataframe`<jupyter_code>from sklearn import preprocessing

le = preprocessing.LabelEncoder()

X_numeric = impute_df.select_dtypes(include=["number"]).copy()
X_categorical = impute_df.select_dtypes(include=["object"]).apply(
    le.fit_transform
)
df_encoded = X_numeric.join(X_categorical, how="outer")<jupyter_output><empty_output><jupyter_text>And here we have our cleaned, organized, imputed `DataFrame` with all our features of interest ready for mahcine learning. Next week, we will dive into using scikit-learn to determine which features are the most important when predicting the first 18 months of oil production in the Turner Sandstone<jupyter_code>df_encoded.head()<jupyter_output><empty_output><jupyter_text>Let's save our munged data to a `.csv` file so we can read it in next week to make predictions<jupyter_code>df_encoded.to_csv("organized_turner_data.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07659331237496428, 'fraction_numerical': 0.005858816804801372, 'mean_word_length': 4.946474086661002, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 45, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '237407', 'license': 'Unlicense', 'n_tokens_mistral': 2070, 'n_tokens_neox': 1978, 'n_words': 895, 'path': 'Turner Sandstone/Turner data munging.ipynb', 'repo_name': 'jessepisel/5minutesofpython', 'size': '6998'}