<jupyter_start><jupyter_text>Preliminary evaluation of Washington oil EXPORT for terminals included in Casey's origin/destination analysis- Same method as "Oil_Cargo_WA_export.ipynb"- "refinery_tags" and "terminal_tags" modified to specify only the locations included in Casey's analysis- 90% of all tanker, ATB, and barge volume exports to the selected refineries and terminals is accounted for using the specified oil type clasifications(see out[12])- Total Export in this analysis:  5,061,390,898 gallons (See printout above In[09])<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from collections import defaultdict
# User inputs
file_dir  = '/Users/rmueller/Data/MIDOSS/DeptOfEcology/'
file_name = 'MuellerTrans4-30-20.xlsx'
# Import columns are: (E) 'StartDateTime, (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="E,G,H,P,Q,R,W,X")


oil_traffic = {}
# oil_traffic['destination']={}

# for reciever in cargo_data.Receiver:
#     # create a list of all recieving entities
#     if reciever not in oil_traffic['destination']:
#         oil_traffic['destination'][f'{reciever}'] = f'{reciever}'<jupyter_output><empty_output><jupyter_text>Evaluate marine oil export  By vessel type (This is the right way though I still need to remove Vancouver, WA)<jupyter_code># Get all cargo fuel transfers
cargo_data = df[df['TransferType']=='Cargo']
# define list of strings that can be used to tag names for WA state refineries and terminals
# still in verification phase around accurately selecting Salish Sea marine terminals.  
# Check to see if Vancouver terminals are included in "export cargo" dataframe
# Note: Casey's data lists Marathon in place of Andeavor
oil_traffic['refinery_tags'] = ['Shell Puget Sound Refinery', 'BP Cherry Point Refinery', '(formerly Tesoro)', 
                               'Phillips 66 Ferndale Refinery', 'U.S. Oil & Refining']
oil_traffic['terminal_tags']  = ['Maxum Petroleum', 'Tidewater Snake', 'Nustar Energy', 'SeaPort Sound', 
                                 'Tesoro Vancouver', 'Ballard Oil Co.', 'Phillips 66', 'Tesoro Port Angeles', 
                                 'NAVSUP Manchester', 'Alon Asphalt Company', 'Kinder Morgan Liquids','Nustar Energy Vancouver', 
                                 'Tesoro Pasco', 'REG Grays Harbor', 'Tidewater Vancouver', 'TLP Management Services LLC',
                                 'NASWI']
# create dataset of export cargo in which export cargo is defined as
# all cargo being deliverd from (land-based) marine terminal
cargo_export = pd.DataFrame(columns = cargo_data.columns)
for refinery in oil_traffic['refinery_tags']:
    cargo_export = cargo_export.append(cargo_data[cargo_data.Deliverer.str.contains(refinery)])
    
# add oil from terminal locations
list_of_terminals = []
for terminal_origin in oil_traffic['terminal_tags']:
    cargo_export = cargo_export.append(
        cargo_data[cargo_data.Deliverer.str.contains(f'{terminal_origin}')]
    )
    if terminal_origin not in list_of_terminals:
        list_of_terminals.append(f'{terminal_origin}')
    
# need to re-set indexing in order to use row-index as data_frame index
cargo_export.reset_index(drop=True, inplace=True)    
cargo_export[:1]
# introduce dictionary for cargo traffic
oil_traffic['cargo'] = {}

# ATB fuel export
oil_traffic['cargo']['atb']                           = {}
oil_traffic['cargo']['atb']['percent_volume_export']  = {} # percentage of total crude export by oil type 
oil_traffic['cargo']['atb']['volume_export_total']    = 0
oil_traffic['cargo']['atb']['volume_export']          = [] # a vector of volumes ordered to pair with oil_type
oil_traffic['cargo']['atb']['oil_type']               = [] # a vector of oil types ordered to pair with volume_export

# barge fuel export
oil_traffic['cargo']['barge']                           = {}
oil_traffic['cargo']['barge']['percent_volume_export']  = {}
oil_traffic['cargo']['barge']['volume_export_total']    = 0
oil_traffic['cargo']['barge']['volume_export']          = []
oil_traffic['cargo']['barge']['oil_type']               = []

# tanker fuel export
oil_traffic['cargo']['tanker']                           = {}
oil_traffic['cargo']['tanker']['percent_volume_export']  = {}
oil_traffic['cargo']['tanker']['volume_export_total']    = 0
oil_traffic['cargo']['tanker']['volume_export']          = []
oil_traffic['cargo']['tanker']['oil_type']               = []

# total
oil_traffic['cargo']['export_total']                       = {}
oil_traffic['cargo']['export_total']['all']                = 0
oil_traffic['cargo']['export_total']['atb_barge_tankers']  = 0

# identify ship traffic 
[nrows,ncols] = cargo_export.shape

# total up volume of oil transferred onto ATB BARGES, non-ATB barges, and other vessels
# create counter for vessel-type
atb_counter = 0
barge_counter = 0
tanker_counter = 0
for rows in range(nrows):   
                
    # Add up all oil import to refineries and terminals, regardless of vessel-type
    oil_traffic['cargo']['export_total']['all'] += cargo_export.TransferQtyInGallon[rows]
    
    # ATB
    if ('ATB' in cargo_export.Receiver[rows] or
        'ITB' in cargo_export.Receiver[rows]): 
        oil_traffic['cargo']['atb']['volume_export_total'] += cargo_export.TransferQtyInGallon[rows]
        oil_traffic['cargo']['atb']['volume_export'].append(cargo_export.TransferQtyInGallon[rows])
        oil_traffic['cargo']['atb']['oil_type'].append(cargo_export.Product[rows])
        atb_counter += 1
    
    # Barge
    elif ('BARGE' in cargo_export.Receiver[rows] or \
        'Barge'   in cargo_export.Receiver[rows] or \
        'PB'      in cargo_export.Receiver[rows] or \
        'YON'     in cargo_export.Receiver[rows] or \
        'DLB'     in cargo_export.Receiver[rows]): 
        oil_traffic['cargo']['barge']['volume_export_total'] += cargo_export.TransferQtyInGallon[rows] 
        oil_traffic['cargo']['barge']['volume_export'].append(cargo_export.TransferQtyInGallon[rows])
        oil_traffic['cargo']['barge']['oil_type'].append(cargo_export.Product[rows])
        barge_counter += 1
        #display(cargo_data.Receiver[rows])
    
    # Tanker
    else:
        oil_traffic['cargo']['tanker']['volume_export_total'] += cargo_export.TransferQtyInGallon[rows] 
        oil_traffic['cargo']['tanker']['volume_export'].append(cargo_export.TransferQtyInGallon[rows])
        oil_traffic['cargo']['tanker']['oil_type'].append(cargo_export.Product[rows])
        tanker_counter += 1
        #display(cargo_data.Receiver[rows])

oil_traffic['cargo']['total_export'] = oil_traffic['cargo']['atb']['volume_export_total'] + oil_traffic['cargo']['barge']['volume_export_total'] + oil_traffic['cargo']['tanker']['volume_export_total'] 
print('Total Export in this analysis: ' + str(oil_traffic['cargo']['export_total']['all']) + ' gallons')
# Calculate percent of total transport by vessel type
atb_percent = 100*oil_traffic['cargo']['atb']['volume_export_total']/oil_traffic['cargo']['export_total']['all']
barge_percent = 100*oil_traffic['cargo']['barge']['volume_export_total']/oil_traffic['cargo']['export_total']['all']
tanker_percent = 100*oil_traffic['cargo']['tanker']['volume_export_total']/oil_traffic['cargo']['export_total']['all']
print(atb_percent)
print(barge_percent)
print(tanker_percent)
volume_export_byvessel = [oil_traffic['cargo']['atb']['volume_export_total'], oil_traffic['cargo']['barge']['volume_export_total'], oil_traffic['cargo']['tanker']['volume_export_total']]
#colors = ['b', 'g', 'r', 'c', 'm', 'y']
labels = [f'ATB ({atb_percent:3.1f}%)', f'Tow Barge ({barge_percent:3.1f}%)',f'Tanker ({tanker_percent:3.1f}%)']
plt.gca().axis("equal")
plt.pie(volume_export_byvessel, labels= labels)
plt.title('Types of vessels by volume receiving oil as cargo from land-based platform \n identified as terminals and refineries in Casey-selection')<jupyter_output><empty_output><jupyter_text>By oil type within vessel type classification<jupyter_code>oil_traffic['cargo']['atb']['CRUDE']=0
oil_traffic['cargo']['atb']['GASOLINE']=0
oil_traffic['cargo']['atb']['JET FUEL/KEROSENE']=0
oil_traffic['cargo']['atb']['DIESEL/MARINE GAS OIL']=0
oil_traffic['cargo']['atb']['DIESEL LOW SULPHUR (ULSD)']=0
oil_traffic['cargo']['atb']['BUNKER OIL/HFO']=0
oil_traffic['cargo']['atb']['other']=0

oil_traffic['cargo']['barge']['CRUDE']=0
oil_traffic['cargo']['barge']['GASOLINE']=0
oil_traffic['cargo']['barge']['JET FUEL/KEROSENE']=0
oil_traffic['cargo']['barge']['DIESEL/MARINE GAS OIL']=0
oil_traffic['cargo']['barge']['DIESEL LOW SULPHUR (ULSD)']=0
oil_traffic['cargo']['barge']['BUNKER OIL/HFO']=0
oil_traffic['cargo']['barge']['other']=0

oil_traffic['cargo']['tanker']['CRUDE']=0
oil_traffic['cargo']['tanker']['GASOLINE']=0
oil_traffic['cargo']['tanker']['JET FUEL/KEROSENE']=0
oil_traffic['cargo']['tanker']['DIESEL/MARINE GAS OIL']=0
oil_traffic['cargo']['tanker']['DIESEL LOW SULPHUR (ULSD)']=0
oil_traffic['cargo']['tanker']['BUNKER OIL/HFO']=0
oil_traffic['cargo']['tanker']['other']=0

oil_types = ['CRUDE', 'GASOLINE', 'JET FUEL/KEROSENE','DIESEL/MARINE GAS OIL', 
             'DIESEL LOW SULPHUR (ULSD)', 'BUNKER OIL/HFO', 'other']

percent_check = 0
for oil_name in range(len(oil_types)):
    
    # ATBs
    for rows in range(len(oil_traffic['cargo']['atb']['volume_export'])):  
        if oil_types[oil_name] in oil_traffic['cargo']['atb']['oil_type'][rows]: 
            oil_traffic['cargo']['atb'][oil_types[oil_name]] += oil_traffic['cargo']['atb']['volume_export'][rows]
    
    # Barges
    for rows in range(len(oil_traffic['cargo']['barge']['volume_export'])):  
        if oil_types[oil_name] in oil_traffic['cargo']['barge']['oil_type'][rows]: 
            oil_traffic['cargo']['barge'][oil_types[oil_name]] += oil_traffic['cargo']['barge']['volume_export'][rows]
    
    # Tankers (non-ATB or Barge)
    for rows in range(len(oil_traffic['cargo']['tanker']['volume_export'])):  
        if oil_types[oil_name] in oil_traffic['cargo']['tanker']['oil_type'][rows]: 
            oil_traffic['cargo']['tanker'][oil_types[oil_name]] += oil_traffic['cargo']['tanker']['volume_export'][rows]
    
    # calculate percentages based on total oil cargo exports
    oil_traffic['cargo']['atb']['percent_volume_export'][oil_types[oil_name]]    = 100 * oil_traffic['cargo']['atb'][oil_types[oil_name]]/oil_traffic['cargo']['export_total']['all']
    oil_traffic['cargo']['barge']['percent_volume_export'][oil_types[oil_name]]  = 100 * oil_traffic['cargo']['barge'][oil_types[oil_name]]/oil_traffic['cargo']['export_total']['all']
    oil_traffic['cargo']['tanker']['percent_volume_export'][oil_types[oil_name]] = 100 * oil_traffic['cargo']['tanker'][oil_types[oil_name]]/oil_traffic['cargo']['export_total']['all']
    
    for name in ['atb', 'barge', 'tanker']:
        percent_check += oil_traffic['cargo'][f'{name}']['percent_volume_export'][oil_types[oil_name]]

percent_check<jupyter_output><empty_output><jupyter_text>Plot up ATB fuel types<jupyter_code>atb_volume_export = [oil_traffic['cargo']['atb']['CRUDE'],
                    oil_traffic['cargo']['atb']['GASOLINE'],
                    oil_traffic['cargo']['atb']['JET FUEL/KEROSENE'],
                    oil_traffic['cargo']['atb']['DIESEL/MARINE GAS OIL'],
                    oil_traffic['cargo']['atb']['DIESEL LOW SULPHUR (ULSD)'],
                    oil_traffic['cargo']['atb']['BUNKER OIL/HFO']]
labels = []
for ii in range(len(oil_types)-1):
    labels.append(f'{oil_types[ii]} ({oil_traffic["cargo"]["atb"]["percent_volume_export"][oil_types[ii]]:3.1f}) %')
    
plt.gca().axis("equal")
plt.pie(atb_volume_export, labels= labels)
plt.title('Types of oil transport by volume for ATBs from WA refineries and terminals')<jupyter_output><empty_output><jupyter_text>Plot up Barge fuel types<jupyter_code># barge_volume_export = [oil_traffic['cargo']['barge']['CRUDE'],
#                     oil_traffic['cargo']['barge']['GASOLINE'],
#                     oil_traffic['cargo']['barge']['JET FUEL/KEROSENE'],
#                     oil_traffic['cargo']['barge']['DIESEL/MARINE GAS OIL'],
#                     oil_traffic['cargo']['barge']['DIESEL LOW SULPHUR (ULSD)'],
#                     oil_traffic['cargo']['barge']['BUNKER OIL/HFO'],
#                     oil_traffic['cargo']['barge']['other']]
barge_volume_export = [oil_traffic['cargo']['barge']['JET FUEL/KEROSENE'],
                    oil_traffic['cargo']['barge']['DIESEL/MARINE GAS OIL'],
                    oil_traffic['cargo']['barge']['DIESEL LOW SULPHUR (ULSD)'],
                    oil_traffic['cargo']['barge']['BUNKER OIL/HFO']]
labels = []
for ii in [2,3,4,5]:
    labels.append(f'{oil_types[ii]} ({oil_traffic["cargo"]["barge"]["percent_volume_export"][oil_types[ii]]:3.1f}) %')

plt.gca().axis("equal")
plt.pie(barge_volume_export, labels= labels)
plt.title('Types of oil transport by volume for barges from WA refineries and terminals')<jupyter_output><empty_output><jupyter_text>Plot up Tanker fuel types<jupyter_code>tanker_volume_export = [oil_traffic['cargo']['tanker']['CRUDE'],
                    oil_traffic['cargo']['tanker']['GASOLINE'],
                    oil_traffic['cargo']['tanker']['JET FUEL/KEROSENE'],
                    oil_traffic['cargo']['tanker']['DIESEL/MARINE GAS OIL'],
                    oil_traffic['cargo']['tanker']['DIESEL LOW SULPHUR (ULSD)'],
                    oil_traffic['cargo']['tanker']['BUNKER OIL/HFO'],
                    oil_traffic['cargo']['tanker']['other']]
labels = []
for ii in range(len(oil_types)):
    labels.append(f'{oil_types[ii]} ({oil_traffic["cargo"]["tanker"]["percent_volume_export"][oil_types[ii]]:3.1f}) %')

plt.gca().axis("equal")
plt.pie(tanker_volume_export, labels= labels)
plt.title('Types of oil transport by volume for tankers from WA refineries and terminals\n with percent of net crude export')<jupyter_output><empty_output><jupyter_text>This is the wrong way (includes ship-to-ship transfers)<jupyter_code># # remove cargo fuel transfers to land-based entity in order to isolate export cargo
# oil_traffic['cargo'] = {}
# cargo_data = cargo_data[~cargo_data.Receiver.str.contains('Refinery')]
# cargo_data = cargo_data[~cargo_data.Receiver.str.contains('Refining')] # this is specifically for U.S. Oil in Tacoma
# cargo_data = cargo_data[~cargo_data.Receiver.str.contains('Terminal')]
# cargo_data = cargo_data[~cargo_data.Receiver.str.contains('Petroleum')] # This is for smaller outfits, like Ranier Petroleum or Maxum Petrolem
# cargo_data = cargo_data[~cargo_data.Receiver.str.contains('Inc')] # Covich, Petrocard
# cargo_data = cargo_data[~cargo_data.Receiver.str.contains('LLC')] # Pacific Functional Fluids, LLC; Coleman Oil Company, LLC
# cargo_data = cargo_data[~cargo_data.Receiver.str.contains('Ballard Oil Co.')]
# cargo_data = cargo_data[~cargo_data.Receiver.str.contains('Reisner Distributor, Inc.')]
# cargo_data = cargo_data[~cargo_data.Receiver.str.contains('NASWI')]

# # This dataset contains Columbia River locations that need to be scrubbed out
# cargo_data = cargo_data[~cargo_data.City.str.contains('vancouver')]

# # cargo_data is no longer indexed chronologically after removing 'Refinery', 'Terminal' and 'NASWI' entries
# # need to re-set indexing in order to use row-index as data_frame index
# cargo_data.reset_index(drop=True, inplace=True)
# [nrows,ncols] = cargo_data.shape

# # introduce dictionary entries for fuel volume export
# oil_traffic['cargo']['atb_volume_export']   = 0
# oil_traffic['cargo']['barge_volume_export'] = 0
# oil_traffic['cargo']['other_export'] = 0
# oil_traffic['cargo']['ship_to_ship'] = 0
# oil_traffic['cargo']['total']        = 0

# # carrier names
# oil_traffic['cargo']['atb_carrier']={}

# # total up volume of oil transferred onto ATB BARGES, non-ATB barges, and other vessels
# for rows in range(nrows):
#     # from land-based terminals and refineries only
#     if 'Refinery' in cargo_data.Deliverer[rows] or 'Refining' in cargo_data.Deliverer[rows] or 'Terminal' in cargo_data.Deliverer[rows]: 
#         if 'ATB' in cargo_data.Receiver[rows] :
#             oil_traffic['cargo']['atb_volume_export'] = oil_traffic['cargo']['atb_volume_export'] + cargo_data.TransferQtyInGallon[rows]
#             #display(cargo_data.Receiver[rows])
#             #if cargo_data.Receiver[rows] not in oil_traffic['cargo']['atb_carrier']:
#             #    oil_traffic['cargo']['atb_carrier'][f'{oil_traffic['cargo']['atb_carrier']}']=[f'{oil_traffic['cargo']['atb_carrier']}']
#         elif 'BARGE' in cargo_data.Receiver[rows] or 'Barge' in cargo_data.Receiver[rows] or 'PB' in cargo_data.Receiver[rows] or 'YON' in cargo_data.Receiver[rows] or 'DLB' in cargo_data.Receiver[rows]:  # and not 'ATB' in cargo_data.Receiver[rows]:  
#             oil_traffic['cargo']['barge_volume_export'] = oil_traffic['cargo']['barge_volume_export'] + cargo_data.TransferQtyInGallon[rows]
#             #display(cargo_data.Receiver[rows])
#         else:
#             oil_traffic['cargo']['other_export'] = oil_traffic['cargo']['other_export'] + cargo_data.TransferQtyInGallon[rows]
#             #display(cargo_data.Receiver[rows])
#     else:
#         oil_traffic['cargo']['ship_to_ship'] = oil_traffic['cargo']['ship_to_ship'] + cargo_data.TransferQtyInGallon[rows]
        
# oil_traffic['cargo']['total'] = oil_traffic['cargo']['atb_volume_export'] + oil_traffic['cargo']['barge_volume_export'] + oil_traffic['cargo']['other_export'] + oil_traffic['cargo']['ship_to_ship']
# oil_traffic['cargo']['total']/cargo_data.TransferQtyInGallon.sum()<jupyter_output><empty_output><jupyter_text>Add up volume transferred by fuel type<jupyter_code># Add up the total volume of marine transport by product (this includes all transfers)
gas_export_data    = cargo_data[cargo_data['Product']=='GASOLINE']
gas_export_total   = gas_export_data['TransferQtyInGallon'].sum()
gas_export_percent = 100*gas_export_total/cargo_data.TransferQtyInGallon.sum()

diesel_export_data    = cargo_data[cargo_data['Product']=='DIESEL/MARINE GAS OIL']
diesel_export_total   = diesel_export_data['TransferQtyInGallon'].sum()
diesel_export_percent = 100*diesel_export_total/cargo_data.TransferQtyInGallon.sum()

bunker_export_data  = cargo_data[cargo_data['Product']=='BUNKER OIL/HFO']
bunker_export_total = bunker_export_data['TransferQtyInGallon'].sum()
bunker_export_percent = 100*bunker_export_total/cargo_data.TransferQtyInGallon.sum()

jet_export_data   = cargo_data[cargo_data['Product']=='JET FUEL/KEROSENE']
jet_export_total  = jet_export_data['TransferQtyInGallon'].sum()
jet_export_percent = 100*jet_export_total/cargo_data.TransferQtyInGallon.sum()

ulsd_export_data  = cargo_data[cargo_data['Product']=='DIESEL LOW SULPHUR (ULSD)']
ulsd_export_total = ulsd_export_data['TransferQtyInGallon'].sum()
ulsd_export_percent = 100*ulsd_export_total/cargo_data.TransferQtyInGallon.sum()


crude_export_data  = cargo_data[cargo_data['Product'].str.contains('CRUDE')]
crude_export_total = crude_export_data['TransferQtyInGallon'].sum()
crude_export_percent = 100*crude_export_total/cargo_data.TransferQtyInGallon.sum()

Other_fuel_total = cargo_data['TransferQtyInGallon'].sum() - crude_export_total - ulsd_export_total - jet_export_total - bunker_export_total - diesel_export_total - gas_export_total 
other_fuel_percent = 100*Other_fuel_total/cargo_data.TransferQtyInGallon.sum()

total_percent = other_fuel_percent+crude_export_percent+ulsd_export_percent+jet_export_percent+bunker_export_percent+diesel_export_percent+gas_export_percent
total_percent<jupyter_output><empty_output><jupyter_text>Plot up results<jupyter_code>oil_export_values = [gas_export_total, diesel_export_total , bunker_export_total, jet_export_total, crude_export_total, ulsd_export_total, Other_fuel_total]
#colors = ['b', 'g', 'r', 'c', 'm', 'y']
labels = [f'Gas ({gas_export_percent:3.1f}%)', f'Diesel ({diesel_export_percent:3.1f}%)', f'Bunker-C ({bunker_export_percent:3.1f}%)', f'Jet ({jet_export_percent:3.1f}%)', f'Crude ({crude_export_percent:3.1f}%)', f'Low Sulpher Diesel ({ulsd_export_percent:3.1f}%)', f'Other ({other_fuel_percent:3.1f}%)']
plt.gca().axis("equal")
plt.pie(oil_export_values, labels= labels)
plt.title('Marine Oil Export from WA Refineries and Terminals')
cargo_data<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15781021897810218, 'fraction_numerical': 0.006520681265206813, 'mean_word_length': 5.352704791344667, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 37, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '442694', 'license': 'Apache-2.0', 'n_tokens_mistral': 7408, 'n_tokens_neox': 7051, 'n_words': 1194, 'path': 'notebooks/graphics/Oil_transport/Oil_Cargo_WA_export_Casey.ipynb', 'repo_name': 'MIDOSS/analysis-rachael', 'size': '20550'}