Interactive pivot tables with R

16 comments
I love interactive pivot tables. That is the number one reason why I keep using spreadsheet software. The ability to look at data quickly in lots of different ways, without a single line of code helps me to get an understanding of the data really fast.

Perhaps I can do the same now in R as well. At yesterday's LondonR meeting Enzo Martoglio presented briefly his rpivotTable package. Enzo builds on Nicolas Kruchten's PivotTable.js JavaScript library that provides drag'n'drop functionality and wraps it with htmlwidget into R. The result is an interactive pivot table rendered in either your default browser or the viewer pane of RStudio with one line of code:


## Install packages
library(devtools)
install_github("ramnathv/htmlwidgets") 
install_github("smartinsightsfromdata/rpivotTable")
## Load rpivotTable
library(rpivotTable)
data(mtcars)
## One line to create pivot table
rpivotTable(mtcars, rows="gear", col="cyl", aggregatorName="Average", 
vals="mpg", rendererName="Treemap")

The following animated Gif from Nicolas' project page gives an idea of the interactive functionality of PivotTable.js.

Example of PivotTable.js Source: Nicolas Kruchten

Session Info

R version 3.1.3 (2015-03-09)
Platform: x86_64-apple-darwin13.4.0 (64-bit)
Running under: OS X 10.10.2 (Yosemite)

locale:
[1] en_GB.UTF-8/en_GB.UTF-8/en_GB.UTF-8/C/en_GB.UTF-8/en_GB.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils    
[5] datasets  methods   base     

other attached packages:
[1] rpivotTable_0.1.3.4

loaded via a namespace (and not attached):
[1] digest_0.6.8      htmltools_0.2.6  
[3] htmlwidgets_0.3.2 RJSONIO_1.3-0    
[5] tools_3.1.3       yaml_2.1.13

16 comments :

  1. Nicolas Kruchten31 March 2015 at 14:17

    Glad you like my little pivot table library! It's awesome to see it making its way into the R ecosystem, which is something I'd tried to do and failed a few years ago. Small typo in your second paragraph though: my last name is "Kruchten" rather than "Krutchen" ;)

    ReplyDelete
  2. Can this be encorporated into a Shiny app?

    ReplyDelete
  3. Oops. Fixed. Thanks for PivotTable.js!!

    ReplyDelete
  4. Yes, I believe so. See the help page to rpivotTableOutput.

    ReplyDelete
  5. Great! I look forward to trying this out.

    ReplyDelete
  6. Could you provide an example of how to use the "rpivotTableOutput" function in a shiny app. I'm hoping for a simple reproducible example.

    ReplyDelete
  7. Well, I suggest you get in touch with Enzo.

    ReplyDelete
  8. Very nice. Thanks for pointing it out!

    ReplyDelete
  9. So simple to implement, but remarkably powerful!

    ReplyDelete
  10. Help required in installing the package


    > install.packages("rpivotTable")

    Installing package into ‘C:/Users/Rohit/Documents/R/win-library/3.1’

    (as ‘lib’ is unspecified)

    Warning in install.packages :

    package ‘rpivotTable’ is not available (as a binary package for R version 3.1.3)

    >

    >

    > install.packages("rpivotTable_0.1.3.4")

    Installing package into ‘C:/Users/Rohit/Documents/R/win-library/3.1’

    (as ‘lib’ is unspecified)

    Warning in install.packages :

    package ‘rpivotTable_0.1.3.4’ is not available (as a binary package for R version 3.1.3)

    >

    ReplyDelete
  11. rpivotTable is not on CRAN yet, but available from GitHub. My code example above has the details to install the package from GitHub.

    ReplyDelete
  12. Thanks issue resolved got it from GitHub - excellent and many thanks :)

    ReplyDelete
  13. davidcasciotti2 April 2015 at 12:52

    I am very interested in what you are attempting. This is a great R feature.
    If you solve this i would greatly appreciate seeing the solution

    ReplyDelete
  14. yes, it is very useful, thank you so much, Nicolas Kruchten

    ReplyDelete
  15. Hi Sir,


    When i run this command, I get the below error. Can you please help me with this error.


    rpivotTable(mtcars, rows="gear", col="cyl", aggregatorName="Average", vals="mpg", rendererName="Treemap")

    Error in as.character(tools:::httpdPort) :

    cannot coerce type 'closure' to vector of type 'character'

    ReplyDelete
  16. The code above still works for me, see my session info below. I suggest you raise this as an rpivotTable project site.

    Session Info:
    R version 3.2.1 (2015-06-18)
    Platform: x86_64-apple-darwin13.4.0 (64-bit)
    Running under: OS X 10.10.4 (Yosemite)
    locale:
    [1] en_GB.UTF-8/en_GB.UTF-8/en_GB.UTF-8/C/en_GB.UTF-8/en_GB.UTF-8
    attached base packages:
    [1] stats graphics grDevices utils datasets methods base
    other attached packages:
    [1] rpivotTable_0.1.5.2 devtools_1.8.0
    loaded via a namespace (and not attached):
    [1] Rcpp_0.11.6 digest_0.6.8 R6_2.1.0 jsonlite_0.9.16
    [5] git2r_0.10.1 magrittr_1.5 httr_1.0.0 stringi_0.5-5
    [9] curl_0.9.1 rstudioapi_0.3.1 xml2_0.1.1 tools_3.2.1
    [13] stringr_1.0.0 htmlwidgets_0.5.1 yaml_2.1.13 rversions_1.0.2
    [17] memoise_0.2.1 htmltools_0.2.6 knitr_1.10.5

    ReplyDelete