preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img style=\"float: left;\" src=\"images/logo.png\" width=200>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Does Mobility Make or Break Friendships? - Preprocessing\n",
    "\n",
    "---\n",
    "## Introduction\n",
    "\n",
    "Although our dataset has previously been wrangled and cleaned, we still have to apply a few modifications in able to perform our analysis. In this notebook we will create new datasets to be used in the creation of our prediction models and all of the steps leading up this. In this first step we will introduce the datasets to be used, we will then modify them and finally export new '.csv' files for analyis.\n",
    "\n",
    "---\n",
    "\n",
    "## The data\n",
    "\n",
    "Our extension considers data from a location-based social networks: Foursquare. The datasets are available in the `data` directory pushed to the same GitHub repo as this notebook. This directory contains four '.txt' files and are described below.\n",
    "\n",
    "### Friendship network data\n",
    "\n",
    "* `dataset_WWW_friendship_old.txt` Friendship network of Foursquare users in April 2012\n",
    "\n",
    "* `dataset_WWW_friendship_new.txt.gz` Friendship network of Foursquare users in January 2014\n",
    "\n",
    "Example:\n",
    "~~~\n",
    "[user]      [friendship]\n",
    "15          595326\n",
    "19          54\n",
    "19          1061\n",
    "...         ...\n",
    "~~~\n",
    "\n",
    "\n",
    "### Anonymised check-in data\n",
    "\n",
    "* `dataset_WWW_Checkins_anonymized.txt` Time and venue ID of check-ins made by Foursquare users.\n",
    "\n",
    "Example:\n",
    "~~~\n",
    "[user]  [venue_ID]                 [checkin_time]                    [offset]\n",
    "822121  4b4b87b5f964a5204a9f26e3   Tue Apr 03 18:00:07 +0000 2012    -420\n",
    "208842  4b4606f2f964a520751426e3   Wed Apr 04 11:32:47 +0000 2012    120\n",
    "113817  3fd66200f964a52000e71ee3   Fri Apr 06 21:30:23 +0000 2012    -300\n",
    "...     ...                        ...                               ...\n",
    "~~~\n",
    "\n",
    "\n",
    "### Venue information data\n",
    "\n",
    "* `raw_POIs.txt` Venue category and location information of Foursquare venue IDs.\n",
    "\n",
    "Example:\n",
    "~~~\n",
    "[venue_ID]                 [latitude]     [longitude]     [category]           [country_code]\n",
    "4b4b87b5f964a5204a9f26e3   40.729209\t  -73.998753      Mall                 US\n",
    "4b4606f2f964a520751426e3   36.309536      -119.31340      Asian Restaurant     MX\n",
    "3fd66200f964a52000e71ee3   -34.28493\t  65.3134064      Library              FR\n",
    "...                        ...            ...             ...                  ...\n",
    "~~~\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## Step 1: Loading the data\n",
    "\n",
    "---\n",
    "### 1.1 Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "from datetime import timedelta\n",
    "import requests"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2  Read the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data folder and dataset location\n",
    "DATA_FOLDER = 'data/'\n",
    "FOURSQUARE_FRIENDSHIP_OLD_DATASET = DATA_FOLDER + \"dataset_WWW_friendship_old.txt\"\n",
    "FOURSQUARE_FRIENDSHIP_NEW_DATASET = DATA_FOLDER + \"dataset_WWW_friendship_new.txt\"\n",
    "FOURSQUARE_CHECKIN_DATASET = DATA_FOLDER + \"dataset_WWW_Checkins_anonymized.txt\"\n",
    "FOURSQUARE_VENUE_DATASET = DATA_FOLDER + \"raw_POIs.txt\"\n",
    "\n",
    "# Read datasets and create dataframes\n",
    "friendship_old_df = pd.read_csv(FOURSQUARE_FRIENDSHIP_OLD_DATASET, sep='\\t', header=None)\n",
    "friendship_new_df = pd.read_csv(FOURSQUARE_FRIENDSHIP_NEW_DATASET, sep='\\t', header=None)\n",
    "checkin_df = pd.read_csv(FOURSQUARE_CHECKIN_DATASET, sep='\\t', header=None)\n",
    "venue_df = pd.read_csv(FOURSQUARE_VENUE_DATASET, sep='\\t', header=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "### 1.3 Rename and visualise dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename data columns\n",
    "friendship_old_df.columns = ['user_id', 'friendship_id']\n",
    "friendship_new_df.columns = ['user_id', 'friendship_id']\n",
    "checkin_df.columns = ['user_id', 'venue_id', 'checkin_time', 'offset']\n",
    "venue_df.columns = ['venue_id', 'latitude', 'longitude', 'category', 'country_code']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We visualise the friendship network dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>friendship_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>57908</th>\n",
       "      <td>49184</td>\n",
       "      <td>856881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26016</th>\n",
       "      <td>16617</td>\n",
       "      <td>993796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291441</th>\n",
       "      <td>799332</td>\n",
       "      <td>1839493</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        user_id  friendship_id\n",
       "57908     49184         856881\n",
       "26016     16617         993796\n",
       "291441   799332        1839493"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visualise dataframe sample\n",
    "friendship_old_df.sample(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>friendship_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>163614</th>\n",
       "      <td>158202</td>\n",
       "      <td>940170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8640</th>\n",
       "      <td>5712</td>\n",
       "      <td>1350060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>555086</th>\n",
       "      <td>1248052</td>\n",
       "      <td>1368464</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        user_id  friendship_id\n",
       "163614   158202         940170\n",
       "8640       5712        1350060\n",
       "555086  1248052        1368464"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visualise dataframe sample\n",
    "friendship_new_df.sample(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We visualise the anonymised check-in dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>venue_id</th>\n",
       "      <th>checkin_time</th>\n",
       "      <th>offset</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12715912</th>\n",
       "      <td>788345</td>\n",
       "      <td>5066b2bde4b0f646451faa9e</td>\n",
       "      <td>Sat Dec 15 18:25:15 +0000 2012</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19327244</th>\n",
       "      <td>533669</td>\n",
       "      <td>4fbe5346e4b057d1bdac03df</td>\n",
       "      <td>Tue May 14 14:23:18 +0000 2013</td>\n",
       "      <td>-240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14156762</th>\n",
       "      <td>865631</td>\n",
       "      <td>4bf031f9d4f70f474f97390f</td>\n",
       "      <td>Wed Jan 16 11:04:07 +0000 2013</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          user_id                  venue_id                    checkin_time  \\\n",
       "12715912   788345  5066b2bde4b0f646451faa9e  Sat Dec 15 18:25:15 +0000 2012   \n",
       "19327244   533669  4fbe5346e4b057d1bdac03df  Tue May 14 14:23:18 +0000 2013   \n",
       "14156762   865631  4bf031f9d4f70f474f97390f  Wed Jan 16 11:04:07 +0000 2013   \n",
       "\n",
       "          offset  \n",
       "12715912     120  \n",
       "19327244    -240  \n",
       "14156762     120  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visualise dataframe sample\n",
    "checkin_df.sample(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We visualise the venue information dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>venue_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>category</th>\n",
       "      <th>country_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8274718</th>\n",
       "      <td>505f7263e4b0be434f8b39c8</td>\n",
       "      <td>38.310618</td>\n",
       "      <td>20.556750</td>\n",
       "      <td>Assisted Living</td>\n",
       "      <td>GR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6621974</th>\n",
       "      <td>4f94c58de4b04c0b8df7ee64</td>\n",
       "      <td>-6.603305</td>\n",
       "      <td>106.776544</td>\n",
       "      <td>Home (private)</td>\n",
       "      <td>ID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7868926</th>\n",
       "      <td>5025df38e4b0aa1674cddd07</td>\n",
       "      <td>51.836607</td>\n",
       "      <td>107.583874</td>\n",
       "      <td>Hostel</td>\n",
       "      <td>RU</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         venue_id   latitude   longitude         category  \\\n",
       "8274718  505f7263e4b0be434f8b39c8  38.310618   20.556750  Assisted Living   \n",
       "6621974  4f94c58de4b04c0b8df7ee64  -6.603305  106.776544   Home (private)   \n",
       "7868926  5025df38e4b0aa1674cddd07  51.836607  107.583874           Hostel   \n",
       "\n",
       "        country_code  \n",
       "8274718           GR  \n",
       "6621974           ID  \n",
       "7868926           RU  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visualise dataframe sample\n",
    "venue_df.sample(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Step 2: Friendship network preprocessing\n",
    "\n",
    "---\n",
    "\n",
    "### 2.1 Remove unwanted users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we remove users with under 70 checkins, about a third of the mean (199 checkins). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The average number of checkins per user is 199.\n",
      "The minimum number of checkins per user is 73.\n"
     ]
    }
   ],
   "source": [
    "# Compute average and minimum checkin values\n",
    "mean_num_checkins = checkin_df['user_id'].value_counts().mean()\n",
    "min_num_checkins  = checkin_df['user_id'].value_counts().min()\n",
    "\n",
    "# Print results\n",
    "print('The average number of checkins per user is %d.' % mean_num_checkins)\n",
    "print('The minimum number of checkins per user is %d.' % min_num_checkins)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Users removed: 0\n",
      "Checkins removed: 0\n"
     ]
    }
   ],
   "source": [
    "# Global variable for minimum checkin number\n",
    "MIN_CHECKINS = 70\n",
    "\n",
    "# For print\n",
    "size_user0 = len(checkin_df.user_id.unique())\n",
    "size_checkins0 = checkin_df.shape[0]\n",
    "\n",
    "# Count number of checkins per user and remove users with under 70 checkins\n",
    "num_checkins = checkin_df['user_id'].value_counts()\n",
    "indexes_to_remove = num_checkins[num_checkins < MIN_CHECKINS].index\n",
    "checkin_df = checkin_df[~checkin_df['user_id'].isin(indexes_to_remove)]\n",
    "\n",
    "# For print\n",
    "size_user1 = len(checkin_df.user_id.unique())\n",
    "size_checkins1 = checkin_df.shape[0]\n",
    "\n",
    "# Print\n",
    "print('Users removed: %d' % (size_user1-size_user0))\n",
    "print('Checkins removed: %d' % (size_checkins1-size_checkins0))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We also want to remove users that are not in both the `dataset_WWW_friendship_old.txt` and `dataset_WWW_friendship_new.txt` datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Users removed (old): 2356\n",
      "Users removed (new): 15824\n",
      "Users removed (total): 18180\n"
     ]
    }
   ],
   "source": [
    "# Get users not in both dataframes\n",
    "user_id_old_array = friendship_old_df.user_id.unique()\n",
    "user_id_new_array = friendship_new_df.user_id.unique()\n",
    "users_to_remove = np.setxor1d(user_id_old_array, user_id_new_array)\n",
    "\n",
    "# For print\n",
    "size_old_user0 = len(user_id_old_array)\n",
    "size_new_user0 = len(user_id_new_array)\n",
    "\n",
    "# Remove unwanted users\n",
    "friendship_old_df = friendship_old_df[~friendship_old_df.user_id.isin(users_to_remove)]\n",
    "friendship_new_df = friendship_new_df[~friendship_new_df.user_id.isin(users_to_remove)]\n",
    "checkin_df = checkin_df[~checkin_df.user_id.isin(users_to_remove)]\n",
    "\n",
    "# For print\n",
    "size_old_user1 = len(friendship_old_df.user_id.unique())\n",
    "size_new_user1 = len(friendship_new_df.user_id.unique())\n",
    "\n",
    "# Print\n",
    "print('Users removed (old): %d' % (size_old_user0-size_old_user1))\n",
    "print('Users removed (new): %d' % (size_new_user0-size_new_user1))\n",
    "print('Users removed (total): %d' % len(users_to_remove))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "### 2.1 Number of friends before and after\n",
    "\n",
    "We must first create a new dataframe with friendship information for each user before and after the 22 month period, as well as the number of friendships that user makes or breaks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get number of friends before and after 22 month period\n",
    "num_before = friendship_old_df.groupby('user_id').count().reset_index()\n",
    "num_after  = friendship_new_df.groupby('user_id').count().reset_index()\n",
    "\n",
    "# Merge dataframes on user_id to have friends before and after in same dataframe\n",
    "friendship_df = num_before.merge(num_after, on='user_id', suffixes=('_before', '_after'), how='inner')\n",
    "\n",
    "# Rename columns\n",
    "friendship_df.columns = ['user_id', 'num_friends_before', 'num_friends_after']\n",
    "\n",
    "# Create column with gained friendships\n",
    "friendship_df['num_friends_gained'] = friendship_df['num_friends_after'] - friendship_df['num_friends_before']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We visualise the friendship information dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>num_friends_before</th>\n",
       "      <th>num_friends_after</th>\n",
       "      <th>num_friends_gained</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>60457</th>\n",
       "      <td>1183799</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15530</th>\n",
       "      <td>210588</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32869</th>\n",
       "      <td>509773</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       user_id  num_friends_before  num_friends_after  num_friends_gained\n",
       "60457  1183799                   3                  2                  -1\n",
       "15530   210588                   2                  6                   4\n",
       "32869   509773                   2                  2                   0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visualise dataframe sample\n",
    "friendship_df.sample(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Step 3: Checkin information preprocessing\n",
    "\n",
    "---\n",
    "\n",
    "### 3.1 Datetime handling\n",
    "\n",
    "Our dataset contains the check-in time for each check-in in the following format in two columns: \n",
    "* **checkin_time:** %a %b %d %H:%M:%S %z %Y (eg. Tue Apr 03 18:00:07 +0000 2012)\n",
    "* **offset:** %M (eg. 180)\n",
    "\n",
    "*(With %a the day, %b the month, %d the date, %H the hour, %M the minute, %S the secind, %Z the zero hour offset and %Y the year)*\n",
    "\n",
    "We want to remove the offset column and consider it in the check-in time column to get the following format in a single column:\n",
    "* **checkin_time:** %Y-%m-%d %H:%M:%S (eg. 2012-04-03 21:00:07)\n",
    "\n",
    "We first notice that one row has a different and unreadable format as shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>venue_id</th>\n",
       "      <th>checkin_time</th>\n",
       "      <th>offset</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17720249</th>\n",
       "      <td>219528</td>\n",
       "      <td>4dc98660d4c0abe9b6320d4f</td>\n",
       "      <td>Mon21239673244639234</td>\n",
       "      <td>-180</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          user_id                  venue_id          checkin_time  offset\n",
       "17720249   219528  4dc98660d4c0abe9b6320d4f  Mon21239673244639234    -180"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display error row\n",
    "checkin_df[checkin_df.checkin_time == 'Mon21239673244639234']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We remove this row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove error row\n",
    "checkin_df = checkin_df.drop(index=checkin_df[checkin_df.checkin_time == 'Mon21239673244639234'].index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we modify the check-in time format and add the offset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert to datetime format\n",
    "checkin_df['checkin_time'] = pd.to_datetime(checkin_df['checkin_time'], format='%a %b %d %H:%M:%S %z %Y')\n",
    "\n",
    "# Apply offset\n",
    "checkin_df['checkin_time'] = checkin_df.apply(lambda x: x.checkin_time + timedelta(minutes=x.offset), axis=1)\n",
    "\n",
    "# Remove time zone from check-in time and remove offset column\n",
    "checkin_df['checkin_time'] = checkin_df['checkin_time'].apply(lambda x: x.replace(tzinfo=None))\n",
    "checkin_df = checkin_df.drop('offset', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We visualise the new check-in time dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>venue_id</th>\n",
       "      <th>checkin_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18340770</th>\n",
       "      <td>38935</td>\n",
       "      <td>4bf15b2870779521fc883e7c</td>\n",
       "      <td>2013-04-22 04:51:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9175291</th>\n",
       "      <td>1463673</td>\n",
       "      <td>4b4bc8c7f964a52091a726e3</td>\n",
       "      <td>2012-09-06 11:55:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2259503</th>\n",
       "      <td>1692228</td>\n",
       "      <td>4b5fa00af964a52048c529e3</td>\n",
       "      <td>2012-04-29 15:40:46</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          user_id                  venue_id        checkin_time\n",
       "18340770    38935  4bf15b2870779521fc883e7c 2013-04-22 04:51:45\n",
       "9175291   1463673  4b4bc8c7f964a52091a726e3 2012-09-06 11:55:52\n",
       "2259503   1692228  4b5fa00af964a52048c529e3 2012-04-29 15:40:46"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visualise dataframe sample\n",
    "checkin_df.sample(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Step 4: Venue information preprocessing\n",
    "\n",
    "---\n",
    "\n",
    "### 4.1 Foursquare API\n",
    "\n",
    "We have created a foursquare developper account to have acces to the API. Below is our client ID and secret.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Foursquare ID\n",
    "CLIENT_ID = '5P0XGYOSTA5D40V4DBIXKOYDPGGYUEC0AQ1RRK22HUXSSKXS'\n",
    "\n",
    "# Foursquare Secret\n",
    "CLIENT_SECRET = 'UYHEXP2ZBWO0JE4V3GUQMXYAC0UQZHOSQ1OUSED3Z3EZD1PL'\n",
    "\n",
    "# Foursquare Vesrsion\n",
    "VERSION = '20140101'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From our identifications we can request a formatted JSON response from Foursquare giving us information on categories in Foursquare, including their hierarchy and other details."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# URL for json request\n",
    "url = 'https://api.foursquare.com/v2/venues/categories?&client_id={}&client_secret={}&v={}'.format(\n",
    "    CLIENT_ID, \n",
    "    CLIENT_SECRET, \n",
    "    VERSION)\n",
    "\n",
    "# Formatted JSON response\n",
    "response = requests.get(url).json()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The 'response' object is a dictionary containing all of the hierarchy, naming, opening hours and more details."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "### 4.1 Foursquare Categories\n",
    "\n",
    "From the response we will mainly be using the hierarchy of categories to be able to group them. Categories have 5 hierarchy levels. Here is an example:\n",
    "\n",
    "* Level 0: Food\n",
    "    * Level 1: African Restaurant\n",
    "        * Level 2: Ethiopian Restaurant <br />\n",
    "        ...\n",
    "        \n",
    "        <br />\n",
    "    * Level 1: Asian Restaurant\n",
    "        * Level 2: Chinese Restaurant\n",
    "            * Level 3: Anhui Restaurant <br />\n",
    "        ...\n",
    "        \n",
    "        <br />\n",
    "            \n",
    "    * Level 1: Latin American Restaurant\n",
    "        * Level 2: South American Restaurant\n",
    "            * Level 3: Brazilian Restaurant\n",
    "                * Level 4: Acai House <br />\n",
    "        ...\n",
    "        \n",
    "  ...\n",
    "  <br />\n",
    "* Level 0: Nightlife Spot\n",
    "    * Level 1: Bar\n",
    "        * Level 2: Beer Bar <br />\n",
    "        ...\n",
    "         \n",
    "\n",
    "First let us see how many categories we have in the 'venue_df' dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of unique categories in dataframe: 519\n"
     ]
    }
   ],
   "source": [
    "# Get unique categories\n",
    "checkin_categories = venue_df.category.unique()\n",
    "\n",
    "# Print\n",
    "print('Number of unique categories in dataframe: %d' % len(checkin_categories))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After analysis, we can see that there are some categories that cannot be found in the JSON response. However, most of these are either slight changes in formulation ('place' instead of 'restaurant') and some have character errors ('�' instead of 'é'). We correct all of these errors, and if the category can really not be found in the response, we add it to a \"Other\" category."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove exception category names from df\n",
    "venue_df.category = venue_df.category.replace({'Drugstore / Pharmacy':'Drugstore', \n",
    "                                               'Caf��':'Café',\n",
    "                                               'Caf�':'Café',\n",
    "                                               'Spa / Massage':'Spa',\n",
    "                                               'Athletic & Sport':'Athletics & Sports',\n",
    "                                               'Ramen /  Noodle House':'Ramen',\n",
    "                                               'Ramen / Noodle House':'Ramen',\n",
    "                                               'Subway':'Metro Station',\n",
    "                                               'Hiking Trail':'Trail',\n",
    "                                               'Car Dealership':'Auto Dealership',\n",
    "                                               'Stable':'Stables',\n",
    "                                               'Ferry':'Boat or Ferry',\n",
    "                                               'Malaysian Restaurant':'Asian Restaurant',\n",
    "                                               'Martial Arts Dojo':'Martial Arts School',\n",
    "                                               'Laboratory':'Research Laboratory',\n",
    "                                               'Frozen Yogurt':'Yogurt',\n",
    "                                               'Yogurts':'Yogurt',\n",
    "                                               'Meatball Place':'Italian Restaurant',\n",
    "                                               'Home Cooking Restaurant':'Other',\n",
    "                                               'Gas Station / Garage':'Gas Station',\n",
    "                                               'Kokore? Restaurant': 'Kokoreç Restaurant',\n",
    "                                               'Kokoretsi Restaurant': 'Kokoreç Restaurant',\n",
    "                                               'Kofte Restaurant': 'Kofte Place',\n",
    "                                               })\n",
    "\n",
    "# Get unique categories\n",
    "checkin_categories = venue_df.category.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now want to look through the JSON response to add the category level 0 and level 1 to the venue information dataframe. This will allow us for better grouping."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Empty arrays to append\n",
    "level_0_category = []\n",
    "level_1_category = []\n",
    "\n",
    "# Flag if category level 1 is found\n",
    "flag = True\n",
    "\n",
    "# Level 0 category list\n",
    "categories_json_0 = response.get('response').get('categories')\n",
    "\n",
    "# Iterate over check-in categories in dataframe to find hierarchy level 1 and level 0\n",
    "for c in checkin_categories:\n",
    "    flag = False\n",
    "\n",
    "    # Tier 0 \n",
    "    for i in range(len(categories_json_0)):\n",
    "        if flag:\n",
    "            break            \n",
    "        name0 = categories_json_0[i].get('name')\n",
    "        name = name0\n",
    "        plural_name = categories_json_0[i].get('pluralName')\n",
    "        short_name = categories_json_0[i].get('shortName')\n",
    "        if (c == name) | (c == plural_name) | (c == short_name):\n",
    "            level_0_category.append(name0)\n",
    "            # If category is already level 0, level 1 takes level 0 name\n",
    "            level_1_category.append(name0)\n",
    "            flag = True\n",
    "            break        \n",
    "        categories_json_1 = categories_json_0[i].get('categories')\n",
    "\n",
    "        # Tier 1\n",
    "        for j in range(len(categories_json_1)):\n",
    "            if flag:\n",
    "                break    \n",
    "            name1 = categories_json_1[j].get('name')\n",
    "            name = name1\n",
    "            plural_name = categories_json_1[j].get('pluralName')\n",
    "            short_name = categories_json_1[j].get('shortName')\n",
    "            if (c == name) | (c == plural_name) | (c == short_name):\n",
    "                level_0_category.append(name0)\n",
    "                level_1_category.append(name1)\n",
    "                flag = True\n",
    "                break           \n",
    "            categories_json_2 = categories_json_1[j].get('categories')\n",
    "\n",
    "            # Tier 2\n",
    "            for k in range(len(categories_json_2)):\n",
    "                if flag:\n",
    "                    break\n",
    "                name = categories_json_2[k].get('name')\n",
    "                plural_name = categories_json_2[k].get('pluralName')\n",
    "                short_name = categories_json_2[k].get('shortName')\n",
    "                if (c == name) | (c == plural_name) | (c == short_name):\n",
    "                    level_0_category.append(name0)\n",
    "                    level_1_category.append(name1)\n",
    "                    flag = True\n",
    "                    break                \n",
    "                categories_json_3 = categories_json_2[k].get('categories')\n",
    "\n",
    "                # Tier 3\n",
    "                for l in range(len(categories_json_3)):\n",
    "                    if flag:\n",
    "                        break\n",
    "                    name = categories_json_3[l].get('name')\n",
    "                    plural_name = categories_json_3[l].get('pluralName')\n",
    "                    short_name = categories_json_3[l].get('shortName')\n",
    "                    if (c == name) | (c == plural_name) | (c == short_name):\n",
    "                        level_0_category.append(name0)\n",
    "                        level_1_category.append(name1)\n",
    "                        flag = True\n",
    "                        break\n",
    "                    categories_json_4 = categories_json_3[l].get('categories')\n",
    "\n",
    "                    # Tier 4\n",
    "                    for m in range(len(categories_json_4)):\n",
    "                        if flag:\n",
    "                            break \n",
    "                        name = categories_json_4[m].get('name')\n",
    "                        plural_name = categories_json_4[m].get('pluralName')\n",
    "                        short_name = categories_json_4[m].get('shortName')\n",
    "                        if (c == name) | (c == plural_name) | (c == short_name):\n",
    "                            level_0_category.append(name0)\n",
    "                            level_1_category.append(name1)\n",
    "                            flag = True\n",
    "                            break\n",
    "                            \n",
    "    # Return category name if not found in Foursquare API response\n",
    "    if not flag:\n",
    "        level_0_category.append(c)\n",
    "        level_1_category.append(c)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create a new datframe showing each category, its level 0 and its level 1 category."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create dataframe from arrays\n",
    "category_df = pd.DataFrame({'category':checkin_categories, \n",
    "                            'level_0_category':level_0_category, \n",
    "                            'level_1_category':level_1_category})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We visualise the category dataferame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category</th>\n",
       "      <th>level_0_category</th>\n",
       "      <th>level_1_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>Airport</td>\n",
       "      <td>Travel &amp; Transport</td>\n",
       "      <td>Airport</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>347</th>\n",
       "      <td>Water Park</td>\n",
       "      <td>Arts &amp; Entertainment</td>\n",
       "      <td>Water Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>341</th>\n",
       "      <td>Bed &amp; Breakfast</td>\n",
       "      <td>Travel &amp; Transport</td>\n",
       "      <td>Hotel</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            category      level_0_category level_1_category\n",
       "188          Airport    Travel & Transport          Airport\n",
       "347       Water Park  Arts & Entertainment       Water Park\n",
       "341  Bed & Breakfast    Travel & Transport            Hotel"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visualise sample of dataframe\n",
    "category_df.sample(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the Foursquare API we know that we have 10 level 0 categories. We have also added the \"Other\" category, so we should have a total of 11 level 0 categories."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of unique level 0 categories: 11\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array(['Professional & Other Places', 'Arts & Entertainment',\n",
       "       'College & University', 'Food', 'Nightlife Spot', 'Shop & Service',\n",
       "       'Travel & Transport', 'Outdoors & Recreation', 'Residence',\n",
       "       'Event', 'Other'], dtype=object)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Print number of level 0 categories\n",
    "print('Number of unique level 0 categories: %d' % len(category_df.level_0_category.unique()))\n",
    "\n",
    "# Display level 0 categories\n",
    "category_df.level_0_category.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we merge the venue information dataframe with the hierarchy category level dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge dataframes\n",
    "venue_df = pd.merge(venue_df, category_df, on='category')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We visualise venue information dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>venue_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>category</th>\n",
       "      <th>country_code</th>\n",
       "      <th>level_0_category</th>\n",
       "      <th>level_1_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7092355</th>\n",
       "      <td>50fbcac8e4b09f8ff685473b</td>\n",
       "      <td>-6.881669</td>\n",
       "      <td>107.615613</td>\n",
       "      <td>Home (private)</td>\n",
       "      <td>ID</td>\n",
       "      <td>Residence</td>\n",
       "      <td>Home (private)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6835229</th>\n",
       "      <td>50267cfae4b04e431bbc170a</td>\n",
       "      <td>10.294478</td>\n",
       "      <td>123.880195</td>\n",
       "      <td>Home (private)</td>\n",
       "      <td>PH</td>\n",
       "      <td>Residence</td>\n",
       "      <td>Home (private)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9670452</th>\n",
       "      <td>4ce0ef7441ed224b54a2e73c</td>\n",
       "      <td>39.477303</td>\n",
       "      <td>-0.442483</td>\n",
       "      <td>Automotive Shop</td>\n",
       "      <td>ES</td>\n",
       "      <td>Shop &amp; Service</td>\n",
       "      <td>Automotive Shop</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         venue_id   latitude   longitude         category  \\\n",
       "7092355  50fbcac8e4b09f8ff685473b  -6.881669  107.615613   Home (private)   \n",
       "6835229  50267cfae4b04e431bbc170a  10.294478  123.880195   Home (private)   \n",
       "9670452  4ce0ef7441ed224b54a2e73c  39.477303   -0.442483  Automotive Shop   \n",
       "\n",
       "        country_code level_0_category level_1_category  \n",
       "7092355           ID        Residence   Home (private)  \n",
       "6835229           PH        Residence   Home (private)  \n",
       "9670452           ES   Shop & Service  Automotive Shop  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visualise dataframe sample \n",
    "venue_df.sample(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Step 5: Exporting the data\n",
    "\n",
    "In this finals step, we export the preprocessed dataframes as `.csv` files for analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Friendship information dataframe\n",
    "friendship_df.to_csv('friendship_df.csv.zip',\n",
    "                     index=False,\n",
    "                     compression=dict(method='zip', archive_name='friendship_df.csv'))\n",
    "\n",
    "# Friendship information dataframe\n",
    "checkin_df.to_csv('checkin_df.csv.zip',\n",
    "                  index=False,\n",
    "                  compression=dict(method='zip', archive_name='checkin_df.csv'))\n",
    "\n",
    "# Venue information dataframe\n",
    "venue_df.to_csv('venue_df.csv.zip',\n",
    "          index=False,\n",
    "          compression=dict(method='zip', archive_name='venue_df.csv'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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"
  },
  "pycharm": {
   "stem_cell": {
    "cell_type": "raw",
    "metadata": {
     "collapsed": false
    },
    "source": []
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}