{"id":3606,"date":"2011-07-08T08:18:44","date_gmt":"2011-07-08T12:18:44","guid":{"rendered":"http:\/\/shermandorn.com\/wordpress\/?p=3606"},"modified":"2011-07-07T08:12:17","modified_gmt":"2011-07-07T12:12:17","slug":"in-cell-bar-charts","status":"publish","type":"post","link":"https:\/\/shermandorn.com\/?p=3606","title":{"rendered":"In-cell bar charts"},"content":{"rendered":"<p>CCPhysicist made some very good suggestions in response to Wednesday&#39;s <a href=\"https:\/\/shermandorn.com\/?p=3589\">chart of educational attainment<\/a>, and&nbsp;I&#39;ve had a few questions about how I produced it&nbsp;(in terms of the chart, not the basic pull-down of data from <a href=\"http:\/\/www.ipums.org\">IPUMS<\/a> and frequency tables). Before I get to CCPhysicist, there are two essential sources of inspiration for the chart:<\/p>\n<ul>\n<li><a href=\"http:\/\/populationpyramid.net\/\">Population pyramids<\/a>, which display population distributions as differently-sized &quot;cake&quot; slices, commonly divided by sex. (Don&#39;t miss a recent&nbsp;<a href=\"http:\/\/www.excelcharts.com\/blog\/beautiful-but-terrible-population-pyramids\/\">variant on population pyramids<\/a>!)<\/li>\n<li>Chris Gemignani&#39;s simple <a href=\"http:\/\/www.juiceanalytics.com\/writing\/lightweight-data-exploration-in-excel\/\">in-cell bar charts<\/a> in Excel as well as <a href=\"http:\/\/www.juiceanalytics.com\/writing\/more-on-excel-in-cell-graphing\/\">several readers&#39; suggestions<\/a> he described in a later blog post.&nbsp;<\/li>\n<\/ul>\n<p>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, &quot;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!&quot; Well, yes, but also incomprehensible.&nbsp;<\/p>\n<p>The technical details:<\/p>\n<ul>\n<li>The horizontal icicles are concatenated mini-bar charts, using the Excel function Gemignani suggested: REPT(&quot;.&quot;,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(&quot;.&quot;,C4),&#8230;.).<\/li>\n<li>I picked symbols that would increase in the vertical direction with greater attainment: periods for primary schooling, &quot;i&quot; for secondary attainment, and &quot;|&quot; (a vertical bar) for university completion.<\/li>\n<li>Very important: use a fixed-space font! Because the WIndows-7 fonts I have available have somewhat greater ink density for &quot;i&quot; than for &quot;|&quot; I may shift the secondary attainment character from &quot;i&quot; to &quot;:&quot; but this was an experiment.<\/li>\n<li>The concatenation order was reversed for male and female &#8212; males started with periods and ended with &quot;|&quot; so I could use the fundamental population-pyramid display scheme (data grouped around the central axis in a symmetrical fashion).<\/li>\n<li>The male-data column was right-justified.<\/li>\n<li>Add central columns for country and year displays.<\/li>\n<li>Add implied 10% markers in the header row, and then sprinkle repeats of that header row later in the table.<\/li>\n<\/ul>\n<p>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&#39;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.&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>CCPhysicist made some very good suggestions in response to Wednesday&#39;s chart of educational attainment, and&nbsp;I&#39;ve had a few questions about how I produced it&nbsp;(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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[4],"tags":[],"class_list":["post-3606","post","type-post","status-publish","format-standard","hentry","category-research"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pag0MB-Wa","_links":{"self":[{"href":"https:\/\/shermandorn.com\/index.php?rest_route=\/wp\/v2\/posts\/3606","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/shermandorn.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/shermandorn.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/shermandorn.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/shermandorn.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3606"}],"version-history":[{"count":5,"href":"https:\/\/shermandorn.com\/index.php?rest_route=\/wp\/v2\/posts\/3606\/revisions"}],"predecessor-version":[{"id":3613,"href":"https:\/\/shermandorn.com\/index.php?rest_route=\/wp\/v2\/posts\/3606\/revisions\/3613"}],"wp:attachment":[{"href":"https:\/\/shermandorn.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/shermandorn.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/shermandorn.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}