Malware_DNS_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6cb398cc-5f08-4f8a-a718-0a9e15aeed8a",
   "metadata": {},
   "source": [
    "# Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d143f57c-e355-430a-937a-24bf9abed346",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import plotly.express as px\n",
    "from plotly.subplots import make_subplots\n",
    "\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import monotonically_increasing_id, col, count, when, lit\n",
    "from pyspark.sql import functions as F\n",
    "\n",
    "import plotly.express as px\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "import requests\n",
    "# import ast\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1533d70f-3e05-4076-8c8b-658a4a35a1a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = SparkSession.builder.appName('Malware').getOrCreate()\n",
    "spark.sparkContext.setLogLevel(\"ERROR\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e2ef9a5-4234-47b9-97ad-e3d352807670",
   "metadata": {},
   "source": [
    "# **Loading datasets**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21deeeb4-13a8-4272-b907-d8c0d945a168",
   "metadata": {},
   "source": [
    "## Malware DNS dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f264b8a-74fa-464a-8d01-f9b32a756691",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1 = spark.read.json('2018.log')\n",
    "df_2 = spark.read.json('2019.log')\n",
    "df_3 = spark.read.json('2020.log')\n",
    "df_4 = spark.read.json('2021.log')\n",
    "df_5 = spark.read.json('2022.log')\n",
    "df_6 = spark.read.json('2023.log')\n",
    "df_7 = spark.read.json('2024.log')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8d7b1e8-2ce9-4520-8984-3c9cb1bb241b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1 = df_1['icann_tld', 'icann_domain', 'query', 'length', 'qtype_name', 'rcode_name', 'Z', 'rtt', 'answers', 'TTLs', 'entropy']\n",
    "df_2 = df_2['icann_tld', 'icann_domain', 'query', 'length', 'qtype_name', 'rcode_name', 'Z', 'rtt', 'answers', 'TTLs', 'entropy']\n",
    "df_3 = df_3['icann_tld', 'icann_domain', 'query', 'length', 'qtype_name', 'rcode_name', 'Z', 'rtt', 'answers', 'TTLs', 'entropy']\n",
    "df_4 = df_4['icann_tld', 'icann_domain', 'query', 'length', 'qtype_name', 'rcode_name', 'Z', 'rtt', 'answers', 'TTLs', 'entropy']\n",
    "df_5 = df_5['icann_tld', 'icann_domain', 'query', 'length', 'qtype_name', 'rcode_name', 'Z', 'rtt', 'answers', 'TTLs', 'entropy']\n",
    "df_6 = df_6['icann_tld', 'icann_domain', 'query', 'length', 'qtype_name', 'rcode_name', 'Z', 'rtt', 'answers', 'TTLs', 'entropy']\n",
    "df_7 = df_7['icann_tld', 'icann_domain', 'query', 'length', 'qtype_name', 'rcode_name', 'Z', 'rtt', 'answers', 'TTLs', 'entropy']\n",
    "df_1.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3172a72a-a7dd-4862-86f8-e972d5e61dd5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1 = df_1.withColumn(\"year\", lit(2018))\n",
    "df_2 = df_2.withColumn(\"year\", lit(2019))\n",
    "df_3 = df_3.withColumn(\"year\", lit(2020))\n",
    "df_4 = df_4.withColumn(\"year\", lit(2021))\n",
    "df_5 = df_5.withColumn(\"year\", lit(2022))\n",
    "df_6 = df_6.withColumn(\"year\", lit(2023))\n",
    "df_7 = df_7.withColumn(\"year\", lit(2024))\n",
    "\n",
    "df_1.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23fd5513-65b5-4aa2-a60c-e67d2dde9d12",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_m = df_1.union(df_2).union(df_3).union(df_4).union(df_5).union(df_6).union(df_7)\n",
    "df_m.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44d559f4-1adc-4d46-8f84-f09f3d159137",
   "metadata": {},
   "outputs": [],
   "source": [
    "# add id column\n",
    "df_m = df_m.withColumn(\"id\", monotonically_increasing_id())\n",
    "df_m = df_m.withColumn(\"id\", df_m[\"id\"] + 1)\n",
    "\n",
    "# add malware columns\n",
    "df_m = df_m.withColumn(\"malware\", lit(1))\n",
    "\n",
    "df_m.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66e6541d-28cc-4230-a608-7797db5a66c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_m = df_m['year', 'id', 'icann_tld', 'icann_domain', 'query', 'length', 'qtype_name', 'rcode_name', 'Z', 'rtt', 'answers', 'TTLs', 'entropy', 'malware']\n",
    "df_m.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d36612a0-a54a-4e51-a95b-0843de4e2677",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\" 2018 count is: {df_1.count()} \\n 2019 count is: {df_2.count()} \\n 2020 count is: {df_3.count()} \\n 2021 count is: {df_4.count()} \\n 2022 count is: {df_5.count()} \\n 2023 count is: {df_6.count()} \\n 2024 count is: {df_7.count()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "587d76d9-8374-4f19-b57e-6c3e02a4fc1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"malware df count is {df_m.count()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e9e7134-8d50-4a8b-95ed-5532cfadd6b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "tld_count_m = df_m.groupBy('icann_tld').count().orderBy(F.col('count').desc()).limit(10)\n",
    "fig1 = px.pie(tld_count_m, names=\"icann_tld\", values='count', title=\"Distribution of icann_tld (malware=0)\")\n",
    "fig1.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "329d8d3b-dc12-48cc-b193-d05d2c1c3984",
   "metadata": {},
   "source": [
    "## Normal DNS dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5845cb93-511b-4585-8e29-6c7bb9ce9eb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_n = spark.read.json('top-1m-dns.log')\n",
    "\n",
    "df_n = df_n[df_n[\"`id.resp_h`\"] == \"8.8.8.8\"]\n",
    "df_n = df_n['icann_tld', 'icann_domain', 'query', 'length', 'qtype_name', 'rcode_name', 'Z', 'rtt', 'answers', 'TTLs', 'entropy']\n",
    "\n",
    "df_n.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03e91cd8-e3ed-4cdb-9508-cbda8315291a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_n = df_n.withColumn(\"year\", lit(0))\n",
    "df_n = df_n.withColumn(\"id\", lit(0))\n",
    "df_n = df_n.withColumn(\"malware\", lit(0))\n",
    "\n",
    "df_n = df_n['year', 'id', 'icann_tld', 'icann_domain', 'query', 'length', 'qtype_name', 'rcode_name', 'Z', 'rtt', 'answers', 'TTLs', 'entropy', 'malware']\n",
    "\n",
    "df_n.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fc85054-4129-4b34-bbb8-7db6b40a52fb",
   "metadata": {},
   "source": [
    "# **data cleaning**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71e67e82-fa2f-41f6-a9e6-cbf466a12fb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_n.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c489902-2adf-4ee6-ad67-0cbacb5b2517",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_m.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6740bdd0-f640-4060-9c23-37b09f573b44",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_m = df_m.join(df_n, df_m['icann_domain'] == df_n['icann_domain'], how='left_anti')\n",
    "df_m = df_m.join(df_n, df_m['query'] == df_n['query'], how='left_anti')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2389bdba-cdad-4783-bf07-ddae3420dbc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = df_n.union(df_m)\n",
    "df_final.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0262ae2-8c11-4a55-93f7-7e9481b42334",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = df_n.union(df_m)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2da7ca33-8c40-4eba-a1ae-8a0385480e13",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90ab8fa4-e6df-4bd6-914d-15ca5eef7b8b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_final = df_final.toPandas()\n",
    "# df_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "422ddf98-4138-4343-b2d2-93fcd0c2d46c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.select(count(when(col(\"icann_domain\").isNull(), 1)).alias(\"null_count_in_name\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "422196f0-8dd6-4b80-8232-6f21fa7e989d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.select(count(when(col(\"query\").isNull(), 1)).alias(\"null_count_in_name\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6dd584b-4201-4505-a12d-12ff36a6c164",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove unknown tld\n",
    "df_final = df_final.dropna(subset=\"icann_domain\")\n",
    "df_final.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c74bf3ae-ea31-4ab9-b90f-2aa7ab41297a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = df_final.filter(df_final['icann_tld'] != \"\")\n",
    "df_final.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3bb8923b-99bb-4f5b-9143-8ab01593b35b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_m = df_final.filter(df_final['malware'] == 1)\n",
    "# remove unknown tld\n",
    "df_m = df_m.filter(df_m['icann_tld'] != \"\")\n",
    "\n",
    "df_n = df_final.filter(df_final['malware'] != 1)\n",
    "\n",
    "print(f\" malware count: {df_m.count()} \\n normal count: {df_n.count()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06ff8bfe-fb20-4cfe-ae19-f4b53d52c395",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.groupBy(F.col('rcode_name')).count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b3c2c69-13fe-4b7d-9ef0-0c63cbf0530a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.sample(fraction=0.2, seed=42).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44645fd2-5f45-476d-9990-6d4db28d2a60",
   "metadata": {},
   "source": [
    "# **Analysis**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c95b1a20-d9f6-4c4a-a2af-67c60e2c3b09",
   "metadata": {},
   "source": [
    "## *TTL*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f45a3e3-3893-4b85-bc5b-89ccc8791a51",
   "metadata": {},
   "source": [
    "### preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48b84c05-fb88-47b1-a137-175947e17ed5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ttl = df_final['icann_tld', 'icann_domain', 'TTLs', 'malware']\n",
    "df_ttl.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05680930-8184-48d0-bd5e-542af9f16980",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"count of TTL bedfor drop nulls: {df_ttl.count()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e8a7fa4-ab7f-48d9-87c2-c9ae1145b290",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"number of null values in normals:   {df_ttl.filter(df_ttl['TTLs'].isNull() & (df_ttl['malware'] == 0)).count()}\")\n",
    "print(f\"number of null values in malwares:  {df_ttl.filter(df_ttl['TTLs'].isNull() & (df_ttl['malware'] == 1)).count()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a53f3926-4a6c-47fb-b02a-d4bac8ee6a6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop nulls\n",
    "df_ttl = df_ttl.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52dda513-3651-439f-ae80-2d326622bc44",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"count of TTL after drop nulls: {df_ttl.count()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e533298-db06-445f-8fab-e9f055983a55",
   "metadata": {},
   "source": [
    "### unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42a8656f-7648-4ae5-b58d-af387217272f",
   "metadata": {},
   "outputs": [],
   "source": [
    "exploded_df = df_ttl.withColumn(\"TTL\", F.explode(\"TTLs\"))\n",
    "\n",
    "df_ttl = exploded_df.groupBy('icann_tld', 'icann_domain', 'malware') \\\n",
    "                       .agg(F.sort_array(F.collect_list('TTL')).alias('sorted_TTLs'))\n",
    "\n",
    "df_ttl.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3588961d-1dc5-45c3-b874-aa51671bf0fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ttl.groupBy('icann_domain').count().orderBy(F.col('count').desc()).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e4db87b-b39b-4038-b0a2-ed98fd025cbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"number of normal values:  {df_ttl.filter(df_ttl['malware'] != 1).count()}\")\n",
    "print(f\"number of malware values: {df_ttl.filter(df_ttl['malware'] == 1).count()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b06c3d0-70c8-4783-a90a-975fe237fa0b",
   "metadata": {},
   "source": [
    "### new ttl dataframe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81876d91-7c9c-4988-92cb-e81f1d66f0fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ttl_result = df_ttl.withColumn(\n",
    "    \"min\", F.expr(\"array_min(sorted_TTLs)\")  # Get the minimum TTL\n",
    ").withColumn(\n",
    "    \"max\", F.expr(\"array_max(sorted_TTLs)\")  # Get the maximum TTL\n",
    ").withColumn(\n",
    "    \"mean\", F.expr(\"aggregate(sorted_TTLs, 0D, (acc, x) -> acc + x) / size(sorted_TTLs)\")  # Get the mean of TTLs\n",
    ")\n",
    "\n",
    "# Select the desired columns\n",
    "df_ttl_result = df_ttl_result.select('icann_tld', 'icann_domain', 'sorted_TTLs', 'min', 'max', 'mean', 'malware')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47036fdf-768c-4c4d-a17b-34ae62254e68",
   "metadata": {},
   "outputs": [],
   "source": [
    "# show random rows\n",
    "df_ttl_result.sample(fraction=0.2, seed=42).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7203b1b2-797f-4676-bda7-41b80a0fa40e",
   "metadata": {},
   "source": [
    "### TTL eda"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "82cc41ed-1217-4c26-8538-b3cab75b1f9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ttl_pd = df_ttl_result.toPandas()\n",
    "\n",
    "# Add a \"Group\" column to distinguish malware categories\n",
    "df_ttl_pd['Group'] = df_ttl_pd['malware'].apply(lambda x: 'Malware' if x == 1 else 'Normal')\n",
    "\n",
    "# Create a boxplot with Plotly Express\n",
    "fig = px.box(\n",
    "    df_ttl_pd,\n",
    "    x='Group',\n",
    "    y='mean',\n",
    "    title='Distribution of Mean Values by Malware Status',\n",
    "    labels={'mean': 'Mean Value', 'Group': 'Malware Status'}\n",
    ")\n",
    "\n",
    "# Show the plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06eaaab6-74ec-44c3-b79c-a821fef1d4bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ttl_filtered = df_ttl_result.filter(df_ttl_result['mean'] < 30000)\n",
    "\n",
    "# Convert the filtered PySpark DataFrame to Pandas\n",
    "df_ttl_filtered_pd = df_ttl_filtered.toPandas()\n",
    "\n",
    "# Add a \"Group\" column to distinguish malware categories\n",
    "df_ttl_filtered_pd['Group'] = df_ttl_filtered_pd['malware'].apply(lambda x: 'Malware' if x == 1 else 'Normal')\n",
    "\n",
    "# Create a boxplot with Plotly Express\n",
    "fig = px.box(\n",
    "    df_ttl_filtered_pd,\n",
    "    x='Group',\n",
    "    y='mean',\n",
    "    title='Distribution of Mean Values by Malware Status (Under 30k)',\n",
    "    labels={'mean': 'Mean Value', 'Group': 'Malware Status'}\n",
    ")\n",
    "\n",
    "# Show the plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4be26a67-cd97-46f8-b42b-af57add67068",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_top_9 = df_ttl.groupBy(\"icann_tld\").count().orderBy(F.col(\"count\").desc()).limit(9)\n",
    "\n",
    "# Join back with cleaned data to filter by malware\n",
    "df_limited = df_ttl.join(df_top_9.select(\"icann_tld\"), on=\"icann_tld\", how=\"inner\")\n",
    "\n",
    "# Count occurrences for malware == 0 within top 9\n",
    "df_malware_0 = df_limited.filter(df_limited['malware'] == 0).groupBy(\"icann_tld\").count().toPandas()\n",
    "\n",
    "# Count occurrences for malware == 1 within top 9\n",
    "df_malware_1 = df_limited.filter(df_limited['malware'] == 1).groupBy(\"icann_tld\").count().toPandas()\n",
    "\n",
    "# Create a subplot figure\n",
    "fig = make_subplots(rows=1, cols=2, subplot_titles=(\"Normal\", \"Malware\"), specs=[[{\"type\": \"pie\"}, {\"type\": \"pie\"}]])\n",
    "\n",
    "# Add pie chart for malware == 0\n",
    "fig.add_trace(go.Pie(labels=df_malware_0[\"icann_tld\"], values=df_malware_0[\"count\"], name=\"Normal\"), row=1, col=1)\n",
    "\n",
    "# Add pie chart for malware == 1\n",
    "fig.add_trace(go.Pie(labels=df_malware_1[\"icann_tld\"], values=df_malware_1[\"count\"], name=\"Malware\"), row=1, col=2)\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title_text=\"Top 9 icann_tld Distribution by Malware Status\", showlegend=True)\n",
    "\n",
    "# Show plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fc872ae-849d-434d-a99b-e91ee2ab6920",
   "metadata": {},
   "source": [
    "## *RTT*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8cb82b6e-5a4e-4fe4-a150-759c15396914",
   "metadata": {},
   "source": [
    "### preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c84bd38-63e6-456e-82bf-ef43ce6c1d45",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_rtt = df_final['icann_tld', 'icann_domain', 'rtt', 'malware']\n",
    "df_rtt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70225840-dd23-451c-a6cb-18794d5db277",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"count of TTL after drop nulls: {df_rtt.count()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd9e393a-f61c-4d65-8442-10036d29e6c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"number of null values in normals:   {df_rtt.filter(df_rtt['rtt'].isNull() & (df_rtt['malware'] == 0)).count()}\")\n",
    "print(f\"number of null values in malwares:  {df_rtt.filter(df_rtt['rtt'].isNull() & (df_rtt['malware'] == 1)).count()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ba9ec60-91cf-4452-b710-e0262858973b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop nulls\n",
    "df_rtt = df_rtt.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a75e0d7e-04d6-4745-b3f0-4dc978231abd",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"count of TTL after drop nulls: {df_rtt.count()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "80543884-db07-4f3b-990e-1275dd204104",
   "metadata": {},
   "source": [
    "### unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd54953d-afd6-4bca-852c-d33c6b80e270",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_rtt = df_rtt.groupBy('icann_domain', 'icann_tld', 'malware') \\\n",
    "                      .agg(F.sort_array(F.collect_list('rtt')).alias('sorted_rtt'))\n",
    "\n",
    "# Show the result\n",
    "df_rtt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae88d63b-bc8d-4cb1-b66f-2417d856a7dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_rtt.groupBy('icann_domain').count().orderBy(F.col('count').desc()).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1731c2a-f6da-4037-8523-3eeeba947bab",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"number of normal values:  {df_rtt.filter(df_rtt['malware'] != 1).count()}\")\n",
    "print(f\"number of malware values: {df_rtt.filter(df_rtt['malware'] == 1).count()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55b44f8f-d168-4f43-8ac4-2f6dd21e0218",
   "metadata": {},
   "source": [
    "### new rtt dataframe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8eca6f7-b430-4cb9-bcb6-b4d5089cff56",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_rtt_result = df_rtt.withColumn(\n",
    "    \"min\", F.expr(\"array_min(sorted_rtt)\")  # Get the minimum TTL\n",
    ").withColumn(\n",
    "    \"max\", F.expr(\"array_max(sorted_rtt)\")  # Get the maximum TTL\n",
    ").withColumn(\n",
    "    \"mean\", F.expr(\"aggregate(sorted_rtt, 0D, (acc, x) -> acc + x) / size(sorted_rtt)\")  # Get the mean of TTLs\n",
    ")\n",
    "\n",
    "# Select the desired columns\n",
    "df_rtt_result = df_rtt_result.select('icann_tld', 'icann_domain', 'sorted_rtt', 'min', 'max', 'mean', 'malware')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f566b63-f376-48ef-9ceb-74ba35ea0b49",
   "metadata": {},
   "outputs": [],
   "source": [
    "# show random rows\n",
    "df_rtt_result.sample(fraction=0.2, seed=42).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c81af23-4ffb-4304-b522-5268b75d1ba1",
   "metadata": {},
   "source": [
    "### RTT eda"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "670d2ade-d64d-4532-bec3-d5fa8d788612",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_rtt_pd = df_rtt_result.toPandas()\n",
    "\n",
    "# Add a \"Group\" column to distinguish malware categories\n",
    "df_rtt_pd['Group'] = df_rtt_pd['malware'].apply(lambda x: 'Malware' if x == 1 else 'Normal')\n",
    "\n",
    "# Create a boxplot with Plotly Express\n",
    "fig = px.box(\n",
    "    df_rtt_pd,\n",
    "    x='Group',\n",
    "    y='mean',\n",
    "    title='RTT values',\n",
    "    labels={'mean': 'Mean Value', 'Group': 'Malware Status'}\n",
    ")\n",
    "\n",
    "# Show the plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5d677df-c4c5-4a5c-b456-06783067d5ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_rtt_filtered = df_rtt_result.filter(df_rtt_result['mean'] < 0.75)\n",
    "\n",
    "# Convert the filtered PySpark DataFrame to Pandas\n",
    "df_rtt_filterd_pd = df_rtt_filtered.toPandas()\n",
    "\n",
    "# Add a \"Group\" column to distinguish malware categories\n",
    "df_rtt_filterd_pd['Group'] = df_rtt_filterd_pd['malware'].apply(lambda x: 'Malware' if x == 1 else 'Normal')\n",
    "\n",
    "# Create a boxplot with Plotly Express\n",
    "fig = px.box(\n",
    "    df_rtt_filterd_pd,\n",
    "    x='Group',\n",
    "    y='mean',\n",
    "    title='RTT filtered values (Under 0.75)',\n",
    "    labels={'mean': 'Mean Value', 'Group': 'Malware Status'}\n",
    ")\n",
    "\n",
    "# Show the plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e274de6-ccd2-47c5-9df4-dedeb2227773",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_top9rtt_tld = df_rtt.groupBy(\"icann_tld\").count().orderBy(F.col(\"count\").desc()).limit(9)\n",
    "\n",
    "# Join back with cleaned data to filter by malware\n",
    "df_limited = df_rtt.join(df_top9rtt_tld.select(\"icann_tld\"), on=\"icann_tld\", how=\"inner\")\n",
    "\n",
    "# Count occurrences for malware == 0 within top 9\n",
    "df_malware_0 = df_limited.filter(df_limited['malware'] == 0).groupBy(\"icann_tld\").count().toPandas()\n",
    "\n",
    "# Count occurrences for malware == 1 within top 9\n",
    "df_malware_1 = df_limited.filter(df_limited['malware'] == 1).groupBy(\"icann_tld\").count().toPandas()\n",
    "\n",
    "# Create a subplot figure\n",
    "fig = make_subplots(rows=1, cols=2, subplot_titles=(\"Normal\", \"Malware\"), specs=[[{\"type\": \"pie\"}, {\"type\": \"pie\"}]])\n",
    "\n",
    "# Add pie chart for malware == 0\n",
    "fig.add_trace(go.Pie(labels=df_malware_0[\"icann_tld\"], values=df_malware_0[\"count\"], name=\"Normal\"), row=1, col=1)\n",
    "\n",
    "# Add pie chart for malware == 1\n",
    "fig.add_trace(go.Pie(labels=df_malware_1[\"icann_tld\"], values=df_malware_1[\"count\"], name=\"Malware\"), row=1, col=2)\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title_text=\"Top 9 icann_tld Distribution by Malware Status\", showlegend=True)\n",
    "\n",
    "# Show plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9520bc0-3032-4f3c-8363-7ba76ee53af3",
   "metadata": {},
   "source": [
    "## *Entropy*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ed4a9e2-7305-463f-892f-bc1104c455a9",
   "metadata": {},
   "source": [
    "### preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a470efb3-cb25-4584-9908-c1880cde7f4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ent = df_final['icann_tld', 'icann_domain', 'entropy', 'malware']\n",
    "df_ent.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64950381-f31c-4ea8-ad7c-ec0d3f0c0901",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"count of TTL after drop nulls: {df_ent.count()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7e47646-36d4-4eaf-9155-caaead16f2c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"number of null values in normals:   {df_ent.filter(df_ent['entropy'].isNull() & (df_ent['malware'] == 0)).count()}\")\n",
    "print(f\"number of null values in malwares:  {df_ent.filter(df_ent['entropy'].isNull() & (df_ent['malware'] == 1)).count()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd4e8c6f-719a-4caa-995a-33b337b46268",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop nulls\n",
    "df_ent = df_ent.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b42eaa7-b721-4d96-80ff-3ce5b399c597",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"count of TTL after drop nulls: {df_ent.count()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b390b6b0-781f-433e-9d83-ab6b8cb57711",
   "metadata": {},
   "source": [
    "### unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4111e97-838e-4d73-a4d9-d61528860654",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ent = df_ent.groupBy('icann_tld', 'icann_domain', 'malware') \\\n",
    "                      .agg(F.sort_array(F.collect_list('entropy')).alias('sorted_entropy'))\n",
    "\n",
    "# Show the result\n",
    "df_ent.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1d70f96-61fd-4c27-8c80-84f79fa0a933",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ent.groupBy('icann_domain').count().orderBy(F.col('count').desc()).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1640ebac-8894-4240-8cd1-d330fd60158a",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"number of normal values:  {df_ent.filter(df_ent['malware'] != 1).count()}\")\n",
    "print(f\"number of malware values: {df_ent.filter(df_ent['malware'] == 1).count()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5027703-7ce3-4122-ba53-45d8c2af9fa4",
   "metadata": {},
   "source": [
    "### new ent dataframe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa367455-c297-4b8d-941a-7a7f7bd3cd4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ent_result = df_ent.withColumn(\n",
    "    \"min\", F.expr(\"array_min(sorted_entropy)\")  # Get the minimum TTL\n",
    ").withColumn(\n",
    "    \"max\", F.expr(\"array_max(sorted_entropy)\")  # Get the maximum TTL\n",
    ").withColumn(\n",
    "    \"mean\", F.expr(\"aggregate(sorted_entropy, 0D, (acc, x) -> acc + x) / size(sorted_entropy)\")  # Get the mean of TTLs\n",
    ")\n",
    "\n",
    "# Select the desired columns\n",
    "df_ent_result = df_ent_result.select('icann_tld', 'icann_domain', 'sorted_entropy', 'min', 'max', 'mean', 'malware')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81bf3d15-eb92-4b41-93b3-ffa549188554",
   "metadata": {},
   "outputs": [],
   "source": [
    "# show random rows\n",
    "df_ent_result.sample(fraction=0.2, seed=42).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86efa486-9608-4422-b5c4-75eb12d24ac9",
   "metadata": {},
   "source": [
    "### Ent eda"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0a2953f-a946-4c34-afd9-be9dd0f62d5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ent_pd = df_ent_result.toPandas()\n",
    "\n",
    "# Add a \"Group\" column to distinguish malware categories\n",
    "df_ent_pd['Group'] = df_ent_pd['malware'].apply(lambda x: 'Malware' if x == 1 else 'Normal')\n",
    "\n",
    "# Create a boxplot with Plotly Express\n",
    "fig = px.box(\n",
    "    df_ent_pd,\n",
    "    x='Group',\n",
    "    y='mean',\n",
    "    title='Distribution of Mean Values by Malware Status',\n",
    "    labels={'mean': 'Mean Value', 'Group': 'Malware Status'}\n",
    ")\n",
    "\n",
    "# Show the plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58ecbce9-06fd-46f3-b797-87c3286f14f9",
   "metadata": {},
   "source": [
    "## *TLD eda*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "04b079a9-ccea-4e37-b20b-4dd3a206d582",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tld = df_final.groupBy(\"icann_tld\", 'malware').count().orderBy(col(\"count\").desc()).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4820aeba-1335-4c57-94c7-649a253b3a71",
   "metadata": {},
   "source": [
    "### **whole year**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "686769f7-2477-4668-adf8-73d676e96e9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Count occurrences of each 'icann_tld' and select the top 10\n",
    "df_top_10 = df_m.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).limit(10)\n",
    "\n",
    "# Convert to Pandas for plotting\n",
    "df_top_10_pd = df_top_10.toPandas()\n",
    "\n",
    "# Create a pie chart\n",
    "fig = go.Figure(data=[go.Pie(labels=df_top_10_pd[\"icann_tld\"], values=df_top_10_pd[\"count\"], name=\"Top 10 icann_tld\")])\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title_text=\"Top 10 icann_tld Distribution for Year 2018 to 2024\", showlegend=True)\n",
    "\n",
    "# Show plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e957af4-a7de-4e10-b1b2-c3c5a7282211",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_m.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60be78b4-f4fe-43e1-8f37-09b87fbb8fab",
   "metadata": {},
   "source": [
    "### **2018**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7343543-d6cb-41a9-9c5f-481b90e613d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2018 = df_m.filter(F.col(\"year\") == 2018)\n",
    "\n",
    "# Count occurrences of each 'icann_tld' and select the top 10\n",
    "df_top_10 = df_2018.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).limit(10)\n",
    "\n",
    "# Convert to Pandas for plotting\n",
    "df_top_10_pd = df_top_10.toPandas()\n",
    "\n",
    "# Create a pie chart\n",
    "fig = go.Figure(data=[go.Pie(labels=df_top_10_pd[\"icann_tld\"], values=df_top_10_pd[\"count\"], name=\"Top 10 icann_tld\")])\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title_text=\"Top 10 icann_tld Distribution for Year 2018\", showlegend=True)\n",
    "\n",
    "# Show plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "390f1b65-5d09-4904-b656-8dea8f2df88d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2018.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7ace81a-b02f-4c7b-8eb0-f29f87af7aec",
   "metadata": {},
   "source": [
    "### **2019**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dea12aa8-2c3c-480a-a2c1-9cf806f11499",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2019 = df_m.filter(F.col(\"year\") == 2019)\n",
    "\n",
    "# Count occurrences of each 'icann_tld' and select the top 10\n",
    "df_top_10 = df_2019.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).limit(10)\n",
    "\n",
    "# Convert to Pandas for plotting\n",
    "df_top_10_pd = df_top_10.toPandas()\n",
    "\n",
    "# Create a pie chart\n",
    "fig = go.Figure(data=[go.Pie(labels=df_top_10_pd[\"icann_tld\"], values=df_top_10_pd[\"count\"], name=\"Top 10 icann_tld\")])\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title_text=\"Top 10 icann_tld Distribution for Year 2019\", showlegend=True)\n",
    "\n",
    "# Show plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f580d628-e28a-4c67-b75a-a5574cf27a94",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2019.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c358479d-96df-49b9-9a0b-9c47b2cbfeb3",
   "metadata": {},
   "source": [
    "### **2020**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c056737-1138-4e5d-8c3c-405ba8a2345b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2020 = df_m.filter(F.col(\"year\") == 2020)\n",
    "\n",
    "# Count occurrences of each 'icann_tld' and select the top 10\n",
    "df_top_10 = df_2020.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).limit(10)\n",
    "\n",
    "# Convert to Pandas for plotting\n",
    "df_top_10_pd = df_top_10.toPandas()\n",
    "\n",
    "# Create a pie chart\n",
    "fig = go.Figure(data=[go.Pie(labels=df_top_10_pd[\"icann_tld\"], values=df_top_10_pd[\"count\"], name=\"Top 10 icann_tld\")])\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title_text=\"Top 10 icann_tld Distribution for Year 2020\", showlegend=True)\n",
    "\n",
    "# Show plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a18fd4a5-a3df-4548-bfb5-07b14aaf7021",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2020.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a3654a0-5e71-46b9-af53-6f06ee161359",
   "metadata": {},
   "source": [
    "### **2021**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bed49b93-9afb-4b73-a11f-836deb8f29f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2021 = df_m.filter(F.col(\"year\") == 2021)\n",
    "\n",
    "# Count occurrences of each 'icann_tld' and select the top 10\n",
    "df_top_10 = df_2021.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).limit(10)\n",
    "\n",
    "# Convert to Pandas for plotting\n",
    "df_top_10_pd = df_top_10.toPandas()\n",
    "\n",
    "# Create a pie chart\n",
    "fig = go.Figure(data=[go.Pie(labels=df_top_10_pd[\"icann_tld\"], values=df_top_10_pd[\"count\"], name=\"Top 10 icann_tld\")])\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title_text=\"Top 10 icann_tld Distribution for Year 2021\", showlegend=True)\n",
    "\n",
    "# Show plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52aced65-0e26-412c-b70c-1e07eb258ad7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2021.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02092464-0344-45de-a3bd-9c018ac35393",
   "metadata": {},
   "source": [
    "### **2022**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4e6f525-97bb-47c2-b60b-fd00a4d6e1e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2022 = df_m.filter(F.col(\"year\") == 2022)\n",
    "\n",
    "# Count occurrences of each 'icann_tld' and select the top 10\n",
    "df_top_10 = df_2022.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).limit(10)\n",
    "\n",
    "# Convert to Pandas for plotting\n",
    "df_top_10_pd = df_top_10.toPandas()\n",
    "\n",
    "# Create a pie chart\n",
    "fig = go.Figure(data=[go.Pie(labels=df_top_10_pd[\"icann_tld\"], values=df_top_10_pd[\"count\"], name=\"Top 10 icann_tld\")])\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title_text=\"Top 10 icann_tld Distribution for Year 2022\", showlegend=True)\n",
    "\n",
    "# Show plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c470ba33-2654-4b5c-b0a4-fbfb50d20b99",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2022.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f6ab1fe-e447-46a8-914a-802eb264aafd",
   "metadata": {},
   "source": [
    "### **2023**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ddacae77-2df1-4416-9e7a-c41bae65a68a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2023 = df_m.filter(F.col(\"year\") == 2023)\n",
    "\n",
    "# Count occurrences of each 'icann_tld' and select the top 10\n",
    "df_top_10 = df_2023.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).limit(10)\n",
    "\n",
    "# Convert to Pandas for plotting\n",
    "df_top_10_pd = df_top_10.toPandas()\n",
    "\n",
    "# Create a pie chart\n",
    "fig = go.Figure(data=[go.Pie(labels=df_top_10_pd[\"icann_tld\"], values=df_top_10_pd[\"count\"], name=\"Top 10 icann_tld\")])\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title_text=\"Top 10 icann_tld Distribution for Year 2023\", showlegend=True)\n",
    "\n",
    "# Show plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b1ab4fb-930a-4153-b09c-24e172ca8681",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2023.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d56da01-0985-4e25-a019-b11220112800",
   "metadata": {},
   "source": [
    "### **2024**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72633170-1086-4617-bdf7-dab81baa569c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2024 = df_m.filter(F.col(\"year\") == 2024)\n",
    "\n",
    "# Count occurrences of each 'icann_tld' and select the top 10\n",
    "df_top_10 = df_2024.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).limit(10)\n",
    "\n",
    "# Convert to Pandas for plotting\n",
    "df_top_10_pd = df_top_10.toPandas()`\n",
    "\n",
    "# Create a pie chart\n",
    "fig = go.Figure(data=[go.Pie(labels=df_top_10_pd[\"icann_tld\"], values=df_top_10_pd[\"count\"], name=\"Top 10 icann_tld\")])\n",
    "\n",
    "# Update layout\n",
    "fig.update_layout(title_text=\"Top 10 icann_tld Distribution for Year 2024\", showlegend=True)\n",
    "\n",
    "# Show plot\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8275408a-89ea-4ad4-92aa-53b4f0d38d9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2024.groupBy(\"icann_tld\").count().orderBy(col(\"count\").desc()).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a1fbe60-3399-4b56-ae98-40a2292546ef",
   "metadata": {},
   "source": [
    "## *rcode eda*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9e5a0d4-9da8-46ed-9aaf-506594267235",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_RN_count = df_final.groupBy('rcode_name', 'malware', 'year').count().orderBy(F.col('count').desc())\n",
    "fig = px.histogram(df_RN_count, x=\"rcode_name\", y='count',\n",
    "             color='malware', barmode='group',\n",
    "             height=400)\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb90d459-c2a8-4915-9709-597e73b5fda4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_RN_count.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7eb87d6f-53d3-49a8-8b3b-dcfac04134d9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a707d5f4-5f22-4694-8237-fc72aa1088d3",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}