notebooks/Window.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Window.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/arangodb/interactive_tutorials/blob/master/notebooks/Window.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "islPPnmmfwIY"
      },
      "source": [
        "# WINDOW AQL Function\n",
        "Aggregate adjacent documents or value ranges with a sliding window to calculate running totals, rolling averages, and other statistical properties"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gwdjttO5zcko"
      },
      "source": [
        "%%capture\n",
        "!pip3 install pyarango\n",
        "!pip3 install \"python-arango>=5.0\""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "J_gNZCCJ5ClS"
      },
      "source": [
        "%%capture\n",
        "!git clone https://github.com/arangodb/interactive_tutorials.git -b oasis_connector --single-branch\n",
        "!rsync -av interactive_tutorials/ ./ --exclude=.git"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YvkFgzTY_0fg"
      },
      "source": [
        "Here we import the oasis package along with our python driver. Here we have imported both but only one is necessary."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9sbDlV9l_fDp"
      },
      "source": [
        "import oasis\n",
        "\n",
        "from pyArango.connection import *\n",
        "from arango import ArangoClient"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "69GLRFL7_-Jn"
      },
      "source": [
        "## Connecting\n",
        "\n",
        "Be sure to update the `tutorialName` variable with your tutorials name."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WOXCQc-xACUv"
      },
      "source": [
        "# Retrieve tmp credentials from ArangoDB Tutorial Service\n",
        "\n",
        "# ** UPDATE THE FOLLOWING VARIABLE  **\n",
        "tutorialName = \"Window\"\n",
        "login = oasis.getTempCredentials(tutorialName=tutorialName, credentialProvider=\"https://tutorials.arangodb.cloud:8529/_db/_system/tutorialDB/tutorialDB\")\n",
        "\n",
        "# Here is an example of connecting with python arango \n",
        "database = oasis.connect_python_arango(login)\n",
        "\n",
        "# These are the credentials\n",
        "print(\"https://\"+login[\"hostname\"]+\":\"+str(login[\"port\"]))\n",
        "print(\"Username: \" + login[\"username\"])\n",
        "print(\"Password: \" + login[\"password\"])\n",
        "print(\"Database: \" + login[\"dbName\"])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zQW-kvq0Eo4A"
      },
      "source": [
        "# Importing Data\n",
        "You are free to parse and import your data however you choose but some simple options for those already familiar with ArangoDB are:\n",
        "* [arangorestore](https://www.arangodb.com/docs/stable/programs-arangorestore.html)\n",
        "* [arangoimport](https://www.arangodb.com/docs/stable/programs-arangoimport.html)\n",
        "\n",
        "It is sometimes necessary to adjust the permissions of the tools folder, if you are using any tools in it."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "q5gXOkinH5GF"
      },
      "source": [
        "!chmod -R 755 ./tools/*\n",
        "!mkdir data\n",
        "!curl -o ./data/sensor_data.csv https://raw.githubusercontent.com/arangodb/interactive_tutorials/master/notebooks/data/2017-07_bme280sof_smaller.csv\n",
        "# Complete data located here: https://www.kaggle.com/hmavrodiev/sofia-air-quality-dataset?select=2017-07_bme280sof.csv"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yPjGeffH9yu3"
      },
      "source": [
        "%%capture\n",
        "! ./tools/arangoimport -c none --server.endpoint http+ssl://{login[\"hostname\"]}:{login[\"port\"]} --server.username {login[\"username\"]} --server.database {login[\"dbName\"]} --server.password {login[\"password\"]} --file \"data/sensor_data.csv\" --type \"csv\" --collection \"sensor_data\" --create-collection true"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IcBG76sQBNUW"
      },
      "source": [
        "#Row-Based Aggregation\n",
        "* Allows aggregating over a fixed number of rows, following or preceding the current row. \n",
        "* It is also possible to define that all preceding or following rows should be aggregated (\"unbounded\").\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GwTg_NIAevK1"
      },
      "source": [
        "aql = database.aql\n",
        "results = aql.execute(\n",
        "    \"\"\"\n",
        "    FOR t IN sensor_data\n",
        "      SORT t.timestamp\n",
        "      WINDOW { preceding: 1, following:1}\n",
        "      AGGREGATE rollingAvg = AVG(t.temperature), rollingSum = SUM(t.temperature)\n",
        "      WINDOW { preceding: \"unbounded\", following: 0}\n",
        "      AGGREGATE cumulativeSum = SUM(t.temperature)\n",
        "      LIMIT 10\n",
        "      RETURN {\n",
        "          time: t.timestamp,\n",
        "          temp: t.temperature,\n",
        "          sensor: t.sensor_id,\n",
        "          rollingAvg,\n",
        "          rollingSum,\n",
        "          cumulativeSum\n",
        "          \n",
        "          }\n",
        "      \"\"\"\n",
        ")\n",
        "for res in results:\n",
        "  print(res)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4gREqUxsBS1v"
      },
      "source": [
        "#Duration-based Aggregation\n",
        "* Allows aggregating over all documents by time intervals. \n",
        "* Calculate timestamp offsets using positive ISO 8601 duration strings (P1Y6, PT30M).\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7WjxeBpTC6sl"
      },
      "source": [
        "results = aql.execute(\n",
        "    \"\"\"\n",
        "    FOR t IN sensor_data\n",
        "      WINDOW DATE_TIMESTAMP(t.timestamp) WITH { preceding: \"PT30M\" }\n",
        "      AGGREGATE rollingAverage = AVG(t.temperature), rollingSum = SUM(t.temperature)\n",
        "      LIMIT 10\n",
        "      RETURN {\n",
        "        time: t.timestamp,\n",
        "        temperature: t.temperature,\n",
        "        sensor: t.sensor_id,\n",
        "        rollingAverage,\n",
        "        rollingSum\n",
        "      }\n",
        "    \"\"\"\n",
        ")\n",
        "times = []\n",
        "temps = []\n",
        "rollingAverages = []\n",
        "for res in results:\n",
        "  times.append(res['time'])\n",
        "  temps.append(res['temperature'])\n",
        "  rollingAverages.append(res['rollingAverage'])\n",
        "  print(res)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "G4C-i6KqXhaf"
      },
      "source": [
        "import time\n",
        "import datetime as dt\n",
        "import matplotlib.pyplot as plt\n",
        "\n",
        "# Create figure for plotting\n",
        "fig = plt.figure()\n",
        "ax = fig.add_subplot(1, 1, 1)\n",
        "ax.plot(times, temps, label=\"Temperatures\")\n",
        "plt.ylabel('Temperature',fontsize=18)\n",
        "plt.xlabel('Dates',fontsize=18)\n",
        "plt.legend(loc=\"upper left\")\n",
        "\n",
        "\n",
        "\n",
        "# Draw plot\n",
        "# ax.annotate(\"Original Temps\", (5,5), color='red', size=20)\n",
        "ax2 = ax.twinx()\n",
        "ax2.plot(times, rollingAverages, 'b-', label=\"Rolling\")\n",
        "plt.ylabel('rollingAverage',fontsize=18)\n",
        "plt.legend(loc=\"upper right\")\n",
        "\n",
        "# Format plot\n",
        "# plt.xticks(rotation=45, ha='right')\n",
        "plt.subplots_adjust(bottom=0.30)\n",
        "fig.set_size_inches(20, 15)\n",
        "plt.title('Temperature over Time', fontsize=20)\n",
        "\n",
        "# Draw the graph\n",
        "plt.show()\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qCt1keEvM5Q6"
      },
      "source": [
        "If you would like to share your notebook simply place it in the `community_notebooks` folder in the interactive-tutorials repository and make a pull request.\n",
        "\n",
        "Good luck and we are excited to see what you are working on!"
      ]
    }
  ]
}