ipython_notebooks/Time-Series analytics on Customer_Analysis_prepared (admin).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Time-Series analytics on Customer_Analysis_prepared"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook, we are going to analyse temporal data.\n",
    "\n",
    "\n",
    "We'll perform the following analysis:\n",
    "* Interpolating and smoothing of the data. If your data is irregular, it will be transformed to regular data. It can then be \"smoothed\" to get it to a lower granularity.\n",
    "* Various plotting: we'll plot individual time series on various scales\n",
    "* Aggregations on rolling windows, aka low-pass filters\n",
    "* Seasonality decomposition, to separate the long-term trends from the short-term variations\n",
    "\n",
    "Important note: this notebook does not include forecasting of time series. Please use the \"Time series forecasting\" notebook.\n",
    "\n",
    "Navigate between sections\n",
    "\n",
    "* [Setup and loading the data](#setup)\n",
    "* [Interpolation and smoothing](#interpolation)\n",
    "* [Raw plots](#plots)\n",
    "* [Rolling windows](#window)\n",
    "* [Seasonality decomposition](#seasonality)\n",
    "\n",
    "<center><strong>Select Cell > Run All to execute the whole analysis</strong></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup and dataset loading <a id=\"setup\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First of all, let's load the libraries that we'll use"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pylab inline\n",
    "import dataiku                                          # Access to Dataiku datasets\n",
    "import pandas as pd, numpy as np                        # Data manipulation \n",
    "import seaborn as sns                                   # Graphing\n",
    "from sklearn.preprocessing import MinMaxScaler          # Rescale utility\n",
    "from statsmodels.tsa.seasonal import seasonal_decompose # For signal decomposition\n",
    "import warnings                                         # Disable some warnings\n",
    "warnings.filterwarnings(\"ignore\",category=DeprecationWarning)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first thing we do is now to load the dataset\n",
    "\n",
    "Since analyzing time series requires to have the data in memory, we are only going to load a sample of the data. Modify the following cell to change the size of the sample."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "mydataset = dataiku.Dataset(\"Customer_Analysis_prepared\")\n",
    "\n",
    "# Load the first 100'000 lines.\n",
    "# You can also load random samples, limit yourself to some columns, or only load\n",
    "# data matching some filters.\n",
    "#\n",
    "# Please refer to the Dataiku Python API documentation for more information\n",
    "df = mydataset.get_dataframe(limit = 100000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "numerical_columns = list(df.select_dtypes(include=[np.number]).columns)\n",
    "categorical_columns = list(df.select_dtypes(include=[object]).columns)\n",
    "date_columns = list(df.select_dtypes(include=['<M8[ns]']).columns)\n",
    "\n",
    "# Print a quick summary of what we just loaded\n",
    "print \"Loaded dataset\"\n",
    "print \"   Rows: %s\" % df.shape[0]\n",
    "print \"   Columns: %s (%s num, %s cat, %s date)\" % (df.shape[1], \n",
    "                                                    len(numerical_columns), len(categorical_columns),\n",
    "                                                    len(date_columns))\n",
    "if not len(date_columns):\n",
    "    print \"No date columns were found, this is an issue. Try to parse your dates before using this notebook!\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Set the time dimension as index\n",
    "\n",
    "All of our analysis will be based on time, so we set the first date as the index of our dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "time_column = df[date_columns[0]]\n",
    "# We assume the time to be the first date column, uncomment this line to override\n",
    "# time_column = \"my_parsed_date\"\n",
    "\n",
    "df.set_index(time_column, inplace=True)\n",
    "df.sort_index(ascending=True, inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "time_column.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Interpolation and smoothing <a id=\"interpolation\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we can dive in further analysis, we need to make sure that we have a \"regular\" time series, ie that there are no holes or \"extraneous data\".\n",
    "\n",
    "We thus generate a \"resambled dataframe\".\n",
    "\n",
    "The first operation is to select the interpolation granularity, ie the time range that we'll standardize on. The following code automatically selects an appropriate granularity based on the total time range in your data:\n",
    "\n",
    "* Hour-based if data covers less than 1 month\n",
    "* Day-based if data covers less than 3 years\n",
    "* Week-based if data covers less than 10 years\n",
    "* Month-based if data covers less than 30 years\n",
    "* Year-based else"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "start_date = df.index.min()\n",
    "end_date = df.index.max()\n",
    "\n",
    "if (end_date - start_date) < timedelta64(1, 'M'):\n",
    "    sample_freq = \"1H\"\n",
    "elif (end_date - start_date) < timedelta64(3, 'Y'):\n",
    "    sample_freq = \"1D\"\n",
    "elif (end_date - start_date) < timedelta64(10, 'Y'):\n",
    "    sample_freq = \"1W\"\n",
    "elif (end_date - start_date) < timedelta64(30, 'Y'):\n",
    "    sample_freq = \"1M\"\n",
    "else:\n",
    "    sample_freq = \"1A\"\n",
    "\n",
    "print \"Based on your data (timedelta: %s), chosen frequency is %s\" % ((end_date-start_date), sample_freq)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you'd prefer to set your own sampling frequency, uncomment the following cell"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# You can find the complete list of options at:\n",
    "# http://pandas.pydata.org/pandas-docs/stable/timeseries.html#offset-aliases\n",
    "#\n",
    "# You can combine these with numerical values. For example use \"2D\" for a \"every two days\" sample rate\n",
    "#\n",
    "#sample_freq = \"1D\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_interpolate = df.resample(sample_freq, label=\"left\", convention=\"e\").mean()\n",
    "\n",
    "df_interpolate = df_interpolate.interpolate(method=\"time\")\n",
    "\n",
    "print \"Interpolated at %s, the time series has %s rows\" % (sample_freq, df_interpolate.shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Let's check how our resampled time series looks (NB: we only show the first time series here)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Draw original and interpolated series\n",
    "fig, ax = plt.subplots(1, figsize=(17, 5))\n",
    "df[numerical_columns[0]].plot(alpha=0.25)\n",
    "df_interpolate[numerical_columns[0]].plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Plotting <a id=\"plots\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### One above another, not scaled\n",
    "\n",
    "Let's plot all of our time series, one above another (NB: scales are not the same).\n",
    "\n",
    "Note that for display reason, we default to only plotting the first 20 time series. You can change this below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Plot all time series in this dataset, with a limit of 20\n",
    "lim_col = min(20, len(numerical_columns))\n",
    "if lim_col == 1:\n",
    "    f, ax = plt.subplots(1, figsize=(17, 3))\n",
    "    df_interpolate[numerical_columns[0]].plot()\n",
    "else:\n",
    "    f, ax = plt.subplots(lim_col, figsize=(17, 3 * lim_col))\n",
    "    for i, col in enumerate(numerical_columns[:20]):\n",
    "        ax[i].set_ylabel(col)\n",
    "        df_interpolate[col].plot(ax=ax[i])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### On the same graph, not scaled\n",
    "\n",
    "Let's plot all of our time series on the same graph (with a single scale)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_lim = df_interpolate[numerical_columns[:lim_col]]\n",
    "df_lim.plot(use_index=True, figsize=(17,6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### On the same graph, rescaled\n",
    "\n",
    "Let's plot all of our time series on the same graph after scaling all of them on the 0-1 range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The scaler doesn't accept NA values, so use filling and then drop all non-filled column\n",
    "df_nona = df_interpolate.fillna(method=\"ffill\")\n",
    "columns_to_drop = []\n",
    "for col in df_nona.columns:\n",
    "    if df_nona[col].isnull().sum() > 0:\n",
    "        columns_to_drop.append(col)\n",
    "df_nona = df_nona.drop(columns_to_drop, axis=1)\n",
    "plotted_columns = list(set(numerical_columns[:lim_col]) - set(columns_to_drop))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_scaled = pd.DataFrame(\n",
    "                    MinMaxScaler().fit_transform(df_nona[plotted_columns]),\n",
    "                    columns=plotted_columns,\n",
    "                    index=df_nona.index)\n",
    "df_scaled.plot(use_index=True, figsize=(17, 6))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Rolling windows (single time series) <a id=\"window\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are now going to plot the time series on various sizes of rolling windows. In essence, this is a low-pass filter on the data, which filters out high-frequency changes.\n",
    "\n",
    "For this whole section, we'll focus on a single time series. So let's start by selecting it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# We assume that the time series of interest is the first column of the dataframe.\n",
    "# You can replace \"numerical_columns[0]\" by your column of interest in the next line\n",
    "series_column = numerical_columns[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_col = df_interpolate[series_column]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    " # Try to change these width with something smart for your data\n",
    "window_widths = [5,20,50,100]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get mean value for varying window widths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "f, ax = plt.subplots(len(window_widths), figsize=(17, 3 * len(window_widths)))\n",
    "for (i, window_size) in enumerate(window_widths):\n",
    "    ax[i].set_ylabel(\"Window size %s\" % window_size)\n",
    "    pd.Series.rolling(df_col, window=window_size, center=False).mean().plot(ax=ax[i])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get min and max values for varying window widths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "f, ax = plt.subplots(len(window_widths), figsize=(17, 3 * len(window_widths)))\n",
    "for (i, window_size) in enumerate(window_widths): # Try to change these width with something smart for your data\n",
    "    ax[i].set_ylabel(\"Window size %s\" % window_size)\n",
    "    pd.Series.rolling(df_col, window=window_size, center=False).min().plot(ax=ax[i], label=\"Max\")\n",
    "    pd.Series.rolling(df_col, window=window_size, center=False).max().plot(ax=ax[i], label=\"Min\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Min, mean, max on the same graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "f, ax = plt.subplots(len(window_widths), figsize=(17, 3 * len(window_widths)))\n",
    "for (i, window_size) in enumerate(window_widths): # Try to change these width with something smart for your data\n",
    "    ax[i].set_ylabel(\"Window size %s\" % window_size)\n",
    "    \n",
    "    rolling =  pd.Series.rolling(df_col, window=window_size, center=False)\n",
    "    mean = rolling.mean()\n",
    "    mean.plot(ax=ax[i], label=\"Mean\")\n",
    "    ax[i].fill_between(mean.index, rolling.min(), rolling.max(), alpha=0.3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## STL Decomposition (single time series) <a id=\"seasonality\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In many cases of time series analytics, there is a natural seasonal variation. \n",
    "\n",
    "A common analysis is to decompose the time series into the three main components:\n",
    "\n",
    "* Trend: the long-term evolution of the time series\n",
    "* Seasonality: the reproducible variation within each time period (as previously defined)\n",
    "* Random: unpredictable variations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The number of samples you want to use as timescale for the seasonality decomposition\n",
    "if sample_freq == \"1W\":\n",
    "    freq = 52\n",
    "elif sample_freq == \"1M\":\n",
    "    freq = 12\n",
    "elif sample_freq == \"1D\":\n",
    "    freq = 365\n",
    "else:\n",
    "    freq = 52 # Change this with a seasonality you think should be present in your data.\n",
    "\n",
    "df_col_nona = df_col.fillna(method=\"ffill\")\n",
    "\n",
    "# Decompose time series\n",
    "decomposition = seasonal_decompose(df_col_nona.values,freq=freq)\n",
    "trend = decomposition.trend\n",
    "seasonal = decomposition.seasonal\n",
    "residual = decomposition.resid\n",
    "df_stl = pd.DataFrame(index=df_col_nona.index)\n",
    "df_stl[\"Trend\"] = trend\n",
    "df_stl[\"Seasonality\"] = seasonal\n",
    "df_stl[\"Residuals\"] = residual\n",
    "\n",
    "#Display    \n",
    "f, ax = plt.subplots(3, figsize=(15, 10))\n",
    "for (i, col) in enumerate(df_stl.columns):\n",
    "    ax[i].set_ylabel(col)\n",
    "    df_stl[col].plot(ax=ax[i])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Signal processing <a id=\"signal\" />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The rest of the analysis is dedicated to \"signal processing\" kind of analysis and is generally not suitable for general-purpose time series."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding the peaks\n",
    "\n",
    "This code finds the peaks in the input signal, and plots the detected peaks together with the original data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.signal import find_peaks_cwt\n",
    "ts_fill = df_col.fillna(method='ffill').fillna(method='bfill')  \n",
    "max_octave = np.log2(ts_fill.shape[0])\n",
    "widths = 2**np.arange(0, max_octave)\n",
    "peaks = find_peaks_cwt(ts_fill, widths) # list\n",
    "ts_peaks = pd.Series(0, index=ts_fill.index)\n",
    "ts_peaks.iloc[peaks] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(1, figsize=(17, 3))\n",
    "\n",
    "ax.plot(df_col.index, df_col, label='Data', color='blue')\n",
    "ax.plot(ts_peaks.index, ts_peaks * df_col.max(), label='Peaks', color='red')\n",
    "ax.set_title('Peaks')\n",
    "ax.legend(loc=\"upper left\")\n",
    "ax.grid()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Power spectrum estimation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "time_range = df_interpolate.index.max() - df_interpolate.index.min()\n",
    "sampling_frequency = df_interpolate.index.shape[0]/ (time_range.total_seconds())\n",
    "print \"Time series covers %s seconds (sampling freq %s Hz, sampling period: %ss)\" % \\\n",
    "                    (time_range.total_seconds(), sampling_frequency, 1/sampling_frequency)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy import signal\n",
    "d1, d2 = signal.welch(df_col.fillna(method=\"ffill\"), sampling_frequency)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.semilogy(d1, d2)\n",
    "plt.xlabel('Frequency [Hz]')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Continuous Wavelet transform\n",
    "\n",
    "NB: For best results, don't interpolate too much for this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy import signal\n",
    "widths = np.arange(1, 10)\n",
    "\n",
    "outputs = [\"wavelet_width%i\" % (int(w)) for w in widths]\n",
    "\n",
    "df_col_filled = df_col.fillna(method=\"ffill\")\n",
    "\n",
    "cwtmat = signal.cwt(df_col_filled.values, signal.ricker, widths)\n",
    "cwtmat_df = pd.DataFrame(cwtmat.T, columns=outputs, index=df_col.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "fig, ax = plt.subplots(2, figsize=(17, 6))\n",
    "ax[0].plot(df_col_filled.index, df_col_filled.values)\n",
    "ax[1].imshow(cwtmat, aspect=\"auto\", cmap='PRGn')"
   ]
  }
 ],
 "metadata": {
  "analyzedDataset": "Customer_Analysis_prepared",
  "creator": "admin",
  "customFields": {},
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.9"
  },
  "tags": [],
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}