6_format_for_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Preparation for Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import packages\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime as dt\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### sentiment data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Python\\lib\\site-packages\\numpy\\lib\\arraysetops.py:472: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison\n",
      "  mask |= (ar1 == a)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>author</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>score</th>\n",
       "      <th>negative_comment</th>\n",
       "      <th>neutral_comment</th>\n",
       "      <th>positive_comment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CryptoHODLer101</td>\n",
       "      <td>AMA</td>\n",
       "      <td>1513292107</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>nappiestapparatus</td>\n",
       "      <td>AMA</td>\n",
       "      <td>1395194709</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Skating2Death</td>\n",
       "      <td>AMA</td>\n",
       "      <td>1390016247</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>OmarJunkman</td>\n",
       "      <td>AMA</td>\n",
       "      <td>1528300155</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>evanc1411</td>\n",
       "      <td>ASU</td>\n",
       "      <td>1525380807</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              author subreddit  created_utc  score  negative_comment  \\\n",
       "0    CryptoHODLer101       AMA   1513292107      0                 0   \n",
       "1  nappiestapparatus       AMA   1395194709      0                 1   \n",
       "2      Skating2Death       AMA   1390016247      0                 1   \n",
       "3        OmarJunkman       AMA   1528300155      0                 0   \n",
       "4          evanc1411       ASU   1525380807      0                 1   \n",
       "\n",
       "   neutral_comment  positive_comment  \n",
       "0                0                 1  \n",
       "1                0                 0  \n",
       "2                0                 0  \n",
       "3                1                 0  \n",
       "4                0                 0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import data\n",
    "even = pd.read_csv('sentiment_data_even.csv', index_col = 0)\n",
    "odd = pd.read_csv('sentiment_data_odd.csv', index_col = 0)\n",
    "\n",
    "odd.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(                 author subreddit  created_utc  score  negative_comment  \\\n",
       " 0              Leaftail       3DS   1416269610      0                 0   \n",
       " 1     AlmightyKingJason       AMA   1470582406      0                 1   \n",
       " 2        The_Allosaurus       AMA   1528016006      0                 1   \n",
       " 3       Garthanthoclops       AMA   1511993554      0                 0   \n",
       " 4  PUBLIQclopAccountant       AMA   1392434970      0                 1   \n",
       " \n",
       "    neutral_comment  positive_comment  \n",
       " 0                0                 1  \n",
       " 1                0                 0  \n",
       " 2                0                 0  \n",
       " 3                0                 1  \n",
       " 4                0                 0  ,\n",
       "                      author              subreddit  created_utc  score  \\\n",
       " 3216088           precrime3  financialindependence   1467473119     -1   \n",
       " 3216089   cyber_numismatist  financialindependence   1461538283     -1   \n",
       " 3216090       fire_throwawa  financialindependence   1489675931     -1   \n",
       " 3216091  SchpittleSchpattle  financialindependence   1511973349     -1   \n",
       " 3216092              mbfire  financialindependence   1515184697     -1   \n",
       " \n",
       "          negative_comment  neutral_comment  positive_comment  \n",
       " 3216088                 0                0                 1  \n",
       " 3216089                 0                0                 1  \n",
       " 3216090                 1                0                 0  \n",
       " 3216091                 0                0                 1  \n",
       " 3216092                 1                0                 0  )"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# concatencate even and odd sentiment_data into one df\n",
    "data = even.append(odd, ignore_index = True)\n",
    "\n",
    "# ['created_utc'] of head should be even, tail should be odd\n",
    "# even data is on top, append means you stick data to the bottom so odd is on the bottom\n",
    "data.head(), data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# delete redundant data\n",
    "del even\n",
    "del odd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>author</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>score</th>\n",
       "      <th>negative_comment</th>\n",
       "      <th>neutral_comment</th>\n",
       "      <th>positive_comment</th>\n",
       "      <th>datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Leaftail</td>\n",
       "      <td>3DS</td>\n",
       "      <td>1416269610</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2014-11-18 00:13:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AlmightyKingJason</td>\n",
       "      <td>AMA</td>\n",
       "      <td>1470582406</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-08-07 15:06:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The_Allosaurus</td>\n",
       "      <td>AMA</td>\n",
       "      <td>1528016006</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2018-06-03 08:53:26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Garthanthoclops</td>\n",
       "      <td>AMA</td>\n",
       "      <td>1511993554</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-11-29 22:12:34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PUBLIQclopAccountant</td>\n",
       "      <td>AMA</td>\n",
       "      <td>1392434970</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2014-02-15 03:29:30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 author subreddit  created_utc  score  negative_comment  \\\n",
       "0              Leaftail       3DS   1416269610      0                 0   \n",
       "1     AlmightyKingJason       AMA   1470582406      0                 1   \n",
       "2        The_Allosaurus       AMA   1528016006      0                 1   \n",
       "3       Garthanthoclops       AMA   1511993554      0                 0   \n",
       "4  PUBLIQclopAccountant       AMA   1392434970      0                 1   \n",
       "\n",
       "   neutral_comment  positive_comment            datetime  \n",
       "0                0                 1 2014-11-18 00:13:30  \n",
       "1                0                 0 2016-08-07 15:06:46  \n",
       "2                0                 0 2018-06-03 08:53:26  \n",
       "3                0                 1 2017-11-29 22:12:34  \n",
       "4                0                 0 2014-02-15 03:29:30  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert ['created_utc'] to datetime date\n",
    "data['datetime'] = pd.to_datetime(data['created_utc'], unit = 's')\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>author</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>score</th>\n",
       "      <th>negative_comment</th>\n",
       "      <th>neutral_comment</th>\n",
       "      <th>positive_comment</th>\n",
       "      <th>datetime</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Leaftail</td>\n",
       "      <td>3DS</td>\n",
       "      <td>1416269610</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2014-11-18 00:13:30</td>\n",
       "      <td>2014-11-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AlmightyKingJason</td>\n",
       "      <td>AMA</td>\n",
       "      <td>1470582406</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-08-07 15:06:46</td>\n",
       "      <td>2016-08-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The_Allosaurus</td>\n",
       "      <td>AMA</td>\n",
       "      <td>1528016006</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2018-06-03 08:53:26</td>\n",
       "      <td>2018-06-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Garthanthoclops</td>\n",
       "      <td>AMA</td>\n",
       "      <td>1511993554</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-11-29 22:12:34</td>\n",
       "      <td>2017-11-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PUBLIQclopAccountant</td>\n",
       "      <td>AMA</td>\n",
       "      <td>1392434970</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2014-02-15 03:29:30</td>\n",
       "      <td>2014-02-15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 author subreddit  created_utc  score  negative_comment  \\\n",
       "0              Leaftail       3DS   1416269610      0                 0   \n",
       "1     AlmightyKingJason       AMA   1470582406      0                 1   \n",
       "2        The_Allosaurus       AMA   1528016006      0                 1   \n",
       "3       Garthanthoclops       AMA   1511993554      0                 0   \n",
       "4  PUBLIQclopAccountant       AMA   1392434970      0                 1   \n",
       "\n",
       "   neutral_comment  positive_comment            datetime        date  \n",
       "0                0                 1 2014-11-18 00:13:30  2014-11-18  \n",
       "1                0                 0 2016-08-07 15:06:46  2016-08-07  \n",
       "2                0                 0 2018-06-03 08:53:26  2018-06-03  \n",
       "3                0                 1 2017-11-29 22:12:34  2017-11-29  \n",
       "4                0                 0 2014-02-15 03:29:30  2014-02-15  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert ['datetime'] to date\n",
    "data['date'] = pd.DatetimeIndex(data['datetime']).date\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# delete redundant data\n",
    "del data['created_utc']\n",
    "del data['datetime']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Here you can create new variables/ group_by different variables. \n",
    "# I don't use ['score'] and ['subreddit'] in this analysis but there are different ways that you could possibly incorporate them into your analysis\n",
    "# for example: using ['score'] to assign weights to comments, cryptocurrency vs non-cryptocurrency ['subreddit'] sentiment, etc....\n",
    "\n",
    "# for now i will delete them\n",
    "del data['score']\n",
    "del data['subreddit']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>negative_comment</th>\n",
       "      <th>neutral_comment</th>\n",
       "      <th>positive_comment</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2009-05-08</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-07-18</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-09-24</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-12-31</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-03-28</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            negative_comment  neutral_comment  positive_comment\n",
       "date                                                           \n",
       "2009-05-08                 2                0                 2\n",
       "2009-07-18                 1                0                 0\n",
       "2009-09-24                 0                1                 1\n",
       "2009-12-31                 0                1                 0\n",
       "2010-03-28                 0                1                 0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# groupby date\n",
    "# returns count of positive/neutral/negative comments for each ['date']\n",
    "# ['date'] is now the index\n",
    "data = data.groupby(by = ['date']).sum()\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## price data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-07-16</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010-07-17</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.08585</td>\n",
       "      <td>0.05941</td>\n",
       "      <td>0.08584</td>\n",
       "      <td>0.08584</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010-07-18</td>\n",
       "      <td>0.08584</td>\n",
       "      <td>0.09307</td>\n",
       "      <td>0.07723</td>\n",
       "      <td>0.08080</td>\n",
       "      <td>0.08080</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010-07-19</td>\n",
       "      <td>0.08080</td>\n",
       "      <td>0.08181</td>\n",
       "      <td>0.07426</td>\n",
       "      <td>0.07474</td>\n",
       "      <td>0.07474</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010-07-20</td>\n",
       "      <td>0.07474</td>\n",
       "      <td>0.07921</td>\n",
       "      <td>0.06634</td>\n",
       "      <td>0.07921</td>\n",
       "      <td>0.07921</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date     Open     High      Low    Close  Adj Close  Volume\n",
       "0  2010-07-16  0.04951  0.04951  0.04951  0.04951    0.04951       0\n",
       "1  2010-07-17  0.04951  0.08585  0.05941  0.08584    0.08584       5\n",
       "2  2010-07-18  0.08584  0.09307  0.07723  0.08080    0.08080      49\n",
       "3  2010-07-19  0.08080  0.08181  0.07426  0.07474    0.07474      20\n",
       "4  2010-07-20  0.07474  0.07921  0.06634  0.07921    0.07921      42"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price = pd.read_csv('yahoo_price.csv')\n",
    "price.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create new date variable that is the same name as the date variable in `data\n",
    "price['date'] = price['Date'].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-- string --\n",
      "data : 2009-05-08\n",
      "price : 2010-07-16\n",
      "------------------------------\n",
      "-- raw string --\n",
      "data : datetime.date(2009, 5, 8)\n",
      "price : '2010-07-16'\n"
     ]
    }
   ],
   "source": [
    "# check to see if `price` and `data` have the same `date` format\n",
    "\n",
    "print('-- string --')\n",
    "print('data : ' + str(data.index[0]))\n",
    "print('price : ' + str(price['date'][0]))\n",
    "print('------------------------------')\n",
    "print('-- raw string --')\n",
    "print('data : ' + repr(data.index[0]))\n",
    "print('price : ' + repr(price['date'][0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While the strings may look the same, they are actually different. `data['date']` is actually<br>\n",
    "a `datetime.date` object type while `price['date']` is just a string. We see this when we <br>\n",
    "print the raw strings, which is what the data actually is and the data that python interprets.<br>\n",
    "In this state, they can't merge. We have to convert `price['date']` to a `datetime.date` object. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('2010-07-16 00:00:00', Timestamp('2010-07-16 00:00:00'))"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert to `datetime` object\n",
    "price['date'] = pd.to_datetime(price['date'])\n",
    "str(price['date'][0]), price['date'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('2010-07-16', datetime.date(2010, 7, 16))"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert datetime into `datetime.date` object\n",
    "price['date'] = pd.DatetimeIndex(price['date']).date\n",
    "str(price['date'][0]), price['date'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-- string --\n",
      "data : 2009-05-08\n",
      "price : 2010-07-16\n",
      "------------------------------\n",
      "-- raw string --\n",
      "data : datetime.date(2009, 5, 8)\n",
      "price : datetime.date(2010, 7, 16)\n"
     ]
    }
   ],
   "source": [
    "# check to see if `price` and `data` have the same `date` format\n",
    "\n",
    "print('-- string --')\n",
    "print('data : ' + str(data.index[0]))\n",
    "print('price : ' + str(price['date'][0]))\n",
    "print('------------------------------')\n",
    "print('-- raw string --')\n",
    "print('data : ' + repr(data.index[0]))\n",
    "print('price : ' + repr(price['date'][0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-07-16</th>\n",
       "      <td>2010-07-16</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-07-17</th>\n",
       "      <td>2010-07-17</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.08585</td>\n",
       "      <td>0.05941</td>\n",
       "      <td>0.08584</td>\n",
       "      <td>0.08584</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-07-18</th>\n",
       "      <td>2010-07-18</td>\n",
       "      <td>0.08584</td>\n",
       "      <td>0.09307</td>\n",
       "      <td>0.07723</td>\n",
       "      <td>0.08080</td>\n",
       "      <td>0.08080</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-07-19</th>\n",
       "      <td>2010-07-19</td>\n",
       "      <td>0.08080</td>\n",
       "      <td>0.08181</td>\n",
       "      <td>0.07426</td>\n",
       "      <td>0.07474</td>\n",
       "      <td>0.07474</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-07-20</th>\n",
       "      <td>2010-07-20</td>\n",
       "      <td>0.07474</td>\n",
       "      <td>0.07921</td>\n",
       "      <td>0.06634</td>\n",
       "      <td>0.07921</td>\n",
       "      <td>0.07921</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Date     Open     High      Low    Close  Adj Close  Volume\n",
       "date                                                                         \n",
       "2010-07-16  2010-07-16  0.04951  0.04951  0.04951  0.04951    0.04951       0\n",
       "2010-07-17  2010-07-17  0.04951  0.08585  0.05941  0.08584    0.08584       5\n",
       "2010-07-18  2010-07-18  0.08584  0.09307  0.07723  0.08080    0.08080      49\n",
       "2010-07-19  2010-07-19  0.08080  0.08181  0.07426  0.07474    0.07474      20\n",
       "2010-07-20  2010-07-20  0.07474  0.07921  0.06634  0.07921    0.07921      42"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# define `price` index as `price['date']`\n",
    "price.index = price['date']\n",
    "del price['date']\n",
    "price.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## merging data and price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "      <th>negative_comment</th>\n",
       "      <th>neutral_comment</th>\n",
       "      <th>positive_comment</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2010-07-16</th>\n",
       "      <td>2010-07-16</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-07-17</th>\n",
       "      <td>2010-07-17</td>\n",
       "      <td>0.04951</td>\n",
       "      <td>0.08585</td>\n",
       "      <td>0.05941</td>\n",
       "      <td>0.08584</td>\n",
       "      <td>0.08584</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-07-18</th>\n",
       "      <td>2010-07-18</td>\n",
       "      <td>0.08584</td>\n",
       "      <td>0.09307</td>\n",
       "      <td>0.07723</td>\n",
       "      <td>0.08080</td>\n",
       "      <td>0.08080</td>\n",
       "      <td>49</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-07-19</th>\n",
       "      <td>2010-07-19</td>\n",
       "      <td>0.08080</td>\n",
       "      <td>0.08181</td>\n",
       "      <td>0.07426</td>\n",
       "      <td>0.07474</td>\n",
       "      <td>0.07474</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-07-20</th>\n",
       "      <td>2010-07-20</td>\n",
       "      <td>0.07474</td>\n",
       "      <td>0.07921</td>\n",
       "      <td>0.06634</td>\n",
       "      <td>0.07921</td>\n",
       "      <td>0.07921</td>\n",
       "      <td>42</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Date     Open     High      Low    Close  Adj Close  Volume  \\\n",
       "date                                                                            \n",
       "2010-07-16  2010-07-16  0.04951  0.04951  0.04951  0.04951    0.04951       0   \n",
       "2010-07-17  2010-07-17  0.04951  0.08585  0.05941  0.08584    0.08584       5   \n",
       "2010-07-18  2010-07-18  0.08584  0.09307  0.07723  0.08080    0.08080      49   \n",
       "2010-07-19  2010-07-19  0.08080  0.08181  0.07426  0.07474    0.07474      20   \n",
       "2010-07-20  2010-07-20  0.07474  0.07921  0.06634  0.07921    0.07921      42   \n",
       "\n",
       "            negative_comment  neutral_comment  positive_comment  \n",
       "date                                                             \n",
       "2010-07-16               NaN              NaN               NaN  \n",
       "2010-07-17               NaN              NaN               NaN  \n",
       "2010-07-18               NaN              NaN               NaN  \n",
       "2010-07-19               NaN              NaN               NaN  \n",
       "2010-07-20               NaN              NaN               NaN  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# combine sentiment and price data\n",
    "data = price.merge(data, how = 'left', left_index = True, right_index = True)\n",
    "data.head()\n",
    "\n",
    "# don't worry about the NaN values. That just means that on that day, there weren't any comments as the bitcoin community was still small"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Adj Close</th>\n",
       "      <th>Volume</th>\n",
       "      <th>negative_comment</th>\n",
       "      <th>neutral_comment</th>\n",
       "      <th>positive_comment</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-08-27</th>\n",
       "      <td>2018-08-27</td>\n",
       "      <td>6915.919922</td>\n",
       "      <td>7136.709961</td>\n",
       "      <td>6877.200195</td>\n",
       "      <td>7091.379883</td>\n",
       "      <td>7091.379883</td>\n",
       "      <td>487789642</td>\n",
       "      <td>587.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>850.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-28</th>\n",
       "      <td>2018-08-28</td>\n",
       "      <td>7091.379883</td>\n",
       "      <td>7137.470215</td>\n",
       "      <td>6944.759766</td>\n",
       "      <td>7052.000000</td>\n",
       "      <td>7052.000000</td>\n",
       "      <td>356139022</td>\n",
       "      <td>590.0</td>\n",
       "      <td>205.0</td>\n",
       "      <td>972.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-29</th>\n",
       "      <td>2018-08-29</td>\n",
       "      <td>7051.609863</td>\n",
       "      <td>7072.640137</td>\n",
       "      <td>6818.689941</td>\n",
       "      <td>6998.759766</td>\n",
       "      <td>6998.759766</td>\n",
       "      <td>425475685</td>\n",
       "      <td>664.0</td>\n",
       "      <td>227.0</td>\n",
       "      <td>955.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-30</th>\n",
       "      <td>2018-08-30</td>\n",
       "      <td>6998.759766</td>\n",
       "      <td>7096.729980</td>\n",
       "      <td>6903.080078</td>\n",
       "      <td>7026.959961</td>\n",
       "      <td>7026.959961</td>\n",
       "      <td>349131152</td>\n",
       "      <td>617.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>886.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>2018-08-31</td>\n",
       "      <td>7026.959961</td>\n",
       "      <td>7272.029785</td>\n",
       "      <td>7025.580078</td>\n",
       "      <td>7203.459961</td>\n",
       "      <td>7203.459961</td>\n",
       "      <td>300301833</td>\n",
       "      <td>599.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>877.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Date         Open         High          Low        Close  \\\n",
       "date                                                                         \n",
       "2018-08-27  2018-08-27  6915.919922  7136.709961  6877.200195  7091.379883   \n",
       "2018-08-28  2018-08-28  7091.379883  7137.470215  6944.759766  7052.000000   \n",
       "2018-08-29  2018-08-29  7051.609863  7072.640137  6818.689941  6998.759766   \n",
       "2018-08-30  2018-08-30  6998.759766  7096.729980  6903.080078  7026.959961   \n",
       "2018-08-31  2018-08-31  7026.959961  7272.029785  7025.580078  7203.459961   \n",
       "\n",
       "              Adj Close     Volume  negative_comment  neutral_comment  \\\n",
       "date                                                                    \n",
       "2018-08-27  7091.379883  487789642             587.0            204.0   \n",
       "2018-08-28  7052.000000  356139022             590.0            205.0   \n",
       "2018-08-29  6998.759766  425475685             664.0            227.0   \n",
       "2018-08-30  7026.959961  349131152             617.0            181.0   \n",
       "2018-08-31  7203.459961  300301833             599.0            204.0   \n",
       "\n",
       "            positive_comment  \n",
       "date                          \n",
       "2018-08-27             850.0  \n",
       "2018-08-28             972.0  \n",
       "2018-08-29             955.0  \n",
       "2018-08-30             886.0  \n",
       "2018-08-31             877.0  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# last date that we have sentiment data for\n",
    "end_date = dt.date(2018, 8, 31)\n",
    "\n",
    "# redefine data with `end_date` as the last point\n",
    "data = data[ : end_date]\n",
    "data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>price</th>\n",
       "      <th>volatility</th>\n",
       "      <th>volume_price</th>\n",
       "      <th>volume_number</th>\n",
       "      <th>positive_comment</th>\n",
       "      <th>neutral_comment</th>\n",
       "      <th>negative_comment</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-08-27</th>\n",
       "      <td>7091.379883</td>\n",
       "      <td>0.036595</td>\n",
       "      <td>487789642</td>\n",
       "      <td>68786.279969</td>\n",
       "      <td>850.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>587.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-28</th>\n",
       "      <td>7052.000000</td>\n",
       "      <td>0.027327</td>\n",
       "      <td>356139022</td>\n",
       "      <td>50501.846568</td>\n",
       "      <td>972.0</td>\n",
       "      <td>205.0</td>\n",
       "      <td>590.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-29</th>\n",
       "      <td>6998.759766</td>\n",
       "      <td>0.036285</td>\n",
       "      <td>425475685</td>\n",
       "      <td>60793.011794</td>\n",
       "      <td>955.0</td>\n",
       "      <td>227.0</td>\n",
       "      <td>664.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-30</th>\n",
       "      <td>7026.959961</td>\n",
       "      <td>0.027558</td>\n",
       "      <td>349131152</td>\n",
       "      <td>49684.522744</td>\n",
       "      <td>886.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>617.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>7203.459961</td>\n",
       "      <td>0.034213</td>\n",
       "      <td>300301833</td>\n",
       "      <td>41688.554476</td>\n",
       "      <td>877.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>599.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  price  volatility  volume_price  volume_number  \\\n",
       "date                                                               \n",
       "2018-08-27  7091.379883    0.036595     487789642   68786.279969   \n",
       "2018-08-28  7052.000000    0.027327     356139022   50501.846568   \n",
       "2018-08-29  6998.759766    0.036285     425475685   60793.011794   \n",
       "2018-08-30  7026.959961    0.027558     349131152   49684.522744   \n",
       "2018-08-31  7203.459961    0.034213     300301833   41688.554476   \n",
       "\n",
       "            positive_comment  neutral_comment  negative_comment  \n",
       "date                                                             \n",
       "2018-08-27             850.0            204.0             587.0  \n",
       "2018-08-28             972.0            205.0             590.0  \n",
       "2018-08-29             955.0            227.0             664.0  \n",
       "2018-08-30             886.0            181.0             617.0  \n",
       "2018-08-31             877.0            204.0             599.0  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# new df with formatted variables\n",
    "df = pd.DataFrame()\n",
    "df['price'] = data['Close']\n",
    "df['volatility'] = (data['High'] - data['Low']) / data['Close']\n",
    "df['volume_price'] = data['Volume']\n",
    "df['volume_number'] = data['Volume'] / data['Close']\n",
    "df['positive_comment'] = data['positive_comment']\n",
    "df['neutral_comment'] = data['neutral_comment']\n",
    "df['negative_comment'] = data['negative_comment']\n",
    "\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('formatted_data_all.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}