{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Every Function You Can (Should) Use in Pandas to Manipulate Time Series\n## From basic time series metrics to window functions\n\n\n \n Photo by \n Jordan Benton\n on \n Pexels\n \n","metadata":{}},{"cell_type":"markdown","source":"## Setup","metadata":{}},{"cell_type":"code","source":"import warnings\n\nimport matplotlib.pyplot as plt\nimport numpy as np\nimport pandas as pd\nimport seaborn as sns\nfrom matplotlib import rcParams\n\nrcParams[\"xtick.labelsize\"] = 15\nrcParams[\"ytick.labelsize\"] = 15\nrcParams[\"legend.fontsize\"] = \"small\"\n\npd.set_option(\"precision\", 2)\nwarnings.filterwarnings(\"ignore\")","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:26.357288Z","iopub.execute_input":"2021-07-13T12:13:26.357694Z","iopub.status.idle":"2021-07-13T12:13:26.363922Z","shell.execute_reply.started":"2021-07-13T12:13:26.357656Z","shell.execute_reply":"2021-07-13T12:13:26.363171Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Introduction to this project on Time Series Forecasting","metadata":{}},{"cell_type":"markdown","source":"Recently, the Optiver Realized Volatility Prediction competition has been launched on Kaggle. As the name suggests, it is a time series forecasting challenge.\n\nI wanted to participate, but it turns out my knowledge in time series couldn't even begin to suffice to participate in a competition of such a magnitude. So, I accepted this as the 'kick in the pants' I needed to start paying serious attention to this large sphere of ML.\n\nAs the first step, I wanted to learn and teach every single Pandas function you can use to manipulate time-series data. These functions are the basic requirements for dealing with any time series data you encounter in the wild.\n\nI have got rather cool and interesting articles/notebooks planned on this topic, and today, you will be reading the first taste of what is to come. Enjoy!","metadata":{}},{"cell_type":"markdown","source":"## Table of Contents ","metadata":{}},{"cell_type":"markdown","source":"#### [**1. Basic date and time functions**](#1)\n * [1.1 Importing time series data](#1.1)\n * [1.2 Pandas TimeStamp](#1.2)\n * [1.3 Sequence of dates (timestamps)](#1.3)\n * [1.4 Slicing](#1.4)\n\n#### [**2. Missing data imputation/interpolation in time series**](#2)\n * [2.1 Mean, median and mode imputation](#2.1)\n * [2.2 Forward and backward filling](#2.2)\n * [2.3 Using pd.interpolate](#2.3)\n * [2.4 Model based imputation with KNN](#2.4)\n\n#### [**3. Basic time series calculations and metrics**](#3)\n * [3.1 Shifts and lags](#3.1)\n * [3.2 Percentage changes](#3.2)\n\n#### [**4. Resampling - upsample and downsample**](#4)\n * [4.1 Changing the frequency with `asfreq`](#4.1)\n * [4.2 Downsampling with resample and aggregating](#4.2)\n * [4.3 Upsampling with resample and interpolating](#4.3)\n * [4.4 Plotting the resampled data](#4.4)\n\n#### [**5. Comparing the growth of multiple time series**](#5)\n\n#### [**6. Window functions**](#6)\n * [6.1 Rolling window functions](#6.1)\n * [6.2 Expanding window functions](#6.2)\n\n#### [**7. Summary**](#7)","metadata":{}},{"cell_type":"markdown","source":"## 1. Basic date and time functions in Pandas","metadata":{}},{"cell_type":"markdown","source":"### 1.1 Importing time series data ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nWhen using the `pd.read_csv` function to import time series, there are 2 arguments you should always use - `parse_dates` and `index_col`:","metadata":{}},{"cell_type":"code","source":"# Import Apple/Google stock prices\naapl_googl = pd.read_csv(\n \"https://raw.githubusercontent.com/BexTuychiev/medium_stories/master/2021/july/3_time_series_manipulation/data/apple_google.csv\",\n parse_dates=[\"Date\"],\n index_col=\"Date\",\n).dropna()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:26.373625Z","iopub.execute_input":"2021-07-13T12:13:26.374312Z","iopub.status.idle":"2021-07-13T12:13:26.602074Z","shell.execute_reply.started":"2021-07-13T12:13:26.374275Z","shell.execute_reply":"2021-07-13T12:13:26.600836Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"aapl_googl.head()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:26.603818Z","iopub.execute_input":"2021-07-13T12:13:26.604118Z","iopub.status.idle":"2021-07-13T12:13:26.61896Z","shell.execute_reply.started":"2021-07-13T12:13:26.604091Z","shell.execute_reply":"2021-07-13T12:13:26.617545Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Import S&P500 stock prices\nsp500 = pd.read_csv(\n \"https://raw.githubusercontent.com/BexTuychiev/medium_stories/master/2021/july/3_time_series_manipulation/data/sp500.csv\",\n parse_dates=[\"date\"],\n index_col=\"date\",\n)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:26.621751Z","iopub.execute_input":"2021-07-13T12:13:26.622309Z","iopub.status.idle":"2021-07-13T12:13:26.812128Z","shell.execute_reply.started":"2021-07-13T12:13:26.622253Z","shell.execute_reply":"2021-07-13T12:13:26.81109Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sp500.head()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:26.813787Z","iopub.execute_input":"2021-07-13T12:13:26.81421Z","iopub.status.idle":"2021-07-13T12:13:26.825074Z","shell.execute_reply.started":"2021-07-13T12:13:26.814174Z","shell.execute_reply":"2021-07-13T12:13:26.823799Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"`parse_dates` converts date-like strings to DateTime objects and `index_col` sets the passed column as the index. This operation is the basis for all time-series manipulation you will do with Pandas.\n\nWhen you don't know which column contains dates upon importing, you can perform the date conversion using `pd.to_datetime` function afterward:","metadata":{}},{"cell_type":"code","source":"# Import the data with unknown date column\nsp500 = pd.read_csv(\"https://raw.githubusercontent.com/BexTuychiev/medium_stories/master/2021/july/3_time_series_manipulation/data/sp500.csv\")\n\n# Inspect the dtypes\nsp500.dtypes","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:26.826741Z","iopub.execute_input":"2021-07-13T12:13:26.827188Z","iopub.status.idle":"2021-07-13T12:13:26.938048Z","shell.execute_reply.started":"2021-07-13T12:13:26.827141Z","shell.execute_reply":"2021-07-13T12:13:26.936867Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now, inspect the datetime format string:","metadata":{}},{"cell_type":"code","source":"sp500.head()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:26.939378Z","iopub.execute_input":"2021-07-13T12:13:26.93971Z","iopub.status.idle":"2021-07-13T12:13:26.951915Z","shell.execute_reply.started":"2021-07-13T12:13:26.93967Z","shell.execute_reply":"2021-07-13T12:13:26.950745Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It is in the format \"%Y-%m-%d\" (full list of datetime format strings can be found [here](https://docs.python.org/3/library/datetime.html#strftime-and-strptime-behavior)). Pass this to `pd.to_datetime`:","metadata":{"execution":{"iopub.status.busy":"2021-07-13T11:56:47.747469Z","iopub.execute_input":"2021-07-13T11:56:47.748045Z","iopub.status.idle":"2021-07-13T11:56:47.753626Z","shell.execute_reply.started":"2021-07-13T11:56:47.748011Z","shell.execute_reply":"2021-07-13T11:56:47.7525Z"}}},{"cell_type":"code","source":"sp500[\"date\"] = pd.to_datetime(sp500[\"date\"], format=\"%Y-%m-%d\", errors=\"coerce\")\n\n# Check if the conversion is successful\nassert sp500[\"date\"].dtype == \"datetime64[ns]\"","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:26.953538Z","iopub.execute_input":"2021-07-13T12:13:26.95392Z","iopub.status.idle":"2021-07-13T12:13:26.96851Z","shell.execute_reply.started":"2021-07-13T12:13:26.953887Z","shell.execute_reply":"2021-07-13T12:13:26.967401Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Passing a format string to `pd.to_datetime` significantly speeds up the conversion for large datasets. Set `errors` to \"coerce\" to mark invalid dates as `NaT` (not a date, i.e. - missing).","metadata":{"execution":{"iopub.status.busy":"2021-07-13T11:56:59.725609Z","iopub.execute_input":"2021-07-13T11:56:59.726301Z","iopub.status.idle":"2021-07-13T11:56:59.733515Z","shell.execute_reply.started":"2021-07-13T11:56:59.726258Z","shell.execute_reply":"2021-07-13T11:56:59.732081Z"}}},{"cell_type":"markdown","source":"After conversion, set the DateTime column as index (a strict requirement for best time series analysis):","metadata":{"execution":{"iopub.status.busy":"2021-07-13T11:57:04.767386Z","iopub.execute_input":"2021-07-13T11:57:04.767916Z","iopub.status.idle":"2021-07-13T11:57:04.773259Z","shell.execute_reply.started":"2021-07-13T11:57:04.767882Z","shell.execute_reply":"2021-07-13T11:57:04.772153Z"}}},{"cell_type":"code","source":"sp500.set_index(\"date\", inplace=True)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:26.971393Z","iopub.execute_input":"2021-07-13T12:13:26.971745Z","iopub.status.idle":"2021-07-13T12:13:26.982951Z","shell.execute_reply.started":"2021-07-13T12:13:26.971713Z","shell.execute_reply":"2021-07-13T12:13:26.981735Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 1.2 Pandas TimeStamp ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nThe basic date data structure in Pandas is a timestamp:","metadata":{}},{"cell_type":"code","source":"stamp = pd.Timestamp(\"2020/12/26\") # You can pass any date-like string\nstamp","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:26.98489Z","iopub.execute_input":"2021-07-13T12:13:26.985193Z","iopub.status.idle":"2021-07-13T12:13:27.001513Z","shell.execute_reply.started":"2021-07-13T12:13:26.985165Z","shell.execute_reply":"2021-07-13T12:13:27.000313Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"You can make even more granular timestamps using the right format or, better yet, using the `datetime` module:","metadata":{}},{"cell_type":"code","source":"from datetime import datetime\n\nstamp = pd.Timestamp(\n datetime(year=2021, month=10, day=5, hour=13, minute=59, second=59)\n)\nstamp","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:27.003228Z","iopub.execute_input":"2021-07-13T12:13:27.003896Z","iopub.status.idle":"2021-07-13T12:13:27.01888Z","shell.execute_reply.started":"2021-07-13T12:13:27.003848Z","shell.execute_reply":"2021-07-13T12:13:27.017183Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"A full timestamp has useful attributes such as these:","metadata":{}},{"cell_type":"code","source":"attributes = [\n \".year\",\n \".month\",\n \".quarter\",\n \".day\",\n \".hour\",\n \".minute\",\n \".second\",\n \".weekday()\",\n \".dayofweek\",\n \".weekofyear\",\n \".dayofyear\",\n]\n\npd.DataFrame(\n {\n \"Attribute\": attributes,\n \"'2021-10-05 13:59:59'\": [\n eval(f\"stamp{attribute}\") for attribute in attributes\n ],\n }\n)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:27.020396Z","iopub.execute_input":"2021-07-13T12:13:27.020786Z","iopub.status.idle":"2021-07-13T12:13:27.044178Z","shell.execute_reply.started":"2021-07-13T12:13:27.020751Z","shell.execute_reply":"2021-07-13T12:13:27.043257Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 1.3 Sequence of dates (timestamps) ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nA `DateTime` column/index in pandas is represented as a series of `TimeStamp` objects.\n\n`pd.date_range` returns a special `DateTimeIndex` object that is a collection of `TimeStamps` with a custom frequency over a given range:","metadata":{}},{"cell_type":"code","source":"index = pd.date_range(start=\"2010-10-10\", end=\"2020-10-10\", freq=\"M\")\nindex","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:27.045278Z","iopub.execute_input":"2021-07-13T12:13:27.045709Z","iopub.status.idle":"2021-07-13T12:13:27.071332Z","shell.execute_reply.started":"2021-07-13T12:13:27.045679Z","shell.execute_reply":"2021-07-13T12:13:27.069925Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"After specifying the date range (from October 10, 2010, to the same date in 2020), we are telling pandas to generate `TimeStamps` on a monthly-basis with `freq='M'`:","metadata":{}},{"cell_type":"code","source":"index[0]","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:27.072584Z","iopub.execute_input":"2021-07-13T12:13:27.073336Z","iopub.status.idle":"2021-07-13T12:13:27.084076Z","shell.execute_reply.started":"2021-07-13T12:13:27.073265Z","shell.execute_reply":"2021-07-13T12:13:27.08285Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Another way to create date ranges is passing the start date and telling how many periods you want, and specifying the frequency:","metadata":{}},{"cell_type":"code","source":"pd.date_range(start=\"2020-01-01\", periods=5, freq=\"Y\")","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:27.086309Z","iopub.execute_input":"2021-07-13T12:13:27.087051Z","iopub.status.idle":"2021-07-13T12:13:27.104158Z","shell.execute_reply.started":"2021-07-13T12:13:27.086995Z","shell.execute_reply":"2021-07-13T12:13:27.10343Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Since we set the frequency to years, `date_range` with 5 periods returns 5 years/timestamp objects. The [list of frequency aliases](https://medium.com/r/?url=https%3A%2F%2Fpandas.pydata.org%2Fpandas-docs%2Fstable%2Fuser_guide%2Ftimeseries.html%23timeseries-offset-aliases) that can be passed to `freq` is large, so I will only mention the most important ones here:","metadata":{}},{"cell_type":"code","source":"aliases = [\"B\", \"D\", \"W\", \"M\", \"BM\", \"MS\", \"Q\", \"H\", \"A, Y\"]\nvalues = [\n \"Business days\",\n \"Calendar days\",\n \"Weekly\",\n \"Month end frequency\",\n \"Business month end frequency\",\n \"Month start frequency\",\n \"Quarterly\",\n \"Hourly\",\n \"Year end\",\n]\n\npd.DataFrame({\"Frequency Alias\": aliases, \"Definition\": values})","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:27.105258Z","iopub.execute_input":"2021-07-13T12:13:27.105593Z","iopub.status.idle":"2021-07-13T12:13:27.130983Z","shell.execute_reply.started":"2021-07-13T12:13:27.105559Z","shell.execute_reply":"2021-07-13T12:13:27.129629Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It is also possible to pass custom frequencies such as \"1h30min\", \"5D\", \"2W\", etc. Again, check out [this link](https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html#timeseries-offset-aliases) for the full info.","metadata":{}},{"cell_type":"markdown","source":"### 1.4 Slicing ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nSlicing time series data can be very intuitive if the index is a `DateTimeIndex`. You can use something called partial slicing:","metadata":{}},{"cell_type":"code","source":"aapl_googl[\"2010\":\"2015\"].sample(5) # All rows within 2010 and 2015","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:27.132864Z","iopub.execute_input":"2021-07-13T12:13:27.133293Z","iopub.status.idle":"2021-07-13T12:13:27.162668Z","shell.execute_reply.started":"2021-07-13T12:13:27.133249Z","shell.execute_reply":"2021-07-13T12:13:27.161547Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"aapl_googl[\"2012-4\":\"2012-12\"].sample(5) # rows within April and December of 2012","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:27.164195Z","iopub.execute_input":"2021-07-13T12:13:27.164628Z","iopub.status.idle":"2021-07-13T12:13:27.180037Z","shell.execute_reply.started":"2021-07-13T12:13:27.16458Z","shell.execute_reply":"2021-07-13T12:13:27.178773Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"You can even go down to hours, minutes, or seconds levels if the DateTime is granular enough.\n\nNote that pandas slices dates in closed intervals. For example, using \"2010\": \"2013\" returns rows for all 4 years - it does not exclude the end of the period like integer slicing.\n\nThis date slicing logic applies to other operations like choosing a specific column after the slice:","metadata":{}},{"cell_type":"code","source":"aapl_googl.loc[\"2012-10-10\":\"2012-12-10\", \"GOOG\"].head()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:27.181031Z","iopub.execute_input":"2021-07-13T12:13:27.181306Z","iopub.status.idle":"2021-07-13T12:13:27.199204Z","shell.execute_reply.started":"2021-07-13T12:13:27.18128Z","shell.execute_reply":"2021-07-13T12:13:27.198067Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 2. Missing data imputation or interpolation ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nMissing data is ubiquitous no matter the type of the dataset. This section is all about imputing it in the context of time series. \n\n> You may also hear it called **interpolation** of missing data in time series lingo.\n\nBesides the basic mean, median and mode imputation, some of the most common techniques include:\n\n1. Forward filling\n2. Backward filling\n3. Intermediate imputations with `pd.interpolate`\n\nWe will also discuss model-based imputation such as KNN imputing. Moreover, we will explore visual methods of comparing the efficiency of the techniques and choose the one that best fits the underlying distribution.","metadata":{}},{"cell_type":"markdown","source":"### 2.1 Mean, median and mode imputation ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nLet's start with the basics. We will randomly select data points in Apple/Google stock dataset and convert them to NaN:","metadata":{}},{"cell_type":"code","source":"# Choose 200 random\nrandom_indices = np.random.choice([_ for _ in range(len(aapl_googl))], size=200)\n\n# Mark the indices as missing\nclone = aapl_googl.copy(deep=True).drop(\"AAPL\", axis=1)\nclone.iloc[random_indices, 0] = np.nan","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:27.200965Z","iopub.execute_input":"2021-07-13T12:13:27.201432Z","iopub.status.idle":"2021-07-13T12:13:27.215196Z","shell.execute_reply.started":"2021-07-13T12:13:27.201352Z","shell.execute_reply":"2021-07-13T12:13:27.214023Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We will also create a function that plots the original distribution before and after an imputation(s) is performed:","metadata":{}},{"cell_type":"code","source":"def compare_dists(original_dist, imputed_dists: dict):\n \"\"\"\n Plot original_dist and imputed_dists on top of each other\n to see the difference in distributions.\n \"\"\"\n fig, ax = plt.subplots(figsize=(12, 7), dpi=140)\n # Plot the original\n sns.kdeplot(\n original_dist, linewidth=5, ax=ax, color=\"black\", label=\"Original dist.\"\n )\n for key, value in imputed_dists.items():\n sns.kdeplot(value, linewidth=3, label=key, ax=ax)\n\n plt.legend()\n plt.show();","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:27.217132Z","iopub.execute_input":"2021-07-13T12:13:27.217641Z","iopub.status.idle":"2021-07-13T12:13:27.228837Z","shell.execute_reply.started":"2021-07-13T12:13:27.217592Z","shell.execute_reply":"2021-07-13T12:13:27.227283Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We will start trying out techniques with `SimpleImputer` from Sklearn:","metadata":{}},{"cell_type":"code","source":"from sklearn.impute import SimpleImputer\n\nfor method in [\"mean\", \"median\", \"most_frequent\"]:\n clone[method] = SimpleImputer(strategy=method).fit_transform(\n clone[\"GOOG\"].values.reshape(-1, 1)\n )","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:27.230667Z","iopub.execute_input":"2021-07-13T12:13:27.231007Z","iopub.status.idle":"2021-07-13T12:13:27.668983Z","shell.execute_reply.started":"2021-07-13T12:13:27.230977Z","shell.execute_reply":"2021-07-13T12:13:27.667821Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's plot the original GOOG distribution against the 3 imputed features we just created:","metadata":{}},{"cell_type":"code","source":"compare_dists(\n clone[\"GOOG\"],\n {\"mean\": clone[\"mean\"], \"median\": clone[\"median\"], \"mode\": clone[\"most_frequent\"]},\n)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:27.670677Z","iopub.execute_input":"2021-07-13T12:13:27.671114Z","iopub.status.idle":"2021-07-13T12:13:28.240412Z","shell.execute_reply.started":"2021-07-13T12:13:27.671069Z","shell.execute_reply":"2021-07-13T12:13:28.239315Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It is hard to say which lines most closely resembles the black line, but I will go with the blue.","metadata":{}},{"cell_type":"code","source":"clone.drop([\"mean\", \"median\", \"most_frequent\"], axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:28.244982Z","iopub.execute_input":"2021-07-13T12:13:28.245324Z","iopub.status.idle":"2021-07-13T12:13:28.252202Z","shell.execute_reply.started":"2021-07-13T12:13:28.245291Z","shell.execute_reply":"2021-07-13T12:13:28.251062Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 2.2 Forward and backward filling ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nConsider this small distribution:","metadata":{}},{"cell_type":"code","source":"sample = pd.Series([np.nan, 2, 3, np.nan, 4, np.nan, np.nan, 5, 12, np.nan]).to_frame(\n name=\"original\"\n)\nsample","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:28.254878Z","iopub.execute_input":"2021-07-13T12:13:28.255237Z","iopub.status.idle":"2021-07-13T12:13:28.274781Z","shell.execute_reply.started":"2021-07-13T12:13:28.255198Z","shell.execute_reply":"2021-07-13T12:13:28.273633Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We will use both forward and backward filling and assign them back to the DataFrame as separate columns:","metadata":{}},{"cell_type":"code","source":"sample[\"ffill\"] = sample[\"original\"].ffill()\nsample[\"bfill\"] = sample[\"original\"].bfill()\n\nsample","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:28.276563Z","iopub.execute_input":"2021-07-13T12:13:28.27702Z","iopub.status.idle":"2021-07-13T12:13:28.302314Z","shell.execute_reply.started":"2021-07-13T12:13:28.276973Z","shell.execute_reply":"2021-07-13T12:13:28.301165Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It should be fairly obvious how these methods work once you examine the above output. \n\nNow, let's perform these methods on the Airquality in India dataset:","metadata":{}},{"cell_type":"code","source":"air_q = pd.read_csv(\n \"https://raw.githubusercontent.com/BexTuychiev/medium_stories/master/2021/july/3_time_series_manipulation/data/station_day.csv\",\n usecols=[\"Date\", \"NO2\"],\n parse_dates=[\"Date\"],\n index_col=\"Date\",\n)\n\nfor method in [\"ffill\", \"bfill\"]:\n air_q[method] = eval(f\"air_q['NO2'].{method}()\")\n\ncompare_dists(air_q[\"NO2\"], {\"ffill\": air_q[\"ffill\"], \"bfill\": air_q[\"bfill\"]})","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:28.303731Z","iopub.execute_input":"2021-07-13T12:13:28.304118Z","iopub.status.idle":"2021-07-13T12:13:30.767383Z","shell.execute_reply.started":"2021-07-13T12:13:28.304086Z","shell.execute_reply":"2021-07-13T12:13:30.766173Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Even though very basic, forward and backward filling actually works pretty well on climate and stocks data since the differences between nearby data points are small.","metadata":{}},{"cell_type":"markdown","source":"### 2.3 Using `pd.interpolate` ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nPandas provides a whole suite of other statistical imputation techniques in `pd.interpolate` function. Its `method` parameter accepts the name of the technique as a string.\n\nThe most popular ones are 'linear' and 'nearest,' but you can see the full list from the function's documentation. Here, we will only discuss those two.\n\nConsider this small distribution:","metadata":{}},{"cell_type":"code","source":"sample = pd.Series([1] + [np.nan] * 6 + [10]).to_frame(name=\"original\")\nsample","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:30.769066Z","iopub.execute_input":"2021-07-13T12:13:30.769535Z","iopub.status.idle":"2021-07-13T12:13:30.783351Z","shell.execute_reply.started":"2021-07-13T12:13:30.769468Z","shell.execute_reply":"2021-07-13T12:13:30.781868Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Once again, we apply the methods and assign their results back:","metadata":{}},{"cell_type":"code","source":"sample[\"linear\"] = sample.original.interpolate(method=\"linear\")\nsample[\"nearest\"] = sample.original.interpolate(method=\"nearest\")\n\nsample","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:30.784861Z","iopub.execute_input":"2021-07-13T12:13:30.785332Z","iopub.status.idle":"2021-07-13T12:13:30.808446Z","shell.execute_reply.started":"2021-07-13T12:13:30.785286Z","shell.execute_reply":"2021-07-13T12:13:30.807391Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Neat, huh? The linear method considers the distance between any two non-missing points as linearly spaced and finds a linear line that connects them (like `np.linspace`). 'Nearest' method should be understandable from its name and the above output.","metadata":{}},{"cell_type":"markdown","source":"### 2.4 Model based imputation with KNN ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nThe last method we will see is the K-Nearest-Neighbors algorithm. I won't detail how the algorithm works but only show how you can use it with Sklearn. If you want the details, I have a separate article [here](https://medium.com/r/?url=https%3A%2F%2Ftowardsdatascience.com%2Fgoing-beyond-the-simpleimputer-for-missing-data-imputation-dd8ba168d505%3Fsource%3Dyour_stories_page-------------------------------------).\n\nThe most important parameter of KNN is `k` - the number of neighbors. We will apply the technique to Apple/Google data with several values of `k` and find the best one the same way as we did in the previous sections:","metadata":{}},{"cell_type":"code","source":"from sklearn.impute import KNNImputer\n\nn_neighbors = [2, 3, 5, 7, 9]\n\nfor k in n_neighbors:\n imp = KNNImputer(n_neighbors=k)\n clone[f\"k={k}\"] = imp.fit_transform(clone[\"GOOG\"].values.reshape(-1, 1))\n\ncompare_dists(clone[\"GOOG\"], {f\"k={k}\": clone[f\"k={k}\"] for k in n_neighbors})","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:30.809952Z","iopub.execute_input":"2021-07-13T12:13:30.810336Z","iopub.status.idle":"2021-07-13T12:13:31.372601Z","shell.execute_reply.started":"2021-07-13T12:13:30.810303Z","shell.execute_reply":"2021-07-13T12:13:31.371769Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 3. Basic time series calculations ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nPandas offers basic functions to calculate the most common time series calculations. These are called shifts, lags, and something called a percentage change.","metadata":{}},{"cell_type":"markdown","source":"### 3.1 Shifts and lags ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nA common operation in time series is to move all data points one or more periods backward or forward to compare past and future values. You can do these operations using `shift` function of pandas. Let's see how to move the data points 1 and 2 periods into the future:","metadata":{}},{"cell_type":"code","source":"sp500 = pd.read_csv(\"https://raw.githubusercontent.com/BexTuychiev/medium_stories/master/2021/july/3_time_series_manipulation/data/sp500.csv\", parse_dates=[\"date\"], index_col=\"date\")\n\nsp500.head()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:31.37363Z","iopub.execute_input":"2021-07-13T12:13:31.374025Z","iopub.status.idle":"2021-07-13T12:13:31.487278Z","shell.execute_reply.started":"2021-07-13T12:13:31.373998Z","shell.execute_reply":"2021-07-13T12:13:31.485339Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sp500[\"shifted_1\"] = sp500[\"SP500\"].shift(periods=1) # the default\nsp500[\"shifted_2\"] = sp500[\"SP500\"].shift(periods=2)\n\nsp500.head(6)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:31.488797Z","iopub.execute_input":"2021-07-13T12:13:31.489121Z","iopub.status.idle":"2021-07-13T12:13:31.514945Z","shell.execute_reply.started":"2021-07-13T12:13:31.489089Z","shell.execute_reply":"2021-07-13T12:13:31.513463Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Shifting forward enables you to compare the current data point to those recorded one or more periods before.\n\nYou can also shift backward. This operation is also called \"lagging\":","metadata":{}},{"cell_type":"code","source":"sp500.drop([\"shifted_1\", \"shifted_2\"], axis=1, inplace=True)\n\nsp500[\"lagged_1\"] = sp500[\"SP500\"].shift(periods=-1)\nsp500[\"lagged_2\"] = sp500[\"SP500\"].shift(periods=-2)\n\nsp500.tail(6)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:31.516628Z","iopub.execute_input":"2021-07-13T12:13:31.516974Z","iopub.status.idle":"2021-07-13T12:13:31.550984Z","shell.execute_reply.started":"2021-07-13T12:13:31.516943Z","shell.execute_reply":"2021-07-13T12:13:31.549887Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Shifting backward enables us to see the difference between the current data point and the one that comes one or more periods later.\n\nA common operation after shifting or lagging is finding the difference and plotting it:","metadata":{}},{"cell_type":"code","source":"sp500.drop(\"lagged_2\", axis=1, inplace=True)\n\nsp500[\"diff_lag\"] = sp500[\"lagged_1\"] - sp500[\"SP500\"]\nsp500.head()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:31.552638Z","iopub.execute_input":"2021-07-13T12:13:31.552942Z","iopub.status.idle":"2021-07-13T12:13:31.569732Z","shell.execute_reply.started":"2021-07-13T12:13:31.552914Z","shell.execute_reply":"2021-07-13T12:13:31.568742Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sp500[\"diff_lag\"].plot(figsize=(16, 4));","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:31.571003Z","iopub.execute_input":"2021-07-13T12:13:31.571312Z","iopub.status.idle":"2021-07-13T12:13:31.975023Z","shell.execute_reply.started":"2021-07-13T12:13:31.571285Z","shell.execute_reply":"2021-07-13T12:13:31.973882Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Since this operation is so common, Pandas has the `diff` function that computes the differences based on the period:","metadata":{}},{"cell_type":"code","source":"sp500.drop([\"lagged_1\", \"diff_lag\"], axis=1, inplace=True)\n\nsp500[\"shifted_diff_1\"] = sp500[\"SP500\"].diff(periods=1)\nsp500[\"shifted_diff_3\"] = sp500[\"SP500\"].diff(periods=3)\nsp500[\"shifted_lagg_1\"] = sp500[\"SP500\"].diff(periods=-1)\nsp500[\"shifted_lagg_3\"] = sp500[\"SP500\"].diff(periods=3)\n\nsp500.drop(\"SP500\", axis=1).plot(figsize=(16, 8), subplots=True);","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:31.977403Z","iopub.execute_input":"2021-07-13T12:13:31.977765Z","iopub.status.idle":"2021-07-13T12:13:33.027767Z","shell.execute_reply.started":"2021-07-13T12:13:31.977731Z","shell.execute_reply":"2021-07-13T12:13:33.026304Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.2 Percentage changes ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nAnother common metric that can be derived from time-series data is day-to-day percentage change:","metadata":{}},{"cell_type":"code","source":"sp500.drop(\n [\"shifted_diff_1\", \"shifted_diff_3\", \"shifted_lagg_1\", \"shifted_lagg_3\"],\n axis=1,\n inplace=True,\n)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.02974Z","iopub.execute_input":"2021-07-13T12:13:33.030511Z","iopub.status.idle":"2021-07-13T12:13:33.03832Z","shell.execute_reply.started":"2021-07-13T12:13:33.030438Z","shell.execute_reply":"2021-07-13T12:13:33.036847Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sp500[\"shifted\"] = sp500[\"SP500\"].shift(1)\nsp500[\"change\"] = sp500[\"SP500\"].div(sp500[\"shifted\"]).sub(1).mul(100)\n\nsp500.head()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.040181Z","iopub.execute_input":"2021-07-13T12:13:33.040561Z","iopub.status.idle":"2021-07-13T12:13:33.07673Z","shell.execute_reply.started":"2021-07-13T12:13:33.040519Z","shell.execute_reply":"2021-07-13T12:13:33.075458Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"To calculate day-to-day percentage change, shift one period forward and divide the original distribution by the shifted one and subtract 1. The resulting values are given as proportions of what they were the day before.\n\nSince it is a common operation, Pandas implements it with the `pct_change` function:","metadata":{}},{"cell_type":"code","source":"sp500[\"pct_change\"] = sp500[\"SP500\"].pct_change().mul(100)\n\nsp500.head()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.078552Z","iopub.execute_input":"2021-07-13T12:13:33.079047Z","iopub.status.idle":"2021-07-13T12:13:33.106013Z","shell.execute_reply.started":"2021-07-13T12:13:33.078998Z","shell.execute_reply":"2021-07-13T12:13:33.104765Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sp500.drop([\"shifted\", \"change\", \"pct_change\"], axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.107744Z","iopub.execute_input":"2021-07-13T12:13:33.108326Z","iopub.status.idle":"2021-07-13T12:13:33.124495Z","shell.execute_reply.started":"2021-07-13T12:13:33.108285Z","shell.execute_reply":"2021-07-13T12:13:33.123292Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 4. Resampling ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nOften, you may want to increase or decrease the granularity of time series to generate new insights. These operations are called resampling or changing the frequency of time series, and we will discuss the Pandas functions related to them in this section.","metadata":{}},{"cell_type":"markdown","source":"### 4.1 Changing the frequency with `asfreq` ","metadata":{}},{"cell_type":"markdown","source":"The SP500 stocks data does not have a fixed date frequency, i.e., the period difference between each date is not the same:","metadata":{}},{"cell_type":"code","source":"sp500.head()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.126411Z","iopub.execute_input":"2021-07-13T12:13:33.127002Z","iopub.status.idle":"2021-07-13T12:13:33.150876Z","shell.execute_reply.started":"2021-07-13T12:13:33.126958Z","shell.execute_reply":"2021-07-13T12:13:33.149623Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's fix this by giving it a calendar day frequency (daily):","metadata":{}},{"cell_type":"code","source":"sp500.asfreq(\"D\").head(7)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.152304Z","iopub.execute_input":"2021-07-13T12:13:33.152832Z","iopub.status.idle":"2021-07-13T12:13:33.180495Z","shell.execute_reply.started":"2021-07-13T12:13:33.152795Z","shell.execute_reply":"2021-07-13T12:13:33.179415Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We just made the frequency of the date in SP500 more granular. As a result, new dates were added, leading to more missing values. You can now interpolate them using any of the techniques we discussed earlier.\n\nYou can see the list of built-in frequency aliases from [here](https://medium.com/r/?url=https%3A%2F%2Fpandas.pydata.org%2Fpandas-docs%2Fstable%2Fuser_guide%2Ftimeseries.html%23offset-aliases). A more interesting scenario would be using custom frequencies:","metadata":{}},{"cell_type":"code","source":"# 5-hour frequency\nsp500.asfreq(\"5h\").head(7) # This makes the dataset very large","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.182057Z","iopub.execute_input":"2021-07-13T12:13:33.182357Z","iopub.status.idle":"2021-07-13T12:13:33.195682Z","shell.execute_reply.started":"2021-07-13T12:13:33.182329Z","shell.execute_reply":"2021-07-13T12:13:33.194632Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 10 day frequency\nsp500.asfreq(\"10d\", method=\"ffill\").head(7) # This makes the dataset smaller","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.197274Z","iopub.execute_input":"2021-07-13T12:13:33.197795Z","iopub.status.idle":"2021-07-13T12:13:33.211095Z","shell.execute_reply.started":"2021-07-13T12:13:33.197749Z","shell.execute_reply":"2021-07-13T12:13:33.209815Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 10 month frequency\nsp500.asfreq(\"10M\", method=\"bfill\").head(7)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.212945Z","iopub.execute_input":"2021-07-13T12:13:33.213401Z","iopub.status.idle":"2021-07-13T12:13:33.235907Z","shell.execute_reply.started":"2021-07-13T12:13:33.213356Z","shell.execute_reply":"2021-07-13T12:13:33.234909Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There is also a `reindex` function that operates similarly and supports additional missing value filling logic. We won't discuss it here as there are better options we will consider.","metadata":{}},{"cell_type":"markdown","source":"### 4.2 Downsampling with `resample` and aggregating ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nIn time series lingo, making the frequency of a `DateTime` less granular is called downsampling. The examples are changing the frequency from hourly to daily, from daily to weekly, etc.\n\nWe saw how to downsample with `asfreq`. A more powerful alternative is `resample` which behaves like `pd.groupby`. Just like `groupby` groups the data based on categorical values, `resample` groups the data by date frequencies.\n\nLet's downsample the Apple/Google stock prices by month-end frequency:","metadata":{}},{"cell_type":"code","source":"aapl_googl.resample(\"M\")","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.237024Z","iopub.execute_input":"2021-07-13T12:13:33.237328Z","iopub.status.idle":"2021-07-13T12:13:33.256437Z","shell.execute_reply.started":"2021-07-13T12:13:33.2373Z","shell.execute_reply":"2021-07-13T12:13:33.255132Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Unlike `asfreq`, using resample only returns the data in the resampled state. To see each group, we need to use some type of function, similar to how we use `groupby`.\n\nSince downsampling decreases the number of data points, we need an aggregation function like mean, median, or mode:","metadata":{}},{"cell_type":"code","source":"aapl_googl.resample(\"M\").mean().tail()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.258524Z","iopub.execute_input":"2021-07-13T12:13:33.258905Z","iopub.status.idle":"2021-07-13T12:13:33.284936Z","shell.execute_reply.started":"2021-07-13T12:13:33.258863Z","shell.execute_reply":"2021-07-13T12:13:33.284191Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There are also functions that return the first or last record of a group:","metadata":{}},{"cell_type":"code","source":"# Resample with business-month frequency\n# and return the first record of each group\naapl_googl.resample(\"BM\").first().tail()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.285919Z","iopub.execute_input":"2021-07-13T12:13:33.286344Z","iopub.status.idle":"2021-07-13T12:13:33.306109Z","shell.execute_reply.started":"2021-07-13T12:13:33.286312Z","shell.execute_reply":"2021-07-13T12:13:33.305403Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Opposite of first()\naapl_googl.resample(\"Y\").last().tail() # Year-end frequency","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.307236Z","iopub.execute_input":"2021-07-13T12:13:33.307675Z","iopub.status.idle":"2021-07-13T12:13:33.321049Z","shell.execute_reply.started":"2021-07-13T12:13:33.307635Z","shell.execute_reply":"2021-07-13T12:13:33.32019Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It is also possible to use multiple aggregating functions using `agg`:","metadata":{}},{"cell_type":"code","source":"aapl_googl.resample(\"Y\").agg([\"mean\", \"median\", \"std\"]).head()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.322115Z","iopub.execute_input":"2021-07-13T12:13:33.322528Z","iopub.status.idle":"2021-07-13T12:13:33.365782Z","shell.execute_reply.started":"2021-07-13T12:13:33.322499Z","shell.execute_reply":"2021-07-13T12:13:33.364706Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 4.3 Upsampling with `resample` and interpolating ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nThe opposite of downsampling is making the `DateTime` more granular. This is called upsampling and includes operations like changing the frequency from daily to hourly, hourly to seconds, etc.\n\nWhen upsampling, you introduce new dates leading to more missing values. This means you need to use some type of imputation:","metadata":{}},{"cell_type":"code","source":"# Resample with business day freq and forward-fill\naapl_googl.resample(\"B\").ffill().tail()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.367314Z","iopub.execute_input":"2021-07-13T12:13:33.367629Z","iopub.status.idle":"2021-07-13T12:13:33.411832Z","shell.execute_reply.started":"2021-07-13T12:13:33.367601Z","shell.execute_reply":"2021-07-13T12:13:33.41071Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Resample with 20-hour frequency and back-fill\naapl_googl.resample(\"20h\").bfill().sample(5)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.413071Z","iopub.execute_input":"2021-07-13T12:13:33.41338Z","iopub.status.idle":"2021-07-13T12:13:33.427043Z","shell.execute_reply.started":"2021-07-13T12:13:33.413348Z","shell.execute_reply":"2021-07-13T12:13:33.425758Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 4.4 Plotting the resampled data ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nResampling isn't going to give much if you don't plot its results.\n\nIn most cases, you will see new trends and patterns when you downsample. This is because downsampling reduces the granularity, thus eliminating noise:","metadata":{}},{"cell_type":"code","source":"quarter_google = aapl_googl.resample(\"Q\")[\"GOOG\"].mean()\nyearly_google = aapl_googl.resample(\"Y\")[\"GOOG\"].mean()\n\nquarter_apple = aapl_googl.resample(\"Q\")[\"AAPL\"].mean()\nyearly_apple = aapl_googl.resample(\"Y\")[\"AAPL\"].mean()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.428803Z","iopub.execute_input":"2021-07-13T12:13:33.429537Z","iopub.status.idle":"2021-07-13T12:13:33.450717Z","shell.execute_reply.started":"2021-07-13T12:13:33.429473Z","shell.execute_reply":"2021-07-13T12:13:33.449474Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Plot Apple's downsampled stocks\naapl_googl[\"AAPL\"].plot(figsize=(16, 5), label=\"Original\")\nquarter_apple.plot(label=\"Quarterly\")\nyearly_apple.plot(label=\"Yearly\")\nplt.legend(fontsize=\"x-large\");","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.45267Z","iopub.execute_input":"2021-07-13T12:13:33.45302Z","iopub.status.idle":"2021-07-13T12:13:33.773713Z","shell.execute_reply.started":"2021-07-13T12:13:33.452988Z","shell.execute_reply":"2021-07-13T12:13:33.772727Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Plot Google's downsampled stocks\naapl_googl[\"GOOG\"].plot(figsize=(16, 5), label=\"Original\")\nquarter_google.plot(label=\"Quarterly\")\nyearly_google.plot(label=\"Yearly\")\nplt.legend(fontsize=\"x-large\");","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:33.774931Z","iopub.execute_input":"2021-07-13T12:13:33.77523Z","iopub.status.idle":"2021-07-13T12:13:34.055113Z","shell.execute_reply.started":"2021-07-13T12:13:33.7752Z","shell.execute_reply":"2021-07-13T12:13:34.054031Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Plotting the upsampled distribution is only going to introduce more noise, so we won't do it here.","metadata":{}},{"cell_type":"markdown","source":"## 5. Comparing the growth of multiple time series ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nIt is common to compare two or more numeric values that change over time. For example, we might want to see the growth rate of Google and Apple's stock prices. But here is the problem:","metadata":{}},{"cell_type":"code","source":"aapl_googl.mean()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:34.056415Z","iopub.execute_input":"2021-07-13T12:13:34.056734Z","iopub.status.idle":"2021-07-13T12:13:34.065663Z","shell.execute_reply.started":"2021-07-13T12:13:34.056706Z","shell.execute_reply":"2021-07-13T12:13:34.064469Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Google's stock prices are way higher than Apple's. Plotting the stocks together would probably squish Apple's to a flat line. In other words, the two stocks have different scales.\n\nTo fix this, statisticians use normalization. The most common variation is choosing the first recorded value and dividing the rest of the samples by that amount. This shows how each record changes compared to the first.\n\nHere is an example:","metadata":{}},{"cell_type":"code","source":"aapl_googl.dropna(inplace=True)\n\n# The first rows will contain ones because\n# they are being divided by themselvs\naapl_googl.div(aapl_googl.iloc[0]).head(10)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:34.066894Z","iopub.execute_input":"2021-07-13T12:13:34.067326Z","iopub.status.idle":"2021-07-13T12:13:34.092149Z","shell.execute_reply.started":"2021-07-13T12:13:34.067283Z","shell.execute_reply":"2021-07-13T12:13:34.091221Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The above output shows that for the first 3 dates, Apple stocks didn't change. Then, it increased by 1% of what it was on the first date ('2010–12–16'). Google's prices are more volatile, fluctuating between 1 and 2% increases during the first 10 dates.\n\nNow, let's plot them to compare growth:","metadata":{}},{"cell_type":"code","source":"# Normalize\nnormalized_aapl_goog = aapl_googl.div(aapl_googl.iloc[0])\n\nnormalized_aapl_goog.plot(figsize=(16, 5))\nplt.legend(fontsize=\"xx-large\");","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:34.093287Z","iopub.execute_input":"2021-07-13T12:13:34.094014Z","iopub.status.idle":"2021-07-13T12:13:34.359009Z","shell.execute_reply.started":"2021-07-13T12:13:34.093965Z","shell.execute_reply":"2021-07-13T12:13:34.357936Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Both Apple's and Google's achieved over 300% growth from 2011 to 2017. This plot may be even more interesting if we compare their growth to other 500 Fortune Companies:","metadata":{}},{"cell_type":"code","source":"# Normalize SP500 dataset\nnormalized_sp500 = sp500.div(sp500.iloc[0])\n\n# PLot\nfig, ax = plt.subplots(figsize=(16, 5))\n\nnormalized_aapl_goog.plot(ax=ax)\nnormalized_sp500[\"2011\":].plot(label=\"S&P500\", ax=ax)\n\nplt.legend(fontsize=\"xx-large\");","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:34.360386Z","iopub.execute_input":"2021-07-13T12:13:34.360733Z","iopub.status.idle":"2021-07-13T12:13:34.662515Z","shell.execute_reply.started":"2021-07-13T12:13:34.3607Z","shell.execute_reply":"2021-07-13T12:13:34.661396Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As you can see, Apple and Google have much higher growth than other top 500 companies in the US.","metadata":{}},{"cell_type":"markdown","source":"## 6. Window functions ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nThere is another type of function that helps you analyze time-series data in novel ways. These are called window functions, and they help you aggregate over a custom number of rows called 'windows.'\n\nFor example, I can create a 30-day window over my [Medium subscribers](https://medium.com/@ibexorigin) data to see the total number of subscribers for the past 30 days on any given day. Or a restaurant owner might create a weekly window to see average sales of the past week. Examples are endless as you can create a window of any size over your data.\n\nLet's explore these in more detail.","metadata":{}},{"cell_type":"markdown","source":"### 6.1 Rolling window functions ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nRolling window functions will have the same length. As they slide through the data, their coverage (number of rows don't change). Here is an example window of 5 periods sliding through the data:","metadata":{}},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"markdown","source":"Here is how we create rolling windows in pandas:","metadata":{}},{"cell_type":"code","source":"aapl_googl.rolling(window=5)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:34.663876Z","iopub.execute_input":"2021-07-13T12:13:34.664193Z","iopub.status.idle":"2021-07-13T12:13:34.670598Z","shell.execute_reply.started":"2021-07-13T12:13:34.66416Z","shell.execute_reply":"2021-07-13T12:13:34.669851Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Just like `resample`, it is in a read-only state - to use each window, we should chain some type of function. For example, let's create a cumulative sum for every past 5 periods:","metadata":{}},{"cell_type":"code","source":"aapl_googl[\"GOOG_5d_roll\"] = aapl_googl[\"GOOG\"].rolling(window=5).sum()\n\naapl_googl.head(10)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:34.672062Z","iopub.execute_input":"2021-07-13T12:13:34.672597Z","iopub.status.idle":"2021-07-13T12:13:34.697191Z","shell.execute_reply.started":"2021-07-13T12:13:34.672548Z","shell.execute_reply":"2021-07-13T12:13:34.696415Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Obviously, the first 4 rows will be NaNs. Any other row will contain the sum of the previous 4 rows and the current one.\n\nPay attention to the window argument. If you pass an integer, the window size will be determined by that number of rows. If you pass a frequency alias such as months, years, 5 hours, or 7 weeks, the window size will be whatever number of rows that includes the single unit of the passed frequency. In other words, a 5-period window might have a different size than a 5-day frequency window.\n\nAs an example, let's plot 90 and 360-day moving averages for Google stock prices and plot them:","metadata":{}},{"cell_type":"code","source":"aapl_googl[\"90D_roll_mean\"] = aapl_googl[\"GOOG\"].rolling(window=\"90D\").mean()\naapl_googl[\"360D_roll_mean\"] = aapl_googl[\"GOOG\"].rolling(window=\"360D\").mean()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:34.699054Z","iopub.execute_input":"2021-07-13T12:13:34.699787Z","iopub.status.idle":"2021-07-13T12:13:34.716367Z","shell.execute_reply.started":"2021-07-13T12:13:34.699737Z","shell.execute_reply":"2021-07-13T12:13:34.714885Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(16, 5))\n\naapl_googl[[\"90D_roll_mean\", \"360D_roll_mean\", \"GOOG\"]].plot(ax=ax)\n\nplt.legend(fontsize=\"xx-large\");","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:34.718292Z","iopub.execute_input":"2021-07-13T12:13:34.718805Z","iopub.status.idle":"2021-07-13T12:13:34.999681Z","shell.execute_reply.started":"2021-07-13T12:13:34.718754Z","shell.execute_reply":"2021-07-13T12:13:34.99866Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Just like `groupby` and `resample`, you can calculate multiple metrics with the `agg` function for each window.","metadata":{}},{"cell_type":"markdown","source":"### 6.2 Expanding window functions ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nAnother type of window function deals with expanding windows. Each new window will contain all the records up to the current date:\n\n","metadata":{}},{"cell_type":"markdown","source":"Expanding windows are useful for calculating 'running' metrics-for example, running sum, mean, min and max, running rate of return, etc.\n\nBelow, you will see how to calculate the cumulative sum. The cumulative sum is actually an expanding window function with a window size of 1:","metadata":{}},{"cell_type":"code","source":"aapl_googl.drop(\n [\"GOOG_5d_roll\", \"90D_roll_mean\", \"360D_roll_mean\"], axis=1, inplace=True\n)","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:35.001472Z","iopub.execute_input":"2021-07-13T12:13:35.001879Z","iopub.status.idle":"2021-07-13T12:13:35.007736Z","shell.execute_reply.started":"2021-07-13T12:13:35.001845Z","shell.execute_reply":"2021-07-13T12:13:35.006596Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"aapl_googl[\"expanding_cumsum\"] = aapl_googl[\"GOOG\"].expanding(min_periods=1).sum()\n# The same operation with cumsum() func\naapl_googl[\"cumsum_function\"] = aapl_googl[\"GOOG\"].cumsum()\n\naapl_googl.head()","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:35.009515Z","iopub.execute_input":"2021-07-13T12:13:35.009847Z","iopub.status.idle":"2021-07-13T12:13:35.042428Z","shell.execute_reply.started":"2021-07-13T12:13:35.009815Z","shell.execute_reply":"2021-07-13T12:13:35.041145Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"`expanding` function has a `min_periods` parameter that determines the initial window size.","metadata":{}},{"cell_type":"markdown","source":"Now, let's plot the running min and max of S&P500 stocks:","metadata":{}},{"cell_type":"code","source":"sp500[\"running_min\"] = sp500[\"SP500\"].expanding().min() # same as cummin()\nsp500[\"running_max\"] = sp500[\"SP500\"].expanding().max()\n\nfig, ax = plt.subplots(figsize=(10, 8))\n\nsp500.plot(ax=ax)\nplt.legend(fontsize=\"xx-large\");","metadata":{"execution":{"iopub.status.busy":"2021-07-13T12:13:35.044068Z","iopub.execute_input":"2021-07-13T12:13:35.044569Z","iopub.status.idle":"2021-07-13T12:13:35.375206Z","shell.execute_reply.started":"2021-07-13T12:13:35.044367Z","shell.execute_reply":"2021-07-13T12:13:35.374064Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Summary ","metadata":{}},{"cell_type":"markdown","source":"[Back to top🔝](#toc)\n\nI think congratulations are in order!\n\nNow, you know every single Pandas function you can use to manipulate time-series data. It has been an excruciatingly long post, but it was definitely worth it since now, you can tackle any time series data thrown at you.\n\nThis post was mainly focused on data manipulation. The next posts in the series will be about more in-depth time series analyses, similar posts on every single plot you can create on time series, and dedicated articles on forecasting. Stay tuned!","metadata":{}},{"cell_type":"markdown","source":"### You might also be interested...","metadata":{}},{"cell_type":"markdown","source":"- [Matplotlib vs. Plotly: Let’s Decide Once and for All](https://towardsdatascience.com/matplotlib-vs-plotly-lets-decide-once-and-for-all-ad25a5e43322?source=your_stories_page-------------------------------------)\n- [6 Things I Do to Consistently Improve My Machine Learning Models](https://medium.com/me/stories/public#:~:text=6%20Things%20I%20Do%20to%20Consistently%20Improve%20My%20Machine%20Learning%20Models)\n- [5 Super Productive Things To Do While Training Machine Learning Models](https://towardsdatascience.com/5-short-but-super-productive-things-to-do-during-model-training-b02e2d7f0d06?source=your_stories_page-------------------------------------)","metadata":{}}]}