data_processing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=pd.read_json(\"articles.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "tweet_influencer = pd.read_json('twitter_influencers.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(16655, 8)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweet_influencer.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.concat([pd.DataFrame(x) for x in tweet_influencer['social_referrals']], keys=tweet_influencer.index).reset_index(level=1,drop=True)\n",
    "tweet_influencer = tweet_influencer.drop('social_referrals', axis=1).join(df1).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>twitter_id</th>\n",
       "      <th>twitter_handle</th>\n",
       "      <th>likes_count</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>following_count</th>\n",
       "      <th>statuses_count</th>\n",
       "      <th>max_retweet_value</th>\n",
       "      <th>article_id</th>\n",
       "      <th>twitter_url</th>\n",
       "      <th>favourite_count</th>\n",
       "      <th>rt_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>description</th>\n",
       "      <th>article_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>759251</td>\n",
       "      <td>CNN</td>\n",
       "      <td>1437</td>\n",
       "      <td>41830534</td>\n",
       "      <td>1108</td>\n",
       "      <td>238672</td>\n",
       "      <td>97</td>\n",
       "      <td>336914f0-d1d6-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816358480277815296</td>\n",
       "      <td>110</td>\n",
       "      <td>97</td>\n",
       "      <td>1483470003000</td>\n",
       "      <td>United Airlines is investigating how a baggage...</td>\n",
       "      <td>http://www.cnn.com/2017/01/03/aviation/trapped...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1932657667</td>\n",
       "      <td>drudgeheadlines</td>\n",
       "      <td>273</td>\n",
       "      <td>168535</td>\n",
       "      <td>1</td>\n",
       "      <td>133104</td>\n",
       "      <td>68</td>\n",
       "      <td>0648ed40-d16a-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816301537752387584</td>\n",
       "      <td>63</td>\n",
       "      <td>68</td>\n",
       "      <td>1483456427000</td>\n",
       "      <td>A MAN has been arrested after he allegedly lau...</td>\n",
       "      <td>http://www.news.com.au/travel/travel-updates/i...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14293310</td>\n",
       "      <td>TIME</td>\n",
       "      <td>517</td>\n",
       "      <td>15966353</td>\n",
       "      <td>410</td>\n",
       "      <td>316491</td>\n",
       "      <td>58</td>\n",
       "      <td>97c8d860-d1ca-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816309417356460032</td>\n",
       "      <td>78</td>\n",
       "      <td>58</td>\n",
       "      <td>1483458306000</td>\n",
       "      <td>The flight reached an altitude of 27,000 feet.</td>\n",
       "      <td>http://fortune.com/2017/01/03/united-airlines-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14293310</td>\n",
       "      <td>TIME</td>\n",
       "      <td>517</td>\n",
       "      <td>15966353</td>\n",
       "      <td>410</td>\n",
       "      <td>316491</td>\n",
       "      <td>58</td>\n",
       "      <td>97c8d860-d1ca-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816480537397301252</td>\n",
       "      <td>67</td>\n",
       "      <td>48</td>\n",
       "      <td>1483499104000</td>\n",
       "      <td>The flight reached an altitude of 27,000 feet.</td>\n",
       "      <td>http://fortune.com/2017/01/03/united-airlines-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>116502194</td>\n",
       "      <td>globalnews</td>\n",
       "      <td>8901</td>\n",
       "      <td>329225</td>\n",
       "      <td>1142</td>\n",
       "      <td>198323</td>\n",
       "      <td>31</td>\n",
       "      <td>efc970d0-d1b9-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816309410117091329</td>\n",
       "      <td>9</td>\n",
       "      <td>31</td>\n",
       "      <td>1483458304000</td>\n",
       "      <td>United Airlines says it is investigating after...</td>\n",
       "      <td>http://globalnews.ca/news/3157095/united-airli...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   twitter_id   twitter_handle  likes_count  followers_count  following_count  \\\n",
       "0      759251              CNN         1437         41830534             1108   \n",
       "1  1932657667  drudgeheadlines          273           168535                1   \n",
       "2    14293310             TIME          517         15966353              410   \n",
       "3    14293310             TIME          517         15966353              410   \n",
       "4   116502194       globalnews         8901           329225             1142   \n",
       "\n",
       "   statuses_count  max_retweet_value                            article_id  \\\n",
       "0          238672                 97  336914f0-d1d6-11e6-ab89-c11942898bd6   \n",
       "1          133104                 68  0648ed40-d16a-11e6-ab89-c11942898bd6   \n",
       "2          316491                 58  97c8d860-d1ca-11e6-ab89-c11942898bd6   \n",
       "3          316491                 58  97c8d860-d1ca-11e6-ab89-c11942898bd6   \n",
       "4          198323                 31  efc970d0-d1b9-11e6-ab89-c11942898bd6   \n",
       "\n",
       "                                         twitter_url  favourite_count  \\\n",
       "0  https://twitter.com/any/status/816358480277815296              110   \n",
       "1  https://twitter.com/any/status/816301537752387584               63   \n",
       "2  https://twitter.com/any/status/816309417356460032               78   \n",
       "3  https://twitter.com/any/status/816480537397301252               67   \n",
       "4  https://twitter.com/any/status/816309410117091329                9   \n",
       "\n",
       "   rt_count     created_at                                        description  \\\n",
       "0        97  1483470003000  United Airlines is investigating how a baggage...   \n",
       "1        68  1483456427000  A MAN has been arrested after he allegedly lau...   \n",
       "2        58  1483458306000     The flight reached an altitude of 27,000 feet.   \n",
       "3        48  1483499104000     The flight reached an altitude of 27,000 feet.   \n",
       "4        31  1483458304000  United Airlines says it is investigating after...   \n",
       "\n",
       "                                         article_url  \n",
       "0  http://www.cnn.com/2017/01/03/aviation/trapped...  \n",
       "1  http://www.news.com.au/travel/travel-updates/i...  \n",
       "2  http://fortune.com/2017/01/03/united-airlines-...  \n",
       "3  http://fortune.com/2017/01/03/united-airlines-...  \n",
       "4  http://globalnews.ca/news/3157095/united-airli...  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweet_influencer.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(21972, 14)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tweet_influencer.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['publication_timestamp']= pd.to_datetime(data['publication_timestamp'], unit='ms')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data.sort_values(by='publication_timestamp',ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>article_id</th>\n",
       "      <th>authors</th>\n",
       "      <th>contents</th>\n",
       "      <th>excerpt</th>\n",
       "      <th>fb_data</th>\n",
       "      <th>has_video</th>\n",
       "      <th>headline</th>\n",
       "      <th>image_link</th>\n",
       "      <th>keywords</th>\n",
       "      <th>li_data</th>\n",
       "      <th>link</th>\n",
       "      <th>max_velocity</th>\n",
       "      <th>pi_data</th>\n",
       "      <th>publication_timestamp</th>\n",
       "      <th>sentiment</th>\n",
       "      <th>source</th>\n",
       "      <th>topics</th>\n",
       "      <th>tw_data</th>\n",
       "      <th>velocity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>76509020-d093-11e6-8d6f-9f4c7b0f94de</td>\n",
       "      <td>[Corazon Miller]</td>\n",
       "      <td>United Airlines New Year's Day flight bound f...</td>\n",
       "      <td>A man was arrested after it was claimed he had...</td>\n",
       "      <td>{'total_engagement_count': 1737, 'likes': 0, '...</td>\n",
       "      <td>False</td>\n",
       "      <td>Passenger arrested after United Airlines fligh...</td>\n",
       "      <td>http://media.nzherald.co.nz/webcontent/image/j...</td>\n",
       "      <td>Business, Aviation, Airlines, Australia, Sydne...</td>\n",
       "      <td>{'li_count': 6}</td>\n",
       "      <td>http://www.nzherald.co.nz/business/news/articl...</td>\n",
       "      <td>97.729933</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 02:30:44.514</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'nzherald.co.nz', 'link': 'http:...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 28, 'name':...</td>\n",
       "      <td>{'tw_count': 100}</td>\n",
       "      <td>0.115518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>b5c0ce50-d0a2-11e6-ab89-c11942898bd6</td>\n",
       "      <td>[Newstalk ZB Staff]</td>\n",
       "      <td>United Airlines New Year's Day flight bound f...</td>\n",
       "      <td>A United Airlines New Year's Day flight bound ...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines flight diverted to Auckland af...</td>\n",
       "      <td>http://www.newstalkzb.co.nz/media/16873988/uni...</td>\n",
       "      <td></td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>http://www.newstalkzb.co.nz/news/national/unit...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 04:19:53.397</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'newstalkzb.co.nz', 'link': 'htt...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}]</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>502a51e0-d0a9-11e6-ab89-c11942898bd6</td>\n",
       "      <td>[LEXI NELSON]</td>\n",
       "      <td>United Airlines New Year's Day flight set to t...</td>\n",
       "      <td>Unruly passenger goes on abusive, racist rant,...</td>\n",
       "      <td>{'total_engagement_count': 38, 'likes': 0, 'co...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines New Year's Day flight diverts ...</td>\n",
       "      <td>https://resources.stuff.co.nz/content/dam/imag...</td>\n",
       "      <td>Sport, Entertainment, Business, Motoring, Worl...</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>http://www.stuff.co.nz/travel/travel-troubles/...</td>\n",
       "      <td>108.874289</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 05:07:09.438</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'stuff.co.nz', 'link': 'http://s...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 443, 'name'...</td>\n",
       "      <td>{'tw_count': 40}</td>\n",
       "      <td>-4.779850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>61489c80-d0b7-11e6-919c-7789120ed8c4</td>\n",
       "      <td>None</td>\n",
       "      <td>You are not permitted to download, save or ema...</td>\n",
       "      <td>A man was arrested after an alleged \"racist ra...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>Unruly passenger arrested in Auckland</td>\n",
       "      <td>https://www.odt.co.nz/sites/default/files/stor...</td>\n",
       "      <td></td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://www.odt.co.nz/news/national/unruly-pas...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 06:42:16.000</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'odt.co.nz', 'link': 'https://od...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}]</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>2ad85e20-d0c0-11e6-ab89-c11942898bd6</td>\n",
       "      <td>None</td>\n",
       "      <td>olice have arrested a badly behaved passenger ...</td>\n",
       "      <td>Police have arrested a badly behaved passenger...</td>\n",
       "      <td>{'total_engagement_count': 3, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>Unruly passenger forces flight to divert</td>\n",
       "      <td>http://www.radionz.co.nz/assets/news_crops/180...</td>\n",
       "      <td>Radio New Zealand, Public Radio, News, Current...</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>http://www.radionz.co.nz/news/national/321644/...</td>\n",
       "      <td>0.649721</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 07:50:45.250</td>\n",
       "      <td>-1</td>\n",
       "      <td>{'publisher': 'radionz.co.nz', 'link': 'http:/...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 668, 'name'...</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.492976</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               article_id              authors  \\\n",
       "3    76509020-d093-11e6-8d6f-9f4c7b0f94de     [Corazon Miller]   \n",
       "142  b5c0ce50-d0a2-11e6-ab89-c11942898bd6  [Newstalk ZB Staff]   \n",
       "25   502a51e0-d0a9-11e6-ab89-c11942898bd6        [LEXI NELSON]   \n",
       "141  61489c80-d0b7-11e6-919c-7789120ed8c4                 None   \n",
       "64   2ad85e20-d0c0-11e6-ab89-c11942898bd6                 None   \n",
       "\n",
       "                                              contents  \\\n",
       "3     United Airlines New Year's Day flight bound f...   \n",
       "142   United Airlines New Year's Day flight bound f...   \n",
       "25   United Airlines New Year's Day flight set to t...   \n",
       "141  You are not permitted to download, save or ema...   \n",
       "64   olice have arrested a badly behaved passenger ...   \n",
       "\n",
       "                                               excerpt  \\\n",
       "3    A man was arrested after it was claimed he had...   \n",
       "142  A United Airlines New Year's Day flight bound ...   \n",
       "25   Unruly passenger goes on abusive, racist rant,...   \n",
       "141  A man was arrested after an alleged \"racist ra...   \n",
       "64   Police have arrested a badly behaved passenger...   \n",
       "\n",
       "                                               fb_data  has_video  \\\n",
       "3    {'total_engagement_count': 1737, 'likes': 0, '...      False   \n",
       "142  {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "25   {'total_engagement_count': 38, 'likes': 0, 'co...      False   \n",
       "141  {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "64   {'total_engagement_count': 3, 'likes': 0, 'com...      False   \n",
       "\n",
       "                                              headline  \\\n",
       "3    Passenger arrested after United Airlines fligh...   \n",
       "142  United Airlines flight diverted to Auckland af...   \n",
       "25   United Airlines New Year's Day flight diverts ...   \n",
       "141              Unruly passenger arrested in Auckland   \n",
       "64            Unruly passenger forces flight to divert   \n",
       "\n",
       "                                            image_link  \\\n",
       "3    http://media.nzherald.co.nz/webcontent/image/j...   \n",
       "142  http://www.newstalkzb.co.nz/media/16873988/uni...   \n",
       "25   https://resources.stuff.co.nz/content/dam/imag...   \n",
       "141  https://www.odt.co.nz/sites/default/files/stor...   \n",
       "64   http://www.radionz.co.nz/assets/news_crops/180...   \n",
       "\n",
       "                                              keywords          li_data  \\\n",
       "3    Business, Aviation, Airlines, Australia, Sydne...  {'li_count': 6}   \n",
       "142                                                     {'li_count': 0}   \n",
       "25   Sport, Entertainment, Business, Motoring, Worl...  {'li_count': 0}   \n",
       "141                                                     {'li_count': 0}   \n",
       "64   Radio New Zealand, Public Radio, News, Current...  {'li_count': 0}   \n",
       "\n",
       "                                                  link  max_velocity  \\\n",
       "3    http://www.nzherald.co.nz/business/news/articl...     97.729933   \n",
       "142  http://www.newstalkzb.co.nz/news/national/unit...      0.000000   \n",
       "25   http://www.stuff.co.nz/travel/travel-troubles/...    108.874289   \n",
       "141  https://www.odt.co.nz/news/national/unruly-pas...      0.000000   \n",
       "64   http://www.radionz.co.nz/news/national/321644/...      0.649721   \n",
       "\n",
       "             pi_data   publication_timestamp  sentiment  \\\n",
       "3    {'pi_count': 0} 2017-01-02 02:30:44.514          0   \n",
       "142  {'pi_count': 0} 2017-01-02 04:19:53.397          0   \n",
       "25   {'pi_count': 0} 2017-01-02 05:07:09.438          0   \n",
       "141  {'pi_count': 0} 2017-01-02 06:42:16.000          0   \n",
       "64   {'pi_count': 0} 2017-01-02 07:50:45.250         -1   \n",
       "\n",
       "                                                source  \\\n",
       "3    {'publisher': 'nzherald.co.nz', 'link': 'http:...   \n",
       "142  {'publisher': 'newstalkzb.co.nz', 'link': 'htt...   \n",
       "25   {'publisher': 'stuff.co.nz', 'link': 'http://s...   \n",
       "141  {'publisher': 'odt.co.nz', 'link': 'https://od...   \n",
       "64   {'publisher': 'radionz.co.nz', 'link': 'http:/...   \n",
       "\n",
       "                                                topics            tw_data  \\\n",
       "3    [{'id': 2, 'name': 'News'}, {'id': 28, 'name':...  {'tw_count': 100}   \n",
       "142                        [{'id': 2, 'name': 'News'}]    {'tw_count': 0}   \n",
       "25   [{'id': 2, 'name': 'News'}, {'id': 443, 'name'...   {'tw_count': 40}   \n",
       "141                        [{'id': 2, 'name': 'News'}]    {'tw_count': 0}   \n",
       "64   [{'id': 2, 'name': 'News'}, {'id': 668, 'name'...    {'tw_count': 0}   \n",
       "\n",
       "     velocity  \n",
       "3    0.115518  \n",
       "142  0.000000  \n",
       "25  -4.779850  \n",
       "141  0.000000  \n",
       "64   0.492976  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "c1=data[['article_id','publication_timestamp']]\n",
    "c2=tweet_influencer[['article_id','rt_count','favourite_count']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>article_id</th>\n",
       "      <th>publication_timestamp</th>\n",
       "      <th>rt_count</th>\n",
       "      <th>favourite_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76509020-d093-11e6-8d6f-9f4c7b0f94de</td>\n",
       "      <td>2017-01-02 02:30:44.514</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>502a51e0-d0a9-11e6-ab89-c11942898bd6</td>\n",
       "      <td>2017-01-02 05:07:09.438</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>aa204f40-d128-11e6-ab89-c11942898bd6</td>\n",
       "      <td>2017-01-02 20:18:46.452</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0648ed40-d16a-11e6-ab89-c11942898bd6</td>\n",
       "      <td>2017-01-03 04:06:38.356</td>\n",
       "      <td>68</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0648ed40-d16a-11e6-ab89-c11942898bd6</td>\n",
       "      <td>2017-01-03 04:06:38.356</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21967</th>\n",
       "      <td>a9cb80c0-6ab2-11e9-b09a-0fee65c1c139</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21968</th>\n",
       "      <td>fa44dfb0-6a26-11e9-b09a-0fee65c1c139</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21969</th>\n",
       "      <td>0967d860-6b5a-11e9-8e10-c9c12ecf2ad3</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21970</th>\n",
       "      <td>0967d860-6b5a-11e9-8e10-c9c12ecf2ad3</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21971</th>\n",
       "      <td>0967d860-6b5a-11e9-8e10-c9c12ecf2ad3</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21972 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 article_id   publication_timestamp  rt_count  \\\n",
       "0      76509020-d093-11e6-8d6f-9f4c7b0f94de 2017-01-02 02:30:44.514         1   \n",
       "1      502a51e0-d0a9-11e6-ab89-c11942898bd6 2017-01-02 05:07:09.438         3   \n",
       "2      aa204f40-d128-11e6-ab89-c11942898bd6 2017-01-02 20:18:46.452         1   \n",
       "3      0648ed40-d16a-11e6-ab89-c11942898bd6 2017-01-03 04:06:38.356        68   \n",
       "4      0648ed40-d16a-11e6-ab89-c11942898bd6 2017-01-03 04:06:38.356         7   \n",
       "...                                     ...                     ...       ...   \n",
       "21967  a9cb80c0-6ab2-11e9-b09a-0fee65c1c139                     NaT         2   \n",
       "21968  fa44dfb0-6a26-11e9-b09a-0fee65c1c139                     NaT         1   \n",
       "21969  0967d860-6b5a-11e9-8e10-c9c12ecf2ad3                     NaT         1   \n",
       "21970  0967d860-6b5a-11e9-8e10-c9c12ecf2ad3                     NaT         1   \n",
       "21971  0967d860-6b5a-11e9-8e10-c9c12ecf2ad3                     NaT         1   \n",
       "\n",
       "       favourite_count  \n",
       "0                    0  \n",
       "1                    1  \n",
       "2                    0  \n",
       "3                   63  \n",
       "4                    6  \n",
       "...                ...  \n",
       "21967                2  \n",
       "21968                1  \n",
       "21969                1  \n",
       "21970                2  \n",
       "21971                0  \n",
       "\n",
       "[21972 rows x 4 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "article_numberfavorite = c1.merge(c2,on='article_id',how='right')\n",
    "article_numberfavorite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "d = data['fb_data'].apply(pd.Series)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "article_fb = pd.concat([data.drop(columns=['fb_data']),d],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>article_id</th>\n",
       "      <th>authors</th>\n",
       "      <th>contents</th>\n",
       "      <th>excerpt</th>\n",
       "      <th>has_video</th>\n",
       "      <th>headline</th>\n",
       "      <th>image_link</th>\n",
       "      <th>keywords</th>\n",
       "      <th>li_data</th>\n",
       "      <th>link</th>\n",
       "      <th>...</th>\n",
       "      <th>sentiment</th>\n",
       "      <th>source</th>\n",
       "      <th>topics</th>\n",
       "      <th>tw_data</th>\n",
       "      <th>velocity</th>\n",
       "      <th>total_engagement_count</th>\n",
       "      <th>likes</th>\n",
       "      <th>comments</th>\n",
       "      <th>shares</th>\n",
       "      <th>fb_overperforming</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>76509020-d093-11e6-8d6f-9f4c7b0f94de</td>\n",
       "      <td>[Corazon Miller]</td>\n",
       "      <td>United Airlines New Year's Day flight bound f...</td>\n",
       "      <td>A man was arrested after it was claimed he had...</td>\n",
       "      <td>False</td>\n",
       "      <td>Passenger arrested after United Airlines fligh...</td>\n",
       "      <td>http://media.nzherald.co.nz/webcontent/image/j...</td>\n",
       "      <td>Business, Aviation, Airlines, Australia, Sydne...</td>\n",
       "      <td>{'li_count': 6}</td>\n",
       "      <td>http://www.nzherald.co.nz/business/news/articl...</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'nzherald.co.nz', 'link': 'http:...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 28, 'name':...</td>\n",
       "      <td>{'tw_count': 100}</td>\n",
       "      <td>0.115518</td>\n",
       "      <td>1737.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>b5c0ce50-d0a2-11e6-ab89-c11942898bd6</td>\n",
       "      <td>[Newstalk ZB Staff]</td>\n",
       "      <td>United Airlines New Year's Day flight bound f...</td>\n",
       "      <td>A United Airlines New Year's Day flight bound ...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines flight diverted to Auckland af...</td>\n",
       "      <td>http://www.newstalkzb.co.nz/media/16873988/uni...</td>\n",
       "      <td></td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>http://www.newstalkzb.co.nz/news/national/unit...</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'newstalkzb.co.nz', 'link': 'htt...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}]</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>502a51e0-d0a9-11e6-ab89-c11942898bd6</td>\n",
       "      <td>[LEXI NELSON]</td>\n",
       "      <td>United Airlines New Year's Day flight set to t...</td>\n",
       "      <td>Unruly passenger goes on abusive, racist rant,...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines New Year's Day flight diverts ...</td>\n",
       "      <td>https://resources.stuff.co.nz/content/dam/imag...</td>\n",
       "      <td>Sport, Entertainment, Business, Motoring, Worl...</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>http://www.stuff.co.nz/travel/travel-troubles/...</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'stuff.co.nz', 'link': 'http://s...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 443, 'name'...</td>\n",
       "      <td>{'tw_count': 40}</td>\n",
       "      <td>-4.779850</td>\n",
       "      <td>38.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>61489c80-d0b7-11e6-919c-7789120ed8c4</td>\n",
       "      <td>None</td>\n",
       "      <td>You are not permitted to download, save or ema...</td>\n",
       "      <td>A man was arrested after an alleged \"racist ra...</td>\n",
       "      <td>False</td>\n",
       "      <td>Unruly passenger arrested in Auckland</td>\n",
       "      <td>https://www.odt.co.nz/sites/default/files/stor...</td>\n",
       "      <td></td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://www.odt.co.nz/news/national/unruly-pas...</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'odt.co.nz', 'link': 'https://od...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}]</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>2ad85e20-d0c0-11e6-ab89-c11942898bd6</td>\n",
       "      <td>None</td>\n",
       "      <td>olice have arrested a badly behaved passenger ...</td>\n",
       "      <td>Police have arrested a badly behaved passenger...</td>\n",
       "      <td>False</td>\n",
       "      <td>Unruly passenger forces flight to divert</td>\n",
       "      <td>http://www.radionz.co.nz/assets/news_crops/180...</td>\n",
       "      <td>Radio New Zealand, Public Radio, News, Current...</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>http://www.radionz.co.nz/news/national/321644/...</td>\n",
       "      <td>...</td>\n",
       "      <td>-1</td>\n",
       "      <td>{'publisher': 'radionz.co.nz', 'link': 'http:/...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 668, 'name'...</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.492976</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               article_id              authors  \\\n",
       "3    76509020-d093-11e6-8d6f-9f4c7b0f94de     [Corazon Miller]   \n",
       "142  b5c0ce50-d0a2-11e6-ab89-c11942898bd6  [Newstalk ZB Staff]   \n",
       "25   502a51e0-d0a9-11e6-ab89-c11942898bd6        [LEXI NELSON]   \n",
       "141  61489c80-d0b7-11e6-919c-7789120ed8c4                 None   \n",
       "64   2ad85e20-d0c0-11e6-ab89-c11942898bd6                 None   \n",
       "\n",
       "                                              contents  \\\n",
       "3     United Airlines New Year's Day flight bound f...   \n",
       "142   United Airlines New Year's Day flight bound f...   \n",
       "25   United Airlines New Year's Day flight set to t...   \n",
       "141  You are not permitted to download, save or ema...   \n",
       "64   olice have arrested a badly behaved passenger ...   \n",
       "\n",
       "                                               excerpt  has_video  \\\n",
       "3    A man was arrested after it was claimed he had...      False   \n",
       "142  A United Airlines New Year's Day flight bound ...      False   \n",
       "25   Unruly passenger goes on abusive, racist rant,...      False   \n",
       "141  A man was arrested after an alleged \"racist ra...      False   \n",
       "64   Police have arrested a badly behaved passenger...      False   \n",
       "\n",
       "                                              headline  \\\n",
       "3    Passenger arrested after United Airlines fligh...   \n",
       "142  United Airlines flight diverted to Auckland af...   \n",
       "25   United Airlines New Year's Day flight diverts ...   \n",
       "141              Unruly passenger arrested in Auckland   \n",
       "64            Unruly passenger forces flight to divert   \n",
       "\n",
       "                                            image_link  \\\n",
       "3    http://media.nzherald.co.nz/webcontent/image/j...   \n",
       "142  http://www.newstalkzb.co.nz/media/16873988/uni...   \n",
       "25   https://resources.stuff.co.nz/content/dam/imag...   \n",
       "141  https://www.odt.co.nz/sites/default/files/stor...   \n",
       "64   http://www.radionz.co.nz/assets/news_crops/180...   \n",
       "\n",
       "                                              keywords          li_data  \\\n",
       "3    Business, Aviation, Airlines, Australia, Sydne...  {'li_count': 6}   \n",
       "142                                                     {'li_count': 0}   \n",
       "25   Sport, Entertainment, Business, Motoring, Worl...  {'li_count': 0}   \n",
       "141                                                     {'li_count': 0}   \n",
       "64   Radio New Zealand, Public Radio, News, Current...  {'li_count': 0}   \n",
       "\n",
       "                                                  link  ...  sentiment  \\\n",
       "3    http://www.nzherald.co.nz/business/news/articl...  ...          0   \n",
       "142  http://www.newstalkzb.co.nz/news/national/unit...  ...          0   \n",
       "25   http://www.stuff.co.nz/travel/travel-troubles/...  ...          0   \n",
       "141  https://www.odt.co.nz/news/national/unruly-pas...  ...          0   \n",
       "64   http://www.radionz.co.nz/news/national/321644/...  ...         -1   \n",
       "\n",
       "                                                source  \\\n",
       "3    {'publisher': 'nzherald.co.nz', 'link': 'http:...   \n",
       "142  {'publisher': 'newstalkzb.co.nz', 'link': 'htt...   \n",
       "25   {'publisher': 'stuff.co.nz', 'link': 'http://s...   \n",
       "141  {'publisher': 'odt.co.nz', 'link': 'https://od...   \n",
       "64   {'publisher': 'radionz.co.nz', 'link': 'http:/...   \n",
       "\n",
       "                                                topics            tw_data  \\\n",
       "3    [{'id': 2, 'name': 'News'}, {'id': 28, 'name':...  {'tw_count': 100}   \n",
       "142                        [{'id': 2, 'name': 'News'}]    {'tw_count': 0}   \n",
       "25   [{'id': 2, 'name': 'News'}, {'id': 443, 'name'...   {'tw_count': 40}   \n",
       "141                        [{'id': 2, 'name': 'News'}]    {'tw_count': 0}   \n",
       "64   [{'id': 2, 'name': 'News'}, {'id': 668, 'name'...    {'tw_count': 0}   \n",
       "\n",
       "     velocity total_engagement_count likes  comments  shares  \\\n",
       "3    0.115518                 1737.0   0.0       0.0     0.0   \n",
       "142  0.000000                    0.0   0.0       0.0     0.0   \n",
       "25  -4.779850                   38.0   0.0       0.0     0.0   \n",
       "141  0.000000                    0.0   0.0       0.0     0.0   \n",
       "64   0.492976                    3.0   0.0       0.0     0.0   \n",
       "\n",
       "     fb_overperforming  \n",
       "3                  NaN  \n",
       "142                NaN  \n",
       "25                 NaN  \n",
       "141                NaN  \n",
       "64                 NaN  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "article_fb.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>article_id</th>\n",
       "      <th>authors</th>\n",
       "      <th>contents</th>\n",
       "      <th>excerpt</th>\n",
       "      <th>fb_data</th>\n",
       "      <th>has_video</th>\n",
       "      <th>headline</th>\n",
       "      <th>image_link</th>\n",
       "      <th>keywords</th>\n",
       "      <th>li_data</th>\n",
       "      <th>link</th>\n",
       "      <th>max_velocity</th>\n",
       "      <th>pi_data</th>\n",
       "      <th>publication_timestamp</th>\n",
       "      <th>sentiment</th>\n",
       "      <th>source</th>\n",
       "      <th>topics</th>\n",
       "      <th>velocity</th>\n",
       "      <th>tw_count</th>\n",
       "      <th>tw_overperforming</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>76509020-d093-11e6-8d6f-9f4c7b0f94de</td>\n",
       "      <td>[Corazon Miller]</td>\n",
       "      <td>United Airlines New Year's Day flight bound f...</td>\n",
       "      <td>A man was arrested after it was claimed he had...</td>\n",
       "      <td>{'total_engagement_count': 1737, 'likes': 0, '...</td>\n",
       "      <td>False</td>\n",
       "      <td>Passenger arrested after United Airlines fligh...</td>\n",
       "      <td>http://media.nzherald.co.nz/webcontent/image/j...</td>\n",
       "      <td>Business, Aviation, Airlines, Australia, Sydne...</td>\n",
       "      <td>{'li_count': 6}</td>\n",
       "      <td>http://www.nzherald.co.nz/business/news/articl...</td>\n",
       "      <td>97.729933</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 02:30:44.514</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'nzherald.co.nz', 'link': 'http:...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 28, 'name':...</td>\n",
       "      <td>0.115518</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>b5c0ce50-d0a2-11e6-ab89-c11942898bd6</td>\n",
       "      <td>[Newstalk ZB Staff]</td>\n",
       "      <td>United Airlines New Year's Day flight bound f...</td>\n",
       "      <td>A United Airlines New Year's Day flight bound ...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines flight diverted to Auckland af...</td>\n",
       "      <td>http://www.newstalkzb.co.nz/media/16873988/uni...</td>\n",
       "      <td></td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>http://www.newstalkzb.co.nz/news/national/unit...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 04:19:53.397</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'newstalkzb.co.nz', 'link': 'htt...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}]</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>502a51e0-d0a9-11e6-ab89-c11942898bd6</td>\n",
       "      <td>[LEXI NELSON]</td>\n",
       "      <td>United Airlines New Year's Day flight set to t...</td>\n",
       "      <td>Unruly passenger goes on abusive, racist rant,...</td>\n",
       "      <td>{'total_engagement_count': 38, 'likes': 0, 'co...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines New Year's Day flight diverts ...</td>\n",
       "      <td>https://resources.stuff.co.nz/content/dam/imag...</td>\n",
       "      <td>Sport, Entertainment, Business, Motoring, Worl...</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>http://www.stuff.co.nz/travel/travel-troubles/...</td>\n",
       "      <td>108.874289</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 05:07:09.438</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'stuff.co.nz', 'link': 'http://s...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 443, 'name'...</td>\n",
       "      <td>-4.779850</td>\n",
       "      <td>40.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>61489c80-d0b7-11e6-919c-7789120ed8c4</td>\n",
       "      <td>None</td>\n",
       "      <td>You are not permitted to download, save or ema...</td>\n",
       "      <td>A man was arrested after an alleged \"racist ra...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>Unruly passenger arrested in Auckland</td>\n",
       "      <td>https://www.odt.co.nz/sites/default/files/stor...</td>\n",
       "      <td></td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://www.odt.co.nz/news/national/unruly-pas...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 06:42:16.000</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'odt.co.nz', 'link': 'https://od...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}]</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>2ad85e20-d0c0-11e6-ab89-c11942898bd6</td>\n",
       "      <td>None</td>\n",
       "      <td>olice have arrested a badly behaved passenger ...</td>\n",
       "      <td>Police have arrested a badly behaved passenger...</td>\n",
       "      <td>{'total_engagement_count': 3, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>Unruly passenger forces flight to divert</td>\n",
       "      <td>http://www.radionz.co.nz/assets/news_crops/180...</td>\n",
       "      <td>Radio New Zealand, Public Radio, News, Current...</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>http://www.radionz.co.nz/news/national/321644/...</td>\n",
       "      <td>0.649721</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 07:50:45.250</td>\n",
       "      <td>-1</td>\n",
       "      <td>{'publisher': 'radionz.co.nz', 'link': 'http:/...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 668, 'name'...</td>\n",
       "      <td>0.492976</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39095</th>\n",
       "      <td>22f57960-6b71-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>[Sean Broderick]</td>\n",
       "      <td>Avianca A320 Avianca Holdings Related Media Av...</td>\n",
       "      <td>United Airlines has not leveraged collateral i...</td>\n",
       "      <td>{'total_engagement_count': 12, 'likes': 4, 'co...</td>\n",
       "      <td>False</td>\n",
       "      <td>United takes no enforcement action in Avianca ...</td>\n",
       "      <td>https://atwonline.com/site-files/atwonline.com...</td>\n",
       "      <td>Airlines, Daily News, ATW Plus</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://atwonline.com/airlines/united-takes-no...</td>\n",
       "      <td>6.398294</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2019-04-30 17:55:27.350</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'atwonline.com', 'link': 'https:...</td>\n",
       "      <td>[{'id': 464, 'name': 'Transport'}, {'id': 459,...</td>\n",
       "      <td>0.025000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39090</th>\n",
       "      <td>dd80f8f0-6b7a-11e9-ada4-e30f7b916ef2</td>\n",
       "      <td>[Lewis Lazare]</td>\n",
       "      <td>The new service will mark Chicago-based United...</td>\n",
       "      <td>The new route is the 22nd international destin...</td>\n",
       "      <td>{'total_engagement_count': 82, 'likes': 48, 'c...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines puts tickets to South Africa o...</td>\n",
       "      <td>https://media.bizj.us/view/img/10999111/united...</td>\n",
       "      <td>United Continental Holdings, Boeing, Travel, T...</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://www.bizjournals.com/chicago/news/2019/...</td>\n",
       "      <td>0.653534</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2019-04-30 19:05:05.791</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'bizjournals.com', 'link': 'http...</td>\n",
       "      <td>[{'id': 28, 'name': 'Business'}, {'id': 443, '...</td>\n",
       "      <td>0.075000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39103</th>\n",
       "      <td>a76fe700-6b82-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>[Sean Broderick]</td>\n",
       "      <td>https://aviationweek Home &gt; AWIN_Commercial &gt; ...</td>\n",
       "      <td>United Airlines has not leveraged collateral i...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Takes No Action In Avianca Shareholder ...</td>\n",
       "      <td>https://aviationweek.com/site-files/aviationwe...</td>\n",
       "      <td>AWIN_Commercial</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://aviationweek.com/awincommercial/united...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2019-04-30 20:00:51.056</td>\n",
       "      <td>-1</td>\n",
       "      <td>{'publisher': 'aviationweek.com', 'link': 'htt...</td>\n",
       "      <td>[{'id': 464, 'name': 'Transport'}, {'id': 459,...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39102</th>\n",
       "      <td>f1f85140-6b96-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>None</td>\n",
       "      <td>Stearn Hodge says he will never forget the hum...</td>\n",
       "      <td>FULL TITLE: 'I had to crawl': Amputee seeks da...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>'I had to crawl': Amputee seeks damages after ...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://www.freerepublic.com/focus/f-chat/3745...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2019-04-30 22:26:06.036</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'freerepublic.com', 'link': 'htt...</td>\n",
       "      <td>[{'id': 19, 'name': 'Opinion'}, {'id': 24, 'na...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39101</th>\n",
       "      <td>8fda4f30-6b9c-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>[Erich Martin]</td>\n",
       "      <td>Last week, United Airlines took the first step...</td>\n",
       "      <td>Last week, United Airlines took the first step...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines Introduces New Fleet Livery | ...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://www.globaltravelerusa.com/united-airli...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2019-04-30 23:06:18.403</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'globaltravelerusa.com', 'link':...</td>\n",
       "      <td>[{'id': 443, 'name': 'Travel'}, {'id': 12, 'na...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>39109 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 article_id              authors  \\\n",
       "3      76509020-d093-11e6-8d6f-9f4c7b0f94de     [Corazon Miller]   \n",
       "142    b5c0ce50-d0a2-11e6-ab89-c11942898bd6  [Newstalk ZB Staff]   \n",
       "25     502a51e0-d0a9-11e6-ab89-c11942898bd6        [LEXI NELSON]   \n",
       "141    61489c80-d0b7-11e6-919c-7789120ed8c4                 None   \n",
       "64     2ad85e20-d0c0-11e6-ab89-c11942898bd6                 None   \n",
       "...                                     ...                  ...   \n",
       "39095  22f57960-6b71-11e9-9225-3fa9c6a5bcbe     [Sean Broderick]   \n",
       "39090  dd80f8f0-6b7a-11e9-ada4-e30f7b916ef2       [Lewis Lazare]   \n",
       "39103  a76fe700-6b82-11e9-9225-3fa9c6a5bcbe     [Sean Broderick]   \n",
       "39102  f1f85140-6b96-11e9-9225-3fa9c6a5bcbe                 None   \n",
       "39101  8fda4f30-6b9c-11e9-9225-3fa9c6a5bcbe       [Erich Martin]   \n",
       "\n",
       "                                                contents  \\\n",
       "3       United Airlines New Year's Day flight bound f...   \n",
       "142     United Airlines New Year's Day flight bound f...   \n",
       "25     United Airlines New Year's Day flight set to t...   \n",
       "141    You are not permitted to download, save or ema...   \n",
       "64     olice have arrested a badly behaved passenger ...   \n",
       "...                                                  ...   \n",
       "39095  Avianca A320 Avianca Holdings Related Media Av...   \n",
       "39090  The new service will mark Chicago-based United...   \n",
       "39103  https://aviationweek Home > AWIN_Commercial > ...   \n",
       "39102  Stearn Hodge says he will never forget the hum...   \n",
       "39101  Last week, United Airlines took the first step...   \n",
       "\n",
       "                                                 excerpt  \\\n",
       "3      A man was arrested after it was claimed he had...   \n",
       "142    A United Airlines New Year's Day flight bound ...   \n",
       "25     Unruly passenger goes on abusive, racist rant,...   \n",
       "141    A man was arrested after an alleged \"racist ra...   \n",
       "64     Police have arrested a badly behaved passenger...   \n",
       "...                                                  ...   \n",
       "39095  United Airlines has not leveraged collateral i...   \n",
       "39090  The new route is the 22nd international destin...   \n",
       "39103  United Airlines has not leveraged collateral i...   \n",
       "39102  FULL TITLE: 'I had to crawl': Amputee seeks da...   \n",
       "39101  Last week, United Airlines took the first step...   \n",
       "\n",
       "                                                 fb_data  has_video  \\\n",
       "3      {'total_engagement_count': 1737, 'likes': 0, '...      False   \n",
       "142    {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "25     {'total_engagement_count': 38, 'likes': 0, 'co...      False   \n",
       "141    {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "64     {'total_engagement_count': 3, 'likes': 0, 'com...      False   \n",
       "...                                                  ...        ...   \n",
       "39095  {'total_engagement_count': 12, 'likes': 4, 'co...      False   \n",
       "39090  {'total_engagement_count': 82, 'likes': 48, 'c...      False   \n",
       "39103  {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "39102  {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "39101  {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "\n",
       "                                                headline  \\\n",
       "3      Passenger arrested after United Airlines fligh...   \n",
       "142    United Airlines flight diverted to Auckland af...   \n",
       "25     United Airlines New Year's Day flight diverts ...   \n",
       "141                Unruly passenger arrested in Auckland   \n",
       "64              Unruly passenger forces flight to divert   \n",
       "...                                                  ...   \n",
       "39095  United takes no enforcement action in Avianca ...   \n",
       "39090  United Airlines puts tickets to South Africa o...   \n",
       "39103  United Takes No Action In Avianca Shareholder ...   \n",
       "39102  'I had to crawl': Amputee seeks damages after ...   \n",
       "39101  United Airlines Introduces New Fleet Livery | ...   \n",
       "\n",
       "                                              image_link  \\\n",
       "3      http://media.nzherald.co.nz/webcontent/image/j...   \n",
       "142    http://www.newstalkzb.co.nz/media/16873988/uni...   \n",
       "25     https://resources.stuff.co.nz/content/dam/imag...   \n",
       "141    https://www.odt.co.nz/sites/default/files/stor...   \n",
       "64     http://www.radionz.co.nz/assets/news_crops/180...   \n",
       "...                                                  ...   \n",
       "39095  https://atwonline.com/site-files/atwonline.com...   \n",
       "39090  https://media.bizj.us/view/img/10999111/united...   \n",
       "39103  https://aviationweek.com/site-files/aviationwe...   \n",
       "39102                                                      \n",
       "39101                                                      \n",
       "\n",
       "                                                keywords          li_data  \\\n",
       "3      Business, Aviation, Airlines, Australia, Sydne...  {'li_count': 6}   \n",
       "142                                                       {'li_count': 0}   \n",
       "25     Sport, Entertainment, Business, Motoring, Worl...  {'li_count': 0}   \n",
       "141                                                       {'li_count': 0}   \n",
       "64     Radio New Zealand, Public Radio, News, Current...  {'li_count': 0}   \n",
       "...                                                  ...              ...   \n",
       "39095                     Airlines, Daily News, ATW Plus  {'li_count': 0}   \n",
       "39090  United Continental Holdings, Boeing, Travel, T...  {'li_count': 0}   \n",
       "39103                                    AWIN_Commercial  {'li_count': 0}   \n",
       "39102                                                     {'li_count': 0}   \n",
       "39101                                                     {'li_count': 0}   \n",
       "\n",
       "                                                    link  max_velocity  \\\n",
       "3      http://www.nzherald.co.nz/business/news/articl...     97.729933   \n",
       "142    http://www.newstalkzb.co.nz/news/national/unit...      0.000000   \n",
       "25     http://www.stuff.co.nz/travel/travel-troubles/...    108.874289   \n",
       "141    https://www.odt.co.nz/news/national/unruly-pas...      0.000000   \n",
       "64     http://www.radionz.co.nz/news/national/321644/...      0.649721   \n",
       "...                                                  ...           ...   \n",
       "39095  https://atwonline.com/airlines/united-takes-no...      6.398294   \n",
       "39090  https://www.bizjournals.com/chicago/news/2019/...      0.653534   \n",
       "39103  https://aviationweek.com/awincommercial/united...      0.000000   \n",
       "39102  https://www.freerepublic.com/focus/f-chat/3745...      0.000000   \n",
       "39101  https://www.globaltravelerusa.com/united-airli...      0.000000   \n",
       "\n",
       "               pi_data   publication_timestamp  sentiment  \\\n",
       "3      {'pi_count': 0} 2017-01-02 02:30:44.514          0   \n",
       "142    {'pi_count': 0} 2017-01-02 04:19:53.397          0   \n",
       "25     {'pi_count': 0} 2017-01-02 05:07:09.438          0   \n",
       "141    {'pi_count': 0} 2017-01-02 06:42:16.000          0   \n",
       "64     {'pi_count': 0} 2017-01-02 07:50:45.250         -1   \n",
       "...                ...                     ...        ...   \n",
       "39095  {'pi_count': 0} 2019-04-30 17:55:27.350          0   \n",
       "39090  {'pi_count': 0} 2019-04-30 19:05:05.791          0   \n",
       "39103  {'pi_count': 0} 2019-04-30 20:00:51.056         -1   \n",
       "39102  {'pi_count': 0} 2019-04-30 22:26:06.036          0   \n",
       "39101  {'pi_count': 0} 2019-04-30 23:06:18.403          0   \n",
       "\n",
       "                                                  source  \\\n",
       "3      {'publisher': 'nzherald.co.nz', 'link': 'http:...   \n",
       "142    {'publisher': 'newstalkzb.co.nz', 'link': 'htt...   \n",
       "25     {'publisher': 'stuff.co.nz', 'link': 'http://s...   \n",
       "141    {'publisher': 'odt.co.nz', 'link': 'https://od...   \n",
       "64     {'publisher': 'radionz.co.nz', 'link': 'http:/...   \n",
       "...                                                  ...   \n",
       "39095  {'publisher': 'atwonline.com', 'link': 'https:...   \n",
       "39090  {'publisher': 'bizjournals.com', 'link': 'http...   \n",
       "39103  {'publisher': 'aviationweek.com', 'link': 'htt...   \n",
       "39102  {'publisher': 'freerepublic.com', 'link': 'htt...   \n",
       "39101  {'publisher': 'globaltravelerusa.com', 'link':...   \n",
       "\n",
       "                                                  topics  velocity  tw_count  \\\n",
       "3      [{'id': 2, 'name': 'News'}, {'id': 28, 'name':...  0.115518     100.0   \n",
       "142                          [{'id': 2, 'name': 'News'}]  0.000000       0.0   \n",
       "25     [{'id': 2, 'name': 'News'}, {'id': 443, 'name'... -4.779850      40.0   \n",
       "141                          [{'id': 2, 'name': 'News'}]  0.000000       0.0   \n",
       "64     [{'id': 2, 'name': 'News'}, {'id': 668, 'name'...  0.492976       0.0   \n",
       "...                                                  ...       ...       ...   \n",
       "39095  [{'id': 464, 'name': 'Transport'}, {'id': 459,...  0.025000       0.0   \n",
       "39090  [{'id': 28, 'name': 'Business'}, {'id': 443, '...  0.075000       3.0   \n",
       "39103  [{'id': 464, 'name': 'Transport'}, {'id': 459,...  0.000000       0.0   \n",
       "39102  [{'id': 19, 'name': 'Opinion'}, {'id': 24, 'na...  0.000000       0.0   \n",
       "39101  [{'id': 443, 'name': 'Travel'}, {'id': 12, 'na...  0.000000       0.0   \n",
       "\n",
       "       tw_overperforming  \n",
       "3                    NaN  \n",
       "142                  NaN  \n",
       "25                   NaN  \n",
       "141                  NaN  \n",
       "64                   NaN  \n",
       "...                  ...  \n",
       "39095                NaN  \n",
       "39090                NaN  \n",
       "39103                NaN  \n",
       "39102                NaN  \n",
       "39101                NaN  \n",
       "\n",
       "[39109 rows x 20 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "article_tw = pd.concat([data.drop(columns=['tw_data']),data['tw_data'].apply(pd.Series)],axis=1)\n",
    "article_tw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>article_id</th>\n",
       "      <th>authors</th>\n",
       "      <th>contents</th>\n",
       "      <th>excerpt</th>\n",
       "      <th>fb_data</th>\n",
       "      <th>has_video</th>\n",
       "      <th>headline</th>\n",
       "      <th>image_link</th>\n",
       "      <th>keywords</th>\n",
       "      <th>link</th>\n",
       "      <th>max_velocity</th>\n",
       "      <th>pi_data</th>\n",
       "      <th>publication_timestamp</th>\n",
       "      <th>sentiment</th>\n",
       "      <th>source</th>\n",
       "      <th>topics</th>\n",
       "      <th>tw_data</th>\n",
       "      <th>velocity</th>\n",
       "      <th>li_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>76509020-d093-11e6-8d6f-9f4c7b0f94de</td>\n",
       "      <td>[Corazon Miller]</td>\n",
       "      <td>United Airlines New Year's Day flight bound f...</td>\n",
       "      <td>A man was arrested after it was claimed he had...</td>\n",
       "      <td>{'total_engagement_count': 1737, 'likes': 0, '...</td>\n",
       "      <td>False</td>\n",
       "      <td>Passenger arrested after United Airlines fligh...</td>\n",
       "      <td>http://media.nzherald.co.nz/webcontent/image/j...</td>\n",
       "      <td>Business, Aviation, Airlines, Australia, Sydne...</td>\n",
       "      <td>http://www.nzherald.co.nz/business/news/articl...</td>\n",
       "      <td>97.729933</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 02:30:44.514</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'nzherald.co.nz', 'link': 'http:...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 28, 'name':...</td>\n",
       "      <td>{'tw_count': 100}</td>\n",
       "      <td>0.115518</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>b5c0ce50-d0a2-11e6-ab89-c11942898bd6</td>\n",
       "      <td>[Newstalk ZB Staff]</td>\n",
       "      <td>United Airlines New Year's Day flight bound f...</td>\n",
       "      <td>A United Airlines New Year's Day flight bound ...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines flight diverted to Auckland af...</td>\n",
       "      <td>http://www.newstalkzb.co.nz/media/16873988/uni...</td>\n",
       "      <td></td>\n",
       "      <td>http://www.newstalkzb.co.nz/news/national/unit...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 04:19:53.397</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'newstalkzb.co.nz', 'link': 'htt...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}]</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>502a51e0-d0a9-11e6-ab89-c11942898bd6</td>\n",
       "      <td>[LEXI NELSON]</td>\n",
       "      <td>United Airlines New Year's Day flight set to t...</td>\n",
       "      <td>Unruly passenger goes on abusive, racist rant,...</td>\n",
       "      <td>{'total_engagement_count': 38, 'likes': 0, 'co...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines New Year's Day flight diverts ...</td>\n",
       "      <td>https://resources.stuff.co.nz/content/dam/imag...</td>\n",
       "      <td>Sport, Entertainment, Business, Motoring, Worl...</td>\n",
       "      <td>http://www.stuff.co.nz/travel/travel-troubles/...</td>\n",
       "      <td>108.874289</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 05:07:09.438</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'stuff.co.nz', 'link': 'http://s...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 443, 'name'...</td>\n",
       "      <td>{'tw_count': 40}</td>\n",
       "      <td>-4.779850</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>61489c80-d0b7-11e6-919c-7789120ed8c4</td>\n",
       "      <td>None</td>\n",
       "      <td>You are not permitted to download, save or ema...</td>\n",
       "      <td>A man was arrested after an alleged \"racist ra...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>Unruly passenger arrested in Auckland</td>\n",
       "      <td>https://www.odt.co.nz/sites/default/files/stor...</td>\n",
       "      <td></td>\n",
       "      <td>https://www.odt.co.nz/news/national/unruly-pas...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 06:42:16.000</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'odt.co.nz', 'link': 'https://od...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}]</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>2ad85e20-d0c0-11e6-ab89-c11942898bd6</td>\n",
       "      <td>None</td>\n",
       "      <td>olice have arrested a badly behaved passenger ...</td>\n",
       "      <td>Police have arrested a badly behaved passenger...</td>\n",
       "      <td>{'total_engagement_count': 3, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>Unruly passenger forces flight to divert</td>\n",
       "      <td>http://www.radionz.co.nz/assets/news_crops/180...</td>\n",
       "      <td>Radio New Zealand, Public Radio, News, Current...</td>\n",
       "      <td>http://www.radionz.co.nz/news/national/321644/...</td>\n",
       "      <td>0.649721</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2017-01-02 07:50:45.250</td>\n",
       "      <td>-1</td>\n",
       "      <td>{'publisher': 'radionz.co.nz', 'link': 'http:/...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 668, 'name'...</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.492976</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39095</th>\n",
       "      <td>22f57960-6b71-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>[Sean Broderick]</td>\n",
       "      <td>Avianca A320 Avianca Holdings Related Media Av...</td>\n",
       "      <td>United Airlines has not leveraged collateral i...</td>\n",
       "      <td>{'total_engagement_count': 12, 'likes': 4, 'co...</td>\n",
       "      <td>False</td>\n",
       "      <td>United takes no enforcement action in Avianca ...</td>\n",
       "      <td>https://atwonline.com/site-files/atwonline.com...</td>\n",
       "      <td>Airlines, Daily News, ATW Plus</td>\n",
       "      <td>https://atwonline.com/airlines/united-takes-no...</td>\n",
       "      <td>6.398294</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2019-04-30 17:55:27.350</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'atwonline.com', 'link': 'https:...</td>\n",
       "      <td>[{'id': 464, 'name': 'Transport'}, {'id': 459,...</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.025000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39090</th>\n",
       "      <td>dd80f8f0-6b7a-11e9-ada4-e30f7b916ef2</td>\n",
       "      <td>[Lewis Lazare]</td>\n",
       "      <td>The new service will mark Chicago-based United...</td>\n",
       "      <td>The new route is the 22nd international destin...</td>\n",
       "      <td>{'total_engagement_count': 82, 'likes': 48, 'c...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines puts tickets to South Africa o...</td>\n",
       "      <td>https://media.bizj.us/view/img/10999111/united...</td>\n",
       "      <td>United Continental Holdings, Boeing, Travel, T...</td>\n",
       "      <td>https://www.bizjournals.com/chicago/news/2019/...</td>\n",
       "      <td>0.653534</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2019-04-30 19:05:05.791</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'bizjournals.com', 'link': 'http...</td>\n",
       "      <td>[{'id': 28, 'name': 'Business'}, {'id': 443, '...</td>\n",
       "      <td>{'tw_count': 3}</td>\n",
       "      <td>0.075000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39103</th>\n",
       "      <td>a76fe700-6b82-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>[Sean Broderick]</td>\n",
       "      <td>https://aviationweek Home &gt; AWIN_Commercial &gt; ...</td>\n",
       "      <td>United Airlines has not leveraged collateral i...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Takes No Action In Avianca Shareholder ...</td>\n",
       "      <td>https://aviationweek.com/site-files/aviationwe...</td>\n",
       "      <td>AWIN_Commercial</td>\n",
       "      <td>https://aviationweek.com/awincommercial/united...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2019-04-30 20:00:51.056</td>\n",
       "      <td>-1</td>\n",
       "      <td>{'publisher': 'aviationweek.com', 'link': 'htt...</td>\n",
       "      <td>[{'id': 464, 'name': 'Transport'}, {'id': 459,...</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39102</th>\n",
       "      <td>f1f85140-6b96-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>None</td>\n",
       "      <td>Stearn Hodge says he will never forget the hum...</td>\n",
       "      <td>FULL TITLE: 'I had to crawl': Amputee seeks da...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>'I had to crawl': Amputee seeks damages after ...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.freerepublic.com/focus/f-chat/3745...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2019-04-30 22:26:06.036</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'freerepublic.com', 'link': 'htt...</td>\n",
       "      <td>[{'id': 19, 'name': 'Opinion'}, {'id': 24, 'na...</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39101</th>\n",
       "      <td>8fda4f30-6b9c-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>[Erich Martin]</td>\n",
       "      <td>Last week, United Airlines took the first step...</td>\n",
       "      <td>Last week, United Airlines took the first step...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines Introduces New Fleet Livery | ...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://www.globaltravelerusa.com/united-airli...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>{'pi_count': 0}</td>\n",
       "      <td>2019-04-30 23:06:18.403</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'globaltravelerusa.com', 'link':...</td>\n",
       "      <td>[{'id': 443, 'name': 'Travel'}, {'id': 12, 'na...</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>39109 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 article_id              authors  \\\n",
       "3      76509020-d093-11e6-8d6f-9f4c7b0f94de     [Corazon Miller]   \n",
       "142    b5c0ce50-d0a2-11e6-ab89-c11942898bd6  [Newstalk ZB Staff]   \n",
       "25     502a51e0-d0a9-11e6-ab89-c11942898bd6        [LEXI NELSON]   \n",
       "141    61489c80-d0b7-11e6-919c-7789120ed8c4                 None   \n",
       "64     2ad85e20-d0c0-11e6-ab89-c11942898bd6                 None   \n",
       "...                                     ...                  ...   \n",
       "39095  22f57960-6b71-11e9-9225-3fa9c6a5bcbe     [Sean Broderick]   \n",
       "39090  dd80f8f0-6b7a-11e9-ada4-e30f7b916ef2       [Lewis Lazare]   \n",
       "39103  a76fe700-6b82-11e9-9225-3fa9c6a5bcbe     [Sean Broderick]   \n",
       "39102  f1f85140-6b96-11e9-9225-3fa9c6a5bcbe                 None   \n",
       "39101  8fda4f30-6b9c-11e9-9225-3fa9c6a5bcbe       [Erich Martin]   \n",
       "\n",
       "                                                contents  \\\n",
       "3       United Airlines New Year's Day flight bound f...   \n",
       "142     United Airlines New Year's Day flight bound f...   \n",
       "25     United Airlines New Year's Day flight set to t...   \n",
       "141    You are not permitted to download, save or ema...   \n",
       "64     olice have arrested a badly behaved passenger ...   \n",
       "...                                                  ...   \n",
       "39095  Avianca A320 Avianca Holdings Related Media Av...   \n",
       "39090  The new service will mark Chicago-based United...   \n",
       "39103  https://aviationweek Home > AWIN_Commercial > ...   \n",
       "39102  Stearn Hodge says he will never forget the hum...   \n",
       "39101  Last week, United Airlines took the first step...   \n",
       "\n",
       "                                                 excerpt  \\\n",
       "3      A man was arrested after it was claimed he had...   \n",
       "142    A United Airlines New Year's Day flight bound ...   \n",
       "25     Unruly passenger goes on abusive, racist rant,...   \n",
       "141    A man was arrested after an alleged \"racist ra...   \n",
       "64     Police have arrested a badly behaved passenger...   \n",
       "...                                                  ...   \n",
       "39095  United Airlines has not leveraged collateral i...   \n",
       "39090  The new route is the 22nd international destin...   \n",
       "39103  United Airlines has not leveraged collateral i...   \n",
       "39102  FULL TITLE: 'I had to crawl': Amputee seeks da...   \n",
       "39101  Last week, United Airlines took the first step...   \n",
       "\n",
       "                                                 fb_data  has_video  \\\n",
       "3      {'total_engagement_count': 1737, 'likes': 0, '...      False   \n",
       "142    {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "25     {'total_engagement_count': 38, 'likes': 0, 'co...      False   \n",
       "141    {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "64     {'total_engagement_count': 3, 'likes': 0, 'com...      False   \n",
       "...                                                  ...        ...   \n",
       "39095  {'total_engagement_count': 12, 'likes': 4, 'co...      False   \n",
       "39090  {'total_engagement_count': 82, 'likes': 48, 'c...      False   \n",
       "39103  {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "39102  {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "39101  {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "\n",
       "                                                headline  \\\n",
       "3      Passenger arrested after United Airlines fligh...   \n",
       "142    United Airlines flight diverted to Auckland af...   \n",
       "25     United Airlines New Year's Day flight diverts ...   \n",
       "141                Unruly passenger arrested in Auckland   \n",
       "64              Unruly passenger forces flight to divert   \n",
       "...                                                  ...   \n",
       "39095  United takes no enforcement action in Avianca ...   \n",
       "39090  United Airlines puts tickets to South Africa o...   \n",
       "39103  United Takes No Action In Avianca Shareholder ...   \n",
       "39102  'I had to crawl': Amputee seeks damages after ...   \n",
       "39101  United Airlines Introduces New Fleet Livery | ...   \n",
       "\n",
       "                                              image_link  \\\n",
       "3      http://media.nzherald.co.nz/webcontent/image/j...   \n",
       "142    http://www.newstalkzb.co.nz/media/16873988/uni...   \n",
       "25     https://resources.stuff.co.nz/content/dam/imag...   \n",
       "141    https://www.odt.co.nz/sites/default/files/stor...   \n",
       "64     http://www.radionz.co.nz/assets/news_crops/180...   \n",
       "...                                                  ...   \n",
       "39095  https://atwonline.com/site-files/atwonline.com...   \n",
       "39090  https://media.bizj.us/view/img/10999111/united...   \n",
       "39103  https://aviationweek.com/site-files/aviationwe...   \n",
       "39102                                                      \n",
       "39101                                                      \n",
       "\n",
       "                                                keywords  \\\n",
       "3      Business, Aviation, Airlines, Australia, Sydne...   \n",
       "142                                                        \n",
       "25     Sport, Entertainment, Business, Motoring, Worl...   \n",
       "141                                                        \n",
       "64     Radio New Zealand, Public Radio, News, Current...   \n",
       "...                                                  ...   \n",
       "39095                     Airlines, Daily News, ATW Plus   \n",
       "39090  United Continental Holdings, Boeing, Travel, T...   \n",
       "39103                                    AWIN_Commercial   \n",
       "39102                                                      \n",
       "39101                                                      \n",
       "\n",
       "                                                    link  max_velocity  \\\n",
       "3      http://www.nzherald.co.nz/business/news/articl...     97.729933   \n",
       "142    http://www.newstalkzb.co.nz/news/national/unit...      0.000000   \n",
       "25     http://www.stuff.co.nz/travel/travel-troubles/...    108.874289   \n",
       "141    https://www.odt.co.nz/news/national/unruly-pas...      0.000000   \n",
       "64     http://www.radionz.co.nz/news/national/321644/...      0.649721   \n",
       "...                                                  ...           ...   \n",
       "39095  https://atwonline.com/airlines/united-takes-no...      6.398294   \n",
       "39090  https://www.bizjournals.com/chicago/news/2019/...      0.653534   \n",
       "39103  https://aviationweek.com/awincommercial/united...      0.000000   \n",
       "39102  https://www.freerepublic.com/focus/f-chat/3745...      0.000000   \n",
       "39101  https://www.globaltravelerusa.com/united-airli...      0.000000   \n",
       "\n",
       "               pi_data   publication_timestamp  sentiment  \\\n",
       "3      {'pi_count': 0} 2017-01-02 02:30:44.514          0   \n",
       "142    {'pi_count': 0} 2017-01-02 04:19:53.397          0   \n",
       "25     {'pi_count': 0} 2017-01-02 05:07:09.438          0   \n",
       "141    {'pi_count': 0} 2017-01-02 06:42:16.000          0   \n",
       "64     {'pi_count': 0} 2017-01-02 07:50:45.250         -1   \n",
       "...                ...                     ...        ...   \n",
       "39095  {'pi_count': 0} 2019-04-30 17:55:27.350          0   \n",
       "39090  {'pi_count': 0} 2019-04-30 19:05:05.791          0   \n",
       "39103  {'pi_count': 0} 2019-04-30 20:00:51.056         -1   \n",
       "39102  {'pi_count': 0} 2019-04-30 22:26:06.036          0   \n",
       "39101  {'pi_count': 0} 2019-04-30 23:06:18.403          0   \n",
       "\n",
       "                                                  source  \\\n",
       "3      {'publisher': 'nzherald.co.nz', 'link': 'http:...   \n",
       "142    {'publisher': 'newstalkzb.co.nz', 'link': 'htt...   \n",
       "25     {'publisher': 'stuff.co.nz', 'link': 'http://s...   \n",
       "141    {'publisher': 'odt.co.nz', 'link': 'https://od...   \n",
       "64     {'publisher': 'radionz.co.nz', 'link': 'http:/...   \n",
       "...                                                  ...   \n",
       "39095  {'publisher': 'atwonline.com', 'link': 'https:...   \n",
       "39090  {'publisher': 'bizjournals.com', 'link': 'http...   \n",
       "39103  {'publisher': 'aviationweek.com', 'link': 'htt...   \n",
       "39102  {'publisher': 'freerepublic.com', 'link': 'htt...   \n",
       "39101  {'publisher': 'globaltravelerusa.com', 'link':...   \n",
       "\n",
       "                                                  topics            tw_data  \\\n",
       "3      [{'id': 2, 'name': 'News'}, {'id': 28, 'name':...  {'tw_count': 100}   \n",
       "142                          [{'id': 2, 'name': 'News'}]    {'tw_count': 0}   \n",
       "25     [{'id': 2, 'name': 'News'}, {'id': 443, 'name'...   {'tw_count': 40}   \n",
       "141                          [{'id': 2, 'name': 'News'}]    {'tw_count': 0}   \n",
       "64     [{'id': 2, 'name': 'News'}, {'id': 668, 'name'...    {'tw_count': 0}   \n",
       "...                                                  ...                ...   \n",
       "39095  [{'id': 464, 'name': 'Transport'}, {'id': 459,...    {'tw_count': 0}   \n",
       "39090  [{'id': 28, 'name': 'Business'}, {'id': 443, '...    {'tw_count': 3}   \n",
       "39103  [{'id': 464, 'name': 'Transport'}, {'id': 459,...    {'tw_count': 0}   \n",
       "39102  [{'id': 19, 'name': 'Opinion'}, {'id': 24, 'na...    {'tw_count': 0}   \n",
       "39101  [{'id': 443, 'name': 'Travel'}, {'id': 12, 'na...    {'tw_count': 0}   \n",
       "\n",
       "       velocity  li_count  \n",
       "3      0.115518         6  \n",
       "142    0.000000         0  \n",
       "25    -4.779850         0  \n",
       "141    0.000000         0  \n",
       "64     0.492976         0  \n",
       "...         ...       ...  \n",
       "39095  0.025000         0  \n",
       "39090  0.075000         0  \n",
       "39103  0.000000         0  \n",
       "39102  0.000000         0  \n",
       "39101  0.000000         0  \n",
       "\n",
       "[39109 rows x 19 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "article_li = pd.concat([data.drop(columns=['li_data']),data['li_data'].apply(pd.Series)],axis=1)\n",
    "article_li"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>article_id</th>\n",
       "      <th>authors</th>\n",
       "      <th>contents</th>\n",
       "      <th>excerpt</th>\n",
       "      <th>fb_data</th>\n",
       "      <th>has_video</th>\n",
       "      <th>headline</th>\n",
       "      <th>image_link</th>\n",
       "      <th>keywords</th>\n",
       "      <th>li_data</th>\n",
       "      <th>link</th>\n",
       "      <th>max_velocity</th>\n",
       "      <th>publication_timestamp</th>\n",
       "      <th>sentiment</th>\n",
       "      <th>source</th>\n",
       "      <th>topics</th>\n",
       "      <th>tw_data</th>\n",
       "      <th>velocity</th>\n",
       "      <th>pi_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>76509020-d093-11e6-8d6f-9f4c7b0f94de</td>\n",
       "      <td>[Corazon Miller]</td>\n",
       "      <td>United Airlines New Year's Day flight bound f...</td>\n",
       "      <td>A man was arrested after it was claimed he had...</td>\n",
       "      <td>{'total_engagement_count': 1737, 'likes': 0, '...</td>\n",
       "      <td>False</td>\n",
       "      <td>Passenger arrested after United Airlines fligh...</td>\n",
       "      <td>http://media.nzherald.co.nz/webcontent/image/j...</td>\n",
       "      <td>Business, Aviation, Airlines, Australia, Sydne...</td>\n",
       "      <td>{'li_count': 6}</td>\n",
       "      <td>http://www.nzherald.co.nz/business/news/articl...</td>\n",
       "      <td>97.729933</td>\n",
       "      <td>2017-01-02 02:30:44.514</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'nzherald.co.nz', 'link': 'http:...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 28, 'name':...</td>\n",
       "      <td>{'tw_count': 100}</td>\n",
       "      <td>0.115518</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>b5c0ce50-d0a2-11e6-ab89-c11942898bd6</td>\n",
       "      <td>[Newstalk ZB Staff]</td>\n",
       "      <td>United Airlines New Year's Day flight bound f...</td>\n",
       "      <td>A United Airlines New Year's Day flight bound ...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines flight diverted to Auckland af...</td>\n",
       "      <td>http://www.newstalkzb.co.nz/media/16873988/uni...</td>\n",
       "      <td></td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>http://www.newstalkzb.co.nz/news/national/unit...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2017-01-02 04:19:53.397</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'newstalkzb.co.nz', 'link': 'htt...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}]</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>502a51e0-d0a9-11e6-ab89-c11942898bd6</td>\n",
       "      <td>[LEXI NELSON]</td>\n",
       "      <td>United Airlines New Year's Day flight set to t...</td>\n",
       "      <td>Unruly passenger goes on abusive, racist rant,...</td>\n",
       "      <td>{'total_engagement_count': 38, 'likes': 0, 'co...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines New Year's Day flight diverts ...</td>\n",
       "      <td>https://resources.stuff.co.nz/content/dam/imag...</td>\n",
       "      <td>Sport, Entertainment, Business, Motoring, Worl...</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>http://www.stuff.co.nz/travel/travel-troubles/...</td>\n",
       "      <td>108.874289</td>\n",
       "      <td>2017-01-02 05:07:09.438</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'stuff.co.nz', 'link': 'http://s...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 443, 'name'...</td>\n",
       "      <td>{'tw_count': 40}</td>\n",
       "      <td>-4.779850</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>61489c80-d0b7-11e6-919c-7789120ed8c4</td>\n",
       "      <td>None</td>\n",
       "      <td>You are not permitted to download, save or ema...</td>\n",
       "      <td>A man was arrested after an alleged \"racist ra...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>Unruly passenger arrested in Auckland</td>\n",
       "      <td>https://www.odt.co.nz/sites/default/files/stor...</td>\n",
       "      <td></td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://www.odt.co.nz/news/national/unruly-pas...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2017-01-02 06:42:16.000</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'odt.co.nz', 'link': 'https://od...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}]</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>2ad85e20-d0c0-11e6-ab89-c11942898bd6</td>\n",
       "      <td>None</td>\n",
       "      <td>olice have arrested a badly behaved passenger ...</td>\n",
       "      <td>Police have arrested a badly behaved passenger...</td>\n",
       "      <td>{'total_engagement_count': 3, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>Unruly passenger forces flight to divert</td>\n",
       "      <td>http://www.radionz.co.nz/assets/news_crops/180...</td>\n",
       "      <td>Radio New Zealand, Public Radio, News, Current...</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>http://www.radionz.co.nz/news/national/321644/...</td>\n",
       "      <td>0.649721</td>\n",
       "      <td>2017-01-02 07:50:45.250</td>\n",
       "      <td>-1</td>\n",
       "      <td>{'publisher': 'radionz.co.nz', 'link': 'http:/...</td>\n",
       "      <td>[{'id': 2, 'name': 'News'}, {'id': 668, 'name'...</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.492976</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39095</th>\n",
       "      <td>22f57960-6b71-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>[Sean Broderick]</td>\n",
       "      <td>Avianca A320 Avianca Holdings Related Media Av...</td>\n",
       "      <td>United Airlines has not leveraged collateral i...</td>\n",
       "      <td>{'total_engagement_count': 12, 'likes': 4, 'co...</td>\n",
       "      <td>False</td>\n",
       "      <td>United takes no enforcement action in Avianca ...</td>\n",
       "      <td>https://atwonline.com/site-files/atwonline.com...</td>\n",
       "      <td>Airlines, Daily News, ATW Plus</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://atwonline.com/airlines/united-takes-no...</td>\n",
       "      <td>6.398294</td>\n",
       "      <td>2019-04-30 17:55:27.350</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'atwonline.com', 'link': 'https:...</td>\n",
       "      <td>[{'id': 464, 'name': 'Transport'}, {'id': 459,...</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.025000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39090</th>\n",
       "      <td>dd80f8f0-6b7a-11e9-ada4-e30f7b916ef2</td>\n",
       "      <td>[Lewis Lazare]</td>\n",
       "      <td>The new service will mark Chicago-based United...</td>\n",
       "      <td>The new route is the 22nd international destin...</td>\n",
       "      <td>{'total_engagement_count': 82, 'likes': 48, 'c...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines puts tickets to South Africa o...</td>\n",
       "      <td>https://media.bizj.us/view/img/10999111/united...</td>\n",
       "      <td>United Continental Holdings, Boeing, Travel, T...</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://www.bizjournals.com/chicago/news/2019/...</td>\n",
       "      <td>0.653534</td>\n",
       "      <td>2019-04-30 19:05:05.791</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'bizjournals.com', 'link': 'http...</td>\n",
       "      <td>[{'id': 28, 'name': 'Business'}, {'id': 443, '...</td>\n",
       "      <td>{'tw_count': 3}</td>\n",
       "      <td>0.075000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39103</th>\n",
       "      <td>a76fe700-6b82-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>[Sean Broderick]</td>\n",
       "      <td>https://aviationweek Home &gt; AWIN_Commercial &gt; ...</td>\n",
       "      <td>United Airlines has not leveraged collateral i...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Takes No Action In Avianca Shareholder ...</td>\n",
       "      <td>https://aviationweek.com/site-files/aviationwe...</td>\n",
       "      <td>AWIN_Commercial</td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://aviationweek.com/awincommercial/united...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2019-04-30 20:00:51.056</td>\n",
       "      <td>-1</td>\n",
       "      <td>{'publisher': 'aviationweek.com', 'link': 'htt...</td>\n",
       "      <td>[{'id': 464, 'name': 'Transport'}, {'id': 459,...</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39102</th>\n",
       "      <td>f1f85140-6b96-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>None</td>\n",
       "      <td>Stearn Hodge says he will never forget the hum...</td>\n",
       "      <td>FULL TITLE: 'I had to crawl': Amputee seeks da...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>'I had to crawl': Amputee seeks damages after ...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://www.freerepublic.com/focus/f-chat/3745...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2019-04-30 22:26:06.036</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'freerepublic.com', 'link': 'htt...</td>\n",
       "      <td>[{'id': 19, 'name': 'Opinion'}, {'id': 24, 'na...</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39101</th>\n",
       "      <td>8fda4f30-6b9c-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>[Erich Martin]</td>\n",
       "      <td>Last week, United Airlines took the first step...</td>\n",
       "      <td>Last week, United Airlines took the first step...</td>\n",
       "      <td>{'total_engagement_count': 0, 'likes': 0, 'com...</td>\n",
       "      <td>False</td>\n",
       "      <td>United Airlines Introduces New Fleet Livery | ...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>{'li_count': 0}</td>\n",
       "      <td>https://www.globaltravelerusa.com/united-airli...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2019-04-30 23:06:18.403</td>\n",
       "      <td>0</td>\n",
       "      <td>{'publisher': 'globaltravelerusa.com', 'link':...</td>\n",
       "      <td>[{'id': 443, 'name': 'Travel'}, {'id': 12, 'na...</td>\n",
       "      <td>{'tw_count': 0}</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>39109 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 article_id              authors  \\\n",
       "3      76509020-d093-11e6-8d6f-9f4c7b0f94de     [Corazon Miller]   \n",
       "142    b5c0ce50-d0a2-11e6-ab89-c11942898bd6  [Newstalk ZB Staff]   \n",
       "25     502a51e0-d0a9-11e6-ab89-c11942898bd6        [LEXI NELSON]   \n",
       "141    61489c80-d0b7-11e6-919c-7789120ed8c4                 None   \n",
       "64     2ad85e20-d0c0-11e6-ab89-c11942898bd6                 None   \n",
       "...                                     ...                  ...   \n",
       "39095  22f57960-6b71-11e9-9225-3fa9c6a5bcbe     [Sean Broderick]   \n",
       "39090  dd80f8f0-6b7a-11e9-ada4-e30f7b916ef2       [Lewis Lazare]   \n",
       "39103  a76fe700-6b82-11e9-9225-3fa9c6a5bcbe     [Sean Broderick]   \n",
       "39102  f1f85140-6b96-11e9-9225-3fa9c6a5bcbe                 None   \n",
       "39101  8fda4f30-6b9c-11e9-9225-3fa9c6a5bcbe       [Erich Martin]   \n",
       "\n",
       "                                                contents  \\\n",
       "3       United Airlines New Year's Day flight bound f...   \n",
       "142     United Airlines New Year's Day flight bound f...   \n",
       "25     United Airlines New Year's Day flight set to t...   \n",
       "141    You are not permitted to download, save or ema...   \n",
       "64     olice have arrested a badly behaved passenger ...   \n",
       "...                                                  ...   \n",
       "39095  Avianca A320 Avianca Holdings Related Media Av...   \n",
       "39090  The new service will mark Chicago-based United...   \n",
       "39103  https://aviationweek Home > AWIN_Commercial > ...   \n",
       "39102  Stearn Hodge says he will never forget the hum...   \n",
       "39101  Last week, United Airlines took the first step...   \n",
       "\n",
       "                                                 excerpt  \\\n",
       "3      A man was arrested after it was claimed he had...   \n",
       "142    A United Airlines New Year's Day flight bound ...   \n",
       "25     Unruly passenger goes on abusive, racist rant,...   \n",
       "141    A man was arrested after an alleged \"racist ra...   \n",
       "64     Police have arrested a badly behaved passenger...   \n",
       "...                                                  ...   \n",
       "39095  United Airlines has not leveraged collateral i...   \n",
       "39090  The new route is the 22nd international destin...   \n",
       "39103  United Airlines has not leveraged collateral i...   \n",
       "39102  FULL TITLE: 'I had to crawl': Amputee seeks da...   \n",
       "39101  Last week, United Airlines took the first step...   \n",
       "\n",
       "                                                 fb_data  has_video  \\\n",
       "3      {'total_engagement_count': 1737, 'likes': 0, '...      False   \n",
       "142    {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "25     {'total_engagement_count': 38, 'likes': 0, 'co...      False   \n",
       "141    {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "64     {'total_engagement_count': 3, 'likes': 0, 'com...      False   \n",
       "...                                                  ...        ...   \n",
       "39095  {'total_engagement_count': 12, 'likes': 4, 'co...      False   \n",
       "39090  {'total_engagement_count': 82, 'likes': 48, 'c...      False   \n",
       "39103  {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "39102  {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "39101  {'total_engagement_count': 0, 'likes': 0, 'com...      False   \n",
       "\n",
       "                                                headline  \\\n",
       "3      Passenger arrested after United Airlines fligh...   \n",
       "142    United Airlines flight diverted to Auckland af...   \n",
       "25     United Airlines New Year's Day flight diverts ...   \n",
       "141                Unruly passenger arrested in Auckland   \n",
       "64              Unruly passenger forces flight to divert   \n",
       "...                                                  ...   \n",
       "39095  United takes no enforcement action in Avianca ...   \n",
       "39090  United Airlines puts tickets to South Africa o...   \n",
       "39103  United Takes No Action In Avianca Shareholder ...   \n",
       "39102  'I had to crawl': Amputee seeks damages after ...   \n",
       "39101  United Airlines Introduces New Fleet Livery | ...   \n",
       "\n",
       "                                              image_link  \\\n",
       "3      http://media.nzherald.co.nz/webcontent/image/j...   \n",
       "142    http://www.newstalkzb.co.nz/media/16873988/uni...   \n",
       "25     https://resources.stuff.co.nz/content/dam/imag...   \n",
       "141    https://www.odt.co.nz/sites/default/files/stor...   \n",
       "64     http://www.radionz.co.nz/assets/news_crops/180...   \n",
       "...                                                  ...   \n",
       "39095  https://atwonline.com/site-files/atwonline.com...   \n",
       "39090  https://media.bizj.us/view/img/10999111/united...   \n",
       "39103  https://aviationweek.com/site-files/aviationwe...   \n",
       "39102                                                      \n",
       "39101                                                      \n",
       "\n",
       "                                                keywords          li_data  \\\n",
       "3      Business, Aviation, Airlines, Australia, Sydne...  {'li_count': 6}   \n",
       "142                                                       {'li_count': 0}   \n",
       "25     Sport, Entertainment, Business, Motoring, Worl...  {'li_count': 0}   \n",
       "141                                                       {'li_count': 0}   \n",
       "64     Radio New Zealand, Public Radio, News, Current...  {'li_count': 0}   \n",
       "...                                                  ...              ...   \n",
       "39095                     Airlines, Daily News, ATW Plus  {'li_count': 0}   \n",
       "39090  United Continental Holdings, Boeing, Travel, T...  {'li_count': 0}   \n",
       "39103                                    AWIN_Commercial  {'li_count': 0}   \n",
       "39102                                                     {'li_count': 0}   \n",
       "39101                                                     {'li_count': 0}   \n",
       "\n",
       "                                                    link  max_velocity  \\\n",
       "3      http://www.nzherald.co.nz/business/news/articl...     97.729933   \n",
       "142    http://www.newstalkzb.co.nz/news/national/unit...      0.000000   \n",
       "25     http://www.stuff.co.nz/travel/travel-troubles/...    108.874289   \n",
       "141    https://www.odt.co.nz/news/national/unruly-pas...      0.000000   \n",
       "64     http://www.radionz.co.nz/news/national/321644/...      0.649721   \n",
       "...                                                  ...           ...   \n",
       "39095  https://atwonline.com/airlines/united-takes-no...      6.398294   \n",
       "39090  https://www.bizjournals.com/chicago/news/2019/...      0.653534   \n",
       "39103  https://aviationweek.com/awincommercial/united...      0.000000   \n",
       "39102  https://www.freerepublic.com/focus/f-chat/3745...      0.000000   \n",
       "39101  https://www.globaltravelerusa.com/united-airli...      0.000000   \n",
       "\n",
       "        publication_timestamp  sentiment  \\\n",
       "3     2017-01-02 02:30:44.514          0   \n",
       "142   2017-01-02 04:19:53.397          0   \n",
       "25    2017-01-02 05:07:09.438          0   \n",
       "141   2017-01-02 06:42:16.000          0   \n",
       "64    2017-01-02 07:50:45.250         -1   \n",
       "...                       ...        ...   \n",
       "39095 2019-04-30 17:55:27.350          0   \n",
       "39090 2019-04-30 19:05:05.791          0   \n",
       "39103 2019-04-30 20:00:51.056         -1   \n",
       "39102 2019-04-30 22:26:06.036          0   \n",
       "39101 2019-04-30 23:06:18.403          0   \n",
       "\n",
       "                                                  source  \\\n",
       "3      {'publisher': 'nzherald.co.nz', 'link': 'http:...   \n",
       "142    {'publisher': 'newstalkzb.co.nz', 'link': 'htt...   \n",
       "25     {'publisher': 'stuff.co.nz', 'link': 'http://s...   \n",
       "141    {'publisher': 'odt.co.nz', 'link': 'https://od...   \n",
       "64     {'publisher': 'radionz.co.nz', 'link': 'http:/...   \n",
       "...                                                  ...   \n",
       "39095  {'publisher': 'atwonline.com', 'link': 'https:...   \n",
       "39090  {'publisher': 'bizjournals.com', 'link': 'http...   \n",
       "39103  {'publisher': 'aviationweek.com', 'link': 'htt...   \n",
       "39102  {'publisher': 'freerepublic.com', 'link': 'htt...   \n",
       "39101  {'publisher': 'globaltravelerusa.com', 'link':...   \n",
       "\n",
       "                                                  topics            tw_data  \\\n",
       "3      [{'id': 2, 'name': 'News'}, {'id': 28, 'name':...  {'tw_count': 100}   \n",
       "142                          [{'id': 2, 'name': 'News'}]    {'tw_count': 0}   \n",
       "25     [{'id': 2, 'name': 'News'}, {'id': 443, 'name'...   {'tw_count': 40}   \n",
       "141                          [{'id': 2, 'name': 'News'}]    {'tw_count': 0}   \n",
       "64     [{'id': 2, 'name': 'News'}, {'id': 668, 'name'...    {'tw_count': 0}   \n",
       "...                                                  ...                ...   \n",
       "39095  [{'id': 464, 'name': 'Transport'}, {'id': 459,...    {'tw_count': 0}   \n",
       "39090  [{'id': 28, 'name': 'Business'}, {'id': 443, '...    {'tw_count': 3}   \n",
       "39103  [{'id': 464, 'name': 'Transport'}, {'id': 459,...    {'tw_count': 0}   \n",
       "39102  [{'id': 19, 'name': 'Opinion'}, {'id': 24, 'na...    {'tw_count': 0}   \n",
       "39101  [{'id': 443, 'name': 'Travel'}, {'id': 12, 'na...    {'tw_count': 0}   \n",
       "\n",
       "       velocity  pi_count  \n",
       "3      0.115518         0  \n",
       "142    0.000000         0  \n",
       "25    -4.779850         0  \n",
       "141    0.000000         0  \n",
       "64     0.492976         0  \n",
       "...         ...       ...  \n",
       "39095  0.025000         0  \n",
       "39090  0.075000         0  \n",
       "39103  0.000000         0  \n",
       "39102  0.000000         0  \n",
       "39101  0.000000         0  \n",
       "\n",
       "[39109 rows x 19 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "article_pi = pd.concat([data.drop(columns=['pi_data']),data['pi_data'].apply(pd.Series)],axis=1)\n",
    "article_pi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "c1=data[['article_id','publication_timestamp','sentiment']]\n",
    "c2=tweet_influencer[['article_id','rt_count','favourite_count']]\n",
    "c3=article_fb[['article_id','total_engagement_count', 'likes', 'shares', 'comments']]\n",
    "c4=article_tw[['article_id','tw_count']]\n",
    "c5=article_li[['article_id','li_count']]\n",
    "c6=article_pi[['article_id','pi_count']]\n",
    "c1.set_index('article_id', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "c2.set_index('article_id', inplace=True)\n",
    "c3.set_index('article_id', inplace=True)\n",
    "c4.set_index('article_id', inplace=True)\n",
    "c5.set_index('article_id', inplace=True)\n",
    "c6.set_index('article_id', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "c7 = c1.join([c2,c3,c4,c5,c6])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>publication_timestamp</th>\n",
       "      <th>sentiment</th>\n",
       "      <th>rt_count</th>\n",
       "      <th>favourite_count</th>\n",
       "      <th>total_engagement_count</th>\n",
       "      <th>likes</th>\n",
       "      <th>shares</th>\n",
       "      <th>comments</th>\n",
       "      <th>tw_count</th>\n",
       "      <th>li_count</th>\n",
       "      <th>pi_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>article_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>00028910-574b-11e8-8772-ef989ddb8d11</th>\n",
       "      <td>2018-05-14 07:47:06.913</td>\n",
       "      <td>-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0002a7e0-a662-11e8-8bfc-ef989ddb8d11</th>\n",
       "      <td>2018-08-22 23:19:58.000</td>\n",
       "      <td>0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0002a7e0-a662-11e8-8bfc-ef989ddb8d11</th>\n",
       "      <td>2018-08-22 23:19:58.000</td>\n",
       "      <td>0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00047e30-3fe3-11e7-a6c9-ebb7ea9adb61</th>\n",
       "      <td>2017-05-23 18:04:16.000</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>290.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00047e30-3fe3-11e7-a6c9-ebb7ea9adb61</th>\n",
       "      <td>2017-05-23 18:04:16.000</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>290.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       publication_timestamp  sentiment  \\\n",
       "article_id                                                                \n",
       "00028910-574b-11e8-8772-ef989ddb8d11 2018-05-14 07:47:06.913         -1   \n",
       "0002a7e0-a662-11e8-8bfc-ef989ddb8d11 2018-08-22 23:19:58.000          0   \n",
       "0002a7e0-a662-11e8-8bfc-ef989ddb8d11 2018-08-22 23:19:58.000          0   \n",
       "00047e30-3fe3-11e7-a6c9-ebb7ea9adb61 2017-05-23 18:04:16.000          0   \n",
       "00047e30-3fe3-11e7-a6c9-ebb7ea9adb61 2017-05-23 18:04:16.000          0   \n",
       "\n",
       "                                      rt_count  favourite_count  \\\n",
       "article_id                                                        \n",
       "00028910-574b-11e8-8772-ef989ddb8d11       NaN              NaN   \n",
       "0002a7e0-a662-11e8-8bfc-ef989ddb8d11      14.0             11.0   \n",
       "0002a7e0-a662-11e8-8bfc-ef989ddb8d11       5.0              4.0   \n",
       "00047e30-3fe3-11e7-a6c9-ebb7ea9adb61       4.0              3.0   \n",
       "00047e30-3fe3-11e7-a6c9-ebb7ea9adb61       1.0              0.0   \n",
       "\n",
       "                                      total_engagement_count  likes  shares  \\\n",
       "article_id                                                                    \n",
       "00028910-574b-11e8-8772-ef989ddb8d11                     6.0    3.0     3.0   \n",
       "0002a7e0-a662-11e8-8bfc-ef989ddb8d11                    56.0   24.0     8.0   \n",
       "0002a7e0-a662-11e8-8bfc-ef989ddb8d11                    56.0   24.0     8.0   \n",
       "00047e30-3fe3-11e7-a6c9-ebb7ea9adb61                    46.0    0.0     0.0   \n",
       "00047e30-3fe3-11e7-a6c9-ebb7ea9adb61                    46.0    0.0     0.0   \n",
       "\n",
       "                                      comments  tw_count  li_count  pi_count  \n",
       "article_id                                                                    \n",
       "00028910-574b-11e8-8772-ef989ddb8d11       0.0       0.0         0         0  \n",
       "0002a7e0-a662-11e8-8bfc-ef989ddb8d11      24.0      21.0         0         0  \n",
       "0002a7e0-a662-11e8-8bfc-ef989ddb8d11      24.0      21.0         0         0  \n",
       "00047e30-3fe3-11e7-a6c9-ebb7ea9adb61       0.0     290.0         2         2  \n",
       "00047e30-3fe3-11e7-a6c9-ebb7ea9adb61       0.0     290.0         2         2  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c7.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "c8=c7.sort_values(by=['publication_timestamp'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>publication_timestamp</th>\n",
       "      <th>sentiment</th>\n",
       "      <th>rt_count</th>\n",
       "      <th>favourite_count</th>\n",
       "      <th>total_engagement_count</th>\n",
       "      <th>likes</th>\n",
       "      <th>shares</th>\n",
       "      <th>comments</th>\n",
       "      <th>tw_count</th>\n",
       "      <th>li_count</th>\n",
       "      <th>pi_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>article_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>76509020-d093-11e6-8d6f-9f4c7b0f94de</th>\n",
       "      <td>2017-01-02 02:30:44.514</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1737.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b5c0ce50-d0a2-11e6-ab89-c11942898bd6</th>\n",
       "      <td>2017-01-02 04:19:53.397</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502a51e0-d0a9-11e6-ab89-c11942898bd6</th>\n",
       "      <td>2017-01-02 05:07:09.438</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61489c80-d0b7-11e6-919c-7789120ed8c4</th>\n",
       "      <td>2017-01-02 06:42:16.000</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2ad85e20-d0c0-11e6-ab89-c11942898bd6</th>\n",
       "      <td>2017-01-02 07:50:45.250</td>\n",
       "      <td>-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       publication_timestamp  sentiment  \\\n",
       "article_id                                                                \n",
       "76509020-d093-11e6-8d6f-9f4c7b0f94de 2017-01-02 02:30:44.514          0   \n",
       "b5c0ce50-d0a2-11e6-ab89-c11942898bd6 2017-01-02 04:19:53.397          0   \n",
       "502a51e0-d0a9-11e6-ab89-c11942898bd6 2017-01-02 05:07:09.438          0   \n",
       "61489c80-d0b7-11e6-919c-7789120ed8c4 2017-01-02 06:42:16.000          0   \n",
       "2ad85e20-d0c0-11e6-ab89-c11942898bd6 2017-01-02 07:50:45.250         -1   \n",
       "\n",
       "                                      rt_count  favourite_count  \\\n",
       "article_id                                                        \n",
       "76509020-d093-11e6-8d6f-9f4c7b0f94de       1.0              0.0   \n",
       "b5c0ce50-d0a2-11e6-ab89-c11942898bd6       NaN              NaN   \n",
       "502a51e0-d0a9-11e6-ab89-c11942898bd6       3.0              1.0   \n",
       "61489c80-d0b7-11e6-919c-7789120ed8c4       NaN              NaN   \n",
       "2ad85e20-d0c0-11e6-ab89-c11942898bd6       NaN              NaN   \n",
       "\n",
       "                                      total_engagement_count  likes  shares  \\\n",
       "article_id                                                                    \n",
       "76509020-d093-11e6-8d6f-9f4c7b0f94de                  1737.0    0.0     0.0   \n",
       "b5c0ce50-d0a2-11e6-ab89-c11942898bd6                     0.0    0.0     0.0   \n",
       "502a51e0-d0a9-11e6-ab89-c11942898bd6                    38.0    0.0     0.0   \n",
       "61489c80-d0b7-11e6-919c-7789120ed8c4                     0.0    0.0     0.0   \n",
       "2ad85e20-d0c0-11e6-ab89-c11942898bd6                     3.0    0.0     0.0   \n",
       "\n",
       "                                      comments  tw_count  li_count  pi_count  \n",
       "article_id                                                                    \n",
       "76509020-d093-11e6-8d6f-9f4c7b0f94de       0.0     100.0         6         0  \n",
       "b5c0ce50-d0a2-11e6-ab89-c11942898bd6       0.0       0.0         0         0  \n",
       "502a51e0-d0a9-11e6-ab89-c11942898bd6       0.0      40.0         0         0  \n",
       "61489c80-d0b7-11e6-919c-7789120ed8c4       0.0       0.0         0         0  \n",
       "2ad85e20-d0c0-11e6-ab89-c11942898bd6       0.0       0.0         0         0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c8.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>article_id</th>\n",
       "      <th>twitter_url</th>\n",
       "      <th>favourite_count</th>\n",
       "      <th>rt_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>description</th>\n",
       "      <th>article_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>336914f0-d1d6-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816358480277815296</td>\n",
       "      <td>110</td>\n",
       "      <td>97</td>\n",
       "      <td>1483470003000</td>\n",
       "      <td>United Airlines is investigating how a baggage...</td>\n",
       "      <td>http://www.cnn.com/2017/01/03/aviation/trapped...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0648ed40-d16a-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816301537752387584</td>\n",
       "      <td>63</td>\n",
       "      <td>68</td>\n",
       "      <td>1483456427000</td>\n",
       "      <td>A MAN has been arrested after he allegedly lau...</td>\n",
       "      <td>http://www.news.com.au/travel/travel-updates/i...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>97c8d860-d1ca-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816309417356460032</td>\n",
       "      <td>78</td>\n",
       "      <td>58</td>\n",
       "      <td>1483458306000</td>\n",
       "      <td>The flight reached an altitude of 27,000 feet.</td>\n",
       "      <td>http://fortune.com/2017/01/03/united-airlines-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>97c8d860-d1ca-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816480537397301252</td>\n",
       "      <td>67</td>\n",
       "      <td>48</td>\n",
       "      <td>1483499104000</td>\n",
       "      <td>The flight reached an altitude of 27,000 feet.</td>\n",
       "      <td>http://fortune.com/2017/01/03/united-airlines-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>efc970d0-d1b9-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816309410117091329</td>\n",
       "      <td>9</td>\n",
       "      <td>31</td>\n",
       "      <td>1483458304000</td>\n",
       "      <td>United Airlines says it is investigating after...</td>\n",
       "      <td>http://globalnews.ca/news/3157095/united-airli...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             article_id  \\\n",
       "0  336914f0-d1d6-11e6-ab89-c11942898bd6   \n",
       "1  0648ed40-d16a-11e6-ab89-c11942898bd6   \n",
       "2  97c8d860-d1ca-11e6-ab89-c11942898bd6   \n",
       "2  97c8d860-d1ca-11e6-ab89-c11942898bd6   \n",
       "3  efc970d0-d1b9-11e6-ab89-c11942898bd6   \n",
       "\n",
       "                                         twitter_url  favourite_count  \\\n",
       "0  https://twitter.com/any/status/816358480277815296              110   \n",
       "1  https://twitter.com/any/status/816301537752387584               63   \n",
       "2  https://twitter.com/any/status/816309417356460032               78   \n",
       "2  https://twitter.com/any/status/816480537397301252               67   \n",
       "3  https://twitter.com/any/status/816309410117091329                9   \n",
       "\n",
       "   rt_count     created_at                                        description  \\\n",
       "0        97  1483470003000  United Airlines is investigating how a baggage...   \n",
       "1        68  1483456427000  A MAN has been arrested after he allegedly lau...   \n",
       "2        58  1483458306000     The flight reached an altitude of 27,000 feet.   \n",
       "2        48  1483499104000     The flight reached an altitude of 27,000 feet.   \n",
       "3        31  1483458304000  United Airlines says it is investigating after...   \n",
       "\n",
       "                                         article_url  \n",
       "0  http://www.cnn.com/2017/01/03/aviation/trapped...  \n",
       "1  http://www.news.com.au/travel/travel-updates/i...  \n",
       "2  http://fortune.com/2017/01/03/united-airlines-...  \n",
       "2  http://fortune.com/2017/01/03/united-airlines-...  \n",
       "3  http://globalnews.ca/news/3157095/united-airli...  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['publication_timestamp', 'sentiment', 'rt_count', 'favourite_count',\n",
       "       'total_engagement_count', 'likes', 'shares', 'comments', 'tw_count',\n",
       "       'li_count', 'pi_count'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c8.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "groupby_time = c8.groupby(pd.Grouper(key='publication_timestamp', freq='1M'))[['publication_timestamp', 'rt_count', 'favourite_count',\n",
    "       'total_engagement_count', 'likes', 'shares', 'comments', 'tw_count',\n",
    "       'li_count', 'pi_count','sentiment']].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "groupby_time['grp'] = pd.cut(groupby_time.reset_index()['publication_timestamp'],7,labels=[1,2,3,4,5,6,7]).tolist()\n",
    "groupby_time_new=groupby_time.groupby('grp')[['rt_count', 'favourite_count',\n",
    "       'total_engagement_count', 'likes', 'shares', 'comments', 'tw_count',\n",
    "       'li_count', 'pi_count','sentiment']].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rt_count</th>\n",
       "      <th>favourite_count</th>\n",
       "      <th>total_engagement_count</th>\n",
       "      <th>likes</th>\n",
       "      <th>shares</th>\n",
       "      <th>comments</th>\n",
       "      <th>tw_count</th>\n",
       "      <th>li_count</th>\n",
       "      <th>pi_count</th>\n",
       "      <th>sentiment</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>grp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>198144.0</td>\n",
       "      <td>263577.0</td>\n",
       "      <td>143116880.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10110410.0</td>\n",
       "      <td>1242125</td>\n",
       "      <td>29938</td>\n",
       "      <td>-6708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20771.0</td>\n",
       "      <td>23988.0</td>\n",
       "      <td>4471641.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>289510.0</td>\n",
       "      <td>139478</td>\n",
       "      <td>3057</td>\n",
       "      <td>-1494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25790.0</td>\n",
       "      <td>46809.0</td>\n",
       "      <td>1701502.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207856.0</td>\n",
       "      <td>131425</td>\n",
       "      <td>1804</td>\n",
       "      <td>-612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>55044.0</td>\n",
       "      <td>115518.0</td>\n",
       "      <td>66617033.0</td>\n",
       "      <td>38242610.0</td>\n",
       "      <td>6493134.0</td>\n",
       "      <td>18561306.0</td>\n",
       "      <td>696521.0</td>\n",
       "      <td>88587</td>\n",
       "      <td>8266</td>\n",
       "      <td>-1530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>27126.0</td>\n",
       "      <td>58312.0</td>\n",
       "      <td>6619242.0</td>\n",
       "      <td>5031098.0</td>\n",
       "      <td>927713.0</td>\n",
       "      <td>660752.0</td>\n",
       "      <td>171138.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3038</td>\n",
       "      <td>-584</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     rt_count  favourite_count  total_engagement_count       likes     shares  \\\n",
       "grp                                                                             \n",
       "1    198144.0         263577.0             143116880.0         0.0        0.0   \n",
       "2     20771.0          23988.0               4471641.0         0.0        0.0   \n",
       "3     25790.0          46809.0               1701502.0         0.0        0.0   \n",
       "4     55044.0         115518.0              66617033.0  38242610.0  6493134.0   \n",
       "5     27126.0          58312.0               6619242.0   5031098.0   927713.0   \n",
       "\n",
       "       comments    tw_count  li_count  pi_count  sentiment  \n",
       "grp                                                         \n",
       "1           0.0  10110410.0   1242125     29938      -6708  \n",
       "2           0.0    289510.0    139478      3057      -1494  \n",
       "3           0.0    207856.0    131425      1804       -612  \n",
       "4    18561306.0    696521.0     88587      8266      -1530  \n",
       "5      660752.0    171138.0         0      3038       -584  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupby_time_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rt_count</th>\n",
       "      <th>favourite_count</th>\n",
       "      <th>total_engagement_count</th>\n",
       "      <th>likes</th>\n",
       "      <th>shares</th>\n",
       "      <th>comments</th>\n",
       "      <th>tw_count</th>\n",
       "      <th>li_count</th>\n",
       "      <th>pi_count</th>\n",
       "      <th>sentiment</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>grp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>198144.0</td>\n",
       "      <td>263577.0</td>\n",
       "      <td>143116880.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10110410.0</td>\n",
       "      <td>1242125</td>\n",
       "      <td>29938</td>\n",
       "      <td>-6708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20771.0</td>\n",
       "      <td>23988.0</td>\n",
       "      <td>4471641.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>289510.0</td>\n",
       "      <td>139478</td>\n",
       "      <td>3057</td>\n",
       "      <td>-1494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25790.0</td>\n",
       "      <td>46809.0</td>\n",
       "      <td>1701502.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>207856.0</td>\n",
       "      <td>131425</td>\n",
       "      <td>1804</td>\n",
       "      <td>-612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>55044.0</td>\n",
       "      <td>115518.0</td>\n",
       "      <td>66617033.0</td>\n",
       "      <td>38242610.0</td>\n",
       "      <td>6493134.0</td>\n",
       "      <td>18561306.0</td>\n",
       "      <td>696521.0</td>\n",
       "      <td>88587</td>\n",
       "      <td>8266</td>\n",
       "      <td>-1530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>27126.0</td>\n",
       "      <td>58312.0</td>\n",
       "      <td>6619242.0</td>\n",
       "      <td>5031098.0</td>\n",
       "      <td>927713.0</td>\n",
       "      <td>660752.0</td>\n",
       "      <td>171138.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3038</td>\n",
       "      <td>-584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4786.0</td>\n",
       "      <td>11167.0</td>\n",
       "      <td>1107792.0</td>\n",
       "      <td>715790.0</td>\n",
       "      <td>117142.0</td>\n",
       "      <td>274986.0</td>\n",
       "      <td>27995.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2595</td>\n",
       "      <td>-290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>39644.0</td>\n",
       "      <td>82885.0</td>\n",
       "      <td>8038698.0</td>\n",
       "      <td>5355633.0</td>\n",
       "      <td>1324830.0</td>\n",
       "      <td>1358236.0</td>\n",
       "      <td>1565953.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3149</td>\n",
       "      <td>-498</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     rt_count  favourite_count  total_engagement_count       likes     shares  \\\n",
       "grp                                                                             \n",
       "1    198144.0         263577.0             143116880.0         0.0        0.0   \n",
       "2     20771.0          23988.0               4471641.0         0.0        0.0   \n",
       "3     25790.0          46809.0               1701502.0         0.0        0.0   \n",
       "4     55044.0         115518.0              66617033.0  38242610.0  6493134.0   \n",
       "5     27126.0          58312.0               6619242.0   5031098.0   927713.0   \n",
       "6      4786.0          11167.0               1107792.0    715790.0   117142.0   \n",
       "7     39644.0          82885.0               8038698.0   5355633.0  1324830.0   \n",
       "\n",
       "       comments    tw_count  li_count  pi_count  sentiment  \n",
       "grp                                                         \n",
       "1           0.0  10110410.0   1242125     29938      -6708  \n",
       "2           0.0    289510.0    139478      3057      -1494  \n",
       "3           0.0    207856.0    131425      1804       -612  \n",
       "4    18561306.0    696521.0     88587      8266      -1530  \n",
       "5      660752.0    171138.0         0      3038       -584  \n",
       "6      274986.0     27995.0         0      2595       -290  \n",
       "7     1358236.0   1565953.0         0      3149       -498  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groupby_time_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = c8.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['created_at'] = df2['publication_timestamp'].apply(lambda x: x.strftime('%Y-%m'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df2.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>article_id</th>\n",
       "      <th>twitter_url</th>\n",
       "      <th>favourite_count</th>\n",
       "      <th>rt_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>description</th>\n",
       "      <th>article_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>336914f0-d1d6-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816358480277815296</td>\n",
       "      <td>110</td>\n",
       "      <td>97</td>\n",
       "      <td>1483470003000</td>\n",
       "      <td>United Airlines is investigating how a baggage...</td>\n",
       "      <td>http://www.cnn.com/2017/01/03/aviation/trapped...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0648ed40-d16a-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816301537752387584</td>\n",
       "      <td>63</td>\n",
       "      <td>68</td>\n",
       "      <td>1483456427000</td>\n",
       "      <td>A MAN has been arrested after he allegedly lau...</td>\n",
       "      <td>http://www.news.com.au/travel/travel-updates/i...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>97c8d860-d1ca-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816309417356460032</td>\n",
       "      <td>78</td>\n",
       "      <td>58</td>\n",
       "      <td>1483458306000</td>\n",
       "      <td>The flight reached an altitude of 27,000 feet.</td>\n",
       "      <td>http://fortune.com/2017/01/03/united-airlines-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>97c8d860-d1ca-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816480537397301252</td>\n",
       "      <td>67</td>\n",
       "      <td>48</td>\n",
       "      <td>1483499104000</td>\n",
       "      <td>The flight reached an altitude of 27,000 feet.</td>\n",
       "      <td>http://fortune.com/2017/01/03/united-airlines-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>efc970d0-d1b9-11e6-ab89-c11942898bd6</td>\n",
       "      <td>https://twitter.com/any/status/816309410117091329</td>\n",
       "      <td>9</td>\n",
       "      <td>31</td>\n",
       "      <td>1483458304000</td>\n",
       "      <td>United Airlines says it is investigating after...</td>\n",
       "      <td>http://globalnews.ca/news/3157095/united-airli...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16651</th>\n",
       "      <td>513da1e0-6a12-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>https://twitter.com/any/status/112284785147250...</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1556542763000</td>\n",
       "      <td>A B.C. man says airline officials and airport ...</td>\n",
       "      <td>https://www.cbc.ca/news/canada/british-columbi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16652</th>\n",
       "      <td>513da1e0-6a12-11e9-9225-3fa9c6a5bcbe</td>\n",
       "      <td>https://twitter.com/any/status/112265730151559...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1556497333000</td>\n",
       "      <td>A B.C. man says airline officials and airport ...</td>\n",
       "      <td>https://www.cbc.ca/news/canada/british-columbi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16653</th>\n",
       "      <td>0967d860-6b5a-11e9-8e10-c9c12ecf2ad3</td>\n",
       "      <td>https://twitter.com/any/status/112324287987321...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1556636946000</td>\n",
       "      <td>Many fliers in California will feel the brunt ...</td>\n",
       "      <td>https://www.sfgate.com/travel/article/Boeing-7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16654</th>\n",
       "      <td>0967d860-6b5a-11e9-8e10-c9c12ecf2ad3</td>\n",
       "      <td>https://twitter.com/any/status/112324509643707...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1556637474000</td>\n",
       "      <td>Many fliers in California will feel the brunt ...</td>\n",
       "      <td>https://www.sfgate.com/travel/article/Boeing-7...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16654</th>\n",
       "      <td>0967d860-6b5a-11e9-8e10-c9c12ecf2ad3</td>\n",
       "      <td>https://twitter.com/any/status/112337837676324...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1556669251000</td>\n",
       "      <td>Many fliers in California will feel the brunt ...</td>\n",
       "      <td>https://www.sfgate.com/travel/article/Boeing-7...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21972 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 article_id  \\\n",
       "0      336914f0-d1d6-11e6-ab89-c11942898bd6   \n",
       "1      0648ed40-d16a-11e6-ab89-c11942898bd6   \n",
       "2      97c8d860-d1ca-11e6-ab89-c11942898bd6   \n",
       "2      97c8d860-d1ca-11e6-ab89-c11942898bd6   \n",
       "3      efc970d0-d1b9-11e6-ab89-c11942898bd6   \n",
       "...                                     ...   \n",
       "16651  513da1e0-6a12-11e9-9225-3fa9c6a5bcbe   \n",
       "16652  513da1e0-6a12-11e9-9225-3fa9c6a5bcbe   \n",
       "16653  0967d860-6b5a-11e9-8e10-c9c12ecf2ad3   \n",
       "16654  0967d860-6b5a-11e9-8e10-c9c12ecf2ad3   \n",
       "16654  0967d860-6b5a-11e9-8e10-c9c12ecf2ad3   \n",
       "\n",
       "                                             twitter_url  favourite_count  \\\n",
       "0      https://twitter.com/any/status/816358480277815296              110   \n",
       "1      https://twitter.com/any/status/816301537752387584               63   \n",
       "2      https://twitter.com/any/status/816309417356460032               78   \n",
       "2      https://twitter.com/any/status/816480537397301252               67   \n",
       "3      https://twitter.com/any/status/816309410117091329                9   \n",
       "...                                                  ...              ...   \n",
       "16651  https://twitter.com/any/status/112284785147250...                3   \n",
       "16652  https://twitter.com/any/status/112265730151559...                0   \n",
       "16653  https://twitter.com/any/status/112324287987321...                1   \n",
       "16654  https://twitter.com/any/status/112324509643707...                2   \n",
       "16654  https://twitter.com/any/status/112337837676324...                0   \n",
       "\n",
       "       rt_count     created_at  \\\n",
       "0            97  1483470003000   \n",
       "1            68  1483456427000   \n",
       "2            58  1483458306000   \n",
       "2            48  1483499104000   \n",
       "3            31  1483458304000   \n",
       "...         ...            ...   \n",
       "16651         1  1556542763000   \n",
       "16652         1  1556497333000   \n",
       "16653         1  1556636946000   \n",
       "16654         1  1556637474000   \n",
       "16654         1  1556669251000   \n",
       "\n",
       "                                             description  \\\n",
       "0      United Airlines is investigating how a baggage...   \n",
       "1      A MAN has been arrested after he allegedly lau...   \n",
       "2         The flight reached an altitude of 27,000 feet.   \n",
       "2         The flight reached an altitude of 27,000 feet.   \n",
       "3      United Airlines says it is investigating after...   \n",
       "...                                                  ...   \n",
       "16651  A B.C. man says airline officials and airport ...   \n",
       "16652  A B.C. man says airline officials and airport ...   \n",
       "16653  Many fliers in California will feel the brunt ...   \n",
       "16654  Many fliers in California will feel the brunt ...   \n",
       "16654  Many fliers in California will feel the brunt ...   \n",
       "\n",
       "                                             article_url  \n",
       "0      http://www.cnn.com/2017/01/03/aviation/trapped...  \n",
       "1      http://www.news.com.au/travel/travel-updates/i...  \n",
       "2      http://fortune.com/2017/01/03/united-airlines-...  \n",
       "2      http://fortune.com/2017/01/03/united-airlines-...  \n",
       "3      http://globalnews.ca/news/3157095/united-airli...  \n",
       "...                                                  ...  \n",
       "16651  https://www.cbc.ca/news/canada/british-columbi...  \n",
       "16652  https://www.cbc.ca/news/canada/british-columbi...  \n",
       "16653  https://www.sfgate.com/travel/article/Boeing-7...  \n",
       "16654  https://www.sfgate.com/travel/article/Boeing-7...  \n",
       "16654  https://www.sfgate.com/travel/article/Boeing-7...  \n",
       "\n",
       "[21972 rows x 7 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "date = sorted(df2['created_at'].drop_duplicates().tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2017-01',\n",
       " '2017-02',\n",
       " '2017-03',\n",
       " '2017-04',\n",
       " '2017-05',\n",
       " '2017-06',\n",
       " '2017-07',\n",
       " '2017-08',\n",
       " '2017-09',\n",
       " '2017-10',\n",
       " '2017-11',\n",
       " '2017-12',\n",
       " '2018-01',\n",
       " '2018-02',\n",
       " '2018-03',\n",
       " '2018-04',\n",
       " '2018-05',\n",
       " '2018-06',\n",
       " '2018-07',\n",
       " '2018-08',\n",
       " '2018-09',\n",
       " '2018-10',\n",
       " '2018-11',\n",
       " '2018-12',\n",
       " '2019-01',\n",
       " '2019-02',\n",
       " '2019-03',\n",
       " '2019-04']"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'floor' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-38-49971fa80fe4>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[0mtrimester\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;33m{\u001b[0m\u001b[1;33m}\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mi\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mrange\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdate\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m     \u001b[0mtrimester\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mdate\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mi\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mfloor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mi\u001b[0m\u001b[1;33m/\u001b[0m\u001b[1;36m4\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'floor' is not defined"
     ]
    }
   ],
   "source": [
    "trimester={}\n",
    "for i in range(0, len(date)):\n",
    "    trimester[date[i]]=floor((i/4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from math import floor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trimester"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trimester['2019-05']=6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trimester"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['trimester'] = df2['created_at'].apply(lambda x: trimester[x])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_time = df2.groupby('trimester')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_time.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_time['trimester']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_time.sum()['trimester']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['favourite_count'].fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.to_csv('article_statistic.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}