Bottomhole Pressure Example

In this guide, our main focus is on leveraging the whitson⁺ API for uploading bottomhole pressure related data. The example can be broken down in four steps.

Uploading Wells and Wellheader Data, e.g. wellnames, UWI, etc.
Uploading Wellbore Data:, e.g. well deviation surveys, top/bottom perforations and
Uploading Production Data:, e.g. oil, gas and water rates.
Running BHP Calculations:, e.g. to be used in FMB, RTA, nodal or numerical modeling.

BHP Input Data Schema Visualization

This data schema shows how the different data tables are related.

Difference between WELL_ID and WELL_DATA_ID

WELL_ID and WELL_DATA_ID serve different purposes. WELL_ID uniquely identifies a specific well, while WELL_DATA_ID is assigned to each wellbore configuration. Over time, multiple configurations can occur. For instance, initially flowing through casing and later installing tubing would result in two separate wellbore configurations, each with its own WELL_DATA_ID and installation DATE

Connect BHP data to whitson⁺: Python Example

What does the bhp.py file do?

This file shows an example of how one connect BHP data to the whitson⁺ domain. The file uses a helper class WhitsonConnection in whitson_connect.py provided here. The example data is from a standard whitson⁺ MASS UPLOAD example that can be downloaded here. Modifying the code to e.g. pulling data from a SQL table is straight forward, as exemplified below.

Connect BHP data to whitson⁺: Python Example

import whitson_connect
import pandas as pd

CLIENT = "your_domain_here" #This is the company suffix in your whitson urls ex. 'courses' in courses.whitson.com
CLIENT_ID = "your_client_id_here" # Available on request
CLIENT_SECRET = "your_client_secret" # Available on request
PROJECT_ID = "your_project_id_here" # This is the int numeric value of the project, available from the URL like '638' in https://courses.whitson.com/fields/5/projects/638/

whitson_connection = whitson_connect.WhitsonConnection(
    CLIENT, CLIENT_ID, CLIENT_SECRET
)

whitson_connection.access_token = whitson_connection.get_access_token_smart()

excel_file = 'docs\\files\\onboarding\\mass-upload-example\\whitson+_mass_well_upload_field_Eagle-Ford_20231110.xlsx'

df = pd.read_excel(excel_file, sheet_name='Well Data', header=1).drop(0)

# ------------------------------------------------------------------------------------------------------------------
# 1. Upload Wells
# ------------------------------------------------------------------------------------------------------------------

for index, row in df.iterrows():
        well_name = row['Well Name']
        uwi_api = row['UWI | API']

        # Add more things here if wanted, all attributes here: https://internal.whitson.com/api-external/swagger/#/Base%20Data/post_api_external_v1_wells
        well_info = {"project_id": PROJECT_ID, "name": well_name, "uwi_api": uwi_api}
        whitson_connection.create_well(payload=well_info)

whitson_wells = whitson_connection.get_wells(PROJECT_ID)

# ------------------------------------------------------------------------------------------------------------------
# 2.1 Upload well deviation surveys
# ------------------------------------------------------------------------------------------------------------------

deviation_df = pd.read_excel(excel_file, sheet_name='Deviation Survey Data').drop(0)

payloads_by_well = {}

for index, row in deviation_df.iterrows():
    well_name = row['Well Name']
    md_value = row['MD']
    tvd_value = row['TVD']

    if well_name in payloads_by_well:
        payloads_by_well[well_name].append({"md": md_value, "tvd": tvd_value})
    else:
        payloads_by_well[well_name] = [{"md": md_value, "tvd": tvd_value}]

for well_name, payload in payloads_by_well.items():
    well_id = whitson_connection.get_well_id_by_wellname(whitson_wells, well_name)
    whitson_connection.edit_well_deviation_survey(well_id=well_id, payload=payload)

# ------------------------------------------------------------------------------------------------------------------
# 2.2 Upload top and bottom perforations
# ------------------------------------------------------------------------------------------------------------------

df_perf = pd.read_excel(excel_file, sheet_name='Wellbore Data').drop(0) 

processed_wells = {}

for index, row in df_perf.iterrows():
    well_name = row['Well Name']
    top_md = row['Top Perforation MD']
    bottom_md = row['Bottom Perforation MD']

    if well_name in processed_wells:
        continue

    payload = {
        "top_perforation_md": top_md,
        "bottom_perforation_md": bottom_md
    }

    well_id = whitson_connection.get_well_id_by_wellname(whitson_wells, well_name)

    whitson_connection.edit_perf_interval(well_id, payload=payload)

    processed_wells[well_name] = True

# ------------------------------------------------------------------------------------------------------------------
# 2.3 Upload wellbore configs
# ------------------------------------------------------------------------------------------------------------------

df_perf = pd.read_excel(excel_file, sheet_name='Wellbore Data').drop(0) 

well_payloads = {}

for index, row in df_perf.iterrows():
    well_name = row['Well Name']
    use_from_date = row['Use from Date'].strftime("%Y-%m-%d") if pd.notna(row['Use from Date']) else None
    flow_path = row['Flowpath'].lower().replace(" ", "_")
    lift_method = row['Lift Method'].lower().replace(" ", "_") if pd.notna(row['Lift Method']) else 'none'
    gas_lift_config = row.get('Gas Lift Configuration', 'poorboy').lower() if pd.notna(row.get('Gas Lift Configuration')) else 'poorboy'
    gauge_depth = row['Gauge Depth'] if pd.notna(row['Gauge Depth']) else None
    gas_lift_valve_md = row.get('Gas Lift Valve MD', 0) if pd.notna(row.get('Gas Lift Valve MD')) else 0

    # Example values, replace with actual logic to determine these
    last_valve_open = True if lift_method == 'Gas Lift' else False
    calculate_from_gauge = False # If gauge in well, put to True
    compute_through = "flowing_side" # use static_side if relevant
    compute_static_down_to = "end_of_tubing"
    roughness = 0.0006 # default in whitson+

    well_data_casing = [
        {
            "d_casing_inner": row['Casing 1 ID'],
            "pipe_number": 1,
            "k_casing": roughness,
            "bottom_md": row['Casing 1 Bottom MD'],
            "top_md": row['Casing 1 Top MD']
        },
    ]

    if pd.notna(row['Casing 2 ID']): # check and append data only if it exists
        well_data_casing.append({
            "d_casing_inner": row['Casing 2 ID'],
            "pipe_number": 2,
            "k_casing": roughness,
            "bottom_md": row['Casing 2 Bottom MD'],
            "top_md": row['Casing 2 Top MD']
        })

    # Example for additional casing IDs:
    # if pd.notna(row['Casing 3 ID'])::
    #     well_data_casing.append({
    #         "d_casing_inner": row['Casing 3 ID'],
    #         "pipe_number": 3,
    #         "k_casing": roughness,
    #         "bottom_md": row['Casing 3 Bottom MD'],
    #         "top_md": row['Casing 3 Top MD']
    #     })

    well_data_tubing = []
    if pd.notna(row['Tubing ID']):
        well_data_tubing.append({
            "pipe_number": 1,
            "bottom_md": row['Tubing Bottom MD'],
            "d_tubing_inner": row['Tubing ID'],
            "d_tubing_outer": row['Tubing OD'],
            "k_tubing": roughness 
        })

    # Example for additional tubing IDs:
    # if pd.notna(row['Tubing ID 2 ID'])::
    #     well_data_tubing.append({
    #         "pipe_number": 2,
    #         "bottom_md": row['Tubing 2 Bottom MD'],
    #         "d_tubing_inner": row['Tubing 2 ID'],
    #         "d_tubing_outer": row['Tubing 2 OD'],
    #         "k_tubing": roughness 
    #     })

    gas_lift_data = []
    if lift_method == 'gas_lift':
        gas_lift_data.append({
            "ps_close": 0,
            "valve_md": gas_lift_valve_md,
            "ps_open": 0,
            "joint": 0
        })

    if well_name not in well_payloads:
        well_payloads[well_name] = []

    well_payloads[well_name].append({
        "use_from_date": use_from_date,
        "flow_path": flow_path,
        "lift_method": lift_method,
        "gas_lift_config": gas_lift_config,
        "last_valve_open": last_valve_open,
        "gauge_depth": gauge_depth,
        "calculate_from_gauge": calculate_from_gauge,
        "compute_through": compute_through,
        "compute_static_down_to": compute_static_down_to,
        "well_data_casing": well_data_casing,
        "well_data_tubing": well_data_tubing,
        "gas_lift_data": gas_lift_data
    })

for well_name, payload_list in well_payloads.items():
    well_id = whitson_connection.get_well_id_by_wellname(whitson_wells, well_name)
    wellbore_data = whitson_connection.get_well_data(well_id)

    # Delete the default wellbore. Not needed if you're simply appending wellbore configurations.
    if wellbore_data: 
        well_data_id = wellbore_data[0]['id']
        whitson_connection.delete_wellbore_config_by_well_data_id(well_data_id=well_data_id)        

    whitson_connection.upload_well_data_to_well(well_id, payload_list)

# ----------------------------------------------------------------------------------------------------------------
# 3. Upload production data
# ----------------------------------------------------------------------------------------------------------------

df_prod = pd.read_excel(excel_file, sheet_name='Production-Data', skiprows=2)
production_payload = []
prior_wellname = None

for index, row in df_prod.iterrows():
    well_name= row.iloc[0]
    date = pd.to_datetime(row.iloc[1], format='%Y-%m-%d %H:%M:%S')
    qo = row.iloc[2]
    qg = row.iloc[3]
    qw = row.iloc[4]
    p_wf_measured = row.iloc[5]
    p_tubing = row.iloc[6]
    p_casing = row.iloc[7]
    qg_gas_lift = row.iloc[8]
    liquid_level = row.iloc[9]

    this_prod_time = {
        "date": date.strftime("%Y-%m-%dT%H:%M:%S.%fZ") if date else None,
        "qo_sc": max(0, qo) if pd.notnull(qo) else None,
        "qg_sc": max(0, qg) if pd.notnull(qg) else None,
        "qw_sc": max(0, qw) if pd.notnull(qw) else None,
        "p_wf_measured": max(0, p_wf_measured) if pd.notnull(p_tubing) else None,
        "p_tubing": max(0, p_tubing) if pd.notnull(p_tubing) else None,
        "p_casing": max(0, p_casing) if pd.notnull(p_casing) else None,
        "qg_gas_lift": max(0, qg_gas_lift) if pd.notnull(qg_gas_lift) else None,
        "liquid_level": max(0, liquid_level) if pd.notnull(liquid_level) else None,
        "choke_size": None,
        "line_pressure": None,
    }

    if well_name == prior_wellname:
        production_payload.append(this_prod_time)
    else:
        if prior_wellname is not None:
            well_id = whitson_connection.get_well_id_by_wellname(whitson_wells, prior_wellname)
            whitson_connection.upload_production_to_well(
                well_id,
                {"production_data": production_payload},
                append_only=True,
            )
        production_payload = []
        production_payload.append(this_prod_time)
        prior_wellname = well_name

# Don't forget to upload the last batch
if production_payload:
    well_id = whitson_connection.get_well_id_by_wellname(whitson_wells, prior_wellname)
    whitson_connection.upload_production_to_well(
        well_id,
        {"production_data": production_payload},
        append_only=True,
    )

# ----------------------------------------------------------------------------------------------------------------
# 4. Run BHP calculations
# ----------------------------------------------------------------------------------------------------------------

# Run BHP calc

for well in whitson_wells: 
    well_id = well['id']
    whitson_connection.run_bhp_calc(well_id)

# Output BHP calc

# for well in whitson_wells: 
#     well_id = well['id']
#     calculated_bhp = whitson_connection.get_bhp_calc(well_id)
#     # To something with it, e.g. third-party dashboard

Reading Data from a SQL Table

The example above demonstrates how to read data from an excel template into whitson⁺. The code can be easily modified to read from a SQL database into whitson⁺ instead. Example of how to read SQL into a Pandas Dataframe can be seen below.

import pandas as pd
import pyodbc

# Establish a connection to your SQL Server
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=your_server;DATABASE=your_database;UID=your_username;PWD=your_password')

# Define your SQL query to select data from your table
sql_query = "SELECT * FROM your_table_name"  # Replace 'your_table_name' with your actual table name

# Read data from SQL table into a pandas DataFrame
df = pd.read_sql(sql_query, conn)

# Close the connection
conn.close()

# Now you have your data in the DataFrame 'df'
print(df.head())  # Display the first few rows of the DataFrame

WellView Queries: Relevant Tables and Queries

Below are example SQL queries designed to extract specific information from the a WellView database. These queries demonstrate how to retrieve tubing components, survey data, perforation details, and casing specifications for wells in select counties. Each query includes placeholders for conditions and patterns to be adjusted according to specific requirements. Note that these examples reference relevant WellView tables and are provided for illustrative purposes only.

Example: Deviation Survey Query

SELECT
   W1."COUNTY" AS "COUNTY",
   W1."DTTMSPUD" AS "DTTMSPUD",
   W1."OPERATOR" AS "OPERATOR",
   W1."WELLCONFIG" AS "WELLCONFIG",
   W1."WELLIDA" AS "WELLIDA",
   W1."WELLNAME" AS "WELLNAME",
   W2."DES" AS "WELLBOREDES",
   W3."DES" AS "DIRSURVEYDES",
   W3."MDTIEIN" AS "MDTIEIN",
   W4."AZIMUTH" AS "AZIMUTH",
   W4."INCLINATION" AS "INCLINATION",
   W4."MD" AS "MD",
   W4."TVDCALC" AS "TVDCALC"
FROM
   "WV103CALCUNITSUS"."WVWELLHEADER" W1,
   "WV103CALCUNITSUS"."WVWELLBORE" W2,
   "WV103CALCUNITSUS"."WVWELLBOREDIRSURVEY" W3,
   "WV103CALCUNITSUS"."WVWELLBOREDIRSURVEYDATA" W4
WHERE
   (W1."IDWELL" = W2."IDWELL")
   AND (W2."IDREC" = W3."IDRECPARENT")
   AND (W3."IDREC" = W4."IDRECPARENT")
   AND (W1."COUNTY" IN ('WILLIAMS', 'MOUNTRAIL', 'DUNN', 'DIVIDE', 'BURKE'))
   AND W1."DTTMSPUD" >= TO_DATE('2005-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
   AND W1."OPERATOR" LIKE 'OPERATOR_PATTERN' ESCAPE '\'
   AND W1."WELLCONFIG" LIKE 'CONFIG_PATTERN' ESCAPE '\'
   AND <conditions>

Example: Perforation Query

SELECT
   W1."COUNTY" AS "COUNTY",
   W1."DTTMSPUD" AS "DTTMSPUD",
   W1."OPERATOR" AS "OPERATOR",
   W1."WELLCONFIG" AS "WELLCONFIG",
   W1."WELLIDA" AS "WELLIDA",
   W1."WELLNAME" AS "WELLNAME",
   W2."DEPTHTOP" AS "DEPTHTOP",
   W2."DEPTHBTM" AS "DEPTHBTM"
FROM
   "WV103CALCUNITSUS"."WVWELLHEADER" W1,
   "WV103CALCUNITSUS"."WVPERFORATION" W2
WHERE
   (W1."IDWELL" = W2."IDWELL")
   AND (W1."COUNTY" IN ('WILLIAMS', 'MOUNTRAIL', 'DUNN', 'DIVIDE', 'BURKE'))
   AND W1."DTTMSPUD" >= TO_DATE('2005-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
   AND W1."OPERATOR" LIKE 'OPERATOR_PATTERN' ESCAPE '\'
   AND W1."WELLCONFIG" LIKE 'CONFIG_PATTERN' ESCAPE '\'
   AND W2."DEPTHTOP" >= 6000
   AND <conditions>

Example: Casing Query

SELECT
   W1."COUNTY" AS "COUNTY",
   W1."DTTMSPUD" AS "DTTMSPUD",
   W1."OPERATOR" AS "OPERATOR",
   W1."WELLCONFIG" AS "WELLCONFIG",
   W1."WELLIDA" AS "WELLIDA",
   W1."WELLNAME" AS "WELLNAME",
   W2."DES" AS "CASINGDES",
   W2."DEPTHBTM" AS "DEPTHBTM",
   W2."DEPTHTOPCALC" AS "DEPTHTOPCALC",
   W2."GRADECALC" AS "GRADECALC",
   W2."SZIDNOMMINCALC" AS "SZIDNOMMINCALC",
   W2."SZODNOMCOMPMAXCALC" AS "SZODNOMCOMPMAXCALC",
   W2."WTPERLENGTHCALC" AS "WTPERLENGTHCALC"
FROM
   "WV103CALCUNITSUS"."WVWELLHEADER" W1,
   "WV103CALCUNITSUS"."WVCAS" W2
WHERE
   (W1."IDWELL" = W2."IDWELL")
   AND (W1."COUNTY" IN ('WILLIAMS', 'MOUNTRAIL', 'DIVIDE', 'DUNN', 'BURKE'))
   AND W1."DTTMSPUD" >= TO_DATE('2005-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
   AND W1."OPERATOR" LIKE 'OPERATOR_PATTERN' ESCAPE '\'
   AND W1."WELLCONFIG" LIKE 'CONFIG_PATTERN' ESCAPE '\'
   AND <conditions>

Example: Tubing Component Query

SELECT
   W1."COUNTY" AS "COUNTY",
   W1."DTTMSPUD" AS "DTTMSPUD",
   W1."OPERATOR" AS "OPERATOR",
   W1."WELLCONFIG" AS "WELLCONFIG",
   W1."WELLIDA" AS "WELLIDA",
   W1."WELLNAME" AS "WELLNAME",
   W2."DEPTHBTM" AS "DEPTHBTM",
   W2."DES" AS "DES",
   W2."DTTMRUN" AS "DTTMRUN",
   W3."DES" AS "TUBCOMPDES",
   W3."COMPSUBTYP" AS "COMPSUBTYP",
   W3."COM" AS "COM",
   W3."DEPTHTOPCALC" AS "DEPTHTOPCALC",
   W3."DEPTHBTMCALC" AS "DEPTHBTMCALC",
   W3."LENGTH" AS "LENGTH",
   W3."SZIDNOM" AS "SZIDNOM",
   W3."SZODNOM" AS "SZODNOM"
FROM
   "WV103CALCUNITSUS"."WVWELLHEADER" W1,
   "WV103CALCUNITSUS"."WVTUB" W2,
   "WV103CALCUNITSUS"."WVTUBCOMP" W3
WHERE
   (W2."IDWELL" = W1."IDWELL")
   AND (W3."IDRECPARENT" = W2."IDREC")
   AND (W1."COUNTY" IN ('WILLIAMS', 'DIVIDE', 'DUNN', 'MOUNTRAIL', 'BURKE'))
   AND W1."DTTMSPUD" >= TO_DATE('2005-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
   AND W1."OPERATOR" LIKE 'OPERATOR_PATTERN' ESCAPE '\'
   AND W1."WELLCONFIG" LIKE 'CONFIG_PATTERN' ESCAPE '\'
   AND <conditions>

Bottomhole Pressure Example

BHP Input Data Schema Visualization

Difference between WELL_ID and WELL_DATA_ID

Connect BHP data to whitson+: Python Example

Connect BHP data to whitson+: Python Example

Reading Data from a SQL Table

WellView Queries: Relevant Tables and Queries

Example: Deviation Survey Query

Example: Perforation Query

Example: Casing Query

Example: Tubing Component Query

Connect BHP data to whitson⁺: Python Example

Connect BHP data to whitson⁺: Python Example