Validating the BBC's statement that “England has had the wettest January since records began in 1766”

[Former Member]

Working at home with the rain pounding on the window and iTunes shuffle brings up Storm by Vanessa Mae, random, coincidental or inspirational?  Well for me it brought together an article I have read about in the UK news about the weather we are experiencing this winter and a tweet by @steverumsby this morning.

http://www.bbc.co.uk/news/magazine-26172688

So the hunt was on to find UK rainfall statistics for as many years as I can.   Thankfully the UK Met Office has a fairly open view to data and historical climate data was easy to find.  http://www.metoffice.gov.uk/datapoint/product/regional-climate

The data download looks fine to identify spot numbers but I needed a way to easily visualise it in a way to validate the statement in the BBC article that “England has had the wettest January since records began in 1766”

Firstly I did a bit of “Quick Analysis” using MSOffice 2013

HHmmm   The red dot which identifies the High point is not on the last record, 2014.  So is the BBC wrong ?

Alas no, the dataset is for the whole of the UK and not just for England.  Try again….

Yep the high point for Jan is in 2014, so in this data set Jan 2014 has the highest rain fall since 1910.

Now I wanted to analyse this further as this data brought on loads of other ideas and hunches to validate on the wider UK dataset.

I wanted to use SAP Lumira but the shape of the data as a crosstab (pivot) table wouldn't work well in SAP Lumira so I needed to flatten it into a columnar format.  Easy you’d think, alas no, well not for me anyway.

I resorted to google and found a small piece of VBA to do a large portion of the work.

The VBA on transpose crosstab table to list:

Sub ConvertTableToList()
Const TEST_COLUMN As String = "A"
Dim i As Long, j As Long
Dim iLastRow As Long
Dim iLastCol As Long
Application.ScreenUpdating = False
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 2 Step -1
iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = iLastCol To 3 Step -1
.Rows(i + 1).Insert
.Cells(i + 1, 2).Value = .Cells(i, j).Value
.Cells(i, j).Value = ""
Next j
Next i
.Rows(1).Delete
End With
Application.ScreenUpdating = True
End Sub

http://www.extendoffice.com/documents/excel/705-excel-convert-table-to-list.html

With a little more effort in Excel I now had a good data table to be used.

After importing the data into SAP Lumira, I simply plotted the Year and Monthly rainfall figures.

Spot the data quality issue? 

Whilst working in excel I had labelled the year 1923 against the records for 1923 and 1924.  A great example of why visualising data draws out things you’d miss in a 1250 row table. After fixing the data in Excel it was easy to refresh the visualisation in SAP Lumira.

Adding on the predictive calculations really didn’t give much hope for lower rainfall for the next 10 years…

You can download the SAP Lumira file here:

Dropbox - UK Rainfall .lums