NG_Unified_DF.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "from pandas.tseries.frequencies import to_offset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['PricesFile1.csv',\n",
       " 'NG_ImpliedVols2020.csv',\n",
       " 'NG_ImpliedVols2022.csv',\n",
       " 'NG_ImpliedVols2021.csv',\n",
       " 'WTI_ImpliedVols2022.csv',\n",
       " 'WTI_ImpliedVols2020.csv',\n",
       " 'WTI_ImpliedVols2021.csv',\n",
       " 'InterestRates.csv']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dir = \"./Data/\"\n",
    "os.listdir(data_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "prices_df = pd.read_csv(data_dir+\"PricesFile1.csv\")\n",
    "\n",
    "prices_df.pricedate = pd.to_datetime(prices_df.pricedate)\n",
    "prices_df.delivdate = pd.to_datetime(prices_df.delivdate)\n",
    "\n",
    "prices_df = prices_df.loc[(prices_df.pricedate < prices_df.delivdate)]\n",
    "\n",
    "tuples = list(zip(*[prices_df.pricedate, prices_df.delivdate]))\n",
    "index = pd.MultiIndex.from_tuples(tuples, names=[\"t\", \"T\"])\n",
    "\n",
    "prices_df.set_index(index, inplace=True)\n",
    "prices_df.drop(columns=[\"pricedate\", \"delivdate\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "NG_prices_df = prices_df.groupby(\"priceindex\").get_group(\"NYMEX Natural Gas\")\n",
    "NG_prices_df = NG_prices_df.loc[NG_prices_df.price > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "NG20_df = pd.read_csv(data_dir+\"NG_ImpliedVols2020.csv\")\n",
    "NG21_df = pd.read_csv(data_dir+\"NG_ImpliedVols2021.csv\")\n",
    "NG22_df = pd.read_csv(data_dir+\"NG_ImpliedVols2022.csv\")\n",
    "\n",
    "frames = [NG20_df, NG21_df, NG22_df]\n",
    "NG_IV_df = pd.concat(frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "NG_IV_df.volatilitydate = pd.to_datetime(NG_IV_df.volatilitydate)\n",
    "NG_IV_df.begtime = pd.to_datetime(NG_IV_df.begtime)\n",
    "\n",
    "NG_IV_df = NG_IV_df.loc[(NG_IV_df.volatilitydate < NG_IV_df.begtime)]\n",
    "\n",
    "tuples = list(zip(*[NG_IV_df.volatilitydate, NG_IV_df.begtime]))\n",
    "index = pd.MultiIndex.from_tuples(tuples, names=[\"t\", \"T\"])\n",
    "\n",
    "NG_IV_df.set_index(index, inplace=True)\n",
    "NG_IV_df.drop(columns=[\"volatilitydate\", \"begtime\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "NG_df = NG_IV_df.merge(NG_prices_df, how=\"inner\", left_index=True, right_index=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "IR_df = pd.read_csv(data_dir+\"InterestRates.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = [to_offset(i) for i in IR_df.maturity]\n",
    "b = [pd.Timestamp(i) for i in IR_df.pricedate]\n",
    "c = [b[i] + a[i] for i in range(len(a))]\n",
    "IR_df.maturity = c\n",
    "IR_df.pricedate = pd.to_datetime(IR_df.pricedate)\n",
    "IR_df.maturity = pd.to_datetime(IR_df.maturity)\n",
    "IR_df.maturity = IR_df.maturity+to_offset(\"1D\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "tuples = list(zip(*[IR_df.pricedate, IR_df.maturity]))\n",
    "index = pd.MultiIndex.from_tuples(tuples, names=[\"t\", \"T\"])\n",
    "\n",
    "IR_df.set_index(index, inplace=True)\n",
    "IR_df.drop(columns=[\"pricedate\", \"maturity\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "NG_df = NG_df.merge(IR_df, how=\"inner\", left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>t</th>\n",
       "      <th>T</th>\n",
       "      <th>strikeprice</th>\n",
       "      <th>volatility</th>\n",
       "      <th>price</th>\n",
       "      <th>bidrate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>1.03</td>\n",
       "      <td>0.592</td>\n",
       "      <td>1.755</td>\n",
       "      <td>1.110001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>1.08</td>\n",
       "      <td>0.576</td>\n",
       "      <td>1.755</td>\n",
       "      <td>1.110001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>1.13</td>\n",
       "      <td>0.562</td>\n",
       "      <td>1.755</td>\n",
       "      <td>1.110001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>1.18</td>\n",
       "      <td>0.548</td>\n",
       "      <td>1.755</td>\n",
       "      <td>1.110001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>1.23</td>\n",
       "      <td>0.535</td>\n",
       "      <td>1.755</td>\n",
       "      <td>1.110001</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>152771</th>\n",
       "      <td>2020-05-29</td>\n",
       "      <td>2022-12-01</td>\n",
       "      <td>4.80</td>\n",
       "      <td>0.236</td>\n",
       "      <td>2.622</td>\n",
       "      <td>0.315002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152772</th>\n",
       "      <td>2020-05-29</td>\n",
       "      <td>2022-12-01</td>\n",
       "      <td>4.85</td>\n",
       "      <td>0.236</td>\n",
       "      <td>2.622</td>\n",
       "      <td>0.315002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152773</th>\n",
       "      <td>2020-05-29</td>\n",
       "      <td>2022-12-01</td>\n",
       "      <td>4.90</td>\n",
       "      <td>0.237</td>\n",
       "      <td>2.622</td>\n",
       "      <td>0.315002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152774</th>\n",
       "      <td>2020-05-29</td>\n",
       "      <td>2022-12-01</td>\n",
       "      <td>4.95</td>\n",
       "      <td>0.237</td>\n",
       "      <td>2.622</td>\n",
       "      <td>0.315002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152775</th>\n",
       "      <td>2020-05-29</td>\n",
       "      <td>2022-12-01</td>\n",
       "      <td>5.00</td>\n",
       "      <td>0.237</td>\n",
       "      <td>2.622</td>\n",
       "      <td>0.315002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>152776 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                t          T  strikeprice  volatility  price   bidrate\n",
       "0      2020-03-02 2020-04-01         1.03       0.592  1.755  1.110001\n",
       "1      2020-03-02 2020-04-01         1.08       0.576  1.755  1.110001\n",
       "2      2020-03-02 2020-04-01         1.13       0.562  1.755  1.110001\n",
       "3      2020-03-02 2020-04-01         1.18       0.548  1.755  1.110001\n",
       "4      2020-03-02 2020-04-01         1.23       0.535  1.755  1.110001\n",
       "...           ...        ...          ...         ...    ...       ...\n",
       "152771 2020-05-29 2022-12-01         4.80       0.236  2.622  0.315002\n",
       "152772 2020-05-29 2022-12-01         4.85       0.236  2.622  0.315002\n",
       "152773 2020-05-29 2022-12-01         4.90       0.237  2.622  0.315002\n",
       "152774 2020-05-29 2022-12-01         4.95       0.237  2.622  0.315002\n",
       "152775 2020-05-29 2022-12-01         5.00       0.237  2.622  0.315002\n",
       "\n",
       "[152776 rows x 6 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "NG_df.drop(columns=[\"volatilityindex\", \"priceindex\"], inplace=True)\n",
    "NG_df.reset_index(inplace=True)\n",
    "NG_df"
   ]
  }
 ],
 "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}