{
"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",
" CarCount | \n",
" device_id | \n",
" raw | \n",
" time | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 35 | \n",
" loratest | \n",
" ACM= | \n",
" 2018-02-21T09:00:13.117325624Z | \n",
"
\n",
" \n",
" 1 | \n",
" 24 | \n",
" loratest | \n",
" ABg= | \n",
" 2018-02-21T09:30:20.546748754Z | \n",
"
\n",
" \n",
" 2 | \n",
" 35 | \n",
" loratest | \n",
" ACM= | \n",
" 2018-02-21T10:00:28.868778915Z | \n",
"
\n",
" \n",
" 3 | \n",
" 24 | \n",
" loratest | \n",
" ABg= | \n",
" 2018-02-21T10:30:36.766391925Z | \n",
"
\n",
" \n",
" 4 | \n",
" 28 | \n",
" loratest | \n",
" ABw= | \n",
" 2018-02-21T11:00:44.669485546Z | \n",
"
\n",
" \n",
"
\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",
" TTNTimeStamp | \n",
" CarCount | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 2018-02-21 10:00:13 | \n",
" 35 | \n",
"
\n",
" \n",
" 1 | \n",
" 2018-02-21 10:30:20 | \n",
" 24 | \n",
"
\n",
" \n",
" 2 | \n",
" 2018-02-21 11:00:28 | \n",
" 35 | \n",
"
\n",
" \n",
" 3 | \n",
" 2018-02-21 11:30:36 | \n",
" 24 | \n",
"
\n",
" \n",
" 4 | \n",
" 2018-02-21 12:00:44 | \n",
" 28 | \n",
"
\n",
" \n",
"
\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
}