Using the MyGeotab python API to create custom reports

Every MyGeotab database comes with a set of built-in Excel reports that allow users to access and analyze their data. For customers who want to change these reports, we allow our templates to be adjusted, downloaded and reuploaded.

However, the raw data available within the stock report may not contain the data points each user needs, at which point you may need a seperate custom report.

Using MyGeotab Python Application Programming Interface (API), customers and resellers can easily pull in the data they want and add it to a spreadsheet. The general workflow outlined below will work with any of our APIs. (Read the Beginner’s Guide to APIs here). Python also has different libraries available that make the process easy and simple to use for everyone.

See Also: Excel Tips for Fleet Reporting: Top Formulas and Functions

Getting Started with the MyGeotab Python API

You will need to have Python installed on your computer. Geotab currently supports Python 2.7.9+, 3.4+ and pypy 4+.

You can install our API easily with pip:

$ pip install mygeotab

The source code as well as more examples can be found on the git repository.

A good development environment makes writing, testing and running scripts easier. A few options include:

• IDLE (includes Python install, a shell and editor for writing and running code)
• PyCharm (JetBrains’ Python IDE, with all the features you’re familiar with from IntelliJ IDEA)
• Atom (portable, customizable and useable for a wide variety of languages)
• Spyder (Python only, geared towards data science projects)

To get the most out of your experience, it is recommended you use other Python libraries to make working with your data easier. 

Here are some libraries you can install:

• Openpyxl (for reading and writing Excel files, keep in mind this will not work with older .xls file extension)
• Xlrd (another general option for working with Excel files)
• Pandas (a popular option for data science/big data projects, it makes working with data frames easy)
• GGmatplotlib (this can be used to generate graphs for insertion into spreadsheets)

Pulling in Data

Once you’ve set up your development environment, you’ll need to decide what data you’re going to display. You can find documentation on available SDK objects and searches on our API reference page.

Here’s an example of polling for raw odometer data:

results = api.get("StatusData", search = {"fromDate": "2018-09-01T00:00:00.000Z",

"diagnosticSearch": {"id": "DiagnosticRawOdometerId"}})

The overall structure of Get and other calls will be the same as our Javascript API.

Note that to moderate the size of data returned, nested objects will only contain the ID, and no other fields from that object (take a look at our building block approach). For example, if you are trying to pull duty status logs, the driver ID will be populated but another Get call would be necessary to attain the driver’s name or license number.

logs = api.get("DutyStatusLog", search = {"fromDate": "2018-09-01T00:00:00.000Z",

"deviceSearch": {"id": device['id']}})

for log in logs:

driver = api.get("User", resultsLimit=1, search={"id": log['driver']['id']})

Creating Your Report

Now that you have your data, it’s time to create a spreadsheet. There are a lot of options to consider such as how many columns and sheets you want, if you need any chart object and the file type you would like to use. Depending on your answers, some libraries may be easier to work with than others.

For example, Pandas has a very simple method to write a data frame to a .csv file.

df = pandas.DataFrame(logs)

df.to_csv(r"C:\Users\kaylanmettus\Desktop\myCSV.csv")

Openpyxl has built-in support for creating simple charts and inserting them in one of your sheets. You can find out more information on Openpyxl here.

In general, the structure of an Excel spreadsheet object is as follows:

• The workbook is the overall file.
• Each workbook contains one or more worksheets.
• Each worksheet contains a grid of cells that will contain your data values. A worksheet may also contain a chart object that is anchored on a cell.

Making a report from scratch provides a lot of flexibility in both the design of the spreadsheet and the way it can be used.

Another factor to consider is when and how your script will be run. Do you need to pull data and email the report every day? In that case, you might want to automate this process and set your script to run as a Windows service or with Windows Task Scheduler.

Or, do you want to reduce the number of files you’re maintaining on a regular basis? If so, store one spreadsheet and append your daily, weekly or monthly numbers to the existing one instead of generating new reports.

You can use Openpyxl to do this:

book = openpyxl.load_workbook(workbookFilePath, data_only=True)

sheet = book["mySheet"]

for user in users():

sheet.append(user)

If you need your script to be portable and available to others within an organization, consider using a Python notebook instead of code stored on your local machine. You can also find more Python examples on the github page.

To learn more about custom reporting, watch this video tutorial on the basics of custom reports and go to the Geotab Marketplace to browse additional reports available for download.

If you have any questions, ideas or suggestions, visit the SDK forum or comment down below.

Read more from Developers:

Validating Data on the Geotab GO Device

Get Started With the MyGeotab API Wrapper