Our previous post on the Paris war commemorative plaques levelled its scrutiny at Feuille, or Sheet 3, in part on the grounds that its data were the more serviceable. That’s true, and bids us in turn to ask why we might then even bother crossing over to Feuille 4.
Indeed – Sheet 3 seems a good deal more ramshackle than its frère, having incurred an utterly empty column C driving through the data for starters. Consider as well the absent titles for A and B, the undigested French characters, the unchecked deployment of the Wrap Text feature (e.g column B), and Sheet 3 is something less than a thing to behold. Dommage.
But sheet 3 has a selling point – those rows of latitudes and longitudes that locate the plaques in the Paris space and can eventually empower a cartographic scatter of the data.
But these numbers need work too. We’ve met up with the issue before (e.g., January 17) – those incriminating green markers padding the cells’ upper-left corners, auguring the Number Stored as Text caution. Again, by selecting the respective data and jabbing at the Convert to Number option (a routine that froze my file for a couple of minutes, by the way) you’ll set all those cowering numbers free. You’ll also want to delete the C column, insert a blank row between 1 and 2 in order to ostracise the (original source)paris-plaques.fr caption from the actual data (of course you could simply delete it instead), and proceed to title columns A and B (I’ll try Names and Address respectively).
Next, you’ll want to again strike any duplicate records from the data, as per last week’s post, thereby skimming 32 redundancies from the deck. But there’s yet more winnowing to do. If you sort the data Largest to Smallest by the latitude field, nine zero-latitude records will shake to the bottom of the mix, along with a clearly misreported lat and long for the plaque at 4, place Rio de Janeiro 75008 Paris. Beaming a blank row between 1180 and 1181 will shuck these ten from the viable data. But don’t leave that button group just yet. Now sort by Smallest to Largest on the longitude field and you’ll jam six records with suspect longs (starting with 3.827, a reading which can’t be Paris-sighted. Remember that a degree of longitude extends east-west about 60 miles, depending on its north-south position). Insert another blank row between 1174 and 1175 and sort Longitude again, this time by Largest to Smallest, and an impossible number , -75.753 (somewhere in the States), plops to the foot of the data. Time, then, for another blank row.
Now we can move to “map” the data, by pinning them to their coordinates via the pivot-table mechanism I described in the October 25 and November 1 posts. Again, by lining the table with Latitudes and Longitudes we’ve literally framed the bordering coordinates of the map, against which the lat-long intersections of every plaque can be plotted. Try
Row labels: Latitude (group by .003)
Column labels: Longitude (group by .003)
Values: Address (by Count, of necessity; labels can only be counted)
That .003 degree-grouping setting seeks to mediate a set of proper gathering spaces in the pivot table among the lats and longs; too large a number will subsume too many plaques beneath but a few intersections in the “map”, while a tinier grouping value will flood the table with a pattern-exploding spray of points. Play around with different values in the Group by field and you’ll see what I mean. (Note: for a discussion of grouping alternatives see my post at datadrivenjournalism.net.)
Moreover, you’ll want to sculpt the cells’ heights and widths in order to square their perimeters, the better to equalize the north-south and east-west dimensions the map means to capture. I’ve found that a column width of 2 bound to a row height of 15 (I know that doesn’t sound symmetrical, but check it out) seems to work; of course your screen display and/or resolution might support a 4/30 or 6/45 split, but again you may need to experiment with absolute values and ratios. And in the interests of maximizing screen visibility, you may very well want to pump the 100% zoom default down to say, 70% or lower.
You could also hide the lat/long row and column borders, but not before making sure that the row label-latitudes are sorted from highest to lowest, reflecting their real-world thrust. By corollary, the columnar longitudes require a Smallest to Largest sort, because Paris’ east-of-Greenwich situation pulls its longitudes upwards from east to west.
And you can paint various conditional formats atop the map (again you can review the October 25 and November 1 post), culminating in something like this:
I could go on, but I suspect that latte beckons.
spreadsheetjournalism 
Leave a Reply