Saturday, 31 December 2011

The Playfair Charts: Scotland ExIm Barchart

Soon after finishing the second Playfair chart, the one on Wheat Price and Wages, I searched the internet for additional charts made by him. I found a Bar chart circa 1786, which showed the Scottish export and import volumes with other countries. For me, the real thrill was to scroll by the list of the name of places long consigned to history books - Jersey Is, Greenland, Prussia, Denmark and Norway (together) and Flanders. 

First, the original Playfair Barchart from Wikipedia, 

Then, my version of it in Excel. 

Couple of parting words: 

  • Excel 2007 no longer support dots and lines as fillers for charts. Hence, the ribbed import chart is given a different color, Gold. 
  • Normally, I'd use data point labels to construct the chart legends and other declarations given at the bottom of the chart. However, given Excel 2007' inability to automatically re-size labels to fit texts, I was forced to use text-boxes instead. 

Wednesday, 28 December 2011

The Playfair Charts: Wheat Price and Wages

After successfully recreating the Playfair trade-balance time-series chart, I took up the second chart shown in Jorge's post which is fancifully titled, "Chart showing at one view the price of the quarter of wheat and Wages of Labour by the week from The year 1565 to 1821."

I left out the top arches. Comparatively, this was the easier chart. Staying true to Jorge' rules, I didn't use any shape or clipart objects in this chart. The big oval shaped object in the middle of the chart, which contains the title is actually a marker for a data point.

The original Playfair Wheat price and Wages chart from Wikipedia,

and underneath, is my version in Excel.

Sunday, 25 December 2011

The Playfair Charts: Trade-balance Time-series

In one of his blog, Jorge Camoes talks about recreating the iconic "Playfair" charts. William Playfair (read more about him here), universally heralded as the "founder of graphical method of statistics" is credited with inventing four types of diagrams. The Line graph and Bar chart in 1786, and the Pie chart and the Circle graph in 1801.

In his post Excel charts meet William Playfair, Jorge includes pictures of the original Playfair England export and the prices of wheat and weekly wages charts alongside his recreated versions. He also lays down some rules for this challenge. "A single chart (no overlapping charts), no shapes/clipart to display data and, obviously, no Photoshop."

The original Playfair trade-balance time-series chart from Wikipedia,

and in below, my recreated version in Excel.

Monday, 19 December 2011

Rugby World Cup Dashboard: Charts Re-done

After creating my version of the Rugby World Cup Dashboard, it is time to redo their existing charts. My gripes were regarding the Treemap, the Stacked Bar and the Line charts, and here, I will attempt to offer easier, cleaner and more legible alternatives.

My alternative to the Treemap Chart,

The Stacked Bar chart, 

and, the Line chart.

Thursday, 15 December 2011

Rugby World Cup Dashboard

The Rugby World Cup Dashboard will live in my memory long for various reasons. Ever since I first received a mention of it in the pages of Bime Analytics, it haunted me with its collection of charts singularly inappropriate for their purpose. And what a collection of varied charts!! No two alike, it features a Treemap, an exploded Pie-chart, a Column chart, a Bar chart and a Line chart.

Left to me, I'd have changed the color of the theme, reduced the number of charts and designed the dashboard this way.

The dashboard now includes more information, and all at the click of a button.

Saturday, 10 December 2011

Wacky and Colorful

I often receive interesting ideas and suggestions from friends which forms the basis of some weird visualization. While personally I prefer to keep my charts simple, effective and legible, I go creative listening to ideas from others.

A friend of mine recently changed jobs. Before making his first presentation, he wrote to me about designing a chart which would WOW all his audience and help him to make a dramatic impact.

The data was about units of products sold in each region and compared with similar data of competitors. Needless to mention, a simple column chart would have served his purpose well. But not to my friend. On his insistence, I drew up a dummy table, and served him the following.

They look like abstract paintings. Right? Utterly amazed and curious, I thought of using this template on the Eurozone Debt data and came up with an even more wackier and colorful one.

In both cases, the width of the lines are indicative of their values. I've avoided using labels in the second chart thinking they would add more clutter.

Thursday, 1 December 2011

Miming A Chart

Unbeknownst to many, the BBC News business page is home to some interesting data-sets and wonderful infographs. So, when I came across the piece titled, "Eurozone debt web: Who owes what to whom?" which revealed, with the help of an interactive chart, what the countries owed each other, I knew I had to dabble with the data.

Replicating that specific chart is close to impossible in Excel. But then, it got me thinking. do we really need a chart to visualize this data? Recognizing the boost conditional formatting received in Office 2007 and 2010 versions, I decided to use its power to mime a chart, rather than creating one.

I gathered the needed data from the information displayed in the chart itself to draft the table for the amounts the countries owed each other. And very shortly, within ten minutes, had my "mimed" chart ready.

Saturday, 26 November 2011

Repeat Violations, Repeat Promises

After regaling us with an Unemployment Panel Chart a week ago, Paresh picked out yet another chart from the NYT pages and asked us to recreate it and improve it in the process.

The chart in question is a dot-plot (learn about them here), that shows the instances of repeat violations by big wall street firms after promising the government never to breach such acts again. Data shows 29 violations of section 17(a) of the Securities Act, and 16 infringements of section 15(c) of the Securities Exchange Act between 1997 - 2011.

The chart was easy to replicate in Excel. For improvement, I shaded every repeat instance in a different color and added labels to show the count.

Tuesday, 22 November 2011

The Cricket Graphs: The Forgotten Chart

The fun of receiving feedback to blog-posts is that one isn't quite sure what they might come up against. While mostly it provides fascinating discussions, insightful comments or explanatory questions, there is also the odd occasion when it throws up unusual requests.

The other day I received an email in response to my post, The Cricket Graphs. The sender wanted me to create a "Partnership" chart, which would show the runs scored by the pair of batsmen for every wicket and the balls faced. I went searching for data in the Cricinfo website, and picked out the 3rd ODI between the South African and Australian cricket teams which was played out in Kingsmead, Durban on October 28, 2011.

The Partnership details were taken from the page titled Partnerships Table and the total balls faced by individual batsmen from the Scorecard. Constructing the graphs was a somewhat tricky affair, with two potential bar charts in two axes representing Balls and Runs, had me wondering how to incorporate the XY charts and show the other necessary details required in the chart. However, some tweaks later, the charts were created.

The South African partnership chart:

The Australian partnership chart:

Wednesday, 16 November 2011

Unemployment Panel Chart

Today we discuss yet another chart mentioned in Paresh's blog. In his post last Sunday, Nov 13, Paresh talks at length about small multiples, Otherwise known as Trellis Chart, Lattice chart, Grid Chart, or Panel Charts as a powerful tool of visualization, and asks us ways on how to replicate the chart published in NYT.

Soon thereafter, I see Chandoo post a tutorial on how to create such charts. But, he uses 3 separate ones to achieve the "Panel" effect.

Here is my response to the question. Since I couldn't locate the data for the charts, I've used approximate values.

Sunday, 13 November 2011

Correcting Wiki Charts: Mauthausen-Gusen

My third pick from the list of abhorrent Wiki charts mentioned by Jorge Camoes is the one from the page of Mauthausen-Gusen, originally a collection of villages of Mauthausen and Gusen in Upper Austria, which became one of the largest Nazi concentration camps by the summer of 1940 and the place of death of several hundred thousand inmates.

There are several reasons for finding this serving of pie unpalatable. Firstly because, it is a "Pie Chart". This same data could be more easily and effectively displayed using a column or a stacked bar chart. Secondly because, it is an exploded pie chart. I prefer an exploded pie chart where the exploded slice merits special attention from the rest. To otherwise create this type only succeeds in bringing a ragged look to an already poorly crafted chart. Third, and most importantly, the use of flags as legends and with it, the redundant use of data labels to state the name of the countries that is absolute chart junk! I'd either have the flags or the names as legends.

I've replaced the exploded pie-chart in favor of a 100% stacked-bar chart. Labeling the flags with the name of their nations didn't appeal to me much, hence the only labels in the chart are those showing the death percentage for every country.

This is, my version of the Mauthausen-Gusen chart.

Saturday, 12 November 2011

Correcting Wiki Charts: Blackpool FC

After the Throughput Accounting chart in Wiki, my second chart of call is the Blackpool FC pie chart. With their obvious disadvantages, I prefer using them on the rarest of occasions. So let's take a look at the follies in this one.

The chart portrays the names and the tenures of all Blackpool FC managers since the club's inception. Ideally, and supposedly, such data is best displayed along a timeline. Instead of picking among the available alternatives, the creator picks a circular shape with no obvious beginning or end points. The slices in increasing order of fading shades of the club's color - Tangerine, is vague and whitish towards the ends, leaving the reader with no definite hint on when the managers tenure starts or ends.

With the limited data on offer, I consulted the Blackpool FC Wiki page for additional info and created my alternative in the shape of a Gantt Chart.

Simple, uncomplicated, chronological and instantly legible.

Correcting Wiki Charts: Throughput Accounting

In one of his earlier posts, Jorge Camoes calls for changing bad charts in Wikipedia. Therein, he  embeds several examples of charts, which can be easily considered "atrocious". Here, I'll attempt to correct the one titled "Throughput Accounting".

The embedded Wiki chart is a 3D horizontal cylinder chart and a contains a long definition of Throughput accounting (T) and it's structural components. The definition also includes two equations, T=Sales less TVC and NP=T less OE.

The 3D aspect of the chart serves no special purpose, and the cylindrical shape of the columns with the alternate blue and white bands seems intended for enhanced aesthetics. The columns are arranged without bearing in mind the relationship between the components, and the chart looks somewhat ragged overall.

I replaced the look of the chart from the 3D horizontal cylindrical to a "Waterfall" chart, using stacked columns. I also arranged the columns in reverse order to bring out the relationship between them that dispenses with quoting the two equations mentioned above.

My version of the Throughput Accounting chart is as follows:

Tuesday, 8 November 2011

Cancer Deaths and Survival Rates

This is a blog written at the Airport. I had an hour to wait for my flight and could think of no better way to spend the time.

I love creating different charts in Excel. It keeps me company in my weekend afternoons and there is the immense benefit of learning something more about Excel.

I looked at this chart in Paresh's blog briefly the week he posted it and thought of recreating it in Excel. Then a family tragedy, and more urgent and important matters pushed this back in my to-do list. Now that all is cleared, I finished making the chart in the airport lobby!

The recreated chart:

And my visualization of the data with some noted difference:

I've chosen to substitute survival rates with mortality rates, and for a valid reason. Since the chart also displays the number of deaths caused by the individual forms of Cancer, I surmised the display of mortality rates would be more appropriate with that figure.

Monday, 31 October 2011

Herman Cain's 9-9-9 Plan

The name of Herman Cain came up a few times in my reading materials recently. A former business executive, he is in the news as the leading Republican presidential candidate in the primaries, and for his 9-9-9 Tax Plan.

Kaiser, of Junk Charts has made a couple of attempts to visualize the 9-9-9 Tax Plan, and its possible impact on people belonging to different cash income quintiles. One can read and learn about his first attempt here. In all fairness, the chart appears somewhat mangled and hence lacking clarity and the utility for the purpose.

At first glance, the lines looked far too thick and the labels were wrongly positioned. But the most critical omission is the missing out on 6.2% of lowest quintile population who remains undisturbed in this tax shake-up.

The data table is available here. I decided to keep the vertical and horizontal axes similar. The horizontal axis labels are brought down below and major gridlines are formatted to create quintile panels every 20 percentage points. The chart lines, which were plotted overlapping each other in the original chart were plotted within their respective quintile panel to render legibility. Finally, labels were placed to mention the increase/decrease of tax for the percentage of people in the quintile.

The new chart clearly shows the answer to "How Tax hike will affect different income groups"? - The higher the income, the greater is the tax relief for a larger number of people.

Friday, 28 October 2011

Using Box Plots Differently

For those of us into performance consulting, Box plot (Know more about them here), is a wonderfully simple and efficient tool to help us get about our job. As every coin has two faces, every data-set has two aspects. One, is the "feel-good" part of it, the part which tells us about the successes and the top performers. Second, is the "ouch" bit of it, that gets us notice the outliers.

Usually, the Box-and-Whisker diagram is used to plot and separate data as the smallest and largest values, the median, and the lower and the upper quartiles. But, the shape and the style of it can be applied to depict other sets of data as well. Here, I've used the B&W template to depict the monthly temperatures of my home city - Calcutta.

The necessary data was gathered from here and there. It included the data for record monthly highs and lows, which were depicted in the chart by the whiskers, and the average monthly range of normal temperature, which is represented by the box, and the average monthly temperature by the line. The maximum and the minimum temperature, of the months of a specific year, taken here as 2010, is represented as a range by the violet-column to show its position vis-a-vis the Box and whisker graph. 

The final look:

Friday, 21 October 2011

US Open Tennis

Charting a sporting event is always great fun. More so, if that happens to be the vibrant US Open Tennis Championships which I came across in this piece. But to me, it appeared somewhat wayward and lacking purpose. Most of its charts could have been more efficiently represented through a table, and the only one which made some sense, or added some value was the one which depicted US women’s singles champion by country over time (1900 - 2011).

I decided to approach this from a different perspective. Firstly, I separated the amateur years from the open era which began in 1968. From 1881-1911, the US Open used a challenge system whereby the defending champion automatically qualified for the next year's final, creating in the bargain, some unbelievable feats like Richard Sears remaining undefeated in the tournament and winning the inaugural seven editions of the Championships. Furthermore, the difficulty in travelling to and from the USA in the earlier years can also be attributed to US ladies winning 26 of the first 28 editions, with only Mabel Cahill from Britain managing to break the stranglehold, and the men topping it by winning all but one between 1881-1925, when in 1903, Lawrence Doherty triumphed.

It also threw up some quirky facts:
  • Players from 5 nations have won the US Open Men's & Ladies Championship before the Open Era. 
  • Players from 11 nations have won the US Open Men's & Ladies Championship in the Open Era. 
  • In 44 years of US Open since 1968, a total of 44 players has won either the Men or the Ladies Championship. 
My Chart looks as follows.

Pick a Country:

Pick a Year:

and finally, Pick a Champion:

Wednesday, 12 October 2011

Views of Parties' Ideologies

US Politics has always intrigued me. Firstly because it is the oldest democracy in the world, and is one of the strongest. Secondly due to its bipartisanship. As someone in India, where new political parties are formed on a daily basis, without any rhyme or reason; it never ceased to amaze me, how the system, and more importantly, the two parties have been able to adapt, transform and evolve over time to appeal to the populace and cater to their needs and aspirations.

The dataset is taken from the Pew Research Center for the People & the Press article captioned, "More Now See GOP as Very Conservative". I chose to present the snapshot based on the results of the survey conducted between Aug 17-21, 2011. 

I chose to keep it simple, 3 charts for 3 tables, and space for Information synopsis for those seeking to read more into the numbers.

Once finished, I had a wicked idea in mind. What if I were to bind my three separate charts into one? It looks good, but doesn't improve on anything, other than saving some space.

Friday, 7 October 2011

Graphing Obesity Trends

The five days of Durga Puja was stupendous as usual. Lots of great times with friends, all my favorite dishes for meals, and with no restrictions, it was a fantastic experience yet again. The countdown to October 20th, 2012 for the next years festival looks a long wait indeed.

This week's visualization was posted as a challenge in on Apr 29th, 2010. It received several novel replies, none more appealing than the heatmaps. One of the problems of approaching a long expired challenge is that all regular and normal forms of visualization is already considered and posted.

When I looked at the data, it was apparent that the distribution could be either viewed with respect to the Age-Group or, the Year of Birth. I therefore sought to plot years of birth along the vertical axis, and the obesity percentage on the horizontal axis, for every panel separated by the age group.

My visualization for the Challenge, is as follows:

A second version of the visualization:

Wednesday, 28 September 2011

Visualizing Our News Sources

While browsing the other evening, I came across a presently expired visualization challenge on the changing sources of present-day news. It talks of Internet gaining on Television as public’s main news source as more young people cite Internet than TV. You can read about the challenge and the article here

Among many who replied was Ole Ă˜string, who submitted this unique chart/table in response. Here is my reproduction of it in Excel.

Saturday, 24 September 2011

Process Quality Dashboard

Looking back isn't always a bad thing. Pleasant memories aside, it also allows one to review earlier works in the light and knowledge of current experience and to realize how much path we have journeyed on since.

I found a folder in my old computer the other evening and it was password-protected. Utterly curious as to what it might contain, I opened it to find copies of monthly dashboards I made in my earlier organization. Thereafter, it was mostly reliving old memories as I browsed through them all, looking at them and thinking of making them better.

One of my favorite dashboards to make then was the Quality Dashboard. 10+ teams, 10+ performance parameters, 100+ associates, it was some ordeal to get all of them in a series of PowerPoint slides. Some dashboard too! Over 15 slides and in excess of 20 charts!

I copied the data to a fresh Excel worksheet, altered and changed some of the names, and started a dashboard which in 3 simple tables will allow the user to review the overall program, the performance of the teams and finally, the performance of every associate.

Remembering the extra hours we needed to put in afterwards to salvage the names of top performers and outliers for R&R and feedbacks. I designed the table to highlight such names automatically. For a few hours of effort, a lifetime of reduced stress in office.

Annie Lausier's Calendar Approach

As I've raved about in my earlier post, Annie Lausier' Calendar Approach in response's "10 Visualizations for Number of Days to Pay Your Taxes" was so far beyond peoples expectations that it received comments almost bordering on adulation, prompting one to comment, "This was definitely one of my favorites, partially because of the smooth interaction and partially probably because I wasn't expecting it."

Her work is available here. It features a very intuitive approach, and a two-way interaction between the dates and the states. In my attempt to see how far I can progress in two hours of working with the data, I came up with the following. 

I'm happy to leave it at the stage it is now. Clicked on any of the pink-shaded dates colors the states for whom it is the Tax Freedom Day.

Thursday, 15 September 2011

Tax Freedom Chart

I bumped into yet another old but beautiful visualization challenge this evening.'s "10 Visualizations for Number of Days to Pay Your Taxes" may have ran in 2009, but it still features some wonderful approaches to data visualization and I had a great time exploring them in some detail, but not before I had made my version of the chart as shown in the picture below.

While exploring, my attention was immediately drawn towards a very unusual "Calendar Interface" designed by Annie Lausier. It was a standout even among some very unique others. I know it would be mighty difficult to replicate it in Excel. For, even with VBA, it will be a tiresome effort. But, I'm willing to give it a couple of hours and see what I can etch out.

Saturday, 10 September 2011

US Unemployment

Replicating a newspaper chart is always a lot of fun, and it was to be no different with this one. The Sep 8, 2011 article in Guardian includes a  chloropleth map to display US unemployment percentage by state. While data is available for every month between November '08 - July '11, the Guardian drop-down box only allows us to pick four months out of 33!

Chloropleth maps are thematic maps in which areas are shaded or patterned in which shows to the measurement of the statistical variable being displayed on the map, such as population density or per-capita income, and post Excel 2002, are mighty hard to create in Excel. I've saved a Jorge Camoes tutorial for years on how to make one, and now, in my last week of recuperation, I've finally had the time and the patience to attempt and complete one.

The finished map and table looks as below.

Moving the scroll bar changes the colors for the states.  The table to the right categorizes unemployment percentage into  4 groups of Critical, Dangerous, Threatening and Consistent- and lists the names of the states under each group.

Thursday, 8 September 2011

US Census

The other evening I came across the US Census data for 1910-2010. Even though I have worked with population data before, this one was hard to pass by. The data is available in site under "Eyeo Data Visualization Challenge" and it does include some nice flash charts to review, compare and analyze the Decennial Census Data.

I've excluded Puerto Rico from my list of State, Regions and outlying territories as it doesn't belong to any of the four census regions of USA.

For Pick A State, I've used a data validation list. Just didn't want a combo box sitting amid my nicely formatted dynamic labels.

My visualization of the census data looks as shown below.

Tabbed Dashboard

I get to work with Google docs a lot. While its spreadsheet is relatively rudimentary and only offers a basic level of customization, I've really grown to love the various gadgets which helps the user quickly build up Gantt charts, Time series with annotations, Heatmap, Spider charts and Word clouds. I was very charmed by the tabbed feature of the Heatmap and wondered if anyway, that feature could be "visually replicated" in an Excel worksheet without using VBA.

I still had the data from The Pac-Man Dashboard handy, so I started off without any definite schema in mind. Ready to use regular Excel features like conditional-formatting, form element and embedded objects to aid me achieve my goal. Sure enough, in three hours, the following was laid out.

Tabbed Business Overview Dashboard (Region)

Tabbed Business Overview Dashboard (Sales Person)

Tabbed Business Overview Dashboard (Product)

And finally, Tabbed Business Overview Dashboard (Customer)

The Pac-Man Dashboard

Over this weekend, in a random conversation, A friend asked if I can build a dashboard entirely of pie charts. Now that is a serious challenge! Pie-charts do not rank among my favorites, and to build a dashboard entirely of them is definitely not a fantasy of mine.

I looked into my collection of excel spreadsheets for a data table. I found one, but unfortunately do not remember where I've downloaded it from. It contains month-wise performance of 4 sales representatives, for 3 products, across 3 customer types, 4 regions and over 2 years.

After adding couple of columns to the original data and creating some much needed tables, and I was ready to set up my pies; - Oops, pie-charts. In about two hours, my dashboard is done. Can't help but feel all the pie charts give it a "Pac Man" look.

The top section of my "Pac Man" dashboard,

And the lower section of it.

Overall, it has been some time well spent and some valuable lessons learnt. This far and no further with pie charts!