{
 "cells": [
  {
   "cell_type": "code",
   "id": "f89a2dfb203eae5b",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2024-02-21T17:38:29.861499Z",
     "start_time": "2024-02-21T17:38:29.537555Z"
    }
   },
   "source": [
    "import os\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np"
   ],
   "outputs": [],
   "execution_count": 1
  },
  {
   "cell_type": "code",
   "source": [
    "devices_root = os.path.abspath('../../../')\n",
    "\n",
    "input_files_labeled = [\n",
    "    \"dev01-shelly-plus-1pm-relais/shelly-relais-activity.pickel\",\n",
    "    \"dev02-sonoff-relais/sonoff_relais_activity_98.pickel\",\n",
    "    \"dev02-sonoff-relais/sonoff_relais_attack_98.pickel\",\n",
    "    \"dev03-tuya-lampe/tuya-lamp-1h-activity.pickel\",\n",
    "    \"dev03-tuya-lampe/tuya-lamp-attacks-1.pickel\",\n",
    "    \"dev04-linkind-zigbee-mini-hub-linkind-lampe/linkind-attacks.pickel\",\n",
    "    \"dev04-linkind-zigbee-mini-hub-linkind-lampe/linkind-30minactivity.pickel\",\n",
    "    \"dev05-tplink-tapo-l530e-birne/lampe-1h-activity.pickel\",\n",
    "    \"dev05-tplink-tapo-l530e-birne/lampe-1h-attacks.pickel\",\n",
    "    \"dev06-tapo-wifi-socket/socket-activity.pickle\",  #-> broken\n",
    "    \"dev06-tapo-wifi-socket/socket-attacks.pickle\",  #-> broken\n",
    "    'dev08-echo-dot-l4s3re/echo-dot-activity.pickel',\n",
    "    'dev08-echo-dot-l4s3re/echo-dot-attack.pickel',\n",
    "    \"dev10-aeotec-z-wave-hub/zwavehub-activity-1h.pickel\",\n",
    "    \"dev10-aeotec-z-wave-hub/zwavehub-attacks.pickel\",\n",
    "    \"dev11-aqara-presence-sensor/aqara-presence-sensor-activities1-annotated.pickle\",\n",
    "    \"dev11-aqara-presence-sensor/aqara-presence-sensor-activities2-annotated.pickle\",\n",
    "    \"dev11-aqara-presence-sensor/aqara-presence-sensor-attacks-annotated.pickle\",\n",
    "    \"dev12-shelly-bewegungsmelder/activity.pickel\",\n",
    "    \"dev12-shelly-bewegungsmelder/attacks.pickel\",\n",
    "    \"dev13-shelly-flood/shelly_flood-1h-activity.pickel\",\n",
    "    \"dev14-shelly-ht-temperatur-sensor/shelly-ht-1h-activity.pickle\",\n",
    "    \"dev14-shelly-ht-temperatur-sensor/shelly-ht-attacks.pickle\",\n",
    "    \"dev15-ring-door-camera/ring-1h-activity.pickel\",\n",
    "    \"dev15-ring-door-camera/ring-attacks.pickel\",\n",
    "]\n",
    "\n",
    "# idle_files_root = os.path.join(devices_root, \"../\", \"datasets\", \"pickel\")\n",
    "# load all files from the idle folder\n",
    "# idle_file_limit = 7\n",
    "# input_files_idle = [os.path.join(idle_files_root, file) for file in os.listdir(idle_files_root) if\n",
    "#                     file.endswith('.pickel')][:idle_file_limit]\n",
    "\n",
    "# input_files_filtered = [file for file in input_files_labeled if \"activit\" in file]\n",
    "input_files_filtered = [file for file in input_files_labeled if \"attack\" in file]\n",
    "\n",
    "input_files = input_files_filtered\n",
    "output_filename = \"attack_merged.pickle\"\n",
    "shuffle = False\n",
    "\n",
    "\n",
    "def load_dataset(dataset_path):\n",
    "    if dataset_path.endswith('.csv'):\n",
    "        return pd.read_csv(dataset_path)\n",
    "    elif dataset_path.endswith('.pickel') or dataset_path.endswith('.pickle'):\n",
    "        return pd.read_pickle(dataset_path)\n",
    "    else:\n",
    "        raise ValueError(f\"Unknown file format for {dataset_path}\")\n",
    "\n",
    "\n",
    "# laod all input datasets\n",
    "input_datasets = [load_dataset(os.path.join(devices_root, input_file)) for input_file in input_files]\n",
    "\n",
    "for i in range(len(input_datasets)):\n",
    "    dataset = input_datasets[i]\n",
    "    if \"ip/ip.dst\" not in dataset.columns:\n",
    "        print(f\"Dataset {input_files[i]} does not contain ip/ip.dst, dropping\")\n",
    "        input_datasets[i] = None\n",
    "\n",
    "input_datasets = [dataset for dataset in input_datasets if dataset is not None]\n",
    "\n",
    "# add name to each dataset\n",
    "for dataset, input_file in zip(input_datasets, input_files):\n",
    "    dataset['name'] = input_file"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-21T17:38:35.229002Z",
     "start_time": "2024-02-21T17:38:29.863140Z"
    }
   },
   "id": "initial_id",
   "outputs": [],
   "execution_count": 2
  },
  {
   "cell_type": "code",
   "source": [
    "time_field = \"geninfo/timestamp\"\n",
    "assert all([time_field in dataset.columns for dataset in input_datasets])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-21T17:38:35.232567Z",
     "start_time": "2024-02-21T17:38:35.229946Z"
    }
   },
   "id": "e9d65986f29c1967",
   "outputs": [],
   "execution_count": 3
  },
  {
   "cell_type": "code",
   "source": [
    "def parse_time_field(field: pd.Series):\n",
    "    # format is Nov 12, 2021 14:00:00.078818000 CET, cut off the timezone\n",
    "    if pd.api.types.is_datetime64_ns_dtype(field):\n",
    "        return field\n",
    "    elif field.dtype == object:\n",
    "        return pd.to_datetime(field.str[:31], format=\"%b %d, %Y %H:%M:%S.%f\", errors='coerce')\n",
    "    elif field.dtype == int:\n",
    "        # assume epoch time in nano seconds\n",
    "        return pd.to_datetime(field, unit='ns', errors='coerce')\n",
    "    else:\n",
    "        raise ValueError(f\"Unknown dtype {field.dtype} for time field {field}\")\n",
    "\n",
    "\n",
    "# overwrite time field with parsed time\n",
    "for dataset in input_datasets:\n",
    "    dataset[time_field] = parse_time_field(dataset[time_field])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-21T17:38:39.432682Z",
     "start_time": "2024-02-21T17:38:35.234267Z"
    }
   },
   "id": "97c3c5c35451a858",
   "outputs": [],
   "execution_count": 4
  },
  {
   "cell_type": "code",
   "source": [
    "# create timedelta field with delta to beginning\n",
    "for dataset in input_datasets:\n",
    "    dataset['timedelta'] = dataset[time_field] - dataset[time_field].min()\n",
    "\n",
    "# create timedelta field with delta to previous packet\n",
    "for dataset in input_datasets:\n",
    "    dataset['timedelta_prev'] = dataset[time_field] - dataset[time_field].shift(1)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-21T17:38:39.466940Z",
     "start_time": "2024-02-21T17:38:39.433736Z"
    }
   },
   "id": "5f629bffada88c66",
   "outputs": [],
   "execution_count": 5
  },
  {
   "cell_type": "code",
   "source": [
    "lenghts_in_packets = [len(dataset) for dataset in input_datasets]\n",
    "max_length_in_packets = max(lenghts_in_packets)\n",
    "longest_dataset = input_datasets[lenghts_in_packets.index(max_length_in_packets)]\n",
    "\n",
    "lengths_in_time = [dataset[time_field].max() - dataset[time_field].min() for dataset in input_datasets]\n",
    "max_time_length = max(lengths_in_time)\n",
    "longest_time_dataset = input_datasets[lengths_in_time.index(max_time_length)]\n",
    "\n",
    "print(f\"Max packet length: {max_length_in_packets}\")\n",
    "print(f\"Max time length: {max_time_length}\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-21T17:38:39.482637Z",
     "start_time": "2024-02-21T17:38:39.467986Z"
    }
   },
   "id": "72805f69e94af20b",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Max packet length: 627034\n",
      "Max time length: 0 days 00:55:51.267808\n"
     ]
    }
   ],
   "execution_count": 6
  },
  {
   "cell_type": "code",
   "source": [
    "# create a new dataframe, with the length of the longest dataset\n",
    "\n",
    "if shuffle:\n",
    "    merged = longest_time_dataset.copy()\n",
    "    for dataset in input_datasets:\n",
    "        # skip the longest dataset\n",
    "        if dataset is longest_time_dataset:\n",
    "            continue\n",
    "\n",
    "        # calculate the max offset for the dataset\n",
    "        max_offset = max_time_length - (dataset[time_field].max() - dataset[time_field].min())\n",
    "\n",
    "        # get a random offset timedelt\n",
    "        offset = pd.Timedelta(np.random.randint(0, max_offset.seconds), unit='s')\n",
    "\n",
    "        # add the offset to the time field\n",
    "        dataset[time_field] = dataset[time_field] + offset\n",
    "\n",
    "        # insert the dataset into the merged dataset\n",
    "        merged = pd.concat([merged, dataset], ignore_index=True)\n",
    "else:\n",
    "    merged = pd.concat(input_datasets, ignore_index=True)\n",
    "\n",
    "# sort by time  \n",
    "merged = merged.sort_values(time_field)\n",
    "merged = merged.reset_index(drop=True)\n",
    "\n",
    "# recalculate the timedelta fields\n",
    "merged[\"timedelta\"] = merged[time_field] - merged[time_field].min()\n",
    "merged.iloc[0][\"timedelta_prev\"] = pd.Timedelta(0)\n",
    "merged[\"timedelta_prev\"] = merged[time_field] - merged[time_field].shift(1)\n",
    "\n",
    "merged"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-21T17:38:44.787118Z",
     "start_time": "2024-02-21T17:38:39.483676Z"
    }
   },
   "id": "2d593b82fd4b00f3",
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_1132560/308333621.py:30: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  merged.iloc[0][\"timedelta_prev\"] = pd.Timedelta(0)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "        frame/frame.offset_shift          geninfo/timestamp  \\\n",
       "0                    0.000000000 2023-11-21 11:25:39.957914   \n",
       "1                    0.000000000 2023-11-21 11:25:40.149325   \n",
       "2                    0.000000000 2023-11-21 11:25:40.149362   \n",
       "3                    0.000000000 2023-11-21 11:25:40.149538   \n",
       "4                    0.000000000 2023-11-21 11:25:40.150012   \n",
       "...                          ...                        ...   \n",
       "1002184              0.000000000 2023-11-29 15:02:05.919180   \n",
       "1002185                      NaN                        NaT   \n",
       "1002186                      NaN                        NaT   \n",
       "1002187                      NaN                        NaT   \n",
       "1002188                      NaN                        NaT   \n",
       "\n",
       "        tcp/tcp.flags/tcp.flags.cwr tcp/tcp.len  \\\n",
       "0                                 0           0   \n",
       "1                               NaN         NaN   \n",
       "2                               NaN         NaN   \n",
       "3                               NaN         NaN   \n",
       "4                                 0          68   \n",
       "...                             ...         ...   \n",
       "1002184                           0           0   \n",
       "1002185                         NaN         NaN   \n",
       "1002186                         NaN         NaN   \n",
       "1002187                         NaN         NaN   \n",
       "1002188                         NaN         NaN   \n",
       "\n",
       "        tcp/tcp.options/tcp.options.mss/tcp.options.mss_val ip/ip.len  \\\n",
       "0                                                     1460         60   \n",
       "1                                                      NaN        NaN   \n",
       "2                                                      NaN        NaN   \n",
       "3                                                      NaN        NaN   \n",
       "4                                                      NaN        108   \n",
       "...                                                    ...        ...   \n",
       "1002184                                                NaN         40   \n",
       "1002185                                                NaN        NaN   \n",
       "1002186                                                NaN        NaN   \n",
       "1002187                                                NaN        NaN   \n",
       "1002188                                                NaN        NaN   \n",
       "\n",
       "        arp/arp.proto.type tcp/tcp.stream eth/eth.dst/eth.dst.oui  \\\n",
       "0                      NaN              0                 9455724   \n",
       "1                   0x0800            NaN                16777215   \n",
       "2                   0x0800            NaN                16777215   \n",
       "3                   0x0800            NaN                 9455724   \n",
       "4                      NaN              1                 5538618   \n",
       "...                    ...            ...                     ...   \n",
       "1002184                NaN           1348                  315211   \n",
       "1002185                NaN            NaN                     NaN   \n",
       "1002186                NaN            NaN                     NaN   \n",
       "1002187                NaN            NaN                     NaN   \n",
       "1002188                NaN            NaN                     NaN   \n",
       "\n",
       "        ip/ip.frag_offset  ... tcp/tcp.flags/tcp.flags.ns  \\\n",
       "0                       0  ...                        NaN   \n",
       "1                     NaN  ...                        NaN   \n",
       "2                     NaN  ...                        NaN   \n",
       "3                     NaN  ...                        NaN   \n",
       "4                       0  ...                        NaN   \n",
       "...                   ...  ...                        ...   \n",
       "1002184                 0  ...                        NaN   \n",
       "1002185               NaN  ...                        NaN   \n",
       "1002186               NaN  ...                        NaN   \n",
       "1002187               NaN  ...                        NaN   \n",
       "1002188               NaN  ...                        NaN   \n",
       "\n",
       "        icmp/icmp.udp/icmp.udp.length  \\\n",
       "0                                 NaN   \n",
       "1                                 NaN   \n",
       "2                                 NaN   \n",
       "3                                 NaN   \n",
       "4                                 NaN   \n",
       "...                               ...   \n",
       "1002184                           NaN   \n",
       "1002185                           NaN   \n",
       "1002186                           NaN   \n",
       "1002187                           NaN   \n",
       "1002188                           NaN   \n",
       "\n",
       "        ip/<>/ip.options.routeralert/ip.opt.sec_cl icmp/icmp.ident  \\\n",
       "0                                              NaN             NaN   \n",
       "1                                              NaN             NaN   \n",
       "2                                              NaN             NaN   \n",
       "3                                              NaN             NaN   \n",
       "4                                              NaN             NaN   \n",
       "...                                            ...             ...   \n",
       "1002184                                        NaN             NaN   \n",
       "1002185                                        NaN             NaN   \n",
       "1002186                                        NaN             NaN   \n",
       "1002187                                        NaN             NaN   \n",
       "1002188                                        NaN             NaN   \n",
       "\n",
       "        igmp/igmp.max_resp frame/frame.ignore Attack  \\\n",
       "0                      NaN                NaN      0   \n",
       "1                      NaN                NaN      1   \n",
       "2                      NaN                NaN      0   \n",
       "3                      NaN                NaN      0   \n",
       "4                      NaN                NaN      0   \n",
       "...                    ...                ...    ...   \n",
       "1002184                NaN                NaN      2   \n",
       "1002185                NaN                NaN      2   \n",
       "1002186                NaN                NaN      2   \n",
       "1002187                NaN                NaN      1   \n",
       "1002188                NaN                NaN      3   \n",
       "\n",
       "                                                      name  \\\n",
       "0               dev15-ring-door-camera/ring-attacks.pickel   \n",
       "1               dev15-ring-door-camera/ring-attacks.pickel   \n",
       "2               dev15-ring-door-camera/ring-attacks.pickel   \n",
       "3               dev15-ring-door-camera/ring-attacks.pickel   \n",
       "4               dev15-ring-door-camera/ring-attacks.pickel   \n",
       "...                                                    ...   \n",
       "1002184  dev05-tplink-tapo-l530e-birne/lampe-1h-attacks...   \n",
       "1002185       dev08-echo-dot-l4s3re/echo-dot-attack.pickel   \n",
       "1002186       dev08-echo-dot-l4s3re/echo-dot-attack.pickel   \n",
       "1002187       dev08-echo-dot-l4s3re/echo-dot-attack.pickel   \n",
       "1002188       dev08-echo-dot-l4s3re/echo-dot-attack.pickel   \n",
       "\n",
       "                     timedelta         timedelta_prev  \n",
       "0              0 days 00:00:00                    NaT  \n",
       "1       0 days 00:00:00.191411 0 days 00:00:00.191411  \n",
       "2       0 days 00:00:00.191448 0 days 00:00:00.000037  \n",
       "3       0 days 00:00:00.191624 0 days 00:00:00.000176  \n",
       "4       0 days 00:00:00.192098 0 days 00:00:00.000474  \n",
       "...                        ...                    ...  \n",
       "1002184 8 days 03:36:25.961266 0 days 00:00:00.000589  \n",
       "1002185                    NaT                    NaT  \n",
       "1002186                    NaT                    NaT  \n",
       "1002187                    NaT                    NaT  \n",
       "1002188                    NaT                    NaT  \n",
       "\n",
       "[1002189 rows x 101 columns]"
      ],
      "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/frame.offset_shift</th>\n",
       "      <th>geninfo/timestamp</th>\n",
       "      <th>tcp/tcp.flags/tcp.flags.cwr</th>\n",
       "      <th>tcp/tcp.len</th>\n",
       "      <th>tcp/tcp.options/tcp.options.mss/tcp.options.mss_val</th>\n",
       "      <th>ip/ip.len</th>\n",
       "      <th>arp/arp.proto.type</th>\n",
       "      <th>tcp/tcp.stream</th>\n",
       "      <th>eth/eth.dst/eth.dst.oui</th>\n",
       "      <th>ip/ip.frag_offset</th>\n",
       "      <th>...</th>\n",
       "      <th>tcp/tcp.flags/tcp.flags.ns</th>\n",
       "      <th>icmp/icmp.udp/icmp.udp.length</th>\n",
       "      <th>ip/&lt;&gt;/ip.options.routeralert/ip.opt.sec_cl</th>\n",
       "      <th>icmp/icmp.ident</th>\n",
       "      <th>igmp/igmp.max_resp</th>\n",
       "      <th>frame/frame.ignore</th>\n",
       "      <th>Attack</th>\n",
       "      <th>name</th>\n",
       "      <th>timedelta</th>\n",
       "      <th>timedelta_prev</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.000000000</td>\n",
       "      <td>2023-11-21 11:25:39.957914</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1460</td>\n",
       "      <td>60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>9455724</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>dev15-ring-door-camera/ring-attacks.pickel</td>\n",
       "      <td>0 days 00:00:00</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.000000000</td>\n",
       "      <td>2023-11-21 11:25:40.149325</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0x0800</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16777215</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>1</td>\n",
       "      <td>dev15-ring-door-camera/ring-attacks.pickel</td>\n",
       "      <td>0 days 00:00:00.191411</td>\n",
       "      <td>0 days 00:00:00.191411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.000000000</td>\n",
       "      <td>2023-11-21 11:25:40.149362</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0x0800</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16777215</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>0</td>\n",
       "      <td>dev15-ring-door-camera/ring-attacks.pickel</td>\n",
       "      <td>0 days 00:00:00.191448</td>\n",
       "      <td>0 days 00:00:00.000037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.000000000</td>\n",
       "      <td>2023-11-21 11:25:40.149538</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0x0800</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9455724</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>0</td>\n",
       "      <td>dev15-ring-door-camera/ring-attacks.pickel</td>\n",
       "      <td>0 days 00:00:00.191624</td>\n",
       "      <td>0 days 00:00:00.000176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.000000000</td>\n",
       "      <td>2023-11-21 11:25:40.150012</td>\n",
       "      <td>0</td>\n",
       "      <td>68</td>\n",
       "      <td>NaN</td>\n",
       "      <td>108</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>5538618</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>dev15-ring-door-camera/ring-attacks.pickel</td>\n",
       "      <td>0 days 00:00:00.192098</td>\n",
       "      <td>0 days 00:00:00.000474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1002184</th>\n",
       "      <td>0.000000000</td>\n",
       "      <td>2023-11-29 15:02:05.919180</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1348</td>\n",
       "      <td>315211</td>\n",
       "      <td>0</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>2</td>\n",
       "      <td>dev05-tplink-tapo-l530e-birne/lampe-1h-attacks...</td>\n",
       "      <td>8 days 03:36:25.961266</td>\n",
       "      <td>0 days 00:00:00.000589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1002185</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</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>...</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>2</td>\n",
       "      <td>dev08-echo-dot-l4s3re/echo-dot-attack.pickel</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1002186</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</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>...</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>2</td>\n",
       "      <td>dev08-echo-dot-l4s3re/echo-dot-attack.pickel</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1002187</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</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>...</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>1</td>\n",
       "      <td>dev08-echo-dot-l4s3re/echo-dot-attack.pickel</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1002188</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</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>...</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>3</td>\n",
       "      <td>dev08-echo-dot-l4s3re/echo-dot-attack.pickel</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1002189 rows × 101 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 7
  },
  {
   "cell_type": "code",
   "source": [
    "leave_as_is = [\n",
    "    'udp/udp.dstport',\n",
    "    'udp/udp.srcport',\n",
    "    'geninfo/timestamp',\n",
    "    'ip/ip.src',\n",
    "    'name',\n",
    "    'timedelta',\n",
    "    'timedelta_prev',\n",
    "    'geninfo/timestamp_human',\n",
    "    'eth/eth.src',\n",
    "    'ip/ip.dst',\n",
    "    'eth/eth.dst',\n",
    "    'igmp/igmp.maddr',\n",
    "    'presence_absence',\n",
    "    'illuminance_level',\n",
    "]\n",
    "no_values = [\n",
    "    \"classicstun.type\",\n",
    "    \"classicstun.length\",\n",
    "    \"ip/<>/ip.options.routeralert/ip.opt.sec_cl\",\n",
    "    \"frame/frame.ignore\",\n",
    "    \"_ws.malformed/_ws.expert/_ws.malformed.expert\",\n",
    "    \"icmp/icmp.udp/icmp.udp.srcport\",\n",
    "    \"icmp/icmp.udp/icmp.udp.length\",\n",
    "    \"frame/frame.packet_flags\",\n",
    "    \"ip/ip.evil_packet\",\n",
    "    \"icmp/icmp.udp/icmp.udp.dstport\"\n",
    "\n",
    "]\n",
    "hexa = [\n",
    "    'ip/ip.flags',\n",
    "    'ip/ip.dsfield',\n",
    "    'eth/eth.type',\n",
    "    'ip/ip.id',\n",
    "    'arp/arp.proto.type',\n",
    "    'igmp/igmp.type',\n",
    "]\n",
    "ints = [\n",
    "    'arp/arp.hw.size',\n",
    "    'udp/udp.length',\n",
    "    'tcp/tcp.hdr_len',\n",
    "    'tcp/tcp.nxtseq',\n",
    "    'tcp/tcp.analysis/tcp.analysis.push_bytes_sent',\n",
    "    'ip/ip.len',\n",
    "    'ip/ip.ttl',\n",
    "    'arp/arp.proto.size',\n",
    "    'ip/ip.hdr_len',\n",
    "    'frame/frame.number',\n",
    "    'tcp/tcp.stream',\n",
    "    'ip/ip.version',\n",
    "    'eth/eth.dst/eth.dst.oui',\n",
    "    'arp/arp.opcode',\n",
    "    'ip/ip.checksum.status',\n",
    "    'eth/eth.src/eth.src.oui',\n",
    "    'tcp/tcp.window_size_scalefactor',\n",
    "    'tcp/tcp.ack',\n",
    "    'tcp/tcp.srcport',\n",
    "    'frame/frame.len',\n",
    "    'tcp/tcp.window_size_value',\n",
    "    'tcp/tcp.checksum.status',\n",
    "    'arp/arp.hw.type',\n",
    "    'udp/udp.stream',\n",
    "    'frame/frame.encap_type',\n",
    "    'ip/ip.dsfield/ip.dsfield.dscp',\n",
    "    'tcp/tcp.completeness',\n",
    "    'tcp/tcp.seq',\n",
    "    'ip/ip.frag_offset',\n",
    "    'tcp/tcp.dstport',\n",
    "    'udp/udp.checksum.status',\n",
    "    'tcp/tcp.len',\n",
    "    'tcp/tcp.window_size',\n",
    "    'ip/ip.proto',\n",
    "    'tcp/tcp.analysis/tcp.analysis.bytes_in_flight',\n",
    "    'tcp/tcp.flags/tcp.flags.ecn',\n",
    "    'icmp/icmp.seq',\n",
    "    'icmp/icmp.ident',\n",
    "    'igmp/igmp.max_resp',\n",
    "    'icmp/icmp.checksum.status',\n",
    "    'tcp/tcp.options/tcp.options.nop',\n",
    "    'icmp/icmp.resp_in',\n",
    "    'icmp/data/data.len',\n",
    "    'igmp/igmp.checksum.status',\n",
    "    'icmp/icmp.code',\n",
    "    'icmp/icmp.type',\n",
    "    'tcp/tcp.flags/tcp.flags.ns',\n",
    "    'tcp/tcp.options/tcp.options.mss/tcp.options.mss_val',\n",
    "    'icmp/icmp.resp_to',\n",
    "    'ip/<>/ip.options.routeralert/ip.opt.ra',\n",
    "    'icmp/icmp.seq_le',\n",
    "    'ip/<>/ip.options.routeralert/ip.opt.type',\n",
    "]\n",
    "floats = [\n",
    "    'frame/frame.time_delta',\n",
    "    'frame/frame.time_relative',\n",
    "]\n",
    "bools = [\n",
    "    'Activity',\n",
    "    'Attack',\n",
    "    'Farbeändern',\n",
    "    'Helligkeitssteuern',\n",
    "    'Weiß/gelb',\n",
    "    'Themen Auswahl',\n",
    "    'Party',\n",
    "    'Zeitschalter',\n",
    "    'Timer Ein/Aus',\n",
    "    'tcp/tcp.flags/tcp.flags.fin',\n",
    "    'tcp/tcp.flags/tcp.flags.cwr',\n",
    "    'ip/ip.dsfield/ip.dsfield.ecn',\n",
    "    'tcp/tcp.flags/tcp.flags.push',\n",
    "    'tcp/tcp.urgent_pointer',\n",
    "    'frame/frame.offset_shift',\n",
    "    'tcp/tcp.flags/tcp.flags.urg',\n",
    "    'tcp/tcp.flags/tcp.flags.syn',\n",
    "    'ip/ip.flags/ip.flags.mf',\n",
    "    'eth/eth.src/eth.lg',\n",
    "    'tcp/tcp.flags/tcp.flags.reset',\n",
    "    'frame/frame.marked',\n",
    "    'eth/eth.dst/eth.lg',\n",
    "    'tcp/tcp.flags/tcp.flags.ack',\n",
    "    'tcp/tcp.flags/tcp.flags.res',\n",
    "    'ip/ip.flags/ip.flags.df',\n",
    "    'eth/eth.dst/eth.dst.ig',\n",
    "    'eth/eth.src/eth.ig',\n",
    "    'ip/ip.flags/ip.flags.rb',\n",
    "]\n",
    "all = leave_as_is + hexa + ints + floats + bools + no_values\n",
    "left = [column for column in merged.columns if column not in all]\n",
    "print(left)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-21T17:38:44.795171Z",
     "start_time": "2024-02-21T17:38:44.788307Z"
    }
   },
   "id": "3d9389f43beb4eae",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "execution_count": 8
  },
  {
   "cell_type": "code",
   "source": [
    "def convert_column(column: pd.Series, dtype: str):\n",
    "    if dtype == 'object':\n",
    "        return column.astype('object')\n",
    "    elif dtype == 'int':\n",
    "        try:\n",
    "            return column.astype('int')\n",
    "        except:\n",
    "            # use float as fallback\n",
    "            return column.astype('float')\n",
    "    elif dtype == 'float':\n",
    "        return column.astype('float')\n",
    "    elif dtype == 'bool':\n",
    "        # make sure to parse ints as bools, and False and True strings as well\n",
    "        return column.apply(lambda x: x in [1, True, 'True', 'true', 'TRUE', '1']).astype('bool')\n",
    "    else:\n",
    "        raise ValueError(f\"Unknown dtype {dtype}\")\n",
    "\n",
    "\n",
    "# convert all columns to the correct type\n",
    "for column in merged.columns:\n",
    "    try:\n",
    "        if column in leave_as_is:\n",
    "            continue\n",
    "        elif column in no_values:\n",
    "            # drop columns with no values\n",
    "            merged = merged.drop(columns=[column])\n",
    "        elif column in hexa:\n",
    "            merged[column] = convert_column(merged[column], 'object')\n",
    "        elif column in ints:\n",
    "            merged[column] = convert_column(merged[column], 'int')\n",
    "        elif column in floats:\n",
    "            merged[column] = convert_column(merged[column], 'float')\n",
    "        elif column in bools:\n",
    "            merged[column] = convert_column(merged[column], 'bool')\n",
    "        else:\n",
    "            raise ValueError(f\"Unknown column {column}\")\n",
    "    except Exception as e:\n",
    "        print(f\"Error converting column {column}: {e}\")\n",
    "        raise e"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-21T17:38:56.192216Z",
     "start_time": "2024-02-21T17:38:44.796165Z"
    }
   },
   "id": "cb48d03c35f91c84",
   "outputs": [],
   "execution_count": 9
  },
  {
   "cell_type": "code",
   "source": [
    "# show dtypes for all columns\n",
    "for column in merged.columns:\n",
    "    print(f\"{column}: {merged[column].dtype}\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-21T17:38:56.200751Z",
     "start_time": "2024-02-21T17:38:56.193950Z"
    }
   },
   "id": "ddf61825d47e9c37",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "frame/frame.offset_shift: bool\n",
      "geninfo/timestamp: datetime64[ns]\n",
      "tcp/tcp.flags/tcp.flags.cwr: bool\n",
      "tcp/tcp.len: float64\n",
      "tcp/tcp.options/tcp.options.mss/tcp.options.mss_val: float64\n",
      "ip/ip.len: float64\n",
      "arp/arp.proto.type: object\n",
      "tcp/tcp.stream: float64\n",
      "eth/eth.dst/eth.dst.oui: float64\n",
      "ip/ip.frag_offset: float64\n",
      "eth/eth.src/eth.src.oui: float64\n",
      "tcp/tcp.hdr_len: float64\n",
      "ip/ip.version: float64\n",
      "arp/arp.proto.size: float64\n",
      "frame/frame.number: float64\n",
      "eth/eth.dst/eth.dst.ig: bool\n",
      "tcp/tcp.window_size: float64\n",
      "tcp/tcp.flags/tcp.flags.fin: bool\n",
      "tcp/tcp.flags/tcp.flags.push: bool\n",
      "eth/eth.src/eth.lg: bool\n",
      "tcp/tcp.window_size_scalefactor: float64\n",
      "tcp/tcp.seq: float64\n",
      "udp/udp.length: float64\n",
      "eth/eth.src/eth.ig: bool\n",
      "frame/frame.time_delta: float64\n",
      "tcp/tcp.flags/tcp.flags.syn: bool\n",
      "eth/eth.type: object\n",
      "udp/udp.dstport: object\n",
      "ip/ip.dsfield/ip.dsfield.ecn: bool\n",
      "tcp/tcp.flags/tcp.flags.reset: bool\n",
      "tcp/tcp.flags/tcp.flags.res: bool\n",
      "ip/ip.dsfield: object\n",
      "udp/udp.checksum.status: float64\n",
      "frame/frame.marked: bool\n",
      "tcp/tcp.window_size_value: float64\n",
      "arp/arp.opcode: float64\n",
      "arp/arp.hw.type: float64\n",
      "ip/ip.flags/ip.flags.df: bool\n",
      "arp/arp.hw.size: float64\n",
      "tcp/tcp.srcport: float64\n",
      "tcp/tcp.analysis/tcp.analysis.push_bytes_sent: float64\n",
      "eth/eth.dst: object\n",
      "frame/frame.len: float64\n",
      "tcp/tcp.nxtseq: float64\n",
      "udp/udp.srcport: object\n",
      "ip/ip.flags/ip.flags.mf: bool\n",
      "tcp/tcp.ack: float64\n",
      "frame/frame.encap_type: float64\n",
      "ip/ip.checksum.status: float64\n",
      "frame/frame.time_relative: float64\n",
      "ip/ip.proto: float64\n",
      "tcp/tcp.urgent_pointer: bool\n",
      "tcp/tcp.checksum.status: float64\n",
      "tcp/tcp.flags/tcp.flags.ack: bool\n",
      "tcp/tcp.completeness: float64\n",
      "ip/ip.hdr_len: float64\n",
      "eth/eth.dst/eth.lg: bool\n",
      "ip/ip.flags: object\n",
      "tcp/tcp.analysis/tcp.analysis.bytes_in_flight: float64\n",
      "ip/ip.dsfield/ip.dsfield.dscp: float64\n",
      "ip/ip.src: object\n",
      "ip/ip.ttl: float64\n",
      "tcp/tcp.dstport: float64\n",
      "eth/eth.src: object\n",
      "ip/ip.dst: object\n",
      "tcp/tcp.flags/tcp.flags.urg: bool\n",
      "ip/ip.flags/ip.flags.rb: bool\n",
      "udp/udp.stream: float64\n",
      "ip/ip.id: object\n",
      "icmp/icmp.seq_le: float64\n",
      "icmp/icmp.seq: float64\n",
      "icmp/icmp.code: float64\n",
      "ip/<>/ip.options.routeralert/ip.opt.type: float64\n",
      "igmp/igmp.type: object\n",
      "igmp/igmp.checksum.status: float64\n",
      "tcp/tcp.flags/tcp.flags.ecn: float64\n",
      "igmp/igmp.maddr: object\n",
      "icmp/icmp.type: float64\n",
      "icmp/data/data.len: float64\n",
      "ip/<>/ip.options.routeralert/ip.opt.ra: float64\n",
      "icmp/icmp.resp_in: float64\n",
      "icmp/icmp.resp_to: float64\n",
      "icmp/icmp.checksum.status: float64\n",
      "tcp/tcp.options/tcp.options.nop: float64\n",
      "tcp/tcp.flags/tcp.flags.ns: float64\n",
      "icmp/icmp.ident: float64\n",
      "igmp/igmp.max_resp: float64\n",
      "Attack: bool\n",
      "name: object\n",
      "timedelta: timedelta64[ns]\n",
      "timedelta_prev: timedelta64[ns]\n"
     ]
    }
   ],
   "execution_count": 10
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-21T17:38:56.257766Z",
     "start_time": "2024-02-21T17:38:56.201836Z"
    }
   },
   "cell_type": "code",
   "source": "len(merged)",
   "id": "f6b3a4dacc0501ea",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1002189"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 11
  },
  {
   "cell_type": "code",
   "source": [
    "# save the merged dataset\n",
    "merged.to_pickle(os.path.join(devices_root, output_filename))"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-02-21T17:36:50.704067Z",
     "start_time": "2024-02-21T17:36:48.250403Z"
    }
   },
   "id": "b5deb12fa10c0113",
   "outputs": [],
   "execution_count": 13
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
