read_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "from sqlalchemy import create_engine\n",
    "from datetime import datetime\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2019, 1, 2, 11, 51, 48, 549697)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dt.datetime.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2019, 1, 2, 11, 51, 49, 156418)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datetime.now()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As SQLite connects to local files, the URL format is slightly different. The “file” portion of the URL is the filename of the database. For a relative file path, this requires three slashes:\n",
    "\n",
    "# sqlite://<nohostname>/<path>\n",
    "# where <path> is relative:\n",
    "engine = create_engine('sqlite:///foo.db')\n",
    "\n",
    "And for an absolute file path, the three slashes are followed by the absolute path:\n",
    "\n",
    "#Unix/Mac - 4 initial slashes in total\n",
    "engine = create_engine('sqlite:////absolute/path/to/foo.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Already Exists\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "#Creating db file from csv\n",
    "#Learn SQL: https://www.w3schools.com/sql/default.asp\n",
    "if not os.path.isfile('/data/so_pred/test.db'):\n",
    "    start = datetime.now()\n",
    "    disk_engine = create_engine('sqlite:////data/so_pred/test.db')\n",
    "    #start = dt.datetime.now()\n",
    "    chunksize = 180000\n",
    "    j = 0\n",
    "    index_start = 1\n",
    "    for df in pd.read_csv('/data/so_pred/Test.csv', names=['Id', 'Title', 'Body'], chunksize=chunksize, iterator=True, encoding='utf-8', ):\n",
    "        df.index += index_start\n",
    "        j+=1\n",
    "        print('{} rows'.format(j*chunksize))\n",
    "        df.to_sql('data', disk_engine, if_exists='append')\n",
    "        index_start = df.index[-1] + 1\n",
    "    print \"Time taken :\", datetime.now() - start\n",
    "else:\n",
    "    print \"Already Exists\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows in the database : \n",
      "2013338\n",
      "Time taken  : 0:00:07.180546\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "if os.path.isfile('/data/so_pred/test.db'):\n",
    "    start = datetime.now()\n",
    "    con = sqlite3.connect('/data/so_pred/test.db')\n",
    "    num_rows = pd.read_sql_query(\"\"\"SELECT count(*) FROM data\"\"\", con)\n",
    "    #Always remember to close the database\n",
    "    print \"Number of rows in the database :\",\"\\n\",num_rows['count(*)'].values[0]\n",
    "    con.close()\n",
    "    print \"Time taken  :\", datetime.now() - start\n",
    "else:\n",
    "    print \"Error !! DB file not found\"\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   index       Id                                              Title  \\\n",
      "0      2  6034196               Getting rid of site-specific hotkeys   \n",
      "1      3  6034197  Nodes inside Cisco VPN. Incoming SSH requests ...   \n",
      "2      4  6034198  Remove old vCenter servers from VMWare vSphere...   \n",
      "3      5  6034199    Replace <span> element with var containing html   \n",
      "4      6  6034200      Will PHP included html content affect my seo?   \n",
      "\n",
      "                                                Body  \n",
      "0  <p>How do I disable site-specific hotkeys if (...  \n",
      "1  <p>I've a gateway-to-gateway VPN setup between...  \n",
      "2  <p>After changing our vCenter servers recently...  \n",
      "3  <p>I have a variable i lifted the contents of,...  \n",
      "4  <p>Today i purchase a small CMS system. With t...  \n",
      "\n",
      " Time taken : 0:00:00.021419\n"
     ]
    }
   ],
   "source": [
    "if os.path.isfile('/data/so_pred/test.db'):\n",
    "    start = datetime.now()\n",
    "    con = sqlite3.connect('/data/so_pred/test.db')\n",
    "    res = pd.read_sql_query(\"\"\"SELECT * FROM data LIMIT 1,5\"\"\", con)\n",
    "    #Always remember to close the database\n",
    "    #print(\"Number of rows in the database :\",\"\\n\",num_rows['count(*)'].values[0])\n",
    "    print(res)\n",
    "    con.close()\n",
    "    print \"\\n Time taken :\", datetime.now() - start\n",
    "else:\n",
    "    print \"Error !! DB file not found\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Id                                              Title  \\\n",
      "0  6034196               Getting rid of site-specific hotkeys   \n",
      "1  6034197  Nodes inside Cisco VPN. Incoming SSH requests ...   \n",
      "2  6034198  Remove old vCenter servers from VMWare vSphere...   \n",
      "3  6034199    Replace <span> element with var containing html   \n",
      "4  6034200      Will PHP included html content affect my seo?   \n",
      "\n",
      "                                                Body  \n",
      "0  <p>How do I disable site-specific hotkeys if (...  \n",
      "1  <p>I've a gateway-to-gateway VPN setup between...  \n",
      "2  <p>After changing our vCenter servers recently...  \n",
      "3  <p>I have a variable i lifted the contents of,...  \n",
      "4  <p>Today i purchase a small CMS system. With t...  \n",
      "\n",
      " Time taken : 0:00:00.006649\n"
     ]
    }
   ],
   "source": [
    "start = datetime.now()\n",
    "test_data = pd.read_csv('/data/so_pred/Test.csv',nrows=5)\n",
    "print test_data.head()\n",
    "print \"\\n Time taken :\", datetime.now() - start"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5, 3)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "da.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"<p>I'd like to check if an uploaded file is an image file (e.g png, jpg, jpeg, gif, bmp) or another file. The problem is that I'm using Uploadify to upload the files, which changes the mime type and gives a 'text/octal' or something as the mime type, no matter which file type you upload.</p>\\n\\n<p>Is there a way to check if the uploaded file is an image apart from checking the file extension using PHP?</p>\\n\""
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "da.iloc[0]['Body']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'<p>In my favorite editor (vim), I regularly use ctrl-w to execute a certain action. Now, it quite often happens to me that firefox is the active window (on windows) while I still look at vim (thinking vim is the active window) and press ctrl-w which closes firefox. This is not what I want. Is there a way to stop ctrl-w from closing firefox?</p>\\n\\n<p>Rene</p>\\n'"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "da.iloc[1]['Body']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'Id', u'Title', u'Body', u'Tags'], dtype='object')"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "da.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "dup_group = da.groupby(['Title','Body','Tags'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'dict' object has no attribute 'count'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-68-9571f4af05a9>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdup_group\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgroups\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcount\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m: 'dict' object has no attribute 'count'"
     ]
    }
   ],
   "source": [
    "dup_group.groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-01-01 15:37:59.471578\n"
     ]
    }
   ],
   "source": [
    "s = datetime.now()\n",
    "print s "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Train Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "180000 rows\n",
      "360000 rows\n",
      "540000 rows\n",
      "720000 rows\n",
      "900000 rows\n",
      "1080000 rows\n",
      "1260000 rows\n",
      "1440000 rows\n",
      "1620000 rows\n",
      "1800000 rows\n",
      "1980000 rows\n",
      "2160000 rows\n",
      "2340000 rows\n",
      "2520000 rows\n",
      "2700000 rows\n",
      "2880000 rows\n",
      "3060000 rows\n",
      "3240000 rows\n",
      "3420000 rows\n",
      "3600000 rows\n",
      "3780000 rows\n",
      "3960000 rows\n",
      "4140000 rows\n",
      "4320000 rows\n",
      "4500000 rows\n",
      "4680000 rows\n",
      "4860000 rows\n",
      "5040000 rows\n",
      "5220000 rows\n",
      "5400000 rows\n",
      "5580000 rows\n",
      "5760000 rows\n",
      "5940000 rows\n",
      "6120000 rows\n",
      "Time taken : 0:03:20.958242\n"
     ]
    }
   ],
   "source": [
    "if not os.path.isfile('/data/so_pred/train.db'):\n",
    "    start = datetime.now()\n",
    "    disk_engine = create_engine('sqlite:////data/so_pred/train.db')\n",
    "    #start = dt.datetime.now()\n",
    "    chunksize = 180000\n",
    "    j = 0\n",
    "    index_start = 1\n",
    "    for df in pd.read_csv('/data/so_pred/Train.csv', names=['Id', 'Title', 'Body', 'Tags'], chunksize=chunksize, iterator=True, encoding='utf-8', ):\n",
    "        df.index += index_start\n",
    "        j+=1\n",
    "        print('{} rows'.format(j*chunksize))\n",
    "        df.to_sql('data', disk_engine, if_exists='append')\n",
    "        index_start = df.index[-1] + 1\n",
    "    print \"Time taken :\", datetime.now() - start\n",
    "else:\n",
    "    print \"Already Exists\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Id                                              Title  \\\n",
      "0   1  How to check if an uploaded file is an image w...   \n",
      "1   2  How can I prevent firefox from closing when I ...   \n",
      "2   3           R Error Invalid type (list) for variable   \n",
      "3   4      How do I replace special characters in a URL?   \n",
      "4   5               How to modify whois contact details?   \n",
      "\n",
      "                                                Body  \\\n",
      "0  <p>I'd like to check if an uploaded file is an...   \n",
      "1  <p>In my favorite editor (vim), I regularly us...   \n",
      "2  <p>I am import matlab file and construct a dat...   \n",
      "3  <p>This is probably very simple, but I simply ...   \n",
      "4  <pre><code>function modify(.......)\\n{\\n  $mco...   \n",
      "\n",
      "                                                Tags  \n",
      "0  php image-processing file-upload upload mime-t...  \n",
      "1                                            firefox  \n",
      "2                          r matlab machine-learning  \n",
      "3                                    c# url encoding  \n",
      "4                          php api file-get-contents  \n",
      "\n",
      " Time taken : 0:00:00.013386\n"
     ]
    }
   ],
   "source": [
    "start = datetime.now()\n",
    "train_sample = pd.read_csv('/data/so_pred/Train.csv',nrows=100)\n",
    "print train_sample.head()\n",
    "print \"\\n Time taken :\", datetime.now() - start"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(100, 4)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_sample.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_sample.to_csv('Train_sample.csv',index=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "d = pd.read_csv('Train_sample.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(100, 4)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "Execution failed on sql 'SELECT Title, Body, Tags, COUNT(*) as cnt_dup FROM data GROUP BY Title, Body, Tags': database or disk is full",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-26-25f4282cdc4f>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      2\u001b[0m     \u001b[0mstart\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnow\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m     \u001b[0mcon\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msqlite3\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'/data/so_pred/train.db'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 4\u001b[0;31m     \u001b[0mdf_no_dup\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_sql_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'SELECT Title, Body, Tags, COUNT(*) as cnt_dup FROM data GROUP BY Title, Body, Tags'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcon\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      5\u001b[0m     \u001b[0mcon\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m     \u001b[0;32mprint\u001b[0m \u001b[0;34m\"Time taken  :\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnow\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m-\u001b[0m \u001b[0mstart\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/anaconda2/lib/python2.7/site-packages/pandas/io/sql.pyc\u001b[0m in \u001b[0;36mread_sql_query\u001b[0;34m(sql, con, index_col, coerce_float, params, parse_dates, chunksize)\u001b[0m\n\u001b[1;32m    330\u001b[0m     return pandas_sql.read_query(\n\u001b[1;32m    331\u001b[0m         \u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindex_col\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mindex_col\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcoerce_float\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcoerce_float\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 332\u001b[0;31m         parse_dates=parse_dates, chunksize=chunksize)\n\u001b[0m\u001b[1;32m    333\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    334\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/anaconda2/lib/python2.7/site-packages/pandas/io/sql.pyc\u001b[0m in \u001b[0;36mread_query\u001b[0;34m(self, sql, index_col, coerce_float, params, parse_dates, chunksize)\u001b[0m\n\u001b[1;32m   1442\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1443\u001b[0m         \u001b[0margs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_convert_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1444\u001b[0;31m         \u001b[0mcursor\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1445\u001b[0m         \u001b[0mcolumns\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mcol_desc\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mcol_desc\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdescription\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1446\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/anaconda2/lib/python2.7/site-packages/pandas/io/sql.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1419\u001b[0m             ex = DatabaseError(\n\u001b[1;32m   1420\u001b[0m                 \"Execution failed on sql '%s': %s\" % (args[0], exc))\n\u001b[0;32m-> 1421\u001b[0;31m             \u001b[0mraise_with_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mex\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1422\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1423\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0mstaticmethod\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/anaconda2/lib/python2.7/site-packages/pandas/io/sql.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1407\u001b[0m                 \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1408\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1409\u001b[0;31m                 \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1410\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mcur\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1411\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDatabaseError\u001b[0m: Execution failed on sql 'SELECT Title, Body, Tags, COUNT(*) as cnt_dup FROM data GROUP BY Title, Body, Tags': database or disk is full"
     ]
    }
   ],
   "source": [
    "if os.path.isfile('/data/so_pred/train.db'):\n",
    "    start = datetime.now()\n",
    "    con = sqlite3.connect('/data/so_pred/train.db')\n",
    "    df_no_dup = pd.read_sql_query('SELECT Title, Body, Tags, COUNT(*) as cnt_dup FROM data GROUP BY Title, Body, Tags', con)\n",
    "    con.close()\n",
    "    print \"Time taken  :\", datetime.now() - start\n",
    "else:\n",
    "    print \"Error !! DB file not found\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"number of duplicate questions :\", num_rows['count(*)'].values[0]- df_no_dup.shape[0], \"(\",(1-((df_no_dup.shape[0])/(num_rows['count(*)'].values[0])))*100,\"% )\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}