{"id":72120,"date":"2017-08-11T16:42:29","date_gmt":"2017-08-11T16:42:29","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=72120"},"modified":"2021-09-15T13:22:18","modified_gmt":"2021-09-15T13:22:18","slug":"sql-server-r-services-working-ggplot2-statistical-graphics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/sql-server-r-services-working-ggplot2-statistical-graphics\/","title":{"rendered":"SQL Server R Services: Working with ggplot2 Statistical Graphics"},"content":{"rendered":"

The series so far:<\/h4>\n
    \n
  1. SQL Server R Services: The Basics<\/a><\/li>\n
  2. SQL Server R Services: Digging into the R Language<\/a><\/li>\n
  3. SQL Server R Services: Working with ggplot2 Statistical Graphics<\/a><\/li>\n
  4. SQL Server R Services: Working with Data Frames<\/a><\/li>\n
  5. SQL Server R Services: Generating Sparklines and Other Types of Spark Graphs<\/a><\/li>\n
  6. SQL Server R Services: Working with Multiple Data Sets<\/a><\/li>\n<\/ol>\n\n

    SQL Server 2016 ships with SQL Server R Services, a new component that incorporates the analytic and visualization capabilities of the R language into the SQL Server environment. An important feature of the R language is the ability to import R packages into a script, making a wide range of functions available for working with SQL Server data.<\/p>\n

    One of these packages is ggplot2<\/strong>, which I introduced in the second article in this series. The ggplot2<\/strong> package includes an extensive set of functions for generating statistical visualizations. The package\u2019s functions let you create layered graphics using data from a data frame. This layered approach is an important concept in ggplot2<\/strong> because that is how you build a graphic\u2014one layer at a time. We used the package in conjunction with SQL Server R Services to generate a bar chart based on SQL Server data.<\/p>\n

    Given that the ggplot2<\/strong> package is so widely used to visualize data, I thought it worthwhile to spend more time looking at various ggplot2<\/strong> concepts. To that end, this article walks through the process of building a scatter plot graphic, using a series of R scripts to add each layer. I follow these with a few examples of other graphic types, based on the same core data.<\/p>\n

    To run an R script in R Services, you must call the sp_execute_external_script<\/strong> stored procedure, passing in the necessary parameter values, one of which is the R script itself. To use the ggplot2<\/strong> package in a script, you must first install the package into the SQL Server library and then import it into the script. The first two articles in this series describe how to carry out these steps. In this article, we focus primarily on the graphic definitions themselves.<\/p>\n

    Creating the initial scatter plot<\/h2>\n

    We\u2019ll start by creating the initial scatter plot structure, based on data from the AdventureWorks2014<\/strong> database. The following example defines an R script and T-SQL script, assigns them to variables, and uses those variables when calling the sp_execute_external_script<\/strong> stored procedure:<\/p>\n

    USE AdventureWorks2014;\r\n  GO\r\n  DECLARE @rscript NVARCHAR(MAX);\r\n  SET @rscript = N'\r\n  # import scales package\r\n  library(ggplot2)\r\n  # set up report file for chart\r\n  chartfile <- \"C:\\\\DataFiles\\\\Chart01.png\"\r\n  png(filename=chartfile, width=1000, height=600)\r\n  # construct data frame\r\n  df <- InputDataSet\r\n  # generate chart\r\n  chart <- ggplot(data=df, aes(x=Subcategory, y=AvgListPrice)) +\r\n  \u00a0 geom_point()\r\n  print(chart)\r\n  dev.off()';\r\n  DECLARE @sqlscript NVARCHAR(MAX);\r\n  SET @sqlscript = N'\r\n  SELECT s.Name Subcategory, c.Name Category,\u00a0\r\n  \u00a0 COUNT(p.ProductID) NumberProducts, AVG(p.ListPrice) AvgListPrice\r\n  FROM Production.Product p INNER JOIN Production.ProductSubcategory s\r\n  \u00a0 ON p.ProductSubcategoryID = s.ProductSubcategoryID\r\n  \u00a0 \u00a0 INNER JOIN Production.ProductCategory c\r\n  \u00a0 ON s.ProductCategoryID = c.ProductCategoryID\r\n  WHERE c.Name <> ''Clothing''\r\n  GROUP BY s.Name, c.Name;';\r\n  EXEC sp_execute_external_script\r\n  @language = N'R',\r\n  @script = @rscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS NONE;\u00a0\r\n  GO<\/pre>\n
    The T-SQL script retrieves data from the Product<\/strong> table and ProductSubcategory<\/strong> table, giving us the following data set:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    Subcategory<\/strong><\/p>\n<\/td>\n
    		\n
    Category<\/strong><\/p>\n<\/td>\n
    		\n
    NumberProducts<\/strong><\/p>\n<\/td>\n
    		\n
    AvgListPrice<\/strong><\/p>\n<\/td>\n<\/tr>\n<\/thead>\n
    \n
    Bike Racks<\/p>\n<\/td>\n
    		\n
    Accessories<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    120.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Bike Stands<\/p>\n<\/td>\n
    		\n
    Accessories<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    159.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Bottles and Cages<\/p>\n<\/td>\n
    		\n
    Accessories<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    7.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    Cleaners<\/p>\n<\/td>\n
    		\n
    Accessories<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    7.95<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fenders<\/p>\n<\/td>\n
    		\n
    Accessories<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    21.98<\/p>\n<\/td>\n<\/tr>\n
    \n
    Helmets<\/p>\n<\/td>\n
    		\n
    Accessories<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    34.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    Hydration Packs<\/p>\n<\/td>\n
    		\n
    Accessories<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    54.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    Lights<\/p>\n<\/td>\n
    		\n
    Accessories<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    31.3233<\/p>\n<\/td>\n<\/tr>\n
    \n
    Locks<\/p>\n<\/td>\n
    		\n
    Accessories<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    25.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Panniers<\/p>\n<\/td>\n
    		\n
    Accessories<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    125.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Pumps<\/p>\n<\/td>\n
    		\n
    Accessories<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n
    		\n
    22.49<\/p>\n<\/td>\n<\/tr>\n
    \n
    Tires and Tubes<\/p>\n<\/td>\n
    		\n
    Accessories<\/p>\n<\/td>\n
    		\n
    11<\/p>\n<\/td>\n
    		\n
    19.4827<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mountain Bikes<\/p>\n<\/td>\n
    		\n
    Bikes<\/p>\n<\/td>\n
    		\n
    32<\/p>\n<\/td>\n
    		\n
    1683.365<\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Bikes<\/p>\n<\/td>\n
    		\n
    Bikes<\/p>\n<\/td>\n
    		\n
    43<\/p>\n<\/td>\n
    		\n
    1597.45<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Bikes<\/p>\n<\/td>\n
    		\n
    Bikes<\/p>\n<\/td>\n
    		\n
    22<\/p>\n<\/td>\n
    		\n
    1425.2481<\/p>\n<\/td>\n<\/tr>\n
    \n
    Bottom Brackets<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    92.24<\/p>\n<\/td>\n<\/tr>\n
    \n
    Brakes<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n
    		\n
    106.50<\/p>\n<\/td>\n<\/tr>\n
    \n
    Chains<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    20.24<\/p>\n<\/td>\n<\/tr>\n
    \n
    Cranksets<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    278.99<\/p>\n<\/td>\n<\/tr>\n
    \n
    Derailleurs<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n
    		\n
    106.475<\/p>\n<\/td>\n<\/tr>\n
    \n
    Forks<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    184.40<\/p>\n<\/td>\n<\/tr>\n
    \n
    Handlebars<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    8<\/p>\n<\/td>\n
    		\n
    73.89<\/p>\n<\/td>\n<\/tr>\n
    \n
    Headsets<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    87.0733<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mountain Frames<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    28<\/p>\n<\/td>\n
    		\n
    678.2535<\/p>\n<\/td>\n<\/tr>\n
    \n
    Pedals<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    7<\/p>\n<\/td>\n
    		\n
    64.0185<\/p>\n<\/td>\n<\/tr>\n
    \n
    Road Frames<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    33<\/p>\n<\/td>\n
    		\n
    780.0436<\/p>\n<\/td>\n<\/tr>\n
    \n
    Saddles<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    9<\/p>\n<\/td>\n
    		\n
    39.6333<\/p>\n<\/td>\n<\/tr>\n
    \n
    Touring Frames<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    18<\/p>\n<\/td>\n
    		\n
    631.4155<\/p>\n<\/td>\n<\/tr>\n
    \n
    Wheels<\/p>\n<\/td>\n
    		\n
    Components<\/p>\n<\/td>\n
    		\n
    14<\/p>\n<\/td>\n
    		\n
    220.9292<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The R script imports the ggplot2<\/strong> package, creates the Chart01.png<\/strong> graphic file for the visualization, constructs the df<\/strong> data frame based on the above data set, and generates the initial scatter plot. We assign the R script to the @rscript<\/strong> variable and the T-SQL script to the @sqlscript<\/strong> variable, and then use those variables when calling the sp_execute_external_script<\/strong> stored procedure.<\/p>\n
    Most of what we\u2019re doing here is covered in the preceding articles, so refer to them as necessary. Our primary concern for this article is the part of the R script that defines the graphic (the section that starts with the generate chart<\/em> comment):<\/p>\n
    # generate chart\r\n  chart <- ggplot(data=df, aes(x=Subcategory, y=AvgListPrice)) +\r\n  &nbsp; geom_point()\r\n  print(chart)\r\n  dev.off()';<\/pre>\n
    For all the examples in this article, we\u2019ll use the same SQL Server data and df<\/strong> data frame (or a derivative of the data frame), changing only the chart definition as we progress through the article.<\/p>\n
    To create the first layer of a ggplot2<\/strong> visualization, we use the ggplot<\/strong> function, which establishes the initial graphic structure. When calling the function, we must provide the source data frame and specify the visualization\u2019s initial aesthetics,<\/em> the properties that define the graphic\u2019s visual aspects.<\/p>\n
    Often when defining the aesthetics, we use the aes<\/strong> function, which maps variables to the aesthetics. Variables, in this case, can include the columns in the source data frame. (The R language essentially treats a column as a variable.) In this case, we\u2019re assigning the Subcategory<\/strong> column in the df<\/strong> data frame to the x<\/strong> property (X-axis), and the AvgListPrice<\/strong> column to the y<\/strong> property (Y-axis). Any aesthetics we define in the initial layer apply to all subsequent layers, unless we specifically override them in another layer.<\/p>\n
    After calling the ggplot<\/strong> function, we can then define the other layers in the graphic, adding a plus sign for each layer we want to include. Normally, a good place to start is by specifying the graphic type, or geom<\/em>. To define a scatter plot geom, we use the geom_point<\/strong> function, in this case, without any arguments, but as you\u2019ll see later in the article, we can add arguments that further define the graphic.<\/p>\n
    The geom_point<\/strong> function represents the second layer in our graphic. Notice that we assign the graphic definition to the chart<\/strong> variable. We can then use the print<\/strong> function to call the variable. This will print the graphic to the Chart01.png<\/strong> file we defined earlier.<\/p>\n
    The last step we take in this part of the R script is to call the dev.off<\/strong> function in order to close the png<\/strong> device. If we now run the sp_execute_external_script<\/strong> stored procedure, the R engine would generate a graphic similar to the one in the following figure.<\/p>\n
    \"Chart01.png\"<\/p>\n
    As you can see, the chart plots the average list price for each product subcategory. Unfortunately, the information is not very readable, especially the subcategory names, but we can define additional layers to fix those.<\/p>\n
    Flipping the chart\u2019s axis points<\/h2>\n
    Each layer in the graphic definition defines a specific element of that graphic. Together, the layers create the entire picture. In the previous example, we defined two layers, the initial ggplot<\/strong> layer and the second geom_point<\/strong> layer. We\u2019ll now add two more layers that switch the X-axis and Y-axis and make the chart more readable:<\/p>\n
    chart <- ggplot(data=df, aes(x=Subcategory, y=AvgListPrice)) +\r\n  \u00a0 geom_point() +\r\n  \u00a0 coord_flip() +\u00a0\r\n  \u00a0 xlim(rev(levels(df$Subcategory)))\r\n  print(chart)\r\n  dev.off()';<\/pre>\n
    If you reviewed the last article in this series, you might recognize what we\u2019re doing here. First, we use the coord_flip<\/strong> function to switch the X-axis and Y-axis. Next, we use the xlim<\/strong> function to modify the order of the subcategory names so they\u2019re listed alphabetically, starting at the top.<\/p>\n
    The xlim<\/strong> function defines our data range, based on the values in the Subcategory<\/strong> column. When referencing that column, the function calls two additional functions. The levels<\/strong> function retrieves the list of values (levels) from the Subcategory<\/strong> column, and the rev<\/strong> function reverses the order of those levels. We need to reverse the order because flipping the chart causes the subcategories to be listed from bottom to top.<\/p>\n
    That\u2019s all there is to flipping the chart and listing the subcategory names in the correct order. The R script now produces the results shown in the following figure.<\/p>\n
    \"Chart02.png\"<\/p>\n
    Although the subcategory labels are now easier to read, there is still much more we can do with our graphic to make it more presentable, so let\u2019s forge ahead.<\/p>\n
    Configuring the plot points<\/h2>\n
    Our next step is to change the color and size of the individual plot points so they\u2019re easier to pick out in the chart. In this case, however, we\u2019re not going to add a layer, but will instead update the geom_point<\/strong> layer by specifying values for the color<\/strong> and size<\/strong> properties:<\/p>\n
    chart <- ggplot(data=df, aes(x=Subcategory, y=AvgListPrice)) +\r\n  \u00a0 geom_point(color=\"slateblue2\", size=5) +\r\n  \u00a0 coord_flip() +\u00a0\r\n  \u00a0 xlim(rev(levels(df$Subcategory)))\r\n  print(chart)\r\n  dev.off()';<\/pre>\n
    The geom functions support numerous properties (aesthetics) that let you better define your graphics. In this case, we\u2019re assigning the value slateblue2<\/strong> to the color<\/strong> property and the value 5<\/strong> (for points) to the size<\/strong> property. You can find lists of colors in various resources online, such as the one on the Columbia University website<\/a>.<\/p>\n
    With these small changes, our R script now produces the following graphic.<\/p>\n
    <\/p>\n
    The new settings make it easier to pick out the plot points than the original black pinpoints, although you might prefer different color<\/strong> and size<\/strong> values. Your choices will depend in part on the nature and amount of the data you\u2019re displaying. Feel free to play around with various settings until you come up with the combination that works best for your circumstances.<\/p>\n
    Mapping the plot points to categories<\/h2>\n
    When defining a graphic\u2019s aesthetics, we can use other data in the data frame to control how those aesthetics are applied. For example, suppose we want to color-code the plot points based on product categories. We can achieve this by assigning the Category<\/strong> column to the color<\/strong> property, rather than an actual color:<\/p>\n
    chart <- ggplot(data=df, aes(x=Subcategory, y=AvgListPrice)) +\r\n  \u00a0 geom_point(aes(color=Category), size=5) +\r\n  \u00a0 coord_flip() +\u00a0\r\n  \u00a0 xlim(rev(levels(df$Subcategory)))\r\n  print(chart)\r\n  dev.off()';<\/pre>\n
    Notice that we must use the aes<\/strong> function when setting the color<\/strong> property. The R engine can then use the values in the Category<\/strong> column as the basis for applying colors to the plot points. In this situation, the column includes only three values, giving us three different colors, as shown in the following figure.<\/p>\n
    \"Chart04.png\"<\/p>\n
    Notice that the R engine also adds the Category<\/strong> legend to the graphic to make it easier to identify the categories and their associated colors. The ggplot2<\/strong> package also provides additional methods for controlling the colors assigned to the categories, but we\u2019ll leave it at this for now.<\/p>\n
    Configuring the chart\u2019s labels<\/h2>\n
    Now that we\u2019ve gotten the chart itself in order, it\u2019s time to focus on the labels, which are currently based on default values. To display more useful labels, we\u2019ll add several layers to our graphic definition:<\/p>\n
    chart <- ggplot(data=df, aes(x=Subcategory, y=AvgListPrice)) +\r\n  \u00a0 geom_point(aes(color=Category), size=5) +\r\n  \u00a0 coord_flip() +\u00a0\r\n  \u00a0 xlim(rev(levels(df$Subcategory))) +\r\n  \u00a0 ggtitle(\"Average List Price per Subcategory\") +\r\n  \u00a0 labs(x=\"Product Subcategories\", y=\"List Prices (US dollars)\") +\r\n  \u00a0 guides(color=guide_legend(\"Categories\"))\r\n  print(chart)\r\n  dev.off()';<\/pre>\n
    We start by using the ggtitle<\/strong> function to define the graphic\u2019s title\u2014Average List Price per Subcategory<\/em>\u2014passing in the title as an argument. We then follow with the labs<\/strong> function to define the X-axis and Y-axis labels, Product Subcategories<\/em> and List Prices (US dollars),<\/em> respectively. We could have used the labs<\/strong> function to define the title as well, but I wanted to demonstrate that there are different ways to do things in R, and using ggtitle<\/strong> makes it easier to pick the title out of the script.<\/p>\n
    The next step is to change the title for the Category<\/strong> legend so it is plural. For that, we use the guides<\/strong> function, which lets you configure or remove a legend. In this case, we\u2019re setting the color<\/strong> property to Categories<\/strong>, using the guide_legend<\/strong> function to specify the new name. The guide_legend<\/strong> function lets us control a number of settings associated with the legend, such as color, size, shape, and transparency. Our graphic should now look like the one shown in the following figure.<\/p>\n
    \"Chart05.png\"<\/p>\n
    There are, of course, a wide range of changes we can make to how the labels and other elements are displayed, but this should give you a sense of types of steps we can take to control the various elements.<\/p>\n
    Applying a pre-built theme<\/h2>\n
    The basic implementation of the R language in SQL Server supports several predefined themes, which specify the graphic\u2019s overall property settings, such as borders, margins, grid lines, and background colors. A theme is essentially a function that defines the various settings, allowing you to apply those settings by calling the function. For example, we can apply the theme_bw<\/strong> theme to our graphic by calling it in a new layer:<\/p>\n
    chart <- ggplot(data=df, aes(x=Subcategory, y=AvgListPrice)) +\r\n  \u00a0 geom_point(aes(color=Category), size=5) +\r\n  \u00a0 coord_flip() +\u00a0\r\n  \u00a0 xlim(rev(levels(df$Subcategory))) +\r\n  \u00a0 ggtitle(\"Average List Price per Subcategory\") +\r\n  \u00a0 labs(x=\"Product Subcategories\", y=\"List Prices (US dollars)\") +\r\n  \u00a0 guides(color=guide_legend(\"Categories\")) +\r\n  \u00a0 theme_bw()\r\n  print(chart)\r\n  dev.off()';<\/pre>\n
    All we\u2019re doing is adding a layer that calls the theme_bw<\/strong> function, which changes the look of our scatter plot to the following chart:<\/p>\n
    <\/p>\n
    The most obvious difference is that there is no longer a gray background, but other elements have changed as well, such as the weight of the grid lines.<\/p>\n
    The basic R language includes only a few pre-built themes, but you can find plenty of other themes out there. You just need only to install the applicable R package. In some cases, however, you might want to update individual theme-related properties, rather than all settings. For that, we need to take a different approach.<\/p>\n
    Configuring theme elements<\/h2>\n
    The ggplot2<\/strong> package supports a wide range of theme-related graphic properties that you can update by using the theme<\/strong> function. For example, we can change the size and color of the various titles:<\/p>\n
    chart <- ggplot(data=df, aes(x=Subcategory, y=AvgListPrice)) +\r\n  \u00a0 geom_point(aes(color=Category), size=5) +\r\n  \u00a0 coord_flip() +\u00a0\r\n  \u00a0 xlim(rev(levels(df$Subcategory))) +\r\n  \u00a0 ggtitle(\"Average List Price per Subcategory\") +\r\n  \u00a0 labs(x=\"Product Subcategories\", y=\"List Prices (US dollars)\") +\r\n  \u00a0 guides(color=guide_legend(\"Categories\")) +\r\n  \u00a0 theme_bw() +\r\n  \u00a0 theme(title=element_text(size=16, color=\"blue3\"))\r\n  print(chart)\r\n  dev.off()';<\/pre>\n
    In this case, when calling the theme<\/strong> function, we\u2019re specifying a value for the title<\/strong> property, using the element_text<\/strong> function to set the size<\/strong> value to 16<\/strong> points and the color<\/strong> value to blue3<\/strong>, giving us the results shown in the following figure.<\/p>\n
    <\/p>\n
    By using the theme<\/strong> function, we can control dozens of settings, ranging from line sizes to legend margins to background colors. We can even control individual axis labels. For information about the type of settings you can control, see the RDocumentation topic theme<\/a>.<\/p>\n
    Faceting the chart based on categories<\/h2>\n
    Another fun thing we can do with ggplot2<\/strong> is to split a graphic into multiple small graphics by faceting the data based on values in one of the data frame\u2019s columns. For this, we call the facet_wrap<\/strong> function and specify the column on which the faceting is based:<\/p>\n
    chart <- ggplot(data=df, aes(x=Subcategory, y=AvgListPrice)) +\r\n  \u00a0 geom_point(aes(color=Category), size=5) +\r\n  \u00a0 coord_flip() +\u00a0\r\n  \u00a0 xlim(rev(levels(df$Subcategory))) +\r\n  \u00a0 ggtitle(\"Average List Price per Subcategory\") +\r\n  \u00a0 labs(x=\"Product Subcategories\", y=\"List Prices (US dollars)\") +\r\n  \u00a0 guides(color=guide_legend(\"Categories\")) +\r\n  \u00a0 theme_bw() +\r\n  \u00a0 theme(title=element_text(size=16, color=\"blue3\")) +\r\n  \u00a0 facet_wrap(~Category)\r\n  print(chart)\r\n  dev.off()';<\/pre>\n
    To implement faceting, we add the facet_wrap<\/strong> function as a new layer and include the Category<\/strong> column as an argument. Notice that we must precede the column name with a tilde (~<\/strong>) operator. This is because the function expects a formula as an argument, and the R language uses the tilde operator to define formulas. In this case, however, we\u2019re providing only the right side of the formula, which is the column name. (The topic of R formulas and the use of the tilde argument is beyond the scope of this article. For now, just know that you need to precede the column name with the operator so the R engine doesn\u2019t baulk.)<\/p>\n
    When we add the facet_wrap<\/strong> function, specifying the Category<\/strong> column, the R script returns the graphic shown in the following figure.<\/p>\n
    \"Chart08.png\"<\/p>\n
    We now have one chart for each product category. Although this is a relatively simple example, it shows how easy it is to create individual charts based on a specific set of values. For complex data, this can provide a valuable resource to help display data more clearly.<\/p>\n
    Creating a bar chart<\/h2>\n
    Our focus to this point has been on adding layers to our scatter plot graphic, but the same principles also apply to other geom types. For example, the following graphic definition defines a bar chart:<\/p>\n
    chart <- ggplot(data=df, aes(x=Subcategory, y=AvgListPrice)) +\r\n  \u00a0 geom_bar(stat=\"identity\", aes(fill=Category)) +\r\n  \u00a0 coord_flip() +\u00a0\r\n  \u00a0 xlim(rev(levels(df$Subcategory))) +\r\n  \u00a0 ggtitle(\"Average List Price per Subcategory\") +\r\n  \u00a0 labs(x=\"Product Subcategories\", y=\"List Prices (US dollars)\", fill=\"Categories\") +\r\n  \u00a0 theme_bw() +\r\n  \u00a0 theme(title=element_text(size=16, color=\"blue3\"))\r\n  print(chart)\r\n  dev.off()';<\/pre>\n
    Notice that many of the layers are similar the scatter plot. We flip the X-axis and Y-axis, reverse the list of subcategories, format the labels, and apply theme settings. However, there are also several differences.<\/p>\n
    To begin with, we specify the geom_bar<\/strong> function, rather than the geom_point<\/strong> function, and pass in the stat<\/strong> and fill<\/strong> arguments. The stat<\/strong> argument and its value, identity<\/strong>, ensure that the data values map correctly to the plot points. The fill<\/strong> argument specifies the bar fill colors. Because we want to color-code the bars based on product categories, we specify the Category<\/strong> column, wrapping the argument and value in the aes<\/strong> function.<\/p>\n
    Another difference from previous examples is that we do not use the guides<\/strong> function to set the legend title. Instead, we include the fill<\/strong> property when calling the labs<\/strong> function, but we still set the value to Categories<\/strong>.<\/p>\n
    You\u2019ll find many small differences like this from one geom to the next, but the general concept of adding layers remains the same. The graphic definition now generates the bar chart shown in the following figure.<\/p>\n
    <\/p>\n
    That\u2019s all there is to creating a bar chart. If you reviewed the second article in this series, most of the script components should look familiar to you. Even if you have not, the approach to creating a bar chart is the same as creating a scatter plot or any other geom.<\/p>\n
    Adding labels to the chart\u2019s bars<\/h2>\n
    We can also add labels to the graphic itself. As a quick example, let\u2019s add the number of products per subcategory by defining an additional layer:<\/p>\n
    chart <- ggplot(data=df, aes(x=Subcategory, y=AvgListPrice)) +\r\n  \u00a0 geom_bar(stat=\"identity\", aes(fill=Category)) +\r\n  \u00a0 coord_flip() +\u00a0\r\n  \u00a0 xlim(rev(levels(df$Subcategory))) +\r\n  \u00a0 ggtitle(\"Average List Price per Subcategory\") +\r\n  \u00a0 labs(x=\"Product Subcategories\", y=\"List Prices (US dollars)\", fill=\"Categories\") +\r\n  \u00a0 theme_bw() +\r\n  \u00a0 theme(title=element_text(size=16, color=\"blue3\")) +\r\n  \u00a0 geom_text(aes(label=NumberProducts), size=4, hjust=-0.2)\r\n  print(chart)\r\n  dev.off()';<\/pre>\n
    In this case, we\u2019re using the geom_text<\/strong> function to specify that the values in the NumberProducts<\/strong> column be used as bar labels. We do this by assigning the column to the label<\/strong> property. Notice that we must use the aes<\/strong> function to set the property value because we\u2019re specifying a column (variable), rather than a literal value.<\/p>\n
    Next, we set the size<\/strong> value to 4<\/strong> points and the hjust<\/strong> value to -0.2<\/strong> points to provide a bit of space between the bar and the label, as shown in the following figure.<\/p>\n
    <\/p>\n
    This is just a small addition to our graphic, but it shows how we can keep adding layers to provide us with exactly the information we need, based on the available data. In fact, we can even incorporate different geoms, treating each one as an additional layer.<\/p>\n
    Creating a stacked bar chart<\/h2>\n
    The ggplot2<\/strong> package also allows us to create a stacked bar chart, in which each bar is broken into segments, based on a set of data values. For example, we can create a stacked bar chart broken down by product category and then by subcategory, as shown in the following graphic definition:<\/p>\n
    df2 <- df[df$NumberProducts > 3, ]\r\n  chart <- ggplot(data=df2, aes(x=Category, fill=Subcategory)) +\r\n  \u00a0 geom_bar() +\r\n  \u00a0 ggtitle(\"Categories and Subcategories\") +\r\n  \u00a0 labs(x=\"Product Categories\", y=\"Number of Subcategories per Category\", fill=\"Subcategories\") +\r\n  \u00a0 theme_bw() +\r\n  \u00a0 theme(title=element_text(size=16, color=\"blue3\"))\r\n  print(chart)\r\n  dev.off()';<\/pre>\n
    You\u2019ve seen all these elements before, but notice that that the ggplot<\/strong> function now specifies the Category<\/strong> column as the X-axis and includes the fill<\/strong> property, with the Subcategory<\/strong> column as its value.<\/p>\n
    Also note that, before defining the graphic, we filter the df<\/strong> data frame so it includes only subcategories with more than three products. We then assign the filtered data frame to the df2<\/strong> variable, which we then use in the ggplot<\/strong> function. The R script now generates the graphic shown in the following figure.<\/p>\n
    <\/p>\n
    That\u2019s all there is to creating a simple stacked bar chart. We need only assign the column name to the fill<\/strong> argument, using the aes<\/strong> function to wrap the argument and value. You might have also noticed here and elsewhere that we are able to include multiple arguments in a single aes<\/strong> call, in this case, x<\/strong> and fill<\/strong>. You can specify multiple aesthetics whenever calling the aes<\/strong> function.<\/p>\n
    Creating a density plot<\/h2>\n
    Let\u2019s look at one more example that demonstrates a few additional concepts. This time we\u2019ll create a density plot, which shows how data values are distributed across a specified interval. For this example, we\u2019ll plot the AvgListPrice<\/strong> values for each product subcategory, as they\u2019re distributed across the three product categories:<\/p>\n
    chart <- ggplot(data=df, aes(x=AvgListPrice, fill=Category)) +\r\n  \u00a0 geom_density(alpha=0.4) +\r\n  \u00a0 ggtitle(\"Product Density Plot Chart\") +\r\n  \u00a0 labs(x=\"Average List Prices\", y=\"Density\", fill=\"Categories\") +\r\n  \u00a0 theme_bw() +\r\n  \u00a0 theme(title=element_text(size=16, color=\"blue3\"))\r\n  print(chart)\r\n  dev.off()';<\/pre>\n
    To create a density plot, we start by defining the aesthetics when calling the ggplot<\/strong> function, setting the x<\/strong> property to the AvgListPrice<\/strong> column and the fill<\/strong> property to the Category<\/strong> column. Next, we call the geom_density<\/strong> function, passing in the alpha<\/strong> argument, which defines the percentage of transparency for the charted data (40% in this case).<\/p>\n
    All other layers are similar to what you\u2019ve seen in earlier examples. This time, however, the R script returns the following graphic.<\/p>\n
    <\/p>\n
    The chart shows the value densities across the categories, with the highest rate in the Accessories<\/strong> category. Because we set the alpha<\/strong> property to 40%, the colors are transparent, which makes it easier to view overlapping categories.<\/p>\n
    Working with ggplot2 R package<\/h2>\n
    There is a great deal more we can do with the ggplot2<\/strong> package when it comes to visualizing data, but what we\u2019ve covered here should give you a good idea of the package\u2019s capabilities. As we move on to the analytics side of the R language, keep the ggplot2<\/strong> package in mind for a quick way to render the analyzed data as graphics. The key is to get the data you need into a data frame and then use that data frame to visualize the data.<\/p>\n
    In the meantime, play around with the ggplot2<\/strong> functions against various types of data so you can get a sense of the many options you have for visualizing data. Even if you rely solely on SQL Server data for now, as we have done here, you can still benefit from the extensive visualization capabilities at your fingertips (and impress the powers-that-be along the way). Then, as you dig deeper into other aspects of the R language, you\u2019ll be ready to turn those analytics into meaningful visualizations, using the large arsenal of tools already at your disposal.<\/p>\n","protected":false},"excerpt":{"rendered":"
    It is when you use R in SQL Server with one of the huge range of packages that comes with it that you can begin to appreciate the power of the system.  With a package such as ggplot there are many ‘knobs one can twiddle’ in order to get spectacular and informative visualisations. Rob Sheldon continues his beginners series for R in SQL Server by  showing how to refine the output to get it as you need it.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[],"coauthors":[6779],"class_list":["post-72120","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/72120","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=72120"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/72120\/revisions"}],"predecessor-version":[{"id":72279,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/72120\/revisions\/72279"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=72120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=72120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=72120"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=72120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}