Management information specialist and Excel detective
Posts tagged open data
In which I use Excel to go Royal Wedding crazy
Apr 28th
I’m not sure, is it actually the law that every blog or press release today has to include references to the Royal Wedding? According to the media the country – if not the world – is currently gripped by Royal Wedding fever. Whilst I’m not sure that I’m “gripped” I am enjoying some of the tenuous press releases on the subject (Dunkin’ Donuts is a particular favourite) so thought I’d add to the mix by a quick analysis of the guest list.
The Guardian’s datablog has put together a spreadsheet based on the snapshot of the guest list released so far. It’s only a few hundred of the 1900 guests and is a simple file with a single field containing the person invited such as “Prime Minister of Saint Vincent and Grenadines and Mrs. Ralph Gonsalves”. I wanted to pull out the titles of those invited, however as you can see it’s not as simple as pulling it out from a single field as the information is mixed in with other data.
Armed with nothing more than a biscuit and a quick bit of excel magic I did some analysis to pull out the titles of both guests and it turns out that of the published guests just over a quarter (27%) have the title of Mr, Mrs, Miss or Ms. Looking to those with aristocratic titles the largest group is the 24 answering to “Lady”, followed by 13 Governor Generals and a dozen princesses. In fact our own Queen is just one of 6 who can answer to that title, with an equal number of Kings and Arch Bishops.
The spread of titles is something like in the chart below, although it’s worth bearing in mind that this is where it’s important to work closely with the client. For example some titles could fall into two or more categories, and as I’m not up to date with the latest information in Debretts I would always discuss carefully to make sure that any analysis rules are defined at the outset.
Whilst Buckingham Palace have not (yet) called upon my skills as an excel detective, pulling out crucial information tucked away in a single field is often needed. It may be that you have a set of address data and need to pull out some specific information that’s embedded within the fields themselves. Or perhaps you’ve a set of data with order numbers or part names mixed up and need them analysed. Rather than sitting down and spending a few hours trawling through manually there are a host of ways you can pull the data out automatically using Excel or other tools, and I can either do this on your behalf, or work with you and your teams to train them on the functions that they need.
Enjoy the long weekend, I’m off to go and find one of those doughnuts
So what was Shropcamp?
Apr 20th
You can barely move on twitter the last 24 hours for tweets about Shropcamp but what was it? It was billed as an afternoon to discuss “How can we use social media and open data to help service providers to work more effectively at a local level?” But what was it?
It wasn’t a conference
This was an unconference. There was no pre designed agenda, no keynote speakers, no wading through handouts. That said we did get goodie bags (all hail the sponsors MoreOpen, Penval, Beyond Brilliance and UKITA) and arrived to a rather splendid buffet lunch at Harper Adams. Ben Proctor, the dynamo behind the day, briefly welcomed us and then handed over to Andy Mabbett to draw out ideas from the group for workshops. Those interested in presenting – or who had something they wanted to hear about – came up to do a 30 second pitch for the idea and after some discussion the agenda was formed
Shropcamp Agenda (Pic courtesy of www.flickr.com/photos/likeaword/)
It wasn’t full of geeks
It wasn’t full of public sector workers
Naively I’d presumed that the room would be brimming with programmer types, and that those who weren’t hackers or developers would be solely from the public sector but there was a wide range. Perhaps it’s because of the blurring between the lines of social media, technology and open data but there were few hard core hackers around, instead it was a blend of private and public sector, suited businesspeople and jeans and t-shirts, voluntary groups and social enterprises, parish councillors and self-admitted disruptors. There were marketers and web designers, social media experts and press officers. There was not a spreadsheet in sight, this was about new ways of working with data, using it to enhance local services, inform local decision making and supporting communities.
It wasn’t a social media techfest
Admittedly the lack of tweeting was more down to the lack of wifi access for a number of attendees, but whilst social media was discussed it wasn’t pushed to the nth degree. I saw only 2 QR codes (one on my own business card) and discussion was more around how to use these tools for communication rather than waggling the latest technology. (Edited to add that I understand I missed the techy workshop on Social Gaming and QR codes)
So what was it?
As ever it was what you made of it. There was debate, there were interesting discussions, some really interesting initiatives. It was a refreshing change to see people mixing from such different environments and in a relaxed yet energetic way (aided by the glorious sunshine). Personally I would have liked to see more debate as some of the workshops were still slightly too presentation reliant, and would have liked a few rounds of introductions somehow (not stuffy stand ups but some way of knowing who shared your particular interests)
A few of us were discussing Shropcamp at the Coalport Jelly this morning, when Nick Deane pointed the bottom line was that the afternoon was about communication. He’s right I think, when it comes down to it yesterday was about communication and facilitating information sharing. It felt like a vibrant and exciting afternoon with ideas a-plenty, I just hope that some, just some of these ideas grow as a more fertile and friendly environment would be hard to have found.