Sorting in rPivotTable

Question

I have this rpivotTable and instead of manually typing in the values I want to sort by, I want to sort by the values within the pivottable. So basically when I change the variables on the left column, I want to sort them by the values within the table.

Below is a reproducible example:

library(devtools)
devtools::install_github("smartinsightsfromdata/rpivotTable")
library(rpivotTable)   

data(HairEyeColor)
rpivotTable(data = HairEyeColor, rows = "Hair",cols="Eye", vals = "Freq", aggregatorName = "Sum", rendererName = "Table", sorters = "
function(attr) { 
var sortAs = $.pivotUtilities.sortAs;
if (attr == \"Hair\") { return sortAs([\"Red\", \"Brown\", \"Blond\", \"Black\"]); }
}", width="100%", height="400px")

If you use `require`, you don't need `library` after. If you install from GitHub with `install_github`, don't use `install.packages` right after. It makes no sense. — , Dec 21 '15 at 23:05
ok thanks. Besides that, when you run this, how do you sort by the values rather than the category? @Pascal — R Guru, Dec 21 '15 at 23:11
Apologies for spotting the question only now. I'm the package author. Sadly I do not understand the question! If it is a problem you are trying to solve, would you mind to kindly express more clearly what sort of output would you expect? Thanks — Enzo, Mar 11 '16 at 21:15
It is possible that the version of rpivotTable now on Cran addresses the sorting issue (but I'm still not sure if it would address all of your issues). — Enzo, Apr 19 '17 at 19:43

score 0 · Answer 1 · answered Nov 07 '20 at 00:42

I came across your question looking for the solution, and I believe I have found a way to do what you want. I know this question is over 2 years old but hopefully it will be found by someone who needs a solution and deemed helpful.

To sort by the "vals" AND also by another metric such as "cols" or "rows", requires a simple inclusion of another "if" statement within the sorters function.

Using the reproducible example provided by the developer as a starting point:

library(rpivotTable)   
data(HairEyeColor)
rpivotTable(data = HairEyeColor, rows = "Hair",cols="Eye", vals = "Freq", aggregatorName = "Sum", rendererName = "Table", sorters = "
function(attr) { 
var sortAs = $.pivotUtilities.sortAs;
if (attr == \"Hair\") { return sortAs([\"Red\", \"Brown\", \"Blond\", \"Black\"]); }
}", width="100%", height="400px")

Your next step is to add another if (attr == '\"\") statement after the closing bracket after the hair color. Let's say for example you also wanted to sort by Eye Color, with the color Brown listed first instead of Blue. You would write the following code to accomplish this:

library(rpivotTable)   
data(HairEyeColor)
rpivotTable(data = HairEyeColor, rows = "Hair",cols="Eye", vals = "Freq", 
aggregatorName = "Sum", rendererName = "Table", sorters = "
function(attr) { 
var sortAs = $.pivotUtilities.sortAs;
if (attr == \"Hair\") { return sortAs([\"Red\", \"Brown\", \"Blond\", \"Black\"]);}
if (attr == \"Eye\") { return sortAs([\"Brown\", \"Blue\", \"Green\", \"Hazel\"]);}
}", width="100%", height="400px")

Remember that this package is developed around PivotTable.js, so you can include the functionality of the $.pivotUtilities.sortAs function within your code to do other sorting, such as ordering ascending and descending. Refer to the PivotTable.js documentation

score 0 · Answer 2 · answered Dec 30 '20 at 17:57

Not sure if this is what the OP was looking for, but I had a similar problem and it may might be usefull to share the solution for anyone trying to sort an rpivottable.

I wanted to sort by the aggregator values, not by the variables on the columns/rows. There are two buttons with small arrows next to the aggregator dropdown that you can click to do just that. However, I wanted the table to be sorted on a particular way by default, so that the user does not have to click the button.

I tried the solution mentioned above, but that was not what I wanted. The sorter option does not seem to help for my use case.

If your table is on some kind of html document (rmarkdown, shiny, flexdashboard...), you can have some javascript that clicks the button when the page is loaded. Place this somewhere in the document (outside of a code chunk)

<script>
window.addEventListener("load", function(){
  //Code inside this function will be executed after the page has been fully loaded
  document.getElementsByClassName("pvtColOrder")[0].click();
  document.getElementsByClassName("pvtColOrder")[0].click();
});
</script>

This will sort the visualization by the columns in decreasing order (clicks the button 2 times). If you change the [0] by [1] it will target the second table in the document, [2] the third and so on. "pvtRowOrder" will click the button to sort the rows instead.

I always place rpivottables inside a flexdashboard since you can define css to customize the fonts, add scrolling and make the headers of the table stick to the top as you scroll, so this solution is nice for me.

you wrote about css to "make the headers of the table stick to the top as you scroll". How does that css code look like? That would be useful, and I haven't found that on SO. — frkbr, Mar 10 '23 at 10:06

Sorting in rPivotTable

2 Answers2