pat310/quick-pivot

Quickly format data to create a pivot table

quick-pivot's Issues

TypeError: Object.values is not a function

Hi,
I'm using this in an application running in node (v6.10.2), was helped by it greatly, but after the change from using babel-polyfill to babel-runtime now I get:

TypeError: Object.values is not a function
at o (.../node_modules/quick-pivot/lib/quick-pivot.min.js:1:3952)
at i (.../node_modules/quick-pivot/lib/quick-pivot.min.js:1:4228)
at s (.../node_modules/quick-pivot/lib/quick-pivot.min.js:1:6506)
at new e (.../node_modules/quick-pivot/lib/quick-pivot.min.js:1:1158)

Upgrading to node 7 does fix the problem, is there an easier alternative?

Support multiple aggregations

turnerniles/react-virtualized-pivot#102

Should filters stack / are they chainable?

Should filters stack / are they chainable? Or do we only allow one filter at a time?

const dataArray = [ ['name', 'gender', 'house', 'age'], ['Jon', 'm', 'Stark', 14], ['Arya', 'f', 'Stark', 10], ['Cersei', 'f', 'Baratheon', 38], ['Tywin', 'm', 'Lannister', 67], ['Tyrion', 'm', 'Lannister', 34], ['Joffrey', 'm', 'Baratheon', 18], ['Bran', 'm', 'Stark', 8], ['Jaime', 'm', 'Lannister', 32], ['Sansa', 'f', 'Stark', 12] ];

const testPivot = new pivot(dataArray, ['age'], ['house','gender'], [], 'sum');
testPivot.filter("house", ["Stark"], 'exclude')
testPivot.filter("gender", ["m"], 'exclude')

returns

"[{"value":["sum ","Stark","Baratheon"],"depth":0,"type":"colHeader","row":0},{"value":["sum ","f","f"],"depth":1,"type":"colHeader","row":1},{"value":["10",null,""],"type":"data","depth":0,"row":2},{"value":["12",null,""],"type":"data","depth":0,"row":3},{"value":["38","",null],"type":"data","depth":0,"row":4}]"

Which is the result of the second filter (first filter is not applied).

Pivot should not add to collapsedRows if the row cannot be collapsed

If user runs pivot.collapse(2); and the second row does not contain collapsable children, {2: undefined} is added to pivot.collapsedRows. Instead nothing should be added.

Row and column total header

Should row and column totals both have blank headers, or text headers of 'Totals' similar to PivotTable.js? Currently row total header is blank and column total header has text of 'aggregated'. See screenshot below.

Support row totals

Similar to PR #62, except aggregate each row for a column with row totals

Grouping order changes upon filtering of sub-grouping

When you filter on certain sub groupings the main grouping order changes and get moved to the bottom.

const dataArray = [['name', 'gender', 'house', 'age'],['Jon', 'm', 'Stark', 14],['Arya', 'f', 'Stark', 10],['Cersei', 'f', 'Baratheon', 38],['Tywin', 'm', 'Lannister', 67],['Tyrion', 'm', 'Lannister', 34],['Joffrey', 'm', 'Baratheon', 18],['Bran', 'm', 'Stark', 8],['Jaime', 'm', 'Lannister', 32],['Sansa', 'f', 'Stark', 12]
];

const testPivot = new pivot(dataArray, ['gender', 'house'], [], 'age', 'sum');

Result of JSON.stringify(testPivot.data.table):
"[{"value":["sum age","sum age"],"depth":0,"type":"colHeader","row":0},{"value":["m",173],"depth":0,"type":"rowHeader","row":1},{"value":["Stark",22],"type":"data","depth":1,"row":2},{"value":["Lannister",133],"type":"data","depth":1,"row":3},{"value":["Baratheon",18],"type":"data","depth":1,"row":4},{"value":["f",60],"depth":0,"type":"rowHeader","row":5},{"value":["Stark",22],"type":"data","depth":1,"row":6},{"value":["Baratheon",38],"type":"data","depth":1,"row":7}]"

testPivot.filter('house', ['Stark'])

Result of JSON.stringify(testPivot.data.table):
"[{"value":["sum age","sum age"],"depth":0,"type":"colHeader","row":0},{"value":["f",38],"depth":0,"type":"rowHeader","row":1},{"value":["Baratheon",38],"type":"data","depth":1,"row":2},{"value":["m",151],"depth":0,"type":"rowHeader","row":3},{"value":["Lannister",133],"type":"data","depth":1,"row":4},{"value":["Baratheon",18],"type":"data","depth":1,"row":5}]"

Notice how the 'm' grouping is shifted to the second position. However, filtering on Baratheon does not cause the shift in order. It's unclear to me what exact scenarios cause the shift.

const dataArray = [['name', 'gender', 'house', 'age'],['Jon', 'm', 'Stark', 14],['Arya', 'f', 'Stark', 10],['Cersei', 'f', 'Baratheon', 38],['Tywin', 'm', 'Lannister', 67],['Tyrion', 'm', 'Lannister', 34],['Joffrey', 'm', 'Baratheon', 18],['Bran', 'm', 'Stark', 8],['Jaime', 'm', 'Lannister', 32],['Sansa', 'f', 'Stark', 12]
];

const testPivot = new pivot(dataArray, ['gender', 'house'], [], 'age', 'sum');

Result of JSON.stringify(testPivot.data.table):
"[{"value":["sum age","sum age"],"depth":0,"type":"colHeader","row":0},{"value":["m",173],"depth":0,"type":"rowHeader","row":1},{"value":["Stark",22],"type":"data","depth":1,"row":2},{"value":["Lannister",133],"type":"data","depth":1,"row":3},{"value":["Baratheon",18],"type":"data","depth":1,"row":4},{"value":["f",60],"depth":0,"type":"rowHeader","row":5},{"value":["Stark",22],"type":"data","depth":1,"row":6},{"value":["Baratheon",38],"type":"data","depth":1,"row":7}]"

testPivot.filter('house', ['Baratheon'])

Result of JSON.stringify(testPivot.data.table):
"[{"value":["sum age","sum age"],"depth":0,"type":"colHeader","row":0},{"value":["m",155],"depth":0,"type":"rowHeader","row":1},{"value":["Stark",22],"type":"data","depth":1,"row":2},{"value":["Lannister",133],"type":"data","depth":1,"row":3},{"value":["f",22],"depth":0,"type":"rowHeader","row":4},{"value":["Stark",22],"type":"data","depth":1,"row":5}]"

Add pivot toggle method for expanding / collapsing groups

In addition to pivot.expand() and pivot.collapse() methods, could we add a pivot.toggle() that either expands or collapses based on that groups current state (if it is collapsed, expand it and if it's expanded, collapse it).

different behaviour between chrome and firefox with same dataset

In actual scenario the app paint three accumulated bar charts, in vue 3, composition API so with same dataset using a pivot object like this one let pivot = new Pivot(initalDataSet, ['quien'], ['cuando'], 'cuanto', 'sum') wich is a three objects with this aspect:

export interface IItem {
labels: string[];
datasets: Dataset[];
}

export interface Dataset {
label: string;
data: number[];
barThickness: number;
borderRadius: number;
backgroundColor: string;
}

Example, I'll shorten the dataset array to make it simpler. The app recieve N numbers of datasets to paint, so after using the pivot object one of the arrays in Mozilla Firefozx looks like this ->

0: "agestc"
1: Array [ {…} ]
2: Array [ {…} ]
3: Array [ {…} ]
4: Array [ {…} ]
5: Array [ {…} ]
6: Array [ {…} ]
7: Array [ {…} ]
8: Array [ {…} ]
9: Array [ {…} ]
10: Array [ {…} ]
11: Array [ {…} ]
12: Array [ {…} ]
13: Array [ {…} ]
14: Array [ {…} ]
15: ""
16: ""
17: ""
18: ""
19: ""
20: ""
21: ""
22: ""
23: ""
24: ""
25: ""
26: ""
27: ""
28: ""
29: ""
30: ""
31: ""
32: ""
33: ""
34: ""
35: ""
36: ""
37: ""
38: ""
39: ""
40: ""
41: ""
42: ""
43: ""
44: ""
45: ""
46: ""
47: ""
48: ""
49: ""
50: ""
51: ""
52: ""
53: ""
54: ""
55: ""
56: ""
57: ""
58: ""
59: ""
60: ""
61: ""​​​​
length: 62

In Google chrome looks like this ->

0: "agestc"
1: ""
2: ""
3: ""
4: ""
5: ""
6: [{…}]
7: ""
8: ""
9: ""
10: ""
11: ""
12: ""
13: ""
14: ""
15: ""
16: ""
17: ""
18: [{…}]
19: ""
20: [{…}]
21: ""
22: ""
23: ""
24: [{…}]
25: [{…}]
26: [{…}]
27: ""
28: ""
29: ""
30: [{…}]
31: [{…}]
32: ""
33: ""
34: ""
35: ""
36: [{…}]
37: ""
38: ""
39: ""
40: [{…}]
41: ""
42: [{…}]
43: [{…}]
44: ""
45: ""
46: ""
47: ""
48: ""
49: ""
50: ""
51: ""
52: ""
53: ""
54: [{…}]
55: ""
56: ""
57: ""
58: [{…}]
59: ""
60: ""
61: ""
length: 62

The problem is that after setting the requested data from the database, with the same pivot dataset object, Mozilla Firefox mix the positions of the array and Google chrome leave them intact.
After this the chart shows well in google chrome and wrong in Mozilla, The question is how can I achieve the expected effect as Google chrome does in Mozilla Firefox?

charts in Mozilla :

charts in Chrome:

Reproduction

https://github.com/enlazoCtrl/charts here is the two vue classes that uses the quick-pivot library

Maximum call stack size exceeded when attempting to pivot on a field that is not present in the data

Maximum call stack size exceeded when attempting to pivot on a field that is not present in the data.

Example call (made in chrome console):
pivot([["dog","bird","fish"],["lab","falcon","guppy"]],["cat"],[],"VALUE_USD","sum")

Result:
Uncaught RangeError: Maximum call stack size exceeded

Should pivot.getUniqueValues return unique values of the current pivot or original?

When adding a filter on a certain dimension and then going back to edit that same filter, I need access to all the original unique values associated with that dimension. I am keeping track of the selected values associated with the dimension in order to filter the data using those values and could add them back into the list of values to select from; however, I would not be able to maintain the original order of values to select from unless I alphabetized strings and sorted numbers from the start. Which perhaps we should do... Other pivots seem to.

Below is an example of the current implementation, where pivot.getUniqueValues returns the unique values based on the current pivot?

const dataArray = [ ['name', 'gender', 'house', 'age'], ['Jon', 'm', 'Stark', 14], ['Arya', 'f', 'Stark', 10], ['Cersei', 'f', 'Baratheon', 38], ['Tywin', 'm', 'Lannister', 67], ['Tyrion', 'm', 'Lannister', 34], ['Joffrey', 'm', 'Baratheon', 18], ['Bran', 'm', 'Stark', 8], ['Jaime', 'm', 'Lannister', 32], ['Sansa', 'f', 'Stark', 12] ];

const pivot = new pivot(dataArray, ['gender'], [], 'age', 'sum');

pivot.getUniqueValues('gender')
-> ["m", "f"]

pivot.filter('gender', ['m'], 'exclude')
pivot.getUniqueValues('gender')
-> ["f"]

Filtering on value dimensions with string filter values instead of ints

When filtering on value fields, strings passed in as filters are not converted into ints. Should we do this in the pivot logic or on the front end? Numbers on the front-end are rendered as strings so when passed back as in a filter as a they do not match the respective int stored in the pivot.

const dataArray = [
 ['name', 'gender', 'house', 'age'],
 ['Jon', 'm', 'Stark', 14],
 ['Arya', 'f', 'Stark', 10],
 ['Cersei', 'f', 'Baratheon', 38],
 ['Tywin', 'm', 'Lannister', 67],
 ['Tyrion', 'm', 'Lannister', 34],
 ['Joffrey', 'm', 'Baratheon', 18],
 ['Bran', 'm', 'Stark', 8],
 ['Jaime', 'm', 'Lannister', 32],
 ['Sansa', 'f', 'Stark', 12]
];

const pivot = new pivot(dataArray, ['age'], [], 'age', 'sum');

// returns original pivoted data because it is excluding a string that does match the int 8 in the pivot
pivot.filter("age", ["8"], 'exclude')

// works
pivot.filter("age", [8], 'exclude')

Providing incorrect data types for rowsToPivot and/or colsToPivot throws filter error

I wasn't thinking when providing strings instead of arrays... But the error message confused me.

Filtering out all values returns empty array. Should return remaining unfiltered fields with 0 values.

Filtering out all values returns empty array. Should return remaining unfiltered fields with 0 values.

const dataArray = [ ['name', 'gender', 'house', 'age'], ['Jon', 'm', 'Stark', 14], ['Arya', 'f', 'Stark', 10], ['Cersei', 'f', 'Baratheon', 38], ['Tywin', 'm', 'Lannister', 67], ['Tyrion', 'm', 'Lannister', 34], ['Joffrey', 'm', 'Baratheon', 18], ['Bran', 'm', 'Stark', 8], ['Jaime', 'm', 'Lannister', 32], ['Sansa', 'f', 'Stark', 12] ];

const testPivot = new pivot(dataArray, ['house', 'gender'], [], 'age', 'sum', '');

testPivot.filter('gender', ['f','m'], 'exclude')

testPivot.data.table
returns []

Instead it should return
"[{"value":["",""],"depth":0,"type":"colHeader","row":0},{"value":["Baratheon",0],"type":"data","depth":0,"row":1},{"value":["Lannister",0],"type":"data","depth":0,"row":2},{"value":["Stark",0],"type":"data","depth":0,"row":3}]"

Or if this is a tremendous amount of work perhaps just the empty header.

Totals functionality

QuickPivot should return row and column totals and have the option to turn them on/off. Should this be done in the quickpivot logic or can it be done in a custom aggregator?

Comparison functions used are causing unexpected results

This issue is well mentioned here : https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/sort#sorting_with_non-well-formed_comparator

It causes all tests to fail in Node 11, 12, 13, 14 etc. However, on Node 10 it works good.

Look at the problem below (same comparison is used in this project)

Code :

On Node 10 sorting is as expected.

I will do PR shortly with fix.

Filtering / Sorting

I was thinking about the filtering and sorting logic. While it's feasible to do it on the front-end, should it be done on the back-end? I think it would be better to do on the back end since the groups of data have to be filtered / sorted.

Support Multiple Aggregation Dimensions

It would be great to have the type of functionality seen in this Pivot component:

Webix Pivot Component

Here, both GDP and OIL are aggregation dimensions, with configurable aggregators. I tried forking the library and working on it on my own, but I was unable to get it working on first pass. Any suggestions or recommendations on where to focus? Thanks!

Aggregation type and aggregation category naming

If we pass undefined aggregation types and categories should we throw an error?

For example:
const examplePivot = new Pivot([['name', 'gender', 'house', 'age'],['Jon', 'm', 'Stark', 14],['Arya', 'f', 'Stark', 10],['Cersei', 'f', 'Baratheon', 38],['Tywin', 'm', 'Lannister', 67],['Tyrion', 'm', 'Lannister', 34],['Joffrey', 'm', 'Baratheon', 18],['Bran', 'm', 'Stark', 8],['Jaime', 'm', 'Lannister', 32],['Sansa', 'f', 'Stark', 12]],['name'],[], 'cat', 'dog')

examplePivot.data.table

So it ignores the aggregation category 'cat' and defaults to aggregationType 'count' which makes sense. But should we throw an error here? The aggregation category is either one of the available fields from the data headers or '' for count. And should it say 'count' instead of 'dog' for the aggregation type since it performed a count?

Filtering out all data throw error

When providing filters that filter out all data, an error is thrown.

const dataArray = [
 ['name', 'gender', 'house', 'age'],
 ['Jon', 'm', 'Stark', 14],
 ['Arya', 'f', 'Stark', 10],
 ['Cersei', 'f', 'Baratheon', 38],
 ['Tywin', 'm', 'Lannister', 67],
 ['Tyrion', 'm', 'Lannister', 34],
 ['Joffrey', 'm', 'Baratheon', 18],
 ['Bran', 'm', 'Stark', 8],
 ['Jaime', 'm', 'Lannister', 32],
 ['Sansa', 'f', 'Stark', 12]
];

const pivot = new pivot(dataArray, ['gender'], [], 'age', 'count');
pivot.filter('gender', ['m', 'f'])

Data formating: Change 'value' key to 'values'

Small formatting change. Should we change all of the key value pair 'value' keys to 'values'. Since they are arrays of multiple values?

'Include' filtering with values that are a part of the unique values of that field throws error

const dataArray = [
 ['name', 'gender', 'house', 'age'],
 ['Jon', 'm', 'Stark', 14],
 ['Arya', 'f', 'Stark', 10],
 ['Cersei', 'f', 'Baratheon', 38],
 ['Tywin', 'm', 'Lannister', 67],
 ['Tyrion', 'm', 'Lannister', 34],
 ['Joffrey', 'm', 'Baratheon', 18],
 ['Bran', 'm', 'Stark', 8],
 ['Jaime', 'm', 'Lannister', 32],
 ['Sansa', 'f', 'Stark', 12]
];

const pivot = new pivot(dataArray, ['gender'], [], 'age', 'count');

pivot.filter('gender', ['dogman'], 'include')

Create excel sheet from the pivot data

How I can create an excel sheet from 'pivot' variable

const pivot = new Pivot(array, ['Region'], ['ActiveCustomers','ActiveUsers'], 'viewaccess', 'sum').collapseAll();

my data looks like

[ { value: [ 'sum viewaccess', '2', 'Totals' ], depth: 0, type: 'colHeader', row: 0 }, { value: [ 'sum viewaccess', '1', '' ], depth: 1, type: 'colHeader', row: 1 }, { value: [ 'North West', 0, 0 ], type: 'data', depth: 0, row: 2 }, { value: [ 'Totals', 0, '' ], type: 'aggregated' } ]

Bizarre aggregation results when column/row fields are provided in certain orders

Quick-pivot is aggregating bizarrely when provided with rows/columns in certain orders. Occurs when providing various combinations of rows and columns. Not specific to the rows and no columns aggregation example below.

const workingPivot = new Pivot([['name', 'gender', 'house', 'age'],['Jon', 'm', 'Stark', 14],['Arya', 'f', 'Stark', 10],['Cersei', 'f', 'Baratheon', 38],['Tywin', 'm', 'Lannister', 67],['Tyrion', 'm', 'Lannister', 34],['Joffrey', 'm', 'Baratheon', 18],['Bran', 'm', 'Stark', 8],['Jaime', 'm', 'Lannister', 32],['Sansa', 'f', 'Stark', 12]],['gender','house'],[], '', 'count')

const bugPivot = new Pivot([['name', 'gender', 'house', 'age'],['Jon', 'm', 'Stark', 14],['Arya', 'f', 'Stark', 10],['Cersei', 'f', 'Baratheon', 38],['Tywin', 'm', 'Lannister', 67],['Tyrion', 'm', 'Lannister', 34],['Joffrey', 'm', 'Baratheon', 18],['Bran', 'm', 'Stark', 8],['Jaime', 'm', 'Lannister', 32],['Sansa', 'f', 'Stark', 12]],['house','gender'],[], '', 'count')

const bugPivot2 = new Pivot([['name', 'gender', 'house', 'age'],['Jon', 'm', 'Stark', 14],['Arya', 'f', 'Stark', 10],['Cersei', 'f', 'Baratheon', 38],['Tywin', 'm', 'Lannister', 67],['Tyrion', 'm', 'Lannister', 34],['Joffrey', 'm', 'Baratheon', 18],['Bran', 'm', 'Stark', 8],['Jaime', 'm', 'Lannister', 32],['Sansa', 'f', 'Stark', 12]],[],['house','gender'], '', 'count')

Preserving order or column and row entries?

Is there a way of setting the sorting algorithm in play? My initial data is sorted properly, but upon pivot seems to be randomized (though consistent).