Module 3: Data Visualization using R

Reading and getting data into R (External Data): Using CSV files, XML files, Web Data,
JSONfiles, Databases, Excel files. Working with R Charts and Graphs: Histograms,
Boxplots ,Bar Charts, Line Graphs, Scatterplots, Pie Charts

Reading and getting data into R (External Data): Using CSV files,
XML files, Web Data, JSONfiles, Databases, Excel files.

 Reading HTML Tables

Reading and retrieving HTML tables from the web, we use the XML and RCurl packages in
R programming.

Below scripts are used to install XML and Rcurl libraries.

[Link]("XML")
[Link]("RCurl")
For load the packages, use the following command:
library("XML")
library("RCurl")

For example, we will fetch the ‘Fiscal annual growth table from a url using the
readHTMLTable() function which stores it as a Data Frame.

#To fetch a table from any website paste the url

url <- "[Link]
tabs <- getURL(url)
#To fetch the first table, if the webpage has more than one table, we use
which = 1
tabs <- readHTMLTable(tabs,which = 1, stringsAsFactors = F)
head(tabs)

Output:

str(tabs)
Output:

Importing a TXT file in R

In this part, we will use the Drake Lyrics dataset to load a text file. The file consists of Lyrics
from the singer Drake. We can use the `readLines` function to load the simple file, but we have to
perform additional tasks to convert it into a dataframe.

We will use [Link]’s alternative function, `[Link]` to load the text file as an R
dataframe. Other [Link]’s alternative functions are [Link], read.csv2, and read.delim2.

Note: by default, it is separating the values on Tab (sep = "\t")

The text file consists of lyrics and doesn't have a header row. To display all of the lyrics in a
row, we need to set `header = F`.

You can also use other parameters to customize your dataframe, for example, the fill
parameter, which sets the blank field to be added to rows of unequal length.
Read the documentation to learn about every parameter in the [Link]’s alternative
functions.

data3 <- [Link]('data/drake_lyrics.txt',header = F)

head(data3, 5)

Importing data from a SAS file

In this section, we will use the haven package to import SAS files. You can download the
data from the GeeksforGeeks blog. The haven package allows you to load SAS, SPSS, and
Stata files into R with minimal code.

Provide the file directory to the `read_sas` function to load the `.sas7bdat` file as a dataframe.
Read the function’s documentation to learn more about it.

library(haven)

data10 <- read_sas('data/lond_small.sas7bdat')

# display data
head(data10,5)

Importing data from a SPSS file

As we already know, we can also use the haven package to load SPSS files into R. You can
download the data from the GeeksforGeeks blog and use the `read_sav` function just to load
the SPSS sav file.

It requires the file directory as a string and you can modify your dataframe by using
additional arguments such as encoding, col_select, and compress.

library(haven)

data11 <- read_sav("data/airline_passengers.sav")

head(data11,5)
You can also use a foreign package to load a `.sav` file as a dataframe using the `[Link]`
function. The function requires only two arguments file and [Link]. Learn more about
other arguments by reading the function's documentation.

Note: foreign package also allows you to load Minitab, S, SAS, SPSS, Stata, Systat, Weka,
and Octave file formats.

library("foreign")

data12 <- [Link]("data/airline_passengers.sav", [Link] = TRUE)

head(data12,5)

Importing Stata Files into R

In this part, we will use the foreign package to load the Stata file from [Link].

The [Link] reads a file in Stata version 5--12 binary formats and converts it into a data
frame.

“It is that simple.“

library("foreign")
data13 <- [Link]("data/[Link]")
head(data13,5)

Importing Matlab Files into R

Matlab is quite famous among students and researchers. The [Link] allows us to load
the`.mat` file, so that we can perform data analysis and run simulations in R.

Download the Matlab files from Kaggle to try it on your own.

library([Link])
data14 <- readMat("data/cross_dsads.mat")
head(data14$[Link])
R CSV Files
 A Comma-Separated Values (CSV) file is a plain text file which contains a list of
data. These files are often used for the exchange of data between different
applications. For example, databases and contact managers mostly support CSV files.
 These files can sometimes be called character-separated values or comma-
delimited files. They often use the comma character to separate data, but sometimes
use other characters such as semicolons. The idea is that we can export the complex
data from one application to a CSV file, and then importing the data in that CSV file
to another application.
 Storing data in excel spreadsheets is the most common way for data storing, which is
used by the data scientists. There are lots of packages in R designed for accessing data
from the excel spreadsheet. Users often find it easier to save their spreadsheets in
comma-separated value files and then use R's built-in functionality to read and
manipulate the data.
 R allows us to read data from files which are stored outside the R environment. Let's
start understanding how we can read and write data into CSV files. The file should be
present in the current working directory so that R can read it. We can also set our
directory and read file from there.
Getting and setting the working directory

In R, getwd() and setwd() are the two useful functions. The getwd() function is used to check
on which directory the R workspace is pointing. And the setwd() function is used to set a new
working directory to read and write files from that directory.

Let's see an example to understand how getwd() and setwd() functions are used.

Example

1. # Getting and printing current working directory.

2. print(getwd())
3. # Setting the current working directory.
4. setwd("C:/Users/ajeet")
5. # Getting and printingthe current working directory.
6. print(getwd())

Output

Creating a CSV File

A text file in which a comma separates the value in a column is known as a CSV file. Let's
start by creating a CSV file with the help of the data, which is mentioned below by saving
with .csv extension using the save As All files(*.*) option in the notepad.

Example: [Link]

1. id,name,salary,start_date,dept
2. 1,Shubham,613.3,2012-01-01,IT
3. 2,Arpita,525.2,2013-09-23,Operations
4. 3,Vaishali,63,2014-11-15,IT
5. 4,Nishka,749,2014-05-11,HR
6. 5,Gunjan,863.25,2015-03-27,Finance
7. 6,Sumit,588,2013-05-21,IT
8. 7,Anisha,932.8,2013-07-30,Operations
9. 8,Akash,712.5,2014-06-17,Financ

Output
Reading a CSV file

R has a rich set of functions. R provides [Link]() function, which allows us to read a CSV
file available in our current working directory. This function takes the file name as an input
and returns all the records present on it.

Let's use our [Link] file to read records from it using [Link]() function.

Example

1. data <- [Link]("[Link]")

2. print(data)

When we execute above code, it will give the following output

Output

Analyzing the CSV File

When we read data from the .csv file using [Link]() function, by default, it gives the output
as a data frame. Before analyzing data, let's start checking the form of our output with the
help of [Link]() function. After that, we will check the number of rows and number of
columns with the help of nrow() and ncol() function.

Example

1. csv_data<- [Link]("[Link]")
 2. print([Link](csv_data))
3. print(ncol(csv_data))
4. print(nrow(csv_data))

When we run above code, it will generate the following output:

Output

From the above output, it is clear that our data is read in the form of the data frame. So we
can apply all the functions of the data frame, which we have discussed in the earlier sections.

Example: Getting the maximum salary

1. # Creating a data frame.

2. csv_data<- [Link]("[Link]")
3.
4. # Getting the maximum salary from data frame.
5. max_sal<- max(csv_data$salary)
6. print(max_sal)

Output
Example: Getting the details of the person who have a maximum salary

1. # Creating a data frame.

2. csv_data<- [Link]("[Link]")
3.
4. # Getting the maximum salary from data frame.
5. max_sal<- max(csv_data$salary)
6. print(max_sal)
7.
8. #Getting the detais of the pweson who have maximum salary
9. details <- subset(csv_data,salary==max(salary))
10. print(details)

Output

Example: Getting the details of all the persons who are working in the IT department

1. # Creating a data frame.

2. csv_data<- [Link]("[Link]")
3.
4. #Getting the detais of all the pweson who are working in IT department
5. details <- subset(csv_data,dept=="IT")
6. print(details)

Output

Example: Getting the details of the persons whose salary is greater than 600 and
working in the IT department.

1. # Creating a data frame.

2. csv_data<- [Link]("[Link]")
3.
4. #Getting the detais of all the pweson who are working in IT department
5. details <- subset(csv_data,dept=="IT"&salary>600)
6. print(details)
 Output

Example: Getting details of those peoples who joined on or after 2014.

1. # Creating a data frame.

2. csv_data<- [Link]("[Link]")
3.
4. #Getting details of those peoples who joined on or after 2014
5. details <- subset(csv_data,[Link](start_date)>[Link]("2014-01-01"))
6. print(details)

Output

Writing into a CSV file

Like reading and analyzing, R also allows us to write into the .csv file. For this purpose, R
provides a [Link]() function. This function creates a CSV file from an existing data frame.
This function creates the file in the current working directory.

Let's see an example to understand how [Link]() function is used to create an output CSV
file.

Example

1. csv_data<- [Link]("[Link]")
2.
3. #Getting details of those peoples who joined on or after 2014
4. details <- subset(csv_data,[Link](start_date)>[Link]("2014-01-01"))
5.
6. # Writing filtered data into a new file.
7. [Link](details,"[Link]")
8. new_details<- [Link]("[Link]")
9. print(new_details)

Output
R Excel file
The xlsx is a file extension of a spreadsheet file format which was created by Microsoft to
work with Microsoft Excel. In the present era, Microsoft Excel is a widely used spreadsheet
program that sores data in the .xls or .xlsx format. R allows us to read data directly from these
files by providing some excel specific packages. There are lots of packages such as
XLConnect, xlsx, gdata, etc. We will use xlsx package, which not only allows us to read data
from an excel file but also allow us to write data in it.

Install xlsx Package

1. [Link]("package name")

Example

1. [Link]("xlsx")

Output

Verifying and Loading of "xlsx" Package

In R, grepl() and any() functions are used to verify the package. If the packages are installed,
these functions will return True else return False. For verifying the package, both the
functions are used together.

For loading purposes, we use the library() function with the appropriate package name. This
function loads all the additional packages also.

Example

1. #Installing xlsx package

2. [Link]("xlsx")
3.
4. # Verifying the package is installed.
5. any(grepl("xlsx",[Link]()))
6.
7. # Loading the library into R workspace.
8. library("xlsx")

Output
Creating an xlsx File

Once the xlsx package is loaded into our system, we will create an excel file with the
following data and named it employee.

Apart from this, we will create another table with the following data and give it a name as
employee_info.
Reading the Excel File

R provides [Link]() function, which takes two arguments as input, i.e., file name and index
of the sheet. This function returns the excel data in the form of a data frame in the R
environment. There is the following syntax of [Link]() function:

1. [Link](file_name,sheet_index)

Let's see an example in which we read data from our [Link] file.

Example

1. #Loading xlsx package

2. library("xlsx")
3.
4. # Reading the first worksheet in the file [Link].
5. excel_data<- [Link]("[Link]", sheetIndex = 1)
6. print(excel_data)

Output
Writing data into Excel File

In R, we can also write the data into our .xlsx file. R provides a [Link]() function to write
data into the excel file. There is the following syntax of [Link]() function:

1. [Link](data_frame,file_name,[Link],[Link],sheetnames,append)

Here,

 The data_frame is our data, which we want to insert into our excel file.
 The file_names is the name of that file in which we want to insert our data.
 The [Link] and [Link] are the logical values that are specifying whether the
column names/row names of the data frame are to be written to the file.
 The append is a logical value, which indicates our data should be appended or not into
an existing file.

Let's see an example to understand how [Link]() function works with its parameters.

Example

1. #Loading xlsx package

2. library("xlsx")
3.
4. #Creating data frame
5. [Link]<- [Link](
6. name = c("Raman","Rafia","Himanshu","jasmine","Yash"),
7. salary = c(623.3,915.2,611.0,729.0,843.25),
8. start_date = [Link](c("2012-01-01", "2013-09-23", "2014-11-15", "2014-05-
11","2015-03-27")),
9. dept = c("Operations","IT","HR","IT","Finance"),
10. stringsAsFactors = FALSE
11. )
12.
13. # Writing the first data set in [Link]
14. [Link]([Link], file = "[Link]", [Link]=TRUE, [Link]=TRUE,sh
eetName="Sheet2",append = TRUE)
15.
16. # Reading the first worksheet in the file [Link].
17. excel_data<- [Link]("[Link]", sheetIndex = 1)
18. print(excel_data)
19.
20. # Reading the first worksheet in the file [Link].
21. excel_data<- [Link]("[Link]", sheetIndex = 2)
22. print(excel_data)

Output
R Binary File
 A binary file is a file which contains information present only in the form of bits and
bytes(0's and 1's). They are not human-readable because the bytes translate into
characters and symbols that contain many other non-printable characters. If we will
read a binary file using any text editor, it will show the characters like ð and Ø.
 The code is relatively very easy to read binary data into R. To read binary data, we
must know how a piece of information has been parsed into binary.
 The binary file must be read by specific programs to be useful. For example, the
binary file of a Microsoft Word program can only be read by the Word program in a
human-readable form. It indicates that, in addition to human-readable text, there is a
lot of information such as character formatting and page numbers, etc., which are also
stored with alphanumeric characters. And finally, a binary file is a contiguous
sequence of bytes. The line break we see in a text file is a character joining the first
line to the next line.
 Sometimes, the data generated by other programs need to be processed by R as a
binary file. Also, R needs to create binary files that can be shared with other
programs. There are two functions writeBin () and readBin () for creating and reading
binary files in R.

Writing the Binary File

Like CSV and Excel files, we can also write into a binary file. R provides a writeBin()
function for writing the data into a binary file. There is the following syntax of writeBin()
function:
 1. writeBin(object,con)

Here,

 The ?con' is the connection object which is used to write the binary file.
 The ?object' is the binary file in which we write our data.

Let's see an example to understand how this function is used to write data into a file in binary
format. In the following example, we will use R inbuilt data "mtcars." We will create a CSV
file from it and convert it into a binary file.

Example

1. # Reading the "mtcars" data frame as a csv file and will store only the columns "cyl",
"am" and "gear".
2. [Link](mtcars, file = "[Link]",[Link] = FALSE, na = "",
3. [Link] = TRUE, sep = ",")
4.
5. # Storing 5 records from the csv file as a new data frame.
6. [Link] <- [Link]("[Link]",sep = ",",header = TRUE,nrows = 5)
7. [Link]
8.
9. # Creating a connection object to write the binary file using mode "wb".
10. [Link] = file("/Users/ajeet/R/[Link]", "wb")
11.
12. # Writing the column names of the data frame to the connection object.
13. writeBin(colnames([Link]), [Link])
14.
15. # Writing the records in each of the column to the file.
16. writeBin(c([Link]$cyl,[Link]$am,[Link]$gear), [Link])
17.
18. # Closing the file for writing so that other programs can read it.
19. close([Link])

Output
Reading the Binary File

We can also read our binary file which we have created before. For this purpose, R provides a
readBin() function for reading the data from a binary file.

There is the following syntax of readbin() function:

1. readBin(con,what,n)

Here,

 The ?con' is the connection object which is used to read the binary file.
 The ?what' is the mode such as character, integer, etc. which represent the bytes to be
read.
 The ?n' is the number of bytes which we want to read from the binary file.

Let's see an example in which we read our binary data from [Link] file.

Example

1. # Creating a connection object to read the file in binary mode using "rb".
2. [Link] <- file("/Users/ajeet/R/[Link]", "rb")
3.
4. # Reading the column names. n = 3 as we have 3 columns.
5. [Link] <- readBin([Link], character(), n = 3)
6.
7. # Reading the column values. n = 18 as we have 3 column names and 15 values.
8. [Link] <- file("/Users/ajeet/R/[Link]", "rb")
9. bin_data <- readBin([Link], integer(), n = 18)
 10.
11. # Printing the data.
12. print(bin_data)
13.
14. # Reading the values from 4th byte to 8th byte, which represents "cyl."
15. cyl_data = bin_data[4:8]
16. print(cyl_data)
17.
18. # Reading the values form 9th byte to 13th byte which represents "am".
19. am_data = bin_data[9:13]
20. print(am_data)
21.
22. # Reading the values form 9th byte to 13th byte which represents "gear".
23. gear_data = bin_data[14:18]
24. print(gear_data)
25.
26. # Combining all the read values to a dat frame.
27. final_data = cbind(cyl_data, am_data, gear_data)
28. colnames(final_data) = [Link]
29. print(final_data)

Output

R JSON File
JSON stands for JavaScript Object Notation. The JSON file contains the data as text
in a human-readable format. Like other files, we can also read and write into the
JSON files. For this purpose, R provides a package named rjson, which we have to
install with the help of the familiar command [Link].

Install rjson package

By running the following command into the R console, we will install the rjson package into
our current working directory.

1. [Link]("rjson")
Creating a JSON file

The extension of JSON file is .json. To create the JSON file, we will save the following data
as employee_info.json. We can write the information of employees in any text editor with its
appropriate rule of writing the JSON file. In JSON files, the information contains in between
the curly braces({}).

Example: employee_info.json

1. {
2. "id":["1","2","3","4","5","6","7","8" ],
3. "name":
["Shubham","Nishka","Gunjan","Sumit","Arpita","Vaishali","Anisha","Ginni" ],
4. "salary":["623","552","669","825","762","882","783","964"],
5.
6. "start_date":[ "1/1/2012","9/15/2013","11/23/2013","5/11/2014","3/27/2015","5/21/
2013",
7. "7/30/2013","6/17/2014"],
8. "dept":[ "IT","Operations","Finance","HR","Finance","IT","Operations","Finance"]
9. }

Output

Read the JSON file

Reading the JSON file in R is a very easy and effective process. R provide from JSON()
function to extract data from a JSON file. This function, by default, extracts the data in the
form of a list. This function takes the JSON file and returns the records which are contained
in it.

Let's see an example to understand how fromJSON() function is used to extract data and print
the result in the form of a list. We will consider the employee_info.json file which we have
created before.

Example

1. # Loading the package which is required to read JSON files.

 2. library("rjson")
3.
4. # Giving the input file name to the function fromJSON.
5. result <- fromJSON(file = "employee_info.json")
6.
7. # Printing the result.
8. print(result)

Output

Converting JSON data to a Data Frame

R provide, [Link]() function to convert the extracted data into data frame. For further
analysis, data analysts use this function. Let's start an example to see how this function is
used, and in our example, we will consider our employee_info.json file.

Example

1. # Loading the package which is required to read JSON files.

2. library("rjson")
3.
4. # Giving the input file name to the function fromJSON.
5. result <- fromJSON(file = "employee_info.json")
6.
7. # Converting the JSON record to a data frame.
8. data_frame <- [Link](result)
9.
10. #Printing JSON data frame
11. print(data_frame)

Output
R XML File
Like HTML, XML is also a markup language which stands for Extensible Markup Language.
It is developed by World Wide Web Consortium(W3C) to define the syntax for encoding
documents which both humans and machine can read. This file contains markup tags. There
is a difference between HTML and XML. In HTML, the markup tag describes the structure
of the page, and in xml, it describes the meaning of the data contained in the file. In R, we
can read the xml files by installing "XML" package into the R environment. This package
will be installed with the help of the familiar command i.e., [Link].

1. [Link]("XML")

Creating XML File

We will create an xml file with the help of the given data. We will save the following data
with the .xml file extension to create an xml file. XML tags describe the meaning of data, so
that data contained in such tags can easily tell or explain about the data.

Example: xml_data.xml

1. <records>
2. <employee_info>
3. <id>1</id>
4. <name>Shubham</name>
5. <salary>623</salary>
6. <date>1/1/2012</date>
7. <dept>IT</dept>
8. </employee_info>
9.
10. <employee_info>
11. <id>2</id>
12. <name>Nishka</name>
13. <salary>552</salary>
14. <date>1/1/2012</date>
15. <dept>IT</dept>
16. </employee_info>
17.
18. <employee_info>
19. <id>1</id>
20. <name>Gunjan</name>
21. <salary>669</salary>
22. <date>1/1/2012</date>
23. <dept>IT</dept>
24. </employee_info>
25.
26. <employee_info>
27. <id>1</id>
28. <name>Sumit</name>
29. <salary>825</salary>
30. <date>1/1/2012</date>
31. <dept>IT</dept>
32. </employee_info>
33.
34. <employee_info>
35. <id>1</id>
36. <name>Arpita</name>
37. <salary>762</salary>
38. <date>1/1/2012</date>
39. <dept>IT</dept>
40. </employee_info>
41.
42. <employee_info>
43. <id>1</id>
44. <name>Vaishali</name>
45. <salary>882</salary>
46. <date>1/1/2012</date>
47. <dept>IT</dept>
48. </employee_info>
49.
50. <employee_info>
51. <id>1</id>
52. <name>Anisha</name>
53. <salary>783</salary>
54. <date>1/1/2012</date>
55. <dept>IT</dept>
56. </employee_info>
57.
58. <employee_info>
59. <id>1</id>
60. <name>Ginni</name>
61. <salary>964</salary>
62. <date>1/1/2012</date>
63. <dept>IT</dept>
64. </employee_info>
65.
66. </records>
Reading XML File

In R, we can easily read an xml file with the help of xmlParse() function. This function is
stored as a list in R. To use this function, we first need to load the xml package with the help
of the library() function. Apart from the xml package, we also need to load one additional
package named methods.

Let's see an example to understand the working of xmlParse() function in which we read our
xml_data.xml file.

Example: Reading xml data in the form of a list.

1. # Loading the package required to read XML files.

2. library("XML")
3.
4. # Also loading the other required package.
5. library("methods")
6.
7. # Giving the input file name to the function.
8. result <- xmlParse(file = "xml_data.xml")
9.
10. xml_data <- xmlToList(result)
11. print(xml_data)

Output
Example: Getting number of nodes present in xml file.

1. # Loading the package required to read XML files.

2. library("XML")
3.
4. # Also loading the other required package.
5. library("methods")
6.
7. # Giving the input file name to the function.
8. result <- xmlParse(file = "xml_data.xml")
9.
10. #Converting the data into list
11. xml_data <- xmlToList(result)
12.
13. #Printing the data
14. print(xml_data)
15.
16. # Exracting the root node form the xml file.
17. root_node <- xmlRoot(result)
18.
19. # Finding the number of nodes in the root.
20. root_size <- xmlSize(root_node)
21.
22. # Printing the result.
23. print(root_size)

Output
 Example: Getting details of the first node in xml.

1. # Loading the package required to read XML files.

2. library("XML")
3.
4. # Also loading the other required package.
5. library("methods")
6.
7. # Giving the input file name to the function.
8. result <- xmlParse(file = "xml_data.xml")
9.
10. # Exracting the root node form the xml file.
11. root_node <- xmlRoot(result)
12.
13. # Printing the result.
14. print(root_node[1])

Output

Example: Getting details of different elements of a node.

1. # Loading the package required to read XML files.

2. library("XML")
3.
 4. # Also loading the other required package.
5. library("methods")
6.
7. # Giving the input file name to the function.
8. result <- xmlParse(file = "xml_data.xml")
9.
10. # Exracting the root node form the xml file.
11. root_node <- xmlRoot(result)
12.
13. # Getting the first element of the first node.
14. print(root_node[[1]][[1]])
15.
16. # Getting the fourth element of the first node.
17. print(root_node[[1]][[4]])
18.
19. # Getting the third element of the third node.
20. print(root_node[[3]][[3]])

Output

How to convert xml data into a data frame

It's not easy to handle data effectively in large files. For this purpose, we read the data in the
xml file as a data frame. Then this data frame is processed by the data analyst. R provide
xmlToDataFrame() function to extract the information in the form of Data Frame.

Let's see an example to understand how this function is used and processed:

Example

1. # Loading the package required to read XML files.

2. library("XML")
3.
4. # Also loading the other required package.
5. library("methods")
6.
7. # Giving the input file name to the function xmlToDataFrame.
8. data_frame <- xmlToDataFrame("xml_data.xml")
 9.
10. #Printing the result
11. print(data_frame)

Output

R Database
In the relational database management system, the data is stored in a normalized format.
Therefore, to complete statistical computing, we need very advanced and complex SQL
queries. The large and huge data which is present in the form of tables require SQL queries to
extract the data from it.

R can easily connect with many of the relational databases like MySql, SQL Server, Oracle,
etc. When we extract the information from these databases, by default, the information is
extracted in the form of data frame. Once, the data comes from the database to the R
environment; it will become a normal R dataset. The data analyst can easily analyze or
manipulate the data with the help of all the powerful packages and functions.

RMySQL Package

RMySQL package is one of the most important built-in package of R. This package provides
native connectivity between the R and MySql database. In R, to work with MySql database,
we first have to install the RMySQL package with the help of the familiar command, which is
as follows:

1. [Link]("RMySQL")

When we run the above command in the R environment, it will start downloading the
package RMySQL.

We have created a database employee in which there is a table employee_info, which has the
following record.

We will use the data which we have mentioned above in our upcoming topics.

Create a connection between R and MySql

To work with MySql database, it is required to create a connection object between R and the
database. For creating a connection, R provides dbConnect() function. This function takes
the username, password, database name, and host name as input parameters. Let's see an
example to understand how the dbConnect() function is used to connect with the database.

Example

1. #Loading RMySQL package into R

2. library("RMySQL")
3.
4. # Creating a connection Object to MySQL database.
5. # Conneting with database named "employee" which we have created befoe with the helpof
XAMPP server.
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
7. host = 'localhost')
 8.
9. # Listing the tables available in this database.
10. dbListTables(mysql_connect)

Output

R MySQL Commands

In R, we can perform all the SQL commands like insert, delete, update, etc. For performing
the query on the database, R provides the dbSendQuery() function. The query is executed in
MySQL, and the result set is returned using the R fetch () function. Finally, it is stored in R as
a data frame. Let's see the example of each and every SQL command to understand how
dbSendQuery() and fetch() functions are used.

Create Table

R provides an additional function to create a table into the database i.e., dbWriteTable(). This
function creates a table in the database; if it does not exist else, it will overwrite the table.
This function takes the data frame as an input.

Example

1. #Loading RMySQL package into R

2. library("RMySQL")
3.
 4. # Creating a connection Object to MySQL database.
5. # Conneting with database named "employee" which we have created befoe with the helpof
XAMPP server.
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
7. host = 'localhost')
8.
9. #Creating data frame to create a table
10. [Link]<- [Link](
11. name = c("Raman","Rafia","Himanshu","jasmine","Yash"),
12. salary = c(623.3,915.2,611.0,729.0,843.25),
13. start_date = [Link](c("2012-01-01", "2013-09-23", "2014-11-15", "2014-05-11","2015-03-
27")),
14. dept = c("Operations","IT","HR","IT","Finance"),
15. stringsAsFactors = FALSE
16. )
17.
18. # All the rows of [Link] are taken inot MySql.
19. dbWriteTable(mysql_connect, "emp", [Link][, ], overwrite = TRUE)

Output

Select

We can simply select the record from the table with the help of the fetch() and
dbSendQuery() function. Let's see an example to understand how to select query works with
these two functions.

Example
 1. #Loading RMySQL package into R
2. library("RMySQL")
3.
4. # Creating a connection Object to MySQL database.
5. # Conneting with database named "employee" which we have created befoe with the helpof
XAMPP server.
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
7. host = 'localhost')
8.
9. # selecting the record from employee_info table.
10. record = dbSendQuery(mysql_connect, "select * from employee_info")
11.
12. # Storing the result in a R data frame object. n = 6 is used to fetch first 6 rows.
13. data_frame = fetch(record, n = 6)
14. print(data_frame)

Output

Select with where clause

We can select the specific record from the table with the help of the fetch() and
dbSendQuery() function. Let's see an example to understand how to select query works with
where clause and these two functions.

Example

1. #Loading RMySQL package into R

2. library("RMySQL")
3.
4. # Creating a connection Object to MySQL database.
5. # Conneting with database named "employee" which we have created befoe with the helpof
XAMPP server.
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
7. host = 'localhost')
8.
9. # selecting the specific record from employee_info table.
10. record = dbSendQuery(mysql_connect, "select * from employee_info where dept='IT'")
11.
12. # Fetching all the records(with n = -1) and storing it as a data frame.
13. data_frame = fetch(record, n = -1)
 14. print(data_frame)

Output

Insert command

We can insert the data into tables with the help of the familiar method dbSendQuery()
function.

Example

1. #Loading RMySQL package into R

2. library("RMySQL")
3.
4. # Creating a connection Object to MySQL database.
5. # Conneting with database named "employee" which we have created befoe with the helpof
XAMPP server.
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
7. host = 'localhost')
8.
9. # Inserting record into employee_info table.
10. dbSendQuery(mysql_connect, "insert into employee_info values(9,'Preeti',1025,'8/25/2013',
'Operations')")

Output
Update command

Updating a record in the table is much easier. For this purpose, we have to pass the update
query to the dbSendQuery() function.

Example

1. #Loading RMySQL package into R

2. library("RMySQL")
3.
4. # Creating a connection Object to MySQL database.
5. # Conneting with database named "employee" which we have created befoe with the helpof
XAMPP server.
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
7. host = 'localhost')
8.
9. # Updating the record in employee_info table.
10. dbSendQuery(mysql_connect, "update employee_info set dept='IT' where id=9")

Output
Delete command

Below is an example in which we delete a specific row from the table by passing the delete
query in the dbSendQuery() function.

Example

1. #Loading RMySQL package into R

2. library("RMySQL")
3.
4. # Creating a connection Object to MySQL database.
5. # Conneting with database named "employee" which we have created befoe with the helpof
XAMPP server.
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
7. host = 'localhost')
8.
9. # Deleting the specific record from employee_info table.
10. dbSendQuery(mysql_connect, "delete from employee_info where id=8")

Output
Drop command

Below is an example in which we drop a table from the database by passing the appropriate
drop query in the dbSendQuery() function.

Example

1. #Loading RMySQL package into R

2. library("RMySQL")
3.
4. # Creating a connection Object to MySQL database.
5. # Conneting with database named "employee" which we have created befoe with the helpof
XAMPP server.
6. mysql_connect = dbConnect(MySQL(), user = 'root', password = '', dbname = 'employee',
7. host = 'localhost')
8.
9. # Dropping the specific table from the employee database.
10. dbSendQuery(mysql_connect, "drop table if exists emp")

Output
 Working with R Charts and Graphs
Graphics in R
 R is a language and environment for statistical computing
and graphics. R is also extremely flexible and easy to use
when it comes to creating visualisations. One of its
capabilities is to produce good-quality plots with minimum
codes.
 Graphics play an important role in carrying out the important features of
the data. Graphics are used to examine marginal distributions,
relationships between variables, and summary of very large data. It is a
very important complement for many statistical and computational
techniques.

R Data Visualization
In R, we can create visually appealing data visualizations by writing few lines of code.
For this purpose, we use the diverse functionalities of R. Data visualization is an
efficient technique for gaining insight about data through a visual medium. With the
help of visualization techniques, a human can easily obtain information about hidden
patterns in data that might be neglected.

By using the data visualization technique, we can work with large datasets to
efficiently obtain key insights about it.

R Visualization Packages
R provides a series of packages for data visualization. These packages are as follows:

1) plotly

The plotly package provides online interactive and quality graphs. This package
extends upon the JavaScript library ?[Link].

2) ggplot2

R allows us to create graphics declaratively. R provides the ggplot package for this
purpose. This package is famous for its elegant and quality graphs, which sets it apart
from other visualization packages.

3) tidyquant

The tidyquant is a financial package that is used for carrying out quantitative
financial analysis. This package adds under tidyverse universe as a financial package
that is used for importing, analyzing, and visualizing the data.
4) taucharts

Data plays an important role in taucharts. The library provides a declarative interface
for rapid mapping of data fields to visual properties.

5) ggiraph

It is a tool that allows us to create dynamic ggplot graphs. This package allows us to
add tooltips, JavaScript actions, and animations to the graphics.

6) geofacets

This package provides geofaceting functionality for 'ggplot2'. Geofaceting arranges a

sequence of plots for different geographical entities into a grid that preserves some
of the geographical orientation.

7) googleVis

googleVis provides an interface between R and Google's charts tools. With the help of
this package, we can create web pages with interactive charts based on R data
frames.

8) RColorBrewer

This package provides color schemes for maps and other graphics, which are
designed by Cynthia Brewer.

9) dygraphs

The dygraphs package is an R interface to the dygraphs JavaScript charting library. It

provides rich features for charting time-series data in R.

10) shiny

R allows us to develop interactive and aesthetically pleasing web apps by providing

a shiny package. This package provides various extensions with HTML widgets, CSS,
and JavaScript.

R Graphics
Graphics play an important role in carrying out the important features of the data.
Graphics are used to examine marginal distributions, relationships between
variables, and summary of very large data. It is a very important complement for
many statistical and computational techniques.
Standard Graphics
R standard graphics are available through package graphics, include several
functions which provide statistical plots, like:

o Scatterplots
o Piecharts
o Boxplots
o Barplots etc.

We use the above graphs that are typically a single function call.

Graphics Devices
It is something where we can make a plot to appear. A graphics device is a
window on your computer (screen device), a PDF file (file device), a Scalable
Vector Graphics (SVG) file (file device), or a PNG or JPEG file (file device).

There are some of the following points which are essential to

understand:

o The functions of graphics devices produce output, which depends on the

active graphics device.
o A screen is the default and most frequently used device.
o R graphical devices such as the PDF device, the JPEG device, etc. are used.
 o We just need to open the graphics output device which we want. Therefore,
R takes care of producing the type of output which is required by the
device.
o For producing a certain plot on the screen or as a GIF R graphics file, the R
code should exactly be the same. We only need to open the target output
device before.
o Several devices can be open at the same time, but there will be only one
active device.

The basics of the grammar of graphics

There are some key elements of a statistical graphic. These elements are the
basics of the grammar of graphics. Let's discuss each of the elements one by one
to gain the basic knowledge of graphics.

1) Data

Data is the most crucial thing which is processed and generates an output.

2) Aesthetic Mappings

Aesthetic mappings are one of the most important elements of a statistical

graphic. It controls the relation between graphics variables and data variables.
In a scatter plot, it also helps to map the temperature variable of a data set into
the X variable.

In graphics, it helps to map the species of a plant into the color of dots.

3) Geometric Objects

Geometric objects are used to express each observation by a point using the
aesthetic mappings. It maps two variables in the data set into the x,y variables
of the plot.

4) Statistical Transformations

Statistical transformations allow us to calculate the statistical analysis of the

data in the [Link] statistical transformation uses the data and approximates it
with the help of a regression line having x,y coordinates, and counts
occurrences of certain values.

5) Scales

It is used to map the data values into values present in the coordinate system
of the graphics device.

6) Coordinate system

The coordinate system plays an important role in the plotting of the data.

o Cartesian
o Plot

7) Faceting

Faceting is used to split the data into subgroups and draw sub-graphs for each
group.

Advantages of Data Visualization in R

1. Understanding

It can be more attractive to look at the business. And, it is easier to understand

through graphics and charts than a written document with text and numbers.
Thus, it can attract a wider range of audiences. Also, it promotes the
widespread use of business insights that come to make better decisions.

2. Efficiency

Its applications allow us to display a lot of information in a small space.

Although, the decision-making process in business is inherently complex and
multifunctional, displaying evaluation findings in a graph can allow companies
to organize a lot of interrelated information in useful ways.

3. Location

Its app utilizing features such as Geographic Maps and GIS can be particularly
relevant to wider business when the location is a very relevant factor. We will
use maps to show business insights from various locations, also consider the
seriousness of the issues, the reasons behind them, and working groups to
address them.

Disadvantages of Data Visualization in R

1. Cost

R application development range a good amount of money. It may not be

possible, especially for small companies, that many resources can be spent on
purchasing them. To generate reports, many companies may employ
professionals to create charts that can increase costs. Small enterprises are
often operating in resource-limited settings, and are also receiving timely
evaluation results that can often be of high importance.

2. Distraction

However, at times, data visualization apps create highly complex and fancy
graphics-rich reports and charts, which may entice users to focus more on the
form than the function. If we first add visual appeal, then the overall value of
the graphic representation will be minimal. In resource-setting, it is required to
understand how resources can be best used. And it is also not caught in the
graphics trend without a clear purpose.

Here is a list of all graph types :

 Barplot
 Boxplot
 Density Plot
 Heatmap
 Histogram
 Line Plot
 Pairs Plot
 Polygon Plot
 QQplot
 Scatterplot
  Venn Diagram
[Link](123)# Set seed for reproducibility
x <-rnorm(30)# Create x variable
y <- x +rnorm(30)# Create correlated y variable

Barplot:

Barplot Definition: A barplot (or barchart; bargraph) illustrates the association between a
numeric and a categorical variable. The barplot represents each category as a bar and reflects
the corresponding numeric value with the bar’s size.

The following R syntax shows how to draw a basic barplot in R:

barplot(x)# Draw barplot in R

Our example barplot looks a follows:

Boxplot

Boxplot Definition: A boxplot (or box-and-whisker plot) displays the distribution of a

numerical variable based on five summary statistics: minimum non-outlier; first quartile;
median; third quartile; and maximum non-outlier. Furthermore, boxplots show the
positioning of outliers and whether the data is skewed.

The following R syntax shows how to draw a basic boxplot in R:

boxplot(x)# Draw boxplot in R

Density Plot

Density Plot Definition: A density plot (or kernel density plot; density trace graph) shows
the distribution of a numerical variable over a continuous interval. Peaks of a density plot
visualize where the values of numerical variables are concentrated.

The following R syntax shows how to draw a basic density plot in R:

plot(density(x))# Draw density plot in R

Heatmap

Heatmap Definition: A heatmap (or shading matrix) visualizes individual values of a matrix
with colors. More common values are typically indicated by brighter reddish colors and less
common values are typically indicated by darker colors.

The following R syntax shows how to draw a basic heatmap in R:

heatmap(cbind(x, y))# Draw heatmap in R

Line Plot

Line Plot Definition: A line plot (or line graph; line chart) visualizes values along a
sequence (e.g. over time). Line plots consist of an x-axis and a y-axis. The x-axis usually
displays the sequence and the y-axis the values corresponding to each point of the sequence.

The following R syntax shows how to draw a basic line plot in R:

plot(1:length(y), y, type ="l")# Draw line plot in R

Histogram

Histogram Definition: A histogram groups continuous data into ranges and plots this data as
bars. The height of each bar shows the amount of observations within each range.

The following R syntax shows how to draw a basic histogram in R:

hist(x)# Draw histogram in R

Pairs Plot

Pairs Plot Definition: A pairs plot is a plot matrix, consisting of scatterplots for each
variable-combination of a data frame.

The following R syntax shows how to draw a basic pairs plot in R:

pairs([Link](x, y))# Draw pairs plot in R

Polygon Plot

Polygon Plot Definition: A polygon plot displays a plane geometric figure (i.e. a polygon)
within the plot.

The following R syntax shows how to draw a basic polygon plot in R:

plot(1, 1, # Draw polygon plot in R

col ="white",
xlab="X", ylab="Y")
polygon(x = c(0.7, 1.3, 1.3, 0.8),
y = c(0.6, 1.0, 1.4, 1.3),
col ="#353436")
QQplot

QQplot Definition: A QQplot (or Quantile-Quantile plot; Quantile-Quantile diagram)

determines whether two data sources come from a common distribution. QQplots draw
the quantiles of the two numerical data sources against each other. If both data sources come
from the same distribution, the points fall on a 45 degree angle.

The following R syntax shows how to draw a basic QQplot in R:

qqplot(x, y)# Draw QQplot in R

Scatterplot
Scatterplot Definition: A scatterplot (or scatter plot; scatter graph; scatter chart; scattergram;
scatter diagram) displays two numerical variables with points, whereby each point represents
the value of one variable on the x-axis and the value of the other variable on the y-axis.

The following R syntax shows how to draw a basic scatterplot in R:

plot(x, y)# Draw scatterplot in R

Venn Diagram

Venn Diagram Definition: A venn diagram (or primary diagram; set diagram; logic
diagram) illustrates all possible logical relations between certain data characteristics. Each
characteristic is represented as a circle, whereby overlapping parts of the circles illustrate
elements that have both characteristics at the same time.

The following R syntax shows how to draw a basic venn diagram in R:

[Link]("VennDiagram")# Install VennDiagram package

library("VennDiagram")# Load VennDiagram package
[Link]()# Draw empty plot
[Link](area =10)# Draw venn diagram

R Pie Charts
R programming language has several libraries for creating charts and graphs. A pie-chart is a
representation of values in the form of slices of a circle with different colors. Slices are
labeled with a description, and the numbers corresponding to each slice are also shown in the
chart. However, pie charts are not recommended in the R documentation, and their
characteristics are limited. The authors recommend a bar or dot plot on a pie chart because
people are able to measure length more accurately than volume.

The Pie charts are created with the help of pie () function, which takes positive numbers as
vector input. Additional parameters are used to control labels, colors, titles, etc.

There is the following syntax of the pie() function:

1. pie(X, Labels, Radius, Main, Col, Clockwise)

R Pie Charts
R programming language has several libraries for creating charts and graphs. A pie-chart is a
representation of values in the form of slices of a circle with different colors. Slices are
labeled with a description, and the numbers corresponding to each slice are also shown in the
chart. However, pie charts are not recommended in the R documentation, and their
characteristics are limited. The authors recommend a bar or dot plot on a pie chart because
people are able to measure length more accurately than volume.

The Pie charts are created with the help of pie () function, which takes positive numbers as
vector input. Additional parameters are used to control labels, colors, titles, etc.

There is the following syntax of the pie() function:

1. pie(X, Labels, Radius, Main, Col, Clockwise)

Here,

1. X is a vector that contains the numeric values used in the pie chart.
2. Labels are used to give the description to the slices.
3. Radius describes the radius of the pie chart.
4. Main describes the title of the chart.
5. Col defines the color palette.
6. Clockwise is a logical value that indicates the clockwise or anti-clockwise direction in which
slices are drawn.

Example

1. # Creating data for the graph.

2. x <- c(20, 65, 15, 50)
3. labels <- c("India", "America", "Shri Lanka", "Nepal")
4. # Giving the chart file a name.
5. png(file = "[Link]")
6. # Plotting the chart.
7. pie(x,labels)
8. # Saving the file.
9. [Link]()

Output:
3 Dimensional Pie Chart

In R, we can also create a three-dimensional pie chart. For this purpose, R provides a plotrix
package whose pie3D() function is used to create an attractive 3D pie chart. The parameters
of pie3D() function remain same as pie() function. Let's see an example to understand how a
3D pie chart is created with the help of this function.

Example

1. # Getting the library.

2. library(plotrix)
3. # Creating data for the graph.
4. x <- c(20, 65, 15, 50,45)
5. labels <- c("India", "America", "Shri Lanka", "Nepal","Bhutan")
6. # Give the chart file a name.
7. png(file = "3d_pie_chart1.jpg")
8. # Plot the chart.
9. pie3D(x,labelslabels = labels,explode = 0.1, main = "Country Pie Chart")
10. # Save the file.
11. [Link]()

Output:
Example

1. # Getting the library.

2. library(plotrix)
3. # Creating data for the graph.
4. x <- c(20, 65, 15, 50,45)
5. labels <- c("India", "America", "Shri Lanka", "Nepal","Bhutan")
6. pie_percent<- round(100*x/sum(x), 1)
7. # Giving the chart file a name.
8. png(file = "three_D_pie.jpg")
9. # Plotting the chart.
10. pie3D(x, labels = pie_percent, main = "Country Pie Chart",col = rainbow(length(x)))
11. legend("topright", c("India", "America", "Shri Lanka", "Nepal","Bhutan"), cex = 0.8,
12. fill = rainbow(length(x)))
13. #Saving the file.
14. [Link]()

Output: