feature-transformation/time-series/pandas-aggregate-weekly.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Datetime Aggregates in Pandas Tutorial"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook explains how to aggregate time series data in `pandas`.\n",
    "\n",
    "This notebook will use gold and silver price data from `rdatasets` for this tutorial"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Packages"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The documentation for each package used in this tutorial is linked below:\n",
    "* [pandas](https://pandas.pydata.org/docs/)\n",
    "* [statsmodels](https://www.statsmodels.org/stable/index.html)\n",
    "    * [statsmodels.api](https://www.statsmodels.org/stable/api.html#statsmodels-api)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import statsmodels.api as sm\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create initial dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data is from `rdatasets` imported using the Python package `statsmodels`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 9132 entries, 0 to 9131\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   date    9132 non-null   object \n",
      " 1   gold    9132 non-null   float64\n",
      " 2   silver  9132 non-null   float64\n",
      "dtypes: float64(2), object(1)\n",
      "memory usage: 214.2+ KB\n"
     ]
    }
   ],
   "source": [
    "df = sm.datasets.get_rdataset('GoldSilver', 'AER').data.reset_index().rename(columns={'index': 'date'})\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['date'] = pd.to_datetime(df.date)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Time series aggregation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `pandas` function `resample` can be used to create aggregations on specified windows.  Here, a weekly aggregate of the daily gold and silver price data will be created.  \n",
    "\n",
    "First, a datetime index needs to be created from the **date** column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.set_index('date', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The parameter **origin** is used to specify the day of week to start the aggregation on.  January 1, 1978 is a Sunday, so each of these aggreagtions will start on Sunday and aggregate over the subsequent week."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "weekly_resample = df.resample('7D', origin=pd.to_datetime('1978-01-01'))\n",
    "aggregated_df = weekly_resample.agg(['min', 'mean', 'max', 'std'])\n",
    "aggregated_df.columns = ['_'.join(col).strip() + '_week' for col in aggregated_df.columns.values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gold_min_week</th>\n",
       "      <th>gold_mean_week</th>\n",
       "      <th>gold_max_week</th>\n",
       "      <th>gold_std_week</th>\n",
       "      <th>silver_min_week</th>\n",
       "      <th>silver_mean_week</th>\n",
       "      <th>silver_max_week</th>\n",
       "      <th>silver_std_week</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</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>1977-12-25</th>\n",
       "      <td>100.00</td>\n",
       "      <td>100.000</td>\n",
       "      <td>100.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>223.42</td>\n",
       "      <td>223.420</td>\n",
       "      <td>223.42</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-01-01</th>\n",
       "      <td>100.00</td>\n",
       "      <td>100.000</td>\n",
       "      <td>100.00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>223.42</td>\n",
       "      <td>226.604</td>\n",
       "      <td>229.84</td>\n",
       "      <td>2.596657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-01-08</th>\n",
       "      <td>100.88</td>\n",
       "      <td>101.586</td>\n",
       "      <td>102.62</td>\n",
       "      <td>0.796825</td>\n",
       "      <td>227.89</td>\n",
       "      <td>230.050</td>\n",
       "      <td>232.55</td>\n",
       "      <td>1.846334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-01-15</th>\n",
       "      <td>100.87</td>\n",
       "      <td>101.832</td>\n",
       "      <td>103.09</td>\n",
       "      <td>0.796976</td>\n",
       "      <td>227.96</td>\n",
       "      <td>229.686</td>\n",
       "      <td>232.49</td>\n",
       "      <td>1.755130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-01-22</th>\n",
       "      <td>102.49</td>\n",
       "      <td>103.306</td>\n",
       "      <td>103.62</td>\n",
       "      <td>0.475742</td>\n",
       "      <td>230.37</td>\n",
       "      <td>232.798</td>\n",
       "      <td>234.50</td>\n",
       "      <td>1.777630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-01-29</th>\n",
       "      <td>101.96</td>\n",
       "      <td>102.578</td>\n",
       "      <td>103.49</td>\n",
       "      <td>0.636530</td>\n",
       "      <td>226.91</td>\n",
       "      <td>229.116</td>\n",
       "      <td>231.61</td>\n",
       "      <td>1.936835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-02-05</th>\n",
       "      <td>101.90</td>\n",
       "      <td>102.572</td>\n",
       "      <td>103.15</td>\n",
       "      <td>0.505193</td>\n",
       "      <td>224.98</td>\n",
       "      <td>227.506</td>\n",
       "      <td>229.90</td>\n",
       "      <td>1.875361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-02-12</th>\n",
       "      <td>103.92</td>\n",
       "      <td>104.936</td>\n",
       "      <td>105.87</td>\n",
       "      <td>0.850312</td>\n",
       "      <td>228.16</td>\n",
       "      <td>231.506</td>\n",
       "      <td>234.01</td>\n",
       "      <td>2.412370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-02-19</th>\n",
       "      <td>105.14</td>\n",
       "      <td>105.956</td>\n",
       "      <td>107.23</td>\n",
       "      <td>0.822028</td>\n",
       "      <td>229.81</td>\n",
       "      <td>232.392</td>\n",
       "      <td>234.55</td>\n",
       "      <td>1.784550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-02-26</th>\n",
       "      <td>106.20</td>\n",
       "      <td>107.076</td>\n",
       "      <td>107.60</td>\n",
       "      <td>0.538451</td>\n",
       "      <td>229.83</td>\n",
       "      <td>231.092</td>\n",
       "      <td>232.22</td>\n",
       "      <td>1.043681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-03-05</th>\n",
       "      <td>108.89</td>\n",
       "      <td>109.916</td>\n",
       "      <td>111.03</td>\n",
       "      <td>0.791789</td>\n",
       "      <td>235.33</td>\n",
       "      <td>244.862</td>\n",
       "      <td>248.98</td>\n",
       "      <td>5.619695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-03-12</th>\n",
       "      <td>106.05</td>\n",
       "      <td>108.042</td>\n",
       "      <td>110.39</td>\n",
       "      <td>1.638740</td>\n",
       "      <td>248.09</td>\n",
       "      <td>251.940</td>\n",
       "      <td>254.95</td>\n",
       "      <td>2.875239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-03-19</th>\n",
       "      <td>102.65</td>\n",
       "      <td>104.458</td>\n",
       "      <td>105.53</td>\n",
       "      <td>1.355865</td>\n",
       "      <td>240.71</td>\n",
       "      <td>243.672</td>\n",
       "      <td>246.05</td>\n",
       "      <td>2.578317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-03-26</th>\n",
       "      <td>104.66</td>\n",
       "      <td>106.180</td>\n",
       "      <td>107.29</td>\n",
       "      <td>1.119710</td>\n",
       "      <td>246.81</td>\n",
       "      <td>249.628</td>\n",
       "      <td>253.55</td>\n",
       "      <td>2.764710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-04-02</th>\n",
       "      <td>103.33</td>\n",
       "      <td>103.968</td>\n",
       "      <td>105.27</td>\n",
       "      <td>0.771894</td>\n",
       "      <td>241.89</td>\n",
       "      <td>243.780</td>\n",
       "      <td>246.38</td>\n",
       "      <td>1.809627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-04-09</th>\n",
       "      <td>102.99</td>\n",
       "      <td>104.120</td>\n",
       "      <td>105.07</td>\n",
       "      <td>0.806908</td>\n",
       "      <td>241.23</td>\n",
       "      <td>243.816</td>\n",
       "      <td>247.27</td>\n",
       "      <td>2.410940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-04-16</th>\n",
       "      <td>99.11</td>\n",
       "      <td>99.996</td>\n",
       "      <td>100.93</td>\n",
       "      <td>0.852397</td>\n",
       "      <td>230.53</td>\n",
       "      <td>232.784</td>\n",
       "      <td>235.15</td>\n",
       "      <td>2.045527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-04-23</th>\n",
       "      <td>97.45</td>\n",
       "      <td>98.234</td>\n",
       "      <td>99.41</td>\n",
       "      <td>0.856756</td>\n",
       "      <td>225.99</td>\n",
       "      <td>228.588</td>\n",
       "      <td>230.49</td>\n",
       "      <td>1.848194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-04-30</th>\n",
       "      <td>98.38</td>\n",
       "      <td>99.778</td>\n",
       "      <td>100.92</td>\n",
       "      <td>1.143119</td>\n",
       "      <td>228.87</td>\n",
       "      <td>232.358</td>\n",
       "      <td>234.71</td>\n",
       "      <td>2.439912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978-05-07</th>\n",
       "      <td>100.11</td>\n",
       "      <td>100.874</td>\n",
       "      <td>101.51</td>\n",
       "      <td>0.644306</td>\n",
       "      <td>232.08</td>\n",
       "      <td>233.124</td>\n",
       "      <td>234.55</td>\n",
       "      <td>0.905003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            gold_min_week  gold_mean_week  gold_max_week  gold_std_week  \\\n",
       "date                                                                      \n",
       "1977-12-25         100.00         100.000         100.00            NaN   \n",
       "1978-01-01         100.00         100.000         100.00       0.000000   \n",
       "1978-01-08         100.88         101.586         102.62       0.796825   \n",
       "1978-01-15         100.87         101.832         103.09       0.796976   \n",
       "1978-01-22         102.49         103.306         103.62       0.475742   \n",
       "1978-01-29         101.96         102.578         103.49       0.636530   \n",
       "1978-02-05         101.90         102.572         103.15       0.505193   \n",
       "1978-02-12         103.92         104.936         105.87       0.850312   \n",
       "1978-02-19         105.14         105.956         107.23       0.822028   \n",
       "1978-02-26         106.20         107.076         107.60       0.538451   \n",
       "1978-03-05         108.89         109.916         111.03       0.791789   \n",
       "1978-03-12         106.05         108.042         110.39       1.638740   \n",
       "1978-03-19         102.65         104.458         105.53       1.355865   \n",
       "1978-03-26         104.66         106.180         107.29       1.119710   \n",
       "1978-04-02         103.33         103.968         105.27       0.771894   \n",
       "1978-04-09         102.99         104.120         105.07       0.806908   \n",
       "1978-04-16          99.11          99.996         100.93       0.852397   \n",
       "1978-04-23          97.45          98.234          99.41       0.856756   \n",
       "1978-04-30          98.38          99.778         100.92       1.143119   \n",
       "1978-05-07         100.11         100.874         101.51       0.644306   \n",
       "\n",
       "            silver_min_week  silver_mean_week  silver_max_week  \\\n",
       "date                                                             \n",
       "1977-12-25           223.42           223.420           223.42   \n",
       "1978-01-01           223.42           226.604           229.84   \n",
       "1978-01-08           227.89           230.050           232.55   \n",
       "1978-01-15           227.96           229.686           232.49   \n",
       "1978-01-22           230.37           232.798           234.50   \n",
       "1978-01-29           226.91           229.116           231.61   \n",
       "1978-02-05           224.98           227.506           229.90   \n",
       "1978-02-12           228.16           231.506           234.01   \n",
       "1978-02-19           229.81           232.392           234.55   \n",
       "1978-02-26           229.83           231.092           232.22   \n",
       "1978-03-05           235.33           244.862           248.98   \n",
       "1978-03-12           248.09           251.940           254.95   \n",
       "1978-03-19           240.71           243.672           246.05   \n",
       "1978-03-26           246.81           249.628           253.55   \n",
       "1978-04-02           241.89           243.780           246.38   \n",
       "1978-04-09           241.23           243.816           247.27   \n",
       "1978-04-16           230.53           232.784           235.15   \n",
       "1978-04-23           225.99           228.588           230.49   \n",
       "1978-04-30           228.87           232.358           234.71   \n",
       "1978-05-07           232.08           233.124           234.55   \n",
       "\n",
       "            silver_std_week  \n",
       "date                         \n",
       "1977-12-25              NaN  \n",
       "1978-01-01         2.596657  \n",
       "1978-01-08         1.846334  \n",
       "1978-01-15         1.755130  \n",
       "1978-01-22         1.777630  \n",
       "1978-01-29         1.936835  \n",
       "1978-02-05         1.875361  \n",
       "1978-02-12         2.412370  \n",
       "1978-02-19         1.784550  \n",
       "1978-02-26         1.043681  \n",
       "1978-03-05         5.619695  \n",
       "1978-03-12         2.875239  \n",
       "1978-03-19         2.578317  \n",
       "1978-03-26         2.764710  \n",
       "1978-04-02         1.809627  \n",
       "1978-04-09         2.410940  \n",
       "1978-04-16         2.045527  \n",
       "1978-04-23         1.848194  \n",
       "1978-04-30         2.439912  \n",
       "1978-05-07         0.905003  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aggregated_df.head(20)"
   ]
  }
 ],
 "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}