Hadley Wickham has added some data management capabilities to R which are pretty useful for getting data ready for tabulation or visualization. One of these is melt(). Melt can do lots of things, but one that caught my eye in particular was the ability to turn a tabular data representation in to a transactional one – a kind of unpivot. In other words to turn this

into this

Tony Hirst pointed this out in one of his posts about R, and it got me thinking that mashing up something like this (a kind of unpivot table) in both VBA and Google Apps Script would be pretty straightforward, since I have all the components already. 

Usage

So lets start with how to use it. At a minimum we need to say something like this.



		


This looks like a strange combination of javaScript and VBA – and I guess it is.  I’m trying to both minimize the work to make a google apps script version of everything so using jSon for specifying options is a good way to average things out.  If you need to recap,  I cover jSon arguments in Excel in this blog post.
reshapeMelt actually returns a cDataSet of the output data, so it can be used directly to chain to other procedures that know how to process these. I’ll show and example of this in a later post.

Options

All options are specified in one jSon string (or as close to one as I can get within the confines of VBA syntax). The full range of options are as below. I’ll no doubt be adding to this as a template for all default options for this topic.



		


Walkthough

The arguments work like jQuery $.extend() in the sense that any arguments you do specify simply override the defaults. In this case “{'outputSheet':'meltOut','id':['id','time']}”  xx outputSheet specifies where to put the result, and  the ID area specifies the columns that need to be combined to produce a unique key for each generated transaction – just like in the rshape addon – melt(mydata, id=c("id","time")) 

The code

Most of this is just patching together existing capabilities – mainly cJobject and cDataSet. I had to add a few bits and pieces to cJobject but nothing major.  I’ll post the Google Apps version shortly.



		


Setting up the default options for re-use



		


Modify jSon args to go as many levels deep as required for optional arguments



		


A few more methods for cJobject to be able to tweak existing cJObjects



		


And that’s it – happy melting. You can download all this at Downloads in the cDataSet.xlsm. 


For more fooling around with jSon in VBA, take a look at jSon 

Continue reading about Rest to Excel Library here