Alfredo Covaleda,
Bogota, Colombia
Stephen Guerin,
Santa Fe, New Mexico, USA
James A. Trostle,
Trinity College, Hartford, Connecticut, USA
Friend Steve Guerin tips us to “Cabspotting,” a fascinating site created by San Francisco's Exploratorium. It's about georgraphy, traffic flow, and complexity. Give a look to “Cabspotting”
Cabspotting traces San Francisco's taxi cabs as they travel throughout the Bay Area. The patterns traced by each cab create a living and always-changing map of city life. This map hints at economic, social, and cultural trends that are otherwise invisible. The Exploratorium has invited artists and researchers to use this information to reveal these “Invisible Dynamics.”
The core of this project is the Cab Tracker. The Tracker averages the last four hours of cab routes into a ghostly image, and then draws the routes of ten in-progress cab rides over it.
The Time Lapse area of the project reveals time-varying patterns such as rush hour, traffic jams, holidays and unusual events. New projects are produced by the Exploratorium's visiting artists and also created by the larger Cabspotting community.
We are currently in South Africa, primarily to participate in Highway Africa 2006 sponsored by the School of Journalism and Media Studies at Rhodes University in Grahamstown. But before heading to Grahamstown, we were asked to give a series of lectures in the Durban, South Africa area. Below is the schedule and links to the presentations (as soon as they are uploaded).
A helpful piece posted today on LLRX.com reminding us that just throwing what we think might be appropriate keywords into a search engine isn't the mostsensible research strategy. While you might find that the title of the articleis not exactly what it is about, the content is helpful. Here are the main points:
“It's Not Rocket Science: Making Sense of Scientific Evidence,” by Paul Barronhttp://www.llrx.com/features/scientificevidence.htm
National Public Radio (USA) had a good piece on the air this Sunday morning about Edward Tufte, the infographics guru. The radio piece, “Edward Tufte, Offering 'Beautiful Evidence” (http://www.npr.org/templates/story/story.php?storyId=5673332) does a good job of explaining, for the ear, information graphics, and we applaude the folks there for taking on a topic that most radio producers/editors would avoid because “there ain't no sound.” Well, yes, but….Also, the NPR web site included a nice film clip of Tufte during a lecture. Be sure to check it out.
Edward Tufte makes a point during a seminar. Graphics Press
An interesting piece in the NYTimes on Sunday, “Finding Tax Revenue Through Aerial Imaging,” highlights yet another industry and example of how public administrators are using GIS, in this case to increase the revenue stream. We think that if journalists are not hip to these tools, then they cannot ask the right questions of the public's administrators.
Once a year, Pictometry flies a Cessna 172 over Philadelphia, taking thousands of black-and-white photographs. The low-altitude shots, unlike satellite images, show buildings at about a 40-degree angle. Pictometry’s computers organize the photos so they can be searched by address. Nearly 200 employees in Mr. Mescolotto’s office have the software on their computers.
Pictometry isn’t the only company offering aerial photos to assessors, but it has won adherents in more than 200 cities and counties, according to Dante Pennacchia, Pictometry’s chief marketing officer. Its competitors include an Israeli company, Ofek International, working with Aerial Cartographics of America, based in Orlando, Fla….”http://www.nytimes.com/2006/08/20/realestate/20nati.html
The annual APDU conference provides a forum for colleagues to discuss data and public policy issues, keep up with new technologies, and provide input into federal, state, and local data activities. In plenary sessions, APDU 2006 attendees will hear from noted speakers on a variety of critical strategic, technical and policy topics.
Communication among data producers, users, and intermediaries is the most effective way to guide the development, expansion, and preservation of data products. In the past year, government data have been important for providing information to the public, informing policy and spurring investment at the local level. Join APDU this year to learn about and discuss issues related to public data.
For session titles, abstracts, and speakers, see the preliminary agenda. Also, don't overlook the opportunities to network with other public data users and statistical agency officials attending.
Conference planning is well underway, but if you have a suggestion for speakers for this year or a topic you would like considered for the future, contact program co-chairs Lisa Neidert or Leonard Gaines by phone or email.
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.
A few days ago, I asked friends and colleagues on listservs to suggest 25 relatively generic numbers journalists should know in order to be responsible, effective reporters and editors. You sent along the great suggestions included below. A handful of folks, however, responded to make two points:
· It is more important to know where to find pertinent numbers than it is to know specific numbers, and
· It is more important to know appropriate calculations – say, how to compute percent of change – that can be applied to specific numbers once they are found.
Yes, points well taken. But I don’t think any of these are mutually exclusive. Here’s why.
Any statistical analysis begins with classifying and counting. That process is only relevant if put in some context. If I tell you that Santa Fe, New Mexico has about 68,000 people, that number by itself has little meaning in terms of scale. Is 68,000 big or small? How do I tease some information out of that lonesome statistic? Ah, but when we can ask how does it compare to other cities in the state, region or nation meaning and information start to bubble up?
The second analytic step is estimation. This is helpful – perhaps necessary – to have some ballpark figure to help the analyst determine if his/her calculations are correct or “make sense.” If the city manager tells a reporter that the town has been growing by about .5 percent per year since 2000, she could not estimate the amount of growth or its current aggregate unless she had a baseline number of 68,000.
So we think that (a) journalists should always have some relevant – and fairly accurate – ballpark figures in mind to help with context (Yes, some of these will vary from beat to beat); (b) journalists should know where and how to find the historic and current statistics; (c) journalists should know how to do some fairly elementary arithmetic to tease information out of the data.
Thanks to all for your contributions.
–Tom Johnson [12 August 2006]
· Distance (in miles/km and time) from your city to provincial/national
· Capital and principal cities of the world
· Average number of calories consumed per day for residents of your nation
· Annual production (either in area or amount) of the five largest food
· Crops in your nation
· The amount (and balance) of trade between your nation and its five
· Largest trading partners (bonus: which commodities contribute most to that trade)
· Average annual rainfall in your city [Wendell Cochran, American University]
· Add in racial and ethnic groupings, homeless/housing character (rent/own, size), language, age, religion, business characteristics, gross domestic product, largest businesses, largest employers (the latter two are often not synonymous), macro-crime rates, major political parties, voter registration and recent political outcomes.
· The rate of change is at least as important as the current or historical raw number, and you need both to provide context.
· The list needs to be adjusted for one's beat(s). It makes no sense to ask a cops reporter to spew out business numbers without end, but he damned sure ought to know that murders have increased for four of the last five years. [Pierce Presley, Master's Candidate, University of Memphis]
· Homicide rate
· Cost of Living for your city compared to nation
· Average Wage in your city/state/nation
· Average commute time to work (in minutes) compared to state/nation
· Median home price for your city compared to peers (similar sized cities)
· Which industry employs the largest proportion of your county/state/nation's population? (In Indiana, we rank 1st in the nation for manufacturing. But that's a double edged sword. In other words, how industry-dependent are you?)
[Carol Rogers, IUPUI, Indianapolis, Indiana]
· Para temas de seguridad: cantidad de policías en actividad en mi ciudad y cantidad de delitos contra la propiedad por mes o día (promedio) [Under the category of security: the number of active police in my city and the daily or monthly average number of property crimes]
· Para temas de salud: cantidad de médicos en ejercicio y cantidad de camas hospitalarias [Health care: the number of practicing doctors and the number of hospital beds.]
· Para temas judiciales: cantidad de causas penales por juzgado y cantidad de funcionarios judiciales por juzgado [Legal system: the number of criminal cases by court and the number of employees in the court system.]
· Para temas de contaminación ambiental: cantidad de monóxido de carbono en el aire que respiramos para la ciudad en la que vivimos [Environment: the amount of carbon monoxide in the air in our city]
· Para temas de tránsito: cantidad de accidentes de tránsito por día, cantidad de automóviles circulando por día y de multas labradas a los infractores por día (promedios) [Transportation: the average number of traffic accidents per day; the average number of cars on the city streets and the average number of tickets/fines per day.]
· Para temas electorales: Cantidad de votos emitidos en elecciones pasadas por partido politico [Elections: the number of votes cast, by political party, in past elections.
[Sandra Crucianelli, journalist and CAR trainer, Bahía Blanca, Argentina]
· Unemployment. Worldwise it is something like 1/3 ( if that isn't gun powder I don't know what is) [Jenny Quillien, FRIAM, Santa Fe, New Mexico]
· GDP — $13.2 trillion as of the second quarter of 2006, in current dollars (source: http://www.bea.gov )
· The U.S. civilian labor force: 151.5 million Employed: 144.3 million, Unemployed: 7.2 million, Not in the labor force: 77.4 million, (source: http://www.bls.gov ) It's also useful, of course, to know these figures for your state/locality.
· The number of households in the U.S.: 105.5 million, as of Census 2000 (again, the local number is very useful)
· U.S. median household income: $44,473 (three-year average, 2002-2004) (only 19 of 50 states are above this, by the way) (source: http://www.census.gov
· Largest US corporation, by sales: Exxon Mobil, $339.4 billion in 2005. Largest by assets: Citicorp, nearly $1.5 trillion. (Source: Fortune magazine)
· Also, a useful math trick is the Rule of 72, a quick way of calculating how long it will take something to double in size. That is, if something is growing at X percent a year, divide X into 72 to get the number of years it will take to double. So, a city growing at 8 percent a year will double in 9 years. [John Byczkowski, Cincinnati Ohio]
· Per capita water (gallons per day) and energy consumption (kilowatts per year) in your country and how they rank versus other countries and global average
· Water consumption % by sector: industrial, agricultural, domestic
· Proportion of oil, gas, coal that is imported in your country
· Proportion of imports of all food consumed ($ and KCals)
· Energy consumed per unit of GDP (kilowatts per $ of gdp) and comparison
· Per cent of GDP spent on defense and national ranking
· Crime rate matrix: gender by race/ethnicity by age range
· Per capita income matrix: gender by race/ethnicity by age range
· Current level of forestation of your country and what it was 100 years ago.
· Geographic size of your land mass of the earth, your country, state, county, town
· Basic unit conversion from mass to volume: water = about 64lb per cubic foot
[Jim Rutt, FRIAM Group, Santa Fe, New Mexico]
I'd suggest working this question from the perspective of readers/viewers.
· The price of a bus ticket/monthly pass.
· The unemployment rate in your country/state/province/city.
· The cheapest interest rate you can get for a mortgage in your reporting area and your federal government's overnight interest rate (the Fed's rate for the U.S.; the Bank of Canada's overnight rate here)
· Your country's trade surplus/deficit
· The operating surplus or deficit of your national, regional, and local government.
· The debt of your national, regional, and local government (and can you clearly explain the difference between the debt and the deficit)
· The percentage of eligible voters that actually exercised their franchise at the last national, regional and local election. Bonus points if you can say if that percentage is up or down compared to the previous election!
· The price of a litre of milk and a litre of gas in your area and the national/regional/local average of those goods. (I know you Americans buy gas by the gallon, but what is it for milk – a quart?)
· The salary of your top municipal/regional/federal politician and the salary of the top bureaucrat in each district.
· An average of mean temperature for your reporting area yesterday and how hot/cold it was a year ago. Five years ago? Ten years ago?
· How much income does a household have to have in your area to avoid being labeled as “poor”?
· I've found that, for survival in a newsroom, it's always a good idea to always know your circulation/viewership now and what it was a year ago. [David Akin]
· Rate of inflation in the local economy.
· Exchange rate of relevant currencies.
· Whatever the reference interest rate is locally
· Year-to-date returns of the local equity market's index
[Bill Alpert, Sr. Editor, Barron's]
· Population make-up by race, ethnicity, etc. for your community? [Jeff Parrott, Projects reporter, South Bend (Ind.) Tribune]
Here's a couple of numbers every journalist should know:
· The phone number to the library
· The phone number to the help desk at the Census Bureau
[Jodi Upton]
· The circumference of the earth (25,000 miles).
· The mileage on your car.
[Teresa Meikle, News Researcher, The Press Democrat, Santa Rosa CA]
· A good idea of the normal curve, such that education reporters wouldn't make a big deal about moving from the 48th to the 53rd percentiles with one year's testing;
· A rough idea of converting units (mph to feet per second once got me a great nugget in a story)
· A general breakdown of national race and trends (more Hispanics than blacks, for the first time, not so many years ago)
· A general idea of long-term debt obligations, which has gotten USA Today some great stories and almost certainly deserves a greater focus by other news organizations: http://www.usatoday.com/news/nation/2004-10-03-debt-cover_x.htmhttp://www.usatoday.com/news/washington/2006-05-24-retiree-taxpayers_x.htm
[Mike Stucka]
· Percentage break-downs based on income and age.
· Current rate of home ownership, as well as the rate 5, 10 and 20 years ago. [Liz Carey]
· World population: 6.5 billion
· Comfortably crowded together= 65 billion square feet = 2,331 square miles. [Mark Houser, Pittsburgh Tribune-Review]
NB: “A multiple choice quiz given before almost every semester’s class for the past couple of decades. Very few students have any conception of the relative sizes of the different ethnic/racial/religious groups in the American population and typically over-represent African Americans, Jews, and (more recently), Latinos.”
· What is the total population of the United States today?
· American Indians comprise what percentage of the U.S. population?
· What percentage of the U.S. population is Asian American?
· What percentage of the U.S. population is non-Hispanic white?
· What percentage of the U.S. population is African American?
· What percentage of the population is Hispanic or Latino?
· What percentage of the American people is Jewish?
· What percentage of the American people is Roman Catholic?
· In 1860, immediately prior to the Civil War, what percentage of the total African American population was free?
· Which is the most rapidly growing ethnic category in the U.S. today?
· What percentage of the U.S. population today is foreign-born?
Answers, except for religion, found at census.gov [Prof. Norm Yetman, American Studies and Sociology, Univ. of Kansas]
· Some measure of broadband penetration for the US, for the most wired countries, and for the area you cover. [Barbara K. Iverson, Journalism – Columbia College Chicago]
Numbers that let you explain numbers in ordinary terms:
· The number of gallons in a typical swimming pool. (Around 15,000 gallons, give or take.) Very useful to describe oil, toxic waste spills. 100,000 gallons equals enough oil to fill nearly seven swimming pools.
· Another from Doig the Elder — the space occupied by a single person in a loose crowd, 10 square feet, or so. Good for counting crowds and deflating overwrought crowd estimates.
· The number of ball bearings that fit in a box car, roughly a billion. Good for explaining “parts per billion.”
· Numbers that let you make on-the-fly measurements. U.S. currency is six inches long. So you can measure feet. Your outstretched arms are roughly equivalent to your height. Figure out your stride so you can pace off distance. The top of your thumb, from middle joint to end, is roughly an inch. Index fingernail is roughly a centimeter.
· Basic metric conversions: Meter=39 inches. Kilometer= 5/8 mile. Inch = 2.54 (I think) centimeters. Ounce = 28 grams. (That one is second nature to those of us who came of age in a certain generation.)
[Neil Reisner, Florida International University]
· (“Credit for the 10-square-feet-per-person rule goes to a Berkeley j-school professor in the '60s whose name I'm embarrassed to have forgotten but who wrote a CJR article in about 1968 on the mechanics of crowd-counting. I've used it a lot.”)
· Two steps of your stride is roughly your height, or so I learned in Boy Scouts a century ago. And to get a good approximation of kilometers, multiply miles by 0.6 (or by 6 and then move the decimal place over one to the left.)
· One other useful formula: The sampling error margin on a poll is pretty close to 1 divided by the square root of the size of the sample; therefore a random sample of 100 respondents has an error margin of plus or minus 10 percentage points. [Steve Doig, Arizona State University]
· The world's population
· Your nation's population and as a percent of the world
· Your state/province/district population and as a percent of your nation
· Your city's pop. and as a percent of your state/province/district
· The percent of change for all of the above in the past 10 years
· The current budget of your nation/state/province/district/city government
· The sub-sections of the above budgets for health, education, public safety, infrastructure and their relative percentages
· The world's live birth rates and same for your nation/state/ province/ district/city
· Average life expectancy for males and females in your nation/state/ province/district/city
· Average family size for your nation/state/province/district/city
· Per capita and per family annual income for your nation/state/ province/district/city
· Average years of education for males and females in the world and your nation/state/province/district/city [Tom Johnson, IAJ, Santa Fe, New Mexico]
Some interesting presentations this morning on visualization and modeling as they can be applied in GIS. See:
Check out http://vissim.uwf.edu/ This is a growing library of public domain shape models. “This website offers access to a new hierarchical data structure that allows the efficient storage of natural and man-made feature data for use in a multitude of both manual and computerized Mapping, Charting & Geodesy systems.”
Also, interesting visualizations at http://www.redlands.edu/x12556.xml