RFSQLIA.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3de01e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import sys\n",
    "import re\n",
    "import statistics\n",
    "from sklearn import tree\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from mlxtend.plotting import plot_confusion_matrix\n",
    "from sklearn.metrics import confusion_matrix\n",
    "from sklearn.model_selection import KFold\n",
    "from sklearn.metrics import accuracy_score\n",
    "from sklearn.metrics import f1_score\n",
    "from explainerdashboard import ClassifierExplainer, ExplainerDashboard\n",
    "import joblib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4c31ae26",
   "metadata": {},
   "outputs": [],
   "source": [
    "class SQLIAD:\n",
    "    def carga_Dataset(self, dataframe):\n",
    "        # El dataframe es creado con un dataset\n",
    "        self.df= pd.read_csv(dataframe)\n",
    "        \n",
    "    def carga_Dato(self, dato):\n",
    "        # Ingresar el dato que uno quiera que sea predecido\n",
    "        # Crea un dataframe con un dato especifico\n",
    "        # para que el modelo pueda hacer predicciones\n",
    "        self.carga = [dato]\n",
    "        self.df = pd.DataFrame()\n",
    "        self.df['Sentence'] = None\n",
    "        self.df['Sentence'] = self.carga\n",
    "        \n",
    "    def iden_Caract(self):\n",
    "        self.F1 = self.df['Sentence'].str.contains('select ', case=False)\n",
    "        self.F2 = self.df['Sentence'].str.contains('union', case=False)\n",
    "        self.F3 = self.df['Sentence'].str.contains('update', case=False)\n",
    "        self.F4 = self.df['Sentence'].str.contains('set', case=False)\n",
    "        self.F5 = self.df['Sentence'].str.contains('alter', case=False)\n",
    "        self.F6 = self.df['Sentence'].str.contains(' where ', case=False)\n",
    "        self.F7 = self.df['Sentence'].str.contains('like', case=False)\n",
    "        self.F8 = self.df['Sentence'].str.contains(' from ', case=False)\n",
    "        self.F9 = self.df['Sentence'].str.contains(' table ', case=False)\n",
    "        self.F10 = self.df['Sentence'].str.contains('database', case=False)\n",
    "        nuevas_columnas1 = pd.DataFrame({'F1': self.F1, 'F2': self.F2, 'F3': self.F3, 'F4': self.F4, 'F5': self.F5, \n",
    "                                         'F6': self.F6, 'F7': self.F7, 'F8': self.F8, 'F9': self.F9, 'F10': self.F10})\n",
    "        self.df = pd.concat([self.df, nuevas_columnas1], axis=1)\n",
    "        self.F11 = self.df['Sentence'].str.contains('drop ', case=False)\n",
    "        self.F12 = self.df['Sentence'].str.contains('delete ', case=False)\n",
    "        self.F13 = self.df['Sentence'].str.contains('insert ', case=False)\n",
    "        self.F14 = self.df['Sentence'].str.contains('And|Or', case=False, regex=True)\n",
    "        self.F15 = self.df['Sentence'].str.contains('null', case=False)\n",
    "        self.F16 = self.df['Sentence'].str.contains('=', case=False)\n",
    "        self.F17 = self.df['Sentence'].str.contains('information_schema', case=False)\n",
    "        self.F18 = self.df['Sentence'].str.contains('user', case=False)\n",
    "        self.F19 = self.df['Sentence'].str.contains('version', case=False)\n",
    "        self.F20 = self.df['Sentence'].str.contains('load_file', case=False)\n",
    "        nuevas_columnas2 = pd.DataFrame({'F11': self.F11, 'F12': self.F12, 'F13': self.F13, 'F14': self.F14, 'F15': self.F15, \n",
    "                                         'F16': self.F16, 'F17': self.F17, 'F18': self.F18, 'F19': self.F19, 'F20': self.F20})\n",
    "        self.df = pd.concat([self.df, nuevas_columnas2], axis=1)\n",
    "        self.F21 = self.df['Sentence'].str.contains('save', case=False)\n",
    "        self.F22 = self.df['Sentence'].str.contains('!|#|%|$|NUL|SOH|STX|ETX|EOT|@', case=False, regex=True)\n",
    "        self.F23 = self.df['Sentence'].str.contains('&', case=False, regex=False)\n",
    "        self.F24 = self.df['Sentence'].str.contains('|', case=False, regex=False)\n",
    "        self.F25 = self.df['Sentence'].str.contains(',', case=False)\n",
    "        self.F26 = self.df['Sentence'].str.contains(';', case=False)\n",
    "        self.F27 = self.df['Sentence'].str.contains('\\\\', case=False, regex=False)\n",
    "        self.F28 = self.df['Sentence'].str.contains('[-+*/]', case=False, regex=True)\n",
    "        self.F29 = self.df['Sentence'].str.contains('commit|rollback|grant|revoke|declare|remove', case=False, regex=True)\n",
    "        nuevas_columnas3 = pd.DataFrame({'F21': self.F21, 'F22': self.F22, 'F23': self.F23, 'F24': self.F24, 'F25': self.F25, \n",
    "                                         'F26': self.F26, 'F27': self.F27, 'F28': self.F28, 'F29': self.F29})\n",
    "        self.df = pd.concat([self.df, nuevas_columnas3], axis=1)\n",
    "        self.df['F30'] = self.df['Sentence'].str.count(';')\n",
    "        self.grouping_name = 'F30'\n",
    "        self.class_dic = {0: '0' , 1: '0'}\n",
    "        self.df[self.grouping_name] = self.df[self.grouping_name].map(self.class_dic)\n",
    "        self.df['F30'] = self.df['F30'].fillna('1')\n",
    "        self.F31 = self.df['Sentence'].str.contains('/*', case=False, regex=False)\n",
    "        self.F32 = self.df['Sentence'].str.contains('*/', case=False, regex=False)\n",
    "        self.F33 = self.df['Sentence'].str.contains('\\\\x', case=False, regex=False)\n",
    "        self.F34 = self.df['Sentence'].str.contains('\\\\u', case=False, regex=False)\n",
    "        self.F35 = self.df['Sentence'].str.contains('connection', case=False)\n",
    "        self.F36 = self.df['Sentence'].str.contains('xor', case=False)\n",
    "        self.F37 = self.df['Sentence'].str.contains('inner join', case=False)\n",
    "        self.F38 = self.df['Sentence'].str.contains('file|load_file|load_data_infile|into_outfile|into_dumpfile', case=False, regex=True)\n",
    "        self.F39 = self.df['Sentence'].str.contains('OS|Operative System|exec', case=False, regex=True)\n",
    "        self.F40 = self.df['Sentence'].str.count('STRING') + self.df['Sentence'].str.count('string')\n",
    "        nuevas_columnas4 = pd.DataFrame({'F31': self.F31, 'F32': self.F32, 'F33': self.F33, 'F34': self.F34, 'F35': self.F35, \n",
    "                                         'F36': self.F36, 'F37': self.F37, 'F38': self.F38, 'F39': self.F39, 'F40': self.F40})\n",
    "        self.df = pd.concat([self.df, nuevas_columnas4], axis=1)\n",
    "        self.F41 = self.df['Sentence'].str.count('SUBSTR') + self.df['Sentence'].str.count('substr')\n",
    "        self.F42 = self.df['Sentence'].str.count('SUBSTRING') + self.df['Sentence'].str.count('substring')\n",
    "        self.F43 = self.df['Sentence'].str.count('MID') + self.df['Sentence'].str.count('mid')\n",
    "        self.F44 = self.df['Sentence'].str.count('ASC') + self.df['Sentence'].str.count('asc')\n",
    "        self.F45 = self.df['Sentence'].str.count('<')\n",
    "        self.F46 = self.df['Sentence'].str.count('>')\n",
    "        self.F47 = self.df['Sentence'].str.count('\"')\n",
    "        self.F48 = self.df['Sentence'].str.count(\"'\")\n",
    "        self.F49 = self.df['Sentence'].str.contains('exists', case=False)\n",
    "        self.F50 = self.df['Sentence'].str.contains('floor', case=False)\n",
    "        nuevas_columnas5 = pd.DataFrame({'F41': self.F41, 'F42': self.F42, 'F43': self.F43, 'F44': self.F44, 'F45': self.F45, \n",
    "                                         'F46': self.F46, 'F47': self.F47, 'F48': self.F48, 'F49': self.F49, 'F50': self.F50})\n",
    "        self.df = pd.concat([self.df, nuevas_columnas5], axis=1)\n",
    "        self.F51 = self.df['Sentence'].str.contains('rand', case=False)\n",
    "        self.F52 = self.df['Sentence'].str.contains('group', case=False)\n",
    "        self.F53 = self.df['Sentence'].str.contains('order', case=False)\n",
    "        self.F54 = self.df['Sentence'].str.contains('lenght', case=False)\n",
    "        self.F55 = self.df['Sentence'].str.contains('ascii', case=False)\n",
    "        self.F56 = self.df['Sentence'].str.contains('if', case=False)\n",
    "        self.F57 = self.df['Sentence'].str.contains('count', case=False)\n",
    "        self.F58 = self.df['Sentence'].str.contains('sleep', case=False)\n",
    "        self.F59 = self.df['Sentence'].str.contains('between', case=False)\n",
    "        self.F60 = self.df['Sentence'].str.contains('values', case=False)\n",
    "        nuevas_columnas6 = pd.DataFrame({'F51': self.F51, 'F52': self.F52, 'F53': self.F53, 'F54': self.F54, 'F55': self.F55, \n",
    "                                         'F56': self.F56, 'F57': self.F57, 'F58': self.F58, 'F59': self.F59, 'F60': self.F60})\n",
    "        self.df = pd.concat([self.df, nuevas_columnas6], axis=1)\n",
    "        self.F61 = self.df['Sentence'].str.contains('delay', case=False)\n",
    "        self.F62 = self.df['Sentence'].str.contains('wait', case=False)\n",
    "        self.F63 = self.df['Sentence'].str.contains('benchmark', case=False)\n",
    "        self.F64 = self.df['Sentence'].str.contains('indentity_insert', case=False)\n",
    "        self.F65 = self.df['Sentence'].str.contains('truncate table', case=False)\n",
    "        self.F66 = self.df['Sentence'].str.contains('username | password', case=False, regex=True)\n",
    "        self.F67 = self.df['Sentence'].str.contains('user | pass', case=False, regex=True)\n",
    "        self.F68 = self.df['Sentence'].str.contains(\"')\", case=False, regex=False)\n",
    "        self.F69 = self.df['Sentence'].str.contains('limit', case=False)\n",
    "        self.F70 = self.df['Sentence'].str.contains('concat', case=False)\n",
    "        nuevas_columnas7 = pd.DataFrame({'F61': self.F61, 'F62': self.F62, 'F63': self.F63, 'F64': self.F64, 'F65': self.F65, \n",
    "                                         'F66': self.F66, 'F67': self.F67, 'F68': self.F68, 'F69': self.F69, 'F70': self.F70})\n",
    "        self.df = pd.concat([self.df, nuevas_columnas7], axis=1)\n",
    "        self.F71 = self.df['Sentence'].str.contains('ne', case=False)\n",
    "        self.F72 = self.df['Sentence'].str.contains('find', case=False)\n",
    "        self.F73 = self.df['Sentence'].str.contains('eq|gt|gte|lt|it|lte|ite|in|nin', case=False, regex=True)\n",
    "        self.F74 = self.df['Sentence'].str.contains('mod| regex | text', case=False, regex=True)\n",
    "        self.F75 = self.df['Sentence'].str.contains('return', case=False)\n",
    "        self.F76 = self.df['Sentence'].str.contains('return true|return 1', case=False, regex=True)\n",
    "        self.F77 = self.df['Sentence'].str.contains('exists', case=False)\n",
    "        self.F78 = self.df['Sentence'].str.contains('create', case=False, regex=True)\n",
    "        self.F79 = self.df['Sentence'].str.contains('show', case=False, regex=True)\n",
    "        self.F80 = self.df['Sentence'].str.contains('collection', case=False, regex=True)\n",
    "        self.F81 = self.df['Sentence'].str.contains('while(loop)', case=False, regex=False)\n",
    "        nuevas_columnas8 = pd.DataFrame({'F71': self.F71, 'F72': self.F72, 'F73': self.F73, 'F74': self.F74, 'F75': self.F75, \n",
    "                                        'F76': self.F76, 'F77': self.F77, 'F78': self.F78, 'F79': self.F79, 'F80': self.F80,\n",
    "                                        'F81': self.F81})\n",
    "        self.df = pd.concat([self.df, nuevas_columnas8], axis=1)\n",
    "        self.numro = 0\n",
    "        self.Igualdad = []\n",
    "        self.F83 = []\n",
    "        self.Numeros = []\n",
    "        self.Espacios = []\n",
    "        self.Bytes = []\n",
    "        self.Pyc = []\n",
    "        self.Especial = []\n",
    "        self.CantMayus = []\n",
    "        self.LectEscrit = []\n",
    "        self.Palabras_Clave = []\n",
    "        #Patrón de palabras clave sql\n",
    "        self.palabrasclave = [\"select\", \"drop\", \"alter\", \"delete\", \"insert\", \"union\", \"update\", \"set\", \n",
    "                                \"where\", \"like\", \" from\", \"table\", \"between\", \"order\", \"join\", \"create\"]\n",
    "        self.byte_utf8max = []\n",
    "        self.byte_utf8min = []\n",
    "        self.byte_utf8DE = []\n",
    "        for self.vx in self.df['Sentence']:\n",
    "            self.partesPyc = self.df['Sentence'][self.numro].split(';') #Identifica si existe texto despues de un ;\n",
    "            if len(self.partesPyc) > 1:\n",
    "                self.Pyc.append('True')\n",
    "            else:\n",
    "                self.Pyc.append('False')\n",
    "            self.texto =  [str(self.x) for self.x in self.df['Sentence']][self.numro].upper() #Identifica todos los caracteres\n",
    "            self.F83.append(len(self.texto))\n",
    "            self.Numeros.append(len([float(s) for s in re.findall(r'-?\\d+\\.?\\d*', self.df['Sentence'][self.numro])])) #Identifica todos los números\n",
    "            self.espac = 0\n",
    "            for i, char in enumerate(self.df['Sentence'][self.numro]): #Identifica cantidad de espacios\n",
    "                if char == ' ':\n",
    "                    self.espac += 1\n",
    "            self.Espacios.append(self.espac)\n",
    "            self.Especial.append(len([str(s) for s in re.findall(r'[^\\w\\s]', self.df['Sentence'][self.numro])])) #Identifica todos los caracteres especiales\n",
    "            self.baites = sys.getsizeof(self.df['Sentence'][self.numro]) #Identifica el peso en bytes del texto\n",
    "            self.Bytes.append(self.baites)\n",
    "            self.conmay = 0\n",
    "            for char in self.df['Sentence'][self.numro]: #Identifica cuantas mayusculas hay en el texto\n",
    "                if char.isupper():\n",
    "                    self.conmay += 1\n",
    "            self.CantMayus.append(self.conmay)\n",
    "            patron_lectura = r'\\b(?:{})\\b'.format('|'.join(map(re.escape, ['select', ' where', 'order by', 'limit', 'join'])))\n",
    "            patron_escritura = r'\\b(?:{})\\b'.format('|'.join(map(re.escape, ['create table', 'insert into', 'update', 'delete', 'alter table '])))\n",
    "            #Identitica los patrones anteriores y clasifica si el texto contiene \n",
    "            #comandos de escritura en sql, comandos de lectura, ambos o ninguno\n",
    "            if re.search(patron_lectura, self.df['Sentence'][self.numro], re.IGNORECASE) and re.search(patron_escritura, self.df['Sentence'][self.numro], re.IGNORECASE):\n",
    "                self.LectEscrit.append(0)\n",
    "            elif re.search(patron_lectura, self.df['Sentence'][self.numro], re.IGNORECASE) and not re.search(patron_escritura, self.df['Sentence'][self.numro], re.IGNORECASE):\n",
    "                self.LectEscrit.append(1)\n",
    "            elif re.search(patron_escritura, self.df['Sentence'][self.numro], re.IGNORECASE) and not re.search(patron_lectura, self.df['Sentence'][self.numro], re.IGNORECASE):\n",
    "                self.LectEscrit.append(2)\n",
    "            else:\n",
    "                self.LectEscrit.append(3)\n",
    "            #Verifica la cantidad de palabras clave de sql en el texto\n",
    "            self.palcla = 0\n",
    "            for palabra in self.palabrasclave:\n",
    "                self.patronPC = r'\\b' + re.escape(palabra) + r'\\b'\n",
    "                self.coincidencias = re.findall(self.patronPC, self.df['Sentence'][self.numro], re.IGNORECASE)\n",
    "                self.palcla += len(self.coincidencias)\n",
    "            self.Palabras_Clave.append(self.palcla)\n",
    "            bytes_utf8 = []\n",
    "            byte_utf8 = []\n",
    "            for caracter in self.texto:\n",
    "                bytes_utf8 = caracter.encode('utf-8') #Indica el caracter y el numero de byte en protocolo utf-8\n",
    "                byte_utf8.append(len(bytes_utf8)) #Indica solo el número de byte\n",
    "            self.byte_utf8max.append(max(byte_utf8))\n",
    "            self.byte_utf8min.append(min(byte_utf8))\n",
    "            self.lista = self.texto.split(None)\n",
    "            self.numro = self.numro + 1\n",
    "            self.elemento = '='  # elemento a buscar\n",
    "            self.posiciones = []  # nuestra lista para guardar las posiciones\n",
    "            self.posicion = -1  # empezaremos a buscar en posicion + 1 (que es 0 inicialmente)\n",
    "            self.yuyu = 0\n",
    "            try:\n",
    "                while True:\n",
    "                    # buscamos empezando a buscar desde la última posición encontrada\n",
    "                    self.posicion = self.lista.index(self.elemento, self.posicion+1)\n",
    "                    self.posiciones.append(self.posicion)\n",
    "                    self.un = self.lista[self.posicion-1]\n",
    "                    self.en = self.lista[self.posicion+1]\n",
    "                    if self.en == self.un:\n",
    "                        self.yuyu = 1\n",
    "            except:\n",
    "                pass  # no hacemos nada si index lanza ValueError\n",
    "            if self.yuyu == 1:\n",
    "                self.Igualdad.append('1')\n",
    "            else:\n",
    "                self.Igualdad.append('0')\n",
    "        self.df['F82'] = self.Igualdad\n",
    "        self.df['F82'] = self.df['F82'].astype(int)\n",
    "        self.df['F83'] = self.F83 #Cantidad de caracteres en el texto\n",
    "        self.df['F84'] = self.Numeros\n",
    "        self.df['F85'] = self.df['F84']/self.df['F83'] #Relacion numeros y texto completo\n",
    "        self.df['F86'] = self.Espacios\n",
    "        self.df['F87'] = self.df['F86']/self.df['F83'] #Relacion espacios y texto completo\n",
    "        self.df['F88'] = self.Especial\n",
    "        self.df['F89'] = self.df['F88']/self.df['F83'] #Relacion caracteres especiales y texto completo\n",
    "        #Cantidad de \"texto normal\" (no numeros, no caracteres especiales, no espacios)\n",
    "        self.df['F90'] = self.df['F83']-(self.df['F84']+self.df['F86']+self.df['F88'])\n",
    "        self.df['F91'] = self.Bytes\n",
    "        self.df['F92'] = self.Pyc\n",
    "        self.df['F92'] = self.df['F92'].astype(bool)\n",
    "        self.df['F93'] = self.CantMayus\n",
    "        self.df['F94'] = self.LectEscrit\n",
    "        self.df['F95'] = self.Palabras_Clave\n",
    "        self.df['F96'] = self.byte_utf8max\n",
    "        self.df['F97'] = self.byte_utf8min\n",
    "        self.F98 = self.df['Sentence'].str.contains('\\s{2,}', case=False, regex=True)\n",
    "        self.F99 = self.df['Sentence'].str.contains('\\dX', case=False, regex=True)\n",
    "        self.F100 = self.df['Sentence'].str.contains('Select\\s\\d', case=False, regex=True)\n",
    "        self.F101 = self.df['Sentence'].str.contains('version()', case=False, regex=False)\n",
    "        self.F102 = self.df['Sentence'].str.contains('server_version|--version|-v|locate bin', case=False, regex=True)\n",
    "        self.F103 = self.df['Sentence'].str.count('null')\n",
    "        self.F104 = self.df['Sentence'].str.count('=')\n",
    "        self.F105 = self.df['Sentence'].str.contains('query', case=False)\n",
    "        self.F106 = self.df['Sentence'].str.contains('union all', case=False)\n",
    "        self.F107 = self.df['Sentence'].str.contains('mysobjects|sysobjects', case=False, regex=True)\n",
    "        self.F108 = self.df['Sentence'].str.contains('if', case=False)\n",
    "        self.F109 = self.df['Sentence'].str.contains(\"'''\", case=False)\n",
    "        self.F110 = self.df['Sentence'].str.contains('Cast|Convert|Collate', case=False, regex=True)\n",
    "        nuevas_columnas9 = pd.DataFrame({'F98': self.F98, 'F99': self.F99, 'F100': self.F100, 'F101': self.F101, 'F102': \n",
    "                                          self.F102, 'F103': self.F103, 'F104': self.F104, 'F105': self.F105, 'F106': self.F106, \n",
    "                                          'F107': self.F107, 'F108': self.F108, 'F109': self.F109, 'F110': self.F110})\n",
    "        self.df = pd.concat([self.df, nuevas_columnas9], axis=1)\n",
    "        self.F111 = self.df['Sentence'].str.contains('; set identity_insert', case=False, regex=False)\n",
    "        self.F112 = self.df['Sentence'].str.contains('; truncate table', case=False, regex=False)\n",
    "        self.F113 = self.df['Sentence'].str.contains('; drop table', case=False, regex=False)\n",
    "        self.F114 = self.df['Sentence'].str.contains('; update', case=False, regex=False)\n",
    "        self.F115 = self.df['Sentence'].str.contains('; insert into', case=False, regex=False)\n",
    "        self.F116 = self.df['Sentence'].str.contains('; delete', case=False, regex=False)\n",
    "        self.F117 = self.df['Sentence'].str.contains('; insert', case=False, regex=False)\n",
    "        self.F118 = self.df['Sentence'].str.contains(\"''))/*&\", case=False, regex=False)\n",
    "        self.F119 = self.df['Sentence'].str.contains('/*&', case=False, regex=False)\n",
    "        self.F120 = self.df['Sentence'].str.contains('And\\s\\d', case=False, regex=True)\n",
    "        nuevas_columnas10 = pd.DataFrame({'F111': self.F111, 'F112': self.F112, 'F113': self.F113, 'F114': self.F114, \n",
    "                                          'F115': self.F115, 'F116': self.F116, 'F117': self.F117, 'F118': self.F118, \n",
    "                                          'F119': self.F119, 'F120': self.F120})\n",
    "        self.df = pd.concat([self.df, nuevas_columnas10], axis=1)\n",
    "        self.F121 = self.df['Sentence'].str.contains(';]/', case=False, regex=False)\n",
    "        self.F122 = self.df['Sentence'].str.contains('createtable()', case=False, regex=False)\n",
    "        self.F123 = self.df['Sentence'].str.contains('showtable()', case=False, regex=False)\n",
    "        self.F124 = self.df['Sentence'].str.contains('createcollection()', case=False, regex=False)\n",
    "        self.F125 = self.df['Sentence'].str.contains('drop()', case=False, regex=False)\n",
    "        self.F126 = self.df['Sentence'].str.contains('dropdatabase()', case=False, regex=False)\n",
    "        #Detecta si existe Or seguido de otra palabra\n",
    "        self.F127 = self.df['Sentence'].str.contains(r'or\\s+\\S+', case=False, regex=True)\n",
    "        self.F128 = self.df['Sentence'].str.contains(r'%\\s+\\S+', case=False, regex=True)\n",
    "        #Detecta si existen Prepocisiones\n",
    "        self.F129 = self.df['Sentence'].str.contains(' a | ante | bajo | cabe | con | contra | de | desde | durante | en | entre | hacia | hasta | mediante | para | por | según | sin | so | sobre | tras | versus | vía | aboard | about | above | across | after | against | along  | amid | among | anti | around | as | at | before | behindbelow | beneath | beside | besides | between | beyond | but | by | concerning | considering | despite | down | during | except | excepting | excluding | following | for | from | in | inside | into | like | minus | near | of | off | on | onto | opposite | outside | over | past | upon | versus | via | with | within | without ', case=False, regex=True)\n",
    "        #Detecta si existen Articulos\n",
    "        self.F130 = self.df['Sentence'].str.contains(' el | la | lo | los | las | un | una | unos | unas | a | an | the ', case=False, regex=True)\n",
    "        nuevas_columnas11 = pd.DataFrame({'F121': self.F121, 'F122': self.F122, 'F123': self.F123, 'F124': self.F124, \n",
    "                                          'F125': self.F125, 'F126': self.F126, 'F127': self.F127, 'F128': self.F128, \n",
    "                                          'F129': self.F129, 'F130': self.F130})\n",
    "        self.df = pd.concat([self.df, nuevas_columnas11], axis=1)\n",
    "        \n",
    "    def cargar_Caract(self):\n",
    "        #Se seleccionan las caracterisitcas del modelo\n",
    "        self.data = self.df[['Label','F1', 'F2', 'F3', 'F4', 'F5', 'F6', 'F7', 'F8', 'F9', 'F10', \n",
    "                      'F11', 'F12', 'F13', 'F14', 'F15', 'F16', 'F17', 'F18', 'F19', 'F20',\n",
    "                      'F21', 'F22', 'F23', 'F24', 'F25', 'F26', 'F27', 'F28', 'F29', 'F30',\n",
    "                      'F31', 'F32', 'F33', 'F34', 'F35', 'F36', 'F37', 'F38', 'F39', 'F40',\n",
    "                      'F41', 'F42', 'F43', 'F44', 'F45', 'F46', 'F47', 'F48', 'F49', 'F50',\n",
    "                      'F51', 'F52', 'F53', 'F54', 'F55', 'F56', 'F57', 'F58', 'F59', 'F60',\n",
    "                      'F61', 'F62', 'F63', 'F64', 'F65', 'F66', 'F67', 'F68', 'F69', 'F70',\n",
    "                      'F71', 'F72', 'F73', 'F74', 'F75', 'F76', 'F77', 'F78', 'F79', 'F80',\n",
    "                      'F81', 'F82', 'F83', 'F84', 'F85', 'F86', 'F87', 'F88', 'F89', 'F90',\n",
    "                      'F91', 'F92', 'F93', 'F94', 'F95', 'F96', 'F97', 'F98', 'F99', 'F100',\n",
    "                      'F101', 'F102', 'F103', 'F104', 'F105', 'F106', 'F107', 'F108', 'F109', 'F110',\n",
    "                      'F111', 'F112', 'F113', 'F114', 'F115', 'F116', 'F117', 'F118', 'F119', 'F120',\n",
    "                      'F121', 'F122', 'F123', 'F124', 'F125', 'F126', 'F127', 'F128', 'F129', 'F130', \n",
    "                     ]]\n",
    "        \n",
    "    def carga_Features(self):\n",
    "        # Guardamos solo las caracteristicas para que el modelo pueda hacer la prediccion\n",
    "        self.Xy = self.df.drop([\"Sentence\"],axis = 1)\n",
    "        \n",
    "    def div_Datos(self):\n",
    "        # Dividimos los datos en entrenamiento y prueba\n",
    "        # X son nuestras variables independientes\n",
    "        self.X = self.data.drop([\"Label\"],axis = 1)\n",
    "        \n",
    "        # y es nuestra variable dependiente\n",
    "        self.y = self.data.Label\n",
    "        \n",
    "        # División 75% de datos para entrenamiento, 25% de datos para test\n",
    "        self.X_train, self.X_test, self.y_train, self.y_test = train_test_split(self.X, self.y, random_state=0)\n",
    "        \n",
    "    def div_Datos(self):\n",
    "        # Dividimos los datos en entrenamiento y prueba\n",
    "        # X son nuestras variables independientes\n",
    "        self.X = self.data.drop([\"Label\"],axis = 1)\n",
    "        \n",
    "        # y es nuestra variable dependiente\n",
    "        self.y = self.data.Label\n",
    "        \n",
    "        # División 75% de datos para entrenamiento, 25% de daatos para test\n",
    "        self.X_train, self.X_test, self.y_train, self.y_test = train_test_split(self.X, self.y,random_state=0)\n",
    "        \n",
    "    def k_Fold(self):\n",
    "        # Dividimos los datos en entrenamiento y prueba\n",
    "        # X son nuestras variables independientes\n",
    "        self.X = self.data.drop([\"Label\"],axis = 1)\n",
    "        \n",
    "        # y es nuestra variable dependiente\n",
    "        self.y = self.data.Label\n",
    "        \n",
    "        K = 5  # Puedes ajustar K según tus necesidades\n",
    "        kf = KFold(n_splits=K, shuffle=True, random_state=0)\n",
    "        \n",
    "        scores = []\n",
    "        \n",
    "        for train_index, test_index in kf.split(self.X):\n",
    "            self.X_train, self.X_test = self.X.iloc[train_index], self.X.iloc[test_index]\n",
    "            self.y_train, self.y_test = self.y.iloc[train_index], self.y.iloc[test_index]\n",
    "        \n",
    "            # Crea y entrena un modelo de árbol de decisión\n",
    "            self.model = RandomForestClassifier(n_estimators = 20, random_state = 2016,)\n",
    "            self.model.fit(self.X_train, self.y_train)\n",
    "        \n",
    "            # Realiza predicciones en el conjunto de prueba\n",
    "            self.y_pred = self.model.predict(self.X_test)\n",
    "        \n",
    "            # Evalúa el rendimiento del modelo (puedes usar métricas como precisión, F1-score, etc.)\n",
    "            accuracy = accuracy_score(self.y_test, self.y_pred)\n",
    "            scores.append(accuracy)\n",
    "            print('Precición de K-Fold por iteración', accuracy)\n",
    "        \n",
    "        # Calcula el promedio de las puntuaciones de precisión de los pliegues\n",
    "        average_accuracy = np.mean(scores)\n",
    "        print(f'Precisión promedio en {K}-Fold Cross-Validation: {average_accuracy}')\n",
    "    \n",
    "    def creacion_RF(self):\n",
    "        # Creamos el modelo Random Forest (se configura el numero de aboles, \n",
    "        # numero minimo de hojas y profundidad maxima)\n",
    "        self.BA_model = RandomForestClassifier(n_estimators = 20,\n",
    "                                    random_state = 2016,)\n",
    "                                    #min_samples_leaf = 10,\n",
    "                                    #max_depth=50,)\n",
    "        \n",
    "    def entren_RF(self):\n",
    "        # El modelo es entrenado\n",
    "        self.BA_model.fit(self.X_train, self.y_train)\n",
    "        \n",
    "    def accuracy_Entren(self):\n",
    "        # Muestra el accuracy de los datos de entrenamiento\n",
    "        print(\"Accuracy de entrenamiento: \", self.BA_model.score(self.X_train, self.y_train))\n",
    "        \n",
    "    def f1_score_Entren(self):\n",
    "        # Muestra el F1_score de los datos de entrenamiento\n",
    "        self.y_pred = self.BA_model.predict(self.X_train)\n",
    "        print('F1 score:', f1_score(self.y_train, self.y_pred))\n",
    "        \n",
    "    def matriz_Conf_Ent(self):\n",
    "        # Muestra la matriz de confusion de los datos de entrenamiento\n",
    "        self.y_pred = self.BA_model.predict(self.X_train)\n",
    "        self.matriz = confusion_matrix(self.y_train,self.y_pred)\n",
    "\n",
    "        plot_confusion_matrix(conf_mat=self.matriz, figsize=(4,4), show_normed=False)\n",
    "        plt.tight_layout()\n",
    "        \n",
    "    def accuracy_Prueba(self):\n",
    "        # Muestra el accuracy de los datos de prueba\n",
    "        print(\"Accuracy de prueba: \", self.BA_model.score(self.X_test, self.y_test))\n",
    "        \n",
    "    def f1_score_Prueba(self):\n",
    "        # Muestra el F1_score de los datos de prueba\n",
    "        self.y_pred = self.BA_model.predict(self.X_test)\n",
    "        print('F1 score:', f1_score(self.y_test, self.y_pred))\n",
    "        \n",
    "    def matriz_Conf_Prue(self):\n",
    "        # Muestra la matriz de confusion de los datos de prueba\n",
    "        self.y_pred = self.BA_model.predict(self.X_test)\n",
    "        self.matriz = confusion_matrix(self.y_test,self.y_pred)\n",
    "\n",
    "        plot_confusion_matrix(conf_mat=self.matriz, figsize=(4,4), show_normed=False)\n",
    "        plt.tight_layout()\n",
    "        \n",
    "    def carga_Modelo(self, modelo):\n",
    "        # El modelo se carga\n",
    "        # El modelo debe de ser un .pkl\n",
    "        self.BA_model = joblib.load(modelo)\n",
    "        \n",
    "    def prediccion(self):\n",
    "        # El modelo hace la prediccion final\n",
    "        self.pred = self.BA_model.predict(self.Xy)\n",
    "        print('La predicción final fue: ',self.pred)\n",
    "        \n",
    "    def expDash(self):\n",
    "        # Convierte las features a int y luego a float\n",
    "        self.X = self.X.astype(int)\n",
    "        self.X = self.X.astype(float)\n",
    "        # Llama al explainer dashboard\n",
    "        explainer = ClassifierExplainer(self.BA_model, self.X, self.y)\n",
    "        ExplainerDashboard(explainer).run(port=8051)\n",
    "        \n",
    "    def guardar_Modelo(self, nomMod):\n",
    "        # Guarda el modelo entrenado\n",
    "        # Es necesario ingresar el nombre del modelo y añadir \".plk\" al final\n",
    "        joblib.dump(self.BA_model, nomMod)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba6d91dd",
   "metadata": {},
   "source": [
    "# Entrenamiento del modelo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91107007",
   "metadata": {},
   "outputs": [],
   "source": [
    "rf = SQLIAD()\n",
    "rf.carga_Dataset('Nombre_dataset.csv')\n",
    "rf.iden_Caract()\n",
    "rf.cargar_Caract()\n",
    "rf.div_Datos()\n",
    "rf.creacion_RF()\n",
    "rf.entren_RF()\n",
    "rf.accuracy_Entren()\n",
    "rf.matriz_Conf_Ent()\n",
    "rf.accuracy_Prueba()\n",
    "rf.matriz_Conf_Prue()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4614d7af",
   "metadata": {},
   "source": [
    "# Comprobación con cross-validation (Accuracy)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb051e23",
   "metadata": {},
   "outputs": [],
   "source": [
    "rfc = SQLIAD()\n",
    "rfc.carga_Dataset('Nombre_dataset.csv')\n",
    "rfc.iden_Caract()\n",
    "rfc.cargar_Caract()\n",
    "rfc.k_Fold()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cca4e017",
   "metadata": {},
   "source": [
    "# Predicción"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3dea0c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# rf = SQLIAD()\n",
    "rf.carga_Dato('Dato a predecir')\n",
    "rf.iden_Caract()\n",
    "rf.carga_Features()\n",
    "# rf.carga_modelo('Nombre_modelo.pkl') # Esta linea solo es necesaria si quiere cargar un modelo\n",
    "rf.prediccion()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3776e7a",
   "metadata": {},
   "source": [
    "# Guardar modelo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0e38483",
   "metadata": {},
   "outputs": [],
   "source": [
    "rf.guardar_Modelo('Nombre_modelo.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e19fa272",
   "metadata": {},
   "source": [
    "# Cargar modelo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d96777f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "rf.carga_Modelo('Nombre_modelo.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "408d19f4",
   "metadata": {},
   "source": [
    "# Explainerdasboard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e7d2ad33",
   "metadata": {},
   "outputs": [],
   "source": [
    "rf.expDash()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "828a1b23",
   "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}