A bit of code for all you Excel fans out there…

OK, so I’m not a programmer in any way, but I’ve started to dabble a little bit in VBA. Working with migration origin/destination data, one job which is a perennial pain in the backside is converting matrix data into a paired list format. Therefore this morning I have written a little bit of VBA which you can run in any Excel spreadsheet to convert matrices to paired lists. Hope you find it useful – just copy and paste the text below into a new VBA module in your spreadsheet … 🙂
Sub MatrixToPairs()
'create a variable called dataFile and set it to equal the Excel file you have open
    Set dataFile = ActiveWorkbook
'count the number of columns with data in
    COLUMN = 1
    Do Until dataFile.Worksheets(1).Cells(1, COLUMN) = "" 'do until you reach an empty cell
        COLUMN = COLUMN + 1
'count the number of rows with data in
    ROW = 1
    Do Until dataFile.Worksheets(1).Cells(ROW, 1) = "" 'do until you reach an empty cell
        ROW = ROW + 1
    ROW = ROW - 1

'MsgBox "There are " & row & "rows and " & column & "columns in this data set"
'create two variables - origin and destination
'now redimension the arrays you are going to put the data into based on the
'number of rows and columns


'read in your row/origin and column/destination labels

    For i = 1 To ORIGIN - 1
        OLABEL(i) = dataFile.Worksheets(1).Cells(i + 1, 1)
    Next i
    For j = 1 To DESTIN - 1
        DLABEL(j) = dataFile.Worksheets(1).Cells(1, j + 1)
    Next j

'now fill your data array with values from the file
    For i = 1 To ORIGIN - 1
        For j = 1 To DESTIN - 1
            DATA(i, j) = dataFile.Worksheets(1).Cells(i + 1, j + 1)
        Next j
    Next i
'now write out the data in a paired list format

'first add a new sheet to your excel file and make it the active sheet

    ActiveSheet.Move After:=Sheets(Worksheets.Count)

'put in some column headers

    k = 1

        dataFile.Worksheets(Worksheets.Count).Cells(k, 1) = "Origin"
        dataFile.Worksheets(Worksheets.Count).Cells(k, 2) = "Destination"
        dataFile.Worksheets(Worksheets.Count).Cells(k, 3) = "Data"

'now write out the data as a paired list
    For i = 1 To ORIGIN - 1
        For j = 1 To DESTIN - 1
            k = k + 1
            dataFile.Worksheets(Worksheets.Count).Cells(k, 1) = OLABEL(i)
            dataFile.Worksheets(Worksheets.Count).Cells(k, 2) = DLABEL(j)
            dataFile.Worksheets(Worksheets.Count).Cells(k, 3) = DATA(i, j)
        Next j
    Next i

End Sub


Lazy mapping gripe

Woo look, a google maps mash-up! Look, it shows us, er, well quite a lot of dots on a map. But look they’re all different colours! So? Well, you know, it’s really neat to put different coloured dots on a map, especially as google makes it so easy when you just upload a spreadsheet containing some postcodes or geo-references to a ‘google fusion table’ and click ‘visualise’.

OK, so this rant was inspired by an article in today’s Grauniad data blog publishing the carbon emissions of every public building in England and Wales: http://www.guardian.co.uk/environment/datablog/interactive/2011/mar/07/carbon-emissions-public-buildings-map . I normally like quite a lot of the stuff they publish in the data blog, but this was just pretty lazy and sullies the good reputation of geography somewhat. A bunch of dots on a map, even if they are coloured in, doesn’t really tell us very much.

The real shame was that there was some interesting data underlying the lazy mapping that went on. Not only were total CO2 emissions per building, per year published, but so were the total floor areas of these buildings, along with various heat/electricity uses, per metre sq and some benchmarks which are typical for buildings of a certain size. This gives anyone with the data the opportunity to compare efficiencies across England and Wales – say for Local Authorities (given that these data are for public buildings…)

Anyway, just as a sampler for something a little more interesting here are the average CO2 emissions per square metre of public building, across local authority districts in England and Wales. It would of course be even more interesting to compare these with the benchmarks to see which local authorities fall above or below what would be expected, but there are only so many hours in the day and proper work beckons…

Average volume of CO2  per m2 (tCO2/m2) across all public buildings in the local authority