Elections: So worth it!

Posted on May 5, 2012 by Andrew Stuart

Hear ye, hear ye! Let it be known from henceforth that I love elections.

I love all the voting, and the counting, and the nerves, and the politicians making fools of themselves vying for votes from hardcore supporters, the passive public, and apathetic majority. These people support and represent every single one of their constituents, and no matter what you think of them individually, or in general, elections are exciting.

And that doesn’t stop me from working either. In fact, I work more. This year, I inputted results for the Press Association, and produced 5 maps with the help from many other people who, like me, forfeited sleep and commandeered coffee.

They are now pride of place on the Daily Post website, for Conwy, Denbighshire, Gwynedd, Flintshire, and Wrexham. Basically, there is no basically about it. They were difficult, and took a lot of prep.

I said in my previous post about this that I was going to use Google Docs. I made the spreadsheets work with a combination of formulas, so that the people who were filling them in just needed to know:

You fill in the box with the votes. It will bring the winner down to the area below, and tell you who was voted in. The majority of times that was one winner, in some cases two, in a few it was three, and in one case four.

The table worked by taking the largest number (for the majority), and in some the second, third and fourth largest numbers, and putting them into a defined area. That was then brought over to a new page, and transposed (or flipped!) into columns, not rows. Easier for the inputters to input data into columns, especially with results, but map KML files need rows. So, we make both work.

The data needed sorting (copy, paste values only, sort by ward name, delete empty rows), and then copying to a prepared clean sheet to merge.

This process took all of five minutes. The result was a clickable map that meant constituents could see who was elected and where. All, if we’re being honest here, could be done for 0 pounds. Google Drive and Google Docs are free. Election results are open and public, rightly so. The way to make it better is to have the reporters filing into it, so it can be done faster. They can provide more detail, to make it better. And, with so many people using it, each with an eye for detail, you can be sure it’s checked!

And this is one of the councils which were elected. Conwy, my home county, so the one that affects me the most:

Add 'Elections: So worth it!' to Del.icio.us

Add 'Elections: So worth it!' to FaceBook

Add 'Elections: So worth it!' to Technorati

Add 'Elections: So worth it!' to Stumble Upon

Add 'Elections: So worth it!' to SlashDot

Add 'Elections: So worth it!' to Twitter

piese auto online

piese auto

Election Night Results – How to make it go smoothly using Google Docs

Posted on May 3, 2012 by Andrew Stuart

Election nights are always fun. They last a long time (longer, indeed, than you think time can last). The candidates are always looking for their piles. The agents with the clipboards are ensuring democracy is done right on their candidate alone. You, meanwhile, are just tired that you’ve been waiting around all night, and there is another recount.

And that’s just if you’re at the count. At the office, you you’ll need coffee to stay awake, so maths is the least of your worries. So help inputting results wouldn’t go amiss.

I (hopefully) have created a bulletproof way of inputting results for the Daily Post, with a little help from my friends. They gave me a few pointers with formulas. I did the rest.

North Wales has five counties up for election. 243 wards. Over 700 people being elected. That’s a lot of numbers. So, how do we input them?

First, I split all the counties up. Five Google Doc spreadsheets. This way, it doesn’t get too confusing. All the wards are in columns, with a space next to it to input the result. Scroll across, input result. Easy.

Then, we need to know who won. So, seeing as the most votes wins, you pull the result down to another cell. Not easy, when numbers are all over the place. So, I needed a formula. With a bit of help from @PaulBradshaw, a Vlookup formula would work. It would pull the top candidate down. Great, for most of them. It, however, would require the results to be inputted on the left.

So, @aendrew pointed me towards an idea that looks amazing. I decided to use a =large formula to rate the candidates. =large(c2:c12,1) would pull the largest number (for example) from that range down to the specified cell where the formula was entered. Great. You can add a 2, 3, or anything up to however many numbers there. You want the 50th in a series of 100? Just select the range, and type 50 at the end.

Now, we need a name. In this case, =index(B2:B12,match(B15,C2:C12, 0)). This would take the value in cell B15 – where the largest number is – and match it with the number in cells C2 to C12, where the results were inputted. It will find the exact match, and then look across at the cell next to it, in B2 to B12, and bring the candidate name down to cell B14, where the formula was entered.

Now, we have the candidate name on one cell, and the total winning votes on the cell directly below. Repeat that if two or three, or four, councillors are being elected. Easily done.

But, you can’t do anything with that data. Yes, you can’t. So, you need to flip it. Looking at that way is easier to input, which is the aim of this spreadsheet. Looking at it another way is easier to collate with other data sources. So, we need another formula.

=transpose(’1: ENTER RESULTS HERE’!A13:BX22)

This will take everything in the cell ranges A13:BX22 (where the results will be sorted and brought down to by the other formulas), move it to another sheet, and transpose them. That is, flip them around. You end up, that way, with columns of numbers that are easier to work with when making maps, and results. Nothing needs to doing to this, apart from selecting all the data, copying, and pasting as values only to the next sheet. Taking the formulas will ruin it, once you see what we’re going to do.

On the third sheet, we will sort the name column, where the ward names are, in A-Z format. Get rid of the spaces with the extra #num rows, and copy to the next workbook, called Conwy Results TO MERGE. So called, because you need a clean workbook to merge with Fusion Tables, which is exactly what this will do.

You put the data only into the workbook, which already has the county, in this case Conwy, repeated down the side many times, and the headings. These are uniform for a reason.

This table just needs the data copying to, then closing. Open Fusion Tables, make a new table from Google Spreadsheet, select the relevant TO MERGE sheet, and then merge that with the KML file of the county.

What we’re going to do is use these headings to write the info box code, so it works for 1 person elected or 4. The code is:

<div class=’googft-info-window’ style=’font-family: sans-serif’>
This is {Ward Name} in {County} 
Winning this ward was: 
{Winner 1} ({Votes 1}) 
{Winner 2} ({Votes 2}) 
{Winner 3} ({Votes 3}) 
{Winner 4} ({Votes 4}) 

This doesn’t leave any marks on it through typing commas. It works by using the cells as the text. If there’s nothing in the cells, nothing will appear in the info box. Clean, and at the same time, it makes nice prose on the top: This is “Ward Name” in “County” will read, for one ward at least: “This is Gogarth in Conwy.”

The map will then have all the details you need to see how the county voted on May 3rd.

I will update with the winning maps – suffice to say, this is the way to do work if you know you’re going to get data, but don’t know the figures. Easy to just drop it in, then spend 5 minutes copying, pasting, and merging. All the hard work has been done beforehand.

Add 'Election Night Results – How to make it go smoothly using Google Docs' to Del.icio.us

Add 'Election Night Results – How to make it go smoothly using Google Docs' to digg

Add 'Election Night Results – How to make it go smoothly using Google Docs' to FaceBook

Add 'Election Night Results – How to make it go smoothly using Google Docs' to Technorati

Add 'Election Night Results – How to make it go smoothly using Google Docs' to Stumble Upon

Add 'Election Night Results – How to make it go smoothly using Google Docs' to reddit

Add 'Election Night Results – How to make it go smoothly using Google Docs' to SlashDot

Add 'Election Night Results – How to make it go smoothly using Google Docs' to Twitter

piese auto online

piese auto

Waiting for an Ambulance in North Wales? Find the figures here

Posted on May 2, 2012 by Andrew Stuart

Of all things, an Ambulance is the one thing you would want to turn up without delay. That goes without saying.

It’s also the one story that comes out every month, with new stats, that mean a lot to a lot of people.

How long you wait for an Ambulance can literally mean the difference between life and death in some cases. In others, it can mean suffering or prolonged agony. In any case, genuine reasons to call an ambulance mean the person needs medical attention quite quickly.

So, how do we measure the data for the public to dissect, and tell the story in a way that matters to them, and at the same time, help any future journalists who are going to come back to the story with future figures.

Easy – you make charts.

This chart shows how the percentage of ambulances which managed to arrive at the scene of the incident within 8 minutes. 8 minutes is the target figure, and the headline figure. Class A emergencies are generally where somebodies life is in serious risk. It is vitally important an ambulance turns up quickly, with a paramedic in tow.

As you can see from the chart, it shows the different areas of North Wales. The ambulances are all from the North Wales Ambulance Services Trust.

The big red bit is North Wales as a whole. The other lines are for the different counties. You can hover over the line and it will tell you the details. This way, you can see who is above and below the North Wales average. Click it, and find out.

To get this data, it’s all freely available as a news release from the Welsh Government news service. It’s not up-to-date, but it’s a release. It’s good. We would love up-to-date stats, but that’s a story for another day though.

So, if you take a look, you see charts of things in PDF format. The evil file format, as it’s more commonly known. Good for locking things down. We know that small charts mean you can’t exactly see all the data though. So, we need to get it ourselves.

Luckily, Wales has a resource for this. It’s called StatsWales, and houses historical data on many things. The one thing we want is the last few years of how the ambulances have been doing.

Whilst we’re there, though, why not pick up some context. How many calls did each area put through to get a response rate like that?

This way, stacked up on each other, you can see which county made the most calls, and which month was the quietest. You can also see which month was the busiest. So, if you were going to move to North Wales, and may have need to call an ambulance to get their quickly, where should you move to? This data is all important. Click it and find out.

The result of this data, done online with Google docs, and worked through by journalists with access to the numbers? An informed piece in the Daily Post.