Alfredo Covaleda,
Bogota, Colombia
Stephen Guerin,
Santa Fe, New Mexico, USA
James A. Trostle,
Trinity College, Hartford, Connecticut, USA
OK, OK. Maybe we've crossed over some line social acceptability, but this is neat addition to the analytic journalist's toolbox. My friend Mike Collins tips us off to:http://www.juiceanalytics.com/weblog/?p=236
Lifehacker, delicious folks! This post generated a ton of great community ideas. Check out our followup post to see some more ideas and to download a spreadsheet with demos. Thanks.
We often are given a chunk of data in Excel that we need to explore. Of course, the first tool you should pull out of your toolbox in cases like this is the trusty PivotTable (it slices, it dices!). But at times we have to dig a little deeper into the toolbox and pull out the in-cell bar chart. Here’s what it looks like.
This picture shows some Major League Baseball data. I’m graphing the number of walks each player has taken. The bar graphs are built using the Excel REPT function which lets you repeat text a certain number of times. REPT looks like this:
=REPT(text,number_of_times)
For instance, REPT(”X”,10) gives you “XXXXXXXXXX”. REPT can also repeat a phrase; REPT(”Oh my goodness! “,3) gives “Oh my goodness! Oh my goodness! Oh my goodness! ” (my daughter’s an Annie fan).
For in-cell bar charts, the trick is to repeat a single bar “|”. When formatted in 8 point Arial font, single bars look like bar graphs. Here’s the formula behind the bars:
What are some practical uses of in-cell bar graphs? For starters, they offer a good way to profile a dataset that has hundreds or thousands of rows. Here’s a picture of in-cell bars compared to a standard excel bar graph for a dataset with about 500 rows. It can be a lot easier to scan the results when they’re in-cell.
Another usage is lightweight dashboards. The report below compares a number of metrics for players using both in-cell bar graphs as well as conditional formatting. The conditional formatting highlights the top 25% of each metric in green and the bottom 25% in red but that is a story for another day.
This entry was posted on Monday, July 31st, 2006 at 2:30 pm and is filed under analysis, excel. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Fascinating display of global statistics on site, Gapminder The homepage currently has some dynamic displays related to Human Development Trends: 2005. Well worth watching, but be sure to scroll down the page to scan all the useful articles and presentations available.Then, perhaps saving the best for last, go to the Gapminder Tool at http://tools.google.com/gapminder. Note that you can play with the axes to change (a) what is graphed and (b) how it is graphed (log or linear), and hit the play button on the bottom to see how the numbers changed over the past years. [Thanks Patti Schank for this good tip.]
Subscribe or go straight to the graph.
Contact gapworld@gapminder.org with questions or suggestions for improvements.
“The (Ongoing) Vitality of Mythical Numbers<http://www.slate.com/id/2144508/ >This article serves as a valuable reminder that we should viewall statistics, no matter how frequently they are used inpublic arguments, with skepticism until we know who producedthem and how they were derived.” From: Neat New Stuff I Found This Week <http://marylaine.com/neatnew.html> Copyright, Marylaine Block, 1999-2006.
From the good folks at Internet Scout:
Kudos this morning to National Public Radio's reporting on a Duke professor who thought the numbers on Chinese engineering grads seemed a little off kilter.
by Adam Davidson
Morning Edition, June 12, 2006 · A report cited in The New York Times and quoted on the House floor claimed China graduates nine times as many engineers as the U.S. Skeptical, a Duke professor had students check the numbers.
Organiser: Dr Sophia Ananiadou (Sophia.Ananiadou@manchester.ac.uk or (0161)3063092), School of Informatics, University of Manchester and National Centre for Text Mining (http://www.nactem.ac.uk/)
28 April 2006, Weston Conference Centre, University of Manchester.
To register for this workshop please complete the registration form.
This workshop aims to bring together researchers from different subject areas (computer scientists, computational linguistics, social scientists, psychologists, etc) in order to explore how text mining techniques can revolutionise quantitative and qualitative research methods in social sciences. New technologies from text mining (e.g. information extraction, summarisation, question-answering, text categorisation, sectioning, topic identification, etc.) which go beyond concordances, frequency counts etc can be used for quantitative and qualitative content analysis of different data types (e.g. transcripts of interviews, questionnaire analysis, archives, chatroom files, weblogs, etc). The semantic analysis of new text types, e.g. weblogs is important for sociologists and political scientists in inferring social trends. Reputation and sentiment analysis collects and identifies people’s opinions, attitudes and sentiments in text. Text mining techniques also aid metadata creation for qualitative data and facilitate their sharing.
A good learning opportunity in the Land of Lakes this summer….
Dear IPUMS Users,
I am pleased to announce the first annual IPUMS Summer Workshop, to be heldin Minneapolis on July 19th-21st. This training session will cover fourmajor databases: IPUMS-USA, IPUMS-International, IPUMS-CPS, and the NorthAtlantic Population Project (NAPP).
For more information, please visithttp://www.pop.umn.edu/training/summer.shtml.
I hope to see some of you in Minneapolis this summer.
Sincerely,
Steven RugglesPrincipal InvestigatorIPUMS Projects
Marylaine Block, at Ex Libris: an E-Zine for Librarians and Other Information Junkies.http://marylaine.com/exlibris/ tips us to another good blog for analytic journalists. Click below to see what Charles Franklin has to say about presidential polls.
Posted on Thu, Jan. 19, 2006 Feds dispute mine safety report By SETH BORENSTEIN and LINDA J. JOHNSON Knight Ridder Newspapers http://www.montereyherald.com/mld/montereyherald/news/nation/13661497.htm WASHINGTON – Federal mine safety officials on Wednesday disputed a Knight Ridder analysis showing a dramatic reduction in the dollar amount of large fines for mine safety violations during the Bush administration, saying in an Internet posting that those fines are actually up. Mine Safety and Health Administration spokesman Dirk Fillpot said that Knight Ridder made “assumptions that were incorrect'' in its Jan. 6 analysis. But when Knight Ridder conducted a new analysis in the manner suggested by Fillpot using MSHA's newest database, it showed the same dramatic drop. The newest data show a 43 percent reduction in proposed median major fines from the last five years of the Clinton administration when compared with the first five years of the Bush administration. That's the same percentage reduction found in Knight Ridder's original analysis, using a smaller, online database of MSHA violations. When asked about that drop and the analysis, Fillpot refused Wednesday to answer 11 specific questions about MSHA's fines, its analysis or the posting of its critique. Instead Fillpot repeated a prepared statement that said “it is unfortunate that Knight Ridder's analysis of MSHA's penalties was inaccurate.'' But four statistical experts who looked at the databases and analyses said Knight Ridder's findings were accurate and that MSHA's assessment didn't contradict the newspaper's findings of smaller fines during the Bush administration. “It's really wrong for them (MSHA) to say you're incorrect,'' said John Grego, a professor of statistics at the University of South Carolina in Columbia. “There's no question that the average/median proposed penalty has gone down.” MSHA's response “is looking at two different things and making a statement as if they are looking at the same thing,'' said Jeff Porter, a database library director for Investigative Reporters and Editors Inc., an association of journalists. Porter also teaches data analysis at the University of Missouri School of Journalism. On its Web site, MSHA said the size of the final assessments — which are lower after bargaining and appeals — are up by “nearly 38 percent.'' Knight Ridder looked only at proposed fines because some of the actual fines are determined not by MSHA, but by administrative judges when mining companies appeal those penalties. Further, Knight Ridder found that fines finally assessed and paid fines are still lower on average in the Bush administration. Fillpot wouldn't explain how his agency came up with the 38 percent figure. The statistical experts said they couldn't understand how MSHA figured that out. Fillpot said “that information is taken from actual MSHA enforcement records and is accurate.'' He refused to elaborate. In an unusual posting on the Internet on the Martin Luther King Jr. Day holiday on Monday, MSHA said, “Knight-Ridder's numbers are inaccurate, obscuring the reality that penalties issued by MSHA have gone up during this Administration — not down.'' After Knight Ridder questioned the posting, it was taken down Tuesday afternoon. It went back up Wednesday morning. Among fines of $10,000 or more, the median penalty levied in the past five years was $27,139. During the last five years of the Clinton administration, the comparable fine was $47,913, according to Knight Ridder's analysis of the newest data from MSHA. That data, which included 221 large fines that weren't in the publicly available database used by Knight Ridder for its initial analysis, show that the total number of large fines increased to 527 in the Bush administration from 461 during the last five Clinton years. Fillpot declined to say where those extra fines came from or why they weren't in the online database.| (Johnson reports for Lexington Herald-Leader.)
Sometimes journalists have a tendency to be too literal. We want to ask a question and we want the response to be a quote that is without ambiguity. One that's fills in some of the space between our anecdotes. But other times, we need tools that work like a periscope, a device that allows us to not look at the object directly but through a helpful lens. Such periscopes for analyzing the economy are indirect indicators. Monday's (5 Dec. 2005) NYTimes' Business Section was loaded with references to such indicators that journos could keep in mind when looking for devices to show and explain what's happening. Check out “What's Ahead: Blue Skies, or More Forecasts of Them?” Be sure to click on the link “Graphic: Indicators From Everyday Life“ Another indirector was mentined Sunday on National Public Radio in “Economic Signs Remain Strong“ There, an economist said he tracks changes in the “titanium dioxide” data, the compound is used in all white paint and reflects manufacturing production.