Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Chart Utility

 

Excel Dashboards

 

Books at Amazon.com

 

Charting Per Capita Earmarks


 

I've prepared this page as part of my response to the Mid-week entertainment: spots blog entry in Kaiser's fine Junk Charts blog. I actually saw the chart in question first in the Worse than a Pie Chart? entry in Andrew Gelman's Statistical Modeling, Causal Inference, and Social Science blog. The original chart was produced by the IBM Collaborative User Experience Research Group and archived on their Many Eyes web site.

   

The chart in question is the eye-catching but remarkably uninformative bubble chart at left. It is trying to show per capita U.S. federal government expenditures by state, using bubble sizes proportional to the per capita expenditure. One conclusion is that the seniority of the government representatives in Alaska, Hawaii, and West Virginia, result in diversion of a greater proportion of federal funds to their states. In Money Travels North, Josh Marshall of Talking Points Memo says:

Another interesting way to look at this is, look at the top three states: Alaska, Hawaii, and West Virginia. All smallish states but each also have senators that have been in office, respectively from 1968 (Stevens), 1963 (Inouye) and 1959 (Byrd).

The chart's bubbles are spacially arranged with only partial regard to expenditure, and to me Vermont appears larger than Alabama and West Virginia; this is a weakness of bubble charts. The chart resembles a petri dish during a mold experiment, or perhaps a magnified view of yeast budding.

Fortunately the data was readily available, so I could make my own charts. My objective is to show the per capita spending in a more orderly fashion, so relative ranks and values are easy to compare and patterns are obvious, without leaving any data unlabeled.

 

Kaiser thought an XY chart of expenditures vs. state population would be interesting, and I'm happy to oblige. That's California in the top right of the chart. All the other states are clumped in the bottom left quadrant of the chart.

 

When plotted on logarithmic scales, the states are spread more evenly. California is still the upper-right extreme, but not blatantly so. I've labeled the endpoints (CA and WY) as well as selected states toward the upper end of the federal funding range.

Below are two charts showing per capita federal spending by state, ranked in decreasing order of spending. The bar chart seems more effective to me than the line chart; in any case line charts should be used with caution when the X axis is purely categorical.

Alaska is the king of federal spending, due in part I suppose to research on harnessing the electrical power of the aurora borealis and building interstate bridges to sparsely populated islands. Another group of states, Hawaii, West Virginia, Alabama, Vermont, and Montana, show per capita funding somewhat elevated from that of the rest of the states. California is an unremarkable 34th in the list, squeezed between Arizona and Tennessee. Both of these charts suffer from having labels rotated 90 from the optimum reading angle. The numerical data labels aren't strictly necessary, but the state axis labels certainly are.

To alleviate neck strain and to make labels easier to read, I've rotated the line and column charts above into the dot and bar charts below. Even on a log scale (below right), Alaska stands out. Any of these charts meet my initial objectives. The relative spending can be readily compared, and the patterns (one large state, Alaska, followed by a group of five, followed by all the rest) are clear in all three.

Of course, the analysis is incomplete. A view of tax revenues, both absolute and per capita, would add context to the chart. So would an indication of GNP for each state (which Kaiser also mentioned). A breakdown of type of expenditure (transportation, education, R&D, military) would also help to explain some of the variation.

 

Page copy protected against web site content infringement by Copyscape

Peltier Tech Chart Utility

Create Excel dashboards quickly with Plug-N-Play reports.


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2013. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

Microsoft Most Valuable Professional

My MVP Profile