Python for the Energy Industry
We can filter Cargo Movements by the carried product in much the same way as we filter by geography.
Products endpoint documentation
We start by doing a search for 'crude':
# Some initial imports / settings
from datetime import datetime
from dateutil.relativedelta import relativedelta
import vortexasdk as v
now = datetime.utcnow()
one_month_ago = now - relativedelta(months=1)
crude_search = v.Products().search(term=['crude'])
2020-12-10 16:47:54,856 vortexasdk.client — WARNING — You are using vortexasdk version 0.26.0, however version 0.27.1 is available. You should consider upgrading via the 'pip install vortexasdk --upgrade' command.
len(crude_search)
19
We get 19 results. Inspecting the first of these, we can see there is a product name and ID, plus information about parent products.
crude_search[0]
{'id': 'e1b3d075a9340969322024a35b8c720e5065d8c95a91b6d2cda2cb06dd707717', 'name': 'Crude Vegoil', 'layer': ['grade'], 'leaf': True, 'parent': [{'name': 'Biodiesel Feedstock', 'layer': ['category'], 'id': '9d52ede1cff0421a8cd7283b0171afe8d23f519dca5f4e489734522f9cdf804c'}], 'meta': {}, 'ref_type': 'product', 'hierarchy': [{'id': 'b68cbb746f8b9098c50e2ba36bcad83001a53bd362e9031fb49085d02c36659c', 'layer': 'group', 'label': 'Clean Petroleum Products'}, {'id': 'a75fcc09bfc7d16496de3336551bc52b5891838bb7c22356d2cb65451587d1e5', 'layer': 'group_product', 'label': 'Biodiesel'}, {'id': '9d52ede1cff0421a8cd7283b0171afe8d23f519dca5f4e489734522f9cdf804c', 'layer': 'category', 'label': 'Biodiesel Feedstock'}, {'id': 'e1b3d075a9340969322024a35b8c720e5065d8c95a91b6d2cda2cb06dd707717', 'layer': 'grade', 'label': 'Crude Vegoil'}]}
Lets take a look at all the products returned by our search:
crude_search.to_df()
id | name | layer.0 | parent.0.name | |
---|---|---|---|---|
0 | e1b3d075a9340969322024a35b8c720e5065d8c95a91b6... | Crude Vegoil | grade | Biodiesel Feedstock |
1 | 6f11b0724c9a4e85ffa7f1445bc768f054af755a090118... | Crude | group_product | Crude/Condensates |
2 | 4fe046d7478c4072b9ecbaa2c03d3b977bbfec5077ee44... | Crude Butadiene | grade | Olefins/Other Chemicals |
3 | 54af755a090118dcf9b0724c9a4e9f14745c26165385ff... | Crude/Condensates | group | NaN |
4 | 9fda040ee8844e47b5239051e322d06dd9d2b96f0c3249... | TPAO crude | grade | Medium-Sour |
5 | 2b76490350005604507ca64567101df7d3db80a973f462... | Crude Vegetable Oil | grade | Biodiesel Feedstock |
6 | f26cd12252b0bb23b4ab4ab590eaee11037d78a7ac5cc6... | Crude Benzene | grade | Chemicals |
7 | e9d1031a9167fff80ef089f2d5591deb1833ee34f1d028... | Crude Palm Oil | grade | Biodiesel Feedstock |
8 | c2aa8895d3d176868cbb0519f2bb5318a74a2a66ac9034... | Eagle Ford crude | grade | Light-Sweet |
9 | 164a4a510343458bbf949a79964ce3bb8efa8d14276c48... | US Shale Crude | grade | Light-Sweet |
10 | 1dfa78c965d56ab9d7b953e424d9160e9f79d0275e460c... | Kaliningrad light crude | grade | Light-Sweet |
11 | 4ff810b966104a658d22e7155596c11bedcd1e3e81594e... | Yuri Korchagin Crude | grade | Light-Sweet |
12 | 6fd8a34225de493ab9bff7f95880d96a3e34737db24849... | Domestic Sweet Crude (DSW) | grade | Light-Sour |
13 | c49a65c1651913a1cb3b185760ab952827b5b9ddc03e1e... | Crude Soybean Oil (CSBO) | grade | Biodiesel Feedstock |
14 | c7bee2499ebba9677e11930891fdd067c293b8536efe53... | Crude Sunflower Oil (CSFO) | grade | Biodiesel Feedstock |
15 | c4ad8221d48642b2adc7b363c68a3d729b3ef7f2d9aa42... | Diluted Crude Oil (DCO) | grade | Heavy-Sour |
16 | 98fa8034b122632b13564878dd75d902faa735b822cac9... | Crude Degummed Soybean Oil | grade | Biodiesel Feedstock |
17 | bc9deabd28d74b5985056195dcbcd33ccae7c616e2584a... | Reconstituted Crude Oil (Recon) | grade | Dirty Condensates |
18 | ca15a14cae72539854f53413a0f8668bcca2ec90053722... | Crude Blendstock for Oxygenate Blending | grade | Blending Components |
There are different layers of product type, ranging from the broad group 'Crude/Condensates', down to specific grades. We can grab the ID of the Crude group/product to filter on.
An assert statement will check that we only get one result for our exact term match.
# Grab the ID for Crude
crude_search = v.Products().search(term=['Crude'],exact_term_match=True)
assert len(crude_search) == 1
crude_ID = crude_search[0]['id']
cm_crude_query = v.CargoMovements().search(
filter_activity="loading_state",
filter_time_min=one_month_ago,
filter_time_max=now,
filter_products=crude_ID)
Loading from API: 2500it [00:01, 1318.26it/s]
As before, we pick the colulmns of interest from our Cargo Movements search and make a DataFrame.
required_columns = ["vessels.0.name","vessels.0.vessel_class","product.group.label","product.grade.label","quantity",
"status","events.cargo_port_load_event.0.location.port.label","events.cargo_port_load_event.0.end_timestamp",
"events.cargo_port_unload_event.0.location.port.label","events.cargo_port_unload_event.0.end_timestamp"]
new_labels = ["vessel_name","vessel_class","product_group","product_grade","quantity","status","loading_port","loading_finish","unloading_port","unloading_finish"]
relabel = dict(zip(required_columns,new_labels))
cm_crude_query.to_df(columns=required_columns).rename(relabel,axis=1)
vessel_name | vessel_class | product_group | product_grade | quantity | status | loading_port | loading_finish | unloading_port | unloading_finish | |
---|---|---|---|---|---|---|---|---|---|---|
0 | ASAHI PRINCESS | aframax | Crude/Condensates | NaN | 190455 | unloading_state | Malampaya [PH] | 2020-12-05 05:59:44+00:00 | Map Ta Phut [TH] | NaT |
1 | KALAHARI | aframax | Crude/Condensates | Siberian Light | 585442 | unloaded_state | Novorossiysk [RU] | 2020-11-21 10:52:20+00:00 | Burgas (Bourgas) [BG] | 2020-11-24 19:37:04+00:00 |
2 | M T VEDMATA | tiny_tanker | Crude/Condensates | NaN | 45280 | unloaded_state | Agge [NG] | 2020-11-17 04:38:46+00:00 | Tulja [NG] | 2020-11-17 15:15:33+00:00 |
3 | SHALAMAR | aframax | Crude/Condensates | Arab Light | 730874 | unloaded_state | Ras Tanura [SA] | 2020-12-01 01:30:06+00:00 | Karachi, SD [PK] | 2020-12-08 22:52:33+00:00 |
4 | NEW ENTERPRISE | vlcc_plus | Crude/Condensates | Murban | 1132667 | transiting_state | Fujairah [AE] | 2020-11-21 18:35:28+00:00 | Tomakomai [JP] | NaT |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
2002 | AMAZON FALCON | panamax | Crude/Condensates | Vasconia | 476332 | unloaded_state | Covenas [CO] | 2020-11-16 17:05:04+00:00 | Chiriqui Grande [PA] | 2020-11-19 23:07:19+00:00 |
2003 | A SYMPHONY | suezmax | Crude/Condensates | NaN | 1021406 | storing_state | NaN | 2020-12-08 19:54:29+00:00 | NaN | NaT |
2004 | VAIL SPIRIT | suezmax | Crude/Condensates | Bonny Light | 1004770 | unloaded_state | Bonny Offshore Oil Terminal [NG] | 2020-11-15 03:38:20+00:00 | Jose Ignacio Terminal [UY] | 2020-12-03 00:20:30+00:00 |
2005 | DAKOTA STRENGHT | aframax | Crude/Condensates | Hibernia | 401320 | unloading_state | Whiffen Head [CA] | 2020-12-05 12:27:34+00:00 | New York, NY [US] | NaT |
2006 | EAGLE TOLEDO | aframax | Crude/Condensates | Eagle Ford crude | 776022 | transiting_state | Corpus Christi, TX [US] | 2020-11-28 19:23:03+00:00 | Mumbai [IN] | NaT |
2007 rows × 10 columns
Let's say you wanted to know which child products belonged to a particular group or category. One way to do this is to full the full products dataset:
search = v.Products().search()
Loading from API: 2000it [00:00, 5183.91it/s]
len(search)
1762
We can convert this into a DataFrame. This allows us to, for example, find all products of 'group' type.
search_df = search.to_df()
search_df[search_df['layer.0'] == 'group']
id | name | layer.0 | parent.0.name | |
---|---|---|---|---|
1155 | b68cbb746f8b9098c50e2ba36bcad83001a53bd362e903... | Clean Petroleum Products | group | NaN |
1198 | 54af755a090118dcf9b0724c9a4e9f14745c26165385ff... | Crude/Condensates | group | NaN |
1290 | 5de0b00094e0fd7542c10f9f8a71b4008d55750f21dc90... | Dirty Petroleum Products | group | NaN |
Let's say we then want to find the 'child' products of 'Crude/Condensates':
search_df[search_df['parent.0.name'] == 'Crude/Condensates']
id | name | layer.0 | parent.0.name | |
---|---|---|---|---|
1170 | d053227a2df2f4d6eacca61341cd6c08e49ba6970cc18a... | Condensates | group_product | Crude/Condensates |
1188 | 6f11b0724c9a4e85ffa7f1445bc768f054af755a090118... | Crude | group_product | Crude/Condensates |
Going one step further:
search_df[search_df['parent.0.name'] == 'Crude']
id | name | layer.0 | parent.0.name | |
---|---|---|---|---|
64 | a7e26956fbb91d786b1d55582981b0d199f72c050958fd... | Medium-Sour | category | Crude |
65 | 1c10c409e5412d5eee38bbbd529509d2fe0251fa99e292... | Medium-Sweet | category | Crude |
1552 | fc2e0746428ed6109045fc1195a4ee4ef2e339d55b7b6e... | Heavy-Sour | category | Crude |
1553 | 3cb81242a4b490d97b78657e8f1d40b994c32d4334051f... | Heavy-Sweet | category | Crude |
1735 | 87acdcdd0f1b2482eb808d5ea2afdc05266f27e4c9b0a5... | Light-Sour | category | Crude |
1736 | 66a98cd9df06660555000f8d4de9bc075e651bce3b57a4... | Light-Sweet | category | Crude |
And finally, to grade level:
search_df[search_df['parent.0.name'] == 'Heavy-Sweet']
id | name | layer.0 | parent.0.name | |
---|---|---|---|---|
54 | fb4d9f3e8c547b7b5be2f69f72279712a11de9deedec42... | Mayna | grade | Heavy-Sweet |
158 | f771796213919845444b4310aa8d6184cc8437b2d29de9... | Nigerian Medium | grade | Heavy-Sweet |
199 | 8be6e28c0b2379e8c74cfea67af4dd570e5ce3dbbb3893... | Olende | grade | Heavy-Sweet |
201 | 3a2b4996b8568a15296f541dc0c16d6060e32f2eb903b5... | Olombendo | grade | Heavy-Sweet |
214 | 382a1fdb7c456190f77ed096a3fcc61ff3f77e5d8fd398... | Ostra | grade | Heavy-Sweet |
266 | 2da055a25689552c9a58bba5a3294114aba62cf73e3bde... | Pazflor | grade | Heavy-Sweet |
270 | acccc718f28b757b433b8809892bc7f4a2d7a7d3e83ad3... | Penglai | grade | Heavy-Sweet |
338 | 497768f75d5f0834a88b6e51a8f805324f3e23b4d2caf6... | Pyrenees | grade | Heavy-Sweet |
347 | 5bcfb3d6ea2b417593fc2524366fbcd74571d6c1bbda4f... | Qinghuangdao | grade | Heavy-Sweet |
419 | 19c99edb8bfa5ecd4efbf3dc4e0017ce338c7a62b49c23... | Roncador Heavy | grade | Heavy-Sweet |
449 | 26c07045de0fbcaa1b3cefd665ca8f322631523da32b40... | Sanga sanga | grade | Heavy-Sweet |
450 | 548dc50e9ec946c12885a46d3b443428b789011cd8c978... | Sangos | grade | Heavy-Sweet |
459 | b9c38044b2df5ef84351ec0b4035767f8c1a485f54c902... | Schiehallion Blend | grade | Heavy-Sweet |
467 | 7057a91c1981ba55f249159655957edb07e72b31509a90... | Sergipano Terra | grade | Heavy-Sweet |
496 | 3ed2629a328ce7f3c2b90183ff21e3be6d71adc5f60e03... | Solan | grade | Heavy-Sweet |
525 | e795d1f160bb6bc1ec6a452040eb2212bc0527ee680bdf... | Stag | grade | Heavy-Sweet |
562 | bb34861c4c1c5d47ae4453e1f5b2583bbe91ba53042ae1... | Tagul | grade | Heavy-Sweet |
693 | 5c273a8a7627c251713a1d3c8886f71571dcc55bb49ee3... | Van Gogh | grade | Heavy-Sweet |
703 | efed961dfabbd2e41f2db0ce2186f8a41fa8b659c2f2cc... | Vincent | grade | Heavy-Sweet |
714 | 892c10e97597abe422148f26eda184ea6087134795e858... | Wandoo | grade | Heavy-Sweet |
715 | c27355b5263c4ff688803a5b6afd7d13504c9eee3e8f4a... | Wassana | grade | Heavy-Sweet |
741 | fd5b4d385b5b4ed18e680de4fbc79debf4e7115982b240... | Woodbourne | grade | Heavy-Sweet |
750 | ef2870346d0eaf18d88059c70ffd8c12296c70e8a8e231... | Yombo | grade | Heavy-Sweet |
869 | 880d79ff1884ed37a8091526b09605bee38546c0a41ed4... | Antan Blend | grade | Heavy-Sweet |
903 | af3ed44f2270bdfe36286edd93faca925e7590dcb49222... | Athena | grade | Heavy-Sweet |
904 | fb3c7267c7654c468f2a5b6454bf443fcfdae6dde7704a... | Atlanta | grade | Heavy-Sweet |
914 | 9a5dfab130b5cd5df3572ed76ca642d01768d6ba8a23c8... | Azadegan | grade | Heavy-Sweet |
942 | d8dc642b92088492c09d26f78d79eedc57c4bbb4c336e1... | Baobab | grade | Heavy-Sweet |
943 | 1a02518edd0e41019194a84e39da3b38fd7cee158cf448... | Barracuda | grade | Heavy-Sweet |
962 | 1af91599b7eb93c7332f1ca1292dc63f267f55cc6ca78c... | Benin | grade | Heavy-Sweet |
974 | 64b7370212a085464093888cd37012343b1ad3a35b7418... | Bima | grade | Heavy-Sweet |
1010 | 92a2672b4e6709ce3e2fdcf1b2cbab32af02f4d658f685... | Bonny Medium | grade | Heavy-Sweet |
1017 | 00880328b73f471e9a61d156b075a2103b3c5273ddac42... | Bozhong | grade | Heavy-Sweet |
1024 | 8d906dcb86d7813d11da8fa71ab056fb5c8df6c2bbfbb0... | Bretana Blend | grade | Heavy-Sweet |
1031 | 62f2c9a279304701a8620f692c72726a09f783bf93cd4b... | Bula | grade | Heavy-Sweet |
1107 | 8980f09ff3f45eaa4fc12ab939265b8a80666b33cbfed7... | Canadon Seco | grade | Heavy-Sweet |
1110 | bdaeac274d2e4f93af94e5877b48f3b43c5c0bcb8b5049... | Caofeidian | grade | Heavy-Sweet |
1112 | e975413418674ef699e96459208c6a9fe25f3eea26c64e... | Caratinga | grade | Heavy-Sweet |
1153 | 6930dea9bdd4a9d50e7716304d49c4f94a036f66070fae... | Clair | grade | Heavy-Sweet |
1178 | 4cbee8cb1e568850c67c6e1677e4e70bb5ab32fcfbee9c... | Corocoro | grade | Heavy-Sweet |
1187 | 8c1995d4bce4c7e80c1eeca2bde92f243de8bec076e845... | Cretaceo | grade | Heavy-Sweet |
1228 | f56a9d776a7dd8f98f8634701414242214e1a694446810... | Dar Blend | grade | Heavy-Sweet |
1295 | 3a35c61bc48d1ea6f1b21214341ee3f844ee0869006ae6... | Djeno | grade | Heavy-Sweet |
1296 | cd2d3394abd22be9bfac91e1844b42ca40417d2c2ef03d... | Doba | grade | Heavy-Sweet |
1314 | 59a21390302993399852a65534066ec94780417f0e1099... | Duri (Sumatran Heavy) | grade | Heavy-Sweet |
1338 | c0c47dcf0c42a38c8e411b69437f24240e83ff579c287f... | Ebok | grade | Heavy-Sweet |
1352 | f3f3fa50b15f528bd21b37e1fade5c635ca1ad80f0e790... | Enfield | grade | Heavy-Sweet |
1359 | b346b21b898ccb662010b3718f785168d292cfc7ffaec6... | Escalante | grade | Heavy-Sweet |
1400 | c90c2e7e5165eca3b56c8928dd961840a19ba03f60d83e... | Foinaven | grade | Heavy-Sweet |
1508 | 17f01902a755732dab2c9f909c24ddce99156c918f2c7f... | Gryphon | grade | Heavy-Sweet |
1685 | b1fdf767738c8a27f327f31e0b633034894deb3bff1d17... | Kraken | grade | Heavy-Sweet |
1714 | f5bc02bfa3d32e5278d22151fd762fec7c3c4a5c54ea1a... | Lapa | grade | Heavy-Sweet |
1741 | 90516c2254f72429666871fe811e03c3d437111460f23f... | Liuhua | grade | Heavy-Sweet |
1745 | 423969c656d3f36588db1961bc19bae08df99bca72136d... | Lokele | grade | Heavy-Sweet |
This is a convenient way of exploring the different products that exist in the data.
Assemble a DataFrame of Cargo Movements that were loading in the last 7 weeks, and that were carrying Diesel out of the United States.
Note: Diesel is a subset of wider group Diesel/Gasoil.