F# | Jamie Dixon's Home

F# and the Open/Closed Principle

April 15, 2014 2 Comments

One of the advantages of using F# is that it is a .NET language. Although F# is a functional-first language, it also supports object-oriented constructs. One of the most powerful (indeed, the most powerful) technique in OO programming is using interfaces to follow the Open/Closed principle. If you are not familiar, a good explanation of Open/Closed principle is found here.

As part of the F# for beginners dojo I am putting on next week, we are consuming and then analyzing Twitter. The problem with always making calls to Twitter is that

1) The data changes every call

2) You might get throttled

Therefore, it makes good sense to have an in-memory representation of the data for testing and some Twitter data on disk so that different experiments can be run on the same data to see the result. Using Interfaces in F# makes this a snap.

First, I created an interface:

namespace NewCo.TwitterAnalysis
 
open System
open System.Collections.Generic
 
type ITweeetProvider =
   abstract member GetTweets : string -> IEnumerable<DateTime * int * string>

Next, I created the actual Twitter feed. Note I am using TweetInvi (available on Nuget) and that this file has to be below the interface in the solution explorer:

namespace NewCo.TwitterAnalysis
 
open System
open System.Configuration
open Tweetinvi
 
type TwitterProvider() = 
    interface ITweeetProvider with 
        member this.GetTweets(stockSymbol: string) =
            let consumerKey = ConfigurationManager.AppSettings.["consumerKey"]
            let consumerSecret = ConfigurationManager.AppSettings.["consumerSecret"]
            let accessToken = ConfigurationManager.AppSettings.["accessToken"]
            let accessTokenSecret = ConfigurationManager.AppSettings.["accessTokenSecret"]
        
            TwitterCredentials.SetCredentials(accessToken, accessTokenSecret, consumerKey, consumerSecret)
            let tweets = Search.SearchTweets(stockSymbol);
            tweets 
                |> Seq.map(fun t -> t.CreatedAt, t.RetweetCount, t.Text)

I then hooked up a unit (integration, really) test

[TestClass]
public class UnitTest1
{
    [TestMethod]
    public void GetTweetsUsingIBM_returnsExpectedValue()
    {
        ITweeetProvider provider = new TwitterProvider();
        var actual = provider.GetTweets("IBM");
        Assert.IsNotNull(actual);
    }
}

Sure enough, it ran green with actual Twitter data coming back:

I then created an In-Memory Tweet provider that can be used to:

1) Provide repeatable results

2) Have 0 external dependencies so that I can monkey with the code and a red unit test really does mean red

Here is its implementation:

namespace NewCo.TwitterAnalysis
 
open System
open System.Collections.Generic
 
type InMemoryProvider() = 
    interface ITweeetProvider with 
        member this.GetTweets(stockSymbol: string) =
            let list = new List<(DateTime*int*string)>()
            list.Add(DateTime.Now, 1,"Test1")
            list.Add(DateTime.Now, 0,"Test2")
            list :> IEnumerable<(DateTime*int*string)>

The only really interesting thing is the smiley/bird character (: >). F# implements interfaces a bit differently than what I was used to –> F# implements interfaces explicitly. I then fired up a true unit test and it also ran green:

[TestClass]
public class InMemoryProviderTests
{
    [TestMethod]
    public void GetTweetsUsingValidInput_ReturnsExpectedValue()
    {
        ITweeetProvider provider = new InMemoryProvider();
        var tweets = provider.GetTweets("TEST");
        var tweetList = tweets.ToList();
        Int32 expected = 2;
        Int32 actual = tweetList.Count;
        Assert.AreEqual(expected, actual);
    }
}

Finally, I created a file-system bound provider so that I can download and then hold static a large dataset. Based on past experience dealing with on-line data sources, getting data local to run multiple tests against is generally a good idea. Here is the implementation:

namespace NewCo.TwitterAnalysis
 
open System
open System.Collections.Generic
open System.IO
 
type FileSystemProvider(filePath: string) = 
    interface ITweeetProvider with 
        member this.GetTweets(stockSymbol: string) =
            let fileContents = File.ReadLines(filePath)
                                |> Seq.map(fun line -> line.Split([|'\t'|]))
                                |> Seq.map(fun values -> DateTime.Parse(values.[0]),int values.[1], string values.[2])
            fileContents

And the covering unit (integration really) tests look like this:

[TestClass]
public class FileSystemProviderTests
{
    [TestMethod]
    public void GetTweetsUsingValidInput_ReturnsExpectedValue()
    {
        var baseDir = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
        var testFile = Path.Combine(baseDir, "TweetData.csv");
        ITweeetProvider provider = new FileSystemProvider(testFile);
        var tweets = provider.GetTweets("TEST");
        var tweetList = tweets.ToList();
        Int32 expected = 2;
        Int32 actual = tweetList.Count;
        Assert.AreEqual(expected, actual);
    }
}

Note that I had to add the actual file in the test project.

Finally, the F# code needs to include try..catches for the external calls (web service and disk) and some argument validation for the strings come in.

In any event, I now have 3 different implementations that I can swap out depending on my needs. I love having the power of Interfaces combined with benefits of using a functional-first language.

Filed under F#, SOLID

Consuming Twitter With F#

April 8, 2014 7 Comments

I set up a meetup for TRINUG’s F#/data analytics SIG to center around consuming and analyzing Tweets. Since Twitter is just JSON, I assumed it would be easy enough to search Tweets for a given subjects in a given time period. How wrong I was. I spent several hours research different ways to consume Twitter to varying degrees of success. My 1st stop was to investigate some of the more common libraries that C# developers use to consume Twitter. Here is my survey of some of the more popular ones:

Twitterizer: No longer maintained

// Install-Package twitterizer -Version 2.4.2
// Update-Package Newtonsoft.Json -Reinstall
 open Twitterizer
 
 type public TwitterProvider() =
    member this.GetTweetsForDateRange(ticker:string, startDate: DateTime, endDate: DateTime) =
        let consumerKey = ConfigurationManager.AppSettings.["consumerKey"]
        let consumerSecret = ConfigurationManager.AppSettings.["consumerSecret"]
        let accessToken = ConfigurationManager.AppSettings.["accessToken"]
        let accessTokenSecret = ConfigurationManager.AppSettings.["accessTokenSecret"]
        
        let tokens = new OAuthTokens()
        tokens.set_ConsumerKey(consumerKey)
        tokens.set_ConsumerSecret(consumerSecret)
        tokens.set_AccessToken(accessToken)
        tokens.set_AccessTokenSecret(accessTokenSecret)
 
        let searchOptions = new SearchOptions()
        searchOptions.SinceDate <- startDate
        searchOptions.UntilDate <- endDate
        let results = TwitterSearch.Search(tokens, ticker,searchOptions)
        results.ResponseObject
                    |> Seq.map(fun r -> r.CreatedDate, r.Text)

TweetSharp: No longer maintained

open TweetSharp
 
 type public TwitterProvider() =
    member this.GetTweetsForDateRange(ticker:string, startDate: DateTime, endDate: DateTime) =
        let consumerKey = ConfigurationManager.AppSettings.["consumerKey"]
        let consumerSecret = ConfigurationManager.AppSettings.["consumerSecret"]
        let accessToken = ConfigurationManager.AppSettings.["accessToken"]
        let accessTokenSecret = ConfigurationManager.AppSettings.["accessTokenSecret"]
        
        let service = new TwitterService(consumerKey, consumerSecret)
        service.AuthenticateWith(accessToken, accessTokenSecret)
 
        let searchOptions = new SearchOptions()
        searchOptions.Q <- "IBM%20since%3A2014-03-01&src=typd"
        service.Search(searchOptions).Statuses
                                        |> Seq.map(fun s -> s.CreatedDate, s.Text)

Note that I did try and add a date range the way the Twitter API instructs, but it still came back with only 20 tweets.

LinqToTwitter: Active but nave to use Linq syntax. Ugh!

Twitterinvi: Active but does not have date range functionality

open System
open System.Configuration
open Tweetinvi
 
type public TwitterProvider() = 
    member this.GetTodaysTweets(ticker: string) = 
        let consumerKey = ConfigurationManager.AppSettings.["consumerKey"]
        let consumerSecret = ConfigurationManager.AppSettings.["consumerSecret"]
        let accessToken = ConfigurationManager.AppSettings.["accessToken"]
        let accessTokenSecret = ConfigurationManager.AppSettings.["accessTokenSecret"]
 
        TwitterCredentials.SetCredentials(accessToken, accessTokenSecret, consumerKey, consumerSecret)
        let tweets = Search.SearchTweets(ticker);
        tweets |> Seq.map(fun t -> t.CreatedAt, t.RetweetCount)
 
    member this.GetTweetsForDateRange(ticker: string, startDate: DateTime)=
        let consumerKey = ConfigurationManager.AppSettings.["consumerKey"]
        let consumerSecret = ConfigurationManager.AppSettings.["consumerSecret"]
        let accessToken = ConfigurationManager.AppSettings.["accessToken"]
        let accessTokenSecret = ConfigurationManager.AppSettings.["accessTokenSecret"]
 
        TwitterCredentials.SetCredentials(accessToken, accessTokenSecret, consumerKey, consumerSecret)
        let searchParameter = Search.GenerateSearchTweetParameter(ticker)
        searchParameter.Until <- startDate;
        let tweets = Search.SearchTweets(searchParameter);
        tweets |> Seq.map(fun t -> t.CreatedAt, t.RetweetCount)

So without an out of the box API to use, I thought about using a Json Type Provider the way Lincoln Atkinson did. The problem is that is example is for V1 of Twitter and V 1.1 uses Oauth. If you run his code, you get

I then thought about a 3rd party API that captures Tweets. I ran across gnip ($500!) and Topsy (no longer accepting new licenses b/c Apple bought them) so I am back to square one.

So finally I thought about rolling my own (with OAuth being the hard part) but I am quickly running out of time to get ready for the SIG and I don’t want to spend the time on only this part.

Why isn’t there a Twitter type provider? I’ll add it to the list….

Filed under F#, Twitter

Apriori Algorithm and F# Using Elevator Inspection Data

March 18, 2014 1 Comment

Now that I have the elevator dataset in a workable state, I wanted to see what I could see with the data. I was reading Machine Learning In Action and the authors suggested that an Apriori Algorithm as a way to quantify associations among data points. I read both Harrington’s code and Wikipedia’s description and I found both the be impenetrable – the former because their code was unreadable and the later because the mathematical formulas depended on a level of algebra that I don’t have.

Fortunately, I found a C# project on Codeproject that had both an excellent example/introduction and C# code. I used the examples on the website to formulate my F# implementation.

The first thing I did was create a class that matched the 1st grid in the example

namespace ChickenSoftware.ElevatorChicken.Analysis
 
open System.Collections.Generic
 
type Transaction = {TID: string; Items: List<string> }
 
type Apriori(database: List<Transaction>, support: float, confidence: float) = 
    member this.Database = database
    member this.Support = support
    member this.Confidence = confidence

Note that because F# is immutable by default, the properties are read-only. I then created a unit test project that makes sure the constructor works without exceptions. The data matches the example:

public AprioriTests()
{
    var database = new List<Transaction>();
    database.Add(new Transaction("100", new List<string>() { "A", "C", "D" }));
    database.Add(new Transaction("200", new List<string>() { "B", "C", "E" }));
    database.Add(new Transaction("300", new List<string>() { "A", "B", "C", "E" }));
    database.Add(new Transaction("400", new List<string>() { "B", "E" }));
 
    _apriori = new Apriori(database, .5, .80);
 
}
 
[TestMethod]
public void ConstructorUsingValidArguments_ReturnsExpected()
{
    Assert.IsNotNull(_apriori);
}

I then need a function to count up all of the items in the Itemsets. I refused to use loops, so I first started using Seq.Fold, but I was having zero luck because I was trying to fold a Seq of List. I then started experimenting with other functions when I found Seq.Collect – which was perfect. So I created a function like this:

member this.GetC1() =
    database
 
member this.GetL1() =
    let numberOfTransactions = this.GetC1().Count
 
    this.GetC1()
        |> Seq.collect(fun d -> d.Items)
        |> Seq.countBy(fun i -> i)
        |> Seq.map(fun (t,i) -> t, i, float i/ float numberOfTransactions)
        |> Seq.filter(fun (t,i,p) -> p >= support)
        |> Seq.map(fun (t,i,p) -> t,i)
        |> Seq.sort
        |> Seq.toList

Note that the numberOfTransactions is for the database, not the individual items in the List<Item>. And the results match the example:

So this is great. My next stop was to build a list of pair combinations of the remaining values

The trick is that is not a Cartesian join of the original sets – it is only the surviving sets that are needed. My first attempt looked like:

let C1 = database
 
let L1 = C1
        |> Seq.map(fun t -> t.Items)
        |> Seq.collect(fun i -> i)
        |> Seq.countBy(fun i -> i)
        |> Seq.map(fun (t,i) -> t, i, float i/ float numberOftransactions)
        |> Seq.filter(fun (t,i,p) -> p >= support)
        |> Seq.toArray
let C2A = L1 
            |> Seq.map(fun (x,y,z) -> x)
            |> Seq.toArray
let C2B = L1 
            |> Seq.map(fun (x,y,z) -> x)
            |> Seq.toArray
let C2 = C2A |> Seq.collect(fun x -> C2B |> Seq.map(fun y -> x+y))
C2   

With the output like this:

I was running out of Saturday morning so I went over to stack overflow and got a couple of responses. I was on the right track with the concat, but I didn’t think about the List.Filter(), which would prune my list. With this in mind, I copied Mark’s code and got what I was looking for

member this.GetC2() =
    let l1Itemset = this.GetL1() 
                    |> Seq.map(fun (i,s) -> i)
 
    let itemset = 
        l1Itemset
            |> Seq.map(fun x -> l1Itemset |> Seq.map(fun y -> (x,y)))
            |> Seq.concat
            |> Seq.filter(fun (x,y) -> x < y)
            |> Seq.sort
            |> Seq.toList         
    
    let listContainsItem(l:List<string>, a,b) =
            l.Contains(a) && l.Contains(b)
    
    let someFunctionINeedToRename(l1:List<string>, l2)=
            l2 |> Seq.map(fun (x,y) -> listContainsItem(l1,x,y))
 
    let itemsetMatches = this.GetC1()
                            |> Seq.map(fun t -> t.Items)
                            |> Seq.map(fun i -> someFunctionINeedToRename(i,itemset))
 
    let itemSupport = itemsetMatches
                            |> Seq.map(Seq.map(fun i -> if i then 1 else 0))
                            |> Seq.reduce(Seq.map2(+))
 
    itemSupport
        |> Seq.zip(itemset)
        |> Seq.toList

So now I have C2 filling correctly:

Taking the results, I needed to get L2.

That was much simpler that getting C2 –> here is the code:

member this.GetL2() = 
    let numberOfTransactions = this.GetC1().Count
    
    this.GetC2()
            |> Seq.map(fun (i,n) -> i,n,float n/float numberOfTransactions)
            |> Seq.filter(fun (i,n,p) -> p >= support)
            |> Seq.map(fun (t,i,p) -> t,i)
            |> Seq.sort
            |> Seq.toList    

And when I run it – it matches this example exactly:

Finally, I added in a C# and L3. This code is identical to the C2/L2 code with one exception: mapping a triple and not a tuple: The C2 code maps like this

let itemset = 
    l1Itemset
        |> Seq.map(fun x -> l1Itemset |> Seq.map(fun y -> (x,y)))
        |> Seq.concat
        |> Seq.filter(fun (x,y) -> x < y)
        |> Seq.sort
        |> Seq.toList     

and the C3 code looks like this (took me 15 minutes to figure out line 3 below):

let itemset = 
    l2Itemset
        |> Seq.map(fun x -> l2Itemset |> Seq.map(fun y-> l2Itemset |> Seq.map(fun z->(fst x,fst y,snd z))))
        |> Seq.concat
        |> Seq.collect(fun d -> d)
        |> Seq.filter(fun (x,y,z) -> x < y && y < z)
        |> Seq.distinct
        |> Seq.sort
        |> Seq.toList    

With the C3 and L3 matching the example also:

I was now ready to put in the elevator data into the analysis. I think I am getting better at F# because I did the mapping, filtering, and transformation of the data from the server without looking at any other material and it look only 15 minutes.

type public ElevatorBuilder() = 
    let connectionString = ConfigurationManager.ConnectionStrings.["localData2"].ConnectionString;
 
    member public this.GetElevatorTransactions() =
        let transactions = this.GetElevators() 
                              |> Seq.map(fun e ->this.ConvertElevatorToTransaction(e))
        let transactionsList = new System.Collections.Generic.List<Transaction>(transactions)
        transactionsList
 
    member public this.ConvertElevatorToTransaction(i: string, t:string, c:string, s:string) =
        let items = new System.Collections.Generic.List<String>()
        items.Add(t)
        items.Add(c)
        items.Add(s)
        let transaction = {TID=i; Items=items}
        transaction
 
    member public this.GetElevators () =
        SqlConnection.GetDataContext(connectionString).ElevatorData201402
            |> Seq.map(fun e -> e.ID, e.EquipType,e.Capacity,e.Speed)
            |> Seq.filter(fun (i,et,c,s) -> not(String.IsNullOrEmpty(et)))
            |> Seq.filter(fun (i,et,c,s) -> c.HasValue)
            |> Seq.filter(fun (i,et,c,s) -> s.HasValue)
            |> Seq.map(fun (i,t,c,s) -> i, this.CatagorizeEquipmentType(t),c,s)
            |> Seq.map(fun (i,t,c,s) -> i,t,this.CatagorizeCapacity(c.Value),s)
            |> Seq.map(fun (i,t,c,s) -> i,t,c,this.CatagorizeSpeed(s.Value))
            |> Seq.map(fun (i,t,c,s) -> i.ToString(),t,c,s)

The longest part was aggregating the free-form text of the Equipment Type field (here is partial snip, you get the idea…)

member public this.CatagorizeEquipmentType(et: string) =
    match et.Trim() with 
        | "OTIS" -> "OTIS"
        | "OTIS (1-2)" -> "OTIS"
        | "OTIS (2-1)" -> "OTIS"
        | "OTIS hydro" -> "OTIS"
        | "OTIS, HYD" -> "OTIS"
        | "OTIS/ ASHEVILLE " -> "OTIS"
        | "OTIS/ MOUNTAIN " -> "OTIS"
        | "OTIS/#1" -> "OTIS"
        | "OTIS/#19 " -> "OTIS"

Assigning categories for speed and capacity was a snap using F#

member public this.CatagorizeCapacity(c: int) =
    let lowerBound = (c/25 * 25) + 1
    let upperBound = lowerBound + 24
    lowerBound.ToString() + "-" + upperBound.ToString()        
 
member public this.CatagorizeSpeed(s: int) =
    let lowerBound = (s/50 * 50) + 1
    let upperBound = lowerBound + 49
    lowerBound.ToString() + "-" + upperBound.ToString()    

With this in hand, I created a Console app that takes the 27K records and pushes them though the apriori algorithm:

private static void RunElevatorAnalysis()
{
    Stopwatch stopwatch = new Stopwatch();
    stopwatch.Start();
    ElevatorBuilder builder = new ElevatorBuilder();
    var transactions = builder.GetElevatorTransactions();
    stopwatch.Stop();
    Console.WriteLine("Building " + transactions.Count + " transactions took: " + stopwatch.Elapsed.TotalSeconds);
    var apriori = new Apriori(transactions, .1, .75);
    var c2 = apriori.GetC2();
    stopwatch.Reset();
    stopwatch.Start();
    var l1 = apriori.GetL1();
    Console.WriteLine("Getting L1 took: " + stopwatch.Elapsed.TotalSeconds);
    var l2 = apriori.GetL2();
    Console.WriteLine("Getting L2 took: " + stopwatch.Elapsed.TotalSeconds);
    var l3 = apriori.GetL3();
    Console.WriteLine("Getting L3 took: " + stopwatch.Elapsed.TotalSeconds);
    stopwatch.Stop();
    Console.WriteLine("–L1");
    foreach (var t in l1)
    {
        Console.WriteLine(t.Item1 + ":" + t.Item2);
    }
    Console.WriteLine("–L2");
    foreach (var t in l2)
    {
        Console.WriteLine(t.Item1 + ":" + t.Item2);
    }
    Console.WriteLine("–L3");
    foreach (var t in l3)
    {
        Console.WriteLine(t.Item1 + ":" + t.Item2);
    }
}

I then made an offering to the F# Gods and hit F5:

Doh! The gods were not pleased. I then went back to my initial filtering function and added a Seq.Take(25000) and the results:

So there a couple of things to draw from this exercise.

1) Apriori Algorithm is the wrong classification technique for this dataset. I had to bring the support way down (10%) to even get any readings. Also, there is too much dispersion of the values. This kind of algorithm is much better with N number of a smaller set of data values versus a fixed number of large values.

2) Even so, how cool is this? Compare the files just to make the C#/OO work versus with F#

And the Total LOC is 539 for C# versus 120 for F# – and the F# can be optimized using a better way to create search and itemsets. Hard-coding each level was a hack I did to get thing working and give me an understanding of how AA works. I bet this can be consolidated to well under 75 lines without sacrificing readability

3) I think the StackOverflow exception is because I am doing a Cartesian join and then paring the result. Using one of the other techniques suggested on SO will give much better results.

I any event, what a fun project! I can’t wait to optimize this and perhaps throw a different algorithm at the dataset in the coming weeks.

Filed under Analytics, F#, Open Data

Elevator App: Part 1 – Data Layer Using F#

March 11, 2014 2 Comments

At Open Data Day, fellow TRINUGER Elaine Cahill told me about a website where you can get all of the elevator inspection data for the state. It is found here. She went ahead and put the Wake County data onto Socrata. I wanted to look at the entire state so I went to the report page like so:

Unfortunately, when you try and pull down the entire state, you cause a server exception:

So I split the download in half. I then Imported it into Access and then SSISed it into Azure Sql. I then created a project to server the data and I decided to use F# type providers as a replacement for Entity Framework for my ORM. I could either use the SqlEntity TP or the SqlDataConnection TP to access the Sql Database on Azure. Both do not work out of the box.

SqlDataConnection

I could not get SqlDataConnection to work at all. When I hooked it up to a standard connection string in the config file, I got:

So when I copy and paste the connection string into the TP directly, it does make the connection to Azure, but then it comes back with this exception:

Without looking at the source. my guess is that the TP has hard-coded a reference to ‘syscomments’ and alas, Azure does not have that table.

SqlEntity

I then headed over to the SlqEntityTP to see if I could have better luck. Fortunately, the SqlEntity does work with both an Azure connection string in the .config file and can make a connection to an Azure database.

The problem I ran into was when I wanted to expose the SqlConnection the the WebAPI project that I wrote in C#. You can not mark SqlEntityTPs as public:

Note that the SqlDataConnection can be marked as public. <sigh>. I marked the SqlEntityTP as internal and then created a POCO to map between the SqlEntity type and a type that can be consumed by the outside world:

type public Elevator ={
        ID: int
        County: string
        StateId: string
        Type: string
        Operation: string
        Owner: string
        O_Address1: string
        O_Address2: string
        O_City: string
        O_State: string
        O_Zip: string
        User: string
        U_Address1: string
        U_Address2: string
        U_City: string
        U_State: string
        U_Zip: string
        U_Lat: double
        U_Long: double
        Installed: DateTime
        Complied: DateTime
        Capacity: int
        CertStatus: int
        EquipType: string
        Drive: string
        Volts: string
        Speed: int
        FloorTo: string
        FloorFrom: string
        Landing: string
        Entrances: string
        Ropes: string
        RopeSize: string
    }
 
type public DataRepository() = 
    let connectionString = ConfigurationManager.ConnectionStrings.["azureData"].ConnectionString;
 
    member public this.GetElevators () =
        SqlConnection.GetDataContext(connectionString).ElevatorData201402
        |> Seq.map(fun x -> this.GetElevatorFromElevatorData(x))
 
    member public this.GetElevator (id: int) =
        SqlConnection.GetDataContext(connectionString).ElevatorData201402
        |> Seq.where(fun x -> x.ID = id)
        |> Seq.map(fun x -> this.GetElevatorFromElevatorData(x))
        |> Seq.head
 
    member internal this.GetElevatorFromElevatorData(elevatorData: SqlConnection.ServiceTypes.ElevatorData201402) =
        let elevator = {ID= elevatorData.ID;
            County=elevatorData.County;
            StateId=elevatorData.StateID;
            Type=elevatorData.Type;
            Operation=elevatorData.Operation;
            Owner=elevatorData.Owner;
            O_Address1=elevatorData.O_Address1;
            O_Address2=elevatorData.O_Address2;
            O_City=elevatorData.O_City;
            O_State=elevatorData.O_St;
            O_Zip=elevatorData.O_Zip;
            User=elevatorData.User;
            U_Address1=elevatorData.U_Address1;
            U_Address2=elevatorData.U_Address2;
            U_City=elevatorData.U_City;
            U_State=elevatorData.U_St;
            U_Zip=elevatorData.U_Zip;
            U_Lat=elevatorData.U_lat;
            U_Long=elevatorData.U_long;
            Installed=elevatorData.Installed.Value;
            Complied=elevatorData.Complied.Value;
            Capacity=elevatorData.Capacity.Value;
            CertStatus=elevatorData.CertStatus.Value;
            EquipType=elevatorData.EquipType;
            Drive=elevatorData.Drive;
            Volts=elevatorData.Volts;
            Speed=int elevatorData.Speed;
            FloorTo=elevatorData.FloorTo;
            FloorFrom=elevatorData.FloorFrom;
            Landing=elevatorData.Landing;
            Entrances=elevatorData.Entrances;
            Ropes=elevatorData.Ropes;
            RopeSize=elevatorData.RopeSize
        }
        elevator

I am not happy about writing any of this code. I have 84 lines of code for a single class. I might have well used the code code gen of EF. I could have taken the performance hit and used System.Reflection to map field of the same names (I have done that on other projects) , but that also feels like a hack. In any event, I then added a reference to my F# project in my C# WebAPI project. I did have to add a reference to FSharp.Core in the C# project (which further vexed me), but then I created a couple of GET methods to expose the data:

public class ElevatorController : ApiController
{
    // GET api/Elevator
    public IEnumerable<Elevator> Get()
    {
        DataRepository repository = new DataRepository();
        return repository.GetElevators();
    }
 
    // GET api/Elevator/5
    public Elevator Get(int id)
    {
        DataRepository repository = new DataRepository();
        return repository.GetElevator(id);
    }
 
}

When I viewed the JSON from a handy browser, it looks like, well, junk:

So now I have to get rid of that random characters (x0040 suffix)– yet a 3rd POCO, this one in C#:

public class ElevatorController : ApiController
{
    // GET api/Elevator
    public IEnumerable<CS.Elevator> Get()
    {
        List<CS.Elevator> elevators = new List<CS.Elevator>();
        FS.DataRepository repository = new FS.DataRepository();
        var fsElevators = repository.GetElevators();
        foreach (var fsElevator in fsElevators)
        {
            elevators.Add(GetElevatorFromFSharpElevator(fsElevator));
        }
        return elevators;
    }
 
    // GET api/Elevator/5
    public CS.Elevator Get(int id)
    {
        FS.DataRepository repository = new FS.DataRepository();
        return GetElevatorFromFSharpElevator(repository.GetElevator(id));
    }
 
    internal CS.Elevator GetElevatorFromFSharpElevator(FS.Elevator fsElevator)
    {
        CS.Elevator elevator = new CS.Elevator();
        elevator.ID = fsElevator.ID;
        elevator.County = fsElevator.County;
        elevator.StateId = fsElevator.StateId;
        elevator.Type = fsElevator.Type;
        elevator.Operation = fsElevator.Operation;
        elevator.Owner = fsElevator.Owner;
        elevator.O_Address1 = fsElevator.O_Address1;
        elevator.O_Address2 = fsElevator.O_Address2;
        elevator.O_City = fsElevator.O_City;
        elevator.O_State = fsElevator.O_State;
        elevator.O_Zip = fsElevator.O_Zip;
        elevator.User = fsElevator.User;
        elevator.U_Address1 = fsElevator.U_Address1;
        elevator.U_Address2 = fsElevator.U_Address2;
        elevator.U_City = fsElevator.U_City;
        elevator.U_State = fsElevator.U_State;
        elevator.U_Zip = fsElevator.U_Zip;
        elevator.Installed = fsElevator.Installed;
        elevator.Complied = fsElevator.Complied;
        elevator.Capacity = fsElevator.Capacity;
        elevator.CertStatus = fsElevator.CertStatus;
        elevator.EquipType = fsElevator.EquipType;
        elevator.Drive = fsElevator.Drive;
        elevator.Volts = fsElevator.Volts;
        elevator.Speed = fsElevator.Speed;
        elevator.FloorTo = fsElevator.FloorTo;
        elevator.FloorFrom = fsElevator.FloorFrom;
        elevator.Landing = fsElevator.Landing;
        elevator.Entrances = fsElevator.Entrances;
        elevator.Ropes = fsElevator.Ropes;
        elevator.RopeSize = fsElevator.RopeSize;
        return elevator;
    }
 
}

So that gives me that I want…

As a side note, I learned the hard way that the only way to force the SqlEntityTP to update based on a schema change in the DB is to change the connection string in the .config file.

Finally, when I published the WebAPI project to Azure, I got an exception.

<Error><Message>An error has occurred.</Message><ExceptionMessage>Could not load file or assembly 'FSharp.Core, Version=4.3.1.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.</ExceptionMessage><ExceptionType>System.IO.FileNotFoundException</ExceptionType><StackTrace> at System.Web.Http.ApiController.<InvokeActionWithExceptionFilters>d__1.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__0.MoveNext()</StackTrace

Turns out you need to not only add a reference to the F# project and FSharp.Core, you have to deploy the .dlls to Azure also. Thanks to hocho on SO for that one.

In conclusion, I love the promise of TPs. I want nothing more than to throw away all of the EF code-gen, .tt files, seeding for code-first nonsense, etc… and replace it with a single line TP. I have done this on a local project, but when I did it with an Azure, things were harder than they should be. Since it is easier to throw hand grenades than catch them, I made a list of the things I want to help the open source FSharp.Data project accomplish in the coming months:

1) SqlDatabaseConnection working with Azure Sql Storage

2) MSAccessConnection needed

3) ActiveDirectoryConnection needed

4) Json and WsdlService ability to handle proxies

5) SqlEntityConnection exposing classes publicly

Regardless of what the open-source community does, MSFT will still have to make a better commitment to F# on Azure, IMHO…

Filed under F#, Open Data

Restaurant Classifier: Async For Faster Performance?

March 4, 2014 2 Comments

Going back to my restaurant classifier using F# from last week, I decided to speed things up some. Each request to the Yellow Pages API takes 1 second, so with the 5,682 records, I am looking at a little over 1.5 hours to pull down the data when running serial.

I first thought about making my methods async so I changed the API call method to async and used the Http.AsyncRequest method like so (line 10 below):

member this.GetCatagoriesAsync(restaurantName: string, restaurantAddress: string) =
         async{
             if(String.IsNullOrEmpty(restaurantName)) then
                 failwith("restaurantName cannot be null or empty.")
             if(String.IsNullOrEmpty(restaurantAddress)) then
                 failwith("restaurantAddress cannot be null or empty.")
             let cleanedName = this.CleanName(restaurantName)
             let cleanedAddress = this.CleanAddress(restaurantAddress);
             let uri = "http://pubapi.atti.com/search-api/search/devapi/search?term=&quot;+cleanedName+"&searchloc="+cleanedAddress+"&format=json&key=qj5l8pphj5"
             let! response = FSharp.Net.Http.AsyncRequest(uri, headers=["user-agent", "None"])
             let ypResult = ypProvider.Parse(response)
             try
                 return ypResult.SearchResult.SearchListings.SearchListing.[0].Categories
             with
                 | ex -> return String.Empty
         } 

I then made the covering function async also (line 11 below)

member this.IsRestaurantInCatagoryAsync(restaurantName: string, restaurantAddress: string, restaurantCatagory: string) =
    async {
        if(String.IsNullOrEmpty(restaurantName)) then
            failwith("restaurantName cannot be null or empty.")
        if(String.IsNullOrEmpty(restaurantAddress)) then
            failwith("restaurantAddress cannot be null or empty.")
        if(String.IsNullOrEmpty(restaurantCatagory)) then
            failwith("restaurantCatagory cannot be null or empty.")
 
        System.Threading.Thread.Sleep(new System.TimeSpan(0,0,1))
        let! catagories = this.GetCatagoriesAsync(restaurantName, restaurantAddress)
        if(String.IsNullOrEmpty(catagories)) then return false
        else return this.IsCatagoryInCatagories(catagories,restaurantCatagory)
    }

The problem is that invoking the covering function via an anonymous method did not work easily.

After screwing around with the synax a bit, I went over to stack overflow where I found out two things:

There is not an easy way to do it (I was hoping for a Seq.FilterAsyc method)
Thomas Petricek is above my pay-grade.

In any event, I decided to drop the async and just look at parallelism. Turns out that there is a Parallel Seq class called PSeq, it is just not in the FSharp core library yet. I created a PSeq file in my project, moved it to the top and dropped the code in. I then changed the method call to use PSeq to invoke the serial methods:

member public this.GetChineseRestaurants () = 
    let catagoryRepository = new RestaurantCatagoryRepository()
    let catagory = "Chinese"
    this.GetRestaurants()
            |> PSeq.filter(fun (name, address) -> catagoryRepository.IsRestaurantInCatagory(name, address,catagory))
            |> Seq.toList    

When I first invoked it and looked at Fiddler (OT: did anyone notice that Fiddler’s new logo looks alot like a FSharp one? Probably just a coincidence), it was clear that things were running in parallel and that performance would improve. I have two cores on this workstation so my time be cut in half.

With the parallel method in my back pocket, I decided to see the ultimate result of the restaurant classification. I created a quick console app

class Program
{
    static void Main(string[] args)
    {
        Console.WriteLine("Start");
 
        Stopwatch stopwatch = new Stopwatch();
        stopwatch.Start();
        RestaurantBuilder builder = new RestaurantBuilder();
        var restaurants = builder.GetChineseRestaurants();
        
        foreach (var restaurant in restaurants)
        {
            Console.WriteLine(restaurant.Item1 + ":" + restaurant.Item2);
        }
        
        stopwatch.Stop();
        Console.WriteLine("Number of Chinese Restaurants: " + restaurants.Count());
        Console.WriteLine(stopwatch.Elapsed.ToString());
        Console.WriteLine("End");
        Console.ReadKey();
    }
}

I then ran the search on YP.com using my 4 core laptop and got the following results:

Compared to my original classifier based on name:

So the results make sense. The YP serial search would take at least 94.7 minutes, the YP parallel search took 41 minutes, and the in-memory name search took 3 seconds. The YP search(s) found restaurants that the name did not (Wang’s Kitchen, Crazy Fire Mongolian Grill, etc…) – 275 to 221, or 24% more restaurants.

I think that the next step is to look at the classifier and see how many restaurants are in both datasets and why the ones that are not in the YP one – where they are (did they even pay to be in the Yellow Pages?). Perhaps there is another YP category that can be considered. Also, it would be interesting to see of the restaurants that are in the name search and in the Yellow Pages that were not classified as Chinese – the false positive rate. Finally, I did see some 500s in Fiddler that had “read time out” so there is room for improvement to account for the transient faults…

Filed under Async, F#, Parallelism

Restaurant Classification Via the Yellow Pages API Using F#

February 25, 2014 1 Comment

As part of the restaurant analysis I did for open data day, I built a crude classifier to identify Chinese restaurants. The classifier looked at the name of the establishment and if certain key words were in the name, it was tagged as a Chinese restaurant.

member public x.IsEstablishmentAChineseRestraurant (establishmentName:string) =
    let upperCaseEstablishmentName = establishmentName.ToUpper()
    let numberOfMatchedWords = upperCaseEstablishmentName.Split(' ')
                                |> Seq.map(fun x -> match x with
                                                        | "ASIA" -> 1
                                                        | "ASIAN" -> 1
                                                        | "CHINA" -> 1
                                                        | "CHINESE" -> 1
                                                        | "PANDA" -> 1
                                                        | "PEKING" -> 1
                                                        | "WOK" -> 1
                                                        | _ -> 0)
                                |> Seq.sum
    match numberOfMatchedWords with
        | 0 -> false
        | _ -> true

Although this worked well enough for the analysis, I was interested in seeing if there was a way of using something that is more precise. To that end, I thought of the Yellow Pages – they classify restaurants into categories and assuming that the restaurant is in the yellow pages, it is a better way to determine the restaurant category versus just a name search.

The first thing I did was head over to the Yellow Pages (YP.com) website and sure enough, they have an API and a developers program. I signed up and had an API key within a couple of minutes.

The first thing I did was to try and search for a restaurant in the browser. I picked the first restaurant I came across in the dataset – Jumbo China #5. I created a request uri based on their API like so

http://pubapi.atti.com/search-api/search/devapi/search?term=Jumbo+China+5&searchloc=6108+Falls+Of+Neuse+Rd+27609&format=json&key=XXXXXXXXXX

When I plugged the name into the browser, I got this:

After screwing around with the code for about ten minutes thinking it was my API Key (Invalid Key would lead you to believe that, no?), Mike Thomas came over and told me that the url encoding was messing with my request – specifically the ‘#’ in Jumbo China #5. When I removed the # symbol, I got Json back:

Throwing the Json into Json2CSharp, the results look great:

I then took this URL and tried to load it into a F# type provider, I couldn’t understand why I was getting a red squiggly line of approbation (Json and XML):

so I pulled out Fiddler to see I was getting a 400. Digging into the response value, I found that “User Agent” was a required field.

The problem was then compounded because the FSharp Json type provider does not allow you to enter a User Agent into the constructor. I headed over to Stack Overflow where Thomas Petricek was kind enough to answer the question – basically you have to use the FSharp Http class to make the request (which you can add the user agent to) and then parse the response via the JsonProvider using the “Parse” versus the “Load” method. So spinning up the method like so:

This gave me the results back that I wanted. I then created a couple of methods to clean up any characters that might screw up the url encoding, added some argument validation, and I had a pretty good module to consume the YP.com listings:

namespace ChickenSoftware.RestaurantClassifier
 
open System
open FSharp.Data
open FSharp.Net
 
type ypProvider = JsonProvider< @"YP.txt">
 
type RestaurantCatagoryRepository() = 
   member this.GetCatagories(restaurantName: string, restaurantAddress: string) =
        if(String.IsNullOrEmpty(restaurantName)) then
            failwith("restaurantName cannot be null or empty.")
        if(String.IsNullOrEmpty(restaurantAddress)) then
            failwith("restaurantAddress cannot be null or empty.")
        let cleanedName = this.CleanName(restaurantName)
        let cleanedAddress = this.CleanAddress(restaurantAddress);
        let uri = "http://pubapi.atti.com/search-api/search/devapi/search?term=&quot;+cleanedName+"&searchloc="+cleanedAddress+"&format=json&key=XXXXXX"
        let response = FSharp.Net.Http.Request(uri, headers=["user-agent", "None"])
        let ypResult = ypProvider.Parse(response)
        try
            ypResult.SearchResult.SearchListings.SearchListing.[0].Categories
        with
            | ex -> String.Empty
 
    member this.CleanName(name: string) =
                name.Replace("#","").Replace(" ","+")
 
    member this.CleanAddress(address: string)=
                address.Replace("#","").Replace(" ","+")
    
    member this.IsCatagoryInCatagories(catagories: string, catagory: string) =
        if(String.IsNullOrEmpty(catagories)) then false
        else if (String.IsNullOrEmpty(catagory)) then false
        else catagories.Contains(catagory)
 
    member this.IsRestaurantInCatagory(restaurantName: string, restaurantAddress: string, restaurantCatagory: string) =
        if(String.IsNullOrEmpty(restaurantName)) then
            failwith("restaurantName cannot be null or empty.")
        if(String.IsNullOrEmpty(restaurantAddress)) then
            failwith("restaurantAddress cannot be null or empty.")
        if(String.IsNullOrEmpty(restaurantCatagory)) then
            failwith("restaurantCatagory cannot be null or empty.")
 
        System.Threading.Thread.Sleep(new System.TimeSpan(0,0,1))
        let catagories = this.GetCatagories(restaurantName, restaurantAddress)
        if(String.IsNullOrEmpty(catagories)) then false
        else this.IsCatagoryInCatagories(catagories,restaurantCatagory)
 
    member this.IsRestaurantInCatagoryAsync(restaurantName: string, restaurantAddress: string, restaurantCatagory: string) =
        async {
            if(String.IsNullOrEmpty(restaurantName)) then
                failwith("restaurantName cannot be null or empty.")
            if(String.IsNullOrEmpty(restaurantAddress)) then
                failwith("restaurantAddress cannot be null or empty.")
            if(String.IsNullOrEmpty(restaurantCatagory)) then
                failwith("restaurantCatagory cannot be null or empty.")
 
            let catagories = this.GetCatagories(restaurantName, restaurantAddress)
            if(String.IsNullOrEmpty(catagories)) then return false
            else return this.IsCatagoryInCatagories(catagories,restaurantCatagory)
        }

The associated unit and integration tests that I made in building this module look like this:

[TestClass]
public class CatagoryBuilderTests
{
 
    [TestMethod]
    public void CleanName_ReturnsExpectedValue()
    {
        RestaurantCatagoryRepository repository = new RestaurantCatagoryRepository();
        String restaurantName = "Jumbo China #5";
 
        String expected = "Jumbo+China+5";
        String actual = repository.CleanName(restaurantName);
        Assert.AreEqual(expected, actual);
    }
 
    [TestMethod]
    public void CleanAddress_ReturnsExpectedValue()
    {
        RestaurantCatagoryRepository repository = new RestaurantCatagoryRepository();
        String restaurantAddress = "6108 Falls Of Neuse Rd 27609";
 
        String expected = "6108+Falls+Of+Neuse+Rd+27609";
        String actual = repository.CleanAddress(restaurantAddress);
        Assert.AreEqual(expected, actual);
    }
 
 
    [TestMethod]
    public void GetCatagories_ReturnsExpectedValue()
    {
        string restaurantName = "Jumbo China #5";
        String restaurantAddress = "6108 Falls Of Neuse Rd 27609";
 
        RestaurantCatagoryRepository repository = new RestaurantCatagoryRepository();
        var result = repository.GetCatagories(restaurantName, restaurantAddress);
        Assert.IsNotNull(result);
    }
 
    [TestMethod]
    public void CatagoryIsContainedInCatagoriesUsingValidTrueData_ReturnsExpectedValue()
    {
        RestaurantCatagoryRepository repository = new RestaurantCatagoryRepository();
 
        String catagories = "Chinese Restaurants|Restaurants|";
        String catagory = "Chinese";
 
        Boolean expected = true;
        Boolean actual = repository.IsCatagoryInCatagories(catagories, catagory);
 
        Assert.AreEqual(expected, actual);
    }
 
    [TestMethod]
    public void CatagoryIsContainedInCatagoriesUsingValidFalseData_ReturnsExpectedValue()
    {
        RestaurantCatagoryRepository repository = new RestaurantCatagoryRepository();
 
        String catagories = "Chinese Restaurants|Restaurants|";
        String catagory = "Seafood";
 
        Boolean expected = false;
        Boolean actual = repository.IsCatagoryInCatagories(catagories, catagory);
 
        Assert.AreEqual(expected, actual);
    }
 
    [TestMethod]
    public void IsJumboChinaAChineseRestaurant_ReturnsTrue()
    {
        RestaurantCatagoryRepository repository = new RestaurantCatagoryRepository();
 
        string restaurantName = "Jumbo China #5";
        String restaurantAddress = "6108 Falls Of Neuse Rd 27609";
        String restaurantCatagory = "Chinese";
 
        Boolean expected = true;
        Boolean actual = repository.IsRestaurantInCatagory(restaurantName, restaurantAddress, restaurantCatagory);
 
        Assert.AreEqual(expected, actual);
    }
 
    [TestMethod]
    public void IsJumboChinaAnItalianRestaurant_ReturnsFalse()
    {
        RestaurantCatagoryRepository repository = new RestaurantCatagoryRepository();
 
        string restaurantName = "Jumbo China #5";
        String restaurantAddress = "6108 Falls Of Neuse Rd 27609";
        String restaurantCatagory = "Italian";
 
        Boolean expected = false;
        Boolean actual = repository.IsRestaurantInCatagory(restaurantName, restaurantAddress, restaurantCatagory);
 
        Assert.AreEqual(expected, actual);
    }
 
    [TestMethod]
    public void IsUnknownAnItalianRestaurant_ReturnsFalse()
    {
        RestaurantCatagoryRepository repository = new RestaurantCatagoryRepository();
 
        string restaurantName = "Some Unknown Restaurant";
        String restaurantAddress = "Some Address";
        String restaurantCatagory = "Italian";
 
        Boolean expected = false;
        Boolean actual = repository.IsRestaurantInCatagory(restaurantName, restaurantAddress, restaurantCatagory);
 
        Assert.AreEqual(expected, actual);
    }
 
 
 
    [TestMethod]
    public void CatagoryIsContainedInCatagoriesUsingEmptyCatagory_ReturnsExpectedValue()
    {
        RestaurantCatagoryRepository repository = new RestaurantCatagoryRepository();
 
        String catagories = "Chinese Restaurants|Restaurants|";
        String catagory = String.Empty;
 
        Boolean expected = false;
        Boolean actual = repository.IsCatagoryInCatagories(catagories, catagory);
 
        Assert.AreEqual(expected, actual);
    }

The hardest test to get run green was the negative test – passing in a restaurant name that is not recognized

[TestMethod]
public void IsUnknownAnItalianRestaurant_ReturnsFalse()
{
    RestaurantCatagoryRepository repository = new RestaurantCatagoryRepository();
 
    string restaurantName = "Some Unknown Restaurant";
    String restaurantAddress = "Some Address";
    String restaurantCatagory = "Italian";
 
    Boolean expected = false;
    Boolean actual = repository.IsRestaurantInCatagory(restaurantName, restaurantAddress, restaurantCatagory);
 
    Assert.AreEqual(expected, actual);
}

To code around the fact that a different set of Json came back and the original code is expecting a specific structure, I finally resorted to a try…catch

try
    ypResult.SearchResult.SearchListings.SearchListing.[0].Categories
with
    | ex -> String.Empty

I feel dirty, but I don’t know how else to get around it. In any event, I then coded up a module that pulled the list of restaurants from Azure and put them through the classifier.

namespace ChickenSoftware.RestaurantClassifier
 
open FSharp.Data
open System.Linq
open System.Configuration
open Microsoft.FSharp.Linq
open Microsoft.FSharp.Data.TypeProviders
 
type internal SqlConnection = SqlEntityConnection<ConnectionStringName="azureData">
 
type public RestaurantBuilder () =
    
    let connectionString = ConfigurationManager.ConnectionStrings.["azureData"].ConnectionString;
    
    member public this.GetRestaurants () =
        SqlConnection.GetDataContext(connectionString).Restaurants
            |> Seq.map(fun x -> x.EstablishmentName, x.EstablishmentAddress + " " + x.EstablishmnetZipCode)
            |> Seq.toArray
            
    member public this.GetChineseRestaurants () = 
        let catagoryRepository = new RestaurantCatagoryRepository()
        let catagory = "Chinese"
        this.GetRestaurants()
                |> Seq.filter(fun (name, address) -> catagoryRepository.IsRestaurantInCatagory(name, address,catagory))
                |> Seq.toList

This code is almost identical to the code I posted 2 weeks ago. Sure enough, When I threw my integration tests at the functions, check out fiddler.

I was getting responses. I ran into the problem on the 50th request though.

To get around this occasional timeout issue, I threw in a second delay between each request, which seemed the solve the problem.

System.Threading.Thread.Sleep(new System.TimeSpan(0,0,1))
let catagories = this.GetCatagories(restaurantName, restaurantAddress)
if(String.IsNullOrEmpty(catagories)) then false
else this.IsCatagoryInCatagories(catagories,restaurantCatagory)

However, this then introduced a new problem. There are 4,000 or so restaurants, so that is over 66 minutes of running. Not good. Next week, I hope to add some parallelism to speed things up…

Filed under F#, Open Data

Trigrams and F#

February 18, 2014 2 Comments

Rob Seder wrote a great post of trigrams last week. He then asked me how the same functionality would be implemented in F# – specifically dropping the for..each. Challenge accepted!.

The first thing I did was hit Stack Overflow to see if there is a built in function to parse a string by groups and I had a answer within minutes for exactly what I was looking for (thanks MattNewport).

So to match Rob’s BuildTrigram function, I wrote this:

type TrigramBuilder() = 
    member this.BuildTrigrams(inputString: string) =
        inputString
            |> Seq.windowed 3
            |> Seq.map(fun a -> System.String a)
            |> Seq.toArray

And I had a covering unit test already created:

[TestMethod]
public void GetTrigrams_ReturnsExpectedValue()
{
    var builder = new TrigramBuilder();
    String inputString = "ABCDEFG";
 
    String[] expected = new String[] { "ABC", "BCD", "CDE", "DEF", "EFG" };
    String[] actual = builder.BuildTrigrams(inputString);
 
    CollectionAssert.AreEqual(expected, actual);
}

I then Implemented a function that matches his double loops (can’t tell the function name from the code snippet on the blog post):

member this.GetMatchPercent(baseString: string, compareString: string) =
    let trigrams = this.BuildTrigrams(compareString)
    let matchCount = trigrams
                        |> Seq.map(fun t -> match baseString.Contains(t) with
                                                | true -> 1
                                                | false -> 0)
                        |> Seq.sum
    let totalCount = trigrams.Length
    float matchCount/float totalCount

And throwing in some covering unit tests:

public void GetMatchPercentageOfExactMatch_ReturnsExpectedValue()
{
    var builder = new TrigramBuilder();
    
    String baseString = "ABCDEF";
    String compareString = "ABCDEF";
 
    double expected = 1.0;
    double actual = builder.GetMatchPercent(baseString, compareString);
 
    Assert.AreEqual(expected, actual);
}
 
[TestMethod]
public void GetMatchPercentageOf50PercentMatch_ReturnsExpectedValue()
{
    var builder = new TrigramBuilder();
 
    String baseString = "ABCD";
    String compareString = "ABCDEF";
 
    double expected = 0.5;
    double actual = builder.GetMatchPercent(baseString, compareString);
 
    Assert.AreEqual(expected, actual);
}

Sure enough, green across the board:

Filed under F#

Analysis of Health Inspection Data using F#

February 11, 2014 4 Comments

As part of the TRINUG F#/Analytics SIG, I did a public records request from Wake County for all of the restaurant inspections in 2013. If you are not familiar, the inspectors go out and then give a score to the restaurant. The restaurant then has to display their score like this:

After some back and forth, I got the data as an Excel spreadsheet that looks like this

I then loaded the spreadsheet into a sql server and exposed it as some OData endpoints.

// GET odata/Restaurant
[Queryable]
public IQueryable<Restaurant> GetRestaurant()
{
    return db.Restaurants;
}
 
// GET odata/Restaurant(5)
[Queryable]
public SingleResult<Restaurant> GetRestaurant([FromODataUri] int key)
{
    return SingleResult.Create(db.Restaurants.Where(restaurant => restaurant.Id == key));
}

I then dove into the data to see if there were any interesting conclusions to be found. Following my pattern of doing analytics using F# and unit testing using C#, I created a project with the following code:

namespace ChickenSoftware.RestraurantChicken.Analysis
 
open System.Linq
open System.Configuration
open Microsoft.FSharp.Linq
open Microsoft.FSharp.Data.TypeProviders
 
type internal SqlConnection = SqlEntityConnection<ConnectionStringName="azureData">
 
type public RestaurantAnalysis () =
    
    let connectionString = ConfigurationManager.ConnectionStrings.["azureData"].ConnectionString;

Note that I am using the connection string in two places – the 1st for the type provider to do its magic at design time and the second for actually accessing the data at run time. With that set up, the 1st question I had was “ is there seasonality in inspection scores like there are in traffic tickets?” To that end, I created the following function:

member public x.GetAverageScoreByMonth () =
    SqlConnection.GetDataContext(connectionString).Restaurants
        |> Seq.map(fun x -> x.InspectionDate.Value.Month, x.InspectionScore.Value)
        |> Seq.groupBy(fun x -> fst x)
        |> Seq.map(fun (x,y) -> (x,y |> Seq.averageBy snd))
        |> Seq.map(fun (x,y) -> x, System.Math.Round(y,2))
        |> Seq.toArray
        |> Array.sort

This is pretty vanilla F# code, with the tricky part being the average by month (lines 4 and 5 here). What the code is doing is grouping up the 4,000 or so tuples that were created on line 3 into another tuple – with the fst being the groupBy value (in this case month) and then the second tuple being a tuple with the month and score. Then, by averaging up the score of the second tuple, we get an average for each month. I create a unit (really integration) test like so:

[TestMethod]
public void GetAverageScoreByMonth_ReturnsTwelveItems()
{
    var analysis = new RestaurantAnalysis();
    var scores = analysis.GetAverageScoreByMonth();
    Int32 expected = 12;
    Int32 actual = scores.Length;
    Assert.AreEqual(expected, actual);
}

And the result ran green.

Putting a break on the Assert and a watch on scores, you can see the values:

A couple of things stand out

1) The overall average is around 96 and change

2) There does not seem to be any significant variance among months.

Since I am trying to also teach myself D3, I then added a MVC5 project to my solution and added an analysis controller that calls the function in the analysis module and serves the results as json:

public JsonResult AverageScoreByMonth()
{
    var analysis = new RestaurantAnalysis();
    var scores = analysis.GetAverageScoreByMonth();
    return Json(scores,JsonRequestBehavior.AllowGet);
}

I then made a page with a simple D3 chart that calls this controller

@{
    Layout = "~/Views/Shared/_Layout.cshtml";
}
 
<svg class="chart"></svg>
 
<style>
    .bar {
        fill: steelblue;
    }
 
        .bar:hover {
            fill: brown;
        }
 
    .axis {
        font: 10px sans-serif;
    }
 
        .axis path,
        .axis line {
            fill: none;
            stroke: #000;
            shape-rendering: crispEdges;
        }
 
    .x.axis path {
        display: none;
    }
</style>
 
 
 
<script>
 
    var margin = { top: 20, right: 20, bottom: 30, left: 40 },
        width = 960 – margin.left – margin.right,
        height = 500 – margin.top – margin.bottom;
 
    var x = d3.scale.ordinal()
        .rangeRoundBands([0, width], .1);
 
    var y = d3.scale.linear()
        .range([height, 0]);
 
    var xAxis = d3.svg.axis()
        .scale(x)
        .orient("bottom");
 
    var yAxis = d3.svg.axis()
        .scale(y)
        .orient("left")
        .ticks(10, "%");
 
    var svg = d3.select("body").append("svg")
        .attr("width", width + margin.left + margin.right)
        .attr("height", height + margin.top + margin.bottom)
      .append("g")
        .attr("transform", "translate(" + margin.left + "," + margin.top + ")");
 
 
 
    $.ajax({
        url: "http://localhost:3057/Analysis/AverageScoreByMonth/&quot;,
        dataType: "json",
        success: function (data) {
            x.domain(data.map(function (d) { return d.Item1; }));
            y.domain([0, d3.max(data, function (d) { return d.Item2; })]);
 
            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.Item1); })
                .attr("width", x.rangeBand())
                .attr("y", function (d) { return y(d.Item2); })
                .attr("height", function (d) { return height – y(d.Item2); });
 
        },
        error: function (e) {
            alert("error");
        }
    });
 
    function type(d) {
        d.Item2 = +d.Item2;
        return d;
    }
</script>

And when I run it, a run-of-the mill barchart (I did have to adjust the F# to shift the decimal to the left two positions so that I could match the scale of the chart’s template. For me, it is easier to alter the F# than the javascript:

Following this pattern, I did some other season analysis like average by DayOfMonth

DayOf Week.

So there does not seem to be any seasonality in inspection scores.

I then did an average of inspectors

And there looks to be some variance, but it is getting lost of the scale of the map. The problem is that the range of the scores is not 0 to 100

Here is a function that counts the number of scores (rounded to 0)

member public x.CountOfRoundedScores () =
    SqlConnection.GetDataContext(connectionString).Restaurants
        |> Seq.map(fun x -> System.Math.Round(x.InspectionScore.Value,0), x.InspectionID)
        |> Seq.groupBy(fun x -> fst x)
        |> Seq.map(fun (x,y) -> (x,y |> Seq.countBy snd))
        |> Seq.map(fun (x,y) -> (x,y |> Seq.sumBy snd))
        |> Seq.toArray

That graphically looks like:

So back to inspectors, I needed to adjust the scale from 0 to 100 to 80 to 100. I also needed to remove the null inspection Ids and the records that were for the ‘test facility’ and the 6 records that were below 80.

member public x.AverageScoreByInspector () =
    SqlConnection.GetDataContext(connectionString).Restaurants
        |> Seq.filter(fun x -> x.EstablishmentName <> "Test Facility")
        |> Seq.filter(fun x -> x.InspectionScore.Value > 80.)
        |> Seq.filter(fun x -> x.InspectionID <> null)
        |> Seq.map(fun x -> x.InspectorID, x.InspectionScore.Value)
        |> Seq.groupBy(fun x -> fst x)
        |> Seq.map(fun (x,y) -> (x,y |> Seq.averageBy snd))
        |> Seq.map(fun (x,y) -> x, y/100.)
        |> Seq.map(fun (x,y) -> x, System.Math.Round(y,4))
        |> Seq.toArray
        |> Array.sort

I then adjusted the scale of the inspector graph to have to domain from 80 to 100 (versus 0 to 100) and the scale of the y axis. This was a good article explaining Scales and Domains in D3.

var yAxis = d3.svg.axis()
    .scale(y)
    .orient("left")
    .ticks(10);

$.ajax({
    url: "http://localhost:3057/Analysis/AverageScoreByInspector/&quot;,
    dataType: "json",
    success: function (data) {
        x.domain(data.map(function (d) { return d.Item1; }));
        y.domain([80, d3.max(data, function (d) { return d.Item2; })]);

and now there is pretty good graph showing the variance among inspectors:

So the interesting this is that #1168 is 2 below the average – which of a domain of 10 is pretty significant. Interestingly, 1168 is also the inspector who has all of the “Test facility” records – so they are probably the trainer and/or lead inspector. With this analysis in the back pocket, ran a function that did the inspection score by establishment type:

This is kinda interesting (esp that pushcarts got the highest scores) but I wanted to see if there was any truth the the common perception that Chinese restaurants are less sanitary than other kinds of restaurants. To that end, I created a rudimentary classifier that searched the name of the establishment to see if it had a name that is typically associated with fast-food Chinese:

member public x.IsEstablishmentAChineseRestraurant (establishmentName:string) =
    let upperCaseEstablishmentName = establishmentName.ToUpper()
    let numberOfMatchedWords = upperCaseEstablishmentName.Split(' ')
                                |> Seq.map(fun x -> match x with
                                                        | "ASIA" -> 1
                                                        | "ASIAN" -> 1
                                                        | "CHINA" -> 1
                                                        | "CHINESE" -> 1
                                                        | "PANDA" -> 1
                                                        | "PEKING" -> 1
                                                        | "WOK" -> 1
                                                        | _ -> 0)
                                |> Seq.sum
    match numberOfMatchedWords with
        | 0 -> false
        | _ -> true

I then created a function that returned the average and ran my unit tests.

[TestMethod]
public void IsEstablishmentAChineseRestraurantUsingWOK_ReturnsTrue()
{
    var analysis = new RestaurantAnalysis();
    String establishmentName = "JAMIE'S WOK";
 
    var expected = true;
    var actual = analysis.IsEstablishmentAChineseRestraurant(establishmentName);
    Assert.AreEqual(expected, actual);
}
 
[TestMethod]
public void IsEstablishmentAChineseRestraurantUsingWok_ReturnsTrue()
{
    var analysis = new RestaurantAnalysis();
    String establishmentName = "Jamie's Wok";
 
    var expected = true;
    var actual = analysis.IsEstablishmentAChineseRestraurant(establishmentName);
    Assert.AreEqual(expected, actual);
}
 
[TestMethod]
public void AverageScoreForChineseRestaurants_ReturnsExpected()
{
    var analysis = new RestaurantAnalysis();
    var actual = analysis.AverageScoreForChineseRestaurants();
    Assert.IsNotNull(actual);
}

When a break was put on the value of the average, it was apparent that Chinese restaurants scored significantly lower than the average of 96

So then I applied 1 more segmentation: Chinese versus Non-Chinese scores by inspector:

member public x.AverageScoresOfChineseAndNonChineseByInspector () =
    let dataSet = SqlConnection.GetDataContext(connectionString).Restaurants
                    |> Seq.map(fun x -> x.EstablishmentName, x.InspectorID,x.InspectionScore.Value)
    let chineseRestraurants = dataSet
                                |> Seq.filter(fun (a,b,c) -> x.IsEstablishmentAChineseRestraurant(a))
                                |> Seq.map(fun (a,b,c) -> b,c)
                                |> Seq.groupBy(fun x -> fst x)
                                |> Seq.map(fun (x,y) -> (x,y |> Seq.averageBy snd))
                                |> Seq.map(fun (x,y) -> x, System.Math.Round(y,2))
                                |> Seq.toArray
                                |> Array.sort
    let nonChineseRestraurants = dataSet
                                |> Seq.filter(fun (a,b,c) -> not(x.IsEstablishmentAChineseRestraurant(a)))
                                |> Seq.map(fun (a,b,c) -> b,c)
                                |> Seq.groupBy(fun x -> fst x)
                                |> Seq.map(fun (x,y) -> (x,y |> Seq.averageBy snd))
                                |> Seq.map(fun (x,y) -> x, System.Math.Round(y,2))
                                |> Seq.toArray
                                |> Array.sort
    Seq.zip chineseRestraurants nonChineseRestraurants
           |> Seq.map(fun ((a,b),(c,d)) -> a,b,d)
           |> Seq.toList

And in graphics using a double-bar chart:

So this is kinda interesting. The lead inspector (1168) who grades everyone lower actually gives Chinese restaurants higher marks. Everyone else pretty much grades Chinese restaurants lower except for 1 inspector. Also, 1708 must really not like Chinese restaurants – or their inspection list has a series of really bad Chinese restaurants.

Note that this may not be statistically significant (I didn’t control for sample size, etc..) – but further analysis might be warranted, no? If you are interested, here is the endpoint: http://restaurantchicken.cloudapp.net/odata/Restaurant

Finally, when I presented this analysis to TRINUG last week, lots of people became interested in F# and analytics (ok, maybe 3). You can see the comments here. Also, I now have an appointment with the head of the health department department and the CIO of Wake County later this week – let’s see what they say…

Filed under Analytics, D3, F#

Unit Testing F# Projects

February 1, 2014 1 Comment

I am a big believer of unit tests and as I write more and more code, I suffer what sociologists call “confirmation bias” whereby I keep finding more and more reasons to confirm that I am right. But I am at the point where I don’t believe in developer documentation (sorry Sandcastle), reflector (sorry Redgate), code comments, or architectural diagrams. I believe in the code, the whole code, and nothing but the code. Or as Rasheed Wallace might say if he was a coder versus a professional basketball player: “Code Don’t Lie!”.

And the only code that tells you what a module is doing is the unit tests. If you want to see how the module behaves, look at the green unit tests. If you want to see how the module is supposed to behave but is not, look at the red unit tests. If you want to see how the module might or might behave because the code is out of control, look for the non-existent unit tests.

So when I started writing code in F#, the unit tests went along for the ride. F# folks will tell you to use the REPL to get your code working and/or use a unit test project in F#. I don’t do either because:

1) The REPL is designed for quick prototyping, not for having a durable, cantonal example of module behavior. Having a full suite of unit tests gives you code coverage, tests at the build, and a fail-proof way of documenting the code’s behavior.

2) Most of the other developers I work with are CSharpers. Having the unit tests in C# allows them to understand the behavior in a language in which they are familiar. Since the tests need to communicate the working code’s intent, having that in a language they understand is critical. They don’t have to understand F# to use a F# module. Also, porting from C# in MSTest to NUnit in C# is a snap.

So when you add a C# Unit Test project to your solution that has a F# module you want to test, there are a couple of things you need to do.

1) Add a reference from the Unit Test Project to the F# Project (Right Click, Add Reference)

2) Add a reference to F#

3) If you are using type providers (and who doesn’t) and you have the connection string in the .config file of the working code project, add a .config file to the unit test project and copy over the connection string

Note that you code has to reflect that the connection string is being used in 2 different ways by the type provider, as explained in this post. Your F# code needs to look like this:

type internal SqlConnection = SqlEntityConnection<ConnectionStringName="azureData">
 
 
type public RestaurantAnalysis () =
    
    let connectionString = ConfigurationManager.ConnectionStrings.["azureData"].ConnectionString;
    member public x.GetScoresByMonth () =
        SqlConnection.GetDataContext(connectionString).Restaurants
            |> Seq.map(fun x -> x.InspectionDate.Value.Month, x.InspectionScore.Value)
            |> Seq.groupBy(fun x -> fst x)
            |> Seq.toList

4) Finally, you have to rebuild the F# project each time you want the unit tests to pick up changes. That is different from a C# unit test project referencing a C# working code project.

Finally, not related to unit testing but too short for a blog post, if you are using the type providers (and who doesn’t) and you need to expose your classes publicly, you can’t use the SqlEntity provider – you need to use the SqlData provider. The catch is that SqlData does not work with Azure Sql Storage as far as I can tell. In my case, I used SqlEntity and exposed tuples and custom types publicly. Not the best, but still better than using C# and Entity Framework…

Filed under F#, Unit Testing || Mocking

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#

← Older posts

Newer posts →

Jamie Dixon's Home

F# and the Open/Closed Principle

Consuming Twitter With F#

Apriori Algorithm and F# Using Elevator Inspection Data

Elevator App: Part 1 – Data Layer Using F#

Restaurant Classifier: Async For Faster Performance?

Restaurant Classification Via the Yellow Pages API Using F#

Trigrams and F#

Analysis of Health Inspection Data using F#

Unit Testing F# Projects

Basic Insert Operation Using F#

Categories

Recent Posts

Archives

Blogroll

Meta