{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"# Geocoding locations and localizing time\n\nThis notebook illustrates the impact of localized time on features which are based on the **time of the day**. It consists of two main sections\n1. A recap of the effect and EDA visualization based on the Google Analytics data challenge\n2. A walkthrough of the code and key points using\n    - geocoded geographical data from Google maps API\n    - localized UTC time usiffromromng pycountry_convert and pytz\n\nIn the context of the Google Analytics Kaggle challenge, localizing the UTC time data from visitStartTime shows a significant impact from an EDA point of view (impact on LB to be confirmed) \n\n--------------------------------------------\n\n# In a nutshel\nSince sessions are recorded in [UTC time ][1] and users live in different time zones, any constructed feature such as *hour of the day* based on UTC time will not be representative of the local hour at which the event took place. The local hour of the day can be useful in a number of situations, for example when predicting online transactions in the Google Analytics challenge. \n\nWe would expect the number of sessions to reflect patterns of every day life, with reduced frequency during the night, and a peak during day time. However when comparing the same plots for UTC and Local time we see that:\n\n- **In UTC time**: the number of sessions vs hour of the day shows small variation between night and day time\n- **In local time**: the same plot shows significantly greater variation between night time and day time\n\nDepending on the geographical distribution of users in the data set, the effect of their respective time zones may cancel out, producing a more \"flat\" distribution over hours of the day. Pushing this intuition to the extreme, a uniform distribution around the globe would lead to a nearly flat plot of frequency across hours of the day. \n\nSee below for various plots of sessions and revenues for UTC and local time, both for the training and testing data sets. \n\n-----------------------------------------\n\n\n\n\n[1]: https://en.wikipedia.org/wiki/Coordinated_Universal_Time"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"from IPython.display import Image\nurl = 'https://i.imgur.com/KYoRYVX.png'\nImage(url,width=1000, height=1000)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0ac1b1d117cdf14d9dcd108ff49683a3995b8068","_kg_hide-input":true},"cell_type":"code","source":"url = 'https://i.imgur.com/Q3gQ9sA.png'\nImage(url,width=1000, height=1000)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cfc52621be6581d035e6e181c17678fdae5f8042"},"cell_type":"markdown","source":"# Code and walkthrough\n\n## General approach\n\nThe main issue here is that the time zone library requires a specific input format of the type**tz**, see [here][1] for a list. The good news is that the Google Maps geocoding API provides all the necessary information (and much more). Moreover, google maps can work with inconsistent and even contradictory search terms, which makes our life much easier. \n\nOnce we have the required tz information for each line, we can use the pytz library to create a locally time zone aware column, and then extract hours and other local time features \n\n**The approach can be summarised as follows:**\n\n- Concatenate the train and test sets into a single data set (size > 1M rows)\n- Generate a \"search term\" column which will be passed to the Google Maps API \n- Keep only the unique search term values (size = 2 - 3k)\n- This search term will also act as a foreign key to help us map the geocoded information and the original data sets\n- Once each line has a timezone, use pytz to generate a locally aware time column\n- Extract hour of the day\n\n[1]: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones\n\n\n## Step 1) Geocoding the geographical information using Google Maps API\n\nBefore using the API you will need to sign up on Google API and generate an API key, this [tutorial][2] can be of use. \n\n**Attention:** The API is free as long as the cost of requests is below 300 $ per month. For this reason, make sure that you set quota limits on the number of requests per day, for example 5,000 or 10,000.\n\nThe actual API call proceeds in a few steps:\n1. Call the *geocode* API to find the geocoded information corresponding to the search term\n2. Extract the latitude and longitude from the returned info\n3. Call the *timezone* API to obtain the corresponding time zone\n\n\n[2]: https://elfsight.com/blog/2018/06/how-to-get-google-maps-api-key-guide/"},{"metadata":{"trusted":true,"_uuid":"f53e8a3400a6300937e512d53196b70468456e20","_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"import os\nimport numpy as np\nimport pandas as pd\nimport time\nimport warnings\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nplt.style.use('ggplot')\n\nfrom pycountry_convert import ( map_countries, country_name_to_country_alpha3,country_name_to_country_alpha2)\nimport pytz as pytz\nimport datetime\n\n\nos.chdir(\"/Users/User/Desktop/Data/Kaggle/Google_Analytics/input\")\npd.options.display.max_columns = None\nwarnings.filterwarnings('ignore')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"658b921cccdfbbec95d4ed48b2b5ba0c9bb82f07"},"cell_type":"markdown","source":"### Example API call "},{"metadata":{"trusted":true,"_uuid":"c30c10b1146d89f83c9f11c4133d0c335d8807c4","_kg_hide-output":true,"_kg_hide-input":false},"cell_type":"code","source":"import googlemaps\nfrom datetime import datetime\n\n#Setting up API key\ngmaps = googlemaps.Client(key='put_your_API_KEY_here')\n\n###EXAMPLE\n# Geocoding an address (API CALL)\ngeocode_result = gmaps.geocode('1600 Amphitheatre Parkway, Mountain View, CA')\n\n#Extracting timezone from latitude and longitude (API CALL)\ntimez = gmaps.timezone(location = geocode_result[0]['geometry']['location'])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3bf3589f3cbcc54fa235190c26345d44e2add617"},"cell_type":"markdown","source":"### Preparing data and search term"},{"metadata":{"trusted":true,"_uuid":"3aff6d0304ecab5938f9d41154df534007cd8244","_kg_hide-output":true},"cell_type":"code","source":"#Utility function\ndef remove_missing_vals(x):\n    remove_list = ['(not set)', 'not available in demo dataset','unknown.unknown']\n    if x in remove_list:\n        return ''\n    else:\n        return x \n\n#Concatenate train and test set\nall_df = pd.concat([train_df, test_df], axis = 0)\n\n#Subset only geographical columns\nall_geo = all_df[GEOGRAPHICAL_COLUMNS]\n\n#Remove \"Not set\" and other inconsistent values\nall_geo = all_geo.applymap(remove_missing_vals)\n\n#Concatenate city, region, country into a single column\nall_city_region_country = all_geo['geoNetwork.city'] + ' ' + all_geo['geoNetwork.region'] + ' ' + all_geo['geoNetwork.country']\n\n#Keep only unique values\nall_city_region_country = all_city_region_country.unique()\nprint('Number of unique values:', all_city_region_country.shape)\n\n#delete for memory\n#del all_geo\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ea9c0a4287f9bdc9307193a6a190082688dbef08"},"cell_type":"markdown","source":"## API calls"},{"metadata":{"trusted":true,"_uuid":"f00c4a318e2871acfaa714cba7a0e5d24b25b15b","_kg_hide-output":true},"cell_type":"code","source":"from pandas.io.json import json_normalize\nimport json\n\n#Initialize empty df\ngeocode_df = pd.DataFrame()\n\nfor search_term in all_city_region_country:\n#Note this can be slow (30 - 45 mins for 3k lines)   \n    try:\n        # Geocoding an address\n        geocode_result = gmaps.geocode(search_term)\n\n        #Extracting timezone from latitude and longitude\n        timezone_result = gmaps.timezone(location = geocode_result[0]['geometry']['location'])\n\n        #Normalize the result so that we can work with it as a df\n        temp_df = json_normalize(geocode_result)\n        temp_df['search_term'] = search_term\n        temp_df['timeZoneId'] = timezone_result['timeZoneId']\n        geocode_df = geocode_df.append(temp_df, ignore_index = True)\n    \n    except:\n        pass","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5a3fded9fe6c2796cf4a04c00fe0fb9c0e8e4234"},"cell_type":"markdown","source":"### Utility functions "},{"metadata":{"trusted":true,"_uuid":"f75b9a75b66b78423c2987a4c7fc58e5fad53706","_kg_hide-output":true},"cell_type":"code","source":"def time_zone_converter(x):\n    try:\n        return pytz.country_timezones(x)[0]\n    except AttributeError:\n        return np.nan\n    \ndef time_localizer(s):\n    #format of series [time,zone]\n    try:\n        tz =pytz.timezone(s[1])\n        return pytz.utc.localize(s[0], is_dst=None).astimezone(tz)\n    except:\n        return np.nan\n    \ndef map_timezone(x):   \n    try:\n        return timezone_dict[x]\n    except KeyError:\n        return 'UTC'\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b10de60584a7d099b61c2d851825919750aeb28f"},"cell_type":"markdown","source":"### Saving / Loading "},{"metadata":{"trusted":true,"_uuid":"bed5ec5420638337c6233b314783fe7c7fe70bdb","_kg_hide-output":true},"cell_type":"code","source":"%%time\n##Load geocoded information\ngeocode_df= pd.read_csv('geocodes_timezones.csv')\n#train_df = pd.read_pickle('train_flat.pkl')\n#test_df = pd.read_pickle('train_flat.pkl')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c5f9053f846c4dc58f05d1446781f3cbcb315a5d"},"cell_type":"markdown","source":"## Step 2) Using the time zone information wity pytz to localize time "},{"metadata":{"trusted":true,"_uuid":"baef070f3f52d7e0b433b66a19e330a50756cec3","_kg_hide-output":true},"cell_type":"code","source":"#Generate foreign key '_search_term' by concatenating city, region, country\ntrain_df['_search_term'] = train_df['geoNetwork.city'].map(remove_missing_vals) + ' ' + train_df['geoNetwork.region'].map(remove_missing_vals) + ' ' + train_df['geoNetwork.country'].map(remove_missing_vals)\ntest_df['_search_term'] = test_df['geoNetwork.city'].map(remove_missing_vals) + ' ' + test_df['geoNetwork.region'].map(remove_missing_vals) + ' ' + test_df['geoNetwork.country'].map(remove_missing_vals)\n\n#Set global variable, needed for map_timezone function\nglobal timezone_dict\ntimezone_dict = dict(zip(geocode_df['search_term'], geocode_df['timeZoneId']))\n\n\n#Map timezones\ntrain_df['_timeZoneId'] = train_df['_search_term'].map(map_timezone)\ntest_df['_timeZoneId'] = test_df['_search_term'].map(map_timezone)\n\n  \n#Create time zone aware column\ntrain_df['_local_time'] = train_df[['visitStartTime', '_timeZoneId']].apply(time_localizer, axis = 1).astype(str)\ntest_df['_local_time'] = test_df[['visitStartTime', '_timeZoneId']].apply(time_localizer, axis = 1).astype(str)  \n\n#Localize hour time\ntrain_df['_local_hourofday'] = train_df['_local_time'].str[11:13]\ntest_df['_local_hourofday'] = test_df['_local_time'].str[11:13]\n\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8fd75803258470ff3c1197d05d778ecc73f6338c"},"cell_type":"markdown","source":"# Plotting UTC and local hour of the day "},{"metadata":{"trusted":true,"_uuid":"e26f89f1686c8ede2b1b8cce60d5a5df84010ca6","_kg_hide-output":true},"cell_type":"code","source":"#Creating a df with visitstarttime as the index\nsub_cols = ['fullVisitorId', 'sessionId', 'visitId','visitStartTime', \n             '_local_time', '_timeZoneId', '_local_hourofday', 'totals.transactionRevenue' ]\n\n\ntrain_ts = train_df[sub_cols].copy()\ntest_ts =  test_df[sub_cols].copy()\ntrain_ts.index = train_ts['visitStartTime']\ntest_ts.index = test_ts['visitStartTime']\n\ntrain_ts['_utc_hourofday'] = train_ts.index.hour\ntest_ts['_utc_hourofday'] = test_ts.index.hour\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e4f0e71375812e458485b73422ec1e8aaac86c5f"},"cell_type":"markdown","source":"### Sessions per hour of day (UTC vs Local time) "},{"metadata":{"trusted":true,"_uuid":"7c22930c2928d53903f66a21613c92455d8c6f54","_kg_hide-output":true},"cell_type":"code","source":"df1 = train_ts.groupby('_utc_hourofday').count()['sessionId']\ndf2 = train_ts.groupby('_local_hourofday').count()['sessionId']\ndf3 = test_ts.groupby('_utc_hourofday').count()['sessionId']\ndf4 = test_ts.groupby('_local_hourofday').count()['sessionId']\n\nplt.figure(figsize = (15,15))\nplt.subplot(2,2,1)\n\nsns.barplot(x = df1.index ,y = df1.values, color = 'darkblue', alpha = .6)\nplt.title('Sessions per hour of day (Training UTC)')\n\nplt.subplot(2,2,2)\nsns.barplot(x = df2.index ,y = df2.values, color = 'darkblue', alpha = .6)\nplt.title('Sessions per hour of day (Training Local)')\n\nplt.subplot(2,2,3)\nsns.barplot(x = df3.index ,y = df3.values, color = 'darkred', alpha = .6)\nplt.title('Sessions per hour of day (Test UTC)')\n\nplt.subplot(2,2,4)\nsns.barplot(x = df4.index ,y = df4.values, color = 'darkred', alpha = .6)\nplt.title('Sessions per hour of day (Test Local)')\n\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2f48a95c6152c060347f00c8dfec2e31a0ca20d1"},"cell_type":"markdown","source":"### Revenues per hour of day (UTC vs local)"},{"metadata":{"trusted":true,"_uuid":"2877987e325f20f9606ac74e784cefae97a75979","_kg_hide-output":true},"cell_type":"code","source":"df1 = train_ts.groupby('_utc_hourofday').sum()['totals.transactionRevenue']\ndf2 = train_ts.groupby('_local_hourofday').sum()['totals.transactionRevenue']\ndf3 = test_ts.groupby('_utc_hourofday').sum()['totals.transactionRevenue']\ndf4 = test_ts.groupby('_local_hourofday').sum()['totals.transactionRevenue']\n\nplt.figure(figsize = (15,15))\n\nplt.subplot(2,2,1)\nsns.barplot(x = df1.index ,y = df1.values, color = 'darkblue', alpha = .6)\nplt.title('Revenues per hour of day (Train UTC) ')\n\nplt.subplot(2,2,2)\nsns.barplot(x = df2.index ,y = df2.values, color = 'darkblue', alpha = .6)\nplt.title('Revenues per hour of day (Train Local)')\n\n\nplt.subplot(2,2,3)\nsns.barplot(x = df3.index ,y = df3.values, color = 'darkred', alpha = .6)\nplt.title('Revenues per hour of day (Test UTC)')\n\nplt.subplot(2,2,4)\nsns.barplot(x = df4.index ,y = df4.values, color = 'darkred', alpha = .6)\nplt.title('Revenues per hour of day (Test Local)')\n\n\nplt.show()","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}