load.py

#!/usr/bin/env python

import glob
import json
import sys
import dateutil.parser
import time
import math
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
  257:  { 'name': 'wiper-motor', 'cvt': float },      # 257 : Wiper Motor
  258:  { 'name': 'wiper-once', 'cvt': float },       # 258 : Wiper Once
  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
}
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
cars = {}
for trip in glob.glob('201*/*/*/*.*'):
  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])