Data_Cleaning_Notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import sqlalchemy\n",
    "\n",
    "import os\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from functions import clean_cols"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Clean Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean benign dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "19:38:47.479462 - benign.csv\n",
      "Shape:  (3577296, 87)\n",
      "Protocols:  [ 6 17  0]\n",
      "Labels:  ['BENIGN']\n",
      "19:42:10.851804 - Saved!\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv('Datasets/benign.csv')\n",
    "print(datetime.now().time(), '- benign.csv')\n",
    "print('Shape: ', df.shape)\n",
    "print('Protocols: ', df['Protocol'].unique())\n",
    "print('Labels: ', df['Label'].unique())\n",
    "df = clean_cols(df)\n",
    "df.to_csv('Datasets/Cleaned/clean_benign.csv')\n",
    "print(datetime.now().time(), '- Saved!\\n')\n",
    "del df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List 2019.01.12 files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DrDoS_NTP.csv\n",
      "Syn.csv\n",
      "DrDoS_DNS.csv\n",
      "TFTP.csv\n",
      "UDPLag.csv\n",
      "DrDoS_SSDP.csv\n",
      "DrDoS_NetBIOS.csv\n",
      "DrDoS_MSSQL.csv\n",
      "DrDoS_UDP.csv\n",
      "DrDoS_LDAP.csv\n",
      "DrDoS_SNMP.csv\n"
     ]
    }
   ],
   "source": [
    "for file in os.listdir('Datasets/01-12/'):\n",
    "    print(file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean 2019.01.12 datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/eric/opt/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3063: DtypeWarning: Columns (85) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "/Users/eric/opt/anaconda3/lib/python3.7/site-packages/numpy/lib/arraysetops.py:569: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison\n",
      "  mask |= (ar1 == a)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "19:42:22.676585 - DrDoS_NTP.csv\n",
      "Shape:  (1217007, 87)\n",
      "Protocols:  [ 6  0 17]\n",
      "Labels:  ['DrDoS_NTP' 'BENIGN']\n",
      "19:44:13.592049 - Saved!\n",
      "\n",
      "19:44:25.663128 - Syn.csv\n",
      "Shape:  (1582681, 87)\n",
      "Protocols:  [ 6  0 17]\n",
      "Labels:  ['Syn' 'BENIGN']\n",
      "19:46:36.180959 - Saved!\n",
      "\n",
      "19:47:20.508913 - DrDoS_DNS.csv\n",
      "Shape:  (5074413, 87)\n",
      "Protocols:  [17  0  6]\n",
      "Labels:  ['DrDoS_DNS' 'BENIGN']\n",
      "19:54:54.552525 - Saved!\n",
      "\n",
      "19:58:44.639591 - TFTP.csv\n",
      "Shape:  (20107827, 87)\n",
      "Protocols:  [ 6 17  0]\n",
      "Labels:  ['TFTP' 'BENIGN']\n",
      "21:07:29.483631 - Saved!\n",
      "\n",
      "21:07:36.055796 - UDPLag.csv\n",
      "Shape:  (370605, 87)\n",
      "Protocols:  [17  0  6]\n",
      "Labels:  ['UDP-lag' 'BENIGN' 'WebDDoS']\n",
      "21:07:59.620620 - Saved!\n",
      "\n",
      "21:08:21.588839 - DrDoS_SSDP.csv\n",
      "Shape:  (2611374, 87)\n",
      "Protocols:  [ 0 17  6]\n",
      "Labels:  ['DrDoS_SSDP' 'BENIGN']\n",
      "21:12:06.946562 - Saved!\n",
      "\n",
      "21:12:39.059363 - DrDoS_NetBIOS.csv\n",
      "Shape:  (4094986, 87)\n",
      "Protocols:  [17  0  6]\n",
      "Labels:  ['DrDoS_NetBIOS' 'BENIGN']\n",
      "21:18:24.405621 - Saved!\n",
      "\n",
      "21:19:00.151471 - DrDoS_MSSQL.csv\n",
      "Shape:  (4524498, 87)\n",
      "Protocols:  [ 0 17  6]\n",
      "Labels:  ['DrDoS_MSSQL' 'BENIGN']\n",
      "21:25:36.540734 - Saved!\n",
      "\n",
      "21:26:05.382785 - DrDoS_UDP.csv\n",
      "Shape:  (3136802, 87)\n",
      "Protocols:  [17  0  6]\n",
      "Labels:  ['DrDoS_UDP' 'BENIGN']\n",
      "21:30:51.824123 - Saved!\n",
      "\n",
      "21:31:08.679343 - DrDoS_LDAP.csv\n",
      "Shape:  (2181542, 87)\n",
      "Protocols:  [ 0 17  6]\n",
      "Labels:  ['DrDoS_LDAP' 'BENIGN']\n",
      "21:34:13.501458 - Saved!\n",
      "\n",
      "21:34:54.784172 - DrDoS_SNMP.csv\n",
      "Shape:  (5161377, 87)\n",
      "Protocols:  [17  6  0]\n",
      "Labels:  ['DrDoS_SNMP' 'BENIGN']\n",
      "21:42:32.209807 - Saved!\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for file in os.listdir('Datasets/01-12/'):\n",
    "    if file[0] == '.':\n",
    "        print('Dot File\\n')\n",
    "        pass\n",
    "    else:\n",
    "        df = pd.read_csv(f'Datasets/01-12/{file}', index_col=0)\n",
    "        print(datetime.now().time(), '-', file)\n",
    "        print('Shape: ', df.shape)\n",
    "        print('Protocols: ', df[' Protocol'].unique())\n",
    "        print('Labels: ', df[' Label'].unique())\n",
    "        df = clean_cols(df)\n",
    "        df.to_csv(f'Datasets/Cleaned/clean_01-12_{file}')\n",
    "        print(datetime.now().time(), '- Saved!\\n')\n",
    "        del df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List 2019.03.11 files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Syn.csv\n",
      "MSSQL.csv\n",
      "UDPLag.csv\n",
      "LDAP.csv\n",
      "UDP.csv\n",
      "Portmap.csv\n",
      "NetBIOS.csv\n",
      ".~lock.UDPLag.csv#\n"
     ]
    }
   ],
   "source": [
    "for file in os.listdir('Datasets/03-11/'):\n",
    "    print(file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean 2019.03.11 datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "21:43:09.432607 - Syn.csv\n",
      "Shape:  (4320541, 87)\n",
      "Protocols:  [ 6 17  0]\n",
      "Labels:  ['Syn' 'BENIGN']\n",
      "21:49:37.406391 - Saved!\n",
      "\n",
      "21:50:25.242840 - MSSQL.csv\n",
      "Shape:  (5775786, 87)\n",
      "Protocols:  [17  0  6]\n",
      "Labels:  ['LDAP' 'BENIGN' 'MSSQL']\n",
      "21:59:01.353304 - Saved!\n",
      "\n",
      "21:59:07.593757 - UDPLag.csv\n",
      "Shape:  (725165, 87)\n",
      "Protocols:  [17  6  0]\n",
      "Labels:  ['UDP' 'BENIGN' 'UDPLag' 'Syn']\n",
      "22:00:07.352170 - Saved!\n",
      "\n",
      "22:00:23.379443 - LDAP.csv\n",
      "Shape:  (2113234, 87)\n",
      "Protocols:  [17  6  0]\n",
      "Labels:  ['NetBIOS' 'BENIGN' 'LDAP']\n",
      "22:03:14.450349 - Saved!\n",
      "\n",
      "22:03:46.420077 - UDP.csv\n",
      "Shape:  (3782206, 87)\n",
      "Protocols:  [17  0  6]\n",
      "Labels:  ['MSSQL' 'BENIGN' 'UDP']\n",
      "22:09:30.822873 - Saved!\n",
      "\n",
      "22:09:32.529464 - Portmap.csv\n",
      "Shape:  (191694, 87)\n",
      "Protocols:  [ 0  6 17]\n",
      "Labels:  ['BENIGN' 'Portmap']\n",
      "22:09:44.890125 - Saved!\n",
      "\n",
      "22:10:11.568484 - NetBIOS.csv\n",
      "Shape:  (3455899, 87)\n",
      "Protocols:  [17  0  6]\n",
      "Labels:  ['NetBIOS' 'BENIGN']\n",
      "22:15:10.030030 - Saved!\n",
      "\n",
      "Dot File\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for file in os.listdir('Datasets/03-11/'):\n",
    "    if file[0] == '.':\n",
    "        print('Dot File\\n')\n",
    "        pass\n",
    "    else:\n",
    "        df = pd.read_csv(f'Datasets/03-11/{file}', index_col=0)\n",
    "        print(datetime.now().time(), '-', file)\n",
    "        print('Shape: ', df.shape)\n",
    "        print('Protocols: ', df[' Protocol'].unique())\n",
    "        print('Labels: ', df[' Label'].unique())\n",
    "        df = clean_cols(df)\n",
    "        df.to_csv(f'Datasets/Cleaned/clean_03-11_{file}')\n",
    "        print(datetime.now().time(), '- Saved!\\n')\n",
    "        del df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combine Benign Flow Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List cleaned files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "clean_03-11_Syn.csv\n",
      "clean_01-12_Syn.csv\n",
      "clean_01-12_DrDoS_MSSQL.csv\n",
      ".DS_Store\n",
      "clean_benign.csv\n",
      "clean_01-12_DrDoS_SSDP.csv\n",
      "clean_03-11_MSSQL.csv\n",
      "clean_01-12_DrDoS_UDP.csv\n",
      "clean_01-12_DrDoS_NetBIOS.csv\n",
      "clean_03-11_UDP.csv\n",
      "clean_01-12_TFTP.csv\n",
      "clean_03-11_NetBIOS.csv\n",
      "clean_01-12_DrDoS_SNMP.csv\n",
      "clean_01-12_DrDoS_LDAP.csv\n",
      "clean_01-12_DrDoS_DNS.csv\n",
      "clean_01-12_UDPLag.csv\n",
      "clean_03-11_Portmap.csv\n",
      "clean_01-12_DrDoS_NTP.csv\n",
      "clean_03-11_LDAP.csv\n",
      "clean_03-11_UDPLag.csv\n"
     ]
    }
   ],
   "source": [
    "for file in os.listdir('Datasets/Cleaned/'):\n",
    "    print(file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Combine benign data flow data and remove it from malicious datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/eric/opt/anaconda3/lib/python3.7/site-packages/numpy/lib/arraysetops.py:580: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison\n",
      "  mask |= (ar1 == a)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12:08:22.961333 Benign Database Shape:  (3577296, 83) \n",
      "\n",
      "12:08:55.721600 - clean_03-11_Syn.csv\n",
      "Shape:  (4320541, 83)\n",
      "New Benign Shape:  (3613086, 83)\n",
      "New Shape:  (4284751, 83)\n",
      "12:12:48.988392 - Saved!\n",
      "\n",
      "12:13:00.186980 - clean_01-12_Syn.csv\n",
      "Shape:  (1582681, 83)\n",
      "New Benign Shape:  (3613478, 83)\n",
      "New Shape:  (1582289, 83)\n",
      "12:14:31.425372 - Saved!\n",
      "\n",
      "12:15:07.034445 - clean_01-12_DrDoS_MSSQL.csv\n",
      "Shape:  (4524498, 83)\n",
      "New Benign Shape:  (3615484, 83)\n",
      "New Shape:  (4522492, 83)\n",
      "12:19:11.151641 - Saved!\n",
      "\n",
      "Dot File\n",
      "\n",
      "clean_benign.csv \n",
      "\n",
      "12:19:31.084515 - clean_01-12_DrDoS_SSDP.csv\n",
      "Shape:  (2611374, 83)\n",
      "New Benign Shape:  (3616247, 83)\n",
      "New Shape:  (2610611, 83)\n",
      "12:22:07.985280 - Saved!\n",
      "\n",
      "12:22:53.768445 - clean_03-11_MSSQL.csv\n",
      "Shape:  (5775786, 83)\n",
      "New Benign Shape:  (3619041, 83)\n",
      "New Shape:  (5772992, 83)\n",
      "12:28:00.766302 - Saved!\n",
      "\n",
      "12:28:24.200824 - clean_01-12_DrDoS_UDP.csv\n",
      "Shape:  (3136802, 83)\n",
      "New Benign Shape:  (3621198, 83)\n",
      "New Shape:  (3134645, 83)\n",
      "12:31:28.385933 - Saved!\n",
      "\n",
      "12:31:59.726908 - clean_01-12_DrDoS_NetBIOS.csv\n",
      "Shape:  (4094986, 83)\n",
      "New Benign Shape:  (3622905, 83)\n",
      "New Shape:  (4093279, 83)\n",
      "12:35:43.765836 - Saved!\n",
      "\n",
      "12:36:13.898897 - clean_03-11_UDP.csv\n",
      "Shape:  (3782206, 83)\n",
      "New Benign Shape:  (3626039, 83)\n",
      "New Shape:  (3779072, 83)\n",
      "12:39:54.303077 - Saved!\n",
      "\n",
      "12:43:20.556569 - clean_01-12_TFTP.csv\n",
      "Shape:  (20107827, 83)\n",
      "New Benign Shape:  (3651286, 83)\n",
      "New Shape:  (20082580, 83)\n",
      "13:02:43.733423 - Saved!\n",
      "\n",
      "13:03:10.991218 - clean_03-11_NetBIOS.csv\n",
      "Shape:  (3455899, 83)\n",
      "New Benign Shape:  (3652607, 83)\n",
      "New Shape:  (3454578, 83)\n",
      "13:06:23.548494 - Saved!\n",
      "\n",
      "13:07:02.282429 - clean_01-12_DrDoS_SNMP.csv\n",
      "Shape:  (5161377, 83)\n",
      "New Benign Shape:  (3654114, 83)\n",
      "New Shape:  (5159870, 83)\n",
      "13:11:45.033482 - Saved!\n",
      "\n",
      "13:12:02.468140 - clean_01-12_DrDoS_LDAP.csv\n",
      "Shape:  (2181542, 83)\n",
      "New Benign Shape:  (3655726, 83)\n",
      "New Shape:  (2179930, 83)\n",
      "13:14:07.855155 - Saved!\n",
      "\n",
      "13:14:47.088289 - clean_01-12_DrDoS_DNS.csv\n",
      "Shape:  (5074413, 83)\n",
      "New Benign Shape:  (3659128, 83)\n",
      "New Shape:  (5071011, 83)\n",
      "13:19:22.195821 - Saved!\n",
      "\n",
      "13:19:25.152013 - clean_01-12_UDPLag.csv\n",
      "Shape:  (370605, 83)\n",
      "New Benign Shape:  (3662833, 83)\n",
      "New Shape:  (366900, 83)\n",
      "13:19:52.381448 - Saved!\n",
      "\n",
      "13:19:53.731456 - clean_03-11_Portmap.csv\n",
      "Shape:  (191694, 83)\n",
      "New Benign Shape:  (3667567, 83)\n",
      "New Shape:  (186960, 83)\n",
      "13:20:09.824279 - Saved!\n",
      "\n",
      "13:20:19.374290 - clean_01-12_DrDoS_NTP.csv\n",
      "Shape:  (1217007, 83)\n",
      "New Benign Shape:  (3681932, 83)\n",
      "New Shape:  (1202642, 83)\n",
      "13:21:39.348252 - Saved!\n",
      "\n",
      "13:21:53.561010 - clean_03-11_LDAP.csv\n",
      "Shape:  (2113234, 83)\n",
      "New Benign Shape:  (3687056, 83)\n",
      "New Shape:  (2108110, 83)\n",
      "13:23:52.856147 - Saved!\n",
      "\n",
      "13:23:58.231643 - clean_03-11_UDPLag.csv\n",
      "Shape:  (725165, 83)\n",
      "New Benign Shape:  (3691124, 83)\n",
      "New Shape:  (721097, 83)\n",
      "13:24:48.213382 - Saved!\n",
      "\n",
      "13:28:29.399885 new_clean_benign.csv Saved!\n",
      "\n"
     ]
    }
   ],
   "source": [
    "benign = pd.read_csv('Datasets/Cleaned/clean_benign.csv', index_col=0)\n",
    "print(datetime.now().time(), 'Benign Database Shape: ', benign.shape, '\\n')\n",
    "\n",
    "for file in os.listdir('Datasets/Cleaned/'):\n",
    "    if file[0] == '.':\n",
    "        print('Dot File\\n')\n",
    "        pass\n",
    "    elif file == 'clean_benign.csv':\n",
    "        print(file, '\\n')\n",
    "        pass\n",
    "    else:\n",
    "        df = pd.read_csv(f'Datasets/Cleaned/{file}', index_col=0)\n",
    "        print(datetime.now().time(), '-', file)\n",
    "        print('Shape: ', df.shape)\n",
    "        new_benign = df[df['Label'] == 'Benign_Unknown']\n",
    "        benign = pd.concat([benign, new_benign])\n",
    "        benign.reset_index(drop=True, inplace=True)\n",
    "        print('New Benign Shape: ', benign.shape)\n",
    "        df = df[df['Label'] != 'Benign_Unknown']\n",
    "        df.reset_index(drop=True, inplace=True)\n",
    "        print('New Shape: ', df.shape)\n",
    "        df.to_csv(f'Datasets/Cleaned/{file}')\n",
    "        print(datetime.now().time(), '- Saved!\\n')\n",
    "        del df\n",
    "\n",
    "benign.to_csv('Datasets/Cleaned/clean_benign.csv')\n",
    "print(datetime.now().time(), 'clean_benign.csv Saved!\\n')\n",
    "del benign"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Drop Rows With NA Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12:40:20.888798 - clean_03-11_Syn.csv\n",
      "Total NAs: 76\n",
      "12:44:14.421134 - Saved!\n",
      "\n",
      "12:44:25.403239 - clean_01-12_Syn.csv\n",
      "Total NAs: 202274\n",
      "12:45:39.941954 - Saved!\n",
      "\n",
      "12:46:11.581654 - clean_01-12_DrDoS_MSSQL.csv\n",
      "Total NAs: 3\n",
      "12:50:02.053454 - Saved!\n",
      "\n",
      "Dot File\n",
      "\n",
      "12:50:32.590209 - clean_benign.csv\n",
      "Total NAs: 155\n",
      "12:54:20.371000 - Saved!\n",
      "\n",
      "12:54:39.700983 - clean_01-12_DrDoS_SSDP.csv\n",
      "Total NAs: 1\n",
      "12:57:03.758335 - Saved!\n",
      "\n",
      "12:57:45.025649 - clean_03-11_MSSQL.csv\n",
      "Total NAs: 4\n",
      "13:02:56.211354 - Saved!\n",
      "\n",
      "13:03:20.220102 - clean_01-12_DrDoS_UDP.csv\n",
      "Total NAs: 2\n",
      "13:06:15.819131 - Saved!\n",
      "\n",
      "13:06:43.297267 - clean_01-12_DrDoS_NetBIOS.csv\n",
      "Total NAs: 6\n",
      "13:10:14.606187 - Saved!\n",
      "\n",
      "13:10:43.922381 - clean_03-11_UDP.csv\n",
      "No NAs\n",
      "\n",
      "13:13:51.591718 - clean_01-12_TFTP.csv\n",
      "Total NAs: 10472\n",
      "13:33:28.461517 - Saved!\n",
      "\n",
      "13:33:54.653586 - clean_03-11_NetBIOS.csv\n",
      "Total NAs: 5\n",
      "13:36:53.861454 - Saved!\n",
      "\n",
      "13:37:28.886935 - clean_01-12_DrDoS_SNMP.csv\n",
      "Total NAs: 7\n",
      "13:41:56.277815 - Saved!\n",
      "\n",
      "13:42:11.101964 - clean_01-12_DrDoS_LDAP.csv\n",
      "Total NAs: 2\n",
      "13:44:02.398655 - Saved!\n",
      "\n",
      "13:44:38.336612 - clean_01-12_DrDoS_DNS.csv\n",
      "Total NAs: 9\n",
      "13:49:00.095486 - Saved!\n",
      "\n",
      "13:49:03.065971 - clean_01-12_UDPLag.csv\n",
      "Total NAs: 36132\n",
      "13:49:21.054479 - Saved!\n",
      "\n",
      "13:49:22.339483 - clean_03-11_Portmap.csv\n",
      "No NAs\n",
      "\n",
      "13:49:31.702488 - clean_01-12_DrDoS_NTP.csv\n",
      "Total NAs: 3\n",
      "13:50:42.650400 - Saved!\n",
      "\n",
      "13:50:56.744182 - clean_03-11_LDAP.csv\n",
      "No NAs\n",
      "\n",
      "13:51:02.607911 - clean_03-11_UDPLag.csv\n",
      "Total NAs: 1\n",
      "13:51:41.472690 - Saved!\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for file in os.listdir('Datasets/Cleaned/'):\n",
    "    if file[0] == '.':\n",
    "        print('Dot File\\n')\n",
    "        pass\n",
    "    else:\n",
    "        df = pd.read_csv(f'Datasets/Cleaned/{file}', index_col=0)\n",
    "        print(datetime.now().time(), '-', file)\n",
    "        if df.isna().sum().sum() == 0:\n",
    "            print('No NAs\\n')\n",
    "            pass\n",
    "        else:\n",
    "            print('Total NAs:', df.isna().sum().sum())\n",
    "            df.dropna(how='any', axis=0, inplace=True)\n",
    "            df.to_csv(f'Datasets/Cleaned/{file}')\n",
    "            print(datetime.now().time(), '- Saved!\\n')\n",
    "        del df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Replace Infinite Values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some bytes/second and packets/second rates were recorded as infinite values,\n",
    "\n",
    "likely because they were too high for the CICFlowMeter-V3 to capture.\n",
    "\n",
    "https://www.unb.ca/cic/research/applications.html#CICFlowMeter\n",
    "\n",
    "The following rates were estimated based on research into these rates in relation to DDOS attacks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "high_bps = 1000000000000\n",
    "high_pps = 100000000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Replace infinite values in bytes/second and packets/second columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/eric/opt/anaconda3/lib/python3.7/site-packages/numpy/lib/arraysetops.py:580: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison\n",
      "  mask |= (ar1 == a)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "19:21:31.308398 - clean_03-11_Syn.csv\n",
      "19:25:11.967638 - Saved!\n",
      "\n",
      "19:25:22.649382 - clean_01-12_Syn.csv\n",
      "19:26:38.887541 - Saved!\n",
      "\n",
      "19:27:09.954049 - clean_01-12_DrDoS_MSSQL.csv\n",
      "19:30:52.708505 - Saved!\n",
      "\n",
      "Dot File\n",
      "\n",
      "19:31:23.797784 - clean_benign.csv\n",
      "19:35:08.778279 - Saved!\n",
      "\n",
      "19:35:28.840723 - clean_01-12_DrDoS_SSDP.csv\n",
      "19:37:49.419153 - Saved!\n",
      "\n",
      "19:38:29.679290 - clean_03-11_MSSQL.csv\n",
      "19:43:15.399379 - Saved!\n",
      "\n",
      "19:43:38.862666 - clean_01-12_DrDoS_UDP.csv\n",
      "19:46:28.858131 - Saved!\n",
      "\n",
      "19:46:57.739196 - clean_01-12_DrDoS_NetBIOS.csv\n",
      "19:50:19.072732 - Saved!\n",
      "\n",
      "19:50:46.898764 - clean_03-11_UDP.csv\n",
      "19:54:10.144602 - Saved!\n",
      "\n",
      "19:57:43.661005 - clean_01-12_TFTP.csv\n",
      "20:16:08.917237 - Saved!\n",
      "\n",
      "20:16:33.971950 - clean_03-11_NetBIOS.csv\n",
      "20:19:24.399285 - Saved!\n",
      "\n",
      "20:19:58.897827 - clean_01-12_DrDoS_SNMP.csv\n",
      "20:24:14.354665 - Saved!\n",
      "\n",
      "20:24:29.324429 - clean_01-12_DrDoS_LDAP.csv\n",
      "20:26:16.636417 - Saved!\n",
      "\n",
      "20:26:53.522789 - clean_01-12_DrDoS_DNS.csv\n",
      "20:31:06.309940 - Saved!\n",
      "\n",
      "20:31:09.081456 - clean_01-12_UDPLag.csv\n",
      "20:31:27.417434 - Saved!\n",
      "\n",
      "20:31:28.637724 - clean_03-11_Portmap.csv\n",
      "20:31:37.960052 - Saved!\n",
      "\n",
      "20:31:47.230711 - clean_01-12_DrDoS_NTP.csv\n",
      "20:32:55.587911 - Saved!\n",
      "\n",
      "20:33:09.537728 - clean_03-11_LDAP.csv\n",
      "20:34:52.968391 - Saved!\n",
      "\n",
      "20:34:58.149815 - clean_03-11_UDPLag.csv\n",
      "20:35:35.517120 - Saved!\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for file in os.listdir('Datasets/Cleaned/'):\n",
    "    if file[0] == '.':\n",
    "        print('Dot File\\n')\n",
    "        pass\n",
    "    else:\n",
    "        df = pd.read_csv(f'Datasets/Cleaned/{file}', index_col=0)\n",
    "        print(datetime.now().time(), '-', file)\n",
    "        df['Flow_Bytes_Sec'].replace(float('inf'), high_bps, inplace=True)\n",
    "        df['Flow_Pkts_Sec'].replace(float('inf'), high_pps, inplace=True)\n",
    "        df.to_csv(f'Datasets/Cleaned/{file}')\n",
    "        print(datetime.now().time(), '- Saved!\\n')\n",
    "        del df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combine Like Databases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List cleaned databases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "clean_03-11_Syn.csv\n",
      "clean_01-12_Syn.csv\n",
      "clean_01-12_DrDoS_MSSQL.csv\n",
      ".DS_Store\n",
      "clean_Benign.csv\n",
      "clean_01-12_DrDoS_SSDP.csv\n",
      "clean_03-11_MSSQL.csv\n",
      "clean_01-12_DrDoS_UDP.csv\n",
      "clean_01-12_DrDoS_NetBIOS.csv\n",
      "clean_03-11_UDP.csv\n",
      "clean_01-12_TFTP.csv\n",
      "clean_03-11_NetBIOS.csv\n",
      "clean_01-12_DrDoS_SNMP.csv\n",
      "clean_03-11_UDP_Lag.csv\n",
      "clean_01-12_DrDoS_LDAP.csv\n",
      "clean_01-12_UDP_Lag.csv\n",
      "clean_01-12_DrDoS_DNS.csv\n",
      "clean_03-11_Portmap.csv\n",
      "clean_01-12_DrDoS_NTP.csv\n",
      "clean_03-11_LDAP.csv\n"
     ]
    }
   ],
   "source": [
    "for file in os.listdir('Datasets/Cleaned/'):\n",
    "    print(file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rename labels for uniformity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/eric/opt/anaconda3/lib/python3.7/site-packages/numpy/lib/arraysetops.py:580: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison\n",
      "  mask |= (ar1 == a)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "17:32:39.168971 - clean_01-12_DrDoS_SSDP.csv\n",
      "Shape:  (2610610, 83)\n",
      "17:35:01.613283 - Saved!\n",
      "\n",
      "17:35:36.153087 - clean_01-12_DrDoS_SNMP.csv\n",
      "Shape:  (5159863, 83)\n",
      "17:39:53.638987 - Saved!\n",
      "\n",
      "17:40:28.981572 - clean_01-12_DrDoS_DNS.csv\n",
      "Shape:  (5071002, 83)\n",
      "17:44:41.986310 - Saved!\n",
      "\n",
      "17:44:51.633782 - clean_01-12_DrDoS_NTP.csv\n",
      "Shape:  (1202639, 83)\n",
      "17:45:59.973441 - Saved!\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_singles = ['clean_01-12_DrDoS_SSDP.csv', 'clean_01-12_DrDoS_SNMP.csv', 'clean_01-12_DrDoS_DNS.csv', 'clean_01-12_DrDoS_NTP.csv']\n",
    "\n",
    "for file in df_singles:\n",
    "    df = pd.read_csv(f'Datasets/Cleaned/{file}', index_col=0)\n",
    "    print(datetime.now().time(), '-', file)\n",
    "    print('Shape: ', df.shape)\n",
    "    replace = {\n",
    "        'DrDoS_DNS':'DNS',\n",
    "        'DrDoS_LDAP':'LDAP',\n",
    "        'DrDoS_MSSQL':'MSSQL',\n",
    "        'DrDoS_NTP':'NTP',\n",
    "        'DrDoS_NetBIOS':'NetBIOS',\n",
    "        'DrDoS_SNMP':'SNMP',\n",
    "        'DrDoS_SSDP':'SSDP',\n",
    "        'DrDoS_UDP':'UDP',\n",
    "        'UDP-lag':'UDP_Lag'\n",
    "    }\n",
    "    df['Label'].replace(to_replace=replace, inplace=True)\n",
    "    df.to_csv(f'Datasets/Final/final_{file}')\n",
    "    print(datetime.now().time(), '- Saved!\\n')\n",
    "    del df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Combine like databases, rename labels for uniformity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pairs = [\n",
    "    ['clean_03-11_Syn.csv', 'clean_01-12_Syn.csv'],\n",
    "    ['clean_01-12_DrDoS_MSSQL.csv', 'clean_03-11_MSSQL.csv'],\n",
    "    ['clean_01-12_DrDoS_UDP.csv', 'clean_03-11_UDP.csv'],\n",
    "    ['clean_01-12_DrDoS_NetBIOS.csv', 'clean_03-11_NetBIOS.csv'],\n",
    "    ['clean_01-12_DrDoS_LDAP.csv', 'clean_03-11_LDAP.csv'],\n",
    "    ['clean_01-12_UDPLag.csv', 'clean_03-11_UDPLag.csv']\n",
    "]\n",
    "\n",
    "for pair in df_pairs:\n",
    "    file_1 = pair[0]\n",
    "    file_2 = pair[1]\n",
    "    df_1 = pd.read_csv(f'Datasets/Cleaned/{file_1}', index_col=0)\n",
    "    print(datetime.now().time(), '-', file_1)\n",
    "    print('Shape:', df_1.shape)\n",
    "    df_2 = pd.read_csv(f'Datasets/Cleaned/{file_2}', index_col=0)\n",
    "    print(datetime.now().time(), '-', file_2)\n",
    "    print('Shape:', df_2.shape)\n",
    "    df = pd.concat([df_1, df_2])\n",
    "    df.reset_index(drop=True, inplace=True)\n",
    "    print('Combined Shape: ', df.shape)\n",
    "    replace = {\n",
    "        'DrDoS_DNS':'DNS',\n",
    "        'DrDoS_LDAP':'LDAP',\n",
    "        'DrDoS_MSSQL':'MSSQL',\n",
    "        'DrDoS_NTP':'NTP',\n",
    "        'DrDoS_NetBIOS':'NetBIOS',\n",
    "        'DrDoS_SNMP':'SNMP',\n",
    "        'DrDoS_SSDP':'SSDP',\n",
    "        'DrDoS_UDP':'UDP',\n",
    "        'UDP-lag':'UDP-Lag'\n",
    "    }\n",
    "    df['Label'].replace(to_replace=replace, inplace=True)\n",
    "    df.to_csv(f'Datasets/Final/final_{file_1}')\n",
    "    print(datetime.now().time(), '- Saved!\\n')\n",
    "    del df, df_1, df_2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Separate by Protocol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "final_TFTP.csv\n",
      "final_NTP.csv\n",
      "final_SNMP.csv\n",
      "final_LDAP.csv\n",
      ".DS_Store\n",
      "final_Portmap.csv\n",
      "final_DNS.csv\n",
      "final_MSSQL.csv\n",
      "final_NetBIOS.csv\n",
      "final_SSDP.csv\n",
      "final_UDP.csv\n",
      "final_Benign.csv\n",
      "final_Syn.csv\n"
     ]
    }
   ],
   "source": [
    "for file in os.listdir('Datasets/Final/'):\n",
    "    print(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/eric/opt/anaconda3/lib/python3.7/site-packages/numpy/lib/arraysetops.py:580: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison\n",
      "  mask |= (ar1 == a)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "15:12:22.277619 - final_TFTP.csv\n",
      "TCP Shape: (146272, 83)\n",
      "UDP Shape: (19925545, 83)\n",
      "HOPOPT Shape: (291, 83)\n",
      "16:23:11.197539 - Saved!\n",
      "\n",
      "16:23:58.708680 - final_NTP.csv\n",
      "TCP Shape: (3713, 83)\n",
      "UDP Shape: (1198770, 83)\n",
      "HOPOPT Shape: (156, 83)\n",
      "16:28:38.120455 - Saved!\n",
      "\n",
      "16:30:56.619536 - final_SNMP.csv\n",
      "TCP Shape: (1246, 83)\n",
      "UDP Shape: (5158526, 83)\n",
      "HOPOPT Shape: (91, 83)\n",
      "16:48:03.602859 - Saved!\n",
      "\n",
      "16:49:57.389055 - final_LDAP.csv\n",
      "TCP Shape: (597, 83)\n",
      "UDP Shape: (4287314, 83)\n",
      "HOPOPT Shape: (127, 83)\n",
      "17:05:06.936508 - Saved!\n",
      "\n",
      "Dot File\n",
      "\n",
      "17:05:15.696410 - final_Portmap.csv\n",
      "TCP Shape: (534, 83)\n",
      "UDP Shape: (186405, 83)\n",
      "HOPOPT Shape: (21, 83)\n",
      "17:06:03.062635 - Saved!\n",
      "\n",
      "17:09:16.079742 - final_DNS.csv\n",
      "TCP Shape: (1293, 83)\n",
      "UDP Shape: (5069514, 83)\n",
      "HOPOPT Shape: (195, 83)\n",
      "17:27:03.082294 - Saved!\n",
      "\n",
      "17:32:15.674030 - final_MSSQL.csv\n",
      "TCP Shape: (632, 83)\n",
      "UDP Shape: (10294772, 83)\n",
      "HOPOPT Shape: (73, 83)\n",
      "18:07:29.015242 - Saved!\n",
      "\n",
      "18:10:44.979525 - final_NetBIOS.csv\n",
      "TCP Shape: (1167, 83)\n",
      "UDP Shape: (7546655, 83)\n",
      "HOPOPT Shape: (24, 83)\n",
      "18:35:36.054533 - Saved!\n",
      "\n",
      "18:36:51.267261 - final_SSDP.csv\n",
      "TCP Shape: (478, 83)\n",
      "UDP Shape: (2610110, 83)\n",
      "HOPOPT Shape: (22, 83)\n",
      "18:46:17.299886 - Saved!\n",
      "\n",
      "18:49:47.525558 - final_UDP.csv\n",
      "TCP Shape: (1305, 83)\n",
      "UDP Shape: (6912341, 83)\n",
      "HOPOPT Shape: (69, 83)\n",
      "19:15:25.497796 - Saved!\n",
      "\n",
      "19:17:23.800267 - final_Benign.csv\n",
      "TCP Shape: (3646364, 83)\n",
      "UDP Shape: (40295, 83)\n",
      "HOPOPT Shape: (4310, 83)\n",
      "19:32:26.234621 - Saved!\n",
      "\n",
      "19:34:55.980974 - final_Syn.csv\n",
      "TCP Shape: (5658802, 83)\n",
      "UDP Shape: (5678, 83)\n",
      "HOPOPT Shape: (210, 83)\n",
      "19:53:53.728756 - Saved!\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for file in os.listdir('Datasets/Final/'):\n",
    "    if file[0] == '.':\n",
    "        print('Dot File\\n')\n",
    "        pass\n",
    "    else:\n",
    "        df = pd.read_csv(f'Datasets/Final/{file}', index_col=0)\n",
    "        print(datetime.now().time(), '-', file)\n",
    "        TCP = df[df['TCP'] == 1]\n",
    "        print('TCP Shape:', TCP.shape)\n",
    "        if len(TCP) > 0:\n",
    "            TCP.reset_index(drop=True, inplace=True)\n",
    "            TCP.to_csv(f'Datasets/TCP/TCP_{file}')\n",
    "        UDP = df[df['UDP'] == 1]\n",
    "        print('UDP Shape:', UDP.shape)\n",
    "        if len(UDP) > 0:\n",
    "            UDP.reset_index(drop=True, inplace=True)\n",
    "            UDP.to_csv(f'Datasets/UDP/UDP_{file}')\n",
    "        HOPOPT = df[df['HOPOPT'] == 1]\n",
    "        print('HOPOPT Shape:', HOPOPT.shape)\n",
    "        if len(HOPOPT) > 0:\n",
    "            HOPOPT.reset_index(drop=True, inplace=True)\n",
    "            HOPOPT.to_csv(f'Datasets/HOPOPT/HOPOPT_{file}')\n",
    "        print(datetime.now().time(), '- Saved!\\n')\n",
    "        del df, TCP, UDP, HOPOPT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combine Datasets as SQL Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(100000, 83)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('Datasets/Cleaned/clean_01-12_DrDoS_DNS.csv', index_col=0, nrows=100000)\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Flow_ID', 'Flow_Duration', 'Fwd_Total_Pkts', 'Bwd_Total_Pkts',\n",
       "       'Fwd_Total_Bytes', 'Bwd_Total_Bytes', 'Fwd_Pkt_Length_Max',\n",
       "       'Fwd_Pkt_Length_Min', 'Fwd_Pkt_Length_Mean', 'Fwd_Pkt_Length_Std',\n",
       "       'Bwd_Pkt_Length_Max', 'Bwd_Pkt_Length_Min', 'Bwd_Pkt_Length_Mean',\n",
       "       'Bwd_Pkt_Length_Std', 'Flow_Bytes_Sec', 'Flow_Pkts_Sec',\n",
       "       'Flow_IAT_Mean', 'Flow_IAT_Std', 'Flow_IAT_Max', 'Flow_IAT_Min',\n",
       "       'Fwd_IAT_Total', 'Fwd_IAT_Mean', 'Fwd_IAT_Std', 'Fwd_IAT_Max',\n",
       "       'Fwd_IAT_Min', 'Bwd_IAT_Total', 'Bwd_IAT_Mean', 'Bwd_IAT_Std',\n",
       "       'Bwd_IAT_Max', 'Bwd_IAT_Min', 'Fwd_PSH_Flags', 'Bwd_PSH_Flags',\n",
       "       'Fwd_URG_Flags', 'Bwd_URG_Flags', 'Fwd_Header_Length',\n",
       "       'Bwd_Header_Length', 'Fwd_Pkts_Sec', 'Bwd_Pkts_Sec', 'Pkt_Length_Min',\n",
       "       'Pkt_Length_Max', 'Pkt_Length_Mean', 'Pkt_Length_Std', 'Pkt_Length_Var',\n",
       "       'FIN_Flag_Count', 'SYN_Flag_Count', 'RST_Flag_Count', 'PSH_Flag_Count',\n",
       "       'ACK_Flag_Count', 'URG_Flag_Count', 'CWE_Flag_Count', 'ECE_Flag_Count',\n",
       "       'Down_Up_Ratio', 'Pkt_Size_Mean', 'Fwd_Segment_Size_Mean',\n",
       "       'Bwd_Segment_Size_Mean', 'Fwd_Header_Length_1',\n",
       "       'Fwd_Byte_Bulk_Rate_Mean', 'Fwd_Pkt_Bulk_Rate_Mean',\n",
       "       'Fwd_Num_Bulk_Rate_Mean', 'Bwd_Byte_Bulk_Rate_Mean',\n",
       "       'Bwd_Pkt_Bulk_Rate_Mean', 'Bwd_Num_Bulk_Rate_Mean', 'Fwd_Subflow_Pkts',\n",
       "       'Fwd_Subflow_Bytes', 'Bwd_Subflow_Pkts', 'Bwd_Subflow_Bytes',\n",
       "       'Fwd_Init_Win_Bytes', 'Bwd_Init_Win_Bytes', 'Fwd_Act_Data_Pkt',\n",
       "       'Fwd_Seg_Size_Min', 'Time_Active_Mean', 'Time_Active_Std',\n",
       "       'Time_Active_Max', 'Time_Active_Min', 'Time_Idle_Mean', 'Time_Idle_Std',\n",
       "       'Time_Idle_Max', 'Time_Idle_Min', 'Label', 'HOPOPT', 'TCP', 'UDP',\n",
       "       'Malicious'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>Flow_ID</th>\n",
       "      <th>Flow_Duration</th>\n",
       "      <th>Fwd_Total_Pkts</th>\n",
       "      <th>Bwd_Total_Pkts</th>\n",
       "      <th>Fwd_Total_Bytes</th>\n",
       "      <th>Bwd_Total_Bytes</th>\n",
       "      <th>Fwd_Pkt_Length_Max</th>\n",
       "      <th>Fwd_Pkt_Length_Min</th>\n",
       "      <th>Fwd_Pkt_Length_Mean</th>\n",
       "      <th>Fwd_Pkt_Length_Std</th>\n",
       "      <th>...</th>\n",
       "      <th>Time_Active_Min</th>\n",
       "      <th>Time_Idle_Mean</th>\n",
       "      <th>Time_Idle_Std</th>\n",
       "      <th>Time_Idle_Max</th>\n",
       "      <th>Time_Idle_Min</th>\n",
       "      <th>Label</th>\n",
       "      <th>HOPOPT</th>\n",
       "      <th>TCP</th>\n",
       "      <th>UDP</th>\n",
       "      <th>Malicious</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>172.16.0.5-192.168.50.1-634-60495-17</td>\n",
       "      <td>28415</td>\n",
       "      <td>97</td>\n",
       "      <td>0</td>\n",
       "      <td>42680.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>DrDoS_DNS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>172.16.0.5-192.168.50.1-60495-634-17</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>880.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>DrDoS_DNS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>172.16.0.5-192.168.50.1-634-46391-17</td>\n",
       "      <td>48549</td>\n",
       "      <td>200</td>\n",
       "      <td>0</td>\n",
       "      <td>88000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>DrDoS_DNS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>172.16.0.5-192.168.50.1-634-11894-17</td>\n",
       "      <td>48337</td>\n",
       "      <td>200</td>\n",
       "      <td>0</td>\n",
       "      <td>88000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>DrDoS_DNS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>172.16.0.5-192.168.50.1-634-27878-17</td>\n",
       "      <td>32026</td>\n",
       "      <td>200</td>\n",
       "      <td>0</td>\n",
       "      <td>88000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>440.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>DrDoS_DNS</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 83 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Flow_ID  Flow_Duration  Fwd_Total_Pkts  \\\n",
       "0  172.16.0.5-192.168.50.1-634-60495-17          28415              97   \n",
       "1  172.16.0.5-192.168.50.1-60495-634-17              2               2   \n",
       "2  172.16.0.5-192.168.50.1-634-46391-17          48549             200   \n",
       "3  172.16.0.5-192.168.50.1-634-11894-17          48337             200   \n",
       "4  172.16.0.5-192.168.50.1-634-27878-17          32026             200   \n",
       "\n",
       "   Bwd_Total_Pkts  Fwd_Total_Bytes  Bwd_Total_Bytes  Fwd_Pkt_Length_Max  \\\n",
       "0               0          42680.0              0.0               440.0   \n",
       "1               0            880.0              0.0               440.0   \n",
       "2               0          88000.0              0.0               440.0   \n",
       "3               0          88000.0              0.0               440.0   \n",
       "4               0          88000.0              0.0               440.0   \n",
       "\n",
       "   Fwd_Pkt_Length_Min  Fwd_Pkt_Length_Mean  Fwd_Pkt_Length_Std  ...  \\\n",
       "0               440.0                440.0                 0.0  ...   \n",
       "1               440.0                440.0                 0.0  ...   \n",
       "2               440.0                440.0                 0.0  ...   \n",
       "3               440.0                440.0                 0.0  ...   \n",
       "4               440.0                440.0                 0.0  ...   \n",
       "\n",
       "   Time_Active_Min  Time_Idle_Mean  Time_Idle_Std  Time_Idle_Max  \\\n",
       "0              0.0             0.0            0.0            0.0   \n",
       "1              0.0             0.0            0.0            0.0   \n",
       "2              0.0             0.0            0.0            0.0   \n",
       "3              0.0             0.0            0.0            0.0   \n",
       "4              0.0             0.0            0.0            0.0   \n",
       "\n",
       "   Time_Idle_Min      Label  HOPOPT  TCP  UDP  Malicious  \n",
       "0            0.0  DrDoS_DNS       0    0    1          1  \n",
       "1            0.0  DrDoS_DNS       0    0    1          1  \n",
       "2            0.0  DrDoS_DNS       0    0    1          1  \n",
       "3            0.0  DrDoS_DNS       0    0    1          1  \n",
       "4            0.0  DrDoS_DNS       0    0    1          1  \n",
       "\n",
       "[5 rows x 83 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save test SQL database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = sqlalchemy.create_engine('sqlite:///test.sqlite')\n",
    "df.to_sql(name='01-12_DrDoS_DNS', con=engine, if_exists='replace')"
   ]
  },
  {
   "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}