You say you can’t get there from here? You better find a way, my friend, because your job is there and staying here won’t pay the rent; and the UK’s Office for National Statistic’s data on work commutes tell us much about the daily to-ing and fro-ing that takes its labor force to its appointed rounds. Download all of that here, by clicking on the Commuting Patterns link.
(If necessary, you’ll want to save the file in xlsx mode. Note there are other official looks at commutation data out there, e.g. here, and the numbers elsewhere may differ. As usual, the methodological fine print is key.)
The workbook discretely devotes its several sheets to incoming and outgoing commuting flows from and to about 380 local education authorities (that’s a UK-specific entity, in London’s case to its 33 boroughs), and for the years 2010 and 2011. The numbers before us were extrapolated from the country’s Annual Population Survey data, and as such are beholden to the confidence intervals the book describes in its Metadata sheet. Moreover (and thanks to the ONS’s Alexa Bradley on this), that sheet brings some significant estimates issues to our attention.
The Inward commuting sheets (Table 6, for example), drape workplace destinations down column A, with the localities from which workers head there named in B. The Outward data flip the parameters, commending Place of Residence to A instead. But it occurred to me that the Inward/Outward numbers could be brought together in a single sheet, preparing us to study each locality comparatively for its I/O movement as a result. With those marching orders in mind I tried this move for the 2011 data: I copied and pasted the Outward flow data in Table 8 to and beneath the Inward rows (Table 6), deleting the newly expendable header row in 9190. I then pushed the A column aside via an Insert, entering an o for outward in A9190 (now home to a usable record, after all) and copying from there down the remainder of the column. Back in A5, the row upholding first Inward record, I entered i and copied down (double-clicking the fill handle will take the copy only as far as the empty cells, and won’t touch all those Os below.), naming the late-coming field Inward/Outward, or something like that. The plan then was to pivot-table the data by locality, proceeding to break these out by the workers emanating from, and steaming toward, each one.
Now we need to think about that data graft. After all – the Inward/Outward sheets aren’t structural equivalents. Columns A and B in the one sheet have been mirror-imaged as B and A in the other; and so my copy-and-paste appears to have admixed residence data with destinations, and vice versa.
The allegation is true, and at the very least seems to expose itself to the charge of bad form. But in fact the ostensible hybridization of the Workplace field is nothing more than, for example, the multiple listing of a student’s name in a data set strung to a nearby column naming various academic subjects and a third field setting forth respective test scores. Don’t be disoriented, then, by the Ins and Outs and their literal semantic opposition. Understood in spreadsheet field-item terms, In and Out are equivalent to Sociology and Physics – different items, and that’s all.
But if you’re not mollified yet humor me and try this pivot table:
Row Labels: Workplace
Column Labels: Inward/Outward
Values: Number of Commuters (Sum)
(I’d turn off Grand Totals here.)
Yes, the Workplace field is rife with Places of Residence data, but again, the Is and Os keep each directional type at arm’s length.
Once put into play, the data draw some most notable, and in at least one case colossal, symmetries. Look at the numbers for the City of London and its minute residential population, for example: An inflow of 616,719, an outflow of 1,303. Broadland, on the other hand, a community in Norwich north of London on the UK’s east coast, “loses” far more commuters than it gains, mustering an I/O split of 32,000 to 60,000. My home authority of Barnet is likewise top-heavy with the outs. Unsurprisingly, larger cities receive more commuters than they give.
And as such, precision might be better served and captured by a simple inward-to-outward ratio, in which a place’s Inward number, for example, could be divided by its Outward. But while the math is simple, making it happen in this pivot table requires a bit of a step back.
What’s needed – though as always I’m prepared to be persuaded by possible alternatives, too – is a Calculated Item, not to be confused with a Calculated Field, an instrument we’ve dusted off on several occasions including here. Because we want to divide a locality’s inward numbers by its outward – that is, fractionate a particular item in a field with another item in the same field – we’re in effect hatching a brand-new item, and one that appears not at all in the source data set.
To start hatching, click in the Inward/Outward column label strip:
Next, click PivotTable Tools > Options > Fields, Items, & Sets > Calculated Item:
Substitute a field name if you wish for Formula 1 (i.e., In-Out Ratio; you can introduce a legal space here)
Double-click the i, thereby lodging that item name in the Formula space; then enter a / in there, double-click the o, and click OK, seeing to it that each locality’s Inward total gets divided by its Outward companion. You should see, in excerpt:
Note that I’ve formatted the numbers to two decimal places – that is all the numbers, whether I’ve wanted to or not, and even the Inward and Outward numbers for which the places are utterly dilatory. That’s because the formatting here is field-wide, and since i, o, and In-Out Ratio are items sprouting in the same field, the decimals are indiscriminately applied.
And note the City of London’s In-Out ratio of 473 to 1. A nice place to work, but folks just don’t seem to want to live there.
spreadsheetjournalism 
Leave a Reply