notebooks/Scraper 2.0.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scraper of Google, Apple and Waze Mobility reports"
   ]
  },
  {
   "source": [
    "This notebook loads Google, Apple, Waze and TomTom Mobility reports, builds cleaned reports in different formats and builds merged files from these sources.\n",
    "\n",
    "Original data:\n",
    "    - Google Community Mobility reports: https://www.google.com/covid19/mobility/\n",
    "    - Apple Mobility Trends reports: https://www.apple.com/covid19/mobility\n",
    "    - Waze COVID-19 local driving trends: https://www.waze.com/covid19\n",
    "    - TomTom Traffic Index: https://www.tomtom.com/en_gb/traffic-index/ranking/"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import datetime\n",
    "import time\n",
    "\n",
    "import requests\n",
    "import urllib.request\n",
    "from bs4 import BeautifulSoup\n",
    "import re\n",
    "import json\n",
    "\n",
    "import pandas as pd\n",
    "import zipfile as zp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_google_link():\n",
    "    '''Get link of Google Community Mobility report file\n",
    "    \n",
    "       Returns:\n",
    "           link (str): link of Google Community report file\n",
    "    '''\n",
    "    # get webpage source\n",
    "    url = 'https://www.google.com/covid19/mobility/'\n",
    "    response = requests.get(url)\n",
    "    soup = BeautifulSoup(response.text, \"html.parser\")\n",
    "    csv_tag = soup.find('a', {\"class\": \"icon-link\"})\n",
    "    link = csv_tag['href']\n",
    "    return link"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def download_google_report(directory=\"google_reports\"):\n",
    "    '''Download Google Community Mobility report in CSV format\n",
    "\n",
    "        Args:\n",
    "            directory: directory to which CSV report will be downloaded\n",
    "\n",
    "        Returns:\n",
    "            new_files (bool): flag indicating whether or not new files have been downloaded\n",
    "    '''\n",
    "    new_files = False\n",
    "\n",
    "    # create directory if it don't exist\n",
    "    if not os.path.exists(directory) and directory!='':\n",
    "        os.makedirs(directory)\n",
    "\n",
    "    # download CSV file\n",
    "    link = get_google_link()\n",
    "    file_name = \"Global_Mobility_Report.csv\"\n",
    "    path = os.path.join(directory, file_name)\n",
    "    old_size = os.path.getsize(path) if os.path.isfile(path) else 0\n",
    "    urllib.request.urlretrieve(link, path)\n",
    "    new_size = os.path.getsize(path)\n",
    "    if old_size!=new_size:\n",
    "        new_files = True\n",
    "\n",
    "    if not new_files:\n",
    "        print('Google: No updates')\n",
    "    else:\n",
    "        print('Google: Update available')\n",
    "    \n",
    "    return new_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def build_google_report(\n",
    "        source=os.path.join(\"google_reports\", \"Global_Mobility_Report.csv\"),\n",
    "        report_type=\"regions\",\n",
    "        countries=None,\n",
    "        world_regions=None,\n",
    "        country_regions_file = os.path.join(\"auxiliary_data\", \"country_worldregions.csv\")):\n",
    "    '''Build cleaned Google report for the worldwide\n",
    "\n",
    "        Args:\n",
    "            source: location of the raw Google CSV report\n",
    "            report_type: available options: \n",
    "                            1) \"regions\" - basic report for the worldwide\n",
    "                            2) \"US\" - report for the US\n",
    "                            3) \"regions_detailed\" - detailed report for selected countries\n",
    "                            4) \"world_regions_detailed\" - detailed report for selected regions (Europe, Asia etc)\n",
    "            countries: list of countries for \"regions_detailed\" option. If None - all countries selected\n",
    "            world_regions: list of regions for \"world_regions_detailed option. If None - all regions selected\n",
    "            country_regions_file: path of the CSV file with matching table of countries and regions\n",
    "\n",
    "        Returns:\n",
    "           google (DataFrame): generated Google report\n",
    "    '''\n",
    "    # read the raw report\n",
    "    google = pd.read_csv(source, low_memory=False)\n",
    "    # shorten value column names\n",
    "    google.columns = google.columns.str.replace(\n",
    "        r'_percent_change_from_baseline', '')\n",
    "    # remove underscores from column names\n",
    "    google.columns = google.columns.str.replace(r'_', ' ')\n",
    "    # rename country column\n",
    "    google = google.rename(columns={'country region': 'country'})\n",
    "    if report_type == \"regions\":\n",
    "        # remove data of subregions of the second level\n",
    "        google = google[google['sub region 2'].isnull()]\n",
    "        # remove metropolitan data\n",
    "        google = google[google['metro area'].isnull()]\n",
    "        # rename region column\n",
    "        google = google.rename(columns={'sub region 1': 'region'})\n",
    "        google = google.loc[:,\n",
    "                            ['country',\n",
    "                             'region',\n",
    "                             'date',\n",
    "                             'retail and recreation',\n",
    "                             'grocery and pharmacy',\n",
    "                             'parks',\n",
    "                             'transit stations',\n",
    "                             'workplaces',\n",
    "                             'residential']]\n",
    "        google['region'].fillna('Total', inplace=True)\n",
    "    elif report_type == \"US\":\n",
    "        google = google[(google['country'] == \"United States\")]\n",
    "        google = google.rename(\n",
    "            columns={\n",
    "                'sub region 1': 'state',\n",
    "                'sub region 2': 'county'})\n",
    "        google = google.loc[:,\n",
    "                            ['state',\n",
    "                             'county',\n",
    "                             'date',\n",
    "                             'retail and recreation',\n",
    "                             'grocery and pharmacy',\n",
    "                             'parks',\n",
    "                             'transit stations',\n",
    "                             'workplaces',\n",
    "                             'residential']]\n",
    "        google['state'].fillna('Total', inplace=True)\n",
    "        google['county'].fillna('Total', inplace=True)\n",
    "    elif report_type == \"regions_detailed\" or report_type == \"world_regions_detailed\":\n",
    "        if countries is not None and report_type == \"regions_detailed\":\n",
    "            google = google[google.country.isin(countries)]\n",
    "        if report_type == \"world_regions_detailed\":\n",
    "            if os.path.isfile(country_regions_file):\n",
    "                country_regions = pd.read_csv(country_regions_file)\n",
    "            google = pd.merge(google, country_regions, on='country')\n",
    "            if world_regions is not None:\n",
    "                google = google[google.world_region.isin(world_regions)]\n",
    "        # metro area -> sub region 1    \n",
    "        google['sub region 1'] = google.apply(lambda x: x['sub region 1'] if isinstance(x['sub region 1'],str)\n",
    "                                                  else x['metro area'], axis=1)\n",
    "        column_list = ['world_region'] if report_type == \"world_regions_detailed\" else []\n",
    "        column_list = column_list + ['country',\n",
    "                         'sub region 1',\n",
    "                         'sub region 2',\n",
    "                         'date',\n",
    "                         'retail and recreation',\n",
    "                         'grocery and pharmacy',\n",
    "                         'parks',\n",
    "                         'transit stations',\n",
    "                         'workplaces',\n",
    "                         'residential']\n",
    "        google = google.loc[:, column_list]\n",
    "        google['sub region 1'].fillna('Total', inplace=True)\n",
    "        google['sub region 2'].fillna('Total', inplace=True)\n",
    "    return google"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_apple_link():\n",
    "    '''Get link of Apple Mobility Trends report file\n",
    "    \n",
    "       Returns:\n",
    "           link (str): link of Apple Mobility Trends report file\n",
    "    '''\n",
    "    # get link via API\n",
    "    json_link = \"https://covid19-static.cdn-apple.com/covid19-mobility-data/current/v3/index.json\"\n",
    "    with urllib.request.urlopen(json_link) as url:\n",
    "        json_data = json.loads(url.read().decode())\n",
    "    link = \"https://covid19-static.cdn-apple.com\" + \\\n",
    "        json_data['basePath'] + json_data['regions']['en-us']['csvPath']\n",
    "    return link"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def download_apple_report(directory=\"apple_reports\"):\n",
    "    '''Download Apple Mobility Trends report in CSV\n",
    "\n",
    "        Args:\n",
    "            directory: directory to which CSV report will be downloaded\n",
    "\n",
    "        Returns:\n",
    "            new_files (bool): flag indicating whether or not a new file has been downloaded\n",
    "    '''\n",
    "    new_files = False\n",
    "    \n",
    "    # create directory if it don't exist\n",
    "    if not os.path.exists(directory) and directory!='':\n",
    "        os.makedirs(directory)\n",
    "    \n",
    "    link = get_apple_link()\n",
    "    file_name = \"applemobilitytrends.csv\"\n",
    "    path = os.path.join(directory, file_name)\n",
    "    path = os.path.join(directory, file_name)\n",
    "    old_size = os.path.getsize(path) if os.path.isfile(path) else 0\n",
    "    urllib.request.urlretrieve(link, path)\n",
    "    new_size = os.path.getsize(path)\n",
    "    if old_size!=new_size:\n",
    "        new_files = True\n",
    "\n",
    "    if not new_files:\n",
    "        print('Apple: No updates')\n",
    "    else:\n",
    "        print('Apple: Update available')\n",
    "    \n",
    "    return new_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def build_apple_report(\n",
    "    source=os.path.join(\n",
    "        'apple_reports',\n",
    "        \"applemobilitytrends.csv\"),\n",
    "        report_type=\"regions\"):\n",
    "    '''Build cleaned Apple report (transform dates from columns to rows, add country names for subregions and cities)\n",
    "       for the worldwide or for some country (currently only for the US)\n",
    "\n",
    "        Args:\n",
    "            source: location of the raw Apple CSV report\n",
    "            destination: destination file path\n",
    "            report_type: two options available: \"regions\" - report for the worldwide, \"US\" - report for the US\n",
    "\n",
    "        Returns:\n",
    "           apple (DataFrame): generated Apple report\n",
    "    '''\n",
    "    apple = pd.read_csv(source, low_memory=False)\n",
    "    apple = apple.drop(columns=['alternative_name'])\n",
    "    apple['country'] = apple.apply(\n",
    "        lambda x: x['region'] if x['geo_type'] == 'country/region' else x['country'],\n",
    "        axis=1)\n",
    "\n",
    "    if report_type == 'regions':\n",
    "        apple = apple[apple.geo_type != 'county']\n",
    "        apple['sub-region'] = apple.apply(lambda x: 'Total' if x['geo_type'] == 'country/region' else (\n",
    "            x['region'] if x['geo_type'] == 'sub-region' else x['sub-region']), axis=1)\n",
    "        apple['subregion_and_city'] = apple.apply(\n",
    "            lambda x: 'Total' if x['geo_type'] == 'country/region' else x['region'], axis=1)\n",
    "        apple = apple.drop(columns=['region'])\n",
    "        apple['sub-region'] = apple['sub-region'].fillna(\n",
    "            apple['subregion_and_city'])\n",
    "\n",
    "        apple = apple.melt(\n",
    "            id_vars=[\n",
    "                'geo_type',\n",
    "                'subregion_and_city',\n",
    "                'sub-region',\n",
    "                'transportation_type',\n",
    "                'country'],\n",
    "            var_name='date')\n",
    "        apple['value'] = apple['value'] - 100\n",
    "\n",
    "        apple = apple.pivot_table(\n",
    "            index=[\n",
    "                \"geo_type\",\n",
    "                \"subregion_and_city\",\n",
    "                \"sub-region\",\n",
    "                \"date\",\n",
    "                \"country\"],\n",
    "            columns='transportation_type').reset_index()\n",
    "        apple.columns = [t + (v if v != \"value\" else \"\")\n",
    "                         for v, t in apple.columns]\n",
    "        apple = apple.loc[:,\n",
    "                          ['country',\n",
    "                           'sub-region',\n",
    "                           'subregion_and_city',\n",
    "                           'geo_type',\n",
    "                           'date',\n",
    "                           'driving',\n",
    "                           'transit',\n",
    "                           'walking']]\n",
    "        apple = apple.sort_values(by=['country',\n",
    "                                      'sub-region',\n",
    "                                      'subregion_and_city',\n",
    "                                      'date']).reset_index(drop=True)\n",
    "    elif report_type == \"US\":\n",
    "        apple = apple[apple.country == \"United States\"].drop(columns=[\n",
    "                                                             'country'])\n",
    "        apple['sub-region'] = apple['sub-region'].fillna(\n",
    "            apple['region']).replace({\"United States\": \"Total\"})\n",
    "        apple['region'] = apple.apply(lambda x: x['region'] if (\n",
    "            x['geo_type'] == 'city' or x['geo_type'] == 'county') else 'Total', axis=1)\n",
    "        apple = apple.rename(\n",
    "            columns={\n",
    "                'sub-region': 'state',\n",
    "                'region': 'county_and_city'})\n",
    "\n",
    "        apple = apple.melt(\n",
    "            id_vars=[\n",
    "                'geo_type',\n",
    "                'state',\n",
    "                'county_and_city',\n",
    "                'transportation_type'],\n",
    "            var_name='date')\n",
    "        apple['value'] = apple['value'] - 100\n",
    "\n",
    "        apple = apple.pivot_table(\n",
    "            index=[\n",
    "                'geo_type',\n",
    "                'state',\n",
    "                'county_and_city',\n",
    "                'date'],\n",
    "            columns='transportation_type').reset_index()\n",
    "        apple.columns = [t + (v if v != \"value\" else \"\")\n",
    "                         for v, t in apple.columns]\n",
    "\n",
    "        apple = apple.loc[:, ['state', 'county_and_city', 'geo_type',\n",
    "                              'date', 'driving', 'transit', 'walking']]\n",
    "        apple = apple.sort_values(\n",
    "            by=['state', 'county_and_city', 'geo_type', 'date']).reset_index(drop=True)\n",
    "    return apple"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_waze_links():\n",
    "    '''Get links of raw Waze local driving Trends report files\n",
    "    \n",
    "       Returns:\n",
    "           link (list): links of Waze local driving Trends report files\n",
    "    '''\n",
    "    github_link = \"https://raw.githubusercontent.com/ActiveConclusion/waze_mobility_scraper/master/\"\n",
    "    country_level_link = github_link + \"Waze_Country-Level_Data.csv\"\n",
    "    city_level_link = github_link + \"Waze_City-Level_Data.csv\"\n",
    "    return [country_level_link, city_level_link]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def download_waze_reports(directory=\"waze_reports\"):\n",
    "    '''Download Waze local driving Trends reports in CSV\n",
    "\n",
    "        Args:\n",
    "            directory: directory to which CSV reports will be downloaded\n",
    "\n",
    "        Returns:\n",
    "            new_files (bool): flag indicating whether or not a new files has been downloaded\n",
    "    '''\n",
    "    new_files = False\n",
    "    \n",
    "    # create directory if it don't exist\n",
    "    if not os.path.exists(directory) and directory!='':\n",
    "        os.makedirs(directory)\n",
    "    \n",
    "    links = get_waze_links()\n",
    "    file_names = [\"Waze_Country-Level_Data.csv\", \"Waze_City-Level_Data.csv\"]\n",
    "    file_links = dict(zip(file_names, links))\n",
    "    for file_name in file_names: \n",
    "        path = os.path.join(directory, file_name)\n",
    "        link = file_links[file_name]\n",
    "        old_size = os.path.getsize(path) if os.path.isfile(path) else 0\n",
    "        urllib.request.urlretrieve(link, path)\n",
    "        new_size = os.path.getsize(path)\n",
    "        if old_size!=new_size:\n",
    "            new_files = True\n",
    "\n",
    "    if not new_files:\n",
    "        print('Waze: No updates')\n",
    "    else:\n",
    "        print('Waze: Update available')\n",
    "    \n",
    "    return new_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def build_waze_report(countries_source=os.path.join(\"waze_reports\", \"Waze_Country-Level_Data.csv\"),\n",
    "                     cities_source=os.path.join(\"waze_reports\", \"Waze_City-Level_Data.csv\")):\n",
    "    '''Build cleaned Waze report (transform dates from string to date format, merge country&city-level data,\n",
    "        add geo_type column)\n",
    "\n",
    "        Args:\n",
    "            countries_source: location of the raw Waze country-level CSV report\n",
    "            cities_source: location of the raw Waze city-level CSV report\n",
    "\n",
    "        Returns:\n",
    "           waze (DataFrame): generated Waze report\n",
    "    '''\n",
    "    waze_countries = pd.read_csv(countries_source, parse_dates=['Date'])\n",
    "    waze_cities = pd.read_csv(cities_source, parse_dates=['Date'])\n",
    "    waze_countries['City'] = 'Total'\n",
    "    waze_countries['geo_type'] = 'country'\n",
    "    waze_cities['geo_type'] = 'city'\n",
    "    \n",
    "    waze = waze_countries.append(waze_cities)\n",
    "    waze = waze.rename(columns={'Country':'country', 'City':'city', \n",
    "                                'Date':'date', '% Change In Waze Driven Miles/KMs':'driving_waze'})\n",
    "    waze['driving_waze'] = waze['driving_waze'] * 100\n",
    "    waze['date'] = waze['date'].dt.date\n",
    "    waze = waze.loc[:,['country', 'city','geo_type','date', 'driving_waze']]\n",
    "    waze = waze.sort_values(by=['country', 'city', 'geo_type', 'date']).reset_index(drop=True)\n",
    "    return waze"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_tomtom_update(\n",
    "    tomtom_source=os.path.join(\"tomtom_reports\", \"tomtom_trafic_index.csv\"),\n",
    "    api_key_check=\"JPN_tokyo\",\n",
    "):\n",
    "    \"\"\"Check if new TomTom data available\n",
    "\n",
    "    Args:\n",
    "        tomtom_source: location of the TomTom report in CSV format (if exist)\n",
    "        api_key_check: which city will be checked on the TomTom site\n",
    "    Returns:\n",
    "        new_files (bool): flag indicating whether or not new data available\n",
    "    \"\"\"\n",
    "    new_files = False\n",
    "    # check if file available\n",
    "    if not os.path.exists(tomtom_source):\n",
    "        new_files = True\n",
    "    else:\n",
    "        # get max date from the CSV report\n",
    "        tomtom = pd.read_csv(tomtom_source, low_memory=False)\n",
    "        last_report_date = tomtom[\"date\"].max()\n",
    "        # get last available date from API\n",
    "        base_api_url = \"https://api.midway.tomtom.com/ranking/dailyStats/\"\n",
    "        api_url = base_api_url + api_key_check\n",
    "        response = requests.get(api_url)\n",
    "        json_data = response.json()\n",
    "        last_api_date = json_data[-1][\"date\"]\n",
    "        if last_api_date != last_report_date:\n",
    "            new_files = True\n",
    "    if not new_files:\n",
    "        print(\"TomTom: No updates\")\n",
    "    else:\n",
    "        print(\"TomTom: Update available\")\n",
    "\n",
    "    return new_files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def download_tomtom_report(\n",
    "    alpha_codes_filename=os.path.join(\"auxiliary_data\", \"country_alpha_codes.csv\")\n",
    "):\n",
    "    \"\"\"Download TomTom Traffic Index\n",
    "\n",
    "    Args:\n",
    "        iso_codes_filename: path to alpha country codes file\n",
    "\n",
    "    Returns:\n",
    "        tomtom_data (DataFrame): scraped TomTom report\n",
    "    \"\"\"\n",
    "    # get all available cities\n",
    "    json_link = (\n",
    "        \"https://www.tomtom.com/en_gb/traffic-index/page-data/ranking/page-data.json\"\n",
    "    )\n",
    "    with urllib.request.urlopen(json_link) as url:\n",
    "        json_data = json.loads(url.read().decode())\n",
    "    # unpack data from json\n",
    "    json_city_data = json_data[\"result\"][\"data\"][\"allCitiesJson\"][\"edges\"]\n",
    "    # merge data and select necessary columns\n",
    "    merged_data = {}\n",
    "    for k in [\"name\", \"country\", \"countryName\", \"continent\", \"key\"]:\n",
    "        merged_data[k] = tuple(merged_data[\"node\"][k] for merged_data in json_city_data)\n",
    "    city_data = pd.DataFrame(merged_data)\n",
    "    # define key exceptions\n",
    "    key_exceptions = {\n",
    "        \"birmingham-alabama\": \"birmingham\",\n",
    "        \"hamilton-nz\": \"hamilton\",\n",
    "        \"london-ontario\": \"london\",\n",
    "        \"newcastle-au\": \"newcastle\",\n",
    "        \"bengaluru\": \"bangalore\",\n",
    "    }\n",
    "    # replace exceptions in DataFrame\n",
    "    city_data.replace({\"key\": key_exceptions}, inplace=True)\n",
    "    # add Alpha3 country codes\n",
    "    # read file with alpha codes\n",
    "    alpha_codes = pd.read_csv(alpha_codes_filename)\n",
    "    # match by alpha2 codes\n",
    "    city_data = pd.merge(\n",
    "        city_data, alpha_codes, left_on=\"country\", right_on=\"Alpha2\", how=\"left\"\n",
    "    )\n",
    "    city_data.drop(\"country\", axis=1, inplace=True)\n",
    "    # create api key for scraping data\n",
    "    city_data[\"api_key\"] = city_data[\"Alpha3\"] + \"_\" + city_data[\"key\"]\n",
    "    # scrape data for each city\n",
    "    base_api_url = \"https://api.midway.tomtom.com/ranking/dailyStats/\"\n",
    "    city_df_list = []\n",
    "    for _, row in city_data.iterrows():\n",
    "        api_url = base_api_url + row[\"api_key\"]\n",
    "        response = requests.get(api_url)\n",
    "        city_df = pd.DataFrame(response.json())\n",
    "        city_df[\"country\"] = row[\"countryName\"]\n",
    "        city_df[\"city\"] = row[\"name\"]\n",
    "        city_df_list.append(city_df)\n",
    "    # merge all data\n",
    "    tomtom_data = pd.concat(city_df_list, ignore_index=True)\n",
    "    tomtom_data = tomtom_data.loc[\n",
    "        :, [\"country\", \"city\", \"date\", \"congestion\", \"diffRatio\"]\n",
    "    ]\n",
    "    tomtom_data = tomtom_data.sort_values(by=[\"country\", \"city\", \"date\"]).reset_index(\n",
    "        drop=True\n",
    "    )\n",
    "    return tomtom_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def build_summary_report(apple_source, google_source, report_type=\"regions\"):\n",
    "    '''Build a merged report from Google and Apple data\n",
    "\n",
    "        Args:\n",
    "            apple_source: location of the CSV report generated by build_apple_report function\n",
    "            google_source: location of the CSV report generated by build_google_report function\n",
    "            report_type: two options available: \"regions\" - report for the worldwide, \"US\" - report for the US\n",
    "\n",
    "        Returns:\n",
    "            summary (DataFrame): merged report from Google and Apple data\n",
    "    '''\n",
    "    apple = pd.read_csv(apple_source, low_memory=False)\n",
    "    google = pd.read_csv(google_source, low_memory=False)\n",
    "    summary = pd.DataFrame()\n",
    "    # build report for regions\n",
    "    if report_type == \"regions\":\n",
    "        apple = apple.rename(columns={'subregion_and_city': 'region'})\n",
    "        apple = apple.loc[:, ['country', 'region',\n",
    "                              'date', 'driving', 'transit', 'walking']]\n",
    "        # get matching table for converting Apple countries and subregions to\n",
    "        # Google names\n",
    "        country_AtoG_file = os.path.join(\n",
    "            'auxiliary_data', 'country_Apple_to_Google.csv')\n",
    "        subregions_AtoG_file = os.path.join(\n",
    "            'auxiliary_data', 'subregions_Apple_to_Google.csv')\n",
    "\n",
    "        if os.path.isfile(country_AtoG_file):\n",
    "            country_AtoG = pd.read_csv(country_AtoG_file, index_col=0)\n",
    "        else:\n",
    "            country_AtoG = None\n",
    "        if os.path.isfile(subregions_AtoG_file):\n",
    "            subregions_AtoG = pd.read_csv(subregions_AtoG_file, index_col=0)\n",
    "        else:\n",
    "            subregions_AtoG = None\n",
    "        # convert Apple countries and subregions to Google names\n",
    "        apple['country'] = apple.apply(lambda x: country_AtoG.loc[x['country'], 'country_google'] if (\n",
    "            country_AtoG is not None and x['country'] in country_AtoG.index) else x['country'], axis=1)\n",
    "        apple['region'] = apple.apply(lambda x: subregions_AtoG.loc[x['region'], 'subregion_Google'] if (\n",
    "            subregions_AtoG is not None and x['region'] in subregions_AtoG.index) else x['region'], axis=1)\n",
    "        # merge reports\n",
    "        apple = apple.set_index(['country', 'region', 'date'])\n",
    "        google = google.set_index(['country', 'region', 'date'])\n",
    "        summary = google.join(apple, how='outer')\n",
    "        summary = summary.reset_index(level=['country', 'region', 'date'])\n",
    "    elif report_type == \"US\":\n",
    "        apple = apple.loc[:, ['state', 'county_and_city',\n",
    "                              'date', 'driving', 'transit', 'walking']]\n",
    "        apple.loc[apple.state == 'Washington DC',\n",
    "                  'state'] = 'District of Columbia'\n",
    "        apple.loc[apple.county_and_city ==\n",
    "                  'Washington DC', 'county_and_city'] = 'Total'\n",
    "\n",
    "        google = google.rename(columns={'county': 'county_and_city'})\n",
    "        # merge reports\n",
    "        apple = apple.set_index(['state', 'county_and_city', 'date'])\n",
    "        google = google.set_index(['state', 'county_and_city', 'date'])\n",
    "        summary = google.join(apple, how='outer')\n",
    "        summary = summary.reset_index(\n",
    "            level=['state', 'county_and_city', 'date'])\n",
    "    return summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.chdir('..')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# process Google reports\n",
    "GOOGLE_ZIP_PATH = os.path.join(\"google_reports\", \"Global_Mobility_Report.zip\")\n",
    "GOOGLE_CSV_PATH = os.path.join(\"google_reports\", \"Global_Mobility_Report.csv\")\n",
    "# unzip existing report\n",
    "if os.path.exists(GOOGLE_ZIP_PATH):\n",
    "    with zp.ZipFile(GOOGLE_ZIP_PATH, 'r') as zf:\n",
    "        zf.extract('Global_Mobility_Report.csv', \"google_reports\")\n",
    "# download new report\n",
    "new_files_status_google = download_google_report()\n",
    "if new_files_status_google:\n",
    "    # build reports\n",
    "    # build basic report for the worldwide\n",
    "    google_world = build_google_report()\n",
    "    # build report for the US\n",
    "    google_US = build_google_report(report_type=\"US\")\n",
    "    # build report for Brazil\n",
    "    google_brazil = build_google_report( report_type=\"regions_detailed\", countries=[\"Brazil\"])\n",
    "    # build detailed reports for world regions\n",
    "    google_world_regions = build_google_report(report_type=\"world_regions_detailed\")\n",
    "    google_europe = google_world_regions[google_world_regions.world_region.isin(['Europe'])]\n",
    "    google_asia_africa = google_world_regions[google_world_regions.world_region.isin(['Asia', 'Africa'])]\n",
    "    google_america_oceania = google_world_regions[\n",
    "        google_world_regions.world_region.isin(['South America', 'North America', 'Oceania'])]\n",
    "    # write reports to CSV and Excel\n",
    "    google_world.to_csv(os.path.join(\"google_reports\", \"mobility_report_countries.csv\"), index=False)\n",
    "    google_world.to_excel(os.path.join(\"google_reports\", \"mobility_report_countries.xlsx\"), \n",
    "                          index=False, sheet_name='Data', engine = 'xlsxwriter')\n",
    "    google_US.to_csv(os.path.join(\"google_reports\", \"mobility_report_US.csv\"), index=False)\n",
    "    google_US.to_excel(os.path.join(\"google_reports\", \"mobility_report_US.xlsx\"), \n",
    "                          index=False, sheet_name='Data', engine = 'xlsxwriter')\n",
    "    google_brazil.to_csv(os.path.join(\"google_reports\", \"mobility_report_brazil.csv\"), index=False)\n",
    "    google_brazil.to_excel(os.path.join(\"google_reports\", \"mobility_report_brazil.xlsx\"), \n",
    "                          index=False, sheet_name='Data', engine = 'xlsxwriter')\n",
    "    google_europe.to_csv(os.path.join(\"google_reports\", \"mobility_report_europe.csv\"), index=False)\n",
    "    google_europe.to_excel(os.path.join(\"google_reports\", \"mobility_report_europe.xlsx\"), \n",
    "                          index=False, sheet_name='Data', engine = 'xlsxwriter')\n",
    "    google_asia_africa.to_csv(os.path.join(\"google_reports\", \"mobility_report_asia_africa.csv\"), index=False)\n",
    "    google_asia_africa.to_excel(os.path.join(\"google_reports\", \"mobility_report_asia_africa.xlsx\"), \n",
    "                          index=False, sheet_name='Data', engine = 'xlsxwriter')\n",
    "    google_america_oceania.to_csv(os.path.join(\"google_reports\", \"mobility_report_america_oceania.csv\"), index=False)\n",
    "    google_america_oceania.to_excel(os.path.join(\"google_reports\", \"mobility_report_america_oceania.xlsx\"), \n",
    "                          index=False, sheet_name='Data', engine = 'xlsxwriter')\n",
    "    # zip raw report\n",
    "    with zp.ZipFile(GOOGLE_ZIP_PATH, 'w', zp.ZIP_DEFLATED) as zf:\n",
    "        zf.write(GOOGLE_CSV_PATH,\"Global_Mobility_Report.csv\")\n",
    "# delete raw CSV report\n",
    "os.remove(GOOGLE_CSV_PATH)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# process Apple reports\n",
    "# download new report\n",
    "new_files_status_apple = download_apple_report()\n",
    "if new_files_status_apple:\n",
    "    # build reports\n",
    "    apple_world = build_apple_report()\n",
    "    apple_US = build_apple_report(report_type=\"US\")\n",
    "    # write reports to CSV and Excel\n",
    "    apple_world.to_csv(os.path.join(\"apple_reports\", \"apple_mobility_report.csv\"), index=False)\n",
    "    apple_world.to_excel(os.path.join(\"apple_reports\", \"apple_mobility_report.xlsx\"), \n",
    "                         index=False, sheet_name='Data', engine = 'xlsxwriter')\n",
    "    apple_US.to_csv(os.path.join(\"apple_reports\", \"apple_mobility_report_US.csv\"), index=False)\n",
    "    apple_US.to_excel(os.path.join(\"apple_reports\", \"apple_mobility_report_US.xlsx\"), \n",
    "                      index=False, sheet_name='Data', engine = 'xlsxwriter')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# process Waze reports\n",
    "# download new report\n",
    "new_files_status_waze = download_waze_reports()\n",
    "if new_files_status_waze:\n",
    "    # build report\n",
    "    waze = build_waze_report()\n",
    "    # write report to CSV and Excel\n",
    "    waze.to_csv(os.path.join(\"waze_reports\", \"waze_mobility.csv\"), index=False)\n",
    "    waze.to_excel(os.path.join(\"waze_reports\", \"waze_mobility.xlsx\"),\n",
    "                  index=False, sheet_name='Data', engine='xlsxwriter')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# process TomTom reports\n",
    "new_files_status_tomtom = check_tomtom_update()\n",
    "if new_files_status_tomtom:\n",
    "    # scrape new data\n",
    "    tomtom = download_tomtom_report()\n",
    "    tomtom.to_csv(\n",
    "        os.path.join(\"tomtom_reports\", \"tomtom_trafic_index.csv\"), index=False\n",
    "    )\n",
    "    tomtom.to_excel(\n",
    "        os.path.join(\"tomtom_reports\", \"tomtom_trafic_index.xlsx\"),\n",
    "        index=False,\n",
    "        sheet_name=\"Data\",\n",
    "        engine=\"xlsxwriter\",\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# build summary reports\n",
    "if new_files_status_apple or new_files_status_google:\n",
    "    print(\"Merging reports...\")\n",
    "    summary_regions = build_summary_report(os.path.join(\"apple_reports\",\"apple_mobility_report.csv\"),\n",
    "                                          os.path.join(\"google_reports\", \"mobility_report_countries.csv\"))\n",
    "    summary_US = build_summary_report(os.path.join(\"apple_reports\", \"apple_mobility_report_US.csv\"), \n",
    "                                      os.path.join(\"google_reports\", \"mobility_report_US.csv\"), 'US')\n",
    "    summary_countries = summary_regions[summary_regions['region']=='Total'].drop(columns=['region'])\n",
    "    \n",
    "    print('Writing merged reports to files...')\n",
    "    summary_regions.to_csv(os.path.join(\"summary_reports\", \"summary_report_regions.csv\"), index=False)\n",
    "    summary_regions.to_excel(os.path.join(\"summary_reports\", \"summary_report_regions.xlsx\"), \n",
    "                             index=False, sheet_name='Data', engine = 'xlsxwriter')\n",
    "    summary_US.to_csv(os.path.join(\"summary_reports\", \"summary_report_US.csv\"), index=False)\n",
    "    summary_US.to_excel(os.path.join(\"summary_reports\", \"summary_report_US.xlsx\"),\n",
    "                        index=False, sheet_name='Data', engine = 'xlsxwriter')\n",
    "    summary_countries.to_csv(os.path.join(\"summary_reports\", \"summary_report_countries.csv\"), index=False)\n",
    "    summary_countries.to_excel(os.path.join(\"summary_reports\", \"summary_report_countries.xlsx\"),\n",
    "                               index=False, sheet_name='Data', engine = 'xlsxwriter')"
   ]
  },
  {
   "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.8.5-final"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}