Glen Gilchrist Male Newport, Gwent United Kingdom: Like

0 members like this; Share on Facebook

Share on Facebook MySpace Tweet

Blog Posts
Discussions (2)
Groups - Find a Special-Interest Group or Start Your Own (1)
Photos
Photo Albums
Videos

Glen Gilchrist's Colleagues

View All

Glen Gilchrist's Groups

Distance Collaborations

628 members
123 Comments 19 Likes

View All

Glen Gilchrist's Page

Glen Gilchrist

Simpsons Diversity Index

batchgeo.com/map/c9ad46984163a39fb84a6d80560fdefa" frameborder="0" width="100%" height="550" style="border:1px solid #aaa;">

Using JSON to pull web data

Update: 9/12/2016 WG have changed the JSON schema used - the technique still works fine, but the JSON path details have changed.

=JsonPathOnUrl(K3,"schName") - now pulls the school name for example.

AND - I am finding http://jsonformatter.org/ as a better JSON viewer.

My Local School is useful if you want to look at a particular school in detail, but what the site explicitly does not do is allow you to compare schools or to extract the data so that you can on process it (with some Excel-fu).

Fortunately, the site is standards based and uses JSON to pull data from their back end server. This means that once we discover what Urls the site calls, we can execute those JSON calls and pull the data straight into Excel.

As I posted before, the json URLs are of the form http://mylocalschool.wales.gov.uk/schools/6734067.json where the 6734067 is the unique school number. (This you should know, however the Welsh Government publish a list of all schools in Wales - so you can download and get them all).

Viewing the JSON

If you follow the URL above you will see:

Clearly data – and we can just about make out that there is some order to it.

{“school":{“basicDetails":{“name": “St Cyres School" …….etc

It’s not much of a leap to consider these as field names and data in a data base. And indeed that’s sort of, kinda what JSON is.

We can do better than that – and load the JSON into a specialist viewer – (http://jsonviewer.stack.hu/) – in this case, our URL is: http://jsonviewer.stack.hu/#http://mylocalschool.wales.gov.uk/schools/6734067.json - just like before we can change the school number if needed:

Using this JSON viewer you can begin to see the structure of the file. Clicking on the top left viewer option and the JSON changes to a TREE VIEW:

Where we can see {}JSON, {}School – the {} braces incicate what is called a JSON node.

For example, here we can see {}JSON.school.pupilPopulation.pupils.all - leads us to data for the school population.

0:154, 1:181 etc up to 7:1184

Under {}pupils the all node has square brackets [] indicating that this is an array. The first element in the array is 0 and corresponds to year 7 (as this is a secondary school). The final array entry [7] = 1184, which is the total for the entire school.

Some schools will have extra nodes, corresponding to Year 14 or for 3-18 schools, additional years. Importantly, the final array element is always the total for the school.

To select a particular nodal element, we enclose it in square brackets, so:

{}JSON.school.pupilPopulation.pupils.all[6] selects array element 6 or 98 in this case.

JSON in Excel

There are a number of ways of getting JSON data in Excel (and particularly for Excel 2013) – for me, the easiest and most reliable way is to install the (free) splendid add-in SeoTools (marketed for webmasters).

When installed, SeoTool adds some extra functions to Excel. The function we want is ,JsonPathOnUrl() – which literally reaches out to a url and pulls in the data.

So, =JsonPathOnUrl(http://mylocalschool.wales.gov.uk/schools/6734067.json,"$school.pupilPopulation.pupils.all[5]“) reaches out the url and pulls in all[5] – in this case, 122 into a cell.

If we change the URL to a cell reference, we can build a sheet that has a URL for every school in Wales, and pulls in what ever data we want for each school.

Accessing the last element in an array

Getting nodal data is easy – .all[5] for example. But for certain data we want the LAST element. This is tricky and some Excel-fu is needed.

With the data for the .all array in J10, (called by =JsonPathOnUrl(D10,"$school.pupilPopulation.pupils.all") (D10 contains the URL for the JSON file).

J10 will contain (in my case) -

“[10, 12, 14, 12, 21, 19, 27, 25, 55, 58, 55, 57, 57, 46, 32, 500]" – the [] indicating an array.

This formula extracts the last element in array J10:

Q10 =MID(J10,FIND(“@",SUBSTITUTE(J10,",","@",LEN(J10)-LEN(SUBSTITUTE(J10,",",""))))+1,99)

Q10 = 500]

We now need to remove the trialling ] with:

R10=MID(Q10,1,LEN(Q10)-1)

R10=500

S10=CLEAN(R10)

S10=500

This is the total number of pupils in the school and is contained in the [].all array as the last element.

Moving forward

Using this technique it is possible to build an Excel sheet that pulls from My Local School ALL the data for every school in Wales (primary, secondary, special, nursery) – all leveraged using the power of JSON and some Excel-fu.

If any reader would like an example Excel sheet – pop a comment below and I’ll tidy up my working code.

R for teachers - WJEC funding from Welsh Government

I've wanted to learn "R" for a long time - the idea of a command line driven (with GUI), open source (ie free) statistical programming language appeals to me. As a long time Minitab user, the output is kind of sucky - difficult to customize charts and the quality of export could be better. R on the other hand is free, infinitely expandable and designed to produce some exceptionally high quality output. Time to learn R.

Getting R is easy:

(1) Download R - https://cran.r-project.org/ - then install it

(2) Download RStudio (provides the development environment - a nice front end) - https://www.rstudio.com/products/rstudio/ - then install it.

Quite easy really.

Then you hit the wall. You see R is not Excel - that means that its not a spreadsheet. Which means the learning curve is steep and yes, it's a programming language.

(3) Buy a book to help (R for Everyone) - well worth the ££

(4) Surf the Interweb for advice.

Using any tool like this needs data - so I moseyed on over to http://gov.wales/about/civilservice/how-we-work/facts-figures/ourfinance/expenditure-over-25k/?lang=en to find data on expenditure over £25K. Being in education, I looked for data on the WJEC.

Some R magic later (plus furious Googling) and I had created:

Nothing special in that chart - could have created it in Excel far quicker. The learning point being that in R, this chart is reporesented by the code it took to create it:

ggplot (WJEC_Year,aes(x=factor(Year),y=Amount))+geom_bar(stat="identity")+labs(title="Welsh Government Funding to WJEC",subtitle="from: http://gov.wales/about/civilservice/how-we-work/facts-figures/ourfinance/expenditure-over-25k/?lang=en",y="Amount(Million)",x="Year";) +scale_y_continuous(labels=function(x)x/1000000)+ theme(panel.grid.major = element_blank(), panel.grid.minor = element_blank(),panel.background = element_blank(), axis.line = element_line(colour = "black"))

All in one line and that looks very complex (and it is to me too) - but the beauty of this approach is that tweaking the chart is instant and very obvious. For example to change the fill of the bars to RED, you modify the code:

ggplot (WJEC_Year,aes(x=factor(Year),y=Amount))+geom_bar(stat="identity", fill="red")+labs(title="Welsh Government Funding to WJEC",subtitle="from: http://gov.wales/about/civilservice/how-we-work/facts-figures/ourfinance/expenditure-over-25k/?lang=en",y="Amount(Million)",x="Year";) +scale_y_continuous(labels=function(x)x/1000000)+ theme(panel.grid.major = element_blank(), panel.grid.minor = element_blank(),panel.background = element_blank(), axis.line = element_line(colour = "black"))

I have added in the instruction fill=red - to the command to draw the chart.

This flexibility and control appeals to me.

Christmas holiday hobby - learning R!

Calculating the transmission of "optically clear" microscope slides

I blogged recently about turning your smartphone into a colorimeter to determine the concentration of an unknown sample using the excellent Physics Toolbox Suite from https://www.vieyrasoftware.net/ and that got me thinking about other uses for a smartphone to measure incident light.

There used to a physics practical that A-level (post 16) students carried out with microscope slides. Measure the light transmitted by a range of stacked slides, plot a graph and determine the number of slides in a "bundle" without counting them. All standard stuff - the problem with that practical was that it was really "physics for the sake of it" - the practical itself had no real intrinsic value. But what if we could measure something that we could look up on a data sheet? Could we use the sensor as a portable QA device?

According to the data sheet for my microscope slides the optical transmission of a single 1mm slide was 91.2% ±1% - could my students quality assure that figure?

In a similar manner to the post on colorimeters (here) microscope slides were progressively stacked over the light sensor and Physics Tooolbox Suite was used to measure the light transmitted by the stack. A graph was plotted from 0 to 15 slides and Excel was used to calculate a 3rd order polynomial line of best fit (R² = 0.9998 - so a pretty excellent fit). From this, the equation of the line was used to calculate the transmission of 1 slide - in this case 2646.435 lux. Using the fitted value for 0 slides (2884.8) the ratio T₁/T₀ was calculated - 2646.435/2884.8 = 91.74%

This is well within the ±1% of 91.2% - my slides are in tolerance and pass the QA test.

Feedback from the students was positive - they particularly liked the fact that they were solving a "real" problem, one that felt authentic to them and not contrived by the teacher to demonstrate a technique. It was particularly satisfying to see a range of mobile phones being used for this experiment - providing 1-to-1 access to sensors that would have been impossible had we used the equipment available in the school laboratory.

Attendance data

I came across this chart on the Interweb recently (copied exactly as shown on a school website):

It's not clear what story this chart is attempting to tell, but I think it's safe to assume that the purpose of the visualisation is to demonstrate those form groups with the lowest attendance and to allow some form of comparison across a year group and across the whole school. I think the chart fails in a number of ways.

The chart starts at 70% - this magnifies the visual perception of the difference between any two forms -- the chart is stretched vertically and this distorts our visual understanding of the data
Whilst it is possible to pick out the form (10PW) with the lowest attendance overall, it is not clear how this relates to other Year 10 classes or overall
The x-axis contains categoric data
There is no context for this chart - we dont know what a "good" or "target" value is.

Some Excel-fu later and you can transform that chart into:

More…

Profile Information

School / Work Affiliation: Subject Team Leader

Blog: http://glengilchrist.co.uk

Website: http://glengilchrist.co.uk

Twitter / Plurk / Other Account: @mrgpg

About Me: My potted history: ”When I did my degree (1999), I did a concurrent PGCE – I wanted to be a teacher so badly – (probably something to do with my Physics teacher – Wendy Kidd, hello) – but I was sponsored by AvON Rubber, so took the easy option after graduation and went to work for them as a research scientist.
After a couple of years, the allure of all things technical got me and I left to work with a company making 30 pin simms (when 1Mb of memory was quite sufficient). I did the technical training and learned how to Spin Sell - something that as a teacher I still come back to.

Having some money behind me, I left to set up my own business selling flash memory for the emerging digital camera market. I did the web stuff and took care of the technical side of things. But I had a secret.
I wanted to teach in a classroom.
I returned to teaching in 2004, and am currently Head of Science at Newport High School – a position and school that I thoroughly love. The position, as it allows me to directly influence the educational direction of a whole institution, plus enjoying the learners in the classroom AND the school / senior leadership for providing an inspirational and supportive place to work.
Personally, I want to cut through all the layers of junk that we are obliged to teach these days and to find a way of connecting with our young people that actually makes a long term difference to their lives.
Sounds like a mission statement or a manifesto promise doesn't it?