In this first work challenge you will use R and SQL to get a by-name-list of those who are domestic violence victims from an HMIS data pull (5.1).
The HMIS Data Pulls are simply a relational database which are broken into multiple CSVs. These CSVs will change in formatting, as stipulated by HUD. The current version of these CSVs is 5.1. For this work challenge the focus will be on two CSVs.
The Client file will contain one row per client and only one row. Each row will contain most of all the Client’s demographic information.
The HealthAndDV file will contain a row for every HUD Entry Assessment completed on the participant. Each entry will contain general health information as well the client’s domestic violence information, which the client reported during the HUD Entry Assessment.
What ties the two files together is the PersonalID column. This ID is meant to be the grand-mamma of all client IDs. It is 32 characters long and contain both numbers and letter:
(note, this ID is called the “Enterprise ID” in our HMIS software)
Both the Client and HealthAndDV contain the PersonalID column. This often referred to as a key when dealing with relational databases. This unique ID in both files will allow us to merge the two data sets together using something called joins.
Now, in the Client.csv the information is readable to us humans. There will be the FirstName, LastName, SSN columns and many more. But in the HealthAndDV.csv the information is trickier. For this challenge we are going to focus on one column DomesticViolenceVictim. When you open the data set you may notice that instead of “Yes” or “No” answers in the columns you will see “1” or “0”. That’s because computers understand the 1 and 0 much quicker than Yes or No.
This is an important side note for managing data. Make the databases easy for computers to understand and report generation will be much faster. You have to think, if using a 1 instead of Yes could save 0.5 seconds on a calculation, then when you have a dataset which contains 1000 records you just saved 500 seconds 8.3 seconds. Now, multiply by 1,700,000 records. Well, you get the picture.
Ok. Back to the problem at hand. Just know “1” is equal to “Yes” and “0” is equal to “No”. So, for this challenge, we will want to find all the clients who have a “1” in the DomesticViolenceVictim column
We are going to merge the two data sets and to discover the following:
A list of clients who are victims of domestic violence.
A count of how many clients are fleeing domestic violence.
Really, the second piece of information is counting how many people are in the list of those who are victims.
To get this information we will need to do the following:
Load the Client.csv and HealthAnd.csv
Filter the HealthAndDV dataset to the most recent according to the column DateCreated
Join (merge) the dataframes where their PersonalID are the same
Filter the merged dataframe to those who’ve reported 1 in DomesticViolenceVictim
Write this data to a file.
Use a function to count how many participants are in this victim list.
Below are the resources which should help you understand each step of the process.
R Programming A-Z – Video 41 – Loading and Importing Data in R
R Programming A-Z – Video 21 – Functions in R
The Complete SQL Bootcamp – All Videos in Section 5
The Complete SQL Bootcamp – All Videos in Section 6
R is a programming language for super nerds. Often, it is used by those working in:
Bio-statistic (genetic sequencing)
Big-data (think all data collected by Facebook)
It is extremely fast computationally. The people who designed it wanted something which could crunch big data sets, on their computer, in a matter of minutes.
One of the neatest parts of R is it allows for user written plugins. Want to translate a data set of ZIP codes into their corresponding GPS coordinateds. Their’s a plugin for that! And did I mention it’s all free!
Think of our approach to data as Spanglish. We are going to take two major programming languages to get the best of both. When we don’t have the exact word to describe something, we will switch to a different language and use its vocabulary.
R is a powerful tool, but often, the syntax is boggish. It is hard to read and figure out what’s going on. SQL on the other hand, it’s pretty simple. And humans have an easier time reading it.
Personally, I’m looking to solve problems as quickly as possible and I’ve found by mixing the two I get to solutions quick. If I’m having a hard time getting Google to tell me how to do something in SQL, I’ll switch and see if Google know’s how to do it in R. Thus, getting to what I need do much quicker. And let’s not fool ourselves–learning a programming language is learning how to Google well.
A second reason to mix SQL is about respect and marketability. R seems to be gaining ground in a lot of data sciences, and seems to be the tool when it comes to economics and statistics, however, most data exchanges have SQL at their heart. Therefore, when I can use my work as an excuse to develop a marketable skill, I’m going to do it.
A third reason to mix the two, most often the servers where our data calculations are being done are extremely limited in their hardware. This results in calculations taking much longer on a server than they would on or personal PC. For example, a query which would take 45 minutes on our HMIS software vendor’s server takes around 30 seconds on my personal PC.
A fourth reason, by completing our calculations on our personal computers it reduces the number of times client level information would need to be transmitted back-and-forth from the server. Of course, this doesn’t mean the data is more secure, it simply means the opportunities for more secure data are in our hands. This method makes follow proper PC security practices much more important (encrypting hard-drives, not sharing passwords, etc)
Localization of data.
However, I’ve recently discovered Google’s Web APIs. Specifically, their Bluetooth Low Energy API.
Now, this might sound like it open for security issues–and perhaps it will be. But there are two requirements Google has put in place which hopefully gets around any issues. First, the API can only be called by action. Secondly, the API can only be called from a secured connection (HTTP over SSL).
Ok, there are few other downers to talk about. First this only works in Chrome–but given this is a Google API, well, duh. The other is not all OSes are currently supported. The following I’ve tested and work right out of the box:
The others which are supposed to be supported but I’ve not tested:
Windows (with some work)
Having worked with Bluetooth LE on all of these OSes I can say there is hope for Windows. In fact, I think with the Creator’s Update the Microsoft folk opened up the last needed ingredient. The real hold out will be iOS. Apple is not a fan of browser apps. They would much rather browsing be done inside their native apps. If I’m being positive, I’d say this is so Apple can make sure the mobile UX is excellent, and by forcing native apps, they have control by app approval. If I’m being negative, well, Apple takes 30% on app purchases and web apps land them nada. Just my opinion.
If you’d like to stay up to date on compatibility of BLE in the browser there is a an implementation status page on the Web Bluetooth Community Group:
The documentation is actually pretty robust–and with this guide, the process of subscribing to a device characteristic should be pretty straight forward.
The first piece we need are service IDs to search for.
This takes the text element of the DOM element ‘optionalServices’, which should be in the in 16 bit hex format, 0x0000. This becomes one of the service IDs searched in the Bluetooth LE search cycle. For the Bluetooth module HM-10, HM-11, HM-16, HM-17 the service ID is 0xFFE0.
Moving on to the search, when the code below is executed the Chrome browser should show a search and pair menu (see image) for pairing a device. When a device has been paired the promise will resolve returning the device which has been paired.
It is important to note this block must be called by a user action. For example, if set to execute on page load it will refuse to fire. But if called onClick then it will show. This is meant to provide more security to the API.
As stated, the requestDevice will return a device. Using the promise .then we can begin working with the BluetoothDevice
Which is returned after it has been paired by the user. The BluetoothDevice object has three items of interest.
name – which provides the string name of the device
id – the ID string
gatt – a gatt which contains a reference to the BluetoothRemoteGATTServer object
The BluetoothRemoteGATTServer interface contains many of the methods needed to interact with the Bluetooth device. For example,
Attempts to asynchronously create a connection with the device through a Promise. If .then is attached then the method will return a service object if succesful. If you are just looking to get something done with Bluetooth, feel free to keep hacking through this article (that’s what I’d do–TL;DR). However, if you want to know more about Bluetooth 4 protocol here a few useful links:
Once the connection attempt has been made and returned succesful, the BluetoothRemoteGATTServer object returned can be petitioned for a list of services.
This will fire asynchronously using promises, and if succesful, return a BluetoothRemoteGATTService object. This represents all the services the device has public. Then, the returned service object may be iterated over to identify get characteristics of the device. (Almost to the data, I swear).
Essentially, the BluetoothRemoteGATTService object is merely an array containing on the services. Using a services.forEach we get each individual service to explore its characteristics.
Now, I’m going to add the whole block which is responsible for iterating over each service and its characteristics, essentially turning on notifications for each device. This will ultimately allow a callback to be fired every every time the device sends data and a reference to a method by which data can be written to the device.
Essentially, each service and characteristic contained in the service enumerated. At each characteristic there are two calls. One is to get a reference to the characteristic for writing. This is the global variable writeCharacteristic. Then, notifications for the writeCharacteristic are started. This will assure any time data is made available on the remote device our program is notified.
Now, it should be noted, this above code is hackish. For example, what if there are multiple characteristics and the last one isn’t the one we want to know about. Well, we’d have a write reference to the wrong characteristic. So, filtering to the desired characteritic is on my TODO list.
But, let’s finish before refactoring.
Let’s take a look at how to write data to the device after getting a reference to the desired characteristic.
The above method creates a promise and writes to the device asynchoronously. On the way, it checks to make sure the device is paired (not connected, that’s on the TODO list). Also, it makes sure we still have a reference to the writeCharacteristic. Then, it will either encode it in utf-8 and write the data, or if the string argument is false it’ll just write the data. After it has written the data, the resolve is executed. This would allow the writeMethod to be called like so:
Ok, last bit. Let’s setup capturing incoming data. To begin, I created a method which holds a list of all the callback methods to call when data has been received.
This method allows a method’s name to be passed in. It then adds an event listener to this method, which will be called whenever characteristicvaluechanged. Also, it saves this method’s name in an array in case I want to stop notifications later (again, not completed, but on the TODO).
The purpose of allowing multiple callbacks is for when I’m working with many modules which all would like to know what’s going on with the Bluetooth LE device.
For example, this module is meant to be a piece of a larger project, which is an uploader app using BLE to upload HEX files to AVRs running TinySafeBoot.
Ok, one last piece. Let us see what the onRecievedData callback could looks like:
This is how I’ve written the notification of data callback. The event.target.value contains the data, which is in an untyped array. I choice to encode it into Uint8 as I’ll be working with both ASCII and non-ASCII data.
Well, that’s it. This code will allow one to search, connect, write data to, and receive data from Bluetooth Low Energy devices from Chrome browser. Let me know if you have any recommendations.
Here is the full code referenced directly from my project:
One of the issues I’ve had in the past with the Lumi projects is manageable UI. The project will start out pretty straight foward, but soon, I’m switching between device types, checking if hardware is ready, and routing callbacks based upon the device selected. It becomes spaghetti code quick. On Lumi4, I’ve decided to bite the bullet and implement MVVM.
After about 20 hours struggling with setting up Lumi4 as an MVVM project I’ve dervied two conclusions:
Apple spoils developers with MVC baked into Xcode
MVVM in C# and UWP isn’t simple. It seems like there is a lot more boiler-plate coding necessary for MVVM than MVC in Xcode. Eventually, I broke down and downloaded the NuGet package Prism, as a MVVM helper. This helped alleviate some of the code necesssary for Commanding (which I still don’t understand well enough to implement without Prism).
Below I’m going to take a look at a couple of controls I’ve written MVVM on. Finding documentation for MVVM in Universal Windows Plateform (UWP) is tricky. It is similar to XamarinForms and WPF, but overall, there are syntax differences which make generalizing the documentation difficult.
First, taking a look at the four text boxes which will hold the network IDs and host IDs the user is to type in:
This sets up the binding to the variables HostIDOne, HostIDTwo, NetworkIDOne, NetworkIDTwo. However, there’s plenty more boilerplate code before things start working.
To setup a ViewModel it’s best to setup an abstract class which can be inherited. This saves on creating boilerplate. Below is the abstract class the internet told me to make:
This code handles the property changing notification for all properties declared in classes which inherit from the MainViewModelBase. Such as the MainViewViewModel which is used in Lumi4
Moving on to the actual implementation of the bound text boxes. Each text box will have a property associated with the string value in the Text attribute. However, before this will work, the DataContext must be set for the MainPage.xaml. This is done in the MainPage.xaml.cs file.
(I told you it was a lot of work to setup. Well, compared to Xcode’s MVC.)
Ok, everything should be in place, time to implement the bound properties. In the MainViewViewModel I’ve the following code:
The if statement under the setter checks if the value about to be set is the same as the value is currently. Note, there is a helper in Prism which will prevent one from having to rewrite this for every attribute. However, I implemented the above code before I downloaded Prism and decided to write this article while it was still fresh in my mind. I’ll probably correct these to use the Prism helper before moving.
The code so far should allow for the properties setter to be called whenever the user types something in one of the four textboxes. On caveat, it seems the setter is not called until the user removes focus from the textbox. I’ll probably need to change the binding of the properties, but for now, it works well enough.
Well, this is all fine and gone, but what about Commands such as Button Click Events? Pretty simple, but with more boilerplate code.
First, bind to the command in the View
Next, and this is the part I don’t understand without Prism helping, there needs to be a delegate which will fire an event whenever the commad is called. In Prism there is the DelegateCommand type which takes care of a lot of the work. The DelegateCommand has to be initialized with two event handlers CanExecute and Execute. These methods will be called in that order every time the DelegateCommand property is accessed.
And that’s it for now. Had to make sure I jotted down my notes while the challenge was still fresh in my head.
Apparently, a Prism ViewModel should inherit from BindableBase instead of the custom ViewModelBase the internet told me to write. Also, to get the prismProp code snippet it looks like you have to download the Prism template packet: