6 ms·

PivotTable.js: a JavaScript Pivot Table implementation

joshuaellinger 13y ago
Yummy. Couple that with a column-orient database and I don't need OLAP any more.
jsmeaton 13y ago
Excellent work! There are no great libraries to handle this type of work anywhere, that I've found, without requiring an entire stack change. My next project was to build something very similar to this. I've done the same before in PHP many years ago for the company I used to work for so I was confident that I could do it. Having this to leap frog my development is going to be extremely useful. And by that, I mean any extras I'd develop I'd contribute back. Now to start playing.
skeletonjelly 13y ago
Would like to see a demo of this with a large data set.
- nicolaskruchten 13y ago
  I'll work on getting something like that posted. I want to demo some of the fancier aggregation functions as well :)
aheilbut 13y ago
Looks interesting... If doing everything in javascript is not sufficient, another nice open source project with a slick interface on top of a real OLAP server (Mondrian) is Saiku - http://analytical-labs.com/ http://analytical-labs.com/
- jsmeaton 13y ago
  I've looked into Saiku and it has a very pretty and usable interface but, as you say, it requires the OLAP server. Not very useful if you plan on doing ad-hoc reporting with django as a backend for instance.
shtylman 13y ago
This is not a JS library!! This is coffeescript. Stop calling coffeescript stuff .js it is really misleading for people that actually develop in JS.
- nsp 13y ago
  How? It compiles down, and produces(not-great but decent) JS, and be included normally, why the objection?
  - hobs 13y ago
    Maybe he wants to contribute? That's all I could think up...
  - ianstormtaylor 13y ago
    Have to agree with the OP. Even though I can generally read CoffeeScript I'd much rather have all the code I depend on be on JavaScript because I much prefer contributing to JavaScript code. I also feel like CoffeeScript encourages incredibly complex practices just because the code can be written in a small amount of space. For example, here's a block of code from the library in question: sumOverSumBound80: (sigfig=3, scaler=1, upper=true) -> ([num, denom]) -> -> sumNum: 0 sumDenom: 0 push: (row) -> @sumNum += parseFloat(row[num]) if not isNaN parseFloat(row[num]) @sumDenom += parseFloat(row[denom]) if not isNaN parseFloat(row[denom]) value: -> sign = if upper then 1 else -1 (0.821187207574908/@sumDenom + @sumNum/@sumDenom + 1.2815515655446004*sign* Math.sqrt(0.410593603787454/ (@sumDenom*@sumDenom) + (@sumNum*(1 - @sumNum/ @sumDenom))/ (@sumDenom*@sumDenom)))/ (1 + 1.642374415149816/@sumDenom) format: numberFormat(sigfig, scaler) Have fun reading that... I think claims that CoffeeScript is a write-only language have very legitimate ground.
    - mokkol 13y ago
      This is misleading. Of course like Javascript you can write terrible CoffeeScript code. There are plenty of terrible written Javascript code examples out there.
jasoncrawford 13y ago
Nice! PivotTables are underrated.
elchief 13y ago
Looks nice and light weight. Also check out Saiku: http://demo.analytical-labs.com/ http://demo.analytical-labs.com/ Talks to OLAP server over XMLA (like Mondrian)
feniv 13y ago
I'm working on a analytics-related project and this looks like something I can use! The input format is a little restrictive, but understandably so (since this is meant to be used for tabular data) The data I'm working with is a little more nested and looks like : [ { color: {r: 0, g: 0, b: 255}, shape: {name: "circle", description: "A round shape"}}, {color: {r: 255, g: 0, b: 0}, shape: {name: "triangle", description: "Has 3 sides"}} ] than the example that was provided.
fibo 13y ago
I just used this other one for a project at local.italiaonline.it and it is really good (all sums are ok :) http://rjackson.github.io/pivot.js/ http://rjackson.github.io/pivot.js/
kachhalimbu 13y ago
This is pretty cool and fast. For Java people check out ZK Pivottable http://www.zkoss.org/product/zkpivottable http://www.zkoss.org/product/zkpivottable
petilon 13y ago
This is very cool. But the inherent limitation here is that it only works well for small datasets. If you have a million rows you don't want to download it all into your web browser and summarize it in the browser. Instead you want to summarize it on the server and only download the summary to the web browser.
- pc86 13y ago
  Agreed; to be fair though, is it really appropriate to use a client-side pivot table library if your use case necessitates a server-side approach?
- dspillett 13y ago
  For a lot of data-sets the amount being passed around is going to be small enough though. I expect CPU power to be more of a limit than bandwidth: depending on what grouping options the user picks a large dataset could take a lot of time to draw making the interface feel very clunky, particularly if like us you have a chunk of your user-base stuck on classic IE instead of more modern browsers. We are working on something similar to enhance our app's existing custom reporting features. While we're erring in the side of "client side will be fine for our current requirements" if you don't mind the extra state storage server-side (or extra database hits to re-collect the data from the back-end each time) you could keep the data there and pull the results back via AJAX calls - with careful design you could even be share the slicing/dicing code between client & server and make the choice dynamically (if the data is small send it all to the client then you are as responsive as possible, if it is above a certain payload size keep it server-side to improve load time at the expense of interactive latency due to the AJAX calls as the user plays with the data). And/Or you could give the user the choice.
- nicolaskruchten 13y ago
  Emphatically agreed! We routinely use this tool to summarize datasets which are, at their most disaggregated, hundreds of millions of rows. We have a server-side process which rolls those up into hundreds of thousands of rows, which are then fed to the browser via a query interface which tends to send only a few thousand at a time, which is easily handled by this tool. Each step in this pipeline 'summarizes' to an extent, and the last mile is this interactive client-side tool, which would be less responsive if it were server-side.
sailfast 13y ago
I'll definitely be spinning this up in the near future to pull together some web service data. Might even be a good excuse to begin working more with coffeescript. Thanks!
tlarkworthy 13y ago
Maybe camel case is not appropriate as the title, from wikipedia:- "The term pivot table is a generic phrase used by multiple vendors. In the United States, Microsoft Corporation has trademarked the specific form PivotTable."[1] [1] http://en.wikipedia.org/wiki/Pivot_table http://en.wikipedia.org/wiki/Pivot_table
minghai 13y ago
Lots of people said that there were no javascript pivot table libraries. Actually, that's not true. As I know, flexmonster pivot table component is a mature implementation. And recently I have a pure javascript implementation. It supports CSV data just exactly like excel and also I am adding OLAP support as well. webpivottable.com

Yummy. Couple that with a column-orient database and I don't need OLAP any more.

Excellent work! There are no great libraries to handle this type of work anywhere, that I've found, without requiring an entire stack change. My next project was to build something very similar to this. I've done the same before in PHP many years ago for the company I used to work for so I was confident that I could do it. Having this to leap frog my development is going to be extremely useful. And by that, I mean any extras I'd develop I'd contribute back. Now to start playing.

skeletonjelly 13y ago

Would like to see a demo of this with a large data set.

nicolaskruchten 13y ago
I'll work on getting something like that posted. I want to demo some of the fancier aggregation functions as well :)

aheilbut 13y ago

Looks interesting... If doing everything in javascript is not sufficient, another nice open source project with a slick interface on top of a real OLAP server (Mondrian) is Saiku - http://analytical-labs.com/ http://analytical-labs.com/

jsmeaton 13y ago
I've looked into Saiku and it has a very pretty and usable interface but, as you say, it requires the OLAP server. Not very useful if you plan on doing ad-hoc reporting with django as a backend for instance.

shtylman 13y ago

This is not a JS library!! This is coffeescript. Stop calling coffeescript stuff .js it is really misleading for people that actually develop in JS.

nsp 13y ago
How? It compiles down, and produces(not-great but decent) JS, and be included normally, why the objection?
- hobs 13y ago
  Maybe he wants to contribute? That's all I could think up...
- ianstormtaylor 13y ago
  Have to agree with the OP. Even though I can generally read CoffeeScript I'd much rather have all the code I depend on be on JavaScript because I much prefer contributing to JavaScript code. I also feel like CoffeeScript encourages incredibly complex practices just because the code can be written in a small amount of space. For example, here's a block of code from the library in question: sumOverSumBound80: (sigfig=3, scaler=1, upper=true) -> ([num, denom]) -> -> sumNum: 0 sumDenom: 0 push: (row) -> @sumNum += parseFloat(row[num]) if not isNaN parseFloat(row[num]) @sumDenom += parseFloat(row[denom]) if not isNaN parseFloat(row[denom]) value: -> sign = if upper then 1 else -1 (0.821187207574908/@sumDenom + @sumNum/@sumDenom + 1.2815515655446004*sign* Math.sqrt(0.410593603787454/ (@sumDenom*@sumDenom) + (@sumNum*(1 - @sumNum/ @sumDenom))/ (@sumDenom*@sumDenom)))/ (1 + 1.642374415149816/@sumDenom) format: numberFormat(sigfig, scaler) Have fun reading that... I think claims that CoffeeScript is a write-only language have very legitimate ground.
  - mokkol 13y ago
    This is misleading. Of course like Javascript you can write terrible CoffeeScript code. There are plenty of terrible written Javascript code examples out there.

jasoncrawford 13y ago

Nice! PivotTables are underrated.

elchief 13y ago

Looks nice and light weight. Also check out Saiku: http://demo.analytical-labs.com/ http://demo.analytical-labs.com/ Talks to OLAP server over XMLA (like Mondrian)

feniv 13y ago

I'm working on a analytics-related project and this looks like something I can use! The input format is a little restrictive, but understandably so (since this is meant to be used for tabular data) The data I'm working with is a little more nested and looks like : [ { color: {r: 0, g: 0, b: 255}, shape: {name: "circle", description: "A round shape"}}, {color: {r: 255, g: 0, b: 0}, shape: {name: "triangle", description: "Has 3 sides"}} ] than the example that was provided.

fibo 13y ago

I just used this other one for a project at local.italiaonline.it and it is really good (all sums are ok :) http://rjackson.github.io/pivot.js/ http://rjackson.github.io/pivot.js/

kachhalimbu 13y ago

This is pretty cool and fast. For Java people check out ZK Pivottable http://www.zkoss.org/product/zkpivottable http://www.zkoss.org/product/zkpivottable

petilon 13y ago

This is very cool. But the inherent limitation here is that it only works well for small datasets. If you have a million rows you don't want to download it all into your web browser and summarize it in the browser. Instead you want to summarize it on the server and only download the summary to the web browser.

pc86 13y ago
Agreed; to be fair though, is it really appropriate to use a client-side pivot table library if your use case necessitates a server-side approach?
dspillett 13y ago
For a lot of data-sets the amount being passed around is going to be small enough though. I expect CPU power to be more of a limit than bandwidth: depending on what grouping options the user picks a large dataset could take a lot of time to draw making the interface feel very clunky, particularly if like us you have a chunk of your user-base stuck on classic IE instead of more modern browsers. We are working on something similar to enhance our app's existing custom reporting features. While we're erring in the side of "client side will be fine for our current requirements" if you don't mind the extra state storage server-side (or extra database hits to re-collect the data from the back-end each time) you could keep the data there and pull the results back via AJAX calls - with careful design you could even be share the slicing/dicing code between client & server and make the choice dynamically (if the data is small send it all to the client then you are as responsive as possible, if it is above a certain payload size keep it server-side to improve load time at the expense of interactive latency due to the AJAX calls as the user plays with the data). And/Or you could give the user the choice.
nicolaskruchten 13y ago
Emphatically agreed! We routinely use this tool to summarize datasets which are, at their most disaggregated, hundreds of millions of rows. We have a server-side process which rolls those up into hundreds of thousands of rows, which are then fed to the browser via a query interface which tends to send only a few thousand at a time, which is easily handled by this tool. Each step in this pipeline 'summarizes' to an extent, and the last mile is this interactive client-side tool, which would be less responsive if it were server-side.

sailfast 13y ago

I'll definitely be spinning this up in the near future to pull together some web service data. Might even be a good excuse to begin working more with coffeescript. Thanks!

tlarkworthy 13y ago

Maybe camel case is not appropriate as the title, from wikipedia:- "The term pivot table is a generic phrase used by multiple vendors. In the United States, Microsoft Corporation has trademarked the specific form PivotTable."[1] [1] http://en.wikipedia.org/wiki/Pivot_table http://en.wikipedia.org/wiki/Pivot_table

minghai 13y ago

Lots of people said that there were no javascript pivot table libraries. Actually, that's not true. As I know, flexmonster pivot table component is a mature implementation. And recently I have a pure javascript implementation. It supports CSV data just exactly like excel and also I am adding OLAP support as well. webpivottable.com