CODEWITHSUNDEEP
javargithubrpivottable
R Guru
R Guru

Reputation: 171

Sorting in rPivotTable

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")

Upvotes: 1

Views: 1163

Answers (2)

victor torres
victor torres

Reputation: 41

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.

Upvotes: 0

myClone
myClone

Reputation: 1739

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

Upvotes: 0

Related Questions