PandasTutorial-Part09.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas Tutorial - Part 9\n",
    "\n",
    "# Working with Dates and Time Series Data\n",
    "\n",
    "### Implementation: Ali Moghanni\n",
    "\n",
    "*Resources:*\n",
    "\n",
    "This Jupyter notebook can be obtained at [https://github.com/alimoghanni/Pandas](https://github.com/alimoghanni/Pandas).\n",
    "\n",
    "updated: **2020-04-02**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Preamble: useful toolboxes, librairies, functions, etc.\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#d_parser = lambda x: pd.datetime.strptime(x, '%Y-%m-%d %I-%p')\n",
    "#df = pd.read_csv('DatetimeTimeseries.csv', parse_dates=['Date'], date_parser=d_parser)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Symbol</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-03-13 08-PM</td>\n",
       "      <td>ETHUSD</td>\n",
       "      <td>129.94</td>\n",
       "      <td>131.82</td>\n",
       "      <td>126.87</td>\n",
       "      <td>128.71</td>\n",
       "      <td>1940673.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-03-13 07-PM</td>\n",
       "      <td>ETHUSD</td>\n",
       "      <td>119.51</td>\n",
       "      <td>132.02</td>\n",
       "      <td>117.10</td>\n",
       "      <td>129.94</td>\n",
       "      <td>7579741.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03-13 06-PM</td>\n",
       "      <td>ETHUSD</td>\n",
       "      <td>124.47</td>\n",
       "      <td>124.85</td>\n",
       "      <td>115.50</td>\n",
       "      <td>119.51</td>\n",
       "      <td>4898735.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-03-13 05-PM</td>\n",
       "      <td>ETHUSD</td>\n",
       "      <td>124.08</td>\n",
       "      <td>127.42</td>\n",
       "      <td>121.63</td>\n",
       "      <td>124.47</td>\n",
       "      <td>2753450.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-03-13 04-PM</td>\n",
       "      <td>ETHUSD</td>\n",
       "      <td>124.85</td>\n",
       "      <td>129.51</td>\n",
       "      <td>120.17</td>\n",
       "      <td>124.08</td>\n",
       "      <td>4461424.71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Date  Symbol    Open    High     Low   Close      Volume\n",
       "0  2020-03-13 08-PM  ETHUSD  129.94  131.82  126.87  128.71  1940673.93\n",
       "1  2020-03-13 07-PM  ETHUSD  119.51  132.02  117.10  129.94  7579741.09\n",
       "2  2020-03-13 06-PM  ETHUSD  124.47  124.85  115.50  119.51  4898735.81\n",
       "3  2020-03-13 05-PM  ETHUSD  124.08  127.42  121.63  124.47  2753450.92\n",
       "4  2020-03-13 04-PM  ETHUSD  124.85  129.51  120.17  124.08  4461424.71"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('DatetimeTimeseries.csv')\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(23674, 7)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2020-03-13 08-PM'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[0, 'Date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'str' object has no attribute 'day_name'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-6-55e7fa569c9a>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'Date'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mday_name\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m: 'str' object has no attribute 'day_name'"
     ]
    }
   ],
   "source": [
    "df.loc[0, 'Date'].day_name()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date'] = pd.to_datetime(df['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date'] = pd.to_datetime(df['Date'], format='%Y-%m-%d %I-%p')\n",
    "\n",
    "df['Date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[0, 'Date'].day_name()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "d_parser = lambda x: pd.datetime.strptime(x, '%Y-%m-%d %I-%p')\n",
    "df = pd.read_csv('DatetimeTimeseries.csv', parse_dates=['Date'], date_parser=d_parser)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date'].dt.day_name()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['DayOfWeek'] = df['Date'].dt.day_name()\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Date'].max() - df['Date'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filt = (df['Date'] >= '2020')\n",
    "\n",
    "df.loc[filt]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filt = (df['Date'] > '2019') & (df['Date'] < '2020')\n",
    "\n",
    "df.loc[filt]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filt = (df['Date'] > pd.to_datetime('2019-01-01')) & (df['Date'] < pd.to_datetime('2020-01-01'))\n",
    "\n",
    "df.loc[filt]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.set_index('Date', inplace=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['2019']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['2020-01':'2020-01-10']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['2020-01':'2020-01-10']['Close'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['2020-01-01']['High'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# resampling\n",
    "\n",
    "highs = df['High'].resample('D').max()  # H, 2D, 3D, W"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "highs['2020-01-01']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "\n",
    "highs.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.resample('W').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df.resample('W').agg({'High':'max', 'Low': 'min', 'Close':'mean', 'Volume':'sum'})\n",
    "\n",
    "df1.head()"
   ]
  }
 ],
 "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}