IoT3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# IoT Project"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### We are going to load up 2 databases  \n",
    "#### Organization Unique Identifier (OUI)\n",
    "\n",
    "OUI is a unique identifier per organization\n",
    "The first 6 alphanumerical characters of the MAC address for each device is the OUI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "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>Registry</th>\n",
       "      <th>Assignment</th>\n",
       "      <th>Organization Name</th>\n",
       "      <th>Organization Address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MA-L</td>\n",
       "      <td>E043DB</td>\n",
       "      <td>Shenzhen ViewAt Technology Co.,Ltd.</td>\n",
       "      <td>9A,Microprofit,6th Gaoxin South Road, High-Tec...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MA-L</td>\n",
       "      <td>2405F5</td>\n",
       "      <td>Integrated Device Technology (Malaysia) Sdn. Bhd.</td>\n",
       "      <td>Phase 3, Bayan Lepas FIZ Bayan Lepas Penang MY...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MA-L</td>\n",
       "      <td>3CD92B</td>\n",
       "      <td>Hewlett Packard</td>\n",
       "      <td>11445 Compaq Center Drive Houston  US 77070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MA-L</td>\n",
       "      <td>9C8E99</td>\n",
       "      <td>Hewlett Packard</td>\n",
       "      <td>11445 Compaq Center Drive Houston  US 77070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MA-L</td>\n",
       "      <td>B499BA</td>\n",
       "      <td>Hewlett Packard</td>\n",
       "      <td>11445 Compaq Center Drive Houston  US 77070</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Registry Assignment                                  Organization Name  \\\n",
       "0     MA-L     E043DB               Shenzhen ViewAt Technology Co.,Ltd.    \n",
       "1     MA-L     2405F5  Integrated Device Technology (Malaysia) Sdn. Bhd.   \n",
       "2     MA-L     3CD92B                                    Hewlett Packard   \n",
       "3     MA-L     9C8E99                                    Hewlett Packard   \n",
       "4     MA-L     B499BA                                    Hewlett Packard   \n",
       "\n",
       "                                Organization Address  \n",
       "0  9A,Microprofit,6th Gaoxin South Road, High-Tec...  \n",
       "1  Phase 3, Bayan Lepas FIZ Bayan Lepas Penang MY...  \n",
       "2       11445 Compaq Center Drive Houston  US 77070   \n",
       "3       11445 Compaq Center Drive Houston  US 77070   \n",
       "4       11445 Compaq Center Drive Houston  US 77070   "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from collections import defaultdict\n",
    "from scapy.all import *\n",
    "from IPy import IP as IPy\n",
    "import pprint\n",
    "\n",
    "\n",
    "OUI_df = pd.read_csv('oui.csv')\n",
    "OUI_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Internet IP address <-> Organization mapping"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note:   \n",
    "you need to download this file (large) to your local directory!  \n",
    "https://iptoasn.com/data/ip2asn-combined.tsv.gz   \n",
    "and unzip it (so you have a local copy of ip2asn-combined.tsv)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "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>start</th>\n",
       "      <th>end</th>\n",
       "      <th>asn</th>\n",
       "      <th>country</th>\n",
       "      <th>organization</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0.0.1</td>\n",
       "      <td>0.255.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0.0.0</td>\n",
       "      <td>9.255.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.0.0.0</td>\n",
       "      <td>10.255.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11.0.0.0</td>\n",
       "      <td>100.63.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100.64.0.0</td>\n",
       "      <td>100.127.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>100.128.0.0</td>\n",
       "      <td>126.255.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>127.0.0.0</td>\n",
       "      <td>127.255.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>128.0.0.0</td>\n",
       "      <td>169.253.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>169.254.0.0</td>\n",
       "      <td>169.254.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>169.255.0.0</td>\n",
       "      <td>172.15.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>172.16.0.0</td>\n",
       "      <td>172.16.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>172.17.0.0</td>\n",
       "      <td>191.255.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>192.0.0.0</td>\n",
       "      <td>192.0.0.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>192.0.1.0</td>\n",
       "      <td>192.0.1.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>192.0.2.0</td>\n",
       "      <td>192.0.2.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>192.0.3.0</td>\n",
       "      <td>192.17.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>192.18.0.0</td>\n",
       "      <td>192.19.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>192.20.0.0</td>\n",
       "      <td>192.88.98.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>192.88.99.0</td>\n",
       "      <td>192.88.99.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>192.88.100.0</td>\n",
       "      <td>192.167.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>192.168.0.0</td>\n",
       "      <td>192.168.25.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>192.168.26.0</td>\n",
       "      <td>198.51.99.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>198.51.100.0</td>\n",
       "      <td>198.51.100.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>198.51.101.0</td>\n",
       "      <td>203.0.112.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>203.0.113.0</td>\n",
       "      <td>203.0.113.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>203.0.114.0</td>\n",
       "      <td>223.255.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>224.0.0.0</td>\n",
       "      <td>239.255.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>240.0.0.0</td>\n",
       "      <td>255.255.255.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1.0.1.0</td>\n",
       "      <td>1.0.3.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1.0.4.0</td>\n",
       "      <td>1.0.4.255</td>\n",
       "      <td>56203</td>\n",
       "      <td>AU</td>\n",
       "      <td>GTELECOM-AUSTRALIA Gtelecom-AUSTRALIA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>1.0.220.0</td>\n",
       "      <td>1.0.220.255</td>\n",
       "      <td>23969</td>\n",
       "      <td>TH</td>\n",
       "      <td>TOT-NET TOT Public Company Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>1.0.221.0</td>\n",
       "      <td>1.0.221.255</td>\n",
       "      <td>23969</td>\n",
       "      <td>TH</td>\n",
       "      <td>TOT-NET TOT Public Company Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>1.0.222.0</td>\n",
       "      <td>1.0.222.255</td>\n",
       "      <td>23969</td>\n",
       "      <td>TH</td>\n",
       "      <td>TOT-NET TOT Public Company Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>1.0.223.0</td>\n",
       "      <td>1.0.223.255</td>\n",
       "      <td>23969</td>\n",
       "      <td>TH</td>\n",
       "      <td>TOT-NET TOT Public Company Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>1.0.224.0</td>\n",
       "      <td>1.0.239.255</td>\n",
       "      <td>23969</td>\n",
       "      <td>TH</td>\n",
       "      <td>TOT-NET TOT Public Company Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>1.0.240.0</td>\n",
       "      <td>1.0.247.255</td>\n",
       "      <td>23969</td>\n",
       "      <td>TH</td>\n",
       "      <td>TOT-NET TOT Public Company Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>1.0.248.0</td>\n",
       "      <td>1.0.255.255</td>\n",
       "      <td>23969</td>\n",
       "      <td>TH</td>\n",
       "      <td>TOT-NET TOT Public Company Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>1.1.0.0</td>\n",
       "      <td>1.1.0.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>1.1.1.0</td>\n",
       "      <td>1.1.1.255</td>\n",
       "      <td>13335</td>\n",
       "      <td>US</td>\n",
       "      <td>CLOUDFLARENET - Cloudflare, Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>1.1.2.0</td>\n",
       "      <td>1.1.7.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>1.1.8.0</td>\n",
       "      <td>1.1.8.255</td>\n",
       "      <td>4134</td>\n",
       "      <td>CN</td>\n",
       "      <td>CHINANET-BACKBONE No.31,Jin-rong Street</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>1.1.9.0</td>\n",
       "      <td>1.1.19.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>1.1.20.0</td>\n",
       "      <td>1.1.20.255</td>\n",
       "      <td>133948</td>\n",
       "      <td>HK</td>\n",
       "      <td>DIL-AS-AP DONGFONG INC LIMITED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>1.1.21.0</td>\n",
       "      <td>1.1.63.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>1.1.64.0</td>\n",
       "      <td>1.1.95.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>1.1.96.0</td>\n",
       "      <td>1.1.102.255</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>Not routed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>1.1.103.0</td>\n",
       "      <td>1.1.103.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>1.1.104.0</td>\n",
       "      <td>1.1.104.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>1.1.105.0</td>\n",
       "      <td>1.1.105.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>1.1.106.0</td>\n",
       "      <td>1.1.106.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>1.1.107.0</td>\n",
       "      <td>1.1.107.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>1.1.108.0</td>\n",
       "      <td>1.1.108.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>1.1.109.0</td>\n",
       "      <td>1.1.109.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>1.1.110.0</td>\n",
       "      <td>1.1.110.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>1.1.111.0</td>\n",
       "      <td>1.1.111.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>1.1.112.0</td>\n",
       "      <td>1.1.112.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>1.1.113.0</td>\n",
       "      <td>1.1.113.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>1.1.114.0</td>\n",
       "      <td>1.1.114.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>1.1.115.0</td>\n",
       "      <td>1.1.115.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>1.1.116.0</td>\n",
       "      <td>1.1.116.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           start              end     asn  country  \\\n",
       "0        0.0.0.1    0.255.255.255       0     None   \n",
       "1        1.0.0.0    9.255.255.255       0     None   \n",
       "2       10.0.0.0   10.255.255.255       0     None   \n",
       "3       11.0.0.0   100.63.255.255       0     None   \n",
       "4     100.64.0.0  100.127.255.255       0     None   \n",
       "5    100.128.0.0  126.255.255.255       0     None   \n",
       "6      127.0.0.0  127.255.255.255       0     None   \n",
       "7      128.0.0.0  169.253.255.255       0     None   \n",
       "8    169.254.0.0  169.254.255.255       0     None   \n",
       "9    169.255.0.0   172.15.255.255       0     None   \n",
       "10    172.16.0.0   172.16.255.255       0     None   \n",
       "11    172.17.0.0  191.255.255.255       0     None   \n",
       "12     192.0.0.0      192.0.0.255       0     None   \n",
       "13     192.0.1.0      192.0.1.255       0     None   \n",
       "14     192.0.2.0      192.0.2.255       0     None   \n",
       "15     192.0.3.0   192.17.255.255       0     None   \n",
       "16    192.18.0.0   192.19.255.255       0     None   \n",
       "17    192.20.0.0    192.88.98.255       0     None   \n",
       "18   192.88.99.0    192.88.99.255       0     None   \n",
       "19  192.88.100.0  192.167.255.255       0     None   \n",
       "20   192.168.0.0   192.168.25.255       0     None   \n",
       "21  192.168.26.0    198.51.99.255       0     None   \n",
       "22  198.51.100.0   198.51.100.255       0     None   \n",
       "23  198.51.101.0    203.0.112.255       0     None   \n",
       "24   203.0.113.0    203.0.113.255       0     None   \n",
       "25   203.0.114.0  223.255.255.255       0     None   \n",
       "26     224.0.0.0  239.255.255.255       0     None   \n",
       "27     240.0.0.0  255.255.255.255       0     None   \n",
       "28       1.0.1.0        1.0.3.255       0     None   \n",
       "29       1.0.4.0        1.0.4.255   56203       AU   \n",
       "..           ...              ...     ...      ...   \n",
       "70     1.0.220.0      1.0.220.255   23969       TH   \n",
       "71     1.0.221.0      1.0.221.255   23969       TH   \n",
       "72     1.0.222.0      1.0.222.255   23969       TH   \n",
       "73     1.0.223.0      1.0.223.255   23969       TH   \n",
       "74     1.0.224.0      1.0.239.255   23969       TH   \n",
       "75     1.0.240.0      1.0.247.255   23969       TH   \n",
       "76     1.0.248.0      1.0.255.255   23969       TH   \n",
       "77       1.1.0.0        1.1.0.255       0     None   \n",
       "78       1.1.1.0        1.1.1.255   13335       US   \n",
       "79       1.1.2.0        1.1.7.255       0     None   \n",
       "80       1.1.8.0        1.1.8.255    4134       CN   \n",
       "81       1.1.9.0       1.1.19.255       0     None   \n",
       "82      1.1.20.0       1.1.20.255  133948       HK   \n",
       "83      1.1.21.0       1.1.63.255       0     None   \n",
       "84      1.1.64.0       1.1.95.255    2519  Unknown   \n",
       "85      1.1.96.0      1.1.102.255       0     None   \n",
       "86     1.1.103.0      1.1.103.255    2519  Unknown   \n",
       "87     1.1.104.0      1.1.104.255    2519  Unknown   \n",
       "88     1.1.105.0      1.1.105.255    2519  Unknown   \n",
       "89     1.1.106.0      1.1.106.255    2519  Unknown   \n",
       "90     1.1.107.0      1.1.107.255    2519  Unknown   \n",
       "91     1.1.108.0      1.1.108.255    2519  Unknown   \n",
       "92     1.1.109.0      1.1.109.255    2519  Unknown   \n",
       "93     1.1.110.0      1.1.110.255    2519  Unknown   \n",
       "94     1.1.111.0      1.1.111.255    2519  Unknown   \n",
       "95     1.1.112.0      1.1.112.255    2519  Unknown   \n",
       "96     1.1.113.0      1.1.113.255    2519  Unknown   \n",
       "97     1.1.114.0      1.1.114.255    2519  Unknown   \n",
       "98     1.1.115.0      1.1.115.255    2519  Unknown   \n",
       "99     1.1.116.0      1.1.116.255    2519  Unknown   \n",
       "\n",
       "                               organization  \n",
       "0                                Not routed  \n",
       "1                                Not routed  \n",
       "2                                Not routed  \n",
       "3                                Not routed  \n",
       "4                                Not routed  \n",
       "5                                Not routed  \n",
       "6                                Not routed  \n",
       "7                                Not routed  \n",
       "8                                Not routed  \n",
       "9                                Not routed  \n",
       "10                               Not routed  \n",
       "11                               Not routed  \n",
       "12                               Not routed  \n",
       "13                               Not routed  \n",
       "14                               Not routed  \n",
       "15                               Not routed  \n",
       "16                               Not routed  \n",
       "17                               Not routed  \n",
       "18                               Not routed  \n",
       "19                               Not routed  \n",
       "20                               Not routed  \n",
       "21                               Not routed  \n",
       "22                               Not routed  \n",
       "23                               Not routed  \n",
       "24                               Not routed  \n",
       "25                               Not routed  \n",
       "26                               Not routed  \n",
       "27                               Not routed  \n",
       "28                               Not routed  \n",
       "29    GTELECOM-AUSTRALIA Gtelecom-AUSTRALIA  \n",
       "..                                      ...  \n",
       "70       TOT-NET TOT Public Company Limited  \n",
       "71       TOT-NET TOT Public Company Limited  \n",
       "72       TOT-NET TOT Public Company Limited  \n",
       "73       TOT-NET TOT Public Company Limited  \n",
       "74       TOT-NET TOT Public Company Limited  \n",
       "75       TOT-NET TOT Public Company Limited  \n",
       "76       TOT-NET TOT Public Company Limited  \n",
       "77                               Not routed  \n",
       "78         CLOUDFLARENET - Cloudflare, Inc.  \n",
       "79                               Not routed  \n",
       "80  CHINANET-BACKBONE No.31,Jin-rong Street  \n",
       "81                               Not routed  \n",
       "82           DIL-AS-AP DONGFONG INC LIMITED  \n",
       "83                               Not routed  \n",
       "84                                   AS2519  \n",
       "85                               Not routed  \n",
       "86                                   AS2519  \n",
       "87                                   AS2519  \n",
       "88                                   AS2519  \n",
       "89                                   AS2519  \n",
       "90                                   AS2519  \n",
       "91                                   AS2519  \n",
       "92                                   AS2519  \n",
       "93                                   AS2519  \n",
       "94                                   AS2519  \n",
       "95                                   AS2519  \n",
       "96                                   AS2519  \n",
       "97                                   AS2519  \n",
       "98                                   AS2519  \n",
       "99                                   AS2519  \n",
       "\n",
       "[100 rows x 5 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ASN_df = pd.read_csv(\"./ip2asn-combined.tsv\", sep='\\t', header=None)\n",
    "ASN_df.columns = ['start','end','asn','country','organization']\n",
    "ASN_df.head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove all the \"Not routed\" rows\n",
    "ASN_df = ASN_df[ASN_df['organization'] != 'Not routed']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add numerical representation for the start and end IP range for faster org search later\n",
    "ASN_df['start.dec'] = ASN_df['start'].apply(lambda x: float(IPy(x).strDec()))\n",
    "ASN_df['end.dec'] = ASN_df['end'].apply(lambda x: float(IPy(x).strDec()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "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>start</th>\n",
       "      <th>end</th>\n",
       "      <th>asn</th>\n",
       "      <th>country</th>\n",
       "      <th>organization</th>\n",
       "      <th>start.dec</th>\n",
       "      <th>end.dec</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1.0.4.0</td>\n",
       "      <td>1.0.4.255</td>\n",
       "      <td>56203</td>\n",
       "      <td>AU</td>\n",
       "      <td>GTELECOM-AUSTRALIA Gtelecom-AUSTRALIA</td>\n",
       "      <td>16778240.0</td>\n",
       "      <td>16778495.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1.0.5.0</td>\n",
       "      <td>1.0.5.255</td>\n",
       "      <td>56203</td>\n",
       "      <td>AU</td>\n",
       "      <td>GTELECOM-AUSTRALIA Gtelecom-AUSTRALIA</td>\n",
       "      <td>16778496.0</td>\n",
       "      <td>16778751.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1.0.6.0</td>\n",
       "      <td>1.0.6.255</td>\n",
       "      <td>56203</td>\n",
       "      <td>AU</td>\n",
       "      <td>GTELECOM-AUSTRALIA Gtelecom-AUSTRALIA</td>\n",
       "      <td>16778752.0</td>\n",
       "      <td>16779007.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>1.0.7.0</td>\n",
       "      <td>1.0.7.255</td>\n",
       "      <td>56203</td>\n",
       "      <td>AU</td>\n",
       "      <td>GTELECOM-AUSTRALIA Gtelecom-AUSTRALIA</td>\n",
       "      <td>16779008.0</td>\n",
       "      <td>16779263.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>1.0.16.0</td>\n",
       "      <td>1.0.16.255</td>\n",
       "      <td>2519</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>AS2519</td>\n",
       "      <td>16781312.0</td>\n",
       "      <td>16781567.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       start         end    asn  country  \\\n",
       "29   1.0.4.0   1.0.4.255  56203       AU   \n",
       "30   1.0.5.0   1.0.5.255  56203       AU   \n",
       "31   1.0.6.0   1.0.6.255  56203       AU   \n",
       "32   1.0.7.0   1.0.7.255  56203       AU   \n",
       "34  1.0.16.0  1.0.16.255   2519  Unknown   \n",
       "\n",
       "                             organization   start.dec     end.dec  \n",
       "29  GTELECOM-AUSTRALIA Gtelecom-AUSTRALIA  16778240.0  16778495.0  \n",
       "30  GTELECOM-AUSTRALIA Gtelecom-AUSTRALIA  16778496.0  16778751.0  \n",
       "31  GTELECOM-AUSTRALIA Gtelecom-AUSTRALIA  16778752.0  16779007.0  \n",
       "32  GTELECOM-AUSTRALIA Gtelecom-AUSTRALIA  16779008.0  16779263.0  \n",
       "34                                 AS2519  16781312.0  16781567.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ASN_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Device labeling  \n",
    "The packet traces we are using come from my home network. I know what these devices are. I am going to import the device information for these devices and merge it with the network traffic dataframe we are using."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "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>Description</th>\n",
       "      <th>Last seen</th>\n",
       "      <th>Usage</th>\n",
       "      <th>OS</th>\n",
       "      <th>IPv4 address</th>\n",
       "      <th>Policy</th>\n",
       "      <th>MAC address</th>\n",
       "      <th>Manufacturer</th>\n",
       "      <th>Device Type</th>\n",
       "      <th>OUI</th>\n",
       "      <th>Manufacturer Device Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Daghan deskphone</td>\n",
       "      <td>6/6/18 11:29</td>\n",
       "      <td>9.626191e+05</td>\n",
       "      <td>Meraki</td>\n",
       "      <td>192.168.1.236</td>\n",
       "      <td>normal</td>\n",
       "      <td>00:18:0a:12:2f:2d</td>\n",
       "      <td>Meraki</td>\n",
       "      <td>Phone</td>\n",
       "      <td>00180A</td>\n",
       "      <td>Meraki Phone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nest</td>\n",
       "      <td>6/6/18 11:30</td>\n",
       "      <td>8.349500e+04</td>\n",
       "      <td>Raspberry Pi</td>\n",
       "      <td>192.168.1.235</td>\n",
       "      <td>normal</td>\n",
       "      <td>18:b4:30:0d:87:18</td>\n",
       "      <td>Nest Labs</td>\n",
       "      <td>Thermostat</td>\n",
       "      <td>18B430</td>\n",
       "      <td>Nest Labs Thermostat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>iPhone</td>\n",
       "      <td>5/19/18 18:11</td>\n",
       "      <td>4.825600e+06</td>\n",
       "      <td>Apple iPhone</td>\n",
       "      <td>192.168.1.9</td>\n",
       "      <td>Privacy please</td>\n",
       "      <td>18:f6:43:57:fc:8f</td>\n",
       "      <td>Apple</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>18F643</td>\n",
       "      <td>Apple iPhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Daghan-Home</td>\n",
       "      <td>6/6/18 11:30</td>\n",
       "      <td>3.126772e+07</td>\n",
       "      <td>Mac OS X 10.13</td>\n",
       "      <td>192.168.1.233</td>\n",
       "      <td>normal</td>\n",
       "      <td>28:f0:76:31:d3:58</td>\n",
       "      <td>Apple</td>\n",
       "      <td>Computer</td>\n",
       "      <td>28F076</td>\n",
       "      <td>Apple Computer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DaghanspleWatch</td>\n",
       "      <td>6/5/18 5:43</td>\n",
       "      <td>2.672940e+05</td>\n",
       "      <td>iOS</td>\n",
       "      <td>192.168.1.7</td>\n",
       "      <td>normal</td>\n",
       "      <td>30:63:6b:65:e4:04</td>\n",
       "      <td>Apple</td>\n",
       "      <td>Watch</td>\n",
       "      <td>30636B</td>\n",
       "      <td>Apple Watch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>daghan-macbook-8</td>\n",
       "      <td>6/6/18 11:21</td>\n",
       "      <td>2.605438e+07</td>\n",
       "      <td>Mac OS X 10.13</td>\n",
       "      <td>192.168.1.21</td>\n",
       "      <td>Privacy please</td>\n",
       "      <td>34:36:3b:7f:3f:a0</td>\n",
       "      <td>Apple</td>\n",
       "      <td>Computer</td>\n",
       "      <td>34363B</td>\n",
       "      <td>Apple Computer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Daghan-Home</td>\n",
       "      <td>6/6/18 11:29</td>\n",
       "      <td>2.155549e+05</td>\n",
       "      <td>Mac OS X</td>\n",
       "      <td>192.168.1.224</td>\n",
       "      <td>normal</td>\n",
       "      <td>38:c9:86:40:7c:a6</td>\n",
       "      <td>Apple</td>\n",
       "      <td>Computer</td>\n",
       "      <td>38C986</td>\n",
       "      <td>Apple Computer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Apple-TV-3</td>\n",
       "      <td>6/3/18 19:35</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>iOS</td>\n",
       "      <td>192.168.1.247</td>\n",
       "      <td>normal</td>\n",
       "      <td>40:cb:c0:bc:36:7c</td>\n",
       "      <td>Apple</td>\n",
       "      <td>TV</td>\n",
       "      <td>40CBC0</td>\n",
       "      <td>Apple TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Apple-TV-3</td>\n",
       "      <td>6/6/18 11:29</td>\n",
       "      <td>6.434089e+05</td>\n",
       "      <td>iOS</td>\n",
       "      <td>192.168.1.249</td>\n",
       "      <td>normal</td>\n",
       "      <td>40:cb:c0:bc:36:7e</td>\n",
       "      <td>Apple</td>\n",
       "      <td>TV</td>\n",
       "      <td>40CBC0</td>\n",
       "      <td>Apple TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>44:65:0d:66:72:60</td>\n",
       "      <td>6/6/18 1:22</td>\n",
       "      <td>3.573050e+05</td>\n",
       "      <td>Generic Linux</td>\n",
       "      <td>192.168.1.19</td>\n",
       "      <td>normal</td>\n",
       "      <td>44:65:0d:66:72:60</td>\n",
       "      <td>Amazon Technologies</td>\n",
       "      <td>Echo</td>\n",
       "      <td>44650D</td>\n",
       "      <td>Amazon Technologies Echo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Amazon Tap</td>\n",
       "      <td>5/20/18 2:08</td>\n",
       "      <td>6.754000e+03</td>\n",
       "      <td>Android</td>\n",
       "      <td>192.168.1.13</td>\n",
       "      <td>normal</td>\n",
       "      <td>44:65:0d:90:60:3a</td>\n",
       "      <td>Amazon Technologies</td>\n",
       "      <td>Echo</td>\n",
       "      <td>44650D</td>\n",
       "      <td>Amazon Technologies Echo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>engin-iPhoneu</td>\n",
       "      <td>6/6/18 11:30</td>\n",
       "      <td>9.831390e+06</td>\n",
       "      <td>Apple iPhone</td>\n",
       "      <td>192.168.1.22</td>\n",
       "      <td>normal</td>\n",
       "      <td>48:74:6e:b0:09:d4</td>\n",
       "      <td>Apple</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>48746E</td>\n",
       "      <td>Apple iPhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Apple-TV</td>\n",
       "      <td>6/6/18 11:30</td>\n",
       "      <td>3.787789e+07</td>\n",
       "      <td>iOS</td>\n",
       "      <td>192.168.1.243</td>\n",
       "      <td>normal</td>\n",
       "      <td>50:32:37:a3:4d:38</td>\n",
       "      <td>Apple</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>503237</td>\n",
       "      <td>Apple iPhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>sonos5CAAFD4C87A0</td>\n",
       "      <td>6/6/18 11:30</td>\n",
       "      <td>1.070524e+06</td>\n",
       "      <td>Cisco/Linksys Router</td>\n",
       "      <td>192.168.1.12</td>\n",
       "      <td>normal</td>\n",
       "      <td>5c:aa:fd:4c:87:a0</td>\n",
       "      <td>Sonos</td>\n",
       "      <td>Speaker</td>\n",
       "      <td>5CAAFD</td>\n",
       "      <td>Sonos Speaker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>SonosZP</td>\n",
       "      <td>6/6/18 11:30</td>\n",
       "      <td>2.291280e+04</td>\n",
       "      <td>Cisco/Linksys Router</td>\n",
       "      <td>192.168.1.15</td>\n",
       "      <td>normal</td>\n",
       "      <td>5c:aa:fd:4c:92:86</td>\n",
       "      <td>Sonos</td>\n",
       "      <td>Speaker</td>\n",
       "      <td>5CAAFD</td>\n",
       "      <td>Sonos Speaker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Samsung</td>\n",
       "      <td>6/6/18 9:18</td>\n",
       "      <td>1.269807e+07</td>\n",
       "      <td>Tizen OS</td>\n",
       "      <td>192.168.1.241</td>\n",
       "      <td>normal</td>\n",
       "      <td>64:1c:b0:7b:f4:10</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>Phone</td>\n",
       "      <td>641CB0</td>\n",
       "      <td>Samsung Phone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>64:b5:c6:b7:60:43</td>\n",
       "      <td>6/6/18 11:29</td>\n",
       "      <td>4.677321e+06</td>\n",
       "      <td>Nokia</td>\n",
       "      <td>192.168.1.10</td>\n",
       "      <td>normal</td>\n",
       "      <td>64:b5:c6:b7:60:43</td>\n",
       "      <td>Nintendo</td>\n",
       "      <td>Switch</td>\n",
       "      <td>64B5C6</td>\n",
       "      <td>Nintendo Switch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>sonos7828CA03800C</td>\n",
       "      <td>6/6/18 11:30</td>\n",
       "      <td>2.876136e+06</td>\n",
       "      <td>Cisco/Linksys Router</td>\n",
       "      <td>192.168.1.232</td>\n",
       "      <td>normal</td>\n",
       "      <td>78:28:ca:03:80:0c</td>\n",
       "      <td>Sonos</td>\n",
       "      <td>Speaker</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>Sonos Speaker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>SonosZP</td>\n",
       "      <td>6/6/18 11:29</td>\n",
       "      <td>1.064901e+05</td>\n",
       "      <td>Cisco/Linksys Router</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>normal</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>Sonos</td>\n",
       "      <td>Speaker</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>Sonos Speaker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Agnethas-MacBook-Air</td>\n",
       "      <td>6/6/18 11:30</td>\n",
       "      <td>4.086181e+07</td>\n",
       "      <td>Mac OS X 10.12</td>\n",
       "      <td>192.168.1.18</td>\n",
       "      <td>Privacy please</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>Apple</td>\n",
       "      <td>Computer</td>\n",
       "      <td>843835</td>\n",
       "      <td>Apple Computer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Android</td>\n",
       "      <td>6/6/18 11:30</td>\n",
       "      <td>3.153601e+05</td>\n",
       "      <td>Android</td>\n",
       "      <td>192.168.1.6</td>\n",
       "      <td>normal</td>\n",
       "      <td>88:71:e5:d2:73:4b</td>\n",
       "      <td>Amazon Technologies</td>\n",
       "      <td>Echo</td>\n",
       "      <td>8871E5</td>\n",
       "      <td>Amazon Technologies Echo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Daghan-Altas-MBP</td>\n",
       "      <td>6/6/18 6:59</td>\n",
       "      <td>2.156007e+07</td>\n",
       "      <td>Mac OS X 10.13</td>\n",
       "      <td>192.168.1.239</td>\n",
       "      <td>normal</td>\n",
       "      <td>88:e9:fe:4c:5e:4f</td>\n",
       "      <td>Apple</td>\n",
       "      <td>Computer</td>\n",
       "      <td>88E9FE</td>\n",
       "      <td>Apple Computer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Pnar-Altas</td>\n",
       "      <td>6/6/18 11:29</td>\n",
       "      <td>1.583700e+07</td>\n",
       "      <td>Apple iPad</td>\n",
       "      <td>192.168.1.23</td>\n",
       "      <td>normal</td>\n",
       "      <td>98:b8:e3:cc:55:e8</td>\n",
       "      <td>Apple</td>\n",
       "      <td>iPad</td>\n",
       "      <td>98B8E3</td>\n",
       "      <td>Apple iPad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Android</td>\n",
       "      <td>6/6/18 11:30</td>\n",
       "      <td>2.016949e+05</td>\n",
       "      <td>Android</td>\n",
       "      <td>192.168.1.5</td>\n",
       "      <td>normal</td>\n",
       "      <td>b4:7c:9c:31:b4:df</td>\n",
       "      <td>Amazon Technologies</td>\n",
       "      <td>Echo</td>\n",
       "      <td>B47C9C</td>\n",
       "      <td>Amazon Technologies Echo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>sonosB8E9378CDA3C</td>\n",
       "      <td>6/6/18 11:29</td>\n",
       "      <td>2.685518e+05</td>\n",
       "      <td>Cisco/Linksys Router</td>\n",
       "      <td>192.168.1.211</td>\n",
       "      <td>normal</td>\n",
       "      <td>b8:e9:37:8c:da:3c</td>\n",
       "      <td>Sonos</td>\n",
       "      <td>Speaker</td>\n",
       "      <td>B8E937</td>\n",
       "      <td>Sonos Speaker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Daghans-iPhone</td>\n",
       "      <td>6/6/18 6:59</td>\n",
       "      <td>2.006734e+07</td>\n",
       "      <td>Apple iPhone</td>\n",
       "      <td>192.168.1.8</td>\n",
       "      <td>normal</td>\n",
       "      <td>cc:08:8d:4c:eb:08</td>\n",
       "      <td>Apple</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>CC088D</td>\n",
       "      <td>Apple iPhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Apple-TV-3</td>\n",
       "      <td>5/28/18 18:39</td>\n",
       "      <td>9.488971e+07</td>\n",
       "      <td>Apple iPhone</td>\n",
       "      <td>192.168.1.238</td>\n",
       "      <td>normal</td>\n",
       "      <td>d0:4f:7e:06:25:ad</td>\n",
       "      <td>Apple</td>\n",
       "      <td>TV</td>\n",
       "      <td>D04F7E</td>\n",
       "      <td>Apple TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>iPhone</td>\n",
       "      <td>6/6/18 11:28</td>\n",
       "      <td>1.066317e+07</td>\n",
       "      <td>Apple iPhone</td>\n",
       "      <td>192.168.1.240</td>\n",
       "      <td>normal</td>\n",
       "      <td>d8:8f:76:bb:35:97</td>\n",
       "      <td>Apple</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>D88F76</td>\n",
       "      <td>Apple iPhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>localhost</td>\n",
       "      <td>6/3/18 21:16</td>\n",
       "      <td>1.893700e+04</td>\n",
       "      <td>Tizen OS</td>\n",
       "      <td>192.168.1.11</td>\n",
       "      <td>normal</td>\n",
       "      <td>e4:7d:bd:3f:96:ec</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>Phone</td>\n",
       "      <td>E47DBD</td>\n",
       "      <td>Samsung Phone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>WeMo</td>\n",
       "      <td>6/6/18 11:29</td>\n",
       "      <td>1.964000e+04</td>\n",
       "      <td>Other</td>\n",
       "      <td>192.168.1.234</td>\n",
       "      <td>normal</td>\n",
       "      <td>ec:1a:59:f6:eb:45</td>\n",
       "      <td>Belkin</td>\n",
       "      <td>Wallplug</td>\n",
       "      <td>EC1A59</td>\n",
       "      <td>Belkin Wallplug</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Description      Last seen         Usage                    OS  \\\n",
       "0       Daghan deskphone   6/6/18 11:29  9.626191e+05                Meraki   \n",
       "1                   Nest   6/6/18 11:30  8.349500e+04          Raspberry Pi   \n",
       "2                 iPhone  5/19/18 18:11  4.825600e+06          Apple iPhone   \n",
       "3            Daghan-Home   6/6/18 11:30  3.126772e+07        Mac OS X 10.13   \n",
       "4        DaghanspleWatch    6/5/18 5:43  2.672940e+05                   iOS   \n",
       "5       daghan-macbook-8   6/6/18 11:21  2.605438e+07        Mac OS X 10.13   \n",
       "6            Daghan-Home   6/6/18 11:29  2.155549e+05              Mac OS X   \n",
       "7             Apple-TV-3   6/3/18 19:35  1.000000e+00                   iOS   \n",
       "8             Apple-TV-3   6/6/18 11:29  6.434089e+05                   iOS   \n",
       "9      44:65:0d:66:72:60    6/6/18 1:22  3.573050e+05         Generic Linux   \n",
       "10            Amazon Tap   5/20/18 2:08  6.754000e+03               Android   \n",
       "11         engin-iPhoneu   6/6/18 11:30  9.831390e+06          Apple iPhone   \n",
       "12              Apple-TV   6/6/18 11:30  3.787789e+07                   iOS   \n",
       "13     sonos5CAAFD4C87A0   6/6/18 11:30  1.070524e+06  Cisco/Linksys Router   \n",
       "14               SonosZP   6/6/18 11:30  2.291280e+04  Cisco/Linksys Router   \n",
       "15               Samsung    6/6/18 9:18  1.269807e+07              Tizen OS   \n",
       "16     64:b5:c6:b7:60:43   6/6/18 11:29  4.677321e+06                 Nokia   \n",
       "17     sonos7828CA03800C   6/6/18 11:30  2.876136e+06  Cisco/Linksys Router   \n",
       "18               SonosZP   6/6/18 11:29  1.064901e+05  Cisco/Linksys Router   \n",
       "19  Agnethas-MacBook-Air   6/6/18 11:30  4.086181e+07        Mac OS X 10.12   \n",
       "20               Android   6/6/18 11:30  3.153601e+05               Android   \n",
       "21      Daghan-Altas-MBP    6/6/18 6:59  2.156007e+07        Mac OS X 10.13   \n",
       "22            Pnar-Altas   6/6/18 11:29  1.583700e+07            Apple iPad   \n",
       "23               Android   6/6/18 11:30  2.016949e+05               Android   \n",
       "24     sonosB8E9378CDA3C   6/6/18 11:29  2.685518e+05  Cisco/Linksys Router   \n",
       "25        Daghans-iPhone    6/6/18 6:59  2.006734e+07          Apple iPhone   \n",
       "26            Apple-TV-3  5/28/18 18:39  9.488971e+07          Apple iPhone   \n",
       "27                iPhone   6/6/18 11:28  1.066317e+07          Apple iPhone   \n",
       "28             localhost   6/3/18 21:16  1.893700e+04              Tizen OS   \n",
       "29                  WeMo   6/6/18 11:29  1.964000e+04                 Other   \n",
       "\n",
       "     IPv4 address          Policy        MAC address         Manufacturer  \\\n",
       "0   192.168.1.236          normal  00:18:0a:12:2f:2d               Meraki   \n",
       "1   192.168.1.235          normal  18:b4:30:0d:87:18            Nest Labs   \n",
       "2     192.168.1.9  Privacy please  18:f6:43:57:fc:8f                Apple   \n",
       "3   192.168.1.233          normal  28:f0:76:31:d3:58                Apple   \n",
       "4     192.168.1.7          normal  30:63:6b:65:e4:04                Apple   \n",
       "5    192.168.1.21  Privacy please  34:36:3b:7f:3f:a0                Apple   \n",
       "6   192.168.1.224          normal  38:c9:86:40:7c:a6                Apple   \n",
       "7   192.168.1.247          normal  40:cb:c0:bc:36:7c                Apple   \n",
       "8   192.168.1.249          normal  40:cb:c0:bc:36:7e                Apple   \n",
       "9    192.168.1.19          normal  44:65:0d:66:72:60  Amazon Technologies   \n",
       "10   192.168.1.13          normal  44:65:0d:90:60:3a  Amazon Technologies   \n",
       "11   192.168.1.22          normal  48:74:6e:b0:09:d4                Apple   \n",
       "12  192.168.1.243          normal  50:32:37:a3:4d:38                Apple   \n",
       "13   192.168.1.12          normal  5c:aa:fd:4c:87:a0                Sonos   \n",
       "14   192.168.1.15          normal  5c:aa:fd:4c:92:86                Sonos   \n",
       "15  192.168.1.241          normal  64:1c:b0:7b:f4:10              Samsung   \n",
       "16   192.168.1.10          normal  64:b5:c6:b7:60:43             Nintendo   \n",
       "17  192.168.1.232          normal  78:28:ca:03:80:0c                Sonos   \n",
       "18  192.168.1.225          normal  78:28:ca:32:4b:28                Sonos   \n",
       "19   192.168.1.18  Privacy please  84:38:35:5a:70:40                Apple   \n",
       "20    192.168.1.6          normal  88:71:e5:d2:73:4b  Amazon Technologies   \n",
       "21  192.168.1.239          normal  88:e9:fe:4c:5e:4f                Apple   \n",
       "22   192.168.1.23          normal  98:b8:e3:cc:55:e8                Apple   \n",
       "23    192.168.1.5          normal  b4:7c:9c:31:b4:df  Amazon Technologies   \n",
       "24  192.168.1.211          normal  b8:e9:37:8c:da:3c                Sonos   \n",
       "25    192.168.1.8          normal  cc:08:8d:4c:eb:08                Apple   \n",
       "26  192.168.1.238          normal  d0:4f:7e:06:25:ad                Apple   \n",
       "27  192.168.1.240          normal  d8:8f:76:bb:35:97                Apple   \n",
       "28   192.168.1.11          normal  e4:7d:bd:3f:96:ec              Samsung   \n",
       "29  192.168.1.234          normal  ec:1a:59:f6:eb:45               Belkin   \n",
       "\n",
       "   Device Type     OUI  Manufacturer Device Type  \n",
       "0        Phone  00180A              Meraki Phone  \n",
       "1   Thermostat  18B430      Nest Labs Thermostat  \n",
       "2       iPhone  18F643              Apple iPhone  \n",
       "3     Computer  28F076            Apple Computer  \n",
       "4        Watch  30636B               Apple Watch  \n",
       "5     Computer  34363B            Apple Computer  \n",
       "6     Computer  38C986            Apple Computer  \n",
       "7           TV  40CBC0                  Apple TV  \n",
       "8           TV  40CBC0                  Apple TV  \n",
       "9         Echo  44650D  Amazon Technologies Echo  \n",
       "10        Echo  44650D  Amazon Technologies Echo  \n",
       "11      iPhone  48746E              Apple iPhone  \n",
       "12      iPhone  503237              Apple iPhone  \n",
       "13     Speaker  5CAAFD             Sonos Speaker  \n",
       "14     Speaker  5CAAFD             Sonos Speaker  \n",
       "15       Phone  641CB0             Samsung Phone  \n",
       "16      Switch  64B5C6           Nintendo Switch  \n",
       "17     Speaker  7828CA             Sonos Speaker  \n",
       "18     Speaker  7828CA             Sonos Speaker  \n",
       "19    Computer  843835            Apple Computer  \n",
       "20        Echo  8871E5  Amazon Technologies Echo  \n",
       "21    Computer  88E9FE            Apple Computer  \n",
       "22        iPad  98B8E3                Apple iPad  \n",
       "23        Echo  B47C9C  Amazon Technologies Echo  \n",
       "24     Speaker  B8E937             Sonos Speaker  \n",
       "25      iPhone  CC088D              Apple iPhone  \n",
       "26          TV  D04F7E                  Apple TV  \n",
       "27      iPhone  D88F76              Apple iPhone  \n",
       "28       Phone  E47DBD             Samsung Phone  \n",
       "29    Wallplug  EC1A59           Belkin Wallplug  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "known_devices = pd.read_csv('known_devices.csv')\n",
    "known_devices['OUI'] = known_devices['MAC address'].apply(lambda x: ''.join(x.upper().split(':')[0:3]))\n",
    "known_devices['Manufacturer Device Type'] = ([' '.join(row) for row in \n",
    "                        zip(known_devices[\"Manufacturer\"],known_devices[\"Device Type\"])])\n",
    "known_devices\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# baseline\n",
    "tshark_cmd = \"-T fields -E header=y -e frame.number -e frame.time -e eth.src -e eth.dst -e frame.protocols\"\n",
    "\n",
    "#add IP/TCP/UDP/ICMP layers\n",
    "tshark_cmd += \" -e ip.src -e ip.dst -e tcp.srcport -e tcp.dstport -e udp.srcport -e udp.dstport\"\n",
    "\n",
    "# add DSN / mDNS layer\n",
    "tshark_cmd += \" -e dns.qry.name -e dns.resp.name -e dns.cname -e dns.a\"\n",
    "\n",
    "# add HTTP layer\n",
    "tshark_cmd += \" -e http.request.method -e http.request.uri -e http.user_agent -e http.host\"\n",
    "\n",
    "# add SSL certificate layer\n",
    "tshark_cmd += \" -e x509sat.printableString -e x509sat.uTF8String\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "!tshark -tud -n -r ./packet_capture.pcap {tshark_cmd} > packets.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!head -n 5 packets.csv \n",
    "#!tail -n 5 packets.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"./packets.csv\", sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "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>frame.number</th>\n",
       "      <th>frame.time</th>\n",
       "      <th>eth.src</th>\n",
       "      <th>eth.dst</th>\n",
       "      <th>frame.protocols</th>\n",
       "      <th>ip.src</th>\n",
       "      <th>ip.dst</th>\n",
       "      <th>tcp.srcport</th>\n",
       "      <th>tcp.dstport</th>\n",
       "      <th>udp.srcport</th>\n",
       "      <th>...</th>\n",
       "      <th>dns.qry.name</th>\n",
       "      <th>dns.resp.name</th>\n",
       "      <th>dns.cname</th>\n",
       "      <th>dns.a</th>\n",
       "      <th>http.request.method</th>\n",
       "      <th>http.request.uri</th>\n",
       "      <th>http.user_agent</th>\n",
       "      <th>http.host</th>\n",
       "      <th>x509sat.printableString</th>\n",
       "      <th>x509sat.uTF8String</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958000000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958052000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958053000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Apr 27, 2018 17:15:18.969772000 PDT</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>443.0</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Apr 27, 2018 17:15:18.971427000 PDT</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp</td>\n",
       "      <td>216.58.195.243</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>443.0</td>\n",
       "      <td>59611.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   frame.number                           frame.time            eth.src  \\\n",
       "0             1  Apr 27, 2018 17:15:18.958000000 PDT  84:38:35:5a:70:40   \n",
       "1             2  Apr 27, 2018 17:15:18.958052000 PDT  84:38:35:5a:70:40   \n",
       "2             3  Apr 27, 2018 17:15:18.958053000 PDT  84:38:35:5a:70:40   \n",
       "3             4  Apr 27, 2018 17:15:18.969772000 PDT  e0:55:3d:02:41:21   \n",
       "4             5  Apr 27, 2018 17:15:18.971427000 PDT  e0:55:3d:02:41:21   \n",
       "\n",
       "             eth.dst                          frame.protocols          ip.src  \\\n",
       "0  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "1  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "2  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "3  84:38:35:5a:70:40      eth:ethertype:vlan:ethertype:ip:tcp   151.101.1.254   \n",
       "4  84:38:35:5a:70:40      eth:ethertype:vlan:ethertype:ip:tcp  216.58.195.243   \n",
       "\n",
       "          ip.dst  tcp.srcport  tcp.dstport  udp.srcport        ...          \\\n",
       "0  151.101.1.254      59554.0        443.0          NaN        ...           \n",
       "1  151.101.1.254      59554.0        443.0          NaN        ...           \n",
       "2  151.101.1.254      59554.0        443.0          NaN        ...           \n",
       "3   192.168.1.14        443.0      59554.0          NaN        ...           \n",
       "4   192.168.1.14        443.0      59611.0          NaN        ...           \n",
       "\n",
       "   dns.qry.name dns.resp.name dns.cname dns.a http.request.method  \\\n",
       "0           NaN           NaN       NaN   NaN                 NaN   \n",
       "1           NaN           NaN       NaN   NaN                 NaN   \n",
       "2           NaN           NaN       NaN   NaN                 NaN   \n",
       "3           NaN           NaN       NaN   NaN                 NaN   \n",
       "4           NaN           NaN       NaN   NaN                 NaN   \n",
       "\n",
       "  http.request.uri http.user_agent http.host x509sat.printableString  \\\n",
       "0              NaN             NaN       NaN                     NaN   \n",
       "1              NaN             NaN       NaN                     NaN   \n",
       "2              NaN             NaN       NaN                     NaN   \n",
       "3              NaN             NaN       NaN                     NaN   \n",
       "4              NaN             NaN       NaN                     NaN   \n",
       "\n",
       "  x509sat.uTF8String  \n",
       "0                NaN  \n",
       "1                NaN  \n",
       "2                NaN  \n",
       "3                NaN  \n",
       "4                NaN  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extract OUI from eth.src\n",
    "df['oui.src'] = df['eth.src'].apply(lambda x: ''.join(x.upper().split(':')[0:3]))\n",
    "df['tail.src'] = df['eth.src'].apply(lambda x: ''.join(x.upper().split(':')[3:6]))\n",
    "df['oui.dst'] = df['eth.dst'].apply(lambda x: ''.join(x.upper().split(':')[0:3]))\n",
    "df['tail.dst'] = df['eth.dst'].apply(lambda x: ''.join(x.upper().split(':')[3:6]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "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>frame.number</th>\n",
       "      <th>frame.time</th>\n",
       "      <th>eth.src</th>\n",
       "      <th>eth.dst</th>\n",
       "      <th>frame.protocols</th>\n",
       "      <th>ip.src</th>\n",
       "      <th>ip.dst</th>\n",
       "      <th>tcp.srcport</th>\n",
       "      <th>tcp.dstport</th>\n",
       "      <th>udp.srcport</th>\n",
       "      <th>...</th>\n",
       "      <th>http.request.method</th>\n",
       "      <th>http.request.uri</th>\n",
       "      <th>http.user_agent</th>\n",
       "      <th>http.host</th>\n",
       "      <th>x509sat.printableString</th>\n",
       "      <th>x509sat.uTF8String</th>\n",
       "      <th>oui.src</th>\n",
       "      <th>tail.src</th>\n",
       "      <th>oui.dst</th>\n",
       "      <th>tail.dst</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958000000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958052000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958053000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Apr 27, 2018 17:15:18.969772000 PDT</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>443.0</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Apr 27, 2018 17:15:18.971427000 PDT</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp</td>\n",
       "      <td>216.58.195.243</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>443.0</td>\n",
       "      <td>59611.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</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>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   frame.number                           frame.time            eth.src  \\\n",
       "0             1  Apr 27, 2018 17:15:18.958000000 PDT  84:38:35:5a:70:40   \n",
       "1             2  Apr 27, 2018 17:15:18.958052000 PDT  84:38:35:5a:70:40   \n",
       "2             3  Apr 27, 2018 17:15:18.958053000 PDT  84:38:35:5a:70:40   \n",
       "3             4  Apr 27, 2018 17:15:18.969772000 PDT  e0:55:3d:02:41:21   \n",
       "4             5  Apr 27, 2018 17:15:18.971427000 PDT  e0:55:3d:02:41:21   \n",
       "\n",
       "             eth.dst                          frame.protocols          ip.src  \\\n",
       "0  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "1  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "2  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "3  84:38:35:5a:70:40      eth:ethertype:vlan:ethertype:ip:tcp   151.101.1.254   \n",
       "4  84:38:35:5a:70:40      eth:ethertype:vlan:ethertype:ip:tcp  216.58.195.243   \n",
       "\n",
       "          ip.dst  tcp.srcport  tcp.dstport  udp.srcport   ...     \\\n",
       "0  151.101.1.254      59554.0        443.0          NaN   ...      \n",
       "1  151.101.1.254      59554.0        443.0          NaN   ...      \n",
       "2  151.101.1.254      59554.0        443.0          NaN   ...      \n",
       "3   192.168.1.14        443.0      59554.0          NaN   ...      \n",
       "4   192.168.1.14        443.0      59611.0          NaN   ...      \n",
       "\n",
       "   http.request.method http.request.uri http.user_agent http.host  \\\n",
       "0                  NaN              NaN             NaN       NaN   \n",
       "1                  NaN              NaN             NaN       NaN   \n",
       "2                  NaN              NaN             NaN       NaN   \n",
       "3                  NaN              NaN             NaN       NaN   \n",
       "4                  NaN              NaN             NaN       NaN   \n",
       "\n",
       "  x509sat.printableString x509sat.uTF8String oui.src tail.src oui.dst tail.dst  \n",
       "0                     NaN                NaN  843835   5A7040  E0553D   024121  \n",
       "1                     NaN                NaN  843835   5A7040  E0553D   024121  \n",
       "2                     NaN                NaN  843835   5A7040  E0553D   024121  \n",
       "3                     NaN                NaN  E0553D   024121  843835   5A7040  \n",
       "4                     NaN                NaN  E0553D   024121  843835   5A7040  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(OUI_df[list(['Assignment', 'Organization Name'])], \n",
    "             left_on = 'oui.src',  right_on= 'Assignment', how = 'left').drop(['Assignment'], axis = 1)\n",
    "df.rename(columns={'Organization Name':'org.src'}, inplace=True)\n",
    "\n",
    "df = df.merge(OUI_df[list(['Assignment', 'Organization Name'])], \n",
    "             left_on = 'oui.dst',  right_on= 'Assignment', how = 'left').drop(['Assignment'], axis = 1)\n",
    "df.rename(columns={'Organization Name':'org.dst'}, inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# let's get rid of white spaces\n",
    "df['org.src'] = df['org.src'].apply(lambda x: x.replace('.','').replace(',','').replace(\" \", \"-\") if pd.notna(x) else x)\n",
    "df['org.dst'] = df['org.dst'].apply(lambda x: x.replace('.','').replace(',','').replace(\" \", \"-\") if pd.notna(x) else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "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>frame.number</th>\n",
       "      <th>frame.time</th>\n",
       "      <th>eth.src</th>\n",
       "      <th>eth.dst</th>\n",
       "      <th>frame.protocols</th>\n",
       "      <th>ip.src</th>\n",
       "      <th>ip.dst</th>\n",
       "      <th>tcp.srcport</th>\n",
       "      <th>tcp.dstport</th>\n",
       "      <th>udp.srcport</th>\n",
       "      <th>...</th>\n",
       "      <th>http.user_agent</th>\n",
       "      <th>http.host</th>\n",
       "      <th>x509sat.printableString</th>\n",
       "      <th>x509sat.uTF8String</th>\n",
       "      <th>oui.src</th>\n",
       "      <th>tail.src</th>\n",
       "      <th>oui.dst</th>\n",
       "      <th>tail.dst</th>\n",
       "      <th>org.src</th>\n",
       "      <th>org.dst</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958000000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>Apple-Inc</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958052000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>Apple-Inc</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958053000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>Apple-Inc</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Apr 27, 2018 17:15:18.969772000 PDT</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>443.0</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "      <td>Apple-Inc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Apr 27, 2018 17:15:18.971427000 PDT</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp</td>\n",
       "      <td>216.58.195.243</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>443.0</td>\n",
       "      <td>59611.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "      <td>Apple-Inc</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   frame.number                           frame.time            eth.src  \\\n",
       "0             1  Apr 27, 2018 17:15:18.958000000 PDT  84:38:35:5a:70:40   \n",
       "1             2  Apr 27, 2018 17:15:18.958052000 PDT  84:38:35:5a:70:40   \n",
       "2             3  Apr 27, 2018 17:15:18.958053000 PDT  84:38:35:5a:70:40   \n",
       "3             4  Apr 27, 2018 17:15:18.969772000 PDT  e0:55:3d:02:41:21   \n",
       "4             5  Apr 27, 2018 17:15:18.971427000 PDT  e0:55:3d:02:41:21   \n",
       "\n",
       "             eth.dst                          frame.protocols          ip.src  \\\n",
       "0  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "1  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "2  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "3  84:38:35:5a:70:40      eth:ethertype:vlan:ethertype:ip:tcp   151.101.1.254   \n",
       "4  84:38:35:5a:70:40      eth:ethertype:vlan:ethertype:ip:tcp  216.58.195.243   \n",
       "\n",
       "          ip.dst  tcp.srcport  tcp.dstport  udp.srcport      ...       \\\n",
       "0  151.101.1.254      59554.0        443.0          NaN      ...        \n",
       "1  151.101.1.254      59554.0        443.0          NaN      ...        \n",
       "2  151.101.1.254      59554.0        443.0          NaN      ...        \n",
       "3   192.168.1.14        443.0      59554.0          NaN      ...        \n",
       "4   192.168.1.14        443.0      59611.0          NaN      ...        \n",
       "\n",
       "   http.user_agent http.host x509sat.printableString x509sat.uTF8String  \\\n",
       "0              NaN       NaN                     NaN                NaN   \n",
       "1              NaN       NaN                     NaN                NaN   \n",
       "2              NaN       NaN                     NaN                NaN   \n",
       "3              NaN       NaN                     NaN                NaN   \n",
       "4              NaN       NaN                     NaN                NaN   \n",
       "\n",
       "  oui.src tail.src oui.dst tail.dst       org.src       org.dst  \n",
       "0  843835   5A7040  E0553D   024121     Apple-Inc  Cisco-Meraki  \n",
       "1  843835   5A7040  E0553D   024121     Apple-Inc  Cisco-Meraki  \n",
       "2  843835   5A7040  E0553D   024121     Apple-Inc  Cisco-Meraki  \n",
       "3  E0553D   024121  843835   5A7040  Cisco-Meraki     Apple-Inc  \n",
       "4  E0553D   024121  843835   5A7040  Cisco-Meraki     Apple-Inc  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This function determines if the IP \n",
    "# address is internal or public \n",
    "# according to RFC1918\n",
    "\n",
    "def determineZone(ip):\n",
    "    if pd.notna(ip):\n",
    "        try:\n",
    "            return IPy(ip).iptype()\n",
    "        except ValueError:\n",
    "            return IPy(ip.split(',')[0]).iptype()\n",
    "    return ip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['zone.src'] = df['ip.src'].apply(lambda x: determineZone(x))\n",
    "df['zone.dst'] = df['ip.dst'].apply(lambda x: determineZone(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "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>frame.number</th>\n",
       "      <th>frame.time</th>\n",
       "      <th>eth.src</th>\n",
       "      <th>eth.dst</th>\n",
       "      <th>frame.protocols</th>\n",
       "      <th>ip.src</th>\n",
       "      <th>ip.dst</th>\n",
       "      <th>tcp.srcport</th>\n",
       "      <th>tcp.dstport</th>\n",
       "      <th>udp.srcport</th>\n",
       "      <th>...</th>\n",
       "      <th>x509sat.printableString</th>\n",
       "      <th>x509sat.uTF8String</th>\n",
       "      <th>oui.src</th>\n",
       "      <th>tail.src</th>\n",
       "      <th>oui.dst</th>\n",
       "      <th>tail.dst</th>\n",
       "      <th>org.src</th>\n",
       "      <th>org.dst</th>\n",
       "      <th>zone.src</th>\n",
       "      <th>zone.dst</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958000000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>Apple-Inc</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>PUBLIC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958052000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>Apple-Inc</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>PUBLIC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958053000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>Apple-Inc</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>PUBLIC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Apr 27, 2018 17:15:18.969772000 PDT</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>443.0</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "      <td>Apple-Inc</td>\n",
       "      <td>PUBLIC</td>\n",
       "      <td>PRIVATE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Apr 27, 2018 17:15:18.971427000 PDT</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp</td>\n",
       "      <td>216.58.195.243</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>443.0</td>\n",
       "      <td>59611.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "      <td>Apple-Inc</td>\n",
       "      <td>PUBLIC</td>\n",
       "      <td>PRIVATE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   frame.number                           frame.time            eth.src  \\\n",
       "0             1  Apr 27, 2018 17:15:18.958000000 PDT  84:38:35:5a:70:40   \n",
       "1             2  Apr 27, 2018 17:15:18.958052000 PDT  84:38:35:5a:70:40   \n",
       "2             3  Apr 27, 2018 17:15:18.958053000 PDT  84:38:35:5a:70:40   \n",
       "3             4  Apr 27, 2018 17:15:18.969772000 PDT  e0:55:3d:02:41:21   \n",
       "4             5  Apr 27, 2018 17:15:18.971427000 PDT  e0:55:3d:02:41:21   \n",
       "\n",
       "             eth.dst                          frame.protocols          ip.src  \\\n",
       "0  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "1  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "2  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "3  84:38:35:5a:70:40      eth:ethertype:vlan:ethertype:ip:tcp   151.101.1.254   \n",
       "4  84:38:35:5a:70:40      eth:ethertype:vlan:ethertype:ip:tcp  216.58.195.243   \n",
       "\n",
       "          ip.dst  tcp.srcport  tcp.dstport  udp.srcport   ...     \\\n",
       "0  151.101.1.254      59554.0        443.0          NaN   ...      \n",
       "1  151.101.1.254      59554.0        443.0          NaN   ...      \n",
       "2  151.101.1.254      59554.0        443.0          NaN   ...      \n",
       "3   192.168.1.14        443.0      59554.0          NaN   ...      \n",
       "4   192.168.1.14        443.0      59611.0          NaN   ...      \n",
       "\n",
       "   x509sat.printableString x509sat.uTF8String oui.src tail.src oui.dst  \\\n",
       "0                      NaN                NaN  843835   5A7040  E0553D   \n",
       "1                      NaN                NaN  843835   5A7040  E0553D   \n",
       "2                      NaN                NaN  843835   5A7040  E0553D   \n",
       "3                      NaN                NaN  E0553D   024121  843835   \n",
       "4                      NaN                NaN  E0553D   024121  843835   \n",
       "\n",
       "  tail.dst       org.src       org.dst zone.src zone.dst  \n",
       "0   024121     Apple-Inc  Cisco-Meraki  PRIVATE   PUBLIC  \n",
       "1   024121     Apple-Inc  Cisco-Meraki  PRIVATE   PUBLIC  \n",
       "2   024121     Apple-Inc  Cisco-Meraki  PRIVATE   PUBLIC  \n",
       "3   5A7040  Cisco-Meraki     Apple-Inc   PUBLIC  PRIVATE  \n",
       "4   5A7040  Cisco-Meraki     Apple-Inc   PUBLIC  PRIVATE  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# clean up IP addresses that have multiple source or dest values\n",
    "df['ip.src'] = df['ip.src'].apply(lambda x: x.split(',')[0] if pd.notna(x) else x)\n",
    "df['ip.dst'] = df['ip.dst'].apply(lambda x: x.split(',')[0] if pd.notna(x) else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "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>frame.number</th>\n",
       "      <th>frame.time</th>\n",
       "      <th>eth.src</th>\n",
       "      <th>eth.dst</th>\n",
       "      <th>frame.protocols</th>\n",
       "      <th>ip.src</th>\n",
       "      <th>ip.dst</th>\n",
       "      <th>tcp.srcport</th>\n",
       "      <th>tcp.dstport</th>\n",
       "      <th>udp.srcport</th>\n",
       "      <th>...</th>\n",
       "      <th>x509sat.uTF8String</th>\n",
       "      <th>oui.src</th>\n",
       "      <th>tail.src</th>\n",
       "      <th>oui.dst</th>\n",
       "      <th>tail.dst</th>\n",
       "      <th>org.src</th>\n",
       "      <th>org.dst</th>\n",
       "      <th>zone.src</th>\n",
       "      <th>zone.dst</th>\n",
       "      <th>protocol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958000000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>Apple-Inc</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>PUBLIC</td>\n",
       "      <td>ssl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958052000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>Apple-Inc</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>PUBLIC</td>\n",
       "      <td>ssl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Apr 27, 2018 17:15:18.958053000 PDT</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp:ssl</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>Apple-Inc</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>PUBLIC</td>\n",
       "      <td>ssl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Apr 27, 2018 17:15:18.969772000 PDT</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp</td>\n",
       "      <td>151.101.1.254</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>443.0</td>\n",
       "      <td>59554.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "      <td>Apple-Inc</td>\n",
       "      <td>PUBLIC</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>tcp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Apr 27, 2018 17:15:18.971427000 PDT</td>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:tcp</td>\n",
       "      <td>216.58.195.243</td>\n",
       "      <td>192.168.1.14</td>\n",
       "      <td>443.0</td>\n",
       "      <td>59611.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E0553D</td>\n",
       "      <td>024121</td>\n",
       "      <td>843835</td>\n",
       "      <td>5A7040</td>\n",
       "      <td>Cisco-Meraki</td>\n",
       "      <td>Apple-Inc</td>\n",
       "      <td>PUBLIC</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>tcp</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   frame.number                           frame.time            eth.src  \\\n",
       "0             1  Apr 27, 2018 17:15:18.958000000 PDT  84:38:35:5a:70:40   \n",
       "1             2  Apr 27, 2018 17:15:18.958052000 PDT  84:38:35:5a:70:40   \n",
       "2             3  Apr 27, 2018 17:15:18.958053000 PDT  84:38:35:5a:70:40   \n",
       "3             4  Apr 27, 2018 17:15:18.969772000 PDT  e0:55:3d:02:41:21   \n",
       "4             5  Apr 27, 2018 17:15:18.971427000 PDT  e0:55:3d:02:41:21   \n",
       "\n",
       "             eth.dst                          frame.protocols          ip.src  \\\n",
       "0  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "1  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "2  e0:55:3d:02:41:21  eth:ethertype:vlan:ethertype:ip:tcp:ssl    192.168.1.14   \n",
       "3  84:38:35:5a:70:40      eth:ethertype:vlan:ethertype:ip:tcp   151.101.1.254   \n",
       "4  84:38:35:5a:70:40      eth:ethertype:vlan:ethertype:ip:tcp  216.58.195.243   \n",
       "\n",
       "          ip.dst  tcp.srcport  tcp.dstport  udp.srcport   ...     \\\n",
       "0  151.101.1.254      59554.0        443.0          NaN   ...      \n",
       "1  151.101.1.254      59554.0        443.0          NaN   ...      \n",
       "2  151.101.1.254      59554.0        443.0          NaN   ...      \n",
       "3   192.168.1.14        443.0      59554.0          NaN   ...      \n",
       "4   192.168.1.14        443.0      59611.0          NaN   ...      \n",
       "\n",
       "   x509sat.uTF8String oui.src tail.src oui.dst tail.dst       org.src  \\\n",
       "0                 NaN  843835   5A7040  E0553D   024121     Apple-Inc   \n",
       "1                 NaN  843835   5A7040  E0553D   024121     Apple-Inc   \n",
       "2                 NaN  843835   5A7040  E0553D   024121     Apple-Inc   \n",
       "3                 NaN  E0553D   024121  843835   5A7040  Cisco-Meraki   \n",
       "4                 NaN  E0553D   024121  843835   5A7040  Cisco-Meraki   \n",
       "\n",
       "        org.dst zone.src zone.dst protocol  \n",
       "0  Cisco-Meraki  PRIVATE   PUBLIC      ssl  \n",
       "1  Cisco-Meraki  PRIVATE   PUBLIC      ssl  \n",
       "2  Cisco-Meraki  PRIVATE   PUBLIC      ssl  \n",
       "3     Apple-Inc   PUBLIC  PRIVATE      tcp  \n",
       "4     Apple-Inc   PUBLIC  PRIVATE      tcp  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['protocol'] = df['frame.protocols'].apply(lambda x: x.split(':')[-1])\n",
    "#df = df.drop(['frame.protocols'], axis = 1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# let's create a list of all the public IPs we see\n",
    "public_IPs = set()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index,row in df.iterrows():\n",
    "    if (row['zone.src'] == \"PUBLIC\"):\n",
    "        public_IPs.add(row['ip.src'])\n",
    "    if (row['zone.dst'] == \"PUBLIC\"):\n",
    "        public_IPs.add(row['ip.dst'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this is a crazy hack... it is best I explain it live\n",
    "# but all it does is to match a public IP to the Internet wide Organization (ASN) it belongs to\n",
    "public_IPs_orgs = {}\n",
    "for ip in public_IPs:\n",
    "    public_IPs_orgs[ip] = ASN_df[(ASN_df['start.dec'] <= float(IPy(ip).strDec())) & \n",
    "                                   (ASN_df['end.dec'] >= float(IPy(ip).strDec()))]['organization']\\\n",
    "                        .values[0].split(' ')[0]\n",
    "\n",
    "#public_IPs_orgs   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# replace org data for public IPs based on the ASN information\n",
    "df['org.src'] = df[['zone.src','ip.src','org.src']].apply(lambda x: ( public_IPs_orgs[x['ip.src']] if x['zone.src'] == \"PUBLIC\" else x['org.src']), axis=1)\n",
    "df['org.dst'] = df[['zone.dst','ip.dst','org.dst']].apply(lambda x: ( public_IPs_orgs[x['ip.dst']] if x['zone.dst'] == \"PUBLIC\" else x['org.dst']), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['ssl', 'tcp', 'data', 'arp', 'icmpv6', 'mdns', 'stp', 'dns',\n",
       "       'http', 'data-text-lines', 'ntp', 'media', 'stun', 'nbns', 'bootp',\n",
       "       'gquic', 'igmp', 'ssdp', 'x509ce', 'json'], dtype=object)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['protocol'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "# protocols we see\n",
    "# 'gquic', 'igmp', 'ssdp', 'x509ce', 'json'\n",
    "\n",
    "def protoLang(line):\n",
    "    proto = line['protocol']\n",
    "    try:\n",
    "        \n",
    "         # x509ce (certificate exchange)\n",
    "        if line['frame.protocols'].find('x509') > 0:\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['tcp.srcport']))\n",
    "            dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    else line['org.dst']\n",
    "            dstport = str(int(line['tcp.dstport']))\n",
    "            x509ce_message = ''\n",
    "            if pd.notna(line['x509sat.printableString']):\n",
    "                x509ce_message += line['x509sat.printableString']\n",
    "            if pd.notna(line['x509sat.uTF8String']):\n",
    "                x509ce_message += line['x509sat.uTF8String']\n",
    "            return \" \".join([src,srcport,proto,dst,dstport,x509ce_message])\n",
    "        \n",
    "        ## ssl \n",
    "        elif proto == 'ssl':\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['tcp.srcport']))\n",
    "            dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    else line['org.dst']\n",
    "            dstport = str(int(line['tcp.dstport']))\n",
    "            return \" \".join([src,srcport,proto,dst,dstport])\n",
    "                \n",
    "        \n",
    "        ## tcp:data\n",
    "        elif line['frame.protocols'].find('tcp:data') > 0:\n",
    "            proto = 'tcp:data'\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['tcp.srcport']))\n",
    "            dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    else line['org.dst']\n",
    "            dstport = str(int(line['tcp.dstport']))\n",
    "            return \" \".join([src,srcport,proto,dst,dstport])\n",
    "        \n",
    "        ## tcp:stun\n",
    "        ## TODO: stun.att.software \"stun.att.realm\": \"belkin.org\",\n",
    "        elif line['frame.protocols'].find('tcp:stun') > 0:\n",
    "            proto = 'tcp:stun'\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['tcp.srcport']))\n",
    "            dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    else line['org.dst']\n",
    "            dstport = str(int(line['tcp.dstport']))\n",
    "            return \" \".join([src,srcport,proto,dst,dstport])\n",
    "        \n",
    "        ## tcp\n",
    "        elif proto == 'tcp':\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['tcp.srcport']))\n",
    "            dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    else line['org.dst']\n",
    "            dstport = str(int(line['tcp.dstport']))\n",
    "            return \" \".join([src,srcport,proto,dst,dstport])\n",
    "        \n",
    "        ## upd:data\n",
    "        ## TODO: Detect broadcasts\n",
    "        elif line['frame.protocols'].find('udp:data') > 0:\n",
    "            proto = 'udp:data'\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['udp.srcport']))\n",
    "            if line['zone.dst'] == 'PRIVATE':\n",
    "                if pd.notna(line['org.dst']):\n",
    "                    dst =  \" \".join([line['org.dst'],line['tail.dst']])\n",
    "                else:\n",
    "                    dst = ''\n",
    "            elif line['zone.dst'] == 'PUBLIC':\n",
    "                dst = line['org.dst']\n",
    "            else:\n",
    "                dst = ''\n",
    "            dstport = str(int(line['udp.dstport']))\n",
    "            return \" \".join([src,srcport,proto,dst,dstport])\n",
    "        \n",
    "        ## upd:nbns (netbios)\n",
    "        ## TODO: nbns.name\": \"MACBOOKAIR-7040<00> (Workstation\\/Redirector)\",\n",
    "        elif line['frame.protocols'].find('udp:nbns') > 0:\n",
    "            proto = 'udp:nbns'\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['udp.srcport']))\n",
    "            #dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    #else line['org.dst']\n",
    "            #dstport = str(int(line['udp.dstport']))\n",
    "            return \" \".join([src,srcport,proto])\n",
    "        \n",
    "        ## upd:bootp (dhcp)\n",
    "        ## TODO: \"bootp.option.hostname\": \"amazon-c4475da2a\"\n",
    "        ## TODO: \"bootp.type\": \"2\" (1 is request, 2 is reply)\n",
    "        ## if it is a reply, add the dst IP address too\n",
    "        elif line['frame.protocols'].find('udp:bootp') > 0:\n",
    "            proto = 'udp:bootp'\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['udp.srcport']))\n",
    "            #dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    #else line['org.dst']\n",
    "            #dstport = str(int(line['udp.dstport']))\n",
    "            return \" \".join([src,srcport,proto])\n",
    "        \n",
    "        ## udP:gquic\n",
    "        ## TODO: \"gquic.tag.sni\": \"0.docs.google.com\"\n",
    "        ## TODO: \"gquic.tag.uaid\": \"Chrome\\/65.0.3325.181 Intel Mac OS X 10_13_3\" \n",
    "        ## TODO: \"gquic.tag\": \"CHLO\" (client hello)\n",
    "        elif line['frame.protocols'].find('udp:gquic') > 0:\n",
    "            proto = 'udp:gquic'\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['udp.srcport']))\n",
    "            dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    else line['org.dst']\n",
    "            dstport = str(int(line['udp.dstport']))\n",
    "            return \" \".join([src,srcport,proto,dst,dstport])\n",
    "        \n",
    "        ## ssdp (simple service discovery protocol)\n",
    "        ## TODO: http.server: \"Linux UPnP\\/1.0 Sonos\\/41.3-50131 (ZPS12)\n",
    "        ## TODO: http.unknown_header: \"HOUSEHOLD.SMARTSPEAKER.AUDIO: Sonos_hOcMvZ0JBvDVZz7BXZc5ILQAT5.Cd7MOjIUy3HWHWEXItIZ\\\\r\\\\n\",\n",
    "        ## TODO: http.request.full_uri: \"http:\\/\\/239.255.255.250:1900*\",\n",
    "        elif proto == 'ssdp':\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['udp.srcport']))\n",
    "            if (line['zone.dst'] == 'PRIVATE'):\n",
    "                dst = \" \".join(line['org.dst'],line['tail.dst'])\n",
    "            elif (line['zone.dst'] == 'RESERVED'):\n",
    "                dst = line['eth.dst']\n",
    "            else:\n",
    "                dst = line['org.dst']\n",
    "            dstport = str(int(line['udp.dstport']))\n",
    "            return \" \".join([src,srcport,proto,dst,dstport])\n",
    "        \n",
    "        ## db-lsp-disc:json (Dropbox Lan sync Discovery Protocol)\n",
    "        ## TODO: Detect and use Broadcast\n",
    "        ## TODO: eth.addr_resolved\": \"Broadcast\"\n",
    "        elif line['frame.protocols'].find('db-lsp-disc:json') > 0:\n",
    "            proto = 'db-lsp-disc'\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['udp.srcport']))\n",
    "            dstport = str(int(line['udp.dstport']))\n",
    "            return \" \".join([src,srcport,proto,dstport])\n",
    "        \n",
    "        ## udp\n",
    "        elif proto == 'udp':\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['udp.srcport']))\n",
    "            dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    else line['org.dst']\n",
    "            dstport = str(int(line['udp.dstport']))\n",
    "            return \" \".join([src,srcport,proto,dst,dstport])\n",
    "        \n",
    "        ## ntp (network time protocol)\n",
    "        elif proto == 'ntp':\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['udp.srcport']))\n",
    "            dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    else line['org.dst']\n",
    "            dstport = str(int(line['udp.dstport']))\n",
    "            return \" \".join([src,srcport,proto,dst,dstport])\n",
    "        \n",
    "        \n",
    "        ## ARP\n",
    "        elif proto == 'arp':\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) \n",
    "            dst =  \" \".join([line['org.dst'],line['tail.dst']]) if pd.notna(line['org.dst']) else '' \n",
    "            return \" \".join([src,proto,dst])\n",
    "            \n",
    "        ## ICMPv6\n",
    "        ## TODO: There is more information to be extracted here\n",
    "        elif proto == 'icmpv6':\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) \n",
    "            return \" \".join([src,proto])\n",
    "        \n",
    "        ## STP (spanning tree protocol)\n",
    "        elif proto == 'stp':\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) \n",
    "            return \" \".join([src,proto])\n",
    "        \n",
    "        ## mdns\n",
    "        elif proto == 'mdns':\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) \n",
    "            srcport = str(int(line['udp.srcport']))\n",
    "            dst = line['eth.dst']\n",
    "            mdns_string =   (line['dns.qry.name'] + \" \" if pd.notna(line['dns.qry.name']) else '') + \\\n",
    "                            (line['dns.resp.name'] + \" \" if pd.notna(line['dns.resp.name']) else '') +\\\n",
    "                            (line['dns.cname'] + \" \" if pd.notna(line['dns.cname']) else '') + \\\n",
    "                            (line['dns.a'] if pd.notna(line['dns.a']) else '')    \n",
    "            return \" \".join([src,srcport,proto,mdns_string])\n",
    "        \n",
    "        ## igmp\n",
    "        elif proto == 'igmp':\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) \n",
    "            dst = line['eth.dst']\n",
    "            return \" \".join([src,proto,dst])\n",
    "        \n",
    "        ## dns\n",
    "        elif proto == 'dns':\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['udp.srcport']))\n",
    "            dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    else line['org.dst']\n",
    "            dstport = str(int(line['udp.dstport']))\n",
    "            dns_string =   (line['dns.qry.name'] + \" \" if pd.notna(line['dns.qry.name']) else '') + \\\n",
    "                            (line['dns.resp.name'] + \" \" if pd.notna(line['dns.resp.name']) else '') +\\\n",
    "                            (line['dns.cname'] + \" \" if pd.notna(line['dns.cname']) else '') + \\\n",
    "                            (line['dns.a'] if pd.notna(line['dns.a']) else '')\n",
    "            #print(dns_string)\n",
    "            return \" \".join([src,srcport,proto,dst,dstport,dns_string])\n",
    "\n",
    "        ## http \n",
    "        elif proto == 'http':\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['tcp.srcport']))\n",
    "            dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    else line['org.dst']\n",
    "            dstport = str(int(line['tcp.dstport']))\n",
    "            payload = (line['http.request.method'] + \" \" if pd.notna(line['http.request.method']) else '') + \\\n",
    "                    (line['http.request.uri'] + \" \" if pd.notna(line['http.request.uri']) else '') + \\\n",
    "                    (line['http.user_agent'] + \" \" if pd.notna(line['http.user_agent']) else '') + \\\n",
    "                    (line['http.host'] if pd.notna(line['http.host']) else '')\n",
    "            #print(payload)\n",
    "            return \" \".join([src,srcport,proto,dst,dstport,payload])\n",
    "        \n",
    "        ## http:data\n",
    "        ## TODO: add http.file_data content\n",
    "        elif line['frame.protocols'].find('http:data') > 0:\n",
    "            proto = 'http:data'\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['tcp.srcport']))\n",
    "            dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    else line['org.dst']\n",
    "            dstport = str(int(line['tcp.dstport']))\n",
    "            payload = (line['http.request.method'] + \" \" if pd.notna(line['http.request.method']) else '') + \\\n",
    "                    (line['http.request.uri'] + \" \" if pd.notna(line['http.request.uri']) else '') + \\\n",
    "                    (line['http.user_agent'] + \" \" if pd.notna(line['http.user_agent']) else '') + \\\n",
    "                    (line['http.host'] if pd.notna(line['http.host']) else '')\n",
    "            #print(payload)\n",
    "            return \" \".join([src,srcport,proto,dst,dstport,payload])\n",
    "        \n",
    "        ## http:media\n",
    "        ## TODO: add  http.content_type , http.content_length\n",
    "        elif line['frame.protocols'].find('http:media') > 0:\n",
    "            proto = 'http:media'\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            srcport = str(int(line['tcp.srcport']))\n",
    "            dst =  \" \".join([line['org.dst'],line['tail.dst']]) if line['zone.dst'] == 'PRIVATE' \\\n",
    "                    else line['org.dst']\n",
    "            dstport = str(int(line['tcp.dstport']))\n",
    "            return \" \".join([src,srcport,proto,dst,dstport])\n",
    "        \n",
    "        \n",
    "        ## icmp:data\n",
    "        ## TODO: Consider adding icmp.type to the frame / protocol language\n",
    "        ## TODO: Detect broadcast\n",
    "        elif line['frame.protocols'].find('icmp:data') > 0:\n",
    "            proto = 'icmp:data'\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) if line['zone.src'] == 'PRIVATE' \\\n",
    "                    else line['org.src']\n",
    "            if line['zone.dst'] == 'PRIVATE':\n",
    "                if pd.notna(line['org.dst']):\n",
    "                    dst =  \" \".join([line['org.dst'],line['tail.dst']])\n",
    "                else:\n",
    "                    dst = \"\"\n",
    "            else:\n",
    "                dst = line['org.dst']\n",
    "            return \" \".join([src,proto,dst])\n",
    "        \n",
    "        ## ethertype:data\n",
    "        elif line['frame.protocols'].find('ethertype:data') > 0:\n",
    "            proto = 'ethertype:data'\n",
    "            src =  \" \".join([line['org.src'],line['tail.src']]) \n",
    "            return \" \".join([src,proto])\n",
    "        \n",
    "        # anything else\n",
    "        else:\n",
    "            print(\"undetected protocol: {}\".format(proto))\n",
    "    except ValueError:\n",
    "        print('Exception!!')\n",
    "        print(line)\n",
    "        \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.iloc[0,:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['sentence'] = df.apply(protoLang, axis=1)\n",
    "#df.loc[5391,:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "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>frame.number</th>\n",
       "      <th>frame.time</th>\n",
       "      <th>eth.src</th>\n",
       "      <th>eth.dst</th>\n",
       "      <th>frame.protocols</th>\n",
       "      <th>ip.src</th>\n",
       "      <th>ip.dst</th>\n",
       "      <th>tcp.srcport</th>\n",
       "      <th>tcp.dstport</th>\n",
       "      <th>udp.srcport</th>\n",
       "      <th>...</th>\n",
       "      <th>oui.src</th>\n",
       "      <th>tail.src</th>\n",
       "      <th>oui.dst</th>\n",
       "      <th>tail.dst</th>\n",
       "      <th>org.src</th>\n",
       "      <th>org.dst</th>\n",
       "      <th>zone.src</th>\n",
       "      <th>zone.dst</th>\n",
       "      <th>protocol</th>\n",
       "      <th>sentence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3377</th>\n",
       "      <td>3378</td>\n",
       "      <td>Apr 27, 2018 17:15:32.971135000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3378</th>\n",
       "      <td>3379</td>\n",
       "      <td>Apr 27, 2018 17:15:32.971555000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>ff:ff:ff:ff:ff:ff</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>255.255.255.255</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>FFFFFF</td>\n",
       "      <td>FFFFFF</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp ff:ff:ff:ff:ff:ff ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3399</th>\n",
       "      <td>3400</td>\n",
       "      <td>Apr 27, 2018 17:15:33.221432000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3400</th>\n",
       "      <td>3401</td>\n",
       "      <td>Apr 27, 2018 17:15:33.221768000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>ff:ff:ff:ff:ff:ff</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>255.255.255.255</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>FFFFFF</td>\n",
       "      <td>FFFFFF</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp ff:ff:ff:ff:ff:ff ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3436</th>\n",
       "      <td>3437</td>\n",
       "      <td>Apr 27, 2018 17:15:33.471876000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3437</th>\n",
       "      <td>3438</td>\n",
       "      <td>Apr 27, 2018 17:15:33.472259000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>ff:ff:ff:ff:ff:ff</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>255.255.255.255</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>FFFFFF</td>\n",
       "      <td>FFFFFF</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp ff:ff:ff:ff:ff:ff ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3457</th>\n",
       "      <td>3458</td>\n",
       "      <td>Apr 27, 2018 17:15:33.722665000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3468</th>\n",
       "      <td>3469</td>\n",
       "      <td>Apr 27, 2018 17:15:33.975031000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3482</th>\n",
       "      <td>3483</td>\n",
       "      <td>Apr 27, 2018 17:15:34.223393000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3488</th>\n",
       "      <td>3489</td>\n",
       "      <td>Apr 27, 2018 17:15:34.473780000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3543</th>\n",
       "      <td>3544</td>\n",
       "      <td>Apr 27, 2018 17:15:34.724105000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3711</th>\n",
       "      <td>3712</td>\n",
       "      <td>Apr 27, 2018 17:15:34.974486000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3749</th>\n",
       "      <td>3750</td>\n",
       "      <td>Apr 27, 2018 17:15:35.224905000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3791</th>\n",
       "      <td>3792</td>\n",
       "      <td>Apr 27, 2018 17:15:35.475168000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3858</th>\n",
       "      <td>3859</td>\n",
       "      <td>Apr 27, 2018 17:15:35.725545000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3968</th>\n",
       "      <td>3969</td>\n",
       "      <td>Apr 27, 2018 17:15:35.975856000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4046</th>\n",
       "      <td>4047</td>\n",
       "      <td>Apr 27, 2018 17:15:36.227352000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4222</th>\n",
       "      <td>4223</td>\n",
       "      <td>Apr 27, 2018 17:15:36.476842000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4335</th>\n",
       "      <td>4336</td>\n",
       "      <td>Apr 27, 2018 17:15:36.727189000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4343</th>\n",
       "      <td>4344</td>\n",
       "      <td>Apr 27, 2018 17:15:36.977786000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4368</th>\n",
       "      <td>4369</td>\n",
       "      <td>Apr 27, 2018 17:15:37.228475000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4371</th>\n",
       "      <td>4372</td>\n",
       "      <td>Apr 27, 2018 17:15:37.484796000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4458</th>\n",
       "      <td>4459</td>\n",
       "      <td>Apr 27, 2018 17:15:37.729409000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4497</th>\n",
       "      <td>4498</td>\n",
       "      <td>Apr 27, 2018 17:15:37.979980000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4518</th>\n",
       "      <td>4519</td>\n",
       "      <td>Apr 27, 2018 17:15:38.230577000 PDT</td>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>eth:ethertype:vlan:ethertype:ip:udp:ssdp</td>\n",
       "      <td>192.168.1.225</td>\n",
       "      <td>239.255.255.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43082.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7828CA</td>\n",
       "      <td>324B28</td>\n",
       "      <td>01005E</td>\n",
       "      <td>7FFFFA</td>\n",
       "      <td>Sonos-Inc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PRIVATE</td>\n",
       "      <td>RESERVED</td>\n",
       "      <td>ssdp</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>25 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      frame.number                           frame.time            eth.src  \\\n",
       "3377          3378  Apr 27, 2018 17:15:32.971135000 PDT  78:28:ca:32:4b:28   \n",
       "3378          3379  Apr 27, 2018 17:15:32.971555000 PDT  78:28:ca:32:4b:28   \n",
       "3399          3400  Apr 27, 2018 17:15:33.221432000 PDT  78:28:ca:32:4b:28   \n",
       "3400          3401  Apr 27, 2018 17:15:33.221768000 PDT  78:28:ca:32:4b:28   \n",
       "3436          3437  Apr 27, 2018 17:15:33.471876000 PDT  78:28:ca:32:4b:28   \n",
       "3437          3438  Apr 27, 2018 17:15:33.472259000 PDT  78:28:ca:32:4b:28   \n",
       "3457          3458  Apr 27, 2018 17:15:33.722665000 PDT  78:28:ca:32:4b:28   \n",
       "3468          3469  Apr 27, 2018 17:15:33.975031000 PDT  78:28:ca:32:4b:28   \n",
       "3482          3483  Apr 27, 2018 17:15:34.223393000 PDT  78:28:ca:32:4b:28   \n",
       "3488          3489  Apr 27, 2018 17:15:34.473780000 PDT  78:28:ca:32:4b:28   \n",
       "3543          3544  Apr 27, 2018 17:15:34.724105000 PDT  78:28:ca:32:4b:28   \n",
       "3711          3712  Apr 27, 2018 17:15:34.974486000 PDT  78:28:ca:32:4b:28   \n",
       "3749          3750  Apr 27, 2018 17:15:35.224905000 PDT  78:28:ca:32:4b:28   \n",
       "3791          3792  Apr 27, 2018 17:15:35.475168000 PDT  78:28:ca:32:4b:28   \n",
       "3858          3859  Apr 27, 2018 17:15:35.725545000 PDT  78:28:ca:32:4b:28   \n",
       "3968          3969  Apr 27, 2018 17:15:35.975856000 PDT  78:28:ca:32:4b:28   \n",
       "4046          4047  Apr 27, 2018 17:15:36.227352000 PDT  78:28:ca:32:4b:28   \n",
       "4222          4223  Apr 27, 2018 17:15:36.476842000 PDT  78:28:ca:32:4b:28   \n",
       "4335          4336  Apr 27, 2018 17:15:36.727189000 PDT  78:28:ca:32:4b:28   \n",
       "4343          4344  Apr 27, 2018 17:15:36.977786000 PDT  78:28:ca:32:4b:28   \n",
       "4368          4369  Apr 27, 2018 17:15:37.228475000 PDT  78:28:ca:32:4b:28   \n",
       "4371          4372  Apr 27, 2018 17:15:37.484796000 PDT  78:28:ca:32:4b:28   \n",
       "4458          4459  Apr 27, 2018 17:15:37.729409000 PDT  78:28:ca:32:4b:28   \n",
       "4497          4498  Apr 27, 2018 17:15:37.979980000 PDT  78:28:ca:32:4b:28   \n",
       "4518          4519  Apr 27, 2018 17:15:38.230577000 PDT  78:28:ca:32:4b:28   \n",
       "\n",
       "                eth.dst                           frame.protocols  \\\n",
       "3377  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3378  ff:ff:ff:ff:ff:ff  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3399  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3400  ff:ff:ff:ff:ff:ff  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3436  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3437  ff:ff:ff:ff:ff:ff  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3457  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3468  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3482  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3488  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3543  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3711  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3749  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3791  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3858  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "3968  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "4046  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "4222  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "4335  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "4343  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "4368  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "4371  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "4458  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "4497  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "4518  01:00:5e:7f:ff:fa  eth:ethertype:vlan:ethertype:ip:udp:ssdp   \n",
       "\n",
       "             ip.src           ip.dst  tcp.srcport  tcp.dstport  udp.srcport  \\\n",
       "3377  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "3378  192.168.1.225  255.255.255.255          NaN          NaN      43082.0   \n",
       "3399  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "3400  192.168.1.225  255.255.255.255          NaN          NaN      43082.0   \n",
       "3436  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "3437  192.168.1.225  255.255.255.255          NaN          NaN      43082.0   \n",
       "3457  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "3468  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "3482  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "3488  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "3543  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "3711  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "3749  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "3791  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "3858  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "3968  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "4046  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "4222  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "4335  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "4343  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "4368  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "4371  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "4458  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "4497  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "4518  192.168.1.225  239.255.255.250          NaN          NaN      43082.0   \n",
       "\n",
       "                            ...                          oui.src tail.src  \\\n",
       "3377                        ...                           7828CA   324B28   \n",
       "3378                        ...                           7828CA   324B28   \n",
       "3399                        ...                           7828CA   324B28   \n",
       "3400                        ...                           7828CA   324B28   \n",
       "3436                        ...                           7828CA   324B28   \n",
       "3437                        ...                           7828CA   324B28   \n",
       "3457                        ...                           7828CA   324B28   \n",
       "3468                        ...                           7828CA   324B28   \n",
       "3482                        ...                           7828CA   324B28   \n",
       "3488                        ...                           7828CA   324B28   \n",
       "3543                        ...                           7828CA   324B28   \n",
       "3711                        ...                           7828CA   324B28   \n",
       "3749                        ...                           7828CA   324B28   \n",
       "3791                        ...                           7828CA   324B28   \n",
       "3858                        ...                           7828CA   324B28   \n",
       "3968                        ...                           7828CA   324B28   \n",
       "4046                        ...                           7828CA   324B28   \n",
       "4222                        ...                           7828CA   324B28   \n",
       "4335                        ...                           7828CA   324B28   \n",
       "4343                        ...                           7828CA   324B28   \n",
       "4368                        ...                           7828CA   324B28   \n",
       "4371                        ...                           7828CA   324B28   \n",
       "4458                        ...                           7828CA   324B28   \n",
       "4497                        ...                           7828CA   324B28   \n",
       "4518                        ...                           7828CA   324B28   \n",
       "\n",
       "     oui.dst tail.dst    org.src org.dst zone.src  zone.dst protocol  \\\n",
       "3377  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3378  FFFFFF   FFFFFF  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3399  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3400  FFFFFF   FFFFFF  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3436  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3437  FFFFFF   FFFFFF  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3457  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3468  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3482  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3488  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3543  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3711  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3749  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3791  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3858  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "3968  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "4046  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "4222  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "4335  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "4343  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "4368  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "4371  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "4458  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "4497  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "4518  01005E   7FFFFA  Sonos-Inc     NaN  PRIVATE  RESERVED     ssdp   \n",
       "\n",
       "                                               sentence  \n",
       "3377  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "3378  Sonos-Inc 324B28 43082 ssdp ff:ff:ff:ff:ff:ff ...  \n",
       "3399  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "3400  Sonos-Inc 324B28 43082 ssdp ff:ff:ff:ff:ff:ff ...  \n",
       "3436  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "3437  Sonos-Inc 324B28 43082 ssdp ff:ff:ff:ff:ff:ff ...  \n",
       "3457  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "3468  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "3482  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "3488  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "3543  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "3711  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "3749  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "3791  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "3858  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "3968  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "4046  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "4222  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "4335  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "4343  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "4368  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "4371  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "4458  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "4497  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "4518  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...  \n",
       "\n",
       "[25 rows x 31 columns]"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['protocol'] == 'ssdp']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "34868\n"
     ]
    }
   ],
   "source": [
    "vocabulary = []\n",
    "for line in df['sentence'].tolist():\n",
    "    vocabulary += line.split()\n",
    "print(len(vocabulary))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "374\n",
      "{'1900', '52712', '59553', '59560', 'CA', '49406', '59631', '324B28', '59636', 'icmpv6', 'HL-L2380DW', '59545', 'COMCAST-7922', '34858', '67', 'pagead.l.doubleclick.net', 'Network,(c)', '59556', 'audio-fac.spotify.com', '44689', '18964', '172.217.6.78', 'tcp:data', 's3-directional-w.amazonaws.com,s3-1-w.amazonaws.com', 'www.example.net', 'ntp-g7g.amazon.com,ntp-g7g.amazon.com', '11046', '59498', '58270', '59637', '59549', '52518', '59565', '34.225.16.57,52.87.128.190,54.85.228.100,54.173.62.245,54.209.14.90,54.236.91.5,54.84.159.64,54.164.74.173', '59627', '49079', 'clients.l.google.com', '80', '41662', '61502', '46201', 'TV._raop._tcp.local,Apple', '46872', '35608', 'dns', 'Symantec', 'Root', '59586', '59532', 'Class', '54.239.27.11', 'OPENDNS', 'use', 'G4,SymantecPKI-1-534Washington,Seattle,Amazon.com,', 'x509ce', 'SHA2', '36313', '3', 'Inc.,dp-gw-na.amazon.com', 'f.d.4.b.1.3.e.f.f.f.c.9.c.7.6.b.0.0.0.0.0.0.0.0.0.0.0.0.0.8.e.f.ip6.arpa,linux-6.local,220.1.168.192.in-addr.arpa', '18002', 'googleads.g.doubleclick.net,pagead46.l.doubleclick.net', '59536', 'Secure', '59632', '59647', '137', '59629', '57659', '59530', 'Inc.,pindorama.amazon.com,DigiCert', 'AS-CRITEO', '27771', '98.138.219.231,98.137.246.7,98.138.219.232,72.30.35.9,72.30.35.10,98.137.246.8', '51570', 'F6EB45', '104.154.127.218,104.154.127.109,104.154.126.22,104.154.126.226,104.154.126.159,104.154.127.143,104.154.126.214,104.154.127.2', '61523', '57621', '59581', '60210', '31912', '44960', 'Amazon-Technologies-Inc', '34.200.197.98,34.203.4.213,34.201.237.220,34.195.83.108,52.86.162.125,34.232.10.102,52.21.35.32,34.206.251.70', 'SA,DigiCert', 'linux-6.local,220.1.168.192.in-addr.arpa,linux-6.local,f.d.4.b.1.3.e.f.f.f.c.9.c.7.6.b.0.0.0.0.0.0.0.0.0.0.0.0.0.8.e.f.ip6.arpa', '59562', '2278', '59528', 'www.googleadservices.com', 'DigiCert', '25008', 'http:data', '8213', '5715', '44353', 'TV._airplay._tcp.local,Apple-TV.local,Apple-TV.local,40CBC0BC367E@Apple', '55151', '52.216.160.59', 'ssdp', '49386', '53721', '0.1.0.0.0.0.0.0.0.0.0.0.0.0.0.0.6.3.8.1.8.5.0.0.8.9.9.4.1.0.0.2.ip6.arpa', 'Authority', '7011', '5021', 'Sonos;One;;;2.56.45', 'TWITTER', '59571', 'mdns', '40CBC0BC367E@Apple', '59645', '_sleep-proxy._udp.local', '35476', 'Inc.,VeriSign', 'ap.gslb.spotify.com', 'FASTLY', '59552', 'D2734B', '38122', '52.46.156.66', 'M5HOSTING', '59583', 'CMCS', '0D8718', '59577', '172.217.6.46', '8080', '57896', '59568', '216.58.195.226', 'pindorama.amazon.com', '59610', 's.amazon-adsystem.com', 'tcp', '33434', 'G2,Washington,Seattle,Amazon.com,', '46114', '59576', 'udp:nbns', 'ap.spotify.com,ap.gslb.spotify.com,ap.gslb.spotify.com,ap.gslb.spotify.com,ap.gslb.spotify.com,ap.gslb.spotify.com,ap.gslb.spotify.com,ap.gslb.spotify.com,ap.gslb.spotify.com', 'dp-gw-na.amazon.com', '50657', '48401', 'VeriSign,', 'tcp:stun', '59547', '59550', 'G5,Symantec', '90603A', '68', 'Sonos-Inc', '192.168.1.220', 'CLOUDFLARENET', 'pagead46.l.doubleclick.net', '03800C', 'ntp-g7g.amazon.com', '59644', '59642', '46109', '37418', 'GET', 'MICROSOFT-CORP-MSN-AS-BLOCK', '72.21.192.213,207.171.178.6', '31D358', '52313', '17500', '3478', '58841', 'For', '47363', '93.184.216.34', 'BC367E', 'Java', '52167', '34949', '59621', '231.219.138.98.in-addr.arpa', '47366', '8.246.137.98.in-addr.arpa', '59635', '61526', 'cm.g.doubleclick.net,pagead.l.doubleclick.net', 'db-lsp-disc', 'Belkin-International-Inc', '38295', '44822', '39458', 'www.bestwestern.com', '/audio/7fd0fe06a28db9706f4bda8b54b1163b6dc8e6c4?1524877897_1OEE149cqh4/+6CJbhcvk2Qr2bpaxoZ8jp5KhMnkA/U=', '56677', 'Global', '0625AD', '59535', '46108', 'Inc.', '41371', 'Network,Symantec', '34.237.248.252,34.227.212.11,34.236.201.111,34.237.251.227,34.231.49.61,34.232.145.203,34.224.157.55,34.235.222.134', 'EDGECAST', '11588', 'docs.google.com', 'ICME', '59525', 'series._pdl-datastream._tcp.local,Brother', 'prod.amcs-tachyon.com', '53', '48673', '59551', '58824', 'Public', '59555', '59524', 'AS8075', '3558', 'clients6.google.com,clients.l.google.com', '31B4DF', '59626', '59548', 'f.d.4.b.1.3.e.f.f.f.c.9.c.7.6.b.0.0.0.0.0.0.0.0.0.0.0.0.0.8.e.f.ip6.arpa,linux-6.local,220.1.168.192.in-addr.arpa,linux-6.local', 'www.googleadservices.com,pagead.l.doubleclick.net', 'dcape-na.amazon.com', '59489', '239.255.255.250', 'G4,VeriSign,', 'authorized', 'Trust', '59474', 'CA,Paris,Criteo', 'ssl', '5353', '407CA6', '46117', 'DROPBOX', '8CDA3C', '57006', 'Brother', 'udp:bootp', '59633', '33656', '7D01CF', 'yahoo.com,yahoo.com,yahoo.com,yahoo.com,yahoo.com,yahoo.com', 'drive.google.com', 'Nintendo-CoLtd', '_sleep-proxy._udp.local,_sleep-proxy._udp.local,<Root>', '56542', '4070', 'TV._airplay._tcp.local,Apple-TV.local,<Root>', '123', '56366', '14508', 'B76043', '59641', '104.86.200.123', '024121', '172.217.5.98', 'Apple-Inc', 'AMAZON-02', '59561', 'icmp:data', '60663', '46110', '_sleep-proxy._udp.local,<Root>', 'CA,DigiCert', '59531', '58952', 'Inc.,dcape-na.amazon.com', 'Certification', '5228', 'yahoo.com', 'GOOGLE', '192.168.1.223', '7D01CE', '59534', '52811', '46115', 'series._pdl-datastream._tcp.local', '443', '4C87A0', 'stp', 'Inc,www.digicert.com,DigiCert', '59559', '59184', '255.255.255.255', '58219', '57652', 'igmp', '65105', 'AMAZON-AES', '59510', 'spectrum.s3.amazonaws.com,s3-directional-w.amazonaws.com,s3-1-w.amazonaws.com', 'ap.spotify.com', '59634', 'Nest-Labs-Inc', '48692', 'cm.g.doubleclick.net', '52.216.1.64', 'Cisco-Meraki', 'Inc.,prod.amcs-tachyon.com', '5A7040', 'G5,DigiCert', 'prod.amcs-tachyon.com,prod.amcs-tachyon.com,prod.amcs-tachyon.com,prod.amcs-tachyon.com,prod.amcs-tachyon.com,prod.amcs-tachyon.com,prod.amcs-tachyon.com,prod.amcs-tachyon.com', '59628', '65461', 'Primary', 'arp', 'udp:gquic', 'Android.local,Android.local', '59638', '12995', '34413', '59630', 'clients6.google.com', 'www.example.com', 'spectrum.s3.amazonaws.com', '57396', 'Android.local,Android.local,Android.local', '59533', '/kindle-wifi/wifistub-echo.html', 'http', '122F2D', '57901', '-', '59554', '9350', '58610', '59578', '65474', 'udp:data', '59558', 'ntp', '61111', '59557', 'Inc,DigiCert', '54.239.17.112', 'SA,CRITEO', '59639', 'CA*.criteo.com', 'AS-TIERP-36024', 'googleads.g.doubleclick.net', 'Server', 'AA4739', '59643', '34948', '2006', 'AA472E', '59543', '44184', 'G2', '216.58.195.66', 'only,VeriSign', 'G2,DigiCert', '59438', '43082', '49181', 'www.example.org', '4C9286', '57FC8F', 'Corporation,Symantec', '50595', 'http:media', '59646', '62476', '54.239.27.116', '36914', '59611', '59588', 'ethertype:data', '41898', 'G2,VeriSign,', 'FACEBOOK', '61544', '72.21.206.140'}\n"
     ]
    }
   ],
   "source": [
    "unique_words = set(vocabulary)\n",
    "print(len(unique_words))\n",
    "print(unique_words)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "src_agg_df = df.groupby('eth.src')['sentence'].agg(lambda x: \"\".join(x + \" <EOS> \")).reset_index()\n",
    "dst_agg_df = df.groupby('eth.dst')['sentence'].agg(lambda x: \"\".join(x + \" <EOS> \")).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_agg_df = src_agg_df.merge(dst_agg_df, left_on='eth.src', right_on='eth.dst', how = 'outer')\n",
    "final_agg_df.columns = [\"eth.src\", \"sentence.src\", \"eth.dst\", \"sentence.dst\"]\n",
    "#TODO: Filter reserved MAC ranges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_agg_df['eth'] = final_agg_df[['eth.src','eth.dst']]\\\n",
    "                        .apply(lambda row: row['eth.src'] if pd.notna(row['eth.src']) else row['eth.dst'], axis=1)\n",
    "\n",
    "#TODO: Redo merging sentences so the sequence of the language is preserved\n",
    "final_agg_df['sentence'] = final_agg_df[['sentence.src', 'sentence.dst']]\\\n",
    "        .apply(lambda row: (row['sentence.src'] + \" \" if pd.notna(row['sentence.src']) else \"\") +\\\n",
    "                           (row['sentence.dst'] + \" \" if pd.notna(row['sentence.dst']) else \"\"), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_agg_df = final_agg_df.drop(['eth.src','sentence.src','eth.dst','sentence.dst'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "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>eth</th>\n",
       "      <th>sentence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00:18:0a:12:2f:2d</td>\n",
       "      <td>Cisco-Meraki 122F2D arp Cisco-Meraki 024121 &lt;E...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00:18:0a:7d:01:ce</td>\n",
       "      <td>Cisco-Meraki 7D01CE icmp:data GOOGLE &lt;EOS&gt; Cis...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00:18:0a:7d:01:cf</td>\n",
       "      <td>Cisco-Meraki 7D01CF stp &lt;EOS&gt; Cisco-Meraki 7D0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18:b4:30:0d:87:18</td>\n",
       "      <td>Nest-Labs-Inc 0D8718 arp Cisco-Meraki 024121 &lt;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18:f6:43:57:fc:8f</td>\n",
       "      <td>Apple-Inc 57FC8F arp Cisco-Meraki 024121 &lt;EOS&gt;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>28:f0:76:31:d3:58</td>\n",
       "      <td>Apple-Inc 31D358 65474 tcp GOOGLE 443 &lt;EOS&gt; Ap...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>38:c9:86:40:7c:a6</td>\n",
       "      <td>Apple-Inc 407CA6 arp Cisco-Meraki 024121 &lt;EOS&gt;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>40:cb:c0:bc:36:7e</td>\n",
       "      <td>Apple-Inc BC367E icmpv6 &lt;EOS&gt; Apple-Inc BC367E...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>44:65:0d:90:60:3a</td>\n",
       "      <td>Amazon-Technologies-Inc 90603A 55151 udp:data ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5c:aa:fd:4c:87:a0</td>\n",
       "      <td>Sonos-Inc 4C87A0 arp Cisco-Meraki 024121 &lt;EOS&gt;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>5c:aa:fd:4c:92:86</td>\n",
       "      <td>Sonos-Inc 4C9286 ethertype:data &lt;EOS&gt; Sonos-In...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>64:b5:c6:b7:60:43</td>\n",
       "      <td>Nintendo-CoLtd B76043 arp Cisco-Meraki 024121 ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>78:28:ca:03:80:0c</td>\n",
       "      <td>Sonos-Inc 03800C arp Cisco-Meraki 024121 &lt;EOS&gt;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>78:28:ca:32:4b:28</td>\n",
       "      <td>Sonos-Inc 324B28 arp Cisco-Meraki 024121 &lt;EOS&gt;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>84:38:35:5a:70:40</td>\n",
       "      <td>Apple-Inc 5A7040 59554 ssl FASTLY 443 &lt;EOS&gt; Ap...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>88:15:44:aa:47:2e</td>\n",
       "      <td>Cisco-Meraki AA472E arp Cisco-Meraki 024121 &lt;E...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>88:15:44:aa:47:39</td>\n",
       "      <td>Cisco-Meraki AA4739 35608 udp:data AS-TIERP-36...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>88:71:e5:d2:73:4b</td>\n",
       "      <td>Amazon-Technologies-Inc D2734B arp Cisco-Merak...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>b4:7c:9c:31:b4:df</td>\n",
       "      <td>Amazon-Technologies-Inc 31B4DF icmp:data Cisco...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>b8:e9:37:8c:da:3c</td>\n",
       "      <td>Sonos-Inc 8CDA3C arp Cisco-Meraki 024121 &lt;EOS&gt;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>d0:4f:7e:06:25:ad</td>\n",
       "      <td>Apple-Inc 0625AD arp Cisco-Meraki 024121 &lt;EOS&gt;...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>e0:55:3d:02:41:21</td>\n",
       "      <td>FASTLY 443 tcp Apple-Inc 5A7040 59554 &lt;EOS&gt; GO...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>ec:1a:59:f6:eb:45</td>\n",
       "      <td>Belkin-International-Inc F6EB45 3558 tcp:stun ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>01:00:5e:00:00:16</td>\n",
       "      <td>Amazon-Technologies-Inc 31B4DF igmp 01:00:5e:0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>01:00:5e:00:00:fb</td>\n",
       "      <td>Apple-Inc BC367E 5353 mdns 40CBC0BC367E@Apple ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>01:00:5e:7f:ff:fa</td>\n",
       "      <td>Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>01:80:c2:00:00:00</td>\n",
       "      <td>Cisco-Meraki 7D01CF stp &lt;EOS&gt; Cisco-Meraki 7D0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>33:33:00:00:00:02</td>\n",
       "      <td>Apple-Inc BC367E icmpv6 &lt;EOS&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>33:33:00:00:00:fb</td>\n",
       "      <td>Apple-Inc BC367E 5353 mdns 40CBC0BC367E@Apple ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>ff:ff:ff:ff:ff:ff</td>\n",
       "      <td>Sonos-Inc 4C9286 ethertype:data &lt;EOS&gt; Apple-In...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  eth                                           sentence\n",
       "0   00:18:0a:12:2f:2d  Cisco-Meraki 122F2D arp Cisco-Meraki 024121 <E...\n",
       "1   00:18:0a:7d:01:ce  Cisco-Meraki 7D01CE icmp:data GOOGLE <EOS> Cis...\n",
       "2   00:18:0a:7d:01:cf  Cisco-Meraki 7D01CF stp <EOS> Cisco-Meraki 7D0...\n",
       "3   18:b4:30:0d:87:18  Nest-Labs-Inc 0D8718 arp Cisco-Meraki 024121 <...\n",
       "4   18:f6:43:57:fc:8f  Apple-Inc 57FC8F arp Cisco-Meraki 024121 <EOS>...\n",
       "5   28:f0:76:31:d3:58  Apple-Inc 31D358 65474 tcp GOOGLE 443 <EOS> Ap...\n",
       "6   38:c9:86:40:7c:a6  Apple-Inc 407CA6 arp Cisco-Meraki 024121 <EOS>...\n",
       "7   40:cb:c0:bc:36:7e  Apple-Inc BC367E icmpv6 <EOS> Apple-Inc BC367E...\n",
       "8   44:65:0d:90:60:3a  Amazon-Technologies-Inc 90603A 55151 udp:data ...\n",
       "9   5c:aa:fd:4c:87:a0  Sonos-Inc 4C87A0 arp Cisco-Meraki 024121 <EOS>...\n",
       "10  5c:aa:fd:4c:92:86  Sonos-Inc 4C9286 ethertype:data <EOS> Sonos-In...\n",
       "11  64:b5:c6:b7:60:43  Nintendo-CoLtd B76043 arp Cisco-Meraki 024121 ...\n",
       "12  78:28:ca:03:80:0c  Sonos-Inc 03800C arp Cisco-Meraki 024121 <EOS>...\n",
       "13  78:28:ca:32:4b:28  Sonos-Inc 324B28 arp Cisco-Meraki 024121 <EOS>...\n",
       "14  84:38:35:5a:70:40  Apple-Inc 5A7040 59554 ssl FASTLY 443 <EOS> Ap...\n",
       "15  88:15:44:aa:47:2e  Cisco-Meraki AA472E arp Cisco-Meraki 024121 <E...\n",
       "16  88:15:44:aa:47:39  Cisco-Meraki AA4739 35608 udp:data AS-TIERP-36...\n",
       "17  88:71:e5:d2:73:4b  Amazon-Technologies-Inc D2734B arp Cisco-Merak...\n",
       "18  b4:7c:9c:31:b4:df  Amazon-Technologies-Inc 31B4DF icmp:data Cisco...\n",
       "19  b8:e9:37:8c:da:3c  Sonos-Inc 8CDA3C arp Cisco-Meraki 024121 <EOS>...\n",
       "20  d0:4f:7e:06:25:ad  Apple-Inc 0625AD arp Cisco-Meraki 024121 <EOS>...\n",
       "21  e0:55:3d:02:41:21  FASTLY 443 tcp Apple-Inc 5A7040 59554 <EOS> GO...\n",
       "22  ec:1a:59:f6:eb:45  Belkin-International-Inc F6EB45 3558 tcp:stun ...\n",
       "23  01:00:5e:00:00:16  Amazon-Technologies-Inc 31B4DF igmp 01:00:5e:0...\n",
       "24  01:00:5e:00:00:fb  Apple-Inc BC367E 5353 mdns 40CBC0BC367E@Apple ...\n",
       "25  01:00:5e:7f:ff:fa  Sonos-Inc 324B28 43082 ssdp 01:00:5e:7f:ff:fa ...\n",
       "26  01:80:c2:00:00:00  Cisco-Meraki 7D01CF stp <EOS> Cisco-Meraki 7D0...\n",
       "27  33:33:00:00:00:02                    Apple-Inc BC367E icmpv6 <EOS>  \n",
       "28  33:33:00:00:00:fb  Apple-Inc BC367E 5353 mdns 40CBC0BC367E@Apple ...\n",
       "29  ff:ff:ff:ff:ff:ff  Sonos-Inc 4C9286 ethertype:data <EOS> Apple-In..."
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#final_agg_df[final_agg_df['eth.src'] == '5c:aa:fd:4c:92:86']\n",
    "final_agg_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_agg_df = final_agg_df.merge(known_devices[list(['MAC address','Manufacturer Device Type'])], \n",
    "             left_on = 'eth',  right_on= 'MAC address', how = 'inner').drop(['eth','MAC address'], axis=1)\n",
    "final_agg_df.columns = ['sentence','type']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [
    {
     "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>sentence</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Cisco-Meraki 122F2D arp Cisco-Meraki 024121 &lt;E...</td>\n",
       "      <td>Meraki Phone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nest-Labs-Inc 0D8718 arp Cisco-Meraki 024121 &lt;...</td>\n",
       "      <td>Nest Labs Thermostat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Apple-Inc 57FC8F arp Cisco-Meraki 024121 &lt;EOS&gt;...</td>\n",
       "      <td>Apple iPhone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Apple-Inc 31D358 65474 tcp GOOGLE 443 &lt;EOS&gt; Ap...</td>\n",
       "      <td>Apple Computer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Apple-Inc 407CA6 arp Cisco-Meraki 024121 &lt;EOS&gt;...</td>\n",
       "      <td>Apple Computer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            sentence                  type\n",
       "0  Cisco-Meraki 122F2D arp Cisco-Meraki 024121 <E...          Meraki Phone\n",
       "1  Nest-Labs-Inc 0D8718 arp Cisco-Meraki 024121 <...  Nest Labs Thermostat\n",
       "2  Apple-Inc 57FC8F arp Cisco-Meraki 024121 <EOS>...          Apple iPhone\n",
       "3  Apple-Inc 31D358 65474 tcp GOOGLE 443 <EOS> Ap...        Apple Computer\n",
       "4  Apple-Inc 407CA6 arp Cisco-Meraki 024121 <EOS>...        Apple Computer"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_agg_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Apple-Inc 31D358 65474 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 61502 tcp:data GOOGLE 4070 <EOS> Apple-Inc 31D358 52167 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 61502 tcp GOOGLE 4070 <EOS> Apple-Inc 31D358 62476 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 61502 tcp:data GOOGLE 4070 <EOS> Apple-Inc 31D358 arp  <EOS> Apple-Inc 31D358 61502 tcp GOOGLE 4070 <EOS> Apple-Inc 31D358 61544 tcp GOOGLE 5228 <EOS> Apple-Inc 31D358 arp  <EOS> Apple-Inc 31D358 52167 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 65461 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 arp  <EOS> Apple-Inc 31D358 65474 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 49386 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 arp  <EOS> Apple-Inc 31D358 62476 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 arp  <EOS> Apple-Inc 31D358 65105 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 65461 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 57659 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 57659 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 57659 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 65474 ssl GOOGLE 443 <EOS> Apple-Inc 31D358 65474 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 65474 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 65474 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 61502 tcp:data GOOGLE 4070 <EOS> Apple-Inc 31D358 57659 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 61502 tcp GOOGLE 4070 <EOS> Apple-Inc 31D358 61502 tcp GOOGLE 4070 <EOS> Apple-Inc 31D358 50657 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 50657 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 62476 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 50657 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 50657 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 50657 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 50657 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 50657 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 50657 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 57659 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 50657 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 50657 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 57659 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 57659 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 50657 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 57659 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 61502 tcp:data GOOGLE 4070 <EOS> Apple-Inc 31D358 57659 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 61502 tcp GOOGLE 4070 <EOS> Apple-Inc 31D358 65461 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 arp Cisco-Meraki 024121 <EOS> Apple-Inc 31D358 52167 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 65105 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 52167 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 49406 tcp CMCS 443 <EOS> Apple-Inc 31D358 65474 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 52167 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 52167 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 62476 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 61502 tcp GOOGLE 4070 <EOS> Apple-Inc 31D358 61526 ssl AMAZON-AES 443 <EOS> Apple-Inc 31D358 61523 ssl AMAZON-AES 443 <EOS> Apple-Inc 31D358 61502 tcp:data GOOGLE 4070 <EOS> Apple-Inc 31D358 61502 tcp GOOGLE 4070 <EOS> Apple-Inc 31D358 25008 dns OPENDNS 53 clients6.google.com  <EOS> Apple-Inc 31D358 57396 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 57396 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 57396 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 57396 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 57396 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 58952 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 65461 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 58952 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 58952 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 58952 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 58952 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 58952 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 49386 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 49386 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 49386 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 58952 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 65474 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 62476 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 17500 db-lsp-disc 17500 <EOS> Apple-Inc 31D358 17500 db-lsp-disc 17500 <EOS> Apple-Inc 31D358 17500 db-lsp-disc 17500 <EOS> Apple-Inc 31D358 61526 ssl AMAZON-AES 443 <EOS> Apple-Inc 31D358 61523 ssl AMAZON-AES 443 <EOS> Apple-Inc 31D358 61523 tcp AMAZON-AES 443 <EOS> Apple-Inc 31D358 61526 tcp AMAZON-AES 443 <EOS> Apple-Inc 31D358 65461 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 57396 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 58952 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 58952 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 58952 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 57396 udp:gquic GOOGLE 443 <EOS> Apple-Inc 31D358 61502 tcp:data GOOGLE 4070 <EOS> Apple-Inc 31D358 65474 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 61502 tcp GOOGLE 4070 <EOS> Apple-Inc 31D358 61502 tcp:data GOOGLE 4070 <EOS> Apple-Inc 31D358 61502 tcp GOOGLE 4070 <EOS> Apple-Inc 31D358 62476 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 61502 tcp:data GOOGLE 4070 <EOS> Apple-Inc 31D358 61502 tcp GOOGLE 4070 <EOS> Apple-Inc 31D358 65105 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 57659 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 52167 udp:data GOOGLE 443 <EOS> Apple-Inc 31D358 65461 tcp GOOGLE 443 <EOS> Apple-Inc 31D358 arp Cisco-Meraki 024121 <EOS>  GOOGLE 443 tcp Apple-Inc 31D358 65474 <EOS> GOOGLE 4070 tcp:data Apple-Inc 31D358 61502 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 52167 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 62476 <EOS> GOOGLE 4070 tcp:data Apple-Inc 31D358 61502 <EOS> GOOGLE 5228 tcp Apple-Inc 31D358 61544 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 52167 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 52167 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 65461 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 65474 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 49386 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 62476 <EOS> GOOGLE 443 ssl Apple-Inc 31D358 65105 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 65461 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 65474 <EOS> GOOGLE 443 ssl Apple-Inc 31D358 65474 <EOS> GOOGLE 443 ssl Apple-Inc 31D358 65474 <EOS> GOOGLE 443 ssl Apple-Inc 31D358 65474 <EOS> GOOGLE 4070 tcp:data Apple-Inc 31D358 61502 <EOS> GOOGLE 4070 tcp:data Apple-Inc 31D358 61502 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 62476 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 50657 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 50657 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 50657 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 50657 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 50657 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 50657 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 50657 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 50657 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 50657 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 50657 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 50657 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 4070 tcp:data Apple-Inc 31D358 61502 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 65461 <EOS> Cisco-Meraki 024121 arp Apple-Inc 31D358 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 52167 <EOS> GOOGLE 443 ssl Apple-Inc 31D358 65105 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 52167 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 52167 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 52167 <EOS> CMCS 443 tcp Apple-Inc 31D358 49406 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 65474 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 52167 <EOS> GOOGLE 443 ssl Apple-Inc 31D358 62476 <EOS> GOOGLE 4070 tcp:data Apple-Inc 31D358 61502 <EOS> AMAZON-AES 443 tcp Apple-Inc 31D358 61526 <EOS> AMAZON-AES 443 tcp Apple-Inc 31D358 61523 <EOS> GOOGLE 4070 tcp:data Apple-Inc 31D358 61502 <EOS> OPENDNS 53 dns Apple-Inc 31D358 25008 clients6.google.com clients6.google.com,clients.l.google.com clients.l.google.com 172.217.6.46 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 57396 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 57396 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 57396 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 57396 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 57396 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 57396 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 58952 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 65461 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 58952 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 58952 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 58952 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 58952 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 58952 <EOS> GOOGLE 443 ssl Apple-Inc 31D358 49386 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 49386 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 49386 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 65474 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 62476 <EOS> AMAZON-AES 443 tcp Apple-Inc 31D358 61526 <EOS> AMAZON-AES 443 ssl Apple-Inc 31D358 61526 <EOS> AMAZON-AES 443 tcp Apple-Inc 31D358 61523 <EOS> AMAZON-AES 443 ssl Apple-Inc 31D358 61523 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 65461 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 57396 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 58952 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 58952 <EOS> GOOGLE 443 udp:gquic Apple-Inc 31D358 58952 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 65474 <EOS> GOOGLE 4070 tcp:data Apple-Inc 31D358 61502 <EOS> GOOGLE 4070 tcp:data Apple-Inc 31D358 61502 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 62476 <EOS> GOOGLE 4070 tcp:data Apple-Inc 31D358 61502 <EOS> GOOGLE 443 ssl Apple-Inc 31D358 65105 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 57659 <EOS> GOOGLE 443 udp:data Apple-Inc 31D358 52167 <EOS> GOOGLE 443 tcp Apple-Inc 31D358 65461 <EOS> Cisco-Meraki 024121 arp Apple-Inc 31D358 <EOS>  '"
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_agg_df.loc[3,'sentence']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LabelEncoder()"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn import preprocessing\n",
    "le = preprocessing.LabelEncoder()\n",
    "le.fit(final_agg_df['type'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Amazon Technologies Echo', 'Apple Computer', 'Apple TV',\n",
       "       'Apple iPhone', 'Belkin Wallplug', 'Meraki Phone',\n",
       "       'Nest Labs Thermostat', 'Nintendo Switch', 'Sonos Speaker'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "le.classes_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [
    {
     "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>sentence</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Cisco-Meraki 122F2D arp Cisco-Meraki 024121 &lt;E...</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nest-Labs-Inc 0D8718 arp Cisco-Meraki 024121 &lt;...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Apple-Inc 57FC8F arp Cisco-Meraki 024121 &lt;EOS&gt;...</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Apple-Inc 31D358 65474 tcp GOOGLE 443 &lt;EOS&gt; Ap...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Apple-Inc 407CA6 arp Cisco-Meraki 024121 &lt;EOS&gt;...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            sentence  label\n",
       "0  Cisco-Meraki 122F2D arp Cisco-Meraki 024121 <E...      5\n",
       "1  Nest-Labs-Inc 0D8718 arp Cisco-Meraki 024121 <...      6\n",
       "2  Apple-Inc 57FC8F arp Cisco-Meraki 024121 <EOS>...      3\n",
       "3  Apple-Inc 31D358 65474 tcp GOOGLE 443 <EOS> Ap...      1\n",
       "4  Apple-Inc 407CA6 arp Cisco-Meraki 024121 <EOS>...      1"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "le.transform(final_agg_df['type'])\n",
    "final_agg_df['label'] = le.transform(final_agg_df['type'])\n",
    "final_agg_df = final_agg_df.drop(['type'], axis=1)\n",
    "final_agg_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9"
      ]
     },
     "execution_count": 193,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(le.classes_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Are all devices there?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [
    {
     "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>sentence</th>\n",
       "      <th>labels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Cisco-Meraki 122F2D arp Cisco-Meraki 024121 &lt;E...</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Nest-Labs-Inc 0D8718 arp Cisco-Meraki 024121 &lt;...</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Apple-Inc 57FC8F arp Cisco-Meraki 024121 &lt;EOS&gt;...</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Apple-Inc 31D358 65474 tcp GOOGLE 443 &lt;EOS&gt; Ap...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Apple-Inc 407CA6 arp Cisco-Meraki 024121 &lt;EOS&gt;...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            sentence  labels\n",
       "0  Cisco-Meraki 122F2D arp Cisco-Meraki 024121 <E...       5\n",
       "1  Nest-Labs-Inc 0D8718 arp Cisco-Meraki 024121 <...       6\n",
       "2  Apple-Inc 57FC8F arp Cisco-Meraki 024121 <EOS>...       3\n",
       "3  Apple-Inc 31D358 65474 tcp GOOGLE 443 <EOS> Ap...       1\n",
       "4  Apple-Inc 407CA6 arp Cisco-Meraki 024121 <EOS>...       1"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_agg_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let the training start"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tensorflow as tf\n",
    "import tensorflow_hub as hub\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# Training input on the whole training set with no limit on training epochs.\n",
    "train_input_fn = tf.estimator.inputs.pandas_input_fn(\n",
    "    final_agg_df, final_agg_df[\"label\"], num_epochs=None, shuffle=True)\n",
    "\n",
    "# Prediction on the whole training set.\n",
    "predict_train_input_fn = tf.estimator.inputs.pandas_input_fn(\n",
    "    final_agg_df, final_agg_df[\"label\"], shuffle=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [],
   "source": [
    "predict_test_input_fn = tf.estimator.inputs.pandas_input_fn(\n",
    "    final_agg_df, final_agg_df[\"label\"], shuffle=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [],
   "source": [
    "embedded_text_feature_column = hub.text_embedding_column(\n",
    "    key=\"sentence\", \n",
    "    module_spec=\"https://tfhub.dev/google/nnlm-en-dim128/1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO:tensorflow:Using default config.\n",
      "WARNING:tensorflow:Using temporary folder as model directory: /var/folders/2r/9kw2cykj317_f5bzwgspyfbr0000gn/T/tmprhiedh9g\n",
      "INFO:tensorflow:Using config: {'_model_dir': '/var/folders/2r/9kw2cykj317_f5bzwgspyfbr0000gn/T/tmprhiedh9g', '_tf_random_seed': None, '_save_summary_steps': 100, '_save_checkpoints_steps': None, '_save_checkpoints_secs': 600, '_session_config': None, '_keep_checkpoint_max': 5, '_keep_checkpoint_every_n_hours': 10000, '_log_step_count_steps': 100, '_train_distribute': None, '_service': None, '_cluster_spec': <tensorflow.python.training.server_lib.ClusterSpec object at 0x128949748>, '_task_type': 'worker', '_task_id': 0, '_global_id_in_cluster': 0, '_master': '', '_evaluation_master': '', '_is_chief': True, '_num_ps_replicas': 0, '_num_worker_replicas': 1}\n"
     ]
    }
   ],
   "source": [
    "estimator = tf.estimator.DNNClassifier(\n",
    "    hidden_units=[500, 100],\n",
    "    feature_columns=[embedded_text_feature_column],\n",
    "    n_classes=len(le.classes_),\n",
    "    optimizer=tf.train.AdagradOptimizer(learning_rate=0.003))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO:tensorflow:Calling model_fn.\n",
      "INFO:tensorflow:Initialize variable dnn/input_from_feature_columns/input_layer/sentence_hub_module_embedding/module/embeddings/part_0:0 from checkpoint b'/var/folders/2r/9kw2cykj317_f5bzwgspyfbr0000gn/T/tfhub_modules/32f2b2259e1cc8ca58c876921748361283e73997/variables/variables' with embeddings\n",
      "INFO:tensorflow:Done calling model_fn.\n",
      "INFO:tensorflow:Create CheckpointSaverHook.\n",
      "INFO:tensorflow:Graph was finalized.\n",
      "INFO:tensorflow:Running local_init_op.\n",
      "INFO:tensorflow:Done running local_init_op.\n",
      "INFO:tensorflow:Saving checkpoints for 1 into /var/folders/2r/9kw2cykj317_f5bzwgspyfbr0000gn/T/tmprhiedh9g/model.ckpt.\n",
      "INFO:tensorflow:loss = 317.35834, step = 1\n",
      "INFO:tensorflow:global_step/sec: 15.8828\n",
      "INFO:tensorflow:loss = 5.729344, step = 101 (6.297 sec)\n",
      "INFO:tensorflow:global_step/sec: 15.7079\n",
      "INFO:tensorflow:loss = 2.2258756, step = 201 (6.366 sec)\n",
      "INFO:tensorflow:global_step/sec: 15.4928\n",
      "INFO:tensorflow:loss = 1.0092354, step = 301 (6.455 sec)\n",
      "INFO:tensorflow:global_step/sec: 15.3951\n",
      "INFO:tensorflow:loss = 0.63980854, step = 401 (6.496 sec)\n",
      "INFO:tensorflow:global_step/sec: 15.8263\n",
      "INFO:tensorflow:loss = 0.49354467, step = 501 (6.318 sec)\n",
      "INFO:tensorflow:global_step/sec: 15.7505\n",
      "INFO:tensorflow:loss = 0.38407904, step = 601 (6.349 sec)\n",
      "INFO:tensorflow:global_step/sec: 15.3132\n",
      "INFO:tensorflow:loss = 0.33636412, step = 701 (6.530 sec)\n",
      "INFO:tensorflow:global_step/sec: 15.8418\n",
      "INFO:tensorflow:loss = 0.25389692, step = 801 (6.312 sec)\n",
      "INFO:tensorflow:global_step/sec: 15.8758\n",
      "INFO:tensorflow:loss = 0.25740144, step = 901 (6.299 sec)\n",
      "INFO:tensorflow:Saving checkpoints for 1000 into /var/folders/2r/9kw2cykj317_f5bzwgspyfbr0000gn/T/tmprhiedh9g/model.ckpt.\n",
      "INFO:tensorflow:Loss for final step: 0.18020931.\n"
     ]
    }
   ],
   "source": [
    "# Training for 1,000 steps means 128,000 training examples with the default\n",
    "# batch size. This is roughly equivalent to 5 epochs since the training dataset\n",
    "# contains 25,000 examples.\n",
    "estimator.train(input_fn=train_input_fn, steps=1000);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO:tensorflow:Calling model_fn.\n",
      "INFO:tensorflow:Initialize variable dnn/input_from_feature_columns/input_layer/sentence_hub_module_embedding/module/embeddings/part_0:0 from checkpoint b'/var/folders/2r/9kw2cykj317_f5bzwgspyfbr0000gn/T/tfhub_modules/32f2b2259e1cc8ca58c876921748361283e73997/variables/variables' with embeddings\n",
      "INFO:tensorflow:Done calling model_fn.\n",
      "INFO:tensorflow:Starting evaluation at 2018-06-30-14:23:38\n",
      "INFO:tensorflow:Graph was finalized.\n",
      "INFO:tensorflow:Restoring parameters from /var/folders/2r/9kw2cykj317_f5bzwgspyfbr0000gn/T/tmprhiedh9g/model.ckpt-1000\n",
      "INFO:tensorflow:Running local_init_op.\n",
      "INFO:tensorflow:Done running local_init_op.\n",
      "INFO:tensorflow:Finished evaluation at 2018-06-30-14:23:40\n",
      "INFO:tensorflow:Saving dict for global step 1000: accuracy = 1.0, average_loss = 0.0015652621, global_step = 1000, loss = 0.028174717\n",
      "Training set accuracy: 1.0\n"
     ]
    }
   ],
   "source": [
    "train_eval_result = estimator.evaluate(input_fn=predict_train_input_fn)\n",
    "\n",
    "print(\"Training set accuracy: {accuracy}\".format(**train_eval_result))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}