05b_Chart_the_trends-BiggestMovers.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 05b Chart the trends - \"Biggest movers\" May-June\n",
    "App to analyze web-site search logs (internal search)<br>\n",
    "**This script:** May-June analysis, fuller than the 05 file. Biggest Movers / Percent change charts<br>\n",
    "Authors: dan.wendling@nih.gov, <br>\n",
    "Last modified: 2018-09-09\n",
    "\n",
    "\n",
    "## Script contents\n",
    "\n",
    "1. Start-up / What to put into place, where\n",
    "2. Unite search log data into single dataframe; globally update columns and rows\n",
    "3. Separate out the queries with non-English characters\n",
    "4. Run STAFF stats\n",
    "5. Run PUBLIC (off-LAN) stats\n",
    "6. Add result to MySQL, process at http://localhost:5000/searchsum\n",
    "\n",
    "\n",
    "## FIXMEs\n",
    "\n",
    "Things Dan wrote for Dan; modify as needed. There are more FIXMEs in context.\n",
    "\n",
    "* [ ] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. Start-up / What to put into place, where\n",
    "# ============================================\n",
    "\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.pyplot import pie, axis, show\n",
    "import numpy as np\n",
    "import os\n",
    "import string\n",
    "\n",
    "# Set working directory\n",
    "os.chdir('/Users/wendlingd/Projects/webDS/_util')\n",
    "\n",
    "localDir = '05_Chart_the_trends_files/'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 2. Unite search log data into single dataframe; globally update columns and rows\n",
    "# =================================================================================\n",
    "# What is your new log file named?\n",
    "\n",
    "newSearchLogFile = '00_Source_files/FY18-q3.xlsx'\n",
    "\n",
    "x1 = pd.read_excel(newSearchLogFile, 'Page1_1', skiprows=2)\n",
    "x2 = pd.read_excel(newSearchLogFile, 'Page1_2', skiprows=2)\n",
    "x3 = pd.read_excel(newSearchLogFile, 'Page1_3', skiprows=2)\n",
    "x4 = pd.read_excel(newSearchLogFile, 'Page1_4', skiprows=2)\n",
    "x5 = pd.read_excel(newSearchLogFile, 'Page1_5', skiprows=2)\n",
    "x6 = pd.read_excel(newSearchLogFile, 'Page1_6', skiprows=2)\n",
    "# x5 = pd.read_excel('00 SourceFiles/2018-06/Queries-2018-05.xlsx', 'Page1_2', skiprows=2)\n",
    "\n",
    "searchLog = pd.concat([x1, x2, x3, x4, x5, x6], ignore_index=True) # , x3, x4, x5, x6, x7\n",
    "\n",
    "searchLog.head(n=5)\n",
    "searchLog.shape\n",
    "searchLog.info()\n",
    "searchLog.columns\n",
    "\n",
    "# Drop ID column, not needed\n",
    "# searchLog.drop(['ID'], axis=1, inplace=True)\n",
    "            \n",
    "# Until Cognos report is fixed, problem of blank columns, multi-word col name\n",
    "# Update col name\n",
    "searchLog = searchLog.rename(columns={'Search Timestamp': 'Timestamp', \n",
    "                                      'NLM IP Y/N':'StaffYN',\n",
    "                                      'IP':'SessionID'})\n",
    "\n",
    "# Remove https:// to become joinable with traffic data\n",
    "searchLog['Referrer'] = searchLog['Referrer'].str.replace('https://', '')\n",
    "\n",
    "# Dupe off the Query column into a lower-cased 'adjustedQueryCase', which \n",
    "# will be the column you match against\n",
    "searchLog['adjustedQueryCase'] = searchLog['Query'].str.lower()\n",
    "\n",
    "# Remove incomplete rows, which can cause errors later\n",
    "searchLog = searchLog[~pd.isnull(searchLog['Referrer'])]\n",
    "searchLog = searchLog[~pd.isnull(searchLog['Query'])]\n",
    "\n",
    "# Limit to NLM Home\n",
    "searchfor = ['www.nlm.nih.gov$', 'www.nlm.nih.gov/$']\n",
    "HmPgLog = searchLog[searchLog.Referrer.str.contains('|'.join(searchfor))]\n",
    "\n",
    "timeBoundHmPgLog = HmPgLog\n",
    "\n",
    "# Limit to May and June and assign month name\n",
    "timeBoundHmPgLog.loc[(timeBoundHmPgLog['Timestamp'] > '2018-05-01 00:00:00') & (timeBoundHmPgLog['Timestamp'] < '2018-06-01 00:00:00'), 'Month'] = 'May'\n",
    "timeBoundHmPgLog.loc[(timeBoundHmPgLog['Timestamp'] > '2018-06-01 00:00:00') & (timeBoundHmPgLog['Timestamp'] < '2018-07-01 00:00:00'), 'Month'] = 'June'\n",
    "timeBoundHmPgLog = timeBoundHmPgLog.loc[(timeBoundHmPgLog['Month'] != \"\")]\n",
    "# or drop nan\n",
    "timeBoundHmPgLog.dropna(subset=['Month'], inplace=True) \n",
    "\n",
    "\n",
    "# Useful to write out the cleaned up version; if you do re-processing, you can skip a bunch of work.\n",
    "writer = pd.ExcelWriter(localDir + 'timeBoundHmPgLog.xlsx')\n",
    "timeBoundHmPgLog.to_excel(writer,'timeBoundHmPgLog')\n",
    "# df2.to_excel(writer,'Sheet2')\n",
    "writer.save()\n",
    "\n",
    "# Remove x1., etc., searchLog, HmPgLog\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3. Separate out the queries with non-English characters\n",
    "# ========================================================\n",
    "'''\n",
    "# FIXME - STOP THIS FROM CHANGING NORMAL ROWS.\n",
    "See comment in function. Trying things from:\n",
    "https://stackoverflow.com/questions/36340627/removing-non-ascii-characters-and-replacing-with-spaces-from-pandas-data-frame\n",
    "https://stackoverflow.com/questions/27084617/detect-strings-with-non-english-characters-in-python\n",
    "https://stackoverflow.com/questions/196345/how-to-check-if-a-string-in-python-is-in-ascii\n",
    "https://stackoverflow.com/questions/16353729/how-do-i-use-pandas-apply-function-to-multiple-columns\n",
    "And other places\n",
    "\n",
    "For testing\n",
    "searchLogClean = pd.read_excel(localDir + 'searchLogClean.xlsx')\n",
    "searchLogClean = searchLogClean.iloc[12000:13000]\n",
    "searchLogClean['preferredTerm'] = searchLogClean['preferredTerm'].str.replace(None, '')\n",
    "\n",
    "Future: Break out languages better; assign language name, find translation API, etc.\n",
    "\n",
    "Re-start\n",
    "MayJuneHmPg = pd.read_excel(localDir + 'searchLog-MayJune-HmPg.xlsx')\n",
    "timeBoundHmPgLog = MayJuneHmPg\n",
    "'''\n",
    "\n",
    "\n",
    "# When it hangs... checkTrouble = searchLog.iloc[156422:156427]\n",
    "\n",
    "\n",
    "timeBoundHmPgLog['preferredTerm'] = \"\"\n",
    "\n",
    "def foreignCharTest(row):\n",
    "    try: \n",
    "        row['Query'].encode('ascii'); \n",
    "        pass # Intention is, don't alter row at all; but returns None.\n",
    "    except UnicodeEncodeError: \n",
    "        return 'NON-ENGLISH CHARACTERS'\n",
    "\n",
    "timeBoundHmPgLog['preferredTerm'] = timeBoundHmPgLog.apply(foreignCharTest, axis=1)\n",
    "\n",
    "# FIXME - Find a way to restore preferredTerm\n",
    "# searchLog['preferredTerm'].replace('', np.nan, inplace=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 4. Run STAFF stats\n",
    "# ==============================\n",
    "'''\n",
    "On-LAN stats\n",
    "FIXME - Check whether Cognos separation of Sfaff-YN can exclude reading room?\n",
    "But, how many of the people in the reading room are on www.nlm.nih.gov at all?\n",
    "'''\n",
    "# Restrict to staff\n",
    "staffStats = timeBoundHmPgLog.loc[timeBoundHmPgLog['StaffYN'].str.contains('Y') == True]\n",
    "\n",
    "# Staff search count\n",
    "totSearchesStaff = staffStats.groupby('Month')['ID'].nunique()\n",
    "print(\"\\nTotal STAFF SEARCHES in raw log file:\\n{}\".format(totSearchesStaff))\n",
    "\n",
    "# Staff unique queries\n",
    "uniqueSearchesStaff = staffStats['Query'].nunique()\n",
    "uniqueSearchesStaff\n",
    "\n",
    "uniqueSearchesStaffByMonth = staffStats.groupby('Month')['Query'].nunique()\n",
    "uniqueSearchesStaffByMonth\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Staff session count\n",
    "totSessionsStaff = staffStats.groupby('Month')['SessionID'].nunique()\n",
    "print(\"\\nTotal STAFF SESSIONS in raw log file:\\n{}\".format(totSessionsStaff))\n",
    "\n",
    "'''\n",
    "Bar chart - by number of searches per session\n",
    "\n",
    "Average searches per session\n",
    "Median searches per session\n",
    "Average searches per day (@ 22d/mo.)\n",
    "Median searches per day  (@ 22d/mo.)\n",
    "Average sessions per day\n",
    "Median sessions per day\n",
    "Highest search count in one session\n",
    "\n",
    "'''\n",
    "\n",
    "\n",
    "# Top 40 queries from NLM LAN, from NLM Home (not normalized)\n",
    "searchLogLanYesHmPg = staffStats.loc[staffStats['StaffYN'].str.contains('Y') == True]\n",
    "searchfor = ['www.nlm.nih.gov$', 'www.nlm.nih.gov/$']\n",
    "searchLogLanYesHmPg = searchLogLanYesHmPg[searchLogLanYesHmPg.Referrer.str.contains('|'.join(searchfor))]\n",
    "searchLogLanYesHmPgQueryCounts = searchLogLanYesHmPg['Query'].value_counts()\n",
    "searchLogLanYesHmPgQueryCounts = searchLogLanYesHmPgQueryCounts.reset_index()\n",
    "searchLogLanYesHmPgQueryCounts = searchLogLanYesHmPgQueryCounts.rename(columns={'index': 'Top queries from NLM LAN, from Home, as entered', 'Query': 'Count'})\n",
    "searchLogLanYesHmPgQueryCounts.head(n=25)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 5. Run PUBLIC (off-LAN) stats\n",
    "# ==============================\n",
    "\n",
    "\n",
    "visitorStats = timeBoundHmPgLog.loc[timeBoundHmPgLog['StaffYN'].str.contains('N') == True]\n",
    "\n",
    "# Count rows with foreign chars\n",
    "foreignCount = visitorStats.loc[visitorStats['preferredTerm'].str.contains('NON-ENGLISH CHARACTERS') == True]\n",
    "foreignCount.count()\n",
    "\n",
    "# Drop rows with foreign chars\n",
    "visitorStats = visitorStats[visitorStats.preferredTerm != 'NON-ENGLISH CHARACTERS']\n",
    "\n",
    "# Visitor search count\n",
    "totSearchesVisitors = visitorStats.groupby('Month')['ID'].nunique()\n",
    "print(\"\\nTotal VISITOR SEARCHES in raw log file:\\n{}\".format(totSearches))\n",
    "\n",
    "# Visitor unique queries\n",
    "uniqueSearchesVisitors = visitorStats['Query'].nunique()\n",
    "uniqueSearchesVisitors\n",
    "\n",
    "uniqueSearchesVisitorsByMonth = visitorStats.groupby('Month')['Query'].nunique()\n",
    "uniqueSearchesVisitorsByMonth\n",
    "\n",
    "\n",
    "# Visitor session count\n",
    "totSessionsVisitors = visitorStats.groupby('Month')['SessionID'].nunique()\n",
    "print(\"\\nTotal VISITOR SESSIONS in raw log file:\\n{}\".format(totSessions))\n",
    "\n",
    "\n",
    "\n",
    "'''\n",
    "Bar chart - by number of searches per session\n",
    "\n",
    "Average searches per session\n",
    "Median searches per session\n",
    "Average searches per day (@ 22d/mo.)\n",
    "Median searches per day  (@ 22d/mo.)\n",
    "Average sessions per day\n",
    "Median sessions per day\n",
    "Highest search count in one session\n",
    "\n",
    "'''\n",
    "\n",
    "\n",
    "# Highest session search count\n",
    "SessionCounts = visitorStats['SessionID'].value_counts()\n",
    "SessionCounts = pd.DataFrame({'TypeCount':SessionCounts})\n",
    "SessionCounts.sort_values(\"TypeCount\", ascending=True, inplace=True)\n",
    "SessionCounts = SessionCounts.reset_index()\n",
    "\n",
    "# test = searchLog.loc[searchLog['SessionID'].str.contains('47C9DEE89B48E22FB53E2BE2DB107763') == True]\n",
    "\n",
    "\n",
    "# Top queries outside NLM LAN, from NLM Home (not normalized)\n",
    "# May-June\n",
    "df3LanNoHmPgQueryCounts = visitorStats['Query'].value_counts()\n",
    "df3LanNoHmPgQueryCounts = df3LanNoHmPgQueryCounts.reset_index()\n",
    "df3LanNoHmPgQueryCounts = df3LanNoHmPgQueryCounts.rename(columns={'index': 'Top queries off of LAN, from Home, as entered', 'Query': 'Count'})\n",
    "df3LanNoHmPgQueryCounts.head(n=25)\n",
    "\n",
    "# May top 25\n",
    "MayVisitorTop25 = visitorStats.loc[visitorStats['Month'].str.contains('May') == True]\n",
    "MayVisitorTop25 = MayVisitorTop25['Query'].value_counts()\n",
    "MayVisitorTop25 = MayVisitorTop25.reset_index()\n",
    "MayVisitorTop25 = MayVisitorTop25.rename(columns={'index': 'Top VISITOR queries from NLM Home page, as entered', 'Query': 'Count'})\n",
    "MayVisitorTop25.head(n=25)\n",
    "\n",
    "# June top 25\n",
    "JuneVisitorTop25 = visitorStats.loc[visitorStats['Month'].str.contains('June') == True]\n",
    "JuneVisitorTop25 = JuneVisitorTop25['Query'].value_counts()\n",
    "JuneVisitorTop25 = JuneVisitorTop25.reset_index()\n",
    "JuneVisitorTop25 = JuneVisitorTop25.rename(columns={'index': 'Top VISITOR queries from NLM Home page, as entered', 'Query': 'Count'})\n",
    "JuneVisitorTop25.head(n=25)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# logAfterFuzzyMatch\n",
    "\n",
    "EffectOfLight = logAfterFuzzyMatch.loc[logAfterFuzzyMatch['Query'].str.contains('effect of light') == True]\n",
    "\n",
    "# Useful to write out the cleaned up version; if you do re-processing, you can skip a bunch of work.\n",
    "writer = pd.ExcelWriter(localDir + 'EffectOfLight.xlsx')\n",
    "EffectOfLight.to_excel(writer,'EffectOfLight')\n",
    "# df2.to_excel(writer,'Sheet2')\n",
    "writer.save()\n",
    "\n",
    "\n",
    "\n",
    "dobby = logAfterFuzzyMatch.loc[logAfterFuzzyMatch['preferredTerm'].str.startswith('Samples of Formatted') == True]\n",
    "\n",
    "# Samples of Formatted References for Authors of Journal Articles\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 6. Add result to MySQL, process at http://localhost:5000/searchsum\n",
    "# ========================================================================\n",
    "'''\n",
    "timeBoundHmPgLog.columns\n",
    "\n",
    "In phpMyAdmin:\n",
    "\n",
    "DROP TABLE IF EXISTS `timeboundhmpglog`;\n",
    "CREATE TABLE `timeboundhmpglog` (\n",
    "  `Timestamp` datetime DEFAULT NULL,\n",
    "  `preferredTerm` text,\n",
    "  `SemanticTypeName` text,\n",
    "  `SemanticTypeCode` int(11) DEFAULT NULL,\n",
    "  `SemanticGroup` text,\n",
    "  `SemanticGroupCode` int(11) DEFAULT NULL,\n",
    "  `Month` text\n",
    ") ENGINE=InnoDB DEFAULT CHARSET=utf8;\n",
    "\n",
    "    \n",
    "        \n",
    "writer = pd.ExcelWriter(localDir + 'timeBoundHmPgLog.xlsx')\n",
    "timeBoundHmPgLog.to_excel(writer,'timeBoundHmPgLog')\n",
    "# df2.to_excel(writer,'Sheet2')\n",
    "writer.save()\n",
    "\n",
    "\n",
    "Re-start\n",
    "MayJuneHmPg = pd.read_excel(localDir + 'searchLog-MayJune-HmPg.xlsx')\n",
    "timeBoundHmPgLog = MayJuneHmPg\n",
    "'''\n",
    "\n",
    "logAfterFuzzyMatch = pd.read_excel('03_Fuzzy_match_files/logAfterFuzzyMatch.xlsx')\n",
    "\n",
    "# Remove nans from Month\n",
    "logAfterFuzzyMatch = logAfterFuzzyMatch.dropna(subset=['Month'])\n",
    "\n",
    "logAfterFuzzyMatch.columns\n",
    "\n",
    "# Reduce size for test\n",
    "test = logAfterFuzzyMatch.iloc[0:49]\n",
    "\n",
    "\n",
    "# Add dataframe to MySQL\n",
    "\n",
    "import mysql.connector\n",
    "from pandas.io import sql\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "dbconn = create_engine('mysql+mysqlconnector://wendlingd:DataSciPwr17@localhost/ia')\n",
    "\n",
    "logAfterFuzzyMatch.to_sql(name='timeboundhmpglog', con=dbconn, if_exists = 'replace', index=False) # or if_exists='append'\n",
    "   \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "'''\n",
    "\n",
    "test = df3\n",
    "\n",
    "df3.set_index('SessionID', inplace=True)\n",
    "test = df3.groupby(['col2','col3'], as_index=False).count()\n",
    "\n",
    "\n",
    "\n",
    "test = df3.groupby(['Month','StaffYN'], as_index=False)['SessionID'].count()\n",
    "test\n",
    "\n",
    "test = df3.groupby(['Month','StaffYN'], as_index=False)['SearchID'].count()\n",
    "test\n",
    "\n",
    "\n",
    "test = df3['ID'].groupby([df3['Month'], df3['StaffYN']]).size()\n",
    "test\n",
    "\n",
    "\n",
    "df3['SearchID'].count()\n",
    "\n",
    "test = df3.groupby(['Month', 'StaffYN'])['Referrer'].size()\n",
    "test\n",
    "\n",
    "totSearches = df3.groupby(['Month', 'StaffYN'])['SearchID'].count()\n",
    "print(\"\\nTotal SEARCHES in raw log file:\\n{}\".format(totSearches))\n",
    "\n",
    "totSessions = df3.groupby(['Month', 'StaffYN']).size()\n",
    "print(\"\\nTotal SESSIONS in raw log file:\\n{}\".format(totSessions))\n",
    "\n",
    "\n",
    "# pd.crosstab(df3.ID, df3.SessionID, margins=True)\n",
    "\n",
    "\n",
    "# df3 = df3.rename(columns={'ID': 'SearchID'})\n",
    "'''\n",
    "\n",
    "\n",
    "\n",
    "# Total SEARCHES in raw log file\n",
    "totSearches = df3['SearchID'].groupby([df3['Month'], df3['StaffYN']]).count()\n",
    "print(\"\\nTotal SEARCHES in raw log file:\\n{}\".format(totSearches))\n",
    "\n",
    "# Total SESSIONS in raw log file\n",
    "totSessions = df3['SessionID'].groupby([df3['Month'], df3['StaffYN']]).count()\n",
    "print(\"\\nTotal SESSIONS in raw log file:\\n{}\".format(totSessions))\n",
    "\n",
    "\n",
    "\n",
    "print(\"Total searches in raw log file: {}\".format(len(df3)))\n",
    "\n",
    "# totals\n",
    "print(\"\\nTotal SEARCH QUERIES, on NLM LAN or not\\n{}\".format(df3['StaffYN'].value_counts()))\n",
    "\n",
    "print(\"\\nTotal SESSIONS, on NLM LAN or not\\n{}\".format(df3.groupby('StaffYN')['SessionID'].nunique()))\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "test = df3['SearchID'].groupby(df3['Month'])\n",
    "test.count()\n",
    "\n",
    "# If you see digits in text col, perhaps these are partial log entries - eyeball for removal\n",
    "# df3.drop(76080, inplace=True)\n",
    "\n",
    "\n",
    "test = df3['StaffYN'].groupby(df3['Month'])\n",
    "test.count()\n",
    "\n",
    "\n",
    "\n",
    "# Total SEARCHES containing 'Non-English characters'\n",
    "print(\"Total SEARCHES with non-English characters\\n{}\".format(df3['preferredTerm'].value_counts()))\n",
    "\n",
    "# Total SESSIONS containing 'Non-English characters'\n",
    "# Future\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# How to set a date range\n",
    "AprMay = logAfterUmlsApi1[(logAfterUmlsApi1['Timestamp'] > '2018-04-01 01:00:00') & (logAfterUmlsApi1['Timestamp'] < '2018-06-01 00:00:00')]\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Top queries from LAN (not normalized)\n",
    "df3LanYes = df3.loc[df3['StaffYN'].str.contains('Y') == True]\n",
    "df3LanYesQueryCounts = df3LanYes['Query'].value_counts()\n",
    "df3LanYesQueryCounts = df3LanYesQueryCounts.reset_index()\n",
    "df3LanYesQueryCounts = df3LanYesQueryCounts.rename(columns={'index': 'Top staff queries as entered', 'Query': 'Count'})\n",
    "df3LanYesQueryCounts.head(n=30)\n",
    "\n",
    "# Top queries from NLM LAN, from NLM Home (not normalized)\n",
    "df3LanYesHmPg = df3.loc[df3['StaffYN'].str.contains('Y') == True]\n",
    "searchfor = ['www.nlm.nih.gov$', 'www.nlm.nih.gov/$']\n",
    "df3LanYesHmPg = df3LanYesHmPg[df3LanYesHmPg.Referrer.str.contains('|'.join(searchfor))]\n",
    "df3LanYesHmPgQueryCounts = df3LanYesHmPg['Query'].value_counts()\n",
    "df3LanYesHmPgQueryCounts = df3LanYesHmPgQueryCounts.reset_index()\n",
    "df3LanYesHmPgQueryCounts = df3LanYesHmPgQueryCounts.rename(columns={'index': 'Top queries from NLM LAN, from Home, as entered', 'Query': 'Count'})\n",
    "df3LanYesHmPgQueryCounts.head(n=25)\n",
    "\n",
    "\n",
    "# Top queries outside NLM LAN (not normalized)\n",
    "df3LanNo = df3.loc[df3['StaffYN'].str.contains('N') == True]\n",
    "df3LanNoQueryCounts = df3LanNo['Query'].value_counts()\n",
    "df3LanNoQueryCounts = df3LanNoQueryCounts.reset_index()\n",
    "df3LanNoQueryCounts = df3LanNoQueryCounts.rename(columns={'index': 'Top queries off of LAN, as entered', 'Query': 'Count'})\n",
    "df3LanNoQueryCounts.head(n=25)\n",
    "\n",
    "\n",
    "\n",
    "# Top home page queries, staff or public\n",
    "searchfor = ['www.nlm.nih.gov$', 'www.nlm.nih.gov/$']\n",
    "df3AllHmPgQueryCounts = df3[df3.Referrer.str.contains('|'.join(searchfor))]\n",
    "df3AllHmPgQueryCounts = df3AllHmPgQueryCounts['Query'].value_counts()\n",
    "df3AllHmPgQueryCounts = df3AllHmPgQueryCounts.reset_index()\n",
    "df3AllHmPgQueryCounts = df3AllHmPgQueryCounts.rename(columns={'index': 'Top home page queries, staff or public, as entered', 'Query': 'Count'})\n",
    "df3AllHmPgQueryCounts.head(n=25)\n",
    "\n",
    "\n",
    "# FIXME - Add table, Percentage of staff, public searches done within pages, within search results\n",
    "\n",
    "\n",
    "# FIXME - Add table for Top queries with columns/counts On LAN, Off LAN, Total\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Remove the searches run from within search results screens, vsearch.nlm.nih.gov/vivisimo/\n",
    "# I'm not looking at these now; you might be.\n",
    "df3 = df3[df3.Referrer.str.startswith(\"www.nlm.nih.gov\") == True]\n",
    "\n",
    "# Not sure what these are, www.nlm.nih.gov/?_ga=2.95055260.1623044406.1513044719-1901803437.1513044719\n",
    "df3 = df3[df3.Referrer.str.startswith(\"www.nlm.nih.gov/?_ga=\") == False]\n",
    "\n",
    "\n",
    "# FIXME - VARIABLE EXPLORER: After saving the stats, remove unneeded 'Type=DataFrame' items\n",
    "'''\n",
    "Remove manually for now.\n",
    "Not finding an equiv to R's rm; cf https://stackoverflow.com/questions/32247643/how-to-delete-multiple-pandas-python-dataframes-from-memory-to-save-ram?rq=1\n",
    "pd.x1(), pd.x2(), # pd.x3(), pd.x4(), pd.x5(), pd.x6(), pd.x7(), \n",
    "              pd.searchLogLanYes(), pd.searchLogLanYesHmPg(), \n",
    "              pd.searchLogLanNo(), pd.searchLogLanNoHmPg(),\n",
    "              pd.searchLogAllHmPg()\n",
    "'''\n"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}