Exp1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~chris/7365.embed\" height=\"525\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import plotly.tools as tls\n",
    "tls.embed('https://plot.ly/~chris/7365')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine , func\n",
    "import datetime as dt\n",
    "from IPython.display import display\n",
    "import plotly.plotly as py # interactive graphing\n",
    "from plotly.graph_objs import Bar, Scatter, Marker, Layout, Histogram, Figure\n",
    "\n",
    "py.plotly.sign_in(\"sfisoc\", \"s57zml09ny\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>CONTENT</th>\n",
       "      <th>CREATEDAT</th>\n",
       "      <th>RETWEET</th>\n",
       "      <th>RTID</th>\n",
       "      <th>RTUSERNAME</th>\n",
       "      <th>OPEN_DATE</th>\n",
       "      <th>USERID</th>\n",
       "      <th>USERNAME</th>\n",
       "      <th>FULLNAME</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>GEO_ENABLED</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>TIMEZONE</th>\n",
       "      <th>LANGUAGE</th>\n",
       "      <th>FOLLOWERS</th>\n",
       "      <th>FRIENDS</th>\n",
       "      <th>RTFOLLOWERS</th>\n",
       "      <th>TRANSLATOR</th>\n",
       "      <th>STATUS_COUNT</th>\n",
       "      <th>PROFILE_IMAGE</th>\n",
       "      <th>BACKGROUND_IMAGE</th>\n",
       "      <th>BANNER_IMAGE</th>\n",
       "      <th>IS_FRIEND</th>\n",
       "      <th>IS_FOLLOWER</th>\n",
       "      <th>IS_DEFAULT_PROFILE</th>\n",
       "      <th>SIZE</th>\n",
       "      <th>LAST_UPDATED</th>\n",
       "      <th>INREPLYTO</th>\n",
       "      <th>SOURCE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>682988187212460032</td>\n",
       "      <td>Last night wasn't all that great tbh</td>\n",
       "      <td>2016-01-01 18:14:07.0000000</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-05-15 00:51:07.0000000</td>\n",
       "      <td>1429176558</td>\n",
       "      <td>Kitchen_sink33</td>\n",
       "      <td>Dan Sink</td>\n",
       "      <td>Guitarist/Bassist/70's + 90's rock/Trump 2016/...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>en</td>\n",
       "      <td>405</td>\n",
       "      <td>335</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>5610</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6997207485...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/14291765...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2336</td>\n",
       "      <td>2016-03-02 02:32:20.9070000</td>\n",
       "      <td>-1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>694644791817166848</td>\n",
       "      <td>Women were not allowed to run the marathon in ...</td>\n",
       "      <td>2016-02-02 22:13:18.0000000</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010-07-30 20:00:32.0000000</td>\n",
       "      <td>172858784</td>\n",
       "      <td>timkaine</td>\n",
       "      <td>Senator Tim Kaine</td>\n",
       "      <td>US Senator from Virginia. Husband &amp; father of ...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Eastern Time (US &amp; Canada)</td>\n",
       "      <td>en</td>\n",
       "      <td>43148</td>\n",
       "      <td>719</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>6324</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/1300989266...</td>\n",
       "      <td>http://pbs.twimg.com/profile_background_images...</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/17285878...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3264</td>\n",
       "      <td>2016-03-07 02:27:10.3710000</td>\n",
       "      <td>-1</td>\n",
       "      <td>&lt;a href=\\http://twitter.com/download/iphone\\\" ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>615608428405366784</td>\n",
       "      <td>@Samcam06 it said about one of our first team ...</td>\n",
       "      <td>2015-06-29 19:51:01.0000000</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2012-06-16 22:19:34.0000000</td>\n",
       "      <td>610316414</td>\n",
       "      <td>tonycottee1986</td>\n",
       "      <td>tonycottee1986</td>\n",
       "      <td>Not the real TC  he's my fave player. West Ham...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Somerset</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>en</td>\n",
       "      <td>960</td>\n",
       "      <td>1238</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1879</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/4991887419...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/61031641...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2984</td>\n",
       "      <td>2016-03-04 02:49:49.2840000</td>\n",
       "      <td>1437633338</td>\n",
       "      <td>&lt;a href=\\http://twitter.com/download/android\\\"...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>700774993978912768</td>\n",
       "      <td>im so Annoying t\\,his is why i had no friends ...</td>\n",
       "      <td>2016-02-19 20:12:32.0000000</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2012-08-04 20:57:52.0000000</td>\n",
       "      <td>737401830</td>\n",
       "      <td>wonswoon</td>\n",
       "      <td>ciara</td>\n",
       "      <td>im stronger than the other members. im sevente...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16 ~ she/they ~ ireland</td>\n",
       "      <td>Casablanca</td>\n",
       "      <td>en</td>\n",
       "      <td>8733</td>\n",
       "      <td>2407</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>37520</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/7039331449...</td>\n",
       "      <td>http://pbs.twimg.com/profile_background_images...</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/73740183...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3008</td>\n",
       "      <td>2016-03-05 02:07:08.8380000</td>\n",
       "      <td>-1</td>\n",
       "      <td>&lt;a href=\\http://twitter.com/download/iphone\\\" ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>424608182007103488</td>\n",
       "      <td>I have the flu... 🙀💩👎</td>\n",
       "      <td>2014-01-18 18:24:14.0000000</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-08-03 16:17:02.0000000</td>\n",
       "      <td>1643156390</td>\n",
       "      <td>queenncam</td>\n",
       "      <td>Camren</td>\n",
       "      <td>✿jessi was here✿ | 卌</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Eastern Time (US &amp; Canada)</td>\n",
       "      <td>en</td>\n",
       "      <td>26</td>\n",
       "      <td>53</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>125</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/4950426387...</td>\n",
       "      <td>http://pbs.twimg.com/profile_background_images...</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/16431563...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2400</td>\n",
       "      <td>2016-03-05 02:46:56.4150000</td>\n",
       "      <td>-1</td>\n",
       "      <td>&lt;a href=\\http://twitter.com\\\" rel=\\\"nofollow\\\"...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   ID                                            CONTENT  \\\n",
       "0  682988187212460032               Last night wasn't all that great tbh   \n",
       "1  694644791817166848  Women were not allowed to run the marathon in ...   \n",
       "2  615608428405366784  @Samcam06 it said about one of our first team ...   \n",
       "3  700774993978912768  im so Annoying t\\,his is why i had no friends ...   \n",
       "4  424608182007103488                           I have the flu... 🙀💩👎   \n",
       "\n",
       "                     CREATEDAT  RETWEET  RTID  RTUSERNAME  \\\n",
       "0  2016-01-01 18:14:07.0000000        0   NaN         NaN   \n",
       "1  2016-02-02 22:13:18.0000000        0   NaN         NaN   \n",
       "2  2015-06-29 19:51:01.0000000        0   NaN         NaN   \n",
       "3  2016-02-19 20:12:32.0000000        0   NaN         NaN   \n",
       "4  2014-01-18 18:24:14.0000000        0   NaN         NaN   \n",
       "\n",
       "                     OPEN_DATE      USERID        USERNAME           FULLNAME  \\\n",
       "0  2013-05-15 00:51:07.0000000  1429176558  Kitchen_sink33           Dan Sink   \n",
       "1  2010-07-30 20:00:32.0000000   172858784        timkaine  Senator Tim Kaine   \n",
       "2  2012-06-16 22:19:34.0000000   610316414  tonycottee1986     tonycottee1986   \n",
       "3  2012-08-04 20:57:52.0000000   737401830        wonswoon              ciara   \n",
       "4  2013-08-03 16:17:02.0000000  1643156390       queenncam            Camren    \n",
       "\n",
       "                                         DESCRIPTION  GEO_ENABLED  LATITUDE  \\\n",
       "0  Guitarist/Bassist/70's + 90's rock/Trump 2016/...            0       NaN   \n",
       "1  US Senator from Virginia. Husband & father of ...            1       NaN   \n",
       "2  Not the real TC  he's my fave player. West Ham...            0       NaN   \n",
       "3  im stronger than the other members. im sevente...            1       NaN   \n",
       "4                               ✿jessi was here✿ | 卌            0       NaN   \n",
       "\n",
       "   LONGITUDE                 LOCATION                    TIMEZONE LANGUAGE  \\\n",
       "0        NaN                      NaN                         NaN       en   \n",
       "1        NaN                      NaN  Eastern Time (US & Canada)       en   \n",
       "2        NaN                 Somerset                   Amsterdam       en   \n",
       "3        NaN  16 ~ she/they ~ ireland                  Casablanca       en   \n",
       "4        NaN                      NaN  Eastern Time (US & Canada)       en   \n",
       "\n",
       "   FOLLOWERS  FRIENDS  RTFOLLOWERS  TRANSLATOR  STATUS_COUNT  \\\n",
       "0        405      335          NaN           0          5610   \n",
       "1      43148      719          NaN           0          6324   \n",
       "2        960     1238          NaN           0          1879   \n",
       "3       8733     2407          NaN           0         37520   \n",
       "4         26       53          NaN           0           125   \n",
       "\n",
       "                                       PROFILE_IMAGE  \\\n",
       "0  http://pbs.twimg.com/profile_images/6997207485...   \n",
       "1  http://pbs.twimg.com/profile_images/1300989266...   \n",
       "2  http://pbs.twimg.com/profile_images/4991887419...   \n",
       "3  http://pbs.twimg.com/profile_images/7039331449...   \n",
       "4  http://pbs.twimg.com/profile_images/4950426387...   \n",
       "\n",
       "                                    BACKGROUND_IMAGE  \\\n",
       "0   http://abs.twimg.com/images/themes/theme1/bg.png   \n",
       "1  http://pbs.twimg.com/profile_background_images...   \n",
       "2   http://abs.twimg.com/images/themes/theme1/bg.png   \n",
       "3  http://pbs.twimg.com/profile_background_images...   \n",
       "4  http://pbs.twimg.com/profile_background_images...   \n",
       "\n",
       "                                        BANNER_IMAGE  IS_FRIEND  IS_FOLLOWER  \\\n",
       "0  https://pbs.twimg.com/profile_banners/14291765...          0            1   \n",
       "1  https://pbs.twimg.com/profile_banners/17285878...          1            0   \n",
       "2  https://pbs.twimg.com/profile_banners/61031641...          1            0   \n",
       "3  https://pbs.twimg.com/profile_banners/73740183...          0            1   \n",
       "4  https://pbs.twimg.com/profile_banners/16431563...          0            1   \n",
       "\n",
       "   IS_DEFAULT_PROFILE  SIZE                 LAST_UPDATED   INREPLYTO  \\\n",
       "0                   0  2336  2016-03-02 02:32:20.9070000          -1   \n",
       "1                   0  3264  2016-03-07 02:27:10.3710000          -1   \n",
       "2                   0  2984  2016-03-04 02:49:49.2840000  1437633338   \n",
       "3                   0  3008  2016-03-05 02:07:08.8380000          -1   \n",
       "4                   0  2400  2016-03-05 02:46:56.4150000          -1   \n",
       "\n",
       "                                              SOURCE  \n",
       "0                                                NaN  \n",
       "1  <a href=\\http://twitter.com/download/iphone\\\" ...  \n",
       "2  <a href=\\http://twitter.com/download/android\\\"...  \n",
       "3  <a href=\\http://twitter.com/download/iphone\\\" ...  \n",
       "4  <a href=\\http://twitter.com\\\" rel=\\\"nofollow\\\"...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.18.0\n"
     ]
    }
   ],
   "source": [
    "pd.set_option('display.max_columns', None)\n",
    "df=pd.read_csv('data',nrows=5,header=5,names=[ \"ID\" , \"CONTENT\"  , \"CREATEDAT\" , \"RETWEET\" , \"RTID\" , \"RTUSERNAME\" , \n",
    "                                                      \"OPEN_DATE\" , \"USERID\" , \"USERNAME\" , \"FULLNAME\" , \"DESCRIPTION\" , \n",
    "                                                      \"GEO_ENABLED\" , \"LATITUDE\" , \"LONGITUDE\"  , \"LOCATION\" , \"TIMEZONE\" ,\n",
    "                                                      \"LANGUAGE\" , \"FOLLOWERS\" , \"FRIENDS\" , \"RTFOLLOWERS\" , \"TRANSLATOR\" , \n",
    "                                                      \"STATUS_COUNT\" , \"PROFILE_IMAGE\" , \"BACKGROUND_IMAGE\" , \"BANNER_IMAGE\" , \n",
    "                                                      \"IS_FRIEND\" , \"IS_FOLLOWER\" , \"IS_DEFAULT_PROFILE\" , \"SIZE\" , \n",
    "                                                      \"LAST_UPDATED\" , \"INREPLYTO\" , \"SOURCE\" ])\n",
    "display(df)\n",
    "print pd.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID                      int64\n",
       "CONTENT                object\n",
       "CREATEDAT              object\n",
       "RETWEET                 int64\n",
       "RTID                  float64\n",
       "RTUSERNAME            float64\n",
       "OPEN_DATE              object\n",
       "USERID                  int64\n",
       "USERNAME               object\n",
       "FULLNAME               object\n",
       "DESCRIPTION            object\n",
       "GEO_ENABLED             int64\n",
       "LATITUDE              float64\n",
       "LONGITUDE             float64\n",
       "LOCATION               object\n",
       "TIMEZONE               object\n",
       "LANGUAGE               object\n",
       "FOLLOWERS               int64\n",
       "FRIENDS                 int64\n",
       "RTFOLLOWERS           float64\n",
       "TRANSLATOR              int64\n",
       "STATUS_COUNT            int64\n",
       "PROFILE_IMAGE          object\n",
       "BACKGROUND_IMAGE       object\n",
       "BANNER_IMAGE           object\n",
       "IS_FRIEND               int64\n",
       "IS_FOLLOWER             int64\n",
       "IS_DEFAULT_PROFILE      int64\n",
       "SIZE                    int64\n",
       "LAST_UPDATED           object\n",
       "INREPLYTO               int64\n",
       "SOURCE                 object\n",
       "dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#!wc -l < data # Number of lines in dataset\n",
    "df.dtypes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "disk_engine = create_engine('sqlite:///tweets.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 seconds: completed 20000 rows\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/dist-packages/IPython/core/interactiveshell.py:2825: DtypeWarning:\n",
      "\n",
      "Columns (20) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3 seconds: completed 40000 rows\n",
      "7 seconds: completed 60000 rows\n",
      "11 seconds: completed 80000 rows\n",
      "15 seconds: completed 100000 rows\n",
      "20 seconds: completed 120000 rows\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/dist-packages/IPython/core/interactiveshell.py:2825: DtypeWarning:\n",
      "\n",
      "Columns (13,19,20,27) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "25 seconds: completed 140000 rows\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python2.7/dist-packages/IPython/core/interactiveshell.py:2825: DtypeWarning:\n",
      "\n",
      "Columns (21) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "31 seconds: completed 160000 rows\n"
     ]
    },
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-15-f91cbc3a952c>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m     14\u001b[0m     \u001b[1;32mprint\u001b[0m \u001b[1;34m'{} seconds: completed {} rows'\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdt\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdatetime\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mnow\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m-\u001b[0m \u001b[0mstart\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mseconds\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mj\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     15\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 16\u001b[1;33m     \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_sql\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'data'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdisk_engine\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mif_exists\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'append'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     17\u001b[0m     \u001b[0mindex_start\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m-\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m+\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     18\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/usr/local/lib/python2.7/dist-packages/pandas/core/generic.pyc\u001b[0m in \u001b[0;36mto_sql\u001b[1;34m(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)\u001b[0m\n\u001b[0;32m   1158\u001b[0m         sql.to_sql(self, name, con, flavor=flavor, schema=schema,\n\u001b[0;32m   1159\u001b[0m                    \u001b[0mif_exists\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mif_exists\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mindex\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindex_label\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mindex_label\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1160\u001b[1;33m                    chunksize=chunksize, dtype=dtype)\n\u001b[0m\u001b[0;32m   1161\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1162\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mto_pickle\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mpath\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/usr/local/lib/python2.7/dist-packages/pandas/io/sql.pyc\u001b[0m in \u001b[0;36mto_sql\u001b[1;34m(frame, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)\u001b[0m\n\u001b[0;32m    569\u001b[0m     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,\n\u001b[0;32m    570\u001b[0m                       \u001b[0mindex_label\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mindex_label\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mschema\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 571\u001b[1;33m                       chunksize=chunksize, dtype=dtype)\n\u001b[0m\u001b[0;32m    572\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    573\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/usr/local/lib/python2.7/dist-packages/pandas/io/sql.pyc\u001b[0m in \u001b[0;36mto_sql\u001b[1;34m(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)\u001b[0m\n\u001b[0;32m   1248\u001b[0m                          schema=schema, dtype=dtype)\n\u001b[0;32m   1249\u001b[0m         \u001b[0mtable\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcreate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1250\u001b[1;33m         \u001b[0mtable\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1251\u001b[0m         \u001b[1;31m# check for potentially case sensitivity issues (GH7815)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1252\u001b[0m         \u001b[0mengine\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnectable\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/usr/local/lib/python2.7/dist-packages/pandas/io/sql.pyc\u001b[0m in \u001b[0;36minsert\u001b[1;34m(self, chunksize)\u001b[0m\n\u001b[0;32m    768\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    769\u001b[0m                 \u001b[0mchunk_iter\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mzip\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0marr\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mstart_i\u001b[0m\u001b[1;33m:\u001b[0m\u001b[0mend_i\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0marr\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mdata_list\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 770\u001b[1;33m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_execute_insert\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkeys\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mchunk_iter\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    771\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    772\u001b[0m     def _query_iterator(self, result, chunksize, columns, coerce_float=True,\n",
      "\u001b[1;32m/usr/lib/python2.7/contextlib.pyc\u001b[0m in \u001b[0;36m__exit__\u001b[1;34m(self, type, value, traceback)\u001b[0m\n\u001b[0;32m     22\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mtype\u001b[0m \u001b[1;32mis\u001b[0m \u001b[0mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     23\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 24\u001b[1;33m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgen\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mnext\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     25\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mStopIteration\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     26\u001b[0m                 \u001b[1;32mreturn\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/usr/local/lib/python2.7/dist-packages/pandas/io/sql.pyc\u001b[0m in \u001b[0;36mrun_transaction\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1075\u001b[0m                 \u001b[1;32myield\u001b[0m \u001b[0mtx\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1076\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1077\u001b[1;33m                 \u001b[1;32myield\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnectable\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1078\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1079\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m__exit__\u001b[1;34m(self, type, value, traceback)\u001b[0m\n\u001b[0;32m   1869\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtransaction\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrollback\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1870\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1871\u001b[1;33m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtransaction\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1872\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mclose_with_result\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1873\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36mcommit\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1572\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_parent\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mis_active\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1573\u001b[0m             \u001b[1;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mInvalidRequestError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"This transaction is inactive\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1574\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_do_commit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1575\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mis_active\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1576\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_do_commit\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1603\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_do_commit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1604\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mis_active\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1605\u001b[1;33m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_commit_impl\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1606\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1607\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_commit_impl\u001b[1;34m(self, autocommit)\u001b[0m\n\u001b[0;32m    686\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mlogger\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0minfo\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"COMMIT\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    687\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 688\u001b[1;33m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdialect\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdo_commit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    689\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    690\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_handle_dbapi_exception\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0me\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mNone\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mNone\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mNone\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.pyc\u001b[0m in \u001b[0;36mdo_commit\u001b[1;34m(self, dbapi_connection)\u001b[0m\n\u001b[0;32m    421\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    422\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_commit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdbapi_connection\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 423\u001b[1;33m         \u001b[0mdbapi_connection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    424\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    425\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_close\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdbapi_connection\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "start = dt.datetime.now()\n",
    "chunksize = 20000\n",
    "j = 0\n",
    "index_start = 1\n",
    "\n",
    "for df in pd.read_csv('data', chunksize=chunksize, iterator=True, encoding='utf-8',names=[ \"ID\" , \"CONTENT\"  , \"CREATEDAT\" , \"RETWEET\" , \"RTID\" , \"RTUSERNAME\" , \"OPEN_DATE\" , \"USERID\" , \"USERNAME\" , \"FULLNAME\" , \"DESCRIPTION\" , \"GEO_ENABLED\" , \"LATITUDE\" , \"LONGITUDE\"  , \"LOCATION\" , \"TIMEZONE\" , \"LANGUAGE\" , \"FOLLOWERS\" , \"FRIENDS\" , \"RTFOLLOWERS\" , \"TRANSLATOR\" , \"STATUS_COUNT\" , \"PROFILE_IMAGE\" , \"BACKGROUND_IMAGE\" , \"BANNER_IMAGE\" , \"IS_FRIEND\" , \"IS_FOLLOWER\" , \"IS_DEFAULT_PROFILE\" , \"SIZE\" , \"LAST_UPDATED\" , \"INREPLYTO\" , \"SOURCE\" ]\n",
    "):\n",
    "    df = df.rename(columns={c: c.replace(' ', '') for c in df.columns})\n",
    "    \n",
    "    #df['CREATEDAT'] = pd.to_datetime(df['CREATEDAT'])\n",
    "    #df['OPEN_DATE'] = pd.to_datetime(df['OPEN_DATE'])\n",
    "    colums=[  \"CONTENT\"  , \"CREATEDAT\", \"OPEN_DATE\" , \"USERID\" , \"USERNAME\" , \"FULLNAME\" , \"DESCRIPTION\" ,\"GEO_ENABLED\"  , \"LOCATION\" , \"TIMEZONE\" \n",
    "            , \"FOLLOWERS\" ,\"STATUS_COUNT\" , \"PROFILE_IMAGE\" , \"BACKGROUND_IMAGE\" , \"BANNER_IMAGE\" ,\"IS_DEFAULT_PROFILE\" , \n",
    "            \"LAST_UPDATED\"  ]\n",
    "    \n",
    "    for c in df.colums\n",
    "        if c not in colums\n",
    "            df=df.drop(c,axis=1)\n",
    "    \n",
    "    j+=1\n",
    "    print '{} seconds: completed {} rows'.format((dt.datetime.now() - start).seconds, j*chunksize)\n",
    "\n",
    "    df.to_sql('data', disk_engine, if_exists='append')\n",
    "    index_start = df.index[-1] + 1\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>ID</th>\n",
       "      <th>CONTENT</th>\n",
       "      <th>CREATEDAT</th>\n",
       "      <th>RETWEET</th>\n",
       "      <th>RTID</th>\n",
       "      <th>RTUSERNAME</th>\n",
       "      <th>OPEN_DATE</th>\n",
       "      <th>USERID</th>\n",
       "      <th>USERNAME</th>\n",
       "      <th>FULLNAME</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>GEO_ENABLED</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>TIMEZONE</th>\n",
       "      <th>LANGUAGE</th>\n",
       "      <th>FOLLOWERS</th>\n",
       "      <th>FRIENDS</th>\n",
       "      <th>RTFOLLOWERS</th>\n",
       "      <th>TRANSLATOR</th>\n",
       "      <th>STATUS_COUNT</th>\n",
       "      <th>PROFILE_IMAGE</th>\n",
       "      <th>BACKGROUND_IMAGE</th>\n",
       "      <th>BANNER_IMAGE</th>\n",
       "      <th>IS_FRIEND</th>\n",
       "      <th>IS_FOLLOWER</th>\n",
       "      <th>IS_DEFAULT_PROFILE</th>\n",
       "      <th>SIZE</th>\n",
       "      <th>LAST_UPDATED</th>\n",
       "      <th>INREPLYTO</th>\n",
       "      <th>SOURCE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>684532631677964288</td>\n",
       "      <td>@genesisbimb @Megan51320 Is this your family?</td>\n",
       "      <td>2016-01-06 00:31:12.0000000</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2014-12-25 01:18:04.0000000</td>\n",
       "      <td>2942393395</td>\n",
       "      <td>George_k410</td>\n",
       "      <td>George</td>\n",
       "      <td>sbhs class of '17</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>en</td>\n",
       "      <td>101</td>\n",
       "      <td>248</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>576</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6559334011...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/29423933...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2480</td>\n",
       "      <td>2016-03-02 02:27:19.2400000</td>\n",
       "      <td>3618701472</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>572315447464480768</td>\n",
       "      <td>@JeffreyAsa typing......</td>\n",
       "      <td>2015-03-02 08:40:10.0000000</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-08-01 12:44:16.0000000</td>\n",
       "      <td>1637928565</td>\n",
       "      <td>babalola_biodun</td>\n",
       "      <td>babalola biodun</td>\n",
       "      <td>am babalola babatunde study @ Tai solarin univ...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Lagos\\,Nigeria</td>\n",
       "      <td>None</td>\n",
       "      <td>en</td>\n",
       "      <td>186</td>\n",
       "      <td>767</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>878</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6212057206...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/16379285...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2760</td>\n",
       "      <td>2016-03-02 02:06:40.1760000</td>\n",
       "      <td>393474189</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>682991170931298304</td>\n",
       "      <td>RT @DocInRealLife: Is there any better way to ...</td>\n",
       "      <td>2016-01-01 18:25:59.0000000</td>\n",
       "      <td>1</td>\n",
       "      <td>682990494473936896</td>\n",
       "      <td>DocInRealLife</td>\n",
       "      <td>2013-05-15 00:51:07.0000000</td>\n",
       "      <td>1429176558</td>\n",
       "      <td>Kitchen_sink33</td>\n",
       "      <td>Dan Sink</td>\n",
       "      <td>Guitarist/Bassist/70's + 90's rock/Trump 2016/...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>en</td>\n",
       "      <td>405</td>\n",
       "      <td>335</td>\n",
       "      <td>36830</td>\n",
       "      <td>0</td>\n",
       "      <td>5610</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6997207485...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/14291765...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>9712</td>\n",
       "      <td>2016-03-02 02:32:20.8960000</td>\n",
       "      <td>-1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>684447502934892544</td>\n",
       "      <td>@POTUS I h8 u</td>\n",
       "      <td>2016-01-05 18:52:55.0000000</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2014-12-25 01:18:04.0000000</td>\n",
       "      <td>2942393395</td>\n",
       "      <td>George_k410</td>\n",
       "      <td>George</td>\n",
       "      <td>sbhs class of '17</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>en</td>\n",
       "      <td>101</td>\n",
       "      <td>248</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>576</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6559334011...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/29423933...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2432</td>\n",
       "      <td>2016-03-02 02:27:19.2470000</td>\n",
       "      <td>1536791610</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>621925679555219456</td>\n",
       "      <td>Looking for someone to have it all and do it a...</td>\n",
       "      <td>2015-07-17 06:13:31.0000000</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2015-01-10 08:11:28.0000000</td>\n",
       "      <td>2971444075</td>\n",
       "      <td>BIackEverything</td>\n",
       "      <td>mood</td>\n",
       "      <td>Join the dark side of twitter | *Copyrighted c...</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Pacific Time (US &amp; Canada)</td>\n",
       "      <td>en</td>\n",
       "      <td>252473</td>\n",
       "      <td>88096</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>601</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6986626484...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/29714440...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2584</td>\n",
       "      <td>2016-03-02 02:06:40.2000000</td>\n",
       "      <td>-1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>587984904455135232</td>\n",
       "      <td>RT @officialdaddymo: Man U\\, Man City and Chel...</td>\n",
       "      <td>2015-04-14 14:25:00.0000000</td>\n",
       "      <td>1</td>\n",
       "      <td>587983010856902656</td>\n",
       "      <td>officialdaddymo</td>\n",
       "      <td>2013-08-01 12:44:16.0000000</td>\n",
       "      <td>1637928565</td>\n",
       "      <td>babalola_biodun</td>\n",
       "      <td>babalola biodun</td>\n",
       "      <td>am babalola babatunde study @ Tai solarin univ...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Lagos\\,Nigeria</td>\n",
       "      <td>None</td>\n",
       "      <td>en</td>\n",
       "      <td>186</td>\n",
       "      <td>767</td>\n",
       "      <td>4160</td>\n",
       "      <td>0</td>\n",
       "      <td>878</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6212057206...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/16379285...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>10216</td>\n",
       "      <td>2016-03-02 02:06:38.6770000</td>\n",
       "      <td>-1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>682988187212460032</td>\n",
       "      <td>Last night wasn't all that great tbh</td>\n",
       "      <td>2016-01-01 18:14:07.0000000</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-05-15 00:51:07.0000000</td>\n",
       "      <td>1429176558</td>\n",
       "      <td>Kitchen_sink33</td>\n",
       "      <td>Dan Sink</td>\n",
       "      <td>Guitarist/Bassist/70's + 90's rock/Trump 2016/...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>en</td>\n",
       "      <td>405</td>\n",
       "      <td>335</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>5610</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6997207485...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/14291765...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2336</td>\n",
       "      <td>2016-03-02 02:32:20.9070000</td>\n",
       "      <td>-1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>694644791817166848</td>\n",
       "      <td>Women were not allowed to run the marathon in ...</td>\n",
       "      <td>2016-02-02 22:13:18.0000000</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2010-07-30 20:00:32.0000000</td>\n",
       "      <td>172858784</td>\n",
       "      <td>timkaine</td>\n",
       "      <td>Senator Tim Kaine</td>\n",
       "      <td>US Senator from Virginia. Husband &amp; father of ...</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Eastern Time (US &amp; Canada)</td>\n",
       "      <td>en</td>\n",
       "      <td>43148</td>\n",
       "      <td>719</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>6324</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/1300989266...</td>\n",
       "      <td>http://pbs.twimg.com/profile_background_images...</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/17285878...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3264</td>\n",
       "      <td>2016-03-07 02:27:10.3710000</td>\n",
       "      <td>-1</td>\n",
       "      <td>&lt;a href=\\http://twitter.com/download/iphone\\\" ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>615608428405366784</td>\n",
       "      <td>@Samcam06 it said about one of our first team ...</td>\n",
       "      <td>2015-06-29 19:51:01.0000000</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2012-06-16 22:19:34.0000000</td>\n",
       "      <td>610316414</td>\n",
       "      <td>tonycottee1986</td>\n",
       "      <td>tonycottee1986</td>\n",
       "      <td>Not the real TC  he's my fave player. West Ham...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Somerset</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>en</td>\n",
       "      <td>960</td>\n",
       "      <td>1238</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>1879</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/4991887419...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/61031641...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2984</td>\n",
       "      <td>2016-03-04 02:49:49.2840000</td>\n",
       "      <td>1437633338</td>\n",
       "      <td>&lt;a href=\\http://twitter.com/download/android\\\"...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>700774993978912768</td>\n",
       "      <td>im so Annoying t\\,his is why i had no friends ...</td>\n",
       "      <td>2016-02-19 20:12:32.0000000</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2012-08-04 20:57:52.0000000</td>\n",
       "      <td>737401830</td>\n",
       "      <td>wonswoon</td>\n",
       "      <td>ciara</td>\n",
       "      <td>im stronger than the other members. im sevente...</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>16 ~ she/they ~ ireland</td>\n",
       "      <td>Casablanca</td>\n",
       "      <td>en</td>\n",
       "      <td>8733</td>\n",
       "      <td>2407</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>37520</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/7039331449...</td>\n",
       "      <td>http://pbs.twimg.com/profile_background_images...</td>\n",
       "      <td>https://pbs.twimg.com/profile_banners/73740183...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3008</td>\n",
       "      <td>2016-03-05 02:07:08.8380000</td>\n",
       "      <td>-1</td>\n",
       "      <td>&lt;a href=\\http://twitter.com/download/iphone\\\" ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                  ID  \\\n",
       "0      0  684532631677964288   \n",
       "1      1  572315447464480768   \n",
       "2      2  682991170931298304   \n",
       "3      3  684447502934892544   \n",
       "4      4  621925679555219456   \n",
       "5      5  587984904455135232   \n",
       "6      6  682988187212460032   \n",
       "7      7  694644791817166848   \n",
       "8      8  615608428405366784   \n",
       "9      9  700774993978912768   \n",
       "\n",
       "                                             CONTENT  \\\n",
       "0      @genesisbimb @Megan51320 Is this your family?   \n",
       "1                           @JeffreyAsa typing......   \n",
       "2  RT @DocInRealLife: Is there any better way to ...   \n",
       "3                                      @POTUS I h8 u   \n",
       "4  Looking for someone to have it all and do it a...   \n",
       "5  RT @officialdaddymo: Man U\\, Man City and Chel...   \n",
       "6               Last night wasn't all that great tbh   \n",
       "7  Women were not allowed to run the marathon in ...   \n",
       "8  @Samcam06 it said about one of our first team ...   \n",
       "9  im so Annoying t\\,his is why i had no friends ...   \n",
       "\n",
       "                     CREATEDAT RETWEET                RTID       RTUSERNAME  \\\n",
       "0  2016-01-06 00:31:12.0000000       0                None             None   \n",
       "1  2015-03-02 08:40:10.0000000       0                None             None   \n",
       "2  2016-01-01 18:25:59.0000000       1  682990494473936896    DocInRealLife   \n",
       "3  2016-01-05 18:52:55.0000000       0                None             None   \n",
       "4  2015-07-17 06:13:31.0000000       0                None             None   \n",
       "5  2015-04-14 14:25:00.0000000       1  587983010856902656  officialdaddymo   \n",
       "6  2016-01-01 18:14:07.0000000       0                None             None   \n",
       "7  2016-02-02 22:13:18.0000000       0                None             None   \n",
       "8  2015-06-29 19:51:01.0000000       0                None             None   \n",
       "9  2016-02-19 20:12:32.0000000       0                None             None   \n",
       "\n",
       "                     OPEN_DATE      USERID         USERNAME  \\\n",
       "0  2014-12-25 01:18:04.0000000  2942393395      George_k410   \n",
       "1  2013-08-01 12:44:16.0000000  1637928565  babalola_biodun   \n",
       "2  2013-05-15 00:51:07.0000000  1429176558   Kitchen_sink33   \n",
       "3  2014-12-25 01:18:04.0000000  2942393395      George_k410   \n",
       "4  2015-01-10 08:11:28.0000000  2971444075  BIackEverything   \n",
       "5  2013-08-01 12:44:16.0000000  1637928565  babalola_biodun   \n",
       "6  2013-05-15 00:51:07.0000000  1429176558   Kitchen_sink33   \n",
       "7  2010-07-30 20:00:32.0000000   172858784         timkaine   \n",
       "8  2012-06-16 22:19:34.0000000   610316414   tonycottee1986   \n",
       "9  2012-08-04 20:57:52.0000000   737401830         wonswoon   \n",
       "\n",
       "            FULLNAME                                        DESCRIPTION  \\\n",
       "0             George                                  sbhs class of '17   \n",
       "1    babalola biodun  am babalola babatunde study @ Tai solarin univ...   \n",
       "2           Dan Sink  Guitarist/Bassist/70's + 90's rock/Trump 2016/...   \n",
       "3             George                                  sbhs class of '17   \n",
       "4               mood  Join the dark side of twitter | *Copyrighted c...   \n",
       "5    babalola biodun  am babalola babatunde study @ Tai solarin univ...   \n",
       "6           Dan Sink  Guitarist/Bassist/70's + 90's rock/Trump 2016/...   \n",
       "7  Senator Tim Kaine  US Senator from Virginia. Husband & father of ...   \n",
       "8     tonycottee1986  Not the real TC  he's my fave player. West Ham...   \n",
       "9              ciara  im stronger than the other members. im sevente...   \n",
       "\n",
       "  GEO_ENABLED LATITUDE LONGITUDE                 LOCATION  \\\n",
       "0           0     None      None                     None   \n",
       "1           0     None      None          Lagos\\,Nigeria    \n",
       "2           0     None      None                     None   \n",
       "3           0     None      None                     None   \n",
       "4           1     None      None                     None   \n",
       "5           0     None      None          Lagos\\,Nigeria    \n",
       "6           0     None      None                     None   \n",
       "7           1     None      None                     None   \n",
       "8           0     None      None                 Somerset   \n",
       "9           1     None      None  16 ~ she/they ~ ireland   \n",
       "\n",
       "                     TIMEZONE LANGUAGE FOLLOWERS FRIENDS RTFOLLOWERS  \\\n",
       "0                        None       en       101     248        None   \n",
       "1                        None       en       186     767        None   \n",
       "2                        None       en       405     335       36830   \n",
       "3                        None       en       101     248        None   \n",
       "4  Pacific Time (US & Canada)       en    252473   88096        None   \n",
       "5                        None       en       186     767        4160   \n",
       "6                        None       en       405     335        None   \n",
       "7  Eastern Time (US & Canada)       en     43148     719        None   \n",
       "8                   Amsterdam       en       960    1238        None   \n",
       "9                  Casablanca       en      8733    2407        None   \n",
       "\n",
       "  TRANSLATOR STATUS_COUNT                                      PROFILE_IMAGE  \\\n",
       "0          0          576  http://pbs.twimg.com/profile_images/6559334011...   \n",
       "1          0          878  http://pbs.twimg.com/profile_images/6212057206...   \n",
       "2          0         5610  http://pbs.twimg.com/profile_images/6997207485...   \n",
       "3          0          576  http://pbs.twimg.com/profile_images/6559334011...   \n",
       "4          0          601  http://pbs.twimg.com/profile_images/6986626484...   \n",
       "5          0          878  http://pbs.twimg.com/profile_images/6212057206...   \n",
       "6          0         5610  http://pbs.twimg.com/profile_images/6997207485...   \n",
       "7          0         6324  http://pbs.twimg.com/profile_images/1300989266...   \n",
       "8          0         1879  http://pbs.twimg.com/profile_images/4991887419...   \n",
       "9          0        37520  http://pbs.twimg.com/profile_images/7039331449...   \n",
       "\n",
       "                                    BACKGROUND_IMAGE  \\\n",
       "0   http://abs.twimg.com/images/themes/theme1/bg.png   \n",
       "1   http://abs.twimg.com/images/themes/theme1/bg.png   \n",
       "2   http://abs.twimg.com/images/themes/theme1/bg.png   \n",
       "3   http://abs.twimg.com/images/themes/theme1/bg.png   \n",
       "4   http://abs.twimg.com/images/themes/theme1/bg.png   \n",
       "5   http://abs.twimg.com/images/themes/theme1/bg.png   \n",
       "6   http://abs.twimg.com/images/themes/theme1/bg.png   \n",
       "7  http://pbs.twimg.com/profile_background_images...   \n",
       "8   http://abs.twimg.com/images/themes/theme1/bg.png   \n",
       "9  http://pbs.twimg.com/profile_background_images...   \n",
       "\n",
       "                                        BANNER_IMAGE IS_FRIEND IS_FOLLOWER  \\\n",
       "0  https://pbs.twimg.com/profile_banners/29423933...         0           1   \n",
       "1  https://pbs.twimg.com/profile_banners/16379285...         0           1   \n",
       "2  https://pbs.twimg.com/profile_banners/14291765...         0           1   \n",
       "3  https://pbs.twimg.com/profile_banners/29423933...         0           1   \n",
       "4  https://pbs.twimg.com/profile_banners/29714440...         0           1   \n",
       "5  https://pbs.twimg.com/profile_banners/16379285...         0           1   \n",
       "6  https://pbs.twimg.com/profile_banners/14291765...         0           1   \n",
       "7  https://pbs.twimg.com/profile_banners/17285878...         1           0   \n",
       "8  https://pbs.twimg.com/profile_banners/61031641...         1           0   \n",
       "9  https://pbs.twimg.com/profile_banners/73740183...         0           1   \n",
       "\n",
       "  IS_DEFAULT_PROFILE   SIZE                 LAST_UPDATED   INREPLYTO  \\\n",
       "0                  0   2480  2016-03-02 02:27:19.2400000  3618701472   \n",
       "1                  0   2760  2016-03-02 02:06:40.1760000   393474189   \n",
       "2                  0   9712  2016-03-02 02:32:20.8960000          -1   \n",
       "3                  0   2432  2016-03-02 02:27:19.2470000  1536791610   \n",
       "4                  0   2584  2016-03-02 02:06:40.2000000          -1   \n",
       "5                  0  10216  2016-03-02 02:06:38.6770000          -1   \n",
       "6                  0   2336  2016-03-02 02:32:20.9070000          -1   \n",
       "7                  0   3264  2016-03-07 02:27:10.3710000          -1   \n",
       "8                  0   2984  2016-03-04 02:49:49.2840000  1437633338   \n",
       "9                  0   3008  2016-03-05 02:07:08.8380000          -1   \n",
       "\n",
       "                                              SOURCE  \n",
       "0                                               None  \n",
       "1                                               None  \n",
       "2                                               None  \n",
       "3                                               None  \n",
       "4                                               None  \n",
       "5                                               None  \n",
       "6                                               None  \n",
       "7  <a href=\\http://twitter.com/download/iphone\\\" ...  \n",
       "8  <a href=\\http://twitter.com/download/android\\\"...  \n",
       "9  <a href=\\http://twitter.com/download/iphone\\\" ...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pd.set_option('display.max_columns', None)\n",
    "df = pd.read_sql_query('SELECT * FROM data LIMIT 10', disk_engine)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>USERID</th>\n",
       "      <th>USERNAME</th>\n",
       "      <th>FULLNAME</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>GEO_ENABLED</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>TIMEZONE</th>\n",
       "      <th>PROFILE_IMAGE</th>\n",
       "      <th>BACKGROUND_IMAGE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2942393395</td>\n",
       "      <td>George_k410</td>\n",
       "      <td>George</td>\n",
       "      <td>sbhs class of '17</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6559334011...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1637928565</td>\n",
       "      <td>babalola_biodun</td>\n",
       "      <td>babalola biodun</td>\n",
       "      <td>am babalola babatunde study @ Tai solarin univ...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Lagos\\,Nigeria</td>\n",
       "      <td>None</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6212057206...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1429176558</td>\n",
       "      <td>Kitchen_sink33</td>\n",
       "      <td>Dan Sink</td>\n",
       "      <td>Guitarist/Bassist/70's + 90's rock/Trump 2016/...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6997207485...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2942393395</td>\n",
       "      <td>George_k410</td>\n",
       "      <td>George</td>\n",
       "      <td>sbhs class of '17</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6559334011...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2971444075</td>\n",
       "      <td>BIackEverything</td>\n",
       "      <td>mood</td>\n",
       "      <td>Join the dark side of twitter | *Copyrighted c...</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Pacific Time (US &amp; Canada)</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6986626484...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1637928565</td>\n",
       "      <td>babalola_biodun</td>\n",
       "      <td>babalola biodun</td>\n",
       "      <td>am babalola babatunde study @ Tai solarin univ...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Lagos\\,Nigeria</td>\n",
       "      <td>None</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6212057206...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1429176558</td>\n",
       "      <td>Kitchen_sink33</td>\n",
       "      <td>Dan Sink</td>\n",
       "      <td>Guitarist/Bassist/70's + 90's rock/Trump 2016/...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/6997207485...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>172858784</td>\n",
       "      <td>timkaine</td>\n",
       "      <td>Senator Tim Kaine</td>\n",
       "      <td>US Senator from Virginia. Husband &amp; father of ...</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Eastern Time (US &amp; Canada)</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/1300989266...</td>\n",
       "      <td>http://pbs.twimg.com/profile_background_images...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>610316414</td>\n",
       "      <td>tonycottee1986</td>\n",
       "      <td>tonycottee1986</td>\n",
       "      <td>Not the real TC  he's my fave player. West Ham...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Somerset</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/4991887419...</td>\n",
       "      <td>http://abs.twimg.com/images/themes/theme1/bg.png</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>737401830</td>\n",
       "      <td>wonswoon</td>\n",
       "      <td>ciara</td>\n",
       "      <td>im stronger than the other members. im sevente...</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>16 ~ she/they ~ ireland</td>\n",
       "      <td>Casablanca</td>\n",
       "      <td>http://pbs.twimg.com/profile_images/7039331449...</td>\n",
       "      <td>http://pbs.twimg.com/profile_background_images...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       USERID         USERNAME           FULLNAME  \\\n",
       "0  2942393395      George_k410             George   \n",
       "1  1637928565  babalola_biodun    babalola biodun   \n",
       "2  1429176558   Kitchen_sink33           Dan Sink   \n",
       "3  2942393395      George_k410             George   \n",
       "4  2971444075  BIackEverything               mood   \n",
       "5  1637928565  babalola_biodun    babalola biodun   \n",
       "6  1429176558   Kitchen_sink33           Dan Sink   \n",
       "7   172858784         timkaine  Senator Tim Kaine   \n",
       "8   610316414   tonycottee1986     tonycottee1986   \n",
       "9   737401830         wonswoon              ciara   \n",
       "\n",
       "                                         DESCRIPTION GEO_ENABLED LATITUDE  \\\n",
       "0                                  sbhs class of '17           0     None   \n",
       "1  am babalola babatunde study @ Tai solarin univ...           0     None   \n",
       "2  Guitarist/Bassist/70's + 90's rock/Trump 2016/...           0     None   \n",
       "3                                  sbhs class of '17           0     None   \n",
       "4  Join the dark side of twitter | *Copyrighted c...           1     None   \n",
       "5  am babalola babatunde study @ Tai solarin univ...           0     None   \n",
       "6  Guitarist/Bassist/70's + 90's rock/Trump 2016/...           0     None   \n",
       "7  US Senator from Virginia. Husband & father of ...           1     None   \n",
       "8  Not the real TC  he's my fave player. West Ham...           0     None   \n",
       "9  im stronger than the other members. im sevente...           1     None   \n",
       "\n",
       "  LONGITUDE                 LOCATION                    TIMEZONE  \\\n",
       "0      None                     None                        None   \n",
       "1      None          Lagos\\,Nigeria                         None   \n",
       "2      None                     None                        None   \n",
       "3      None                     None                        None   \n",
       "4      None                     None  Pacific Time (US & Canada)   \n",
       "5      None          Lagos\\,Nigeria                         None   \n",
       "6      None                     None                        None   \n",
       "7      None                     None  Eastern Time (US & Canada)   \n",
       "8      None                 Somerset                   Amsterdam   \n",
       "9      None  16 ~ she/they ~ ireland                  Casablanca   \n",
       "\n",
       "                                       PROFILE_IMAGE  \\\n",
       "0  http://pbs.twimg.com/profile_images/6559334011...   \n",
       "1  http://pbs.twimg.com/profile_images/6212057206...   \n",
       "2  http://pbs.twimg.com/profile_images/6997207485...   \n",
       "3  http://pbs.twimg.com/profile_images/6559334011...   \n",
       "4  http://pbs.twimg.com/profile_images/6986626484...   \n",
       "5  http://pbs.twimg.com/profile_images/6212057206...   \n",
       "6  http://pbs.twimg.com/profile_images/6997207485...   \n",
       "7  http://pbs.twimg.com/profile_images/1300989266...   \n",
       "8  http://pbs.twimg.com/profile_images/4991887419...   \n",
       "9  http://pbs.twimg.com/profile_images/7039331449...   \n",
       "\n",
       "                                    BACKGROUND_IMAGE  \n",
       "0   http://abs.twimg.com/images/themes/theme1/bg.png  \n",
       "1   http://abs.twimg.com/images/themes/theme1/bg.png  \n",
       "2   http://abs.twimg.com/images/themes/theme1/bg.png  \n",
       "3   http://abs.twimg.com/images/themes/theme1/bg.png  \n",
       "4   http://abs.twimg.com/images/themes/theme1/bg.png  \n",
       "5   http://abs.twimg.com/images/themes/theme1/bg.png  \n",
       "6   http://abs.twimg.com/images/themes/theme1/bg.png  \n",
       "7  http://pbs.twimg.com/profile_background_images...  \n",
       "8   http://abs.twimg.com/images/themes/theme1/bg.png  \n",
       "9  http://pbs.twimg.com/profile_background_images...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = pd.read_sql_query('SELECT USERID ,USERNAME,FULLNAME,DESCRIPTION,GEO_ENABLED,LATITUDE,LONGITUDE,LOCATION,TIMEZONE,PROFILE_IMAGE,BACKGROUND_IMAGE FROM data LIMIT 10', disk_engine)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([u'George_k410', u'babalola_biodun', u'Kitchen_sink33',\n",
       "       u'BIackEverything', u'timkaine', u'tonycottee1986', u'wonswoon'], dtype=object)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.unique(df.USERNAME.ravel())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TIMEZONE</th>\n",
       "      <th>number_of_zones</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>54863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Eastern Time (US &amp; Canada)</td>\n",
       "      <td>26100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Pacific Time (US &amp; Canada)</td>\n",
       "      <td>21096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Central Time (US &amp; Canada)</td>\n",
       "      <td>16014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Quito</td>\n",
       "      <td>6615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Atlantic Time (Canada)</td>\n",
       "      <td>6170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>London</td>\n",
       "      <td>5334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>2944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>2765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Hawaii</td>\n",
       "      <td>1890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Mountain Time (US &amp; Canada)</td>\n",
       "      <td>1827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>1501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Casablanca</td>\n",
       "      <td>961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Athens</td>\n",
       "      <td>815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>West Central Africa</td>\n",
       "      <td>658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Ljubljana</td>\n",
       "      <td>453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Dublin</td>\n",
       "      <td>405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Paris</td>\n",
       "      <td>387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Brasilia</td>\n",
       "      <td>369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>New Delhi</td>\n",
       "      <td>367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Central America</td>\n",
       "      <td>357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Beijing</td>\n",
       "      <td>355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Sydney</td>\n",
       "      <td>337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Berlin</td>\n",
       "      <td>329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Baghdad</td>\n",
       "      <td>286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Rome</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Mazatlan</td>\n",
       "      <td>239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Tijuana</td>\n",
       "      <td>238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Madrid</td>\n",
       "      <td>231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Jakarta</td>\n",
       "      <td>216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>495</th>\n",
       "      <td>instagram: @damnWILDchild</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>496</th>\n",
       "      <td>jarpad</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497</th>\n",
       "      <td>jersey girl</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>knoxville\\, tennessee</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499</th>\n",
       "      <td>location: where the food is</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>looking for the answer</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>md</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>nezoshi/maé</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>on yo bitch mind</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>504</th>\n",
       "      <td>peckham</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>505</th>\n",
       "      <td>sW̸s</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506</th>\n",
       "      <td>sao paulo</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>507</th>\n",
       "      <td>she/they • philippines</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>508</th>\n",
       "      <td>snapchat_eduardo9985</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>509</th>\n",
       "      <td>southampton\\, uk</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>510</th>\n",
       "      <td>st cloud state\\,mn</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>511</th>\n",
       "      <td>stamford bridge</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>512</th>\n",
       "      <td>stay woke</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>513</th>\n",
       "      <td>steel city</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514</th>\n",
       "      <td>us</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>515</th>\n",
       "      <td>uww</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>516</th>\n",
       "      <td>where data flows</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>517</th>\n",
       "      <td>wherever the sun shines ☀</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518</th>\n",
       "      <td>with asap rocky ❣</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>519</th>\n",
       "      <td>your local juice brand</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>520</th>\n",
       "      <td>your wildest dreams</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>521</th>\n",
       "      <td>ÜT: 41.654712\\,-83.528995</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522</th>\n",
       "      <td>Россия (Russia) #ru</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523</th>\n",
       "      <td>♡ OFFICIAL TWITTER ACCOUNT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>524</th>\n",
       "      <td>❁•~✿°~❀MagicLove☪astle❀~°✿~•❁</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>525 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          TIMEZONE  number_of_zones\n",
       "0                             None            54863\n",
       "1       Eastern Time (US & Canada)            26100\n",
       "2       Pacific Time (US & Canada)            21096\n",
       "3       Central Time (US & Canada)            16014\n",
       "4                            Quito             6615\n",
       "5           Atlantic Time (Canada)             6170\n",
       "6                           London             5334\n",
       "7                          Arizona             2944\n",
       "8                        Amsterdam             2765\n",
       "9                           Hawaii             1890\n",
       "10     Mountain Time (US & Canada)             1827\n",
       "11                          Alaska             1501\n",
       "12                      Casablanca              961\n",
       "13                          Athens              815\n",
       "14             West Central Africa              658\n",
       "15                       Ljubljana              453\n",
       "16                          Dublin              405\n",
       "17                           Paris              387\n",
       "18                        Brasilia              369\n",
       "19                       New Delhi              367\n",
       "20                 Central America              357\n",
       "21                         Beijing              355\n",
       "22                          Sydney              337\n",
       "23                          Berlin              329\n",
       "24                         Baghdad              286\n",
       "25                            Rome              240\n",
       "26                        Mazatlan              239\n",
       "27                         Tijuana              238\n",
       "28                          Madrid              231\n",
       "29                         Jakarta              216\n",
       "..                             ...              ...\n",
       "495      instagram: @damnWILDchild                1\n",
       "496                         jarpad                1\n",
       "497                    jersey girl                1\n",
       "498          knoxville\\, tennessee                1\n",
       "499   location: where the food is                 1\n",
       "500       looking for the answer                  1\n",
       "501                             md                1\n",
       "502                   nezoshi/maé                 1\n",
       "503              on yo bitch mind                 1\n",
       "504                       peckham                 1\n",
       "505                           sW̸s                1\n",
       "506                      sao paulo                1\n",
       "507         she/they • philippines                1\n",
       "508           snapchat_eduardo9985                1\n",
       "509               southampton\\, uk                1\n",
       "510             st cloud state\\,mn                1\n",
       "511               stamford bridge                 1\n",
       "512                      stay woke                1\n",
       "513                     steel city                1\n",
       "514                             us                1\n",
       "515                            uww                1\n",
       "516               where data flows                1\n",
       "517      wherever the sun shines ☀                1\n",
       "518              with asap rocky ❣                1\n",
       "519         your local juice brand                1\n",
       "520            your wildest dreams                1\n",
       "521      ÜT: 41.654712\\,-83.528995                1\n",
       "522            Россия (Russia) #ru                1\n",
       "523    ♡ OFFICIAL TWITTER ACCOUNT                 1\n",
       "524  ❁•~✿°~❀MagicLove☪astle❀~°✿~•❁                1\n",
       "\n",
       "[525 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = pd.read_sql_query('SELECT TIMEZONE, COUNT(*) as `number_of_zones`'\n",
    "                       'FROM data '\n",
    "                       'GROUP BY TIMEZONE '\n",
    "                       'ORDER BY -number_of_zones', disk_engine)\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "High five! You successfuly sent some data to your account on plotly. View your plot in your browser at https://plot.ly/~sfisoc/0 or inside your plot.ly account where it is named 'timezonevstatuscount'\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~sfisoc/0.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df = pd.read_sql_query('SELECT TIMEZONE, COUNT(*) as `zonescount`'\n",
    "                       'FROM data '\n",
    "                       'GROUP BY TIMEZONE '\n",
    "                       'ORDER BY -zonescount', disk_engine)\n",
    "\n",
    "py.iplot([Bar(x=df.TIMEZONE, y=df.zonescount)], filename='timezonevstatuscount')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>USERNAME</th>\n",
       "      <th>STATUS_COUNT</th>\n",
       "      <th>statuscount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TatySimoneC</td>\n",
       "      <td>2104</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LPringle19</td>\n",
       "      <td>25655.0</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>lmayes</td>\n",
       "      <td>10841</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TweetinFeminist</td>\n",
       "      <td>2190</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Blake_mayes24</td>\n",
       "      <td>5602</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>KenrickLlamarse</td>\n",
       "      <td>1063</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>hannahrichburg1</td>\n",
       "      <td>1759</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>sarahirwin__10</td>\n",
       "      <td>3626</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>OmariHardwick</td>\n",
       "      <td>4479</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>HailState</td>\n",
       "      <td>7733</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>paolothehammer</td>\n",
       "      <td>5351</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Sammy_Taylor88</td>\n",
       "      <td>9777</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>KeshaRose</td>\n",
       "      <td>5913.0</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>zypisfy</td>\n",
       "      <td>10402</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>ChidoD_</td>\n",
       "      <td>697</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>SarcasmSaid</td>\n",
       "      <td>9660</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>socialmoms</td>\n",
       "      <td>63133</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>HChollman</td>\n",
       "      <td>5402.0</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>kimttran</td>\n",
       "      <td>1332.0</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Faptallica</td>\n",
       "      <td>2224</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Iam_Imanse</td>\n",
       "      <td>1770.0</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>aangienguyen</td>\n",
       "      <td>5079</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>hannahfaith7_</td>\n",
       "      <td>15394</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>DavidMuir</td>\n",
       "      <td>5880</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>HarrisonB123</td>\n",
       "      <td>2719</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>genievefiggis</td>\n",
       "      <td>2591</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>jnusser62</td>\n",
       "      <td>15703</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>pled17s</td>\n",
       "      <td>11646</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>_SIMPLYlaurenk</td>\n",
       "      <td>33846</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>emgreerie</td>\n",
       "      <td>30813</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6738</th>\n",
       "      <td>2013-10-15 23:41:07.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6739</th>\n",
       "      <td>2013-11-06 13:19:07.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6740</th>\n",
       "      <td>2013-12-05 03:16:10.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6741</th>\n",
       "      <td>2013-12-05 06:56:53.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6742</th>\n",
       "      <td>2013-12-12 21:50:28.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6743</th>\n",
       "      <td>2013-12-25 01:45:31.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6744</th>\n",
       "      <td>2013-12-29 01:13:02.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6745</th>\n",
       "      <td>2014-01-19 15:10:14.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6746</th>\n",
       "      <td>2014-01-20 11:42:53.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6747</th>\n",
       "      <td>2014-01-24 20:13:59.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6748</th>\n",
       "      <td>2014-01-27 12:54:06.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6749</th>\n",
       "      <td>2014-02-25 18:28:14.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6750</th>\n",
       "      <td>2014-03-29 04:33:21.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6751</th>\n",
       "      <td>2014-06-24 20:51:18.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6752</th>\n",
       "      <td>2014-08-04 03:01:23.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6753</th>\n",
       "      <td>2014-09-05 18:03:58.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6754</th>\n",
       "      <td>2014-10-13 21:02:17.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6755</th>\n",
       "      <td>2014-10-28 12:17:20.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6756</th>\n",
       "      <td>2014-11-04 18:27:09.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6757</th>\n",
       "      <td>2014-11-08 17:02:29.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6758</th>\n",
       "      <td>2014-11-10 21:12:48.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6759</th>\n",
       "      <td>2015-01-21 18:46:41.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6760</th>\n",
       "      <td>2015-03-30 01:48:32.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6761</th>\n",
       "      <td>2015-05-05 07:01:52.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6762</th>\n",
       "      <td>2015-05-12 10:46:22.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6763</th>\n",
       "      <td>2015-06-24 23:19:19.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6764</th>\n",
       "      <td>2015-08-06 22:59:47.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6765</th>\n",
       "      <td>2015-08-20 17:52:46.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6766</th>\n",
       "      <td>2015-09-03 07:48:35.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6767</th>\n",
       "      <td>2016-01-07 05:05:01.0000000</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6768 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         USERNAME STATUS_COUNT  statuscount\n",
       "0                     TatySimoneC         2104          111\n",
       "1                      LPringle19      25655.0          109\n",
       "2                          lmayes        10841          106\n",
       "3                 TweetinFeminist         2190          100\n",
       "4                   Blake_mayes24         5602           99\n",
       "5                 KenrickLlamarse         1063           97\n",
       "6                 hannahrichburg1         1759           97\n",
       "7                  sarahirwin__10         3626           96\n",
       "8                   OmariHardwick         4479           94\n",
       "9                       HailState         7733           93\n",
       "10                 paolothehammer         5351           93\n",
       "11                 Sammy_Taylor88         9777           92\n",
       "12                      KeshaRose       5913.0           91\n",
       "13                        zypisfy        10402           91\n",
       "14                        ChidoD_          697           90\n",
       "15                    SarcasmSaid         9660           89\n",
       "16                     socialmoms        63133           89\n",
       "17                      HChollman       5402.0           88\n",
       "18                       kimttran       1332.0           88\n",
       "19                     Faptallica         2224           87\n",
       "20                     Iam_Imanse       1770.0           87\n",
       "21                   aangienguyen         5079           87\n",
       "22                  hannahfaith7_        15394           87\n",
       "23                      DavidMuir         5880           86\n",
       "24                   HarrisonB123         2719           86\n",
       "25                  genievefiggis         2591           86\n",
       "26                      jnusser62        15703           86\n",
       "27                        pled17s        11646           86\n",
       "28                 _SIMPLYlaurenk        33846           85\n",
       "29                      emgreerie        30813           85\n",
       "...                           ...          ...          ...\n",
       "6738  2013-10-15 23:41:07.0000000         None            0\n",
       "6739  2013-11-06 13:19:07.0000000         None            0\n",
       "6740  2013-12-05 03:16:10.0000000         None            0\n",
       "6741  2013-12-05 06:56:53.0000000         None            0\n",
       "6742  2013-12-12 21:50:28.0000000         None            0\n",
       "6743  2013-12-25 01:45:31.0000000         None            0\n",
       "6744  2013-12-29 01:13:02.0000000         None            0\n",
       "6745  2014-01-19 15:10:14.0000000         None            0\n",
       "6746  2014-01-20 11:42:53.0000000         None            0\n",
       "6747  2014-01-24 20:13:59.0000000         None            0\n",
       "6748  2014-01-27 12:54:06.0000000         None            0\n",
       "6749  2014-02-25 18:28:14.0000000         None            0\n",
       "6750  2014-03-29 04:33:21.0000000         None            0\n",
       "6751  2014-06-24 20:51:18.0000000         None            0\n",
       "6752  2014-08-04 03:01:23.0000000         None            0\n",
       "6753  2014-09-05 18:03:58.0000000         None            0\n",
       "6754  2014-10-13 21:02:17.0000000         None            0\n",
       "6755  2014-10-28 12:17:20.0000000         None            0\n",
       "6756  2014-11-04 18:27:09.0000000         None            0\n",
       "6757  2014-11-08 17:02:29.0000000         None            0\n",
       "6758  2014-11-10 21:12:48.0000000         None            0\n",
       "6759  2015-01-21 18:46:41.0000000         None            0\n",
       "6760  2015-03-30 01:48:32.0000000         None            0\n",
       "6761  2015-05-05 07:01:52.0000000         None            0\n",
       "6762  2015-05-12 10:46:22.0000000         None            0\n",
       "6763  2015-06-24 23:19:19.0000000         None            0\n",
       "6764  2015-08-06 22:59:47.0000000         None            0\n",
       "6765  2015-08-20 17:52:46.0000000         None            0\n",
       "6766  2015-09-03 07:48:35.0000000         None            0\n",
       "6767  2016-01-07 05:05:01.0000000         None            0\n",
       "\n",
       "[6768 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = pd.read_sql_query('SELECT USERNAME, STATUS_COUNT, COUNT(STATUS_COUNT) as `statuscount`'\n",
    "                       'FROM data '\n",
    "                       'GROUP BY USERNAME '\n",
    "                       'ORDER BY -statuscount', disk_engine)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~sfisoc/4.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df = pd.read_sql_query('SELECT USERNAME, STATUS_COUNT, COUNT(STATUS_COUNT) as `statuscount`'\n",
    "                       'FROM data '\n",
    "                       'GROUP BY USERNAME '\n",
    "                       'ORDER BY -statuscount', disk_engine)\n",
    "\n",
    "\n",
    "\n",
    "trace = Scatter(\n",
    "    x = df.USERNAME,\n",
    "    y = df.statuscount\n",
    ")\n",
    "\n",
    "data = [trace]\n",
    "\n",
    "\n",
    "py.iplot(data, filename='useridvstatuscount')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>USERNAME</th>\n",
       "      <th>FRIENDS</th>\n",
       "      <th>numbers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>George_k410</td>\n",
       "      <td>248</td>\n",
       "      <td>248.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>babalola_biodun</td>\n",
       "      <td>767</td>\n",
       "      <td>767.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Kitchen_sink33</td>\n",
       "      <td>335</td>\n",
       "      <td>335.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>George_k410</td>\n",
       "      <td>248</td>\n",
       "      <td>248.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BIackEverything</td>\n",
       "      <td>88096</td>\n",
       "      <td>88096.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>babalola_biodun</td>\n",
       "      <td>767</td>\n",
       "      <td>767.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Kitchen_sink33</td>\n",
       "      <td>335</td>\n",
       "      <td>335.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>timkaine</td>\n",
       "      <td>719</td>\n",
       "      <td>719.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>tonycottee1986</td>\n",
       "      <td>1238</td>\n",
       "      <td>1238.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>wonswoon</td>\n",
       "      <td>2407</td>\n",
       "      <td>2407.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>queenncam</td>\n",
       "      <td>53</td>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Matt_one_kid</td>\n",
       "      <td>328</td>\n",
       "      <td>328.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>PR1investments</td>\n",
       "      <td>255</td>\n",
       "      <td>255.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>RogersTimi</td>\n",
       "      <td>200</td>\n",
       "      <td>200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>babalola_biodun</td>\n",
       "      <td>767</td>\n",
       "      <td>767.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>schoneck</td>\n",
       "      <td>343</td>\n",
       "      <td>343.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>wonswoon</td>\n",
       "      <td>2407</td>\n",
       "      <td>2407.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>timkaine</td>\n",
       "      <td>719</td>\n",
       "      <td>719.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>lindseyelsbury</td>\n",
       "      <td>673</td>\n",
       "      <td>673.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>__samone__</td>\n",
       "      <td>259</td>\n",
       "      <td>259.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Matt_one_kid</td>\n",
       "      <td>328</td>\n",
       "      <td>328.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>schoneck</td>\n",
       "      <td>343</td>\n",
       "      <td>343.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>EmilySantelli</td>\n",
       "      <td>184</td>\n",
       "      <td>184.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>lindseyelsbury</td>\n",
       "      <td>673</td>\n",
       "      <td>673.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>wonswoon</td>\n",
       "      <td>2407</td>\n",
       "      <td>2407.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>AllHipHopModels</td>\n",
       "      <td>38025</td>\n",
       "      <td>38025.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>BIackEverything</td>\n",
       "      <td>88096</td>\n",
       "      <td>88096.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Kelencoleman</td>\n",
       "      <td>400</td>\n",
       "      <td>400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>__samone__</td>\n",
       "      <td>259</td>\n",
       "      <td>259.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>BIackEverything</td>\n",
       "      <td>88096</td>\n",
       "      <td>88096.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159970</th>\n",
       "      <td>BreanneMarie123</td>\n",
       "      <td>854</td>\n",
       "      <td>854.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159971</th>\n",
       "      <td>FHSWarriorCheer</td>\n",
       "      <td>63</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159972</th>\n",
       "      <td>Emkaylynn</td>\n",
       "      <td>589</td>\n",
       "      <td>589.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159973</th>\n",
       "      <td>baby_simmer</td>\n",
       "      <td>136</td>\n",
       "      <td>136.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159974</th>\n",
       "      <td>ambrjet</td>\n",
       "      <td>536</td>\n",
       "      <td>536.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159975</th>\n",
       "      <td>stonecoldbetch</td>\n",
       "      <td>268</td>\n",
       "      <td>268.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159976</th>\n",
       "      <td>RenaeMEddy</td>\n",
       "      <td>2292</td>\n",
       "      <td>2292.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159977</th>\n",
       "      <td>THATSJUSTYA</td>\n",
       "      <td>1081</td>\n",
       "      <td>1081.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159978</th>\n",
       "      <td>CantuRandy2</td>\n",
       "      <td>714</td>\n",
       "      <td>714.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159979</th>\n",
       "      <td>BreanneMarie123</td>\n",
       "      <td>854</td>\n",
       "      <td>854.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159980</th>\n",
       "      <td>Emkaylynn</td>\n",
       "      <td>589</td>\n",
       "      <td>589.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159981</th>\n",
       "      <td>robson62_lew</td>\n",
       "      <td>375</td>\n",
       "      <td>375.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159982</th>\n",
       "      <td>baby_simmer</td>\n",
       "      <td>136</td>\n",
       "      <td>136.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159983</th>\n",
       "      <td>JulianneAlmasUS</td>\n",
       "      <td>12587</td>\n",
       "      <td>12587.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159984</th>\n",
       "      <td>RadicalRhymez</td>\n",
       "      <td>381</td>\n",
       "      <td>381.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159985</th>\n",
       "      <td>CantuRandy2</td>\n",
       "      <td>714</td>\n",
       "      <td>714.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159986</th>\n",
       "      <td>A5ELOS</td>\n",
       "      <td>436045</td>\n",
       "      <td>436045.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159987</th>\n",
       "      <td>huntingxchurch</td>\n",
       "      <td>4989</td>\n",
       "      <td>4989.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159988</th>\n",
       "      <td>AndreaDeckard</td>\n",
       "      <td>5187</td>\n",
       "      <td>5187.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159989</th>\n",
       "      <td>baby_simmer</td>\n",
       "      <td>136</td>\n",
       "      <td>136.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159990</th>\n",
       "      <td>Emkaylynn</td>\n",
       "      <td>589</td>\n",
       "      <td>589.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159991</th>\n",
       "      <td>robson62_lew</td>\n",
       "      <td>375</td>\n",
       "      <td>375.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159992</th>\n",
       "      <td>340937564</td>\n",
       "      <td>137</td>\n",
       "      <td>137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159993</th>\n",
       "      <td>CantuRandy2</td>\n",
       "      <td>714</td>\n",
       "      <td>714.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159994</th>\n",
       "      <td>NCquitestorm</td>\n",
       "      <td>5590</td>\n",
       "      <td>5590.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159995</th>\n",
       "      <td>huntingxchurch</td>\n",
       "      <td>4989</td>\n",
       "      <td>4989.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159996</th>\n",
       "      <td>baby_simmer</td>\n",
       "      <td>136</td>\n",
       "      <td>136.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159997</th>\n",
       "      <td>RenaeMEddy</td>\n",
       "      <td>2292</td>\n",
       "      <td>2292.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159998</th>\n",
       "      <td>whoissizzle</td>\n",
       "      <td>36783</td>\n",
       "      <td>36783.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159999</th>\n",
       "      <td>Emkaylynn</td>\n",
       "      <td>589</td>\n",
       "      <td>589.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>160000 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               USERNAME FRIENDS   numbers\n",
       "0           George_k410     248     248.0\n",
       "1       babalola_biodun     767     767.0\n",
       "2        Kitchen_sink33     335     335.0\n",
       "3           George_k410     248     248.0\n",
       "4       BIackEverything   88096   88096.0\n",
       "5       babalola_biodun     767     767.0\n",
       "6        Kitchen_sink33     335     335.0\n",
       "7              timkaine     719     719.0\n",
       "8        tonycottee1986    1238    1238.0\n",
       "9              wonswoon    2407    2407.0\n",
       "10            queenncam      53      53.0\n",
       "11         Matt_one_kid     328     328.0\n",
       "12       PR1investments     255     255.0\n",
       "13           RogersTimi     200     200.0\n",
       "14      babalola_biodun     767     767.0\n",
       "15             schoneck     343     343.0\n",
       "16             wonswoon    2407    2407.0\n",
       "17             timkaine     719     719.0\n",
       "18       lindseyelsbury     673     673.0\n",
       "19           __samone__     259     259.0\n",
       "20         Matt_one_kid     328     328.0\n",
       "21             schoneck     343     343.0\n",
       "22        EmilySantelli     184     184.0\n",
       "23       lindseyelsbury     673     673.0\n",
       "24             wonswoon    2407    2407.0\n",
       "25      AllHipHopModels   38025   38025.0\n",
       "26      BIackEverything   88096   88096.0\n",
       "27         Kelencoleman     400     400.0\n",
       "28           __samone__     259     259.0\n",
       "29      BIackEverything   88096   88096.0\n",
       "...                 ...     ...       ...\n",
       "159970  BreanneMarie123     854     854.0\n",
       "159971  FHSWarriorCheer      63      63.0\n",
       "159972        Emkaylynn     589     589.0\n",
       "159973      baby_simmer     136     136.0\n",
       "159974          ambrjet     536     536.0\n",
       "159975   stonecoldbetch     268     268.0\n",
       "159976       RenaeMEddy    2292    2292.0\n",
       "159977      THATSJUSTYA    1081    1081.0\n",
       "159978      CantuRandy2     714     714.0\n",
       "159979  BreanneMarie123     854     854.0\n",
       "159980        Emkaylynn     589     589.0\n",
       "159981     robson62_lew     375     375.0\n",
       "159982      baby_simmer     136     136.0\n",
       "159983  JulianneAlmasUS   12587   12587.0\n",
       "159984    RadicalRhymez     381     381.0\n",
       "159985      CantuRandy2     714     714.0\n",
       "159986           A5ELOS  436045  436045.0\n",
       "159987   huntingxchurch    4989    4989.0\n",
       "159988    AndreaDeckard    5187    5187.0\n",
       "159989      baby_simmer     136     136.0\n",
       "159990        Emkaylynn     589     589.0\n",
       "159991     robson62_lew     375     375.0\n",
       "159992        340937564     137     137.0\n",
       "159993      CantuRandy2     714     714.0\n",
       "159994     NCquitestorm    5590    5590.0\n",
       "159995   huntingxchurch    4989    4989.0\n",
       "159996      baby_simmer     136     136.0\n",
       "159997       RenaeMEddy    2292    2292.0\n",
       "159998      whoissizzle   36783   36783.0\n",
       "159999        Emkaylynn     589     589.0\n",
       "\n",
       "[160000 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = pd.read_sql_query('SELECT USERNAME, FRIENDS, CAST(data.FRIENDS as int64) as numbers '\n",
    "                       'FROM data ', disk_engine)\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~sfisoc/6.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_sql_query('SELECT USERNAME, FRIENDS, IS_FRIEND, MAX(FRIENDS) as `checkFriend`'\n",
    "                       'FROM data '\n",
    "                       'GROUP BY USERNAME '\n",
    "                       'ORDER BY -checkFriend', disk_engine)\n",
    "\n",
    "\n",
    "xData = df.IS_FRIEND\n",
    "yData = df.FRIENDS\n",
    "zData = df.USERNAME\n",
    "trace = Scatter(\n",
    "    x = yData,\n",
    "    y = xData,\n",
    "    mode = 'markers'\n",
    ")\n",
    "\n",
    "layout = Layout(\n",
    "    xaxis=dict(\n",
    "        range=[0, df.checkFriend]\n",
    "    ),\n",
    "    yaxis=dict(\n",
    "        range=[0, 1]\n",
    "    )\n",
    "    \n",
    ")\n",
    "\n",
    "data = [trace]\n",
    "\n",
    "fig = Figure(data = data, layout= layout)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "py.iplot(fig, filename='useridvisfriend')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [Root]",
   "language": "python",
   "name": "Python [Root]"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}