CCPhysicist made some very good suggestions in response to Wednesday's chart of educational attainment, and I've had a few questions about how I produced it (in terms of the chart, not the basic pull-down of data from IPUMS and frequency tables). Before I get to CCPhysicist, there are two essential sources of inspiration for the chart:
- Population pyramids, which display population distributions as differently-sized "cake" slices, commonly divided by sex. (Don't miss a recent variant on population pyramids!)
- Chris Gemignani's simple in-cell bar charts in Excel as well as several readers' suggestions he described in a later blog post.
The central problem I face with this data set is not the complexity of a statistical procedure; what I created is essentially a very large 4-way frequency table, where the data of interest are the row percentages (proportion of a country-year-sex slice in different attainment strata). Rather, the central need is to display the information in a way that facilitates scanning for patterns and demonstrating them. I am reasonably accomplished at scanning tables, and I can sort the frequency tables in different ways. But 3-6 cohorts each among 29 countries and Puerto Rico becomes a bit unwieldy, and I can use any assistance I can manage. In addition, I am presenting this at a meeting of historians, mostly historians of education who focus on the United States. I could just imagine saying at a critical point, "Please compare now the third column in the fifth row on the first page to the fifth column on the fourteenth row on the third page. This is incontrovertible!" Well, yes, but also incomprehensible.
The technical details:
- The horizontal icicles are concatenated mini-bar charts, using the Excel function Gemignani suggested: REPT(".",C4) might be the function for displaying however many periods there are percentage points of individuals in a country/census that had completed primary schooling but not secondary schooling. Then you string them together with CONCATENATE(REPT(".",C4),….).
- I picked symbols that would increase in the vertical direction with greater attainment: periods for primary schooling, "i" for secondary attainment, and "|" (a vertical bar) for university completion.
- Very important: use a fixed-space font! Because the WIndows-7 fonts I have available have somewhat greater ink density for "i" than for "|" I may shift the secondary attainment character from "i" to ":" but this was an experiment.
- The concatenation order was reversed for male and female — males started with periods and ended with "|" so I could use the fundamental population-pyramid display scheme (data grouped around the central axis in a symmetrical fashion).
- The male-data column was right-justified.
- Add central columns for country and year displays.
- Add implied 10% markers in the header row, and then sprinkle repeats of that header row later in the table.
The rest is figuring out the proper size of virtual paper for a PDF, then copying the PDF data into an image file. That part happened in a mediocre fashion early this week, and when I try changing the vertical symbol for secondary attainment, I'll address the benchmark issue CCPhysicist noted in the PDF, probably with Adobe Illustrator unless someone has a clever suggestion for painting blocks of shading (to go under the text layer) within Excel.
Happy to help.
I should emphasize here that I REALLY like that way of displaying those multi-dimensional data. Indeed, I was astounded at how a variety of correlations popped off the screen after I put a background and border behind it. Left-right for gender, down for time, and in blocks (I want to emphasize that) across nations.
Two observations:
It will have to be split across two pages in print, so think carefully about what will be on each page when it comes to grouping countries. In print, using density of a font with light shading (all in gray scale) should work well.
Presenting it this way within a PowerPoint slide would be pointless unless you were looking at a specific country or comparing two countries. Too much info. However, in that medium it would make sense to shift to thin bars using color to indicate the three levels of attainment. Once you know what the bars mean, you can see a larger pattern across groups of 4 countries (perhaps more) and comprehend it during a talk.
BTW, it is really too bad that you don’t have complete data for the “BRIC” countries (missing both Russia and China), but you do have all four of the current “PIGS” countries (Portugal, Ireland, Greece, and Spain if you use the version that drops Italy, which you don’t have anyway) that are of particular interest if you have followed any of the excitement in the Eurozone. A four-panel comparison of those four is interesting because the attainment varies a lot.
Thanks for the kind comments. One of the things this teaches me is the limits of generic “infographic” packages. I have enough substantive expertise to know what to look for and thus how to frame comparisons. That, plus I have no shame in cribbing the clever ideas of others (as long as I give them credit).
I’m currently reading Ian Bogost’s Newsgames, and the chapter on making games out of statistics is tantalizing… and difficult because there is no system simulation “game engine” that I could find online.
I’m limited partly by what IPUMS has available, which is substantially limited by the willingness of countries to let census microdata become public (thus the absence of PRC and USSR/former USSR data). I also eliminated countries with fewer than three censuses available because the changes over time are important.