Feature request - Pivot table feature

[boumboum]

It would be nice to implement a pivot table feature based on the already available grid data where the user could define groups by selecting the rows and columns with the mouse

[olifolkerd]

Hey @boumboum

Thanks for the suggestion. the idea is interesting.

Could you provide more details on what you feel the user experience would be like?

Cheers

Oli :)

[boumboum]

Well, best would be a kind of Excel like pivot table where basically there is 2 multi select field (based on the declared data/columns info) and a simple select field. (here are some examples: https://dzone.com/articles/comparison-of-javascript-pivot-grids-for-developer) The first multi select would define the rows of the fields to group by (more or less what you have already done with the grouping feature) The second multi select would be the columns And the third simple select would be the function (sum, count, avg, etc.) as you've already done in the column footer.

But at the very first step (like a poor man's pivot table) as it would represent the least work, you could let define the same footer options for the group (header or footer) as you have for the table's column footer and let display all groups as closed as initial state (or provide an API function (if it is not done already) to programmatically open and close the groups and set the group and table footer options). The trick is that it could be several embedded group level (like a continent having countries, having cities). At the end either you are providing a general header to the grid (as is done for example for the w2ui grid: http://w2ui.com/web/demo/grid) or let the developer do this where one could put the above described selects and a button to execute the action.

Just to give you and idea in the backend there is a python module called pandas which has a similar feature: https://pandas.pydata.org/pandas-docs/version/0.23.4/generated/pandas.DataFrame.pivot.html

I hope I could help.

[rkhapre]

Yes, very true . I love this library feature, although have not implemented or used this. But from the documentation this is the best currently available. It will beat everyone if we have Pivot table feature as mentioned above by @boumboum.

Do we have any plans for this....

[olifolkerd]

Hey @rkhapre

Thanks for your kind words

This is the first time the idea has been suggested, so it is not on the roadmap yet, but i can certainly see how it could be useful.

At the moment the way Tabulator is constructed could make this rather tricky, but the overhaul in the 5.0 release should make this possible

Cheers

Oli :)

[rkhapre]

Hi @olifolkerd Thanks for your response, you planned any dates for Rel 5.0.

Thanks.

[olifolkerd]

Hey @rkhapre

No specifics yet, it will be in 2020, there is still a couple of release left in 4.0 at least.

Cheers

Oli :)

[rkhapre]

Hi @olifolkerd , i see this as a wonderful project, but only feature i am looking for is Pivot Table along with what you have in already existing feature. The combination of both takes it to next level. Since you told it will take a years time, thats a long time. Please let me know if you or your team can take up a paid project and complete it for me.

This will be a big bump for community users as well as your project will go to next level. If interested please pm me

Thanks

[boumboum]

But, Oli, at the first step what if you could implement some additional possibilities at the already existing grouping level. You have there already a count of elements. It would be nice to improve (or change) this behaviour to let define some aggregate functions (sum, count, in, max, avg) for the columns in the group header bar (already in place) This would IMHO represent the "least" work as the main parts for this are already in place (group header, one aggregate function (count)), so the job would be to increase this capability to let define and place the aggregates to the right place (under the right columns)

[8483]

@olifolkerd What are the plans for this? Is it still a 5.0 feature?

I am amazed there are no good pivot table libraries out there.

[olifolkerd]

@8483 realistically i think it will be the 6.0 release

The 5.0 will include the cellular vDOM that will be part of allowing the pivot table to built in the first place, but there will still be a fair bit of work to build out the rest of it.

The reason theyre arnt many good pivot tables out there is it is a really tricky task, the whole table has been optimised for its current layout and people want to flip it on its side which is no easy feat, it is pretty much a ground up rebuilt of half the table modules

Im afraid there are a lot more features much higher up my priority list that pivot tables at the moment, the feature request is kept open to keep it on the agenda, but it is a long way off. At this point Tabulator is not really intended as a spreadsheeting system, more of a dynamic table layout. once that is perfected i will consider diversifying into more spreadsheet based ideas

Cheers

Oli

[maritmk]

Hello, Is there anything new about the pivot feature? Tabulator is great, but many finance systems need a pivot as well. Kind of the nested data tree you already have with some aggregate functions (sum, avg etc)

[olifolkerd]

@maritmk please see the above post from last month.

[bornova]

If anyone is interested, I use the rowFormatter to display my data in a pivot table like format. It's a hack but works for me.

Here is an example: https://jsfiddle.net/dku674gy/

I basically hide any cell whose value matches the previous row's cell up to a specified number of columns from left. Couple caveats though:

• Table must be sorted by 1st column.
• If you filter the table, must call table.redraw() after filtered table is rendered. Otherwise the row formatting is lost.

A built in pivot table option someday would be amazing!

[mikeymckay]

While it would be impressive to get a pivot table bundled inside of tabulator, it feels like scope creep. I use tabulator to filter and display data, and then pivottable.js to build a pivot table below the tabulator. I don't love the fact that pivottable.js uses jquery (and jquery ui if you want the fancy drag and drop features which I think are worth it), but it was actually super easy to get working.

$("#pivotTable").pivot(tabulator.getData("active"))

[riziles]

Perspective's data grid has pretty sweet pivot table functionality: https://bl.ocks.org/texodus/45b868833c9f456bd39a51e606412c5d