Log File Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import requests\n",
    "import re\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#upload data\n",
    "data = ('/Users/rutheverett/Downloads/www-deepcrawl-com_12-10-2020_All_Pages_basic.csv')\n",
    "#select colums \n",
    "columns = ['url',\n",
    "           'log_requests_total',\n",
    "           'log_requests_desktop',\n",
    "           'log_requests_mobile',\n",
    "           'indexable',\n",
    "           'http_status_code',\n",
    "           'primary_page',\n",
    "           'duplicate_page', \n",
    "           'search_console_total_clicks',\n",
    "           'level',\n",
    "           'links_in_count']\n",
    "#read in data colums \n",
    "df = pd.read_csv(data , usecols=columns)\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Segment URLs \n",
    "segment_definitions = [\n",
    "    [(r'\\/blog\\/'), 'Blog'],\n",
    "    [(r'\\/technical-seo-library\\/'), 'Technical SEO Library'],\n",
    "    [(r'\\/hangout-library\\/'), 'Hangout Library'],\n",
    "    [(r'\\/guides\\/'), 'Guides'],\n",
    "    [(r'\\/case-studies\\/'), 'Case Studies'],\n",
    "    [(r'\\/why-'), 'Solutions'],\n",
    "    ]\n",
    "\n",
    "use_segment_definitions = True\n",
    "\n",
    "def get_segment(url):\n",
    "    \n",
    "    if use_segment_definitions == True:\n",
    "        for segment_definition in segment_definitions:\n",
    "            if re.findall(segment_definition[0], url):\n",
    "                return segment_definition[1]\n",
    "        return 'Other'\n",
    "\n",
    "df['segment'] = df['url'].apply(lambda x: get_segment(x))\n",
    "\n",
    "# Replace NaN with 0\n",
    "df['search_console_total_clicks'].replace(np.nan, 0, inplace=True)\n",
    "df['log_requests_total'].replace(np.nan, 0, inplace=True)\n",
    "df['log_requests_mobile'].replace(np.nan, 0, inplace=True)\n",
    "df['log_requests_desktop'].replace(np.nan, 0, inplace=True)\n",
    "df['level'].replace(np.nan, 0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create pivot table with total log file requests for each segment\n",
    "total_log_requests = df.pivot_table(index='segment', values=['url', 'log_requests_total', 'log_requests_mobile', 'log_requests_desktop'], aggfunc={'url':len, 'log_requests_total':sum, 'log_requests_mobile':sum, 'log_requests_desktop':sum})\n",
    "total_log_requests['percent_of_total_log_requests']= (total_log_requests['log_requests_total']/total_log_requests['log_requests_total'].sum()).apply('{:.2%}'.format)\n",
    "#Reset Index\n",
    "new_total_log_requests = total_log_requests.reset_index('segment')\n",
    "new_total_log_requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create bar chart for total requests per category\n",
    "sns.barplot(data=new_pivot, x='segment', y='log_requests_total')\n",
    "plt.xticks(rotation=90)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create bar chart for total mobile requests per category\n",
    "sns.barplot(data=new_pivot, x='segment', y='log_requests_mobile')\n",
    "plt.xticks(rotation=90)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create bar chart for total desktop requests per category\n",
    "sns.barplot(data=new_pivot, x='segment', y='log_requests_desktop')\n",
    "plt.xticks(rotation=90)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create pivot table with total log file requests for indexability status \n",
    "indexable_log_file = df.pivot_table(index='indexable', values=['url', 'log_requests_total', 'log_requests_mobile', 'log_requests_desktop'], aggfunc={'url':len, 'log_requests_total':sum, 'log_requests_mobile':sum, 'log_requests_desktop':sum})\n",
    "indexable_log_file['percent_of_total_log_requests']= (indexable_log_file['log_requests_total']/indexable_log_file['log_requests_total'].sum()).apply('{:.2%}'.format)\n",
    "indexable_log_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to create a pivot table with a variable number of indexes\n",
    "\n",
    "def pivot_table(df, indexes):\n",
    "    pivot_table = df.pivot_table(index=indexes, values=['url', 'log_requests_total'], aggfunc={'url':len, 'log_requests_total':sum})\n",
    "\n",
    "    pivot_table = pivot_table.sort_values('log_requests_total', ascending=False)\n",
    "    pivot_table['percent_of_total_log_requests']= (pivot_table['log_requests_total']/pivot_table['log_requests_total'].sum()).apply('{:.2%}'.format)\n",
    "    pivot_table['average_log_requests_per_url']= (pivot_table['log_requests_total']/pivot_table['url']).apply('{:.1f}'.format)\n",
    "    pivot_table['log_requests_total'] = (pivot_table['log_requests_total']).apply('{:,.0f}'.format)\n",
    "    pivot_table['url'] = (pivot_table['url']).apply('{:,}'.format)\n",
    "    pivot_table = pivot_table.rename({'url': 'url_count'}, axis='columns')\n",
    "\n",
    "    return pivot_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create pivot table with total log file requests for status code\n",
    "pivot_status = pivot_table(df, ['category', 'http_status_code'])\n",
    "pivot_status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create pivot table with total log file requests for 200 status code, indexable pages - you can change these to be any status code and either indexable or non-indexable\n",
    "df_200 = df[(df.http_status_code == 200)]\n",
    "\n",
    "pivot_indexable = pivot_table(df_200, ['category', 'indexable'])\n",
    "pivot_indexable"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you would like to see how log file requests have changed over 6 months: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#upload multiple dfs \n",
    "june_df = pd.read_csv(june , usecols=columns)\n",
    "july_df = pd.read_csv(july , usecols=columns)\n",
    "aug_df = pd.read_csv(aug , usecols=columns) \n",
    "sep_df = pd.read_csv(sep , usecols=columns)\n",
    "oct_df = pd.read_csv(oct , usecols=columns)\n",
    "nov_df = pd.read_csv(nov , usecols=columns)\n",
    "\n",
    "june_df['month'] = 'May'\n",
    "july_df['month'] = 'June'\n",
    "aug_df['month'] = 'July'\n",
    "sep_df['month'] = 'August'\n",
    "oct_df['month'] = 'September'\n",
    "nov_df['month'] = 'October'\n",
    "\n",
    "#concatenate dataframes together\n",
    "df_all = pd.concat(objs =[june_df, july_df, aug_df, sep_df, oct_df, nov_df], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create pivot table to store all data\n",
    "def pivot_table(df, indexes):\n",
    "    pivot_table = df.pivot_table(index=indexes, values=['url', 'log_requests_total'], aggfunc={'url':len, 'log_requests_total':sum})\n",
    "\n",
    "    pivot_table = pivot_table.sort_values('log_requests_total', ascending=False)\n",
    "    pivot_table['percent_of_total_log_requests']= (pivot_table['log_requests_total']/pivot_table['log_requests_total'].sum()).apply('{:.2%}'.format)\n",
    "    pivot_table['average_log_requests_per_url']= (pivot_table['log_requests_total']/pivot_table['url']).apply('{:.1f}'.format)\n",
    "    pivot_table['log_requests_total'] = (pivot_table['log_requests_total']).apply('{:,.0f}'.format)\n",
    "    pivot_table['url'] = (pivot_table['url']).apply('{:,}'.format)\n",
    "    pivot_table = pivot_table.rename({'url': 'url_count'}, axis='columns')\n",
    "\n",
    "    return pivot_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create pivot table to get count of log file requests for each month\n",
    "pivot_table_total = df.pivot_table(index='month', values=['url', 'log_requests_total', 'log_requests_mobile', 'log_requests_desktop'], aggfunc={'url':len, 'log_requests_total':sum, 'log_requests_mobile':sum, 'log_requests_desktop':sum})\n",
    "pivot_table_total \n",
    "\n",
    "#reset index to get months in order\n",
    "new_index = ['May', 'June', 'July', 'August', 'September', 'October']\n",
    "new_pivot = pivot_table_total.reindex(new_index)\n",
    "new_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create line graph to show log requests over time \n",
    "sns.lineplot(x = \"month\", y = \"log_requests_total\", data=new_pivot)\n",
    "plt.show()"
   ]
  }
 ],
 "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}