<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/creating-interactive-charts-with-r-shiny-mysql-and-anychart-js-via-template/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://r-posts.com/creating-interactive-charts-with-r-shiny-mysql-and-anychart-js-via-template/"> R-posts.com</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p><img alt="How to creating interactive chart with R, Shiny, MySQL and AnyChart JS via template" src="https://i0.wp.com/www.anychart.com/blog/wp-content/uploads/2017/03/charting-with-AnyChart-JavaScript-library-in-R-Shiny-MySQL.png?resize=660%2C381" data-recalc-dims="1"></p>
<p>Data visualization and charting are actively evolving as a more and more important field of web development. In fact, people perceive information much better when it is represented graphically rather than numerically as raw data. As a result, various business intelligence apps, reports, and so on widely implement graphs and charts to visualize and clarify data and, consequently, to speed up and facilitate its analysis for further decision making.</p>
<p>While there are many ways you can follow to handle data visualization in R, today let’s see how to create interactive charts with the help of popular JavaScript (HTML5) charting library <a target="_blank" rel="nofollow" href="http://www.anychart.com/">AnyChart</a>. It has <a target="_blank" rel="nofollow" href="http://www.anychart.com/blog/2016/12/19/js-charting-without-borders-anychart-integration-templates-popular-stacks/">recently</a> got an official <a target="_blank" rel="nofollow" href="http://www.anychart.com/integrations/r-shiny-mysql-charts/">R, Shiny and MySQL template</a> that makes the whole process pretty straightforward and easy. (<strong>Disclaimer:</strong> I am the CTO at the AnyChart team. The template I am talking about here is released under the Apache 2.0 license; the library itself can be used for free in any personal, educational and other non-profit projects and is <a target="_blank" rel="nofollow" href="https://github.com/AnyChart/AnyChart">open on GitHub</a>, but for commercial purposes it requires a <a target="_blank" rel="nofollow" href="http://www.anychart.com/buy/">commercial license</a> though is fully functional when <a target="_blank" rel="nofollow" href="https://cdn.anychart.com/">taken</a> on a free trial.)</p>
<p>In this step-by-step tutorial, we will take a closer look at the template and the basic pie chart example it includes, and then I will show you how to quickly modify it to get some different data visualization, e.g. a 3D column (vertical bar) chart.</p>
<h2>Briefly about AnyChart</h2>
<p>AnyChart is a flexible, cross-browser JS charting library for adding interactive charts to websites and web apps in quite a simple way. Basically, it does not require any installations and work with any platform and database. Some more of AnyChart’s features include (but are not limited to):</p>
<ul>
<li>dozens of <a target="_blank" rel="nofollow" href="http://docs.anychart.com/Quick_Start/Supported_Charts_Types">supported chart types</a>, and the number keeps growing;</li>
<li>comprehensive <a target="_blank" rel="nofollow" href="http://api.anychart.com/">API reference</a> and <a target="_blank" rel="nofollow" href="http://docs.anychart.com/">documentation</a>;</li>
<li>a plenty of ready-to-use <a target="_blank" rel="nofollow" href="http://playground.anychart.com/">chart samples</a>, making first steps with this library easy;</li>
<li>the appearance of all the graphics and of a variety of additional elements is greatly customizable.</li>
</ul>
<p><a target="_blank" rel="nofollow" href="http://www.anychart.com/integrations">Templates</a> for popular technology stacks, like R, Shiny and MySQL in the present case, can further facilitate AnyChart’s integration.</p>
<h2>Getting started</h2>
<p>First of all, let’s make sure the R language is installed. If not, you can visit the <a target="_blank" rel="nofollow" href="https://www.r-project.org/">official R website</a> and follow the instructions.</p>
<p>If you have worked with R before, most likely you already have RStudio. Then you are welcome to create a project in it now, because the part devoted to R can be done there. If currently you do not have RStudio, you are welcome to install it from the <a target="_blank" rel="nofollow" href="https://www.rstudio.com/products/rstudio/download/">official RStudio website</a>. But, actually, using RStudio is not mandatory, and the pad will be enough in our case.</p>
<p>After that, we should check if MySQL is properly installed. To do that, you can open a terminal window and enter the next command:</p>
<pre>
$ mysql --version
mysql  Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using  EditLine wrapper
</pre>
<p>You should receive the above written response (or a similar one) to be sure all is well. Please follow <a target="_blank" rel="nofollow" href="https://www.cyberciti.biz/faq/howto-install-mysql-on-ubuntu-linux-16-04/">these instructions</a> to install MySQL, if you do not have it at the moment.</p>
<p>Now that all the required components have been installed, we are ready to write some code for our example.</p>
<h2>Basic template</h2>
<p>First, to download the R, Shiny and MySQL template for AnyChart, type the next command in the terminal:</p>
<pre>
$ git clone https://github.com/anychart-integrations/r-shiny-mysql-template.git
</pre>
<p>The folder you are getting here features the following structure:</p>
<pre>
r-shiny-mysql-template/
     www/
          css/
               style.css  # css style
     app.R                # main application code
     database_backup.sql  # MySQL database dump
     LICENSE
     README.md
     index.html           # html template
</pre>
<p>Let’s take a look at the project files and examine how this sample works. We’ll run the example first.</p>
<p>Open the terminal and go to the repository folder:</p>
<pre>
$ cd r-shiny-mysql-template
</pre>
<p>Set up the MySQL database. To specify your username and password, make use of <code>-u</code> and <code>-p</code> flags:</p>
<pre>
$ mysql &lt; database_backup.sql
</pre>
<p>Then run the R command line, using the command below:</p>
<pre>
$ R
</pre>
<p>And install the Shiny and RMySQL packages as well as initialize the Shiny library at the end:</p>
<pre>
> install.packages(&amp;quot;shiny&amp;quot;)
> install.packages(&amp;quot;RMySQL&amp;quot;)
> library(shiny)
</pre>
<p>If you face any problems during the installation of these dependencies, carefully read error messages, e.g. you might need <code>sudo apt-get install libmysqlclient-dev</code> for installing RMySQL.</p>
<p>Finally, run the application:</p>
<pre>
> runApp(&amp;quot;{PATH_TO_TEMPLATE}&amp;quot;) # e.g. runApp(&amp;quot;/workspace/r-shiny-mysql-template&amp;quot;)
</pre>
<p>And the new tab that should have just opened in your browser shows you the example included in the template:</p>
<p><img alt="Interactive Pie chart created with R and AnyChart JS charting library. Basic sample from R, Shiny and MySQL integration template" src="https://i2.wp.com/www.anychart.com/blog/wp-content/uploads/2017/03/interactive-pie-chart-in-r-with-anychart-js.png?resize=412%2C420" data-recalc-dims="1"></p>
<h2>Basic template: code</h2>
<p>Now, let’s go back to the folder with our template to see how it works.</p>
<p>Files <code>LICENSE</code> and <code>README.md</code> contain information about the license and the template (how to run it, technologies, structure, etc.) respectively. They are not functionally important to our project, and therefore we will not explore them here. Please check these files by yourself for a general understanding.</p>
<p>The <code>style.css</code> file is responsible for the styles of the page.</p>
<p>The <code>database_backup.sql</code> file contains a code for the MySQL table and user creation and for writing data to the table. You can use your own table or change the data in this one.</p>
<p>Let’s move on to the code. First, open the <code>app.R</code> file. This file ensures the connection to the MySQL database, reads data, and passes it to the <code>index.html</code> file, which contains the main code of using AnyChart. The following is a part of the <code>app.R</code> code, which contains the <code>htmlTemplate</code> function; here we specify the name of the file where the data will be transmitted to, the names of our page and chart, and the JSON encoded chart data from MySQL database.</p>
<pre>
htmlTemplate(&quot;index.html&quot;,
     title = &quot;Anychart R Shiny template&quot;,
     chartTitle = shQuote(&quot;Top 5 fruits&quot;),
     chartData = toJSON(loadData())
</pre>
<p>The main thing here is the <code>index.html</code> file, which is actually where the template for creating charts is. As you see, the first part of this file simply connects all necessary files to the code, including the AnyChart library, the CSS file with styles, and so on. I’ll skip this for now and proceed directly to the <code>script</code> tag and the <code>anychart.onDocumentReady (function () {...})</code> function.</p>
<pre>
anychart.onDocumentReady(function() {
     var chart = anychart.pie({{ chartData }});
     chart.title({{ chartTitle }});
     chart.container(&quot;container&quot;);
     chart.draw();
});
</pre>
<p>This pattern works as follows. We create a pie chart by using the function <code>pie()</code> and get the data that have already been read and prepared using the R code. Please note that the names of the variables containing data are the same in the <code>app.R</code> and <code>index.html</code> files. Then we display the chart title via (<code>chart.title({{ chartTitle }})</code>) and specify the ID of the element that will contain a chart, which is a div with <code>id = container</code> in this case. To show all that was coded, we use <code>сhart.draw()</code>.</p>
<h2>Modifying the template to create a custom chart</h2>
<p>Now that we’ve explored the basic example included in the template, we can move forward and create our own, custom interactive chart. To do that, we simply need to change the template a little bit and add some features if needed. Let’s see how it works.</p>
<p>First, we create all the necessary files by ourselves or make a new project using RStudio.</p>
<p>Second, we add a project folder named <code>anychart</code>. Its structure should look like illustrated below. Please note that some difference is possible (and acceptable) if you are using a new project in RStudio.</p>
<pre>
anychart/
     www/
          css/
               style.css  # css style
     ui.R                 # main application code
     server.R             # sub code
     database_backup.sql  # data set
     index.html           # html template
</pre>
<p>Now you know what files you need. If you’ve made a project with the studio, the <code>ui.R</code> and <code>server.R</code> files are created automatically. If you’ve made a project by yourself, just create empty files with the same names and extensions as specified above.</p>
<p>The main difference from the original example included in the template is that we should change the file <code>index.html</code> and divide <code>app.R</code> into parts. You can copy the rest of the files or create new ones for your own chart.</p>
<p>Please take a look at the file <code>server.R</code>. If you’ve made a project using the studio, it was created automatically and you don’t need to change anything in it. However, if you’ve made it by yourself, open it in the Notepad and add the code below, which is standard for the Shiny framework. You can read more about that <a target="_blank" rel="nofollow" href="https://shiny.rstudio.com/">here</a>.</p>
<p>The file structure of <code>ui.R</code> is similar to the one of <code>app.R</code>, so you can copy <code>app.R</code> from the template and change/add the following lines:</p>
<pre>
loadData = dbGetQuery(db, &quot;SELECT name, value FROM fruits&quot;)
data1 &lt;- character()

#data preparation
for(var in 1:nrow(loadData)){
     c = c(as.character(loadData[var, 1]), loadData[var, 2])
     data1 &lt;- c(data1, c)
}

data = matrix(data1,  nrow=nrow(loadData), ncol=2, byrow=TRUE)

ui = function(){
     htmlTemplate(&quot;index.html&quot;,
          title = &quot;Anychart R Shiny template&quot;,
          chartTitle = shQuote(&quot;Fruits&quot;),
          chartData = toJSON(data)
)}
</pre>
<p>Since we are going to change the chart type, from pie to 3D vertical bar (column), the data needs some preparation before being passed to <code>index.html</code>. The main difference is that we will use the entire data from the database, not simply the top 5 positions.</p>
<p>We will slightly modify and expand the basic template. Let’s see the resulting code of the <code>index.html</code> first (the <code>script</code> tag) and then explore it.</p>
<pre>
anychart.onDocumentReady(function() {

	var chart = anychart.column3d({{ chartData }});
	chart.title({{ chartTitle }});
	chart.animation(true);
   
	var xAxis = chart.xAxis();
 	xAxis.title(&quot;fruits&quot;);
 	var yAxis = chart.yAxis();
 	yAxis.title(&quot;pounds, t&quot;);

 	var yScale = chart.yScale();
 	yScale.minimum(0);
 	yScale.maximum(120);

	chart.container(&quot;container&quot;);
	chart.draw();
});
</pre>
<p>With the help of <code>var chart = anychart.column3d({{chartData}})</code>, we are creating a 3D column chart by using the function <code>column3d()</code>. Here you can choose any other chart type you need; consider getting help from <a target="_blank" rel="nofollow" href="http://www.anychart.com/chartopedia/">Chartopedia</a> if you are unsure which one works best in your situation.</p>
<p>Next, we are adding animation to the column chart via <code>chart.animation (true)</code> to make it appear on page load gradually.</p>
<p>In the following section, we are creating two variables, <code>xAxis</code> and <code>yAxis</code>. Including these is required if you want to provide the coordinate axes of the chart with captions. So, you should create variables that will match the captions for the X and Y axes, and then use the function, transmit the values that you want to see.</p>
<p>The next unit is basically optional. We are explicitly specifying the maximum and minimum values for the Y axis, or else AnyChart will independently calculate these values. You can do that the same way for the X axis.</p>
<p>And that’s it! Our 3D column chart is ready, and all seems to be fine for successfully running the code. The only thing left to do before that is to change the MySQL table to make it look as follows:</p>
<pre>
('apple',100),
('orange',58),
('banana',81),
('lemon',42),
('melon',21),
('kiwi',66),
('mango',22),
('pear',48),
('coconut',29),
('cherries',65),
('grapes',31),
('strawberries',76),
</pre>
<p>To see what you’ve got, follow the same steps as for running the R, Shiny and MySQL template example, but do not forget to change the path to the folder and the folder name to <code>anychart</code>. So, let’s open the terminal and command the following:</p>
<pre>
$ cd anychart
$ mysql &lt; database_backup.sql
$ R
     > install.packages(&quot;shiny&quot;)
     > install.packages(&quot;RMySQL&quot;)
     > library(shiny)
     > runApp(&quot;{PATH_TO_TEMPLATE}&quot;) # e.g. runApp(&quot;/workspace/anychart&quot;)
</pre>
<p><img alt="Interactive 3D Column chart made with R and AnyChart JS charting library, based on R, Shiny and MySQL integration template" src="https://i0.wp.com/www.anychart.com/blog/wp-content/uploads/2017/03/interactive-3d-bar-column-chart-in-r-with-anychart-js.png?resize=660%2C213" data-recalc-dims="1"></p>
<p>For consistency purposes, I am including the code of <code>ui.R</code> and <code>server.R</code> below. The full source code of this example can be found <a target="_blank" rel="nofollow" href="https://github.com/grasshoppergn/charting-with-R-and-AnyChart-JS">on GitHub</a>.</p>
<h3><code>ui.R</code>:</h3>
<pre>
library(shiny)
library(RMySQL)
library(jsonlite)

data1 &lt;- character()

db = dbConnect(MySQL(),
     dbname = &quot;anychart_db&quot;,
     host = &quot;localhost&quot;, 
     port = 3306, 
     user = &quot;anychart_user&quot;, 
     password = &quot;anychart_pass&quot;)

loadData = dbGetQuery(db, &quot;SELECT name, value FROM fruits&quot;)

#data preparation
for(var in 1:nrow(loadData)){
     c = c(as.character(loadData[var, 1]), loadData[var, 2])
     data1 &lt;- c(data1, c)
}

data = matrix(data1,  nrow=nrow(loadData), ncol=2, byrow=TRUE)

server = function(input, output){}

ui = function(){
     htmlTemplate(&quot;index.html&quot;,
     title = &quot;Anychart R Shiny template&quot;,
     chartTitle = shQuote(&quot;Fruits&quot;),
     chartData = toJSON(data)
)}

shinyApp(ui = ui, server = server)
</pre>
<h3><code>server.R</code>:</h3>
<pre>

library(shiny)

shinyServer(function(input, output) {

     output$distPlot &lt;- renderPlot({

          # generate bins based on input$bins from ui.R
          x    &lt;- faithful[, 2]
          bins &lt;- seq(min(x), max(x), length.out = input$bins + 1)

          # draw the chart with the specified number of bins
          hist(x, breaks = bins, col = 'darkgray', border = 'white')

 })
})
</pre>
<h3>Conclusion</h3>
<p>When your technology stack includes R, Shiny and MySQL, using AnyChart JS with the integration template we were talking about in this tutorial requires no big effort and allows you to add beautiful interactive JavaScript-based charts to your web apps quite quickly. It is also worth mentioning that you can customize the look and feel of charts created this way as deeply as needed by using some of the library’s numerous out-of-the-box features: add or remove axis labels, change the background color and how the axis is positioned, leverage interactivity, and so on.</p>
<p>The scope of this tutorial is likely to be actually even broader, because the process I described here not only applies to the <a target="_blank" rel="nofollow" href="http://www.anychart.com/products/anychart/overview/">AnyChart JS charting library</a>, but also is mostly the same for its sister libraries <a target="_blank" rel="nofollow" href="http://www.anychart.com/products/anymap/overview/">AnyMap</a> (geovisualization in maps), <a target="_blank" rel="nofollow" href="http://www.anychart.com/products/anystock/overview/">AnyStock</a> (date/time graphs), and <a target="_blank" rel="nofollow" href="http://www.anychart.com/products/anygantt/overview/">AnyGantt</a> (charts for project management). All of them are free for non-profit projects but – I must put it clearly here again just in case – require a special <a target="_blank" rel="nofollow" href="http://www.anychart.com/buy/">license</a> for commercial use.</p>
<p>I hope you find this article helpful in your activities when it comes to interactive data visualization in R. Now ask your questions, please, if any.</p>
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://r-posts.com/creating-interactive-charts-with-r-shiny-mysql-and-anychart-js-via-template/"> R-posts.com</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/ItY5_3woEpM"></a></div>