Sunday, December 18, 2011

PNC 12 Days of Christmas Price Index

Since 1984, PNC Bank has been issuing their 12 Days of Christmas Price Index.  This index is comprised of the costs of all 12 gifts in the song from the 12 Days of Christmas.  I have heard of the index in the past, but didn't know that it was PNC who put it together or that they have been doing so since 1984.  They have a really fun website this year that involves interactive flash video.  You should definitely right click on this link and "open in new tab" and go check it out.  I'll wait for ya...

PNC 12 Days of Christmas Price Index

I would think this could be fun for children or (if you are like me) those who are a child at heart.

Fortunately, PNC makes their historical data  (.xls file) available for the general public.  So in an effort to spread some Christmas cheer via interactive data visualizations I put together a dashboard of sorts to explore the Christmas Price Index from 1984 till 2011.

A couple of interesting tidbits about this data.  It appears that the bulk of the cost comes from the Seven Swans a Swimming.  And if you look at the yearly trends for a lot of the individual gifts, they tend to have relative periods of stability.  Five Golden Rings is the exception to this.  Also, any of the gifts that involve people have a gradual slope to them, but that reflects the economics of having to pay people and changes in the minimum wage.  To check out their methodology you should go to the media page for their Price Index and click on the FAQ link.

The Data
Probably one of the most difficult parts of this was the data shaping.  Only because I did not want to have to do it manually. I wanted to be able to figure out a way to account for each additional gift that is added each day.  The total amount of gifts is not the sum of all 12 days.  It is actually Day 1 + Day 1 + Day 2 + Day 1 + Day 2 + Day 3, etc etc.  But try as I might, I could not figure out a way to do it before my deadline (I head out of town on the 21st).  I am sure there is a Tableau jedi superstar out there who could probably figure it out though.

The data from PNC comes in the form of YEARS in columns and GIFTS in rows, and the COST in each cell.  So to prepare it for Tableau, I put it into 4 columns:  Date (I used Christmas day), Gift, Cost, and Day of Christmas.  Then I realized that in order to show the total number of gifts for each day, I actually had to add in each additional gift under each day.  For instance, the Eleven Pipers Piping is listed as both 11 and 12 in the Day of Christmas column for each year.  The Five Golden Rings gift is listed as 5, 6, 7, 8, 9, 10, 11, and 12 for each year.  Fortunately, I realized that I could use MSAccess to shape the data how I wanted it (which is good because I was actually doing it manually by copying and pasting and doing it that way was killing me).

Dashboard Design
I am still having problems with sizing containers and worksheets dashboards.  Especially when it comes to legends and quick filters.  They tend to have this nasty habit of popping out of the layout container when you size one of the items inside the container.  Also, I've been having some problems figuring out how to next layout containers.  I work on these visualizations as a way to enhance my skills and get better and quicker with Tableau.  While I think I have made some good progress, I unfortunately, still do not have a good handle on layout containers and sizing them and other items on dashboards.

Visualizing the Data
You would think that with the 12 Days of Christmas, I would be able to use custom shapes to represent each of the gifts.  Well, I would have if I have better skills and software for designing or editing images.  As I mention in my About Me I am a data person who dabbles in design.  Not to mention, there are so many ways to view this data, and I am working with a small space, I just didn't think there would be any way to provide all the different views of the data, while including the images in an appropriate size.

Oh, and one last thing about the design.  Before you dot that "i" or cross that "t" in your comments, I fully realize that red and green are bad colors to use for visualizations.  But come on...give me a break!  It is Christmas after all!

Have a wonderful holiday and thank you for visiting my blog this year!

PS:  More 12 Days of Christmas fun!

No comments:

Post a Comment