Conwy Councillors – Who is yours? Local data mapped.

The need for public data from councils is getting ever so much more important.

I live in two areas: Conwy, in Wales, and Islington, North London. Both areas, I have worked in journalism, and the need to be able to access councillors details very quickly is highly important.

But, we shouldn’t forget the main stay of a councillors work is to be able to communicate with constituents, and vica-versa. The ability to find councillors details, and even, who your councillor is, is important. But, if you spot a problem or need to talk about an issue with someone who isn’t in your ward, you have problems.

1) What’s the ward called?
2) Where is that ward?
3) Who’s the councillor?

You may know where the issue is, but you may not know what it’s called. How do you report it the councillor to change it if the information isn’t known? I decided to make it simple with a map.

Using data from OpenlyLocal and My Society Mapit, and cleaning and fusing them with a combination of Google Refine, Microsoft Excel and Google Fusion Tables, I created this. It lists all the councillors for Conwy, their ward name, and contact details.

I should add that writing this on March 31, 2012, it is only valid until May 3rd, 2012, when all the seats are contested. I’ll do an updated one after the election.

I’ll also do a full data post about this to explain how it was done at a later point.

Behind the data: Ogwen Valley Mountain Rescue Team

Earlier this year, I published on my blog data from the Ogwen Valley Rescue Mountain Team.

The Ogwen Valley Mountain Rescue Team, based in North Wales, are an emergency service for those in trouble in the mountains. They cover the most northerly parts of the Snowdonia mountain range, including Snowdon, at 1,058m high. They work 24/7, 365 days a year on callout, and can be called for anything at anytime. On top of it all, they are volunteers – willingly giving up their time in what tend to be bad conditions to go mountaineering.

They publish data on their website going back to 1961, showing how the services of the mountain rescue team have been used over the years, and what sort of injuries, if any, were sustained. The data, just from looking at it, shows an increase over the years – so I decided to visualise it and see just exactly what it shows.

The data they have available – up to 2007 – is comprehensive. The data after that I have to work with from the website. I emailed the press officer for the group, a very helpful man called Chris Lloyd I know from working in media around North Wales. He filled me in on some of the more recent injury data, and I managed to gather the rest of the data on how many people were involved in incidents from the detailed incident pages.

The Process:

I used OutWit Hub, the extension for Firefox, to grab the main data on the page and put it into an excel format to work with. Simple process that takes all of a few seconds, and saves messing around with formatting it. Then I manually inputted the remaining data to the spreadsheet when I had the information.

To actually visualise it, i looked through a few options. My first idea was to see if some sort of Google gadget would work to show the data over time – turned out it didn’t. I used, in the end, a chart with a mixture of lines and bars to show the data callouts since 1961. The bars – a lighter colour, so all the lines could still be seen effectively, was the total number of callouts. The lines were split into how many people were injured and uninjured, and on top of the bars. The reader can hover over each bar or line and find the specific data, and at the same time see the trend over time.

The chart was designed to see if there was a correlation between people calling out the rescue services unnecessarily or without much cause, and if that linked with my general belief and an understanding over time working in the media that the services are being called out more frequently than need be. The above chart only shows so much data in that regard – for that, I needed to break the stats down into why people were rescued.

For this, Many Eyes was the only tool I could think of that would show the data properly over time. I copied the data to Many Eyes, and produced a tree diagram, which enables the viewer to select which year they wanted to see, and how the type of injuries looks compared to the total of the injuries in that year.

I also produced a chart which enabled the reader to compare two years and see if there is a significant change in the type of injury, or if there were people taken off the mountains with no injury. This, linked with the fist chart of the change over time, helps to answer the question “are the mountain rescue being stretched with unnecessary rescues?”.

Data isn’t difficult, as I said on Twitter, and a bit of analysis and exploring it can really help to answer some questions, and show data a lot better than a simple chart.

Behind the Data: The Doomsday Clock

In January, I published a piece for the Guardian Datablog about the Doomsday clock being moved closer towards midnight. The clock is a check on how close the world is towards destruction – not literally a clock, but rather a way of analysing the political, environmental and military factors that could start a chain of events that would lead the supposed destruction of the world, at least in part or in whole.

I wrote the article The Guardian, and published the interactive graphic to show how the clock has changed over time:

The process

The challenge with any data is to put it into context. With this, the Bulletin of Atomic Scientists have a website that lists all the times the clock has changed, and what they Bulletin said about the change at the time. This was helpful to provide context to the story; As the chart shows, there have only been four instances where the time to midnight, or total destruction, was closer, and one where it was equal to the five minutes to midnight. Equally, the time in the past few years has gone back down to levels previously seen in the early 50s and the mid 80s, from the time after the end of the Cold War and the end of the Cuban missile crisis, where the clock reached a height of 17 minutes away from midnight in 1991.

Very quickly, and in a small piece of the page, that information can be imparted. The dates can also be compared with whatever the read wants, therefore justifying and making the interactive chart an excellent idea.

The chart itself is a Google trends chart from the Google Docs spreadsheet, and features a scrollable timeline with the years, an expanded timeline to show the changes with more detail, and the explanation data on the side of the chart, so the reader can click on it and find out more information.

To get the information for the dates, I needed to find out when the clock was published for the Google chart to work properly – it wouldn’t display the data in simple year form. To this end, I utilised a Google Books search to find the front covers of the magazine over the years. This was quicker (at 10am in the morning) to find the data than to contact the bulletin direct and ask them to provide me with the exact date, who are based in Chicago, Illinois – a 6 hour time difference away.

Google books would be just as accurate, in any case, as the original front cover with publication date was shown. 15 minutes (if that) of scrolling through and noting the publication date was preferable to the 5 hours of waiting for the bulletin to open, then a few hours on top of that for them to gather the info I needed, call me and email it through.

Further to the Google Doc requiring a date that wasn’t just a year – it also required a date, not just a month. In that instance, I substituted the first date of the month, which put the date in the correct format, and allowed the chart to work.

The spreadsheet behind it ended up looking like this:

The explanation, in the third column, was visible on the final chart, the date in the correct format helped to make the chart work, and and the minutes to midnight was a figure to scroll. Manipulating the data and bringing different sources together worked.

At the end of it I was left with a chart and story with, as of today, 182 tweet shares, 143 Facebook shares, 14 comments generally positive, and an email from the bulletin praising the way it was written and how well I understood the data.

Ogwen Valley Mountain Rescue: Data with Interactive Charts

Ogwen Valley from the Glyders | Photo: Phil Rogers (erase) on Flickr

Ogwen Valley Mountain Rescue team are one of the teams of volunteers that respond to 999 calls within the mountains of the Ogwen Valley – including North West Wales from Llandudno onwards.

Their website states the dangers of the mountains they respond to incidents in and try and keep safe:

The mountains that surround the valley are about 1000 metres high and the terrain varies quite considerably. With eleven of the Welsh 3000-feet peaks and cliffs that reach about 300-400 metres in height our area is very popular with both walkers and climbers.

They publish data on the website going back to 1961. I decided to map the data with injured and non-injured patients separated to see just how often the team give up their day-to-day life to go risking their safety for those in the dangerous and unpredictable weather of Snowdonia.

The chart below shows the dramatic increase in incidents over the past few years, which in turn takes up more man-hours and costing more in fuel for the vehicles and equipment used.

I am waiting for data for the past four years to show the injured and non-injured, but accurate data up to 2011 is available for call outs. Recent data has not yet been collated correctly so would be unfair to guesstimate from what’s available through the OVMRT.

Hover over each point or bar to see the data for this chart.

Below, this chart shows how many call outs there were and how many people were involved in it. The number tends to rise steadily with the number of call outs. Indeed, the average tends to be between 1 and 2 people per call out. The majority of cases will be just one person. Click interact to see the chart properly.

The different types of injuries sustained are interesting to see. As the years move on, more and more people are not actually injured, rather they are lost. This may be attributed to more people being on the mountains, or more people without a map or a lack of orientation skills. The total amount of incidents rising in recent years does point to an increase in people climbing these mountains not being prepared enough.

It could be attributed to a rise in people attempting to become fitter and healthier, and in the process it is natural that more call outs will be needed, as people have accidents. It can also be linked to the rise in mobile phone use, and the ease of which emergency services can be contacted. That in itself is a good thing – but used badly. The emergency services reaching stranded rescuers are volunteers in the vast majority of mountain rescue cases.

They may be reimbursed petrol money, but a recent report into the cost of the volunteers says they shelled out thousands of their own pounds on vehicle maintenance. If the RAF needs to be called out for search and rescue, thats a expensive.

If the RAF need to attempt a landing or winch rescue in the mountains, with unpredictable winds and low visibility, that is also challenging and dangerous. The injuries sustained by people, ordered by year (1961-2007) and also including one page for total injuries, is below.

And here, you can compare two years worth of data to see how it compares for that year.

All the data used for this does not outright suggest any of the reasons I have mentioned in the text. It is simply gleamed from the Ogwen Valley Mountain Rescue team website, and visualised here for you to see. I would be interested to know what you make of this.