Previous posts in the series:

• Car Stats: OBDII + Android + Torque: I introduced the hardware I was using to have this fun.
• Car Stats: Speed vs Instant MPG: I showed what the scatter plot looks like from a single run.
• Car Stats: Automatic Upload to Dropbox: How to get the data off your phone to your computer, easily.
• Car Stats: Where Have I been?:  Plotting where we’ve been with Powershell and Windows Forms

Revisiting the second post in this series, but this time:

• All the data collected to date
• Red for uphill, green for downhill, size of point for magnitude
• Blue for mostly level

Analysis:

• The ECU allows idling the engine when coasting, until I get to the last gear, at which time it changes its strategy – it always provides enough fuel to keep the engine purring at a somewhat higher number.  Probably because it doesn’t disengage the drivetrain.  But it does reduce the gas such that it’s a flat line across at 110 mpg or so.    (just enough oomph to prevent the engine from spinning down so fast that the car feels like its stuck in mud, probably.)
• I get better gas mileage around 42 mph – closer to 40mpg.  Then it drops down to the 33mpg range as I get up to 55, but pretty much stays there through 75mph.
• When accelerating, the engine opens up in such a way that I get a nice flat line at the bottom of the graph.

Code comments:

• I added a column for altitude change – detected it within a file, I didn’t want to do it outside of the file boundary.
• Sometimes, there’s a trailing comma in the column names.
• I added better Axes to the graph.

Code:

$alldata = @(); 
$files = gci . -r -include "trackLog.csv"
foreach ($file in $files) { 
   $lines = get-content $file
   "Processing {0}: {1} lines" -f $file, $lines.count
   
   # to get around errors with header names not being valid object names
   $lines[0] = $lines[0] -ireplace '[^a-z,]','' 
   if (-not $lines[0].EndsWith(",")) { $lines[0] = $lines[0] + "," } 
   $lines[0] = $lines[0] + "AltChange"
   
   $data = ($lines | convertfrom-csv)
   
   for ($i=1; $i -lt $data.Count; $i++) { 
       $prevAlt = [double]$data[$i-1].AltitudeM
       $Alt = [double]$data[$i].AltitudeM
       if ($prevAlt -ne $null -and $Alt -ne $null) { 
           $data[$i].AltChange = $Alt - $prevAlt
       }
   }
   
   $alldata = $alldata + $data
}
"Total of {0} items" -f $alldata.count

$altmeasure = $alldata | measure-object AltChange -min -max -average

[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")
$chart = new-object System.Windows.Forms.DataVisualization.Charting.Chart
$chart.width = 800
$chart.Height = 600
$chart.Left = 40
$chart.top = 30
$chart.Name = "Foo"

$chartarea = new-object system.windows.forms.datavisualization.charting.chartarea
$chart.ChartAreas.Add($chartarea)

$legend = New-Object system.Windows.Forms.DataVisualization.Charting.Legend
$chart.Legends.Add($legend)

$series = $chart.Series.Add("Series1")
$series = $chart.Series["Series1"]
#FastPoint ignores color
$series.ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Point
$series.IsXValueIndexed = $false

$thresh = 0.05

foreach ($data in $alldata)
{
    if ($data.MilesPerGallonInstantMpg -eq $null) { continue } 
    if ($data.GpsSpeedkmh              -eq $null) { continue } 
    if ($data.AltChange                -eq $null) { continue } 

    $speed = [double]$data.GpsSpeedkmh * 0.621371          
    $mpg =   [double]$data.MilesPerGallonInstantMpg 
    $alt =   [double]$data.AltChange  
        
    if ($alt -lt -$thresh) { 
        # downhill green
        $color = [System.Drawing.Color]::FromARGB(100,0,255,0)
        $markersize = 1 - ($alt*2)
    } elseif ($alt -lt $thresh) { 
        $color = [System.Drawing.Color]::FromARGB(100,0,0,255)
        $markersize = 2
    } else  { 
        # uphill red
        $color = [System.Drawing.Color]::FromARGB(100,255,0,0)
        $markersize = 1+$alt*2
    }  
    
    if ($markersize -gt 5) { $markersize = 5 }
    
    $datapoint = New-Object System.Windows.Forms.DataVisualization.Charting.DataPoint($speed,$mpg)   
    $datapoint.Color = $color
    $datapoint.MarkerSize = $markersize
    
    $series.Points.Add($datapoint)
}

$chartarea.AxisX.Name = "Speed MPH"
$chartarea.AxisX.Interval = 5
$chartarea.AxisX.Minimum = 0
$chartarea.AxisX.IsStartedFromZero=$true

$chartarea.AxisY.Name = "MPG"
$chartarea.AxisY.Interval = 10
$chartArea.AxisY.Minimum = 0
$chartarea.AxisY.IsStartedFromZero=$true

$Form = New-Object Windows.Forms.Form 
$Form.Text = "PowerShell Chart" 
$Form.Width = 1100 
$Form.Height = 600 
$Form.controls.add($Chart) 
$Chart.Dock = "Fill" 
$Form.Add_Shown({$Form.Activate()}) 
$Form.ShowDialog()

Previous posts in the series:

• Car Stats: OBDII + Android + Torque: I introduced the hardware I was using to have this fun.
• Car Stats: Speed vs Instant MPG: I showed what the scatter plot looks like from a single run.
• Car Stats: Automatic Upload to Dropbox:  How to get the data off your phone to your computer, easily.

I could barely contain myself.  Thurday night, I had all kinds of data.. just calling my name.  

GPS Time, Device Time, Longitude, Latitude,GPS Speed(km/h), Horizontal Dilution of Precision, Altitude(m), Bearing, Gravity X(G), Gravity Y(G), Gravity Z(G),Miles Per Gallon(Instant)(mpg),GPS Altitude(m),Speed (GPS)(km/h),Run time since engine start(s),Speed (OBD)(km/h),Miles Per Gallon(Long Term Average)(mpg),Fuel flow rate/minute(cc/min),CO₂ in g/km (Average)(g/km),CO₂ in g/km (Instantaneous)(g/km)
Thu Jan 03 16:29:06 EST 2013,03-Jan-2013 16:29:11.133,-85.57728556666666,38.24568238333333,0.0,16.0,196.9,0.0,-0.015994536,0.9956599,0.0949334,0,196.9,0,17,0,31.66748047,19.45585442,259.47247314,-
Thu Jan 03 16:29:09 EST 2013,03-Jan-2013 16:29:14.004,-85.57729401666667,38.245684716666666,0.0,12.0,195.2,0.0,-0.015994536,0.9956599,0.0949334,0,195.2,0,20,0,31.66731453,45.80973816,259.47247314,-

Friday afternoon, once work things were completed, I started playing with it.  To start, I tried to read in the CSV file into Powershell.   I figured once I had it there, I could do *something* with it.

I faced some challenges:

• The CSV column names are not “clean”, so I needed to sanitize them
• Some files did not have certain pieces of data.
• CSV import was a string, needed to be casted to a number before certain operations (“137” –gt 80.0 = false)
• The units are fixed, part of the Torque app. (Actually, part of the OBDII standard)

After getting it read in, I looked around for a graphing library. Turns out I can use System.Windows.Forms.DataVisualization with Powershell. (thank you sir), which had some fun stuff:

• FastPoint ignores colors
• Had to turn off auto-scale on the Y-Axis
• Made the charting controll Dock-Fill in the form

I ended up with this script:

$alldata = @(); 
$files = gci . -r -include "trackLog.csv"
foreach ($file in $files) { 
   $lines = get-content $file
   "Processing {0}: {1} lines" -f $file, $lines.count
   
   # to get around errors with header names not being valid object names
   $lines[0] = $lines[0] -ireplace '[^a-z,]','' 
   
   $data = ($lines | convertfrom-csv)
   $alldata = $alldata + $data
}
"Total of {0} items" -f $alldata.count

$speedmeasure = $alldata | measure-object GpsSpeedKmh -min -max
$speedspread = $speedmeasure.Maximum - $speedmeasure.Minimum
if ($speedspread -le 1.0) { $speedspread = 1.0 }

$mpgmeasure = $alldata | measure-object MilesPerGallonInstantmpg -min -max
$mpgspread = $mpgmeasure.Maximum - $mpgmeasure.Minimum
if ($mpgspread -le 1.0) { $mpgspread = 1.0 }

$ffmeasure = $alldata | where-object { $_.Fuelflowrateminuteccmin -ne "-" } | measure-object Fuelflowrateminuteccmin -min -max
$ffspread = $ffmeasure.Maximum - $ffmeasure.Minimum
if ($ffspread -le 1.0) { $ffspread = 1.0 }

[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")
$chart = new-object System.Windows.Forms.DataVisualization.Charting.Chart
$chart.width = 800
$chart.Height = 600
$chart.Left = 40
$chart.top = 30
$chart.Name = "Foo"

$chartarea = new-object system.windows.forms.datavisualization.charting.chartarea
$chart.ChartAreas.Add($chartarea)

$legend = New-Object system.Windows.Forms.DataVisualization.Charting.Legend
$chart.Legends.Add($legend)

$series = $chart.Series.Add("Series1")
$series = $chart.Series["Series1"]
#FastPoint ignores color
$series.ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Point
$series.IsXValueIndexed = $false

foreach ($data in $alldata)
{
    if ($data.MilesPerGallonInstantMpg -eq $null) { continue } 
    if ($data.Fuelflowrateminuteccmin -eq $null) { continue } 
    if ($data.Fuelflowrateminuteccmin -eq "-") { continue } 

    $speed = (([double]$data.GpsSpeedkmh              - $speedmeasure.Minimum) / $speedspread)  
    $mpg =   (([double]$data.MilesPerGallonInstantMpg -    $mpgspread.Minimum) /   $mpgspread)
    $ff    = (([double]$data.Fuelflowrateminuteccmin  -    $ffmeasure.Minimum) /    $ffspread)
    
    $higherspeed = $speed; 
    if ($higherspeed -gt 0.05) { $higherspeed = [Math]::Sqrt($speed) }
    $lowerspeed = $speed * $speed; 
    
    # MPG numbers seem to be clustered closer to 0 with a few annoying outlyers. spread them up a bit.
    #if ($mpg -gt 0.05) { $mpg = [Math]::Sqrt($mpg) }    

    # calculate color.   
    $blue = 250*$ff;
    
    # slower = more red
    # faster = more green
    # medium = yELLOW!
    $red = 250 - (250 * $lowerspeed)
    $green = 250 * $higherspeed
    
    $datapoint = New-Object System.Windows.Forms.DataVisualization.Charting.DataPoint($data.Longitude, $data.Latitude)   
    $color = [System.Drawing.Color]::FromARgb(125,$red, $green, $blue)
    
    $datapoint.Color = $color
    $series.Points.Add($datapoint)
    $datapoint.MarkerSize = ($mpg)*5 + 1
}

$chartarea.AxisY.IsStartedFromZero=$false

$Form = New-Object Windows.Forms.Form 
$Form.Text = "PowerShell Chart" 
$Form.Width = 1100 
$Form.Height = 600 
$Form.controls.add($Chart) 
$Chart.Dock = "Fill" 
$Form.Add_Shown({$Form.Activate()}) 
$Form.ShowDialog()

Which reads everything in, scrubs some data, figures some transforms, and yields me the following pretty picture:

• Green = faster, Red = slower.
• Blue = Gas used (doesn’t show very well)
• Fatter = Better MPG (I should reverse this, probably)
• The green lines are the interstates (I71, I264, I64, and I265 shown)
• Stop lights show up as little red dots.
• I had to hand-scale the window till it looked right. Real co-ordinate systems some other day.

I would like to do this in 3-D, but I haven’t gotten my Processing chops quite figured out yet. Maybe next week!