How to Switch from Excel to R Shiny: First Steps

Reading time:

time

min

September 30, 2020

If you're still using Excel or Google Sheets for business, you might already know that <a href="https://appsilon.com/excel-is-obsolete-here-are-the-top-2-alternatives-from-r-and-python/" target="_blank" rel="noopener noreferrer">Excel is obsolete</a> for many business use-cases. But how do you switch from Excel to <a href="http://appsilon.com/shiny" target="_blank" rel="noopener noreferrer">R Shiny</a> which is a much better alternative? Getting started is easier than you might think, especially if you're already an Excel power user. This article will walk you through a sample migration of an analytics tool built with Excel and Google Sheets to a dashboard built with R Shiny. We'll show you how to prepare the data for migration, create a simple R Shiny app, view/filter tables in R Shiny, modify a table using SQL, and also how to add interactive filters using the library ShinyWidgets. You don't need prior experience with R to follow this tutorial. Updated: April 12, 2022. <ul><li><a href="#excel-is-problematic">Why Excel is problematic for businesses</a></li><li><a href="#excel-alternatives">Alternatives to Excel: R and Python</a></li><li><a href="#migration">Migration from Excel to R: getting started</a></li><li><a href="#first-shiny-dashboard">Creating your first Shiny dashboard</a></li><li><a href="#migration-shiny-widgets">Migration: SQL and ShinyWidgets</a></li><li><a href="#learn-more">Learn more</a></li></ul> This article shouldn't take you more than 15 minutes to complete if you're following along with the code - The final Shiny dashboard has only 45 lines of well-formatted code!. You can download <a href="https://appsilon.com/wp-content/uploads/2020/09/j_h2.csv" target="_blank" rel="noopener noreferrer">the dataset we use</a> to try it yourself. <hr /> <h2 id="excel-is-problematic">Why Excel is a bad choice for businesses</h2> Excel has been an excellent tool for decades due to its relative intuitiveness and the WYSIWYG (What You See Is What You Get) user interface. Still, running an important business process in an Excel workbook in 2022 is a big mistake, as there are a variety of more stable and sophisticated tools readily available. Here are the reasons why Excel is a bad choice for businesses: <ol><li>Excel is prone to human errors.</li><li>It's not possible to fully version control Excel.</li><li>It's not safe to keep critical information in Excel.</li></ol> For more details on each point, read along. Excel is prone to human errors. Everyone with access to an Excel document can edit all of its features, which can lead to unintentional changes or the removal of crucial functions. As a result, you can easily end up with a broken spreadsheet whenever a user accidentally (or intentionally) changes a parameter. This is a serious issue because it is currently not possible to fully version control Excel. This means that you cannot effectively keep track of changes in an Excel spreadsheet and revert to a previous version if something breaks. To put it simply - it is not safe to keep a company’s critical information in Excel workbooks, especially when it comes to business analytics. Excel can still be useful as an alternative tool to SQL-based solutions to create simple 'databases' as it is very intuitive, and anyone can contribute. However, in the long run, Excel is just too fragile to handle complex business analyses. <blockquote>Learn more: <a class="editor-rtfLink" href="https://appsilon.com/excel-is-obsolete-here-are-the-top-2-alternatives-from-r-and-python/" target="_blank" rel="noopener noreferrer">Excel is Obsolete: Here Are the Top 2 Alternatives from R and Python</a></blockquote> <h2 id="excel-alternatives">Alternatives to Excel: Python and R</h2> To effectively plan your next business move, you need access to features that Excel doesn't offer, such as integration with machine learning models. You also might need a scalable way of connecting to external data sources via APIs. Selecting a flexible and reliable tool is crucial, due to the variety of analytical approaches that a modern business needs to make decisions. Therefore, it is necessary to move as many analytical operations as possible away from Excel. There are plenty of better-equipped tools for data analysis and data visualization, and they should not be too difficult to master for someone already proficient in Excel. Python and R are two of the most popular beginner-friendly programming languages. Many non-IT people find R easier and more intuitive to use than Python. This is good news because R and R Shiny alone can cover the majority of business operations stored in Excel and they really can open the door to modern, ground-breaking data analysis. <blockquote>Learn More: <a class="editor-rtfLink" href="https://appsilon.com/forget-about-excel-use-r-shiny-packages-instead/" target="_blank" rel="noopener noreferrer">Better than Excel: Use these R Shiny Packages Instead</a></blockquote> <h2 id="migration">Migration from Excel to R: getting started</h2> Every migration starts with proper data preparation. This step might take some time if a company has a lot of Excel workbooks. The most important thing is to extract original data from analyzed data, create tables, and save them in a CSV format. If you're just starting - start small. Pick one or two CSVs that move the needle for a start. In almost any use case, you don't have to start with a complete dataset. Remember: <ul><li>When dealing with multiple sheets in a workbook, you need to combine them into one or divide them into different CSVs.</li><li>When combining all sheets from the workbook into one table, make sure all sheets have the same number of columns and the same column names.</li></ul> It would make sense to switch from CSV to SQL in the future, but using CSV is not a dealbreaker in the beginning. Moreover, you’ve likely performed some useful and effective analytical operations within Excel. Don’t get rid of them just yet. By all means, analyze/verify these operations one more time, and describe their logic in detail (to recreate it later in R). <h3>Sample case: Job hunt analysis</h3> A couple of years back, one of our consultants was searching for a full-time job, and they started tracking application processes to see a more accurate picture of their career prospects - primarily to find out which companies/industries find a single profile interesting. Information about all applications sent in 2019 and 2020 were stored in two Excel sheets (original data) and one in Google Sheets (analysis). Let’s take a quick preview of this data. Original data: <img class="size-full wp-image-12413" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021e3a597077171c050be_original-data-source.webp" alt="Image 1 - Original source data" width="1814" height="1098" /> Image 1 - Original source data Analysis: <img class="size-full wp-image-12411" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021e36cd8f7584c8364a9_original-analysis-dataset.gif" alt="Image 2 - Original analysis data" width="1528" height="580" /> Image 2 - Original analysis data As you can see in the screen recording above, the dashboard works fine. However, there are some issues we encountered while using and maintaining it: <ul><li>When sharing the analysis with others, it is impossible to filter data without granting editing rights to users.</li><li>We can’t select multiple options in filters, e.g. we are not able to check the results for Poland and Spain simultaneously.</li><li>We need to maintain four different tables and functions that show almost the same thing.</li></ul> Because of the reasons above, it makes sense to migrate this dashboard to R Shiny and see if these problems can be eliminated. <h3>Data preparation</h3> In this case, data preparation was fairly easy. Two sheets were merged into one, some values got replaced (YES -> 1, NO -> empty cell) using Find and Replace, and the data was saved to a CSV file: <img class="size-full wp-image-12417" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021e4afc0ff7c657b4856_saving-excel-workbook-as-csv.webp" alt="Image 3 - Saving Excel workbook as a CSV file" width="1482" height="592" /> Image 3 - Saving Excel workbook as a CSV file The next logical step is describing the features we need to migrate from Excel to R Shiny. The following table summarizes the steps pretty well: <img class="size-full wp-image-12405" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021e648c3845e4b4de96f_features-needed-to-migrate-from-excel-to-r-shiny.webp" alt="Image 4 - Features needed to migrate from Excel to R Shiny" width="1024" height="499" /> Image 4 - Features needed to migrate from Excel to R Shiny <h3>Loading a dataset</h3> To start the migration process, we <a class="editor-rtfLink" href="https://rstudio.com/products/rstudio/download/" target="_blank" rel="noopener noreferrer">downloaded RStudio</a> (a free development environment for R from our partner <a href="https://appsilon.com/appsilon-data-science-is-now-an-rstudio-full-service-certified-partner/" target="_blank" rel="noopener noreferrer">RStudio, PBC</a>), found the CSV file we wanted to use, and imported it into RStudio: <img class="size-full wp-image-12409" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021e73c5d0379a4fa2fa8_loading-a-dataset-in-rstudio.gif" alt="Image 5 - Loading a dataset in RStudio" width="1678" height="908" /> Image 5 - Loading a dataset in RStudio After a successful import, the file appeared in the Global Environment. R had no problem with recognizing the CSV table. <blockquote>New to R or want to speed up your workflow? Check out our favorite <a href="https://appsilon.com/rstudio-shortcuts-and-tips/" target="_blank" rel="noopener noreferrer">RStudio Shortcuts and Tricks</a>.</blockquote> <h3 id="first-shiny-dashboard">Creating your first R Shiny dashboard</h3> Let’s start simple with something that remotely resembles the original dashboard. The main goal is to make a simple app that displays the source data and filters it by Job Category. It’s important to understand the two main components of an R Shiny app - the UI (User Interface) and the server. UI is a graphic layout of an app - everything the user sees on a webpage. The <a class="editor-rtfLink" href="https://shiny.rstudio.com/articles/shiny-server.html" target="_blank" rel="noopener noreferrer">server</a> is the backend of an application. The app is stored on the computer that runs R in the form of a page that can be viewed in a web browser. <blockquote>If you are a beginner with R Shiny, get started with our <a class="editor-rtfLink" href="https://appsilon.com/video-tutorial-create-and-customize-a-simple-shiny-dashboard/" target="_blank" rel="noopener noreferrer">Video Tutorial to Create and Customize a Simple Shiny Dashboard</a></blockquote> Note: To share the R Shiny app with others, you either need to send them a copy of a script or host this page via an external web server. To start, let’s use the most basic Shiny app template: <script src="https://gist.github.com/darioappsilon/73f0bb6cedcbc73997679370a0759df4.js"></script> <h3>Defining input and output</h3> <img class="size-full wp-image-12401" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021e96738007ca3671aee_basic-r-shiny-app.webp" alt="Image 6 - Basic Shiny app" width="2808" height="1114" /> Image 6 - Basic Shiny app Input is everything the user can interact with on a website. To name a few: <ul><li>select boxes - <code>selectInput()</code></li><li>radio buttons - <code>radioButtons()</code></li><li>sliders - <code>sliderInput()</code></li><li>date ranges - <code>dateRangeInput()</code></li><li>passwords - <code>passwordInput()</code></li></ul> Each input must have an <code>inputId</code> (local name, e.g. 'value'), and a <code>label</code> (a description that will be displayed in an app, e.g. 'Select value'). In addition, depending on the type of input, you can provide additional parameters that will specify/limit the actions a user can perform. For more on defining <code>input</code> and <code>output</code>, and other aspects of Shiny, read <a href="https://shiny.rstudio.com/tutorial/?utm_source=appsilon" target="_blank" rel="noopener noreferrer">RStudio's tutorial</a>. In the first draft of the app, let’s create a reactive select box from which the user can choose any job category that appears in the dataset. Therefore, besides defining <code>inputId</code> and a label we need a list of choices for a dropdown list (<code>choices = TableName$ColumnName</code>): <script src="https://gist.github.com/darioappsilon/4181dfe61d9502be0c63b3cbbd23b5b7.js"></script> On the other hand, <code>output</code> is the second argument in <code>fluidPage()</code>. In this case, it is the result of actions taken by the user in inputs. It can be displayed in the form of a: <ul><li>Graph - <code>plotOutput()</code>, </li><li>Table - <code>tableOutput()</code>, </li><li>Text - <code>textOutput()</code>, </li><li>Image - <code>imageOutput()</code>, </li></ul> and so on. Just like input, the output needs to have an ID - <code>outputId</code>. We’ll display the results as a table, so let’s use the <code>tableOutput()</code> function and name our reactive output <code>jobhuntData</code>: <script src="https://gist.github.com/darioappsilon/ee64db38814c76f1c5ddee0c031e8f6e.js"></script> Like many basic Shiny apps, our draft Shiny app is quite ugly by default. Let’s fix this with some elements: <code>titlePanel()</code>, <code>sidebarLayout()</code>, <code>sidebarPanel()</code>, and <code>mainPanel()</code>. At this point, after adding all elements to a <code>fluidPage()</code> function, our code looks like this: <script src="https://gist.github.com/darioappsilon/b5e4ad0bfa7ea3a88a7f4032c09d8159.js"></script> <img class="size-full wp-image-12403" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021eaf75d40a9af727a4e_dropdown-menu-in-r-shiny-app.webp" alt="Image 7 - Dropdown menu in R Shiny app" width="2502" height="1080" /> Image 7 - Dropdown menu in R Shiny app We can see the filter, but there is no table yet. This is because R Shiny does not know what kind of table we want to generate. Let’s introduce server requirements to address this. <blockquote>Want to Make a Beautiful Shiny App Fast? <a href="https://appsilon.com/r-shiny-dashboard-templates/">Leverage Appsilon's R Shiny templates that are completely free</a>.</blockquote> <h3>How to use Shiny Server</h3> To build the first draft of the app, we need to create a source for the <code>tableOutput()</code> function by using Render Functions: <ul><li><code>renderImage()</code> to render an image, </li><li><code>renderPlot()</code> if you want to render a plot/graph, </li><li><code>renderText()</code> to display text</li></ul> and so on. Below you can see how we assigned the <code>outputId</code> (<code>jobhuntData</code>) to a function that renders the desired output - in our case, <code>renderTable()</code> to render a table. Inside this function, we specified the data that we want to see in the table. Please mind that <code>input$jobcategory</code> refers to the Input Function from the UI, and it is always equal to the current value of the input (a value selected by a user). <script src="https://gist.github.com/darioappsilon/a99cc125a0d6cbe7aedb623272ab6990.js"></script> The current version of the app does not look amazing, but we can see that the correct data is shown, and the server generates proper output according to the input provided by the user: <img class="size-full wp-image-12415" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021ec22f94709a7bfaf08_reactive-r-shiny-app.gif" alt="Image 8 - Reactive R Shiny app" width="1414" height="788" /> Image 8 - Reactive R Shiny app <h3 id="migration-shiny-widgets">Migration: SQL and ShinyWidgets</h3> Now that we know how to create a basic dashboard in R Shiny, we are going to migrate other features from our original dashboard. First and foremost, we had to not only create filters for all columns but also aggregate/group data by YEAR and COUNTRY. There are several ways to modify the dataset in R, but we decided to do it using an SQL SELECT statement. SQL is another topic on its own, but we recommend that you learn the basics of SQL if you work with data on a daily (or even weekly) basis. This is one of the SQL statements we used to create an aggregated view in Google Sheets: <img class="size-full wp-image-12419" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021ec21b80e6ed3cf9d50_sql-statement-in-googlesheets.webp" alt="Image 9 - SQL statement used in Google Sheets" width="3316" height="74" /> Image 9 - SQL statement used in Google Sheets Below is the logic that we applied in R using the <code>sqldf</code> library. It enables us to see how many phone screenings, interviews, and offers we had each year in every country: <script src="https://gist.github.com/darioappsilon/8070b1a45308939ec0bdd731ac7d35e0.js"></script> This is what the new table <code>aggregated_data</code> looks like: <img class="size-full wp-image-12399" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021ed21b80e6ed3cf9d9e_aggregated-data-table.webp" alt="Image 10 - Aggregated data table" width="892" height="1024" /> Image 10 - Aggregated data table Adding multiple filters that are conditional can be a very difficult task, but the <a class="editor-rtfLink" href="https://www.rdocumentation.org/packages/shinyWidgets/versions/0.4.9" target="_blank" rel="noopener noreferrer">ShinyWidgets</a> library offers a perfect solution: <a class="editor-rtfLink" href="https://www.rdocumentation.org/packages/shinyWidgets/versions/0.4.9/topics/selectizeGroup-module" target="_blank" rel="noopener noreferrer">selectizeGroup-module</a>. Having imported ShinyWidgets, we've replaced <code>selectInput()</code> with <code>selecticizeGroupUI()</code> and added one more function - <code>callModule()</code>. This way we have eliminated the possibility of choosing a combination that does not exist. Below you can see the entire solution: <script src="https://gist.github.com/darioappsilon/93e8ead9c6b7ac2a6654a2b4c8b40d9c.js"></script> <img class="size-full wp-image-12407" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b021ef6bb6e6a6629db0fb_final-shiny-app.gif" alt="Image 11 - Final Shiny app" width="1414" height="880" /> Image 11 - Final Shiny app <hr /> <h2>Switching from Excel to R Shiny: key takeaways</h2> Working with a new tool like <a href="http://appsilon.com/shiny" target="_blank" rel="noopener noreferrer">R Shiny</a> can be intimidating at first, but in some ways, it can be even easier to learn and understand than Excel or Google Sheets. It is more flexible in terms of adding new features or modifying existing ones. Because we replaced four tables with one, the dashboard not only looks better than our Excel and Google Sheets tool - it is also much easier to use. Moreover, we managed to create an app where the user is in complete control of the displayed data but does not have access to the backend. This means we do not need to worry about non-technical users making accidental changes to the source code or breaking the app. We can also apply version control and store the source code of the app on services like GitHub in a way that allows us to safely revert to previous versions. This way, anyone with whom I want to share my code can download it and make contributions in a controlled environment. <blockquote>Interested in learning R Shiny further? <a href="https://appsilon.com/how-to-start-a-career-as-an-r-shiny-developer/">Here's how to start a career as an R Shiny developer</a>.</blockquote> If you need a PoC fast or an improvement on an existing concept, reach out to us. Appsilon is a global leader in R/Shiny development. And the Appsilon team plays an active role within the R/Shiny community, developing and maintaining open source packages and Shiny templates. From design to infrastructure, our <a href="https://shiny.tools/" target="_blank" rel="noopener noreferrer">Shiny tools</a> can be used to build efficient, <a href="https://appsilon.com/shiny/" target="_blank" rel="noopener noreferrer">beautiful Shiny applications</a> that scale. We’ve got the skillset to solve the most challenging of problems and we’re an <a href="https://appsilon.com/appsilon-is-an-rstudio-full-service-certified-partner/" target="_blank" rel="noopener noreferrer">RStudio Full Service Certified Partner</a>. This article was originally written by <a href="https://www.linkedin.com/in/zuzanna-maria-danowska/" target="_blank" rel="noopener noreferrer">Zuzanna Danowska</a> with further edits from Appsilon team members <a href="https://appsilon.com/author/marcin/" target="_blank" rel="noopener noreferrer">Marcin Dubel</a> and <a href="https://medium.com/@radecicdario" target="_blank" rel="noopener noreferrer">Dario Radečić</a>.