Posts tagged data analysis
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