3-merge-pair-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b5df3fb2",
   "metadata": {},
   "source": [
    "At this stage, we will prepare the downloaded data for individual pair analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "dd1560fc",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:39.750088Z",
     "start_time": "2022-11-01T17:09:39.730081Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59387b5c",
   "metadata": {},
   "source": [
    "## IMPORT DATA"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51500712",
   "metadata": {},
   "source": [
    "#### SHOW SAVED PAIRS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "cdf7c618",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:39.797083Z",
     "start_time": "2022-11-01T17:09:39.754081Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          DUK       VST       WEC     WTRG       XEL\n",
      "AEE  0.032540       NaN       NaN      NaN       NaN\n",
      "AEP       NaN  0.033961       NaN      NaN       NaN\n",
      "CMS  0.000526       NaN       NaN      NaN       NaN\n",
      "CNP       NaN  0.017855       NaN      NaN       NaN\n",
      "ED        NaN  0.003137       NaN      NaN       NaN\n",
      "OGE       NaN       NaN       NaN      NaN  0.031319\n",
      "RUN       NaN       NaN       NaN  0.04778       NaN\n",
      "VST       NaN       NaN  0.047495      NaN       NaN\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('AEE', 'DUK'),\n",
       " ('AEP', 'VST'),\n",
       " ('CMS', 'DUK'),\n",
       " ('CNP', 'VST'),\n",
       " ('ED', 'VST'),\n",
       " ('OGE', 'XEL'),\n",
       " ('RUN', 'WTRG'),\n",
       " ('VST', 'WEC')]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get dataframe from the previous step\n",
    "df = pd.read_pickle(\"pairs_to_download.pkl\")\n",
    "print(df)\n",
    "\n",
    "x_list = df.index.tolist()\n",
    "y_list = df.columns.tolist()\n",
    "\n",
    "new_pairs_list = [] # list of pairs as tuples (A,B)\n",
    "\n",
    "for i in range(len(x_list)):\n",
    "    for j in range(len(y_list)):\n",
    "        if df.iloc[i,j]>0:\n",
    "            new_pairs_list.append((x_list[i], y_list[j]))\n",
    "\n",
    "new_pairs_list"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69a79ba4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-10-18T16:38:03.249512Z",
     "start_time": "2022-10-18T16:38:03.236509Z"
    }
   },
   "source": [
    "#### SELECT TICKERS & RESOLUTION TO READ DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "e5a12948",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:39.829082Z",
     "start_time": "2022-11-01T17:09:39.800081Z"
    }
   },
   "outputs": [],
   "source": [
    "# PICK FILENAME FORMAT\n",
    "# \"Y\": \"MA_1D.csv\" for downloads with yfinance\n",
    "# \"T\": \"NYSE_MA, 1D.csv\" for downloads from TradingView\n",
    "\n",
    "file_format = \"Y\"\n",
    "\n",
    "# SELECT TICKERS\n",
    "ticker1 =\"CNP\"\n",
    "ticker2 =\"VST\"\n",
    "\n",
    "# need exchange info if fileformat is T\n",
    "ticker1_exchange = 'NYSE'\n",
    "ticker2_exchange = 'NYSE'\n",
    "\n",
    "# SELECT RESOLUTION\n",
    "# 5m,30m,1h,1d\n",
    "resolution = '1D'\n",
    "\n",
    "if file_format == \"T\":\n",
    "    filename1 = \"data/\"+ ticker1_exchange.upper() +\"_\" + ticker1 + \", \" + resolution.upper() + \".csv\"\n",
    "    filename2 = \"data/\"+ ticker2_exchange.upper() +\"_\" + ticker2 + \", \" + resolution.upper() + \".csv\"\n",
    "elif file_format == \"Y\":\n",
    "    filename1 = \"data/\"+ ticker1 +\"_\" + resolution.upper() + \".csv\"\n",
    "    filename2 = \"data/\"+ ticker2 +\"_\" + resolution.upper() + \".csv\"\n",
    "else:\n",
    "    filename1 = \"data/NYSE_MA, 1D.csv\"\n",
    "    filename2 = \"data/NYSE_V, 1D.csv\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1b2f0b1",
   "metadata": {},
   "source": [
    "#### READ SAMPLE DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "70baeb2a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:39.923085Z",
     "start_time": "2022-11-01T17:09:39.835082Z"
    }
   },
   "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>time</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>ticker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-02-17 00:00:00-05:00</td>\n",
       "      <td>26.83</td>\n",
       "      <td>26.940001</td>\n",
       "      <td>26.42</td>\n",
       "      <td>26.809999</td>\n",
       "      <td>26.518515</td>\n",
       "      <td>7949100</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-03-07 00:00:00-05:00</td>\n",
       "      <td>28.91</td>\n",
       "      <td>29.049999</td>\n",
       "      <td>28.40</td>\n",
       "      <td>28.730000</td>\n",
       "      <td>28.417639</td>\n",
       "      <td>5326400</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        time   Open       High    Low      Close  Adj Close  \\\n",
       "0  2022-02-17 00:00:00-05:00  26.83  26.940001  26.42  26.809999  26.518515   \n",
       "1  2022-03-07 00:00:00-05:00  28.91  29.049999  28.40  28.730000  28.417639   \n",
       "\n",
       "    Volume ticker  \n",
       "0  7949100    CNP  \n",
       "1  5326400    CNP  "
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get a sample from file1\n",
    "import random\n",
    "p = 0.01  # 1% of all the lines\n",
    "df1_sample = pd.read_csv(filename1,header=0, skiprows=lambda i: i>0 and random.random() > p)\n",
    "df1_sample.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "70781cdd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:40.016082Z",
     "start_time": "2022-11-01T17:09:39.928081Z"
    }
   },
   "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>time</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>ticker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-04-01 00:00:00-04:00</td>\n",
       "      <td>23.309999</td>\n",
       "      <td>23.500</td>\n",
       "      <td>23.080000</td>\n",
       "      <td>23.440001</td>\n",
       "      <td>23.087934</td>\n",
       "      <td>4560100</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-10-21 00:00:00-04:00</td>\n",
       "      <td>21.480000</td>\n",
       "      <td>21.768</td>\n",
       "      <td>21.120001</td>\n",
       "      <td>21.750000</td>\n",
       "      <td>21.750000</td>\n",
       "      <td>4248900</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        time       Open    High        Low      Close  \\\n",
       "0  2022-04-01 00:00:00-04:00  23.309999  23.500  23.080000  23.440001   \n",
       "1  2022-10-21 00:00:00-04:00  21.480000  21.768  21.120001  21.750000   \n",
       "\n",
       "   Adj Close   Volume ticker  \n",
       "0  23.087934  4560100    VST  \n",
       "1  21.750000  4248900    VST  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get a sample from file2\n",
    "df2_sample = pd.read_csv(filename2,header=0, skiprows=lambda i: i>0 and random.random() > p)\n",
    "df2_sample.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2562dc17",
   "metadata": {},
   "source": [
    "#### GET ALL DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "a2d0bec5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:40.220840Z",
     "start_time": "2022-11-01T17:09:40.020611Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 252 entries, 0 to 251\n",
      "Data columns (total 8 columns):\n",
      " #   Column     Non-Null Count  Dtype  \n",
      "---  ------     --------------  -----  \n",
      " 0   time       252 non-null    object \n",
      " 1   Open       252 non-null    float64\n",
      " 2   High       252 non-null    float64\n",
      " 3   Low        252 non-null    float64\n",
      " 4   Close      252 non-null    float64\n",
      " 5   Adj Close  252 non-null    float64\n",
      " 6   Volume     252 non-null    int64  \n",
      " 7   ticker     252 non-null    object \n",
      "dtypes: float64(5), int64(1), object(2)\n",
      "memory usage: 15.9+ KB\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.read_csv(filename1, parse_dates=[\"time\"])\n",
    "df1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "594ab9a9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:40.409435Z",
     "start_time": "2022-11-01T17:09:40.229846Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 252 entries, 0 to 251\n",
      "Data columns (total 8 columns):\n",
      " #   Column     Non-Null Count  Dtype  \n",
      "---  ------     --------------  -----  \n",
      " 0   time       252 non-null    object \n",
      " 1   Open       252 non-null    float64\n",
      " 2   High       252 non-null    float64\n",
      " 3   Low        252 non-null    float64\n",
      " 4   Close      252 non-null    float64\n",
      " 5   Adj Close  252 non-null    float64\n",
      " 6   Volume     252 non-null    int64  \n",
      " 7   ticker     252 non-null    object \n",
      "dtypes: float64(5), int64(1), object(2)\n",
      "memory usage: 15.9+ KB\n"
     ]
    }
   ],
   "source": [
    "df2=pd.read_csv(filename2, parse_dates=[\"time\"])\n",
    "df2.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69e508fa",
   "metadata": {},
   "source": [
    "#### FILTER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "add922f1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:40.488984Z",
     "start_time": "2022-11-01T17:09:40.413431Z"
    }
   },
   "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>time</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>ticker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-11-01 00:00:00-04:00</td>\n",
       "      <td>26.120001</td>\n",
       "      <td>26.350000</td>\n",
       "      <td>26.040001</td>\n",
       "      <td>26.290001</td>\n",
       "      <td>25.675735</td>\n",
       "      <td>3730100</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-02 00:00:00-04:00</td>\n",
       "      <td>26.370001</td>\n",
       "      <td>26.370001</td>\n",
       "      <td>25.830000</td>\n",
       "      <td>26.190001</td>\n",
       "      <td>25.578075</td>\n",
       "      <td>5620500</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-03 00:00:00-04:00</td>\n",
       "      <td>26.049999</td>\n",
       "      <td>26.420000</td>\n",
       "      <td>26.010000</td>\n",
       "      <td>26.290001</td>\n",
       "      <td>25.675735</td>\n",
       "      <td>3350900</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-04 00:00:00-04:00</td>\n",
       "      <td>26.440001</td>\n",
       "      <td>26.740000</td>\n",
       "      <td>26.309999</td>\n",
       "      <td>26.500000</td>\n",
       "      <td>25.880831</td>\n",
       "      <td>3364800</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-11-05 00:00:00-04:00</td>\n",
       "      <td>26.709999</td>\n",
       "      <td>26.889999</td>\n",
       "      <td>26.430000</td>\n",
       "      <td>26.670000</td>\n",
       "      <td>26.046860</td>\n",
       "      <td>3179000</td>\n",
       "      <td>CNP</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>247</th>\n",
       "      <td>2022-10-25 00:00:00-04:00</td>\n",
       "      <td>27.200001</td>\n",
       "      <td>27.820000</td>\n",
       "      <td>27.200001</td>\n",
       "      <td>27.650000</td>\n",
       "      <td>27.650000</td>\n",
       "      <td>3556000</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>2022-10-26 00:00:00-04:00</td>\n",
       "      <td>27.830000</td>\n",
       "      <td>27.969999</td>\n",
       "      <td>27.549999</td>\n",
       "      <td>27.600000</td>\n",
       "      <td>27.600000</td>\n",
       "      <td>3132200</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>2022-10-27 00:00:00-04:00</td>\n",
       "      <td>27.889999</td>\n",
       "      <td>28.260000</td>\n",
       "      <td>27.840000</td>\n",
       "      <td>27.990000</td>\n",
       "      <td>27.990000</td>\n",
       "      <td>2833500</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>2022-10-28 00:00:00-04:00</td>\n",
       "      <td>28.070000</td>\n",
       "      <td>28.820000</td>\n",
       "      <td>28.070000</td>\n",
       "      <td>28.809999</td>\n",
       "      <td>28.809999</td>\n",
       "      <td>2803400</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>2022-10-31 00:00:00-04:00</td>\n",
       "      <td>28.799999</td>\n",
       "      <td>28.879999</td>\n",
       "      <td>28.430000</td>\n",
       "      <td>28.610001</td>\n",
       "      <td>28.610001</td>\n",
       "      <td>4470200</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>252 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          time       open       high        low      close  \\\n",
       "0    2021-11-01 00:00:00-04:00  26.120001  26.350000  26.040001  26.290001   \n",
       "1    2021-11-02 00:00:00-04:00  26.370001  26.370001  25.830000  26.190001   \n",
       "2    2021-11-03 00:00:00-04:00  26.049999  26.420000  26.010000  26.290001   \n",
       "3    2021-11-04 00:00:00-04:00  26.440001  26.740000  26.309999  26.500000   \n",
       "4    2021-11-05 00:00:00-04:00  26.709999  26.889999  26.430000  26.670000   \n",
       "..                         ...        ...        ...        ...        ...   \n",
       "247  2022-10-25 00:00:00-04:00  27.200001  27.820000  27.200001  27.650000   \n",
       "248  2022-10-26 00:00:00-04:00  27.830000  27.969999  27.549999  27.600000   \n",
       "249  2022-10-27 00:00:00-04:00  27.889999  28.260000  27.840000  27.990000   \n",
       "250  2022-10-28 00:00:00-04:00  28.070000  28.820000  28.070000  28.809999   \n",
       "251  2022-10-31 00:00:00-04:00  28.799999  28.879999  28.430000  28.610001   \n",
       "\n",
       "     adj close   volume ticker  \n",
       "0    25.675735  3730100    CNP  \n",
       "1    25.578075  5620500    CNP  \n",
       "2    25.675735  3350900    CNP  \n",
       "3    25.880831  3364800    CNP  \n",
       "4    26.046860  3179000    CNP  \n",
       "..         ...      ...    ...  \n",
       "247  27.650000  3556000    CNP  \n",
       "248  27.600000  3132200    CNP  \n",
       "249  27.990000  2833500    CNP  \n",
       "250  28.809999  2803400    CNP  \n",
       "251  28.610001  4470200    CNP  \n",
       "\n",
       "[252 rows x 8 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.columns = map(str.lower, df1.columns)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "b93c3b81",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:40.566988Z",
     "start_time": "2022-11-01T17:09:40.492987Z"
    }
   },
   "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>datetime</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-11-01 04:00:00+00:00</td>\n",
       "      <td>26.290001</td>\n",
       "      <td>3730100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-02 04:00:00+00:00</td>\n",
       "      <td>26.190001</td>\n",
       "      <td>5620500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-03 04:00:00+00:00</td>\n",
       "      <td>26.290001</td>\n",
       "      <td>3350900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-04 04:00:00+00:00</td>\n",
       "      <td>26.500000</td>\n",
       "      <td>3364800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-11-05 04:00:00+00:00</td>\n",
       "      <td>26.670000</td>\n",
       "      <td>3179000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>2022-10-25 04:00:00+00:00</td>\n",
       "      <td>27.650000</td>\n",
       "      <td>3556000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>2022-10-26 04:00:00+00:00</td>\n",
       "      <td>27.600000</td>\n",
       "      <td>3132200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>2022-10-27 04:00:00+00:00</td>\n",
       "      <td>27.990000</td>\n",
       "      <td>2833500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>2022-10-28 04:00:00+00:00</td>\n",
       "      <td>28.809999</td>\n",
       "      <td>2803400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>2022-10-31 04:00:00+00:00</td>\n",
       "      <td>28.610001</td>\n",
       "      <td>4470200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>252 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     datetime      close   volume\n",
       "0   2021-11-01 04:00:00+00:00  26.290001  3730100\n",
       "1   2021-11-02 04:00:00+00:00  26.190001  5620500\n",
       "2   2021-11-03 04:00:00+00:00  26.290001  3350900\n",
       "3   2021-11-04 04:00:00+00:00  26.500000  3364800\n",
       "4   2021-11-05 04:00:00+00:00  26.670000  3179000\n",
       "..                        ...        ...      ...\n",
       "247 2022-10-25 04:00:00+00:00  27.650000  3556000\n",
       "248 2022-10-26 04:00:00+00:00  27.600000  3132200\n",
       "249 2022-10-27 04:00:00+00:00  27.990000  2833500\n",
       "250 2022-10-28 04:00:00+00:00  28.809999  2803400\n",
       "251 2022-10-31 04:00:00+00:00  28.610001  4470200\n",
       "\n",
       "[252 rows x 3 columns]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.columns = map(str.lower, df1.columns)\n",
    "df1 = df1[['time','close','volume']]\n",
    "df1 = df1.rename(columns={\"time\": \"datetime\"})\n",
    "df1['datetime'] = pd.to_datetime(df1['datetime'], utc=True)  # be sure that you get datetime\n",
    "\n",
    "# we don't want null or zero values (price & volume), use forward fill\n",
    "df1['close'] = df1['close'].fillna(method='ffill')\n",
    "df1['volume'] = df1['volume'].replace(to_replace=0, method='ffill')\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "635faa26",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:40.646662Z",
     "start_time": "2022-11-01T17:09:40.587650Z"
    }
   },
   "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>datetime</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-11-01 04:00:00+00:00</td>\n",
       "      <td>20.010000</td>\n",
       "      <td>2756500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-02 04:00:00+00:00</td>\n",
       "      <td>19.870001</td>\n",
       "      <td>4006000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-03 04:00:00+00:00</td>\n",
       "      <td>19.680000</td>\n",
       "      <td>2460100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-04 04:00:00+00:00</td>\n",
       "      <td>19.209999</td>\n",
       "      <td>6093500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-11-05 04:00:00+00:00</td>\n",
       "      <td>20.500000</td>\n",
       "      <td>9136100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>2022-10-25 04:00:00+00:00</td>\n",
       "      <td>21.990000</td>\n",
       "      <td>4838400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>2022-10-26 04:00:00+00:00</td>\n",
       "      <td>21.900000</td>\n",
       "      <td>3419400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>2022-10-27 04:00:00+00:00</td>\n",
       "      <td>21.980000</td>\n",
       "      <td>3738400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>2022-10-28 04:00:00+00:00</td>\n",
       "      <td>22.980000</td>\n",
       "      <td>4167400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>2022-10-31 04:00:00+00:00</td>\n",
       "      <td>22.969999</td>\n",
       "      <td>4428700</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>252 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     datetime      close   volume\n",
       "0   2021-11-01 04:00:00+00:00  20.010000  2756500\n",
       "1   2021-11-02 04:00:00+00:00  19.870001  4006000\n",
       "2   2021-11-03 04:00:00+00:00  19.680000  2460100\n",
       "3   2021-11-04 04:00:00+00:00  19.209999  6093500\n",
       "4   2021-11-05 04:00:00+00:00  20.500000  9136100\n",
       "..                        ...        ...      ...\n",
       "247 2022-10-25 04:00:00+00:00  21.990000  4838400\n",
       "248 2022-10-26 04:00:00+00:00  21.900000  3419400\n",
       "249 2022-10-27 04:00:00+00:00  21.980000  3738400\n",
       "250 2022-10-28 04:00:00+00:00  22.980000  4167400\n",
       "251 2022-10-31 04:00:00+00:00  22.969999  4428700\n",
       "\n",
       "[252 rows x 3 columns]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.columns = map(str.lower, df2.columns)\n",
    "df2 = df2[['time','close','volume']]\n",
    "df2 = df2.rename(columns={\"time\": \"datetime\"})\n",
    "df2['datetime'] = pd.to_datetime(df2['datetime'], utc=True)  # be sure that you get datetime\n",
    "\n",
    "# we don't want null or zero values (price & volume), use forward fill\n",
    "df2['close'] = df2['close'].fillna(method='ffill')\n",
    "df2['volume'] = df2['volume'].replace(to_replace=0, method='ffill')\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d432ef3",
   "metadata": {},
   "source": [
    "#### ADD TICKER INFORMATION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "a5195860",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:40.676630Z",
     "start_time": "2022-11-01T17:09:40.658626Z"
    }
   },
   "outputs": [],
   "source": [
    "# if not added already in the previous steps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "0c10c4dd",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:40.723626Z",
     "start_time": "2022-11-01T17:09:40.680628Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datetime</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>ticker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-11-01 04:00:00+00:00</td>\n",
       "      <td>26.290001</td>\n",
       "      <td>3730100</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-02 04:00:00+00:00</td>\n",
       "      <td>26.190001</td>\n",
       "      <td>5620500</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-03 04:00:00+00:00</td>\n",
       "      <td>26.290001</td>\n",
       "      <td>3350900</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-04 04:00:00+00:00</td>\n",
       "      <td>26.500000</td>\n",
       "      <td>3364800</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-11-05 04:00:00+00:00</td>\n",
       "      <td>26.670000</td>\n",
       "      <td>3179000</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   datetime      close   volume ticker\n",
       "0 2021-11-01 04:00:00+00:00  26.290001  3730100    CNP\n",
       "1 2021-11-02 04:00:00+00:00  26.190001  5620500    CNP\n",
       "2 2021-11-03 04:00:00+00:00  26.290001  3350900    CNP\n",
       "3 2021-11-04 04:00:00+00:00  26.500000  3364800    CNP\n",
       "4 2021-11-05 04:00:00+00:00  26.670000  3179000    CNP"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1_1 = df1.copy();\n",
    "if not 'ticker' in df1_1.columns:\n",
    "    df1_1['ticker'] = ticker1;\n",
    "df1_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "14d13947",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:40.771645Z",
     "start_time": "2022-11-01T17:09:40.728628Z"
    }
   },
   "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>datetime</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>ticker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-11-01 04:00:00+00:00</td>\n",
       "      <td>20.010000</td>\n",
       "      <td>2756500</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-02 04:00:00+00:00</td>\n",
       "      <td>19.870001</td>\n",
       "      <td>4006000</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-03 04:00:00+00:00</td>\n",
       "      <td>19.680000</td>\n",
       "      <td>2460100</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-04 04:00:00+00:00</td>\n",
       "      <td>19.209999</td>\n",
       "      <td>6093500</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-11-05 04:00:00+00:00</td>\n",
       "      <td>20.500000</td>\n",
       "      <td>9136100</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   datetime      close   volume ticker\n",
       "0 2021-11-01 04:00:00+00:00  20.010000  2756500    VST\n",
       "1 2021-11-02 04:00:00+00:00  19.870001  4006000    VST\n",
       "2 2021-11-03 04:00:00+00:00  19.680000  2460100    VST\n",
       "3 2021-11-04 04:00:00+00:00  19.209999  6093500    VST\n",
       "4 2021-11-05 04:00:00+00:00  20.500000  9136100    VST"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2_1 = df2.copy();\n",
    "if not 'ticker' in df2_1.columns:\n",
    "    df2_1['ticker'] = ticker2;\n",
    "df2_1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8a6e65f",
   "metadata": {},
   "source": [
    "## METHOD 1 (CONCATENATE  & KEEP ALL ROWS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "4fa6dde1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:40.834594Z",
     "start_time": "2022-11-01T17:09:40.784065Z"
    },
    "scrolled": true
   },
   "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>datetime</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>ticker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-11-01 04:00:00+00:00</td>\n",
       "      <td>20.010000</td>\n",
       "      <td>2756500</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-02 04:00:00+00:00</td>\n",
       "      <td>19.870001</td>\n",
       "      <td>4006000</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-03 04:00:00+00:00</td>\n",
       "      <td>19.680000</td>\n",
       "      <td>2460100</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-04 04:00:00+00:00</td>\n",
       "      <td>19.209999</td>\n",
       "      <td>6093500</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-11-05 04:00:00+00:00</td>\n",
       "      <td>20.500000</td>\n",
       "      <td>9136100</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499</th>\n",
       "      <td>2022-10-25 04:00:00+00:00</td>\n",
       "      <td>27.650000</td>\n",
       "      <td>3556000</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>2022-10-26 04:00:00+00:00</td>\n",
       "      <td>27.600000</td>\n",
       "      <td>3132200</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>2022-10-27 04:00:00+00:00</td>\n",
       "      <td>27.990000</td>\n",
       "      <td>2833500</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>2022-10-28 04:00:00+00:00</td>\n",
       "      <td>28.809999</td>\n",
       "      <td>2803400</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>2022-10-31 04:00:00+00:00</td>\n",
       "      <td>28.610001</td>\n",
       "      <td>4470200</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>504 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     datetime      close   volume ticker\n",
       "0   2021-11-01 04:00:00+00:00  20.010000  2756500    VST\n",
       "1   2021-11-02 04:00:00+00:00  19.870001  4006000    VST\n",
       "2   2021-11-03 04:00:00+00:00  19.680000  2460100    VST\n",
       "3   2021-11-04 04:00:00+00:00  19.209999  6093500    VST\n",
       "4   2021-11-05 04:00:00+00:00  20.500000  9136100    VST\n",
       "..                        ...        ...      ...    ...\n",
       "499 2022-10-25 04:00:00+00:00  27.650000  3556000    CNP\n",
       "500 2022-10-26 04:00:00+00:00  27.600000  3132200    CNP\n",
       "501 2022-10-27 04:00:00+00:00  27.990000  2833500    CNP\n",
       "502 2022-10-28 04:00:00+00:00  28.809999  2803400    CNP\n",
       "503 2022-10-31 04:00:00+00:00  28.610001  4470200    CNP\n",
       "\n",
       "[504 rows x 4 columns]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df12_c = pd.concat([df2_1,df1_1]).reset_index(drop=True) # need to drop old index values\n",
    "df12_c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "71720c56",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:40.882155Z",
     "start_time": "2022-11-01T17:09:40.842605Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datetime</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>ticker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-11-01 04:00:00+00:00</td>\n",
       "      <td>20.010000</td>\n",
       "      <td>2756500</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-02 04:00:00+00:00</td>\n",
       "      <td>19.870001</td>\n",
       "      <td>4006000</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-03 04:00:00+00:00</td>\n",
       "      <td>19.680000</td>\n",
       "      <td>2460100</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-04 04:00:00+00:00</td>\n",
       "      <td>19.209999</td>\n",
       "      <td>6093500</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-11-05 04:00:00+00:00</td>\n",
       "      <td>20.500000</td>\n",
       "      <td>9136100</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>2021-11-01 04:00:00+00:00</td>\n",
       "      <td>26.290001</td>\n",
       "      <td>3730100</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>2021-11-02 04:00:00+00:00</td>\n",
       "      <td>26.190001</td>\n",
       "      <td>5620500</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>2021-11-03 04:00:00+00:00</td>\n",
       "      <td>26.290001</td>\n",
       "      <td>3350900</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>2021-11-04 04:00:00+00:00</td>\n",
       "      <td>26.500000</td>\n",
       "      <td>3364800</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>2021-11-05 04:00:00+00:00</td>\n",
       "      <td>26.670000</td>\n",
       "      <td>3179000</td>\n",
       "      <td>CNP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     datetime      close   volume ticker\n",
       "0   2021-11-01 04:00:00+00:00  20.010000  2756500    VST\n",
       "1   2021-11-02 04:00:00+00:00  19.870001  4006000    VST\n",
       "2   2021-11-03 04:00:00+00:00  19.680000  2460100    VST\n",
       "3   2021-11-04 04:00:00+00:00  19.209999  6093500    VST\n",
       "4   2021-11-05 04:00:00+00:00  20.500000  9136100    VST\n",
       "252 2021-11-01 04:00:00+00:00  26.290001  3730100    CNP\n",
       "253 2021-11-02 04:00:00+00:00  26.190001  5620500    CNP\n",
       "254 2021-11-03 04:00:00+00:00  26.290001  3350900    CNP\n",
       "255 2021-11-04 04:00:00+00:00  26.500000  3364800    CNP\n",
       "256 2021-11-05 04:00:00+00:00  26.670000  3179000    CNP"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df12_c_agg = df12_c.groupby('DateTime').agg({'ticker':['close','Volume']})\n",
    "df12_c_agg = df12_c.groupby('ticker')\n",
    "df12_c_agg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "8563673d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:40.930208Z",
     "start_time": "2022-11-01T17:09:40.887146Z"
    },
    "scrolled": true
   },
   "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></th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ticker</th>\n",
       "      <th>datetime</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">CNP</th>\n",
       "      <th>2021-11-01 04:00:00+00:00</th>\n",
       "      <td>26.290001</td>\n",
       "      <td>3730100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-02 04:00:00+00:00</th>\n",
       "      <td>26.190001</td>\n",
       "      <td>5620500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-03 04:00:00+00:00</th>\n",
       "      <td>26.290001</td>\n",
       "      <td>3350900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-04 04:00:00+00:00</th>\n",
       "      <td>26.500000</td>\n",
       "      <td>3364800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-05 04:00:00+00:00</th>\n",
       "      <td>26.670000</td>\n",
       "      <td>3179000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      close   volume\n",
       "ticker datetime                                     \n",
       "CNP    2021-11-01 04:00:00+00:00  26.290001  3730100\n",
       "       2021-11-02 04:00:00+00:00  26.190001  5620500\n",
       "       2021-11-03 04:00:00+00:00  26.290001  3350900\n",
       "       2021-11-04 04:00:00+00:00  26.500000  3364800\n",
       "       2021-11-05 04:00:00+00:00  26.670000  3179000"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df12_c_1 = df12_c.set_index([\"ticker\", \"datetime\"]).sort_index() # set indexes\n",
    "df12_c_1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12b2e654",
   "metadata": {},
   "source": [
    "#### SLICE DATAFRAME TO GET TICKER DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "ce44977d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:40.977145Z",
     "start_time": "2022-11-01T17:09:40.937149Z"
    },
    "scrolled": true
   },
   "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>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>datetime</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021-11-01 04:00:00+00:00</th>\n",
       "      <td>26.290001</td>\n",
       "      <td>3730100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-02 04:00:00+00:00</th>\n",
       "      <td>26.190001</td>\n",
       "      <td>5620500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-03 04:00:00+00:00</th>\n",
       "      <td>26.290001</td>\n",
       "      <td>3350900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-04 04:00:00+00:00</th>\n",
       "      <td>26.500000</td>\n",
       "      <td>3364800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-05 04:00:00+00:00</th>\n",
       "      <td>26.670000</td>\n",
       "      <td>3179000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-25 04:00:00+00:00</th>\n",
       "      <td>27.650000</td>\n",
       "      <td>3556000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-26 04:00:00+00:00</th>\n",
       "      <td>27.600000</td>\n",
       "      <td>3132200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-27 04:00:00+00:00</th>\n",
       "      <td>27.990000</td>\n",
       "      <td>2833500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-28 04:00:00+00:00</th>\n",
       "      <td>28.809999</td>\n",
       "      <td>2803400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-31 04:00:00+00:00</th>\n",
       "      <td>28.610001</td>\n",
       "      <td>4470200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>252 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               close   volume\n",
       "datetime                                     \n",
       "2021-11-01 04:00:00+00:00  26.290001  3730100\n",
       "2021-11-02 04:00:00+00:00  26.190001  5620500\n",
       "2021-11-03 04:00:00+00:00  26.290001  3350900\n",
       "2021-11-04 04:00:00+00:00  26.500000  3364800\n",
       "2021-11-05 04:00:00+00:00  26.670000  3179000\n",
       "...                              ...      ...\n",
       "2022-10-25 04:00:00+00:00  27.650000  3556000\n",
       "2022-10-26 04:00:00+00:00  27.600000  3132200\n",
       "2022-10-27 04:00:00+00:00  27.990000  2833500\n",
       "2022-10-28 04:00:00+00:00  28.809999  2803400\n",
       "2022-10-31 04:00:00+00:00  28.610001  4470200\n",
       "\n",
       "[252 rows x 2 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df12_c_1.xs(ticker1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "16a379a1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:41.009148Z",
     "start_time": "2022-11-01T17:09:40.982148Z"
    },
    "scrolled": true
   },
   "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>volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>datetime</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021-11-01 04:00:00+00:00</th>\n",
       "      <td>20.010000</td>\n",
       "      <td>2756500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-02 04:00:00+00:00</th>\n",
       "      <td>19.870001</td>\n",
       "      <td>4006000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-03 04:00:00+00:00</th>\n",
       "      <td>19.680000</td>\n",
       "      <td>2460100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-04 04:00:00+00:00</th>\n",
       "      <td>19.209999</td>\n",
       "      <td>6093500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-05 04:00:00+00:00</th>\n",
       "      <td>20.500000</td>\n",
       "      <td>9136100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-25 04:00:00+00:00</th>\n",
       "      <td>21.990000</td>\n",
       "      <td>4838400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-26 04:00:00+00:00</th>\n",
       "      <td>21.900000</td>\n",
       "      <td>3419400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-27 04:00:00+00:00</th>\n",
       "      <td>21.980000</td>\n",
       "      <td>3738400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-28 04:00:00+00:00</th>\n",
       "      <td>22.980000</td>\n",
       "      <td>4167400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-31 04:00:00+00:00</th>\n",
       "      <td>22.969999</td>\n",
       "      <td>4428700</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>252 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               close   volume\n",
       "datetime                                     \n",
       "2021-11-01 04:00:00+00:00  20.010000  2756500\n",
       "2021-11-02 04:00:00+00:00  19.870001  4006000\n",
       "2021-11-03 04:00:00+00:00  19.680000  2460100\n",
       "2021-11-04 04:00:00+00:00  19.209999  6093500\n",
       "2021-11-05 04:00:00+00:00  20.500000  9136100\n",
       "...                              ...      ...\n",
       "2022-10-25 04:00:00+00:00  21.990000  4838400\n",
       "2022-10-26 04:00:00+00:00  21.900000  3419400\n",
       "2022-10-27 04:00:00+00:00  21.980000  3738400\n",
       "2022-10-28 04:00:00+00:00  22.980000  4167400\n",
       "2022-10-31 04:00:00+00:00  22.969999  4428700\n",
       "\n",
       "[252 rows x 2 columns]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df12_c_1.xs(ticker2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "7c58e4d5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:41.055147Z",
     "start_time": "2022-11-01T17:09:41.020154Z"
    }
   },
   "outputs": [],
   "source": [
    "# END OF METHOD 1, CONTINUE WITH METHOD 2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70ae30da",
   "metadata": {},
   "source": [
    "## METHOD 2 (MERGE TICKERS  & KEEP ONLY INNER TIME DATA)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "2a400060",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:41.100685Z",
     "start_time": "2022-11-01T17:09:41.059147Z"
    },
    "scrolled": true
   },
   "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>datetime</th>\n",
       "      <th>close_x</th>\n",
       "      <th>volume_x</th>\n",
       "      <th>ticker_x</th>\n",
       "      <th>close_y</th>\n",
       "      <th>volume_y</th>\n",
       "      <th>ticker_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-11-01 04:00:00+00:00</td>\n",
       "      <td>26.290001</td>\n",
       "      <td>3730100</td>\n",
       "      <td>CNP</td>\n",
       "      <td>20.010000</td>\n",
       "      <td>2756500</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-02 04:00:00+00:00</td>\n",
       "      <td>26.190001</td>\n",
       "      <td>5620500</td>\n",
       "      <td>CNP</td>\n",
       "      <td>19.870001</td>\n",
       "      <td>4006000</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-03 04:00:00+00:00</td>\n",
       "      <td>26.290001</td>\n",
       "      <td>3350900</td>\n",
       "      <td>CNP</td>\n",
       "      <td>19.680000</td>\n",
       "      <td>2460100</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-04 04:00:00+00:00</td>\n",
       "      <td>26.500000</td>\n",
       "      <td>3364800</td>\n",
       "      <td>CNP</td>\n",
       "      <td>19.209999</td>\n",
       "      <td>6093500</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-11-05 04:00:00+00:00</td>\n",
       "      <td>26.670000</td>\n",
       "      <td>3179000</td>\n",
       "      <td>CNP</td>\n",
       "      <td>20.500000</td>\n",
       "      <td>9136100</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   datetime    close_x  volume_x ticker_x    close_y  \\\n",
       "0 2021-11-01 04:00:00+00:00  26.290001   3730100      CNP  20.010000   \n",
       "1 2021-11-02 04:00:00+00:00  26.190001   5620500      CNP  19.870001   \n",
       "2 2021-11-03 04:00:00+00:00  26.290001   3350900      CNP  19.680000   \n",
       "3 2021-11-04 04:00:00+00:00  26.500000   3364800      CNP  19.209999   \n",
       "4 2021-11-05 04:00:00+00:00  26.670000   3179000      CNP  20.500000   \n",
       "\n",
       "   volume_y ticker_y  \n",
       "0   2756500      VST  \n",
       "1   4006000      VST  \n",
       "2   2460100      VST  \n",
       "3   6093500      VST  \n",
       "4   9136100      VST  "
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df12_m = pd.merge(df1_1, df2_1, on=\"datetime\")\n",
    "df12_m.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4460c8e",
   "metadata": {},
   "source": [
    "#### TIME ZONE CONVERSION (IF NEEDED)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "6a27a3b8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:41.177079Z",
     "start_time": "2022-11-01T17:09:41.122082Z"
    }
   },
   "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>datetime</th>\n",
       "      <th>close_x</th>\n",
       "      <th>volume_x</th>\n",
       "      <th>ticker_x</th>\n",
       "      <th>close_y</th>\n",
       "      <th>volume_y</th>\n",
       "      <th>ticker_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-11-01 04:00:00+00:00</td>\n",
       "      <td>26.290001</td>\n",
       "      <td>3730100</td>\n",
       "      <td>CNP</td>\n",
       "      <td>20.010000</td>\n",
       "      <td>2756500</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-02 04:00:00+00:00</td>\n",
       "      <td>26.190001</td>\n",
       "      <td>5620500</td>\n",
       "      <td>CNP</td>\n",
       "      <td>19.870001</td>\n",
       "      <td>4006000</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-03 04:00:00+00:00</td>\n",
       "      <td>26.290001</td>\n",
       "      <td>3350900</td>\n",
       "      <td>CNP</td>\n",
       "      <td>19.680000</td>\n",
       "      <td>2460100</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-04 04:00:00+00:00</td>\n",
       "      <td>26.500000</td>\n",
       "      <td>3364800</td>\n",
       "      <td>CNP</td>\n",
       "      <td>19.209999</td>\n",
       "      <td>6093500</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-11-05 04:00:00+00:00</td>\n",
       "      <td>26.670000</td>\n",
       "      <td>3179000</td>\n",
       "      <td>CNP</td>\n",
       "      <td>20.500000</td>\n",
       "      <td>9136100</td>\n",
       "      <td>VST</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>247</th>\n",
       "      <td>2022-10-25 04:00:00+00:00</td>\n",
       "      <td>27.650000</td>\n",
       "      <td>3556000</td>\n",
       "      <td>CNP</td>\n",
       "      <td>21.990000</td>\n",
       "      <td>4838400</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>248</th>\n",
       "      <td>2022-10-26 04:00:00+00:00</td>\n",
       "      <td>27.600000</td>\n",
       "      <td>3132200</td>\n",
       "      <td>CNP</td>\n",
       "      <td>21.900000</td>\n",
       "      <td>3419400</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>2022-10-27 04:00:00+00:00</td>\n",
       "      <td>27.990000</td>\n",
       "      <td>2833500</td>\n",
       "      <td>CNP</td>\n",
       "      <td>21.980000</td>\n",
       "      <td>3738400</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>2022-10-28 04:00:00+00:00</td>\n",
       "      <td>28.809999</td>\n",
       "      <td>2803400</td>\n",
       "      <td>CNP</td>\n",
       "      <td>22.980000</td>\n",
       "      <td>4167400</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>2022-10-31 04:00:00+00:00</td>\n",
       "      <td>28.610001</td>\n",
       "      <td>4470200</td>\n",
       "      <td>CNP</td>\n",
       "      <td>22.969999</td>\n",
       "      <td>4428700</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>252 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     datetime    close_x  volume_x ticker_x    close_y  \\\n",
       "0   2021-11-01 04:00:00+00:00  26.290001   3730100      CNP  20.010000   \n",
       "1   2021-11-02 04:00:00+00:00  26.190001   5620500      CNP  19.870001   \n",
       "2   2021-11-03 04:00:00+00:00  26.290001   3350900      CNP  19.680000   \n",
       "3   2021-11-04 04:00:00+00:00  26.500000   3364800      CNP  19.209999   \n",
       "4   2021-11-05 04:00:00+00:00  26.670000   3179000      CNP  20.500000   \n",
       "..                        ...        ...       ...      ...        ...   \n",
       "247 2022-10-25 04:00:00+00:00  27.650000   3556000      CNP  21.990000   \n",
       "248 2022-10-26 04:00:00+00:00  27.600000   3132200      CNP  21.900000   \n",
       "249 2022-10-27 04:00:00+00:00  27.990000   2833500      CNP  21.980000   \n",
       "250 2022-10-28 04:00:00+00:00  28.809999   2803400      CNP  22.980000   \n",
       "251 2022-10-31 04:00:00+00:00  28.610001   4470200      CNP  22.969999   \n",
       "\n",
       "     volume_y ticker_y  \n",
       "0     2756500      VST  \n",
       "1     4006000      VST  \n",
       "2     2460100      VST  \n",
       "3     6093500      VST  \n",
       "4     9136100      VST  \n",
       "..        ...      ...  \n",
       "247   4838400      VST  \n",
       "248   3419400      VST  \n",
       "249   3738400      VST  \n",
       "250   4167400      VST  \n",
       "251   4428700      VST  \n",
       "\n",
       "[252 rows x 7 columns]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df12_m['datetime'] = pd.to_datetime(df12_m['datetime'], utc=True)\n",
    "#df12_m.datetime = df12_m.datetime.dt.tz_convert(\"Europe/Istanbul\")\n",
    "#df12_m.datetime = df12_m.datetime.dt.tz_convert(\"Canada/Pacific\")\n",
    "df12_m.datetime = df12_m.datetime.dt.tz_convert(\"UTC\")\n",
    "df12_m"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "effd2d05",
   "metadata": {},
   "source": [
    "#### GET START & END DATES & SET INDEX"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "6bbe490b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:41.192082Z",
     "start_time": "2022-11-01T17:09:41.181079Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('11/01/2021', '10/31/2022')"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get start and end days of time series\n",
    "min_date, max_date = df12_m.datetime.min(), df12_m.datetime.max()\n",
    "#str(min_date), str(max_date)\n",
    "min_date.strftime('%m/%d/%Y'), max_date.strftime('%m/%d/%Y')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "9a01a50d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:41.255082Z",
     "start_time": "2022-11-01T17:09:41.195079Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>close_x</th>\n",
       "      <th>volume_x</th>\n",
       "      <th>ticker_x</th>\n",
       "      <th>close_y</th>\n",
       "      <th>volume_y</th>\n",
       "      <th>ticker_y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>datetime</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>2021-11-01 04:00:00+00:00</th>\n",
       "      <td>26.290001</td>\n",
       "      <td>3730100</td>\n",
       "      <td>CNP</td>\n",
       "      <td>20.010000</td>\n",
       "      <td>2756500</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-02 04:00:00+00:00</th>\n",
       "      <td>26.190001</td>\n",
       "      <td>5620500</td>\n",
       "      <td>CNP</td>\n",
       "      <td>19.870001</td>\n",
       "      <td>4006000</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-03 04:00:00+00:00</th>\n",
       "      <td>26.290001</td>\n",
       "      <td>3350900</td>\n",
       "      <td>CNP</td>\n",
       "      <td>19.680000</td>\n",
       "      <td>2460100</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-04 04:00:00+00:00</th>\n",
       "      <td>26.500000</td>\n",
       "      <td>3364800</td>\n",
       "      <td>CNP</td>\n",
       "      <td>19.209999</td>\n",
       "      <td>6093500</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-11-05 04:00:00+00:00</th>\n",
       "      <td>26.670000</td>\n",
       "      <td>3179000</td>\n",
       "      <td>CNP</td>\n",
       "      <td>20.500000</td>\n",
       "      <td>9136100</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             close_x  volume_x ticker_x    close_y  volume_y  \\\n",
       "datetime                                                                       \n",
       "2021-11-01 04:00:00+00:00  26.290001   3730100      CNP  20.010000   2756500   \n",
       "2021-11-02 04:00:00+00:00  26.190001   5620500      CNP  19.870001   4006000   \n",
       "2021-11-03 04:00:00+00:00  26.290001   3350900      CNP  19.680000   2460100   \n",
       "2021-11-04 04:00:00+00:00  26.500000   3364800      CNP  19.209999   6093500   \n",
       "2021-11-05 04:00:00+00:00  26.670000   3179000      CNP  20.500000   9136100   \n",
       "\n",
       "                          ticker_y  \n",
       "datetime                            \n",
       "2021-11-01 04:00:00+00:00      VST  \n",
       "2021-11-02 04:00:00+00:00      VST  \n",
       "2021-11-03 04:00:00+00:00      VST  \n",
       "2021-11-04 04:00:00+00:00      VST  \n",
       "2021-11-05 04:00:00+00:00      VST  "
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df12_m = df12_m.set_index(\"datetime\").sort_index()\n",
    "df12_m.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe8f6ec1",
   "metadata": {},
   "source": [
    "#### GET CUSTOM DATES (IF NEEDED):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "556c9bc1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:41.286078Z",
     "start_time": "2022-11-01T17:09:41.259097Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2018-06-01 00:00:00+00:00', '2018-06-02 00:00:00+00:00',\n",
       "               '2018-06-03 00:00:00+00:00', '2018-06-04 00:00:00+00:00',\n",
       "               '2018-06-05 00:00:00+00:00', '2018-06-06 00:00:00+00:00',\n",
       "               '2018-06-07 00:00:00+00:00', '2018-06-08 00:00:00+00:00',\n",
       "               '2018-06-09 00:00:00+00:00', '2018-06-10 00:00:00+00:00',\n",
       "               ...\n",
       "               '2021-12-23 00:00:00+00:00', '2021-12-24 00:00:00+00:00',\n",
       "               '2021-12-25 00:00:00+00:00', '2021-12-26 00:00:00+00:00',\n",
       "               '2021-12-27 00:00:00+00:00', '2021-12-28 00:00:00+00:00',\n",
       "               '2021-12-29 00:00:00+00:00', '2021-12-30 00:00:00+00:00',\n",
       "               '2021-12-31 00:00:00+00:00', '2022-01-01 00:00:00+00:00'],\n",
       "              dtype='datetime64[ns, UTC]', length=1311, freq='D')"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# CUSTOM DATE\n",
    "import datetime\n",
    "from_date = datetime.datetime(2018, 6, 1)\n",
    "to_date = datetime.datetime(2022, 1, 1)\n",
    "# from_date = '2021-01-01 13:30:00+00:00' # UTC\n",
    "# to_date = '2022-01-01 13:30:00+00:00' # UTC\n",
    "# date_format = '%Y-%m-%d'\n",
    "# from_date = datetime.strptime(from_date, date_format)\n",
    "# to_date = datetime.strptime(to_date, date_format)\n",
    "\n",
    "new_index = pd.date_range(from_date, to_date, freq='1D', tz='UTC')\n",
    "new_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "bcafced0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:41.348080Z",
     "start_time": "2022-11-01T17:09:41.290080Z"
    }
   },
   "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_x</th>\n",
       "      <th>volume_x</th>\n",
       "      <th>ticker_x</th>\n",
       "      <th>close_y</th>\n",
       "      <th>volume_y</th>\n",
       "      <th>ticker_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-06-01 00:00:00+00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-02 00:00:00+00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-03 00:00:00+00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-04 00:00:00+00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-05 00:00:00+00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-28 00:00:00+00:00</th>\n",
       "      <td>27.580000</td>\n",
       "      <td>2132800.0</td>\n",
       "      <td>CNP</td>\n",
       "      <td>22.190001</td>\n",
       "      <td>2283600.0</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-29 00:00:00+00:00</th>\n",
       "      <td>27.850000</td>\n",
       "      <td>2119100.0</td>\n",
       "      <td>CNP</td>\n",
       "      <td>22.049999</td>\n",
       "      <td>1864300.0</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-30 00:00:00+00:00</th>\n",
       "      <td>27.950001</td>\n",
       "      <td>2618600.0</td>\n",
       "      <td>CNP</td>\n",
       "      <td>21.870001</td>\n",
       "      <td>2895400.0</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-12-31 00:00:00+00:00</th>\n",
       "      <td>27.830000</td>\n",
       "      <td>2815300.0</td>\n",
       "      <td>CNP</td>\n",
       "      <td>22.389999</td>\n",
       "      <td>7636100.0</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-01-01 00:00:00+00:00</th>\n",
       "      <td>27.910000</td>\n",
       "      <td>3039300.0</td>\n",
       "      <td>CNP</td>\n",
       "      <td>22.770000</td>\n",
       "      <td>3243500.0</td>\n",
       "      <td>VST</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1311 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             close_x   volume_x ticker_x    close_y  \\\n",
       "2018-06-01 00:00:00+00:00        NaN        NaN      NaN        NaN   \n",
       "2018-06-02 00:00:00+00:00        NaN        NaN      NaN        NaN   \n",
       "2018-06-03 00:00:00+00:00        NaN        NaN      NaN        NaN   \n",
       "2018-06-04 00:00:00+00:00        NaN        NaN      NaN        NaN   \n",
       "2018-06-05 00:00:00+00:00        NaN        NaN      NaN        NaN   \n",
       "...                              ...        ...      ...        ...   \n",
       "2021-12-28 00:00:00+00:00  27.580000  2132800.0      CNP  22.190001   \n",
       "2021-12-29 00:00:00+00:00  27.850000  2119100.0      CNP  22.049999   \n",
       "2021-12-30 00:00:00+00:00  27.950001  2618600.0      CNP  21.870001   \n",
       "2021-12-31 00:00:00+00:00  27.830000  2815300.0      CNP  22.389999   \n",
       "2022-01-01 00:00:00+00:00  27.910000  3039300.0      CNP  22.770000   \n",
       "\n",
       "                            volume_y ticker_y  \n",
       "2018-06-01 00:00:00+00:00        NaN      NaN  \n",
       "2018-06-02 00:00:00+00:00        NaN      NaN  \n",
       "2018-06-03 00:00:00+00:00        NaN      NaN  \n",
       "2018-06-04 00:00:00+00:00        NaN      NaN  \n",
       "2018-06-05 00:00:00+00:00        NaN      NaN  \n",
       "...                              ...      ...  \n",
       "2021-12-28 00:00:00+00:00  2283600.0      VST  \n",
       "2021-12-29 00:00:00+00:00  1864300.0      VST  \n",
       "2021-12-30 00:00:00+00:00  2895400.0      VST  \n",
       "2021-12-31 00:00:00+00:00  7636100.0      VST  \n",
       "2022-01-01 00:00:00+00:00  3243500.0      VST  \n",
       "\n",
       "[1311 rows x 6 columns]"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df12_m_custom = df12_m.reindex(new_index, method=\"ffill\")\n",
    "df12_m_custom"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c82dcc9",
   "metadata": {},
   "source": [
    "#### SAVE TO FILE FOR MORE ANALYSIS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "261ee804",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-11-01T17:09:41.380080Z",
     "start_time": "2022-11-01T17:09:41.355083Z"
    }
   },
   "outputs": [],
   "source": [
    "df12_m.to_pickle(\"saved.pkl\")\n",
    "if 'df12_m_custom' in locals():\n",
    "    df12_m_custom.to_pickle(\"saved_custom.pkl\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}