{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "### Reading Data from The Things Network Data Storage Console:\n", "To Read the data from the Swagger API of the Things Network we use the following website [curl.trillworks.com](https://curl.trillworks.com/) to Parse **Curl** link from TTN Swagger API to Python request code." ] }, { "cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [], "source": [ "# Author: Dilip Rajkumar\n", "# Email: d.rajkumar@hbksaar.de\n", "# Date: 18/01/2018\n", "# Revision: version#1\n", "# License: MIT License" ] }, { "cell_type": "code", "execution_count": 40, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import requests" ] }, { "cell_type": "code", "execution_count": 41, "metadata": {}, "outputs": [], "source": [ "headers = {'Accept': 'application/json','Authorization': 'key ttn-account-v2.P4kRaEqenNGbIdFSgSLDJGMav5K9YrekkMm_F1lOVrw'}\n", "\n", "## Query from TTN Swagger API\n", "## last = 12h represents the last 12 hour, you can also query data for 1d, 48h, 1h, 30s etc..\n", "\n", "# response = requests.get('https://vehiclecounter.data.thethingsnetwork.org/api/v2/query?last=12h', headers=headers).json()" ] }, { "cell_type": "code", "execution_count": 42, "metadata": {}, "outputs": [], "source": [ "## Alternate Query method from curl.trillworks.com\n", "## This query method may come in handy if you want to automate this script and increment the 1440m\n", "params = (('last', '97h'),)\n", "response = requests.get('https://vehiclecounter.data.thethingsnetwork.org/api/v2/query', headers=headers, params=params).json()" ] }, { "cell_type": "code", "execution_count": 43, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Raw DataFrame from TTN Swagger API:\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CarCountdevice_idrawtime
035loratestACM=2018-02-21T09:00:13.117325624Z
124loratestABg=2018-02-21T09:30:20.546748754Z
235loratestACM=2018-02-21T10:00:28.868778915Z
324loratestABg=2018-02-21T10:30:36.766391925Z
428loratestABw=2018-02-21T11:00:44.669485546Z
\n", "
" ], "text/plain": [ " CarCount device_id raw time\n", "0 35 loratest ACM= 2018-02-21T09:00:13.117325624Z\n", "1 24 loratest ABg= 2018-02-21T09:30:20.546748754Z\n", "2 35 loratest ACM= 2018-02-21T10:00:28.868778915Z\n", "3 24 loratest ABg= 2018-02-21T10:30:36.766391925Z\n", "4 28 loratest ABw= 2018-02-21T11:00:44.669485546Z" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "## Read the responses into a Pandas Dataframe\n", "df = pd.DataFrame.from_dict(response)\n", "print('Raw DataFrame from TTN Swagger API:')\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [], "source": [ "# Writing Raw Data as .csv file\n", "df.to_csv('Raw_TTNData.csv', date_format=\"%d/%m/%Y %H:%M:%S\",index=False)\n", "# Writing the Raw Data file as json file\n", "df.to_json('Raw_TTNData.json', date_format=\"%d/%m/%Y %H:%M:%S\",index=False)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Data Cleaning and Organizing:" ] }, { "cell_type": "code", "execution_count": 44, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The Cleaned DataFrame:\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TTNTimeStampCarCount
02018-02-21 10:00:1335
12018-02-21 10:30:2024
22018-02-21 11:00:2835
32018-02-21 11:30:3624
42018-02-21 12:00:4428
\n", "
" ], "text/plain": [ " TTNTimeStamp CarCount\n", "0 2018-02-21 10:00:13 35\n", "1 2018-02-21 10:30:20 24\n", "2 2018-02-21 11:00:28 35\n", "3 2018-02-21 11:30:36 24\n", "4 2018-02-21 12:00:44 28" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "## Converting TTN Timestamps to pandas datetime format\n", "df.rename(columns={'time': 'TTNTimeStamp'}, inplace=True)\n", "df['TTNTimeStamp'] = pd.to_datetime(df['TTNTimeStamp'])\n", "\n", "## Offset Time by 1 hour to fix TimeZone Error of Swagger API TimeStamps\n", "df['TTNTimeStamp'] = df['TTNTimeStamp'] + pd.Timedelta(hours=1)\n", "\n", "## Strip the Microseconds from the time column\n", "df['TTNTimeStamp'] = df['TTNTimeStamp'].values.astype('datetime64[s]')\n", "\n", "## Setting index to the the TimeStamps Column\n", "# df.set_index(df[\"TTNTimeStamp\"],inplace=True) \n", "\n", "## Specify columns to Drop\n", "drop_cols = ['raw','device_id']\n", "df = df.drop(drop_cols, 1)\n", "\n", "## Reorder the dataframe\n", "df = df.reindex(['TTNTimeStamp','CarCount'], axis=1)\n", "\n", "print('The Cleaned DataFrame:')\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "RangeIndex: 153 entries, 0 to 152\n", "Data columns (total 2 columns):\n", "TTNTimeStamp 153 non-null datetime64[ns]\n", "CarCount 153 non-null int64\n", "dtypes: datetime64[ns](1), int64(1)\n", "memory usage: 2.5 KB\n" ] } ], "source": [ "df.info()" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [], "source": [ "# Writing the file as csv\n", "df.to_csv('TTN_FakeData_Clean.csv', date_format=\"%d/%m/%Y %H:%M:%S\",index=True)\n", "# Writing the file as json\n", "df.to_json('TTN_FakeData_Clean.json', date_format=\"%d/%m/%Y %H:%M:%S\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Resources:\n", "* [Downloading files with CURL from Command Line Interface](http://www.compciv.org/recipes/cli/downloading-with-curl/)" ] } ], "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.6.4" } }, "nbformat": 4, "nbformat_minor": 2 }