preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c4d7a6ea",
   "metadata": {},
   "source": [
    "# Bitcoin price prediction based on *sentiment analysis* of related tweets\n",
    "-----------"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35fbde58",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "This project employs sentiment analysis on tweets related to cryptocurrencies in order to create a model that can predict how valuations might change based on public sentiment. We decided to do this to see whether there is a correlation between the behaviour of cryptocurrencies and people's sentiments about them since they are based on intangible services and as such could be considered volatile based on the availability of these services.\n",
    "\n",
    "We have taken a sentiment-tagged dataset of Bitcoin ($BTC) related tweets, and used that to train a machine learning model in order to sentiment-tag a curated dataset which we have pulled from the Twitter API. We then compare the change in sentiment per day to the change in price of BTC per day, in order to analyse the correlation. Finally, we attempt to determine whether the sentiment influences the price or vice versa.\n",
    "\n",
    "## Data\n",
    "First, we selected the datasets we would use for the analysis as well as to train and test our models later on. We have chosen the following datasets for our analysis:\n",
    "\n",
    "- Bitcoin Sentiment Twitter dataset\n",
    "\n",
    "> We use data from [this](https://www.kaggle.com/code/alexandrayuliu/bitcoin-tweets-sentiment-analysis/data) sentiment tagged dataset to train and test a model for tagging our curated data\n",
    "\n",
    "- Twitter API web-crawled dataset\n",
    "\n",
    "> As we want to analyse the most dramatic and interesting points in time realted to BTC, we are using a custom dataset which we have gathered using [Twitter's Developer API](https://developer.twitter.com/en/docs/twitter-api)\n",
    "\n",
    "## Research Question \n",
    "Is the sentiment of online discourse about Bitcoin representative of the change of its market price? \n",
    "\n",
    "If there is a correlation, which one affects the other more?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3213531",
   "metadata": {},
   "source": [
    "# Preprocessing of training data\n",
    "-----------\n",
    "Only to be run once, with results saved to CSV file for later access"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "07ad5916",
   "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>date</th>\n",
       "      <th>tweets</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-02-05 10:52:04</td>\n",
       "      <td>AT_USER AT_USER AT_USER right here w/ AT_USER ...</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-02-05 10:52:04</td>\n",
       "      <td>AT_USER AT_USER please donate bitcoin19 donate...</td>\n",
       "      <td>0.6597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-02-05 10:52:06</td>\n",
       "      <td>$sos market cap is 308 million. if they’re min...</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-02-05 10:52:07</td>\n",
       "      <td>bitcoin btc current price (gbp): £34,880 like ...</td>\n",
       "      <td>0.3612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-02-05 10:52:26</td>\n",
       "      <td>AT_USER right here w/ AT_USER URL referral cod...</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date                                             tweets  \\\n",
       "0  2021-02-05 10:52:04  AT_USER AT_USER AT_USER right here w/ AT_USER ...   \n",
       "1  2021-02-05 10:52:04  AT_USER AT_USER please donate bitcoin19 donate...   \n",
       "2  2021-02-05 10:52:06  $sos market cap is 308 million. if they’re min...   \n",
       "3  2021-02-05 10:52:07  bitcoin btc current price (gbp): £34,880 like ...   \n",
       "4  2021-02-05 10:52:26  AT_USER right here w/ AT_USER URL referral cod...   \n",
       "\n",
       "    score  \n",
       "0  0.0000  \n",
       "1  0.6597  \n",
       "2  0.0000  \n",
       "3  0.3612  \n",
       "4  0.0000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "(1519555, 3)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "sent_tweets = pd.read_csv(\"data/sent_tweets.csv\").drop(columns=['user_location', 'user_description', \n",
    "                                                           'user_followers', 'user_friends',])\n",
    "display(sent_tweets.head(5))\n",
    "sent_tweets.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9b8e2d3",
   "metadata": {},
   "source": [
    "## Filtering and formatting\n",
    "\n",
    "We chose to not remove the stopwords as Saif et al ( LREC 2014) suggests that it might degrade the quality of classification.\n",
    "\n",
    "[On Stopwords, Filtering and Data Sparsity for Sentiment Analysis of Twitter](http://www.lrec-conf.org/proceedings/lrec2014/pdf/292_Paper.pdf) (Saif et al., LREC 2014)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "63e21da5",
   "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>date</th>\n",
       "      <th>tweets</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-02-05 10:52:04</td>\n",
       "      <td>right here w   referral code</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-02-05 10:52:04</td>\n",
       "      <td>please donate bitcoin donate challenge from ...</td>\n",
       "      <td>0.6597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-02-05 10:52:06</td>\n",
       "      <td>sos market cap is  million. if theyre mining ....</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-02-05 10:52:07</td>\n",
       "      <td>bitcoin btc current price gbp  like my updates...</td>\n",
       "      <td>0.3612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-02-05 10:52:26</td>\n",
       "      <td>right here w   referral code  helpmehelpyou</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date                                             tweets  \\\n",
       "0  2021-02-05 10:52:04                     right here w   referral code     \n",
       "1  2021-02-05 10:52:04    please donate bitcoin donate challenge from ...   \n",
       "2  2021-02-05 10:52:06  sos market cap is  million. if theyre mining ....   \n",
       "3  2021-02-05 10:52:07  bitcoin btc current price gbp  like my updates...   \n",
       "4  2021-02-05 10:52:26       right here w   referral code  helpmehelpyou    \n",
       "\n",
       "    score  \n",
       "0  0.0000  \n",
       "1  0.6597  \n",
       "2  0.0000  \n",
       "3  0.3612  \n",
       "4  0.0000  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re \n",
    "\n",
    "# Provide case insensitive data\n",
    "tweets = sent_tweets[\"tweets\"].str.lower().astype(str)\n",
    "S\n",
    "# Take out links with or without www\n",
    "tweets = tweets.apply(lambda x: re.sub(r'https?:\\/\\/\\S+', '', x))\n",
    "tweets = tweets.apply(lambda x: re.sub(r\"www\\.[a-z]?\\.?(com)+|[a-z]+\\.(com)\", '', x))\n",
    "\n",
    "# Take out possible HTML character references \n",
    "tweets = tweets.apply(lambda x: re.sub(r'&[a-z]+;', '', x))\n",
    "\n",
    "# Take out nonletter characters except for spaces and sentence delimitators\n",
    "tweets = tweets.apply(lambda x: re.sub(r\"[^a-z\\s.!?]\", '', x))\n",
    "\n",
    "# Sometimes twitter data has links preprocessed into a reference such as {link}\n",
    "tweets = tweets.apply(lambda x: re.sub(r'{link}', '', x))\n",
    "\n",
    "# Noticed the dataset contains at user and url references so we can remove them\n",
    "tweets = tweets.str.replace('url', '')\n",
    "tweets = tweets.str.replace('atuser', '')\n",
    "\n",
    "sent_tweets[\"tweets\"] = tweets\n",
    "\n",
    "sent_tweets.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01f04576",
   "metadata": {},
   "source": [
    "## Tokenisation and POS-tagging\n",
    "\n",
    "Using the regular expression tokeniser from NLTK as it gives us a high level of customisability"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "54d19f51",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "([('bitcoin', 'NN'),\n",
       "  ('btc', 'NN'),\n",
       "  ('current', 'JJ'),\n",
       "  ('price', 'NN'),\n",
       "  ('gbp', 'NN'),\n",
       "  ('like', 'IN'),\n",
       "  ('my', 'PRP$'),\n",
       "  ('updates?', 'NN'),\n",
       "  ('you', 'PRP'),\n",
       "  ('can', 'MD'),\n",
       "  ('tip', 'VB'),\n",
       "  ('me', 'PRP'),\n",
       "  ('at', 'IN'),\n",
       "  ('ldztlqrcxnpnvgfnrbazuqvmrz', 'NN')],\n",
       " 0.3612)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import nltk\n",
    "from nltk.tokenize import RegexpTokenizer\n",
    "\n",
    "tweets = list(zip(sent_tweets[\"tweets\"], sent_tweets[\"score\"]))\n",
    "\n",
    "regextk = RegexpTokenizer('\\s+', gaps=True)\n",
    "\n",
    "tokens = [(regextk.tokenize(tweet), sentiment) for (tweet, sentiment) in tweets if type(tweet) == str]\n",
    "\n",
    "tagged = [(nltk.pos_tag(tweet), sentiment) for tweet, sentiment in tokens]\n",
    "\n",
    "tagged[3]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb5a8c26",
   "metadata": {},
   "source": [
    "## Lemmatisation with Wordnet POS-tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "bda6aff6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(['bitcoin',\n",
       "  'btc',\n",
       "  'current',\n",
       "  'price',\n",
       "  'gbp',\n",
       "  'like',\n",
       "  'my',\n",
       "  'update',\n",
       "  'you',\n",
       "  'can',\n",
       "  'tip',\n",
       "  'me',\n",
       "  'at',\n",
       "  'ldztlqrcxnpnvgfnrbazuqvmrz'],\n",
       " 0.3612)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import string\n",
    "from nltk.corpus import wordnet as wn\n",
    "from nltk.stem import WordNetLemmatizer\n",
    "\n",
    "def wn_pos(tag):\n",
    "    \"converts treebank tags into wordbank tags for lemmatization\"\n",
    "    if tag.startswith('J'):\n",
    "        return wn.ADJ\n",
    "    if tag.startswith('V'):\n",
    "        return wn.VERB\n",
    "    if tag.startswith('N'):\n",
    "        return wn.NOUN\n",
    "    if tag.startswith('R'):\n",
    "        return wn.ADV\n",
    "    return None\n",
    "\n",
    "lem_tweets = []\n",
    "lem = WordNetLemmatizer()\n",
    "\n",
    "for tweet in tagged:\n",
    "    lemmas = []\n",
    "    \n",
    "    for word, tag in tweet[0]:\n",
    "        wn_tag = wn_pos(tag)\n",
    "        \n",
    "        if word[-1] in string.punctuation:\n",
    "                word = word[:-1]\n",
    "\n",
    "        if wn_pos(tag) is not None:\n",
    "            lemmas.append(lem.lemmatize(word, wn_tag))\n",
    "        else:\n",
    "            lemmas.append(lem.lemmatize(word))\n",
    "                \n",
    "    lem_tweets.append((lemmas, tweet[1]))\n",
    "\n",
    "lem_tweets[3]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f489530a",
   "metadata": {},
   "source": [
    "## Saving to CSV file for later use"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "639a43a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(lem_tweets).to_csv(\"lem_tweets.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a40b113",
   "metadata": {},
   "source": [
    "End of training data preprocessing\n",
    "\n",
    "\n",
    "# Training the model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "088f9e32",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "def clean_dataset(df):\n",
    "    assert isinstance(df, pd.DataFrame), \"df needs to be a pd.DataFrame\"\n",
    "    df.dropna(inplace=True)\n",
    "    indices_to_keep = ~df.isin([np.nan, np.inf, -np.inf]).any(1)\n",
    "    return df[indices_to_keep]\n",
    "\n",
    "lem_tweets = pd.read_csv(\"data/lem_tweets.csv\").drop(columns=\"Unnamed: 0\").rename(columns={\"0\":\"Tweet\", \"1\":\"Sentiment\"})\n",
    "\n",
    "# joining data back to strings\n",
    "new = []\n",
    "for tweet in lem_tweets[\"Tweet\"]:\n",
    "    new.append((tweet[1:-1].replace(\",\", \"\").replace(\"'\", \"\")))\n",
    "    \n",
    "data = pd.DataFrame(zip(new, lem_tweets[\"Sentiment\"]), columns=[\"Tweet\", \"Sentiment\"])\n",
    "data = clean_dataset(data)\n",
    "\n",
    "X = data['Tweet'].astype(str)\n",
    "y = data['Sentiment']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60a85109",
   "metadata": {},
   "source": [
    "## Splitting and vectorising\n",
    "\n",
    "TF-IDF (term frequency inverse document frequency) is used as our baseline for word representation.\n",
    "\n",
    "Term frequency TF (number of times a term appears in a document) multiplied by Inverse Document Frequency IDF (log of the number of documents over the document frequency of term). \n",
    "\n",
    "Does not incorporate similarity between different words."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "aefb8a03",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.feature_extraction.text import TfidfTransformer, CountVectorizer\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "count = CountVectorizer()\n",
    "tfidf_transformer = TfidfTransformer()\n",
    "\n",
    "# split data for train and test\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, train_size=0.8)\n",
    "\n",
    "train_counts = count.fit_transform(X_train)\n",
    "train_tfidf = tfidf_transformer.fit_transform(train_counts)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e68d7959",
   "metadata": {},
   "source": [
    "## Applying ML model to train sentiment tagger\n",
    "\n",
    "Using a regression model as we are trying to predict sentiment on a scale of -1 to 1.\n",
    "\n",
    "The *Ridge Regression* model proved to be the best with some testing, as it excels at estimating coefficients of multiple-regression models in scenarios where linearly independent variables are highly correlated.\n",
    "\n",
    "In this model, the loss function is a *linear least squares* and regularization is given by the *l2-norm*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "cab1c437",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.linear_model import SGDRegressor, Ridge\n",
    "\n",
    "ridge = Ridge().fit(train_tfidf, y_train)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "880f0bac",
   "metadata": {},
   "source": [
    "#### Using MSE for accuracy scoring"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "bd77e32b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.04653416756033122"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.metrics import mean_squared_error\n",
    "\n",
    "X_test_V = count.transform(X_test)\n",
    "X_t = tfidf_transformer.transform(X_test_V)\n",
    "y_pred = ridge.predict(X_t)\n",
    "\n",
    "mean_squared_error(y_test, y_pred)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "910c3542",
   "metadata": {},
   "source": [
    "#### Let's try to improve this regressor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "9197d56d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['alpha', 'copy_X', 'fit_intercept', 'max_iter', 'normalize', 'positive', 'random_state', 'solver', 'tol'])"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ridge.get_params().keys()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46c6e452",
   "metadata": {},
   "source": [
    "## Changing the Alpha parameter\n",
    "\n",
    "Alpha is the constant that multiplies the L2 term, controlling regularization strength. \n",
    "\n",
    "Alpha must be a non-negative float."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "cfb7772a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fitting 5 folds for each of 9 candidates, totalling 45 fits\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'alpha': 0.25}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from scipy.stats import uniform\n",
    "from sklearn.model_selection import RandomizedSearchCV\n",
    "\n",
    "alpha = [x for x in [0.1, 0.2, 0.25, 0.3, 0.5, 0.75, 1, 2, 5, 7.5, 10]]\n",
    "\n",
    "params = {\"alpha\": alpha}\n",
    "\n",
    "rscv = RandomizedSearchCV(ridge, params, n_iter=9, cv=5, verbose=2, n_jobs=-1, random_state=42)\n",
    "search = rscv.fit(train_tfidf, y_train)\n",
    "search.best_params_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "fa53e55f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.04650984289811764"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ridge_opt = Ridge(alpha=0.75).fit(train_tfidf, y_train)\n",
    "opt_pred = ridge_opt.predict(X_t)\n",
    "\n",
    "mean_squared_error(y_test, opt_pred)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee9b1f44",
   "metadata": {},
   "source": [
    "Minor improvement (~ 0.002)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f0262b1",
   "metadata": {},
   "source": [
    "# Applying model to API data\n",
    "\n",
    "Reading in and formatting the web-scraped tweets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "014bcec5",
   "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>Tweet</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>current stats of delegatedonthate block find p...</td>\n",
       "      <td>2022-05-19 23:59:59+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bbcworld for all those who be new to this work...</td>\n",
       "      <td>2022-05-19 23:59:58+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>smilingpunks floor price  no gas fee polygon b...</td>\n",
       "      <td>2022-05-19 23:59:56+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>i be claim my free lightning sat from bitcoine...</td>\n",
       "      <td>2022-05-19 23:59:56+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>washingtonpost for all those who be new to thi...</td>\n",
       "      <td>2022-05-19 23:59:54+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               Tweet  \\\n",
       "0  current stats of delegatedonthate block find p...   \n",
       "1  bbcworld for all those who be new to this work...   \n",
       "2  smilingpunks floor price  no gas fee polygon b...   \n",
       "3  i be claim my free lightning sat from bitcoine...   \n",
       "4  washingtonpost for all those who be new to thi...   \n",
       "\n",
       "                        Date  \n",
       "0  2022-05-19 23:59:59+00:00  \n",
       "1  2022-05-19 23:59:58+00:00  \n",
       "2  2022-05-19 23:59:56+00:00  \n",
       "3  2022-05-19 23:59:56+00:00  \n",
       "4  2022-05-19 23:59:54+00:00  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "(500000, 2)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv(\"data/Btc_tweets_17_23.csv\").drop(columns=[\"Unnamed: 0\"])\n",
    "\n",
    "updated = []\n",
    "for tweet in data[\"tweet\"]:\n",
    "    updated.append((tweet[1:-1].replace(\",\", \"\").replace(\"'\", \"\")))\n",
    "    \n",
    "api = pd.DataFrame(zip(updated, data[\"date\"]), columns=[\"Tweet\", \"Date\"])\n",
    "\n",
    "display(api.head(5))\n",
    "api.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbd21e36",
   "metadata": {},
   "source": [
    "Creating a new dataframe to store our tweets and their new sentiment tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "9e246cfa",
   "metadata": {
    "scrolled": false
   },
   "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>Tweet</th>\n",
       "      <th>Date</th>\n",
       "      <th>Sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>41012</th>\n",
       "      <td>a legal case involve the hack of crypto platfo...</td>\n",
       "      <td>2022-05-19 13:19:09+00:00</td>\n",
       "      <td>-0.021642</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137662</th>\n",
       "      <td>you know that all confidence be lose when you ...</td>\n",
       "      <td>2022-05-18 14:29:03+00:00</td>\n",
       "      <td>0.056309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308382</th>\n",
       "      <td>brucelaing backbitcoin rbw mariuscrypt several...</td>\n",
       "      <td>2022-05-21 20:55:47+00:00</td>\n",
       "      <td>-0.384532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>432656</th>\n",
       "      <td>the european central bank will decide to raise...</td>\n",
       "      <td>2022-05-23 11:40:35+00:00</td>\n",
       "      <td>0.189848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56843</th>\n",
       "      <td>royalkeyafrica turkish lira be a brilliant exa...</td>\n",
       "      <td>2022-05-19 09:16:19+00:00</td>\n",
       "      <td>0.264485</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    Tweet  \\\n",
       "41012   a legal case involve the hack of crypto platfo...   \n",
       "137662  you know that all confidence be lose when you ...   \n",
       "308382  brucelaing backbitcoin rbw mariuscrypt several...   \n",
       "432656  the european central bank will decide to raise...   \n",
       "56843   royalkeyafrica turkish lira be a brilliant exa...   \n",
       "\n",
       "                             Date  Sentiment  \n",
       "41012   2022-05-19 13:19:09+00:00  -0.021642  \n",
       "137662  2022-05-18 14:29:03+00:00   0.056309  \n",
       "308382  2022-05-21 20:55:47+00:00  -0.384532  \n",
       "432656  2022-05-23 11:40:35+00:00   0.189848  \n",
       "56843   2022-05-19 09:16:19+00:00   0.264485  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "api_counts = count.transform(api[\"Tweet\"])\n",
    "api_tfidf = tfidf_transformer.transform(api_counts)\n",
    "\n",
    "r_pred = ridge_opt.predict(api_tfidf)\n",
    "\n",
    "api_tagged = pd.DataFrame(zip(api[\"Tweet\"], api[\"Date\"], r_pred), columns=[\"Tweet\", \"Date\", \"Sentiment\"])\n",
    "api_tagged.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3574d7a4",
   "metadata": {},
   "source": [
    "## Format dates and send to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "d23e36c6",
   "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>Tweet</th>\n",
       "      <th>Date</th>\n",
       "      <th>Sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>current stats of delegatedonthate block find p...</td>\n",
       "      <td>2022-05-19</td>\n",
       "      <td>-0.079393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bbcworld for all those who be new to this work...</td>\n",
       "      <td>2022-05-19</td>\n",
       "      <td>0.192822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>smilingpunks floor price  no gas fee polygon b...</td>\n",
       "      <td>2022-05-19</td>\n",
       "      <td>0.446237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>i be claim my free lightning sat from bitcoine...</td>\n",
       "      <td>2022-05-19</td>\n",
       "      <td>0.333951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>washingtonpost for all those who be new to thi...</td>\n",
       "      <td>2022-05-19</td>\n",
       "      <td>0.192822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499995</th>\n",
       "      <td>natashacryptous luna lunaterra lunaburn will h...</td>\n",
       "      <td>2022-05-22</td>\n",
       "      <td>0.215744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499996</th>\n",
       "      <td>cryptocurrencies bitcoin litecoin and ethereum...</td>\n",
       "      <td>2022-05-22</td>\n",
       "      <td>0.209185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499997</th>\n",
       "      <td>cryptocurrencies bitcoin litecoin and ethereum...</td>\n",
       "      <td>2022-05-22</td>\n",
       "      <td>0.209185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499998</th>\n",
       "      <td>this be a big opportunity.made by a very profe...</td>\n",
       "      <td>2022-05-22</td>\n",
       "      <td>0.799702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499999</th>\n",
       "      <td>hopium hope and optimism alux meme nft collect...</td>\n",
       "      <td>2022-05-22</td>\n",
       "      <td>0.230545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>500000 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    Tweet        Date  \\\n",
       "0       current stats of delegatedonthate block find p...  2022-05-19   \n",
       "1       bbcworld for all those who be new to this work...  2022-05-19   \n",
       "2       smilingpunks floor price  no gas fee polygon b...  2022-05-19   \n",
       "3       i be claim my free lightning sat from bitcoine...  2022-05-19   \n",
       "4       washingtonpost for all those who be new to thi...  2022-05-19   \n",
       "...                                                   ...         ...   \n",
       "499995  natashacryptous luna lunaterra lunaburn will h...  2022-05-22   \n",
       "499996  cryptocurrencies bitcoin litecoin and ethereum...  2022-05-22   \n",
       "499997  cryptocurrencies bitcoin litecoin and ethereum...  2022-05-22   \n",
       "499998  this be a big opportunity.made by a very profe...  2022-05-22   \n",
       "499999  hopium hope and optimism alux meme nft collect...  2022-05-22   \n",
       "\n",
       "        Sentiment  \n",
       "0       -0.079393  \n",
       "1        0.192822  \n",
       "2        0.446237  \n",
       "3        0.333951  \n",
       "4        0.192822  \n",
       "...           ...  \n",
       "499995   0.215744  \n",
       "499996   0.209185  \n",
       "499997   0.209185  \n",
       "499998   0.799702  \n",
       "499999   0.230545  \n",
       "\n",
       "[500000 rows x 3 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import datetime as dt\n",
    "\n",
    "api_tagged[\"Date\"] = [d.date() for d in pd.to_datetime(api_tagged[\"Date\"])]\n",
    "api_tagged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "6034bb0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "api_tagged.to_csv(\"data/api_tagged.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3104c159",
   "metadata": {},
   "source": [
    "# Big dataset tagging"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ae526014",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "btc_tweets = pd.read_csv(\"data/BIG_btc_tweets.csv\", low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "af87a144",
   "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>date</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-02-10 23:59:04</td>\n",
       "      <td>Blue Ridge Bank shares halted by NYSE after #b...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-02-10 23:58:48</td>\n",
       "      <td>😎 Today, that's this #Thursday, we will do a \"...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-02-10 23:54:48</td>\n",
       "      <td>Guys evening, I have read this article about B...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-02-10 23:54:33</td>\n",
       "      <td>$BTC A big chance in a billion! Price: \\487264...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-02-10 23:54:06</td>\n",
       "      <td>This network is secured by 9 508 nodes as of t...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date                                               text\n",
       "0  2021-02-10 23:59:04  Blue Ridge Bank shares halted by NYSE after #b...\n",
       "1  2021-02-10 23:58:48  😎 Today, that's this #Thursday, we will do a \"...\n",
       "2  2021-02-10 23:54:48  Guys evening, I have read this article about B...\n",
       "3  2021-02-10 23:54:33  $BTC A big chance in a billion! Price: \\487264...\n",
       "4  2021-02-10 23:54:06  This network is secured by 9 508 nodes as of t..."
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "btc_tweets = btc_tweets[['date', 'text']]\n",
    "btc_tweets.head(5) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2a96b297",
   "metadata": {},
   "outputs": [],
   "source": [
    "sorted = btc_tweets.sort_values('date').dropna().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3548383c",
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced = sorted.iloc[1200000:1999000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3c7df288",
   "metadata": {},
   "outputs": [],
   "source": [
    "dates = [d.date() for d in pd.to_datetime(reduced[\"date\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "d5f1a602",
   "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>Date</th>\n",
       "      <th>Tweet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1200000</th>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>bitcointrustbct i hope its a great project.  a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1200001</th>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>jobcashofficial good project\\n\\nairdropinside\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1200002</th>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>jobcashofficial nice project is implemented ve...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1200003</th>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>bitcoin atms illinoisfunded coinflip new compl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1200004</th>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>what was the third part of the holy trinity? a...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Date                                              Tweet\n",
       "1200000  2021-08-26  bitcointrustbct i hope its a great project.  a...\n",
       "1200001  2021-08-26  jobcashofficial good project\\n\\nairdropinside\\...\n",
       "1200002  2021-08-26  jobcashofficial nice project is implemented ve...\n",
       "1200003  2021-08-26  bitcoin atms illinoisfunded coinflip new compl...\n",
       "1200004  2021-08-26  what was the third part of the holy trinity? a..."
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re \n",
    "\n",
    "# Provide case insensitive data\n",
    "text = reduced[\"text\"].str.lower().astype(str)\n",
    "\n",
    "# Take out links with or without www\n",
    "text = text.apply(lambda x: re.sub(r'https?:\\/\\/\\S+', '', x))\n",
    "text.apply(lambda x: re.sub(r\"www\\.[a-z]?\\.?(com)+|[a-z]+\\.(com)\", '', x))\n",
    "\n",
    "# Take out possible HTML character references \n",
    "text = text.apply(lambda x: re.sub(r'&[a-z]+;', '', x))\n",
    "\n",
    "# Take out nonletter characters except for spaces and sentence delimitators\n",
    "text = text.apply(lambda x: re.sub(r\"[^a-z\\s.!?]\", '', x))\n",
    "\n",
    "# Sometimes twitter data has links preprocessed into a reference such as {link}\n",
    "text = text.apply(lambda x: re.sub(r'{link}', '', x))\n",
    "\n",
    "# Noticed the dataset contains at user and url references so we can remove them\n",
    "text = text.str.replace('url', '')\n",
    "text = text.str.replace('atuser', '')\n",
    "\n",
    "filtered = pd.DataFrame({\"Date\": dates, \"Tweet\": text})\n",
    "\n",
    "filtered.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "9175db73",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([datetime.date(2021, 8, 26), datetime.date(2021, 9, 10),\n",
       "       datetime.date(2021, 10, 18), datetime.date(2021, 10, 19),\n",
       "       datetime.date(2021, 10, 20), datetime.date(2021, 10, 21),\n",
       "       datetime.date(2021, 10, 22), datetime.date(2021, 10, 23),\n",
       "       datetime.date(2021, 10, 27), datetime.date(2021, 10, 28),\n",
       "       datetime.date(2021, 10, 29), datetime.date(2021, 11, 4),\n",
       "       datetime.date(2021, 11, 5), datetime.date(2021, 11, 6),\n",
       "       datetime.date(2021, 11, 11), datetime.date(2021, 11, 12),\n",
       "       datetime.date(2021, 11, 18), datetime.date(2021, 11, 19),\n",
       "       datetime.date(2021, 11, 24), datetime.date(2021, 11, 25),\n",
       "       datetime.date(2021, 11, 26), datetime.date(2021, 12, 11),\n",
       "       datetime.date(2021, 12, 17), datetime.date(2021, 12, 29),\n",
       "       datetime.date(2021, 12, 30)], dtype=object)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered[\"Date\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "91b587b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import nltk\n",
    "from nltk.tokenize import RegexpTokenizer\n",
    "\n",
    "regextk = RegexpTokenizer('\\s+', gaps=True)\n",
    "\n",
    "reduced_tagged = [nltk.pos_tag(regextk.tokenize(tweet)) for tweet in filtered[\"Tweet\"] if type(tweet) == str]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2dd7dfcc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import string\n",
    "from nltk.stem import WordNetLemmatizer\n",
    "from nltk.corpus import wordnet as wn\n",
    "\n",
    "def wn_pos(tag):\n",
    "    if tag.startswith('J'):\n",
    "        return wn.ADJ\n",
    "    if tag.startswith('V'):\n",
    "        return wn.VERB\n",
    "    if tag.startswith('N'):\n",
    "        return wn.NOUN\n",
    "    if tag.startswith('R'):\n",
    "        return wn.ADV\n",
    "    return None\n",
    "\n",
    "lem_tweets = []\n",
    "lem = WordNetLemmatizer()\n",
    "\n",
    "for tweet in reduced_tagged:\n",
    "    lemmas = []\n",
    "    \n",
    "    for word, tag in tweet:\n",
    "        wn_tag = wn_pos(tag)\n",
    "\n",
    "        if wn_pos(tag) is not None:\n",
    "            lemmas.append(lem.lemmatize(word, wn_tag))\n",
    "        else:\n",
    "            lemmas.append(lem.lemmatize(word))\n",
    "                \n",
    "    lem_tweets.append(lemmas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "4eb1ffe4",
   "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>Date</th>\n",
       "      <th>Tweet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1200000</th>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>bitcointrustbct i hope it a great project. and...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1200001</th>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>jobcashofficial good project airdropinside pan...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1200002</th>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>jobcashofficial nice project be implement very...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1200003</th>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>bitcoin atm illinoisfunded coinflip new compli...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1200004</th>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>what be the third part of the holy trinity? ah...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998995</th>\n",
       "      <td>2021-12-30</td>\n",
       "      <td>georgehahn jerrysaltz ill be buy crypto!!! my ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998996</th>\n",
       "      <td>2021-12-30</td>\n",
       "      <td>the late bitcoin block with transaction be jus...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998997</th>\n",
       "      <td>2021-12-30</td>\n",
       "      <td>saylor wonder what historical event happen in ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998998</th>\n",
       "      <td>2021-12-30</td>\n",
       "      <td>happy new year eve everyone xcad zil btc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998999</th>\n",
       "      <td>2021-12-30</td>\n",
       "      <td>btcusdt short . on binance utc btc cryptocurrency</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>799000 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Date                                              Tweet\n",
       "1200000  2021-08-26  bitcointrustbct i hope it a great project. and...\n",
       "1200001  2021-08-26  jobcashofficial good project airdropinside pan...\n",
       "1200002  2021-08-26  jobcashofficial nice project be implement very...\n",
       "1200003  2021-08-26  bitcoin atm illinoisfunded coinflip new compli...\n",
       "1200004  2021-08-26  what be the third part of the holy trinity? ah...\n",
       "...             ...                                                ...\n",
       "1998995  2021-12-30  georgehahn jerrysaltz ill be buy crypto!!! my ...\n",
       "1998996  2021-12-30  the late bitcoin block with transaction be jus...\n",
       "1998997  2021-12-30  saylor wonder what historical event happen in ...\n",
       "1998998  2021-12-30           happy new year eve everyone xcad zil btc\n",
       "1998999  2021-12-30  btcusdt short . on binance utc btc cryptocurrency\n",
       "\n",
       "[799000 rows x 2 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined = [\" \".join(tweet) for tweet in lem_tweets]\n",
    "\n",
    "big = pd.DataFrame({\"Date\" : reduced[\"date\"], \n",
    "                    \"Tweet\" : joined})\n",
    "big"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "3a961a01",
   "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>Tweet</th>\n",
       "      <th>Date</th>\n",
       "      <th>Sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>153270</th>\n",
       "      <td>a backtest of the old all time high allright l...</td>\n",
       "      <td>2021-10-21</td>\n",
       "      <td>-0.156123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135330</th>\n",
       "      <td>gdet add more s .s...yall know this be go to t...</td>\n",
       "      <td>2021-10-20</td>\n",
       "      <td>-0.071871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295611</th>\n",
       "      <td>crypto price usd bitcoin . ethereum . binance ...</td>\n",
       "      <td>2021-10-27</td>\n",
       "      <td>-0.145125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>510185</th>\n",
       "      <td>you can do cloud mine use this site mining dog...</td>\n",
       "      <td>2021-11-11</td>\n",
       "      <td>0.068456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>444095</th>\n",
       "      <td>thanks cathiedwood and charlieshrem for share ...</td>\n",
       "      <td>2021-11-05</td>\n",
       "      <td>0.685202</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    Tweet        Date  \\\n",
       "153270  a backtest of the old all time high allright l...  2021-10-21   \n",
       "135330  gdet add more s .s...yall know this be go to t...  2021-10-20   \n",
       "295611  crypto price usd bitcoin . ethereum . binance ...  2021-10-27   \n",
       "510185  you can do cloud mine use this site mining dog...  2021-11-11   \n",
       "444095  thanks cathiedwood and charlieshrem for share ...  2021-11-05   \n",
       "\n",
       "        Sentiment  \n",
       "153270  -0.156123  \n",
       "135330  -0.071871  \n",
       "295611  -0.145125  \n",
       "510185   0.068456  \n",
       "444095   0.685202  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "big_counts = count.transform(big[\"Tweet\"])\n",
    "big_tfidf = tfidf_transformer.transform(big_counts)\n",
    "\n",
    "r_pred = ridge_opt.predict(big_tfidf)\n",
    "\n",
    "df = pd.DataFrame(zip(big[\"Tweet\"], big[\"Date\"], r_pred), columns=[\"Tweet\", \"Date\", \"Sentiment\"])\n",
    "df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "4e203c64",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"data/big_tagged.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "6660db73",
   "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>Tweet</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>yberthieves use malware to swipe  bitcoin from...</td>\n",
       "      <td>2021-09-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ur ceo and cofounder raypaxful believe that if...</td>\n",
       "      <td>2021-09-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ennisporter i agree  although loss of hope can...</td>\n",
       "      <td>2021-09-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>hat a fast growing ecosystem the xrp ledger ha...</td>\n",
       "      <td>2021-09-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>inancekeep i have participate  i believe this ...</td>\n",
       "      <td>2021-09-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20155</th>\n",
       "      <td>itcoin week social activity galaxy score  out ...</td>\n",
       "      <td>2021-11-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20156</th>\n",
       "      <td>itcoin face ban in another major country wonde...</td>\n",
       "      <td>2021-11-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20157</th>\n",
       "      <td>ulte bitcoin pult</td>\n",
       "      <td>2021-11-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20158</th>\n",
       "      <td>think this be very strong project thanks for ...</td>\n",
       "      <td>2021-11-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20159</th>\n",
       "      <td>itcoin i value bitcoin because it be the de fa...</td>\n",
       "      <td>2021-11-23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20160 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   Tweet        Date\n",
       "0      yberthieves use malware to swipe  bitcoin from...  2021-09-01\n",
       "1      ur ceo and cofounder raypaxful believe that if...  2021-09-01\n",
       "2      ennisporter i agree  although loss of hope can...  2021-09-01\n",
       "3      hat a fast growing ecosystem the xrp ledger ha...  2021-09-01\n",
       "4      inancekeep i have participate  i believe this ...  2021-09-01\n",
       "...                                                  ...         ...\n",
       "20155  itcoin week social activity galaxy score  out ...  2021-11-23\n",
       "20156  itcoin face ban in another major country wonde...  2021-11-23\n",
       "20157                                  ulte bitcoin pult  2021-11-23\n",
       "20158   think this be very strong project thanks for ...  2021-11-23\n",
       "20159  itcoin i value bitcoin because it be the de fa...  2021-11-23\n",
       "\n",
       "[20160 rows x 2 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "old = pd.read_csv(\"data/sep_dec_tagged.csv\").drop(columns=[\"Unnamed: 0\"])\n",
    "updated = []\n",
    "for tweet in old[\"Tweet\"]:\n",
    "    updated.append((tweet[1:-1].replace(\",\", \"\").replace(\"'\", \"\")))\n",
    "    \n",
    "old = pd.DataFrame(zip(updated, old[\"Date\"]), columns=[\"Tweet\", \"Date\"])\n",
    "\n",
    "old"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "7becdb79",
   "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>Tweet</th>\n",
       "      <th>Date</th>\n",
       "      <th>Sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15098</th>\n",
       "      <td>inance bitcoin holder you be ready roxtoken be...</td>\n",
       "      <td>2021-11-02</td>\n",
       "      <td>0.173462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7212</th>\n",
       "      <td>ey i join a network call surveyj and earn toda...</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>0.567837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4377</th>\n",
       "      <td>irdropinspect i be very happy to participate i...</td>\n",
       "      <td>2021-09-19</td>\n",
       "      <td>0.639991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4743</th>\n",
       "      <td>ajolleratzii your wall street cheat sheet anal...</td>\n",
       "      <td>2021-09-20</td>\n",
       "      <td>-0.373922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17221</th>\n",
       "      <td>sc pitchfinance bitcoin this project be powerf...</td>\n",
       "      <td>2021-11-11</td>\n",
       "      <td>0.307505</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   Tweet        Date  \\\n",
       "15098  inance bitcoin holder you be ready roxtoken be...  2021-11-02   \n",
       "7212   ey i join a network call surveyj and earn toda...  2021-10-01   \n",
       "4377   irdropinspect i be very happy to participate i...  2021-09-19   \n",
       "4743   ajolleratzii your wall street cheat sheet anal...  2021-09-20   \n",
       "17221  sc pitchfinance bitcoin this project be powerf...  2021-11-11   \n",
       "\n",
       "       Sentiment  \n",
       "15098   0.173462  \n",
       "7212    0.567837  \n",
       "4377    0.639991  \n",
       "4743   -0.373922  \n",
       "17221   0.307505  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "old_counts = count.transform(old[\"Tweet\"])\n",
    "old_tfidf = tfidf_transformer.transform(old_counts)\n",
    "\n",
    "r_pred = ridge_opt.predict(old_tfidf)\n",
    "\n",
    "df = pd.DataFrame(zip(old[\"Tweet\"], old[\"Date\"], r_pred), columns=[\"Tweet\", \"Date\", \"Sentiment\"])\n",
    "df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "432996ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"data/api_sep_dec_tagged.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "8779da2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "big = pd.read_csv(\"data/big_tagged.csv\").drop(columns=[\"Unnamed: 0\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "264db18f",
   "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>Tweet</th>\n",
       "      <th>Date</th>\n",
       "      <th>Sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cyberthieves use malware to swipe  bitcoin fro...</td>\n",
       "      <td>2021-09-01</td>\n",
       "      <td>0.211445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>our ceo and cofounder raypaxful believe that i...</td>\n",
       "      <td>2021-09-01</td>\n",
       "      <td>0.222049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>dennisporter i agree  although loss of hope ca...</td>\n",
       "      <td>2021-09-01</td>\n",
       "      <td>0.294836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>what a fast growing ecosystem the xrp ledger h...</td>\n",
       "      <td>2021-09-01</td>\n",
       "      <td>0.171399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>financekeep i have participate  i believe this...</td>\n",
       "      <td>2021-09-01</td>\n",
       "      <td>0.328214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6995</th>\n",
       "      <td>mchooyah riggsbtc we buy bitcoin  we hodl  the...</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>0.213034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6996</th>\n",
       "      <td>bitcoin try to break out out right now</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>-0.192061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6997</th>\n",
       "      <td>brooklyn police station upland block chain her...</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>-0.224463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6998</th>\n",
       "      <td>current bitcoin transaction fee bch next block...</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>-0.307874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6999</th>\n",
       "      <td>bitcoin be top out on the one hour  dont be sc...</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>0.492103</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7000 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  Tweet        Date  Sentiment\n",
       "0     cyberthieves use malware to swipe  bitcoin fro...  2021-09-01   0.211445\n",
       "1     our ceo and cofounder raypaxful believe that i...  2021-09-01   0.222049\n",
       "2     dennisporter i agree  although loss of hope ca...  2021-09-01   0.294836\n",
       "3     what a fast growing ecosystem the xrp ledger h...  2021-09-01   0.171399\n",
       "4     financekeep i have participate  i believe this...  2021-09-01   0.328214\n",
       "...                                                 ...         ...        ...\n",
       "6995  mchooyah riggsbtc we buy bitcoin  we hodl  the...  2021-09-30   0.213034\n",
       "6996          bitcoin try to break out out right now     2021-09-30  -0.192061\n",
       "6997  brooklyn police station upland block chain her...  2021-09-30  -0.224463\n",
       "6998  current bitcoin transaction fee bch next block...  2021-09-30  -0.307874\n",
       "6999  bitcoin be top out on the one hour  dont be sc...  2021-09-30   0.492103\n",
       "\n",
       "[7000 rows x 3 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "old[\"Date\"] = [d.date() for d in pd.to_datetime(old[\"Date\"])]\n",
    "old"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "7b4d45b0",
   "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>Tweet</th>\n",
       "      <th>Date</th>\n",
       "      <th>Sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bitcointrustbct i hope it a great project. and...</td>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>1.100450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>jobcashofficial good project airdropinside pan...</td>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>0.658448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>jobcashofficial nice project be implement very...</td>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>0.796862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bitcoin atm illinoisfunded coinflip new compli...</td>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>0.141093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>what be the third part of the holy trinity? ah...</td>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>0.284733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>805995</th>\n",
       "      <td>mchooyah riggsbtc we buy bitcoin  we hodl  the...</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>0.213034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>805996</th>\n",
       "      <td>bitcoin try to break out out right now</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>-0.192061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>805997</th>\n",
       "      <td>brooklyn police station upland block chain her...</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>-0.224463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>805998</th>\n",
       "      <td>current bitcoin transaction fee bch next block...</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>-0.307874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>805999</th>\n",
       "      <td>bitcoin be top out on the one hour  dont be sc...</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>0.492103</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>806000 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    Tweet        Date  \\\n",
       "0       bitcointrustbct i hope it a great project. and...  2021-08-26   \n",
       "1       jobcashofficial good project airdropinside pan...  2021-08-26   \n",
       "2       jobcashofficial nice project be implement very...  2021-08-26   \n",
       "3       bitcoin atm illinoisfunded coinflip new compli...  2021-08-26   \n",
       "4       what be the third part of the holy trinity? ah...  2021-08-26   \n",
       "...                                                   ...         ...   \n",
       "805995  mchooyah riggsbtc we buy bitcoin  we hodl  the...  2021-09-30   \n",
       "805996          bitcoin try to break out out right now     2021-09-30   \n",
       "805997  brooklyn police station upland block chain her...  2021-09-30   \n",
       "805998  current bitcoin transaction fee bch next block...  2021-09-30   \n",
       "805999  bitcoin be top out on the one hour  dont be sc...  2021-09-30   \n",
       "\n",
       "        Sentiment  \n",
       "0        1.100450  \n",
       "1        0.658448  \n",
       "2        0.796862  \n",
       "3        0.141093  \n",
       "4        0.284733  \n",
       "...           ...  \n",
       "805995   0.213034  \n",
       "805996  -0.192061  \n",
       "805997  -0.224463  \n",
       "805998  -0.307874  \n",
       "805999   0.492103  \n",
       "\n",
       "[806000 rows x 3 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined = pd.concat([big, old], ignore_index=True)\n",
    "joined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "5bd84fe5",
   "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>Tweet</th>\n",
       "      <th>Date</th>\n",
       "      <th>Sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bitcointrustbct i hope it a great project. and...</td>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>1.100450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bitcoin atm illinoisfunded coinflip new compli...</td>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>0.141093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>what be the third part of the holy trinity? ah...</td>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>0.284733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>jobcashofficial very excellent project airdrop...</td>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>0.178776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>supply on exchange wallet d bitcoin exchange c...</td>\n",
       "      <td>2021-08-26</td>\n",
       "      <td>-0.061778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796655</th>\n",
       "      <td>fortunemagazine break banxaofficial nasdaq lis...</td>\n",
       "      <td>2021-12-30</td>\n",
       "      <td>0.280302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796654</th>\n",
       "      <td>btc derivs sheet tweet every min. cme delay mi...</td>\n",
       "      <td>2021-12-30</td>\n",
       "      <td>0.168403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796652</th>\n",
       "      <td>i be hop for a volatile bitcoin btc trading da...</td>\n",
       "      <td>2021-12-30</td>\n",
       "      <td>-0.053625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796662</th>\n",
       "      <td>aave long positionv entry price . target. stop...</td>\n",
       "      <td>2021-12-30</td>\n",
       "      <td>-0.045778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>784973</th>\n",
       "      <td>runway parabolic mania phase and we go to woon...</td>\n",
       "      <td>2021-12-30</td>\n",
       "      <td>0.196290</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>806000 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    Tweet        Date  \\\n",
       "0       bitcointrustbct i hope it a great project. and...  2021-08-26   \n",
       "3       bitcoin atm illinoisfunded coinflip new compli...  2021-08-26   \n",
       "4       what be the third part of the holy trinity? ah...  2021-08-26   \n",
       "5       jobcashofficial very excellent project airdrop...  2021-08-26   \n",
       "6       supply on exchange wallet d bitcoin exchange c...  2021-08-26   \n",
       "...                                                   ...         ...   \n",
       "796655  fortunemagazine break banxaofficial nasdaq lis...  2021-12-30   \n",
       "796654  btc derivs sheet tweet every min. cme delay mi...  2021-12-30   \n",
       "796652  i be hop for a volatile bitcoin btc trading da...  2021-12-30   \n",
       "796662  aave long positionv entry price . target. stop...  2021-12-30   \n",
       "784973  runway parabolic mania phase and we go to woon...  2021-12-30   \n",
       "\n",
       "        Sentiment  \n",
       "0        1.100450  \n",
       "3        0.141093  \n",
       "4        0.284733  \n",
       "5        0.178776  \n",
       "6       -0.061778  \n",
       "...           ...  \n",
       "796655   0.280302  \n",
       "796654   0.168403  \n",
       "796652  -0.053625  \n",
       "796662  -0.045778  \n",
       "784973   0.196290  \n",
       "\n",
       "[806000 rows x 3 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined[\"Date\"] = [d.date() for d in pd.to_datetime(joined[\"Date\"])]\n",
    "joined = joined.sort_values(by=\"Date\")\n",
    "joined"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "abb8104d",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined.to_csv(\"data/joined_tagged.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6229dda",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}