eda_lyca_nl.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# LYCA EDA, Close ports after sending!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import plotly.graph_objs as go"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Files in directory: ['data_Lyca_server_7.csv', 'data_Lyca_server_8.csv', 'data_lyca_7_rapid_phone.csv', 'data_lyca_3_phone_long.csv', 'data_Lyca_1_phone.csv', 'data_Lyca_server_9.csv', 'data_Lyca_server_3.csv', 'data_lyca_6_phone_rapid.csv', 'data_Lyca_server_10.csv', 'Lyca_LONG_phone_10.csv', 'data_Lyca_5_phone_long.csv', 'data_Lyca_server_2.csv', 'data_Lyca_server_6.csv', 'data_Lyca_server_5.csv', 'data_lyca_4_long_phone.csv', 'data_Lyca_2_phone.csv', 'Lyca_LONG_phone_8.csv', 'data_Lyca_server_1.csv', 'data_Lyca_server_4.csv', 'Lyca_LONG_phone_9.csv']\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "def get_files_in_directory(directory):\n",
    "    # Initialize an empty list to store file names\n",
    "    files = []\n",
    "\n",
    "    # Loop through all files and directories in the specified directory\n",
    "    for file_name in os.listdir(directory):\n",
    "        # Check if the current entry is a file\n",
    "        if os.path.isfile(os.path.join(directory, file_name)):\n",
    "            # If it's a file, add its name to the list\n",
    "            files.append(file_name)\n",
    "\n",
    "    return files\n",
    "\n",
    "# Example usage\n",
    "directory = \"data/lyca_nl/\"\n",
    "files_in_directory = get_files_in_directory(directory)\n",
    "print(\"Files in directory:\", files_in_directory)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Open File 1 and visualize it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load data and clean up\n",
    "\n",
    "df_phone = pd.read_csv(\"data/lyca_nl/data_Lyca_1_phone.csv\")\n",
    "df_server = pd.read_csv(\"data/lyca_nl/data_Lyca_server_1.csv\")\n",
    "trimmed_columns = {col: col.strip() for col in df_server.columns}\n",
    "df_server = df_server.rename(columns=trimmed_columns)\n",
    "trimmed_columns = {col: col.strip() for col in df_phone.columns}\n",
    "df_phone = df_phone.rename(columns=trimmed_columns)\n",
    "df_merged = df_phone.merge(df_server, how=\"inner\", on=\"Message id\")\n",
    "df_merged.sort_values(by=\"Time_y\")\n",
    "df_merged[\"Time_y\"] = df_merged[\"Time_y\"].astype(int)\n",
    "df_merged[\"time_difference_y\"] = df_merged['Time_y'].diff()\n",
    "df_merged.sort_values(by=\"Time_x\")\n",
    "df_merged[\"Time_x\"] = df_merged[\"Time_x\"].astype(int)\n",
    "df_merged[\"time_difference_x\"] = df_merged['Time_x'].diff()\n",
    "df_merged[\"Sender Port_x\"] = df_merged[\"Sender Port_x\"].astype(str)\n",
    "df_merged[\"Sender Port_y\"] = df_merged[\"Sender Port_y\"].astype(str)\n",
    "\n",
    "\n",
    "print(f\"Number of rows {len(df_merged.index)}\")\n",
    "# Visualize \n",
    "\n",
    "# Create a line chart using Plotly\n",
    "fig = go.Figure()\n",
    "\n",
    "# Add a line trace\n",
    "fig.add_trace(go.Scatter(x=df_merged['Time_x'], y=df_merged['Sender Port_y'], mode='lines', name='Port_y'))\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title='Line Chart of Time and Value for set 1',\n",
    "                  xaxis_title='Time',\n",
    "                  yaxis_title='Value')\n",
    "\n",
    "# Show the plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Open file 2 and visualize "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load data and clean up\n",
    "\n",
    "df_phone = pd.read_csv(\"data/lyca_nl/data_Lyca_2_phone.csv\")\n",
    "df_server = pd.read_csv(\"data/lyca_nl/data_Lyca_server_2.csv\")\n",
    "trimmed_columns = {col: col.strip() for col in df_server.columns}\n",
    "df_server = df_server.rename(columns=trimmed_columns)\n",
    "trimmed_columns = {col: col.strip() for col in df_phone.columns}\n",
    "df_phone = df_phone.rename(columns=trimmed_columns)\n",
    "df_merged = df_phone.merge(df_server, how=\"inner\", on=\"Message id\")\n",
    "df_merged.sort_values(by=\"Time_y\")\n",
    "df_merged[\"Time_y\"] = df_merged[\"Time_y\"].astype(int)\n",
    "df_merged[\"time_difference_y\"] = df_merged['Time_y'].diff()\n",
    "df_merged.sort_values(by=\"Time_x\")\n",
    "df_merged[\"Time_x\"] = df_merged[\"Time_x\"].astype(int)\n",
    "df_merged[\"time_difference_x\"] = df_merged['Time_x'].diff()\n",
    "df_merged[\"Sender Port_x\"] = df_merged[\"Sender Port_x\"].astype(str)\n",
    "df_merged[\"Sender Port_y\"] = df_merged[\"Sender Port_y\"].astype(str)\n",
    "\n",
    "print(f\"Number of rows {len(df_merged.index)}\")\n",
    "\n",
    "\n",
    "# Visualize \n",
    "\n",
    "# Create a line chart using Plotly\n",
    "fig = go.Figure()\n",
    "\n",
    "# Add a line trace\n",
    "fig.add_trace(go.Scatter(x=df_merged['Time_x'], y=df_merged['Sender Port_y'], mode='lines', name='Port_y'))\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title='Line Chart of Time and Value for set 2',\n",
    "                  xaxis_title='Time',\n",
    "                  yaxis_title='Value')\n",
    "\n",
    "# Show the plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Open file 3 and visualize "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load data and clean up\n",
    "\n",
    "df_phone = pd.read_csv(\"data/lyca_nl/data_lyca_3_phone_long.csv\")\n",
    "df_server = pd.read_csv(\"data/lyca_nl/data_Lyca_server_3.csv\")\n",
    "trimmed_columns = {col: col.strip() for col in df_server.columns}\n",
    "df_server = df_server.rename(columns=trimmed_columns)\n",
    "trimmed_columns = {col: col.strip() for col in df_phone.columns}\n",
    "df_phone = df_phone.rename(columns=trimmed_columns)\n",
    "df_merged = df_phone.merge(df_server, how=\"inner\", on=\"Message id\")\n",
    "df_merged.sort_values(by=\"Time_y\")\n",
    "df_merged[\"Time_y\"] = df_merged[\"Time_y\"].astype(int)\n",
    "df_merged[\"time_difference_y\"] = df_merged['Time_y'].diff()\n",
    "df_merged.sort_values(by=\"Time_x\")\n",
    "df_merged[\"Time_x\"] = df_merged[\"Time_x\"].astype(int)\n",
    "df_merged[\"time_difference_x\"] = df_merged['Time_x'].diff()\n",
    "df_merged[\"Sender Port_x\"] = df_merged[\"Sender Port_x\"].astype(str)\n",
    "df_merged[\"Sender Port_y\"] = df_merged[\"Sender Port_y\"].astype(str)\n",
    "\n",
    "print(f\"Number of rows {len(df_merged.index)}\")\n",
    "\n",
    "# Visualize \n",
    "\n",
    "# Create a line chart using Plotly\n",
    "fig = go.Figure()\n",
    "\n",
    "# Add a line trace\n",
    "fig.add_trace(go.Scatter(x=df_merged['Time_x'], y=df_merged['Sender Port_y'], mode='lines', name='Port_y'))\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title='Line Chart of Time and Value for set 2',\n",
    "                  xaxis_title='Time',\n",
    "                  yaxis_title='Value')\n",
    "\n",
    "# Show the plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Concatenate all of them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Time_x</th>\n",
       "      <th>Sender Port_x</th>\n",
       "      <th>Sender Port_y</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1708107803585</td>\n",
       "      <td>1831</td>\n",
       "      <td>63833</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1708107803607</td>\n",
       "      <td>36192</td>\n",
       "      <td>53922</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1708107803629</td>\n",
       "      <td>16452</td>\n",
       "      <td>15691</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1708107803651</td>\n",
       "      <td>33864</td>\n",
       "      <td>46725</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1708107803674</td>\n",
       "      <td>40637</td>\n",
       "      <td>36204</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>288376</th>\n",
       "      <td>1708444339049</td>\n",
       "      <td>58060</td>\n",
       "      <td>26295</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>288377</th>\n",
       "      <td>1708444339434</td>\n",
       "      <td>29135</td>\n",
       "      <td>47414</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>288378</th>\n",
       "      <td>1708444339818</td>\n",
       "      <td>38907</td>\n",
       "      <td>31873</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>288379</th>\n",
       "      <td>1708444340203</td>\n",
       "      <td>57776</td>\n",
       "      <td>21762</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>288380</th>\n",
       "      <td>1708444340589</td>\n",
       "      <td>45967</td>\n",
       "      <td>14901</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>288381 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Time_x  Sender Port_x  Sender Port_y  label\n",
       "0       1708107803585           1831          63833      2\n",
       "1       1708107803607          36192          53922      2\n",
       "2       1708107803629          16452          15691      2\n",
       "3       1708107803651          33864          46725      2\n",
       "4       1708107803674          40637          36204      2\n",
       "...               ...            ...            ...    ...\n",
       "288376  1708444339049          58060          26295      9\n",
       "288377  1708444339434          29135          47414      9\n",
       "288378  1708444339818          38907          31873      9\n",
       "288379  1708444340203          57776          21762      9\n",
       "288380  1708444340589          45967          14901      9\n",
       "\n",
       "[288381 rows x 4 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame()\n",
    "for i in range(1,11):\n",
    "    names = list(filter(lambda x: f\"_{i}\" in x, files_in_directory))\n",
    "    phone = list(filter(lambda x: \"phone\" in x, names))[0]\n",
    "    server = list(filter(lambda x: \"server\" in x, names))[0]\n",
    "    df_phone = pd.read_csv(f\"data/lyca_nl/{phone}\")\n",
    "    df_server = pd.read_csv(f\"data/lyca_nl/{server}\")\n",
    "    trimmed_columns = {col: col.strip() for col in df_server.columns}\n",
    "    df_server = df_server.rename(columns=trimmed_columns)\n",
    "    trimmed_columns = {col: col.strip() for col in df_phone.columns}\n",
    "    df_phone = df_phone.rename(columns=trimmed_columns)\n",
    "    df_merged = df_phone.merge(df_server, how=\"inner\", on=\"Message id\")\n",
    "    df_merged.sort_values(by=\"Time_y\")\n",
    "    df_merged[\"Time_y\"] = df_merged[\"Time_y\"].astype(int)\n",
    "    df_merged[\"time_difference_y\"] = df_merged['Time_y'].diff()\n",
    "    df_merged.sort_values(by=\"Time_x\")\n",
    "    df_merged[\"Time_x\"] = df_merged[\"Time_x\"].astype(int)\n",
    "    df_merged[\"time_difference_x\"] = df_merged['Time_x'].diff()\n",
    "    df_merged[\"Sender Port_x\"] = df_merged[\"Sender Port_x\"].astype(int)\n",
    "    df_merged[\"Sender Port_y\"] = df_merged[\"Sender Port_y\"].astype(int)\n",
    "    df_merged[\"label\"] = i\n",
    "    df_merged = df_merged[[\"Time_x\", 'Sender Port_x', 'Sender Port_y', \"label\"]]\n",
    "    df = pd.concat([df, df_merged], ignore_index=True)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2048\n",
      "65535\n"
     ]
    }
   ],
   "source": [
    "print(df['Sender Port_y'].min())\n",
    "print(df['Sender Port_y'].max())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Sender Port_y\n",
       "29641    22\n",
       "52620    20\n",
       "11343    19\n",
       "34870    19\n",
       "7985     19\n",
       "         ..\n",
       "23757     1\n",
       "63649     1\n",
       "11755     1\n",
       "49089     1\n",
       "16428     1\n",
       "Name: count, Length: 50442, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Sender Port_y'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Time_x</th>\n",
       "      <th>Sender Port_x</th>\n",
       "      <th>Sender Port_y</th>\n",
       "      <th>label</th>\n",
       "      <th>differences</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>65507</th>\n",
       "      <td>1708158964701</td>\n",
       "      <td>8030</td>\n",
       "      <td>29641</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66461</th>\n",
       "      <td>1708159407002</td>\n",
       "      <td>25435</td>\n",
       "      <td>29641</td>\n",
       "      <td>3</td>\n",
       "      <td>442.301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70738</th>\n",
       "      <td>1708161385877</td>\n",
       "      <td>50577</td>\n",
       "      <td>29641</td>\n",
       "      <td>3</td>\n",
       "      <td>1978.875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75215</th>\n",
       "      <td>1708163460382</td>\n",
       "      <td>62815</td>\n",
       "      <td>29641</td>\n",
       "      <td>3</td>\n",
       "      <td>2074.505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77844</th>\n",
       "      <td>1708164676357</td>\n",
       "      <td>61207</td>\n",
       "      <td>29641</td>\n",
       "      <td>3</td>\n",
       "      <td>1215.975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87243</th>\n",
       "      <td>1708169919864</td>\n",
       "      <td>12092</td>\n",
       "      <td>29641</td>\n",
       "      <td>4</td>\n",
       "      <td>5243.507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95502</th>\n",
       "      <td>1708173325761</td>\n",
       "      <td>9396</td>\n",
       "      <td>29641</td>\n",
       "      <td>4</td>\n",
       "      <td>3405.897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96255</th>\n",
       "      <td>1708173637032</td>\n",
       "      <td>39697</td>\n",
       "      <td>29641</td>\n",
       "      <td>4</td>\n",
       "      <td>311.271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96635</th>\n",
       "      <td>1708173793255</td>\n",
       "      <td>38352</td>\n",
       "      <td>29641</td>\n",
       "      <td>4</td>\n",
       "      <td>156.223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108019</th>\n",
       "      <td>1708178503377</td>\n",
       "      <td>22167</td>\n",
       "      <td>29641</td>\n",
       "      <td>4</td>\n",
       "      <td>4710.122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114664</th>\n",
       "      <td>1708181232708</td>\n",
       "      <td>54288</td>\n",
       "      <td>29641</td>\n",
       "      <td>4</td>\n",
       "      <td>2729.331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129167</th>\n",
       "      <td>1708187213169</td>\n",
       "      <td>3294</td>\n",
       "      <td>29641</td>\n",
       "      <td>4</td>\n",
       "      <td>5980.461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130762</th>\n",
       "      <td>1708187871034</td>\n",
       "      <td>16337</td>\n",
       "      <td>29641</td>\n",
       "      <td>4</td>\n",
       "      <td>657.865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136624</th>\n",
       "      <td>1708190283482</td>\n",
       "      <td>17880</td>\n",
       "      <td>29641</td>\n",
       "      <td>4</td>\n",
       "      <td>2412.448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141078</th>\n",
       "      <td>1708192114669</td>\n",
       "      <td>56916</td>\n",
       "      <td>29641</td>\n",
       "      <td>4</td>\n",
       "      <td>1831.187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148773</th>\n",
       "      <td>1708213607377</td>\n",
       "      <td>31646</td>\n",
       "      <td>29641</td>\n",
       "      <td>5</td>\n",
       "      <td>21492.708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150441</th>\n",
       "      <td>1708214332973</td>\n",
       "      <td>18792</td>\n",
       "      <td>29641</td>\n",
       "      <td>5</td>\n",
       "      <td>725.596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151691</th>\n",
       "      <td>1708214872595</td>\n",
       "      <td>20475</td>\n",
       "      <td>29641</td>\n",
       "      <td>5</td>\n",
       "      <td>539.622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160954</th>\n",
       "      <td>1708218878857</td>\n",
       "      <td>59827</td>\n",
       "      <td>29641</td>\n",
       "      <td>5</td>\n",
       "      <td>4006.262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161151</th>\n",
       "      <td>1708218963991</td>\n",
       "      <td>50865</td>\n",
       "      <td>29641</td>\n",
       "      <td>5</td>\n",
       "      <td>85.134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163781</th>\n",
       "      <td>1708220103249</td>\n",
       "      <td>21821</td>\n",
       "      <td>29641</td>\n",
       "      <td>5</td>\n",
       "      <td>1139.258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165622</th>\n",
       "      <td>1708220899836</td>\n",
       "      <td>30641</td>\n",
       "      <td>29641</td>\n",
       "      <td>5</td>\n",
       "      <td>796.587</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Time_x  Sender Port_x  Sender Port_y  label  differences\n",
       "65507   1708158964701           8030          29641      3          NaN\n",
       "66461   1708159407002          25435          29641      3      442.301\n",
       "70738   1708161385877          50577          29641      3     1978.875\n",
       "75215   1708163460382          62815          29641      3     2074.505\n",
       "77844   1708164676357          61207          29641      3     1215.975\n",
       "87243   1708169919864          12092          29641      4     5243.507\n",
       "95502   1708173325761           9396          29641      4     3405.897\n",
       "96255   1708173637032          39697          29641      4      311.271\n",
       "96635   1708173793255          38352          29641      4      156.223\n",
       "108019  1708178503377          22167          29641      4     4710.122\n",
       "114664  1708181232708          54288          29641      4     2729.331\n",
       "129167  1708187213169           3294          29641      4     5980.461\n",
       "130762  1708187871034          16337          29641      4      657.865\n",
       "136624  1708190283482          17880          29641      4     2412.448\n",
       "141078  1708192114669          56916          29641      4     1831.187\n",
       "148773  1708213607377          31646          29641      5    21492.708\n",
       "150441  1708214332973          18792          29641      5      725.596\n",
       "151691  1708214872595          20475          29641      5      539.622\n",
       "160954  1708218878857          59827          29641      5     4006.262\n",
       "161151  1708218963991          50865          29641      5       85.134\n",
       "163781  1708220103249          21821          29641      5     1139.258\n",
       "165622  1708220899836          30641          29641      5      796.587"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp =  df[df['Sender Port_y'] == 29641].sort_values(by=\"Time_x\")\n",
    "temp['differences'] = temp['Time_x'].diff()\n",
    "temp['differences'] = temp['differences'] /1000\n",
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib as plt\n",
    "df[\"time\"] = pd.to_datetime(df[\"Time_x\"], unit='ms' )\n",
    "# # Add a line trace\n",
    "# fig.add_trace(go.Scatter(x=df['time'], y=df['Sender Port_y'], name=df[\"label\"], mode='lines' ))\n",
    "df[\"label\"] = df[\"label\"].astype(str)\n",
    "# Get unique categories\n",
    "categories = df['label'].unique()\n",
    "\n",
    "colors = ['blue', 'red', 'green', 'cyan', 'magenta', 'yellow', 'black', 'white', 'gray', 'purple']\n",
    "\n",
    "\n",
    "# Create a dictionary to map categories to colors\n",
    "color_mapping = {category: color for category, color in zip(categories, colors)}\n",
    "\n",
    "\n",
    "# Create a scatter plot using Plotly\n",
    "fig = go.Figure()\n",
    "\n",
    "# Add a scatter trace with colored markers for each category\n",
    "for category, color in color_mapping.items():\n",
    "    subset = df[df['label'] == category]\n",
    "    fig.add_trace(go.Scatter(x=subset['time'], y=subset['Sender Port_y'], mode='lines+markers', \n",
    "                                                          line=dict(color=color), name=category))\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title='Line Chart of Time and Value',\n",
    "                  xaxis_title='Time',\n",
    "                  yaxis_title='Value')\n",
    "\n",
    "# Show the plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "288299"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l = [[0]]\n",
    "THRESHOLD = 1\n",
    "prev = df.iloc[0]\n",
    "for index, row in df.iterrows():\n",
    "    \n",
    "\n",
    "    if abs(row[\"Sender Port_y\"] - prev[\"Sender Port_y\"]) > THRESHOLD or row[\"Time_x\"] - prev[\"Time_x\"] > 10000:\n",
    "        l[-1].append(index -1)\n",
    "        l.append([index])\n",
    "\n",
    "\n",
    "    prev = row\n",
    "len(l)\n",
    "# print(l)\n",
    "# print(len(l))\n",
    "# l.pop()\n",
    "# differences = [ e-b  for b,e in l ]\n",
    "# print(differences)\n",
    "# print(len(differences))\n",
    "\n",
    "# filtered = list(filter(lambda x: x < 5, differences))\n",
    "# len(filtered)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# differences_df = pd.DataFrame(differences, columns=[\"diffs\"])\n",
    "# fig = go.Figure(data=[go.Histogram(x=differences_df[\"diffs\"], nbinsx=len(differences))])\n",
    "# fig.update_layout(title='Histogram of Sample Data', xaxis_title='Value', yaxis_title='Frequency')\n",
    "# fig.show()\n",
    "# # Calculate the PDF using numpy\n",
    "# data = differences\n",
    "# density, bins = np.histogram(data, bins=10, density=True)\n",
    "# midpoints = (bins[1:] + bins[:-1]) / 2\n",
    "\n",
    "# # Create line plot of the PDF using Plotly\n",
    "# fig = go.Figure(data=[go.Scatter(x=midpoints, y=density, mode='lines')])\n",
    "# fig.update_layout(title='Probability Density Function of Sample Data', xaxis_title='Value', yaxis_title='Density')\n",
    "# fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Time_x</th>\n",
       "      <th>Sender Port_x</th>\n",
       "      <th>Sender Port_y</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100000</th>\n",
       "      <td>1708175187587</td>\n",
       "      <td>47830</td>\n",
       "      <td>19977</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100001</th>\n",
       "      <td>1708175187992</td>\n",
       "      <td>15175</td>\n",
       "      <td>17912</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100002</th>\n",
       "      <td>1708175188397</td>\n",
       "      <td>55128</td>\n",
       "      <td>8234</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100003</th>\n",
       "      <td>1708175188803</td>\n",
       "      <td>26123</td>\n",
       "      <td>57560</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100004</th>\n",
       "      <td>1708175189209</td>\n",
       "      <td>17381</td>\n",
       "      <td>48539</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100005</th>\n",
       "      <td>1708175189615</td>\n",
       "      <td>1235</td>\n",
       "      <td>20623</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100006</th>\n",
       "      <td>1708175190021</td>\n",
       "      <td>30190</td>\n",
       "      <td>9477</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100007</th>\n",
       "      <td>1708175190427</td>\n",
       "      <td>8576</td>\n",
       "      <td>32404</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100008</th>\n",
       "      <td>1708175190833</td>\n",
       "      <td>16607</td>\n",
       "      <td>5516</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100009</th>\n",
       "      <td>1708175191239</td>\n",
       "      <td>48208</td>\n",
       "      <td>29271</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100010</th>\n",
       "      <td>1708175191644</td>\n",
       "      <td>56546</td>\n",
       "      <td>57811</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100011</th>\n",
       "      <td>1708175192050</td>\n",
       "      <td>15298</td>\n",
       "      <td>19985</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100012</th>\n",
       "      <td>1708175192456</td>\n",
       "      <td>18588</td>\n",
       "      <td>48248</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100013</th>\n",
       "      <td>1708175192863</td>\n",
       "      <td>4340</td>\n",
       "      <td>13812</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100014</th>\n",
       "      <td>1708175193268</td>\n",
       "      <td>40651</td>\n",
       "      <td>30926</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100015</th>\n",
       "      <td>1708175193673</td>\n",
       "      <td>6476</td>\n",
       "      <td>28704</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100016</th>\n",
       "      <td>1708175194078</td>\n",
       "      <td>48546</td>\n",
       "      <td>60022</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100017</th>\n",
       "      <td>1708175194484</td>\n",
       "      <td>18681</td>\n",
       "      <td>55342</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100018</th>\n",
       "      <td>1708175194889</td>\n",
       "      <td>36230</td>\n",
       "      <td>50385</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100019</th>\n",
       "      <td>1708175195296</td>\n",
       "      <td>52574</td>\n",
       "      <td>38375</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100020</th>\n",
       "      <td>1708175195702</td>\n",
       "      <td>49819</td>\n",
       "      <td>9485</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100021</th>\n",
       "      <td>1708175196109</td>\n",
       "      <td>41455</td>\n",
       "      <td>23104</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100022</th>\n",
       "      <td>1708175196514</td>\n",
       "      <td>7981</td>\n",
       "      <td>44203</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100023</th>\n",
       "      <td>1708175196920</td>\n",
       "      <td>21289</td>\n",
       "      <td>47044</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100024</th>\n",
       "      <td>1708175197326</td>\n",
       "      <td>26413</td>\n",
       "      <td>28677</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100025</th>\n",
       "      <td>1708175197733</td>\n",
       "      <td>2238</td>\n",
       "      <td>25325</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100026</th>\n",
       "      <td>1708175198139</td>\n",
       "      <td>32724</td>\n",
       "      <td>16300</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100027</th>\n",
       "      <td>1708175198544</td>\n",
       "      <td>33242</td>\n",
       "      <td>33991</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100028</th>\n",
       "      <td>1708175198949</td>\n",
       "      <td>13222</td>\n",
       "      <td>50375</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100029</th>\n",
       "      <td>1708175199356</td>\n",
       "      <td>48396</td>\n",
       "      <td>25341</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100030</th>\n",
       "      <td>1708175199761</td>\n",
       "      <td>47467</td>\n",
       "      <td>18152</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100031</th>\n",
       "      <td>1708175200166</td>\n",
       "      <td>20120</td>\n",
       "      <td>10451</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100032</th>\n",
       "      <td>1708175200572</td>\n",
       "      <td>46485</td>\n",
       "      <td>42344</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100033</th>\n",
       "      <td>1708175200977</td>\n",
       "      <td>57163</td>\n",
       "      <td>60026</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100034</th>\n",
       "      <td>1708175201383</td>\n",
       "      <td>27317</td>\n",
       "      <td>61548</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100035</th>\n",
       "      <td>1708175201789</td>\n",
       "      <td>22060</td>\n",
       "      <td>8584</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100036</th>\n",
       "      <td>1708175202195</td>\n",
       "      <td>62637</td>\n",
       "      <td>19967</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100037</th>\n",
       "      <td>1708175202600</td>\n",
       "      <td>6977</td>\n",
       "      <td>19976</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100038</th>\n",
       "      <td>1708175203007</td>\n",
       "      <td>35895</td>\n",
       "      <td>17248</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100039</th>\n",
       "      <td>1708175203413</td>\n",
       "      <td>19930</td>\n",
       "      <td>11661</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100040</th>\n",
       "      <td>1708175203820</td>\n",
       "      <td>62998</td>\n",
       "      <td>20369</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100041</th>\n",
       "      <td>1708175204226</td>\n",
       "      <td>62059</td>\n",
       "      <td>4861</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100042</th>\n",
       "      <td>1708175204632</td>\n",
       "      <td>41793</td>\n",
       "      <td>40446</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100043</th>\n",
       "      <td>1708175205037</td>\n",
       "      <td>30460</td>\n",
       "      <td>37744</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100044</th>\n",
       "      <td>1708175205443</td>\n",
       "      <td>20946</td>\n",
       "      <td>18778</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100045</th>\n",
       "      <td>1708175205848</td>\n",
       "      <td>19949</td>\n",
       "      <td>28086</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100046</th>\n",
       "      <td>1708175206254</td>\n",
       "      <td>5570</td>\n",
       "      <td>37108</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100047</th>\n",
       "      <td>1708175206661</td>\n",
       "      <td>50346</td>\n",
       "      <td>14788</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100048</th>\n",
       "      <td>1708175207066</td>\n",
       "      <td>1043</td>\n",
       "      <td>31542</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100049</th>\n",
       "      <td>1708175207471</td>\n",
       "      <td>29463</td>\n",
       "      <td>53250</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Time_x  Sender Port_x  Sender Port_y  label\n",
       "100000  1708175187587          47830          19977      4\n",
       "100001  1708175187992          15175          17912      4\n",
       "100002  1708175188397          55128           8234      4\n",
       "100003  1708175188803          26123          57560      4\n",
       "100004  1708175189209          17381          48539      4\n",
       "100005  1708175189615           1235          20623      4\n",
       "100006  1708175190021          30190           9477      4\n",
       "100007  1708175190427           8576          32404      4\n",
       "100008  1708175190833          16607           5516      4\n",
       "100009  1708175191239          48208          29271      4\n",
       "100010  1708175191644          56546          57811      4\n",
       "100011  1708175192050          15298          19985      4\n",
       "100012  1708175192456          18588          48248      4\n",
       "100013  1708175192863           4340          13812      4\n",
       "100014  1708175193268          40651          30926      4\n",
       "100015  1708175193673           6476          28704      4\n",
       "100016  1708175194078          48546          60022      4\n",
       "100017  1708175194484          18681          55342      4\n",
       "100018  1708175194889          36230          50385      4\n",
       "100019  1708175195296          52574          38375      4\n",
       "100020  1708175195702          49819           9485      4\n",
       "100021  1708175196109          41455          23104      4\n",
       "100022  1708175196514           7981          44203      4\n",
       "100023  1708175196920          21289          47044      4\n",
       "100024  1708175197326          26413          28677      4\n",
       "100025  1708175197733           2238          25325      4\n",
       "100026  1708175198139          32724          16300      4\n",
       "100027  1708175198544          33242          33991      4\n",
       "100028  1708175198949          13222          50375      4\n",
       "100029  1708175199356          48396          25341      4\n",
       "100030  1708175199761          47467          18152      4\n",
       "100031  1708175200166          20120          10451      4\n",
       "100032  1708175200572          46485          42344      4\n",
       "100033  1708175200977          57163          60026      4\n",
       "100034  1708175201383          27317          61548      4\n",
       "100035  1708175201789          22060           8584      4\n",
       "100036  1708175202195          62637          19967      4\n",
       "100037  1708175202600           6977          19976      4\n",
       "100038  1708175203007          35895          17248      4\n",
       "100039  1708175203413          19930          11661      4\n",
       "100040  1708175203820          62998          20369      4\n",
       "100041  1708175204226          62059           4861      4\n",
       "100042  1708175204632          41793          40446      4\n",
       "100043  1708175205037          30460          37744      4\n",
       "100044  1708175205443          20946          18778      4\n",
       "100045  1708175205848          19949          28086      4\n",
       "100046  1708175206254           5570          37108      4\n",
       "100047  1708175206661          50346          14788      4\n",
       "100048  1708175207066           1043          31542      4\n",
       "100049  1708175207471          29463          53250      4"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[100000:100050]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# LYCA, WHEN CLOSING THE PORTS SEEMS TO BE COMPLETELY RANDOM!!!!\n",
    "\n",
    "Wikipedia says they use KPN but it does not behave like KPN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = df.groupby([\"Sender Port_x\", \"Sender Port_y\"],as_index=False).size()\n",
    "len(grouped[grouped['size'] >1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "50442"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Sender Port_y'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Seems that there is no repeating pair  X -> Y probably just an unsorted FIFO queue.\n",
    "\n",
    "### JUST 17 repetitions in 280k rows. Probably just a huge unsorted queue that everyone consumes ports from!!!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "telecom-analysis",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}