Get your personal Signal Ocean API subscription key (acquired here) and replace it below:
signal_ocean_api_key = '' # Replace with your subscription key
Scraped Fixtures API¶
The goal of Scraped Fixtures API is to collect and return scraped fixtures by the given filters or fixture IDs. This can be done by using the ScrapedFixturesAPI
class and calling appropriate methods
1. Request by filters¶
Fixtures can be retrieved for specific filters, by calling the get_fixtures
method with the following arguments:
Required¶
vessel_type
The vessel type
Additionally, at least one of the following is required
imos
List of IMOs
message_ids
List of MessageIDs
external_message_ids
List of ExternalMessageIDs
received_date_from
Earliest date the fixture received
received_date_to
Latest date the fixture received
updated_date_from
Earliest date the fixture updated
updated_date_to
Latest date the fixture updated
Mixing received and updated dates is not allowed
It's highly recommended to use UTC dates, since this is the internally used format
2. Request by fixture IDs¶
Fixtures can be retrieved for specific fixture IDs, by calling the get_fixtures_by_fixture_ids
method with the following argument:
Required¶
fixture_ids
A list of fixture ids to retrieve
Additional optional arguments¶
Both methods, also accept the following optional arguments:
include_details
If this field is True
the following columns will be included in the response (otherwise they will be None
):
parsed_part_id, line_from, line_to, in_line_order, source
include_scraped_fields
If this field is True
the following columns will be included in the response (otherwise they will be None
):
scraped_vessel_name, scraped_deadweight, scraped_year_built, scraped_laycan, scraped_load, scraped_load2, scraped_discharge, scraped_discharge_options, scraped_discharge2, scraped_charterer, scraped_cargo_type, scraped_quantity, scraped_rate, scraped_rate_options, scraped_delivery_date, scraped_delivery, scraped_redelivery_from, scraped_redelivery_to
include_vessel_details
If this field is True
the following columns will be included in the response (otherwise they will be None
):
vessel_name, deadweight, year_built, liquid_capacity, vessel_type_id, vessel_type, vessel_class, commercial_operator_id, commercial_operator
include_labels
If this field is True
the following columns will be included in the response (otherwise they will be None
):
load_name, load_taxonomy, load_name2, load_taxonomy2, discharge_name, discharge_taxonomy, discharge_name2, discharge_taxonomy2, charterer, cargo_type, cargo_group, open_geo_name, open_taxonomy, delivery_name, delivery_taxonomy, redelivery_from_name, redelivery_from_taxonomy, redelivery_to_name, redelivery_to_taxonomy, charter_type, fixture_status
include_content
If this field is True
the following columns will be included in the response (otherwise they will be None
):
content
include_sender
If this field is True
the following columns will be included in the response (otherwise they will be None
):
sender
include_debug_info
If this field is True
the following columns will be included in the response (otherwise they will be None
):
is_private, is_invalidated, is_partial
Default value is
True
for the arguments described above
Installation¶
To install Signal Ocean SDK, simply run the following command
%%capture
%pip install signal-ocean
Quickstart¶
Import signal-ocean
and other modules required for this demo
from signal_ocean import Connection
from signal_ocean.scraped_fixtures import ScrapedFixturesAPI
from datetime import datetime, timedelta
import pandas as pd
import plotly.graph_objects as go
Create a new instance of the ScrapedFixturesAPI
class
connection = Connection(signal_ocean_api_key)
api = ScrapedFixturesAPI(connection)
Now you are ready to retrieve your data
Request by date¶
To get all tanker fixtures received the last 2 days, you must declare appropriate vessel_type
and received_date_from
variables
vessel_type = 1 # Tanker
received_date_from = datetime.utcnow() - timedelta(days=2)
And then call get_fixtures
method, as below
scraped_fixtures = api.get_fixtures(
vessel_type=vessel_type,
received_date_from=received_date_from,
)
next(iter(scraped_fixtures), None)
ScrapedFixture(fixture_id=128507741, message_id=47983127, external_message_id=None, parsed_part_id=58837078, line_from=19, line_to=19, in_line_order=None, source='Email', updated_date=datetime.datetime(2023, 9, 25, 3, 6, 39, tzinfo=datetime.timezone.utc), received_date=datetime.datetime(2023, 9, 25, 3, 4, 29, tzinfo=datetime.timezone.utc), is_deleted=False, scraped_vessel_name='minerva vera', scraped_deadweight=None, scraped_year_built=None, imo=9411941, vessel_name='Minerva Vera', deadweight=158022, year_built=2009, liquid_capacity=167711, vessel_type_id=1, vessel_type='Tanker', vessel_class_id=85, vessel_class='Suezmax', commercial_operator_id=962, commercial_operator='Minerva Marine', scraped_laycan='3-oct', laycan_from=datetime.datetime(2023, 10, 3, 0, 0, tzinfo=datetime.timezone.utc), laycan_to=datetime.datetime(2023, 10, 3, 0, 0, tzinfo=datetime.timezone.utc), scraped_load='ras tanura', load_geo_id=3778, load_name='Ras Tanura', load_taxonomy_id=2, load_taxonomy='Port', scraped_load2=None, load_geo_id2=None, load_name2=None, load_taxonomy_id2=None, load_taxonomy2=None, scraped_discharge='mumbai', scraped_discharge_options=None, discharge_geo_id=3526, discharge_name='Mumbai', discharge_taxonomy_id=2, discharge_taxonomy='Port', scraped_discharge2=None, discharge_geo_id2=None, discharge_name2=None, discharge_taxonomy_id2=None, discharge_taxonomy2=None, scraped_charterer='sci', charterer_id=718, charterer='Indian Oil', scraped_cargo_type=None, cargo_type_id=None, cargo_type=None, cargo_group_id=None, cargo_group=None, scraped_quantity='95', quantity=95000.0, quantity_buffer=0.0, quantity_from=95000.0, quantity_to=95000.0, scraped_rate='ws160', scraped_rate_options=None, rate_value=160.0, rate_type='WS', open_geo_id=None, open_geo_name=None, open_taxonomy_id=None, open_taxonomy=None, open_date=None, scraped_delivery_date=None, delivery_date_from=None, delivery_date_to=None, scraped_delivery=None, delivery_geo_id=None, delivery_name=None, delivery_taxonomy_id=None, delivery_taxonomy=None, scraped_redelivery_from=None, redelivery_from_geo_id=None, redelivery_from_name=None, redelivery_from_taxonomy_id=None, redelivery_from_taxonomy=None, scraped_redelivery_to=None, redelivery_to_geo_id=None, redelivery_to_name=None, redelivery_to_taxonomy_id=None, redelivery_to_taxonomy=None, charter_type_id=0, charter_type='Voyage', fixture_status_id=None, fixture_status=None, is_owners_option=False, is_coa=False, content='minerva vera p/c 95 ras tanura / mumbai 3-oct ws160 sci', subject='ALLIANCE SUEZMAX MARKET REPORT 23 SEP 2023', sender='Alliance Tanker', is_private=False, is_invalidated=False, is_partial=False)
For better visualization, it's convenient to insert data into a DataFrame
df = pd.DataFrame(scraped_fixtures)
df.head()
fixture_id | message_id | external_message_id | parsed_part_id | line_from | line_to | in_line_order | source | updated_date | received_date | ... | fixture_status_id | fixture_status | is_owners_option | is_coa | content | subject | sender | is_private | is_invalidated | is_partial | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 128507741 | 47983127 | None | 58837078 | 19 | 19 | NaN | 2023-09-25 03:06:39+00:00 | 2023-09-25 03:04:29+00:00 | ... | NaN | None | False | False | minerva vera p/c 95 ras tanura / mumbai 3-oct ... | ALLIANCE SUEZMAX MARKET REPORT 23 SEP 2023 | Alliance Tanker | False | False | False | |
1 | 128507742 | 47983127 | None | 58837078 | 25 | 25 | NaN | 2023-09-25 03:06:39+00:00 | 2023-09-25 03:04:29+00:00 | ... | NaN | None | False | False | silverway 145 usg / ukcm 3-oct ws45 atmi | ALLIANCE SUEZMAX MARKET REPORT 23 SEP 2023 | Alliance Tanker | False | False | False | |
2 | 128507743 | 47983127 | None | 58837078 | 22 | 22 | NaN | 2023-09-25 03:06:39+00:00 | 2023-09-25 03:04:29+00:00 | ... | NaN | None | False | False | raptor 130 nigeria / ukcm 9-oct ws67.5 csssa | ALLIANCE SUEZMAX MARKET REPORT 23 SEP 2023 | Alliance Tanker | False | False | False | |
3 | 128508076 | 47984493 | None | 58837863 | 59 | 59 | NaN | 2023-09-25 03:26:04+00:00 | 2023-09-25 03:24:16+00:00 | ... | 0.0 | OnSubs | False | False | minerva vera 95p/c 3 oct ras tan/mumbai w160 s... | SSY SINGAPORE MORNING SUEZMAX UPDATE | SSY | False | False | False | |
4 | 128508077 | 47984493 | None | 58837863 | 72 | 72 | NaN | 2023-09-25 03:26:04+00:00 | 2023-09-25 03:24:16+00:00 | ... | 0.0 | OnSubs | False | False | raptor 130 9-10 oct nigeria/ukcm w67.5 total -... | SSY SINGAPORE MORNING SUEZMAX UPDATE | SSY | False | False | False |
5 rows × 101 columns
Request by IMOs¶
To get fixtures for specific vessel(s) by their IMO number(s), you can simple call the get_fixtures
method for a list of desired IMO(s)
Adding some date argument is always feasible
imos = [9534054, 9588469, 9893096, 9453999, 9402328] # Or add a list of your desired IMOs
scraped_fixtures_by_imos = api.get_fixtures(
vessel_type=vessel_type,
received_date_from=received_date_from,
imos=imos,
)
df_by_imos = pd.DataFrame(scraped_fixtures_by_imos)
df_by_imos.head()
fixture_id | message_id | external_message_id | parsed_part_id | line_from | line_to | in_line_order | source | updated_date | received_date | ... | fixture_status_id | fixture_status | is_owners_option | is_coa | content | subject | sender | is_private | is_invalidated | is_partial | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 128604897 | 48067456 | None | 58896627 | 21 | 21 | None | 2023-09-25 17:53:07+00:00 | 2023-09-25 17:49:36+00:00 | ... | 0 | OnSubs | False | True | None | MJLF PANPAC AFRAMAX POSITION LIST | MJLF | False | False | True |
1 rows × 101 columns
Request by Message or ExternalMessage IDs¶
To retrieve fixtures for particular message ID(s), you should include an extra parameter called message_ids
when using the get_fixtures
method. This parameter should contain a list of message IDs. For instance,
message_ids = [47406384, 47413937, 47415977]
scraped_fixtures_by_message_ids = api.get_fixtures(
vessel_type=vessel_type,
message_ids=message_ids,
)
next(iter(scraped_fixtures_by_message_ids), None)
ScrapedFixture(fixture_id=127585222, message_id=47406384, external_message_id=None, parsed_part_id=58412545, line_from=32, line_to=32, in_line_order=None, source='Email', updated_date=datetime.datetime(2023, 9, 13, 13, 16, 58, tzinfo=datetime.timezone.utc), received_date=datetime.datetime(2023, 9, 13, 13, 14, 52, tzinfo=datetime.timezone.utc), is_deleted=False, scraped_vessel_name='tbn', scraped_deadweight=None, scraped_year_built=None, imo=None, vessel_name=None, deadweight=None, year_built=None, liquid_capacity=None, vessel_type_id=0, vessel_type=None, vessel_class_id=None, vessel_class=None, commercial_operator_id=None, commercial_operator=None, scraped_laycan='01/oct', laycan_from=datetime.datetime(2023, 10, 1, 0, 0, tzinfo=datetime.timezone.utc), laycan_to=datetime.datetime(2023, 10, 1, 0, 0, tzinfo=datetime.timezone.utc), scraped_load='wafr', load_geo_id=24772, load_name='Africa Atlantic Coast', load_taxonomy_id=4, load_taxonomy='Level0', scraped_load2=None, load_geo_id2=None, load_name2=None, load_taxonomy_id2=None, load_taxonomy2=None, scraped_discharge='ukcm', scraped_discharge_options='usac-abidjan', discharge_geo_id=25025, discharge_name='Mediterranean / UK Continent', discharge_taxonomy_id=6, discharge_taxonomy='Level2', scraped_discharge2=None, discharge_geo_id2=None, discharge_name2=None, discharge_taxonomy_id2=None, discharge_taxonomy2=None, scraped_charterer='shell', charterer_id=1482, charterer='Shell', scraped_cargo_type='nhc', cargo_type_id=19, cargo_type='Crude Oil', cargo_group_id=130000, cargo_group='Dirty', scraped_quantity='130', quantity=130000.0, quantity_buffer=0.0, quantity_from=130000.0, quantity_to=130000.0, scraped_rate='rnr', scraped_rate_options=None, rate_value=None, rate_type=None, open_geo_id=None, open_geo_name=None, open_taxonomy_id=None, open_taxonomy=None, open_date=None, scraped_delivery_date=None, delivery_date_from=None, delivery_date_to=None, scraped_delivery=None, delivery_geo_id=None, delivery_name=None, delivery_taxonomy_id=None, delivery_taxonomy=None, scraped_redelivery_from=None, redelivery_from_geo_id=None, redelivery_from_name=None, redelivery_from_taxonomy_id=None, redelivery_from_taxonomy=None, scraped_redelivery_to=None, redelivery_to_geo_id=None, redelivery_to_name=None, redelivery_to_taxonomy_id=None, redelivery_to_taxonomy=None, charter_type_id=0, charter_type='Voyage', fixture_status_id=None, fixture_status=None, is_owners_option=False, is_coa=False, content='tbn 130 nhc wafr/ukcm-usac-abidjan 01/oct rnr shell-rplc', subject='Fixture Report for 13-Sep-2023', sender='Southport Maritime', is_private=False, is_invalidated=False, is_partial=False)
You can achieve a similar result for external message IDs by providing an argument called external_message_ids
.
Request by Fixture IDs¶
In the same manner, to get data for specific fixtures ID(s), you must call the get_fixtures_by_fixture_ids
method for a list of desired fixture ID(s)
Date arguments are not available in this method
fixture_ids = [102145930, 102078599, 102080476, 102095717, 102078587] # Or add a list of your desired fixture IDs
scraped_fixtures_by_ids = api.get_fixtures_by_fixture_ids(
fixture_ids=fixture_ids,
)
df_by_ids = pd.DataFrame(scraped_fixtures_by_ids)
df_by_ids.head()
fixture_id | message_id | external_message_id | parsed_part_id | line_from | line_to | in_line_order | source | updated_date | received_date | ... | fixture_status_id | fixture_status | is_owners_option | is_coa | content | subject | sender | is_private | is_invalidated | is_partial | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 102078587 | 30802065 | None | 45796535 | 62 | 62 | None | 2022-11-17 16:50:38+00:00 | 2022-11-17 16:47:52+00:00 | ... | 0.0 | OnSubs | False | False | shenlong spirit bahamas 17 nov - subs | EVENING SUEZMAX FIXTURE REPORT FROM SIMPSON SP... | SSY | False | False | True | |
1 | 102078599 | 30802065 | None | 45796535 | 76 | 76 | None | 2022-11-17 16:50:38+00:00 | 2022-11-17 16:47:52+00:00 | ... | 0.0 | OnSubs | False | False | ridgebury john zipser 130 25-26 nov wafr/ukc w... | EVENING SUEZMAX FIXTURE REPORT FROM SIMPSON SP... | SSY | False | False | False | |
2 | 102080476 | 30802185 | None | 45796726 | 49 | 49 | None | 2022-11-17 16:53:58+00:00 | 2022-11-17 16:52:03+00:00 | ... | NaN | None | False | False | red nova 270 10/12 usg/ukc $8.0m p66 | SIMPSON | SPENCE | YOUNG LONDON VLCC COB REPOR... | SSY | False | False | False | |
3 | 102095717 | 30806098 | None | 45801752 | 74 | 74 | None | 2022-11-17 23:04:04+00:00 | 2022-11-17 23:00:57+00:00 | ... | 0.0 | OnSubs | False | False | nordic cross 145 usg/ukcm 27-28nov w200 exxon ... | THE MJLF DIRTY DISH - NOVEMBER 17, 2022 | MJLF | False | False | False | |
4 | 102145930 | 30841280 | None | 45829800 | 46 | 46 | None | 2022-11-18 11:45:18+00:00 | 2022-11-18 11:43:41+00:00 | ... | NaN | None | False | False | tbn 260 18/12 wafr/ukc w142.5 equinor | SIMPSON | SPENCE | YOUNG LONDON VLCC MORNING R... | SSY | False | False | False |
5 rows × 101 columns
Usage of optional arguments¶
By default, all fields are returned. In many cases, it is convenient to select specific columns. For example, if we want to compare scraped and mapped fields
scraped_mapped_columns = [
'scraped_vessel_name',
'vessel_name',
'scraped_quantity',
'quantity',
'scraped_charterer',
'charterer',
'scraped_laycan',
'laycan_from',
'laycan_to',
]
scraped_mapped_df = pd.DataFrame(scraped_fixtures, columns=scraped_mapped_columns)
scraped_mapped_df.head()
scraped_vessel_name | vessel_name | scraped_quantity | quantity | scraped_charterer | charterer | scraped_laycan | laycan_from | laycan_to | |
---|---|---|---|---|---|---|---|---|---|
0 | minerva vera | Minerva Vera | 95 | 95000.0 | sci | Indian Oil | 3-oct | 2023-10-03 00:00:00+00:00 | 2023-10-03 00:00:00+00:00 |
1 | silverway | Silverway | 145 | 145000.0 | atmi | Atlantic Trading and Marketing | 3-oct | 2023-10-03 00:00:00+00:00 | 2023-10-03 00:00:00+00:00 |
2 | raptor | Raptor | 130 | 130000.0 | csssa | Total | 9-oct | 2023-10-09 00:00:00+00:00 | 2023-10-09 00:00:00+00:00 |
3 | minerva vera | Minerva Vera | 95 | 95000.0 | sci | Indian Oil | 3 oct | 2023-10-03 00:00:00+00:00 | 2023-10-03 00:00:00+00:00 |
4 | raptor | Raptor | 130 | 130000.0 | total | Total | 9-10 oct | 2023-10-09 00:00:00+00:00 | 2023-10-10 00:00:00+00:00 |
Examples¶
Let's start by fetching all tanker fixtures received the last week
example_vessel_type = 1 # Tanker
example_date_from = datetime.utcnow() - timedelta(days=7)
example_scraped_fixtures = api.get_fixtures(
vessel_type=example_vessel_type,
received_date_from=example_date_from,
)
Exclude deleted scraped fixtures¶
The is_deleted
property of a scraped fixture indicates whether it is valid or not. If it is set to True
, the corresponding fixture_id
has been replaced by a new one.
For the sake of completeness, we will exclude deleted scraped fixtures in the following examples.
example_scraped_fixtures = [fixture for fixture in example_scraped_fixtures if not fixture.is_deleted]
next(iter(example_scraped_fixtures), None)
ScrapedFixture(fixture_id=128033504, message_id=47718568, external_message_id=None, parsed_part_id=58639629, line_from=57, line_to=57, in_line_order=None, source='Email', updated_date=datetime.datetime(2023, 9, 19, 11, 9, 47, tzinfo=datetime.timezone.utc), received_date=datetime.datetime(2023, 9, 19, 11, 7, 27, tzinfo=datetime.timezone.utc), is_deleted=False, scraped_vessel_name='dht taiga', scraped_deadweight=None, scraped_year_built=None, imo=9590888, vessel_name='DHT Taiga', deadweight=318130, year_built=2012, liquid_capacity=345527, vessel_type_id=1, vessel_type='Tanker', vessel_class_id=84, vessel_class='VLCC', commercial_operator_id=404, commercial_operator='DHT Holdings', scraped_laycan='19/10', laycan_from=datetime.datetime(2023, 10, 19, 0, 0, tzinfo=datetime.timezone.utc), laycan_to=datetime.datetime(2023, 10, 19, 0, 0, tzinfo=datetime.timezone.utc), scraped_load='brazil', load_geo_id=38, load_name='Brazil', load_taxonomy_id=3, load_taxonomy='Country', scraped_load2=None, load_geo_id2=None, load_name2=None, load_taxonomy_id2=None, load_taxonomy2=None, scraped_discharge='china', scraped_discharge_options=None, discharge_geo_id=55, discharge_name='China', discharge_taxonomy_id=3, discharge_taxonomy='Country', scraped_discharge2=None, discharge_geo_id2=None, discharge_name2=None, discharge_taxonomy_id2=None, discharge_taxonomy2=None, scraped_charterer='petrobras', charterer_id=1242, charterer='Petrobras', scraped_cargo_type=None, cargo_type_id=None, cargo_type=None, cargo_group_id=None, cargo_group=None, scraped_quantity='260', quantity=260000.0, quantity_buffer=0.0, quantity_from=260000.0, quantity_to=260000.0, scraped_rate='w45', scraped_rate_options=None, rate_value=45.0, rate_type='WS', open_geo_id=None, open_geo_name=None, open_taxonomy_id=None, open_taxonomy=None, open_date=None, scraped_delivery_date=None, delivery_date_from=None, delivery_date_to=None, scraped_delivery=None, delivery_geo_id=None, delivery_name=None, delivery_taxonomy_id=None, delivery_taxonomy=None, scraped_redelivery_from=None, redelivery_from_geo_id=None, redelivery_from_name=None, redelivery_from_taxonomy_id=None, redelivery_from_taxonomy=None, scraped_redelivery_to=None, redelivery_to_geo_id=None, redelivery_to_name=None, redelivery_to_taxonomy_id=None, redelivery_to_taxonomy=None, charter_type_id=0, charter_type='Voyage', fixture_status_id=None, fixture_status=None, is_owners_option=False, is_coa=False, content='dht taiga 260 19/10 brazil/china w45 petrobras', subject='SIMPSON | SPENCE | YOUNG LONDON VLCC MORNING REPORT 19TH SEPTEMBER 2023', sender='SSY', is_private=False, is_invalidated=False, is_partial=False)
Now, we are ready to insert our data into a dataframe and keep only specific fields
example_columns = [
'imo',
'charterer',
'laycan_from',
'load_name',
'is_deleted',
]
data = pd.DataFrame(example_scraped_fixtures, columns=example_columns).astype({'imo': 'Int64'})
data.head()
imo | charterer | laycan_from | load_name | is_deleted | |
---|---|---|---|---|---|
0 | 9590888 | Petrobras | 2023-10-19 00:00:00+00:00 | Brazil | False |
1 | 9294305 | PTT Public Company Limited | 2023-10-01 00:00:00+00:00 | Arabian Gulf | False |
2 | 9838008 | None | NaT | Arabian Gulf | False |
3 | 9858474 | Equinor | 2023-10-01 00:00:00+00:00 | US Gulf | False |
4 | 9312494 | Trafigura | 2023-10-15 00:00:00+00:00 | US Gulf | False |
Top 10 Charterers¶
In this example, we will find the top 10 Charterers, based on the number of distinct voyages
top_chrtr_ser = data[['charterer', 'imo']].drop_duplicates().charterer.value_counts().head(10)
top_chrtr_df = top_chrtr_ser.to_frame(name='FixtureCount').reset_index().rename(columns={'index': 'Charterer'})
top_chrtr_df
charterer | FixtureCount | |
---|---|---|
0 | Unipec | 44 |
1 | Aramco Trading Company | 34 |
2 | Total | 32 |
3 | BP | 28 |
4 | Vitol | 27 |
5 | ExxonMobil | 25 |
6 | Shell | 18 |
7 | Trafigura | 17 |
8 | Repsol | 14 |
9 | Admic | 13 |
And display results in a bar plot
top_chrtr_fig = go.Figure()
bar = go.Bar(
x=top_chrtr_df.charterer.tolist(),
y=top_chrtr_df.FixtureCount.tolist(),
)
top_chrtr_fig.add_trace(bar)
top_chrtr_fig.update_xaxes(title_text="Charterer")
top_chrtr_fig.update_yaxes(title_text="Number of Voyages")
top_chrtr_fig.show()
Vessels loading in specific areas¶
In this example, we will create a visualization, for the number of distinct vessels loading in specific areas per day over the next week
this_week_days = pd.date_range(start=datetime.utcnow().date(), freq='D', periods=7, tz='UTC')
areas = data[data.load_name.notna()].load_name.value_counts().head().index.tolist()
areas
['Arabian Gulf', 'US Gulf', 'Korea, Republic of', 'Continent', 'Singapore']
Create the pivot table
areas_mask = data.load_name.isin(areas) & data.laycan_from.isin(this_week_days)
df_areas = data[areas_mask]
df_pivot = pd.pivot_table(
df_areas,
columns='load_name',
index='laycan_from',
values='imo',
aggfunc=pd.Series.nunique,
fill_value=0,
).reindex(index=this_week_days, fill_value=0).reset_index().rename(columns={'index': 'laycan_from'})
df_pivot
load_name | laycan_from | Arabian Gulf | Continent | Korea, Republic of | Singapore | US Gulf |
---|---|---|---|---|---|---|
0 | 2023-09-26 00:00:00+00:00 | 1 | 1 | 3 | 2 | 5 |
1 | 2023-09-27 00:00:00+00:00 | 1 | 2 | 2 | 2 | 0 |
2 | 2023-09-28 00:00:00+00:00 | 11 | 0 | 6 | 3 | 3 |
3 | 2023-09-29 00:00:00+00:00 | 5 | 0 | 2 | 0 | 4 |
4 | 2023-09-30 00:00:00+00:00 | 4 | 2 | 1 | 3 | 2 |
5 | 2023-10-01 00:00:00+00:00 | 13 | 1 | 2 | 2 | 3 |
6 | 2023-10-02 00:00:00+00:00 | 8 | 3 | 0 | 2 | 1 |
And display the results as timeseries
def area_button(area):
args = [
{'visible': [i == areas.index(area) for i in range(len(areas))]},
{
'title': f'Vessels loading in {area} per day',
'showlegend': True
},
]
return dict(
label=area,
method='update',
args=args,
)
title = 'Vessels loading per day'
today = datetime.combine(datetime.utcnow().date(), datetime.min.time())
areas_fig = go.Figure()
area_buttons = []
for area in areas:
if area not in df_pivot.columns:
continue
area_scatter_plot = go.Scatter(
x=df_pivot.laycan_from,
y=df_pivot[area],
name=area,
mode='lines',
)
areas_fig.add_trace(area_scatter_plot)
area_buttons.append(area_button(area))
buttons = list([
dict(
label='All',
method='update',
args=[
{'visible': [True for _ in range(len(areas))]},
{
'title': title,
'showlegend': True
}
],
),
*area_buttons,
])
areas_fig.update_layout(
title=title,
updatemenus=[go.layout.Updatemenu(
active=0,
buttons=buttons,
)],
xaxis_range=[today - timedelta(hours=4), today + timedelta(hours=24*6 + 4)],
)
areas_fig.show()
Export data to csv¶
output_path = '' # Change output_path with your path
filename = 'last_week_fixtures.csv'
if not data.empty:
data.to_csv(output_path+filename, index=False)