load.py

#!/usr/bin/env python

import sqlite3
import glob
import json
import sys
import dateutil.parser
import time
import math
import tables
import pandas as pd
import numpy as np
import h5py
import csv
import os
import isodate

signals = {
  191:  { 'name': 'speed', 'cvt': float },            # 191 Speed number km/h Vehicle speed
  25:   { 'name': 'brake', 'cvt': int },              # 25 Brake boolean On/Off Brake pedal is pressed
  13:   { 'name': 'temp', 'cvt': float },             # 13 Ambient Air Temperature number °C Temperature outside the vehicle
  262:  { 'name': 'wiper-slow', 'cvt': float },       # 262 Wiper Slow boolean On/Off Wiper is in slow mode
  254:  { 'name': 'wiper-interval', 'cvt': int },     # 254 Wiper Interval boolean On/Off Wiper is in interval mode
  253:  { 'name': 'wiper-fast', 'cvt': int },         # 253 Wiper Fast boolean On/Off Wiper is operating in the highest possible speed
  112:  { 'name': 'hazard', 'cvt': int },             # 112 Hazard Lights Switch boolean On/Off Hazard lights switch is enabled
  102:  { 'name': 'fuel-consumption', 'cvt': float }, # 102 Fuel Consumption number mL Amount of fuel consumed by the engine
  100:  { 'name': 'fog-front', 'cvt': int },          # 100 Front Fog Light boolean On/Off Front fog lights are on
  170:  { 'name': 'fog-rear', 'cvt': int },           # 100 Front Fog Light boolean On/Off Front fog lights are on
  257:  { 'name': '257', 'cvt': float },
  258:  { 'name': '258', 'cvt': float },
}
signal_names = [signal['name'] for signal in signals.values()]

pluck = lambda dict, *args: ((dict[arg] if arg in dict else None) for arg in args)

#def haversine(lon1, lat1, lon2, lat2):
#    """
#    Calculate the great circle distance between two points
#    on the earth (specified in decimal degrees)
#    """
#    # convert decimal degrees to radians
#    lon1, lat1, lon2, lat2 = map(math.radians, [lon1, lat1, lon2, lat2])
#
#    # haversine formula
#    dlon = lon2 - lon1
#    dlat = lat2 - lat1
#    a = math.sin(dlat/2)**2 + math.cos(lat1) * math.cos(lat2) * math.sin(dlon/2)**2
#    c = 2 * math.asin(math.sqrt(a))
#    r = 6371 # Radius of earth in kilometers. Use 3956 for miles
#    return c * r

stored = 0
novin = 0
nopos = 0
newsig = {}
cars = {}
for trip in glob.glob('*/*/*/*.json'):
  data = json.load(open(trip))['Result']
  if data is None: continue
  for row in data:
    vin, location, signal, value, ts = pluck(row, 'Vin', 'Location', 'SignalId', 'Value', 'Time')
    if vin is None:
      novin += 1
      continue

    lat, lon = [float(coord) for coord in location.replace('"', '').replace(' ', '').split(',')]
    if lat is None or math.isnan(lat) or lon is None or math.isnan(lon):
      print(json.dumps(row))
      continue

    #ts = isodate.parse_datetime(ts)

    stored += 1
    if stored % 10000 == 0: print(stored)

    # one line per t:vin
    t = ts + ':' + vin
    if not t in cars: cars[t] = {}
    tick = cars[t]

    # if we get conflicting gps coords, tell me the distance between them
    #if (tick['lat'] is not None and tick['lat'] != lat) or (tick['lon'] is not None and tick[vin][ts]['lon'] != lon):
    #  dist = haversine(tick['lon'], tick['lat'], lon, lat)
    #  if dist > 0.5: print('location mismatch ' + str(dist))

    tick['time'] = ts
    tick['timestamp'] = isodate.parse_datetime(ts).timestamp()
    tick['vin'] = vin
    tick['lat'] = lat
    tick['lon'] = lon

    tick[signals[signal]['name']] = signals[signal]['cvt'](value)

  #if stored > 1000: break

print(f'{stored} stored, {novin} ({novin * 100 / (stored + novin)}%) without a VIN')

def convertToNumber (s):
  return int.from_bytes(s.encode(), 'little')

os.remove('pivoted.csv')
csvdump = csv.writer(open('pivoted.csv', 'w'), quoting=csv.QUOTE_MINIMAL)
cols = ['time', 'timestamp', 'vin', 'lat', 'lon'] + signal_names
csvdump.writerow(cols)
for t in sorted(cars.keys()):
  data = cars[t]
  csvdump.writerow([(data[col] if col in data else None) for col in cols])