twitter_mine_bag_of_words.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Purpose: Mine Tweets from trending topics, clean and find most used words with in those tweets, output to DF"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Some Boring Twitter Rules\n",
    "\n",
    "Twitter says they will rate limit your requests:\n",
    "\n",
    ">When using application-only authentication, rate limits are determined globally for the entire application. If a method allows for 15 requests per rate limit window, then it allows you to make 15 requests per window — on behalf of your application. This limit is considered completely separately from per-user limits. https://dev.twitter.com/rest/public/rate-limiting\n",
    "\n",
    "Here's a quick overview of what Twitter says are \"the rulez\":\n",
    "\n",
    "![](https://snag.gy/yJ6vIH.jpg)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import twitter, re, datetime, pandas as pd\n",
    "import tweepy\n",
    "import datetime\n",
    "import re\n",
    "import HTMLParser\n",
    "import numpy as np\n",
    "from nltk.corpus import stopwords # Import the stop word list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<module 'twitter.api' from '/Users/Stav/anaconda2/lib/python2.7/site-packages/twitter/api.pyc'>"
      ]
     },
     "execution_count": 239,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "twitter.api "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "twitter_keys = {\n",
    "    'consumer_key':        'dhZbJCvhl79hmz6eGpsy6WRfY',\n",
    "    'consumer_secret':     'TaBDp9pXOlQcxbhELuyzDFilEz5onut1hDtuOAPOYmQu15Veby',\n",
    "    'access_token_key':    '729878120338063360-RuyFwFOyq9rsRk0X8FMhkbnBxeasaL0',\n",
    "    'access_token_secret': 'TRuE65px8xUj76ayD2ffiv3lhabTTFHBHp1R6fa92k8oU'\n",
    "}\n",
    "\n",
    "auth = tweepy.OAuthHandler(twitter_keys['consumer_key'], twitter_keys['consumer_secret'])\n",
    "auth.set_access_token(twitter_keys['access_token_key'], twitter_keys['access_token_secret'])\n",
    "\n",
    "api = tweepy.API(auth)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for tweet in tweepy.Cursor(api.search, q=\"#BaeIn3Words\", lang=\"tr\").items():\n",
    "    print tweet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 314,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "%23VeteransDay http://twitter.com/search?q=%23VeteransDay\n",
      "%23UFC205 http://twitter.com/search?q=%23UFC205\n",
      "%23Chicago http://twitter.com/search?q=%23Chicago\n",
      "%22Electoral+College%22 http://twitter.com/search?q=%22Electoral+College%22\n",
      "Clemson http://twitter.com/search?q=Clemson\n",
      "Auburn http://twitter.com/search?q=Auburn\n",
      "%23USAvMEX http://twitter.com/search?q=%23USAvMEX\n",
      "%23MannequinChallenge http://twitter.com/search?q=%23MannequinChallenge\n",
      "%23GetRichIn4Words http://twitter.com/search?q=%23GetRichIn4Words\n",
      "%23NationalPizzaDay http://twitter.com/search?q=%23NationalPizzaDay\n",
      "%23UnitedAgainstHate http://twitter.com/search?q=%23UnitedAgainstHate\n"
     ]
    }
   ],
   "source": [
    "trending_list = []\n",
    "trends = api.trends_place(2487956)\n",
    "for i in trends:\n",
    "    for trend in i['trends']:\n",
    "        if trend['tweet_volume'] > 1000:\n",
    "            print str(trend['query']), trend['url']\n",
    "            trending_list.append(trend['query'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import twitter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 469,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0            VeteransDay\n",
       "1                 UFC205\n",
       "2                Chicago\n",
       "3      Electoral+College\n",
       "4                Clemson\n",
       "5                 Auburn\n",
       "6                USAvMEX\n",
       "7     MannequinChallenge\n",
       "8        GetRichIn4Words\n",
       "9       NationalPizzaDay\n",
       "10     UnitedAgainstHate\n",
       "dtype: object"
      ]
     },
     "execution_count": 469,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "trending_list = pd.Series(trending_list).apply(lambda x: x.encode('utf-8'))\n",
    "trending_list = trending_list.apply(lambda x: re.sub(\"[^a-zA-Z0-9\\+]\", \"\",x))\n",
    "trending_list = trending_list.apply(lambda x: x.replace('23',''))\n",
    "trending_list = trending_list.apply(lambda x: x.replace('22',''))\n",
    "\n",
    "trending_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "twitter_keys = {\n",
    "    'consumer_key':        'dhZbJCvhl79hmz6eGpsy6WRfY',\n",
    "    'consumer_secret':     'TaBDp9pXOlQcxbhELuyzDFilEz5onut1hDtuOAPOYmQu15Veby',\n",
    "    'access_token_key':    '729878120338063360-RuyFwFOyq9rsRk0X8FMhkbnBxeasaL0',\n",
    "    'access_token_secret': 'TRuE65px8xUj76ayD2ffiv3lhabTTFHBHp1R6fa92k8oU'\n",
    "}\n",
    "\n",
    "api = twitter.Api(\n",
    "    consumer_key         =   twitter_keys['consumer_key'],\n",
    "    consumer_secret      =   twitter_keys['consumer_secret'],\n",
    "    access_token_key     =   twitter_keys['access_token_key'],\n",
    "    access_token_secret  =   twitter_keys['access_token_secret']\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 317,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "class twitterminer():\n",
    "\n",
    "    request_limit   =   200 \n",
    "    api             =   False\n",
    "    data            =   []\n",
    "    \n",
    "    twitter_keys = {\n",
    "    'consumer_key':        'dhZbJCvhl79hmz6eGpsy6WRfY',\n",
    "    'consumer_secret':     'TaBDp9pXOlQcxbhELuyzDFilEz5onut1hDtuOAPOYmQu15Veby',\n",
    "    'access_token_key':    '729878120338063360-RuyFwFOyq9rsRk0X8FMhkbnBxeasaL0',\n",
    "    'access_token_secret': 'TRuE65px8xUj76ayD2ffiv3lhabTTFHBHp1R6fa92k8oU', \n",
    "}\n",
    "\n",
    "    \n",
    "    def __init__(self,  request_limit = 200):\n",
    "        \n",
    "        self.request_limit = request_limit\n",
    "        \n",
    "        # This sets the twitter API object for use internall within the class\n",
    "        self.set_api()\n",
    "        \n",
    "    def set_api(self):\n",
    "        \n",
    "        self.api = twitter.Api(\n",
    "            consumer_key         =   self.twitter_keys['consumer_key'],\n",
    "            consumer_secret      =   self.twitter_keys['consumer_secret'],\n",
    "            access_token_key     =   self.twitter_keys['access_token_key'],\n",
    "            access_token_secret  =   self.twitter_keys['access_token_secret']\n",
    "        ,sleep_on_rate_limit=True)\n",
    "\n",
    "    def mine_search_tweets(self, term, mine_rewteets=False, max_pages = 10):\n",
    "\n",
    "        data           =  []\n",
    "        last_tweet_id  =  False\n",
    "        page           =  0\n",
    "        \n",
    "        while page <= max_pages:\n",
    "            \n",
    "            if last_tweet_id:\n",
    "                statuses   =   self.api.GetSearch(term, count=self.request_limit, max_id=last_tweet_id - 1)        \n",
    "            else:\n",
    "                statuses   =   self.api.GetSearch(term, count=self.request_limit)\n",
    "                \n",
    "            for item in statuses:\n",
    "                \n",
    "                mined = {\n",
    "                    'tweet_id': item.id,\n",
    "                    'handle': item.user.name,\n",
    "                    'retweet_count': item.retweet_count,\n",
    "                    'text': item.text,\n",
    "                    'mined_at': datetime.datetime.now(),\n",
    "                    'created_at': item.created_at,\n",
    "                }\n",
    "    \n",
    "               \n",
    "                \n",
    "                last_tweet_id =   item.id\n",
    "                data.append(mined)\n",
    "                \n",
    "            page +=  1\n",
    "\n",
    "        return data\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 470,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "VeteransDay\n",
      "UFC205\n",
      "Chicago\n",
      "Electoral+College\n",
      "Clemson\n",
      "Auburn\n",
      "USAvMEX\n",
      "MannequinChallenge\n",
      "GetRichIn4Words\n",
      "NationalPizzaDay\n",
      "UnitedAgainstHate\n"
     ]
    }
   ],
   "source": [
    "miner = twitterminer(request_limit=200)\n",
    "tweets_mined_dict = {}\n",
    "for trend in trending_list:\n",
    "    print trend\n",
    "    tweets_mined_dict[trend] = miner.mine_search_tweets(term = trend, max_pages = 10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 434,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "%23NationalPizzaDay\n",
      "%23UFC205\n",
      "%23USAvMEX\n",
      "%23UnitedAgainstHate\n",
      "%23GetRichIn4Words\n",
      "%23MannequinChallenge\n",
      "Auburn\n",
      "%23VeteransDay\n",
      "%23Chicago\n",
      "Clemson\n",
      "%22Electoral+College%22\n"
     ]
    }
   ],
   "source": [
    "extracted_tweets_dict = {}\n",
    "for mined in tweets_mined_dict:\n",
    "    print mined\n",
    "    extracted_tweets_dict[mined] = []\n",
    "    for indiv_tweet in tweets_mined_dict[mined]:\n",
    "        extracted_tweets_dict[mined].append(indiv_tweet['text'])\n",
    "\n",
    "tweets_df_dict = {}\n",
    "for name in extracted_tweets_dict:\n",
    "    tweets_df_dict[name] = cloud_tweets(extracted_tweets_dict[name])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 445,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "from sklearn.feature_extraction.text import CountVectorizer\n",
    "tweet_df_dict = {}\n",
    "stops = set(stopwords.words(\"english\")) \n",
    "\n",
    "def cloud_tweets(tweets):\n",
    "\n",
    "    import re\n",
    "    from nltk.corpus import stopwords\n",
    "\n",
    "\n",
    "    clean_tweets = pd.Series(tweets).apply(lambda x: re.sub('https(.)*', '', x, flags=re.MULTILINE))\n",
    "    #print clean_tweets[0:5]\n",
    "    clean_tweets = clean_tweets.apply(lambda x: x.split(':')[1] if \":\" in x else x )\n",
    "    clean_tweets = clean_tweets.apply(lambda x: str(x.encode('utf-8')))\n",
    "    clean_tweets = clean_tweets.apply(lambda x: re.sub(\"[^a-zA-Z0-9]\", \" \",x))\n",
    "    clean_tweets = clean_tweets.apply(lambda x: x.replace('CatsIn5Words',''))\n",
    "# camel case\n",
    "    clean_tweets = clean_tweets.apply(lambda x: re.sub(\"([A-Z])\",\" \\g<0>\",x))\n",
    "    clean_tweets = clean_tweets.apply(lambda x: x.lower().split())\n",
    "    clean_tweets = clean_tweets.apply(lambda x: [w for w in x if not w in stops])\n",
    "    clean_tweets = clean_tweets.apply(lambda x: \" \".join(x))\n",
    "    \n",
    "\n",
    "    word_df = bag_words(clean_tweets)\n",
    "    return word_df\n",
    "def bag_words(clean_tweets):\n",
    "    # Initialize the \"CountVectorizer\" object, which is scikit-learn's\n",
    "    # bag of words tool.  \n",
    "    vectorizer = CountVectorizer(analyzer = \"word\",   \\\n",
    "                                 tokenizer = None,    \\\n",
    "                                 preprocessor = None, \\\n",
    "                                 stop_words = None,   \\\n",
    "                                 max_features = 5000) \n",
    "\n",
    "    # fit_transform() does two functions: First, it fits the model\n",
    "    # and learns the vocabulary; second, it transforms our training data\n",
    "    # into feature vectors. The input to fit_transform should be a list of \n",
    "    # strings.\n",
    "    train_data_features = vectorizer.fit_transform(clean_tweets)\n",
    "\n",
    "    # Numpy arrays are easy to work with, so convert the result to an \n",
    "    # array\n",
    "    train_data_features = train_data_features.toarray()\n",
    "\n",
    "    # Take a look at the words in the vocabulary\n",
    "    vocab = vectorizer.get_feature_names()\n",
    "\n",
    "    # Sum up the counts of each vocabulary word\n",
    "    dist = np.sum(train_data_features, axis=0)\n",
    "\n",
    "    # For each, print the vocabulary word and the number of times it \n",
    "    # appears in the training set\n",
    "\n",
    "    count_list = []\n",
    "    word_list = []\n",
    "    for tag, count in zip(vocab, dist):\n",
    "        #print count, tag\n",
    "        count_list.append(count)\n",
    "        word_list.append(tag)\n",
    "\n",
    "    import pandas as pd\n",
    "    word_df = pd.DataFrame({name: word_list,'count': count_list})\n",
    "\n",
    "    word_df = word_df.set_index('count').sort_index(ascending = False)\n",
    "    \n",
    "    return word_df\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tweets_df_dict[]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 441,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "short_cloud = {}\n",
    "new_cloud = {}\n",
    "for i in tweets_df_dict:\n",
    "    try:\n",
    "        new_cloud[i] = tweets_df_dict[i].set_index(i).drop('xa').reset_index().sort_values('count', ascending = False)   \n",
    "        short_cloud = new_cloud[i][0:20]\n",
    "    except:\n",
    "        new_cloud[i] = tweets_df_dict[i].reset_index().sort_values('count', ascending = False)\n",
    "        short_cloud[i] = new_cloud[i][0:20]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 443,
   "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>count</th>\n",
       "      <th>%22Electoral+College%22</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>788</td>\n",
       "      <td>electoral</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>766</td>\n",
       "      <td>college</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>203</td>\n",
       "      <td>trump</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>184</td>\n",
       "      <td>vote</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>140</td>\n",
       "      <td>clinton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>120</td>\n",
       "      <td>sign</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>108</td>\n",
       "      <td>president</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>101</td>\n",
       "      <td>petition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>96</td>\n",
       "      <td>hillary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>90</td>\n",
       "      <td>popular</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>75</td>\n",
       "      <td>votes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>74</td>\n",
       "      <td>make</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>62</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>53</td>\n",
       "      <td>election</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>52</td>\n",
       "      <td>electors</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>52</td>\n",
       "      <td>december</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>45</td>\n",
       "      <td>elector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>44</td>\n",
       "      <td>like</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>41</td>\n",
       "      <td>party</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>40</td>\n",
       "      <td>called</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    count %22Electoral+College%22\n",
       "0     788               electoral\n",
       "1     766                 college\n",
       "2     203                   trump\n",
       "3     184                    vote\n",
       "4     140                 clinton\n",
       "5     120                    sign\n",
       "6     108               president\n",
       "7     101                petition\n",
       "8      96                 hillary\n",
       "9      90                 popular\n",
       "10     75                   votes\n",
       "11     74                    make\n",
       "12     62                      19\n",
       "13     53                election\n",
       "14     52                electors\n",
       "15     52                december\n",
       "16     45                 elector\n",
       "17     44                    like\n",
       "18     41                   party\n",
       "20     40                  called"
      ]
     },
     "execution_count": 443,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "short_cloud['%22Electoral+College%22']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 466,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "initial_column = str(short_cloud.keys()[0])\n",
    "final_cloud_df = pd.DataFrame({initial_column:short_cloud[initial_column][initial_column],initial_column+'_count':short_cloud[initial_column]['count']})\n",
    "\n",
    "for i in short_cloud:\n",
    "    if i != initial_column:\n",
    "        \n",
    "        #print i, np.array(short_cloud[i]['word'])[0:5], pd.Series(short_cloud[i]['count'])[0:5]\n",
    "        final_cloud_df[i] = short_cloud[i][i].tolist()\n",
    "        final_cloud_df[i+'_count'] = short_cloud[i]['count'].tolist()\n",
    "    else:\n",
    "        continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 467,
   "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>%23NationalPizzaDay</th>\n",
       "      <th>%23NationalPizzaDay_count</th>\n",
       "      <th>%23UFC205</th>\n",
       "      <th>%23UFC205_count</th>\n",
       "      <th>%23USAvMEX</th>\n",
       "      <th>%23USAvMEX_count</th>\n",
       "      <th>%23Chicago</th>\n",
       "      <th>%23Chicago_count</th>\n",
       "      <th>%23GetRichIn4Words</th>\n",
       "      <th>%23GetRichIn4Words_count</th>\n",
       "      <th>...</th>\n",
       "      <th>Auburn</th>\n",
       "      <th>Auburn_count</th>\n",
       "      <th>%23VeteransDay</th>\n",
       "      <th>%23VeteransDay_count</th>\n",
       "      <th>%23UnitedAgainstHate</th>\n",
       "      <th>%23UnitedAgainstHate_count</th>\n",
       "      <th>Clemson</th>\n",
       "      <th>Clemson_count</th>\n",
       "      <th>%22Electoral+College%22</th>\n",
       "      <th>%22Electoral+College%22_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>pizza</td>\n",
       "      <td>1339</td>\n",
       "      <td>c205</td>\n",
       "      <td>695</td>\n",
       "      <td>av</td>\n",
       "      <td>1016</td>\n",
       "      <td>chicago</td>\n",
       "      <td>1071</td>\n",
       "      <td>get</td>\n",
       "      <td>1120</td>\n",
       "      <td>...</td>\n",
       "      <td>auburn</td>\n",
       "      <td>945</td>\n",
       "      <td>veterans</td>\n",
       "      <td>1131</td>\n",
       "      <td>hate</td>\n",
       "      <td>1130</td>\n",
       "      <td>clemson</td>\n",
       "      <td>815</td>\n",
       "      <td>electoral</td>\n",
       "      <td>788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>day</td>\n",
       "      <td>1036</td>\n",
       "      <td>ufc205</td>\n",
       "      <td>380</td>\n",
       "      <td>wall</td>\n",
       "      <td>322</td>\n",
       "      <td>trump</td>\n",
       "      <td>969</td>\n",
       "      <td>rich</td>\n",
       "      <td>1095</td>\n",
       "      <td>...</td>\n",
       "      <td>georgia</td>\n",
       "      <td>278</td>\n",
       "      <td>day</td>\n",
       "      <td>988</td>\n",
       "      <td>united</td>\n",
       "      <td>963</td>\n",
       "      <td>pitt</td>\n",
       "      <td>227</td>\n",
       "      <td>college</td>\n",
       "      <td>766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>national</td>\n",
       "      <td>1005</td>\n",
       "      <td>edgar</td>\n",
       "      <td>234</td>\n",
       "      <td>unity</td>\n",
       "      <td>279</td>\n",
       "      <td>providing</td>\n",
       "      <td>492</td>\n",
       "      <td>words</td>\n",
       "      <td>1048</td>\n",
       "      <td>...</td>\n",
       "      <td>game</td>\n",
       "      <td>152</td>\n",
       "      <td>thank</td>\n",
       "      <td>264</td>\n",
       "      <td>trump</td>\n",
       "      <td>365</td>\n",
       "      <td>pittsburgh</td>\n",
       "      <td>146</td>\n",
       "      <td>trump</td>\n",
       "      <td>203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>happy</td>\n",
       "      <td>162</td>\n",
       "      <td>frankie</td>\n",
       "      <td>218</td>\n",
       "      <td>divide</td>\n",
       "      <td>274</td>\n",
       "      <td>officers</td>\n",
       "      <td>491</td>\n",
       "      <td>in4</td>\n",
       "      <td>1044</td>\n",
       "      <td>...</td>\n",
       "      <td>since</td>\n",
       "      <td>95</td>\n",
       "      <td>served</td>\n",
       "      <td>135</td>\n",
       "      <td>protest</td>\n",
       "      <td>252</td>\n",
       "      <td>top</td>\n",
       "      <td>145</td>\n",
       "      <td>vote</td>\n",
       "      <td>184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>trump</td>\n",
       "      <td>60</td>\n",
       "      <td>khabib</td>\n",
       "      <td>182</td>\n",
       "      <td>mexico</td>\n",
       "      <td>130</td>\n",
       "      <td>protest</td>\n",
       "      <td>442</td>\n",
       "      <td>trump</td>\n",
       "      <td>91</td>\n",
       "      <td>...</td>\n",
       "      <td>13</td>\n",
       "      <td>89</td>\n",
       "      <td>trump</td>\n",
       "      <td>128</td>\n",
       "      <td>downtown</td>\n",
       "      <td>214</td>\n",
       "      <td>death</td>\n",
       "      <td>132</td>\n",
       "      <td>clinton</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>nationalpizzaday</td>\n",
       "      <td>57</td>\n",
       "      <td>stephens</td>\n",
       "      <td>104</td>\n",
       "      <td>la</td>\n",
       "      <td>114</td>\n",
       "      <td>thank</td>\n",
       "      <td>425</td>\n",
       "      <td>give</td>\n",
       "      <td>76</td>\n",
       "      <td>...</td>\n",
       "      <td>half</td>\n",
       "      <td>80</td>\n",
       "      <td>honor</td>\n",
       "      <td>114</td>\n",
       "      <td>000</td>\n",
       "      <td>189</td>\n",
       "      <td>valley</td>\n",
       "      <td>131</td>\n",
       "      <td>sign</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>get</td>\n",
       "      <td>54</td>\n",
       "      <td>garden</td>\n",
       "      <td>95</td>\n",
       "      <td>el</td>\n",
       "      <td>113</td>\n",
       "      <td>donald</td>\n",
       "      <td>424</td>\n",
       "      <td>money</td>\n",
       "      <td>71</td>\n",
       "      <td>...</td>\n",
       "      <td>beat</td>\n",
       "      <td>76</td>\n",
       "      <td>service</td>\n",
       "      <td>110</td>\n",
       "      <td>100</td>\n",
       "      <td>189</td>\n",
       "      <td>playoff</td>\n",
       "      <td>126</td>\n",
       "      <td>president</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>today</td>\n",
       "      <td>52</td>\n",
       "      <td>guy</td>\n",
       "      <td>91</td>\n",
       "      <td>miseleccionmx</td>\n",
       "      <td>109</td>\n",
       "      <td>spread</td>\n",
       "      <td>424</td>\n",
       "      <td>start</td>\n",
       "      <td>70</td>\n",
       "      <td>...</td>\n",
       "      <td>team</td>\n",
       "      <td>76</td>\n",
       "      <td>amp</td>\n",
       "      <td>99</td>\n",
       "      <td>wilshire</td>\n",
       "      <td>183</td>\n",
       "      <td>opponent</td>\n",
       "      <td>126</td>\n",
       "      <td>petition</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>like</td>\n",
       "      <td>45</td>\n",
       "      <td>team</td>\n",
       "      <td>89</td>\n",
       "      <td>por</td>\n",
       "      <td>101</td>\n",
       "      <td>real</td>\n",
       "      <td>424</td>\n",
       "      <td>clinton</td>\n",
       "      <td>66</td>\n",
       "      <td>...</td>\n",
       "      <td>today</td>\n",
       "      <td>73</td>\n",
       "      <td>today</td>\n",
       "      <td>91</td>\n",
       "      <td>notmypresident</td>\n",
       "      <td>180</td>\n",
       "      <td>since</td>\n",
       "      <td>123</td>\n",
       "      <td>hillary</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>week</td>\n",
       "      <td>45</td>\n",
       "      <td>vs</td>\n",
       "      <td>78</td>\n",
       "      <td>victoria</td>\n",
       "      <td>101</td>\n",
       "      <td>tower</td>\n",
       "      <td>82</td>\n",
       "      <td>foundation</td>\n",
       "      <td>56</td>\n",
       "      <td>...</td>\n",
       "      <td>win</td>\n",
       "      <td>68</td>\n",
       "      <td>country</td>\n",
       "      <td>79</td>\n",
       "      <td>tr</td>\n",
       "      <td>171</td>\n",
       "      <td>win</td>\n",
       "      <td>104</td>\n",
       "      <td>popular</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>election</td>\n",
       "      <td>43</td>\n",
       "      <td>fight</td>\n",
       "      <td>77</td>\n",
       "      <td>bien</td>\n",
       "      <td>99</td>\n",
       "      <td>jobs</td>\n",
       "      <td>76</td>\n",
       "      <td>pay</td>\n",
       "      <td>45</td>\n",
       "      <td>...</td>\n",
       "      <td>amp</td>\n",
       "      <td>66</td>\n",
       "      <td>happy</td>\n",
       "      <td>74</td>\n",
       "      <td>shutting</td>\n",
       "      <td>157</td>\n",
       "      <td>lost</td>\n",
       "      <td>104</td>\n",
       "      <td>votes</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>explains</td>\n",
       "      <td>43</td>\n",
       "      <td>fighting</td>\n",
       "      <td>61</td>\n",
       "      <td>felicidades</td>\n",
       "      <td>97</td>\n",
       "      <td>trumpprotest</td>\n",
       "      <td>74</td>\n",
       "      <td>taxes</td>\n",
       "      <td>44</td>\n",
       "      <td>...</td>\n",
       "      <td>smith</td>\n",
       "      <td>66</td>\n",
       "      <td>serve</td>\n",
       "      <td>68</td>\n",
       "      <td>least</td>\n",
       "      <td>157</td>\n",
       "      <td>1st</td>\n",
       "      <td>91</td>\n",
       "      <td>make</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>celebrate</td>\n",
       "      <td>37</td>\n",
       "      <td>mc</td>\n",
       "      <td>60</td>\n",
       "      <td>oficialgio</td>\n",
       "      <td>96</td>\n",
       "      <td>government</td>\n",
       "      <td>73</td>\n",
       "      <td>invent</td>\n",
       "      <td>36</td>\n",
       "      <td>...</td>\n",
       "      <td>top</td>\n",
       "      <td>66</td>\n",
       "      <td>women</td>\n",
       "      <td>64</td>\n",
       "      <td>anti</td>\n",
       "      <td>130</td>\n",
       "      <td>loss</td>\n",
       "      <td>89</td>\n",
       "      <td>19</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>slice</td>\n",
       "      <td>36</td>\n",
       "      <td>gregor</td>\n",
       "      <td>58</td>\n",
       "      <td>muy</td>\n",
       "      <td>96</td>\n",
       "      <td>tonight</td>\n",
       "      <td>71</td>\n",
       "      <td>back</td>\n",
       "      <td>36</td>\n",
       "      <td>...</td>\n",
       "      <td>vs</td>\n",
       "      <td>65</td>\n",
       "      <td>men</td>\n",
       "      <td>60</td>\n",
       "      <td>misogyny</td>\n",
       "      <td>84</td>\n",
       "      <td>wins</td>\n",
       "      <td>88</td>\n",
       "      <td>election</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>let</td>\n",
       "      <td>35</td>\n",
       "      <td>moment</td>\n",
       "      <td>57</td>\n",
       "      <td>hecho</td>\n",
       "      <td>96</td>\n",
       "      <td>friends</td>\n",
       "      <td>69</td>\n",
       "      <td>sell</td>\n",
       "      <td>35</td>\n",
       "      <td>...</td>\n",
       "      <td>teams</td>\n",
       "      <td>64</td>\n",
       "      <td>military</td>\n",
       "      <td>59</td>\n",
       "      <td>amp</td>\n",
       "      <td>78</td>\n",
       "      <td>chances</td>\n",
       "      <td>85</td>\n",
       "      <td>electors</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>love</td>\n",
       "      <td>31</td>\n",
       "      <td>away</td>\n",
       "      <td>55</td>\n",
       "      <td>de</td>\n",
       "      <td>86</td>\n",
       "      <td>working</td>\n",
       "      <td>69</td>\n",
       "      <td>make</td>\n",
       "      <td>34</td>\n",
       "      <td>...</td>\n",
       "      <td>football</td>\n",
       "      <td>61</td>\n",
       "      <td>veteransday</td>\n",
       "      <td>59</td>\n",
       "      <td>never</td>\n",
       "      <td>77</td>\n",
       "      <td>look</td>\n",
       "      <td>84</td>\n",
       "      <td>december</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>podesta</td>\n",
       "      <td>31</td>\n",
       "      <td>seconds</td>\n",
       "      <td>51</td>\n",
       "      <td>trump</td>\n",
       "      <td>82</td>\n",
       "      <td>workin</td>\n",
       "      <td>68</td>\n",
       "      <td>take</td>\n",
       "      <td>33</td>\n",
       "      <td>...</td>\n",
       "      <td>alabama</td>\n",
       "      <td>57</td>\n",
       "      <td>veteran</td>\n",
       "      <td>46</td>\n",
       "      <td>body</td>\n",
       "      <td>73</td>\n",
       "      <td>live</td>\n",
       "      <td>81</td>\n",
       "      <td>elector</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>rough</td>\n",
       "      <td>31</td>\n",
       "      <td>tonight</td>\n",
       "      <td>49</td>\n",
       "      <td>love</td>\n",
       "      <td>76</td>\n",
       "      <td>place</td>\n",
       "      <td>68</td>\n",
       "      <td>trump101</td>\n",
       "      <td>33</td>\n",
       "      <td>...</td>\n",
       "      <td>clemson</td>\n",
       "      <td>57</td>\n",
       "      <td>donald</td>\n",
       "      <td>46</td>\n",
       "      <td>choice</td>\n",
       "      <td>72</td>\n",
       "      <td>2007</td>\n",
       "      <td>78</td>\n",
       "      <td>like</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>delivered</td>\n",
       "      <td>30</td>\n",
       "      <td>jeremy</td>\n",
       "      <td>48</td>\n",
       "      <td>mexican</td>\n",
       "      <td>70</td>\n",
       "      <td>telling</td>\n",
       "      <td>68</td>\n",
       "      <td>love</td>\n",
       "      <td>32</td>\n",
       "      <td>...</td>\n",
       "      <td>loss</td>\n",
       "      <td>56</td>\n",
       "      <td>vets</td>\n",
       "      <td>44</td>\n",
       "      <td>people</td>\n",
       "      <td>71</td>\n",
       "      <td>defeating</td>\n",
       "      <td>76</td>\n",
       "      <td>party</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>earned</td>\n",
       "      <td>30</td>\n",
       "      <td>fuck</td>\n",
       "      <td>46</td>\n",
       "      <td>soccer</td>\n",
       "      <td>69</td>\n",
       "      <td>eat</td>\n",
       "      <td>68</td>\n",
       "      <td>day</td>\n",
       "      <td>30</td>\n",
       "      <td>...</td>\n",
       "      <td>athens</td>\n",
       "      <td>55</td>\n",
       "      <td>freedom</td>\n",
       "      <td>41</td>\n",
       "      <td>rape</td>\n",
       "      <td>68</td>\n",
       "      <td>upset</td>\n",
       "      <td>72</td>\n",
       "      <td>called</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   %23NationalPizzaDay  %23NationalPizzaDay_count %23UFC205  %23UFC205_count  \\\n",
       "0                pizza                       1339      c205              695   \n",
       "1                  day                       1036    ufc205              380   \n",
       "2             national                       1005     edgar              234   \n",
       "3                happy                        162   frankie              218   \n",
       "4                trump                         60    khabib              182   \n",
       "5     nationalpizzaday                         57  stephens              104   \n",
       "6                  get                         54    garden               95   \n",
       "7                today                         52       guy               91   \n",
       "8                 like                         45      team               89   \n",
       "9                 week                         45        vs               78   \n",
       "10            election                         43     fight               77   \n",
       "11            explains                         43  fighting               61   \n",
       "12           celebrate                         37        mc               60   \n",
       "13               slice                         36    gregor               58   \n",
       "14                 let                         35    moment               57   \n",
       "16                love                         31      away               55   \n",
       "17             podesta                         31   seconds               51   \n",
       "15               rough                         31   tonight               49   \n",
       "18           delivered                         30    jeremy               48   \n",
       "19              earned                         30      fuck               46   \n",
       "\n",
       "       %23USAvMEX  %23USAvMEX_count    %23Chicago  %23Chicago_count  \\\n",
       "0              av              1016       chicago              1071   \n",
       "1            wall               322         trump               969   \n",
       "2           unity               279     providing               492   \n",
       "3          divide               274      officers               491   \n",
       "4          mexico               130       protest               442   \n",
       "5              la               114         thank               425   \n",
       "6              el               113        donald               424   \n",
       "7   miseleccionmx               109        spread               424   \n",
       "8             por               101          real               424   \n",
       "9        victoria               101         tower                82   \n",
       "10           bien                99          jobs                76   \n",
       "11    felicidades                97  trumpprotest                74   \n",
       "12     oficialgio                96    government                73   \n",
       "13            muy                96       tonight                71   \n",
       "14          hecho                96       friends                69   \n",
       "16             de                86       working                69   \n",
       "17          trump                82        workin                68   \n",
       "15           love                76         place                68   \n",
       "18        mexican                70       telling                68   \n",
       "19         soccer                69           eat                68   \n",
       "\n",
       "   %23GetRichIn4Words  %23GetRichIn4Words_count  \\\n",
       "0                 get                      1120   \n",
       "1                rich                      1095   \n",
       "2               words                      1048   \n",
       "3                 in4                      1044   \n",
       "4               trump                        91   \n",
       "5                give                        76   \n",
       "6               money                        71   \n",
       "7               start                        70   \n",
       "8             clinton                        66   \n",
       "9          foundation                        56   \n",
       "10                pay                        45   \n",
       "11              taxes                        44   \n",
       "12             invent                        36   \n",
       "13               back                        36   \n",
       "14               sell                        35   \n",
       "16               make                        34   \n",
       "17               take                        33   \n",
       "15           trump101                        33   \n",
       "18               love                        32   \n",
       "19                day                        30   \n",
       "\n",
       "                ...                  Auburn  Auburn_count %23VeteransDay  \\\n",
       "0               ...                  auburn           945       veterans   \n",
       "1               ...                 georgia           278            day   \n",
       "2               ...                    game           152          thank   \n",
       "3               ...                   since            95         served   \n",
       "4               ...                      13            89          trump   \n",
       "5               ...                    half            80          honor   \n",
       "6               ...                    beat            76        service   \n",
       "7               ...                    team            76            amp   \n",
       "8               ...                   today            73          today   \n",
       "9               ...                     win            68        country   \n",
       "10              ...                     amp            66          happy   \n",
       "11              ...                   smith            66          serve   \n",
       "12              ...                     top            66          women   \n",
       "13              ...                      vs            65            men   \n",
       "14              ...                   teams            64       military   \n",
       "16              ...                football            61    veteransday   \n",
       "17              ...                 alabama            57        veteran   \n",
       "15              ...                 clemson            57         donald   \n",
       "18              ...                    loss            56           vets   \n",
       "19              ...                  athens            55        freedom   \n",
       "\n",
       "    %23VeteransDay_count %23UnitedAgainstHate  %23UnitedAgainstHate_count  \\\n",
       "0                   1131                 hate                        1130   \n",
       "1                    988               united                         963   \n",
       "2                    264                trump                         365   \n",
       "3                    135              protest                         252   \n",
       "4                    128             downtown                         214   \n",
       "5                    114                  000                         189   \n",
       "6                    110                  100                         189   \n",
       "7                     99             wilshire                         183   \n",
       "8                     91       notmypresident                         180   \n",
       "9                     79                   tr                         171   \n",
       "10                    74             shutting                         157   \n",
       "11                    68                least                         157   \n",
       "12                    64                 anti                         130   \n",
       "13                    60             misogyny                          84   \n",
       "14                    59                  amp                          78   \n",
       "16                    59                never                          77   \n",
       "17                    46                 body                          73   \n",
       "15                    46               choice                          72   \n",
       "18                    44               people                          71   \n",
       "19                    41                 rape                          68   \n",
       "\n",
       "       Clemson  Clemson_count %22Electoral+College%22  \\\n",
       "0      clemson            815               electoral   \n",
       "1         pitt            227                 college   \n",
       "2   pittsburgh            146                   trump   \n",
       "3          top            145                    vote   \n",
       "4        death            132                 clinton   \n",
       "5       valley            131                    sign   \n",
       "6      playoff            126               president   \n",
       "7     opponent            126                petition   \n",
       "8        since            123                 hillary   \n",
       "9          win            104                 popular   \n",
       "10        lost            104                   votes   \n",
       "11         1st             91                    make   \n",
       "12        loss             89                      19   \n",
       "13        wins             88                election   \n",
       "14     chances             85                electors   \n",
       "16        look             84                december   \n",
       "17        live             81                 elector   \n",
       "15        2007             78                    like   \n",
       "18   defeating             76                   party   \n",
       "19       upset             72                  called   \n",
       "\n",
       "    %22Electoral+College%22_count  \n",
       "0                             788  \n",
       "1                             766  \n",
       "2                             203  \n",
       "3                             184  \n",
       "4                             140  \n",
       "5                             120  \n",
       "6                             108  \n",
       "7                             101  \n",
       "8                              96  \n",
       "9                              90  \n",
       "10                             75  \n",
       "11                             74  \n",
       "12                             62  \n",
       "13                             53  \n",
       "14                             52  \n",
       "16                             52  \n",
       "17                             45  \n",
       "15                             44  \n",
       "18                             41  \n",
       "19                             40  \n",
       "\n",
       "[20 rows x 22 columns]"
      ]
     },
     "execution_count": 467,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_cloud_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for i in short_cloud:\n",
    "    if i != 'Ichido':\n",
    "        #short_cloud[i].sort_values('count', ascending = False, inplace = True)\n",
    "        print i, np.array(short_cloud[i]['word'])[0:5], pd.Series(short_cloud[i]['count'])[0:5]\n",
    "        final_cloud_df[i] = short_cloud[i]['word'].tolist()\n",
    "        final_cloud_df[i+'_count'] = short_cloud[i]['count'].tolist()\n",
    "    else:\n",
    "        continue"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python2"
  },
  "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
}