Watson Assistant - Logs Extraction.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extraction of Watson Assistant Logs from Workspace\n",
    "\n",
    "####  Author Omar Megawer\n",
    "2018-Mar-09"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pixiedust in /anaconda3/lib/python3.6/site-packages (1.1.9)\n",
      "Requirement already satisfied: lxml in /anaconda3/lib/python3.6/site-packages (from pixiedust) (4.1.1)\n",
      "Requirement already satisfied: geojson in /anaconda3/lib/python3.6/site-packages (from pixiedust) (2.3.0)\n",
      "Requirement already satisfied: astunparse in /anaconda3/lib/python3.6/site-packages (from pixiedust) (1.5.0)\n",
      "Requirement already satisfied: markdown in /anaconda3/lib/python3.6/site-packages (from pixiedust) (2.6.11)\n",
      "Requirement already satisfied: mpld3 in /anaconda3/lib/python3.6/site-packages (from pixiedust) (0.3)\n",
      "Requirement already satisfied: wheel<1.0,>=0.23.0 in /anaconda3/lib/python3.6/site-packages (from astunparse->pixiedust) (0.31.1)\n",
      "Requirement already satisfied: six<2.0,>=1.6.1 in /anaconda3/lib/python3.6/site-packages (from astunparse->pixiedust) (1.11.0)\n",
      "\u001b[33mYou are using pip version 18.1, however version 19.0.2 is available.\n",
      "You should consider upgrading via the 'pip install --upgrade pip' command.\u001b[0m\n",
      "Requirement already satisfied: update in /anaconda3/lib/python3.6/site-packages (0.0.1)\n",
      "Requirement already satisfied: watson-developer-cloud in /anaconda3/lib/python3.6/site-packages (2.4.1)\n",
      "Requirement already satisfied: style==1.1.0 in /anaconda3/lib/python3.6/site-packages (from update) (1.1.0)\n",
      "Requirement already satisfied: requests<3.0,>=2.0 in /anaconda3/lib/python3.6/site-packages (from watson-developer-cloud) (2.18.4)\n",
      "Requirement already satisfied: python-dateutil>=2.5.3 in /anaconda3/lib/python3.6/site-packages (from watson-developer-cloud) (2.7.2)\n",
      "Requirement already satisfied: websocket-client==0.47.0 in /anaconda3/lib/python3.6/site-packages (from watson-developer-cloud) (0.47.0)\n",
      "Requirement already satisfied: chardet<3.1.0,>=3.0.2 in /anaconda3/lib/python3.6/site-packages (from requests<3.0,>=2.0->watson-developer-cloud) (3.0.4)\n",
      "Requirement already satisfied: idna<2.7,>=2.5 in /anaconda3/lib/python3.6/site-packages (from requests<3.0,>=2.0->watson-developer-cloud) (2.6)\n",
      "Requirement already satisfied: urllib3<1.23,>=1.21.1 in /anaconda3/lib/python3.6/site-packages (from requests<3.0,>=2.0->watson-developer-cloud) (1.22)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /anaconda3/lib/python3.6/site-packages (from requests<3.0,>=2.0->watson-developer-cloud) (2018.10.15)\n",
      "Requirement already satisfied: six>=1.5 in /anaconda3/lib/python3.6/site-packages (from python-dateutil>=2.5.3->watson-developer-cloud) (1.11.0)\n",
      "\u001b[33mYou are using pip version 18.1, however version 19.0.2 is available.\n",
      "You should consider upgrading via the 'pip install --upgrade pip' command.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!pip install pixiedust\n",
    "!pip install update watson-developer-cloud"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Pixiedust database opened successfully\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "        <div style=\"margin:10px\">\n",
       "            <a href=\"https://github.com/ibm-watson-data-lab/pixiedust\" target=\"_new\">\n",
       "                <img src=\"https://github.com/ibm-watson-data-lab/pixiedust/raw/master/docs/_static/pd_icon32.png\" style=\"float:left;margin-right:10px\"/>\n",
       "            </a>\n",
       "            <span>Pixiedust version 1.1.9</span>\n",
       "        </div>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>Warning: You are not running the latest version of PixieDust. Current is 1.1.9, Latest is 1.1.15</div>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "\n",
       "                <div>Please copy and run the following command in a new cell to upgrade: <span style=\"background-color:#ececec;font-family:monospace;padding:0 5px\">!pip install --user --upgrade pixiedust</span></div>\n",
       "            "
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>Please restart kernel after upgrading.</div>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from watson_developer_cloud import AssistantV1\n",
    "import numpy as np\n",
    "from sklearn.model_selection import KFold\n",
    "import pixiedust\n",
    "import json\n",
    "from urllib.parse import urlparse, parse_qs\n",
    "import itertools\n",
    "import matplotlib.pyplot as plt\n",
    "import datetime, dateutil.parser\n",
    "import seaborn as sns\n",
    "%matplotlib inline\n",
    "import warnings\n",
    "import openpyxl\n",
    "import pytz\n",
    "server_timezone = pytz.timezone(\"Etc/GMT+1\")\n",
    "uae_timezone = pytz.timezone(\"Asia/Dubai\")\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Add the Watson Conversation credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctx = {\n",
    "  \"url\": \"https://gateway-lon.watsonplatform.net/assistant/api\",\n",
    "  \"username\": \"apikey\",\n",
    "  \"password\": \"\"\n",
    "}\n",
    "\n",
    "workspace_id = \"83bc687f-\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Logs Extraction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Reading Logs for Page:  0\n"
     ]
    },
    {
     "ename": "WatsonApiException",
     "evalue": "Error: Property missing or empty, Code: 400",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mWatsonApiException\u001b[0m                        Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-4-e59d5203046a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     10\u001b[0m     \u001b[0mprint\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0;34m\"Reading Logs for Page: \"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcounter\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     11\u001b[0m     \u001b[0mcounter\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcounter\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 12\u001b[0;31m     \u001b[0mresponse1\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconversation\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mlist_logs\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mworkspace_id\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mworkspace_id\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mpage_limit\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m1000\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfilter\u001b[0m\u001b[0;34m=\u001b[0m \u001b[0mfltr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcursor\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     13\u001b[0m     \u001b[0mextractedLogs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse1\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'logs'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     14\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/anaconda3/lib/python3.6/site-packages/watson_developer_cloud/assistant_v1.py\u001b[0m in \u001b[0;36mlist_logs\u001b[0;34m(self, workspace_id, sort, filter, page_limit, cursor, **kwargs)\u001b[0m\n\u001b[1;32m   2692\u001b[0m             \u001b[0mheaders\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mheaders\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2693\u001b[0m             \u001b[0mparams\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2694\u001b[0;31m             accept_json=True)\n\u001b[0m\u001b[1;32m   2695\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2696\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/anaconda3/lib/python3.6/site-packages/watson_developer_cloud/watson_service.py\u001b[0m in \u001b[0;36mrequest\u001b[0;34m(self, method, url, accept_json, headers, params, json, data, files, **kwargs)\u001b[0m\n\u001b[1;32m    436\u001b[0m         \u001b[0mauth\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    437\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtoken_manager\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 438\u001b[0;31m             \u001b[0maccess_token\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtoken_manager\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_token\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    439\u001b[0m             \u001b[0mheaders\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Authorization'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'{0} {1}'\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mBEARER\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maccess_token\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    440\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0musername\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpassword\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/anaconda3/lib/python3.6/site-packages/watson_developer_cloud/iam_token_manager.py\u001b[0m in \u001b[0;36mget_token\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m     45\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0muser_access_token\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     46\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtoken_info\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'access_token'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 47\u001b[0;31m             \u001b[0mtoken_info\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_request_token\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     48\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_save_token_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtoken_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     49\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtoken_info\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'access_token'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/anaconda3/lib/python3.6/site-packages/watson_developer_cloud/iam_token_manager.py\u001b[0m in \u001b[0;36m_request_token\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m     76\u001b[0m             \u001b[0murl\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0miam_url\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     77\u001b[0m             \u001b[0mheaders\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mheaders\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 78\u001b[0;31m             data=data)\n\u001b[0m\u001b[1;32m     79\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     80\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/anaconda3/lib/python3.6/site-packages/watson_developer_cloud/iam_token_manager.py\u001b[0m in \u001b[0;36mrequest\u001b[0;34m(self, method, url, headers, params, data, **kwargs)\u001b[0m\n\u001b[1;32m     32\u001b[0m             \u001b[0;32mfrom\u001b[0m \u001b[0;34m.\u001b[0m\u001b[0mwatson_service\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mWatsonApiException\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mget_error_message\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     33\u001b[0m             \u001b[0merror_message\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mget_error_message\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 34\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mWatsonApiException\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstatus_code\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmessage\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0merror_message\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mhttpResponse\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mresponse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     35\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     36\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mget_token\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mWatsonApiException\u001b[0m: Error: Property missing or empty, Code: 400"
     ]
    }
   ],
   "source": [
    "conversation = AssistantV1( username=ctx.get('username'), password=ctx.get('password'), version='2018-02-18', url=ctx.get('url'))\n",
    "\n",
    "extractedLogs = []\n",
    "response1 = { 'pagination': 'DUMMY' } #This is set to start the loop\n",
    "cursor = None\n",
    "counter = 0\n",
    "fltr = \"response_timestamp>=2019-01-16,response_timestamp<2019-01-17\"  # Can be used to get a specific date\n",
    "\n",
    "while response1['pagination']:\n",
    "    print (\"Reading Logs for Page: \", counter)\n",
    "    counter = counter + 1\n",
    "    response1 = conversation.list_logs(workspace_id=workspace_id, page_limit = 1000, filter= fltr, cursor=cursor).get_result()\n",
    "    extractedLogs.append(response1['logs'])\n",
    "    \n",
    "    #The API has a limit of a 100 logs to pull\n",
    "    #To get the full log You need to update the cursor variable with a new cursor pointing to the next page\n",
    "    #The cursor is fetched using the next_url variable\n",
    "    \n",
    "    if 'pagination' in response1 and 'next_url' in response1['pagination']:\n",
    "        p = response1['pagination']['next_url']\n",
    "        u = urlparse(p) \n",
    "        query = parse_qs(u.query)\n",
    "        cursor = query['cursor'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "extractedLogs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Formatting the logs into a Dataframe\n",
    "The only needed fields are Conversation ID, Timestamp, Input, Output,  Confidence Level and Intent Detected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pixiedust": {
     "displayParams": {
      "aggregation": "COUNT",
      "clusterby": "Request_TimeStamp",
      "filter": "{}",
      "handlerId": "tableView",
      "keyFields": "Intent Detected",
      "legend": "true",
      "logy": "false",
      "mpld3": "true",
      "no_margin": "true",
      "rowCount": "574",
      "timeseries": "true"
     }
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "extraction = []\n",
    "for logs in extractedLogs:\n",
    "    for elem in logs:\n",
    "        userinput = elem['response']['input']\n",
    "        logID = elem['response']['context']['conversation_id']\n",
    "        if not elem['response']['intents']:\n",
    "            intentss = [{'confidence': 0, 'intent': 'No Intent Detected'}]\n",
    "            intentss = intentss[0]\n",
    "        else:\n",
    "            intentss = elem['response']['intents'][0]\n",
    "        \n",
    "    \n",
    "        wcsoutput = elem['response']['output']\n",
    "        userInput = userinput.get('text')\n",
    "        wcsOutput = wcsoutput.get('text')\n",
    "        time = elem['request_timestamp']\n",
    "        timestamp = dateutil.parser.parse(time)\n",
    "        timestamp = timestamp.astimezone(uae_timezone)\n",
    "        \n",
    "        row = {\n",
    "            'Log_ID': logID,\n",
    "            'Request_Date': timestamp.strftime('%d/%m/%Y'),\n",
    "            'Request_Time': timestamp.strftime('%H:%M'),\n",
    "            'User_Input': userInput,\n",
    "            'Watson_Output': wcsOutput,\n",
    "            'Confidence_Level': intentss['confidence'] * 100,\n",
    "            'Intent_Detected': intentss['intent']\n",
    "        }\n",
    "        extraction.append(row)\n",
    "df = pd.DataFrame(extraction, columns=['Log_ID', 'User_Input', 'Intent_Detected','Confidence_Level','Watson_Output', 'Request_Date','Request_Time'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "timestamp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-5-bb6e65ba6688>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdf\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msort_values\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Request_Date'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'Request_Time'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'Log_ID'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mascending\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mFalse\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'df' is not defined"
     ]
    }
   ],
   "source": [
    "df = df.sort_values(['Request_Date', 'Request_Time', 'Log_ID'], ascending=[True, True, False])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-6-00cf07b74dcd>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdf\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'df' is not defined"
     ]
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Save Logs as Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "writer = pd.ExcelWriter('MOHAP_En_Logs_16012019.xlsx')\n",
    "df.to_excel(writer, \"Extracted Logs\")\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Number of Conversations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uniqueConversations = df.Log_ID.nunique() \n",
    "print(\"Number of Conversations in the Log: \", uniqueConversations)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Conversations Per Day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pixiedust": {
     "displayParams": {
      "handlerId": "barChart",
      "keyFields": "Date",
      "mpld3": "true",
      "orientation": "horizontal",
      "timeseries": "false",
      "valueFields": "Conversation Count"
     }
    }
   },
   "outputs": [],
   "source": [
    "convPerDay = df.groupby(['Request_TimeStamp'], as_index=False).count()\n",
    "convPerDay = convPerDay.drop(['User_Input', 'Intent_Detected','Confidence_Level','Watson_Output'], axis=1)\n",
    "convPerDay = convPerDay.sort_values('Request_TimeStamp')\n",
    "convPerDay.columns=['Date', 'Conversation Count']\n",
    "display(convPerDay)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "sns.set(style=\"white\", context=\"notebook\")\n",
    "vis1 = sns.barplot(x = 'Conversation Count', y = 'Date', data = convPerDay, palette=\"PuBuGn_d\").set_title('Conversations Per Day')\n",
    "plt.savefig('ConversationsPerDay.png', bbox_inches='tight')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Intent Frequency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pixiedust": {
     "displayParams": {
      "handlerId": "tableView",
      "keyFields": "Intent",
      "mpld3": "false",
      "rowCount": "50",
      "sortby": "Keys DESC",
      "stretch": "true",
      "tableFields": "Count,Intent",
      "valueFields": "Count"
     }
    }
   },
   "outputs": [],
   "source": [
    "intentCount = df.groupby(['Intent_Detected'], as_index=False).count()\n",
    "intentCount = intentCount.drop(['User_Input', 'Request_TimeStamp','Confidence_Level','Watson_Output'], axis=1)\n",
    "intentCount.columns =  ['Intent', 'Count']\n",
    "intentCount = intentCount.sort_values('Count', ascending=False)\n",
    "intentCount = intentCount[intentCount.Intent != 'No Intent Detected'] # Removing the intents that were not matched"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "plt.xticks(rotation = 90)\n",
    "\n",
    "sns.set(rc={'figure.figsize':(50,50)}, font_scale=6, style = 'white')\n",
    "\n",
    "vis2 = sns.barplot(x = 'Intent' , y = 'Count', data = intentCount, color = \"#3b5b92\")\n",
    "\n",
    "plt.savefig('IntentFreq.png', bbox_inches='tight')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Intents with Low Confidence"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lowConf = df[df.Confidence_Level < 40]\n",
    "lowConf.to_excel(writer, \"Low Confidence\")\n",
    "writer.save()"
   ]
  },
  {
   "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.6.4"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": false,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": false,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}