January | 2014 | Jamie Dixon's Home

Basic Insert Operation Using F#

January 28, 2014 4 Comments

So the more I use F#, the more I come to understand the benefits and limitations of the language. Since I spend a majority of my day job in C# and JavaScript, it is a natural comparison between these two languages and F#. One of the tenants of F# is ‘less noise, more signal’. After looking at some projects, I am coming to the conclusion that Entity Framework, LinqToSql, <Any other ORM> is just noise. It is expensive noise at that – if you have worked on a production app using EF and you to do anything outside of the examples on MSDN, you know what I mean.

So can EF type providers replace the overhead, code bloat, and additional costs of Entity Framework? I decided to do a small test to see. I needed to load into SqlServer 27,481 records of crash data that I get from the North Carolina DOT. The records came to me in an Excel Spreadsheet which I pumped into MSAccess. Then, instead of using SQl Server SSIS/Bulk Data Load functions, I decided to create an app that pulls that data from the Access database and load it into the SqlServer database via a type provider.

My first step was to look for a MSAccess type provider. No luck. I then hit up Stack Overflow and found this and this article for working with Access. I coded up a solution to get the data into a DataReader like so

static member GetCrashData =
    let connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Documents\Road Alert\WakeCountyCrashes.accdb; Persist Security Info=False;"
    use connection = new OleDbConnection(connectionString)
    let commandText = "Select * from Data"
    use command = new OleDbCommand(commandText,connection)
    connection.Open()
    use reader = command.ExecuteReader()

My first attempt to get the data from the read was a tuple like so:

[while reader.Read() do
     yield reader.GetInt32(0), 
     reader.GetFieldValue(1).ToString(), 
     reader.GetFieldValue(2).ToString(), 
     reader.GetDouble(3),
     reader.GetFieldValue(4).ToString(), 
     reader.GetFieldValue(5).ToString(), 
     reader.GetFieldValue(6).ToString(), 
     reader.GetDateTime(7), 
     reader.GetDateTime(8), 
     reader.GetFieldValue(9).ToString(), 
     reader.GetFieldValue(10).ToString() 
 ]

Sure enough, it works like a champ from my C# UI (once I ran AccessDatabaseEngine.exe on my machine – sigh)

private static void GetCrashData()
{
    var results = CrashDataLoader.GetCrashData;
    Console.WriteLine(results.Count().ToString());
}

Gives

The next thing I did was to create a Type Provider

And then create the method to insert the data into the database:

static member LoadCrashData  =
    let targetDatabase = targetSchema.GetDataContext()
    let rows = CrashDataLoader.GetCrashData
    targetDatabase.TrafficCrashes.InsertAllOnSubmit(rows)
    targetDatabase.DataContext.SubmitChanges()
    true

The problem is that I ran into was that the GetCrashData was returning a Tuple and the LoadCrashData was expecting a Typed CrashData element. I searched for a bit and then gave up trying to figure out how to map the two without explicitly assigning each field. So then I did it the old fashion way like so:

static member TrafficCrashFromReader(reader: OleDbDataReader) =
    let trafficCrash = new targetSchema.ServiceTypes.TrafficCrashes()
    trafficCrash.NCDmvCrashId <- System.Nullable<float> (float (reader.GetFieldValue(0).ToString()))
    trafficCrash.Municipality <- reader.GetFieldValue(1).ToString()  
    trafficCrash.OnRoad <- reader.GetFieldValue(2).ToString()  
    trafficCrash.Miles <- System.Nullable<double> (double (reader.GetFieldValue(3).ToString()))
    trafficCrash.Direction <- reader.GetFieldValue(4).ToString()
    trafficCrash.FromRoad <- reader.GetFieldValue(5).ToString()
    trafficCrash.TowardRoad <- reader.GetFieldValue(6).ToString()
    trafficCrash.DateOfCrash <- System.Nullable<DateTime> (reader.GetDateTime(7))
    trafficCrash.TimeOfCrash <- System.Nullable<DateTime> (reader.GetDateTime(8))
    trafficCrash.CrashType <- reader.GetFieldValue(9).ToString()
    trafficCrash.CrashSeverity <- reader.GetFieldValue(10).ToString()
    trafficCrash

The fact that I am using the <- symbol is a code smell, but I am not sure how to get around it.

In any event, once I ran it in my Console app:

private static void LoadCrashData()
{
    Stopwatch stopWatch = new Stopwatch();
    stopWatch.Start();
    CrashDataLoader.LoadCrashData();
    stopWatch.Stop();
    Console.WriteLine("The load took " + stopWatch.Elapsed.TotalSeconds + " seconds.");
}

I got nothing after 30m minutes! Yikes.

I then went back and wrote a function to insert 1 row at a time:

static member LoadCrashDataRow dataRow =
    let targetDatabase = targetSchema.GetDataContext()
    targetDatabase.TrafficCrashes.InsertOnSubmit(dataRow)
    targetDatabase.DataContext.SubmitChanges()
    true

And the consuming app:

private static void LoadCrashData()
{
 
    var crashRows = CrashDataLoader.GetCrashData;
    Stopwatch stopWatch = new Stopwatch();
    stopWatch.Start();
    foreach (var crashRow in crashRows)
    {
        CrashDataLoader.LoadCrashDataRow(crashRow);
        Console.WriteLine(crashRow.NCDmvCrashId + " loaded.");
    }
    stopWatch.Stop();
    Console.WriteLine("The load took " + stopWatch.Elapsed.TotalSeconds + " seconds.");
}

Sure enough, it works like a champ.

So it is slow – though I am not sure EF is any faster. But not having to deal with that .edmx files, the .tt files, the whatever-else-we-throw-in files, I think further research is definitely warranted. Also, there are some other things I think F# Type Providers need to have:

1) Ability to handle proxies

2) Making Plural tables singular (The table name is Crashes, the type should be Crash)

3) An MS Access TP would be great

4) An Azure Sql Database TP would be doubly great

Filed under Entity Frameworks, F#

Traffic Stop Visualization Using D3

January 28, 2014 Leave a comment

One of the comments I got from the TRINUG Data SIG was that the data and analysis were exciting but the results were, well, boring. So I went back to the traffic stop data and thought about how I could sexy it up. Since lots of people have been using D3 to present the data. I thought it would be a good place to start.

My 1st step was to look at their samples page and they have a simple bar chart that seems like a good introduction to the library. I created an endpoint on my webapi for the summary data like so:

[HttpGet]
[Route("api/TrafficStopSearch/StopsByMonth/")]
public dynamic StopsByMonth()
{
    return ChickenSoftware.RoadAlert.Analysis.AnalysisEngine.TrafficStopsByMonth;
 
}

I then spun up an empty asp.net website and created an index page based on their sample. I then added an ajax call to the controller and replaced the reference to the data.tsv file:

$.ajax({
   url: "http://localhost:17680/api/TrafficStopSearch/StopsByMonth/&quot;,
   dataType: "json",
   success: function (data) {
       x.domain(data.map(function (d) { return d.m_Item1; }));
       y.domain([0, d3.max(data, function (d) { return d.m_Item6; })]);
 
       svg.append("g")
           .attr("class", "x axis")
           .attr("transform", "translate(0," + height + ")")
           .call(xAxis);
 
       svg.append("g")
           .attr("class", "y axis")
           .call(yAxis)
         .append("text")
           .attr("transform", "rotate(-90)")
           .attr("y", 6)
           .attr("dy", ".71em")
           .style("text-anchor", "end")
           .text("Frequency");
 
       svg.selectAll(".bar")
           .data(data)
         .enter().append("rect")
           .attr("class", "bar")
           .attr("x", function (d) { return x(d.m_Item1); })
           .attr("width", x.rangeBand())
           .attr("y", function (d) { return y(d.m_Item6); })
           .attr("height", function (d) { return height – y(d.m_Item6); });
       
   },
   error: function(e){
       alert("error");
   }
});

On thing to note is that the tuple that was created in F# and then passed though via the C# controller had its name changed. Specially, Tuple.Item1 became Tuple.m_Item1. I think that passing out tuple.anything is a horrible idea, so I created a POCO that actually lets the consumer know what each field means:

public class OutputValue
{
    public Int32 Month { get; set; }
    public Int32 ExpectedStops { get; set; }
    public Int32 ActualStops { get; set; }
    public Double DifferenceBetweenExpectedAndActual { get; set; }
    public Double PercentDifferenceBetweenExpectedAndActual { get; set; }
    public Double Frequency { get; set; }
}

and then I adjusted the controller like so:

 
[HttpGet]
[Route("api/TrafficStopSearch/StopsByMonth/")]
public dynamic StopsByMonth()
{
    var outputs = new List<OutputValue>();
    var resultSet= ChickenSoftware.RoadAlert.Analysis.AnalysisEngine.TrafficStopsByMonth;
    foreach (var tuple in resultSet)
    {
        var outputValue = new OutputValue()
        {
            Month = tuple.Item1,
            ExpectedStops = tuple.Item2,
            ActualStops = tuple.Item3,
            DifferenceBetweenExpectedAndActual = tuple.Item4,
            PercentDifferenceBetweenExpectedAndActual = tuple.Item5,
            Frequency = tuple.Item6
        };
        outputs.Add(outputValue);
    }
 
    return outputs;
}

So I adjusted the javascript and voila: a bar chart:

Up next – some real charts…

Filed under D3

Screen Scraping The Department Of Health

January 21, 2014 Leave a comment

As part of TRINUG’s Analytics SIG, some of the people were interested in health inspections found here. I created a Public Records Request (PRR) on their website’s intact form:

And in the comments, I said this:

I would like to make a Public Records Request on this data. I would like the following fields for all inspections 1/1/2013 to 12/31/2013:

InspectionId
InspectorId
EstablishmentId
EstablishmenName
EstablishmentAddress
EstablishmentCity
EstablishmentZip
EstablishmentLat
EstablishmentLong
EstablishmentTypeId
EstablishmentTypeDesc
InspectionScore
NumberOfNonCriticalViolations
NumberOfCriticalViolations
InspectionDate

After a week, I did not hear back (their response is supposed to be 48 hours) so I emailed the director:

Not wanting to wait any longer for the data, I decided to do some screen scraping. To that end, I did an on-line report and go something like this:

with the pages of the report down here:

I then went into source and checked out the pagination. Fortunately, it was uri-based so there are 144 different uris like this one:

I pulled down all of the uris and put them into Excel. I then trimmed off the <a and the text after the word “class”:

Fortunately, there was no restaurant with the word “class” in its name. I know have 144 uris ready to go. I then saved the uris into a .csv.

My next step is to suck these uris into a a database. I have learned the hard way that screen scraping is fraught with mal-formed data and unstable connections. Therefore, I will make a request and pull down a page and store it when the getting is good. I will then parse that page separately. Since the data appears in the past, I am less concerned about the data changing after I pull it local.

When I spun up an instance of Sql Server and tried to import the data, I kept getting things like this.

So it is pretty obvious that Sql Server doesn’t make it easy to import text like uris (and I can image HTML). I decided to spin up an instance of MongoDb. Also, because the F# MongoDb driver is not in NuGet, I decided to go with C#.

I then fired up a C# and read all of the uris into a List

static List<String> GetUrisFromFileSystem()
{
    var path = @"C:\HealthDepartment\Inspections.csv";
    var contents = File.ReadAllText(path);
    var splitContents = contents.Split('\n');
    var contentList = splitContents.ToList<String>();
    contentList.RemoveAt(0);
    return contentList;
}

I then wrote the list into MongoDb.

static void LoadDataIntoMongo(List<String> uris)
{
    var connectionString = "mongodb://localhost";
    var client = new MongoClient(connectionString);
    var server = client.GetServer();
    var database = server.GetDatabase("HealthDepartment");
    var collection = database.GetCollection<String>("UriEntities");
    foreach (String uri in uris)
    {
        var entity = new UriEntity { Uri = uri };
        collection.Insert(entity);
        var id = entity.Id;
        Console.WriteLine(id);
    }
 
    
}

The 1 gotcha is that I made my UriEntity class have a string as the Id. This is not idomatic to Mongo and I got this:

It needs to be a a type of ObjectId. Once I made that switch, I got this:

The fact that MongoDb makes things so much easier than SqlServer is really impressive.

With the Uris in Mongo, I then wanted to make a request to the individual pages. I created a method that got to the contents of the page:

static String GetHtmlForAUri(String uri)
{
    var fullyQualifiedUri = "http://wake.digitalhealthdepartment.com/&quot; + uri;
    var request = WebRequest.Create(fullyQualifiedUri);
    var response = request.GetResponse();
    using(var stream = response.GetResponseStream())
    {
        using(var reader = new StreamReader(stream))
        {
            return reader.ReadToEnd();
        }
    }
 
}

I then fired up class to take the contents that are associated with the Uri:

public class PageContentEntity
{
    public ObjectId Id { get; set; }
    public ObjectId UriId { get; set; }
    public String PageContent { get; set; }
}

And created a method to persist the contents:

static void LoadPageContentIntoMongo(PageContentEntity entity)
{
    var connectionString = "mongodb://localhost";
    var client = new MongoClient(connectionString);
    var server = client.GetServer();
    var database = server.GetDatabase("HealthDepartment");
    var collection = database.GetCollection<PageContentEntity>("PageContentEntities");
    collection.Insert(entity);
    Console.WriteLine(entity.Id);
}

And then a method to put everything together

static void LoadAllPageContentIntoMongo()
{
    var connectionString = "mongodb://localhost";
    var client = new MongoClient(connectionString);
    var server = client.GetServer();
    var database = server.GetDatabase("HealthDepartment");
    var collection = database.GetCollection<UriEntity>("UriEntities");
    foreach(var uriEntity in collection.FindAllAs<UriEntity>())
    {
        String pageContent =  GetHtmlForAUri(uriEntity.TargetUri);
        var pageEntity = new PageContentEntity()
        {
            UriId = uriEntity.Id,
            PageContent =pageContent
        };
        LoadPageContentIntoMongo(pageEntity);
    }
}

So sure enough, I know have all of the pages local. Doing something with it – that is the next trick…

Note that as soon as I finished up this piece, I got a note from the director of the department saying that they are looking at my request and will get back to me soon.

Filed under MongoDb

Traffic Stop Disposition: Classification Using F# and KNN

January 14, 2014 1 Comment

I have already looked at the summary statistics of the traffic stop data I received from the town here. My next stop was to try and do a machine learning exercise with the data. One of the more interesting questions I want to answer is what factors into weather a person gets a warning or a ticket (called disposition)? Of all of the factors that may be involved, the dataset that I have is fairly limited:

Using dispositionId as the result variable, there is StopDateTime and Location (Latitude/Longitude). Fortunately, DateTime can be decomposed into several input variables. For this exercise, I wanted to use the following:

TimeOfDay
DayOfWeek
DayOfMonth
MonthOfYear
Location (Latitude:Longitude)

And the resulting variable being disposition. To make it easier for analysis, I limited the analysis set to finalDisposition as either “verbal warning” or “citation” I decided to do a K-Nearest Neighbor because it is regarded as an easy machine learning algorithm to learn and the question does seem to be a classification problem.

My first step was to decide weather to write or borrow the KNN algorithm. After looking at what kind of code would be needed to write my own and then looking at some other libraries, I decided to use Accord.Net.

My next first step was to get the data via the web service I spun up here.

namespace ChickenSoftware.RoadAlert.Analysis
 
open FSharp.Data
open Microsoft.FSharp.Data.TypeProviders
open Accord.MachineLearning
 
type roadAlert2 = JsonProvider<"http://chickensoftware.com/roadalert/api/trafficstopsearch/Sample&quot;>
type MachineLearningEngine =
    static member RoadAlertDoc = roadAlert2.Load("http://chickensoftware.com/roadalert/api/trafficstopsearch&quot;)

My next first step was to filter the data to only verbal warnings (7) or citations (15).

  static member BaseDataSet =
      MachineLearningEngine.RoadAlertDoc
            |> Seq.filter(funx -> x.DispositionId = 7 || x.DispositionId = 15)
          |> Seq.map(fun x -> x.Id, x.StopDateTime, x.Latitude, x.Longitude, x.DispositionId)
          |> Seq.map(fun (a,b,c,d,e) -> a, b, System.Math.Round(c,3), System.Math.Round(d,3), e)
          |> Seq.map(fun (a,b,c,d,e) -> a, b, c.ToString() + ":" + d.ToString(), e)
          |> Seq.map(fun (a,b,c,d) -> a,b,c, match d with
                                              |7 -> 0
                                              |15 -> 1
                                              |_ -> 1)
          |> Seq.map(fun (a,b,c,d) -> a, b.Hour, b.DayOfWeek.GetHashCode(), b.Day, b.Month, c, d)
          |> Seq.toList

You will notice that I had to transform the dispositionIds from 7 and 15 to 1 and 0. The reason why is that the KNN method in Accord.Net assumes that the values match the index position in the array. I had to dig into the source code of Accord.Net to figure that one out.

My next step was to divide the dataset in half: one half being the training sample and the other the validation sample:

static member TrainingSample =
    let midNumber = MachineLearningEngine.NumberOfRecords/ 2
    MachineLearningEngine.BaseDataSet
        |> Seq.filter(fun (a,b,c,d,e,f,g) -> a < midNumber)
        |> Seq.toList
 
static member ValidationSample =
    let midNumber = MachineLearningEngine.NumberOfRecords/ 2
    MachineLearningEngine.BaseDataSet
        |> Seq.filter(fun (a,b,c,d,e,f,g) -> a > midNumber)
        |> Seq.toList

The next step was to actually run the KKN. Before I could do that though, I had to create the distance function. Since this was my 1st time, I dropped the geocoordinates and focused only on the time of day derivatives.

static member RunKNN inputs outputs input =
    let distanceFunction (a:int,b:int,c:int,d:int) (e:int,f:int,g:int,h:int) =  
      let b1 = b * 4
      let f1 = f * 4
      let d1 = d * 2
      let h1 = h * 2
      float((pown(a-e) 2) + (pown(b1-f1) 2) + (pown(c-g) 2) + (pown(d1-h1) 2))
 
    let distanceDelegate = 
          System.Func<(int * int * int * int),(int * int * int * int),float>(distanceFunction)
    
    let knn = new KNearestNeighbors<int*int*int*int>(10,2,inputs,outputs,distanceDelegate)
    knn.Compute(input)

You will notice I tried to normalize the values so that they all had the same basis. They are not exact, but they are close. You will also notice that I had to create a delegate from for the distanceFunction (thanks to Mimo on SO). This is because Accord.NET was written in C# with C# consumers in mind and F# has a couple of places where the interfaces are not as seemless as one would hope.

In any event, once the KKN function was written, I wrote a function that to the validation sample, made a guess via KKN, and then reported the result:

static member GetValidationsViaKKN  =
    let inputs = MachineLearningEngine.TrainingInputClass
    let outputs = MachineLearningEngine.TrainingOutputClass
    let validations = MachineLearningEngine.ValidationClass
 
    validations
        |> Seq.map(fun (a,b,c,d,e) -> e, MachineLearningEngine.RunKNN inputs outputs (a,b,c,d))
        |> Seq.toList
 
static member GetSuccessPercentageOfValidations =
    let validations = MachineLearningEngine.GetValidationsViaKKN
    let matches = validations
                    |> Seq.map(fun (a,b) -> match (a=b) with
                                                | true -> 1
                                                | false -> 0)
 
    let recordCount =  validations |> Seq.length
    let numberCorrect = matches |> Seq.sum
    let successPercentage = double(numberCorrect) / double(recordCount)
    recordCount, numberCorrect, successPercentage

I then hopped over to my UI console app and looked that the success percentage.

private static void GetSuccessPercentageOfValidations()
{
    var output = MachineLearningEngine.GetSuccessPercentageOfValidations;
    Console.WriteLine(output.Item1.ToString() + ":" + output.Item2.ToString() + ":" + output.Item3.ToString());
}

So there are 12,837 records in the validation sample and the classifier guessed the correct disposition 9,001 times – a success percentage of 70%

So it looks like there is something there. However, it is not clear that this is a good classifier without further tests – specifically seeing if the how to most common case results when pushing though the classifier. Also, I would assume to make this a true ‘machine learning’ algorithm I would have to feed the results back to the distance function to see if I can alter it to get the success percentage higher.

One quick note about methodology – I used unit tests pretty extensively to understand how the KKN works. I created a series of tests with some sample data to see who the function reacted.

[TestMethod]
public void TestKKN_ReturnsExpected()
{
 
    Tuple<int, int, int, int>[] inputs = { 
        new Tuple<int, int, int, int>(1, 0, 15, 1), 
        new Tuple<int,int,int,int>(1,0,11,1)};
    int[] outputs = { 1, 1 };
 
    var input = new Tuple<int, int, int, int>(1, 1, 1, 1);
 
    var output = MachineLearningEngine.RunKNN(inputs, outputs, input);
 
}

This was a big help to get me up and running (walking, really..)…

Filed under Analytics, F#

Traffic Stop Analysis Using F#

January 7, 2014 1 Comment

Now that I have the traffic stop services up and running, it is time to actually do something with the data. The data set is all traffic stops in my town for 2012 with some limited information: date/time of the stop, the geolocation of the stop, and the final disposition of the stop. The data looks like this:

My 1st step was to look at the Date/Time and see if there are any patterns in DayOfMonth, MonthOfYear, And TimeOfDay. To that end, I spun up a F# project and added my 1st method that determines the total number of records in the dataset:

type roadAlert = JsonProvider<"http://chickensoftware.com/roadalert/api/trafficstopsearch/Sample&quot;>
type AnalysisEngine =
    static member RoadAlertDoc = roadAlert.Load("http://chickensoftware.com/roadalert/api/trafficstopsearch&quot;)
 
    static member NumberOfRecords =
        AnalysisEngine.RoadAlertDoc 
            |> Seq.length

Since I am a TDDer more than a REPLer, I went and wrote a covering unit test.

[TestMethod]
public void NumberOfRecords_ReturnsExpected()
{
    Int32 notEpected = 0;
    Int32 actual = AnalysisEngine.NumberOfRecords;
    Assert.AreNotEqual(notEpected, actual);
}

A couple of things to note about this:

1) This is really an integration test, not a unit test. I could have written the test like this:

[TestMethod]
public void NumberOfRecordsFor2012DataSet_ReturnsExpected()
{
    Int32 expected = 27778;
    Int32 actual = AnalysisEngine.NumberOfRecords;
    Assert.AreEqual(expected, actual);
}

But that means I am tying the test to the specific data sample (in its current state) – and I don’t want to do that.

2) I am finding that my F# code has many more functions than the code written by other people – esp data scientists. I think it has to do with contrasting methodologies. Instead of spending time in the REPL with a small piece of code to get it right and then adding the code into the larger code base, I am writing very small piece of code in the class and then using unit tests to get it right. The upshot of that is that there are lots of small, independently testable pieces of code – I think this stems from my background of writing production apps that are for business problems and not for academic papers. Also, I use classes in source files versus script files because I plan to plug the code into larger .NET applications that will be written in C# and/or VB.NET.

In any event, once I has the total number of records, I went to see how they broke down into month:

static member ActualTrafficStopsByMonth =
    AnalysisEngine.RoadAlertDoc
        |> Seq.map(fun x -> x.StopDateTime.Month)
        |> Seq.countBy(fun x-> x)
        |> Seq.toList

[TestMethod]
public void ActualTrafficStopsByMonth_ReturnsExpected()
{
    Int32 notExpected = 0;
    var stops = AnalysisEngine.ActualTrafficStopsByMonth;
    Assert.AreNotEqual(notExpected, stops.Length);
 
}

I then created a function that shows the expected number of stops by month. Pattern matching with F# makes creating the month list a snap. Note that is is a true unit test because I am not dependent on external data:

static member Months =
    let monthList = [1..12]
    Seq.map (fun x -> 
            match x with
                | 1 | 3 | 5 | 7 | 8 | 10 | 12 -> x,31,31./365.
                | 2 -> x,28,28./365.
                | 4 | 6 | 9 | 11 -> x,30, 30./365.
                | _ -> x,0,0.                    
        ) monthList
    |> Seq.toList   

static member ExpectedTrafficStopsByMonth numberOfStops =
    AnalysisEngine.Months
        |> Seq.map(fun (x,y,z) -> 
            x, int(z*numberOfStops))
        |> Seq.toList

[TestMethod]
public void ExpectedTrafficStopsByMonth_ReturnsExpected()
{
    var stops = AnalysisEngine.ExpectedTrafficStopsByMonth(27778);
    double expected = 2359;
    double actual =stops[0].Item2;
 
    Assert.AreEqual(expected, actual);
}

With the actual and expected ready to go, I then put the two side by side:

static member TrafficStopsByMonth =
    let numberOfStops = float(AnalysisEngine.NumberOfRecords)
    let monthlyExpected = AnalysisEngine.ExpectedTrafficStopsByMonth numberOfStops
    let monthlyActual = AnalysisEngine.ActualTrafficStopsByMonth
    Seq.zip monthlyExpected monthlyActual
        |> Seq.map(fun (x,y) -> fst x, snd x, snd y, snd y – snd x, (float(snd y) – float(snd x))/float(snd x))
        |> Seq.toList

[TestMethod]
public void TrafficStopsByMonth_ReturnsExpected()
{
    var output = AnalysisEngine.TrafficStopsByMonth;
    Assert.IsNotNull(output);
 
}

All of my unit tests ran green

so now I am ready to roll. I created a quick console UI

static void Main(string[] args)
{
    Console.WriteLine("Start");
 
    foreach (var tuple in AnalysisEngine.TrafficStopsByMonth)
    {
        Console.WriteLine(tuple.Item1 + ":" + tuple.Item2 + ":" + tuple.Item3 + ":" + tuple.Item4 + ":" + tuple.Item5);
    }
 
    Console.WriteLine("End");
    Console.ReadKey();
}

With the output. Obviously, a UX person could put some real pizzaz front of this data, but that is something to do another day. If you didn’t see it in the code above, the tuple is constructed as: Month,ExpectedStops,ActualStops,Difference,%Difference. So the real interesting thing is that September was 47% higher than expected with December 26% less. That kind of wide variation begs for more analysis.

I then did a similar analysis by DayOfMonth:

static member ActualTrafficStopsByDay = 
    AnalysisEngine.RoadAlertDoc
        |> Seq.map(fun x -> x.StopDateTime.Day)
        |> Seq.countBy(fun x-> x)
        |> Seq.toList
 
static member Days =
    let dayList = [1..31]
    Seq.map (fun x -> 
            match x with
                | x when x < 29 -> x, 12, 12./365.
                | 29 | 30 -> x, 11, 11./365.
                | 31 -> x, 7, 7./365.
                | _ -> x, 0, 0.                 
        ) dayList
    |> Seq.toList     
 
static member ExpectedTrafficStopsByDay numberOfStops =
    AnalysisEngine.Days
        |> Seq.map(fun (x,y,z) -> 
            x, int(z*numberOfStops))
        |> Seq.toList    
 
static member TrafficStopsByDay =
    let numberOfStops = float(AnalysisEngine.NumberOfRecords)
    let dailyExpected = AnalysisEngine.ExpectedTrafficStopsByDay numberOfStops
    let dailyActual = AnalysisEngine.ActualTrafficStopsByDay
    Seq.zip dailyExpected dailyActual
        |> Seq.map(fun (x,y) -> fst x, snd x, snd y, snd y – snd x, (float(snd y) – float(snd x))/float(snd x))
        |> Seq.toList

The interesting thing is that there are higher than expected traffic stops in the last half of the month (esp the 25th and 26th) and much lower in the 1st part of the month.

And by TimeOfDay

static member ActualTrafficStopsByHour = 
    AnalysisEngine.RoadAlertDoc
        |> Seq.map(fun x -> x.StopDateTime.Hour)
        |> Seq.countBy(fun x-> x)
        |> Seq.toList
 
static member Hours =
    let hourList = [1..24]
    Seq.map (fun x -> 
                x,1, 1./24.
        ) hourList
    |> Seq.toList     
 
static member ExpectedTrafficStopsByHour numberOfStops =
    AnalysisEngine.Hours
        |> Seq.map(fun (x,y,z) -> 
            x, int(z*numberOfStops))
        |> Seq.toList    
 
static member TrafficStopsByHour =
    let numberOfStops = float(AnalysisEngine.NumberOfRecords)
    let hourlyExpected = AnalysisEngine.ExpectedTrafficStopsByHour numberOfStops
    let hourlyActual = AnalysisEngine.ActualTrafficStopsByHour
    Seq.zip hourlyExpected hourlyActual
        |> Seq.map(fun (x,y) -> fst x, snd x, snd y, snd y – snd x, (float(snd y) – float(snd x))/float(snd x))
        |> Seq.toList

The interesting thing here is that there are much higher than expected number of traffic stops from 1-2 AM (61% and 123%) with significantly less between 8PM and midnight. Finally, I looked at GPS location for the stops.

static member ActualTrafficStopsByGPS =  
    AnalysisEngine.RoadAlertDoc
        |> Seq.map(fun x -> System.Math.Round(x.Latitude,3).ToString() + ":" + System.Math.Round(x.Longitude,3).ToString())
        |> Seq.countBy(fun x-> x)
        |> Seq.sortBy snd
        |> Seq.toList
        |> List.rev
 
static member GetVarianceOfTrafficStopsByGPS =
    let trafficStopList = AnalysisEngine.ActualTrafficStopsByGPS
                            |> Seq.map(fun x -> double(snd x))
                            |> Seq.toList
    AnalysisEngine.Variance(trafficStopList)
 
static member GetAverageOfTrafficStopsByGPS =
    AnalysisEngine.ActualTrafficStopsByGPS
        |> Seq.map(fun x -> double(snd x))
        |> Seq.average

You can see that I rounded the Latitude and Longitude to 3 decimal places. Using Wikipedia, saying that 4 decimals at 23N is 10.24M and 45N it is 7.87M for latitude, I imputed that 35 is 8.94M. With 1 M = 3.28 feet, that means that 4 decimals is with 30 feet and 3 decimals is within 300 feet and 2 decimals is within 3,000 feet. 300 feet seems like a good compromise so I ran with that.

So running the average and variance and the top GPS locations:

With an average of 11 stops per GPS location (less than 1 a month) and a variance of 725, there does not seem be a strong relationship between GPS location and traffic stops.

The upshot of all of this analysis seems to point to avoid getting stopped it is less important where you are than when you are. This is confirmed anecdotally too – the Town actually broadcasts when they will have heightened traffic surveillance on Twitter and the like. Ignore open data at your own risk.

In any event, I my next step is to run this data though a machine-learning algorithm to see if there is anything else to uncover.

Filed under Analytics, F#

Setting up an OData Service on WebAPI2 to be used by F# Type Providers

January 7, 2014 1 Comment

I am prepping for the F#/Data Analytics workshop on January 8th and wanted to get the data that I used for the Road Alert application beck to better shape. By better, I mean out of that crusty WCF SOAP that I have had it in for the last 2 years. To that end, I jumped over to Mike Wasson’s Creating an OData tutorial. All in all, it is a good step by step guide, but I had to make some changes to get it working for me.

Change #1 is that I am not using a local database, I am using a database located on WinHost. Therefore, I had to swap out the EF connection string.

One of the things I can appreciate about the template is the comments to get the routing set up (I guess there is not attribute-based routing for O-Data?)

        /*
To add a route for this controller, merge these statements into the Register method of the WebApiConfig class. Note that OData URLs are case sensitive.
 
using System.Web.Http.OData.Builder;
using ChkickenSoftware.RoadAlertServices.Models;
ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet<TrafficStop>("TrafficStop");
config.Routes.MapODataRoute("odata", "odata", builder.GetEdmModel());
*/

Things were looking good when I took a departure from the tutorial and added in a couple of unit tests (Change #2). The 1st one was fairly benign:

[TestClass]
public class TrafficStopControllerIntegrationTests
{
    [TestMethod]
    public void GetTrafficStopUsingKey_ReturnsExpected()
    {
        TrafficStopController controller = new TrafficStopController();
        var trafficStop = controller.GetTrafficStop(1);
        Assert.IsNotNull(trafficStop);
    }
}

Note that I had to add an app.config to the test project b/c this is an integration test and I am making a real database call – a unit test would using a mocking framework. In any event, when I went to run the test, I got a compile error – I needed to add a reference to System.Web.Http.OData to resolve the return value from the controller. Not big thing, though I wish I could install packages from Nuget via their .dll name and not just their package name:

In any event, I then ran the test and I got this exception:

So this is another reason why EF drives me nuts. I have to add a reference to Entity Framework (and throw some crap in the .config file)

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  <providers>
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  </providers>
</entityFramework>

– even thought the calling application has nothing to do with EF. In 2014, we have such dependency drip? Really? In any event, once I added a reference to EF and updated the .config file, my unit/integration test ran green so I was on the right track.

I then went to fiddler and tried to call the controller:

Yikes, it looks like my model has to match the EF exactly

The database:

And the model:

public class TrafficStop
{
    public Int32 Id { get; set; }
    public double CadCallId { get; set; }
    public DateTime StopDateTime { get; set; }
    public Int32 DispositionId { get; set; }
    public String DispositionDesc { get; set; }
    public double Latitude { get; set; }
    public double Longitude { get; set; }
}

– I assume that I should be able to override this behavior – another thing to research.

So after matching up field names, I ran fiddler and sure enough:

So that was pretty painless to get an OData Service up and running. I then removed everything but the read methods and I added an auth header (you can see the value in the screen shot above), feel free to hit up the service now that it is deployed to WinHost:

One of the coolest things about OData is that it has a .WSDL type discovery:

http://chickensoftware.com/RoadAlert/odata/$metadata

I was really missing that when we went from SOAP Services to REST

Note that I had to do a couple of more things in Tsql (remember that?) to the original data to get it ready for general consumption (and analytics). I had to create a real date/time from the 2 varchar fields:

Update [XXXX].[dbo].[TrafficStops]
Set StopDateTime = Convert(DateTime, right (left([Date],6),2) + ‘/’ + right([Date],2) + ‘/’ + left([Date],4) + ‘ ‘ + left(Time,2) + ‘:’ + Right(left(Time,4),2) + ‘:’ + Right(left(Time,6),2))

I also had to add an integral value for when we do statistical analysis:

Update [XXXXX].[dbo].[TrafficStops]
Set dispositionId =
CASE
     WHEN dispositionDesc = ‘FURTHER ACTION NECESSARY’ THEN 1
     WHEN dispositionDesc = ‘UNABLE TO LOCATE’ THEN 2
     WHEN dispositionDesc = ‘FALSE ALARM’ THEN 3
     WHEN dispositionDesc = ‘WRITTEN WARNING’ THEN 4
     WHEN dispositionDesc = ‘OTHER    SEE NOTES’ THEN 5
     WHEN dispositionDesc = ‘REFERRED TO PROPER AGENCY’ THEN 6
     WHEN dispositionDesc = ‘VERBAL WARNING’ THEN 7
     WHEN dispositionDesc = ‘NULL’ THEN 8
     WHEN dispositionDesc = ‘ARREST’ THEN 9
     WHEN dispositionDesc = ‘NO FURTHER ACTION NECESSARY’ THEN 10
     WHEN dispositionDesc = ‘CIVIL PROBLEM’ THEN 11
     WHEN dispositionDesc = ‘COMPLETED AS REQUESTED’ THEN 12
     WHEN dispositionDesc = ‘INCIDENT REPORT’ THEN 13
     WHEN dispositionDesc = ‘UNFOUNDED’ THEN 14
     WHEN dispositionDesc = ‘CITATION’ THEN 15
     WHEN dispositionDesc = ‘FIELD CONTACT’ THEN 16
     WHEN dispositionDesc = ‘BACK UP UNIT’ THEN 17
     WHEN dispositionDesc = ‘CITY ORDINANCE VIOLATION’ THEN 18
END

So now I am ready to roll with doing the analytics.

So when I say “ready to roll”, I really meant to say “ready to flail.” When we last left the show, I was ready to start consuming the data from OData using the F# type providers. Using Fiddler, I can see the data coming out of the OData service

The problem started when I went to consume the data using the F# OData Type Provider as documented here. I got the red squiggly line of approbation when I went to create the type:

with the following message:

Error 1 The type provider ‘Microsoft.FSharp.Data.TypeProviders.DesignTime.DataProviders’ reported an error: error 7001: The element ‘DataService’ has an attribute ‘DataServiceVersion’ with an unrecognized version ‘3.0’.

I went over to the F#-open source Google group to seek help and Isaac Abraham had this response:

WebAPI 2 now pushes out OData 3 endpoints by default, which are actually not even backwards compatible with the OData 2 standard. OData 3 was (AFAIK) released some time after the OData Type Provider was written, so I suspect it doesn’t support OData 3.

So I am stuck. I really want to use type providers but they are behind. I thought about if I could downgrade my WebAPI2 OData to go to OData2 standard (whatever that is).

My 1st thought was to trick out the client by removing the DataServiceVersion header like so:

public class HeadersHandler : DelegatingHandler
{
async protected override Task<HttpResponseMessage> SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
{
HttpResponseMessage response = await base.SendAsync(request, cancellationToken);
response.Content.Headers.Remove("DataServiceVersion");
return response;
}
}

The header was removed, but alas, the RSLA is still with me with the same message. I then thought, perhaps I can go back to the old version of Json so I modified the header like so:

public class HeadersHandler : DelegatingHandler
{
async protected override Task<HttpResponseMessage> SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
{
request.Headers.Add("Accept", "application/json;odata=verbose");
HttpResponseMessage response = await base.SendAsync(request, cancellationToken);
response.Content.Headers.Remove("DataServiceVersion");
return response;
}
}

So the Json is now the “old” version, but I am still getting the RSLA. I then ran fiddler when creating the type provider and I see this:

Crap. I need to have Entity Framework use a lower version (I am using EF 6.0). I guess? My 1st thought was to remove EF from the situation entirely, which is always a good idea. My next, and more time-efficient, thought was to ask Stack Overflow – which is what I did here. While I wait for Stack Overflow to come to the rescue. I decided to press on. I just exposed the data via a normal controller like so:

public class TrafficStopSearchController : ApiController
{
public List<TrafficStop> Get()
{
DataContext context = new DataContext();
return context.TrafficStops.ToList<TrafficStop>();
}
public TrafficStop Get(int id)
{
DataContext context = new DataContext();
return context.TrafficStops.Where(ts => ts.Id == id).FirstOrDefault();
}
[HttpGet]
[Route("api/TrafficStopSearch/Sample/")]
public List<TrafficStop> Sample()
{
DataContext context = new DataContext();
return context.TrafficStops.Where(ts => ts.Id < 100).ToList();
}
}

The reason I threw in the Sample method is that the F# JSON type provider uses a sample to infer types and I didn’t want to send the entire set of data across the wire for that. Once that was done, the traffic stop data was consumable in my F# application like so:

type roadAlert = JsonProvider<"http://chickensoftware.com/roadalert/api/trafficstopsearch/Sample">
type AnalysisEngine =
static member RoadAlertDoc = roadAlert.Load("http://chickensoftware.com/roadalert/api/trafficstopsearch")

Once/if I get the OData set up, I will swap this out but this is good enough for now – after all the interesting piece is not getting the data – but doing something with it!

Filed under F#, OData, WebApi

Jamie Dixon's Home

Basic Insert Operation Using F#

Traffic Stop Visualization Using D3

Screen Scraping The Department Of Health

Traffic Stop Disposition: Classification Using F# and KNN

Traffic Stop Analysis Using F#

Setting up an OData Service on WebAPI2 to be used by F# Type Providers

Categories

Recent Posts

Archives

Blogroll

Meta