sql py.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Connect to database\n",
    "\n",
    "Creating connection to Microsoft SQL Server and two databases with positive and negative sets. We should use generators for batch processing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pyodbc\n",
    "import numpy as np\n",
    "\n",
    "from pprint import pprint\n",
    "\n",
    "batch_size = 32"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def data_generator(constring, query):\n",
    "    cnxn = pyodbc.connect(constring)\n",
    "    cursor = cnxn.cursor()\n",
    "    cursor.execute(query)\n",
    "    \n",
    "    y, t = [], []\n",
    "    for row in cursor:\n",
    "        r_text = row.ttext\n",
    "        r_type = row.ttype\n",
    "        y.append(r_text)\n",
    "        t.append(r_type)\n",
    "        \n",
    "        if len(y) == batch_size:\n",
    "            npx = np.array(y)\n",
    "            npy = np.array(t)\n",
    "            yield npx, npy\n",
    "            y, t = [], []\n",
    "    pyodbc.Connection.close(cnxn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Tokenization\n",
    "We should encode words as their indexes (computed by overall frequency in the dataset).\n",
    "Using russian http://www.ruscorpora.ru/en/\n",
    "#### Step 1. Clear dataset. \n",
    "#### Step 2. Select meaningful words.\n",
    "#### Step 3. Calculate frequency of each word\n",
    "#### Step 4. Replace words by indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import nltk\n",
    "import string\n",
    "from nltk.corpus import stopwords\n",
    "from nltk import pos_tag, word_tokenize\n",
    "\n",
    "hash_map = {}\n",
    "max_features = 56000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Step 1. Clear dataset. \n",
    "Select words one by one. Symbols are meaningful because of smiles and emotions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def tokenize(file_text):\n",
    "    if file_text is not None:\n",
    "        #firstly let's apply nltk tokenization\n",
    "        tokens = nltk.word_tokenize(file_text)\n",
    "\n",
    "        #let's delete punctuation symbols\n",
    "        stop_words = ([',','\\\\','/','*','','-','http',';',':','@',',','.'])\n",
    "        tokens = [i for i in tokens if ( i not in stop_words )]\n",
    "\n",
    "        #deleting stop_words\n",
    "        #stop_words = stopwords.words('russian')\n",
    "        #stop_words.extend(['что', 'это', 'так', 'вот', 'быть', 'в', '—', 'к', 'на', 'http'])\n",
    "       \n",
    "       # tokens = [i for i in tokens if ( i not in stop_words )]\n",
    "\n",
    "        #cleaning words\n",
    "        #tokens = [i.replace(\"«\", \"\").replace(\"»\", \"\") for i in tokens]\n",
    "\n",
    "        return tokens\n",
    "    else:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will create a HashMap by using a Python dictionary to store the word frequencies of a book.\n",
    "A dictionary is an associative array (also known as hashes).\n",
    "Any key of the dictionary is associated, or mapped, to a value.\n",
    "The values of a dictionary can be any Python data type, so dictionaries are unordered key-value-pairs.\n",
    "\n",
    "By creating the dictionary, we will store the words as the keys and the value will represent the count. By doing this, we can retrieve any word without having to recount every single word."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\n",
    "def map_words(tokens):\n",
    "    \n",
    "    if tokens is not None:\n",
    "        for word in tokens:\n",
    "            word = word.lower()\n",
    "            # Word Exist?\n",
    "            if word in hash_map:\n",
    "                hash_map[word] = hash_map[word] + 1\n",
    "            else:\n",
    "                hash_map[word] = 1\n",
    "\n",
    "        return hash_map\n",
    "    else:\n",
    "        return None\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cnstr_positive = 'Trusted_Connection=yes;DRIVER={SQL Server};SERVER=DESKTOP-1RHDOBR\\GORDASQL;DATABASE=positive;UID=pyuser;PWD=pypypy'\n",
    "\n",
    "cnstr_negative = 'Trusted_Connection=yes;DRIVER={SQL Server};SERVER=DESKTOP-1RHDOBR\\GORDASQL;DATABASE=negative;UID=pyuser;PWD=pypypy'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def fill_frequency():\n",
    "    hash_map.clear()\n",
    "    \n",
    "    query_positive = \"SELECT [ttext], [ttype] FROM [dbo].[sortpos]\"\n",
    "    pdg = data_generator(cnstr_positive, query_positive)\n",
    "    \n",
    "    query_negative = \"SELECT [ttext], [ttype] FROM [dbo].[sortneg]\"\n",
    "    ndg = data_generator(cnstr_negative, query_negative)\n",
    "    \n",
    "    for current_positive_set in pdg:\n",
    "        for sentence in current_positive_set[0]:\n",
    "            words = tokenize(sentence)\n",
    "            map = map_words(words)\n",
    "            \n",
    "    for current_negative_set in ndg:\n",
    "        for sentence in current_negative_set[0]:\n",
    "            words = tokenize(sentence)\n",
    "            map = map_words(words)\n",
    "    \n",
    "    min_frequency = 2#max(map.values()) - max_features if max(map.values()) > max_features else 2\n",
    "    map = {key: value for key, value in map.items() if value > min_frequency}\n",
    "    return map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "map = fill_frequency()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check few words"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Word: [привет] Frequency: 969\n",
      "Word: [,] Frequency: 0\n",
      "Word: [дела] Frequency: 564\n",
      "Word: [)] Frequency: 151314\n",
      "Word: [(] Frequency: 181929\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "56166"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "word_list = ['привет',',','дела',')','(']\n",
    "\n",
    "for word in word_list:\n",
    "    print('Word: [' + word + '] Frequency: ' + str(map.get(word,0)))\n",
    "len(map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "from __future__ import print_function\n",
    "\n",
    "from keras.preprocessing import sequence\n",
    "from keras.models import Sequential\n",
    "from keras.layers import Dense, Embedding\n",
    "from keras.layers import LSTM\n",
    "from keras.datasets import imdb\n",
    "\n",
    "maxlen = 128  # cut texts after this number of words (among top max_features most common words)\n",
    "batch_size = 32"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def train_model():\n",
    "    max_frequency = max(map.values())\n",
    "    \n",
    "    train_cnt = 30000\n",
    "    #cnxn = pyodbc.connect(cnstr_positive)\n",
    "    #query = \"SELECT count(*) as cnt FROM [dbo].[mixedmessages]\"\n",
    "    #cursor = cnxn.cursor()\n",
    "    #cursor.execute(query)\n",
    "    #for row in cursor:\n",
    "    #    train_cnt = row.cnt/2\n",
    "    #pyodbc.Connection.close(cnxn)\n",
    "    \n",
    "    x_train, y_train, x_test, y_test = ([] for i in range(4))\n",
    "    \n",
    "    query = \"SELECT top 60000 [ttext], [ttype] FROM [dbo].[mixedmessages] order by newid()\"\n",
    "    mdg = data_generator(cnstr_positive, query)\n",
    "    \n",
    "    for current_set in mdg:\n",
    "        for sentence in current_set[0]:\n",
    "            words = tokenize(sentence)\n",
    "            w = []\n",
    "            #print(words)\n",
    "            for word in words:\n",
    "                #print(word, map.get(word,0))\n",
    "                w.append(map.get(word,0))\n",
    "            if len(x_train)<train_cnt:\n",
    "                x_train.append(w)\n",
    "            else:\n",
    "                x_test.append(w)\n",
    "                \n",
    "        for value in current_set[1]:\n",
    "            if len(y_train)<train_cnt:\n",
    "                y_train.append(value)\n",
    "            else:\n",
    "                y_test.append(value)\n",
    "                \n",
    "                       \n",
    "    x_train = np.array(x_train)\n",
    "    x_test = np.array(x_test)\n",
    "    y_train = np.array(y_train)\n",
    "    y_test = np.array(y_test)\n",
    "    #print(x_train)\n",
    "    return x_train, y_train, x_test, y_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "x_train, y_train, x_test, y_test = train_model()\n",
    "x_train = sequence.pad_sequences(x_train, maxlen=maxlen)\n",
    "x_test = sequence.pad_sequences(x_test, maxlen=maxlen)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Build model...\n"
     ]
    }
   ],
   "source": [
    "print('Build model...')\n",
    "model = Sequential()\n",
    "model.add(Embedding(max_features, 128))\n",
    "model.add(LSTM(128, dropout=0.2, recurrent_dropout=0.2))\n",
    "model.add(Dense(1, activation='sigmoid'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train...\n",
      "Train on 30000 samples, validate on 30000 samples\n",
      "Epoch 1/15\n",
      "30000/30000 [==============================] - 457s - loss: 0.6259 - acc: 0.6478 - val_loss: 0.6031 - val_acc: 0.6681\n",
      "Epoch 2/15\n",
      "30000/30000 [==============================] - 710s - loss: 0.5854 - acc: 0.6873 - val_loss: 0.5922 - val_acc: 0.6808\n",
      "Epoch 3/15\n",
      "30000/30000 [==============================] - 665s - loss: 0.5716 - acc: 0.6987 - val_loss: 0.5926 - val_acc: 0.6784\n",
      "Epoch 4/15\n",
      "30000/30000 [==============================] - 678s - loss: 0.5592 - acc: 0.7080 - val_loss: 0.5939 - val_acc: 0.6806\n",
      "Epoch 5/15\n",
      "30000/30000 [==============================] - 658s - loss: 0.5477 - acc: 0.7181 - val_loss: 0.5975 - val_acc: 0.6793\n",
      "Epoch 6/15\n",
      "30000/30000 [==============================] - 680s - loss: 0.5354 - acc: 0.7246 - val_loss: 0.5992 - val_acc: 0.6780\n",
      "Epoch 7/15\n",
      "30000/30000 [==============================] - 467s - loss: 0.5251 - acc: 0.7336 - val_loss: 0.6066 - val_acc: 0.6764\n",
      "Epoch 8/15\n",
      "30000/30000 [==============================] - 450s - loss: 0.5114 - acc: 0.7448 - val_loss: 0.6248 - val_acc: 0.6777\n",
      "Epoch 9/15\n",
      "30000/30000 [==============================] - 448s - loss: 0.4992 - acc: 0.7489 - val_loss: 0.6296 - val_acc: 0.6748\n",
      "Epoch 10/15\n",
      "30000/30000 [==============================] - 449s - loss: 0.4866 - acc: 0.7573 - val_loss: 0.6378 - val_acc: 0.6710\n",
      "Epoch 11/15\n",
      "30000/30000 [==============================] - 446s - loss: 0.4710 - acc: 0.7680 - val_loss: 0.6501 - val_acc: 0.6733\n",
      "Epoch 12/15\n",
      "30000/30000 [==============================] - 450s - loss: 0.4560 - acc: 0.7774 - val_loss: 0.6724 - val_acc: 0.6693\n",
      "Epoch 13/15\n",
      "30000/30000 [==============================] - 445s - loss: 0.4435 - acc: 0.7845 - val_loss: 0.6914 - val_acc: 0.6658\n",
      "Epoch 14/15\n",
      "30000/30000 [==============================] - 457s - loss: 0.4295 - acc: 0.7910 - val_loss: 0.7048 - val_acc: 0.6682\n",
      "Epoch 15/15\n",
      "30000/30000 [==============================] - 504s - loss: 0.4155 - acc: 0.7999 - val_loss: 0.7151 - val_acc: 0.6657\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<keras.callbacks.History at 0x22b74652cc0>"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model.compile(loss='binary_crossentropy',\n",
    "              optimizer='adam',\n",
    "              metrics=['accuracy'])\n",
    "\n",
    "print('Train...')\n",
    "model.fit(x_train, y_train,\n",
    "          batch_size=batch_size,\n",
    "          epochs=15,\n",
    "          validation_data=(x_test, y_test))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "30000/30000 [==============================] - 81s    \n",
      "Test score: 0.71510816261\n",
      "Test accuracy: 0.665666666667\n"
     ]
    }
   ],
   "source": [
    "score, acc = model.evaluate(x_test, y_test,\n",
    "                            batch_size=batch_size)\n",
    "print('Test score:', score)\n",
    "print('Test accuracy:', acc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "from keras.models import model_from_json\n",
    "\n",
    "#serialize model to JSON\n",
    "model_json = model.to_json()\n",
    "with open(\"model.json\", \"w\") as json_file:\n",
    "    json_file.write(model_json)\n",
    "#serialize weights to HDF5\n",
    "model.save_weights(\"model.h5\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#load json and create model\n",
    "json_file = open('model.json','r')\n",
    "loaded_model_json = json_file.read()\n",
    "json_file.close()\n",
    "loaded_model = model_from_json(loaded_model_json)\n",
    "#load weights into new model\n",
    "loaded_model.load_weights(\"model.h5\")\n",
    "loaded_model.compile(loss='binary_crossentropy',\n",
    "              optimizer='adam',\n",
    "              metrics=['accuracy'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def tokenize_message(d):\n",
    "    words = tokenize(d)\n",
    "    res = []\n",
    "    for word in words:\n",
    "        res.append(map.get(word,0))\n",
    "    ntm = np.array([res])\n",
    "    \n",
    "    nres = sequence.pad_sequences(ntm, maxlen=maxlen)\n",
    "        #res.append([word_2_ind[w] for w in sent.split(' ')])\n",
    "    return nres#np.pad(ntm, (maxlen-len(ntm),0),'constant', constant_values=(0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def getRating(value):\n",
    "    return {\n",
    "         value < 0.2: 'Крайне негативная оценка',\n",
    "         0.2 <= value < 0.4: 'Негативная оценка',\n",
    "         0.4 <= value < 0.6: 'Нейтральная оценка',\n",
    "         0.6 <= value < 0.8: 'Положительная оценка',\n",
    "         0.8 <= value:       'Крайне положительная оценка'\n",
    "    }[True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def assess(text):\n",
    "    inp = np.array(tokenize_message(text), dtype=np.int32)\n",
    "    p = model.predict(inp)\n",
    "    return getRating(p[0][0]), p[0][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('Крайне положительная оценка', 0.95532614)"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "message1 = 'Мне нравится фильм. Отличная игра актеров и интересный сюжет'\n",
    "assess(message1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('Нейтрально', 0.40854117)"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "message2 = 'Мне не нравится фильм. Хотя актеры сыграли отлично'\n",
    "assess(message2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('Негативная оценка', 0.24573153)"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "message2 = 'Мне не нравится фильм. Сюжет совсем неинтересный и актеры сыграли плохо'\n",
    "assess(message2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.30967087"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "inp = np.array(tokenize_message(data), dtype=np.int32)\n",
    "inp = np.array(tokenize_message(data), dtype=np.int32)\n",
    "p = model.predict(inp)\n",
    "p[0][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#import pymorphy2\n",
    "#morph = pymorphy2.MorphAnalyzer()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "celltoolbar": "Raw Cell Format",
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}