Search
  • kayleebement

How to create a custom CSV with Medium One event data

Updated: Aug 26

Medium One's Dashboard Widgets allow users to export CSVs with event data, but sometimes users want more control over the customization of their CSV. This is a quick tutorial to show you how to build a python script to query Medium One data and build a CSV.


In this Quick Start Guide, you will:

  1. Use Medium One's REST API to log in and query data

  2. Construct a CSV with the data you are interested in


Here’s what you need to get started:

  1. Medium One Prototyping Sandbox Account [Buy]

  2. Completion of Getting Started Tutorial [Link]

  3. Data on Medium One

  4. Python (Python2 is used in this tutorial) & basic understanding of Python

  5. requests Python library [Installation Instructions]


Step 1. Create a Business User

It is possible to complete this tutorial using a Basic User login, but a Business User is recommended so that you can access any user's data. For more information about the different user types on Medium One, read our User Roles and Permissions docs.


It is important to note that using your personal Medium One login will not work for these queries. You must use either a Business or Basic User created within the platform.


  1. On the Setup Manage Administrators page, click Add New User.

  2. Select the API Business user type and enter credentials for your user.

  3. Click Save. Remember these credentials; you will need them later.


Step 2. Create an API Key

Although you should already have an API Key from the Getting Started Tutorial, it is recommended to have a new API Key for each source that interacts with your Medium One project for security purposes.


  1. On the SetupManage API Keys page, click Add New API Key. Enter a brief description and make sure Enabled is checked.


Step 3. Create Python script

Now we will go over each section of the Python script in order.


Imports + Start HTTP Session

import requests # for HTTP requests
import json # to parse JSON
import csv # to create CSV file

session = requests.session() # starts HTTP session

The imports should be at the beginning of your python file. They are necessary for the rest of the script. The HTTP session should be right after the imports since we will need the session for the upcoming requests to Medium One.


Log In

# Log in as business user
login_id = "<business user login>"
password = "<business user password>"
api_key = "<api key>"

login_body = {'login_id': login_id, 'password': password, 'api_key': api_key}
login_headers = {'Content-Type': 'application/json', 'Accept': 'application/json'} # required headers for login

response = session.post('https://api.mediumone.com/v2/login', data=json.dumps(login_body), headers=lrequsogin_headers)

Replace <business user login> and <business user password> with your credentials from Step 1, and replace the <api key> with your API Key from Step 2.


For more information about logging into Medium One via REST, check out our Authentication doc.


Request Data

# Get user data
basic_user = "<user to get data from>"
request_url = 'https://api.mediumone.com/v2/events/<stream name>/' + basic_user
request_headers = {'Accept': 'application/json'}
params = {
    "since":"2020-08-12T00:00:00.000-0800", 
    "until":"2020-08-24T00:00:00.000-0800", 
    "limit":100}
response = session.get(url=request_url, params=params, headers=request_headers)

Replace <user to get data from> with the basic user you are interested in and replace <stream name> with the name of the stream you want to query data from. For example, if you are querying from user "device_1" from the raw stream, your request_url should be "https://api.mediumone.com/v2/events/raw/device_1".


You can also change the params dictionary to fit the data you are interested in.


To see all of the possible parameters and learn more about this request, go to the "Retrieve events" section of our CRUD Events doc.


Prepare Data for CSV

# Prepare data for CSV
event_strings = response.content.splitlines() # one event per line in response, this will create a list of events

events = [json.loads(event) for event in event_strings] # events come in as strings, need to convert to dictionaries

rows = []
for event in events: # create a list of rows for the csv including all event data as well as the time of the event (observed_at)
    row = event["event_data"]
    row["observed_at"] = event["observed_at"]
    rows.append(row)

The incoming event data is received as 1 event string per line, so we convert this to an array of strings and then an array of dictionaries so we can access the data. Then, we create a "rows" array, which will be provided to the CSV writer.


If you are not interested in the observed_at time, "rows" can simply be initialized as:

rows = [event["event_data"] for event in events]

Create CSV

# Create CSV
csv_headers = ["device_id", "observed_at", "batt", "cellLoc", "gps", "gpsAlt", "gpsQual", "temp", "rssi", "totalTime", "gpsTTFF", "cellTTFF"] # all tags we are interested in

filename = "mydata.csv" # name of file to save to

with open(filename, 'w') as f: # open file for writing
    writer = csv.DictWriter(f, fieldnames=csv_headers, extrasaction='ignore') # last param means that if a row includes a tag we don't have in our headers, it will simply ignore it instead of raising an error
    writer.writeheader() # create header
    writer.writerows(rows) # write all rows

The last part of the code actually creates the CSV file with the specified headers and the data we processed in the last step.


Putting It All Together

The finalized script should look like this:

import requests # for HTTP requests
import json # to parse JSON
import csv # to create CSV file

session = requests.session() # starts HTTP session

# Log in as business user
login_id = "<business user login>"
password = "<business user password>"
api_key = "<api key>"

login_body = {'login_id': login_id, 'password': password, 'api_key': api_key}
login_headers = {'Content-Type': 'application/json', 'Accept': 'application/json'} # required headers for login

response = session.post('https://api.mediumone.com/v2/login', data=json.dumps(login_body), headers=login_headers)

# Get user data
basic_user = "<user to get data from>"
request_url = 'https://api.mediumone.com/v2/events/<stream>/' + basic_user
request_headers = {'Accept': 'application/json'}
params = {
    "since":"2020-08-12T00:00:00.000-0800", 
    "until":"2020-08-24T00:00:00.000-0800", 
    "limit":100}
response = session.get(url=request_url, params=params, headers=request_headers)

# Prepare data for CSV
event_strings = response.content.splitlines() # one event per line in response, this will create a list of events

events = [json.loads(event) for event in event_strings] # events come in as strings, need to convert to dictionaries

rows = []
for event in events: # create a list of rows for the csv including all event data as well as the time of the event (observed_at)
    row = event["event_data"]
    row["observed_at"] = event["observed_at"]
    rows.append(row)

# Create CSV
csv_headers = ["device_id", "observed_at", "batt", "cellLoc", "gps", "gpsAlt", "gpsQual", "temp", "rssi", "totalTime", "gpsTTFF", "cellTTFF"] # all tags we are interested in

filename = "mydata.csv" # name of file to save to

with open(filename, 'w') as f: # open file for writing
    writer = csv.DictWriter(f, fieldnames=csv_headers, extrasaction='ignore') # last param means that if a row includes a tag we don't have in our headers, it will simply ignore it instead of raising an error
    writer.writeheader() # create header
    writer.writerows(rows) # write all rows

Then you can run the script to get your CSV!


Congratulations, you have now created a script to process your data and create a custom CSV. This script just contains the basics - with simple modifications, you can add different request parameters, iterate over multiple users, and more.

Want to learn more about what Medium One can do? Check out our documentation and tutorials & get started on your next IoT project!

© 2018 Medium One   All Rights Reserved

4633 Old Ironsides Dr. #280, Santa Clara, CA  95054                

info@mediumone.com

|

  • YouTube - White Circle