{"cells":[{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"!pip install plotly==4.12.0 -q\n!pip install geopandas -q\n!pip install shapely -q","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# Set your own project id here\nfrom google.cloud import bigquery\nfrom google.cloud import storage\nimport plotly.express as px\nimport pandas as pd\n\nimport warnings\n\nfrom pandas.core.common import SettingWithCopyWarning\n\nwarnings.simplefilter(action=\"ignore\", category=SettingWithCopyWarning)\n\nPROJECT_ID = 'stunning-object-277601'\nbigquery_client = bigquery.Client(project=PROJECT_ID)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Summary\n\nThis notebook summarizes my analysis of short-term COVID-19 related impacts on KPI measurements. It is of my opinion that based on the data provided, excluding KPI measurements for the 2020 year will allow for a clearer assessment of the true impact of existing environmental policies or services. The visualizations below will serve to illustrate the following:\n\n* COVID-19 impacts on KPIs related to emmission reduction programs for air travel\n* COVID-19 impacts on KPIs related to emmission reduction programs for road traffic\n  "},{"metadata":{},"cell_type":"markdown","source":"### COVID-19 impacts on KPIs related to emmission reduction programs for air travel\n\n#### Overview\n\nIt is estimated that approximately 2.4% of global CO2 emmissions are directly attributed to air travel, and the industry as a whole is responsible for 5% of global warming. Though this contribution may seem minor, it is important to note that only a very small percentage of the world flys frequently, with estimates for even more developed countries like the UK and US having around half of the population fly in any given year, and just 12-15% are frequent fliers. Though there is no exact data, Dan Rutherford, shipping and aviation director at the International Council on Clean Transportation (ICCT), a US-based non-profit, estimates just 3% of the global population take regular flights. In fact, if everyone in the world took just one long-haul flight per year, aircraft emissions would far exceed the US’s entire CO2 emissions, according to ICCT analysis [Reference](https://www.bbc.com/future/article/20200218-climate-change-how-to-cut-your-carbon-emissions-when-flying).\n\nWhile improving fuel efficiency is gradually reducing the emissions per passenger, it is not keeping up with the rapid increase in total passenger numbers, which were projected to double in the next 20 years [Reference](https://www.iata.org/en/pressroom/pr/2018-10-24-02/). \n\nConsidering these factors, programs targeting GHG reductions from aviation related sources aim to substitute non-essential air travel with other modes of transportation in the short term and continue efficiency and sustainable aviation innovation in the long term.\n\n\n#### Analysis\n\nThe data for this analysis was obtained from a COVID-19 related mobility analysis study conducted by GEOTAB. More details about the analysis and data can be found here (https://data.geotab.com/covid-19-mobility-impact). \n\n##### Understanding the visualization\n* The diagram below illustrates the change in Airport traffic volume relative to a baseline period (1st Feb to 15th March 2020) for 28 high-traffic airports in the US, Canada, Chile and Australia. \n* The data is aggregated by month, where each bar represents the distribution of the changes of each airport from the baseline period.\n* The visualization below is a Box-And-Whiskers plot, where each box represents the region of central tendency, and the tails representing the extremes of deviations from the baseline period\n* A box is generated for each month from March to October\n* Points outside the tails are considered outliers\n* The dotted line represents the reference period\n\n**The data indicates that while airport traffic, a proxy of passeger demand, has increased from it's lowest periods in April and May, it is still significantly lower that was it was prior to the pandemic period.**\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"PROJECT_ID = 'bigquery-public-data'\nDATASET_ID = 'covid19_geotab_mobility_impact'\nTABLE_ID = 'airport_traffic'\n\ndataset_ref = bigquery_client.dataset(DATASET_ID, \n                             project=PROJECT_ID)\ndataset = bigquery_client.get_dataset(dataset_ref)\ntable = bigquery_client.get_table(f\"{PROJECT_ID}.{DATASET_ID}.{TABLE_ID}\")\n\nairport_traffic = bigquery_client.list_rows(table).to_dataframe()\n\nairport_traffic['month'] = airport_traffic['date'].apply(lambda x: x.strftime(\"%b\"))\nairport_traffic['month_int']  = airport_traffic['date'].apply(lambda x: x.month)\n\nairport_traffic_montly= airport_traffic.groupby(['airport_name', 'city', 'state_region', 'country_name', 'month', 'month_int'], as_index=False)['percent_of_baseline'].mean()\nairport_traffic_montly = airport_traffic_montly.sort_values(['airport_name',  'month_int'])\n\nfig = px.box(airport_traffic_montly, \n             y='percent_of_baseline', \n             x='month',\n             hover_name=\"airport_name\", \n             hover_data=['airport_name', 'city', 'state_region', 'country_name'],\n             points = \"outliers\",\n             labels={\n                 \"percent_of_baseline\": \"Percentage of Baseline\",\n                 'airport_name': \"Airport\", \n                 'city': \"City\", \n                 'state_region': \"State\", \n                 'country_name': \"Country\",\n                 'month': \"Month (2020)\"\n                    })\n\nfig.update_yaxes(tickvals=list(range(-100,110,10)),ticksuffix=\"%\")\nfig.add_hline(y=100, line_dash=\"dot\",\n              annotation_text=\"Baseline Activity Level\", \n              annotation_position=\"top right\")\n\nfig.update_traces(marker_color='crimson')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Recommendations\n\nDue to the COVID-19 pandemic, passenger demand is not expected to return to the 2019 level for atleast 4 to 9 years [Reference](https://www.thestreet.com/mishtalk/economics/how-long-will-it-take-for-the-airline-industry-to-recover). Therefore, I recommend that a greater emphasis be placed on KPIs related to increasing fuel effeciency and innovation in the short term, with an emphasis on alternative travel modes when passenger demand returns. "},{"metadata":{},"cell_type":"markdown","source":"### COVID-19 impacts on KPIs related to emmission reduction programs for road transport"},{"metadata":{},"cell_type":"markdown","source":"#### Overview\n\nTransport emissions — which primarily involve road, rail, air and marine transportation — accounted for over 24% of global CO2 emissions in 2016. They're also expected to grow at a faster rate than that from any other sector, posing a major challenge to efforts to reduce emissions in line with the Paris Agreement and other global goals. In terms of transport modes, 72% of global transport emissions come from road vehicles, which accounted for 80% of the rise in emissions from 1970-2010 [Reference](https://www.wri.org/blog/2019/10/everything-you-need-know-about-fastest-growing-source-global-emissions-transport#:~:text=In%20terms%20of%20transport%20modes,and%20international%20and%20coastal%20shipping.).\n\nIn order to address this, cities accross the world have implemented a variety of policies and programs to reduce road transport related emmissions. Here are a few examples [Reference](https://www.theguardian.com/environment/2016/may/17/how-are-cities-around-the-world-tackling-air-pollution):\n* Paris bans cars in many historic central districts at weekends, imposes odd-even bans on vehicles, makes public transport free during major pollution events and encourages car- and bike-sharing programmes.\n* Delhi bans all new large diesel cars and SUVs with engines of more than 2,000CC and to phase out tens of thousands of diesel taxis. The city has experimented with  and is now encouraging Uber-style minibuses on demand.\n* Oslo plans to halve its climate emissions by 2020 and proposes a large no-car zone, the building of 40 miles of new bike lanes, steep congestion charges, a rush-hour fee for motorists, and the removal of many parking spaces.\n* Zurich has capped the number of parking spaces in the city, only allows a certain number of cars into the city at any one time, and is building more car-free areas, plazas, tram lines and pedestrianised streets. \n\nGiven the nature of these programs, leveraging KPIs directly measure the reduction of road transport traffic in the short term, and measuring the implementation of environmentally sustainable alternatives in the long term would serve as effective measures of success.\n\n#### Analysis\n\nThe data for this analysis was also obtained from a COVID-19 related mobility analysis study conducted by GEOTAB. More details about the analysis and data can be found here (https://data.geotab.com/covid-19-mobility-impact). \n\n\n\nThe first diagram below illustrates the overall change in city congestion levels relative to a baseline period. The city congestion metric is calculated by first averaging four separate intersection metrics across an entire city. These individual intersection metrics include: number of trips, average speed, average total time stopped and average number of stops through each intersection. These metrics are then individually normalized using the following equation:\n\n\n(Current Hour Value - Minimum Value) / (Maximum Value - Minimum Value)\n\n\nThe maximum and minimum values are derived from data between February 1st 2020 and the current date. The individual normalized metrics are combined via equal-weighted averaging to yield a single congestion metric per city per hour. The distribution of the measurements are grouped for each city by month, where each bar represents the distribution of congestion values for the city for the given month.\n\n\n\n**The data indicates that COVID-19 has a lasting and prolonged effect of reducing city congestion levels where even in October, city congestion levels are still significantly lower than prior to the pandemic period.**"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"PROJECT_ID = 'bigquery-public-data'\nDATASET_ID = 'covid19_geotab_mobility_impact'\nTABLE_ID = 'city_congestion'\n\ndataset_ref = bigquery_client.dataset(DATASET_ID, \n                             project=PROJECT_ID)\ndataset = bigquery_client.get_dataset(dataset_ref)\ntable = bigquery_client.get_table(f\"{PROJECT_ID}.{DATASET_ID}.{TABLE_ID}\")\n\ncity_congestion = bigquery_client.list_rows(table).to_dataframe()\ncity_congestion['month'] = city_congestion['date_time'].apply(lambda x: x.strftime(\"%b\"))\ncity_congestion['month_int']  = city_congestion['date_time'].apply(lambda x: x.month)\ncity_congestion = city_congestion.sort_values(['city_name', 'date_time'])\n\nfig = px.box(city_congestion[city_congestion['month_int']>=2], \n             y='percent_congestion', \n             x='month',\n             color = 'city_name',\n             hover_name=\"city_name\", \n             hover_data=['city_name', 'date_time'],\n             labels = {\n                 \"percent_congestion\": \"Percentage of Baseline City Congestion\",\n                 'date_time': \"Date\", \n                 'city_name': \"City\", \n                 'month': \"Month (2020)\"\n                    })\nfig.add_hline(y=100, line_dash=\"dot\",\n              annotation_text=\"Baseline Congestion Level\", \n              annotation_position=\"bottom right\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The second diagram below illustrates the overall the change in volume of commercial activity each day (at local time) from March 16 onwards, as measured by the number of trips taken. Volume of activity is calculated on a relative basis using data from Feb. 1st and March 15th, 2020 as a benchmark, controlled for day-of-week. The data is further broken down by the type of business activity and is available for each state/province in the US, Canada and Mexico (see https://data.geotab.com/covid-19-mobility-impact/commercial-vehicle-traffic-analysis).\n\n\n**The data indicates that although overall commercial activity has been significantly impacted by COVID-19 during the months of March through to May, it is currently recovered to pre-COVID levels. The recovery however has not been uniform, with warehouse activity significantly increasing, which may be attributed to the increase in online and ecommerce activity.**"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"PROJECT_ID = 'bigquery-public-data'\nDATASET_ID = 'covid19_geotab_mobility_impact'\nTABLE_ID = 'lookup_region'\n\ndataset_ref = bigquery_client.dataset(DATASET_ID, \n                             project=PROJECT_ID)\ndataset = bigquery_client.get_dataset(dataset_ref)\ntable = bigquery_client.get_table(f\"{PROJECT_ID}.{DATASET_ID}.{TABLE_ID}\")\n\nlookup_region = bigquery_client.list_rows(table).to_dataframe()\n\nPROJECT_ID = 'stunning-object-277601'\nDATASET_ID = 'cdp_unlocking_climate_solutions_questions_analysis'\nTABLE_ID = 'geotab_mobility_impact__commercial_traffic'\n\ndataset_ref = bigquery_client.dataset(DATASET_ID, \n                             project=PROJECT_ID)\ndataset = bigquery_client.get_dataset(dataset_ref)\ntable = bigquery_client.get_table(f\"{PROJECT_ID}.{DATASET_ID}.{TABLE_ID}\")\n\ncommercial_traffic = bigquery_client.list_rows(table).to_dataframe()\n\ncommercial_traffic = commercial_traffic.merge(lookup_region, on='country_iso_code_2', how = \"left\")\n\ncommercial_traffic_province = commercial_traffic.dropna(axis = 0, subset = ['country_iso_code_2'])\n\ncommercial_traffic_province = commercial_traffic.dropna(axis = 0, subset = ['country_iso_code_2'])\ncommercial_traffic_province['month'] = commercial_traffic_province['date'].apply(lambda x: x.strftime(\"%b\"))\ncommercial_traffic_province['month_int']  = commercial_traffic_province['date'].apply(lambda x: x.month)\ncommercial_traffic_province = commercial_traffic_province.groupby(['country_iso_code_2', 'month', 'month_int'], as_index=False)[['percent_of_baseline_activity',\n                                                                                                                                 'percent_of_baseline_commercial', \n                                                                                                                                 'percent_of_baseline_industrial',\n                                                                                                                                 'percent_of_baseline_warehouse',\n                                                                                                                                 'percent_of_baseline_grocery_store',\n                                                                                                                                 'percent_of_baseline_other_retail']].agg(lambda x: x.mean(skipna=False))\ncommercial_traffic_province_month = pd.wide_to_long(commercial_traffic_province,\n                                              stubnames='percent_of_baseline',\n                                              i=['country_iso_code_2', 'month', 'month_int'], j='activity_type',\n                                              sep='_', suffix='\\w+'\n                                             ).reset_index()\n\ncommercial_traffic_province_month.sort_values(['country_iso_code_2', 'month_int'], inplace = True)\n\nfig = px.box(commercial_traffic_province_month, \n             y='percent_of_baseline', \n             x='month',\n             color = 'activity_type',\n             hover_name=\"country_iso_code_2\", \n             hover_data=['country_iso_code_2'],\n             labels = {\n                 \"percent_of_baseline\": \"Percentage of Baseline Congestion\",\n                 'activity_type': \"Activity Type\", \n                 'country_iso_code_2': \"Country\", \n                 'month': \"Month (2020)\"\n                    })\nfig.add_hline(y=100, line_dash=\"dot\",\n              annotation_text=\"Baseline Congestion Level\", \n              annotation_position=\"right\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The third diagram below leverages the same data source and measure as the prior, but illustrates the impact on commercial activity broken down by industry for the US and Canada.\n\nThe data indicates the following:\n\n* **The reduction of commercial activity during the pandemic is more pronounced in the US than in Canada, with the construction, public sector and hospitality industries being affected the most.**\n* **The reduction of commercial activity is not uniform across industries, with customer services, healthcare and hospitality being affected the most.**\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"PROJECT_ID = 'bigquery-public-data'\nDATASET_ID = 'covid19_geotab_mobility_impact'\nTABLE_ID = 'commercial_traffic_by_industry'\n\ndataset_ref = bigquery_client.dataset(DATASET_ID, \n                             project=PROJECT_ID)\ndataset = bigquery_client.get_dataset(dataset_ref)\ntable = bigquery_client.get_table(f\"{PROJECT_ID}.{DATASET_ID}.{TABLE_ID}\")\n\ncommercial_traffic_by_industry = bigquery_client.list_rows(table).to_dataframe()\n\ncommercial_traffic_by_industry_country = commercial_traffic_by_industry.dropna(axis = 0, subset = ['alpha_code_3'])\ncommercial_traffic_by_industry_country['month'] = commercial_traffic_by_industry_country['date'].apply(lambda x: x.strftime(\"%b\"))\ncommercial_traffic_by_industry_country['month_int']  = commercial_traffic_by_industry_country['date'].apply(lambda x: x.month)\ncommercial_traffic_by_industry_country_month = commercial_traffic_by_industry_country.groupby(['alpha_code_3','industry' ,\"month\",'month_int'], as_index=False)['percent_of_baseline'].agg(lambda x: x.mean(skipna=False))\ncommercial_traffic_by_industry_country_month = commercial_traffic_by_industry_country_month.sort_values(['alpha_code_3','industry' ,\"month_int\"])\n\n\nfig = px.bar(commercial_traffic_by_industry_country_month[~commercial_traffic_by_industry_country_month['industry'].isin([\"Software\", 'Organizations','Holding Companies & Conglomerates', 'Insurance', 'Media & Internet'])], \n             x=\"month\", y=\"percent_of_baseline\", color=\"industry\", facet_col=\"industry\", facet_row=\"alpha_code_3\",\n             labels = {\n                 \"percent_of_baseline\": \"% of Baseline Congestion\",\n                 'industry': \"Industry Type\", \n                 'alpha_code_3': \"Country\", \n                 'month': \"Month\"\n                    },\n            height = 1000)\n\ndef clean_faucet_labels(label):\n    if \"Country\" in label:\n        return label.split(\"=\")[-1] \n    else:\n        return \"\"\n\nfig.for_each_annotation(lambda a: a.update(text=clean_faucet_labels(a.text)))\n\n\nfig.update_layout(legend=dict(\n    orientation=\"h\",\n    yanchor=\"bottom\",\n    y=-.3,\n    xanchor=\"left\",\n    x=0\n\n))\n\nfig.show()\n\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Recommendations\n\nA multitude of factors related to the Covid-19 pandemic have resulted in decreased levels of domestic and commercial transportation activity. Although it is reasonable to expect that these current levels of mobility will eventually increase, it will not be uniform and certain policy changes (i.e. flexible work arrangements) may have lasting impacts on future traffic levels. Therefore, I recommend that a greater emphasis be placed on KPI's related to increasing adoption of environmentally sustainable modes of transportation and improving vehicle effeciency. "}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}