{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"provenance":[],"gpuType":"T4"},"kernelspec":{"name":"python3","display_name":"Python 3"},"language_info":{"name":"python"},"accelerator":"GPU"},"cells":[{"cell_type":"markdown","metadata":{"id":"title"},"source":["# 02 — NLP Preprocessing\n","\n","Cleans CVE descriptions, runs spaCy NER, engineers keyword features, encodes CVSS metadata.\n","\n","**Run order:**\n","1. Cell 1 — Mount Drive + set paths\n","2. Cell 2 — Install spaCy\n","3. Cell 3 — Define all functions (cleaning + feature extraction)\n","4. Cell 4 — Run preprocessing (smart: handles first run AND update runs)\n","5. Cell 5 — Final verification\n","\n","---\n","**First time running:** processes all rows from scratch (~47 min for 200k rows)\n","\n","**After live updater:** only processes NEW rows, appends to existing file (~5-15 min)"]},{"cell_type":"markdown","metadata":{"id":"cell1_header"},"source":["## Cell 1 — Mount Drive + set paths"]},{"cell_type":"code","metadata":{"id":"cell1","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1774171160930,"user_tz":-330,"elapsed":29623,"user":{"displayName":"Manglam Jaiswal","userId":"07075031299373953626"}},"outputId":"4e5aa116-7911-4104-d5b4-de1a409137d5"},"source":["from google.colab import drive\n","drive.mount('/content/drive')\n","\n","import pandas as pd\n","import os\n","\n","BASE      = '/content/drive/MyDrive/CVE_Project'\n","RAW       = f'{BASE}/raw_data'\n","PROCESSED = f'{BASE}/processed'\n","os.makedirs(PROCESSED, exist_ok=True)\n","\n","# Load raw CSV\n","df_raw = pd.read_csv(f'{RAW}/cves_raw.csv')\n","print(f'Raw CSV loaded:     {len(df_raw)} rows')\n","print(f'Columns:            {list(df_raw.columns)}')\n","\n","# Check if processed file already exists\n","if os.path.exists(f'{PROCESSED}/cves_processed.csv'):\n","    df_existing = pd.read_csv(f'{PROCESSED}/cves_processed.csv')\n","    already_done = set(df_existing['cve_id'].tolist())\n","    print(f'\\nProcessed file found: {len(df_existing)} rows already done')\n","    print(f'New rows to process:  {len(df_raw) - len(already_done)}')\n","else:\n","    df_existing  = None\n","    already_done = set()\n","    print(f'\\nNo processed file found. Will process all {len(df_raw)} rows from scratch.')"],"execution_count":1,"outputs":[{"output_type":"stream","name":"stdout","text":["Mounted at /content/drive\n","Raw CSV loaded:     200431 rows\n","Columns:            ['cve_id', 'description', 'cvss_score', 'cvss_label', 'attack_vector', 'attack_complexity', 'privileges_required', 'user_interaction', 'scope']\n","\n","Processed file found: 200431 rows already done\n","New rows to process:  0\n"]}]},{"cell_type":"markdown","metadata":{"id":"cell2_header"},"source":["## Cell 2 — Install spaCy"]},{"cell_type":"code","metadata":{"id":"cell2","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1774171184396,"user_tz":-330,"elapsed":23447,"user":{"displayName":"Manglam Jaiswal","userId":"07075031299373953626"}},"outputId":"fe6f93ed-3164-4861-f98d-c61b130e4214"},"source":["!pip install spacy tqdm -q\n","!python -m spacy download en_core_web_sm -q\n","\n","import spacy\n","nlp = spacy.load('en_core_web_sm')\n","print('spaCy loaded successfully.')"],"execution_count":2,"outputs":[{"output_type":"stream","name":"stdout","text":["\u001b[?25l     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m0.0/12.8 MB\u001b[0m \u001b[31m?\u001b[0m eta \u001b[36m-:--:--\u001b[0m\r\u001b[2K     \u001b[91m━━━━━━━━━━\u001b[0m\u001b[91m╸\u001b[0m\u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m3.5/12.8 MB\u001b[0m \u001b[31m105.6 MB/s\u001b[0m eta \u001b[36m0:00:01\u001b[0m\r\u001b[2K     \u001b[91m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[91m╸\u001b[0m\u001b[90m━━━━━━━\u001b[0m \u001b[32m10.5/12.8 MB\u001b[0m \u001b[31m207.7 MB/s\u001b[0m eta \u001b[36m0:00:01\u001b[0m\r\u001b[2K     \u001b[91m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[91m╸\u001b[0m\u001b[90m━━━━━━━\u001b[0m \u001b[32m10.5/12.8 MB\u001b[0m \u001b[31m207.7 MB/s\u001b[0m eta \u001b[36m0:00:01\u001b[0m\r\u001b[2K     \u001b[91m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[91m╸\u001b[0m \u001b[32m12.8/12.8 MB\u001b[0m \u001b[31m95.6 MB/s\u001b[0m eta \u001b[36m0:00:01\u001b[0m\r\u001b[2K     \u001b[91m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m\u001b[91m╸\u001b[0m \u001b[32m12.8/12.8 MB\u001b[0m \u001b[31m95.6 MB/s\u001b[0m eta \u001b[36m0:00:01\u001b[0m\r\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m12.8/12.8 MB\u001b[0m \u001b[31m66.2 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[?25h\u001b[38;5;2m✔ Download and installation successful\u001b[0m\n","You can now load the package via spacy.load('en_core_web_sm')\n","\u001b[38;5;3m⚠ Restart to reload dependencies\u001b[0m\n","If you are in a Jupyter or Colab notebook, you may need to restart Python in\n","order to load all the package's dependencies. You can do this by selecting the\n","'Restart kernel' or 'Restart runtime' option.\n","spaCy loaded successfully.\n"]}]},{"cell_type":"markdown","metadata":{"id":"cell3_header"},"source":["## Cell 3 — Define all functions"]},{"cell_type":"code","metadata":{"id":"cell3","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1774171184433,"user_tz":-330,"elapsed":26,"user":{"displayName":"Manglam Jaiswal","userId":"07075031299373953626"}},"outputId":"75b05982-773c-43e4-cf75-6516df25f0cc"},"source":["import re\n","import pandas as pd\n","\n","# ── Text cleaning ──────────────────────────────────────────────────────\n","def clean_text(text):\n","    text = str(text).lower()\n","    text = re.sub(r'http\\S+|www\\S+', '', text)          # remove URLs\n","    text = re.sub(r'<.*?>', '', text)                    # remove HTML tags\n","    text = re.sub(r'cve-\\d{4}-\\d+', 'CVE_TOKEN', text)  # normalise CVE IDs\n","    text = re.sub(r'v?\\d+\\.\\d+[\\./\\d]*', 'VERSION_TOKEN', text)  # normalise versions\n","    text = re.sub(r'[^a-z0-9\\s_]', ' ', text)           # remove special chars\n","    text = re.sub(r'\\s+', ' ', text).strip()             # collapse whitespace\n","    return text\n","\n","# ── Keyword feature flags ──────────────────────────────────────────────\n","REMOTE_WORDS   = ['remote', 'remotely', 'network']\n","UNAUTH_WORDS   = ['unauthenticated', 'unauthorized', 'no authentication']\n","EXEC_WORDS     = ['execute', 'execution', 'arbitrary code', 'command injection', 'rce']\n","PRIVESC_WORDS  = ['privilege escalation', 'root', 'admin', 'elevated privileges']\n","DOS_WORDS      = ['denial of service', 'dos', 'crash', 'unavailable']\n","OVERFLOW_WORDS = ['buffer overflow', 'heap overflow', 'stack overflow', 'out-of-bounds']\n","\n","def extract_features(text):\n","    text_lower = text.lower()\n","    doc        = nlp(text_lower)\n","    entities   = [e.text for e in doc.ents if e.label_ in ['PRODUCT', 'ORG', 'GPE']]\n","    return {\n","        'entity_count':    len(entities),\n","        'entities':        ', '.join(entities[:5]),\n","        'has_remote':      int(any(w in text_lower for w in REMOTE_WORDS)),\n","        'has_unauth':      int(any(w in text_lower for w in UNAUTH_WORDS)),\n","        'has_exec':        int(any(w in text_lower for w in EXEC_WORDS)),\n","        'has_priv_esc':    int(any(w in text_lower for w in PRIVESC_WORDS)),\n","        'has_dos':         int(any(w in text_lower for w in DOS_WORDS)),\n","        'has_overflow':    int(any(w in text_lower for w in OVERFLOW_WORDS)),\n","        'desc_word_count': len(text.split())\n","    }\n","\n","# ── Encoding maps ──────────────────────────────────────────────────────\n","ATTACK_VECTOR_MAP = {'NETWORK': 3, 'ADJACENT': 2, 'LOCAL': 1, 'PHYSICAL': 0}\n","COMPLEXITY_MAP    = {'LOW': 1, 'HIGH': 0}\n","PRIVS_MAP         = {'NONE': 2, 'LOW': 1, 'HIGH': 0}\n","UI_MAP            = {'NONE': 1, 'REQUIRED': 0}\n","SCOPE_MAP         = {'CHANGED': 1, 'UNCHANGED': 0}\n","\n","def encode_metadata(df):\n","    df['attack_vector_enc']       = df['attack_vector'].map(ATTACK_VECTOR_MAP).fillna(0)\n","    df['attack_complexity_enc']   = df['attack_complexity'].map(COMPLEXITY_MAP).fillna(0)\n","    df['privileges_required_enc'] = df['privileges_required'].map(PRIVS_MAP).fillna(0)\n","    df['user_interaction_enc']    = df['user_interaction'].map(UI_MAP).fillna(0)\n","    df['scope_enc']               = df['scope'].map(SCOPE_MAP).fillna(0)\n","    return df\n","\n","print('All functions defined.')"],"execution_count":3,"outputs":[{"output_type":"stream","name":"stdout","text":["All functions defined.\n"]}]},{"cell_type":"markdown","metadata":{"id":"cell4_header"},"source":["## Cell 4 — Run preprocessing\n","\n","**Smart mode — automatically detects which rows need processing:**\n","- First run → processes all rows from scratch\n","- After updater → only processes new rows, appends to existing file"]},{"cell_type":"code","metadata":{"id":"cell4","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1774171184499,"user_tz":-330,"elapsed":59,"user":{"displayName":"Manglam Jaiswal","userId":"07075031299373953626"}},"outputId":"2b1c0095-7465-40b5-ccea-4a043ec79c15"},"source":["from tqdm import tqdm\n","tqdm.pandas()\n","\n","# ── Identify which rows need processing ───────────────────────────────\n","df_to_process = df_raw[~df_raw['cve_id'].isin(already_done)].copy()\n","df_to_process = df_to_process.reset_index(drop=True)\n","\n","print(f'Total raw rows:      {len(df_raw)}')\n","print(f'Already processed:   {len(already_done)}')\n","print(f'Rows to process now: {len(df_to_process)}')\n","\n","if len(df_to_process) == 0:\n","    print('\\nNothing new to process. Everything is up to date.')\n","else:\n","    # Step 1 — Clean text\n","    print('\\nStep 1/3 — Cleaning text...')\n","    df_to_process['description_clean'] = df_to_process['description'].apply(clean_text)\n","    print(f'  Done. Sample: {df_to_process[\"description_clean\"].iloc[0][:80]}...')\n","\n","    # Step 2 — NER + keyword features (slow step)\n","    print(f'\\nStep 2/3 — Extracting NLP features ({len(df_to_process)} rows)...')\n","    print('  This is the slow step — ~14 min per 100k rows on Colab')\n","    features_df = df_to_process['description'].progress_apply(\n","        lambda x: pd.Series(extract_features(x))\n","    )\n","    df_to_process = pd.concat([df_to_process, features_df], axis=1)\n","    print(f'  Done. Shape: {df_to_process.shape}')\n","\n","    # Step 3 — Encode metadata\n","    print('\\nStep 3/3 — Encoding CVSS metadata...')\n","    df_to_process = encode_metadata(df_to_process)\n","\n","    # Fix null entities\n","    df_to_process['entities'] = df_to_process['entities'].fillna('')\n","    print('  Done.')\n","\n","    # ── Combine with existing and save ────────────────────────────────\n","    if df_existing is not None:\n","        print(f'\\nAppending {len(df_to_process)} new rows to existing {len(df_existing)} rows...')\n","        df_final = pd.concat([df_existing, df_to_process], ignore_index=True)\n","    else:\n","        df_final = df_to_process\n","\n","    df_final.to_csv(f'{PROCESSED}/cves_processed.csv', index=False)\n","    print(f'\\nSaved {len(df_final)} rows to {PROCESSED}/cves_processed.csv')\n","    print(f'Total columns: {len(df_final.columns)}')\n","    print(f'Columns: {list(df_final.columns)}')"],"execution_count":4,"outputs":[{"output_type":"stream","name":"stdout","text":["Total raw rows:      200431\n","Already processed:   200431\n","Rows to process now: 0\n","\n","Nothing new to process. Everything is up to date.\n"]}]},{"cell_type":"markdown","metadata":{"id":"cell5_header"},"source":["## Cell 5 — Final verification"]},{"cell_type":"code","metadata":{"id":"cell5","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1774171190620,"user_tz":-330,"elapsed":6114,"user":{"displayName":"Manglam Jaiswal","userId":"07075031299373953626"}},"outputId":"475dfbe0-49d6-4e59-be8e-6081c6a2766e"},"source":["import pandas as pd\n","\n","df_check  = pd.read_csv(f'{PROCESSED}/cves_processed.csv')\n","raw_count = len(pd.read_csv(f'{RAW}/cves_raw.csv'))\n","\n","print(f'Processed rows:   {len(df_check)}')\n","print(f'Raw rows:         {raw_count}')\n","print(f'Rows match:       {len(df_check) == raw_count}')\n","print(f'Columns:          {len(df_check.columns)} (expected 24)')\n","print()\n","\n","# Null check\n","nulls = df_check.isnull().sum()\n","nulls = nulls[nulls > 0]\n","if len(nulls) == 0:\n","    print('Nulls:            None')\n","else:\n","    print(f'Nulls found:\\n{nulls}')\n","\n","# Feature stats\n","print()\n","print('Feature stats:')\n","print(df_check[['has_remote','has_unauth','has_exec',\n","                'has_priv_esc','has_dos','has_overflow']].mean().round(3))\n","\n","print()\n","if len(df_check) == raw_count and len(df_check.columns) == 24:\n","    print('All checks passed. Ready to run 04_embeddings.ipynb')\n","else:\n","    print('MISMATCH — check above and re-run Cell 4.')"],"execution_count":5,"outputs":[{"output_type":"stream","name":"stdout","text":["Processed rows:   200431\n","Raw rows:         200431\n","Rows match:       True\n","Columns:          24 (expected 24)\n","\n","Nulls found:\n","entities    144360\n","dtype: int64\n","\n","Feature stats:\n","has_remote      0.261\n","has_unauth      0.111\n","has_exec        0.339\n","has_priv_esc    0.133\n","has_dos         0.105\n","has_overflow    0.079\n","dtype: float64\n","\n","All checks passed. Ready to run 04_embeddings.ipynb\n"]}]}]}