To Read the data from the Swagger API of the Things Network we use the following website curl.trillworks.com to Parse Curl link from TTN Swagger API to Python request code.
# Author: Dilip Rajkumar
# Email: d.rajkumar@hbksaar.de
# Date: 18/01/2018
# Revision: version#1
# License: MIT License
import pandas as pd
import requests
headers = {'Accept': 'application/json','Authorization': 'key ttn-account-v2.P4kRaEqenNGbIdFSgSLDJGMav5K9YrekkMm_F1lOVrw'}
## Query from TTN Swagger API
## last = 12h represents the last 12 hour, you can also query data for 1d, 48h, 1h, 30s etc..
# response = requests.get('https://vehiclecounter.data.thethingsnetwork.org/api/v2/query?last=12h', headers=headers).json()
## Alternate Query method from curl.trillworks.com
## This query method may come in handy if you want to automate this script and increment the 1440m
params = (('last', '97h'),)
response = requests.get('https://vehiclecounter.data.thethingsnetwork.org/api/v2/query', headers=headers, params=params).json()
## Read the responses into a Pandas Dataframe
df = pd.DataFrame.from_dict(response)
print('Raw DataFrame from TTN Swagger API:')
df.head()
Raw DataFrame from TTN Swagger API:
CarCount | device_id | raw | time | |
---|---|---|---|---|
0 | 35 | loratest | ACM= | 2018-02-21T09:00:13.117325624Z |
1 | 24 | loratest | ABg= | 2018-02-21T09:30:20.546748754Z |
2 | 35 | loratest | ACM= | 2018-02-21T10:00:28.868778915Z |
3 | 24 | loratest | ABg= | 2018-02-21T10:30:36.766391925Z |
4 | 28 | loratest | ABw= | 2018-02-21T11:00:44.669485546Z |
# Writing Raw Data as .csv file
df.to_csv('Raw_TTNData.csv', date_format="%d/%m/%Y %H:%M:%S",index=False)
# Writing the Raw Data file as json file
df.to_json('Raw_TTNData.json', date_format="%d/%m/%Y %H:%M:%S",index=False)
## Converting TTN Timestamps to pandas datetime format
df.rename(columns={'time': 'TTNTimeStamp'}, inplace=True)
df['TTNTimeStamp'] = pd.to_datetime(df['TTNTimeStamp'])
## Offset Time by 1 hour to fix TimeZone Error of Swagger API TimeStamps
df['TTNTimeStamp'] = df['TTNTimeStamp'] + pd.Timedelta(hours=1)
## Strip the Microseconds from the time column
df['TTNTimeStamp'] = df['TTNTimeStamp'].values.astype('datetime64[s]')
## Setting index to the the TimeStamps Column
# df.set_index(df["TTNTimeStamp"],inplace=True)
## Specify columns to Drop
drop_cols = ['raw','device_id']
df = df.drop(drop_cols, 1)
## Reorder the dataframe
df = df.reindex(['TTNTimeStamp','CarCount'], axis=1)
print('The Cleaned DataFrame:')
df.head()
The Cleaned DataFrame:
TTNTimeStamp | CarCount | |
---|---|---|
0 | 2018-02-21 10:00:13 | 35 |
1 | 2018-02-21 10:30:20 | 24 |
2 | 2018-02-21 11:00:28 | 35 |
3 | 2018-02-21 11:30:36 | 24 |
4 | 2018-02-21 12:00:44 | 28 |
df.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 153 entries, 0 to 152 Data columns (total 2 columns): TTNTimeStamp 153 non-null datetime64[ns] CarCount 153 non-null int64 dtypes: datetime64[ns](1), int64(1) memory usage: 2.5 KB
# Writing the file as csv
df.to_csv('TTN_FakeData_Clean.csv', date_format="%d/%m/%Y %H:%M:%S",index=True)
# Writing the file as json
df.to_json('TTN_FakeData_Clean.json', date_format="%d/%m/%Y %H:%M:%S")