def get_imports_lay_can_dates(item):
"""Get laycan dates for import vessel movements.
First try to call the API with origin parameter (to improve accuracy).
If no trades are returned, call the API without the origin parameter.
Match trades by checking that both destination date and installations are
accurate to what's stipulated in the report (return the first one that matches).
Finally, get the lay_can_start and lay_can_end from the final matched trade.
Args:
item (Dict[str, str]):
Returns:
Tuple[str | None, str | None]:
"""
if not item['lay_can']:
return None, None
import_date = parse_date(item['lay_can'], dayfirst=False)
# define search timeframe to be 4 months before and 1 month after import date (c.f. analysts)
# dates are formatted according to Kpler API specification.
start_date = (import_date - relativedelta(months=4)).strftime(KP_API_DATE_PARAM_FORMAT)
end_date = (import_date + relativedelta(months=1)).strftime(KP_API_DATE_PARAM_FORMAT)
# get all trades within timeframe for the vessel with origin parameter
params = {
'vessels': item['vessel_name'].lower(),
'startdate': start_date,
'enddate': end_date,
# data source supplies only UK imports
'zonesdestination': 'united kingdom',
'zonesorigin': item['last_port'].lower(),
}
trades = list(kp_api.get_session(platform=item['platform']).get_trades(params))
# if no trades matched, relax the search criteria by removing the origin_zone
if len(trades) == 0:
params.pop('zonesorigin')
trades = list(kp_api.get_session(platform=item['platform']).get_trades(params))
# sanity check, in case we match to an irrelevant port call
matched_trade = _match_trades(item, trades, import_date)
if not matched_trade:
return None, None
origin_date = parse_date(matched_trade['Date (origin)'], dayfirst=False)
# lay_can_start is 2 days before origin date, lay_can_end is 1 day after origin date
lay_can_start = (origin_date - relativedelta(days=2)).isoformat()
lay_can_end = (origin_date + relativedelta(days=1)).isoformat()
return lay_can_start, lay_can_end
def process_item(raw_item):
"""Transform raw item into a usable SpotCharter event.
Args:
raw_item (Dict[str, str]):
Yields:
Dict[str, str]:
"""
item = map_keys(raw_item, charter_mapping())
# discard vessels if it's yet to be named (TBN)
if not item['vessel']:
return
# process "import" portcalls accordingly (charters are defined by their departure)
if 'import' in item.pop('cargo_movement', '').lower():
logger.info(
f'Attempting to find matching export port call for {item["vessel"]["name"]}'
)
# get trade from oil platform
_trade = kp_api.get_session('oil', recreate=True).get_import_trade(
vessel=item['vessel']['name'],
origin='',
dest=item['departure_zone'],
end_date=item['lay_can_start'],
)
# mutate item with relevant laycan periods and load/discharge port info
_process_import_charter(item, _trade)
return item
def process_item(raw_item):
"""Transform raw item into a usable event.
Args:
raw_item (Dict[str, str]):
Returns:
Dict[str, str]:
"""
item = map_keys(raw_item, charters_mapping())
# remove vessels not named
if not item['vessel']['name']:
return
movement = item.pop('cargo_movement', '')
item['current_port'] = PORT_MAPPING.get(item['current_port'],
item['current_port'])
# build a proper cargo dict according to Cargo model
item['cargo'] = {
'product': 'crude/co',
'volume': None,
'volume_unit': None,
'movement': 'load'
}
item['lay_can_start'] = normalize_dates(item['lay_can_start'],
item['year'])
if 'discharge' in movement:
_trade = None
if item['lay_can_start']:
# get trade from either oil or cpp platform
for platform in ('oil', 'cpp'):
_trade = kp_api.get_session(platform,
recreate=True).get_import_trade(
vessel=item['vessel']['name'],
origin=item.get(
'load_dis_port', ''),
dest=item['current_port'],
end_date=item['lay_can_start'],
)
if _trade:
break
# mutate item with relevant laycan periods and load/discharge port info
post_process_import_item(item, _trade)
if 'load' in movement:
item['departure_zone'] = item['current_port']
item['arrival_zone'] = (item['load_dis_port'] if 'REVERT'
not in item['load_dis_port'] else '')
for field in ('load_dis_port', 'current_port', 'year'):
item.pop(field, None)
return item
def process_item(raw_item):
"""Transform raw item into a usable event.
Args:
raw_item (Dict[str, str]):
Returns:
Dict[str, str]:
"""
item = map_keys(raw_item, charters_mapping(), skip_missing=True)
# build cargo sub-model
volume = item['cargo_volume'] if item.get(
'cargo_volume') else item['cargo_volume_nominated']
item['cargo'] = {
'product': item.pop('cargo_product', None),
'movement': 'load',
'volume': volume,
'volume_unit': Unit.kilobarrel,
}
# if date is blank assume reported date
if not item.get('laycan'):
item['laycan'] = parse_date(item['reported_date'],
dayfirst=True).strftime('%Y-%m-%d')
# check if vessel movement is import/export
# export movements can just be returned as is, since a SpotCharter is defined by exports
if 'export' in item['sheet_name']:
item['departure_zone'] = 'Port of Jose'
item['arrival_zone'] = ''
item['lay_can_start'] = to_isoformat(item.pop('laycan', None))
# import movements needed to be treated specially to obtain the proper laycan dates
if 'import' in item['sheet_name']:
item['arrival_zone'] = 'Port of Jose'
_trade = None
# get trade from cpp or oil platform
for platform in ('oil', 'cpp'):
_trade = kp_api.get_session(platform,
recreate=True).get_import_trade(
vessel=item['vessel']['name'],
origin='',
dest=item['arrival_zone'],
end_date=item['laycan'],
)
if _trade:
break
# mutate item with relevant laycan periods and load/discharge port info
post_process_import_item(item, _trade)
# cleanup unused fields
for field in ('laycan', 'sheet_name', 'cargo_volume_nominated',
'cargo_volume'):
item.pop(field, None)
return item
def get_imports_lay_can_dates(item):
"""Get laycan dates for import vessel movements.
Call the API with origin parameter (to improve accuracy).
If no known origin, call the API without the origin parameter.
Match trades by checking that both destination date and installations are
accurate to what's stipulated in the report (return the first one that matches).
Finally, get the lay_can_start and lay_can_end from the final matched trade.
Args:
item (Dict[str, str]):
Returns:
Tuple[str | None, str | None]:
"""
# get trade from different platforms
if item['load_disch_zone'] and item['lay_can_start']:
if isinstance(item['lay_can_start'], datetime):
import_date = to_isoformat(
item['lay_can_start'].strftime(KP_API_DATE_PARAM_FORMAT))
else:
import_date = to_isoformat(item['lay_can_start'])
for platform in PLATFORMS:
trade = kp_api.get_session(platform,
recreate=True).get_import_trade(
vessel=item['vessel_name'],
origin=item['load_disch_zone'],
dest=item['current_zone'],
end_date=import_date,
)
if trade:
break
# obtain lay_can dates from trade
if trade:
# lay_can_start is 2 days before origin date,
# lay_can_end is 1 day after origin date (c.f. analysts)
lay_can = parse_date(trade['Date (origin)'], dayfirst=False)
lay_can_start = (lay_can - dt.timedelta(days=2)).isoformat()
lay_can_end = (lay_can + dt.timedelta(days=1)).isoformat()
else:
lay_can_start = None
lay_can_end = None
else:
lay_can_start = None
lay_can_end = None
return lay_can_start, lay_can_end
def process_item(raw_item: Dict[str, Any]) -> Dict[str, Any]:
item = map_keys(raw_item, field_mapping())
item['vessel'] = search_vessel_name(item['raw_string'])
if not item['vessel'] or item['vessel']['name'].replace(' ', '') == 'tbn':
return
# build cargo sub model
item['cargo'] = search_cargo_details(item['raw_string'])
item['eta'] = search_eta(item['raw_string'], item['reported_date'])
item['berthed'] = search_etb(item['raw_string'], item['reported_date'])
item['departure'] = search_etd(item['raw_string'], item['reported_date'])
# since the source doesn't provide lay_can information,
# we need to use Kp_api to get the trades info and from that we get lay_can dates
for date_col in ('departure', 'eta', 'arrival', 'berthed',
'cargo_movement'):
if item.get(date_col):
_trade = None
# get trade from either oil or cpp platform
for platform in ('oil', 'cpp'):
_trade = kp_api.get_session(platform,
recreate=True).get_import_trade(
vessel=item['vessel']['name'],
origin='',
dest=item.get('port_name'),
end_date=item.get(date_col),
)
if _trade:
break
post_process_import_item(item, _trade)
break
else:
spider.MISSING_ROWS.append(item['raw_string'])
return
for col in (
'berthed',
'eta',
'departure',
'berthed',
'raw_string',
'port_name',
'cargo_movement',
):
item.pop(col, None)
return item
def process_item(raw_item):
"""Transform raw item into a usable event.
Args:
raw_item (Dict[str, str]):
Returns:
Dict[str, str]:
"""
item = map_keys(raw_item, charters_mapping(), skip_missing=True)
# discard items with no charterer field
if 'charterer' not in item.keys():
return
item['vessel'] = {'name': item.pop('vessel_name')}
item['cargo_volume'], item['cargo_units'] = normalize_volume_unit(
item['cargo_volume_unit'])
# build cargo sub-model
item['cargo'] = {
'product': item.pop('cargo_product', None),
'movement': 'load',
'volume': item.pop('cargo_volume', None),
'volume_unit': item.pop('cargo_units', None),
}
# FIXME: API trick does not work if products were intentionally left out
# to use veson to back calculate
_trade = None
# get trade from cpp or lpg platform
if item['lay_can_start']:
_trade = kp_api.get_session('coal', recreate=True).get_import_trade(
vessel=item['vessel']['name'],
origin=item['departure_zone'],
dest=item['arrival_zone'][0],
end_date=item['lay_can_start'],
)
# mutate item with relevant laycan periods and load/discharge port info
post_process_import_item(item, _trade)
item.pop('cargo_volume_unit', None)
return item
def process_item(raw_item):
"""Transform raw item into a usable event.
Args:
raw_item (Dict[str, str]):
Returns:
Dict[str, str]:
"""
item = map_keys(raw_item, field_mapping())
# remove vessels not named yet
if not item['vessel']['name']:
return
if item['raw_port_name']:
item['port_name'] = item['raw_port_name']
item.pop('raw_port_name', None)
item['cargo_unit'] = Unit.tons if item['cargo_volume'] else None
# build cargo sub model
item['cargo'] = {
'product': may_strip(item.pop('cargo_product')),
'movement': 'load',
'volume': item.pop('cargo_volume', None),
'volume_unit': item.pop('cargo_unit', None),
}
# check if vessel movement is import/export
# export movements can just be returned as is, since a SpotCharter is defined by exports
if item['is_export'] == 'load':
item['departure_zone'] = item['port_name']
item['arrival_zone'] = [item['next_zone']]
# import movements needed to be treated specially to obtain the proper laycan dates
else:
_trade = None
# get trade from cpp or oil platform
_platforms = ['oil', 'cpp']
if item['lay_can_start']:
for platform in _platforms:
_trade = kp_api.get_session(platform,
recreate=True).get_import_trade(
vessel=item['vessel']['name'],
origin=item['previous_zone'],
dest=item['port_name'],
end_date=item['lay_can_start'],
)
if _trade:
break
# mutate item with relevant laycan periods and load/discharge port info
post_process_import_item(item, _trade)
for x in [
'raw_port_name', 'port_name', 'previous_zone', 'next_zone',
'is_export'
]:
item.pop(x, None)
return item
def process_item(raw_item):
"""Transform raw item into a usable event.
Args:
raw_item (Dict[str, str]):
Returns:
Dict[str, str]:
"""
item = map_keys(raw_item, charters_mapping(), skip_missing=True)
# completely disregard cancelled vessel movements
if item['cancelled']:
return
# remove vessels that have not been named
if 'TBN' in item['vessel_name']:
return
item['vessel'] = {
'name': item.pop('vessel_name'),
'imo': item.pop('vessel_imo')
}
if not item['lay_can_start']:
item['lay_can_start'] = item['lay_can_start_alt']
# check if vessel movement is import/export
# export movements can just be returned as is, since a SpotCharter is defined by exports
if item['is_export']:
item['departure_zone'] = item['current_zone']
item['arrival_zone'] = [item['next_zone']]
# import movements needed to be treated specially to obtain the proper laycan dates
else:
_trade = None
# get trade from cpp or lpg platform
if item['lay_can_start']:
for platform in ('oil', 'cpp', 'lpg'):
_trade = kp_api.get_session(platform,
recreate=True).get_import_trade(
vessel=item['vessel']['name'],
origin=item['previous_zone'],
dest=item['current_zone'],
end_date=item['lay_can_start'],
)
if _trade:
break
# mutate item with relevant laycan periods and load/discharge port info
post_process_import_item(item, _trade)
# build cargo sub-model for lpg product
if item.get('cargo_product'):
# because the volume/product column may be swapped, we need to check before we normalize
# the product/volume individually
if item['cargo_product'][0].isdigit():
item['cargo_volume'], item['cargo_product'] = (
item['cargo_product'],
item['cargo_volume'],
)
volume, unit = normalize_cargo_volume(item['cargo_volume'])
item['cargo'] = {
'product': item.pop('cargo_product', None),
'movement': 'load',
'volume': volume,
'volume_unit': unit,
}
# pop for crude oil, granular grades inserted in grades spider
if item['cargo']['volume_unit'] == Unit.barrel:
item['cargo']['product'] = 'crude oil'
# cleanup unused fields
for field in (
'previous_zone',
'current_zone',
'next_zone',
'is_export',
'lay_can_start_alt',
'cancelled',
'cargo_product',
'cargo_volume',
):
item.pop(field, None)
return item
def process_item(raw_item):
"""Transform raw item into a usable event.
Args:
raw_item (Dict[str, str]):
Returns:
Dict[str, str]:
"""
item = map_keys(raw_item, charters_mapping())
# discard items without vessel names
vessel_name, charter_status = item.pop('vessel_name_and_charter_status')
if not vessel_name:
logger.warning(f'Item has no vessel name, discarding:\n{item}')
return
# assign charter status, if any
item['status'] = CHARTER_MAPPING.get(charter_status.lower())
# build a proper vessel dict according to Vessel model
item['vessel'] = {'name': vessel_name, 'imo': item.pop('vessel_imo', None)}
# get buyer or seller
if item['is_export']:
player = 'seller'
else:
player = 'buyer'
cargo_player = item.pop('buyer_seller', None)
# build a proper cargo dict according to Cargo model
item['cargo'] = {
'product': item.pop('product', None),
'volume': item.pop('volume', None),
'volume_unit': Unit.tons,
'movement': 'load',
player: {
'name': cargo_player
} if cargo_player else None,
}
# sometimes, `lay_can_start` may be missing. we'll use alternate date
if not item.get('lay_can_start'):
item['lay_can_start'] = item.pop('lay_can_start_alt')
# post-process import spot charters
# this is necessary since by default,
# spot charters are defined by their export dates, not import dates
if not item['is_export']:
_trade = None
if item['lay_can_start']:
# get trade from either oil or cpp platform
for platform in ('oil', 'cpp'):
_trade = kp_api.get_session(platform,
recreate=True).get_import_trade(
vessel=item['vessel']['name'],
origin=item['previous_zone'],
dest=item['current_zone'],
end_date=item['lay_can_start'],
)
if _trade:
break
# mutate item with relevant laycan periods and load/discharge port info
post_process_import_item(item, _trade)
# discard irrelevant fields
for field in ('lay_can_start_alt', 'is_export', 'current_zone',
'previous_zone'):
item.pop(field, None)
# need cargo info for BMS_Charters_Clean
if raw_item['spider_name'] != 'BMS_Charters_Clean':
item.pop('cargo')
yield item
def process_item(raw_item):
"""Transform raw item into a usable SpotCharter event.
Args:
raw_item (Dict[str, str]):
Yields:
Dict[str, str]:
"""
item = map_keys(raw_item, charters_mapping())
# discard vessels if it's yet to be named (TBN)
if not item['vessel']:
return
# only process charterers that carry ethane cargos
if 'ethane' not in item['cargo_product'].lower():
return
for col in ('berthed', 'eta', 'departure'):
if item.get(col):
item[col] = normalize_dates(item[col], item['reported_date'])
item['lay_can_start'] = None
while not item['lay_can_start']:
for col in ('berthed', 'eta', 'departure'):
if item.get(col) and item[col]:
item['lay_can_start'] = item[col]
break
break
# process "import" portcalls accordingly (charters are defined by their departure)
if 'discharge' in item['cargo_movement']:
item['arrival_zone'] = item.pop('zone', None)
logger.info(
f'Attempting to find matching export port call for {item["vessel"]["name"]}'
)
# get trade from oil platform
_trade = kp_api.get_session('lpg', recreate=True).get_import_trade(
vessel=item['vessel']['name'],
origin='',
dest=item['arrival_zone'],
end_date=item['lay_can_start'],
)
# mutate item with relevant laycan periods and load/discharge port info
_process_import_charter(item, _trade)
if 'load' in item['cargo_movement']:
item['departure_zone'] = item.pop('zone', None)
volume, units, grades = normalize_vol_unit(item.pop('cargo_volume', None),
item.pop('cargo_product', None))
# build Cargo sub-model
item['cargo'] = {
'product': grades,
'movement': item.pop('cargo_movement', None),
'volume': volume,
'volume_unit': units,
}
for _col in ('berthed', 'eta', 'departure'):
item.pop(_col, None)
return item
def process_item(raw_item):
"""Transform raw item into a usable event.
Args:
raw_item (Dict[str, str]):
Returns:
Dict[str, str]:
"""
item = map_keys(raw_item, charters_mapping())
# extract relevant months, there is a risk of scraping January again for current year and the
# next year
if item['month'] not in extract_relevant_month(item['reported_date']):
return
# remove vessels not named
if 'TBN' in item['vessel']['name']:
return
# build a proper cargo dict according to Cargo model
item['cargo'] = {
'product': item.pop('product', None),
'volume': item.pop('volume', None),
'volume_unit': Unit.kilotons,
'movement': 'load',
}
if 'discharge' in item['is_export']:
_trade = None
if item['lay_can_start']:
# get trade from either oil or cpp platform
for platform in ('oil', 'cpp'):
_trade = kp_api.get_session(platform,
recreate=True).get_import_trade(
vessel=item['vessel']['name'],
origin=item.get(
'previous_port', ''),
dest=item['departure_zone'],
end_date=item['lay_can_start'],
)
if _trade:
break
# mutate item with relevant laycan periods and load/discharge port info
post_process_import_item(item, _trade)
if 'load' in item['is_export'] and 'Klaipeda' in (item['sheet_name'] and
item['arrival_zone']):
# data can be incorrect in klaipeda sheet, arrival is the same as departure
# when is_export = load, replace such instances with a blank
item['arrival_zone'] = ''
if item['spider_name'] == 'UN_KlaipedaButinge_Fixtures_OIL':
item.pop('cargo', None)
for field in ('is_export', 'sheet_name', 'spider_name', 'previous_port',
'month'):
item.pop(field, None)
return item
