code/nb1_data_prep.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Preparation\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Details\n",
    "\n",
    "Using both externally collected and personally collected Tweets. \n",
    "\n",
    "### External Data \n",
    "* [Tweets about distance learning] (https://www.kaggle.com/barishasdemir/tweets-about-distance-learning)\n",
    "* Collected by the refrenced Kaggle user with the Twitter API via tweepy. \n",
    "* Collection Dates: July 23 2020 through August 14 2020\n",
    "* Location: Worldwide\n",
    "* Hashtags: \n",
    "    * #distancelearning, #onlineschool, #onlineteaching, #virtuallearning, #onlineducation, #distanceeducation, #OnlineClasses, #DigitalLearning, #elearning, #onlinelearning\n",
    "* Keywords: \n",
    "    * “distance learning”, “online teaching”, “online education”, “online course”, “online semester”, “distance course”, “distance education”, “online class”,” e-learning”, “e learning\"\n",
    "\n",
    "### Collected Data\n",
    "* Collected by the author using the Twitter API via __tweepy__. Tweets collected via Queries and Streaming. Did not collect Retweets.\n",
    "* Stored in __MongoDb__ then exported to csv files for further processing.\n",
    "* I performed sentiment labeling for a portion of the Tweets I collected. Took a hybrid approach of some human labeling and sentiment tool labeling using __VADER__ and __TextBlob__ tools.\n",
    "* Collection Dates: January 06 2021 through January 14 2021\n",
    "* Location: United States\n",
    "* Keywords/Query terms:\n",
    "    * 'k-12 remote', 'k-12 distance', 'k-12 (on-line OR online)', 'k-12 virtual', 'k-12 hybrid', 'teach remote learn', 'teach distance learn', 'teach (on-line OR online) learn', 'teach virtual learn', 'teach hybrid learn', '(kid OR child) remote learn', '(kid OR child) distance learn', '(kid OR child) (on-line OR online) learn', '(kid OR child) virtual learn', '(kid OR child) hybrid learn'\n",
    "\n",
    "\n",
    "### Data Prep steps: \n",
    "* Repeat for each data set:\n",
    "    * Read in csv\n",
    "    * (External only) Standardize column names as needed    \n",
    "    * (External only) Select only tweets with likely US locations\n",
    "    * Select Tweets with character count 100 and above\n",
    "    * Apply the VADER and Text Blob sentiment tools to Tweets that are not human labeled. \n",
    "    * Select Tweets where both tools determine the __same__ sentiment (alternative to Human labeling)\n",
    "    * Save individual dataset to file\n",
    "* Combine all data sets to ONE dataframe \n",
    "* Check for null and duplicate content\n",
    "* Save to ONE csv file\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the required libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "\n",
    "from vaderSentiment.vaderSentiment import SentimentIntensityAnalyzer\n",
    "from textblob import TextBlob\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Functions \n",
    "usa_states_fullname_regex = '(ALABAMA|ALASKA|ARIZONA|ARKANSAS|CALIFORNIA|'\\\n",
    "                            'COLORADO|CONNECTICUT|DELAWARE|FLORIDA|GEORGIA|HAWAII|'\\\n",
    "                            'IDAHO|ILLINOIS|INDIANA|IOWA|KANSAS|KENTUCKY|'\\\n",
    "                            'LOUISIANA|MAINE|MARYLAND|MASSACHUSETTS|MICHIGAN|'\\\n",
    "                            'MINNESOTA|MISSISSIPPI|MISSOURI|MONTANA|'\\\n",
    "                            'NEBRASKA|NEVADA|NEW\\sHAMPSHIRE|NEWSJERSEY|'\\\n",
    "                            'NEW\\sMEXICO|NEW\\sYORK|NORTH\\sCAROLINA|'\\\n",
    "                            'NORTH\\sDAKOTA|OHIO|OKLAHOMA|OREGON|PENNSYLVANIA|'\\\n",
    "                            'RHODE\\sISLAND|SOUTH\\sCAROLINA|SOUTH\\sDAKOTA|'\\\n",
    "                            'TENNESSEE|TEXAS|UTAH|VERMONT|VIRGINIA|'\\\n",
    "                            'WASHINGTON|WEST\\sVIRGINIA|WISCONSIN|WYOMING|USA)'\n",
    "\n",
    "\n",
    "usa_states_regex = ',\\s{1}(A[KLRZ]|C[AOT]|D[CE]|FL|GA|HI|I[ADLN]|K[SY]|LA|M[ADEINOST]|N[CDEHJMVY]|O[HKR]|P[AR]|RI|S[CD]|T[NX]|UT|V[AIT]|W[AIVY])'\n",
    "\n",
    "def get_exact_dups(df):\n",
    "    '''\n",
    "    Returns duplicates\n",
    "    '''\n",
    "    dups = df[df.duplicated()]\n",
    "    return dups\n",
    "\n",
    "def get_tweet_dups(df, col_names):\n",
    "    '''\n",
    "    Returns duplicates based on given column name\n",
    "    '''\n",
    "    dups = df[df.duplicated(subset=col_names)]\n",
    "    return dups\n",
    "\n",
    "def get_is_us_loc(loc_string):\n",
    "    '''\n",
    "    Uses regular expression(s) to detect if provided Location string \n",
    "    is a probable United States location.\n",
    "    '''\n",
    "    matches_abbrev = bool(re.search(usa_states_regex, loc_string.upper()))\n",
    "    if not matches_abbrev:\n",
    "        matches_full_name = bool(re.search(usa_states_fullname_regex, loc_string.upper())) \n",
    "    return (matches_abbrev or matches_full_name)\n",
    "\n",
    "def get_vader_sentiment(vader_analyzer, tweet):\n",
    "    '''\n",
    "    Get sentiment of given Tweet text using VADER sentiment tool.\n",
    "    '''\n",
    "    tweet = tweet.replace('#','')  # we want things like #fail to be included in text\n",
    "    vader_scores = vader_analyzer.polarity_scores(tweet)\n",
    "    compound_score = vader_scores['compound']\n",
    "    vader_sentiment = None\n",
    "    # using thresholds from VADER developers/researchers\n",
    "    if (compound_score >= 0.05):\n",
    "        vader_sentiment = 'positive'\n",
    "    elif (compound_score < 0.05 and compound_score > -0.05):\n",
    "        vader_sentiment = 'neutral'\n",
    "    elif (compound_score <= -0.05):\n",
    "        vader_sentiment = 'negative'\n",
    "    return vader_sentiment\n",
    "\n",
    "def get_text_blob_sentiment(tweet):\n",
    "    '''\n",
    "    Get sentiment of given Tweet text using TextBlob sentiment tool\n",
    "    '''\n",
    "    polarity = TextBlob(tweet).sentiment.polarity\n",
    "    # The polarity score is a float within the range [-1.0, 1.0]. \n",
    "    textblob_sentiment = None\n",
    "    if (polarity > 0):\n",
    "        textblob_sentiment = 'positive'\n",
    "    elif (polarity == 0):\n",
    "        textblob_sentiment = 'neutral'\n",
    "    elif (polarity < 0):\n",
    "        textblob_sentiment = 'negative'\n",
    "    return textblob_sentiment  \n",
    "    \n",
    "\n",
    "def get_tools_match(vader_sentiment, tb_sentiment):\n",
    "    '''\n",
    "    Return True if the provided sentiment labels match, False if not.\n",
    "    '''\n",
    "    return vader_sentiment == tb_sentiment\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 202645 entries, 0 to 202644\n",
      "Data columns (total 8 columns):\n",
      " #   Column         Non-Null Count   Dtype \n",
      "---  ------         --------------   ----- \n",
      " 0   Unnamed: 0     202645 non-null  int64 \n",
      " 1   Unnamed: 0.1   202645 non-null  int64 \n",
      " 2   Content        202645 non-null  object\n",
      " 3   Location       155123 non-null  object\n",
      " 4   Username       202645 non-null  object\n",
      " 5   Retweet-Count  202645 non-null  int64 \n",
      " 6   Favorites      202645 non-null  int64 \n",
      " 7   Created at     202645 non-null  object\n",
      "dtypes: int64(4), object(4)\n",
      "memory usage: 12.4+ MB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# Read in the external data: \n",
    "external_data = pd.read_csv('../external_data/tweets_raw.csv')\n",
    "print(external_data.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 202645 entries, 0 to 202644\n",
      "Data columns (total 8 columns):\n",
      " #   Column            Non-Null Count   Dtype \n",
      "---  ------            --------------   ----- \n",
      " 0   Unnamed: 0        202645 non-null  int64 \n",
      " 1   Unnamed: 0.1      202645 non-null  int64 \n",
      " 2   content           202645 non-null  object\n",
      " 3   user_loc          155123 non-null  object\n",
      " 4   user_screen_name  202645 non-null  object\n",
      " 5   retweet_count     202645 non-null  int64 \n",
      " 6   fav_count         202645 non-null  int64 \n",
      " 7   created_at        202645 non-null  object\n",
      "dtypes: int64(4), object(4)\n",
      "memory usage: 12.4+ MB\n"
     ]
    }
   ],
   "source": [
    "#rename columns\n",
    "external_data.rename(columns = { 'Content':'content',\n",
    "                                 'Location':'user_loc', \n",
    "                                 'Username':'user_screen_name', \n",
    "                                 'Retweet-Count':'retweet_count', \n",
    "                                 'Favorites':'fav_count', \n",
    "                                 'Created at': 'created_at'}, inplace = True) \n",
    "external_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(139410, 8)\n"
     ]
    }
   ],
   "source": [
    "# Drop the rows with null user_location and Duplicated content\n",
    "external_data.dropna(subset=['user_loc'], inplace=True)\n",
    "external_data.drop_duplicates(subset=['content'], inplace=True)\n",
    "print(external_data.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(64344, 8)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check location for a US state (using regex). We only want to use Tweets with a US location\n",
    "external_data['is_us_loc'] = external_data.apply(lambda row: get_is_us_loc(row['user_loc']), axis=1)\n",
    "us_only_data = external_data[external_data['is_us_loc'] == True]\n",
    "us_only_data.drop('is_us_loc', axis=1, inplace=True)\n",
    "us_only_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 0.1</th>\n",
       "      <th>content</th>\n",
       "      <th>user_loc</th>\n",
       "      <th>user_screen_name</th>\n",
       "      <th>retweet_count</th>\n",
       "      <th>fav_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>char_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>“Instructional Considerations for the 2020-21 ...</td>\n",
       "      <td>Illinois, USA</td>\n",
       "      <td>Erik_Youngman</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-08-02 00:10:26</td>\n",
       "      <td>276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>With all the uncertainty of what September wil...</td>\n",
       "      <td>Lyndhurst, NJ</td>\n",
       "      <td>Renee_LoBue</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-01 23:57:31</td>\n",
       "      <td>264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>Check this out on Wakelet - Digital learning a...</td>\n",
       "      <td>Cary, NC</td>\n",
       "      <td>SupriyaVasu</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-01 23:20:38</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0  Unnamed: 0.1  \\\n",
       "9            9             9   \n",
       "10          10            10   \n",
       "11          11            11   \n",
       "\n",
       "                                              content       user_loc  \\\n",
       "9   “Instructional Considerations for the 2020-21 ...  Illinois, USA   \n",
       "10  With all the uncertainty of what September wil...  Lyndhurst, NJ   \n",
       "11  Check this out on Wakelet - Digital learning a...       Cary, NC   \n",
       "\n",
       "   user_screen_name  retweet_count  fav_count           created_at  char_count  \n",
       "9     Erik_Youngman              0          2  2020-08-02 00:10:26         276  \n",
       "10      Renee_LoBue              0          0  2020-08-01 23:57:31         264  \n",
       "11      SupriyaVasu              0          0  2020-08-01 23:20:38         133  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the char count\n",
    "us_only_data['char_count'] = us_only_data.apply(lambda row: len(row['content']), axis=1)\n",
    "us_only_data.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(58941, 9)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Keep only the tweets with 100 and above characters\n",
    "us_only_data = us_only_data[us_only_data['char_count'] >= 100]\n",
    "us_only_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 0.1</th>\n",
       "      <th>content</th>\n",
       "      <th>user_loc</th>\n",
       "      <th>user_screen_name</th>\n",
       "      <th>retweet_count</th>\n",
       "      <th>fav_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>char_count</th>\n",
       "      <th>vader_sentiment</th>\n",
       "      <th>text_blob_sentiment</th>\n",
       "      <th>tools_match</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>“Instructional Considerations for the 2020-21 ...</td>\n",
       "      <td>Illinois, USA</td>\n",
       "      <td>Erik_Youngman</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-08-02 00:10:26</td>\n",
       "      <td>276</td>\n",
       "      <td>neutral</td>\n",
       "      <td>neutral</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>With all the uncertainty of what September wil...</td>\n",
       "      <td>Lyndhurst, NJ</td>\n",
       "      <td>Renee_LoBue</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-01 23:57:31</td>\n",
       "      <td>264</td>\n",
       "      <td>positive</td>\n",
       "      <td>positive</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>Check this out on Wakelet - Digital learning a...</td>\n",
       "      <td>Cary, NC</td>\n",
       "      <td>SupriyaVasu</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-01 23:20:38</td>\n",
       "      <td>133</td>\n",
       "      <td>neutral</td>\n",
       "      <td>neutral</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0  Unnamed: 0.1  \\\n",
       "9            9             9   \n",
       "10          10            10   \n",
       "11          11            11   \n",
       "\n",
       "                                              content       user_loc  \\\n",
       "9   “Instructional Considerations for the 2020-21 ...  Illinois, USA   \n",
       "10  With all the uncertainty of what September wil...  Lyndhurst, NJ   \n",
       "11  Check this out on Wakelet - Digital learning a...       Cary, NC   \n",
       "\n",
       "   user_screen_name  retweet_count  fav_count           created_at  \\\n",
       "9     Erik_Youngman              0          2  2020-08-02 00:10:26   \n",
       "10      Renee_LoBue              0          0  2020-08-01 23:57:31   \n",
       "11      SupriyaVasu              0          0  2020-08-01 23:20:38   \n",
       "\n",
       "    char_count vader_sentiment text_blob_sentiment  tools_match  \n",
       "9          276         neutral             neutral         True  \n",
       "10         264        positive            positive         True  \n",
       "11         133         neutral             neutral         True  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the VADER and TextBlob sentiments\n",
    "analyzer = SentimentIntensityAnalyzer()\n",
    "us_only_data['vader_sentiment'] = us_only_data.apply(lambda row: get_vader_sentiment(analyzer, row['content']), axis=1)\n",
    "us_only_data['text_blob_sentiment'] = us_only_data.apply(lambda row: get_text_blob_sentiment(row['content']), axis=1)\n",
    "\n",
    "# Mark where the two tools agree\n",
    "us_only_data['tools_match'] = us_only_data.apply(lambda row: get_tools_match(row['vader_sentiment'], row['text_blob_sentiment']), axis=1)\n",
    "\n",
    "# keep only the tweets where the tools agree \n",
    "us_only_data = us_only_data[us_only_data['tools_match']]\n",
    "us_only_data.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 0.1</th>\n",
       "      <th>content</th>\n",
       "      <th>user_loc</th>\n",
       "      <th>user_screen_name</th>\n",
       "      <th>retweet_count</th>\n",
       "      <th>fav_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>char_count</th>\n",
       "      <th>sentiment_method</th>\n",
       "      <th>sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>“Instructional Considerations for the 2020-21 ...</td>\n",
       "      <td>Illinois, USA</td>\n",
       "      <td>Erik_Youngman</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-08-02 00:10:26</td>\n",
       "      <td>276</td>\n",
       "      <td>tools</td>\n",
       "      <td>neutral</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>With all the uncertainty of what September wil...</td>\n",
       "      <td>Lyndhurst, NJ</td>\n",
       "      <td>Renee_LoBue</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-01 23:57:31</td>\n",
       "      <td>264</td>\n",
       "      <td>tools</td>\n",
       "      <td>positive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>Check this out on Wakelet - Digital learning a...</td>\n",
       "      <td>Cary, NC</td>\n",
       "      <td>SupriyaVasu</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-01 23:20:38</td>\n",
       "      <td>133</td>\n",
       "      <td>tools</td>\n",
       "      <td>neutral</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0  Unnamed: 0.1  \\\n",
       "9            9             9   \n",
       "10          10            10   \n",
       "11          11            11   \n",
       "\n",
       "                                              content       user_loc  \\\n",
       "9   “Instructional Considerations for the 2020-21 ...  Illinois, USA   \n",
       "10  With all the uncertainty of what September wil...  Lyndhurst, NJ   \n",
       "11  Check this out on Wakelet - Digital learning a...       Cary, NC   \n",
       "\n",
       "   user_screen_name  retweet_count  fav_count           created_at  \\\n",
       "9     Erik_Youngman              0          2  2020-08-02 00:10:26   \n",
       "10      Renee_LoBue              0          0  2020-08-01 23:57:31   \n",
       "11      SupriyaVasu              0          0  2020-08-01 23:20:38   \n",
       "\n",
       "    char_count sentiment_method sentiment  \n",
       "9          276            tools   neutral  \n",
       "10         264            tools  positive  \n",
       "11         133            tools   neutral  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now create cols for sentiment and sentiment method\n",
    "us_only_data['sentiment_method'] = 'tools'\n",
    "us_only_data['sentiment'] = us_only_data.apply(lambda row: row['vader_sentiment'], axis=1)\n",
    "\n",
    "# delete the now un-needed columns\n",
    "us_only_data.drop('vader_sentiment', axis=1, inplace=True)\n",
    "us_only_data.drop('text_blob_sentiment', axis=1, inplace=True)\n",
    "us_only_data.drop('tools_match', axis=1, inplace=True)\n",
    "us_only_data.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(38044, 11)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_only_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# SAVE to file\n",
    "us_only_data.to_csv('../data/us_only_external_data_tweets_TOOL_labeled.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now prepare the data I collected in January.  \n",
    "### Only keep tweets of 100 characters or more (same criteria applied to external dataset of tweets)\n",
    "### I labeled 356 tweets for sentiment. For time contraints, use the tools to labels the rest - use sentiment from tools where both agree."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tweets from Query in January - HUMAN sentiment label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>_id</th>\n",
       "      <th>id_str</th>\n",
       "      <th>content</th>\n",
       "      <th>user_loc</th>\n",
       "      <th>user_screen_name</th>\n",
       "      <th>retweet_count</th>\n",
       "      <th>fav_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ffde71b5e4953000d99dc7c</td>\n",
       "      <td>1.348996e+18</td>\n",
       "      <td>These really are critical.\\n2yrs ago I took 9 ...</td>\n",
       "      <td>Texas, USA</td>\n",
       "      <td>summers_llm</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 14:11:21</td>\n",
       "      <td>neutral</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ffde71b5e4953000d99dc7d</td>\n",
       "      <td>1.348986e+18</td>\n",
       "      <td>ConnectEd After School Lesson Grades K-2\\nThur...</td>\n",
       "      <td>North Dakota, USA</td>\n",
       "      <td>ncecnd</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 13:30:04</td>\n",
       "      <td>neutral</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ffde71b5e4953000d99dc7e</td>\n",
       "      <td>1.348978e+18</td>\n",
       "      <td>Don't forget to register for our FREE Remote a...</td>\n",
       "      <td>New York City</td>\n",
       "      <td>ReadWorks</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 13:01:36</td>\n",
       "      <td>neutral</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id        id_str  \\\n",
       "0  5ffde71b5e4953000d99dc7c  1.348996e+18   \n",
       "1  5ffde71b5e4953000d99dc7d  1.348986e+18   \n",
       "2  5ffde71b5e4953000d99dc7e  1.348978e+18   \n",
       "\n",
       "                                             content           user_loc  \\\n",
       "0  These really are critical.\\n2yrs ago I took 9 ...         Texas, USA   \n",
       "1  ConnectEd After School Lesson Grades K-2\\nThur...  North Dakota, USA   \n",
       "2  Don't forget to register for our FREE Remote a...      New York City   \n",
       "\n",
       "  user_screen_name  retweet_count  fav_count           created_at sentiment  \n",
       "0      summers_llm              0          0  2021-01-12 14:11:21   neutral  \n",
       "1           ncecnd              0          0  2021-01-12 13:30:04   neutral  \n",
       "2        ReadWorks              0          0  2021-01-12 13:01:36   neutral  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "human_labeled_q_tweets =  pd.read_csv('../data/jan_queried_tweets_HUMAN_labeled.csv')\n",
    "human_labeled_q_tweets.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(348, 10)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Keep only the tweets 100 chars and over\n",
    "human_labeled_q_tweets['char_count'] = human_labeled_q_tweets.apply(lambda row: len(row['content']), axis=1)\n",
    "human_labeled_q_tweets = human_labeled_q_tweets[human_labeled_q_tweets['char_count'] >= 100]\n",
    "human_labeled_q_tweets.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>_id</th>\n",
       "      <th>id_str</th>\n",
       "      <th>content</th>\n",
       "      <th>user_loc</th>\n",
       "      <th>user_screen_name</th>\n",
       "      <th>retweet_count</th>\n",
       "      <th>fav_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>sentiment</th>\n",
       "      <th>char_count</th>\n",
       "      <th>sentiment_method</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ffde71b5e4953000d99dc7c</td>\n",
       "      <td>1.348996e+18</td>\n",
       "      <td>These really are critical.\\n2yrs ago I took 9 ...</td>\n",
       "      <td>Texas, USA</td>\n",
       "      <td>summers_llm</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 14:11:21</td>\n",
       "      <td>neutral</td>\n",
       "      <td>213</td>\n",
       "      <td>human</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ffde71b5e4953000d99dc7d</td>\n",
       "      <td>1.348986e+18</td>\n",
       "      <td>ConnectEd After School Lesson Grades K-2\\nThur...</td>\n",
       "      <td>North Dakota, USA</td>\n",
       "      <td>ncecnd</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 13:30:04</td>\n",
       "      <td>neutral</td>\n",
       "      <td>122</td>\n",
       "      <td>human</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ffde71b5e4953000d99dc7e</td>\n",
       "      <td>1.348978e+18</td>\n",
       "      <td>Don't forget to register for our FREE Remote a...</td>\n",
       "      <td>New York City</td>\n",
       "      <td>ReadWorks</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 13:01:36</td>\n",
       "      <td>neutral</td>\n",
       "      <td>303</td>\n",
       "      <td>human</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id        id_str  \\\n",
       "0  5ffde71b5e4953000d99dc7c  1.348996e+18   \n",
       "1  5ffde71b5e4953000d99dc7d  1.348986e+18   \n",
       "2  5ffde71b5e4953000d99dc7e  1.348978e+18   \n",
       "\n",
       "                                             content           user_loc  \\\n",
       "0  These really are critical.\\n2yrs ago I took 9 ...         Texas, USA   \n",
       "1  ConnectEd After School Lesson Grades K-2\\nThur...  North Dakota, USA   \n",
       "2  Don't forget to register for our FREE Remote a...      New York City   \n",
       "\n",
       "  user_screen_name  retweet_count  fav_count           created_at sentiment  \\\n",
       "0      summers_llm              0          0  2021-01-12 14:11:21   neutral   \n",
       "1           ncecnd              0          0  2021-01-12 13:30:04   neutral   \n",
       "2        ReadWorks              0          0  2021-01-12 13:01:36   neutral   \n",
       "\n",
       "   char_count sentiment_method  \n",
       "0         213            human  \n",
       "1         122            human  \n",
       "2         303            human  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Flag these tweets as having their sentiment set by a human\n",
    "human_labeled_q_tweets['sentiment_method'] = 'human'\n",
    "human_labeled_q_tweets.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 348 entries, 0 to 355\n",
      "Data columns (total 11 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   _id               348 non-null    object \n",
      " 1   id_str            348 non-null    float64\n",
      " 2   content           348 non-null    object \n",
      " 3   user_loc          348 non-null    object \n",
      " 4   user_screen_name  348 non-null    object \n",
      " 5   retweet_count     348 non-null    int64  \n",
      " 6   fav_count         348 non-null    int64  \n",
      " 7   created_at        348 non-null    object \n",
      " 8   sentiment         348 non-null    object \n",
      " 9   char_count        348 non-null    int64  \n",
      " 10  sentiment_method  348 non-null    object \n",
      "dtypes: float64(1), int64(3), object(7)\n",
      "memory usage: 32.6+ KB\n"
     ]
    }
   ],
   "source": [
    "human_labeled_q_tweets.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save to file \n",
    "human_labeled_q_tweets.to_csv('../data/jan_2021_queried_tweets_HUMAN_labeled.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tweets from query in January - not labeled yet\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>_id</th>\n",
       "      <th>id_str</th>\n",
       "      <th>content</th>\n",
       "      <th>user_loc</th>\n",
       "      <th>user_screen_name</th>\n",
       "      <th>retweet_count</th>\n",
       "      <th>fav_count</th>\n",
       "      <th>created_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ffde71f5e4953000d99dd55</td>\n",
       "      <td>1.348735e+18</td>\n",
       "      <td>Virtual learning has caused a fair share of fr...</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>Edficiency</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-11 20:55:39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ffde71f5e4953000d99dd58</td>\n",
       "      <td>1.348704e+18</td>\n",
       "      <td>Preparing for high school is an exciting time!...</td>\n",
       "      <td>St. Augustine, Florida</td>\n",
       "      <td>SJCSD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-11 18:51:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ffde71f5e4953000d99dd59</td>\n",
       "      <td>1.348685e+18</td>\n",
       "      <td>Updated list of Wi-Fi Hotspots in Accomack Cou...</td>\n",
       "      <td>Oak Hall, Virginia</td>\n",
       "      <td>AMSPANTHERS</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-11 17:33:40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id        id_str  \\\n",
       "0  5ffde71f5e4953000d99dd55  1.348735e+18   \n",
       "1  5ffde71f5e4953000d99dd58  1.348704e+18   \n",
       "2  5ffde71f5e4953000d99dd59  1.348685e+18   \n",
       "\n",
       "                                             content                user_loc  \\\n",
       "0  Virtual learning has caused a fair share of fr...               Wisconsin   \n",
       "1  Preparing for high school is an exciting time!...  St. Augustine, Florida   \n",
       "2  Updated list of Wi-Fi Hotspots in Accomack Cou...      Oak Hall, Virginia   \n",
       "\n",
       "  user_screen_name  retweet_count  fav_count           created_at  \n",
       "0       Edficiency              0          0  2021-01-11 20:55:39  \n",
       "1            SJCSD              0          0  2021-01-11 18:51:41  \n",
       "2      AMSPANTHERS              2          2  2021-01-11 17:33:40  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q_tweets =  pd.read_csv('../data/jan_queried_tweets_NO_label.csv')\n",
    "q_tweets.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(222, 8)"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q_tweets.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(212, 9)\n"
     ]
    },
    {
     "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>_id</th>\n",
       "      <th>id_str</th>\n",
       "      <th>content</th>\n",
       "      <th>user_loc</th>\n",
       "      <th>user_screen_name</th>\n",
       "      <th>retweet_count</th>\n",
       "      <th>fav_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>char_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ffde71f5e4953000d99dd55</td>\n",
       "      <td>1.348735e+18</td>\n",
       "      <td>Virtual learning has caused a fair share of fr...</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>Edficiency</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-11 20:55:39</td>\n",
       "      <td>272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ffde71f5e4953000d99dd58</td>\n",
       "      <td>1.348704e+18</td>\n",
       "      <td>Preparing for high school is an exciting time!...</td>\n",
       "      <td>St. Augustine, Florida</td>\n",
       "      <td>SJCSD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-11 18:51:41</td>\n",
       "      <td>221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ffde71f5e4953000d99dd59</td>\n",
       "      <td>1.348685e+18</td>\n",
       "      <td>Updated list of Wi-Fi Hotspots in Accomack Cou...</td>\n",
       "      <td>Oak Hall, Virginia</td>\n",
       "      <td>AMSPANTHERS</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-11 17:33:40</td>\n",
       "      <td>146</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id        id_str  \\\n",
       "0  5ffde71f5e4953000d99dd55  1.348735e+18   \n",
       "1  5ffde71f5e4953000d99dd58  1.348704e+18   \n",
       "2  5ffde71f5e4953000d99dd59  1.348685e+18   \n",
       "\n",
       "                                             content                user_loc  \\\n",
       "0  Virtual learning has caused a fair share of fr...               Wisconsin   \n",
       "1  Preparing for high school is an exciting time!...  St. Augustine, Florida   \n",
       "2  Updated list of Wi-Fi Hotspots in Accomack Cou...      Oak Hall, Virginia   \n",
       "\n",
       "  user_screen_name  retweet_count  fav_count           created_at  char_count  \n",
       "0       Edficiency              0          0  2021-01-11 20:55:39         272  \n",
       "1            SJCSD              0          0  2021-01-11 18:51:41         221  \n",
       "2      AMSPANTHERS              2          2  2021-01-11 17:33:40         146  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Keep only the tweets 100 chars and over\n",
    "q_tweets['char_count'] = q_tweets.apply(lambda row: len(row['content']), axis=1)\n",
    "q_tweets = q_tweets[q_tweets['char_count'] >= 100]\n",
    "print(q_tweets.shape)\n",
    "q_tweets.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(130, 12)\n"
     ]
    },
    {
     "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>_id</th>\n",
       "      <th>id_str</th>\n",
       "      <th>content</th>\n",
       "      <th>user_loc</th>\n",
       "      <th>user_screen_name</th>\n",
       "      <th>retweet_count</th>\n",
       "      <th>fav_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>char_count</th>\n",
       "      <th>vader_sentiment</th>\n",
       "      <th>text_blob_sentiment</th>\n",
       "      <th>tools_match</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ffde71f5e4953000d99dd55</td>\n",
       "      <td>1.348735e+18</td>\n",
       "      <td>Virtual learning has caused a fair share of fr...</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>Edficiency</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-11 20:55:39</td>\n",
       "      <td>272</td>\n",
       "      <td>positive</td>\n",
       "      <td>positive</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ffde71f5e4953000d99dd58</td>\n",
       "      <td>1.348704e+18</td>\n",
       "      <td>Preparing for high school is an exciting time!...</td>\n",
       "      <td>St. Augustine, Florida</td>\n",
       "      <td>SJCSD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-11 18:51:41</td>\n",
       "      <td>221</td>\n",
       "      <td>positive</td>\n",
       "      <td>positive</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5ffde71f5e4953000d99dd5d</td>\n",
       "      <td>1.348672e+18</td>\n",
       "      <td>The Nature-based 4K Parent Night for enrollmen...</td>\n",
       "      <td>Newburg, WI</td>\n",
       "      <td>RiveredgeNC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-11 16:42:40</td>\n",
       "      <td>276</td>\n",
       "      <td>positive</td>\n",
       "      <td>positive</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id        id_str  \\\n",
       "0  5ffde71f5e4953000d99dd55  1.348735e+18   \n",
       "1  5ffde71f5e4953000d99dd58  1.348704e+18   \n",
       "3  5ffde71f5e4953000d99dd5d  1.348672e+18   \n",
       "\n",
       "                                             content                user_loc  \\\n",
       "0  Virtual learning has caused a fair share of fr...               Wisconsin   \n",
       "1  Preparing for high school is an exciting time!...  St. Augustine, Florida   \n",
       "3  The Nature-based 4K Parent Night for enrollmen...             Newburg, WI   \n",
       "\n",
       "  user_screen_name  retweet_count  fav_count           created_at  char_count  \\\n",
       "0       Edficiency              0          0  2021-01-11 20:55:39         272   \n",
       "1            SJCSD              0          0  2021-01-11 18:51:41         221   \n",
       "3      RiveredgeNC              0          0  2021-01-11 16:42:40         276   \n",
       "\n",
       "  vader_sentiment text_blob_sentiment  tools_match  \n",
       "0        positive            positive         True  \n",
       "1        positive            positive         True  \n",
       "3        positive            positive         True  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the VADER and TextBlob sentiments\n",
    "analyzer = SentimentIntensityAnalyzer()\n",
    "q_tweets['vader_sentiment'] = q_tweets.apply(lambda row: get_vader_sentiment(analyzer, row['content']), axis=1)\n",
    "q_tweets['text_blob_sentiment'] = q_tweets.apply(lambda row: get_text_blob_sentiment(row['content']), axis=1)\n",
    "\n",
    "# Mark where the two tools agree\n",
    "q_tweets['tools_match'] = q_tweets.apply(lambda row: get_tools_match(row['vader_sentiment'], row['text_blob_sentiment']), axis=1)\n",
    "\n",
    "# keep only the tweets where the tools agree \n",
    "q_tweets = q_tweets[q_tweets['tools_match']]\n",
    "print(q_tweets.shape)\n",
    "q_tweets.head(3)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(130, 11)\n"
     ]
    },
    {
     "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>_id</th>\n",
       "      <th>id_str</th>\n",
       "      <th>content</th>\n",
       "      <th>user_loc</th>\n",
       "      <th>user_screen_name</th>\n",
       "      <th>retweet_count</th>\n",
       "      <th>fav_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>char_count</th>\n",
       "      <th>sentiment_method</th>\n",
       "      <th>sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ffde71f5e4953000d99dd55</td>\n",
       "      <td>1.348735e+18</td>\n",
       "      <td>Virtual learning has caused a fair share of fr...</td>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>Edficiency</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-11 20:55:39</td>\n",
       "      <td>272</td>\n",
       "      <td>tools</td>\n",
       "      <td>positive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ffde71f5e4953000d99dd58</td>\n",
       "      <td>1.348704e+18</td>\n",
       "      <td>Preparing for high school is an exciting time!...</td>\n",
       "      <td>St. Augustine, Florida</td>\n",
       "      <td>SJCSD</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-11 18:51:41</td>\n",
       "      <td>221</td>\n",
       "      <td>tools</td>\n",
       "      <td>positive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5ffde71f5e4953000d99dd5d</td>\n",
       "      <td>1.348672e+18</td>\n",
       "      <td>The Nature-based 4K Parent Night for enrollmen...</td>\n",
       "      <td>Newburg, WI</td>\n",
       "      <td>RiveredgeNC</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-11 16:42:40</td>\n",
       "      <td>276</td>\n",
       "      <td>tools</td>\n",
       "      <td>positive</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id        id_str  \\\n",
       "0  5ffde71f5e4953000d99dd55  1.348735e+18   \n",
       "1  5ffde71f5e4953000d99dd58  1.348704e+18   \n",
       "3  5ffde71f5e4953000d99dd5d  1.348672e+18   \n",
       "\n",
       "                                             content                user_loc  \\\n",
       "0  Virtual learning has caused a fair share of fr...               Wisconsin   \n",
       "1  Preparing for high school is an exciting time!...  St. Augustine, Florida   \n",
       "3  The Nature-based 4K Parent Night for enrollmen...             Newburg, WI   \n",
       "\n",
       "  user_screen_name  retweet_count  fav_count           created_at  char_count  \\\n",
       "0       Edficiency              0          0  2021-01-11 20:55:39         272   \n",
       "1            SJCSD              0          0  2021-01-11 18:51:41         221   \n",
       "3      RiveredgeNC              0          0  2021-01-11 16:42:40         276   \n",
       "\n",
       "  sentiment_method sentiment  \n",
       "0            tools  positive  \n",
       "1            tools  positive  \n",
       "3            tools  positive  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now create cols for sentiment and sentiment method\n",
    "q_tweets['sentiment_method'] = 'tools'\n",
    "q_tweets['sentiment'] = q_tweets.apply(lambda row: row['vader_sentiment'], axis=1)\n",
    "\n",
    "# delete the now un-needed columns\n",
    "q_tweets.drop('vader_sentiment', axis=1, inplace=True)\n",
    "q_tweets.drop('text_blob_sentiment', axis=1, inplace=True)\n",
    "q_tweets.drop('tools_match', axis=1, inplace=True)\n",
    "print(q_tweets.shape)\n",
    "q_tweets.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save to file \n",
    "q_tweets.to_csv('../data/jan_2021_queried_tweets_TOOL_labeled.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now prepare the Tweets that I collected via Streaming over several days in January\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>_id</th>\n",
       "      <th>id_str</th>\n",
       "      <th>content</th>\n",
       "      <th>user_loc</th>\n",
       "      <th>user_screen_name</th>\n",
       "      <th>retweet_count</th>\n",
       "      <th>fav_count</th>\n",
       "      <th>created_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ffde42364e25f9f26125929</td>\n",
       "      <td>1.349054e+18</td>\n",
       "      <td>Open Forum!\\n\\nVirtual Parent Hangout - Januar...</td>\n",
       "      <td>Indianapolis, IN</td>\n",
       "      <td>ISDHoosiers</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 18:02:06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ffde4c064e25f9f2612592a</td>\n",
       "      <td>1.349055e+18</td>\n",
       "      <td>@besf0rt Never forget in 2000 profiling a Japa...</td>\n",
       "      <td>Florida hellmouth</td>\n",
       "      <td>ImperialeNancy</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 18:04:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ffde54d64e25f9f2612592b</td>\n",
       "      <td>1.349055e+18</td>\n",
       "      <td>“This year, the “mothers and others” are turni...</td>\n",
       "      <td>Suburban DC, Maryland</td>\n",
       "      <td>gunsensemelissa</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 18:07:04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id        id_str  \\\n",
       "0  5ffde42364e25f9f26125929  1.349054e+18   \n",
       "1  5ffde4c064e25f9f2612592a  1.349055e+18   \n",
       "2  5ffde54d64e25f9f2612592b  1.349055e+18   \n",
       "\n",
       "                                             content               user_loc  \\\n",
       "0  Open Forum!\\n\\nVirtual Parent Hangout - Januar...       Indianapolis, IN   \n",
       "1  @besf0rt Never forget in 2000 profiling a Japa...      Florida hellmouth   \n",
       "2  “This year, the “mothers and others” are turni...  Suburban DC, Maryland   \n",
       "\n",
       "  user_screen_name  retweet_count  fav_count           created_at  \n",
       "0      ISDHoosiers              0          0  2021-01-12 18:02:06  \n",
       "1   ImperialeNancy              0          0  2021-01-12 18:04:43  \n",
       "2  gunsensemelissa              0          0  2021-01-12 18:07:04  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s_tweets =  pd.read_csv('../data/jan_streaming_tweets_NO_label.csv')\n",
    "s_tweets.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 386 entries, 0 to 385\n",
      "Data columns (total 8 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   _id               386 non-null    object \n",
      " 1   id_str            386 non-null    float64\n",
      " 2   content           386 non-null    object \n",
      " 3   user_loc          386 non-null    object \n",
      " 4   user_screen_name  386 non-null    object \n",
      " 5   retweet_count     386 non-null    int64  \n",
      " 6   fav_count         386 non-null    int64  \n",
      " 7   created_at        386 non-null    object \n",
      "dtypes: float64(1), int64(2), object(5)\n",
      "memory usage: 24.2+ KB\n"
     ]
    }
   ],
   "source": [
    "s_tweets.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(347, 9)\n"
     ]
    },
    {
     "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>_id</th>\n",
       "      <th>id_str</th>\n",
       "      <th>content</th>\n",
       "      <th>user_loc</th>\n",
       "      <th>user_screen_name</th>\n",
       "      <th>retweet_count</th>\n",
       "      <th>fav_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>char_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ffde42364e25f9f26125929</td>\n",
       "      <td>1.349054e+18</td>\n",
       "      <td>Open Forum!\\n\\nVirtual Parent Hangout - Januar...</td>\n",
       "      <td>Indianapolis, IN</td>\n",
       "      <td>ISDHoosiers</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 18:02:06</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ffde4c064e25f9f2612592a</td>\n",
       "      <td>1.349055e+18</td>\n",
       "      <td>@besf0rt Never forget in 2000 profiling a Japa...</td>\n",
       "      <td>Florida hellmouth</td>\n",
       "      <td>ImperialeNancy</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 18:04:43</td>\n",
       "      <td>289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ffde54d64e25f9f2612592b</td>\n",
       "      <td>1.349055e+18</td>\n",
       "      <td>“This year, the “mothers and others” are turni...</td>\n",
       "      <td>Suburban DC, Maryland</td>\n",
       "      <td>gunsensemelissa</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 18:07:04</td>\n",
       "      <td>283</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id        id_str  \\\n",
       "0  5ffde42364e25f9f26125929  1.349054e+18   \n",
       "1  5ffde4c064e25f9f2612592a  1.349055e+18   \n",
       "2  5ffde54d64e25f9f2612592b  1.349055e+18   \n",
       "\n",
       "                                             content               user_loc  \\\n",
       "0  Open Forum!\\n\\nVirtual Parent Hangout - Januar...       Indianapolis, IN   \n",
       "1  @besf0rt Never forget in 2000 profiling a Japa...      Florida hellmouth   \n",
       "2  “This year, the “mothers and others” are turni...  Suburban DC, Maryland   \n",
       "\n",
       "  user_screen_name  retweet_count  fav_count           created_at  char_count  \n",
       "0      ISDHoosiers              0          0  2021-01-12 18:02:06         265  \n",
       "1   ImperialeNancy              0          0  2021-01-12 18:04:43         289  \n",
       "2  gunsensemelissa              0          0  2021-01-12 18:07:04         283  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Keep only the tweets 100 chars and over\n",
    "s_tweets['char_count'] = s_tweets.apply(lambda row: len(row['content']), axis=1)\n",
    "s_tweets = s_tweets[s_tweets['char_count'] >= 100]\n",
    "print(s_tweets.shape)\n",
    "s_tweets.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(193, 13)\n"
     ]
    },
    {
     "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>_id</th>\n",
       "      <th>id_str</th>\n",
       "      <th>content</th>\n",
       "      <th>user_loc</th>\n",
       "      <th>user_screen_name</th>\n",
       "      <th>retweet_count</th>\n",
       "      <th>fav_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>char_count</th>\n",
       "      <th>sentiment_method</th>\n",
       "      <th>vader_sentiment</th>\n",
       "      <th>text_blob_sentiment</th>\n",
       "      <th>tools_match</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5ffde60d64e25f9f2612592c</td>\n",
       "      <td>1.349056e+18</td>\n",
       "      <td>If a student is missing class a bunch, their g...</td>\n",
       "      <td>Provo, UT</td>\n",
       "      <td>avatargrace</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 18:10:16</td>\n",
       "      <td>275</td>\n",
       "      <td>tools</td>\n",
       "      <td>negative</td>\n",
       "      <td>negative</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5ffdf9c180d443513a191637</td>\n",
       "      <td>1.349077e+18</td>\n",
       "      <td>@JamesTodaroMD @ConservMama17 I went to proms,...</td>\n",
       "      <td>Mountains of California</td>\n",
       "      <td>AudreyJeanne</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 19:34:19</td>\n",
       "      <td>308</td>\n",
       "      <td>tools</td>\n",
       "      <td>positive</td>\n",
       "      <td>positive</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5ffdfcbbed845d2d2c79e966</td>\n",
       "      <td>1.349080e+18</td>\n",
       "      <td>Last weeks blog by @RachelJTeaches provides an...</td>\n",
       "      <td>Arlington, VA</td>\n",
       "      <td>intellispark</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-01-12 19:47:01</td>\n",
       "      <td>200</td>\n",
       "      <td>tools</td>\n",
       "      <td>positive</td>\n",
       "      <td>positive</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id        id_str  \\\n",
       "3  5ffde60d64e25f9f2612592c  1.349056e+18   \n",
       "8  5ffdf9c180d443513a191637  1.349077e+18   \n",
       "9  5ffdfcbbed845d2d2c79e966  1.349080e+18   \n",
       "\n",
       "                                             content                 user_loc  \\\n",
       "3  If a student is missing class a bunch, their g...                Provo, UT   \n",
       "8  @JamesTodaroMD @ConservMama17 I went to proms,...  Mountains of California   \n",
       "9  Last weeks blog by @RachelJTeaches provides an...            Arlington, VA   \n",
       "\n",
       "  user_screen_name  retweet_count  fav_count           created_at  char_count  \\\n",
       "3      avatargrace              0          0  2021-01-12 18:10:16         275   \n",
       "8     AudreyJeanne              0          0  2021-01-12 19:34:19         308   \n",
       "9     intellispark              0          0  2021-01-12 19:47:01         200   \n",
       "\n",
       "  sentiment_method vader_sentiment text_blob_sentiment  tools_match  \n",
       "3            tools        negative            negative         True  \n",
       "8            tools        positive            positive         True  \n",
       "9            tools        positive            positive         True  "
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the VADER and TextBlob sentiments\n",
    "analyzer = SentimentIntensityAnalyzer()\n",
    "s_tweets['vader_sentiment'] = s_tweets.apply(lambda row: get_vader_sentiment(analyzer, row['content']), axis=1)\n",
    "s_tweets['text_blob_sentiment'] = s_tweets.apply(lambda row: get_text_blob_sentiment(row['content']), axis=1)\n",
    "\n",
    "# Mark where the two tools agree\n",
    "s_tweets['tools_match'] = s_tweets.apply(lambda row: get_tools_match(row['vader_sentiment'], row['text_blob_sentiment']), axis=1)\n",
    "\n",
    "# keep only the tweets where the tools agree \n",
    "s_tweets = s_tweets[s_tweets['tools_match']]\n",
    "print(s_tweets.shape)\n",
    "s_tweets.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(193, 11)\n"
     ]
    }
   ],
   "source": [
    "# Now create cols for sentiment and sentiment method\n",
    "s_tweets['sentiment_method'] = 'tools'\n",
    "s_tweets['sentiment'] = s_tweets.apply(lambda row: row['vader_sentiment'], axis=1)\n",
    "s_tweets.head(3)\n",
    "\n",
    "# delete the now un-needed columns\n",
    "s_tweets.drop('vader_sentiment', axis=1, inplace=True)\n",
    "s_tweets.drop('text_blob_sentiment', axis=1, inplace=True)\n",
    "s_tweets.drop('tools_match', axis=1, inplace=True)\n",
    "print(s_tweets.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save to file \n",
    "s_tweets.to_csv('../data/jan_2021_streaming_tweets_TOOL_labeled.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now create a single file with both external tweets and the tweets collected in January"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 38392 entries, 0 to 38391\n",
      "Data columns (total 13 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   Unnamed: 0        38044 non-null  float64\n",
      " 1   Unnamed: 0.1      38044 non-null  float64\n",
      " 2   content           38392 non-null  object \n",
      " 3   user_loc          38392 non-null  object \n",
      " 4   user_screen_name  38392 non-null  object \n",
      " 5   retweet_count     38392 non-null  int64  \n",
      " 6   fav_count         38392 non-null  int64  \n",
      " 7   created_at        38392 non-null  object \n",
      " 8   char_count        38392 non-null  int64  \n",
      " 9   sentiment_method  38392 non-null  object \n",
      " 10  sentiment         38392 non-null  object \n",
      " 11  _id               348 non-null    object \n",
      " 12  id_str            348 non-null    float64\n",
      "dtypes: float64(3), int64(3), object(7)\n",
      "memory usage: 3.8+ MB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(38392, 13)"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "all_tweets = us_only_data.append(human_labeled_q_tweets, ignore_index=True)\n",
    "all_tweets.append(q_tweets, ignore_index=True)\n",
    "all_tweets.append(s_tweets, ignore_index=True)\n",
    "\n",
    "all_tweets.info()\n",
    "all_tweets.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>Unnamed: 0</th>\n",
       "      <th>Unnamed: 0.1</th>\n",
       "      <th>content</th>\n",
       "      <th>user_loc</th>\n",
       "      <th>user_screen_name</th>\n",
       "      <th>retweet_count</th>\n",
       "      <th>fav_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>char_count</th>\n",
       "      <th>sentiment_method</th>\n",
       "      <th>sentiment</th>\n",
       "      <th>_id</th>\n",
       "      <th>id_str</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>“Instructional Considerations for the 2020-21 ...</td>\n",
       "      <td>Illinois, USA</td>\n",
       "      <td>Erik_Youngman</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-08-02 00:10:26</td>\n",
       "      <td>276</td>\n",
       "      <td>tools</td>\n",
       "      <td>neutral</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>With all the uncertainty of what September wil...</td>\n",
       "      <td>Lyndhurst, NJ</td>\n",
       "      <td>Renee_LoBue</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-01 23:57:31</td>\n",
       "      <td>264</td>\n",
       "      <td>tools</td>\n",
       "      <td>positive</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>Check this out on Wakelet - Digital learning a...</td>\n",
       "      <td>Cary, NC</td>\n",
       "      <td>SupriyaVasu</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-01 23:20:38</td>\n",
       "      <td>133</td>\n",
       "      <td>tools</td>\n",
       "      <td>neutral</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>Happy Friendship Day!\\n#rdnums #nagaland #kohi...</td>\n",
       "      <td>Kohima, India</td>\n",
       "      <td>rdnums</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-08-01 23:17:09</td>\n",
       "      <td>264</td>\n",
       "      <td>tools</td>\n",
       "      <td>positive</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>Beat the summer heat with over 400 cool games ...</td>\n",
       "      <td>Providence, RI</td>\n",
       "      <td>ABCyaGames</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-08-01 23:00:00</td>\n",
       "      <td>146</td>\n",
       "      <td>tools</td>\n",
       "      <td>positive</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Unnamed: 0.1  \\\n",
       "0         9.0           9.0   \n",
       "1        10.0          10.0   \n",
       "2        11.0          11.0   \n",
       "3        12.0          12.0   \n",
       "4        13.0          13.0   \n",
       "\n",
       "                                             content        user_loc  \\\n",
       "0  “Instructional Considerations for the 2020-21 ...   Illinois, USA   \n",
       "1  With all the uncertainty of what September wil...   Lyndhurst, NJ   \n",
       "2  Check this out on Wakelet - Digital learning a...        Cary, NC   \n",
       "3  Happy Friendship Day!\\n#rdnums #nagaland #kohi...   Kohima, India   \n",
       "4  Beat the summer heat with over 400 cool games ...  Providence, RI   \n",
       "\n",
       "  user_screen_name  retweet_count  fav_count           created_at  char_count  \\\n",
       "0    Erik_Youngman              0          2  2020-08-02 00:10:26         276   \n",
       "1      Renee_LoBue              0          0  2020-08-01 23:57:31         264   \n",
       "2      SupriyaVasu              0          0  2020-08-01 23:20:38         133   \n",
       "3           rdnums              2          1  2020-08-01 23:17:09         264   \n",
       "4       ABCyaGames              0          2  2020-08-01 23:00:00         146   \n",
       "\n",
       "  sentiment_method sentiment  _id  id_str  \n",
       "0            tools   neutral  NaN     NaN  \n",
       "1            tools  positive  NaN     NaN  \n",
       "2            tools   neutral  NaN     NaN  \n",
       "3            tools  positive  NaN     NaN  \n",
       "4            tools  positive  NaN     NaN  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_tweets.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 38392 entries, 0 to 38391\n",
      "Data columns (total 9 columns):\n",
      " #   Column            Non-Null Count  Dtype \n",
      "---  ------            --------------  ----- \n",
      " 0   content           38392 non-null  object\n",
      " 1   user_loc          38392 non-null  object\n",
      " 2   user_screen_name  38392 non-null  object\n",
      " 3   retweet_count     38392 non-null  int64 \n",
      " 4   fav_count         38392 non-null  int64 \n",
      " 5   created_at        38392 non-null  object\n",
      " 6   char_count        38392 non-null  int64 \n",
      " 7   sentiment_method  38392 non-null  object\n",
      " 8   sentiment         38392 non-null  object\n",
      "dtypes: int64(3), object(6)\n",
      "memory usage: 2.6+ MB\n"
     ]
    }
   ],
   "source": [
    "# drop the columns we don't need\n",
    "all_tweets.drop('Unnamed: 0', axis=1, inplace=True)\n",
    "all_tweets.drop('Unnamed: 0.1', axis=1, inplace=True)\n",
    "all_tweets.drop('_id', axis=1, inplace=True)\n",
    "all_tweets.drop('id_str', axis=1, inplace=True)\n",
    "\n",
    "all_tweets.info()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "content             0\n",
       "user_loc            0\n",
       "user_screen_name    0\n",
       "retweet_count       0\n",
       "fav_count           0\n",
       "created_at          0\n",
       "char_count          0\n",
       "sentiment_method    0\n",
       "sentiment           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check for any nulls\n",
    "all_tweets.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "38392"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# no nulls...now check for duplicate contents\n",
    "len(all_tweets['content'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save all tweets to file \n",
    "all_tweets.to_csv('../data/all_tweets_combined.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "flatiron",
   "language": "python",
   "name": "flatiron"
  },
  "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}