Forget about Excel, Use these R Shiny Packages Instead

tl; dr

Transferring your Excel sheet to a Shiny app can be the easiest way to create an enterprise ready dashboard. In this post, I  present 6 Shiny alternatives for the table-like data that Excel users love.

Intro 

Excel has its limitations regarding advanced statistics and calculations, quality and version control, user experience and scalability. Switching to a more sophisticated data analysis tool or a dashboard is often an answer. Transferring your Excel sheet to a Shiny app can be the easiest way to create an enterprise ready dashboard. Take a look at Filip Stachura’s article “Excel Is Obsolete” which addresses, from the architecture point of view, when to stick with Excel and when it is time for a change 

In this blogpost we’ll focus on the functionalities that may be implemented in a Shiny app.

You’re probably aware of Shiny’s cool interactive plot and charts features that are well ahead of what you can do in Excel. What may still prevent you from switching from Excel to a more advanced Shiny dashboard is the fear of missing the beloved Excel functionalities to work with table-like data. Don’t worry! It is super easy to implement and extend them using Shiny. The most commonly used table widgets in Shiny are DT and rhandsontable. Let’s take a deep dive into their features but also look at some other packages strictly dedicated to help with popular spreadsheets tasks.  

1. Editable tables

A basic reason for using a spreadsheet is the simplicity of data manipulation. Displaying data is not always enough. Content may require spell checking, fixing, adding rows or columns.
The closest solution to what can be found in spreadsheets is rhandsontable. It contains all of the cool spreadsheet features like autocomplete, selecting a value from a list, adding row/column, sparklines in cells, freezing, comments, input validation, read only mode, and so on.

Source: http://jrowen.github.io/rhandsontable/

A similar solution is available in excelR. The package is worth testing as it contains many interesting solutions such as radio selection inside table and the multiple well-known Excel functions, like SUM presented below. The package also allows users to easily manipulate cells with actions like resizing, merging or switching row/column positions. Nested headers are also useful as they can organize your data. Implementing the solution in Shiny is easy and intuitive with well-known render/output dedicated functions.

There are two downsides with excelR at the moment — cloning formulas between columns, and calculation approximations, which do not work as one would expect.

Source: https://swechhya.github.io/excelR

The DT package has a lot of great features and is a great option when heavy data editing is not the main goal. And as you can see in the gif below, tables implemented with DT look really nice. It has less functionality than rhandsontable though, basically just allowing the user to replace the values inside cells when double-clicked, without validating the values typed in. Some columns may be restricted to be read-only.

Source: https://rstudio.github.io/DT/

A possible workaround for DT’s limited editing functionality is a more advanced DTedit package. It comes with a pleasing interface (modal dialog) for editing single table rows as well as buttons to add, delete or copy data. The package is currently only available on GitHub, but we will keep our fingers crossed for its expansion and increase in popularity.

Source: https://github.com/jbryer/DTedit

2. Conditional formatting

Conditional formatting is a super useful tool for getting a quick overview when dealing with tons of values. Both rhandsontable and DT allow users to format cells according to its values. If your highest priority for your application is beautiful data presentation, then the package formattable is worth checking out. The formatting interface is more user-friendly than in rhandsontable and it is based on R functions, not pure javascript code. Besides working on tables, it also contains functionality of formatting R vectors which might be useful when presenting results of pure R analysis.

Source: https://github.com/renkun-ken/formattable

3. Sorting and filtering

Sorting and filtering are also crucial when examining a huge dataset. In the rhandsontable package, sorting columns can be enabled by a single parameter, however filtering is not implemented inside the feature and may require adding some extra Shiny components. On the other hand, in DT, column sorting is available by default as well as global search. Enabling column filtering is as easy as adding a single parameter (filter = top/bottom, depending on where the filters should be placed). 

Source: https://rstudio.github.io/DT/

4. Drag & drop pivot tables

Excel users love pivot tables. Allowing the users to create their own stories based on data is an excellent feature – sometimes the valuable info is only generated when looking at the data from the right angle. For a plug-and-play pivot table we recommend using the rpivotTable package. As you can see in the gif, it is super easy to produce tables and manipulate the aggregation variables with drag & drop. You can also filter by specific values and/or choose which variable should be calculated based on the selected function – like presented below sum and sum as percentage of total. Quickly switching from table to different types of (interactive!) charts is a great bonus.

Source: https://github.com/smartinsightsfromdata/rpivotTable

If you would like to combine pivoting with other features, a combination of shinydnd and DT Custom Table Containers as well as data manipulation is needed. Nevertheless, the results can be amazing. Maybe we’ll get into that in a future post.  

5. Reacting on selection

The usual scenario in dashboards applications is reacting to user selection and continuing to work on a selected element. When it needs to be a key feature of the application, then the DT package is a great choice. It is easy to implement logic for reacting to user cell/row/column selection.   

The sky’s the limit really! Options range from custom edit data tools to going deep into nested tables. Or as presented in the gif below, you see the graph dynamically reacts to user selection in the table.

Source: https://rstudio.github.io/DT/shiny.html

6. Expandable rows

…is an extra nice feature that allows you to hide (in an elegant way) additional information and bring the crucial part to the top. This is another feature that does not exist in spreadsheets. Expandable rows is also useful in presenting database-like structures with one-to-many relations. It requires a little javascript magic in DT for now, but the various examples (including this one) are easy to follow.

Source: http://www.reigo.eu/2018/04/extending-dt-child-row-example/

Conclusion

You may have felt that if you switched from Excel to Shiny, you would be limited in the table data feature set. I hope you can see by now that Shiny offers a comparable feature set to Excel as well as exciting new possibilities!

You can reach me on Twitter @DubelMarcin.

Article Forget about Excel, Use these R Shiny Packages Instead comes from Appsilon Data Science | End­ to­ End Data Science Solutions.