<jupyter_start><jupyter_text>Oil type by origin "US" (general attribution)Goal: Determine if null values for oil transfer from - Phillips Tacoma [all vessels], - Kinder Morgan Liquids [atbs]- Grays Harbour [all vessels]are legit?<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import yaml

file_dir  = '/Users/rmueller/Data/MIDOSS/DeptOfEcology/'
file_name = 'MuellerTrans4-30-20.xlsx'

# Import columns are: (G) Deliverer, (H) Receiver, (P) Product, 
#                     (Q) Quantity in Gallons, (R) Transfer Type (Fueling, Cargo, or Other)', 
#                     (w) DelivererTypeDescription, (x) ReceiverTypeDescription 
df = pd.read_excel(f'{file_dir}{file_name}',sheet_name='Vessel Oil Transfer', 
                   usecols="G,H,P,Q,R,W,X")

# list of names in DOE dataset that are used as "DelivererTypeDescription" for vessel transfers
vessel_names = ['TANK SHIP', 'TANK BARGE', 'TUGBOAT']

# list of terminals that we want to focus on for marine transfers
terminals = [
    'REG Grays Harbor, LLC', 
    'Kinder Morgan Liquids Terminal - Harbor Island', 
    'Phillips 66 Tacoma Terminal'
]<jupyter_output><empty_output><jupyter_text>Extract cargo data transferred from facility to vessel<jupyter_code># Get all cargo fuel transfers
bool_cargo = df['TransferType']=='Cargo'
cargo_data = df[bool_cargo]
cargo_from_facility = cargo_data[ cargo_data['DelivererTypeDescription']=='Facility' ]
cargo_to_tank   = cargo_from_facility[ cargo_from_facility['ReceiverTypeDescription'] == 'TANK SHIP' ]
cargo_to_barge  = cargo_from_facility[ cargo_from_facility['ReceiverTypeDescription'] == 'TANK BARGE' ] 
cargo_to_tug    = cargo_from_facility[ cargo_from_facility['ReceiverTypeDescription'] == 'TUGBOAT' ]

# reset indices so they increase chronologically from 0 instead of keeping parent index values
cargo_to_tank.reset_index( drop=True, inplace=True )
cargo_to_barge.reset_index( drop=True, inplace=True )
cargo_to_tug.reset_index( drop=True, inplace=True )
greys_origin = df[ df.Deliverer == 'REG Grays Harbor, LLC' ]
kinder_origin = df[ df.Deliverer == 'Kinder Morgan Liquids Terminal - Harbor Island' ]
phillips_origin = df[ df.Deliverer == 'Phillips 66 Tacoma Terminal']

greys_destination = df[ df.Receiver == 'REG Grays Harbor, LLC' ]
kinder_destination = df[ df.Receiver == 'Kinder Morgan Liquids Terminal - Harbor Island' ]
phillips_destination = df[ df.Receiver == 'Phillips 66 Tacoma Terminal']
transfer_data = {'Grays Harbor' :  [sum(greys_origin.TransferQtyInGallon), sum(greys_destination.TransferQtyInGallon)],
        'Kinder Morgan': [sum(kinder_origin.TransferQtyInGallon), sum(kinder_destination.TransferQtyInGallon)],
        'Phillips 66 Tacoma': [sum(phillips_origin.TransferQtyInGallon), sum(phillips_destination.TransferQtyInGallon)]
}
transfer_data
kinder_origin.shape
kinder_origin[:1]
kinder_barge = kinder_origin[kinder_origin.ReceiverTypeDescription == 'TANK BARGE']
kinder_barge.shape
kinder_tug = kinder_origin[kinder_origin.ReceiverTypeDescription == 'TUGBOAT']
kinder_tug.shape
kinder_tanker = kinder_origin[kinder_origin.ReceiverTypeDescription == 'TANK SHIP']
kinder_tanker.shape
len(kinder_barge[kinder_barge.Receiver=='DR. BONN<NAME>']) + len(kinder_barge[kinder_barge.Receiver ==  'NATHAN SCHMIDT'])
kinder_sub = kinder_barge[kinder_barge.Receiver !=  'NATHAN SCHMIDT']
leftover = kinder_sub[kinder_sub.Receiver !=  '<NAME>']
leftover<jupyter_output><empty_output><jupyter_text>create a list of all ATB/ITBs<jupyter_code>atb_receivers = df[df['Receiver'].str.contains('ATB')]
atb_deliverers = df[df['Deliverer'].str.contains('ATB')]
itb_receivers = df[df['Receiver'].str.contains('ITB')]
itb_deliverers = df[df['Deliverer'].str.contains('ITB')]
atb_out = pd.concat([atb_receivers, itb_receivers])
atb_in = pd.concat([atb_deliverers, itb_deliverers])
atb_out.reset_index( drop=True, inplace=True )
atb_in.reset_index( drop=True, inplace=True )
# count atbs/itbs receiving oil 
[rows,cols] = atb_out.shape
atb_names = []
for row in range(rows):
    if atb_out.Receiver[row] not in atb_names:
         atb_names.append(atb_out.Receiver[row])

# add atbs/itbs delivering oil 
[rows,cols] = atb_in.shape
for row in range(rows):
    if atb_in.Deliverer[row] not in atb_names:
         atb_names.append(atb_in.Deliverer[row])
atb_names<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11543778801843319, 'fraction_numerical': 0.0034562211981566822, 'mean_word_length': 6.104746317512275, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 15, 'https://': 0, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '378021', 'license': 'Apache-2.0', 'n_tokens_mistral': 1600, 'n_tokens_neox': 1535, 'n_words': 326, 'path': 'notebooks/monte_carlo_dev/investigate_null_WA_transfers.ipynb', 'repo_name': 'MIDOSS/analysis-rachael', 'size': '4360'}