ch_11/0-simulating_the_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Simulating the data\n",
    "Before we go into some exploratory data analysis, let's see how we simulated the data:\n",
    "\n",
    "```\n",
    "# example of how to run this simulation from jupyter (remove the ! to run from the command line)\n",
    "!python3 simulate.py -s 0 --stealthy -l logs/jan_2018.csv -hl logs/hackers_jan_2018.csv 31 \"2018-01-01\" 0.01 0.5\n",
    "```\n",
    "\n",
    "Each month was simulated on its own with a different set of parameters:\n",
    "\n",
    "|| Jan 2018 | Feb 2018 | Mar 2018 | Apr 2018 | May 2018 | Jun 2018 | Jul 2018 | Aug 2018 | Sep 2018 | Oct 2018 | Nov 2018 | Dec 2018 | Jan 2019 | Feb 2019 | Mar 2019 |\n",
    "| --- | :---: | :---: | :---: | :---: | :---: | :---: | :---: | :---: | :---: | :---: | :---: | :---: | :---: | :---: | :---: | \n",
    "| Probability of attack in a given hour | 1.00% | 0.50% | 0.10% | 1.00% | 0.01% | 0.05% | 1.00% | 0.50% | 0.50% | 0.20% | 0.70% | 1.00% | 0.80% | 0.20% | 1.00% |\n",
    "| Probability of trying entire user base | 50% | 25% | 10% | 65% | 5% | 5% | 15% | 10% | 10% | 12% | 17% | 88% | 8% | 18% | 18% |\n",
    "| Vary IP addresses? | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | No | No | Yes | Yes | Yes | Yes | Yes |\n",
    "\n",
    "We use pandas to combine the files by year. First, we create a utility function for concatenating the files:\n",
    "\n",
    "```python\n",
    "import pandas as pd\n",
    "\n",
    "def cat_csvs(format_string_file_pattern, index_col, month_list):\n",
    "    \"\"\"\n",
    "    Utility function for concatentating CSV files from simulation.\n",
    "    \n",
    "    Parameters: \n",
    "        - format_string_file_pattern: The pattern for the file name with `{}` in the place of the month\n",
    "        - index_col: The column with the datetimes to sort on.\n",
    "        - month_list: The list of the months as formatted in the file names.\n",
    "    \n",
    "    Returns:\n",
    "        A concatenated `pandas.DataFrame`\n",
    "    \"\"\"\n",
    "    return pd.concat([\n",
    "        pd.read_csv(\n",
    "            format_string_file_pattern.format(file), index_col=index_col, parse_dates=True\n",
    "        ) for file in month_list\n",
    "    ]).sort_index()\n",
    "```\n",
    "\n",
    "and a second utility function to handle any spillover from one simulation to the next:\n",
    "```python\n",
    "def get_spillover(data, when):\n",
    "    \"\"\"Returns data from spillover\"\"\"\n",
    "    try:\n",
    "        return data.loc[when]\n",
    "    except KeyError:\n",
    "        return pd.DataFrame()\n",
    "```\n",
    "\n",
    "Next, we concatenate the 2018 logs making sure to not record any data from early January 1, 2019 which may have been generated from the Poisson process in December 2018:\n",
    "```python\n",
    "logs_2018 = cat_csvs(\n",
    "    'logs/{}_2018.csv', 'datetime', \n",
    "    ['jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec']\n",
    ")\n",
    "logs_2018.loc['2018'].to_csv('logs/logs_2018.csv') # sometimes the simulation overshoots the end date\n",
    "```\n",
    "\n",
    "Now, we concatenate the 2019 logs remembering to add back the 2019 entries that got into the December 2018 simulation and clip the April 2019 entries from the March simulation:\n",
    "```python\n",
    "logs_2019 = pd.concat([\n",
    "    cat_csvs('logs/{}_2019.csv', 'datetime', ['jan', 'feb', 'mar']), \n",
    "    get_spillover(logs_2018, '2019')\n",
    "]).sort_index()\n",
    "logs_2019.loc['2019-Q1'].to_csv('logs/logs_2019.csv') # sometimes the simulation overshoots the end date\n",
    "```\n",
    "\n",
    "After we have the login attempts logs, we concatenate the 2018 hacker logs:\n",
    "```python\n",
    "hackers_2018 = cat_csvs(\n",
    "    'logs/hackers_{}_2018.csv', 'start', \n",
    "    ['jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec']\n",
    ")\n",
    "hackers_2018.loc['2018'].to_csv('logs/hackers_2018.csv')\n",
    "```\n",
    "\n",
    "Concatenating the 2019 hacker logs is the same process:\n",
    "```python\n",
    "hackers_2019 = pd.concat([\n",
    "    cat_csvs('logs/hackers_{}_2019.csv', 'start', ['jan', 'feb', 'mar']), get_spillover(hackers_2018, '2019')\n",
    "]).sort_index()\n",
    "hackers_2019.loc['2019-Q1'].to_csv('logs/hackers_2019.csv')\n",
    "```\n",
    "\n",
    "The process of building the CSV files from the individual simulations is contained in `merge_logs.py` and the entire process is in the bash script `run_simulations.sh`. You don't have to run either of these.\n",
    "\n",
    "# Create SQLite Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# read in files\n",
    "logs_2018 = pd.read_csv('logs/logs_2018.csv', index_col='datetime')\n",
    "logs_2019 = pd.read_csv('logs/logs_2019.csv', index_col='datetime')\n",
    "hackers_2018 = pd.read_csv('logs/hackers_2018.csv', index_col='start')\n",
    "hackers_2019 = pd.read_csv('logs/hackers_2019.csv', index_col='start')\n",
    "\n",
    "# write to database\n",
    "with sqlite3.connect('logs/logs.db') as conn:\n",
    "    logs_2018.to_sql('logs', conn, if_exists='replace')\n",
    "    logs_2019.to_sql('logs', conn, if_exists='append')\n",
    "    hackers_2018.to_sql('attacks', conn, if_exists='replace')\n",
    "    hackers_2019.to_sql('attacks', conn, if_exists='append')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<div style=\"overflow: hidden; margin-bottom: 10px;\">\n",
    "    <div style=\"float: left;\">\n",
    "        <a href=\"../../ch_10/red_wine.ipynb\">\n",
    "            <button>&#8592; Chapter 10</button>\n",
    "        </a>\n",
    "    </div>\n",
    "    <div style=\"float: right;\">\n",
    "        <a href=\"./1-EDA_unlabeled_data.ipynb\">\n",
    "            <button>Next Notebook &#8594;</button>\n",
    "        </a>\n",
    "    </div>\n",
    "</div>\n",
    "<hr>"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}