{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Module 5\n", "\n", "## Video 23: Filtering by Geography\n", "**Python for the Energy Industry**\n", "\n", "In previous lessons, we have seen that the Geography endpoint of the Vortexa SDK can be used to search for, and get ID numbers for different types of location - countries, shipping regions, ports, etc. These IDs can be used to filter searches based on e.g. origin and destination. \n", "\n", "[Geographies endpoint documentation.](https://vortechsa.github.io/python-sdk/endpoints/geographies/)" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "# Some initial imports / settings\n", "from datetime import datetime\n", "from dateutil.relativedelta import relativedelta\n", "import vortexasdk as v\n", "\n", "now = datetime.utcnow()\n", "one_month_ago = now - relativedelta(months=1)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If we use the Geographies endpoint search function to search for a high-level term like 'china', we will likely get a scattering of results at different levels that match that search term:" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2020-12-10 16:24:14,147 vortexasdk.client — WARNING — You are using vortexasdk version 0.26.0, however version 0.27.1 is available.\n", "You should consider upgrading via the 'pip install vortexasdk --upgrade' command.\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namelayer
0China[country]
1South China[shipping_region]
2North China[shipping_region]
3China (excl. HK & Macau)[shipping_region]
4China Steel Chemical[terminal]
5China Energy Services Ningbo[terminal]
6East China Sea STS[sts_zone]
7Multipurpose (China Merchants) Terminal[terminal]
8China Union, Freeport Of Monrovia[terminal]
9China Resources Chemical Holding Terminal[terminal]
10China General Terminal Distribution Corp[terminal]
11China Bay(Ioc Terminal) (Ex-Trincomalee)[terminal]
12Wenzhou China Petroleum Fuel Bitumen Co., Ltd.[terminal]
\n", "
" ], "text/plain": [ " name layer\n", "0 China [country]\n", "1 South China [shipping_region]\n", "2 North China [shipping_region]\n", "3 China (excl. HK & Macau) [shipping_region]\n", "4 China Steel Chemical [terminal]\n", "5 China Energy Services Ningbo [terminal]\n", "6 East China Sea STS [sts_zone]\n", "7 Multipurpose (China Merchants) Terminal [terminal]\n", "8 China Union, Freeport Of Monrovia [terminal]\n", "9 China Resources Chemical Holding Terminal [terminal]\n", "10 China General Terminal Distribution Corp [terminal]\n", "11 China Bay(Ioc Terminal) (Ex-Trincomalee) [terminal]\n", "12 Wenzhou China Petroleum Fuel Bitumen Co., Ltd. [terminal]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "china_search = v.Geographies().search(\"china\")\n", "china_search.to_df(columns=['name','layer'])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Having done this search, we can of course pick the one we want and grab its ID." ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "China ID: 934c47f36c16a58d68ef5e007e62a23f5f036ee3f3d1f5f85a48c572b90ad8b2\n" ] } ], "source": [ "china_ID = china_search[0]['id']\n", "print('China ID:',china_ID)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There are a couple of ways of attempting to get the right ID directly, without having to inspect the search results. One of these is to require that the geography name is a direct match to our search query:" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "China ID: 934c47f36c16a58d68ef5e007e62a23f5f036ee3f3d1f5f85a48c572b90ad8b2\n" ] } ], "source": [ "china_search = v.Geographies().search(\"China\",exact_term_match=True)\n", "assert len(china_search) == 1\n", "\n", "china_ID = china_search[0]['id']\n", "print('China ID:',china_ID)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Here, the line `assert len(china_search) == 1` is used to ensure that we have only got one matching result. This line will throw an error if this is not the case. \n", "\n", "We can also do this without requiring an exact match, by specifying the particular geographic layer:" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "China ID: 934c47f36c16a58d68ef5e007e62a23f5f036ee3f3d1f5f85a48c572b90ad8b2\n" ] } ], "source": [ "china_geo = [g for g in v.Geographies().search(\"china\") if 'country' in g['layer']]\n", "assert len(china_geo) == 1\n", "\n", "china_ID = china_search[0]['id']\n", "print('China ID:',china_ID)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can see what our assert statement does if our search yields multiple results:" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "ename": "AssertionError", "evalue": "", "output_type": "error", "traceback": [ "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[1;31mAssertionError\u001b[0m Traceback (most recent call last)", "\u001b[1;32m\u001b[0m in \u001b[0;36m\u001b[1;34m\u001b[0m\n\u001b[0;32m 1\u001b[0m \u001b[0mkorea_geo\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mg\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mg\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mv\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mGeographies\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msearch\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"korea\"\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mif\u001b[0m \u001b[1;34m'country'\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mg\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'layer'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[1;32massert\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkorea_geo\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m", "\u001b[1;31mAssertionError\u001b[0m: " ] } ], "source": [ "korea_geo = [g for g in v.Geographies().search(\"korea\") if 'country' in g['layer']]\n", "# Uncomment and run the following line \n", "#assert len(korea_geo) == 1" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Filtering Cargo Movements\n", "\n", "We will now look at filtering Cargo Movements searches based on geographies. Let's say we want so find all cargoes that were loading in the last month, and that are destined for China:" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "Loading from API: 2500it [00:02, 966.33it/s] \n" ] } ], "source": [ "cm_china_query = v.CargoMovements().search(\n", " filter_activity=\"loading_state\",\n", " filter_time_min=one_month_ago,\n", " filter_time_max=now,\n", " filter_destinations=china_ID)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We'll convert this into a DataFrame. In a previous lesson, we saw that we can specify the particular columns that we want to put in our Cargo Movements DataFrame. We also saw that these columns can be given shorter, less cumbersome labels. We make two lists, with the full labels and our chosen new labels in corresponding order:" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "required_columns = [\n", " # Primary vessel details\n", " \"vessels.0.name\",\n", " \"vessels.0.vessel_class\",\n", " # Product information\n", " \"product.group.label\",\n", " \"product.grade.label\",\n", " \"quantity\",\n", " # Vessel status\n", " \"status\",\n", " # Loading port and time\n", " \"events.cargo_port_load_event.0.location.port.label\",\n", " \"events.cargo_port_load_event.0.end_timestamp\",\n", " # Discharge port and time\n", " \"events.cargo_port_unload_event.0.location.port.label\",\n", " \"events.cargo_port_unload_event.0.end_timestamp\",\n", "]\n", "\n", "new_labels = [\n", " \"vessel_name\",\n", " \"vessel_class\",\n", " \"product_group\",\n", " \"product_grade\",\n", " \"quantity\",\n", " \"status\",\n", " \"loading_port\",\n", " \"loading_finish\",\n", " \"unloading_port\",\n", " \"unloading_finish\",\n", "]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can then make a dictionary for renaming, by zipping these two lists together:" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
vessel_namevessel_classproduct_groupproduct_gradequantitystatusloading_portloading_finishunloading_portunloading_finish
0ATLANTICOShandysizeDirty Petroleum ProductsNaN167491unloaded_stateSlavyanka [RU]2020-11-19 10:59:27+00:00Ningbo (Beilun) [CN]2020-12-03 02:16:24+00:00
1SW SOUTHPORT IhandymaxClean Petroleum ProductsNaN273706unloaded_stateYeosu (Yosu), Gwangyang [KR]2020-11-25 15:27:26+00:00Dongguan, Humen [CN]2020-12-03 04:13:26+00:00
2NEW TINOSvlcc_plusDirty Petroleum ProductsNaN670951unloaded_stateNaNNaTHong Kong [CN]2020-11-25 19:11:45+00:00
3HANG HAI YOU 17general_purposeCrude/CondensatesNaN147432unloaded_stateDongjiakou [CN]2020-12-08 04:04:06+00:00Rizhao - Lanshan Area [CN]2020-12-09 23:47:47+00:00
4QUANLINLIANGYOU 5tiny_tankerClean Petroleum ProductsNaN35952unloaded_stateTaixing [CN]2020-11-21 03:39:28+00:00Nantong [CN]2020-11-22 05:44:38+00:00
.................................
2294XIN DE116tiny_tankerClean Petroleum ProductsNaN35398unloaded_stateDalian [CN]2020-11-29 19:46:12+00:00Shanghai [CN]2020-12-04 05:44:43+00:00
2295SC TIANJINgeneral_purposeClean Petroleum ProductsMonoethylene Glycol (MEG)14774unloaded_stateMai Liao - Taiwan [TW]2020-12-01 11:38:16+00:00Jiangyin [CN]2020-12-10 05:46:48+00:00
2296TIAN EN 1001tiny_tankerClean Petroleum ProductsNaN15850unloaded_stateNanjing [CN]2020-12-03 21:04:10+00:00Zhenjiang [CN]2020-12-05 08:43:42+00:00
2297SC DALIANgeneral_purposeClean Petroleum ProductsNaN38952unloaded_stateUlsan, Onsan [KR]2020-11-25 23:36:28+00:00Jingjiang [CN]2020-11-30 14:27:25+00:00
2298BLUE SPRINGhandymaxClean Petroleum ProductsNaN175340unloaded_stateNaN2020-11-16 15:52:16+00:00Nantong [CN]2020-11-26 08:20:41+00:00
\n", "

2299 rows × 10 columns

\n", "
" ], "text/plain": [ " vessel_name vessel_class product_group \\\n", "0 ATLANTICOS handysize Dirty Petroleum Products \n", "1 SW SOUTHPORT I handymax Clean Petroleum Products \n", "2 NEW TINOS vlcc_plus Dirty Petroleum Products \n", "3 HANG HAI YOU 17 general_purpose Crude/Condensates \n", "4 QUANLINLIANGYOU 5 tiny_tanker Clean Petroleum Products \n", "... ... ... ... \n", "2294 XIN DE116 tiny_tanker Clean Petroleum Products \n", "2295 SC TIANJIN general_purpose Clean Petroleum Products \n", "2296 TIAN EN 1001 tiny_tanker Clean Petroleum Products \n", "2297 SC DALIAN general_purpose Clean Petroleum Products \n", "2298 BLUE SPRING handymax Clean Petroleum Products \n", "\n", " product_grade quantity status \\\n", "0 NaN 167491 unloaded_state \n", "1 NaN 273706 unloaded_state \n", "2 NaN 670951 unloaded_state \n", "3 NaN 147432 unloaded_state \n", "4 NaN 35952 unloaded_state \n", "... ... ... ... \n", "2294 NaN 35398 unloaded_state \n", "2295 Monoethylene Glycol (MEG) 14774 unloaded_state \n", "2296 NaN 15850 unloaded_state \n", "2297 NaN 38952 unloaded_state \n", "2298 NaN 175340 unloaded_state \n", "\n", " loading_port loading_finish \\\n", "0 Slavyanka [RU] 2020-11-19 10:59:27+00:00 \n", "1 Yeosu (Yosu), Gwangyang [KR] 2020-11-25 15:27:26+00:00 \n", "2 NaN NaT \n", "3 Dongjiakou [CN] 2020-12-08 04:04:06+00:00 \n", "4 Taixing [CN] 2020-11-21 03:39:28+00:00 \n", "... ... ... \n", "2294 Dalian [CN] 2020-11-29 19:46:12+00:00 \n", "2295 Mai Liao - Taiwan [TW] 2020-12-01 11:38:16+00:00 \n", "2296 Nanjing [CN] 2020-12-03 21:04:10+00:00 \n", "2297 Ulsan, Onsan [KR] 2020-11-25 23:36:28+00:00 \n", "2298 NaN 2020-11-16 15:52:16+00:00 \n", "\n", " unloading_port unloading_finish \n", "0 Ningbo (Beilun) [CN] 2020-12-03 02:16:24+00:00 \n", "1 Dongguan, Humen [CN] 2020-12-03 04:13:26+00:00 \n", "2 Hong Kong [CN] 2020-11-25 19:11:45+00:00 \n", "3 Rizhao - Lanshan Area [CN] 2020-12-09 23:47:47+00:00 \n", "4 Nantong [CN] 2020-11-22 05:44:38+00:00 \n", "... ... ... \n", "2294 Shanghai [CN] 2020-12-04 05:44:43+00:00 \n", "2295 Jiangyin [CN] 2020-12-10 05:46:48+00:00 \n", "2296 Zhenjiang [CN] 2020-12-05 08:43:42+00:00 \n", "2297 Jingjiang [CN] 2020-11-30 14:27:25+00:00 \n", "2298 Nantong [CN] 2020-11-26 08:20:41+00:00 \n", "\n", "[2299 rows x 10 columns]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "relabel = dict(zip(required_columns,new_labels))\n", "\n", "cm_china_query.to_df(columns=required_columns).rename(relabel,axis=1)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We could choose to filter out intranational transfers by also using the ID for China with the `exclude_origins` argument." ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "Loading from API: 1500it [00:00, 1762.69it/s] \n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
vessel_namevessel_classproduct_groupproduct_gradequantitystatusloading_portloading_finishunloading_portunloading_finish
0SC XIAMENgeneral_purposeClean Petroleum ProductsNaN88906unloaded_stateUlsan, Onsan [KR]2020-11-25 23:29:57+00:00Pinghu, Jiaxing [CN]2020-12-09 01:18:41+00:00
1HEUNG-A PIONEERgeneral_purposeClean Petroleum ProductsNaN76018transiting_stateSingapore [SG]2020-12-05 10:00:50+00:00Zhangjiagang [CN]NaT
2STX IRISgeneral_purposeClean Petroleum ProductsNaN97850unloaded_stateDaesan [KR]2020-11-12 21:04:00+00:00Pinghu, Jiaxing [CN]2020-11-21 06:31:10+00:00
3SEA CRYSTALgeneral_purposeClean Petroleum ProductsNaN102649unloaded_stateIncheon [KR]2020-11-27 22:34:18+00:00Jiangyin [CN]2020-12-03 05:40:32+00:00
4JS NANJINGtiny_tankerDirty Petroleum ProductsNaN15460unloaded_stateYeosu (Yosu), Gwangyang [KR]2020-11-19 00:00:45+00:00Nanjing [CN]2020-11-25 18:12:21+00:00
.................................
1053NAVE ATROPOSpanamaxClean Petroleum ProductsNaN295545transiting_stateSingapore [SG]2020-11-21 15:05:26+00:00Dalian [CN]NaT
1054ASIA DAWNvlcc_plusCrude/CondensatesOman Blend1931245transiting_stateMina Al Fahal, Muscat [OM]2020-11-27 21:14:27+00:00Rizhao - Lanshan Area [CN]NaT
1055SC TIANJINgeneral_purposeClean Petroleum ProductsMonoethylene Glycol (MEG)14774unloaded_stateMai Liao - Taiwan [TW]2020-12-01 11:38:16+00:00Jiangyin [CN]2020-12-10 05:46:48+00:00
1056SC DALIANgeneral_purposeClean Petroleum ProductsNaN38952unloaded_stateUlsan, Onsan [KR]2020-11-25 23:36:28+00:00Jingjiang [CN]2020-11-30 14:27:25+00:00
1057BLUE SPRINGhandymaxClean Petroleum ProductsNaN175340unloaded_stateNaN2020-11-16 15:52:16+00:00Nantong [CN]2020-11-26 08:20:41+00:00
\n", "

1058 rows × 10 columns

\n", "
" ], "text/plain": [ " vessel_name vessel_class product_group \\\n", "0 SC XIAMEN general_purpose Clean Petroleum Products \n", "1 HEUNG-A PIONEER general_purpose Clean Petroleum Products \n", "2 STX IRIS general_purpose Clean Petroleum Products \n", "3 SEA CRYSTAL general_purpose Clean Petroleum Products \n", "4 JS NANJING tiny_tanker Dirty Petroleum Products \n", "... ... ... ... \n", "1053 NAVE ATROPOS panamax Clean Petroleum Products \n", "1054 ASIA DAWN vlcc_plus Crude/Condensates \n", "1055 SC TIANJIN general_purpose Clean Petroleum Products \n", "1056 SC DALIAN general_purpose Clean Petroleum Products \n", "1057 BLUE SPRING handymax Clean Petroleum Products \n", "\n", " product_grade quantity status \\\n", "0 NaN 88906 unloaded_state \n", "1 NaN 76018 transiting_state \n", "2 NaN 97850 unloaded_state \n", "3 NaN 102649 unloaded_state \n", "4 NaN 15460 unloaded_state \n", "... ... ... ... \n", "1053 NaN 295545 transiting_state \n", "1054 Oman Blend 1931245 transiting_state \n", "1055 Monoethylene Glycol (MEG) 14774 unloaded_state \n", "1056 NaN 38952 unloaded_state \n", "1057 NaN 175340 unloaded_state \n", "\n", " loading_port loading_finish \\\n", "0 Ulsan, Onsan [KR] 2020-11-25 23:29:57+00:00 \n", "1 Singapore [SG] 2020-12-05 10:00:50+00:00 \n", "2 Daesan [KR] 2020-11-12 21:04:00+00:00 \n", "3 Incheon [KR] 2020-11-27 22:34:18+00:00 \n", "4 Yeosu (Yosu), Gwangyang [KR] 2020-11-19 00:00:45+00:00 \n", "... ... ... \n", "1053 Singapore [SG] 2020-11-21 15:05:26+00:00 \n", "1054 Mina Al Fahal, Muscat [OM] 2020-11-27 21:14:27+00:00 \n", "1055 Mai Liao - Taiwan [TW] 2020-12-01 11:38:16+00:00 \n", "1056 Ulsan, Onsan [KR] 2020-11-25 23:36:28+00:00 \n", "1057 NaN 2020-11-16 15:52:16+00:00 \n", "\n", " unloading_port unloading_finish \n", "0 Pinghu, Jiaxing [CN] 2020-12-09 01:18:41+00:00 \n", "1 Zhangjiagang [CN] NaT \n", "2 Pinghu, Jiaxing [CN] 2020-11-21 06:31:10+00:00 \n", "3 Jiangyin [CN] 2020-12-03 05:40:32+00:00 \n", "4 Nanjing [CN] 2020-11-25 18:12:21+00:00 \n", "... ... ... \n", "1053 Dalian [CN] NaT \n", "1054 Rizhao - Lanshan Area [CN] NaT \n", "1055 Jiangyin [CN] 2020-12-10 05:46:48+00:00 \n", "1056 Jingjiang [CN] 2020-11-30 14:27:25+00:00 \n", "1057 Nantong [CN] 2020-11-26 08:20:41+00:00 \n", "\n", "[1058 rows x 10 columns]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cm_china_query = v.CargoMovements().search(\n", " filter_activity=\"loading_state\",\n", " filter_time_min=one_month_ago,\n", " filter_time_max=now,\n", " filter_destinations=china_ID,\n", " exclude_origins=china_ID)\n", "\n", "cm_china_query.to_df(columns=required_columns).rename(relabel,axis=1)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Note: there is another way of excluding intranational transfers: use the disable_geographic_exclusion_rules argument, and set this to True.*" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The full list of filters on Geography is:\n", "- filter_destinations\n", "- filter_origins\n", "- filter_storage_locations\n", "- filter_ship_to_ship_locations\n", "- filter_waypoints\n", "- exclude_origins\n", "- exclude_destinations\n", "\n", "Each of these will also accept a list of IDs, rather than just a single ID." ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
vessel_namevessel_classproduct_groupproduct_gradequantitystatusloading_portloading_finishunloading_portunloading_finish
0SC XIAMENgeneral_purposeClean Petroleum ProductsNaN88906unloaded_stateUlsan, Onsan [KR]2020-11-25 23:29:57+00:00Pinghu, Jiaxing [CN]2020-12-09 01:18:41+00:00
1STX IRISgeneral_purposeClean Petroleum ProductsNaN97850unloaded_stateDaesan [KR]2020-11-12 21:04:00+00:00Pinghu, Jiaxing [CN]2020-11-21 06:31:10+00:00
2SEA CRYSTALgeneral_purposeClean Petroleum ProductsNaN102649unloaded_stateIncheon [KR]2020-11-27 22:34:18+00:00Jiangyin [CN]2020-12-03 05:40:32+00:00
3JS NANJINGtiny_tankerDirty Petroleum ProductsNaN15460unloaded_stateYeosu (Yosu), Gwangyang [KR]2020-11-19 00:00:45+00:00Nanjing [CN]2020-11-25 18:12:21+00:00
4GAS EMERALDsgcClean Petroleum ProductsNaN7610unloaded_stateUlsan, Onsan [KR]2020-11-17 17:27:54+00:00Zhapu [CN]2020-11-21 06:42:21+00:00
.................................
366HUA HANG HE CHANGtiny_tankerDirty Petroleum ProductsNaN34310unloaded_stateYeosu (Yosu), Gwangyang [KR]2020-11-23 07:50:40+00:00Nanjing [CN]2020-11-30 06:21:44+00:00
367CRANE MIMOSAtiny_tankerClean Petroleum ProductsNaN48745unloaded_stateAnegasaki-Chiba [JP]2020-11-25 21:47:22+00:00Zhangjiagang [CN]2020-12-02 00:44:23+00:00
368NEW SILVERgeneral_purposeClean Petroleum ProductsNaN18831unloaded_stateUlsan, Onsan [KR]2020-11-19 01:29:57+00:00Dalian [CN]2020-11-26 08:01:41+00:00
369WOOSHIN ACEtiny_tankerClean Petroleum ProductsNaN21328unloaded_stateYeosu (Yosu), Gwangyang [KR]2020-11-23 17:53:11+00:00Nantong [CN]2020-11-26 23:46:12+00:00
370SC DALIANgeneral_purposeClean Petroleum ProductsNaN38952unloaded_stateUlsan, Onsan [KR]2020-11-25 23:36:28+00:00Jingjiang [CN]2020-11-30 14:27:25+00:00
\n", "

371 rows × 10 columns

\n", "
" ], "text/plain": [ " vessel_name vessel_class product_group \\\n", "0 SC XIAMEN general_purpose Clean Petroleum Products \n", "1 STX IRIS general_purpose Clean Petroleum Products \n", "2 SEA CRYSTAL general_purpose Clean Petroleum Products \n", "3 JS NANJING tiny_tanker Dirty Petroleum Products \n", "4 GAS EMERALD sgc Clean Petroleum Products \n", ".. ... ... ... \n", "366 HUA HANG HE CHANG tiny_tanker Dirty Petroleum Products \n", "367 CRANE MIMOSA tiny_tanker Clean Petroleum Products \n", "368 NEW SILVER general_purpose Clean Petroleum Products \n", "369 WOOSHIN ACE tiny_tanker Clean Petroleum Products \n", "370 SC DALIAN general_purpose Clean Petroleum Products \n", "\n", " product_grade quantity status loading_port \\\n", "0 NaN 88906 unloaded_state Ulsan, Onsan [KR] \n", "1 NaN 97850 unloaded_state Daesan [KR] \n", "2 NaN 102649 unloaded_state Incheon [KR] \n", "3 NaN 15460 unloaded_state Yeosu (Yosu), Gwangyang [KR] \n", "4 NaN 7610 unloaded_state Ulsan, Onsan [KR] \n", ".. ... ... ... ... \n", "366 NaN 34310 unloaded_state Yeosu (Yosu), Gwangyang [KR] \n", "367 NaN 48745 unloaded_state Anegasaki-Chiba [JP] \n", "368 NaN 18831 unloaded_state Ulsan, Onsan [KR] \n", "369 NaN 21328 unloaded_state Yeosu (Yosu), Gwangyang [KR] \n", "370 NaN 38952 unloaded_state Ulsan, Onsan [KR] \n", "\n", " loading_finish unloading_port unloading_finish \n", "0 2020-11-25 23:29:57+00:00 Pinghu, Jiaxing [CN] 2020-12-09 01:18:41+00:00 \n", "1 2020-11-12 21:04:00+00:00 Pinghu, Jiaxing [CN] 2020-11-21 06:31:10+00:00 \n", "2 2020-11-27 22:34:18+00:00 Jiangyin [CN] 2020-12-03 05:40:32+00:00 \n", "3 2020-11-19 00:00:45+00:00 Nanjing [CN] 2020-11-25 18:12:21+00:00 \n", "4 2020-11-17 17:27:54+00:00 Zhapu [CN] 2020-11-21 06:42:21+00:00 \n", ".. ... ... ... \n", "366 2020-11-23 07:50:40+00:00 Nanjing [CN] 2020-11-30 06:21:44+00:00 \n", "367 2020-11-25 21:47:22+00:00 Zhangjiagang [CN] 2020-12-02 00:44:23+00:00 \n", "368 2020-11-19 01:29:57+00:00 Dalian [CN] 2020-11-26 08:01:41+00:00 \n", "369 2020-11-23 17:53:11+00:00 Nantong [CN] 2020-11-26 23:46:12+00:00 \n", "370 2020-11-25 23:36:28+00:00 Jingjiang [CN] 2020-11-30 14:27:25+00:00 \n", "\n", "[371 rows x 10 columns]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "south_korea_geo = v.Geographies().search(\"South Korea\",exact_term_match=True)\n", "assert len(south_korea_geo) == 1\n", "\n", "japan_geo = v.Geographies().search(\"Japan\",exact_term_match=True)\n", "assert len(japan_geo) == 1\n", "\n", "south_korea_ID = south_korea_geo[0]['id']\n", "japan_ID = japan_geo[0]['id']\n", "\n", "cm_query = v.CargoMovements().search(\n", " filter_activity=\"loading_state\",\n", " filter_time_min=one_month_ago,\n", " filter_time_max=now,\n", " filter_destinations=china_ID,\n", " filter_origins=[south_korea_ID,japan_ID])\n", "\n", "cm_query.to_df(columns=required_columns).rename(relabel,axis=1)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Exercise\n", "\n", "Assemble a DataFrame of all Cargo Movements that were loading in the last month and that are destined for the United Kingdom. Make a pie chart of the country of origin (think about changing columns in the DataFrame to make this information easier to access)." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.9" } }, "nbformat": 4, "nbformat_minor": 4 }