Using crossfilter with Google Apps Script

Visitors to this site will know that I'm a big fan of d3.js. Crossfilter.js (see the link for details, credits, licensing and API usage) is a library to play around with large data sets, often in preparation for visualization with d3, and they fit very nicely together. It's also useful for data manipulation in Google Apps Script. Here's a GAS library enabling crossfilter. 

M-tju_1qulZXW63vIuwyLOqi_d-phDA33

Example

In this example, we'll use DriverFusion to get data to play around with. In this case, I'm using the same list of airports I used for the Flight data from Fusion app, taken directly from a Fusion table

function crossFilterTest() {

// get a bunch of data - all airports
  var fusionHandler = new cDataHandler.DataHandler("1Ug6IA-L5NKq79I0ioilPXlojEklytFMMtKDNzvA",cDataHandler.dhConstants.DB.FUSION,undefined, 'airports');               
  var fusionData = fusionHandler.query();
  
// load it to crossFilter
  var cf = cCrossFilter.crossfilter(fusionData.data);

// how many airports are there?
  Logger.log( cf.groupAll().reduceCount().value());

// create a states dimension
  var states = cf.dimension ( function (d) { return d.state });

// how many airports are there in california
  Logger.log( states.filter("CA").bottom(Infinity).length);

// list all states and the count of airports
  Logger.log( states.filter(null).group().all());
}

Results

// how many airports are there?
  Logger.log( cf.groupAll().reduceCount().value());
3376.0

// how many airports are there in california
  Logger.log( states.filter("CA").bottom(Infinity).length);
205.0

// list all states and the count of airports
  Logger.log( states.filter(null).group().all());
 [{value=263.0, key=AK}, {value=73.0, key=AL}, {value=74.0, key=AR}, {value=3.0, key=AS}, {value=59.0, key=AZ}, {value=205.0, key=CA}, {value=49.0, key=CO}, {value=4.0, key=CQ}, {value=15.0, key=CT}, {value=1.0, key=DC}, {value=5.0, key=DE}, {value=100.0, key=FL}, {value=97.0, key=GA}, {value=1.0, key=GU}, {value=16.0, key=HI}, {value=78.0, key=IA}, {value=37.0, key=ID}, {value=88.0, key=IL}, {value=65.0, key=IN}, {value=78.0, key=KS}, {value=50.0, key=KY}, {value=55.0, key=LA}, {value=30.0, key=MA}, {value=18.0, key=MD}, {value=34.0, key=ME}, {value=94.0, key=MI}, {value=89.0, key=MN}, {value=74.0, key=MO}, {value=72.0, key=MS}, {value=71.0, key=MT}, {value=12.0, key=NA}, {value=72.0, key=NC}, {value=52.0, key=ND}, {value=73.0, key=NE}, {value=14.0, key=NH}, {value=35.0, key=NJ}, {value=51.0, key=NM}, {value=32.0, key=NV}, {value=97.0, key=NY}, {value=100.0, key=OH}, {value=102.0, key=OK}, {value=57.0, key=OR}, {value=71.0, key=PA}, {value=11.0, key=PR}, {value=6.0, key=RI}, {value=52.0, key=SC}, {value=57.0, key=SD}, {value=70.0, key=TN}, {value=209.0, key=TX}, {value=35.0, key=UT}, {value=47.0, key=VA}, {value=5.0, key=VI}, {value=13.0, key=VT}, {value=65.0, key=WA}, {value=84.0, key=WI}, {value=24.0, key=WV}, {value=32.0, key=WY}]

Take a look at the Crossfilter API reference for how do more things with this library.


For help and more information join our forumfollow the blogfollow me on twitter

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.



Comments