DS101_DataBasics.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "DS101_DataBasics.ipynb",
      "version": "0.3.2",
      "provenance": [],
      "collapsed_sections": [
        "anje91qHZBHD",
        "wAkRkVBoqrgJ",
        "n2eFC2YMq1Wd",
        "awru6Uw2xjDN",
        "-oa-Dxqcx-Rz"
      ],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "metadata": {
        "id": "anje91qHZBHD",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# Get Set Up"
      ]
    },
    {
      "metadata": {
        "id": "hTAlb_J_Zxsp",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Import Libraries"
      ]
    },
    {
      "metadata": {
        "id": "Yu_GGlGJZMJ_",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# Pandas provides an extremely useful data structure\n",
        "import pandas as pd\n",
        "\n",
        "# RE provides regular expression pattern matching\n",
        "import re\n",
        "\n",
        "# datetime provides a datetime object class and conversion utilities\n",
        "from datetime import datetime\n",
        "\n",
        "# Web file access\n",
        "from urllib.request import urlopen\n",
        "\n",
        "\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "CHGVznn_5Z6_",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Define Some Functions"
      ]
    },
    {
      "metadata": {
        "id": "EG3BBk8Q5eF6",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "def log_ReadFile(logfile):\n",
        "  with open(logfile) as fh:\n",
        "    loglines = fh.readlines()\n",
        "  loglines = [line.strip() for line in loglines]\n",
        "  return loglines\n",
        "\n",
        "\n",
        "def log_ReadURL(logfile):\n",
        "  loglines = urlopen(logfile).readlines()\n",
        "  loglines = [line.decode().strip() for line in loglines]\n",
        "  return loglines"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "QR0QirU-WwPq",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# Instructions"
      ]
    },
    {
      "metadata": {
        "id": "h5JbwoF8mhzT",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "The Data Basics lab presents the process of preparing and manipulating data.  Using two datasets, the differences between structured and unstructured data are highlighted.  The basic operations of relational algebra are explored using the Pandas DataFrame class.  An example of text parsing using regular expressions is also provided.\n",
        "\n",
        "* Read through the explanations and examples in each section.\n",
        "\n",
        "* Based on the examples, complete the exercises labeled \"Try it yourself.\"\n",
        "\n",
        "* This lab is intended to develop techniques that will be used throughout the Exploratory Data Analysis lab later on.\n",
        "\n",
        "**To begin, choose \"Runtime -> Run All\" from the menu options.**"
      ]
    },
    {
      "metadata": {
        "id": "wAkRkVBoqrgJ",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# Structured Data: Threat Indicators"
      ]
    },
    {
      "metadata": {
        "id": "BA1-Ea22rzCP",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Prepare Data"
      ]
    },
    {
      "metadata": {
        "id": "jFZNneFmr4hs",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Preparing structured data is relatively easy, as the structure can be relied on to simplify parsing.  Here, we load a prepared collection of malicious IP address indicators into a Pandas DataFrame."
      ]
    },
    {
      "metadata": {
        "id": "2mxWaW3GsMCN",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# URL of source data file\n",
        "intel_url = \"https://raw.githubusercontent.com/urbansec/ds101/master/av_ip_reputation_2019-04-07.csv\"\n",
        "\n",
        "# Column names for data\n",
        "intel_cols = ['ip', 'risk', 'reliability', 'activity', 'country', 'city', 'lat_lon', 'unknown']\n",
        "\n",
        "\n",
        "# Read intel into dataframe\n",
        "intel_df = pd.read_csv(intel_url, sep='#', header=None, names=intel_cols)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "BbQg4nAdwjRf",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Examine Results"
      ]
    },
    {
      "metadata": {
        "id": "192PAyyTwusa",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "The \".head()\" method can be used to preview the first few lines of a data frame."
      ]
    },
    {
      "metadata": {
        "id": "dmfTRSegwnT5",
        "colab_type": "code",
        "outputId": "c17f0333-4766-4bc7-c5fa-f9bb7ddb8a3c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 309
        }
      },
      "cell_type": "code",
      "source": [
        "intel_df.head(5)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>ip</th>\n",
              "      <th>risk</th>\n",
              "      <th>reliability</th>\n",
              "      <th>activity</th>\n",
              "      <th>country</th>\n",
              "      <th>city</th>\n",
              "      <th>lat_lon</th>\n",
              "      <th>unknown</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>46.246.124.162</td>\n",
              "      <td>4</td>\n",
              "      <td>2</td>\n",
              "      <td>Malicious Host</td>\n",
              "      <td>SE</td>\n",
              "      <td>NaN</td>\n",
              "      <td>59.3246994019,18.0559997559</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>23.94.213.6</td>\n",
              "      <td>4</td>\n",
              "      <td>2</td>\n",
              "      <td>Malicious Host</td>\n",
              "      <td>US</td>\n",
              "      <td>Los Angeles</td>\n",
              "      <td>34.0544013977,-118.244003296</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>23.116.185.114</td>\n",
              "      <td>4</td>\n",
              "      <td>2</td>\n",
              "      <td>Malicious Host</td>\n",
              "      <td>US</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.7509994507,-97.8219985962</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>23.30.95.53</td>\n",
              "      <td>4</td>\n",
              "      <td>2</td>\n",
              "      <td>Malicious Host</td>\n",
              "      <td>US</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.7509994507,-97.8219985962</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>14.58.252.223</td>\n",
              "      <td>4</td>\n",
              "      <td>2</td>\n",
              "      <td>Malicious Host</td>\n",
              "      <td>KR</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37.5111999512,126.974098206</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "               ip  risk  reliability        activity country         city  \\\n",
              "0  46.246.124.162     4            2  Malicious Host      SE          NaN   \n",
              "1     23.94.213.6     4            2  Malicious Host      US  Los Angeles   \n",
              "2  23.116.185.114     4            2  Malicious Host      US          NaN   \n",
              "3     23.30.95.53     4            2  Malicious Host      US          NaN   \n",
              "4   14.58.252.223     4            2  Malicious Host      KR          NaN   \n",
              "\n",
              "                        lat_lon unknown  \n",
              "0   59.3246994019,18.0559997559       3  \n",
              "1  34.0544013977,-118.244003296       3  \n",
              "2  37.7509994507,-97.8219985962       3  \n",
              "3  37.7509994507,-97.8219985962       3  \n",
              "4   37.5111999512,126.974098206       3  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 5
        }
      ]
    },
    {
      "metadata": {
        "id": "n2eFC2YMq1Wd",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# Unstructured Data: Web Error Logs"
      ]
    },
    {
      "metadata": {
        "id": "RHJ-2OMwbKpW",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Prepare Data"
      ]
    },
    {
      "metadata": {
        "id": "7dDm116csn8F",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Preparing unstructured data can be difficult.  By its nature, it is often irregular and you will be responsible for determining reliable patterns that can be used during parsing.  This process is shown below for a sample of web server error logs.  Machine logs, while unstructured, tend to be more regular in format than data generated by people.  You will see that we are able to parse these logs into a structured form via automation we write."
      ]
    },
    {
      "metadata": {
        "id": "M7FXhE-jtNnr",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### Download the file to a list"
      ]
    },
    {
      "metadata": {
        "id": "wr_QkeyDbWaB",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# URL of source data file\n",
        "error_url = \"https://raw.githubusercontent.com/urbansec/ds101/master/error.log.2019-03-22\"\n",
        "\n",
        "# Read log files into lists\n",
        "error_logs = log_ReadURL(error_url)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "F62iveOjC_zx",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### Examine the downloaded content"
      ]
    },
    {
      "metadata": {
        "id": "WdX0RUiDC-Wy",
        "colab_type": "code",
        "outputId": "d854388b-f56f-41a3-b467-42d9b05d2fd2",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 54
        }
      },
      "cell_type": "code",
      "source": [
        "# Check our data type\n",
        "type(error_logs)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "list"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 7
        }
      ]
    },
    {
      "metadata": {
        "id": "Z-oS2LE2DwlL",
        "colab_type": "code",
        "outputId": "94102753-dfe2-4f54-cdbd-521dcf542f1c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 54
        }
      },
      "cell_type": "code",
      "source": [
        "# Check data type of lists members\n",
        "type(error_logs[0])"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "str"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "metadata": {
        "id": "9eV9_8H_6Lyg",
        "colab_type": "code",
        "outputId": "4c4d3282-da08-418e-893b-3d33a614b622",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 122
        }
      },
      "cell_type": "code",
      "source": [
        "# Display first 5 lines in list\n",
        "display(error_logs[:5])"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "['[Fri Mar 22 01:58:55 2019] [error] [client 54.36.148.18] File does not exist: /home/sooshie/secrepo.com/self.logs/error.log.2016-04-07.gz',\n",
              " '[Fri Mar 22 02:04:26 2019] [error] [client 54.36.148.62] File does not exist: /home/sooshie/secrepo.com/self.logs/access.log.2016-10-30.gz',\n",
              " '[Fri Mar 22 02:08:27 2019] [error] [client 27.255.4.117] ModSecurity: Access denied with code 418 (phase 1). Pattern match \"^Mozilla/5.0 \\\\\\\\\\\\\\\\(Windows NT 6.1; WOW64; rv:40.0\\\\\\\\\\\\\\\\) Gecko/20100101 Firefox/40.1$\" at REQUEST_HEADERS:User-Agent. [file \"/dh/apache2/template/etc/mod_sec2/99_dreamhost_rules.conf\"] [line \"345\"] [id \"1990098\"] [msg \"Malicious Bot UA\"] [hostname \"www.secrepo.com\"] [uri \"/wp-login.php\"] [unique_id \"XJSmC9BxoAQAACFcAa8AAAAE\"]',\n",
              " '[Fri Mar 22 02:08:28 2019] [error] [client 27.255.4.117] ModSecurity: Access denied with code 418 (phase 1). Pattern match \"^Mozilla/5.0 \\\\\\\\\\\\\\\\(Windows NT 6.1; WOW64; rv:40.0\\\\\\\\\\\\\\\\) Gecko/20100101 Firefox/40.1$\" at REQUEST_HEADERS:User-Agent. [file \"/dh/apache2/template/etc/mod_sec2/99_dreamhost_rules.conf\"] [line \"345\"] [id \"1990098\"] [msg \"Malicious Bot UA\"] [hostname \"www.secrepo.com\"] [uri \"/\"] [unique_id \"XJSmDNBxoAQAABM8BPsAAAAG\"]',\n",
              " '[Fri Mar 22 02:13:49 2019] [error] [client 54.36.149.5] File does not exist: /home/sooshie/secrepo.com/self.logs/access.log.2015-09-07.gz']"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "metadata": {
        "id": "Wl8Nr-pJOlXM",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### Parse the data"
      ]
    },
    {
      "metadata": {
        "id": "ZO5vSqMTRShL",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Regular expressions can be used to flexibly parse each line of the log file into separate fields based on a pattern.\n",
        "For more info on regular expressions, check out the re library documentation\n",
        "https://docs.python.org/3/library/re.html"
      ]
    },
    {
      "metadata": {
        "id": "F8Rv312QJ_GE",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# define a regex pattern to parse lines into fields\n",
        "# sample line:\n",
        "# '[Fri Mar 22 02:13:49 2019] [error] [client 54.36.149.5] File does not exist: /home/sooshie/secrepo.com/self.logs/access.log.2015-09-07.gz']\n",
        "web_error_pattern = re.compile('\\[(?P<date>[^\\]]+)\\]'\n",
        "                                '\\s+\\[(?P<msg_type>[^\\]]+)\\]'\n",
        "                                '\\s+\\[client\\s+(?P<client_ip>[^\\]]+)\\]'\n",
        "                                '\\s+(?P<error_type>[^:]+):'\n",
        "                                '\\s+(?P<error_message>.*)')\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "wiJZM-0KAvyt",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Once the regex parses each field correctly, use it to parse each line.  Add functionality to display lines that are not parsed correctly."
      ]
    },
    {
      "metadata": {
        "id": "YEnbH-ASKKl7",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# create an empty lists to contain results\n",
        "error_logs_parsed = []\n",
        "parse_fails = []\n",
        "\n",
        "# loop through input\n",
        "for line in error_logs:\n",
        "  match = web_error_pattern.match(line)\n",
        "  if match:\n",
        "    # parse fields into matrix\n",
        "    error_logs_parsed.append([match.group('date'), \n",
        "                               match.group('msg_type'),\n",
        "                               match.group('client_ip'), \n",
        "                               match.group('error_type'), \n",
        "                               match.group('error_message')])\n",
        "  else:\n",
        "    # save all failed attempts to a separate list\n",
        "    parse_fails.append(line)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "ulJZYVTHUetJ",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Examine parsed fields, and check for lines that were not parsed successfully."
      ]
    },
    {
      "metadata": {
        "id": "AzUG_9lzUljh",
        "colab_type": "code",
        "outputId": "f5e9a9f9-c256-49fa-a1c0-335b79982c11",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 122
        }
      },
      "cell_type": "code",
      "source": [
        "# Display first parsed line\n",
        "error_logs_parsed[0]"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['Fri Mar 22 01:58:55 2019',\n",
              " 'error',\n",
              " '54.36.148.18',\n",
              " 'File does not exist',\n",
              " '/home/sooshie/secrepo.com/self.logs/error.log.2016-04-07.gz']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "metadata": {
        "id": "Xk2IHDezU1-6",
        "colab_type": "code",
        "outputId": "e02391af-0035-4f1b-af55-dc730178fab6",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 54
        }
      },
      "cell_type": "code",
      "source": [
        "# Display any lines for which parsing failed\n",
        "# should be empty\n",
        "parse_fails"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 13
        }
      ]
    },
    {
      "metadata": {
        "id": "Ry24UFOoX9wA",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### Convert to DataFrame"
      ]
    },
    {
      "metadata": {
        "id": "1IsGiY2Ud6_B",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Examine the contents of our parsed data structure.  It's handy, but could be more efficient.  Convert to a Pandas DataFrame so it's easier to work with."
      ]
    },
    {
      "metadata": {
        "id": "OIpx3NOXGeVZ",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# Define list of columns to use\n",
        "error_column_list = ['date', 'msg_type', 'client_ip', 'error_type', 'error_message']\n",
        "\n",
        "# Convert to Pandas DataFrame and display it\n",
        "error_logs_df = pd.DataFrame.from_records(error_logs_parsed, columns=error_column_list)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "agTepbkpxU70",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Examine Results"
      ]
    },
    {
      "metadata": {
        "id": "tL0Ircu5xYJE",
        "colab_type": "code",
        "outputId": "635adb79-9791-45ee-9d30-e521aa7dd432",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 479
        }
      },
      "cell_type": "code",
      "source": [
        "error_logs_df.head()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>msg_type</th>\n",
              "      <th>client_ip</th>\n",
              "      <th>error_type</th>\n",
              "      <th>error_message</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Fri Mar 22 01:58:55 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.148.18</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/error.log....</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Fri Mar 22 02:04:26 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.148.62</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Fri Mar 22 02:08:27 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>27.255.4.117</td>\n",
              "      <td>ModSecurity</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Fri Mar 22 02:08:28 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>27.255.4.117</td>\n",
              "      <td>ModSecurity</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Fri Mar 22 02:13:49 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.149.5</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                       date msg_type     client_ip           error_type  \\\n",
              "0  Fri Mar 22 01:58:55 2019    error  54.36.148.18  File does not exist   \n",
              "1  Fri Mar 22 02:04:26 2019    error  54.36.148.62  File does not exist   \n",
              "2  Fri Mar 22 02:08:27 2019    error  27.255.4.117          ModSecurity   \n",
              "3  Fri Mar 22 02:08:28 2019    error  27.255.4.117          ModSecurity   \n",
              "4  Fri Mar 22 02:13:49 2019    error   54.36.149.5  File does not exist   \n",
              "\n",
              "                                       error_message  \n",
              "0  /home/sooshie/secrepo.com/self.logs/error.log....  \n",
              "1  /home/sooshie/secrepo.com/self.logs/access.log...  \n",
              "2  Access denied with code 418 (phase 1). Pattern...  \n",
              "3  Access denied with code 418 (phase 1). Pattern...  \n",
              "4  /home/sooshie/secrepo.com/self.logs/access.log...  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "metadata": {
        "id": "x1JOwVYdcOqX",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Check out what data type each column is defined as using DataFrame.dtypes attribute"
      ]
    },
    {
      "metadata": {
        "id": "3ZRPs17LcaDP",
        "colab_type": "code",
        "outputId": "2cf82519-7ecd-4afa-e96d-6d62ab5c4265",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 139
        }
      },
      "cell_type": "code",
      "source": [
        "error_logs_df.dtypes"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "date             object\n",
              "msg_type         object\n",
              "client_ip        object\n",
              "error_type       object\n",
              "error_message    object\n",
              "dtype: object"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 16
        }
      ]
    },
    {
      "metadata": {
        "id": "awru6Uw2xjDN",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# Basic Data Operations"
      ]
    },
    {
      "metadata": {
        "id": "4cNmRMZmOY14",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Subset / Select"
      ]
    },
    {
      "metadata": {
        "id": "3zht0YQMPHpz",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "You can **select** a subset of columns in the following manner."
      ]
    },
    {
      "metadata": {
        "id": "JDV1CvjHPMTm",
        "colab_type": "code",
        "outputId": "92066dcf-b490-4854-c347-5f0e72eb912f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "cell_type": "code",
      "source": [
        "error_logs_df[['client_ip', 'error_message']].head()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>client_ip</th>\n",
              "      <th>error_message</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>54.36.148.18</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/error.log....</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>54.36.148.62</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>27.255.4.117</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>27.255.4.117</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>54.36.149.5</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      client_ip                                      error_message\n",
              "0  54.36.148.18  /home/sooshie/secrepo.com/self.logs/error.log....\n",
              "1  54.36.148.62  /home/sooshie/secrepo.com/self.logs/access.log...\n",
              "2  27.255.4.117  Access denied with code 418 (phase 1). Pattern...\n",
              "3  27.255.4.117  Access denied with code 418 (phase 1). Pattern...\n",
              "4   54.36.149.5  /home/sooshie/secrepo.com/self.logs/access.log..."
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    },
    {
      "metadata": {
        "id": "yn0Kb-DFQC-U",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### Try it yourself:"
      ]
    },
    {
      "metadata": {
        "id": "lh0rS9J8QGW5",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Modify the above command to display only the \"date\", \"msg_type\", and \"error_type\" columns."
      ]
    },
    {
      "metadata": {
        "id": "Zso5oBZ8QXYr",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# Place your code here\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "txNkx9UZZeSG",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "The \".iloc\" attribute can be used to **select** a subset of rows.  Here we use a slice to define a range of rows.  Note that omitting the argument following the comma returns all columns."
      ]
    },
    {
      "metadata": {
        "id": "oe0mV4l4Znuy",
        "colab_type": "code",
        "outputId": "3467c929-f672-4051-cd1a-3564ddb20059",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "cell_type": "code",
      "source": [
        "error_logs_df.iloc[10:15,]"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>msg_type</th>\n",
              "      <th>client_ip</th>\n",
              "      <th>error_type</th>\n",
              "      <th>error_message</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>Fri Mar 22 02:35:30 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>123.129.224.7</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/utility, referer: ht...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>Fri Mar 22 02:35:31 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>123.129.224.7</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/uploads, referer: ht...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>Fri Mar 22 02:35:31 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>123.129.224.7</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/uploads, referer: ht...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>Fri Mar 22 02:45:50 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>92.241.103.84</td>\n",
              "      <td>ModSecurity</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>Fri Mar 22 02:45:50 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>92.241.103.84</td>\n",
              "      <td>ModSecurity</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                        date msg_type      client_ip           error_type  \\\n",
              "10  Fri Mar 22 02:35:30 2019    error  123.129.224.7  File does not exist   \n",
              "11  Fri Mar 22 02:35:31 2019    error  123.129.224.7  File does not exist   \n",
              "12  Fri Mar 22 02:35:31 2019    error  123.129.224.7  File does not exist   \n",
              "13  Fri Mar 22 02:45:50 2019    error  92.241.103.84          ModSecurity   \n",
              "14  Fri Mar 22 02:45:50 2019    error  92.241.103.84          ModSecurity   \n",
              "\n",
              "                                        error_message  \n",
              "10  /home/sooshie/secrepo.com/utility, referer: ht...  \n",
              "11  /home/sooshie/secrepo.com/uploads, referer: ht...  \n",
              "12  /home/sooshie/secrepo.com/uploads, referer: ht...  \n",
              "13  Access denied with code 418 (phase 1). Pattern...  \n",
              "14  Access denied with code 418 (phase 1). Pattern...  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 18
        }
      ]
    },
    {
      "metadata": {
        "id": "R0ENL-Gkg8Zy",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Add a Column"
      ]
    },
    {
      "metadata": {
        "id": "oQlVUbckldsP",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "A new column can be added to a dataframe per the example below.  In this example, each value in the new column will be the same, as is shown in the output.  If we had an additional column (or list) of sufficient length, we could also add that by the same method."
      ]
    },
    {
      "metadata": {
        "id": "Hxyn-CBLlf9r",
        "colab_type": "code",
        "outputId": "30803ea7-0926-4098-fe50-2bbe74bfcc3e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "cell_type": "code",
      "source": [
        "error_logs_df['web_server'] = \"www.secrepo.com\"\n",
        "error_logs_df.head()\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>msg_type</th>\n",
              "      <th>client_ip</th>\n",
              "      <th>error_type</th>\n",
              "      <th>error_message</th>\n",
              "      <th>web_server</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Fri Mar 22 01:58:55 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.148.18</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/error.log....</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Fri Mar 22 02:04:26 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.148.62</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Fri Mar 22 02:08:27 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>27.255.4.117</td>\n",
              "      <td>ModSecurity</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Fri Mar 22 02:08:28 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>27.255.4.117</td>\n",
              "      <td>ModSecurity</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Fri Mar 22 02:13:49 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.149.5</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                       date msg_type     client_ip           error_type  \\\n",
              "0  Fri Mar 22 01:58:55 2019    error  54.36.148.18  File does not exist   \n",
              "1  Fri Mar 22 02:04:26 2019    error  54.36.148.62  File does not exist   \n",
              "2  Fri Mar 22 02:08:27 2019    error  27.255.4.117          ModSecurity   \n",
              "3  Fri Mar 22 02:08:28 2019    error  27.255.4.117          ModSecurity   \n",
              "4  Fri Mar 22 02:13:49 2019    error   54.36.149.5  File does not exist   \n",
              "\n",
              "                                       error_message       web_server  \n",
              "0  /home/sooshie/secrepo.com/self.logs/error.log....  www.secrepo.com  \n",
              "1  /home/sooshie/secrepo.com/self.logs/access.log...  www.secrepo.com  \n",
              "2  Access denied with code 418 (phase 1). Pattern...  www.secrepo.com  \n",
              "3  Access denied with code 418 (phase 1). Pattern...  www.secrepo.com  \n",
              "4  /home/sooshie/secrepo.com/self.logs/access.log...  www.secrepo.com  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 19
        }
      ]
    },
    {
      "metadata": {
        "id": "xwsPmJgKg_St",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Add a Row"
      ]
    },
    {
      "metadata": {
        "id": "RVajy3jMl_XG",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Adding a row is different from adding a column.  The best way to do it is to create a second DataFrame containing only the new row, and then use the \".append()\" method to add it to the bottom of the original DataFrame.\n",
        "\n",
        "Note: It is important that the schema of the DataFrame containing the new row matches the schema of the original DataFrame.  As seen below, we specify matching column names before appending."
      ]
    },
    {
      "metadata": {
        "id": "ZtPTyzRImCkz",
        "colab_type": "code",
        "outputId": "3688065a-e212-4152-d0b2-426b889ac7d9",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "cell_type": "code",
      "source": [
        "# define new row to add as a list\n",
        "new_row = ['2019-03-22 02:13:49', 'test', '201.55.86.182', 'New row added', 'We added a test row.  Did it work?', 'www.secrepo.com']\n",
        "\n",
        "# column names are critical, so define here as a safeguard\n",
        "error_column_list = ['date', 'msg_type', 'client_ip', 'error_type', 'error_message', 'web_server']\n",
        "\n",
        "# convert new row to data frame and append to existing data frame\n",
        "error_logs_df = error_logs_df.append(pd.DataFrame([new_row], columns=error_column_list), ignore_index=True)\n",
        "error_logs_df.tail()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>msg_type</th>\n",
              "      <th>client_ip</th>\n",
              "      <th>error_type</th>\n",
              "      <th>error_message</th>\n",
              "      <th>web_server</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>272</th>\n",
              "      <td>Sat Mar 23 02:00:53 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>122.114.72.221</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/plus, referer: http:...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>273</th>\n",
              "      <td>Sat Mar 23 02:02:36 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.148.99</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/error.log....</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>274</th>\n",
              "      <td>Sat Mar 23 02:05:54 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.149.96</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>275</th>\n",
              "      <td>Sat Mar 23 02:08:08 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.148.231</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>276</th>\n",
              "      <td>2019-03-22 02:13:49</td>\n",
              "      <td>test</td>\n",
              "      <td>201.55.86.182</td>\n",
              "      <td>New row added</td>\n",
              "      <td>We added a test row.  Did it work?</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                         date msg_type       client_ip           error_type  \\\n",
              "272  Sat Mar 23 02:00:53 2019    error  122.114.72.221  File does not exist   \n",
              "273  Sat Mar 23 02:02:36 2019    error    54.36.148.99  File does not exist   \n",
              "274  Sat Mar 23 02:05:54 2019    error    54.36.149.96  File does not exist   \n",
              "275  Sat Mar 23 02:08:08 2019    error   54.36.148.231  File does not exist   \n",
              "276       2019-03-22 02:13:49     test   201.55.86.182        New row added   \n",
              "\n",
              "                                         error_message       web_server  \n",
              "272  /home/sooshie/secrepo.com/plus, referer: http:...  www.secrepo.com  \n",
              "273  /home/sooshie/secrepo.com/self.logs/error.log....  www.secrepo.com  \n",
              "274  /home/sooshie/secrepo.com/self.logs/access.log...  www.secrepo.com  \n",
              "275  /home/sooshie/secrepo.com/self.logs/access.log...  www.secrepo.com  \n",
              "276                 We added a test row.  Did it work?  www.secrepo.com  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 20
        }
      ]
    },
    {
      "metadata": {
        "id": "aCSFXVXwIS0W",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Rename a Column"
      ]
    },
    {
      "metadata": {
        "id": "8IpfNSEPJ4eK",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Columns can be renamed using the \".rename()\" method.  This is often useful when preparing data.  When used in a database, this is part of modifying the schema."
      ]
    },
    {
      "metadata": {
        "id": "wSHLSqpCIWAV",
        "colab_type": "code",
        "outputId": "7598e77f-f0fb-4780-8fbc-74c4ef0ae72a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "cell_type": "code",
      "source": [
        "error_logs_df.rename(columns={'web_server':'server_name'}, inplace=True)\n",
        "error_logs_df.head()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>msg_type</th>\n",
              "      <th>client_ip</th>\n",
              "      <th>error_type</th>\n",
              "      <th>error_message</th>\n",
              "      <th>server_name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Fri Mar 22 01:58:55 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.148.18</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/error.log....</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Fri Mar 22 02:04:26 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.148.62</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Fri Mar 22 02:08:27 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>27.255.4.117</td>\n",
              "      <td>ModSecurity</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Fri Mar 22 02:08:28 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>27.255.4.117</td>\n",
              "      <td>ModSecurity</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Fri Mar 22 02:13:49 2019</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.149.5</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                       date msg_type     client_ip           error_type  \\\n",
              "0  Fri Mar 22 01:58:55 2019    error  54.36.148.18  File does not exist   \n",
              "1  Fri Mar 22 02:04:26 2019    error  54.36.148.62  File does not exist   \n",
              "2  Fri Mar 22 02:08:27 2019    error  27.255.4.117          ModSecurity   \n",
              "3  Fri Mar 22 02:08:28 2019    error  27.255.4.117          ModSecurity   \n",
              "4  Fri Mar 22 02:13:49 2019    error   54.36.149.5  File does not exist   \n",
              "\n",
              "                                       error_message      server_name  \n",
              "0  /home/sooshie/secrepo.com/self.logs/error.log....  www.secrepo.com  \n",
              "1  /home/sooshie/secrepo.com/self.logs/access.log...  www.secrepo.com  \n",
              "2  Access denied with code 418 (phase 1). Pattern...  www.secrepo.com  \n",
              "3  Access denied with code 418 (phase 1). Pattern...  www.secrepo.com  \n",
              "4  /home/sooshie/secrepo.com/self.logs/access.log...  www.secrepo.com  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 21
        }
      ]
    },
    {
      "metadata": {
        "id": "NllxWgB9hByv",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Transform a Column"
      ]
    },
    {
      "metadata": {
        "id": "HWHOxnxIkS78",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Transforming an existing column is similar to adding a new column.  In this case, the old column is replaced with a new column of the same name, containing the transformed values."
      ]
    },
    {
      "metadata": {
        "id": "7u7is1BBkh-Q",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "This could be useful for many things including rounding numerical values, substituting string values with shortened or normalized values, or replacing values with a simple Boolean.\n",
        "\n",
        "Here, we modify the \"date\" column to use a python datetime object instead of a string.  This could be seen as a schema modification as well, as we are changing the data type of the column."
      ]
    },
    {
      "metadata": {
        "id": "wcfObuf-k4Tv",
        "colab_type": "code",
        "outputId": "61ce7b93-3185-49ce-d0be-a47505cd33cb",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "cell_type": "code",
      "source": [
        "error_logs_df['date'] = pd.to_datetime(error_logs_df['date'])\n",
        "error_logs_df.head()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>msg_type</th>\n",
              "      <th>client_ip</th>\n",
              "      <th>error_type</th>\n",
              "      <th>error_message</th>\n",
              "      <th>server_name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2019-03-22 01:58:55</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.148.18</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/error.log....</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2019-03-22 02:04:26</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.148.62</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2019-03-22 02:08:27</td>\n",
              "      <td>error</td>\n",
              "      <td>27.255.4.117</td>\n",
              "      <td>ModSecurity</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2019-03-22 02:08:28</td>\n",
              "      <td>error</td>\n",
              "      <td>27.255.4.117</td>\n",
              "      <td>ModSecurity</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2019-03-22 02:13:49</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.149.5</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                 date msg_type     client_ip           error_type  \\\n",
              "0 2019-03-22 01:58:55    error  54.36.148.18  File does not exist   \n",
              "1 2019-03-22 02:04:26    error  54.36.148.62  File does not exist   \n",
              "2 2019-03-22 02:08:27    error  27.255.4.117          ModSecurity   \n",
              "3 2019-03-22 02:08:28    error  27.255.4.117          ModSecurity   \n",
              "4 2019-03-22 02:13:49    error   54.36.149.5  File does not exist   \n",
              "\n",
              "                                       error_message      server_name  \n",
              "0  /home/sooshie/secrepo.com/self.logs/error.log....  www.secrepo.com  \n",
              "1  /home/sooshie/secrepo.com/self.logs/access.log...  www.secrepo.com  \n",
              "2  Access denied with code 418 (phase 1). Pattern...  www.secrepo.com  \n",
              "3  Access denied with code 418 (phase 1). Pattern...  www.secrepo.com  \n",
              "4  /home/sooshie/secrepo.com/self.logs/access.log...  www.secrepo.com  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 22
        }
      ]
    },
    {
      "metadata": {
        "id": "-oa-Dxqcx-Rz",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# More Interesting Stuff"
      ]
    },
    {
      "metadata": {
        "id": "Yq0aUoKIxzTI",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Select Where (condition is met)"
      ]
    },
    {
      "metadata": {
        "id": "-ZxacYW8YVhP",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Using criteria to select only certain rows allows you to search  and filter your data set.  This is one of the most useful basic data functions."
      ]
    },
    {
      "metadata": {
        "id": "nsEihMUmZMol",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "The example below shows how to search this dataset for a specific IP."
      ]
    },
    {
      "metadata": {
        "id": "S3FEAuBVOiIu",
        "colab_type": "code",
        "outputId": "3f1489fe-6f65-4ae1-94cf-8640c5b03126",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        }
      },
      "cell_type": "code",
      "source": [
        "# Search for 54.36.150.75\n",
        "error_logs_df.loc[error_logs_df['client_ip'] == '54.36.150.75',]"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>msg_type</th>\n",
              "      <th>client_ip</th>\n",
              "      <th>error_type</th>\n",
              "      <th>error_message</th>\n",
              "      <th>server_name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>201</th>\n",
              "      <td>2019-03-22 16:06:14</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.150.75</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/error.log....</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                   date msg_type     client_ip           error_type  \\\n",
              "201 2019-03-22 16:06:14    error  54.36.150.75  File does not exist   \n",
              "\n",
              "                                         error_message      server_name  \n",
              "201  /home/sooshie/secrepo.com/self.logs/error.log....  www.secrepo.com  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 23
        }
      ]
    },
    {
      "metadata": {
        "id": "h5gP2KzNZTnP",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "You can search for text within fields in a similar way (it's not necessary to do an exact '==' match).  We could also search for specific IP subnets or other factors.  We can even combine multiple criteria.\n",
        "\n",
        "Here we search for errors containing the word \"admin\" from the subnet \"222.186.x.x\".  "
      ]
    },
    {
      "metadata": {
        "id": "ZpK7dDNVb6vR",
        "colab_type": "code",
        "outputId": "631ac418-8f4f-47c2-d773-38dd730d1a54",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        }
      },
      "cell_type": "code",
      "source": [
        "error_logs_df[error_logs_df.error_message.str.contains(\"admin\") & error_logs_df.client_ip.str.startswith(\"222.186\")]"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>msg_type</th>\n",
              "      <th>client_ip</th>\n",
              "      <th>error_type</th>\n",
              "      <th>error_message</th>\n",
              "      <th>server_name</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>36</th>\n",
              "      <td>2019-03-22 03:55:33</td>\n",
              "      <td>error</td>\n",
              "      <td>222.186.160.61</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/admin, referer: http...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>54</th>\n",
              "      <td>2019-03-22 04:12:45</td>\n",
              "      <td>error</td>\n",
              "      <td>222.186.59.44</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/admin, referer: http...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>153</th>\n",
              "      <td>2019-03-22 13:05:36</td>\n",
              "      <td>error</td>\n",
              "      <td>222.186.57.109</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/admin, referer: http...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                   date msg_type       client_ip           error_type  \\\n",
              "36  2019-03-22 03:55:33    error  222.186.160.61  File does not exist   \n",
              "54  2019-03-22 04:12:45    error   222.186.59.44  File does not exist   \n",
              "153 2019-03-22 13:05:36    error  222.186.57.109  File does not exist   \n",
              "\n",
              "                                         error_message      server_name  \n",
              "36   /home/sooshie/secrepo.com/admin, referer: http...  www.secrepo.com  \n",
              "54   /home/sooshie/secrepo.com/admin, referer: http...  www.secrepo.com  \n",
              "153  /home/sooshie/secrepo.com/admin, referer: http...  www.secrepo.com  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 24
        }
      ]
    },
    {
      "metadata": {
        "id": "wLzrAHL0NjI8",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### Try it yourself:"
      ]
    },
    {
      "metadata": {
        "id": "8zGllqysNsQf",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Modify the search above to return rows with the text \"Access denied\" in the \"error_message\" field.  Don't include any criteria for the \"client_ip\" field.\n",
        "\n",
        "Note: this is a case sensitive string match, so make sure the \"A\" is capitalized."
      ]
    },
    {
      "metadata": {
        "id": "IkP_s-P2O5zJ",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# Put your code here\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "gYjkEo_MfH65",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Join On (index field)"
      ]
    },
    {
      "metadata": {
        "id": "ktXGC9qgfKcW",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Join operations allow you to combine two datasets based on a shared piece of information.  In data science, this is often used to add context to the base data set.  Here, we can join our threat indicator data with out error log data to see if any errors were generated by known bad IPs."
      ]
    },
    {
      "metadata": {
        "id": "8kN1Vvl_fokH",
        "colab_type": "code",
        "outputId": "f2ab7e36-b37f-4b26-af79-684bf570a11c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 394
        }
      },
      "cell_type": "code",
      "source": [
        "joined_data = error_logs_df.join(intel_df.set_index('ip'), on='client_ip')\n",
        "joined_data.head()"
      ],
      "execution_count": 32,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>msg_type</th>\n",
              "      <th>client_ip</th>\n",
              "      <th>error_type</th>\n",
              "      <th>error_message</th>\n",
              "      <th>server_name</th>\n",
              "      <th>risk</th>\n",
              "      <th>reliability</th>\n",
              "      <th>activity</th>\n",
              "      <th>country</th>\n",
              "      <th>city</th>\n",
              "      <th>lat_lon</th>\n",
              "      <th>unknown</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2019-03-22 01:58:55</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.148.18</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/error.log....</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2019-03-22 02:04:26</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.148.62</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2019-03-22 02:08:27</td>\n",
              "      <td>error</td>\n",
              "      <td>27.255.4.117</td>\n",
              "      <td>ModSecurity</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2019-03-22 02:08:28</td>\n",
              "      <td>error</td>\n",
              "      <td>27.255.4.117</td>\n",
              "      <td>ModSecurity</td>\n",
              "      <td>Access denied with code 418 (phase 1). Pattern...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2019-03-22 02:13:49</td>\n",
              "      <td>error</td>\n",
              "      <td>54.36.149.5</td>\n",
              "      <td>File does not exist</td>\n",
              "      <td>/home/sooshie/secrepo.com/self.logs/access.log...</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                 date msg_type     client_ip           error_type  \\\n",
              "0 2019-03-22 01:58:55    error  54.36.148.18  File does not exist   \n",
              "1 2019-03-22 02:04:26    error  54.36.148.62  File does not exist   \n",
              "2 2019-03-22 02:08:27    error  27.255.4.117          ModSecurity   \n",
              "3 2019-03-22 02:08:28    error  27.255.4.117          ModSecurity   \n",
              "4 2019-03-22 02:13:49    error   54.36.149.5  File does not exist   \n",
              "\n",
              "                                       error_message      server_name  risk  \\\n",
              "0  /home/sooshie/secrepo.com/self.logs/error.log....  www.secrepo.com   NaN   \n",
              "1  /home/sooshie/secrepo.com/self.logs/access.log...  www.secrepo.com   NaN   \n",
              "2  Access denied with code 418 (phase 1). Pattern...  www.secrepo.com   NaN   \n",
              "3  Access denied with code 418 (phase 1). Pattern...  www.secrepo.com   NaN   \n",
              "4  /home/sooshie/secrepo.com/self.logs/access.log...  www.secrepo.com   NaN   \n",
              "\n",
              "   reliability activity country city lat_lon unknown  \n",
              "0          NaN      NaN     NaN  NaN     NaN     NaN  \n",
              "1          NaN      NaN     NaN  NaN     NaN     NaN  \n",
              "2          NaN      NaN     NaN  NaN     NaN     NaN  \n",
              "3          NaN      NaN     NaN  NaN     NaN     NaN  \n",
              "4          NaN      NaN     NaN  NaN     NaN     NaN  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 32
        }
      ]
    },
    {
      "metadata": {
        "id": "VAxhnhf5f9HU",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "The columns from the threat indicator data frame appear with those of the error log data frame.  For IPs that did not match a malicious IP, the values of the new columns are set to \"NaN\", which is like a null value."
      ]
    },
    {
      "metadata": {
        "id": "6hU-imrPgT1Y",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "We can search for values that are not NaN to find any matches that exist.  In this case, only our test row should match."
      ]
    },
    {
      "metadata": {
        "id": "tnvECFkggbzz",
        "colab_type": "code",
        "outputId": "c857cdcc-9bca-4d5b-846b-4d74618aae9d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 134
        }
      },
      "cell_type": "code",
      "source": [
        "joined_data.loc[pd.notna(joined_data['activity']),]"
      ],
      "execution_count": 33,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>msg_type</th>\n",
              "      <th>client_ip</th>\n",
              "      <th>error_type</th>\n",
              "      <th>error_message</th>\n",
              "      <th>server_name</th>\n",
              "      <th>risk</th>\n",
              "      <th>reliability</th>\n",
              "      <th>activity</th>\n",
              "      <th>country</th>\n",
              "      <th>city</th>\n",
              "      <th>lat_lon</th>\n",
              "      <th>unknown</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>276</th>\n",
              "      <td>2019-03-22 02:13:49</td>\n",
              "      <td>test</td>\n",
              "      <td>201.55.86.182</td>\n",
              "      <td>New row added</td>\n",
              "      <td>We added a test row.  Did it work?</td>\n",
              "      <td>www.secrepo.com</td>\n",
              "      <td>4.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>Malicious Host</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>-23.5,-47.4595985413</td>\n",
              "      <td>3</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                   date msg_type      client_ip     error_type  \\\n",
              "276 2019-03-22 02:13:49     test  201.55.86.182  New row added   \n",
              "\n",
              "                          error_message      server_name  risk  reliability  \\\n",
              "276  We added a test row.  Did it work?  www.secrepo.com   4.0          2.0   \n",
              "\n",
              "           activity country city               lat_lon unknown  \n",
              "276  Malicious Host     NaN  NaN  -23.5,-47.4595985413       3  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 33
        }
      ]
    }
  ]
}