IS602_HW9.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3>HW9: Working with Pandas Dataframes</h3> \n",
    "\n",
    "Use the pandas module to answer the following questions about the EPA-HTTP data set.  Print the result of each part to the console.  Use pandas as much as you can; this includes the data structure and the analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import re\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from StringIO import StringIO\n",
    "import Tkinter\n",
    "import tkFileDialog"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def data_clean(data_file):\n",
    "    \"\"\"\n",
    "    restructure the epa-http.txt file \"HW9_data.txt\" locally\n",
    "    input: text file from local system selected using dialog box\n",
    "    returns: pandas dataframe with and correct timestamp and quoting\n",
    "    \"\"\"\n",
    "\n",
    "    pattern = r\"(\\\" )(?=HTTP)\"  # filter triple quoted lines\n",
    "\n",
    "    with open(data_file) as d:\n",
    "        raw = d.read()\n",
    "\n",
    "    f_txt = re.sub(pattern, \" \", raw) # removes the extra quote\n",
    "\n",
    "    x = StringIO(f_txt) # load the data into StringIO\n",
    "\n",
    "    df = pd.read_csv(x, sep=\"\\s+\", header=None, na_values=\"-\")  # separate by space.\n",
    "    df.columns = ['IP_address', 'time_stamp', 'request', 'status', 'bytes']\n",
    "\n",
    "    df['time_stamp'] += \"1995-08\" # add year and month\n",
    "    df['time_stamp'] = pd.to_datetime(df['time_stamp'], format=\"[%d:%H:%M:%S]%Y-%m\")\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Select file using dialog box\n",
    "root = Tkinter.Tk()\n",
    "root.withdraw()\n",
    "file_p = tkFileDialog.askopenfilename(parent=root)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                IP_address          time_stamp  \\\n",
      "0            141.243.1.172 1995-08-29 23:53:25   \n",
      "1  query2.lycos.cs.cmu.edu 1995-08-29 23:53:36   \n",
      "2         tanuki.twics.com 1995-08-29 23:53:53   \n",
      "3       wpbfl2-45.gate.net 1995-08-29 23:54:15   \n",
      "4       wpbfl2-45.gate.net 1995-08-29 23:54:16   \n",
      "\n",
      "                                     request  status  bytes  \n",
      "0                GET /Software.html HTTP/1.0     200   1497  \n",
      "1                GET /Consumer.html HTTP/1.0     200   1325  \n",
      "2                    GET /News.html HTTP/1.0     200   1014  \n",
      "3                             GET / HTTP/1.0     200   4889  \n",
      "4  GET /icons/circle_logo_small.gif HTTP/1.0     200   2624  \n"
     ]
    }
   ],
   "source": [
    "# clean up the file using data_clean()\n",
    "new_data = data_clean(file_p)\n",
    "\n",
    "# check to make sure it's structured properly\n",
    "print new_data[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "IP_address or hostname with most requests:\n",
      "sandy.rtptok1.epa.gov    294\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# 1. Which hostname or IP address made the most requests?\n",
    "print \"IP_address or hostname with most requests:\"\n",
    "print new_data['IP_address'].value_counts()[:1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "IP_address and hostname with most total bytes:\n",
      "IP_address\n",
      "piankhi.cs.hamptonu.edu    7267751\n",
      "Name: bytes, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# 2. Which hostname or IP address received the most total bytes from the server? How many bytes did it receive?\n",
    "ip_1 = new_data.groupby(new_data.IP_address)\n",
    "byte_totals = ip_1['bytes'].aggregate(np.sum)\n",
    "byte_totals.sort(inplace=True, ascending=False)\n",
    "print \"IP_address and hostname with most total bytes:\"\n",
    "print byte_totals[:1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Busiest server hour | number of requests:\n",
      "                 14    4716\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#3. During what hour was the server the busiest in terms of requests?\n",
    "time_1 = new_data.groupby(new_data['time_stamp'].dt.hour)\n",
    "group_size = time_1.size()\n",
    "group_size.sort(inplace=True, ascending=False)\n",
    "print \"Busiest server hour | number of requests:\"\n",
    "print \"                 \" + str(group_size[:1]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                Most downloaded gif image | download count\n",
      "GET /icons/circle_logo_small.gif HTTP/1.0    2465\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#4 Which .gif image was downloaded the most during the day?\n",
    "s = new_data[(new_data.status == 200) & (new_data.request.str.contains('\\\\.gif'))]\n",
    "print \"                Most downloaded gif image | download count\"\n",
    "print sub.request.value_counts()[:1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Codes | counts:\n",
      "304    5300\n",
      "302    4506\n",
      "404     611\n",
      "501     272\n",
      "403     272\n",
      "500      69\n",
      "400       6\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#5 What HTTP reply codes were sent other than 200?\n",
    "http_s = new_data[new_data.status != 200]\n",
    "print \"Codes | counts:\"\n",
    "print http_s.status.value_counts()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}