Phase 3 - Data Manipulation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b3379e3c",
   "metadata": {},
   "source": [
    "# Data Science Project | Common Vulnerability Exposures \n",
    "# Phase 3 - Data Manipulation \n",
    "\n",
    "### During this phase we created 4 functions that mainpulate the data:\n",
    "\n",
    "1. **function load_dataframe** - will load our csv file into a dataframe.\n",
    "2. **function remove_unnecessary_data** - will remove a column that contain only None value, will remove rows with the CVE Score of 0, the removal is necessary since that CVE Score 0 indicates that the row is not actually a vulnerability, but more of a informational view of a product, will remove the vulnerabilities that is not in the last decade, since our project is  concerned with the last decade only, will remove duplicate rows and will keep the first one.\n",
    "3. **function replace_missing_data** - will replace missing values such as '???' and NaN with the 0 value, we kept the rows with null values on our dataframe since a row could have a null values on a feature of a vulnerability but the key value of a vulnerability such as the CVE score was not null.\n",
    "4. **function convert_string_to_int** - will convert each string value to an int value based on the diffrent columns, this function is mostly necessary for the machine learning phase. the values were converted mostly based on unique values and specific date was converted to a year since the month and day was not important for our research question "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "899c7a4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sklearn.preprocessing import LabelEncoder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "5d805abe",
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_dataframe(filename):\n",
    "    return pd.read_csv(filename)\n",
    "\n",
    "df = load_dataframe('cve_DF.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "e28e5214",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The dataframe befor manipulation:\n"
     ]
    },
    {
     "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>CVE_ID</th>\n",
       "      <th>Vulnerability Type</th>\n",
       "      <th>Publish Date</th>\n",
       "      <th>Update Date</th>\n",
       "      <th>Score</th>\n",
       "      <th>Gained Access Level</th>\n",
       "      <th>Access</th>\n",
       "      <th>Complexity</th>\n",
       "      <th>Authentication</th>\n",
       "      <th>Confidentiality</th>\n",
       "      <th>Integrity</th>\n",
       "      <th>Availability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CVE-2022-29603</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\tSql \\t\\t\\t\\t\\t</td>\n",
       "      <td>25/04/2022</td>\n",
       "      <td>25/04/2022</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</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>1</th>\n",
       "      <td>CVE-2022-29589</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\tXSS \\t\\t\\t\\t\\t</td>\n",
       "      <td>22/04/2022</td>\n",
       "      <td>22/04/2022</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</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>2</th>\n",
       "      <td>CVE-2022-29583</td>\n",
       "      <td>\\n</td>\n",
       "      <td>22/04/2022</td>\n",
       "      <td>22/04/2022</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</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>3</th>\n",
       "      <td>CVE-2022-29582</td>\n",
       "      <td>\\n</td>\n",
       "      <td>22/04/2022</td>\n",
       "      <td>22/04/2022</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</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>4</th>\n",
       "      <td>CVE-2022-29577</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\tXSS \\t\\t\\t\\t\\t</td>\n",
       "      <td>21/04/2022</td>\n",
       "      <td>22/04/2022</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</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>...</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2995</th>\n",
       "      <td>CVE-2021-20295</td>\n",
       "      <td>\\n</td>\n",
       "      <td>01/04/2022</td>\n",
       "      <td>08/04/2022</td>\n",
       "      <td>2.1</td>\n",
       "      <td>None</td>\n",
       "      <td>Local</td>\n",
       "      <td>Low</td>\n",
       "      <td>Not required</td>\n",
       "      <td>Partial</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2996</th>\n",
       "      <td>CVE-2021-20269</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\t+Info \\t\\t\\t\\t\\t</td>\n",
       "      <td>10/03/2022</td>\n",
       "      <td>15/03/2022</td>\n",
       "      <td>2.1</td>\n",
       "      <td>None</td>\n",
       "      <td>Local</td>\n",
       "      <td>Low</td>\n",
       "      <td>Not required</td>\n",
       "      <td>Partial</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2997</th>\n",
       "      <td>CVE-2021-20263</td>\n",
       "      <td>\\n</td>\n",
       "      <td>09/03/2021</td>\n",
       "      <td>01/01/2022</td>\n",
       "      <td>2.1</td>\n",
       "      <td>None</td>\n",
       "      <td>Local</td>\n",
       "      <td>Low</td>\n",
       "      <td>Not required</td>\n",
       "      <td>None</td>\n",
       "      <td>Partial</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2998</th>\n",
       "      <td>CVE-2021-20257</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\tDoS \\t\\t\\t\\t\\t</td>\n",
       "      <td>16/03/2022</td>\n",
       "      <td>25/04/2022</td>\n",
       "      <td>2.1</td>\n",
       "      <td>None</td>\n",
       "      <td>Local</td>\n",
       "      <td>Low</td>\n",
       "      <td>Not required</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Partial</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999</th>\n",
       "      <td>CVE-2021-20255</td>\n",
       "      <td>\\n\\t\\t\\t\\t\\t\\tDoS Overflow \\t\\t\\t\\t\\t</td>\n",
       "      <td>09/03/2021</td>\n",
       "      <td>11/04/2021</td>\n",
       "      <td>2.1</td>\n",
       "      <td>None</td>\n",
       "      <td>Local</td>\n",
       "      <td>Low</td>\n",
       "      <td>Not required</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Partial</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3000 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              CVE_ID                     Vulnerability Type Publish Date  \\\n",
       "0     CVE-2022-29603           \\n\\t\\t\\t\\t\\t\\tSql \\t\\t\\t\\t\\t   25/04/2022   \n",
       "1     CVE-2022-29589           \\n\\t\\t\\t\\t\\t\\tXSS \\t\\t\\t\\t\\t   22/04/2022   \n",
       "2     CVE-2022-29583                                     \\n   22/04/2022   \n",
       "3     CVE-2022-29582                                     \\n   22/04/2022   \n",
       "4     CVE-2022-29577           \\n\\t\\t\\t\\t\\t\\tXSS \\t\\t\\t\\t\\t   21/04/2022   \n",
       "...              ...                                    ...          ...   \n",
       "2995  CVE-2021-20295                                     \\n   01/04/2022   \n",
       "2996  CVE-2021-20269         \\n\\t\\t\\t\\t\\t\\t+Info \\t\\t\\t\\t\\t   10/03/2022   \n",
       "2997  CVE-2021-20263                                     \\n   09/03/2021   \n",
       "2998  CVE-2021-20257           \\n\\t\\t\\t\\t\\t\\tDoS \\t\\t\\t\\t\\t   16/03/2022   \n",
       "2999  CVE-2021-20255  \\n\\t\\t\\t\\t\\t\\tDoS Overflow \\t\\t\\t\\t\\t   09/03/2021   \n",
       "\n",
       "     Update Date  Score Gained Access Level Access Complexity Authentication  \\\n",
       "0     25/04/2022    0.0                None    ???        ???            ???   \n",
       "1     22/04/2022    0.0                None    ???        ???            ???   \n",
       "2     22/04/2022    0.0                None    ???        ???            ???   \n",
       "3     22/04/2022    0.0                None    ???        ???            ???   \n",
       "4     22/04/2022    0.0                None    ???        ???            ???   \n",
       "...          ...    ...                 ...    ...        ...            ...   \n",
       "2995  08/04/2022    2.1                None  Local        Low   Not required   \n",
       "2996  15/03/2022    2.1                None  Local        Low   Not required   \n",
       "2997  01/01/2022    2.1                None  Local        Low   Not required   \n",
       "2998  25/04/2022    2.1                None  Local        Low   Not required   \n",
       "2999  11/04/2021    2.1                None  Local        Low   Not required   \n",
       "\n",
       "     Confidentiality Integrity Availability  \n",
       "0                ???       ???          ???  \n",
       "1                ???       ???          ???  \n",
       "2                ???       ???          ???  \n",
       "3                ???       ???          ???  \n",
       "4                ???       ???          ???  \n",
       "...              ...       ...          ...  \n",
       "2995         Partial      None         None  \n",
       "2996         Partial      None         None  \n",
       "2997            None   Partial         None  \n",
       "2998            None      None      Partial  \n",
       "2999            None      None      Partial  \n",
       "\n",
       "[3000 rows x 12 columns]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Printing the dataframe befor manipulation\n",
    "print(\"The dataframe befor manipulation:\")\n",
    "df.head(3000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "86d19599",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of the dataframe: (174953, 12)\n",
      "\n",
      "The dataframe count of null values:\n",
      " CVE_ID                 0\n",
      "Vulnerability Type     0\n",
      "Publish Date           0\n",
      "Update Date            0\n",
      "Score                  0\n",
      "Gained Access Level    0\n",
      "Access                 0\n",
      "Complexity             0\n",
      "Authentication         0\n",
      "Confidentiality        0\n",
      "Integrity              0\n",
      "Availability           0\n",
      "dtype: int64\n"
     ]
    },
    {
     "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>CVE_ID</th>\n",
       "      <th>Vulnerability Type</th>\n",
       "      <th>Publish Date</th>\n",
       "      <th>Update Date</th>\n",
       "      <th>Score</th>\n",
       "      <th>Gained Access Level</th>\n",
       "      <th>Access</th>\n",
       "      <th>Complexity</th>\n",
       "      <th>Authentication</th>\n",
       "      <th>Confidentiality</th>\n",
       "      <th>Integrity</th>\n",
       "      <th>Availability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>174953</td>\n",
       "      <td>174953</td>\n",
       "      <td>174953</td>\n",
       "      <td>174953</td>\n",
       "      <td>174953.000000</td>\n",
       "      <td>174953</td>\n",
       "      <td>174953</td>\n",
       "      <td>174953</td>\n",
       "      <td>174953</td>\n",
       "      <td>174953</td>\n",
       "      <td>174953</td>\n",
       "      <td>174953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>174943</td>\n",
       "      <td>214</td>\n",
       "      <td>6463</td>\n",
       "      <td>3511</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>CVE-2022-29603</td>\n",
       "      <td>\\n</td>\n",
       "      <td>31/12/2004</td>\n",
       "      <td>03/10/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>Remote</td>\n",
       "      <td>Low</td>\n",
       "      <td>Not required</td>\n",
       "      <td>Partial</td>\n",
       "      <td>Partial</td>\n",
       "      <td>Partial</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>2</td>\n",
       "      <td>50220</td>\n",
       "      <td>1098</td>\n",
       "      <td>5740</td>\n",
       "      <td>NaN</td>\n",
       "      <td>174953</td>\n",
       "      <td>144671</td>\n",
       "      <td>101722</td>\n",
       "      <td>147459</td>\n",
       "      <td>85881</td>\n",
       "      <td>92051</td>\n",
       "      <td>74851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.884570</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.018607</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.300000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.400000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.500000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                CVE_ID Vulnerability Type Publish Date Update Date  \\\n",
       "count           174953             174953       174953      174953   \n",
       "unique          174943                214         6463        3511   \n",
       "top     CVE-2022-29603                 \\n   31/12/2004  03/10/2019   \n",
       "freq                 2              50220         1098        5740   \n",
       "mean               NaN                NaN          NaN         NaN   \n",
       "std                NaN                NaN          NaN         NaN   \n",
       "min                NaN                NaN          NaN         NaN   \n",
       "25%                NaN                NaN          NaN         NaN   \n",
       "50%                NaN                NaN          NaN         NaN   \n",
       "75%                NaN                NaN          NaN         NaN   \n",
       "max                NaN                NaN          NaN         NaN   \n",
       "\n",
       "                Score Gained Access Level  Access Complexity Authentication  \\\n",
       "count   174953.000000              174953  174953     174953         174953   \n",
       "unique            NaN                   1       4          4              3   \n",
       "top               NaN                None  Remote        Low   Not required   \n",
       "freq              NaN              174953  144671     101722         147459   \n",
       "mean         5.884570                 NaN     NaN        NaN            NaN   \n",
       "std          2.018607                 NaN     NaN        NaN            NaN   \n",
       "min          0.000000                 NaN     NaN        NaN            NaN   \n",
       "25%          4.300000                 NaN     NaN        NaN            NaN   \n",
       "50%          5.400000                 NaN     NaN        NaN            NaN   \n",
       "75%          7.500000                 NaN     NaN        NaN            NaN   \n",
       "max         10.000000                 NaN     NaN        NaN            NaN   \n",
       "\n",
       "       Confidentiality Integrity Availability  \n",
       "count           174953    174953       174953  \n",
       "unique               4         4            4  \n",
       "top            Partial   Partial      Partial  \n",
       "freq             85881     92051        74851  \n",
       "mean               NaN       NaN          NaN  \n",
       "std                NaN       NaN          NaN  \n",
       "min                NaN       NaN          NaN  \n",
       "25%                NaN       NaN          NaN  \n",
       "50%                NaN       NaN          NaN  \n",
       "75%                NaN       NaN          NaN  \n",
       "max                NaN       NaN          NaN  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Exploring the data befor manipulation\n",
    "print(\"shape of the dataframe:\" ,df.shape)\n",
    "print(\"\\nThe dataframe count of null values:\\n\",df.isnull().sum(axis = 0))\n",
    "df.describe(include='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "8d6469a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_unnecessary_data(dataframe):\n",
    "    \n",
    "    df = dataframe.copy()\n",
    "    \n",
    "    #remove a column that contain only None\n",
    "    df.drop('Gained Access Level', inplace=True, axis=1)\n",
    "    \n",
    "    #remove all the rows with vulnerability score 0\n",
    "    df = df[df.Score != 0.0]\n",
    "    \n",
    "    #remove all the vulnerabilities from over a decade\n",
    "    for i in range(1998,2012):\n",
    "        df = df[df[\"CVE_ID\"].str.contains(str(i)) == False]\n",
    "    \n",
    "    #remove duplicate rows\n",
    "    df = df.drop_duplicates(keep='first').copy() \n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "65d02f2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def replace_missing_data(dataframe):\n",
    "    # Fill null values with 0\n",
    "    dt = dataframe.copy()\n",
    "    dt['Authentication'].mask(dt['Authentication'] == '???', 0, inplace=True)\n",
    "    dt = dt.fillna(0)\n",
    "    \n",
    "    return dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "50837de5",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_string_to_int(dataframe):\n",
    "    \n",
    "    df = dataframe.copy()\n",
    "        \n",
    "    # Convert CVE_ID from string to int\n",
    "    df['CVE_ID'] = df['CVE_ID'].str.replace(\"-\",\"\")\n",
    "    df['CVE_ID'] = df['CVE_ID'].str.replace(\"CVE\",\"\")\n",
    "    \n",
    "    # Convert Vulnerability Type to unique values\n",
    "    df.rename(columns = {'Vulnerability Type': 'VulnerabilityType'}, inplace=True)\n",
    "    le = LabelEncoder()\n",
    "    df['VulnerabilityType'] = le.fit_transform(df.VulnerabilityType)\n",
    "    \n",
    "    # Convert date to datetime \n",
    "    df['Publish Date'] = pd.to_datetime(df['Publish Date'])\n",
    "    df['Update Date'] = pd.to_datetime(df['Update Date'])\n",
    "    \n",
    "    # Extract a year from the date time\n",
    "    df['Update Date'] = pd.DatetimeIndex(df['Update Date']).year\n",
    "    df['Publish Date'] = pd.DatetimeIndex(df['Publish Date']).year\n",
    "    \n",
    "    # Convert Access\n",
    "    replaceAccess = {'Local':1, 'Local Network':2, 'Remote':3}\n",
    "    df.replace(replaceAccess,inplace=True)\n",
    "    \n",
    "    # Convert Complexity\n",
    "    replaceComplexity = {'Low':1, 'Medium':2, 'High':3}\n",
    "    df.replace(replaceComplexity,inplace=True)\n",
    "    \n",
    "    # Convert Authentication\n",
    "    replaceAuthentication = {'Not required':1, 'Single system':2}\n",
    "    df.replace(replaceAuthentication,inplace=True)\n",
    "    \n",
    "    # Convert Confidentiality\n",
    "    replaceConfidentiality = {'None':1, 'Partial':2, 'Complete':3}\n",
    "    df.replace(replaceConfidentiality,inplace=True)\n",
    "    \n",
    "    # Convert Integrity\n",
    "    replaceIntegrity = {'None':1, 'Partial':2, 'Complete':3}\n",
    "    df.replace(replaceIntegrity,inplace=True)\n",
    "    \n",
    "    # Convert Availability\n",
    "    replaceAvailability = {'None':1, 'Partial':2, 'Complete':3}\n",
    "    df.replace(replaceAvailability,inplace=True)\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "8a58a8ce",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The dataframe after manipulation:\n"
     ]
    },
    {
     "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>CVE_ID</th>\n",
       "      <th>VulnerabilityType</th>\n",
       "      <th>Publish Date</th>\n",
       "      <th>Update Date</th>\n",
       "      <th>Score</th>\n",
       "      <th>Access</th>\n",
       "      <th>Complexity</th>\n",
       "      <th>Authentication</th>\n",
       "      <th>Confidentiality</th>\n",
       "      <th>Integrity</th>\n",
       "      <th>Availability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>792</th>\n",
       "      <td>202227841</td>\n",
       "      <td>0</td>\n",
       "      <td>2022</td>\n",
       "      <td>2022</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>793</th>\n",
       "      <td>202227049</td>\n",
       "      <td>0</td>\n",
       "      <td>2022</td>\n",
       "      <td>2022</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794</th>\n",
       "      <td>202226355</td>\n",
       "      <td>0</td>\n",
       "      <td>2022</td>\n",
       "      <td>2022</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>795</th>\n",
       "      <td>202225831</td>\n",
       "      <td>0</td>\n",
       "      <td>2022</td>\n",
       "      <td>2022</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796</th>\n",
       "      <td>202225368</td>\n",
       "      <td>0</td>\n",
       "      <td>2022</td>\n",
       "      <td>2022</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4136</th>\n",
       "      <td>20203580</td>\n",
       "      <td>133</td>\n",
       "      <td>2020</td>\n",
       "      <td>2020</td>\n",
       "      <td>2.6</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4137</th>\n",
       "      <td>20203541</td>\n",
       "      <td>0</td>\n",
       "      <td>2020</td>\n",
       "      <td>2020</td>\n",
       "      <td>2.1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4138</th>\n",
       "      <td>20203520</td>\n",
       "      <td>1</td>\n",
       "      <td>2020</td>\n",
       "      <td>2020</td>\n",
       "      <td>2.1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4139</th>\n",
       "      <td>20203504</td>\n",
       "      <td>36</td>\n",
       "      <td>2020</td>\n",
       "      <td>2020</td>\n",
       "      <td>2.1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4140</th>\n",
       "      <td>20203477</td>\n",
       "      <td>90</td>\n",
       "      <td>2020</td>\n",
       "      <td>2021</td>\n",
       "      <td>2.1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3000 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         CVE_ID  VulnerabilityType  Publish Date  Update Date  Score  Access  \\\n",
       "792   202227841                  0          2022         2022    1.9       1   \n",
       "793   202227049                  0          2022         2022    1.9       1   \n",
       "794   202226355                  0          2022         2022    1.9       1   \n",
       "795   202225831                  0          2022         2022    1.9       1   \n",
       "796   202225368                  0          2022         2022    1.9       1   \n",
       "...         ...                ...           ...          ...    ...     ...   \n",
       "4136   20203580                133          2020         2020    2.6       3   \n",
       "4137   20203541                  0          2020         2020    2.1       1   \n",
       "4138   20203520                  1          2020         2020    2.1       1   \n",
       "4139   20203504                 36          2020         2020    2.1       1   \n",
       "4140   20203477                 90          2020         2021    2.1       1   \n",
       "\n",
       "      Complexity  Authentication  Confidentiality  Integrity  Availability  \n",
       "792            2               1                2          1             1  \n",
       "793            2               1                1          2             1  \n",
       "794            2               1                2          1             1  \n",
       "795            2               1                2          1             1  \n",
       "796            2               1                2          1             1  \n",
       "...          ...             ...              ...        ...           ...  \n",
       "4136           3               1                1          2             1  \n",
       "4137           1               1                2          1             1  \n",
       "4138           1               1                2          1             1  \n",
       "4139           1               1                1          1             2  \n",
       "4140           1               1                2          1             1  \n",
       "\n",
       "[3000 rows x 11 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = load_dataframe('cve_DF.csv')\n",
    "df = remove_unnecessary_data(df)\n",
    "dt = replace_missing_data(df)\n",
    "ds = convert_string_to_int(dt)\n",
    "print(\"The dataframe after manipulation:\")\n",
    "ds.head(3000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "3d1d3543",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of the dataframe: (123144, 11)\n",
      "\n",
      "The dataframe count of null values:\n",
      " CVE_ID                0\n",
      "Vulnerability Type    0\n",
      "Publish Date          0\n",
      "Update Date           0\n",
      "Score                 0\n",
      "Access                0\n",
      "Complexity            0\n",
      "Authentication        0\n",
      "Confidentiality       0\n",
      "Integrity             0\n",
      "Availability          0\n",
      "dtype: int64\n"
     ]
    },
    {
     "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>CVE_ID</th>\n",
       "      <th>Vulnerability Type</th>\n",
       "      <th>Publish Date</th>\n",
       "      <th>Update Date</th>\n",
       "      <th>Score</th>\n",
       "      <th>Access</th>\n",
       "      <th>Complexity</th>\n",
       "      <th>Authentication</th>\n",
       "      <th>Confidentiality</th>\n",
       "      <th>Integrity</th>\n",
       "      <th>Availability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>123144</td>\n",
       "      <td>123144</td>\n",
       "      <td>123144</td>\n",
       "      <td>123144</td>\n",
       "      <td>123144.000000</td>\n",
       "      <td>123144</td>\n",
       "      <td>123144</td>\n",
       "      <td>123144</td>\n",
       "      <td>123144</td>\n",
       "      <td>123144</td>\n",
       "      <td>123144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>123144</td>\n",
       "      <td>182</td>\n",
       "      <td>3287</td>\n",
       "      <td>2768</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>CVE-2022-27841</td>\n",
       "      <td>\\n</td>\n",
       "      <td>15/04/2020</td>\n",
       "      <td>03/10/2019</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Remote</td>\n",
       "      <td>Low</td>\n",
       "      <td>Not required</td>\n",
       "      <td>Partial</td>\n",
       "      <td>Partial</td>\n",
       "      <td>Partial</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>1</td>\n",
       "      <td>40409</td>\n",
       "      <td>508</td>\n",
       "      <td>5710</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100732</td>\n",
       "      <td>68571</td>\n",
       "      <td>99199</td>\n",
       "      <td>60943</td>\n",
       "      <td>64146</td>\n",
       "      <td>50955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.741184</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.954354</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.300000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.200000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                CVE_ID Vulnerability Type Publish Date Update Date  \\\n",
       "count           123144             123144       123144      123144   \n",
       "unique          123144                182         3287        2768   \n",
       "top     CVE-2022-27841                 \\n   15/04/2020  03/10/2019   \n",
       "freq                 1              40409          508        5710   \n",
       "mean               NaN                NaN          NaN         NaN   \n",
       "std                NaN                NaN          NaN         NaN   \n",
       "min                NaN                NaN          NaN         NaN   \n",
       "25%                NaN                NaN          NaN         NaN   \n",
       "50%                NaN                NaN          NaN         NaN   \n",
       "75%                NaN                NaN          NaN         NaN   \n",
       "max                NaN                NaN          NaN         NaN   \n",
       "\n",
       "                Score  Access Complexity Authentication Confidentiality  \\\n",
       "count   123144.000000  123144     123144         123144          123144   \n",
       "unique            NaN       3          3              3               3   \n",
       "top               NaN  Remote        Low   Not required         Partial   \n",
       "freq              NaN  100732      68571          99199           60943   \n",
       "mean         5.741184     NaN        NaN            NaN             NaN   \n",
       "std          1.954354     NaN        NaN            NaN             NaN   \n",
       "min          1.000000     NaN        NaN            NaN             NaN   \n",
       "25%          4.300000     NaN        NaN            NaN             NaN   \n",
       "50%          5.000000     NaN        NaN            NaN             NaN   \n",
       "75%          7.200000     NaN        NaN            NaN             NaN   \n",
       "max         10.000000     NaN        NaN            NaN             NaN   \n",
       "\n",
       "       Integrity Availability  \n",
       "count     123144       123144  \n",
       "unique         3            3  \n",
       "top      Partial      Partial  \n",
       "freq       64146        50955  \n",
       "mean         NaN          NaN  \n",
       "std          NaN          NaN  \n",
       "min          NaN          NaN  \n",
       "25%          NaN          NaN  \n",
       "50%          NaN          NaN  \n",
       "75%          NaN          NaN  \n",
       "max          NaN          NaN  "
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Exploring the data after manipulation\n",
    "print(\"shape of the dataframe:\" ,df.shape)\n",
    "print(\"\\nThe dataframe count of null values:\\n\",df.isnull().sum(axis = 0))\n",
    "df.describe(include='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fc72fcc",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}