3.data_preparation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d67746f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import packages\n",
    "import ast\n",
    "from collections import Counter\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0a886ad4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_submissions = pd.read_csv(\"data/feature_submissions.csv\", delimiter=\";\", index_col=\"created_utc\", encoding=\"utf-8\")\n",
    "df_comments = pd.read_csv(\"data/feature_comments_pratik.csv\", delimiter=\";\", index_col=\"created_utc_x\", encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1297097e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_submissions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b7b796bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_comments.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f9b46f4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Do this with every features of type Counter\n",
    "df_submissions[\"emojis\"] = df_submissions[\"emojis\"].apply(lambda x: ast.literal_eval(x))\n",
    "df_submissions_emojis = pd.DataFrame(df_submissions[\"emojis\"].tolist())\n",
    "df_submissions_emojis[\"created_utc\"] = df_submissions.index\n",
    "df_submissions_emojis.set_index(\"created_utc\", inplace=True)\n",
    "\n",
    "df_submissions[\"category\"] = df_submissions[\"category\"].apply(lambda x: ast.literal_eval(x))\n",
    "df_submissions_category = pd.DataFrame(df_submissions[\"category\"].tolist())\n",
    "df_submissions_category[\"created_utc\"] = df_submissions.index\n",
    "df_submissions_category.set_index(\"created_utc\", inplace=True)\n",
    "\n",
    "df_submissions[\"sp500_stocks\"] = df_submissions[\"sp500_stocks\"].apply(lambda x: ast.literal_eval(x))\n",
    "df_submissions_sp500_stocks = pd.DataFrame(df_submissions[\"sp500_stocks\"].tolist())\n",
    "df_submissions_sp500_stocks[\"created_utc\"] = df_submissions.index\n",
    "df_submissions_sp500_stocks.set_index(\"created_utc\", inplace=True)\n",
    "\n",
    "df_submissions[\"non_sp500_stocks\"] = df_submissions[\"non_sp500_stocks\"].apply(lambda x: ast.literal_eval(x))\n",
    "df_submissions_non_sp500_stocks = pd.DataFrame(df_submissions[\"non_sp500_stocks\"].tolist())\n",
    "df_submissions_non_sp500_stocks[\"created_utc\"] = df_submissions.index\n",
    "df_submissions_non_sp500_stocks.set_index(\"created_utc\", inplace=True)\n",
    "\n",
    "df_submissions[\"sp500_lingos\"] = df_submissions[\"sp500_lingos\"].apply(lambda x: ast.literal_eval(x))\n",
    "df_submissions_sp500_lingos = pd.DataFrame(df_submissions[\"sp500_lingos\"].tolist())\n",
    "df_submissions_sp500_lingos[\"created_utc\"] = df_submissions.index\n",
    "df_submissions_sp500_lingos.set_index(\"created_utc\", inplace=True)\n",
    "\n",
    "df_submissions[\"non_sp500_lingos\"] = df_submissions[\"non_sp500_lingos\"].apply(lambda x: ast.literal_eval(x))\n",
    "df_submissions_non_sp500_lingos = pd.DataFrame(df_submissions[\"non_sp500_lingos\"].tolist())\n",
    "df_submissions_non_sp500_lingos[\"created_utc\"] = df_submissions.index\n",
    "df_submissions_non_sp500_lingos.set_index(\"created_utc\", inplace=True)\n",
    "\n",
    "# @todo Do the same conversion for comments dataframe\n",
    "# @todo Maybe we need to combine emojis, sp500 and non sp500 stocks for both submissions and comments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0fa2ba5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the feature dataframe that will be feed into machine learning model\n",
    "df_features = pd.concat([df_submissions[\"title_polarity\"]/df_submissions[\"count\"], \n",
    "                        df_submissions[\"body_polarity\"]/df_submissions[\"count\"], \n",
    "                        df_submissions[\"score\"]/df_submissions[\"count\"], \n",
    "                        df_submissions[\"upvote_ratio\"]/df_submissions[\"count\"],\n",
    "                        df_comments[\"body_polarity\"]/df_comments[\"count\"],\n",
    "                        df_comments[\"score_x\"]/df_comments[\"count\"]],\n",
    "                        axis=1)\n",
    "\n",
    "df_features.columns = [\"submission_title_polarity\", \"submission_body_polarity\", \"submission_score\",\n",
    "                      \"submission_upvote_ratio\", \"comment_polarity\", \"comment_score\"]\n",
    "\n",
    "df_features = pd.concat([df_features,\n",
    "                         df_submissions_category, \n",
    "                         df_submissions_emojis,\n",
    "                         df_submissions_sp500_stocks, \n",
    "                         df_submissions_non_sp500_stocks,\n",
    "                         df_submissions_sp500_lingos, \n",
    "                         df_submissions_non_sp500_lingos], axis=1)\n",
    "\n",
    "column_names = []\n",
    "for col in df_features.columns:\n",
    "    name = col.replace(\"'\", \"\").replace(\"]\", \"\").replace(\" \", \"\")\n",
    "    column_names.append(name)\n",
    "    \n",
    "df_features.columns = column_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d91cba7e",
   "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>submission_title_polarity</th>\n",
       "      <th>submission_body_polarity</th>\n",
       "      <th>submission_score</th>\n",
       "      <th>submission_upvote_ratio</th>\n",
       "      <th>comment_polarity</th>\n",
       "      <th>comment_score</th>\n",
       "      <th>Discussion</th>\n",
       "      <th>YOLO</th>\n",
       "      <th>Shitpost</th>\n",
       "      <th>Meme</th>\n",
       "      <th>...</th>\n",
       "      <th>YOLO</th>\n",
       "      <th>moon</th>\n",
       "      <th>holding</th>\n",
       "      <th>buying</th>\n",
       "      <th>BUY</th>\n",
       "      <th>high</th>\n",
       "      <th>TENDIES</th>\n",
       "      <th>squeeze</th>\n",
       "      <th>BAGHOLDER</th>\n",
       "      <th>BAGHOLDER</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>0.070225</td>\n",
       "      <td>0.062297</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22</td>\n",
       "      <td>5.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>0.034676</td>\n",
       "      <td>0.003171</td>\n",
       "      <td>15.769784</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23</td>\n",
       "      <td>5.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>0.039069</td>\n",
       "      <td>0.052156</td>\n",
       "      <td>318.086957</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28</td>\n",
       "      <td>3.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>0.065873</td>\n",
       "      <td>0.003330</td>\n",
       "      <td>592.285714</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-05</th>\n",
       "      <td>0.022946</td>\n",
       "      <td>0.074473</td>\n",
       "      <td>6.865672</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-03-27</th>\n",
       "      <td>0.051192</td>\n",
       "      <td>0.036946</td>\n",
       "      <td>1.109091</td>\n",
       "      <td>0.994727</td>\n",
       "      <td>-0.047500</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>47</td>\n",
       "      <td>36.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-03-28</th>\n",
       "      <td>0.073997</td>\n",
       "      <td>0.036091</td>\n",
       "      <td>1.720721</td>\n",
       "      <td>0.971284</td>\n",
       "      <td>0.024776</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>130</td>\n",
       "      <td>65.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>98.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-03-29</th>\n",
       "      <td>0.047325</td>\n",
       "      <td>0.032280</td>\n",
       "      <td>1.055602</td>\n",
       "      <td>0.996765</td>\n",
       "      <td>0.070625</td>\n",
       "      <td>1.034483</td>\n",
       "      <td>343</td>\n",
       "      <td>173.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>176.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-03-30</th>\n",
       "      <td>0.053576</td>\n",
       "      <td>0.031882</td>\n",
       "      <td>1.016949</td>\n",
       "      <td>0.998508</td>\n",
       "      <td>0.058682</td>\n",
       "      <td>0.980000</td>\n",
       "      <td>336</td>\n",
       "      <td>189.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>164.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-03-31</th>\n",
       "      <td>0.046992</td>\n",
       "      <td>0.037166</td>\n",
       "      <td>1.018197</td>\n",
       "      <td>0.998081</td>\n",
       "      <td>0.043558</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>375</td>\n",
       "      <td>222.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>117.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>443 rows × 5033 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            submission_title_polarity  submission_body_polarity  \\\n",
       "2020-01-01                   0.070225                  0.062297   \n",
       "2020-01-02                   0.034676                  0.003171   \n",
       "2020-01-03                   0.039069                  0.052156   \n",
       "2020-01-04                   0.065873                  0.003330   \n",
       "2020-01-05                   0.022946                  0.074473   \n",
       "...                               ...                       ...   \n",
       "2021-03-27                   0.051192                  0.036946   \n",
       "2021-03-28                   0.073997                  0.036091   \n",
       "2021-03-29                   0.047325                  0.032280   \n",
       "2021-03-30                   0.053576                  0.031882   \n",
       "2021-03-31                   0.046992                  0.037166   \n",
       "\n",
       "            submission_score  submission_upvote_ratio  comment_polarity  \\\n",
       "2020-01-01          1.000000                 0.000000               NaN   \n",
       "2020-01-02         15.769784                 0.000000               NaN   \n",
       "2020-01-03        318.086957                 0.000000               NaN   \n",
       "2020-01-04        592.285714                 0.000000               NaN   \n",
       "2020-01-05          6.865672                 0.000000               NaN   \n",
       "...                      ...                      ...               ...   \n",
       "2021-03-27          1.109091                 0.994727         -0.047500   \n",
       "2021-03-28          1.720721                 0.971284          0.024776   \n",
       "2021-03-29          1.055602                 0.996765          0.070625   \n",
       "2021-03-30          1.016949                 0.998508          0.058682   \n",
       "2021-03-31          1.018197                 0.998081          0.043558   \n",
       "\n",
       "            comment_score  Discussion   YOLO  Shitpost   Meme  ...  YOLO  \\\n",
       "2020-01-01            NaN          22    5.0      16.0    3.0  ...   NaN   \n",
       "2020-01-02            NaN          23    5.0      28.0    1.0  ...   NaN   \n",
       "2020-01-03            NaN          28    3.0      32.0    3.0  ...   NaN   \n",
       "2020-01-04            NaN           7    1.0       9.0    2.0  ...   NaN   \n",
       "2020-01-05            NaN          14    1.0      13.0    1.0  ...   NaN   \n",
       "...                   ...         ...    ...       ...    ...  ...   ...   \n",
       "2021-03-27       1.000000          47   36.0       NaN   29.0  ...   NaN   \n",
       "2021-03-28       1.000000         130   65.0       NaN   98.0  ...   NaN   \n",
       "2021-03-29       1.034483         343  173.0       NaN  176.0  ...   NaN   \n",
       "2021-03-30       0.980000         336  189.0       NaN  164.0  ...   NaN   \n",
       "2021-03-31       1.000000         375  222.0       NaN  117.0  ...   NaN   \n",
       "\n",
       "            moon  holding  buying  BUY  high  TENDIES  squeeze  BAGHOLDER  \\\n",
       "2020-01-01   NaN      NaN     NaN  NaN   NaN      NaN      NaN        NaN   \n",
       "2020-01-02   NaN      NaN     NaN  NaN   NaN      NaN      NaN        NaN   \n",
       "2020-01-03   NaN      NaN     NaN  NaN   NaN      NaN      NaN        NaN   \n",
       "2020-01-04   NaN      NaN     NaN  NaN   NaN      NaN      NaN        NaN   \n",
       "2020-01-05   NaN      NaN     NaN  NaN   NaN      NaN      NaN        NaN   \n",
       "...          ...      ...     ...  ...   ...      ...      ...        ...   \n",
       "2021-03-27   NaN      1.0     NaN  NaN   NaN      NaN      NaN        NaN   \n",
       "2021-03-28   NaN      NaN     NaN  NaN   NaN      NaN      NaN        NaN   \n",
       "2021-03-29   NaN      NaN     NaN  NaN   NaN      NaN      NaN        NaN   \n",
       "2021-03-30   NaN      NaN     NaN  NaN   NaN      NaN      NaN        NaN   \n",
       "2021-03-31   NaN      NaN     NaN  NaN   NaN      NaN      NaN        NaN   \n",
       "\n",
       "            BAGHOLDER  \n",
       "2020-01-01        NaN  \n",
       "2020-01-02        NaN  \n",
       "2020-01-03        NaN  \n",
       "2020-01-04        NaN  \n",
       "2020-01-05        NaN  \n",
       "...               ...  \n",
       "2021-03-27        NaN  \n",
       "2021-03-28        NaN  \n",
       "2021-03-29        NaN  \n",
       "2021-03-30        NaN  \n",
       "2021-03-31        NaN  \n",
       "\n",
       "[443 rows x 5033 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da23ee1a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "850ca52e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e36ed477",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}