Excel Heatmap Visualization

We're working on a large-scale project with a client who must remain nameless.  Our main contact had a high-level presentation; they wanted to make a lot of complex relationships clear, more-or-less instantly.  

For client reasons, "heatmap" was the visualization of choice. (Don't get me wrong - I understand the appeal of heatmap-style visuals.  I'm particularly fond of the iPad/iPhone app Stocktouch because it makes such elegant use of heatmap visualization.  The whole stock market, at a glance, on a single iPhone screen.  Wow.)  But markets are, by definition, a bunch of like measurements of unlike things.  "Like Measurements" is the key concept there - if AAPL is more red than GOOG, it's "as measured on the same scale."

OK, that's the fun & challenge of helping clients.  You wanna heatmap, we gotta heatmap inna back.  We knew that the data was (cough) in flux, so that ruled out hand-rolling color scales in Illustrator or InDesign.  The magnitude of cross-comparisons probably would have ruled those out anyway - 10 groups, 30 different measures, 3 different comparison-range variable PER MEASURE.   (You want to try hand-calibrating 9000 colored blocks against each other, knock yourself out. Not me.)

So the tool we turned to: big data geeks may sniff at this, but i'll meet 'em at the OK Corral any day with Excel in my holster.  It wasn't a perfect solution, but the Conditional Formatting tool in Excel does include color scales, and we were able to engineer all of the visuals directly in Excel. Here's a snapshot - variables and data changed, of course. 

View fullsize

There are actually 27 color scales here - one per row.  Unlike the stock market, each row had different units of measure and a different basis for comparison; high wasn't always Tempus.  Where Excel cooked on this heatmap problem was in enabling all of the calibrations and comparisons, and letting us change formulas/relationships quickly.

The color scale conditional formatting isn't a perfect tool by any stretch.  Really fundamental Excel tools can be manipulated with & against each other - range references, functions, that kind of stuff. The color scale conditionals are more-or-less 'hard coded' on each row - for example, the LOW and HIGH references are absolute cell references.  (Meaning, move the scale and the whole thing breaks...)  Plus - and I know this is a nit - the only way to manipulate the conditional formatting is to click through the stupid dialog boxes.  

All that said - if you want to take a heatmap view of something out for a run, it's not a bad way to go. Yeah, Processing or R could probably do it better, but deadlines and data-changes make coding solutions difficult.  If the format gets fixed/frozen, we'll give that a shot. 

If you want the actual Excel sheet...enjoy!

--md

Another Excel heat map post here. 

P.S.  Great post on heatmaps and color blindness from our favorite technical partner, Wistia. 

Italian Shoes

Me:  jeans, t-shirt, buffalo mocs.  '46 Willys Jeep, frequently in shop.

Italian guys:  silk suit, perfect casual dress shirt, loafers.  Vespa scooters.

Taste and design sense must be in the water supply there or something, because work like this - heck, did I need to tell you it was from Italy?  It's a visualization of Harold Bloom's book Genius, from the marvelous Giorgia Lupi and Accurat. 

View fullsize

A visualization of a book on writing...let's pause for a moment and enjoy that.  Ciao.

-md

Putting the AH! in STEAHM

The Commission on the Humanities and Social Sciences just released their report, The Heart of the Matter.  Does this have anything to do with complex subjects, visual communications, technology, business, and all that stuff?  Yes - everything.

"At the very moment when China and some European nations are seeking to replicate our model of broad education in the humanities, social sciences, and natural sciences—as a stimulus to innovation and a source of social cohesion—we are instead narrowing our focus and abandoning our sense of what education has been and should continue to be—our sense of what makes America great."

I serve on an external board for the College of Sciences and Technology at Western Washington University.  Every third sentence someone says "STEM Education" (Science, Technology, Engineering and Mathematics).   My rejoinder is "STEAHM Education" - do let's please leave the Arts and Humanities in the equation.

As an "arts person" it's easy to come up with the broad emotional and cultural reasons; they seem obvious.  But then I put that aside, and put on my business hat.  Is there really a business justification to arts and humanities education? My response would be, the very fact that our business is viable is evidence of the need for, and value of, putting the AH! in STEAHM.   

"Ah!  I get it!"  The utterance is self-defining, and the meaning critical.  The amount of data in the world is rising dramatically; the amount of information, less so but likewise. Discerning the knowledge in all that information is a humanities challenge. If we need a shared understanding to do our jobs - build a pyramid, launch a rocket, or whatever - we're not going to get that shared understanding by staring at our hard disks.  We need to be literate ("-iterate" - it's not just a question of language any more) enough in our field to create and consume the communications that enable understanding.

Beyond 'grasping the point someone else is making' is knowledge creation - making your own points.  There, again, the AH! dimensions of education are vital.  Invention, innovation, research, discovery - these are creative acts of the mind.  The AH! disciplines foster the mindset and toolset - and the teamwork, I think - that make for more and better knowledge creation, no matter the field. I'm not saying there's no creativity in science education - actually, quite the opposite. But at their best, arts and humanities foster playing with boundaries, rather than within, and that makes all the difference.

The roster of people involved in this report was stunning - YoYo Ma, John Lithgow, Emmylou Harris, Ken Burns, David Brooks, David Souter, John Warnock (Chairman of Adobe), James McNerny (Chairman/President/CEO of Boeing) - it's really quite something. These are serious people, making a serious point. 

CP Snow codified the science/humanities rift in The Two Cultures, over fifty years ago.  It's been critiqued - I think with some justification - yet the push for STEM rumbles of that rift re-opening.  It's heartening to see a national-level endorsement of the vital, competitive value of maintaining and upgrading the educational bridges that cross it.  

 --md

2,000 Years, 1 Little Graph, ROYGBIV

I'm not sure whether to type "love it" or "hate it."  It's fascinating to see a huge topic addressed in an itty-bitty chart.  Look it over a bit. 

The Economic History of the Last 2,000 Years in 1 Little Graph

What's not to love?  A few things, at least from my point of view.  The complete rainbow of colors - including the opposing-primaries for China and the US - aren't really necessary.  It would probably read just fine if it were monochromatic - it's not particularly hard to follow the consistent stack.  I think I'd run really light grey % lines across the whole thing - eyeballing whether the US (for example) was above or below 40% in 1960 is a little tricky.   

The now-it's-logarithmic, now-it's arbitrary historical scale is a bit dodgy.  1900-1913 get the same horizontal allocation as 1913-1940.  Of course since there weren't any major economic upheavals during that period, it's probably OK.  This may well have been driven by available data, but still...

I'm surprised at the UK's relatively small GDP proportion during the sun-never-sets phase.  I'd have expected more, frankly, and it makes me wonder about the definition and figures of GDP.  Was the US really a bigger player in 1870 than Olde England?

All that said - read the article.  Or give the chart to a middle-school kid and tell 'em to explain some of the big spiky change-points. They may well remember this one garish chart better than their whole textbook.

 -md

PS anyone in the Netherlands, Finland, Brazil, etc feeling a bit slighted?  

Truth With Visuals

Is this your mental picture of the last national election?

Or was it more like this - the 'usual' view on TV, Web, etc.

This is a new cartogram from the US Census Dept, showing the size and overall distribution of the U.S. population as of 2010.  The top image is just a quick recolor.

The usual view doesn't represent the facts very well.  It lets geography express population.  Big blotches of RED (i'm lookin' at you, Wyoming) and BLUE (Nevada, not  so much) are telling us a very misleading story.  If a national publication put exaggerations of this sort into words and numbers, they'd be pounced on by fact-checkers.  But, for the moment, it's still acceptable to mis-state visually. 

The recolored cartogram tells quite a different story from the default geographical fill-in at the top, doesn't it?  The usual view says to your eye "RED WON!" - there are more red pixels than blue, because of geography.  (Alaska to scale would really tilt it.)  But of course we're supposed ignore that, tap Wikipedia and run 50 quick population-to-land-mass calculations in our head and ignore what we see. Sure. 

The colored cartogram feels more like that fatal point in Risk where you really wish you hadn't gone strategically Fortress 'Merica.

Does stuff like this matter, or is it a nice-to-have?  I wrestle with this, particularly when we help clients with presentations.  They've got the full set of power-tools (-point), but they're frequently torn between making sense and making an impression. Bad facts and calculations propagate themselves WAY too easily in visual forms. ("Hey, that's a great visual, let's re-use it in this other deck.")

What do you think?  Should there be a grading scale, or a code of ethics, for truthfulness in visualization? 

--md

Nasa's state of the planet in graphics

This collection tells a compelling story of the current state of planet Earth. Instead of telling you in writing, Nasa is saying 'look for yourself' and discover the implications of global warming. This is a great example of information hierarchy - you get the message just by going to the main page. The further you drill down into each section, the more detail is presented to the user, therefor allowing for an exploration of the topic at their own level of detail.

http://www.nasa.gov/externalflash/earthmonth2013/index.html