With food hygiene in the news following the horsemeat scandal, I thought I’d put together a quick guide on getting information about your own local schools’ hygiene ratings.
You can download FSA ratings of food hygiene for each local authority. This includes schools, after school clubs, and other childcare providers.
However, the downloads are in XML format, so you’ll need to first convert it to a spreadsheet. There are online tools to help you do this and you can also use the free data cleaning tool Google Refine.
Once you’ve done that, you’ll need to drill down to the schools.
Finding the schools
Open the spreadsheet. Create a new column, called ‘Schools?’ – this is best done between the current columns C and D (I’ll explain why).
In the first cell under that heading, type this formula:
=COUNTIF(G2, “*school*”)
When you press Enter, this will give you ‘1’ if the named cell (G2) contains ‘school’ and 0 if it doesn’t.
Why? The * is a wildcard: it means ‘zero or more of any character’. So “*school*” translated into computer-speak means “any character, followed by the string of characters ‘school’, followed by any character”.
Now copy that down the whole column – the quickest way to do this is:
- Select the cell with that formula (don’t double-click, just a single click to select)
- Hover over the bottom right corner until the cursor turns to a black cross.
- When it’s a black cross, double-click. This will copy that formula down the whole column until there’s a blank cell to the left (this is why we put this column after column C, which is always full)
You can now sort this data (Data > Sort) so that the 1s come to the top and you can copy that data into another sheet to analyse, map etc.
PS: You can repeat this for the column H (the column that was G before you added a new one) so that you find any mentions in the address (it’s not always in the business name column)
Other filters
Of course, names or addresses with ‘school’ in them might include ‘after school club’ or even ‘Schoolhouse Road’, so you might want to filter it further.
For example, you could repeat the process above to find any mention of ‘after school’ or ‘club’, or omit the second asterisk so that your formula only finds names that end in ‘*school’ without any characters following.
You can also select the ‘business type’ column and use Data > Filter… and then select only ‘Hospitals/Childcare/Caring Premises’.
What next?
Once you’ve identified the schools, there are a number of things you might do. Here are just some:
- Sort the data by ‘Rating value’ (the score). Which ones score below 3 (Satisfactory)?
- What proportion of after school clubs or schools score below ‘satisfactory’?
- Speak to the organisation that was rated. Will they tell you what the problems were, what caused them – and what has been done since?
- You might also use WhatDoTheyKnow to send an FOI request for specific reports. Does this back up what the organisation said?
If you do use these tips, let us know how you get on.