02_preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import sys, os\n",
    "data_dir = '../noaa-runtime/data/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#dst_labs = pd.read_csv(data_dir+'dst_labels.csv' ,index_col=[\"period\", \"timedelta\"],)\n",
    "#sat_pos  = pd.read_csv(data_dir+'satellite_positions.csv',index_col=[\"period\", \"timedelta\"],)\n",
    "sw       = pd.read_csv(data_dir+'solar_wind.csv',index_col=[\"period\", \"timedelta\"],)\n",
    "#sunspots = pd.read_csv(data_dir+'sunspots.csv',index_col=[\"period\", \"timedelta\"],)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Interpolate and aggregate other files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Droping the columns: ['bx_gsm', 'by_gsm', 'bz_gsm', 'theta_gsm', 'phi_gsm', 'source']\n",
      "remaining columns: Index(['bx_gse', 'by_gse', 'bz_gse', 'theta_gse', 'phi_gse', 'bt', 'density',\n",
      "       'speed', 'temperature'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# drop the unnecessary columns\n",
    "cols_to_drop= ['bx_gsm','by_gsm', 'bz_gsm', 'theta_gsm', 'phi_gsm', 'source']\n",
    "sw.drop(cols_to_drop, axis=1, inplace=True)\n",
    "\n",
    "print ('Droping the columns:', cols_to_drop)\n",
    "print ('remaining columns:', sw.columns )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Interpolation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The Null values in the data:\n",
      " bx_gse         325888\n",
      "by_gse         325888\n",
      "bz_gse         325888\n",
      "theta_gse      325888\n",
      "phi_gse        326388\n",
      "bt             325888\n",
      "density        684890\n",
      "speed          689555\n",
      "temperature    811768\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print (\"The Null values in the data:\\n\", sw.isnull().sum() )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "sw.interpolate(method='linear', limit_direction='forward', axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of the data shape: (8392320, 9)\n",
      "Null values after interpolation: 0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>bx_gse</th>\n",
       "      <th>by_gse</th>\n",
       "      <th>bz_gse</th>\n",
       "      <th>theta_gse</th>\n",
       "      <th>phi_gse</th>\n",
       "      <th>bt</th>\n",
       "      <th>density</th>\n",
       "      <th>speed</th>\n",
       "      <th>temperature</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>period</th>\n",
       "      <th>timedelta</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">train_a</th>\n",
       "      <th>0 days 00:00:00</th>\n",
       "      <td>-5.55</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1.25</td>\n",
       "      <td>11.09</td>\n",
       "      <td>153.37</td>\n",
       "      <td>6.80</td>\n",
       "      <td>1.53</td>\n",
       "      <td>383.92</td>\n",
       "      <td>110237.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0 days 00:01:00</th>\n",
       "      <td>-5.58</td>\n",
       "      <td>3.16</td>\n",
       "      <td>1.17</td>\n",
       "      <td>10.10</td>\n",
       "      <td>151.91</td>\n",
       "      <td>6.83</td>\n",
       "      <td>1.69</td>\n",
       "      <td>381.79</td>\n",
       "      <td>123825.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0 days 00:02:00</th>\n",
       "      <td>-5.15</td>\n",
       "      <td>3.66</td>\n",
       "      <td>0.85</td>\n",
       "      <td>7.87</td>\n",
       "      <td>146.04</td>\n",
       "      <td>6.77</td>\n",
       "      <td>1.97</td>\n",
       "      <td>389.11</td>\n",
       "      <td>82548.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0 days 00:03:00</th>\n",
       "      <td>-5.20</td>\n",
       "      <td>3.68</td>\n",
       "      <td>0.68</td>\n",
       "      <td>6.17</td>\n",
       "      <td>146.17</td>\n",
       "      <td>6.74</td>\n",
       "      <td>1.97</td>\n",
       "      <td>389.11</td>\n",
       "      <td>82548.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0 days 00:04:00</th>\n",
       "      <td>-5.12</td>\n",
       "      <td>3.68</td>\n",
       "      <td>0.49</td>\n",
       "      <td>4.62</td>\n",
       "      <td>145.72</td>\n",
       "      <td>6.65</td>\n",
       "      <td>1.77</td>\n",
       "      <td>384.26</td>\n",
       "      <td>94269.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         bx_gse  by_gse  bz_gse  theta_gse  phi_gse    bt  \\\n",
       "period  timedelta                                                           \n",
       "train_a 0 days 00:00:00   -5.55    3.00    1.25      11.09   153.37  6.80   \n",
       "        0 days 00:01:00   -5.58    3.16    1.17      10.10   151.91  6.83   \n",
       "        0 days 00:02:00   -5.15    3.66    0.85       7.87   146.04  6.77   \n",
       "        0 days 00:03:00   -5.20    3.68    0.68       6.17   146.17  6.74   \n",
       "        0 days 00:04:00   -5.12    3.68    0.49       4.62   145.72  6.65   \n",
       "\n",
       "                         density   speed  temperature  \n",
       "period  timedelta                                      \n",
       "train_a 0 days 00:00:00     1.53  383.92     110237.0  \n",
       "        0 days 00:01:00     1.69  381.79     123825.0  \n",
       "        0 days 00:02:00     1.97  389.11      82548.0  \n",
       "        0 days 00:03:00     1.97  389.11      82548.0  \n",
       "        0 days 00:04:00     1.77  384.26      94269.0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print (\"shape of the data shape:\", sw.shape)\n",
    "print (\"Null values after interpolation:\", sw.isnull().sum().sum())\n",
    "sw.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def using_reset_index(df):\n",
    "    df = df.reset_index(level='period')\n",
    "    df.index=pd.to_timedelta(df.index)\n",
    "    return df.groupby('period').resample('H').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of the data shape: (139872, 9)\n",
      "Null values : 0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>bx_gse</th>\n",
       "      <th>by_gse</th>\n",
       "      <th>bz_gse</th>\n",
       "      <th>theta_gse</th>\n",
       "      <th>phi_gse</th>\n",
       "      <th>bt</th>\n",
       "      <th>density</th>\n",
       "      <th>speed</th>\n",
       "      <th>temperature</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>period</th>\n",
       "      <th>timedelta</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">train_a</th>\n",
       "      <th>00:00:00</th>\n",
       "      <td>-6.149500</td>\n",
       "      <td>1.6455</td>\n",
       "      <td>0.844667</td>\n",
       "      <td>7.103667</td>\n",
       "      <td>165.420333</td>\n",
       "      <td>7.140000</td>\n",
       "      <td>1.174167</td>\n",
       "      <td>355.278250</td>\n",
       "      <td>68880.341667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01:00:00</th>\n",
       "      <td>-6.693833</td>\n",
       "      <td>0.7665</td>\n",
       "      <td>1.265167</td>\n",
       "      <td>10.183333</td>\n",
       "      <td>174.267833</td>\n",
       "      <td>7.284833</td>\n",
       "      <td>0.815917</td>\n",
       "      <td>330.080583</td>\n",
       "      <td>52203.225000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     bx_gse  by_gse    bz_gse  theta_gse     phi_gse  \\\n",
       "period  timedelta                                                      \n",
       "train_a 00:00:00  -6.149500  1.6455  0.844667   7.103667  165.420333   \n",
       "        01:00:00  -6.693833  0.7665  1.265167  10.183333  174.267833   \n",
       "\n",
       "                         bt   density       speed   temperature  \n",
       "period  timedelta                                                \n",
       "train_a 00:00:00   7.140000  1.174167  355.278250  68880.341667  \n",
       "        01:00:00   7.284833  0.815917  330.080583  52203.225000  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sw_ipt_agg_ml=using_reset_index(sw)\n",
    "print (\"shape of the data shape:\", sw_ipt_agg_ml.shape)\n",
    "print (\"Null values :\", sw_ipt_agg_ml.isnull().sum().sum())\n",
    "sw_ipt_agg_ml.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## join `dst labels` to the original solar wind data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of the data dst_labs: (139872, 1)\n",
      "Null values: 0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>dst</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>period</th>\n",
       "      <th>timedelta</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">train_a</th>\n",
       "      <th>0 days 00:00:00</th>\n",
       "      <td>-7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0 days 01:00:00</th>\n",
       "      <td>-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0 days 02:00:00</th>\n",
       "      <td>-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0 days 03:00:00</th>\n",
       "      <td>-6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0 days 04:00:00</th>\n",
       "      <td>-2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         dst\n",
       "period  timedelta           \n",
       "train_a 0 days 00:00:00   -7\n",
       "        0 days 01:00:00  -10\n",
       "        0 days 02:00:00  -10\n",
       "        0 days 03:00:00   -6\n",
       "        0 days 04:00:00   -2"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dst_labs = pd.read_csv(data_dir+'dst_labels.csv' ,index_col=[\"period\", \"timedelta\"],)\n",
    "print (\"shape of the data dst_labs:\", dst_labs.shape)\n",
    "print (\"Null values:\", dst_labs.isnull().sum().sum())\n",
    "dst_labs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sample only hourly data\n",
    "dst_labs = using_reset_index(dst_labs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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></th>\n",
       "      <th>bx_gse</th>\n",
       "      <th>by_gse</th>\n",
       "      <th>bz_gse</th>\n",
       "      <th>theta_gse</th>\n",
       "      <th>phi_gse</th>\n",
       "      <th>bt</th>\n",
       "      <th>density</th>\n",
       "      <th>speed</th>\n",
       "      <th>temperature</th>\n",
       "      <th>dst</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>period</th>\n",
       "      <th>timedelta</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">train_a</th>\n",
       "      <th>00:00:00</th>\n",
       "      <td>-6.149500</td>\n",
       "      <td>1.6455</td>\n",
       "      <td>0.844667</td>\n",
       "      <td>7.103667</td>\n",
       "      <td>165.420333</td>\n",
       "      <td>7.140000</td>\n",
       "      <td>1.174167</td>\n",
       "      <td>355.278250</td>\n",
       "      <td>68880.341667</td>\n",
       "      <td>-7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01:00:00</th>\n",
       "      <td>-6.693833</td>\n",
       "      <td>0.7665</td>\n",
       "      <td>1.265167</td>\n",
       "      <td>10.183333</td>\n",
       "      <td>174.267833</td>\n",
       "      <td>7.284833</td>\n",
       "      <td>0.815917</td>\n",
       "      <td>330.080583</td>\n",
       "      <td>52203.225000</td>\n",
       "      <td>-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     bx_gse  by_gse    bz_gse  theta_gse     phi_gse  \\\n",
       "period  timedelta                                                      \n",
       "train_a 00:00:00  -6.149500  1.6455  0.844667   7.103667  165.420333   \n",
       "        01:00:00  -6.693833  0.7665  1.265167  10.183333  174.267833   \n",
       "\n",
       "                         bt   density       speed   temperature  dst  \n",
       "period  timedelta                                                     \n",
       "train_a 00:00:00   7.140000  1.174167  355.278250  68880.341667   -7  \n",
       "        01:00:00   7.284833  0.815917  330.080583  52203.225000  -10  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sw_dst=sw_ipt_agg_ml.join(dst_labs)\n",
    "df_sw_dst.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(139872, 9) (139872, 1) (139872, 10)\n"
     ]
    }
   ],
   "source": [
    "print(sw_ipt_agg_ml.shape , dst_labs.shape, df_sw_dst.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## join `Sunspots data` to the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of the data sunspots: (192, 1)\n",
      "Null values: 0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>smoothed_ssn</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>period</th>\n",
       "      <th>timedelta</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">train_a</th>\n",
       "      <th>00:00:00</th>\n",
       "      <td>65.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01:00:00</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>02:00:00</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>03:00:00</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04:00:00</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   smoothed_ssn\n",
       "period  timedelta              \n",
       "train_a 00:00:00           65.4\n",
       "        01:00:00            NaN\n",
       "        02:00:00            NaN\n",
       "        03:00:00            NaN\n",
       "        04:00:00            NaN"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sunspots = pd.read_csv(data_dir+'sunspots.csv',index_col=[\"period\", \"timedelta\"],)\n",
    "print (\"shape of the data sunspots:\", sunspots.shape)\n",
    "print (\"Null values:\", sunspots.isnull().sum().sum())\n",
    "sunspots = using_reset_index(sunspots)\n",
    "sunspots.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(139872, 9) (139872, 1) (139872, 10) (137643, 1)\n"
     ]
    }
   ],
   "source": [
    "print(sw_ipt_agg_ml.shape, dst_labs.shape, df_sw_dst.shape, sunspots.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "the rows are slightly different so, let's join the dataframe anyway and then replace the NULL data by interpolation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sw_dst_ss=df_sw_dst.join(sunspots)\n",
    "df_sw_dst_ss['smoothed_ssn']=df_sw_dst_ss[['smoothed_ssn']].interpolate(method='linear', limit_direction='forward', axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of all: (139872, 9) (139872, 1) (139872, 10) (137643, 1)\n",
      "Total Null values: 0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>bx_gse</th>\n",
       "      <th>by_gse</th>\n",
       "      <th>bz_gse</th>\n",
       "      <th>theta_gse</th>\n",
       "      <th>phi_gse</th>\n",
       "      <th>bt</th>\n",
       "      <th>density</th>\n",
       "      <th>speed</th>\n",
       "      <th>temperature</th>\n",
       "      <th>dst</th>\n",
       "      <th>smoothed_ssn</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>period</th>\n",
       "      <th>timedelta</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">train_c</th>\n",
       "      <th>2435 days 22:00:00</th>\n",
       "      <td>-2.3850</td>\n",
       "      <td>2.860333</td>\n",
       "      <td>-2.221667</td>\n",
       "      <td>-29.645667</td>\n",
       "      <td>130.601500</td>\n",
       "      <td>4.716667</td>\n",
       "      <td>2.148917</td>\n",
       "      <td>343.488167</td>\n",
       "      <td>41803.641667</td>\n",
       "      <td>-15</td>\n",
       "      <td>42.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2435 days 23:00:00</th>\n",
       "      <td>-2.2515</td>\n",
       "      <td>2.619333</td>\n",
       "      <td>-2.821000</td>\n",
       "      <td>-39.064000</td>\n",
       "      <td>130.277667</td>\n",
       "      <td>4.554833</td>\n",
       "      <td>2.283583</td>\n",
       "      <td>343.848750</td>\n",
       "      <td>36191.908333</td>\n",
       "      <td>-14</td>\n",
       "      <td>42.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            bx_gse    by_gse    bz_gse  theta_gse     phi_gse  \\\n",
       "period  timedelta                                                               \n",
       "train_c 2435 days 22:00:00 -2.3850  2.860333 -2.221667 -29.645667  130.601500   \n",
       "        2435 days 23:00:00 -2.2515  2.619333 -2.821000 -39.064000  130.277667   \n",
       "\n",
       "                                  bt   density       speed   temperature  dst  \\\n",
       "period  timedelta                                                               \n",
       "train_c 2435 days 22:00:00  4.716667  2.148917  343.488167  41803.641667  -15   \n",
       "        2435 days 23:00:00  4.554833  2.283583  343.848750  36191.908333  -14   \n",
       "\n",
       "                            smoothed_ssn  \n",
       "period  timedelta                         \n",
       "train_c 2435 days 22:00:00          42.5  \n",
       "        2435 days 23:00:00          42.5  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print (\"shape of all:\", sw_ipt_agg_ml.shape, dst_labs.shape, df_sw_dst.shape, sunspots.shape)\n",
    "print (\"Total Null values:\", df_sw_dst_ss.isnull().sum().sum())\n",
    "df_sw_dst_ss.tail(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## join `Satellite Position` to the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of the data sunspots: (5828, 6)\n",
      "Null values: 14382\n",
      "Columns: Index(['gse_x_ace', 'gse_y_ace', 'gse_z_ace', 'gse_x_dscovr', 'gse_y_dscovr',\n",
      "       'gse_z_dscovr'],\n",
      "      dtype='object')\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>gse_x_ace</th>\n",
       "      <th>gse_y_ace</th>\n",
       "      <th>gse_z_ace</th>\n",
       "      <th>gse_x_dscovr</th>\n",
       "      <th>gse_y_dscovr</th>\n",
       "      <th>gse_z_dscovr</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>period</th>\n",
       "      <th>timedelta</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 rowspan=\"2\" valign=\"top\">train_a</th>\n",
       "      <th>00:00:00</th>\n",
       "      <td>1522376.9</td>\n",
       "      <td>143704.6</td>\n",
       "      <td>149496.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01: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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   gse_x_ace  gse_y_ace  gse_z_ace  gse_x_dscovr  \\\n",
       "period  timedelta                                                  \n",
       "train_a 00:00:00   1522376.9   143704.6   149496.7           NaN   \n",
       "        01:00:00         NaN        NaN        NaN           NaN   \n",
       "\n",
       "                   gse_y_dscovr  gse_z_dscovr  \n",
       "period  timedelta                              \n",
       "train_a 00:00:00            NaN           NaN  \n",
       "        01:00:00            NaN           NaN  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sat_pos  = pd.read_csv(data_dir+'satellite_positions.csv',index_col=[\"period\", \"timedelta\"],)\n",
    "\n",
    "\n",
    "print (\"shape of the data sunspots:\", sat_pos.shape)\n",
    "print (\"Null values:\", sat_pos.isnull().sum().sum())\n",
    "sat_pos = using_reset_index(sat_pos)\n",
    "print (\"Columns:\", sat_pos.columns)\n",
    "sat_pos.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Droping the columns: ['gse_x_dscovr', 'gse_y_dscovr', 'gse_z_dscovr']\n",
      "remaining columns: Index(['gse_x_ace', 'gse_y_ace', 'gse_z_ace'], dtype='object')\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>gse_x_ace</th>\n",
       "      <th>gse_y_ace</th>\n",
       "      <th>gse_z_ace</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>period</th>\n",
       "      <th>timedelta</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">train_a</th>\n",
       "      <th>00:00:00</th>\n",
       "      <td>1522376.9</td>\n",
       "      <td>143704.6</td>\n",
       "      <td>149496.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01:00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>02:00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>03:00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04:00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   gse_x_ace  gse_y_ace  gse_z_ace\n",
       "period  timedelta                                 \n",
       "train_a 00:00:00   1522376.9   143704.6   149496.7\n",
       "        01:00:00         NaN        NaN        NaN\n",
       "        02:00:00         NaN        NaN        NaN\n",
       "        03:00:00         NaN        NaN        NaN\n",
       "        04:00:00         NaN        NaN        NaN"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop the unnecessary columns\n",
    "cols_to_drop = ['gse_x_dscovr', 'gse_y_dscovr', 'gse_z_dscovr']\n",
    "sat_pos.drop(cols_to_drop, axis=1, inplace=True)\n",
    "\n",
    "print ('Droping the columns:', cols_to_drop)\n",
    "print ('remaining columns:', sat_pos.columns )\n",
    "sat_pos.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final=df_sw_dst_ss.join(sat_pos)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Null values: bx_gse               0\n",
      "by_gse               0\n",
      "bz_gse               0\n",
      "theta_gse            0\n",
      "phi_gse              0\n",
      "bt                   0\n",
      "density              0\n",
      "speed                0\n",
      "temperature          0\n",
      "dst                  0\n",
      "smoothed_ssn         0\n",
      "gse_x_ace       134044\n",
      "gse_y_ace       134044\n",
      "gse_z_ace       134044\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print (\"Total Null values:\", df_final.isnull().sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Interpolate the above three columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "final_shape: (139872, 14)\n",
      "Number of nulls: 0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>bx_gse</th>\n",
       "      <th>by_gse</th>\n",
       "      <th>bz_gse</th>\n",
       "      <th>theta_gse</th>\n",
       "      <th>phi_gse</th>\n",
       "      <th>bt</th>\n",
       "      <th>density</th>\n",
       "      <th>speed</th>\n",
       "      <th>temperature</th>\n",
       "      <th>dst</th>\n",
       "      <th>smoothed_ssn</th>\n",
       "      <th>gse_x_ace</th>\n",
       "      <th>gse_y_ace</th>\n",
       "      <th>gse_z_ace</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>period</th>\n",
       "      <th>timedelta</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">train_a</th>\n",
       "      <th>00:00:00</th>\n",
       "      <td>-6.149500</td>\n",
       "      <td>1.6455</td>\n",
       "      <td>0.844667</td>\n",
       "      <td>7.103667</td>\n",
       "      <td>165.420333</td>\n",
       "      <td>7.140000</td>\n",
       "      <td>1.174167</td>\n",
       "      <td>355.278250</td>\n",
       "      <td>68880.341667</td>\n",
       "      <td>-7</td>\n",
       "      <td>65.400000</td>\n",
       "      <td>1.522377e+06</td>\n",
       "      <td>143704.600000</td>\n",
       "      <td>149496.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01:00:00</th>\n",
       "      <td>-6.693833</td>\n",
       "      <td>0.7665</td>\n",
       "      <td>1.265167</td>\n",
       "      <td>10.183333</td>\n",
       "      <td>174.267833</td>\n",
       "      <td>7.284833</td>\n",
       "      <td>0.815917</td>\n",
       "      <td>330.080583</td>\n",
       "      <td>52203.225000</td>\n",
       "      <td>-10</td>\n",
       "      <td>65.421154</td>\n",
       "      <td>1.522503e+06</td>\n",
       "      <td>143388.108333</td>\n",
       "      <td>149560.758333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     bx_gse  by_gse    bz_gse  theta_gse     phi_gse  \\\n",
       "period  timedelta                                                      \n",
       "train_a 00:00:00  -6.149500  1.6455  0.844667   7.103667  165.420333   \n",
       "        01:00:00  -6.693833  0.7665  1.265167  10.183333  174.267833   \n",
       "\n",
       "                         bt   density       speed   temperature  dst  \\\n",
       "period  timedelta                                                      \n",
       "train_a 00:00:00   7.140000  1.174167  355.278250  68880.341667   -7   \n",
       "        01:00:00   7.284833  0.815917  330.080583  52203.225000  -10   \n",
       "\n",
       "                   smoothed_ssn     gse_x_ace      gse_y_ace      gse_z_ace  \n",
       "period  timedelta                                                            \n",
       "train_a 00:00:00      65.400000  1.522377e+06  143704.600000  149496.700000  \n",
       "        01:00:00      65.421154  1.522503e+06  143388.108333  149560.758333  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols_to_interp=['gse_x_ace', 'gse_y_ace', 'gse_z_ace']\n",
    "df_final[cols_to_interp]=df_final[cols_to_interp].interpolate(method='linear', limit_direction='forward', axis=0)\n",
    "print ('final_shape:', df_final.shape)\n",
    "print ('Number of nulls:', df_sw_dst_ss.isnull().sum().sum() )\n",
    "df_final.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "shape of all: (139872, 9) (139872, 1) (139872, 10) (139803, 3) (139872, 14)\n"
     ]
    }
   ],
   "source": [
    "print (\"shape of all:\", sw_ipt_agg_ml.shape, dst_labs.shape, df_sw_dst.shape, sat_pos.shape, df_final.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding the Standard Deviation columns for `by_gse`, `bz_gse`, `bt`, `density`, `speed`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def agg_std(df_final, df_sw):\n",
    "    cols_to_keep = ['by_gse', 'bz_gse', 'bt', 'density', 'speed']\n",
    "    df_sw = df_sw[cols_to_keep]\n",
    "    \n",
    "    df_sw = df_sw.reset_index(level='period')\n",
    "    df_sw.index=pd.to_timedelta(df_sw.index)\n",
    "    df_sw = df_sw.groupby('period').resample('H').std()\n",
    "    \n",
    "    # rename the columns for the std\n",
    "    columns = [c+'_std' for c in df_sw.columns]\n",
    "    df_sw.columns = columns\n",
    "\n",
    "    df = df_final.join(df_sw)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df_final.shape: (139872, 19)\n",
      "Number of nulls: 0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>bx_gse</th>\n",
       "      <th>by_gse</th>\n",
       "      <th>bz_gse</th>\n",
       "      <th>theta_gse</th>\n",
       "      <th>phi_gse</th>\n",
       "      <th>bt</th>\n",
       "      <th>density</th>\n",
       "      <th>speed</th>\n",
       "      <th>temperature</th>\n",
       "      <th>dst</th>\n",
       "      <th>smoothed_ssn</th>\n",
       "      <th>gse_x_ace</th>\n",
       "      <th>gse_y_ace</th>\n",
       "      <th>gse_z_ace</th>\n",
       "      <th>by_gse_std</th>\n",
       "      <th>bz_gse_std</th>\n",
       "      <th>bt_std</th>\n",
       "      <th>density_std</th>\n",
       "      <th>speed_std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>period</th>\n",
       "      <th>timedelta</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">train_a</th>\n",
       "      <th>00:00:00</th>\n",
       "      <td>-6.149500</td>\n",
       "      <td>1.6455</td>\n",
       "      <td>0.844667</td>\n",
       "      <td>7.103667</td>\n",
       "      <td>165.420333</td>\n",
       "      <td>7.140000</td>\n",
       "      <td>1.174167</td>\n",
       "      <td>355.278250</td>\n",
       "      <td>68880.341667</td>\n",
       "      <td>-7</td>\n",
       "      <td>65.400000</td>\n",
       "      <td>1.522377e+06</td>\n",
       "      <td>143704.600000</td>\n",
       "      <td>149496.700000</td>\n",
       "      <td>1.224754</td>\n",
       "      <td>0.580028</td>\n",
       "      <td>1.607043</td>\n",
       "      <td>0.479647</td>\n",
       "      <td>15.025364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01:00:00</th>\n",
       "      <td>-6.693833</td>\n",
       "      <td>0.7665</td>\n",
       "      <td>1.265167</td>\n",
       "      <td>10.183333</td>\n",
       "      <td>174.267833</td>\n",
       "      <td>7.284833</td>\n",
       "      <td>0.815917</td>\n",
       "      <td>330.080583</td>\n",
       "      <td>52203.225000</td>\n",
       "      <td>-10</td>\n",
       "      <td>65.421154</td>\n",
       "      <td>1.522503e+06</td>\n",
       "      <td>143388.108333</td>\n",
       "      <td>149560.758333</td>\n",
       "      <td>0.907098</td>\n",
       "      <td>0.731278</td>\n",
       "      <td>0.325293</td>\n",
       "      <td>0.698365</td>\n",
       "      <td>17.203299</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     bx_gse  by_gse    bz_gse  theta_gse     phi_gse  \\\n",
       "period  timedelta                                                      \n",
       "train_a 00:00:00  -6.149500  1.6455  0.844667   7.103667  165.420333   \n",
       "        01:00:00  -6.693833  0.7665  1.265167  10.183333  174.267833   \n",
       "\n",
       "                         bt   density       speed   temperature  dst  \\\n",
       "period  timedelta                                                      \n",
       "train_a 00:00:00   7.140000  1.174167  355.278250  68880.341667   -7   \n",
       "        01:00:00   7.284833  0.815917  330.080583  52203.225000  -10   \n",
       "\n",
       "                   smoothed_ssn     gse_x_ace      gse_y_ace      gse_z_ace  \\\n",
       "period  timedelta                                                             \n",
       "train_a 00:00:00      65.400000  1.522377e+06  143704.600000  149496.700000   \n",
       "        01:00:00      65.421154  1.522503e+06  143388.108333  149560.758333   \n",
       "\n",
       "                   by_gse_std  bz_gse_std    bt_std  density_std  speed_std  \n",
       "period  timedelta                                                            \n",
       "train_a 00:00:00     1.224754    0.580028  1.607043     0.479647  15.025364  \n",
       "        01:00:00     0.907098    0.731278  0.325293     0.698365  17.203299  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final = agg_std(df_final, sw)\n",
    "print ('df_final.shape:', df_final.shape)\n",
    "print ('Number of nulls:', df_final.isnull().sum().sum() )\n",
    "df_final.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>period</th>\n",
       "      <th>bx_gse</th>\n",
       "      <th>by_gse</th>\n",
       "      <th>bz_gse</th>\n",
       "      <th>theta_gse</th>\n",
       "      <th>phi_gse</th>\n",
       "      <th>bt</th>\n",
       "      <th>density</th>\n",
       "      <th>speed</th>\n",
       "      <th>temperature</th>\n",
       "      <th>dst</th>\n",
       "      <th>smoothed_ssn</th>\n",
       "      <th>gse_x_ace</th>\n",
       "      <th>gse_y_ace</th>\n",
       "      <th>gse_z_ace</th>\n",
       "      <th>by_gse_std</th>\n",
       "      <th>bz_gse_std</th>\n",
       "      <th>bt_std</th>\n",
       "      <th>density_std</th>\n",
       "      <th>speed_std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timedelta</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>00:00:00</th>\n",
       "      <td>train_a</td>\n",
       "      <td>-6.149500</td>\n",
       "      <td>1.6455</td>\n",
       "      <td>0.844667</td>\n",
       "      <td>7.103667</td>\n",
       "      <td>165.420333</td>\n",
       "      <td>7.140000</td>\n",
       "      <td>1.174167</td>\n",
       "      <td>355.278250</td>\n",
       "      <td>68880.341667</td>\n",
       "      <td>-7</td>\n",
       "      <td>65.400000</td>\n",
       "      <td>1.522377e+06</td>\n",
       "      <td>143704.600000</td>\n",
       "      <td>149496.700000</td>\n",
       "      <td>1.224754</td>\n",
       "      <td>0.580028</td>\n",
       "      <td>1.607043</td>\n",
       "      <td>0.479647</td>\n",
       "      <td>15.025364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01:00:00</th>\n",
       "      <td>train_a</td>\n",
       "      <td>-6.693833</td>\n",
       "      <td>0.7665</td>\n",
       "      <td>1.265167</td>\n",
       "      <td>10.183333</td>\n",
       "      <td>174.267833</td>\n",
       "      <td>7.284833</td>\n",
       "      <td>0.815917</td>\n",
       "      <td>330.080583</td>\n",
       "      <td>52203.225000</td>\n",
       "      <td>-10</td>\n",
       "      <td>65.421154</td>\n",
       "      <td>1.522503e+06</td>\n",
       "      <td>143388.108333</td>\n",
       "      <td>149560.758333</td>\n",
       "      <td>0.907098</td>\n",
       "      <td>0.731278</td>\n",
       "      <td>0.325293</td>\n",
       "      <td>0.698365</td>\n",
       "      <td>17.203299</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            period    bx_gse  by_gse    bz_gse  theta_gse     phi_gse  \\\n",
       "timedelta                                                               \n",
       "00:00:00   train_a -6.149500  1.6455  0.844667   7.103667  165.420333   \n",
       "01:00:00   train_a -6.693833  0.7665  1.265167  10.183333  174.267833   \n",
       "\n",
       "                 bt   density       speed   temperature  dst  smoothed_ssn  \\\n",
       "timedelta                                                                    \n",
       "00:00:00   7.140000  1.174167  355.278250  68880.341667   -7     65.400000   \n",
       "01:00:00   7.284833  0.815917  330.080583  52203.225000  -10     65.421154   \n",
       "\n",
       "              gse_x_ace      gse_y_ace      gse_z_ace  by_gse_std  bz_gse_std  \\\n",
       "timedelta                                                                       \n",
       "00:00:00   1.522377e+06  143704.600000  149496.700000    1.224754    0.580028   \n",
       "01:00:00   1.522503e+06  143388.108333  149560.758333    0.907098    0.731278   \n",
       "\n",
       "             bt_std  density_std  speed_std  \n",
       "timedelta                                    \n",
       "00:00:00   1.607043     0.479647  15.025364  \n",
       "01:00:00   0.325293     0.698365  17.203299  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_to_save = df_final.reset_index(level='period')\n",
    "df_to_save.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>period</th>\n",
       "      <th>bx_gse</th>\n",
       "      <th>by_gse</th>\n",
       "      <th>bz_gse</th>\n",
       "      <th>theta_gse</th>\n",
       "      <th>phi_gse</th>\n",
       "      <th>bt</th>\n",
       "      <th>density</th>\n",
       "      <th>speed</th>\n",
       "      <th>temperature</th>\n",
       "      <th>dst</th>\n",
       "      <th>smoothed_ssn</th>\n",
       "      <th>gse_x_ace</th>\n",
       "      <th>gse_y_ace</th>\n",
       "      <th>gse_z_ace</th>\n",
       "      <th>by_gse_std</th>\n",
       "      <th>bz_gse_std</th>\n",
       "      <th>bt_std</th>\n",
       "      <th>density_std</th>\n",
       "      <th>speed_std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timedelta</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2435 days 22:00:00</th>\n",
       "      <td>train_c</td>\n",
       "      <td>-2.3850</td>\n",
       "      <td>2.860333</td>\n",
       "      <td>-2.221667</td>\n",
       "      <td>-29.645667</td>\n",
       "      <td>130.601500</td>\n",
       "      <td>4.716667</td>\n",
       "      <td>2.148917</td>\n",
       "      <td>343.488167</td>\n",
       "      <td>41803.641667</td>\n",
       "      <td>-15</td>\n",
       "      <td>42.5</td>\n",
       "      <td>1426937.9</td>\n",
       "      <td>234813.7</td>\n",
       "      <td>140701.8</td>\n",
       "      <td>0.876128</td>\n",
       "      <td>1.328527</td>\n",
       "      <td>0.202373</td>\n",
       "      <td>0.680983</td>\n",
       "      <td>5.550519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2435 days 23:00:00</th>\n",
       "      <td>train_c</td>\n",
       "      <td>-2.2515</td>\n",
       "      <td>2.619333</td>\n",
       "      <td>-2.821000</td>\n",
       "      <td>-39.064000</td>\n",
       "      <td>130.277667</td>\n",
       "      <td>4.554833</td>\n",
       "      <td>2.283583</td>\n",
       "      <td>343.848750</td>\n",
       "      <td>36191.908333</td>\n",
       "      <td>-14</td>\n",
       "      <td>42.5</td>\n",
       "      <td>1426937.9</td>\n",
       "      <td>234813.7</td>\n",
       "      <td>140701.8</td>\n",
       "      <td>0.488619</td>\n",
       "      <td>0.599527</td>\n",
       "      <td>0.049076</td>\n",
       "      <td>0.918391</td>\n",
       "      <td>2.906941</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     period  bx_gse    by_gse    bz_gse  theta_gse  \\\n",
       "timedelta                                                            \n",
       "2435 days 22:00:00  train_c -2.3850  2.860333 -2.221667 -29.645667   \n",
       "2435 days 23:00:00  train_c -2.2515  2.619333 -2.821000 -39.064000   \n",
       "\n",
       "                       phi_gse        bt   density       speed   temperature  \\\n",
       "timedelta                                                                      \n",
       "2435 days 22:00:00  130.601500  4.716667  2.148917  343.488167  41803.641667   \n",
       "2435 days 23:00:00  130.277667  4.554833  2.283583  343.848750  36191.908333   \n",
       "\n",
       "                    dst  smoothed_ssn  gse_x_ace  gse_y_ace  gse_z_ace  \\\n",
       "timedelta                                                                \n",
       "2435 days 22:00:00  -15          42.5  1426937.9   234813.7   140701.8   \n",
       "2435 days 23:00:00  -14          42.5  1426937.9   234813.7   140701.8   \n",
       "\n",
       "                    by_gse_std  bz_gse_std    bt_std  density_std  speed_std  \n",
       "timedelta                                                                     \n",
       "2435 days 22:00:00    0.876128    1.328527  0.202373     0.680983   5.550519  \n",
       "2435 days 23:00:00    0.488619    0.599527  0.049076     0.918391   2.906941  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_to_save.tail(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_to_save.to_csv('processed_df.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}