Iterative Proportional Nitwit

Iterative Proportional Fitting? That old chestnut? Haven’t people been banging on about that for hundreds of years?! Well, about 70, but I guess some of the old ones are the best ones… (Deming and Stephan for all those pub quiz fans out there!)

Anyway, I recently stumbled across IPF again in the way that people who work with data matrices do from time to time. I also realised that there’s not a whole lot of useful generic programmes out there for you to carry out this pretty useful procedure, and like a total nit, I’ve been doing it the hard way for a long time. Therefore I have spent the last day or so writing a nice little bit of VBA (arrghh, not Microsoft?! Yes! And it even runs in the much maligned but fantastic Excel). The program will take in data in either matrix or paired list format and will run IPF to update the interior cell values to a new set of column and row margins whilst maintaining the original ratio structure of the table.

I think Paul Norman produced a similar program in Excel a few years ago, but if you can find it anywhere on the web you’re a better Googler than me! Hopefully this programme improves Paul’s a little with the flexibility for matrices and paired lists and with it taking advantage of the increased number of columns available in the latest versions of Excel. Also, this will be freely available to download from here from now on. If you’d like to use the program, it can be downloaded from the link below:

http://dl.dropbox.com/u/8649795/DESKTOP_IPF.xlsm

Enjoy!

Oh and here’s a nice picture that my ENFOLD-ing colleagues will enjoy! 🙂

 

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
    Loop
    
    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
    Loop
    
    ROW = ROW - 1

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

    ReDim OLABEL(ORIGIN), DLABEL(DESTIN), DATA(ORIGIN, DESTIN)

'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

    dataFile.Sheets.Add
    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

 

CIDER Migration Classification mash-up

Thanks to some help from Richard Milton and Ollie O’Brien at CASA I have now managed to produce a google maps mash-up of the Migration Classification I developed as part of my thesis.

CIDER Migration Classification

CIDER Migration Classification

The full interactive map can be accessed on Maptube using the link below:

http://www.maptube.org/map.aspx?s=DHxUoTZpbLOmWER8mDwKVcHAp1nAoHbd

The Census Campaign

Ooh look, two of my favourite things in the same place – the Census and Secularism.

Census Campaign

The latest social attitudes survey suggests around 43% of people in Britain say they have no religion – in the 2001 Census this figure in England was recorded as being around 15%. Such a big difference is most likely attributed to non-religious Brits who were exposed to bits of the CofE when they grew up and when faced with a leading questions like ‘What is your religion?’ feel obliged to choose one.

Even taking all of the Jedi into consideration (http://www.statistics.gov.uk/census2001/profiles/rank/jedi.asp) – a whole 0.7% of the England and Wales population – the last Census almost certainly did not record successfully the true religious geography of the UK. With such figures used to bolster arguments for social maladies such as faith schools, this time we should all owe it to the country to be honest on March 27th…

Census 2011

Internal migration in the news

Internal migration in the news! Well, sort of…

Source: Guardian data blog: http://www.guardian.co.uk/news/datablog/2010/nov/08/housing-benefit-reform-impact-area#data *No legend provided, but the colours would seem to represent an average loss of housing benefit – I’m guessing purple is the worst!*

In this article in today’s Grauniad (http://www.guardian.co.uk/society/2010/nov/08/housing-benefit-north-south-divide), it is warned that housing benefit cuts will make the South of England unaffordable for those claiming housing benefits,  heralding a wave of south-north migration.

If this were to happen, it would be unprecedented in recent history – not only has the dominant trend been for flows from the north to the south, driven by London’s position at the top of the urban and economic hierarchy; but migration flows have been chiefly characterised by moves of the more well-off.

As the recession (and the Coalition) bites harder and the welfare state comes under more strain, we await to see the knock-on effects on internal migration…

For a very comprehensive overview of the determinates of out-migration (circa the turn of the millennium) see:

Fotheringham, A. S., Rees, P., Champion, T., Kalogirou, S., and Tremayne, A. R. (2004), ‘The development of a migration model for England and Wales: overview and modelling out-migration’, Environment and Planning A, 36 (9), 1633-72.