prac09_web_mining.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Practical 9: Web Mining\n",
    "\n",
    "### In this practical\n",
    "1. [Loading data](#load)\n",
    "2. [Cleaning web logs](#prep)\n",
    "3. [Applying data mining methods to cleaned data](#application)\n",
    "\n",
    "---\n",
    "**Written by Hendi Lie (h2.lie@qut.edu.au) and Richi Nayak (r.nayak@qut.edu.au). All rights reserved.**\n",
    "\n",
    "This practical note introduces you to the web data processing for performing mining in Python. The input file for this exercise is `datasets/wdata.txt` that contains web log data in text format. You will learn to clean and perform user session identification in preparation of applying one of the data mining techniques you have learned so far in the previous practicals.\n",
    "\n",
    "## 1. Loading Data\n",
    "\n",
    "Web mining is a branch of data mining that concentrates on mining useful information from the web. The significant tasks of this web mining include resource finding, information selection, preprocessing, generalisation and analysis.\n",
    "\n",
    "There are many types of web mining, including web usage mining, web structure mining and web content mining. Web usage mining, in particular, have allowed organisation to analyse user usage patterns, resulting in great insights for organisations to improve site design, identify potential customers and improve search results.\n",
    "\n",
    "In general, log analysis is include in web usage mining process. It takes raw web data and process them in order to extract statistical information, such as:\n",
    "* Key statistical figures (number of visitors, average number of hits, view time, etc)\n",
    "* Diagnostic statistics (server reports and page not found errors)\n",
    "* Server statistics (top pages visited, entry/exit pages)\n",
    "* Referrer statistics (top referrering sites, search engine, key words)\n",
    "* User demographics, client statistics and so on.\n",
    "\n",
    "Web usage mining commonly uses web log data, which contain raw information related to pages served and recorded by the web server. This raw information is not sufficient and is not accurate to infer the behavior of the user. Thus, we need to perform preprocessing to extract meaningful information.\n",
    "\n",
    "In this practical, we will be using `wdata.txt` log dataset. Load them using files (not pandas) as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "web_logs\n",
      "\n",
      "j2439.inktomisearch.com - - [18/Apr/2005:21:16:54 +1000] \"GET /robots.txt HTTP/1.0\" 404 204 \"-\" \"Mozilla/5.0 (compatible; Yahoo! Slurp; http://help.yahoo.com/help/us/ysearch/slurp)\"\n",
      "\n",
      "lj2559.inktomisearch.com - - [18/Apr/2005:21:16:55 +1000] \"GET /code/Global/code/menu.html HTTP/1.0\" 200 6092 \"-\" \"Mozilla/5.0 (compatible; Yahoo! Slurp; http://help.yahoo.com/help/us/ysearch/slurp)\"\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# load logs from wdata\n",
    "wdata = open('datasets/wdata.txt', 'r').readlines()\n",
    "\n",
    "# print the first 3 lines\n",
    "print('\\n'.join(wdata[:3]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the first couple of lines from this file, we could see the structure of this dataset. Its contents are:\n",
    "\n",
    "1. **Host**:  The first part of the web log is called host. This is either ip address (202.183.101.13) or  the  host  name (lj2439.inktomisearch.com)  of  the  remote  user  requesting  the  page. For  performance  reasons, many  web  servers  are  configured  to  publish  their  IP  address instead of host name, but in this dataset, the host contains host names.\n",
    "2. **Identd result**: The  dash  (-)  next  to  the  host  name  represents  the  logging  response  returned  by  the  remote  user’s  Identd  result. Almost no  web servers uses this; in most web log this field is always just a dash (-).    \n",
    "3. **Authuser**: The next part of the log displays the authentication code of the user if there exists any for that particular web site or else just dash (-) is displayed. \n",
    "4. **Date and time**: Next to come in the   row   is   the   date   and   time   inside   the   square   brackets [18/Apr/2005:21:16:54  +1000].  It is in the day/month/year  format.  The  time  is  followed  by the date is displayed in 24 hours format with time zone offset at the end. The time-zone offset corresponds to Universal Time/Greenwich Mean Time.\n",
    "5. **Request**: This  is  the  request  sent  by  the  user  enclosed  in  double  quotes.  Normally  it  looks  something  like  “GET  /robots.txt  HTTP/1.0\".  In  this  part  the  **GET**  represents  request method, **/robots.txt** is the path of requested web page and **HTTP/1.0** being the request protocol.\n",
    "6. **Status code**: This  is  a  3-digit  code  returned  by  the  server  indicating  the  status  of  the  request  to  server. For example the code 200 stands for successful completion and 404 stands for unsuccessful completion or if the page could not be found.\n",
    "7. **Bytes sent**: This represents the amount of the data delivered from the server excluding the header line.  \n",
    " \n",
    "The extended version of this log format is called combined log format, with addition of two more fields.\n",
    "8. **Agent**: The  user  agent  reported  by  the  requesting  user's  browser.  Typically,  this  is  a  string  describing the type and version of browser software being used.\n",
    "9. **Referrer**: This is the referencing page of the user, in this case the referring URL is: http://help.yahoo.com/help/us/ysearch/slurp.  \n",
    "\n",
    "Once we know the columns available in this dataset, you could reload the dataset using the `.read_csv` function. Each field is separated by spaces, thus we should specify its `sep` or separator as ' ' or space. We have also added `names` variable into `names` parameter of the read function to allow pandas set column names during read process."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# set names of pandas dataframe\n",
    "names=['Host', 'Identd', 'Authuser', 'Date and time', 'Timezone', 'Request',\n",
    "       'Status code', 'Bytes Sent', 'Referrer', 'Agent']\n",
    "# read the dataframe\n",
    "df = pd.read_csv('datasets/wdata.txt', sep=' ', names=names, header=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Host</th>\n",
       "      <th>Identd</th>\n",
       "      <th>Authuser</th>\n",
       "      <th>Date and time</th>\n",
       "      <th>Timezone</th>\n",
       "      <th>Request</th>\n",
       "      <th>Status code</th>\n",
       "      <th>Bytes Sent</th>\n",
       "      <th>Referrer</th>\n",
       "      <th>Agent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>web_logs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>j2439.inktomisearch.com</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[18/Apr/2005:21:16:54</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>GET /robots.txt HTTP/1.0</td>\n",
       "      <td>404.0</td>\n",
       "      <td>204</td>\n",
       "      <td>-</td>\n",
       "      <td>Mozilla/5.0 (compatible; Yahoo! Slurp; http://...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>lj2559.inktomisearch.com</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[18/Apr/2005:21:16:55</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>GET /code/Global/code/menu.html HTTP/1.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>6092</td>\n",
       "      <td>-</td>\n",
       "      <td>Mozilla/5.0 (compatible; Yahoo! Slurp; http://...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>c210-49-32-6.rochd2.qld.optusnet.com.au</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[18/Apr/2005:21:25:07</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>GET / HTTP/1.1</td>\n",
       "      <td>200.0</td>\n",
       "      <td>7138</td>\n",
       "      <td>http://www.google.com.au/search?hl=en&amp;q=snap+p...</td>\n",
       "      <td>Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>c210-49-32-6.rochd2.qld.optusnet.com.au</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[18/Apr/2005:21:25:07</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>GET /images/index3_01.gif HTTP/1.1</td>\n",
       "      <td>200.0</td>\n",
       "      <td>382</td>\n",
       "      <td>http://www.copyspecialists.com.au/</td>\n",
       "      <td>Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Host Identd Authuser  \\\n",
       "0                                 web_logs    NaN      NaN   \n",
       "1                  j2439.inktomisearch.com      -        -   \n",
       "2                 lj2559.inktomisearch.com      -        -   \n",
       "3  c210-49-32-6.rochd2.qld.optusnet.com.au      -        -   \n",
       "4  c210-49-32-6.rochd2.qld.optusnet.com.au      -        -   \n",
       "\n",
       "           Date and time Timezone                                   Request  \\\n",
       "0                    NaN      NaN                                       NaN   \n",
       "1  [18/Apr/2005:21:16:54   +1000]                  GET /robots.txt HTTP/1.0   \n",
       "2  [18/Apr/2005:21:16:55   +1000]  GET /code/Global/code/menu.html HTTP/1.0   \n",
       "3  [18/Apr/2005:21:25:07   +1000]                            GET / HTTP/1.1   \n",
       "4  [18/Apr/2005:21:25:07   +1000]        GET /images/index3_01.gif HTTP/1.1   \n",
       "\n",
       "   Status code Bytes Sent                                           Referrer  \\\n",
       "0          NaN        NaN                                                NaN   \n",
       "1        404.0        204                                                  -   \n",
       "2        200.0       6092                                                  -   \n",
       "3        200.0       7138  http://www.google.com.au/search?hl=en&q=snap+p...   \n",
       "4        200.0        382                 http://www.copyspecialists.com.au/   \n",
       "\n",
       "                                               Agent  \n",
       "0                                                NaN  \n",
       "1  Mozilla/5.0 (compatible; Yahoo! Slurp; http://...  \n",
       "2  Mozilla/5.0 (compatible; Yahoo! Slurp; http://...  \n",
       "3  Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...  \n",
       "4  Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# preview\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first line/row of this data is not part of the web logs, thus we should drop it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.drop(0, inplace=True)  # drop the row with index 0, on axis 0 (row-wise)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the `Request` column still has both HTTP request method and protocol in it. The code below will separate these information from the column into their respective columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Host</th>\n",
       "      <th>Identd</th>\n",
       "      <th>Authuser</th>\n",
       "      <th>Date and time</th>\n",
       "      <th>Timezone</th>\n",
       "      <th>Request</th>\n",
       "      <th>Status code</th>\n",
       "      <th>Bytes Sent</th>\n",
       "      <th>Referrer</th>\n",
       "      <th>Agent</th>\n",
       "      <th>Method</th>\n",
       "      <th>Protocol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>j2439.inktomisearch.com</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[18/Apr/2005:21:16:54</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>/robots.txt</td>\n",
       "      <td>404.0</td>\n",
       "      <td>204</td>\n",
       "      <td>-</td>\n",
       "      <td>Mozilla/5.0 (compatible; Yahoo! Slurp; http://...</td>\n",
       "      <td>GET</td>\n",
       "      <td>HTTP/1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>lj2559.inktomisearch.com</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[18/Apr/2005:21:16:55</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>/code/Global/code/menu.html</td>\n",
       "      <td>200.0</td>\n",
       "      <td>6092</td>\n",
       "      <td>-</td>\n",
       "      <td>Mozilla/5.0 (compatible; Yahoo! Slurp; http://...</td>\n",
       "      <td>GET</td>\n",
       "      <td>HTTP/1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>c210-49-32-6.rochd2.qld.optusnet.com.au</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[18/Apr/2005:21:25:07</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>/</td>\n",
       "      <td>200.0</td>\n",
       "      <td>7138</td>\n",
       "      <td>http://www.google.com.au/search?hl=en&amp;q=snap+p...</td>\n",
       "      <td>Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...</td>\n",
       "      <td>GET</td>\n",
       "      <td>HTTP/1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>c210-49-32-6.rochd2.qld.optusnet.com.au</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[18/Apr/2005:21:25:07</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>/images/index3_01.gif</td>\n",
       "      <td>200.0</td>\n",
       "      <td>382</td>\n",
       "      <td>http://www.copyspecialists.com.au/</td>\n",
       "      <td>Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...</td>\n",
       "      <td>GET</td>\n",
       "      <td>HTTP/1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>c210-49-32-6.rochd2.qld.optusnet.com.au</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[18/Apr/2005:21:25:07</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>/images/index3_02.gif</td>\n",
       "      <td>200.0</td>\n",
       "      <td>1284</td>\n",
       "      <td>http://www.copyspecialists.com.au/</td>\n",
       "      <td>Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...</td>\n",
       "      <td>GET</td>\n",
       "      <td>HTTP/1.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      Host Identd Authuser  \\\n",
       "1                  j2439.inktomisearch.com      -        -   \n",
       "2                 lj2559.inktomisearch.com      -        -   \n",
       "3  c210-49-32-6.rochd2.qld.optusnet.com.au      -        -   \n",
       "4  c210-49-32-6.rochd2.qld.optusnet.com.au      -        -   \n",
       "5  c210-49-32-6.rochd2.qld.optusnet.com.au      -        -   \n",
       "\n",
       "           Date and time Timezone                      Request  Status code  \\\n",
       "1  [18/Apr/2005:21:16:54   +1000]                  /robots.txt        404.0   \n",
       "2  [18/Apr/2005:21:16:55   +1000]  /code/Global/code/menu.html        200.0   \n",
       "3  [18/Apr/2005:21:25:07   +1000]                            /        200.0   \n",
       "4  [18/Apr/2005:21:25:07   +1000]        /images/index3_01.gif        200.0   \n",
       "5  [18/Apr/2005:21:25:07   +1000]        /images/index3_02.gif        200.0   \n",
       "\n",
       "  Bytes Sent                                           Referrer  \\\n",
       "1        204                                                  -   \n",
       "2       6092                                                  -   \n",
       "3       7138  http://www.google.com.au/search?hl=en&q=snap+p...   \n",
       "4        382                 http://www.copyspecialists.com.au/   \n",
       "5       1284                 http://www.copyspecialists.com.au/   \n",
       "\n",
       "                                               Agent Method  Protocol  \n",
       "1  Mozilla/5.0 (compatible; Yahoo! Slurp; http://...    GET  HTTP/1.0  \n",
       "2  Mozilla/5.0 (compatible; Yahoo! Slurp; http://...    GET  HTTP/1.0  \n",
       "3  Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...    GET  HTTP/1.1  \n",
       "4  Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...    GET  HTTP/1.1  \n",
       "5  Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...    GET  HTTP/1.1  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def extract_method_and_protocol(row):\n",
    "    # function to extract HTTP request method and protocol from a request string\n",
    "    request_splits = row['Request'].split()  # split request string by space\n",
    "    row['Method'] = request_splits[0]\n",
    "    row['Protocol'] = request_splits[-1]\n",
    "    row['Request'] = ' '.join(request_splits[1:-1])  # stitch remaining request string back\n",
    "    return row\n",
    "\n",
    "df = df.apply(extract_method_and_protocol, axis=1)\n",
    "\n",
    "# show the result\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After the loading process is completed, we should explore this dataset. Run the following code cells for exploration."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 51327 entries, 1 to 51327\n",
      "Data columns (total 12 columns):\n",
      "Host             51327 non-null object\n",
      "Identd           51327 non-null object\n",
      "Authuser         51327 non-null object\n",
      "Date and time    51327 non-null object\n",
      "Timezone         51327 non-null object\n",
      "Request          51327 non-null object\n",
      "Status code      51327 non-null float64\n",
      "Bytes Sent       51327 non-null object\n",
      "Referrer         51327 non-null object\n",
      "Agent            51327 non-null object\n",
      "Method           51327 non-null object\n",
      "Protocol         51327 non-null object\n",
      "dtypes: float64(1), object(11)\n",
      "memory usage: 5.1+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are **51327** columns in this dataset, with most columns being object/string type."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Cleaning web logs\n",
    "\n",
    "This section discusses techniques and steps taken to process the imported, raw web log data.\n",
    "\n",
    "### 2.1. Removing useless requests\n",
    "\n",
    "In this  process, we are removing requests relating to non-analysed resources such as extraneous references to \n",
    "embedded objects, graphics, sound files, and removing references due to spider navigations. In addition, we also remove all unsuccessful request (i.e. status code other than 200). A significant reason for cleaning these logs is to reduce storage space and facilitate the upcoming data mining tasks effectively. This list might change when planning for specific analysis. For example, when looking to analyse the performance web cache  application, there is a need for having image and graphic files in the dataset. \n",
    "\n",
    "Use the following code cell to remove image requests from the log such as `.gif`, `.jpg` and `.jpeg` and removing logs with status code other than 200."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# Rows before: 51327\n",
      "After images removal 5866\n",
      "After unsuccessful requests removal 3288\n"
     ]
    }
   ],
   "source": [
    "# correct the incorrect dataframe types\n",
    "df['Status code'] = df['Status code'].astype(int)  # set status code to int\n",
    "df['Datetime'] = pd.to_datetime(df['Date and time'], format='[%d/%b/%Y:%H:%M:%S')  # set date time to pandas datatime obj\n",
    "df = df.drop(['Date and time'], axis=1)\n",
    "\n",
    "# create a mask to filter all images\n",
    "mask = (df['Request'].str.endswith('.gif') | df['Request'].str.endswith('.jpg') | df['Request'].str.endswith('.jpeg'))\n",
    "print(\"# Rows before:\", len(df))\n",
    "\n",
    "# invert the mask, only keep records without .gif, .jpg and .jpeg in the request column\n",
    "df2 = df[~mask]\n",
    "\n",
    "print(\"After images removal\", len(df2))\n",
    "\n",
    "# second mask, remove all unsuccessful requests (code != 200)\n",
    "df2 = df2[df2['Status code'] == 200]\n",
    "print(\"After unsuccessful requests removal\", len(df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After useless requests are removed, we are left with approximately 6.4% of the logs.\n",
    "\n",
    "### 2.2 User Session Identification\n",
    "\n",
    "This step is performed after data cleaning. The goal of user session identification is to divide the page access of each user into individual sessions. This step is non-trivial as it involves many things to take into consideration such as proxy servers, dynamic address, cases where multiple users access the same computer or one user uses multiple browsers and computers. These issues pose a major challenge in identification of sessions. However, there are some techniques that can provide additional information about session identification. The most common ones are cookies, dynamic web pages (with session ID in the URL) and user registrations.\n",
    "\n",
    "One of the most popular techniques for identifying the session is using data from user logins. By combining user logins with the host and user agent it is possible to identify sessions. However it is not always possible to use this technique as not all users use their logins. So for identifying session, this practical uses the **time-out technique** described by Cooley (2002). The average session time-out used by most of the commercial products is 30 min. The session time-out is used in identifying the sessions. In the process of identifying sessions, each log entry is compared to identify if they belong to the same date. In the next step, if the date was same then, the IP address of the user is compared. If IP addresses are same, the time difference is calculated and compared with the average session time out to assign session id. The session id is incremented if the log values don't match. In the output, a session may include one or more requests merely depending on the session time out, IP and date.\n",
    "\n",
    "### 2.3 Path Traversal\n",
    "\n",
    "Path traversal provides an understanding of user access patterns in online environments. This analysis will not only help in improving the system design but also be able to lead to better marketing decisions. The users during their navigation to a Website visit some objects not due to their content but due to their location as they are part of navigation. This feature of traversal patterns increases the difficulty in extracting useful information from the traversal patterns of the users. In this practical, the date and time along with IP address is used to identify the path traversal of the users in their sessions. Once the sessions a recalculated, the requests made by the users in that sessions a reranked based on the time the request is made. The identification of the sessions and path traversal would help in performing associative mining for finding frequent sequential patterns, association and correlation among the sets of items. These associative mining rules are used to identify the correlation between the requests made during a session. These indicate the possible relationship between the pages that are often viewed even if they are not directly connected. Additionally, it can even reveal the association between users with particular interests.\n",
    "\n",
    "### 2.4. User Identification\n",
    "\n",
    "This is the most complex task in preprocessing web logs. The goal of this step is to identify unique users. But in most cases, the log files just provide the IP address of the users. In some cases the registered logins to web sites are found in the logs that aid in identifying the users. Even though user logins are not possible for every site, it is still possible to identify users by using IP address, browser and the rest of the fields. In this lab exercise, sessions are compared to identify the similarities in dates and IP address for identifying users. If the date and IP address are matched, it compares the time and browser. If the time difference is less than an hour and if browser is the same, it will imagine that same user is having two sessions. At any point of time, if the fields don’t match each other, the user id is incremented thinking that it is a different user.\n",
    "\n",
    "Follow the code in cells below to execute all three tasks mentioned above. We provide comments on each line to ensure you understand each steps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from collections import defaultdict\n",
    "import datetime\n",
    "\n",
    "# first, make a copy of df2 just in case\n",
    "df3 = df2.copy()\n",
    "\n",
    "# sort the rows based on datetime, descending\n",
    "df3.sort_values(by='Datetime', inplace=True)\n",
    "\n",
    "# initiate session ID and user ID to 0\n",
    "session_id = 0\n",
    "user_id = 0\n",
    "\n",
    "# create a dictionaries to hold last access information\n",
    "last_access = defaultdict(lambda:datetime.datetime.utcfromtimestamp(0))\n",
    "\n",
    "# dictionary to find previous session, user ID and steps assigned to a specific date/ip/browser key\n",
    "session_dict = defaultdict(lambda:1)\n",
    "user_id_dict = defaultdict(lambda:1)\n",
    "session_steps = defaultdict(lambda:1)\n",
    "\n",
    "# function to be applied row wise\n",
    "# for each row, produce session, user ID and path traversal\n",
    "def get_log_user_info(row):\n",
    "    # access global variables shared between all rows\n",
    "    global session_id, user_id, session_dict, user_id_dict, session_steps, last_access\n",
    "    \n",
    "    session_key = str(row['Datetime'].date()) + '_' + row['Host']  # date + IP key for finding session\n",
    "    user_key = str(row['Datetime'].date()) + '_' + row['Host'] + '_' + row['Agent']  # date + IP + browser key for finding user\n",
    "    time_diff_session = row['Datetime'] - last_access[session_key]  # session time diff\n",
    "    time_diff_user = row['Datetime'] - last_access[user_key]  # user time diff\n",
    "    \n",
    "    # if the time diff from previous session is > 30 mins, assign new session ID\n",
    "    if time_diff_session.total_seconds() > 1800:\n",
    "        session_id += 1\n",
    "        session_dict[session_key] = session_id\n",
    "    \n",
    "    # if the time diff from previous session is > 60 mins, assign new user ID\n",
    "    if time_diff_user.total_seconds() > 3600:\n",
    "        user_id += 1\n",
    "        user_id_dict[user_key] = user_id\n",
    "        \n",
    "    # update last access for session and user\n",
    "    last_access[session_key] = row['Datetime']\n",
    "    last_access[user_key] = row['Datetime']\n",
    "    \n",
    "    # assign extracted info from the row\n",
    "    row['Session'] = session_dict[session_key]\n",
    "    row['Step'] = session_steps[row['Session']]\n",
    "    row['User_ID'] = user_id_dict[user_key]\n",
    "    session_steps[row['Session']] += 1\n",
    "    return row\n",
    "    \n",
    "# apply function above to get a new dataframe with added information\n",
    "df3 = df3.apply(get_log_user_info, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Host</th>\n",
       "      <th>Identd</th>\n",
       "      <th>Authuser</th>\n",
       "      <th>Timezone</th>\n",
       "      <th>Request</th>\n",
       "      <th>Status code</th>\n",
       "      <th>Bytes Sent</th>\n",
       "      <th>Referrer</th>\n",
       "      <th>Agent</th>\n",
       "      <th>Method</th>\n",
       "      <th>Protocol</th>\n",
       "      <th>Datetime</th>\n",
       "      <th>Session</th>\n",
       "      <th>Step</th>\n",
       "      <th>User_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>lj2559.inktomisearch.com</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>/code/Global/code/menu.html</td>\n",
       "      <td>200</td>\n",
       "      <td>6092</td>\n",
       "      <td>-</td>\n",
       "      <td>Mozilla/5.0 (compatible; Yahoo! Slurp; http://...</td>\n",
       "      <td>GET</td>\n",
       "      <td>HTTP/1.0</td>\n",
       "      <td>2005-04-18 21:16:55</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>c210-49-32-6.rochd2.qld.optusnet.com.au</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>/</td>\n",
       "      <td>200</td>\n",
       "      <td>7138</td>\n",
       "      <td>http://www.google.com.au/search?hl=en&amp;q=snap+p...</td>\n",
       "      <td>Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...</td>\n",
       "      <td>GET</td>\n",
       "      <td>HTTP/1.1</td>\n",
       "      <td>2005-04-18 21:25:07</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>c210-49-32-6.rochd2.qld.optusnet.com.au</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>/services.html</td>\n",
       "      <td>200</td>\n",
       "      <td>15289</td>\n",
       "      <td>http://www.copyspecialists.com.au/</td>\n",
       "      <td>Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...</td>\n",
       "      <td>GET</td>\n",
       "      <td>HTTP/1.1</td>\n",
       "      <td>2005-04-18 21:25:16</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>c210-49-32-6.rochd2.qld.optusnet.com.au</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>/more.html</td>\n",
       "      <td>200</td>\n",
       "      <td>8975</td>\n",
       "      <td>http://www.copyspecialists.com.au/services.html</td>\n",
       "      <td>Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...</td>\n",
       "      <td>GET</td>\n",
       "      <td>HTTP/1.1</td>\n",
       "      <td>2005-04-18 21:25:39</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>c210-49-32-6.rochd2.qld.optusnet.com.au</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>+1000]</td>\n",
       "      <td>/guarantee.html</td>\n",
       "      <td>200</td>\n",
       "      <td>5947</td>\n",
       "      <td>http://www.copyspecialists.com.au/more.html</td>\n",
       "      <td>Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...</td>\n",
       "      <td>GET</td>\n",
       "      <td>HTTP/1.1</td>\n",
       "      <td>2005-04-18 21:25:55</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       Host Identd Authuser Timezone  \\\n",
       "2                  lj2559.inktomisearch.com      -        -   +1000]   \n",
       "3   c210-49-32-6.rochd2.qld.optusnet.com.au      -        -   +1000]   \n",
       "40  c210-49-32-6.rochd2.qld.optusnet.com.au      -        -   +1000]   \n",
       "84  c210-49-32-6.rochd2.qld.optusnet.com.au      -        -   +1000]   \n",
       "99  c210-49-32-6.rochd2.qld.optusnet.com.au      -        -   +1000]   \n",
       "\n",
       "                        Request  Status code Bytes Sent  \\\n",
       "2   /code/Global/code/menu.html          200       6092   \n",
       "3                             /          200       7138   \n",
       "40               /services.html          200      15289   \n",
       "84                   /more.html          200       8975   \n",
       "99              /guarantee.html          200       5947   \n",
       "\n",
       "                                             Referrer  \\\n",
       "2                                                   -   \n",
       "3   http://www.google.com.au/search?hl=en&q=snap+p...   \n",
       "40                 http://www.copyspecialists.com.au/   \n",
       "84    http://www.copyspecialists.com.au/services.html   \n",
       "99        http://www.copyspecialists.com.au/more.html   \n",
       "\n",
       "                                                Agent Method  Protocol  \\\n",
       "2   Mozilla/5.0 (compatible; Yahoo! Slurp; http://...    GET  HTTP/1.0   \n",
       "3   Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...    GET  HTTP/1.1   \n",
       "40  Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...    GET  HTTP/1.1   \n",
       "84  Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...    GET  HTTP/1.1   \n",
       "99  Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-us...    GET  HTTP/1.1   \n",
       "\n",
       "              Datetime  Session  Step  User_ID  \n",
       "2  2005-04-18 21:16:55        1     1        1  \n",
       "3  2005-04-18 21:25:07        2     1        2  \n",
       "40 2005-04-18 21:25:16        2     2        2  \n",
       "84 2005-04-18 21:25:39        2     3        2  \n",
       "99 2005-04-18 21:25:55        2     4        2  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This output dataset now consists of IP address, requests, steps, session ids and user ids."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Applying Data Mining Methods\n",
    "\n",
    "The pre-processed web log data provides a source dataset for data mining. Various data mining operations such as clustering and association mining can now be applied on this dataset. This application forms a task in the Assignment 2."
   ]
  }
 ],
 "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}