Touch the firehose of ds106, the most recent flow of content from all of the blogs syndicated into ds106. As of right now, there have been 92503 posts brought in here going back to December 2010. If you want to be part of the flow, first learn more about ds106. Then, if you are truly ready and up to the task of creating web art, sign up and start doing it.

Punchcard charts in Google Sheets/Spreadsheets (querying Google’s plan to drop gadgets)

Posted by
|

A couple of days ago Mat Morrison posted:

I’ve been messing around with Excel; trying to create a “punchcard chart” that will let me visualise data by hour and day at the same time. This is where I’ve got to so far, using nothing more than COUNTIFS and SUMIFS.

It works, more or less, although there are some bits to tidy up; notably that y-axis, which should really read Sunday through Saturday or the like.

Here’s a link to the Excel workbook if you want to try this out: https://docs.google.com/file/d/0B26bhH2SxecqcDN4c3JvYTlfYTA/edit?usp=sharing

Punchcard example in Excel by Mat Morrison

Knowing that Google Sheets/Spreadsheets permits the QUERY formula which allows SQL like data manipulation I thought I’d give it a try. The result is mixed. As thought reshaping the data was relatively straight forward using a query along the lines of:

Query data in google sheets

 =QUERY(raw!A:A,"SELECT COUNT(A) WHERE HOUR(A) = "&C2&" AND DAYOFWEEK(A) = "&D2&" LABEL COUNT(A) ''")

where raw!A:A is some datetime data (in this case imported from TAGS).  Here’s the source spreadsheet if you want a closer look at the formula.

So far so good. Next to graph the data. This is where the trouble starts. First here’s the result (click for the interactive version):

punchcard - bubble plot in google sheets

Looking at the bubble chart option it says:

The first column in the table should be text, and represents the label of that bubble. The numbers in the second column are plotted on the x axis. The numbers in the third column are plotted on the y axis. The optional fourth column should be text, and determines the bubble colour. The optional fifth column is numeric, and determines the size of the bubble.

Okay … so in the first image you’ll see I’ve had to start spoofing columns. In B:B I’ve had to create a column of unique labels. In E:E I needed to create a dataset for results where the count is zero. This is because the bubble chart will render bubbles even if the size is zero. To get around this I’ve tried removed the fill on zero values. Another aspect I’m not entirely convinced with is the bubble scaling, but I’ll let you decide.

The really worrying aspect of all this is given the recent announcement of Deprecation of Gadgets in Google Spreadsheets which allowed you to embed your own charts in Google Sheets (here’s an example gadget I wrote to include a d3.js force layout graph), come July/August  if I want to visualise data in a spreadsheet all I’m going to be left with is Google’s crappy charts … sigh :(

Add a comment

ds106 in[SPIRE]