aggregate_datasets.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>web_url</th>\n",
       "      <th>content</th>\n",
       "      <th>headline</th>\n",
       "      <th>document_type</th>\n",
       "      <th>pub_date</th>\n",
       "      <th>source</th>\n",
       "      <th>section_name</th>\n",
       "      <th>author</th>\n",
       "      <th>word_count</th>\n",
       "      <th>content_no_stopwords</th>\n",
       "      <th>SENTIMENT_VALUE</th>\n",
       "      <th>SENTIMENT</th>\n",
       "      <th>relevance</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.nytimes.com/2019/01/02/world/europ...</td>\n",
       "      <td>halmstad, sweden — maria hussein, who escaped ...</td>\n",
       "      <td>Where Doulas Calm Nerves and Bridge Cultures D...</td>\n",
       "      <td>article</td>\n",
       "      <td>2019-01-02T22:17:38+0000</td>\n",
       "      <td>The New York Times</td>\n",
       "      <td>World</td>\n",
       "      <td>CHRISTINA ANDERSON</td>\n",
       "      <td>1326</td>\n",
       "      <td>halmstad, sweden — maria hussein, escaped war ...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Very Positive</td>\n",
       "      <td>0.002</td>\n",
       "      <td>0.0020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://www.nytimes.com/2019/01/02/us/politics...</td>\n",
       "      <td>voting rights and partisan gerrymandering, tra...</td>\n",
       "      <td>Voting Issues and Gerrymanders Are Now Key Pol...</td>\n",
       "      <td>article</td>\n",
       "      <td>2019-01-02T17:00:47+0000</td>\n",
       "      <td>The New York Times</td>\n",
       "      <td>U.S.</td>\n",
       "      <td>TRIP GABRIEL</td>\n",
       "      <td>1779</td>\n",
       "      <td>voting rights partisan gerrymandering, traditi...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Very Positive</td>\n",
       "      <td>0.060</td>\n",
       "      <td>0.0600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://www.nytimes.com/2019/01/02/world/asia/...</td>\n",
       "      <td>new delhi — two women, accompanied by plainclo...</td>\n",
       "      <td>2 Indian Women Enter Sabarimala Temple, Settin...</td>\n",
       "      <td>article</td>\n",
       "      <td>2019-01-02T12:37:37+0000</td>\n",
       "      <td>The New York Times</td>\n",
       "      <td>World</td>\n",
       "      <td>KAI SCHULTZ and AYESHA VENKATARAMAN</td>\n",
       "      <td>778</td>\n",
       "      <td>new delhi — two women, accompanied plainclothe...</td>\n",
       "      <td>-0.8</td>\n",
       "      <td>Very Negative</td>\n",
       "      <td>0.002</td>\n",
       "      <td>-0.0016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.nytimes.com/2019/01/01/us/politics...</td>\n",
       "      <td>good tuesday morning and happy new year. here ...</td>\n",
       "      <td>On Politics: Elizabeth Warren Is Running for P...</td>\n",
       "      <td>article</td>\n",
       "      <td>2019-01-01T05:18:48+0000</td>\n",
       "      <td>The New York Times</td>\n",
       "      <td>U.S.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>419</td>\n",
       "      <td>good tuesday morning happy new year. stories m...</td>\n",
       "      <td>0.9</td>\n",
       "      <td>Very Positive</td>\n",
       "      <td>0.060</td>\n",
       "      <td>0.0540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://www.nytimes.com/2018/12/31/world/asia/...</td>\n",
       "      <td>tokyo — kim jong-un, north korea’s leader, sai...</td>\n",
       "      <td>Kim Jong-un, Ready to Meet Trump ‘at Any Time,...</td>\n",
       "      <td>article</td>\n",
       "      <td>2019-01-01T01:44:15+0000</td>\n",
       "      <td>The New York Times</td>\n",
       "      <td>World</td>\n",
       "      <td>MOTOKO RICH and DAVID E. SANGER</td>\n",
       "      <td>1319</td>\n",
       "      <td>tokyo — kim jong-un, north korea’s leader, sai...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Very Positive</td>\n",
       "      <td>0.060</td>\n",
       "      <td>0.0600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             web_url  \\\n",
       "0  https://www.nytimes.com/2019/01/02/world/europ...   \n",
       "1  https://www.nytimes.com/2019/01/02/us/politics...   \n",
       "2  https://www.nytimes.com/2019/01/02/world/asia/...   \n",
       "3  https://www.nytimes.com/2019/01/01/us/politics...   \n",
       "4  https://www.nytimes.com/2018/12/31/world/asia/...   \n",
       "\n",
       "                                             content  \\\n",
       "0  halmstad, sweden — maria hussein, who escaped ...   \n",
       "1  voting rights and partisan gerrymandering, tra...   \n",
       "2  new delhi — two women, accompanied by plainclo...   \n",
       "3  good tuesday morning and happy new year. here ...   \n",
       "4  tokyo — kim jong-un, north korea’s leader, sai...   \n",
       "\n",
       "                                            headline document_type  \\\n",
       "0  Where Doulas Calm Nerves and Bridge Cultures D...       article   \n",
       "1  Voting Issues and Gerrymanders Are Now Key Pol...       article   \n",
       "2  2 Indian Women Enter Sabarimala Temple, Settin...       article   \n",
       "3  On Politics: Elizabeth Warren Is Running for P...       article   \n",
       "4  Kim Jong-un, Ready to Meet Trump ‘at Any Time,...       article   \n",
       "\n",
       "                   pub_date              source section_name  \\\n",
       "0  2019-01-02T22:17:38+0000  The New York Times        World   \n",
       "1  2019-01-02T17:00:47+0000  The New York Times         U.S.   \n",
       "2  2019-01-02T12:37:37+0000  The New York Times        World   \n",
       "3  2019-01-01T05:18:48+0000  The New York Times         U.S.   \n",
       "4  2019-01-01T01:44:15+0000  The New York Times        World   \n",
       "\n",
       "                                 author  word_count  \\\n",
       "0                    CHRISTINA ANDERSON        1326   \n",
       "1                          TRIP GABRIEL        1779   \n",
       "2   KAI SCHULTZ and AYESHA VENKATARAMAN         778   \n",
       "3                                   NaN         419   \n",
       "4       MOTOKO RICH and DAVID E. SANGER        1319   \n",
       "\n",
       "                                content_no_stopwords  SENTIMENT_VALUE  \\\n",
       "0  halmstad, sweden — maria hussein, escaped war ...              1.0   \n",
       "1  voting rights partisan gerrymandering, traditi...              1.0   \n",
       "2  new delhi — two women, accompanied plainclothe...             -0.8   \n",
       "3  good tuesday morning happy new year. stories m...              0.9   \n",
       "4  tokyo — kim jong-un, north korea’s leader, sai...              1.0   \n",
       "\n",
       "       SENTIMENT  relevance   score  \n",
       "0  Very Positive      0.002  0.0020  \n",
       "1  Very Positive      0.060  0.0600  \n",
       "2  Very Negative      0.002 -0.0016  \n",
       "3  Very Positive      0.060  0.0540  \n",
       "4  Very Positive      0.060  0.0600  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "    \n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "price_raw_1 = pd.read_csv('dataset/minuteByminutePrice/priceDataJanuary2019.csv')\n",
    "price_raw_2 = pd.read_csv('dataset/minuteByminutePrice/priceDataFebMarch2019.csv')\n",
    "price_raw = price_raw_1.append(price_raw_2)\n",
    "\n",
    "news_raw_1 = pd.read_csv('dataset/news_data/jan_score.csv')\n",
    "news_raw_2 = pd.read_csv('dataset/news_data/feb_score.csv')\n",
    "news_raw_3 = pd.read_csv('dataset/news_data/march_score.csv')\n",
    "news_raw = news_raw_1.append(news_raw_2).append(news_raw_3)\n",
    "news_raw[news_raw.columns[1:]].to_csv('dataset/news_data/jan-feb-mar.csv')\n",
    "news_raw[news_raw.columns[1:]].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>content</th>\n",
       "      <th>headline</th>\n",
       "      <th>document_type</th>\n",
       "      <th>source</th>\n",
       "      <th>SENTIMENT_VALUE</th>\n",
       "      <th>relevance</th>\n",
       "      <th>pub_date</th>\n",
       "      <th>score</th>\n",
       "      <th>timestamp_rounded</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>halmstad, sweden — maria hussein, who escaped ...</td>\n",
       "      <td>Where Doulas Calm Nerves and Bridge Cultures D...</td>\n",
       "      <td>article</td>\n",
       "      <td>The New York Times</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.002</td>\n",
       "      <td>2019-01-02 22:17:38</td>\n",
       "      <td>0.0020</td>\n",
       "      <td>2019-01-02 22:17:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>voting rights and partisan gerrymandering, tra...</td>\n",
       "      <td>Voting Issues and Gerrymanders Are Now Key Pol...</td>\n",
       "      <td>article</td>\n",
       "      <td>The New York Times</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.060</td>\n",
       "      <td>2019-01-02 17:00:47</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>2019-01-02 17:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>new delhi — two women, accompanied by plainclo...</td>\n",
       "      <td>2 Indian Women Enter Sabarimala Temple, Settin...</td>\n",
       "      <td>article</td>\n",
       "      <td>The New York Times</td>\n",
       "      <td>-0.8</td>\n",
       "      <td>0.002</td>\n",
       "      <td>2019-01-02 12:37:37</td>\n",
       "      <td>-0.0016</td>\n",
       "      <td>2019-01-02 12:37:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>good tuesday morning and happy new year. here ...</td>\n",
       "      <td>On Politics: Elizabeth Warren Is Running for P...</td>\n",
       "      <td>article</td>\n",
       "      <td>The New York Times</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.060</td>\n",
       "      <td>2019-01-01 05:18:48</td>\n",
       "      <td>0.0540</td>\n",
       "      <td>2019-01-01 05:18:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>tokyo — kim jong-un, north korea’s leader, sai...</td>\n",
       "      <td>Kim Jong-un, Ready to Meet Trump ‘at Any Time,...</td>\n",
       "      <td>article</td>\n",
       "      <td>The New York Times</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.060</td>\n",
       "      <td>2019-01-01 01:44:15</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>2019-01-01 01:44:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             content  \\\n",
       "0  halmstad, sweden — maria hussein, who escaped ...   \n",
       "1  voting rights and partisan gerrymandering, tra...   \n",
       "2  new delhi — two women, accompanied by plainclo...   \n",
       "3  good tuesday morning and happy new year. here ...   \n",
       "4  tokyo — kim jong-un, north korea’s leader, sai...   \n",
       "\n",
       "                                            headline document_type  \\\n",
       "0  Where Doulas Calm Nerves and Bridge Cultures D...       article   \n",
       "1  Voting Issues and Gerrymanders Are Now Key Pol...       article   \n",
       "2  2 Indian Women Enter Sabarimala Temple, Settin...       article   \n",
       "3  On Politics: Elizabeth Warren Is Running for P...       article   \n",
       "4  Kim Jong-un, Ready to Meet Trump ‘at Any Time,...       article   \n",
       "\n",
       "               source  SENTIMENT_VALUE  relevance            pub_date   score  \\\n",
       "0  The New York Times              1.0      0.002 2019-01-02 22:17:38  0.0020   \n",
       "1  The New York Times              1.0      0.060 2019-01-02 17:00:47  0.0600   \n",
       "2  The New York Times             -0.8      0.002 2019-01-02 12:37:37 -0.0016   \n",
       "3  The New York Times              0.9      0.060 2019-01-01 05:18:48  0.0540   \n",
       "4  The New York Times              1.0      0.060 2019-01-01 01:44:15  0.0600   \n",
       "\n",
       "    timestamp_rounded  \n",
       "0 2019-01-02 22:17:00  \n",
       "1 2019-01-02 17:00:00  \n",
       "2 2019-01-02 12:37:00  \n",
       "3 2019-01-01 05:18:00  \n",
       "4 2019-01-01 01:44:00  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert news to minute by minute dataset\n",
    "news_data = news_raw[['content','headline','document_type','source','SENTIMENT_VALUE','relevance','pub_date','score']]\n",
    "news_data['pub_date'] =  pd.to_datetime(news_data['pub_date'], format='%Y-%m-%d %H:%M:%S')\n",
    "\n",
    "def round_timestamp(t):\n",
    "    t = datetime.datetime(t.year, t.month, t.day, t.hour, t.minute, 0)  \n",
    "    return t\n",
    "\n",
    "news_data['timestamp_rounded'] = news_data['pub_date'].apply(round_timestamp)\n",
    "news_data.to_csv('dataset/to_visualize/jan-feb-mar.csv')\n",
    "\n",
    "\n",
    "news_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp_rounded</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-01-01 00:36:00</th>\n",
       "      <td>-0.0120</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-01 00:45:00</th>\n",
       "      <td>-0.0600</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-01 01:04:00</th>\n",
       "      <td>-0.0018</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-01 01:44:00</th>\n",
       "      <td>0.0600</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-01 05:18:00</th>\n",
       "      <td>0.0540</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-01 08:24:00</th>\n",
       "      <td>-0.0014</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-01 10:00:00</th>\n",
       "      <td>0.1180</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-01 13:37:00</th>\n",
       "      <td>-0.0020</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-01 15:28:00</th>\n",
       "      <td>0.0600</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-01-01 15:43:00</th>\n",
       "      <td>-0.0540</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      score      \n",
       "                        sum count\n",
       "timestamp_rounded                \n",
       "2019-01-01 00:36:00 -0.0120     1\n",
       "2019-01-01 00:45:00 -0.0600     1\n",
       "2019-01-01 01:04:00 -0.0018     1\n",
       "2019-01-01 01:44:00  0.0600     1\n",
       "2019-01-01 05:18:00  0.0540     1\n",
       "2019-01-01 08:24:00 -0.0014     1\n",
       "2019-01-01 10:00:00  0.1180     3\n",
       "2019-01-01 13:37:00 -0.0020     1\n",
       "2019-01-01 15:28:00  0.0600     1\n",
       "2019-01-01 15:43:00 -0.0540     1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Aggregate news_data\n",
    "# news_data['value'] = news_data['SENTIMENT_VALUE'] *  news_data['relevance'] \n",
    "# news_data.groupby('pub_date_rounded').agg({'SENTIMENT_VALUE':['sum', 'max','count'], \n",
    "#                          'relevance': lambda x: x.max() - x.min()})\n",
    "# 2019-01-01 10:00:00\t\n",
    "news_data_aggregated = news_data.groupby('timestamp_rounded').agg({'score':['sum', 'count']})\n",
    "news_data_aggregated.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Close</th>\n",
       "      <th>Date</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Open</th>\n",
       "      <th>Time</th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Volume</th>\n",
       "      <th>id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>timestamp_rounded</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3698.00</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>3698.79</td>\n",
       "      <td>3693.85</td>\n",
       "      <td>3693.85</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.491289</td>\n",
       "      <td>3676576.0</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3690.65</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>3694.72</td>\n",
       "      <td>3690.65</td>\n",
       "      <td>3694.72</td>\n",
       "      <td>00:01:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.500151</td>\n",
       "      <td>3676577.0</td>\n",
       "      <td>2019-01-01 00:01:00</td>\n",
       "      <td>2019-01-01 00:01:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3686.62</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>3689.73</td>\n",
       "      <td>3686.62</td>\n",
       "      <td>3689.73</td>\n",
       "      <td>00:02:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.965966</td>\n",
       "      <td>3676578.0</td>\n",
       "      <td>2019-01-01 00:02:00</td>\n",
       "      <td>2019-01-01 00:02:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3692.35</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>3692.85</td>\n",
       "      <td>3688.32</td>\n",
       "      <td>3692.85</td>\n",
       "      <td>00:03:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.296662</td>\n",
       "      <td>3676579.0</td>\n",
       "      <td>2019-01-01 00:03:00</td>\n",
       "      <td>2019-01-01 00:03:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3690.34</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>3692.35</td>\n",
       "      <td>3690.34</td>\n",
       "      <td>3692.35</td>\n",
       "      <td>00:04:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.111622</td>\n",
       "      <td>3676580.0</td>\n",
       "      <td>2019-01-01 00:04:00</td>\n",
       "      <td>2019-01-01 00:04:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Close        Date     High      Low     Open      Time  Unnamed: 0  \\\n",
       "0  3698.00  2019-01-01  3698.79  3693.85  3693.85  00:00:00         NaN   \n",
       "1  3690.65  2019-01-01  3694.72  3690.65  3694.72  00:01:00         NaN   \n",
       "2  3686.62  2019-01-01  3689.73  3686.62  3689.73  00:02:00         NaN   \n",
       "3  3692.35  2019-01-01  3692.85  3688.32  3692.85  00:03:00         NaN   \n",
       "4  3690.34  2019-01-01  3692.35  3690.34  3692.35  00:04:00         NaN   \n",
       "\n",
       "     Volume         id           timestamp   timestamp_rounded  \n",
       "0  5.491289  3676576.0 2019-01-01 00:00:00 2019-01-01 00:00:00  \n",
       "1  9.500151  3676577.0 2019-01-01 00:01:00 2019-01-01 00:01:00  \n",
       "2  0.965966  3676578.0 2019-01-01 00:02:00 2019-01-01 00:02:00  \n",
       "3  0.296662  3676579.0 2019-01-01 00:03:00 2019-01-01 00:03:00  \n",
       "4  0.111622  3676580.0 2019-01-01 00:04:00 2019-01-01 00:04:00  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# process price_data\n",
    "price_raw['timestamp'] = price_raw['Date'] + ' ' + price_raw['Time']\n",
    "price_raw['timestamp'] =  pd.to_datetime(price_raw['timestamp'], format='%Y-%m-%d %H:%M:%S')\n",
    "price_raw['timestamp_rounded'] = price_raw['timestamp'].apply(round_timestamp)\n",
    "\n",
    "price_raw.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Close                92623\n",
       "Date                 92623\n",
       "High                 92623\n",
       "Low                  92623\n",
       "Open                 92623\n",
       "Time                 92623\n",
       "Unnamed: 0           52481\n",
       "Volume               92623\n",
       "id                   40142\n",
       "timestamp            92623\n",
       "timestamp_rounded    92623\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_data_aggregated = price_raw.groupby('timestamp_rounded').agg({\n",
    "    'Open':['mean'],\n",
    "    'High':['max'],\n",
    "    'Low':['min'],\n",
    "    'Close':['mean'],\n",
    "    'Volume':['sum'] \n",
    "})\n",
    "\n",
    "price_raw.count()"
   ]
  },
  {
   "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>Timestamp_Rounded</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Volume</th>\n",
       "      <th>Score</th>\n",
       "      <th>news_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-01 00:00:00</td>\n",
       "      <td>3693.85</td>\n",
       "      <td>3698.79</td>\n",
       "      <td>3693.85</td>\n",
       "      <td>3698.00</td>\n",
       "      <td>5.491289</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-01-01 00:01:00</td>\n",
       "      <td>3694.72</td>\n",
       "      <td>3694.72</td>\n",
       "      <td>3690.65</td>\n",
       "      <td>3690.65</td>\n",
       "      <td>9.500151</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-01-01 00:02:00</td>\n",
       "      <td>3689.73</td>\n",
       "      <td>3689.73</td>\n",
       "      <td>3686.62</td>\n",
       "      <td>3686.62</td>\n",
       "      <td>0.965966</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-01-01 00:03:00</td>\n",
       "      <td>3692.85</td>\n",
       "      <td>3692.85</td>\n",
       "      <td>3688.32</td>\n",
       "      <td>3692.35</td>\n",
       "      <td>0.296662</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-01-01 00:04:00</td>\n",
       "      <td>3692.35</td>\n",
       "      <td>3692.35</td>\n",
       "      <td>3690.34</td>\n",
       "      <td>3690.34</td>\n",
       "      <td>0.111622</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-01-01 00:05:00</td>\n",
       "      <td>3690.40</td>\n",
       "      <td>3690.85</td>\n",
       "      <td>3690.40</td>\n",
       "      <td>3690.85</td>\n",
       "      <td>2.247676</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2019-01-01 00:06:00</td>\n",
       "      <td>3688.61</td>\n",
       "      <td>3688.61</td>\n",
       "      <td>3687.08</td>\n",
       "      <td>3687.08</td>\n",
       "      <td>1.556315</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2019-01-01 00:07:00</td>\n",
       "      <td>3684.59</td>\n",
       "      <td>3685.52</td>\n",
       "      <td>3684.59</td>\n",
       "      <td>3684.77</td>\n",
       "      <td>0.823009</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2019-01-01 00:08:00</td>\n",
       "      <td>3684.97</td>\n",
       "      <td>3688.79</td>\n",
       "      <td>3683.45</td>\n",
       "      <td>3683.45</td>\n",
       "      <td>4.493121</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2019-01-01 00:09:00</td>\n",
       "      <td>3685.00</td>\n",
       "      <td>3685.12</td>\n",
       "      <td>3684.58</td>\n",
       "      <td>3685.12</td>\n",
       "      <td>1.077187</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2019-01-01 00:10:00</td>\n",
       "      <td>3685.46</td>\n",
       "      <td>3694.96</td>\n",
       "      <td>3684.91</td>\n",
       "      <td>3694.96</td>\n",
       "      <td>3.583453</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2019-01-01 00:15:00</td>\n",
       "      <td>3690.83</td>\n",
       "      <td>3690.85</td>\n",
       "      <td>3690.83</td>\n",
       "      <td>3690.85</td>\n",
       "      <td>0.443813</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2019-01-01 00:16:00</td>\n",
       "      <td>3690.61</td>\n",
       "      <td>3690.61</td>\n",
       "      <td>3687.96</td>\n",
       "      <td>3687.96</td>\n",
       "      <td>0.150000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2019-01-01 00:18:00</td>\n",
       "      <td>3690.15</td>\n",
       "      <td>3690.15</td>\n",
       "      <td>3690.15</td>\n",
       "      <td>3690.15</td>\n",
       "      <td>0.005000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2019-01-01 00:20:00</td>\n",
       "      <td>3688.75</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>3688.75</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>0.922095</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2019-01-01 00:22:00</td>\n",
       "      <td>3688.35</td>\n",
       "      <td>3688.35</td>\n",
       "      <td>3688.35</td>\n",
       "      <td>3688.35</td>\n",
       "      <td>1.063004</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2019-01-01 00:23:00</td>\n",
       "      <td>3688.35</td>\n",
       "      <td>3688.35</td>\n",
       "      <td>3688.35</td>\n",
       "      <td>3688.35</td>\n",
       "      <td>0.102770</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2019-01-01 00:25:00</td>\n",
       "      <td>3684.81</td>\n",
       "      <td>3684.81</td>\n",
       "      <td>3684.81</td>\n",
       "      <td>3684.81</td>\n",
       "      <td>0.116025</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2019-01-01 00:26:00</td>\n",
       "      <td>3684.81</td>\n",
       "      <td>3688.35</td>\n",
       "      <td>3684.81</td>\n",
       "      <td>3688.35</td>\n",
       "      <td>0.162767</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2019-01-01 00:28:00</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>0.018166</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2019-01-01 00:29:00</td>\n",
       "      <td>3686.27</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>3686.27</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>0.503600</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2019-01-01 00:30:00</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>2.339559</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2019-01-01 00:31:00</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>3688.85</td>\n",
       "      <td>12.912918</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2019-01-01 00:32:00</td>\n",
       "      <td>3689.10</td>\n",
       "      <td>3693.98</td>\n",
       "      <td>3689.10</td>\n",
       "      <td>3693.98</td>\n",
       "      <td>4.554418</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2019-01-01 00:33:00</td>\n",
       "      <td>3693.73</td>\n",
       "      <td>3698.50</td>\n",
       "      <td>3693.73</td>\n",
       "      <td>3697.06</td>\n",
       "      <td>5.404000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2019-01-01 00:34:00</td>\n",
       "      <td>3697.06</td>\n",
       "      <td>3698.49</td>\n",
       "      <td>3694.57</td>\n",
       "      <td>3698.49</td>\n",
       "      <td>0.566454</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2019-01-01 00:35:00</td>\n",
       "      <td>3697.84</td>\n",
       "      <td>3697.85</td>\n",
       "      <td>3695.58</td>\n",
       "      <td>3695.58</td>\n",
       "      <td>2.534310</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2019-01-01 00:36:00</td>\n",
       "      <td>3695.58</td>\n",
       "      <td>3696.87</td>\n",
       "      <td>3693.14</td>\n",
       "      <td>3696.87</td>\n",
       "      <td>4.185164</td>\n",
       "      <td>-0.0120</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2019-01-01 00:38:00</td>\n",
       "      <td>3695.26</td>\n",
       "      <td>3695.26</td>\n",
       "      <td>3695.26</td>\n",
       "      <td>3695.26</td>\n",
       "      <td>0.197947</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2019-01-01 00:39:00</td>\n",
       "      <td>3696.87</td>\n",
       "      <td>3696.87</td>\n",
       "      <td>3696.87</td>\n",
       "      <td>3696.87</td>\n",
       "      <td>0.045916</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93451</th>\n",
       "      <td>2019-03-29 23:50:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0020</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93452</th>\n",
       "      <td>2019-03-29 23:53:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93453</th>\n",
       "      <td>2019-03-29 23:58:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0020</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93454</th>\n",
       "      <td>2019-03-30 01:10:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0300</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93455</th>\n",
       "      <td>2019-03-30 01:21:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93456</th>\n",
       "      <td>2019-03-30 06:57:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93457</th>\n",
       "      <td>2019-03-30 07:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93458</th>\n",
       "      <td>2019-03-30 08:25:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0020</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93459</th>\n",
       "      <td>2019-03-30 09:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93460</th>\n",
       "      <td>2019-03-30 11:58:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0420</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93461</th>\n",
       "      <td>2019-03-30 12:17:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0020</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93462</th>\n",
       "      <td>2019-03-30 13:24:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0600</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93463</th>\n",
       "      <td>2019-03-30 15:30:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0020</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93464</th>\n",
       "      <td>2019-03-30 15:50:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0600</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93465</th>\n",
       "      <td>2019-03-30 16:46:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0240</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93466</th>\n",
       "      <td>2019-03-30 16:52:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0600</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93467</th>\n",
       "      <td>2019-03-30 19:33:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0020</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93468</th>\n",
       "      <td>2019-03-31 00:21:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0480</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93469</th>\n",
       "      <td>2019-03-31 00:33:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93470</th>\n",
       "      <td>2019-03-31 11:06:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0300</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93471</th>\n",
       "      <td>2019-03-31 13:06:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0020</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93472</th>\n",
       "      <td>2019-03-31 15:30:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0480</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93473</th>\n",
       "      <td>2019-03-31 17:03:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93474</th>\n",
       "      <td>2019-03-31 18:09:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0020</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93475</th>\n",
       "      <td>2019-03-31 18:56:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0016</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93476</th>\n",
       "      <td>2019-03-31 19:30:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93477</th>\n",
       "      <td>2019-03-31 19:59:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0600</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93478</th>\n",
       "      <td>2019-03-31 20:11:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0020</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93479</th>\n",
       "      <td>2019-03-31 21:01:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.0020</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93480</th>\n",
       "      <td>2019-03-31 21:56:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0020</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>93481 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Timestamp_Rounded     Open     High      Low    Close     Volume  \\\n",
       "0     2019-01-01 00:00:00  3693.85  3698.79  3693.85  3698.00   5.491289   \n",
       "1     2019-01-01 00:01:00  3694.72  3694.72  3690.65  3690.65   9.500151   \n",
       "2     2019-01-01 00:02:00  3689.73  3689.73  3686.62  3686.62   0.965966   \n",
       "3     2019-01-01 00:03:00  3692.85  3692.85  3688.32  3692.35   0.296662   \n",
       "4     2019-01-01 00:04:00  3692.35  3692.35  3690.34  3690.34   0.111622   \n",
       "5     2019-01-01 00:05:00  3690.40  3690.85  3690.40  3690.85   2.247676   \n",
       "6     2019-01-01 00:06:00  3688.61  3688.61  3687.08  3687.08   1.556315   \n",
       "7     2019-01-01 00:07:00  3684.59  3685.52  3684.59  3684.77   0.823009   \n",
       "8     2019-01-01 00:08:00  3684.97  3688.79  3683.45  3683.45   4.493121   \n",
       "9     2019-01-01 00:09:00  3685.00  3685.12  3684.58  3685.12   1.077187   \n",
       "10    2019-01-01 00:10:00  3685.46  3694.96  3684.91  3694.96   3.583453   \n",
       "11    2019-01-01 00:15:00  3690.83  3690.85  3690.83  3690.85   0.443813   \n",
       "12    2019-01-01 00:16:00  3690.61  3690.61  3687.96  3687.96   0.150000   \n",
       "13    2019-01-01 00:18:00  3690.15  3690.15  3690.15  3690.15   0.005000   \n",
       "14    2019-01-01 00:20:00  3688.75  3688.85  3688.75  3688.85   0.922095   \n",
       "15    2019-01-01 00:22:00  3688.35  3688.35  3688.35  3688.35   1.063004   \n",
       "16    2019-01-01 00:23:00  3688.35  3688.35  3688.35  3688.35   0.102770   \n",
       "17    2019-01-01 00:25:00  3684.81  3684.81  3684.81  3684.81   0.116025   \n",
       "18    2019-01-01 00:26:00  3684.81  3688.35  3684.81  3688.35   0.162767   \n",
       "19    2019-01-01 00:28:00  3688.85  3688.85  3688.85  3688.85   0.018166   \n",
       "20    2019-01-01 00:29:00  3686.27  3688.85  3686.27  3688.85   0.503600   \n",
       "21    2019-01-01 00:30:00  3688.85  3688.85  3688.85  3688.85   2.339559   \n",
       "22    2019-01-01 00:31:00  3688.85  3688.85  3688.85  3688.85  12.912918   \n",
       "23    2019-01-01 00:32:00  3689.10  3693.98  3689.10  3693.98   4.554418   \n",
       "24    2019-01-01 00:33:00  3693.73  3698.50  3693.73  3697.06   5.404000   \n",
       "25    2019-01-01 00:34:00  3697.06  3698.49  3694.57  3698.49   0.566454   \n",
       "26    2019-01-01 00:35:00  3697.84  3697.85  3695.58  3695.58   2.534310   \n",
       "27    2019-01-01 00:36:00  3695.58  3696.87  3693.14  3696.87   4.185164   \n",
       "28    2019-01-01 00:38:00  3695.26  3695.26  3695.26  3695.26   0.197947   \n",
       "29    2019-01-01 00:39:00  3696.87  3696.87  3696.87  3696.87   0.045916   \n",
       "...                   ...      ...      ...      ...      ...        ...   \n",
       "93451 2019-03-29 23:50:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93452 2019-03-29 23:53:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93453 2019-03-29 23:58:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93454 2019-03-30 01:10:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93455 2019-03-30 01:21:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93456 2019-03-30 06:57:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93457 2019-03-30 07:00:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93458 2019-03-30 08:25:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93459 2019-03-30 09:00:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93460 2019-03-30 11:58:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93461 2019-03-30 12:17:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93462 2019-03-30 13:24:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93463 2019-03-30 15:30:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93464 2019-03-30 15:50:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93465 2019-03-30 16:46:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93466 2019-03-30 16:52:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93467 2019-03-30 19:33:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93468 2019-03-31 00:21:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93469 2019-03-31 00:33:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93470 2019-03-31 11:06:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93471 2019-03-31 13:06:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93472 2019-03-31 15:30:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93473 2019-03-31 17:03:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93474 2019-03-31 18:09:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93475 2019-03-31 18:56:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93476 2019-03-31 19:30:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93477 2019-03-31 19:59:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93478 2019-03-31 20:11:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93479 2019-03-31 21:01:00      NaN      NaN      NaN      NaN        NaN   \n",
       "93480 2019-03-31 21:56:00      NaN      NaN      NaN      NaN        NaN   \n",
       "\n",
       "        Score  news_count  \n",
       "0      0.0000         0.0  \n",
       "1      0.0000         0.0  \n",
       "2      0.0000         0.0  \n",
       "3      0.0000         0.0  \n",
       "4      0.0000         0.0  \n",
       "5      0.0000         0.0  \n",
       "6      0.0000         0.0  \n",
       "7      0.0000         0.0  \n",
       "8      0.0000         0.0  \n",
       "9      0.0000         0.0  \n",
       "10     0.0000         0.0  \n",
       "11     0.0000         0.0  \n",
       "12     0.0000         0.0  \n",
       "13     0.0000         0.0  \n",
       "14     0.0000         0.0  \n",
       "15     0.0000         0.0  \n",
       "16     0.0000         0.0  \n",
       "17     0.0000         0.0  \n",
       "18     0.0000         0.0  \n",
       "19     0.0000         0.0  \n",
       "20     0.0000         0.0  \n",
       "21     0.0000         0.0  \n",
       "22     0.0000         0.0  \n",
       "23     0.0000         0.0  \n",
       "24     0.0000         0.0  \n",
       "25     0.0000         0.0  \n",
       "26     0.0000         0.0  \n",
       "27    -0.0120         1.0  \n",
       "28     0.0000         0.0  \n",
       "29     0.0000         0.0  \n",
       "...       ...         ...  \n",
       "93451  0.0020         1.0  \n",
       "93452  0.0600         1.0  \n",
       "93453 -0.0020         1.0  \n",
       "93454  0.0300         1.0  \n",
       "93455  0.0600         1.0  \n",
       "93456  0.0600         1.0  \n",
       "93457  0.0600         1.0  \n",
       "93458 -0.0020         1.0  \n",
       "93459  0.0600         1.0  \n",
       "93460 -0.0420         1.0  \n",
       "93461 -0.0020         1.0  \n",
       "93462 -0.0600         1.0  \n",
       "93463 -0.0020         1.0  \n",
       "93464 -0.0600         1.0  \n",
       "93465  0.0240         1.0  \n",
       "93466 -0.0600         1.0  \n",
       "93467 -0.0020         1.0  \n",
       "93468 -0.0480         1.0  \n",
       "93469  0.0600         1.0  \n",
       "93470 -0.0300         1.0  \n",
       "93471 -0.0020         1.0  \n",
       "93472  0.0480         1.0  \n",
       "93473  0.0600         1.0  \n",
       "93474 -0.0020         1.0  \n",
       "93475 -0.0016         1.0  \n",
       "93476  0.0600         1.0  \n",
       "93477  0.0600         1.0  \n",
       "93478 -0.0020         1.0  \n",
       "93479 -0.0020         1.0  \n",
       "93480  0.0020         1.0  \n",
       "\n",
       "[93481 rows x 8 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_data = price_data_aggregated.join(news_data_aggregated, how='outer', on = ['timestamp_rounded'])\n",
    "final_data.count()\n",
    "final_data = final_data.reset_index()\n",
    "final_data.columns = ['Timestamp_Rounded', 'Open','High','Low','Close','Volume','Score','news_count']\n",
    "# Fill null values for news with 0 (neutrial news ?!)\n",
    "final_data['news_count'] = final_data['news_count'].fillna(0)\n",
    "final_data['Score'] = final_data['Score'].fillna(0)\n",
    "final_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_data.to_csv('dataset/aggregated/min_by_min/jan-feb-mar.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}