My friend Emmet called me with an interesting problem today. He had a spreadsheet with 28k rows. One of the columns was an address, sort of separated by commas. The address column was very inconsistent. Some ended in Ireland, others just had the county name, some used the form “County Limerick”, others “Co. Limerick”, and others still just “Limerick”. He needed to do some calculations by county so he needed to extract the county name for each row. As I learned doing the pubs research, Irish addresses are a pain!

I’d dealt with this sort of problem before (not with 28k rows mind!) when I used to work for national charities and it used to bug me terribly.

Emmet found a way to anonymise the data so he could send me a subset. I had a play around with a spreadsheet-based solution before breaking out the Python for a quick hack.

The script below reads in a file called “input.csv” and writes to a file called “output.csv” with the same data, but with the county name and a comma added to the start of each line (or Unknown if the script couldn’t work it out).

The script is case-insensitive, and matches the rightmost county on each line, so an address in Omeath, Co. Louth is correctly identified as being in Louth, and Dublin Rd., Athlone, Co. Westmeath matches Westmeath, not Dublin.

The script is fairly flexible about the structure of the input file, so the address data can be in different columns, or all in one.

Here’s the script:

###Usage: python parse-csv.py###
###Input file must be called input.csv###


#function definition: input_text is a string, all_ireland is a bool
def prepend_address_with_county(input_text,all_ireland):
    #prepare the county list
    counties_list = ['Carlow','Cavan','Clare','Cork','Donegal','Dublin','Galway','Kerry','Kildare','Kilkenny','Laois','Leitrim','Limerick','Longford','Louth','Mayo','Meath','Monaghan','Offaly','Roscommon','Sligo','Tipperary','Waterford','Westmeath','Wexford','Wicklow']
    #add on the six counties if we want all-Ireland
    if all_ireland == True:
        counties_list.extend(['Antrim','Armagh','Derry','Down','Fermanagh','Tyrone'])
    outfile = ''
    errorcount = 0
    linecount = 0
    #loop over each line
    for line in input_text:
        linecount += 1
        #keep track of the county we're going to feed in
        county_match = ''
        #let's keep track of the index of what we have found, we want the rightmost match
        old_find_index = 0
        #loop over all counties
        for county in counties_list:
            #look for the county, starting from the RHS
            # also convert to uppercase first
            find_index = line.upper().rfind(county.upper())
            #have we found anything? (find_index will be -1 if we haven't found anything)
            if find_index > old_find_index:
                #keep the county match
                county_match = county
                #update the rightmost index count
                old_find_index = find_index
        #have we got any matches?
        if old_find_index != 0:
            outfile += county_match+","+line
        else:
            outfile += "Unknown,"+line
            errorcount += 1
    return {"output":outfile,"errors":errorcount,"lines":linecount}




file = open("input.csv" ,'rU')
output_csv = open("output.csv",'w')
result_dict = prepend_address_with_county(file,True)
percentage_error = 100*result_dict["errors"]/result_dict["lines"]
print "%d lines processed, %d Unknown counties (%.2f%%)" % (result_dict["lines"], result_dict["errors"],percentage_error)
output_csv.write(result_dict["output"])

My father-in-law moved in with us two weeks ago. He is a lovely kind man in his late eighties and it’s a pleasure having him around.

He has Alzheimer’s and needs someone around him all the time during the day. We’re managing this OK while juggling the needs of the business. Unfortunately he can sometimes wake up in the middle of the night in a state of anxiety because he doesn’t know where he is.  This means that Sheila and I can’t get out to the local for a quick pint even after he’s gone to bed.

Enter the lazy programmer…

There’s nothing like the prospect of a pint to stir the motivations of a hacker!  I had a Raspberry Pi lying around the house. I’d been looking for an excuse to mess around with push notifications on the iPhone for a while, and I thought I could rig something up that would send a push notification to our phones if he left his bedroom during the night.

Internet of things mats

Our local electronics retailer Maplin sells a pressure mat – it’s a simple switch designed to be placed under a doormat. Hooking this up to a GPIO pin on the Pi would allow me to send a notification to our phones using Apple’s Push Notification Service. Here’s the general flow:

Pressure mat -> Raspberry Pi -> Apple Servers -> our phones

Not the most complicated of hacks by any means, but if it was going to allow us to get out for a pint it would be worth it.

Setting up the Pi

I bought the Pi two years ago with the aim of doing a bit of tinkering over Christmas, before realising that, although the Pi is an amazing platform at an amazing price, it’s still a Linux box and I didn’t want to be tinkering with .conf files to get things working over my Christmas holidays. But I’d been feeling vaguely guilty that this credit-card sized wonder was sitting gathering dust while kids a quarter of my age around the world were using it to achieve amazing things.

I downloaded the Raspbian Linux distribution from the Raspberry Pi website – choosing the Raspbian Jessie Lite distribution as I didn’t need any graphical niceties. I was hoping I could just boot up the Pi and get its IP address from the local network but ping 255.255.255.255 didn’t show anything up and my Apple AirPort Extreme router, although rock solid and wonderful in many ways, doesn’t show a list of ethernet clients, so I had to hook up a HDMI cable to finish the setup. Setting a static IP was a bit of a pain as the network stack changed significantly last year and most of the links I found had outdated information, but there’s a very comprehensive thread on StackExchange which explains what you need to do to dhcpd.conf (note to self, recent distros include a zeroconf implementation so you should be able to do something like ssh pi@rasbperrypi.local and it should get you in to a Pi with a fresh OS install)

Processing a push notification with an iOS device

This was actually the easiest bit of the whole process. Apple’s docs on push notifications are really good.

Here’s the entirety of the code of the iOS app, pretty much taken verbatim from Apple’s example code:

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {
    // Override point for customization after application launch.
    // bitwise OR to include all notification types
    UIUserNotificationType types = (UIUserNotificationType) (UIUserNotificationTypeBadge |
                                                             UIUserNotificationTypeSound | UIUserNotificationTypeAlert);
    //register these notification types
    UIUserNotificationSettings *mySettings = [UIUserNotificationSettings settingsForTypes:types categories:nil];
    [[UIApplication sharedApplication] registerUserNotificationSettings:mySettings];
    // Register for remote notifications -- this will prompt the user to allow notifications
    [[UIApplication sharedApplication] registerForRemoteNotifications];
    
    return YES;
}

-(void)application:(UIApplication *)application didRegisterForRemoteNotificationsWithDeviceToken:(NSData *)deviceToken
{
    //triggered when user allows notifications
    //Log the token
    NSLog(@"device token: %@",deviceToken);
    // Also store the token in NSUserDefaults
    NSUserDefaults *defaults = [NSUserDefaults standardUserDefaults];
    [defaults setObject:deviceToken forKey:@"deviceToken"];
    [defaults synchronize];
}

I wrote the device token to NSUserDefaults in case I wanted to display it in the app, but I took the lazy way out and just printed it with an NSLog statement, so I could copy it into my Python script.

Sending a push notification with a Python script

Here’s my Python script that detects someone stepping on to the mat, and which sends a request to Apple’s APNS server:

import RPi.GPIO as GPIO
import time
import datetime
import socket
from apns import APNs, Frame, Payload

GPIO.setmode(GPIO.BCM)
GPIO.setwarnings(False)

# our pressure mat is connected to GPIO pin 18
button = 18

# setup the pin as input, and enable the pullup resister
# this means when the switch is pressed, it will read GND, i.e. False
GPIO.setup(button, GPIO.IN, GPIO.PUD_UP)

# insert the tokens you NSLogged from your iOS app here - long lines of hex
token_hex = ''
sheila_token = ''
# setting the payload here - we just want the default sound, and we don't care
# what the badge says so we'll just set it to 1 each time
payload = Payload(alert="Joe is on the move!", sound="default", badge=1)

# run in an infinite loop
while True:
    # if switch is pressed (reading False - see note on GPIO.setup() above)
    if GPIO.input(button) == False:
        #logging for debugging
        print str(datetime.datetime.now()), "button pressed"
        #create the push notification object
        apns = APNs(use_sandbox=True, cert_file='push-notification-cert.pem', key_file='push-notification-key.pem')
        time.sleep(1)
        # wrapping this in a try/except block as the script was occasionally
        # barfing on a socket error
        try:
            apns.gateway_server.send_notification(sheila_token, payload)
        except socket.error, (value, message):
            print str(datetime.datetime.now()),"Socket Error", message
            #recreate the apns object
            apns = APNs(use_sandbox=True, cert_file='push-notification-cert.pem', key_file='push-notification-key.pem')
        time.sleep(1)
        #we're done with Sheila's phone, now let's do the same for mine
        try:
            apns.gateway_server.send_notification(token_hex, payload)
        except socket.error, (value, message):
            print str(datetime.datetime.now()),"Socket Error", message
        time.sleep(10)

Apple recently changed their push notification protocol to a brand new version, which I tried to connect to using a Python HTTP/2 library called Hyper – it’s labelled as a ‘very early alpha’ and it may work very well but after a few hours of trying to create the HTTP/2 request I was still getting errors so I used a library that uses the old protocol called PyAPNS which worked fine although I had to insert a few sleep() statements to avoid some socket errors.  I also had to do some fandangling with the push notification certificate, splitting up the .p12 file into a certificate and key in Keychain Access to use with the script.

My Linux skills are a bit rusty so it took a while to remember to type screen then <CTRL-A> d to detach a separate terminal which would run even after I logged out of the SSH session.

It works!

For all the ugly hacking, it seems to work! Sheila and I can nip down for a quick pint to our local knowing that we can quickly return home if her dad starts wandering.

Further work

I spliced in an old telephone cable to connect the pressure mat outside the door with the Raspberry Pi beside my bed. I was in a rush so I used sellotape! Aside from tidying that up into something a little less ugly, here’s the things I’d like to do next with the project

1. Replace the Raspberry Pi with something like an ESP8266 module that could run off a battery, so I don’t need trailing wires. This post from Stavros Korokithakis shows how he recreated an Amazon Dash button using an ESP8266 looks promising.
2. Using the ESP8266 would mean that I would have to handle the push notification logic on an external server, so that the ESP8266 would send a HTTP request to a Flask server which would send the request to Apple.
3. Having the logic on the server means that I could log the data to a database, which means we could track how often the night interruptions were happening.
4. which reminds me I should enter some logic so that the script only does push notifications and logging at night, sometimes when we’re going in and out of his room to do washing our phones go crazy!

This evening my project was to get a generic Arduino Mega board that Baz ordered from China working with my MacBook Air running El Capitan. Plugging the board in and launching the Arduino IDE yielded nothing in Tools -> Port.

After a lot of searching, it seems like the problem is the USB to serial chip used on this Arduino clone is a CH340G chip, not the usual FTDI chip that ships with official Arduino boards.  This chip handles the communication between the USB connection and the serial interface to the Arduino board. Unfortunately it is not recognised by OSX.

There is a driver online, provided by a Chinese manufacturer, which provides a kernel extension (ktext) to recognise the chip. Unfortunately the ktext is not signed.  Previous versions of OS X would let you disable the requirement for ktext signing by typing a sudo command in Terminal, but with El Capitan you have to boot to the recovery partition to disable the System Integrity Protection (SIP) which prevents unsigned ktexts from running.

Disabling SIP is a really bad idea! Fortunately there is a company called CodeBender which produces a plugin for Firefox and Chrome that gives you an in-browser Arduino IDE. They have released a signed ktext for the CH340G chips so you don’t have to mess around with the security settings on your Mac. It’s not possible to download the drivers directly, but if you install their add-on in Firefox or Chrome (doesn’t work in Safari), you’ll get a link to install the drivers. When you next launch the Arduino IDE, you’ll see your generic Arduino listed in the Tools > Port menu.

Here’s the link to install the add-on.

 

This part came with an Arduino starter kit bought in preparation for the first Limerick Hackathon. It was really difficult to find information on the web about this part.

The part description said that this was a common Anode display, which is wrong, it’s a common cathode. This means that you need to connect resistors to the pins driving each digit (rather than each segment pin with a common anode) to avoid blowing the LEDs.

Here’s the pinout:

1	Segment E
2	Segment D
3	Decimal point
4	Segment C
5	Segment G
6	Digit 4
7	Segment B
8	Digit 3
9	Digit 2
10	Segment F
11	Segment A
12	Digit 1

Segment labels (I can never seem to remember these):

So to display a number 1 on the third digit you would power pins 4, 7 and 8.

To display numbers on all 4 digits, you need to introduce a delay in between powering each digit in your loop() block.

To test I rigged up a LM35 temperature sensor to an Arduino board and displayed the temperature on the display. The display was a bit dim as the lowest resistors I had were 1kΩ which were too high.

Here’s the quick code I wrote to read the temperature from a LM35 temperature sensor and display it on the KYX-5461AS display.

//reading temperature and outputting to display
int sensorPin = 0;

//display pins
int segA = 5;
int segB = 13;
int segC = 10;

int segD = 8;
int segE = 7;
int segF = 4;
int segG = 11;
int segPt = 9;

int d1 = 6;

int d2 = 3;
int d3 = 2;
int d4 = 12;

int delayTime = 900;

int counter = 0;

float temperature = 77.7;

void setup() {
// put your setup code here, to run once:
//start serial communications
Serial.begin(9600);

//set up outputs
pinMode(12, OUTPUT);
pinMode(11, OUTPUT);
pinMode(10, OUTPUT);
pinMode(9, OUTPUT);
pinMode(8, OUTPUT);
pinMode(7, OUTPUT);
pinMode(6, OUTPUT);
pinMode(5, OUTPUT);
pinMode(4, OUTPUT);
pinMode(3, OUTPUT);
pinMode(2, OUTPUT);
pinMode(13, OUTPUT);
pinMode(0,INPUT);
delay(1000);
}

void loop() {

// put your main code here, to run repeatedly:

//only read temp every 100 cycles
if(counter%500 == 0)
{
// read the pin
int reading = analogRead(sensorPin);
//convert reading to volts
float volts = (reading * 5.0);
volts /= 1024.0;
// Serial.print(volts);
// Serial.println(" v");
temperature = volts * 100.0;
Serial.print(temperature);
Serial.println(" degrees Celsius");

//test output to display
// allHigh();
//reset our counter
counter = 0;
}

counter ++;

selectDigit(1);
sendDigit(tens(temperature));
delayMicroseconds(delayTime);

digitalWrite(d1, HIGH);
selectDigit(2);
sendDigit(ones(temperature));
point();
delayMicroseconds(delayTime);

digitalWrite(d2, HIGH);
selectDigit(3);
sendDigit(points(temperature));
delayMicroseconds(delayTime);
//turn point off

digitalWrite(d3, HIGH);
digitalWrite(segPt,HIGH);
selectDigit(4);
cee();
delayMicroseconds(delayTime);
digitalWrite(d4, HIGH);



}

void allLow() {

digitalWrite( 13, LOW); // A
digitalWrite( 2, LOW); // B
digitalWrite( 3, LOW); // C
digitalWrite(4, LOW); // D
digitalWrite(5, LOW); // E
digitalWrite( 6, LOW); // F
digitalWrite( 7, LOW); // G
digitalWrite(8, LOW); //point
digitalWrite(9, LOW);
digitalWrite(10, LOW);
digitalWrite(11, LOW);
digitalWrite(12, LOW);

}

void allHigh() {

digitalWrite( 13, HIGH); // A
digitalWrite( 2, HIGH); // B
digitalWrite( 3, HIGH); // C
digitalWrite(4, HIGH); // D
digitalWrite(5, HIGH); // E
digitalWrite( 6, HIGH); // F
digitalWrite( 7, HIGH); // G
digitalWrite(8, HIGH); //point
digitalWrite(9, HIGH);
digitalWrite(10, HIGH);
digitalWrite(11, HIGH);
digitalWrite(12, HIGH);

}

void one()
{
digitalWrite(segA, LOW);
digitalWrite(segB, HIGH);
digitalWrite(segC, HIGH);
digitalWrite(segD, LOW);
digitalWrite(segE, LOW);
digitalWrite(segF, LOW);
digitalWrite(segG, LOW);
digitalWrite(segPt, LOW);

}
void two()
{
digitalWrite(segA, HIGH);
digitalWrite(segB, HIGH);
digitalWrite(segC, LOW);
digitalWrite(segD, HIGH);
digitalWrite(segE, HIGH);
digitalWrite(segF, LOW);
digitalWrite(segG, HIGH);
digitalWrite(segPt, LOW);

}
void three()
{
digitalWrite(segA, HIGH);
digitalWrite(segB, HIGH);
digitalWrite(segC, HIGH);
digitalWrite(segD, HIGH);
digitalWrite(segE, LOW);
digitalWrite(segF, LOW);
digitalWrite(segG, HIGH);
digitalWrite(segPt, LOW);

}
void four()
{
digitalWrite(segA, LOW);
digitalWrite(segB, HIGH);
digitalWrite(segC, HIGH);
digitalWrite(segD, LOW);
digitalWrite(segE, LOW);
digitalWrite(segF, HIGH);
digitalWrite(segG, HIGH);
digitalWrite(segPt, LOW);

}
void five()
{
digitalWrite(segA, HIGH);
digitalWrite(segB, LOW);
digitalWrite(segC, HIGH);
digitalWrite(segD, HIGH);
digitalWrite(segE, LOW);
digitalWrite(segF, HIGH);
digitalWrite(segG, HIGH);
digitalWrite(segPt, LOW);

}
void six()
{
digitalWrite(segA, HIGH);
digitalWrite(segB, LOW);
digitalWrite(segC, HIGH);
digitalWrite(segD, HIGH);
digitalWrite(segE, HIGH);
digitalWrite(segF, HIGH);
digitalWrite(segG, HIGH);
digitalWrite(segPt, LOW);

}
void seven()
{
digitalWrite(segA, HIGH);
digitalWrite(segB, HIGH);
digitalWrite(segC, HIGH);
digitalWrite(segD, LOW);
digitalWrite(segE, LOW);
digitalWrite(segF, LOW);
digitalWrite(segG, LOW);
digitalWrite(segPt, LOW);

}
void eight()
{
digitalWrite(segA, HIGH);
digitalWrite(segB, HIGH);
digitalWrite(segC, HIGH);
digitalWrite(segD, HIGH);
digitalWrite(segE, HIGH);
digitalWrite(segF, HIGH);
digitalWrite(segG, HIGH);
digitalWrite(segPt, LOW);

}
void nine()
{
digitalWrite(segA, HIGH);
digitalWrite(segB, HIGH);
digitalWrite(segC, HIGH);
digitalWrite(segD, HIGH);
digitalWrite(segE, LOW);
digitalWrite(segF, HIGH);
digitalWrite(segG, HIGH);
digitalWrite(segPt, LOW);

}
void zero()
{
digitalWrite(segA, HIGH);
digitalWrite(segB, HIGH);
digitalWrite(segC, HIGH);
digitalWrite(segD, HIGH);
digitalWrite(segE, HIGH);
digitalWrite(segF, HIGH);
digitalWrite(segG, LOW);
digitalWrite(segPt, LOW);

}

void cee()
{
digitalWrite(segA, HIGH);
digitalWrite(segB, LOW);
digitalWrite(segC, LOW);
digitalWrite(segD, HIGH);
digitalWrite(segE, HIGH);
digitalWrite(segF, HIGH);
digitalWrite(segG, LOW);
digitalWrite(segPt, LOW);
}

void point()
{
digitalWrite(segPt, HIGH);
}

void selectDigit(int d)
{
/*
digitalWrite(d1,HIGH);

digitalWrite(d2,HIGH);
digitalWrite(d3,HIGH);
digitalWrite(d4,HIGH);
*/

switch (d)
{
case 1:
digitalWrite(d1, LOW);
break;
case 2:
digitalWrite(d2, LOW);
break;
case 3:
digitalWrite(d3, LOW);
break;
default:
digitalWrite(d4, LOW);
break;
}
}

void sendDigit(int x)
{
switch(x)
{
case 1:
one();
break;
case 2:
two();
break;
case 3:
three();
break;
case 4:
four();
break;
case 5:
five();
break;
case 6:
six();
break;
case 7:
seven();
break;
case 8:
eight();
break;
case 9:
nine();
break;
case 10:
cee();
break;
default:
zero();
break;
}
}

int tens(float x)
{
float divided = x/10.0;
return (int)divided;
}

int ones(float x)
{
float divided = x - (10.0 * tens(x));
// Serial.print(divided);
// Serial.println(" ***ones***");
return (int)divided;
}

int points(float x)
{
float divided = x - (10.0 * tens(x)) - ones(x);
divided *= 10;
return (int)divided;
}