Visualization_Demo.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Visualizing Time Series in Pandas Demo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!wget --no-verbose -nc https://archive.ics.uci.edu/ml/machine-learning-databases/event-detection/CalIt2.data;\n",
    "!wget --no-verbose -nc https://archive.ics.uci.edu/ml/machine-learning-databases/event-detection/CalIt2.events;\n",
    "!wget --no-verbose -nc https://archive.ics.uci.edu/ml/machine-learning-databases/event-detection/CalIt2.names;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dataset\n",
    "\n",
    "https://archive.ics.uci.edu/ml/datasets/CalIt2+Building+People+Counts\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Raw Data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_csv('CalIt2.data', header=None, names=['Flow', 'Date', 'Time', 'Count'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Process Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_data(filepath):\n",
    "    df = pd.read_csv('CalIt2.data', header=None, names=['Flow', 'Date', 'Time', 'Count'])\n",
    "    \n",
    "    # Process times\n",
    "    df['Timestamp'] = df['Date'] + ' ' + df['Time']\n",
    "    df['Timestamp'] = pd.to_datetime(df['Timestamp'])\n",
    "    \n",
    "    df['Date'] = df['Timestamp'].dt.date\n",
    "    df['Time'] = df['Timestamp'].dt.time\n",
    "    \n",
    "    \n",
    "    # Process Flow Column\n",
    "    df.loc[df['Flow']==7, 'Flow'] = 'Out'\n",
    "    df.loc[df['Flow']==9, 'Flow'] = 'In'\n",
    "    \n",
    "    df = df.set_index('Timestamp')\n",
    "    return df\n",
    "    \n",
    "\n",
    "df = load_data('CalIt2.data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivot to get separate columns for Inflow and Outflow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def pivot_data(df):\n",
    "    \n",
    "    df_pivot = df.pivot_table(index='Timestamp', columns=['Flow'])\n",
    "    df_pivot.columns = ['In', 'Out']\n",
    "    \n",
    "    df_pivot['Net'] = df_pivot['In'] - df_pivot['Out']\n",
    "    \n",
    "    df_pivot = df_pivot.reset_index()\n",
    "    \n",
    "    df_pivot['Date'] = df_pivot['Timestamp'].dt.date\n",
    "    df_pivot['Time'] = df_pivot['Timestamp'].dt.time\n",
    "    \n",
    "    df_pivot = df_pivot.set_index('Timestamp')\n",
    "    \n",
    "    return df_pivot\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pivot = pivot_data(df)\n",
    "df_pivot.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cumulative flow throughout day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def daily_cumulative(df_pivot):\n",
    "\n",
    "    return (df_pivot\n",
    "      .groupby('Date')\n",
    "      .cumsum())\n",
    "\n",
    "(daily_cumulative(df_pivot)).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(daily_cumulative(df_pivot)\n",
    "  .loc['2005-07-28', 'In']\n",
    "  .plot())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Resample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def resample_pivot(df_pivot, sampling='h'):\n",
    "    \n",
    "    return (df_pivot\n",
    "            .resample(sampling)\n",
    "            .agg({'In':'sum',\n",
    "                  'Out': 'sum',\n",
    "                  'Net':'sum',\n",
    "                  'Date': 'last',\n",
    "                  'Time': 'first'}))\n",
    "\n",
    "\n",
    "df_hourly = resample_pivot(df_pivot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def hourly_with_confidence_bars():\n",
    "    df_hourly = resample_pivot(df_pivot)\n",
    "    df_g = (df_hourly\n",
    "     .groupby('Time')\n",
    "     .agg({'In':['mean', 'std']}))\n",
    "\n",
    "    df_g.columns = df_g.columns.get_level_values(1)\n",
    "\n",
    "\n",
    "    ax = df_g.plot(y='mean')\n",
    "\n",
    "    ax.fill_between(df_g.index, \n",
    "                    df_g['mean']+df_g['std'],\n",
    "                    df_g['mean']-df_g['std'],\n",
    "                    alpha=0.3)\n",
    "\n",
    "hourly_with_confidence_bars()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## By month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def resample_pivot_month(df_pivot, sampling='m'):\n",
    "    return (df_pivot\n",
    "            .resample(sampling)\n",
    "            .agg({'In':'mean',\n",
    "                  'Out': 'mean',\n",
    "                  'Net':'mean',\n",
    "                  'Date': 'last',\n",
    "                  'Time': 'first'}))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_monthly(df_hourly):\n",
    "    \n",
    "    monthly = df_hourly.reset_index()\n",
    "    \n",
    "    monthly['Month'] = monthly['Timestamp'].dt.month\n",
    "    \n",
    "    return monthly.set_index('Timestamp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "monthly = get_monthly(df_hourly).reset_index()\n",
    "monthly['Month'] = monthly['Timestamp'].dt.month\n",
    "\n",
    "monthly = monthly.set_index('Timestamp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_monthly_inflow(monthly):\n",
    "    \n",
    "    monthly_in = monthly.loc[:,'In']\n",
    "    \n",
    "    monthly_in_groups = monthly_in.reset_index().melt(id_vars='Timestamp').groupby('Timestamp').groups\n",
    "    monthly_in_melt = monthly_in.reset_index().melt(id_vars='Timestamp')\n",
    "    monthly_in_melt['Month'] = monthly_in_melt['Timestamp'].dt.month    \n",
    "    \n",
    "    \n",
    "    return monthly_in_melt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def month_sparkline(df, max_month='November'):\n",
    "    \n",
    "    months = {\n",
    "    7:'July',\n",
    "    8:'August',\n",
    "    9:'September',\n",
    "    10:'October',\n",
    "    11:'November'}\n",
    "    \n",
    "    \n",
    "    fig, ax = plt.subplots(1,1,figsize=(4,1))\n",
    "    df.plot(x='Time', y='value', ax=ax, legend=None)\n",
    "\n",
    "    month = months[df['Month'][0]]\n",
    "\n",
    "\n",
    "    # remove all the axes\n",
    "    for k,v in ax.spines.items():\n",
    "        v.set_visible(False)\n",
    "    if month is not 'November':\n",
    "        plt.xlabel('')\n",
    "    \n",
    "    \n",
    "        ax.set_xticks([])\n",
    "    ax.set_yticks([])\n",
    "    #plt.axis('off')\n",
    "    \n",
    "    plt.ylabel(month, rotation='horizontal', labelpad=40, size=10)\n",
    "    # remove legend\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}