{"id":2012,"date":"2015-06-02T00:00:00","date_gmt":"2015-06-02T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/using-r-to-explore-data-by-analysis-for-sql-professionals\/"},"modified":"2021-05-17T18:33:14","modified_gmt":"2021-05-17T18:33:14","slug":"using-r-to-explore-data-by-analysis-for-sql-professionals","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/using-r-to-explore-data-by-analysis-for-sql-professionals\/","title":{"rendered":"Using R to Explore Data by Analysis – for SQL Professionals"},"content":{"rendered":"
\n

Data analysis is the process by which data becomes understanding, knowledge and insight<\/i><\/p>\n

In writing this article, I aim to show how you can explore data by analysis, using R to investigate data stored in flat files. In doing so, I’ll compare how some of these tasks are done in T-SQL and in R, as a way of introducing some of the basics of R for T-SQL developers.<\/p>\n

Let’s assume that we are doing some preliminary investigation of some fairly structured data, and we want to perform a rudimentary analysis just to see if we can get value from it. This can of course be done with the tools of the trade in any RDBMS system, however with the R environment this is significantly more valuable because, even if we don’t take advantage of its unique ability to do parametric statistical analysis, it is free, takes little time compared to RDBMS systems and the data modelling part is fairly easy to do.<\/p>\n

Here is what the data exploration process looks like: <\/p>\n

\"2204-img7.jpg\"<\/p>\n

This concept is not new, and it is applied similarly in the RDBMS world. The main contrast is that in ordinary RDBMS systems, the data gets persisted in several stages whereas in R the operations are done in memory as soon as the data is read into it. You can, of course, persist the data at any point with R, though without the resilience of a database. An additional contrast is that there is no need for a physical model layer in R, whereas the RDBMS heavily relies on it.  <\/p>\n

It is essential to tidy the data and have a manageable convention for it: In an RDBMS, every column refers to an attribute and every row denotes an entity. In R every column is called a variable<\/b>, and every row is called an observation<\/b>. The purpose of tidying the data is to form properly the variables and the observations, so it is easier to model, transform and visualize later on. <\/p>\n

After the data is tidy, we get into the loop of modelling, transformation and visualization. This is an iterative task, which has the purpose of modeling and transforming the data until the best visualization can be produced. There are many ways to do the separate steps of modelling, transformation and visualization steps: However, in this article I have chosen to use dplyr<\/b> and ggplot2<\/b> packages. I tried to be flippant by writing that the modelling is done by ‘trial and error’ – there are many packages which perform modelling and the most suitable choice depends on what our goal is: Are we doing regression? Are we doing predictive analysis? In this case I will do just a rudimentary exploration analysis, just to see if there is anything even worth extracting from the data. In the real world, this would be done first, and later on a modelling tool would be used to do a deeper analysis, depending on the findings of the exploratory analysis. <\/p>\n

For this article, we will be using aircraft performance data as sample data. As a side note: I do not have deep knowledge about aircraft, but my curiosity about the field started early, after my first flight on a Tupolev-134. <\/p>\n

The tidying process will be omitted, since the data I found on the net is very well structured, it is in a csv format already. You can download the datasets from the top of this article. <\/p>\n

The tools of the trade<\/h1>\n

We will be using the R language environment for the purpose of data exploration:  RStudio and two packages: dplyr and ggplot2. Dplyr<\/b> is a powerful package for managing and transforming datasets and ggplot2<\/b> is a comprehensive package which is used to visualize the data.<\/p>\n

Here are the most important commands in dplyr<\/b> which we will be using in this article:<\/p>\n