<jupyter_start><jupyter_text>IntroductionIn this notebook we will work with both the Exploration wellbore and the lithostratigraphy datasets from Norwegian Petroleum Directorate (NPD). The wellbore datasets are public domain. They are available here: https://data.norge.no/data/oljedirektoratet/br%C3%B8nnbane-wellbore. The datasets are available via an API which we will use.  Description of the different data 'wrangling' steps, until ultimately generating a shapefile (displaying on map):We are first going to get the Exploration dataset, select columns and the wells for North Sea and Norwegian Sea only.The lithostratigraphy dataset has information about Formations and Groups for each well. It means that in this dataset, each well will be represented by several rows, each row being one Formation with top and bottom depths. We are also going to select specific columns and only North Sea and Norwegian Sea areas. We are going to merge a selection of columns from the Exploration dataset together with the lithostratigraphy dataset.The lithostratigraphy dataset does not have Geological ages (Paleocene, Cretaceous, Jurassic, Triassic, Permian). We created a new table for Groups with matching Ages, before adding this information to our lithostratigraphy dataset (using pandas 'map'). The resulting is a dataframe for all Explorations wells in North Sea and Norwegian Sea, with Formation, Group and Age for each row. Each row also has coordinates, which will allow us to export to a point SHAPEFILE and examine our dataset in a geospatial context, here we are using MAPBOX.<jupyter_code>%matplotlib inline
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

import requests
import json
import re

import geopandas
from shapely.geometry import Point
from IPython.display import IFrame

from mapboxgl.utils import *
from mapboxgl.viz import *

import altair as alt
import seaborn as sns
# add the line below if you use notebooks and comment it if you use jupyter lab
# alt.renderers.enable('notebook')<jupyter_output><empty_output><jupyter_text>API connection - check for statusMake a general get request, print out status code.Status code = 200 means API connection is up and running.<jupyter_code>requests.get('http://data.norge.no/api/dcat/870917732/data.json')<jupyter_output><empty_output><jupyter_text>Get request - Exploration datasetThe dataset is paginated. The first page will tell us how many pages there are in total, and how many rows for the whole dataset. We go through all the page of the dataset and create a pandas dataframe from the json file.<jupyter_code>dataset_explo = requests.get('http://hotell.difi.no/api/json/npd/wellbore/exploration?page=1')
json_dataset_explo = dataset_explo.json()
print(f'number of pages: {json_dataset_explo["pages"]}')
print(f'number of rows:  {json_dataset_explo["posts"]}')
url_dataset ='http://hotell.difi.no/api/json/npd/wellbore/exploration'

def get_all_dataset():
    '''looping through all pages,
    append in JSON dictionary,
    create dataframe
    '''
    
    page = json_dataset_explo['pages']
    all_data_list = []
    for i in range(0, page):
        url = url_dataset + f'?page={i+1}'
        dataset_all = requests.get(url)
        dataset_all_json = dataset_all.json()
        number_of_entries = len(dataset_all_json['entries'])

        for entry in range(0, number_of_entries):
            all_data_list.append(dataset_all_json['entries'][entry])

    df_all_explo = pd.DataFrame(all_data_list)
    return df_all_explo
df_all_explo = get_all_dataset()
df_all_explo.shape<jupyter_output><empty_output><jupyter_text>Select columns out in a new dataframe (from 81 columns down to 22)<jupyter_code>df_all_explo_sel_col = df_all_explo.filter(['wlbWellboreName', 'wlbEwDesDeg','wlbNsDecDeg', 'wlbEntryYear',
                                                'wlbCompletionYear','wlbEwUtm','wlbNsUtm','wlbUtmZone',
                                                'wlbContent', 'wlbDiscoveryWellbore',
                                                'wlbTotalDepth','wlbAgeAtTd','wlbFormationAtTd',
                                                'wlbAgeWithHc1', 'wlbAgeWithHc2', 'wlbAgeWithHc3',
                                                'wlbFormationWithHc1', 'wlbFormationWithHc2', 'wlbFormationWithHc3',
                                                'wlbDrillingOperator', 'wlbMainArea', 'datesyncNPD'],
                                                axis=1)<jupyter_output><empty_output><jupyter_text>We select wells only within North Sea and Norwegian Sea areas<jupyter_code>df_explo_NTS_NS = df_all_explo_sel_col.loc[df_all_explo_sel_col['wlbMainArea'].isin(['NORTH SEA','NORWEGIAN SEA'])]
df_explo_NTS_NS.shape
df_explo_NTS_NS.head()<jupyter_output><empty_output><jupyter_text>---  Get request - Lithostratigraphy datasetThe dataset is paginated. The first page will tell us how many pages there are in total, and how many rows for the whole dataset. We go through all the page of the dataset and create a pandas dataframe from the json file.<jupyter_code>dataset_strati = requests.get('http://hotell.difi.no/api/json/npd/wellbore/with-lithostratigraphy?page=1')
json_dataset_strati = dataset_strati.json()
print(f'number of pages: {json_dataset_strati["pages"]}')
print(f'number of rows:  {json_dataset_strati["posts"]}')
url_dataset ='http://hotell.difi.no/api/json/npd/wellbore/with-lithostratigraphy'

def get_all_dataset():
    '''looping through all pages,
    append in JSON dictionary,
    create dataframe
    '''
    
    page = json_dataset_strati['pages']
    all_data_list = []
    for i in range(0, page):
        url = url_dataset + f'?page={i+1}'
        dataset_all = requests.get(url)
        dataset_all_json = dataset_all.json()
        number_of_entries = len(dataset_all_json['entries'])

        for entry in range(0, number_of_entries):
            all_data_list.append(dataset_all_json['entries'][entry])

    df_all_explo_strati = pd.DataFrame(all_data_list)
    return df_all_explo_strati
df_all_explo_strati = get_all_dataset()
df_all_explo_strati.shape<jupyter_output><empty_output><jupyter_text>Re-arrange colums to make it more readable<jupyter_code>df_all_explo_strati = df_all_explo_strati[['wlbName', 'wlbNpdidWellbore', 'lsuTopDepth','lsuBottomDepth',
                                           'lsuName', 'lsuNameParent', 'lsuNpdidLithoStrat',
                                           'lsuNpdidLithoStratParent', 'lsuLevel',
                                           'IsuWellboreUpdatedDate', 'datesyncNPD']]
df_all_explo_strati.head()<jupyter_output><empty_output><jupyter_text>Fill all empty values with NaN<jupyter_code>df_all_explo_strati = df_all_explo_strati.replace('', np.nan)<jupyter_output><empty_output><jupyter_text>Merge with coordinates and region (North Sea, Norwegian Sea, Barents Sea) from dataframe df_all_explo_sel_col<jupyter_code>df_all_explo_strati_merge = pd.merge(df_all_explo_strati, 
                              df_all_explo_sel_col[['wlbEwDesDeg', 'wlbNsDecDeg', 'wlbEwUtm', 'wlbNsUtm',
                                                   'wlbWellboreName', 'wlbMainArea']], how='left',
                              left_on='wlbName', right_on='wlbWellboreName')
df_all_explo_strati_merge.head()<jupyter_output><empty_output><jupyter_text>Do we have any NaNs in the merged dataframe columns? Where and how many?<jupyter_code>df_all_explo_strati_merge.isnull().sum()<jupyter_output><empty_output><jupyter_text>Let's have a closer look at the 126 entries with NaNs which appeared after we merged the two dataframes.<jupyter_code>df_all_explo_strati_merge_coord_nan = df_all_explo_strati_merge.loc[
                                        df_all_explo_strati_merge['wlbWellboreName'].isnull()]
df_all_explo_strati_merge_coord_nan.head()<jupyter_output><empty_output><jupyter_text>We seem to have a nomentclature issue between 'wlbName' and 'wlbWellboreName' for the following wells:<jupyter_code>df_all_explo_strati_merge_coord_nan['wlbName'].unique()<jupyter_output><empty_output><jupyter_text>The missing wells are not in the original Exploration wells dataset, therefore we remove them from the merged dataframe.<jupyter_code>df_all_explo_strati_merge = df_all_explo_strati_merge.dropna(subset=['wlbWellboreName'])
df_all_explo_strati_merge.shape<jupyter_output><empty_output><jupyter_text>Let's remove the column 'IsuWellboreUpdatedDate' which is almost all empty<jupyter_code>df_all_explo_strati_merge = df_all_explo_strati_merge.drop('IsuWellboreUpdatedDate', axis = 1)<jupyter_output><empty_output><jupyter_text>Let's rework our datatypes to float and integer<jupyter_code>df_all_explo_strati_merge.dtypes
float_col = ['lsuTopDepth', 'lsuBottomDepth', 'wlbEwDesDeg', 'wlbNsDecDeg', 'wlbEwUtm', 'wlbNsUtm']
for f in float_col:
    df_all_explo_strati_merge[f] = df_all_explo_strati_merge[f].astype(np.float64)

int_col = ['wlbNpdidWellbore', 'lsuNpdidLithoStrat']
for i in int_col:
    df_all_explo_strati_merge[i] = df_all_explo_strati_merge[i].astype(int)
df_all_explo_strati_merge.head()
df_all_explo_strati_merge['wlbMainArea'].value_counts()<jupyter_output><empty_output><jupyter_text>Let's remove all our BARENTS SEA rows from 'df_all_explo_strati_merge'<jupyter_code>df_all_explo_strati_merge_NTS_NS = df_all_explo_strati_merge.loc[
    df_all_explo_strati_merge['wlbMainArea'].isin(['NORTH SEA','NORWEGIAN SEA'])]
df_all_explo_strati_merge_NTS_NS.shape
df_all_explo_strati_merge_NTS_NS.dtypes
df_all_explo_strati_merge_NTS_NS['wlbMainArea'].value_counts()
len(df_all_explo_strati_merge_NTS_NS['wlbName'].unique())<jupyter_output><empty_output><jupyter_text>We have now 1616 unique wells in our dataframe with lithostratigraphies, for North Sea and Norwegian Sea.<jupyter_code>df_all_explo_strati_merge_NTS_NS.head()<jupyter_output><empty_output><jupyter_text>--- Let's make a copy of our lithostratigraphy dataframe and shorten its name, then sort it by wlbName and IsuTopDepth and from South to North with wlbNsDecDeg, and finally re-index the dataframe<jupyter_code>df_strati_merge_NTS_NS_sort = df_all_explo_strati_merge_NTS_NS.copy()
df_strati_merge_NTS_NS_sort = df_strati_merge_NTS_NS_sort.sort_values(['wlbName', 'lsuTopDepth', 'wlbNsDecDeg'])
df_strati_merge_NTS_NS_sort = df_strati_merge_NTS_NS_sort.reset_index(drop=True)
df_strati_merge_NTS_NS_sort.head()
df_strati_merge_NTS_NS_sort.shape<jupyter_output><empty_output><jupyter_text>We will now remove all rows with 'IsuLevel' == GROUP so that we have only Formations and their corresponding depths. Groups names are in the column 'IsuNameParent' as well. We'll keep that column. We then re-index the dataframe.<jupyter_code>df_strati_merge_NTS_NS_formations = df_strati_merge_NTS_NS_sort[df_strati_merge_NTS_NS_sort.lsuLevel != 'GROUP']
df_strati_merge_NTS_NS_formations = df_strati_merge_NTS_NS_formations.reset_index(drop=True)
df_strati_merge_NTS_NS_formations.head()<jupyter_output><empty_output><jupyter_text>How many wells do we have now?<jupyter_code>len(df_strati_merge_NTS_NS_formations.wlbName.unique())<jupyter_output><empty_output><jupyter_text>We see that we have less wells now that we have removed the 'Group' level in column 'lsuLevel'. It means that some wells had only Groups without any Formations. let's check out how many Groups we have in 'IsuNameParent'<jupyter_code>df_strati_merge_NTS_NS_formations['lsuNameParent'].unique()<jupyter_output><empty_output><jupyter_text>Let's create a dictionary with Age and Groups (from 'IsuNameParent' column), and create a new dataframe with Group and Age.<jupyter_code>age_dict = {'Paleocene': ['ROGALAND GP', 'HORDALAND GP', 'NORDLAND GP', 'HEIMDAL FM', 'FLADEN GP'],
            'Cretaceous': ['SHETLAND GP', 'CROMER KNOLL GP'],
            'Jurassic': ['VIKING GP', 'BRENT GP', 'DUNLIN GP', 'TYNE GP', 'VESTLAND GP', 'BOKNFJORD GP',
                        'FANGST GP', 'BÅT GP'],
            'Triassic': ['HEGRE GP', 'STATFJORD GP'],
            'Permian': ['ZECHSTEIN GP']}
age_dat = [[group, age]
           for age, groups in age_dict.items()
           for group in groups]
df_ages = pd.DataFrame(age_dat, columns=['group', 'age'])
df_ages
# let's first copy the well lithostratigraphy frame
df_strati_NTS_NS_age = df_strati_merge_NTS_NS_formations.copy()
df_strati_NTS_NS_age.shape<jupyter_output><empty_output><jupyter_text>We are now adding 'age' from dataframe df_ages into our lithostratigraphy dataframe 'df_strati_NTS_NS'<jupyter_code>df_strati_NTS_NS_age['age'] = df_strati_NTS_NS_age['lsuNameParent'].map(df_ages.set_index('group')['age'])<jupyter_output><empty_output><jupyter_text>Let's place the new column 'age' besides column 'lsuNameParent'.<jupyter_code>df_strati_NTS_NS_age = df_strati_NTS_NS_age[['wlbName', 'wlbNpdidWellbore', 'lsuTopDepth', 'lsuBottomDepth',
       'lsuName', 'lsuNameParent', 'age', 'lsuNpdidLithoStrat',
       'lsuNpdidLithoStratParent', 'lsuLevel', 'datesyncNPD', 'wlbEwDesDeg',
       'wlbNsDecDeg', 'wlbEwUtm', 'wlbNsUtm', 'wlbWellboreName', 'wlbMainArea']]
df_strati_NTS_NS_age.head()<jupyter_output><empty_output><jupyter_text>Let's have an overview over our well distribution for each age.<jupyter_code># how many wells for each age?
df_count = df_strati_NTS_NS_age.groupby('age')['wlbName'].unique()
df_count = pd.DataFrame(df_count).reset_index(drop=False)
df_count['nb_of_wells'] = df_count['wlbName'].str.len()
df_count
alt.Chart(df_count).mark_bar().encode(
   x = 'nb_of_wells',
   y = alt.Y(
            'age',
            sort=alt.EncodingSortField(
            field='nb_of_wells',  # The field to use for the sort
            op='sum',  # The operation to run on the field prior to sorting
            order='descending'  # The order to sort in
        )
    ),
    color=alt.value('brown')
).properties(height=150, width=500)<jupyter_output><empty_output><jupyter_text>---  Geospatialisation - converting our dataframe to ultimately a shapefile.<jupyter_code># creating a tuple with long/lat in DD
df_strati_NTS_NS_age_DD = df_strati_NTS_NS_age.copy()

df_strati_NTS_NS_age_DD['Coordinates'] = list(zip(df_strati_NTS_NS_age_DD.wlbEwDesDeg, 
                                                  df_strati_NTS_NS_age_DD.wlbNsDecDeg))

# Tuples to Point
df_strati_NTS_NS_age_DD['Coordinates'] = df_strati_NTS_NS_age_DD['Coordinates'].apply(Point)

# Now, we can create the GeoDataFrame by setting geometry with the coordinates
df_strati_NTS_NS_age_DD = geopandas.GeoDataFrame(df_strati_NTS_NS_age_DD, geometry='Coordinates')<jupyter_output><empty_output><jupyter_text>We can now create 2 separate dataframes, one for North Sea wells, and the other for Norwegian Sea wells:<jupyter_code>df_strati_NTS_age_DD = df_strati_NTS_NS_age_DD.loc[df_strati_NTS_NS_age_DD.wlbMainArea=='NORTH SEA']
df_strati_NS_age_DD = df_strati_NTS_NS_age_DD.loc[df_strati_NTS_NS_age_DD.wlbMainArea=='NORWEGIAN SEA']<jupyter_output><empty_output><jupyter_text>Quick display of the geodataframe with both North Sea and Norwegian Sea - This is to QC the general position of our wells<jupyter_code>df_strati_NTS_NS_age_DD.plot(figsize=(8,8), color='grey', markersize = 1)<jupyter_output><empty_output><jupyter_text>We can now save our dataframe to SHAPEFILE:<jupyter_code># create a shapefile from df_strati_NS_age_DD
# df_strati_NTS_NS_age_DD.to_file('../shapefiles/strati_NS_age_DD.shp',driver ='ESRI Shapefile')<jupyter_output><empty_output><jupyter_text>---  Using MAPBOX It is now possible to display our shapefile in MAPBOX. Below is an example showing all wells with Triassic. More about MapBox here: www.mapbox.com.<jupyter_code>IFrame(src='https://api.mapbox.com/styles/v1/annee/cjtxe6kxk2n9v1fpiqravigpi.html?fresh=true&title=true&access_token=<KEY>.Iq8vP18GcAukiVpWNqJ5Pg#5.2/59.5/5.0/0', width=900, height=700)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09307921230925094, 'fraction_numerical': 0.00481225859558032, 'mean_word_length': 5.100424874468907, 'pattern_counts': {'":': 0, '<': 122, '<?xml version=': 0, '>': 122, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 25, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '30', 'dir': 'jupyter-structured-clean-dedup', 'id': '115153', 'license': 'MIT', 'n_tokens_mistral': 5639, 'n_tokens_neox': 5428, 'n_words': 1187, 'path': 'notebooks/geospatialisation_well_data_norge.ipynb', 'repo_name': 'AnneEstoppey/GeoToolsPython', 'size': '15851'}